Funkcja Wyszukaj

Przykłady opisane w tej lekcji dostępne są w arkuszu Excela: Funkcja Wyszukaj.xls, tylko ich samodzielne przerobienie daje gwarancję zapamiętania tej lekcji.

Przykłady dla wszystkich lekcji szkolenia Excel 2003: ExcelSzkolenie.pl Cwiczenia Excel 2003.zip

Funkcja wyszukaj jest jedną z najbardziej użytecznych funkcji Excela.

Jej działanie zostanie wytłumaczone na kilku prostych przykładach, w których będzie niewielka ilość danych, w rzeczywistości jednak funkcja ta jest najczęściej używana i najbardziej użyteczna w przypadku pracy z tabelami z ogromną ilością danych.

Przykład 1.

(Arkusz ‘Funkcja Wyszukaj 1’)

W poniższym przykładzie mamy 2 tabele z danymi dla 20 produktów, chcielibyśmy wprowadzić ceny z Tabeli 2 do tabeli 1.

Stajemy w komórce D4 i klikamy symbol funkcji

W oknie ‘Wyszukaj funcję:’ wpisujemy tekst ‘wyszukaj’, po czym z poniższego okna ‘Wybierz funcje’ wybieramy ‘WYSZUKAJ.PIONOWO’

Wprowadzamy następujące dane:

· W komórkę ‘Szukana_warotść’ wprowadzamy, po czym będzie wyszukiwanie w tym przykładzie jest nazwa produktu, komórka B4

· W komórkę ‘Tabela_tablica’ wprowadzamy obszar, w którym jest tabela zawierająca dane, które mają być ściągnięte

· W komórkę ‘Nr_indeksu_kolumny’ wprowadzamy numer kolumny, w którym są dane, które mają być ściągnięte

· W komórkę ‘Przeszukiwany_zakres’ wprowadzamy 0 dla dokładnego wyszukania, lub 1 dla wyszukania mniej-więcej – nie polecam!

Wprowadzając obszar należy użyć odwołania bezwzględnego tak, aby przy kopiowaniu funkcji z komórki D4 do poniższych komórek obszar wyszukiwania nie zmieniał się.

Pierwszą kolumną obszaru, w którym dane mają być wyszukiwane musi być kolumna zawierająca to, po czym wyszukujemy (w naszym przykładzie nazwy produktów), jeżeli układ tabeli jest inny należy go zmienić przed skorzystaniem z funkcji wyszukaj.

Jeżeli występuje najmniejsza różnica w nazwach produktów np. ktoś dopiszę dodatkową spację, wyszukiwanie nie powiedzie się.

Podobnie, jeśli wyszukujemy np. po numerach produktów/klientów i w jednej z tabel numery te są wprowadzone jako wartości liczbowe a w drugiej jako tekst również wartości nie zostaną wyszukane. W opisanych powyżej przypadkach należy najpierw ujednolicić nazwy i ich format w obu tabelach.

Po przeprowadzeniu powyższych operacji i skopiowaniu funkcji z komórki D4 do poniższych komórek, Tabela 1 powinna wyglądać tak jak na poniższym rysunku.

Ponieważ 2 produktów w ‘Tabeli 2‘ nie znaleziono w miejscach tych został wprowadzony symbol: #N/D!

Symbol ten nie wygląda zbyt dobrze, aby był niewidoczny należy użyć formatowania warunkowego – lekcja Formatowanie Warunkowe.

Jeżeli w Tabeli 2 któraś z nazw produktów występowałaby dwukrotnie wyszukana zostanie tylko wartość dla pierwszego z wystąpień, Excel nie w żaden sposób nie poinformuje nas, że zaszła taka sytuacja.

Przykład 2.

(Arkusz ‘Funkcja Wyszukaj 2’)

Celem tego przykładu jest utworzenie jednej formuły, która wyszuka ceny odpowiednio dla stycznia lub lutego.

Pierwszym krokiem będzie upewnienie się że kolejność produktów w tabelach 2 i 3 jest taka sama. Najprościej jest zrobić to wprowadzając np. w komórce ‘L4’ formułę: ‘=G4=J4’ i kopiując ją do końca tabeli 3. Jeżeli wynik dla wszystkich komórek będzie prawda możemy przejść do kolejnego kroku, gdyby tak nie było należałoby najpierw doprowadzić do takiej samej kolejności w obu tabelach.

Jeżeli potraktujemy tabele 2 i 3 jako jedną tabelę, cena w styczniu będzie znajdować się 2 kolumnie takiej tabeli a cena w lutym w 5.

Przed tabelą 1, dopisujemy cyfrę 2 dla wierszy, w których miesiąc to styczeń i cyfrę 5 dla wierszy, w których miesiąc to luty.

Możemy teraz wprowadzić funkcję wyszukaj w komórce E4, tak jak to pokazane na poniższym rysunku.

Funkcja będzie pobierać dane z 2 kolumny, jeżeli w kolumnie A jest 2 i z 5 kolumny, jeżeli w kolumnie A jest 5.

Po skopiowaniu formuły do poniższych komórek wszystkie koszty zostaną wyszukane.

Można ominąć konieczność wprowadzania oznaczeń w kolumnę A korzystając z nieco bardziej skomplikowanej funkcji:

=WYSZUKAJ.PIONOWO(B4;G:K;JEŻELI(C4="styczeń";2;5);0)

Przykład 3.

(Arkusz ‘Funkcja Wyszukaj 3’)

Ponownie wyszukamy ceny wg 2 kryteriów: nazwy produktu i miesiąca. Ceny znajdują się w 2 tabelach.

Poniższy sposób może wydawać się skomplikowany ale jest bardziej uniwersalny i może być zastosowany także dla większej ilości kryteriów.

Ponieważ funkcja wyszukaj nie daje możliwości wyszukiwania wg wielu kryteriów ani w kilku tabelach na raz, najpierw będziemy musieli zmienić układ danych w tabelach i połączyć kryteria.

Połączymy tabele 2 i 3 tworząc tabele 4 i dodając do niej miesiąc, którego dotyczy dana cena, najlepiej, jeśli układ kolumn będzie taki sam jak w tabeli 1.

Kolejnym krokiem będzie wprowadzenie w kolumnie H i B funkcji ‘ZŁĄCZ.TEKSTY’, która sumuje kryteria wg., których chcemy wyszukiwać.

Na poniższym rysunku widać wprowadzanie tej funkcji do komórki ‘H4’, w tym przypadku funkcja łączy teksty z komórek ‘I4’ i ‘J4’. Kopiujemy tą funkcję do komórek od H4 do H43 i od B4 do B29.

Teraz możemy już użyć funkcji wyszukaj wprowadzając wartości tak jak na poniższym rysunku. Właściwe ceny zostaną wyszukane.

Na tej samej zasadzie możemy wyszukiwać dane dla większej ilości kryteriów.

Przykład 4.

(Arkusz ‘Funkcja Wyszukaj 4’)

Analogicznie działa funkcja WYSZUKAJ.POZIOMO z tą tylko różnicą, że zgodnie z nazwą wyszukuje w kolejnych kolumnach a nie w wierszach, jest użyteczna dla tabel, w których dane umieszczone są ‘poziomo’.

Przećwiczmy ją na poniższym znanym już nam przykładzie, do Tabeli 1 należy dodać ceny produktów znajdujące się w tabeli 2.

Wszystkie argumenty funkcji WYSZUKAJ.POZIOMO wprowadzamy analogicznie do WYSZUKAJ.PIONOWO, na poniższym rysunku przedstawiono właściwe argumenty dla tego przykładu.

Po kliknięciu OK kopiujemy funkcję do pozostałych komórek tabeli. Powinniśmy uzyskać rezultat taki jak przedstawiony poniżej.

Przykład 5.

Najczęściej występujące problemy

(Arkusz ‘Funkcja Wyszukaj 5’)

Dość często zdarza się, że Excel nie znajduje danych które naszym zdaniem powinny być w tabeli lub też nawet widzimy że się w niej znajdują.

Wprowadźmy funkcję WYSZUKAJ.PIONOWO w Arkuszu ‘Funkcja Wyszukaj 5’ wg poniższego rysunku.

Rezultat przedstawiony poniżej z reguły komentowany jest przez użytkownika następująco ‘Jak to nie ma przecież widzę że są, co za głupi komputer/Excel.’

(Dane, które teoretycznie powinny być znalezione, oznaczono zielonym tłem.)

Często dane liczbowe są wprowadzone w Excelu jako tekst i zmiana formatu komórki z tekstowego na liczbowy nie załatwia tego problemu.

Zdarza się tak często wtedy gdy dane do Excela są importowane z innego programu lub ze strony www.

Najszybszą metodą jego rozwiązania jest wprowadzenie w innej kolumnie formuły w której przemnażamy liczby wprowadzone jako tekst przez jeden tak jak na poniższym rysunku.

Należy teraz obszar A4:A10 skopiować i wkleić jako wartości na obszarze B4:B10.

Funkcja WYSZUKAJ.PIONOWO od razu znalazła odpowiednie dane i zostały one wyświetlone w komórkach F4 i F5.

Wciąż jednak pozostał problem kodów, które nie są liczbą.

W tym przypadku od razu widać gdzie jest produkt, którego poszukujemy, ale w nieuproszczonej tabeli zawierającej kilka, kilkanaście lub kilkadziesiąt tysięcy wierszy należałoby się posłużyć poleceniem Znajdź aby ustalić w której z komórek kolumny B on się znajduje.

Po znalezieniu odpowiedniego kodu, dokonujemy szczegółowej inspekcji ciągu znaków i odkrywamy że przed literą F znajduje się zbędna spacja. Po usunięciu spacji funkcja WYSZUKAJ.PIONOWO, znajdzie kod bez problemu.

W drugim przypadku spacja znajdowała się na końcu kodu.

Jeżeli występuje najmniejsza różnica w nazwach produktów wyszukiwanie nie powiedzie się, powyżej opisałem tylko te najczęściej spotykane problemy.

Więcej o funkcjach wyszukaj można przeczytać w lekcjach: ‘Profesjonalne Raporty I’ i ‘Profesjonalne Raporty dla Zaawansowanych’

Szkolenie Excel 2003 Szkolenie Excel 2007