Wyszukiwanie – Funkcja Wyszukaj

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

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

Ta lekcja może być obejrzana lub przeczytana poniżej.

Film wygląda najlepiej jeśli będzie odtwarzany w rozdzielczości 720p HD, rozdzielczość można zmienić dopiero po uruchomieniu filmu klikając na ikonie trybika która pojawi się w prawym dolnym rogu poniższego ekranu. Po kilku sekundach od zmiany obraz wyostrzy się.

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 i 18 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, co będzie wyszukiwanie w tym przykładzie jest to nazwa produktu, czyli 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, tabela 2. ma dwie kolumny, cena jest w drugiej z nich

W komórkę ‘Przeszukiwany_zakres’ wprowadzamy 0 dla dokładnego wyszukania, lub 1 dla wyszukania przybliżonego – nie polecam

Wprowadzając obszar należy użyć odwołania absolutnego 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 nic się nie znajduje 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ż dwóch produktów w ‘Tabeli 2‘ nie znaleziono, w miejscach tych został wprowadzony symbol błędu: #N/D!

Symbol ten nie wygląda zbyt dobrze, aby był niewidoczny możemy użyć formatowania warunkowego – co zostało opisane w lekcji 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 24.

Następnie zmieniamy ostatnią z formuł (komórka E24) tak aby dane były wyszukiwane z tabeli dla lutego.

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 PRAWDĄ 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 kolumnie A korzystając z nieco bardziej skomplikowanej funkcji:

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

Funkcja jeżeli dla produktów ze stycznia będzie dawała wynik 2, dla pozostałych produktów będzie dawała wynik 5.

Alternatywne rozwiązanie tego problemu przedstawiłem w przykładzie 8.

Przykład 4

(arkusz Funkcja Wyszukaj 4)

W tym przykładzie 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 kolumnach A i G, sumy kryteriów wg., których chcemy wyszukiwać.

Teksty łączymy posługując się znakiem &.

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

Zamiast dwóch kryteriów mamy już tylko jedno i możemy użyć funkcji WYSZUKAJ.PIONOWO 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, które połączymy w jedno kryterium.

Regularnie przygotowywałem kiedyś raport w którym wyszukiwanie pionowe opierało się na 4 połączonych kryteriach. Raport działał szybko i sprawnie.

Przykład 5.

(Arkusz ‘Funkcja Wyszukaj 5’)

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 6.

Najczęściej występujące problemy

(Arkusz ‘Funkcja Wyszukaj 6’)

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 6’ wg poniższego rysunku.

Rezultat przedstawiony poniżej z reguły komentowany jest przez użytkowników następująco ‘Jak to nie ma, przecież widzę że są, głupi 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 z Internetu.

Najszybszą metodą jego rozwiązania jest wpisanie 1 w dowolną komórkę, skopiowanie tej komórki i przemnożenie kolumny z liczbami przez tą jedynkę.

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.

Użycie polecenia zamień i zamiana wszystkich spacji na ‘nic’ pozwoli nam oczyścić dane.

Przed zamianami należy wybrać zakres aby nie popsuć nagłówków kolumn i ewentualnych innych tekstów.

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.

Przykład 7.

(Arkusze: ‘Funkcja Wyszukaj 7’, ‘Funkcja Wyszukaj 8’)

W tym przykładzie, danymi z tabeli w arkuszu ‘Funkcja Wyszukaj 7’, chcielibyśmy uzupełnić tabelę w arkuszu ‘Funkcja Wyszukaj 8’.

Tabele różnią się strukturą zarówno wierszy jak i kolumn, a chcielibyśmy posłużyć się tylko jedną formułą.

W tabeli z danymi oprócz miesięcy są także kwartały, aby rozwiązać ten problem nad miesiącami w tabeli docelowej wpisujemy w której kolumnie tabeli źródłowej jest każdy z miesięcy.

Tam gdzie występuje kwartał numeracja ‘przeskakuje’ o jeden.

Następnie wprowadzamy funkcję wyszukującą, gdzie numerem kolumny jest liczba z wiersza drugiego.

Dodajemy adresowanie absolutne aby formuła była odpowiednia dla wszystkich komórek tabeli.

Tak przygotowaną formułę możemy skopiować na całą tabelę.

Cyfry z wiersza drugiego możemy ukryć zmieniając ich kolor na biały lub ukrywając wiersz.

Przykład 8.

(Arkusze: ‘Funkcja Wyszukaj 9’)

Ponownie spróbujemy odnaleźć cenę dla produktów w Tabeli 1. Ceny znajdują się w 2 tabelach

W przykładzie 3 zaproponowałem rozwiązanie tego problemu gdy kolejność produktów w obu tabelach jest taka sama. Poniższe rozwiązanie będzie dawało poprawne wyniki niezależnie od kolejności produktów.

Jeżeli mamy wyszukiwać w kilku tabelach a wybór zależy od jakiegoś parametru, w tym przypadku od miesiąca z kolumny C, a parametr ten przyjmuje tylko kilka wartości, możemy skorzystać z połączenia funkcji WYSZUKAJ.PIONOWO i JEŻELI.

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

Funkcja JEŻELI, gdy w komórce kolumny C jest „styczeń” przyjmie jako wartość wynikową kolumny G:H, w przeciwnym razie kolumny J:H.

Wynik funkcji JEŻELI jest następnie użyty w funkcji WYSZUKAJ.PIONOWO.

