WYSZUKAJ.PIONOWO w praktyce – dopasowanie dokładne i przybliżone, błędy #N/D! i #ADR!, blokowanie zakresu dolarami i łączenie danych z dwóch plików po kluczu.
WYSZUKAJ.PIONOWO odpowiada na jedno pytanie: „mam klucz, podaj mi resztę wiersza". Funkcja przechodzi w dół pierwszą kolumnę wskazanego zakresu, znajduje w niej szukaną wartość i zwraca to, co stoi w tym samym wierszu, w kolumnie o podanym numerze. Na maturze to podstawowe narzędzie do łączenia danych z kilku plików: dane osobowe są w jednym pliku, zdarzenia w drugim, a spina je identyfikator powtórzony w obu.
Cztery zasady, bez których funkcja zwraca błąd albo – gorzej – zły wynik bez błędu:
B2:E100 kolumna B ma numer 1, a E numer 4.FAŁSZ (albo 0) to dopasowanie dokładne, PRAWDA (albo 1) to dopasowanie przybliżone. Pominięcie tego argumentu Excel rozumie jako PRAWDA – i to jest najczęstsze źródło cichych pomyłek."103" to dla Excela dwie różne wartości; spacja na końcu klucza też psuje dopasowanie.Dopasowanie przybliżone nie jest błędem sztuki – ma swoje zastosowanie. Wymaga jednak, żeby pierwsza kolumna tabeli była posortowana rosnąco, i zwraca wiersz o największej wartości mniejszej lub równej szukanej. Tak działają progi: punkty na ocenę, obrót na rabat, wynik na przedział. Wszędzie tam, gdzie klucz ma być trafiony co do znaku, wpisuj FAŁSZ. Więcej o samych komunikatach znajdziesz w błędach w Excelu.
W polskiej wersji Excela argumenty oddzielamy średnikiem. Argumenty zapisane w nawiasach kwadratowych są opcjonalne.
=WYSZUKAJ.PIONOWO(szukana_wartość; tabela_tablica; nr_indeksu_kolumny; [przeszukiwany_zakres])
=WYSZUKAJ.POZIOMO(szukana_wartość; tabela_tablica; nr_indeksu_wiersza; [przeszukiwany_zakres])
=X.WYSZUKAJ(szukana_wartość; szukana_tablica; zwracana_tablica; [jeżeli_nie_znaleziono]; [tryb_dopasowywania]; [tryb_wyszukiwania])
=JEŻELI.BŁĄD(wartość; wartość_jeżeli_błąd)
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Id | Nazwisko | Punkty | 103 | |
| 2 | 101 | Adamska | 42 | 999 | |
| 3 | 102 | Borowicz | 58 | ||
| 4 | 103 | Cieślak | 91 | ||
| 5 | 104 | Dąbrowa | 67 | ||
| 6 |
| Formuła | Wynik | Co się dzieje |
|---|---|---|
| =WYSZUKAJ.PIONOWO(D1;A2:C5;2;FAŁSZ) | Cieślak | w D1 jest 103, klucz znaleziony w kolumnie A, wynik z drugiej kolumny zakresu |
| =WYSZUKAJ.PIONOWO(D1;A2:C5;3;FAŁSZ) | 91 | ten sam wiersz, tylko trzecia kolumna zakresu |
| =WYSZUKAJ.PIONOWO(102;A2:C5;2;FAŁSZ) | Borowicz | klucz wpisany wprost, bez odwołania do komórki |
| =WYSZUKAJ.PIONOWO(102;A2:C5;1;FAŁSZ) | 102 | numer 1 to kolumna, w której szukamy – zwraca sam klucz |
| =WYSZUKAJ.PIONOWO(D2;A2:C5;2;FAŁSZ) | #N/D! | 999 nie występuje w kolumnie A, a dopasowanie dokładne zgłasza brak |
| =WYSZUKAJ.PIONOWO(D2;A2:C5;2) | Dąbrowa | pominięty czwarty argument to dopasowanie przybliżone: zwraca wiersz największego klucza nie większego niż 999, czyli wynik wyglądający poprawnie i całkowicie błędny |
| =WYSZUKAJ.PIONOWO(102,5;A2:C5;2;PRAWDA) | Borowicz | dopasowanie przybliżone: największy klucz mniejszy lub równy 102,5 to 102 |
| =WYSZUKAJ.PIONOWO(100;A2:C5;2;PRAWDA) | #N/D! | 100 jest mniejsze od najmniejszego klucza w kolumnie A, nie ma czego dopasować |
| =WYSZUKAJ.PIONOWO(103;A2:C5;4;FAŁSZ) | #ADR! | zakres ma trzy kolumny, czwartej po prostu nie ma |
| =WYSZUKAJ.PIONOWO(103;A2:C5;0;FAŁSZ) | #ARG! | numer kolumny mniejszy niż 1 |
| =WYSZUKAJ.PIONOWO("Cieślak";A2:C5;1;FAŁSZ) | #N/D! | nazwiska są w kolumnie B, a przeszukiwana jest wyłącznie pierwsza kolumna zakresu |
| =WYSZUKAJ.PIONOWO(103;B2:C5;2;FAŁSZ) | #N/D! | zakres zaczyna się od kolumny B, więc identyfikatora szuka wśród nazwisk |
| =WYSZUKAJ.PIONOWO("103";A2:C5;2;FAŁSZ) | #N/D! | tekst 103 to dla Excela co innego niż liczba 103 |
| =JEŻELI.BŁĄD(WYSZUKAJ.PIONOWO(D2;A2:C5;2;FAŁSZ);"brak") | brak | ten sam brak klucza, tylko z własnym komunikatem zamiast błędu |
| =WYSZUKAJ.PIONOWO(D1;$A$2:$C$5;3;FAŁSZ) | 91 | wynik identyczny, ale zablokowany zakres przetrwa kopiowanie formuły w dół |
| Polska nazwa | Angielska nazwa | Co robi |
|---|---|---|
| WYSZUKAJ.PIONOWO | VLOOKUP | szuka klucza w pierwszej kolumnie zakresu i zwraca wartość z tego samego wiersza |
| WYSZUKAJ.POZIOMO | HLOOKUP | to samo, ale klucze leżą w pierwszym wierszu zakresu |
| X.WYSZUKAJ | XLOOKUP | wyszukiwanie w obie strony, domyślnie dokładne (Excel 2021 i nowsze) |
| JEŻELI.BŁĄD | IFERROR | podmienia wynik formuły, gdy kończy się ona błędem |
| INDEKS | INDEX | zwraca wartość z zakresu wskazaną numerem wiersza i kolumny |
| PODAJ.POZYCJĘ | MATCH | zwraca numer pozycji szukanej wartości w zakresie |
| INDEKS + PODAJ.POZYCJĘ | INDEX + MATCH | para zastępująca WYSZUKAJ.PIONOWO, pozwala szukać również w lewo |
| WYSZUKAJ | LOOKUP | starsza funkcja, zawsze dopasowanie przybliżone na posortowanych danych |
| CZY.BRAK | ISNA | sprawdza, czy w komórce jest błąd #N/D! |
Pominięty czwarty argument. =WYSZUKAJ.PIONOWO(D1;A2:C5;2) nie zgłosi niczego podejrzanego, bo brak argumentu znaczy PRAWDA, czyli dopasowanie przybliżone. Excel zwróci wiersz najbliższego mniejszego klucza – dla nieistniejącego identyfikatora dostaniesz cudze nazwisko zamiast #N/D!. Przy łączeniu danych po kluczu czwarty argument to zawsze FAŁSZ. Ten sam błąd w drugą stronę: PRAWDA na nieposortowanej kolumnie daje wyniki, które wyglądają losowo, i nadal bez komunikatu.
Tabela nie zaczyna się od kolumny z kluczem. =WYSZUKAJ.PIONOWO(103;B2:C5;2;FAŁSZ) daje #N/D!, bo identyfikatorów szuka w kolumnie z nazwiskami. Zakres musi zaczynać się dokładnie tam, gdzie stoją klucze. Gdy kolumna z wynikiem leży na lewo od klucza, WYSZUKAJ.PIONOWO jest bezradna – wtedy albo przestaw kolumny, albo użyj pary INDEKS i PODAJ.POZYCJĘ: =INDEKS(A2:A5;PODAJ.POZYCJĘ("Cieślak";B2:B5;0)).
Brak dolarów przy kopiowaniu w dół. =WYSZUKAJ.PIONOWO(D2;A2:C5;2;FAŁSZ) przeciągnięta o wiersz niżej zmienia się w =WYSZUKAJ.PIONOWO(D3;A3:C6;2;FAŁSZ) – tabela „ucieka" w dół i po kilkudziesięciu wierszach zaczyna zwracać #N/D! dla kluczy, które w niej są. Zakres blokuje się dolarami: =WYSZUKAJ.PIONOWO(D2;$A$2:$C$5;2;FAŁSZ). Zmieniać ma się tylko odwołanie do szukanej wartości.
Zły numer kolumny. #ADR! oznacza, że nr_indeksu_kolumny jest większy niż liczba kolumn zakresu, a #ARG! – że jest mniejszy niż 1. Numer liczy się wewnątrz zakresu: w $B$2:$F$500 kolumna F ma numer 5, nie 6. Jeśli po rozszerzeniu tabeli wynik nagle zaczyna pochodzić z sąsiedniej kolumny, sprawdź, czy zakres i numer zmieniły się razem.
Liczba zapisana jako tekst. Po imporcie pliku tekstowego jedna kolumna bywa liczbami, a druga tekstem wyglądającym jak liczby (wyrównany do lewej). Wtedy =WYSZUKAJ.PIONOWO(103;A2:C5;2;FAŁSZ) zwraca #N/D!, mimo że 103 widać w kolumnie A. Klucze trzeba sprowadzić do jednego typu: tekst na liczbę mnożeniem przez 1 albo funkcją WARTOŚĆ, liczbę na tekst przez TEKST. Sprawdzianem jest =CZY.LICZBA(A2) w obu plikach naraz.
Spacja i niewidoczne znaki w kluczu. Klucz K001 ze spacją na końcu nie zrówna się z K001, a różnicy nie widać na ekranie. Długość porównaj funkcją DŁ, a odstępy usuń przez USUŃ.ZBĘDNE.ODSTĘPY – opis obu jest w funkcjach tekstowych. Najbezpieczniej wyczyścić kolumnę kluczy w obu plikach, zanim zaczniesz wyszukiwać.
Angielska wersja arkusza. Nazwy zależą od języka programu: VLOOKUP, HLOOKUP, IFERROR, INDEX, MATCH, a argumenty logiczne to TRUE i FALSE. Argumenty oddziela tam zwykle przecinek zamiast średnika, więc formuła przepisana z polskiego Excela nie zadziała bez zamiany separatorów.
W arkuszu jest tabela progów punktowych (kolumna A posortowana rosnąco), a w komórce D1 wynik jednego studenta. Wpisz wynik każdej formuły.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Prog | Ocena | 62 | ||
| 2 | 0 | 2 | |||
| 3 | 41 | 3 | |||
| 4 | 51 | 4 | |||
| 5 | 80 | 5 | |||
| 6 | 90 | 6 | |||
| 7 |
| Formuła | Wynik |
|---|---|
| =WYSZUKAJ.PIONOWO(62;A2:B6;2;PRAWDA) | |
| =WYSZUKAJ.PIONOWO(D1;A2:B6;2) | |
| =WYSZUKAJ.PIONOWO(90;A2:B6;2;PRAWDA) | |
| =WYSZUKAJ.PIONOWO(40;A2:B6;2;PRAWDA) | |
| =WYSZUKAJ.PIONOWO(89;A2:B6;2;PRAWDA) | |
| =WYSZUKAJ.PIONOWO(51;A2:B6;2;FAŁSZ) | |
| =WYSZUKAJ.PIONOWO(62;A2:B6;1;PRAWDA) |
Dokładnie 62 nie ma w kolumnie A, więc =WYSZUKAJ.PIONOWO(62;A2:B6;2;FAŁSZ) zwróci . Dopasowanie przybliżone wymaga, żeby kolumna A była posortowana , i sięga po próg , czyli największą wartość mniejszą lub równą 62. Pominięty czwarty argument Excel rozumie jako .
Dopasowanie dokładne (FAŁSZ albo 0 jako czwarty argument) szuka wartości równej co do znaku i zgłasza #N/D!, gdy jej nie ma. Dopasowanie przybliżone (PRAWDA, 1 albo pominięty argument) zwraca wiersz o największej wartości mniejszej lub równej szukanej i wymaga pierwszej kolumny posortowanej rosnąco. Do łączenia danych po identyfikatorze zawsze FAŁSZ, do progów – PRAWDA.
Najczęściej klucze różnią się typem albo zawartością, a nie wyglądem: liczba kontra tekst po imporcie, spacja na końcu, inna wielkość liter przy danych z różnych plików. Sprawdź =DŁ(A2) i =CZY.LICZBA(A2) po obu stronach. Druga częsta przyczyna to zakres bez dolarów, który uciekł przy kopiowaniu w dół.
WYSZUKAJ.PIONOWO tego nie potrafi – przeszukuje wyłącznie pierwszą kolumnę zakresu i patrzy w prawo. Rozwiązania są dwa: przestawić kolumny tak, żeby klucz był pierwszy, albo użyć pary INDEKS i PODAJ.POZYCJĘ, na przykład =INDEKS(A2:A5;PODAJ.POZYCJĘ("Cieślak";B2:B5;0)). W Excelu 2021 i Microsoft 365 to samo robi X.WYSZUKAJ.
Z numeru kolumny większego niż liczba kolumn w zakresie: =WYSZUKAJ.PIONOWO(103;A2:C5;4;FAŁSZ) prosi o czwartą kolumnę trzykolumnowego zakresu. Numer liczy się wewnątrz zakresu, więc po jego skróceniu trzeba poprawić także numer. Numer mniejszy niż 1 daje inny błąd – #ARG!.
Tak, jeśli formuła będzie kopiowana w dół albo w bok – a przy tysiącu wierszy zawsze jest. Bez $A$2:$C$5 odwołanie przesuwa się razem z formułą i tabela stopniowo wyjeżdża poza dane. Odwołanie do szukanej wartości zostaje względne, bo ma zmieniać się wiersz po wierszu.
Opakuj wyszukiwanie w JEŻELI.BŁĄD: =JEŻELI.BŁĄD(WYSZUKAJ.PIONOWO(D2;$A$2:$C$5;2;FAŁSZ);"brak"). Uwaga: to zasłania problem, a nie go rozwiązuje. Zanim podmienisz komunikat, policz, ile wierszy nie znalazło pary – funkcja CZY.BRAK zwraca dla nich PRAWDA.
Nie ma takiej pewności. X.WYSZUKAJ działa w Excelu 2021 i w Microsoft 365, a na komputerze egzaminacyjnym może być starsza wersja pakietu albo inny program biurowy – wtedy formuła zwróci #NAZWA?. Rozwiązanie oparte na WYSZUKAJ.PIONOWO albo na parze INDEKS i PODAJ.POZYCJĘ zadziała wszędzie.
Wczytaj każdy plik do osobnego arkusza, ustaw kolumnę z identyfikatorem jako pierwszą w zakresie tabeli słownikowej i w pliku ze zdarzeniami dodaj kolumnę pomocniczą z WYSZUKAJ.PIONOWO i argumentem FAŁSZ. Jedna kolumna pomocnicza to jedna doklejana wartość. Dopiero na tak uzupełnionej tabeli licz sumy, zliczenia i zestawienia.
Do tego hasła nie mamy jeszcze przypisanych pojedynczych zadań. Cały spis zadań maturalnych z tego działu jest tutaj:
Zagadnienia z tego samego obszaru matury – warto je powtórzyć razem: