Automatyczne wypełnianie rejestru zamówień

Od wielu miesięcy prowadzisz rejestr zamówień na poszczególne towary oferowane przez Twoją firmę. Przy wszystkich transakcjach jesteś zmuszony każdorazowo wpisywać informacje o konkretnym towarze: jego numer identyfikacyjny, cenę, gatunek i oznaczenie producenta. Najbardziej uciążliwe i czasochłonne okazuje się wyszukanie tych danych w bazie zbiorczej, znajdującej się w oddzielnym arkuszu. Poznaj więc kilka wskazówek, dzięki którym nie będziesz musiał wszystkich wartości wpisywać ręcznie. Wystarczy, że podasz datę zamówienia i nazwę towaru, a resztę danych Excel uzupełni automatycznie!

Przykładowy niewypełniony rejestr zamówień i bazę towarów przedstawiają rysunki 1 i 2.

Rys. 1. Roboczy rejestr zamówień

Rys. 2. Baza (spis) ze szczegółowymi danymi dotyczącymi towarów

Aby przyspieszyć wypełnianie rejestru danymi pochodzącymi z bazy, w pierwszej kolejności wstawimy w kolumnie B arkusza rejestr rozwijane listy, z których będziesz mógł wybierać nazwy towarów, zamiast każdorazowo wpisywać je ręcznie z klawiatury. Następnie zbudujemy formułę, którą automatycznie będzie wypełniać pozostałe komórki z informacjami dotyczącymi towarów.

W tym celu:

1. Przejdź do arkusza spis i zaznacz wszystkie nazwy towarów z kolumny A.

2. Z menu Wstaw wybierz polecenie Nazwa/Definiuj.

3. W oknie dialogowym Definiuj nazwy, w polu Nazwy w skoroszycie wpisz towary.

4. Kliknij przycisk Dodaj, a następnie OK.

5. Teraz przejdź do arkusza rejestr i zaznacz komórkę B2.

6. Z menu Dane wybierz polecenie Sprawdzanie poprawności i przejdź do zakładki Ustawienia.

7. W polu Dozwolone wybierz z rozwijanej listy pozycję Lista, a w polu Źródło wpisz:

=towary

8. Zatwierdź OK.

Obok aktywnej komórki powinien pojawić się przycisk rozwijanej listy. Gdy go klikniesz, przekonasz się, że wszystkie nazwy towarów możesz wybierać z listy, a nie wpisywać ręcznie.

Rys. 3. Rozwijana lista z nazwami towarów

9. Skorzystaj z uchwytu wypełniania i przenieś właściwość komórki B1 na pozostałe w kolumnie B.

Teraz nadszedł czas, aby zautomatyzować wprowadzanie danych do kolejnych pól rejestru.

W tym celu:

10. Do komórki C2 wpisz następującą formułę:

=INDEKS(spis!$A$2:$E$10;PODAJ.POZYCJĘ($B2;spis!$A$2:$A$10;0);PODAJ.POZYCJĘ(C$1;spis!$A$1:$E$1;0))

Przeszukuje ona cały spis towarów i zwraca identyfikator towaru odpowiadający nazwie towaru wprowadzonej w komórce B2.

11. Skopiuj ją w prawo aż do komórki w kolumnie F oraz do leżących poniżej.

Wskazówka
Nic nie stoi na przeszkodzie, abyś dostosował formułę do swoich potrzeb. Powinieneś zwrócić uwagę na jej następujące elementy i odpowiednio je zmodyfikować, zgodnie z układem danych w Twoim arkuszu:

- spis!$A$2:$E$10 – to zakres komórek, który formuła przeszukuje. Zwróć uwagę, że po nazwie arkusza znajduje się wykrzyknik;

- spis!$A$2:$A$10 – to obszar, w którym znajdują się nazwy towarów;

- spis!$A$1:$E$1 - to komórki, w których znajdują się nazwy kolumn bazy danych.

Na pewno zauważyłeś, że zostały automatycznie wypełnione pola dla pierwszego zamówienia, natomiast w komórkach poniżej pojawiły się symbole błędu.

Rys. 4. Automatycznie wypełnione pola dla pierwszego zamówienia

Zostaną one wypełnione poprawnymi danymi dopiero wtedy, gdy wybierzesz z listy kolejną nazwę towaru. Jeśli symbole błędów przeszkadzają Ci w pracy, możesz je ukryć, stosując pewien trik:

1. Zaznacz zakres komórek zawierający błędy i z menu Format wybierz polecenie Formatowanie warunkowe.

2. W pierwszym polu po lewej stronie ustaw Formuła jest, a obok wpisz następującą formułę:

=CZY.BŁĄD(C3)

3. Kliknij przycisk Formatuj, przejdź do zakładki Czcionka i ustaw biały kolor czcionki.

4. Zatwierdź ustawienia w obu otwartych oknach, klikając przycisk OK.

Rys. 5. Efekt zastosowania formatowania warunkowego

Dzięki takiemu sformatowaniu komórek biały kolor czcionki będzie zastosowany tylko i wyłącznie do błędów zwróconych przez formuły. Gdy podasz nazwę kolejnego towaru, dane w pozostałych kolumnach zostaną błyskawicznie wyświetlone, a Ty oszczędzisz czas i wysiłek.