Kombinacja funkcji INDEKS I DOPASUJ w Excelu (10 prostych przykładów)

Excel ma wiele funkcji - około 450+ z nich.

A wiele z nich jest po prostu niesamowitych. Ilość pracy, jaką można wykonać za pomocą kilku formuł, wciąż mnie zaskakuje (nawet po ponad 10 latach używania Excela).

A wśród tych wszystkich niesamowitych funkcji wyróżnia się kombinacja funkcji INDEX MATCH.

Jestem wielkim fanem kombinacji INDEX MATCH i wiele razy dałem to jasno do zrozumienia.

Napisałem nawet artykuł o Index Match Vs VLOOKUP, który wywołał trochę debaty (możesz sprawdzić sekcję komentarzy, aby znaleźć fajerwerki).

A dzisiaj piszę ten artykuł, który koncentruje się wyłącznie na dopasowaniu indeksowym, aby pokazać kilka prostych i zaawansowanych scenariuszy, w których można użyć tego potężnego zestawu formuł i wykonać pracę.

Notatka: Istnieją inne formuły wyszukiwania w programie Excel - takie jak WYSZUKAJ.PIONOWO i WYSZUKAJ.POZIOMO, które są świetne. Wiele osób uważa, że ​​funkcja WYSZUKAJ.PIONOWO jest łatwiejsza w użyciu (i to również prawda). Uważam, że w wielu przypadkach INDEX MATCH jest lepszą opcją. Ale ponieważ ludziom jest to trudne, jest mniej używany. Więc staram się to uprościć za pomocą tego samouczka.

Teraz zanim pokażę Wam, jak połączenie INDEX MATCH zmienia świat analityków i data sciences, pozwólcie, że najpierw przedstawię Wam poszczególne części - funkcje INDEX i MATCH.

Funkcja INDEKS: znajduje wartość na podstawie współrzędnych

Najłatwiejszym sposobem zrozumienia działania funkcji Index jest myślenie o niej jako o satelicie GPS.

Gdy tylko podasz satelitę współrzędne szerokości i długości geograficznej, będzie wiedział dokładnie, gdzie się udać i znaleźć tę lokalizację.

Tak więc pomimo oszałamiającej liczby kombinacji szerokości i długości satelita wiedziałby dokładnie, gdzie szukać.

Szybko wyszukałem miejsce pracy i to właśnie otrzymałem.

W każdym razie dość geografii.

Podobnie jak satelita potrzebuje współrzędnych szerokości i długości geograficznej, funkcja INDEKS w programie Excel potrzebuje numeru wiersza i kolumny, aby wiedzieć, do której komórki się odnosisz.

A to w skrócie funkcja INDEX Excela.

Więc pozwól, że zdefiniuję to w prostych słowach dla Ciebie.

Funkcja INDEX użyje numeru wiersza i numeru kolumny, aby znaleźć komórkę w podanym zakresie i zwróci w niej wartość.

Sam w sobie INDEX jest bardzo prostą funkcją, bezużyteczną. W końcu w większości przypadków prawdopodobnie nie znasz numerów wierszy i kolumn.

Jednak…

Fakt, że można go używać z innymi funkcjami (wskazówka: MATCH), które potrafią znaleźć numer wiersza i numer kolumny, sprawia, że ​​INDEKS jest niezwykle potężną funkcją Excela.

Poniżej znajduje się składnia funkcji INDEKS:

=INDEX (tablica, numer_wiersza, [num_kolumny]) =INDEX (tablica, numer_wiersza, [num_kolumny], [num_obszaru])

• tablica - a zakres komórek lub stała tablicowa.
• numer_wiersza - numer wiersza, z którego ma zostać pobrana wartość.
• [liczba_kolumny] - numer kolumny, z której ma zostać pobrana wartość. Chociaż jest to argument opcjonalny, ale jeśli nie podano numeru wiersza, należy go podać.
• [numer_obszaru] - (Opcjonalnie) Jeśli argument tablica składa się z wielu zakresów, ta liczba zostanie użyta do wybrania odwołania ze wszystkich zakresów.

Funkcja INDEX ma 2 składnie (tylko FYI).

W większości przypadków używany jest pierwszy. Drugi jest używany tylko w zaawansowanych przypadkach (takich jak wyszukiwanie trójstronne), które omówimy w jednym z przykładów w dalszej części tego samouczka.

Ale jeśli jesteś nowy w tej funkcji, pamiętaj tylko o pierwszej składni.

Poniżej znajduje się film wyjaśniający, jak korzystać z funkcji INDEX

Funkcja MATCH: znajduje pozycję na podstawie wartości wyszukiwania

Wracając do mojego poprzedniego przykładu długości i szerokości geograficznej, PODAJ.POZYCJĘ jest funkcją, która może znaleźć te pozycje (w świecie arkuszy kalkulacyjnych Excel).

W prostym języku funkcja Excel MATCH może znaleźć pozycję komórki w zakresie.

I na jakiej podstawie znalazłby pozycję komórki?

Na podstawie wartości wyszukiwania.

Na przykład, jeśli masz listę pokazaną poniżej i chcesz znaleźć w niej pozycję nazwy „Mark”, możesz użyć funkcji PODAJ.POZYCJĘ.

Funkcja zwraca 3, ponieważ jest to pozycja komórki z nazwą Mark.

Funkcja MATCH rozpoczyna wyszukiwanie od góry do dołu wartości wyszukiwania (czyli „Mark”) w określonym zakresie (w tym przykładzie jest to A1:A9). Gdy tylko znajdzie nazwę, zwraca pozycję w tym konkretnym zakresie.

Poniżej znajduje się składnia funkcji PODAJ.POZYCJĘ w programie Excel.

= PODAJ.(wyszukiwana_wartość, wyszukiwana_tablica, [typ_dopasowania])

• szukana_wartość - Wartość, dla której szukasz dopasowania w lookup_array.
• szukana_tablica - Zakres komórek, w których szukasz szukanej_wartości.
• [typ_dopasowania] - (Opcjonalnie) Określa, jak program Excel powinien szukać pasującej wartości. Może przyjmować trzy wartości -1, 0 lub 1.

Zrozumienie argumentu typu dopasowania w funkcji DOPASUJ

Jest jeszcze jedna rzecz, którą musisz wiedzieć o funkcji PODAJ.POZYCJĘ, a chodzi o to, jak przechodzi przez dane i znajduje pozycję komórki.

Trzecim argumentem funkcji PODAJ.POZYCJĘ może być 0, 1 lub -1.

Poniżej znajduje się wyjaśnienie, jak działają te argumenty:

• 0 - szuka dokładnego dopasowania wartości. Jeśli zostanie znalezione dokładne dopasowanie, funkcja PODAJ.POZYCJĘ zwróci pozycję komórki. W przeciwnym razie zwróci błąd.
• 1 - znajduje największą wartość, która jest mniejsza lub równa wartości wyszukiwania. Aby to zadziałało, zakres danych musi być posortowany w porządku rosnącym.
• -1 - znajduje najmniejszą wartość, która jest większa lub równa wartości wyszukiwania. Aby to zadziałało, zakres danych musi być posortowany w kolejności malejącej.

Poniżej znajduje się wideo wyjaśniające, jak używać funkcji PODAJ.POZYCJĘ (wraz z argumentem typu dopasowania)

Podsumowując i ujmując to w prostych słowach:

• INDEKS potrzebuje pozycji komórki (numeru wiersza i kolumny) i podaje wartość komórki.
• PODAJ.POZYCJĘ znajduje pozycję przy użyciu wartości wyszukiwania.

Połączmy je, aby stworzyć potęgę (INDEKS + DOPASOWANIE)

Teraz, gdy masz podstawową wiedzę na temat działania funkcji INDEKS i PODAJ.POZYCJĘ, połączmy te dwa i poznajmy wszystkie wspaniałe rzeczy, które mogą zrobić.

Aby lepiej to zrozumieć, mam kilka przykładów, które używają kombinacji INDEX MATCH.

Zacznę od prostego przykładu, a następnie pokażę kilka zaawansowanych przypadków użycia.

Kliknij tutaj, aby pobrać przykładowy plik

Przykład 1: Proste wyszukiwanie za pomocą kombinacji INDEX MATCH

Zróbmy proste wyszukiwanie za pomocą INDEX/MATCH.

Poniżej znajduje się tabela, w której mam oceny dla dziesięciu uczniów.

Z tego stołu chcę znaleźć znaki dla Jima.

Poniżej znajduje się formuła, która może to łatwo zrobić:

=INDEKS($A$2:$B$11,DOPASUJ("Jim",$A$2:$A$11,0),2)

Teraz, jeśli myślisz, że można to łatwo zrobić za pomocą funkcji WYSZUKAJ.PIONOWO, masz rację! To nie jest najlepsze wykorzystanie niesamowitości INDEX MATCH. Pomimo tego, że jestem fanem INDEX MATCH, jest to trochę trudniejsze niż VLOOKUP. Jeśli pobieranie danych z kolumny po prawej stronie to wszystko, co chcesz zrobić, zalecamy skorzystanie z funkcji WYSZUKAJ.PIONOWO.

Powodem, dla którego pokazałem ten przykład, który można również łatwo zrobić za pomocą funkcji WYSZUKAJ.PIONOWO, jest pokazanie, jak działa DOPASOWANIE DO INDEKSU w prostym ustawieniu.

Teraz pokażę zaletę INDEX MATCH.

Załóżmy, że masz te same dane, ale zamiast w kolumnach, masz je w wierszach (jak pokazano poniżej).

Wiesz co, nadal możesz użyć kombinacji INDEX MATCH, aby uzyskać znaki Jima.

Poniżej znajduje się formuła, która da Ci wynik:

=INDEKS($B$1:$K$2,2,DOPASUJ("Jim",$B$1:$K$1,0))

Pamiętaj, że musisz zmienić zakres i zamienić części wiersza/kolumny, aby ta formuła działała również dla danych poziomych.

Nie można tego zrobić za pomocą WYSZUKAJ.PIONOWO, ale nadal można to łatwo zrobić za pomocą WYSZUKAJ.POZIOMO.

Kombinacja INDEX MATCH może z łatwością obsługiwać dane zarówno poziome, jak i pionowe.

Kliknij tutaj, aby pobrać przykładowy plik

Przykład 2: Wyszukaj w lewo

To bardziej powszechne niż myślisz.

Wiele razy może być konieczne pobranie danych z kolumny znajdującej się po lewej stronie kolumny zawierającej wartość wyszukiwania.

Coś, jak pokazano poniżej:

Aby sprawdzić sprzedaż Michaela, będziesz musiał sprawdzić po lewej stronie.

Jeśli myślisz WYSZUKAJ.PIONOWO, pozwól, że zatrzymam się w tym miejscu.

WYSZUKAJ.PIONOWO nie służy do wyszukiwania i pobierania wartości po lewej stronie.

Czy nadal możesz to zrobić za pomocą funkcji WYSZUKAJ.PIONOWO?

Tak, możesz!

Ale to może zmienić się w długą i brzydką formułę.

Więc jeśli chcesz wykonać wyszukiwanie i pobrać dane z kolumn po lewej stronie, lepiej jest użyć kombinacji INDEX MATCH.

Poniżej znajduje się wzór, który otrzyma numer sprzedaży Michaela:

=INDEKS($A$2:$C$11;MATCH("Michael";C2:C11,0);2)

Kolejny punkt dotyczący dopasowania indeksu. WYSZUKAJ.PIONOWO może pobrać dane tylko z kolumn znajdujących się po prawej stronie kolumny, która ma wartość wyszukiwania.

Przykład 3: Wyszukiwanie dwukierunkowe

Do tej pory widzieliśmy przykłady, w których chcieliśmy pobrać dane z kolumny sąsiadującej z kolumną, która ma wartość wyszukiwania.

Ale w rzeczywistości dane często obejmują wiele kolumn.

INDEX MATCH może z łatwością obsłużyć wyszukiwanie dwukierunkowe.

Poniżej znajduje się zbiór danych o ocenach ucznia z trzech różnych przedmiotów.

Jeśli chcesz szybko uzyskać oceny ucznia ze wszystkich trzech przedmiotów, możesz to zrobić za pomocą INDEX MATCH.

Poniższa formuła da ci znaki dla Jima dla wszystkich trzech przedmiotów (skopiuj i wklej w jednej komórce i przeciągnij, aby wypełnić inne komórki lub skopiuj i wklej w innych komórkach).

=INDEKS($ B$2:$D$11,DOPASUJ($F$3,$A$2:$A$11,0),DOPASUJ(G$2,$B$1:$D$1,0))

Pozwolę sobie szybko wyjaśnić również tę formułę.

Formuła INDEKS używa B2:D11 jako zakresu.

Pierwsza funkcja PODAJ.POZYCJĘ używa nazwy (Jim w komórce F3) i pobiera jej pozycję w kolumnie nazw (A2:A11). Staje się to numerem wiersza, z którego należy pobrać dane.

Druga formuła PODAJ Na przykład matematyka to 1, fizyka to 2, a chemia to 3.

Ponieważ te pozycje MATCH są wprowadzane do funkcji INDEX, zwraca ona wynik na podstawie nazwiska ucznia i nazwy przedmiotu.

Ta formuła jest dynamiczna, co oznacza, że ​​jeśli zmienisz imię ucznia lub nazwy przedmiotów, nadal będzie działać i pobiera prawidłowe dane.

Jedną wielką zaletą korzystania z funkcji INDEX/MATCH jest to, że nawet jeśli zamienisz nazwy przedmiotów, nadal będzie to dawać poprawny wynik.

Przykład 4: Wyszukiwanie wartości z wielu kolumn/kryteriów

Załóżmy, że masz zestaw danych, jak pokazano poniżej i chcesz pobrać znaki dla „Mark Long”.

Ponieważ dane są w dwóch kolumnach, nie mogę wyszukać Marka i uzyskać danych.

Jeśli zrobię to w ten sposób, otrzymam dane o ocenach dla Marka Frosta, a nie Marka Longa (ponieważ funkcja MATCH da mi wynik dla MARK, który spełnia).

Jednym ze sposobów na zrobienie tego jest utworzenie kolumny pomocniczej i połączenie nazw. Gdy masz już kolumnę pomocnika, możesz użyć funkcji WYSZUKAJ.PIONOWO i uzyskać dane dotyczące znaków.

Jeśli chcesz dowiedzieć się, jak to zrobić, przeczytaj ten samouczek dotyczący korzystania z funkcji WYSZUKAJ.PIONOWO z wieloma kryteriami.

Ale dzięki kombinacji INDEX/MATCH nie potrzebujesz kolumny pomocniczej. Możesz utworzyć formułę, która w samej formule obsługuje wiele kryteriów.

Poniższy wzór da wynik.

=INDEKS($C$2:$C$11;DOPASUJ($E$3&"|"&$F$3;$A$2:A11&"|"&$B$2:$B$11,0))

Pozwólcie, że szybko wyjaśnię, co robi ta formuła.

