Masz listę numerów uczniów, kodów produktów albo nazwisk i chcesz, aby Excel sam zwrócił odpowiadającą im ocenę, cenę czy klasę. Najczęściej służy do tego funkcja WYSZUKAJ.PIONOWO, choć wynik będzie poprawny tylko wtedy, gdy właściwie ustawisz zakres oraz tryb dopasowania. Jeden pominięty argument potrafi zwrócić pozornie wiarygodną, lecz błędną wartość.
Najważniejsza decyzja dotyczy sposobu dopasowania
- Pierwsza kolumna zakresu musi zawierać szukaną wartość.
- Ostatni argument decyduje, czy Excel szuka dokładnego wyniku, czy najbliższego progu.
- Tabele z kodami wymagają innego ustawienia niż tabele z ocenami lub rabatami.
- Wyszukiwanie w lewo wymaga INDEKS i PODAJ.POZYCJĘ albo X.WYSZUKAJ.
- Zakres z symbolem $ nie przesuwa się po skopiowaniu formuły.
WYSZUKAJ.PIONOWO działa od pierwszej kolumny do wyniku
Wyobraź sobie tabelę, w której w kolumnie A znajdują się numery uczniów, w kolumnie B nazwiska, a w kolumnie C oceny. Jeżeli numer ucznia wpiszesz do komórki E2, formuła może wyglądać tak:
=WYSZUKAJ.PIONOWO(E2;A2:C30;3;FAŁSZ)
Excel szuka wartości z E2 w pierwszej kolumnie wskazanego zakresu, czyli w A2:A30. Gdy znajdzie odpowiedni wiersz, zwraca dane z trzeciej kolumny zakresu A2:C30, a więc z kolumny C. Numer 3 nie oznacza trzeciej kolumny całego arkusza, tylko trzecią kolumnę wybranego zakresu.
Dokumentacja Microsoft dotycząca funkcji WYSZUKAJ.PIONOWO opisuje cztery elementy formuły w podobny sposób.
| Element formuły | Przykład | Znaczenie |
|---|---|---|
| Szukaną wartość | E2 |
Numer, kod lub nazwa, którą Excel ma odnaleźć. |
| Zakres tabeli | A2:C30 |
Obszar, w którym znajduje się kolumna wyszukiwania i wynik. |
| Numer kolumny | 3 |
Kolumna, z której ma zostać zwrócona wartość. |
| Tryb dopasowania | FAŁSZ lub PRAWDA |
Wybór między dopasowaniem dokładnym a przybliżonym. |
Uważam, że najlepiej czytać tę formułę jak krótkie polecenie: znajdź wartość, przeszukaj tabelę, zwróć dane z wybranej kolumny, a na końcu zdecyduj, jak dokładne ma być dopasowanie. Najwięcej pomyłek pojawia się właśnie przy ostatnim fragmencie.
Pusty czwarty argument może podsunąć złą ocenę
Wiele osób zakłada, że pominięcie ostatniego argumentu oznacza zwykłe wyszukiwanie konkretnej wartości. W rzeczywistości pusty argument działa jak PRAWDA, czyli uruchamia dopasowanie przybliżone.
Według materiału Microsoft o funkcjach wyszukiwania wpisanie PRAWDA albo pozostawienie czwartego argumentu pustego pozwala zwrócić najbliższe dopasowanie. Przy kodach uczniów, numerach faktur i identyfikatorach produktów takie ustawienie może przypisać dane z niewłaściwego wiersza.
| Sytuacja | Objaw | Prawdopodobna przyczyna | Co zrobić |
|---|---|---|---|
| Formuła nie ma ostatniego argumentu | Excel zwraca wynik, choć kod nie występuje w tabeli | Włączone jest dopasowanie przybliżone | Dodaj właściwy tryb dopasowania do formuły |
Pojawia się #N/D! |
Wartość wygląda podobnie do tej w tabeli, lecz nie zostaje znaleziona | Spacja na początku, spacja na końcu albo liczba zapisana jako tekst | Usuń zbędne znaki i ujednolić format danych |
Pojawia się #ADR! |
Formuła odwołuje się do nieistniejącej kolumny zakresu | Numer kolumny jest większy niż szerokość tabeli | Zmniejsz numer kolumny albo poszerz zakres |
| Po skopiowaniu formuły zmieniają się wyniki | Każdy kolejny wiersz pobiera dane z innego obszaru | Zakres przesuwa się razem z formułą | Zablokuj zakres, na przykład $A$2:$C$30 |
Przy kopiowaniu formuł przydatne jest adresowanie bezwzględne, czyli zapis z symbolem $. Poradnik Microsoft dotyczący rozwiązywania problemów z WYSZUKAJ.PIONOWO wskazuje, że stabilny zakres ogranicza błędy po przenoszeniu formuły do kolejnych komórek.
Przy ocenach przybliżenie działa, bo liczy się próg
Tryb przybliżony nie jest z definicji błędny. Przydaje się wtedy, gdy tabela opisuje przedziały, a nie pojedyncze identyfikatory. Dobrym przykładem może być szkolna skala ocen użyta wyłącznie jako ćwiczenie w arkuszu.
| Minimalny wynik | Ocena |
|---|---|
| 0 | niedostateczny |
| 50 | dopuszczający |
| 60 | dostateczny |
| 75 | dobry |
| 90 | bardzo dobry |
Jeżeli tabela znajduje się w zakresie A2:B6, a wynik ucznia jest w D2, możesz użyć formuły:
=WYSZUKAJ.PIONOWO(D2;A2:B6;2;PRAWDA)
Dla wyniku 78 Excel wybierze próg 75 i zwróci ocenę „dobry”. Dla wyniku 49 nie znajdzie odpowiedniego progu, ponieważ jest on niższy niż najmniejsza wartość w pierwszej kolumnie. Tabela progów musi być ułożona rosnąco, ponieważ przybliżenie opiera się na ostatniej wartości nie większej od wyniku.
Materiał edukacyjny Zintegrowanej Platformy Edukacyjnej pokazuje podobny mechanizm na przykładzie ceny, przy której Excel wybiera najbliższą niższą wartość.
- Gdy szukasz numeru ucznia, kodu produktu lub nazwiska, użyj dopasowania dokładnego.
- Gdy szukasz oceny, stawki, rabatu albo przedziału cenowego, dopasowanie przybliżone ma sens tylko przy uporządkowanej tabeli progów.
- Gdy najniższy próg nie obejmuje możliwego wyniku, dodaj odpowiednią wartość graniczną albo obsłuż błąd osobną formułą.
Gdy wynik jest po lewej, zmień narzędzie
Największe ograniczenie tej funkcji widać wtedy, gdy szukana wartość znajduje się po prawej stronie wyniku. Jeżeli w kolumnie B masz nazwę miejscowości, a w kolumnie A liczbę mieszkańców, funkcja WYSZUKAJ.PIONOWO nie zwróci poprawnie danych z lewej kolumny.
| Układ danych | Rozsądny wybór | Powód |
|---|---|---|
| Szukany kod jest w pierwszej kolumnie, wynik po prawej | WYSZUKAJ.PIONOWO | Układ pasuje do sposobu działania funkcji. |
| Szukana nazwa jest po prawej, wynik po lewej | X.WYSZUKAJ | Funkcja może zwracać dane z kolumny znajdującej się po dowolnej stronie. |
| Plik musi działać w starszej wersji Excela | INDEKS + PODAJ.POZYCJĘ | To rozwiązanie omija ograniczenie kierunku wyszukiwania. |
| Nowy arkusz ma być łatwy do rozbudowy | X.WYSZUKAJ | Nie wymaga wpisywania numeru kolumny zwrotnej. |
Microsoft opisuje połączenie INDEKS i PODAJ.POZYCJĘ jako rozwiązanie dla sytuacji, w której szukana wartość nie znajduje się w pierwszej kolumnie. Przykładowa formuła może wyglądać tak:
=INDEKS(A2:A20;PODAJ.POZYCJĘ(B2;B2:B20;0))
W nowszym Excelu podobny efekt daje:
=X.WYSZUKAJ(B2;B2:B20;A2:A20;"Brak")
Dokumentacja funkcji X.WYSZUKAJ wskazuje, że może ona przeszukiwać jedną kolumnę i zwracać wynik z innej, niezależnie od kierunku. Microsoft zaznacza też, że funkcja nie jest dostępna w Excelu 2016 i Excelu 2019.
Gdybym tworzył nowy arkusz w wersji obsługującej X.WYSZUKAJ, wybrałbym tę funkcję, szczególnie przy tabelach, które będą później rozbudowywane. Do starszych plików przekazywanych między różnymi komputerami bezpieczniejsza może być klasyczna formuła albo połączenie INDEKS i PODAJ.POZYCJĘ.
Przed wysłaniem arkusza wykonaj krótki test
Formuła może wyglądać poprawnie, a mimo to pobierać niewłaściwe dane. Przed przekazaniem arkusza nauczycielowi, uczniowi albo współpracownikowi przejdź przez trzy krótkie kontrole.
- Wpisz znany identyfikator. Wybierz wiersz, którego wynik da się łatwo sprawdzić ręcznie, i porównaj go z rezultatem formuły.
- Sprawdź wartość nieistniejącą. Dzięki temu zobaczysz, czy pojawia się
#N/D!, czy Excel zwraca dane z sąsiedniego progu. - Przeciągnij formułę w dół. Jeśli zakres zmienia się przy kopiowaniu, zablokuj go odwołaniami bezwzględnymi.
Najprostsza zasada brzmi: identyfikator wymaga dokładności, a przedział wymaga porządku. Kiedy rozpoznasz, do której z tych dwóch sytuacji należy tabela, wybór funkcji i argumentów przestaje być zgadywaniem.