Czytaj i pisz VLOOKUP, nadal obecny we wszystkich istniejących skoroszytach, także przy dopasowaniu przybliżonym.
Wyszukiwanie · 5 krótkich ćwiczeń w żywym arkuszu: sam wpisujesz każdą formułę i odpowiadaj na pytania, które każą przewidywać wyniki lub wykrywać błędy. Każda odpowiedź jest sprawdzana od razu.
Co będziesz ćwiczyć
Wyszukiwanie wartości i zwracanie kolumny
Zwracanie wartości z dalszej kolumny
Dlaczego VLOOKUP potrzebuje klucza w pierwszej kolumnie
WYSZUKAJ.PIONOWO (Angielski: VLOOKUP)(co; tabela; numer kolumny; FALSE) szuka w pierwszej kolumnie tabeli i zwraca wartość z kolumny o podanym numerze (1 = pierwsza kolumna). FALSE oznacza dopasowanie dokładne.
Z TRUE (dopasowanie przybliżone) WYSZUKAJ.PIONOWO (Angielski: VLOOKUP) znajduje największą wartość, która jest mniejsza lub równa szukanej. Świetnie nadaje się do przedziałów i progów; pierwsza kolumna musi być posortowana rosnąco.
WYSZUKAJ.PIONOWO (Angielski: VLOOKUP) szuka wartości w pierwszej kolumnie tabeli i zwraca wartość z innej kolumny w tym samym wierszu. Funkcja istnieje w Excelu od dziesięcioleci, więc spotkasz ją w niemal każdym starszym skoroszycie. Jej zasady pokazują też, jak działa każde wyszukiwanie.
Cztery argumenty
Argumenty to wartość do znalezienia, tabela, numer kolumny do zwrócenia i typ dopasowania. Weź tabelę w A2:C5, z identyfikatorami produktów w kolumnie A, nazwami w kolumnie B i cenami w kolumnie C.
=WYSZUKAJ.PIONOWO(101;A2:C5;3;FAŁSZ)
Szuka ID 101 w kolumnie A i zwraca cenę z trzeciej kolumny tabeli.
Numer kolumny liczy się w podanej tabeli, zaczynając od 1 dla pierwszej kolumny. Klucz musi znajdować się w tej pierwszej kolumnie. WYSZUKAJ.PIONOWO (Angielski: VLOOKUP) nie potrafi szukać w lewo.
Dokładnie czy przybliżenie
Ostatni argument decyduje o typie dopasowania. FALSE oznacza dopasowanie dokładne i to właśnie chcesz dla identyfikatorów i nazw. TRUE albo pominięcie argumentu oznacza dopasowanie przybliżone: Excel zwraca wiersz z największym kluczem, który nie przekracza wartości. Wymaga to, aby pierwsza kolumna była posortowana rosnąco.
Dopasowanie przybliżone służy do przedziałów. Wpisz limity wagi w A2:A5 (0, 1, 5, 10) i ceny wysyłki w B2:B5 (4, 6, 9, 15).
=WYSZUKAJ.PIONOWO(7;A2:B5;2;PRAWDA)
7 nie ma na liście, więc Excel używa przedziału zaczynającego się od 5 i zwraca 9.
Wartość poniżej pierwszego klucza, na przykład -1, zwraca #N/D. Jeśli pierwsza kolumna nie jest posortowana, wyszukiwanie przybliżone może bez ostrzeżenia zwrócić zły wiersz.
Częste błędy
Pominięte FALSE. Brak ostatniego argumentu oznacza dopasowanie przybliżone. Przy nieposortowanych identyfikatorach daje to wiarygodnie wyglądające, ale błędne wartości.
Wpisane na sztywno numery kolumn. Jeśli ktoś wstawi kolumnę wewnątrz tabeli, liczba 3 wskazuje już na złą kolumnę. Liczenie za pomocą PODAJ.POZYCJĘ (Angielski: MATCH) zamiast wpisywania liczby temu zapobiega.
Przesuwająca się tabela. Gdy kopiujesz formułę w dół, odwołanie do tabeli też się przesuwa. Zablokuj je: =WYSZUKAJ.PIONOWO(E2;$A$2:$C$5;3;FAŁSZ).
Niezgodne typy i spacje. Liczba 101 i tekst "101" to co innego, podobnie jak "Bolt" i "Bolt ". Sprawdź to funkcją CZY.LICZBA (Angielski: ISNUMBER) i oczyść tekst za pomocą USUŃ.ZBĘDNE.ODSTĘPY (Angielski: TRIM).
Bardziej odporna wersja
Dwie małe zmiany sprawiają, że WYSZUKAJ.PIONOWO (Angielski: VLOOKUP) znacznie trudniej się zepsuć. Po pierwsze, niech PODAJ.POZYCJĘ (Angielski: MATCH) znajdzie numer kolumny z nagłówka, tak aby formuła nadążała za kolumną nawet po zmianach w tabeli:
Po drugie, licz się z brakami. Owiń wyszukiwanie w JEŻELI.ND (Angielski: IFNA), aby pokazać czytelny komunikat zamiast #N/D. Pamiętaj też, że WYSZUKAJ.PIONOWO (Angielski: VLOOKUP) zwraca tylko pierwsze dopasowanie: jeśli identyfikator występuje dwa razy, drugi wiersz nigdy nie zostanie znaleziony. W dokładnym wyszukiwaniu szukana wartość może zawierać symbole wieloznaczne, więc =WYSZUKAJ.PIONOWO("Bo*";B2:C5;2;FAŁSZ) znajduje pierwszą nazwę zaczynającą się od Bo.
Czego nauczyć się dalej
Gdy poczujesz się pewnie z WYSZUKAJ.PIONOWO (Angielski: VLOOKUP), zajrzyj do X.WYSZUKAJ (Angielski: XLOOKUP) przy nowej pracy oraz do INDEKS (Angielski: INDEX) z PODAJ.POZYCJĘ (Angielski: MATCH) dla wyszukiwań w lewo. Rozwiązują ten sam problem z mniejszą liczbą pułapek.