Część PODAJ.POZYCJĘ formuły łączy w sobie wartość odnośnika (Mark i Long) oraz całą tablicę odnośników. Kiedy $A$2:A11&”|”&$B$2:$B$11 jest używana jako tablica wyszukiwania, w rzeczywistości sprawdza wartość wyszukiwania względem połączonego ciągu imienia i nazwiska (oddzielonego symbolem potoku).

Gwarantuje to uzyskanie właściwego wyniku bez używania kolumn pomocniczych.

Możesz wykonać tego rodzaju wyszukiwanie (gdzie istnieje wiele kolumn/kryteriów) również za pomocą funkcji WYSZUKAJ.PIONOWO, ale musisz użyć kolumny pomocniczej. Kombinacja INDEX MATCH ułatwia wykonanie tego zadania bez żadnych kolumn pomocniczych.

Przykład 5: Pobierz wartości z całego wiersza/kolumny

W powyższych przykładach użyliśmy funkcji INDEX, aby uzyskać wartość z określonej komórki. Podajesz numer wiersza i kolumny, a zwraca wartość w tej konkretnej komórce.

Ale możesz zrobić więcej.

Możesz również użyć funkcji INDEKS, aby uzyskać wartości z całego wiersza lub kolumny.

A jak to może być przydatne, pytasz!

Załóżmy, że chcesz poznać łączny wynik Jima ze wszystkich trzech przedmiotów.

Możesz użyć funkcji INDEX, aby najpierw uzyskać wszystkie oceny Jima, a następnie użyć funkcji SUMA, aby uzyskać sumę.

Zobaczmy, jak to zrobić.

Poniżej mam wyniki wszystkich uczniów z trzech przedmiotów.

Poniższy wzór da mi łączny wynik Jima we wszystkich trzech przedmiotach.

= SUMA (INDEKS ($ B 2 $: $ D 11 $, DOPASOWANIE ($ F $ 4, $ A 2 $: $ A 11, 0 $), 0))

Pozwólcie, że wyjaśnię, jak działa ta formuła.

Sztuczka polega na tym, aby użyć 0 jako numeru kolumny.

Jeśli użyjesz 0 jako numeru kolumny w funkcji INDEKS, zwróci wszystkie wartości wierszy. Podobnie, jeśli użyjesz 0 jako numeru wiersza, zwróci wszystkie wartości w kolumnie.

Zatem poniższa część formuły zwraca tablicę wartości - {97, 70, 73}

