Funkcja wyszukaj

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

Przykłady dla wszystkich lekcji szkolenia Excel 2007: ExcelSzkolenie.pl Cwiczenia Excel 2007.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 została użyta 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 funkcję:’ wpisujemy tekst ‘wyszukaj’, po czym z poniższego okna ‘Wybierz funkcję’ 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ę. Jeżeli pod i nad tabelą w której wyszukujemy dane nie znajduje się nic co zawiera nazwy które wyszukujemy wygodnym rozwiązaniem będzie zaznaczenie całych kolumn, tak jak to zrobiono w poniższym przykładzie.

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.

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 w żaden sposób nie poinformuje nas, że zaszła taka sytuacja.

Przykład 2

(arkusz Funkcja Wyszukaj 2)

Przećwiczymy teraz używanie funkcji WYSZUKAJ.PIONOWO na tabeli 1 do której chcielibyśmy dociągnąć ceny ze stycznia i lutego znajdujące się w tabelach 2 i 3.

Dla produktów ze stycznia argumenty funkcji będą wyglądać tak jak poniżej.

Funkcje tą kopiujemy do wiersza 23.

Argumenty funkcji dla lutego będą wyglądać tak jak poniżej.

Funkcję kopiujemy do końca tabeli.

Przykład 3

(arkusz Funkcja Wyszukaj 3)

Po przeanalizowaniu rozwiązania z poprzedniego przykładu możemy się zastanowić czy nie dałoby się zrobić tego szybciej.

W tym przykładzie ponownie wykorzystamy tabele użyte w przykładzie 2.

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)

W Przykładzie 5 sposób z podaniem w numerze indeksu kolumny odwołania do komórki w arkuszu

Przykład 4

(arkusz Funkcja Wyszukaj 4)

W przykładzie 2 musimy wyszukać ceny wg 2 kryteriów: nazwy produktu i miesiąca. Ceny znajdują się w 2 tabelach.

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

Połączymy tabele 2 i 3 i dodając do niej kolumnę z nazwą odpowiedniego miesiąca, najlepiej, jeśli układ kolumn będzie taki sam jak w tabeli 1.

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

Na poniższym rysunku widać wprowadzanie tej funkcji do komórki ‘A4’, w tym przypadku funkcja łączy teksty z komórek ‘B4’ i ‘C4’.

Kopiujemy tą funkcję do komórek od A4 do A35 i od G4 do G43.

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ć danych dla większej ilości kryteriów, które połączymy w jedno kryterium.

Przykład 5

(Arkusze: Funkcja Wyszukaj 5, Funkcja Wyszukaj 6)

Przykład w budowie

Przykład 6

(Arkusz ‘Funkcja Wyszukaj 7’)

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.

Najczęściej występujące problemy

(Arkusz ‘Funkcja Wyszukaj 8’)

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 8’ 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.’

(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 ‘Profesjonalne Raporty dla Zaawansowanych’