Gdyby parametr miesiąc przyjmował np. 3 wartości należałoby użyć kolejnej funkcji JEŻELI.

Gdyby parametr miał mieć więcej wartości niż 4 nie warto składać wiele funkcji JEŻELI, szybciej będzie przyjąć rozwiązanie zaproponowane w Przykładzie 4 z połączeniem kilku warunków w jeden.

Przykład 9.

(Arkusze: ‘Funkcja Wyszukaj 10’)

Do tej pory używaliśmy funkcji WYSZUKAJ.PIONOWO do wyszukiwania dokładnych wartości.

Funkcji tej można też używać do wyszukiwania wartości w zadanych przedziałach.

W takich przypadkach WYSZUKAJ.PIONOWO działa poprawnie wyłącznie gdy lista gdzie szukamy jest posortowana rosnąco.

W poniższym przykładzie chcemy ustalić wysokość rabatu na podstawie wartości zamówienia.

Tabela z przedziałami wielkości zamówień i wysokości odpowiadającego im raportu ma 6 pozycji, rabat jest przyznawany jeśli kwota zamówienia wynosi co najmniej tyle co początek danego przedziału w tabeli i nie więcej niż początek kolejnego przedziału.

W argumencie funkcji ‘Przeszukiwany_zakres’ wprowadzamy wartość 1, aby Excel znajdował „najlepsze dopasowanie” jak podaje opis funkcji.

„Najlepsze dopasowanie” nie oznacza najbliższej wartości ale właśnie wartość dla dolnej granicy przedziału.

Dobrze to widać na poniższym przykładzie. Dla kwoty 9 999 zł rabat nadal wynosi 2%, pomimo tego że liczbą znacznie bliższą niż 5 000 zł jest 10 000 zł.

Chciałbym jeszcze raz podkreślić że funkcja działa poprawnie wyłącznie gdy lista jest posortowana rosnąco.

Poniżej na liście nie posortowanej rosnąco wyszukiwana jest niepoprawna wartość.

Podobny przykład w tym samym arkuszu dotyczy wyszukiwania oceny z kolokwium w zależności od ilości zdobytych punktów.

Przykład 10.

(Arkusze: ‘Funkcja Wyszukaj 11’)

Funkcja WYSZUKAJ ma tą ogromną przewagę nad WYSZUKAJ.PIONOWO, że może znajdować nie tylko szukaną wartość lub elementy w kolumnach na prawo od niej ale także na lewo lub nawet w tabeli będącej zupełnie gdzie indziej.

Funkcja ta należy do nielicznej grupy funkcji, które mają kilka list możliwych do zastosowania argumentów.

Pierwszym krokiem będzie więc wybranie pierwszej z 2 opcji.

Następnie wprowadzamy argumenty jak poniżej, nazwy produktu szukamy w kolumnie I a cena na której nam zależy jest na lewo od I w kolumnie H.

Tak przygotowaną formułę kopiujemy w dół.

Niestety nazwy produktów w tabeli 2 nie były posortowane rosnąco i pomimo tego że ‘produkt 11’ (zaznaczony żółtym tłem) jest w tej tabeli nie został on odnaleziony.

Po posortowaniu wg kolumny „produkt” (kolumna I) właściwe ceny dla wszystkich produktów zostały odnalezione.

Przykład 11.

(Arkusze: ‘Funkcja Wyszukaj 12’)

Wyszukiwanie w pionie i poziomie jednocześnie.

W ostatnim przykładzie tej lekcji będziemy wyszukiwać zarówno po wierszach jak i kolumnach.

W górnej tabeli tego arkusza znajdują się dane źródłowe z informacją o ilościach lekarzy odwiedzanych przez przedstawicieli medycznych w 10 regionach i z podziałem na specjalizacje lekarskie.

Poproszono nas o skopiowanie danych do tabeli która ma inny układ nagłówków zarówno dla wierszy, jak i dla kolumn.

Moglibyśmy skorzystać z 7 funkcji WYSZKUKAJ.PIONOWO, innej dla każdego z regionów. Sortowanie tablicy źródłowej lub docelowej nie wchodzi w grę ponieważ mamy inną ilość zarówno regionów jak i specjalizacji.

Użyjemy złożenia funkcji INDEKS i PODAJ POZYCJE

Dla funkcji INDEKS wybieramy pierwszą z list argumentów.

W argumentach funkcji INDEKS wprowadzamy zakres danych źródłowych czyli C4:L8.

Numer wiersza zostanie określony przy użyciu funkcji PODAJ.POZYCJĘ której argumenty to: szukamy pierwszej ze specjalizacji która jest w komórce B19 i szukamy jej w liście specjalizacji tabeli źródłowej czyli B4:B8.

Numer kolumny także zostanie określony przy użyciu funkcji PODAJ.POZYCJĘ której argumenty to: szukamy pierwszego z regionów, który jest w komórce C18 i szukamy go na liście regionów tabeli źródłowej czyli C3:L3.

Funkcja INDEKS poda wartość będącą w 4 kolumnie i 4 wierszu tablicy będącej pierwszym argumentem funkcji.

Dodajemy adresowanie absolutne aby formułę można było skopiować do wszystkich komórek tabeli.

Po skopiowaniu formuły do wszystkich komórek tabeli poprawne dane są znajdowane.

Dla pewności możemy jeszcze sprawdzić przykładową wartość.

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