INDEKS ($ B 2 $: $ D 11 $, DOPASUJ ($ F $ 4, $ A $ 2: $ A $ 11, 0), 0

Jeśli po prostu wpiszesz powyższą formułę w komórce w programie Excel i naciśniesz Enter, zobaczysz #ARG! błąd. Dzieje się tak, ponieważ nie zwraca pojedynczej wartości, ale tablicę wartości.

Ale nie martw się, tablica wartości nadal istnieje. Możesz to sprawdzić, wybierając formułę i naciskając klawisz F9. Pokaże wynik formuły, która w tym przypadku jest tablicą trzech wartości - {97, 70, 73}

Teraz, jeśli zawiniesz tę formułę INDEKS w funkcję SUMA, otrzymasz sumę wszystkich ocen uzyskanych przez Jima.

Możesz również użyć tego samego, aby uzyskać najwyższą, najniższą i średnią ocenę Jima.

Tak jak zrobiliśmy to dla ucznia, możesz to zrobić również dla przedmiotu. Na przykład, jeśli chcesz uzyskać średni wynik w temacie, możesz zachować numer wiersza jako 0 w formule INDEKS, a otrzymasz wszystkie wartości kolumn tego przedmiotu.

Kliknij tutaj, aby pobrać przykładowy plik

Przykład 6: Znajdź ocenę ucznia (technika dopasowania przybliżonego)

Do tej pory używaliśmy formuły PODAJ.POZYCJĘ, aby uzyskać dokładne dopasowanie wartości wyszukiwania.

Ale możesz również użyć go do przybliżonego dopasowania.

Czym do diabła jest przybliżony mecz?

Pozwól mi wyjaśnić.

Kiedy szukasz rzeczy, takich jak nazwy lub identyfikatory, szukasz dokładnego dopasowania. Ale czasami musisz znać zakres, w którym leżą twoje wartości wyszukiwania. Tak jest zwykle w przypadku liczb.

Na przykład, jako nauczyciel klasy, możesz chcieć wiedzieć, jaka jest ocena każdego ucznia z danego przedmiotu, a ocena jest ustalana na podstawie wyniku.

Poniżej znajduje się przykład, w którym chcę ocenić wszystkich uczniów, a ocena jest ustalana na podstawie tabeli po prawej stronie.

Więc jeśli uczeń dostaje mniej niż 33, ocena to F, a jeśli dostaje mniej niż 50, ale więcej niż 33, to jest E i tak dalej.

Poniżej znajduje się formuła, która to zrobi.

= INDEKS ($ F $ 3: $ F $ 8, DOPASUJ (B2, $ E $ 3: $ E $ 8, 1), 1)

Pozwólcie, że wyjaśnię, jak działa ta formuła.

W funkcji PODAJ.POZYCJĘ użyliśmy 1 jako argumentu [typ_dopasowania]. Ten argument zwróci największą wartość, która jest mniejsza lub równa wartości wyszukiwania.

Oznacza to, że formuła PODAJ.POZYCJĘ przechodzi przez zakres znaków i jak tylko znajdzie zakres znaków, który jest równy lub mniejszy niż wartość znaczników wyszukiwania, zatrzyma się tam i zwróci swoją pozycję.

Więc jeśli wartość znacznika wyszukiwania wynosi 20, funkcja PODAJ.POZYCJĘ zwróci 1, a jeśli jest to 85, zwróci 5.

A funkcja INDEX używa tej wartości pozycji, aby uzyskać ocenę.

WAŻNE: Aby to zadziałało, Twoje dane muszą być posortowane w porządku rosnącym. Jeśli tak nie jest, możesz uzyskać błędne wyniki.

Zauważ, że powyższe można również wykonać za pomocą poniższej formuły WYSZUKAJ.PIONOWO:

=WYSZUKAJ.PIONOWO(B2,$E$3:$F8,2$;PRAWDA)

Ale funkcja MATCH może pójść o krok dalej, jeśli chodzi o dopasowanie przybliżone.

Możesz również mieć malejące dane i użyć kombinacji INDEX MATCH, aby znaleźć wynik. Na przykład, jeśli zmienię kolejność tabeli ocen (jak pokazano poniżej), nadal mogę znaleźć oceny uczniów.

Aby to zrobić, wystarczy zmienić argument [typ_dopasowania] na -1.

Poniżej formuła, której użyłem:

=INDEKS($F$3:$F$8,DOPASUJ(B2,$E$3:$E$8,-1),1)

WYSZUKAJ.PIONOWO może również wykonać przybliżone dopasowanie, ale tylko wtedy, gdy dane są sortowane w kolejności rosnącej (ale nie działa, jeśli dane są sortowane w kolejności malejącej).

Przykład 7: Wyszukiwanie z uwzględnieniem wielkości liter

Do tej pory wszystkie wyszukiwania, które przeprowadziliśmy, nie uwzględniały wielkości liter.

Oznacza to, że nie miało to znaczenia, czy wartością wyszukiwania było Jim, JIM czy jim. Otrzymasz ten sam wynik.

Ale co, jeśli chcesz, aby w wyszukiwaniu była rozróżniana wielkość liter.

Zwykle dzieje się tak, gdy masz duże zbiory danych i możliwość powtórzenia lub różnych nazw/identyfikatorów (z tą jedyną różnicą)

Załóżmy na przykład, że mam następujący zestaw danych uczniów, w którym jest dwóch uczniów o imieniu Jim (jedyna różnica polega na tym, że jeden jest wprowadzony jako Jim, a drugi jako Jim).

Zauważ, że jest dwóch uczniów o tym samym imieniu - Jim (komórka A2 i A5).

Ponieważ normalne wyszukiwanie nie zadziała, musisz przeprowadzić wyszukiwanie z uwzględnieniem wielkości liter.

Poniżej znajduje się formuła, która da Ci właściwy wynik. Ponieważ jest to formuła tablicowa, musisz użyć klawiszy Control + Shift + Enter.

=INDEKS($B$2:$B$11;DOPASUJ(PRAWDA;DOKŁADNIE(D3;A2:A11);0);1)

Pozwólcie, że wyjaśnię, jak działa ta formuła.

Funkcja DOKŁADNE sprawdza dokładne dopasowanie wartości wyszukiwania (którą w tym przypadku jest „jim”). Przechodzi przez wszystkie nazwy i zwraca FALSE, jeśli nie pasuje, i TRUE, jeśli jest dopasowanie.

Wynik funkcji DOKŁADNE w tym przykładzie to - {FAŁSZ;FAŁSZ;FAŁSZ;PRAWDA;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ}

Zauważ, że jest tylko jedna PRAWDA, co oznacza, że ​​funkcja DOKŁADNIE znalazła idealne dopasowanie.

Funkcja PODAJ.POZYCJĘ następnie znajduje pozycję PRAWDA w tablicy zwróconej przez funkcję DOKŁADNE, która w tym przykładzie wynosi 4.

Gdy już mamy pozycję, funkcja INDEX używa jej do znalezienia znaczników.

Przykład 8: Znajdź najbliższe dopasowanie

Przejdźmy teraz trochę dalej.

Załóżmy, że masz zbiór danych, w którym chcesz znaleźć osobę, której doświadczenie zawodowe jest najbliższe wymaganemu doświadczeniu (wymienione w komórce D2).

Chociaż formuły wyszukiwania nie są w tym celu stworzone, możesz połączyć je z innymi funkcjami (takimi jak MIN i ABS), aby to zrobić.

Poniżej znajduje się formuła, która odnajdzie osobę z doświadczeniem najbliższym wymaganemu i zwróci nazwisko osoby. Zauważ, że doświadczenie musi być najbliższe (może być mniej lub więcej).

=INDEKS($A$2:$A$15,DOPASOWANIE(MIN(ABS(D2-B2:B15));ABS(D2-$B$2:$B$15);0))

Ponieważ jest to formuła tablicowa, musisz użyć klawiszy Control + Shift + Enter.

Sztuczka w tej formule polega na zmianie wartości wyszukiwania i tablicy wyszukiwania, aby znaleźć minimalną różnicę doświadczenia w wartościach wymaganych i rzeczywistych.

Zanim wyjaśnię formułę, zrozummy, jak zrobiłbyś to ręcznie.

Przejdziesz przez każdą komórkę w kolumnie B i znajdziesz różnicę w doświadczeniu między tym, co jest wymagane, a tym, które ma dana osoba. Kiedy już masz wszystkie różnice, znajdziesz tę, która jest minimalna i pobierzesz imię tej osoby.

Dokładnie to robimy z tą formułą.

Pozwól mi wyjaśnić.

Wartość wyszukiwania w formule PODAJ.POZYCJĘ to MIN(ABS(D2-B2:B15)).

Ta część podaje minimalną różnicę między danym doświadczeniem (czyli 2,5 roku) a wszystkimi innymi doświadczeniami. W tym przykładzie zwraca 0,3

Zauważ, że użyłem ABS, aby upewnić się, że szukam najbliższego (co może być mniej więcej niż podane doświadczenie).

Teraz ta minimalna wartość staje się naszą wartością wyszukiwania.

Tablica wyszukiwania w funkcji PODAJ.POZYCJĘ to ABS(D2-$B$2:$B$15).

Daje nam to tablicę liczb, od których odjęto 2,5 (wymagane doświadczenie).

Więc teraz mamy wartość wyszukiwania (0,3) i tablicę wyszukiwania ({6,8;0,8;19,5;21,8;14,5;11,2;0,3;9,2;2;9,8;14,8;0,4;23,8;2,9})

Funkcja MATCH znajduje w tej tablicy pozycję 0.3, która jest jednocześnie pozycją imienia osoby, która ma najbliższe doświadczenie.

Ten numer pozycji jest następnie używany przez funkcję INDEX do zwrócenia nazwiska osoby.

Powiązane Przeczytaj: Znajdź najbliższe dopasowanie w programie Excel (przykłady z użyciem formuł wyszukiwania)

Kliknij tutaj, aby pobrać przykładowy plik

Przykład 9: Użyj INDEX MATCH ze znakami wieloznacznymi

Jeśli chcesz wyszukać wartość, gdy występuje częściowe dopasowanie, musisz użyć symboli wieloznacznych.

Na przykład poniżej znajduje się zestaw danych z nazwą firmy i jej kapitalizacją rynkową, a chcesz uzyskać kapitalizację rynkową. dane dla trzech firm po prawej.

Ponieważ nie są to dokładne dopasowania, w tym przypadku nie można przeprowadzić regularnego wyszukiwania.

Ale nadal możesz uzyskać właściwe dane, używając gwiazdki (*), która jest znakiem wieloznacznym.

Poniżej znajduje się formuła, która poda dane, dopasowując nazwy firm z głównej kolumny i pobierając dla niej wartość kapitalizacji rynkowej.

=INDEKS($B$2:$B$10;DOPASUJ(D2&”*”,$A$2:$A$10,0)),1)

