Formuła Vlookup działa tylko wtedy, gdy tablica tabeli w formule się nie zmienia, ale jeśli do tabeli zostanie wstawiona nowa kolumna lub kolumna zostanie usunięta, formuła daje niepoprawny wynik lub odzwierciedla błąd, aby formuła była wolna od błędów W takich dynamicznych sytuacjach używamy funkcji match, aby faktycznie dopasować indeks danych i zwrócić rzeczywisty wynik.
Połącz WYSZUKAJ.PIONOWO z dopasowaniem
Formuła vlookup jest najczęściej używaną funkcją, która służy do wyszukiwania i zwracania tej samej wartości w określonym indeksie kolumny lub wartości z innego indeksu kolumny w odniesieniu do dopasowanej wartości z pierwszej kolumny. Głównym wyzwaniem podczas korzystania z vlookup jest to, że indeks kolumny, który ma zostać określony, jest statyczny i nie ma funkcji dynamicznej. Zwłaszcza podczas pracy z wieloma kryteriami, które wymagają ręcznej zmiany indeksu kolumny odniesienia. W ten sposób potrzeba ta jest spełniona poprzez użycie formuły „DOPASUJ”, aby mieć lepszą przyczepność lub kontrolę nad często zmieniającym się indeksem kolumny w formule WYSZUKAJ.PIONOWO.
Wyszukaj i dopasuj formułę
# 1 - Formuła WYSZUKAJ.PIONOWO
Formuła funkcji WYSZUKAJ.PIONOWO w programie Excel
Tutaj wszystkie argumenty do wprowadzenia są obowiązkowe.
- Lookup_value - w tym miejscu należy wprowadzić odwołanie do komórki lub tekst w podwójnych cudzysłowach, aby został zidentyfikowany w zakresie kolumn.
- Tablica tablicowa - ten argument wymaga wprowadzenia zakresu tabeli, w którym należy przeszukać wartość Lookup_value, a dane do pobrania znajdują się w określonym zakresie kolumn.
- Col_index_num - W tym argumencie numer indeksu kolumny lub liczba kolumny z pierwszej kolumny odniesienia musi zostać wprowadzona, z której należy pobrać odpowiednią wartość z tego samego miejsca, co wartość wyszukiwana w pierwszej kolumnie.
- (Range_lookup) - ten argument daje dwie opcje.
- PRAWDA - Przybliżone dopasowanie: - Argument można wprowadzić jako PRAWDA lub jako wartość liczbową „1”, która zwraca przybliżone dopasowanie odpowiadające kolumnie odniesienia lub pierwszej kolumnie. Ponadto wartości w pierwszej kolumnie tablicy tabeli muszą być sortowane w kolejności rosnącej.
- FALSE - Dokładne dopasowanie: - W tym przypadku argument, który ma zostać wprowadzony, może mieć wartość FAŁSZ lub wartość liczbową „0”. Ta opcja zwróci tylko dokładne dopasowanie wartości, która ma być zidentyfikowana na podstawie pozycji w zakresie pierwszej kolumny. Niepowodzenie wyszukiwania wartości z pierwszej kolumny zwróciłby komunikat o błędzie „# N / A”.
# 2 - Formuła meczu
Funkcja dopasowania zwraca pozycję komórki wartości wprowadzonej dla danej tablicy tabeli.
Wszystkie argumenty w składni są obowiązkowe.
- Lookup_value - w tym przypadku wprowadzony argument może być odwołaniem do komórki wartości lub ciągiem tekstowym z podwójnymi cudzysłowami, którego pozycja komórki ma zostać pobrana.
- Lookup_array - należy wprowadzić zakres tablicy dla tabeli, którego wartość lub zawartość komórki ma zostać zidentyfikowana.
- (typ dopasowania) - ten argument zapewnia trzy opcje, jak wyjaśniono poniżej.
- „1-Mniejsze niż” - w tym przypadku argumentem, który ma zostać wprowadzony, jest liczba „1”, która zwróci wartość mniejszą lub równą wyszukanej wartości. Ponadto tablica wyszukiwania musi być posortowana w kolejności rosnącej.
- „0-dokładne dopasowanie” - w tym przypadku argument, który ma zostać wprowadzony, powinien mieć wartość liczbową „0”. Ta opcja zwróci dokładną pozycję dopasowanej wartości wyszukiwania. Jednak tablica wyszukiwania może mieć dowolną kolejność.
- „-1-Większe niż” - argument do wprowadzenia powinien mieć wartość liczbową „-1”. Trzecia opcja umożliwia znalezienie najmniejszej wartości, która jest większa lub równa wartości wyszukiwania. Tutaj kolejność dla tablicy wyszukiwania musi być ułożona w porządku malejącym.
# 3 - WYSZUKAJ.PIONOWO z formułą MATCH
= WYSZUKAJ.PIONOWO (wartość_wyszukiwania; tablica_tabeli; PODAJ.POZYCJĘ (wartość_wyszukiwania; tablica_wyszukiwania; (typ_dopasowania)), (wyszukiwanie zakresu))
Jak korzystać z funkcji WYSZUKAJ.PIONOWO z formułą dopasowania w programie Excel?
Poniższy przykład pomoże zrozumieć działanie funkcji vlookup i formuły dopasowania podczas składania.
Weź pod uwagę poniższą tabelę danych, która opisuje specyfikacje danego zakupionego pojazdu.
Aby uzyskać przejrzystość połączonej funkcji dla funkcji vlookup i match, zrozummy, jak działa indywidualna formuła, a następnie uzyskamy wyniki dopasowania vlookup po zestawieniu.
Krok 1 - Zastosujmy formułę vlookup na poziomie indywidualnym, aby dojść do wyniku.
Wynik jest pokazany poniżej:
Tutaj wartość wyszukiwania odnosi się do $ B9, czyli modelu „E”, a tablica przeglądowa jest podana jako zakres tabeli danych z wartością bezwzględną „$” indeks kolumny odnosi się do kolumny „4”, która jest liczbą dla kolumna „Typ”, a wyszukiwanie zakresu otrzyma dokładne dopasowanie.
W związku z tym do zwrócenia wartości w kolumnie „Paliwo” stosuje się następujący wzór .
Wynik jest pokazany poniżej:
Tutaj wartość wyszukiwania z ciągiem bezwzględnym „$” zastosowanym jako wartość wyszukiwania i tablica_wyszukiwania pomaga naprawić komórkę odniesienia, nawet jeśli formuła jest kopiowana do innej komórki. W kolumnie „Paliwo” musimy zmienić indeks kolumny na „5”, ponieważ zmienia się wartość, z której dane są potrzebne do pobrania.
Krok # 2 - Teraz zastosujmy formułę dopasowania, aby pobrać pozycję dla podanej wartości wyszukiwania.
Wynik jest pokazany poniżej:
Jak widać na powyższym zrzucie ekranu, tutaj próbujemy pobrać pozycję kolumny z tablicy tabeli. W tym przypadku numer kolumny do ściągnięcia jest określany jako komórka C8, czyli kolumna „Typ”, a zakres wyszukiwania do przeszukiwania jest podany jako zakres nagłówków kolumn, a typ dopasowania otrzymuje dokładne dopasowanie jako „0”.
Dlatego poniższa tabela da pożądany wynik dla pozycji kolumny „Paliwo”.
W tym przypadku kolumną do przeszukania jest komórka D8, a żądany indeks kolumny jest zwracany jako „5”.
Krok # 3 - Teraz formuła dopasowania zostanie użyta w funkcji vlookup, aby uzyskać wartość ze zidentyfikowanej pozycji kolumny.
Wynik jest pokazany poniżej:
W powyższym wzorze funkcja dopasowania jest umieszczana w miejsce parametru indeksu kolumny funkcji vlookup. W tym przypadku funkcja dopasowania zidentyfikuje komórkę odniesienia do wartości wyszukiwania „C8” i zwróci numer kolumny za pośrednictwem podanej tablicy tabeli. Ta pozycja kolumny będzie służyć jako dane wejściowe dla argumentu indeksu kolumny w funkcji vlookup. Co z kolei pomoże vlookup zidentyfikować wartość, która ma zostać zwrócona na podstawie numeru indeksu kolumny wynikowej?
Podobnie zastosowaliśmy podgląd z formułą dopasowania również dla kolumny „Paliwo”.
Wynik jest pokazany poniżej:
W ten sposób możemy zastosować tę funkcję kombinacji również dla innych kolumn „Typ” i „Paliwo”.
Rzeczy do zapamiętania
- WYSZUKAJ.PIONOWO można zastosować do wyszukiwania wartości tylko w jego przedniej lewej części. Wszelkie wartości, które mają być przeszukiwane po prawej stronie tabeli danych, zwrócą wartość błędu „# N / A”.
- Zakres tablicy tablica_tabeli wprowadzony w drugim argumencie powinien być bezwzględnym odwołaniem do komórki „$”, co spowoduje zachowanie stałego zakresu tablicy tabeli podczas stosowania formuły wyszukiwania do innych komórek, w przeciwnym razie komórki odniesienia dla zakresu tablicy tabeli zostaną przesunięte do następnej komórki odniesienie.
- Wartość wpisana w wyszukiwanej wartości nie powinna być mniejsza niż najmniejsza wartość w pierwszej kolumnie tablicy tabeli, w przeciwnym razie funkcja zwróci wartość błędu „# N / A”.
- Przed zastosowaniem przybliżonego dopasowania „TRUE” lub „1” w ostatnim argumencie zawsze pamiętaj, aby posortować tablicę tabeli w porządku rosnącym.
- Funkcja dopasowania zwraca tylko pozycję wartości w tablicy tabeli vlookup i nie zwraca wartości.
- W przypadku, gdy funkcja dopasowania nie jest w stanie zidentyfikować pozycji szukanej wartości w tablicy tabeli, wówczas formuła zwraca „# N / A” w wartości błędu.
- Funkcje wyszukiwania i dopasowania nie uwzględniają wielkości liter podczas dopasowywania wartości wyszukiwania do pasującej wartości tekstowej w tablicy tabeli.
