Samouczek Excela VLOOKUP dla początkujących
Charlotte Miller
⚡ Inteligentne podsumowanie
Samouczek Excela dotyczący funkcji WYSZUKAJ.PIONOWO wyjaśnia, jak funkcja wyszukiwania pionowego przeszukuje pierwszą kolumnę tabeli i zwraca pasującą wartość z innej kolumny. W tym przewodniku omówiono składnię, dopasowania dokładne i przybliżone, wyszukiwania między arkuszami, typowe błędy oraz nowoczesną alternatywę dla funkcji WYSZUKAJ.PIONOWO.
Co to jest WYSZUKAJ.PIONOWO?
VLOOKUP (V oznacza „pionowo”) to wbudowana funkcja programu Excel, która tworzy relacje między kolumnami w arkuszu kalkulacyjnym. Umożliwia ona wyszukiwanie wartości w jednej kolumnie i zwracanie odpowiadającej jej wartości z innej kolumny w tym samym wierszu.
Składnia i argumenty funkcji WYSZUKAJ.PIONOWO
Przed użyciem funkcji WYSZUKAJ.PIONOWO warto zrozumieć strukturę formuły. Funkcja przyjmuje cztery argumenty i działa według spójnego wzorca w każdej wersji programu Excel.
- lookup_value — wartość, którą chcesz znaleźć (odwołanie do komórki lub literał).
- table_array — zakres komórek zawierający kolumnę wyszukiwania i kolumnę zwracaną.
- col_index_num — numer kolumny w tablicy table_array, z której ma zostać zwrócona wartość (1 to kolumna najbardziej na lewo).
- Zakres wyszukiwania — FAŁSZ w przypadku dokładnego dopasowania, PRAWDA (lub pominięte) w przypadku przybliżonego dopasowania w posortowanych danych.
Ważne: Szukana wartość musi znajdować się w skrajnej lewej kolumnie tablicy table_array. Funkcja WYSZUKAJ.PIONOWO przeszukuje wyłącznie obszary od lewej do prawej.
Korzystanie z funkcji WYSZUKAJ.PIONOWO
Gdy trzeba znaleźć konkretne informacje w dużym arkuszu kalkulacyjnym lub wielokrotnie pobierać ten sam rodzaj wartości, funkcja WYSZUKAJ.PIONOWO pozwala zaoszczędzić znaczną ilość czasu w porównaniu z filtrowaniem ręcznym.
Rozważmy Tabela wynagrodzeń firmy obsługiwane przez zespół finansowy. Zaczynasz od znanej informacji — indeksu — i używasz funkcji WYSZUKAJ.PIONOWO, aby pobrać nieznaną wartość.
Na przykład znasz już imię i nazwisko pracownika:
A chcesz sprawdzić pensję pracownika:
Arkusz kalkulacyjny programu Excel dla powyższego przypadku:
Aby znaleźć nieznaną pensję pracownika, wpisujemy Pracownika Code który jest już dostępny.
Stosując funkcję WYSZUKAJ.PIONOWO, można obliczyć wartość wynagrodzenia odpowiadającą danemu pracownikowi Code pojawia się automatycznie.
Jak korzystać z funkcji WYSZUKAJ.PIONOWO w programie Excel
Postępuj zgodnie z tym przewodnikiem krok po kroku, aby zastosować funkcję WYSZUKAJ.PIONOWO w programie Excel:
Krok 1) Przejdź do komórki docelowej
Kliknij komórkę, w której chcesz wyświetlić wynagrodzenie wybranego pracownika — w tym przykładzie jest to komórka H3.
Krok 2) Wprowadź funkcję WYSZUKAJ.PIONOWO =WYSZUKAJ.PIONOWO()
Wpisz funkcję w komórce. Zacznij od znaku równości (który informuje Excela o formule), a następnie wpisz słowo kluczowe WYSZUKAJ.PIONOWO: =WYSZUKAJ PIONOWO().
W nawiasach znajduje się zbiór argumentów (fragmentów danych, których potrzebuje funkcja).
Funkcja WYSZUKAJ.PIONOWO wymaga czterech argumentów:
Krok 3) Pierwszy argument — wartość wyszukiwania
Pierwszy argument to odwołanie do komórki zawierającej wartość, której szukasz. W tym przypadku jest to Pracownik Code jest wartością wyszukiwania, więc pierwszym argumentem jest H2 — komórka, której zawartość powinna odpowiadać plikowi Excel.
Krok 4) Drugi argument — tablica tabel
Odnosi się to do bloku wartości, które mają zostać przeszukane, znanego w programie Excel jako tablica tablicowa lub tabela wyszukiwania. W naszym przykładzie tabela wyszukiwania działa od B2 do E25.
UWAGA: Kolumna wyszukiwania musi być skrajną lewą kolumną tablicy tabeli.
Krok 5) Trzeci argument — col_index_num
Informuje to funkcję WYSZUKAJ.PIONOWO, która kolumna w tablicy tabeli zawiera wartość zwracaną. Wynagrodzenie pracownika znajduje się w czwartej kolumnie, więc indeks kolumny to 4.
Krok 6) Czwarty argument — dopasowanie dokładne lub przybliżone
Ostatnim argumentem jest flaga wyszukiwania zakresu. Kontroluje ona, czy funkcja WYSZUKAJ.PIONOWO zwraca dopasowanie dokładne, czy przybliżone. W tym przypadku chcemy uzyskać dopasowanie dokładne (FAŁSZ).
- FAŁSZYWY — dokładne dopasowanie.
- TRUE — przybliżone dopasowanie.
Krok 7) Naciśnij Enter
Naciśnij Enter, aby uzupełnić formułę. Początkowo pojawi się błąd, ponieważ nie ma pracownika. Code nie został jeszcze wpisany w H2.
Po wprowadzeniu prawidłowego pracownika Code w komórce H2 zwracana jest odpowiednia pensja pracownika.
Krótko mówiąc, formuła informuje program Excel, że znane wartości znajdują się w lewej kolumnie danych (Pracownik) Code). Następnie funkcja WYSZUKAJ.PIONOWO skanuje tabelę i zwraca wartość czwartej kolumny w pasującym wierszu — wynagrodzenie pracownika.
W tym przykładzie omówione zostały dopasowania dokładne (słowo kluczowe FALSE). W następnej sekcji wyjaśniono dopasowania przybliżone.
WYSZUKAJ PIONOWO dla przybliżonych dopasowań (słowo kluczowe TRUE jako ostatni parametr)
Rozważmy scenariusz, w którym tabela oblicza zniżki dla klientów, którzy nie kupują dokładnie dziesiątek lub setek artykułów.
Jak pokazano poniżej, firma stosuje rabaty w przypadku ilości od 1 do 10 000:
Klient rzadko kupuje dokładnie 100 lub 1,000 sztuk. Tryb dopasowania przybliżonego pozwala funkcji WYSZUKAJ.PIONOWO znaleźć najbliższą, niższą wartość, zamiast nalegać na podanie dokładnej liczby. Kroki:
Krok 1) Kliknij komórkę, do której ma zostać wprowadzona funkcja WYSZUKAJ.PIONOWO — odwołanie do komórki I2.
Krok 2) Wpisz =WYSZUKAJ.PIONOWO() w komórce i dodaj argumenty w nawiasach.
Krok 3) Argument 1: Wprowadź odwołanie do komórki, której wartość ma zostać porównana z tabelą wyszukiwania.
Krok 4) Argument 2: Wybierz tabelę wyszukiwania — w tym przypadku kolumny Ilość i Rabat.
Krok 5) Argument 3: Wprowadź indeks kolumny w tabeli wyszukiwania, z której chcesz zwrócić pasującą wartość.
Krok 6) Argument 4: Ustaw ostatni argument na TRUE dla przybliżonych dopasowań.
Krok 7) Naciśnij Enter. Formuła zostanie zastosowana do komórki. Po wpisaniu dowolnej ilości Excel zwróci przedział rabatowy na podstawie przybliżonego dopasowania.
UWAGA: Jeśli pozostawisz czwarty argument pusty, Excel domyślnie przyjmie wartość PRAWDA (dopasowanie przybliżone). W przypadku dopasowań przybliżonych kolumna wyszukiwania musi być posortowana rosnąco.
Funkcja przeglądania pionowego zastosowana pomiędzy 2 różnymi arkuszami umieszczonymi w tym samym skoroszycie
Rozważmy teraz skoroszyt z dwoma arkuszami. Arkusz 1 zawiera listę pracowników Code, Imię i nazwisko oraz stanowisko; Arkusz 2 zawiera listę pracowników Code i wynagrodzenia pracowników.
ARKUSZ 1:
ARKUSZ 2:
Celem jest konsolidacja wszystkich danych na Arkuszu 1, jak pokazano poniżej:
Funkcja WYSZUKAJ.PIONOWO umożliwia agregację danych, dzięki czemu pracownicy Code, Imię i nazwisko oraz pensja pojawiają się razem na jednym arkuszu.
Zaczynamy od Arkusza 2, ponieważ zawiera on dwa argumenty — kolumnę „Wynagrodzenie pracownika” można znaleźć tutaj, a kolumnę „Wynagrodzenie pracownika” tutaj. indeks kolumny wynosi 2.
Chcemy znaleźć wynagrodzenie odpowiadające każdemu pracownikowi Code.
Dane rozciągają się od komórek A2 do B25 — to jest nasza tablica tabelaryczna.
Krok 1) Przejdź do Arkusza 1 i wprowadź wyświetlone nagłówki.
Krok 2) Kliknij komórkę obok pozycji Wynagrodzenie pracownika — komórka F3 — w której zostanie umieszczona formuła WYSZUKAJ.PIONOWO.
Wprowadź funkcję WYSZUKAJ.PIONOWO: =WYSZUKAJ.PIONOWO().
Krok 3) Argument 1: Wpisz F2 — komórka zawierająca Pracownika Code aby dopasować w tabeli wyszukiwania.
Krok 4) Argument 2: Tabela wyszukiwania znajduje się na innym arkuszu, więc odwołuj się do niej, podając nazwę arkusza: Arkusz2!A2:B25.
Krok 5) Argument 3: Wprowadź indeks kolumny w tabeli wyszukiwania, która zawiera wartość zwracaną.
Krok 6) Argument 4: Użyj FALSE, aby uzyskać dokładne dopasowanie, ponieważ chcemy uzyskać dokładne wynagrodzenie odpowiadające każdemu pracownikowi Code.
Krok 7) Naciśnij Enter. Kiedy wpisujesz Pracownika Code, komórka zwraca odpowiednią pensję pobraną z Arkusza 2.
Typowe błędy funkcji WYSZUKAJ.PIONOWO i ich rozwiązania
Nawet doświadczeni użytkownicy napotykają błędy funkcji WYSZUKAJ.PIONOWO. Najczęstsze z nich i szybkie rozwiązania:
- # N / A — Funkcja WYSZUKAJ.PIONOWO nie może znaleźć szukanej wartości. Sprawdź, czy nie występują dodatkowe spacje, niezgodne typy danych (liczby zapisane jako tekst) lub czy wartość rzeczywiście znajduje się w pierwszej kolumnie tablicy table_array.
- #ADR! — col_index_num jest większe niż liczba kolumn w table_array. Obniż indeks kolumny lub rozszerz zakres.
- #WARTOŚĆ! — col_index_num jest mniejsze niż 1 lub argument jest nieprawidłowy. Sprawdź składnię formuły.
- Zwrócono błędny wynik — czwarty argument ma wartość PRAWDA lub jest pominięty, ale kolumna wyszukiwania jest nieposortowana. Zmień na FAŁSZ lub posortuj kolumnę rosnąco.
- Zablokowane odniesienia — kopiując formułę w dół, należy używać odwołań bezwzględnych (na przykład $B$2:$E$25), aby tablica table_array nie uległa przesunięciu.
VLOOKUP czy XLOOKUP: którą funkcję powinieneś wybrać?
Microsoft wprowadził XLOOKUP w Microsoft 365 i Excel 2021 jako nowoczesny zamiennik funkcji WYSZUKAJ.PIONOWO. Usuwa ona kilka ograniczeń funkcji WYSZUKAJ.PIONOWO i jest obecnie zalecanym wyborem w obsługiwanych wersjach.
| Cecha | WYSZUKAJ.PIONOWO | XWYSZUKAJ |
|---|---|---|
| Kierunek wyszukiwania | Tylko od lewej do prawej | Dowolny kierunek (lewo, prawo, góra, dół) |
| Domyślny typ dopasowania | Przybliżone (PRAWDA) | Dokładny |
| Obsługa „jeśli nie znaleziono” | Zwraca #N/A | Wbudowany argument if_not_found |
| Indeks kolumn | Zakodowany na stałe numer | Odwołanie do zakresu kolumn zwrotnych |
| Dostępność: | Wszystkie wersje Excela | Microsoft 365, Excel 2021, Excel dla sieci Web |
Kiedy wybrać funkcję WYSZUKAJ.PIONOWO: skoroszyt musi działać w programie Excel 2019 lub starszym, w przeciwnym razie będziesz musiał zachować starsze formuły. Kiedy wybrać XLOOKUP: Tworzysz nowe skoroszyty w nowoczesnym Excelu i chcesz korzystać z wyszukiwania lewego, bardziej przejrzystej obsługi błędów i domyślnego dopasowania dokładnego. Dowiedz się więcej o funkcjach wyszukiwania w Samouczki Excela series.
Wniosek
Trzy powyższe scenariusze wyjaśniają, jak funkcja WYSZUKAJ.PIONOWO działa w przypadku dopasowań dokładnych, przybliżonych i odniesień między arkuszami. Ćwicz na własnych zbiorach danych, aby rozwinąć biegłość. WYSZUKAJ.PIONOWO pozostaje ważną funkcją w MS Excel do efektywnego zarządzania danymi, a funkcja XLOOKUP rozszerza zestaw tych narzędzi w nowoczesnym programie Excel.