Pozwólcie, że wyjaśnię, jak działa ta formuła.

Ponieważ nie ma dokładnego dopasowania wartości wyszukiwania, użyłem D2&”*” jako wartość wyszukiwania w funkcji PODAJ.POZYCJĘ.

Gwiazdka to symbol wieloznaczny reprezentujący dowolną liczbę znaków. Oznacza to, że Apple* w formule oznaczałby dowolny ciąg tekstowy, który zaczyna się od słowa Apple i może mieć po nim dowolną liczbę znaków.

Więc kiedy Jabłko* jest używany jako wartość wyszukiwania, a formuła PODAJ.POZYCJĘ szuka jej w kolumnie A, zwraca pozycję „Apple Inc.”, ponieważ zaczyna się od słowa Apple.

Możesz również użyć symboli wieloznacznych, aby znaleźć ciągi tekstowe, w których wartość wyszukiwania znajduje się pomiędzy. Na przykład, jeśli użyjesz *Apple* jako wartości wyszukiwania, znajdzie każdy ciąg, który zawiera słowo jabłko w dowolnym miejscu.

Uwaga: Ta technika działa dobrze, gdy masz tylko jedną instancję dopasowania. Ale jeśli masz wiele wystąpień dopasowania (na przykład Apple Inc i Apple Corporation, funkcja PODAJ.POZYCJĘ zwróci tylko pozycję pierwszego zgodnego wystąpienia.

Przykład 10: Wyszukiwanie trójdrożne

Jest to zaawansowane zastosowanie funkcji INDEX MATCH, ale nadal omówię to, aby pokazać moc tej kombinacji.

Pamiętaj, że powiedziałem, że funkcja INDEX ma dwie składnie:

=INDEX (tablica, numer_wiersza, [num_kolumny]) =INDEX (tablica, numer_wiersza, [num_kolumny], [num_obszaru])

Do tej pory we wszystkich naszych przykładach używaliśmy tylko pierwszego.

Ale do wyszukiwania trójstronnego musisz użyć drugiej składni.

Pozwólcie, że najpierw wyjaśnię, co oznacza trójstronny wygląd.

W wyszukiwaniu dwukierunkowym używamy formuły INDEX MATCH, aby uzyskać oceny, gdy mamy nazwisko ucznia i nazwę przedmiotu. Na przykład pobieranie znaków Jima w matematyce jest wyszukiwaniem dwukierunkowym.

Trójstronny wygląd nadałby mu inny wymiar. Załóżmy na przykład, że masz zestaw danych, jak pokazano poniżej i chcesz poznać wynik Jima z matematyki na egzaminie śródsemestralnym, to byłoby to wyszukiwanie trójstronne.

Poniżej znajduje się formuła, która da wynik.

=INDEKS((($B$3:$D$7,$B$11:$D$15,$B$19:$D$23),DOPASUJ($F$5,$A$3:$A$7,0),DOPASUJ(G$4 ,$B$2:$D$2,0),(JEŻELI(G$3="Test jednostkowy",1,JEŻELI(G$3="Średniookresowy",2,3))))

Powyższy wzór sprawdzał się pod kątem trzech rzeczy – nazwiska ucznia, przedmiotu i egzaminu. Po znalezieniu właściwej wartości zwraca ją w komórce.

Pozwólcie, że wyjaśnię, jak działa ta formuła, dzieląc ją na części.

• tablica - ($ B $ 3: $ D $ 7, $ B $ 11: $ D $ 15, $ B $ 19: $ D $ 23): Zamiast używać pojedynczej tablicy, w tym przypadku użyłem trzech tablic w nawiasach.
• row_num - MATCH ($ F $ 5, $ A $ 3: $ A $ 7, 0): Funkcja MATCH służy do znalezienia pozycji nazwiska ucznia w komórce $ F $ 5 na liście nazwisk ucznia.
• col_num - MATCH(G$4,$B$2:$D$2,0): Funkcja MATCH służy do znalezienia pozycji nazwy podmiotu w komórce $ B $ 2 na liście nazw podmiotu.
• [numer_obszaru] - JEŻELI(G$3=”Test jednostkowy”,1,JEŻELI(G$3=”Średniookresowy”,2,3)): Wartość numeru obszaru informuje funkcję INDEX, której z trzech tablic użyć do pobrania wartości. Jeśli egzaminem jest Unit Term, funkcja JEŻELI zwróci 1, a funkcja INDEX użyje pierwszej tablicy do pobrania wartości. Jeśli egzamin jest w połowie semestru, formuła JEŻELI zwróci 2, w przeciwnym razie zwróci 3.

Jest to zaawansowany przykład korzystania z funkcji INDEX MATCH i prawdopodobnie nie znajdziesz sytuacji, w której będziesz musiał tego użyć. Ale nadal dobrze jest wiedzieć, co potrafią formuły Excela.

Kliknij tutaj, aby pobrać przykładowy plik

Dlaczego indeks/dopasowanie jest lepsze niż WYSZUKAJ.PIONOWO?

Albo to jest?

Tak, w większości przypadków.

Swoją sprawę przedstawię za chwilę.

Ale zanim to zrobię, pozwól, że powiem to - WYSZUKAJ.PIONOWO to niezwykle przydatna funkcja i uwielbiam ją. Potrafi wiele rzeczy w Excelu i sam go używam od czasu do czasu. Powiedziawszy to, nie oznacza to, że nie może być nic lepszego, a INDEX/MATCH (z większą elastycznością i funkcjonalnością) jest lepszy.

Więc jeśli chcesz wykonać podstawowe wyszukiwanie, lepiej użyć funkcji WYSZUKAJ.PIONOWO.

INDEX/MATCH to funkcja WYSZUKAJ.PIONOWO na sterydach. A kiedy już nauczysz się INDEKS / DOPASOWANIE, zawsze możesz wolą go używać (szczególnie ze względu na elastyczność, jaką ma).

Nie rozciągając go zbyt daleko, szybko przedstawię powody, dla których INDEX/MATCH jest lepszy niż WYSZUKAJ.PIONOWO.

INDEX/MATCH może patrzeć na lewo (jak również na prawo) od wartości wyszukiwania

Omówiłem to w jednym z powyższych przykładów.

Jeśli masz wartość, która znajduje się po lewej stronie wartości wyszukiwania, nie możesz tego zrobić za pomocą funkcji WYSZUKAJ.PIONOWO

Przynajmniej nie tylko dzięki funkcji WYSZUKAJ.PIONOWO.

Tak, możesz połączyć VLOOKUP z innymi formułami i zrobić to, ale staje się to skomplikowane i niechlujne.

Z drugiej strony INDEX/MATCH służy do wyszukiwania wszędzie (w lewo, w prawo, w górę lub w dół)

INDEX/MATCH może pracować z zakresami pionowymi i poziomymi

Ponownie, z pełnym szacunkiem dla funkcji WYSZUKAJ.PIONOWO, nie jest do tego stworzony.

W końcu litera V w funkcji WYSZUKAJ.PIONOWO oznacza pion.

WYSZUKAJ.PIONOWO może przeszukiwać tylko dane w pionie, podczas gdy INDEKS / DOPASOWANIE może przeszukiwać dane zarówno w pionie, jak iw poziomie.

Oczywiście istnieje funkcja WYSZUKAJ.POZIOMO, która zajmuje się wyszukiwaniem poziomym, ale to nie jest WYSZUKAJ.PIONOWO… prawda?

Podoba mi się fakt, że kombinacja INDEX MATCH jest wystarczająco elastyczna, aby pracować zarówno z danymi pionowymi, jak i poziomymi.

WYSZUKAJ.PIONOWO nie działa z malejącymi danymi

Jeśli chodzi o przybliżone dopasowanie, WYSZUKAJ.PIONOWO i INDEX/MATCH są na tym samym poziomie.

Ale INDEX MATCH ma rację, ponieważ może również obsługiwać dane w kolejności malejącej.

Pokazuję to w jednym z przykładów w tym samouczku, w którym musimy znaleźć oceny uczniów na podstawie tabeli ocen. Jeśli tabela jest posortowana w kolejności malejącej, funkcja WYSZUKAJ.PIONOWO nie zadziała (ale DOPASOWANIE DO INDEKSU).

INDEX/MATCH może być nieco szybszy

Będę prawdomówny. Sam nie przeprowadziłem tego testu.

Opieram się na mądrości mistrza Excela - Charleya Kyda.

Różnica w szybkości funkcji WYSZUKAJ.PIONOWO i INDEX/MATCH jest prawie niezauważalna, gdy masz małe zestawy danych. Ale jeśli masz tysiące wierszy i wiele kolumn, może to być decydujący czynnik.

W swoim artykule Charley Kyd stwierdza:

„W najgorszym przypadku metoda INDEX-MATCH jest tak szybka, jak WYSZUKAJ.PIONOWO; w najlepszym wydaniu jest znacznie szybszy”.

INDEX/MATCH jest niezależny od aktualnej pozycji kolumny

Jeśli masz zestaw danych, jak pokazano poniżej, gdy pobierasz wynik Jima z fizyki, możesz to zrobić za pomocą funkcji WYSZUKAJ.PIONOWO.

Aby to zrobić, możesz określić numer kolumny jako 3 w funkcji WYSZUKAJ.PIONOWO.

Wszystko w porządku.

Ale co, jeśli usunę kolumnę Math.

W takim przypadku formuła WYSZUKAJ.PIONOWO zepsuje się.

Czemu? - Ponieważ było zakodowane użycie trzeciej kolumny, a kiedy usunę kolumnę pomiędzy, trzecia kolumna staje się drugą kolumną.

W tym przypadku użycie funkcji INDEX/MATCH jest lepsze, ponieważ możesz zmienić numer kolumny na dynamiczną za pomocą funkcji PODAJ.POZYCJĘ. Więc zamiast numeru kolumny sprawdza nazwę podmiotu i używa go do zwrócenia numeru kolumny.

Z pewnością możesz to zrobić, łącząc VLOOKUP z MATCH, ale jeśli mimo to łączysz, dlaczego nie zrobić tego za pomocą INDEX, który jest o wiele bardziej elastyczny.

Korzystając z funkcji INDEX/MATCH, możesz bezpiecznie wstawiać/usuwać kolumny w swoim zestawie danych.

Pomimo tych wszystkich czynników istnieje powód, dla którego WYSZUKAJ.PIONOWO jest tak popularny.

I to jest ważny powód.

WYSZUKAJ.PIONOWO jest łatwiejszy w użyciu

WYSZUKAJ.PIONOWO przyjmuje maksymalnie cztery argumenty. Jeśli potrafisz owinąć głowę wokół tych czterech, możesz iść.

A ponieważ większość podstawowych przypadków wyszukiwania jest również obsługiwana przez funkcję WYSZUKAJ.PIONOWO, szybko stała się ona najpopularniejszą funkcją Excela.

Nazywam to królem funkcji Excela.

Z drugiej strony INDEX/MATCH jest nieco trudniejszy w użyciu. Możesz się zawiesić, gdy zaczniesz go używać, ale dla początkującego funkcja WYSZUKAJ.PIONOWO jest znacznie łatwiejsza do wyjaśnienia i nauczenia.

A to nie jest gra o sumie zerowej.

Tak więc, jeśli jesteś nowy w świecie wyszukiwania i nie wiesz, jak korzystać z funkcji WYSZUKAJ.PIONOWO, lepiej się tego naucz.

Mam szczegółowy przewodnik dotyczący korzystania z funkcji WYSZUKAJ.PIONOWO w programie Excel (z wieloma przykładami)

Moim zamiarem w tym artykule nie jest zestawienie ze sobą dwóch niesamowitych funkcji. Chciałem pokazać Ci moc kombinacji INDEX MATCH i wszystkie wspaniałe rzeczy, które może zdziałać.

Mam nadzieję, że ten artykuł okazał się przydatny.

Daj mi znać swoje przemyślenia w sekcji komentarzy, a jeśli znajdziesz jakiś błąd w tym samouczku, daj mi znać.