Dlaczego INDEKS i PODAJ.POZYCJĘ są lepsze niż WYSZUKAJ.PIONOWO w programie Excel

Wcześniej wyjaśniliśmy początkującym, jak korzystać z podstawowych funkcji WYSZUKAJ.PIONOWO (po angielsku VLOOKUP, skrót oznacza „pionową funkcję wyszukiwania”). Doświadczonym użytkownikom pokazano kilka bardziej skomplikowanych formuł.

W tym artykule postaramy się podać informacje o innej metodzie pracy z wyszukiwaniem pionowym.

Możesz się zastanawiać: „Dlaczego jest to konieczne?”. A to jest konieczne, aby pokazać wszystkie możliwe metody wyszukiwania. Ponadto liczne ograniczenia WYSZUKAJ.PIONOWO często uniemożliwiają uzyskanie pożądanego rezultatu. Pod tym względem funkcja INDEX() MATCH() jest znacznie bardziej funkcjonalna i różnorodna, a także ma mniej ograniczeń.

Podstawy DOPASOWANIE DO INDEKSU

Ponieważ celem tego przewodnika jest pokazanie, jak dobra jest ta funkcja, my Przyjrzyjmy się podstawowym informacjom dotyczącym zasad jego działania. Pokażemy przykłady, a także zastanowimy się, dlaczego jest lepszy niż WYSZUKAJ.PIONOWO ().

INDEKS Składnia funkcji i użycie

Ta funkcja pomaga w znalezieniu żądanej wartości wśród określonych obszarów wyszukiwania na podstawie numeru kolumny lub wiersza. Składnia:

=INDEX(tablica, numer wiersza, numer kolumny):

• array – obszar, w którym będzie się odbywać wyszukiwanie;
• line number – numer linii do przeszukania w określonej tablicy. Jeśli numer wiersza jest nieznany, należy podać numer kolumny;
• numer kolumny – numer kolumny, który ma znaleźć się w określonej tablicy. Jeśli wartość jest nieznana, wymagany jest numer wiersza.

Przykład prostej formuły:

=INDEKS(A1:S10,2,3;XNUMX)

Funkcja wyszuka w zakresie od A1 do C10. Liczby pokazują, z którego wiersza (2) i kolumny (3) ma być wyświetlana żądana wartość. Wynikiem będzie komórka C2.

Całkiem proste, prawda? Ale kiedy pracujesz z prawdziwymi dokumentami, prawdopodobnie nie będziesz mieć informacji dotyczących numerów kolumn lub komórek. Do tego służy funkcja MATCH().

Składnia i użycie funkcji DOPASUJ

Funkcja MATCH() wyszukuje żądaną wartość i pokazuje jej przybliżoną liczbę w określonym obszarze wyszukiwania.

Składnia searchpos() wygląda tak:

=MATCH(wartość do wyszukania, tablica do wyszukania, typ dopasowania)

• search value – liczba lub tekst do wyszukania;
• przeszukiwana tablica – obszar, w którym nastąpi wyszukiwanie;
• typ dopasowania – określa, czy szukać dokładnej wartości, czy wartości najbliższych jej:
  • 1 (lub brak określonej wartości) – zwraca największą wartość, która jest równa lub mniejsza od podanej wartości;
  • 0 – pokazuje dokładne dopasowanie do szukanej wartości. W kombinacji INDEX() MATCH() prawie zawsze będziesz potrzebować dokładnego dopasowania, więc piszemy 0;
  • -1 — pokazuje najmniejszą wartość, która jest większa lub równa wartości określonej w formule. Sortowanie odbywa się w kolejności malejącej.

Na przykład w zakresie B1:B3 zarejestrowane są Nowy Jork, Paryż, Londyn. Poniższy wzór pokaże numer 3, ponieważ Londyn jest trzeci na liście:

= EKSPOZYCJA(Londyn;B1:B3,0)

Jak pracować z funkcją INDEX MATCH 

Zapewne już zacząłeś rozumieć zasadę, na której zbudowana jest wspólna praca tych funkcji. Krótko mówiąc, więc INDEX() wyszukuje żądaną wartość wśród określonych wierszy i kolumn. A MATCH() pokazuje liczby tych wartości:

=INDEX(kolumna, z której zwracana jest wartość, PODAJ.POZYCJĘ(wartość do wyszukania, kolumna do wyszukania, 0))

Nadal nie możesz zrozumieć, jak to działa? Może przykład wyjaśnię lepiej. Załóżmy, że masz listę światowych stolic i ich ludności:

W celu określenia wielkości populacji określonej stolicy, na przykład stolicy Japonii, posługujemy się następującym wzorem:

=INDEKS(C2:C10; PODAJ.POZYCJĘ(Japonia;A2:A10,0;XNUMX))

Wyjaśnienie:

• Funkcja PODAJ.POZYCJĘ() szuka wartości „Japonia” w tablicy A2:A10 i zwraca liczbę 3, ponieważ Japonia jest trzecią wartością na liście. 
• Ta liczba idzie donumer kolejki” w formule INDEX() i nakazuje funkcji wydrukowanie wartości z tego wiersza.

Zatem powyższa formuła staje się formułą standardową INDEKS(C2:C10,3;XNUMX). Formuła przeszukuje komórki od C2 do C10 i zwraca dane z trzeciej komórki z tego zakresu, czyli C4, ponieważ odliczanie rozpoczyna się od drugiego wiersza.

Nie chcesz przepisywać nazwy miasta w formule? Następnie wpisz go w dowolnej komórce, powiedz F1 i użyj go jako odniesienia w formule PODAJ.POZYCJĘ(). W efekcie otrzymujesz dynamiczną formułę wyszukiwania:

=ИНДЕКС(С2:С10, ПОИСКПОЗ( )(F1,A2:A10,0))

Ważne! Liczba wierszy w szyk INDEX() musi być taka sama jak liczba wierszy w uważany za tablicę w MATCH(), w przeciwnym razie otrzymasz zły wynik.

Poczekaj chwilę, dlaczego po prostu nie użyć formuły WYSZUKAJ.PIONOWO()?

=WYSZUKAJ.PIONOWO(F1, A2:C10; 3; Fałsz)

 Jaki jest sens marnowania czasu na rozgryzanie tych wszystkich zawiłości funkcji DOPASOWANIE DO INDEKSU?

W takim przypadku nie ma znaczenia, której funkcji użyć. To jest tylko przykład, aby zrozumieć, jak działają funkcje INDEX() i MATCH(). Inne przykłady pokażą, do czego te funkcje są zdolne w sytuacjach, gdy funkcja WYSZUKAJ.PIONOWO jest bezsilna. 

DOPASOWANIE DO INDEKSU lub WYSZUKAJ.PIONOWO

Przy podejmowaniu decyzji, której formuły wyszukiwania użyć, wiele osób zgadza się, że INDEX() i PODAJ.POZYCJĘ() są znacznie lepsze od funkcji WYSZUKAJ.PIONOWO. Jednak wiele osób nadal używa funkcji WYSZUKAJ.PIONOWO(). Po pierwsze, VLOOKUP() jest prostsze, a po drugie, użytkownicy nie w pełni rozumieją wszystkie zalety pracy z INDEX() i MATCH(). Bez tej wiedzy nikt nie zgodzi się poświęcić czasu na studiowanie złożonego systemu.

Oto kluczowe zalety funkcji INDEX() i MATCH() w porównaniu z funkcją WYSZUKAJ.PIONOWO():

• Szukaj od prawej do lewej. VLOOKUP() nie może wyszukiwać od prawej do lewej, więc szukane wartości muszą zawsze znajdować się w skrajnych lewej kolumnach tabeli. Ale INDEX() i MATCH() poradzą sobie z tym bez problemu. Z tego artykułu dowiesz się, jak to wygląda w praktyce: jak znaleźć pożądaną wartość po lewej stronie.

1. Bezpieczne dodawanie lub usuwanie kolumn. Formuła WYSZUKAJ.PIONOWO() wyświetla nieprawidłowe wyniki podczas usuwania lub dodawania kolumn, ponieważ funkcja WYSZUKAJ.PIONOWO() wymaga dokładnego numeru kolumny, aby się powiodła. Oczywiście, gdy kolumny są dodawane lub usuwane, zmieniają się również ich numery. 

A w formułach INDEKS() i PODAJ.POZYCJĘ() określany jest zakres kolumn, a nie poszczególne kolumny. Dzięki temu możesz bezpiecznie dodawać i usuwać kolumny bez konieczności każdorazowego aktualizowania formuły.

1. Brak ograniczeń liczby wyszukiwań. Podczas korzystania z funkcji WYSZUKAJ.PIONOWO() łączna liczba kryteriów wyszukiwania nie może przekraczać 255 znaków, w przeciwnym razie otrzymasz #ARG! Więc jeśli twoje dane zawierają dużą liczbę znaków, INDEX() i MATCH() są najlepszą opcją.
2. Wysoka prędkość przetwarzania. Jeśli Twoje stoły są stosunkowo małe, prawdopodobnie nie zauważysz żadnej różnicy. Ale jeśli tabela zawiera setki lub tysiące wierszy, a zatem są setki i tysiące formuł, INDEKS () i PODAJ.POZYCJĘ () poradzą sobie znacznie szybciej niż WYSZUKAJ.PIONOWO (). Faktem jest, że Excel przetworzy tylko kolumny określone w formule, zamiast przetwarzać całą tabelę. 

Wpływ na wydajność funkcji WYSZUKAJ.PIONOWO() będzie szczególnie zauważalny, jeśli arkusz zawiera dużą liczbę formuł, takich jak WYSZUKAJ.PIONOWO() i SUMA(). Do przeanalizowania każdej wartości w tablicy wymagane są oddzielne sprawdzenia funkcji WYSZUKAJ.PIONOWO(). Tak więc Excel musi przetwarzać ogromną ilość informacji, a to znacznie spowalnia pracę.

Przykłady formuł 

Zorientowaliśmy się już przydatność tych funkcji, więc możemy przejść do najciekawszej części: zastosowania wiedzy w praktyce.

Formuła do wyszukiwania od prawej do lewej

Jak już wspomniano, funkcja WYSZUKAJ.PIONOWO nie może przeprowadzić tej formy wyszukiwania. Tak więc, jeśli żądane wartości nie znajdują się w skrajnej lewej kolumnie, WYSZUKAJ.PIONOWO() nie da wyniku. Funkcje INDEX() i MATCH() są bardziej uniwersalne, a lokalizacja wartości nie odgrywa dużej roli w ich działaniu.

Na przykład dodamy kolumnę rang po lewej stronie naszej tabeli i spróbujemy dowiedzieć się, jaką rangę pod względem liczby ludności zajmuje stolica Naszego Kraju.

W komórce G1 zapisujemy wartość do znalezienia, a następnie używamy następującej formuły, aby wyszukać w zakresie C1:C10 i zwrócić odpowiednią wartość z A2:A10:

=ИНДЕКС(А2:А10, ПОИСКПОЗ(G1,C1:C10,0))

Monituj Jeśli planujesz użyć tej formuły dla wielu komórek, upewnij się, że naprawiłeś zakresy za pomocą adresowania bezwzględnego (na przykład 2 $ A $: 10 $ A $ C $ 2: 4 C $ 10).

INDEKS WIĘCEJ EKSPOZYCYJNE WIĘCEJ EKSPOZYCYJNE  wyszukiwać w kolumnach i wierszach

W powyższych przykładach użyliśmy tych funkcji jako zamiennika funkcji WYSZUKAJ.PIONOWO() do zwracania wartości z predefiniowanego zakresu wierszy. Ale co, jeśli potrzebujesz przeprowadzić wyszukiwanie macierzowe lub dwustronne?

Brzmi to skomplikowanie, ale wzór na takie obliczenia jest podobny do standardowej formuły INDEX() PODAJ.POZYCJĘ(), z tylko jedną różnicą: formuła PODAJ.POZYCJĘ() musi być użyta dwukrotnie. Za pierwszym razem, aby uzyskać numer wiersza, a za drugim razem, aby uzyskać numer kolumny:

=INDEKS(tablica; PODAJ.POZYCJĘ(pionowa wartość wyszukiwania; kolumna wyszukiwania; 0); PODAJ.POZYCJĘ(pozioma wartość wyszukiwania; wiersz wyszukiwania; 0))

Spójrzmy na poniższą tabelę i spróbujmy stworzyć formułę INDEKS () WYRAŻ () WYRAŻ () w celu wyświetlenia danych demograficznych w określonym kraju w wybranym roku.

Kraj docelowy znajduje się w komórce G1 (wyszukiwanie pionowe), a rok docelowy znajduje się w komórce G2 (wyszukiwanie poziome). Formuła będzie wyglądać tak:

=ИНДЕКС(B2:D11, ПОИСКПОЗ(G1,A2:A11,0), ПОИСКПОЗ(G2,B1:D1,0))

Jak działa ta formuła

Podobnie jak w przypadku innych złożonych formuł, łatwiej je zrozumieć, rozkładając je na poszczególne równania. A potem możesz zrozumieć, co robi każda pojedyncza funkcja:

• PODAJ.POZYCJĘ(G1,A2:A11,0) – szuka wartości (G1) w zakresie A2:A11 i pokazuje numer tej wartości, w naszym przypadku jest to 2;
• SZUKAJ(G2,B1:D1,0) – szuka wartości (G2) w zakresie B1:D1. W tym przypadku wynik wynosił 3.

Znalezione numery wierszy i kolumn są wysyłane do odpowiedniej wartości we wzorze INDEX():

=INDEKS(B2:D11,2,3;XNUMX)

W rezultacie mamy wartość, która znajduje się w komórce na przecięciu 2 wierszy i 3 kolumn w zakresie B2:D11. Formuła pokazuje żądaną wartość, która znajduje się w komórce D3.

Szukaj według wielu warunków za pomocą INDEKSU i DOPASUJ

Jeśli przeczytałeś nasz przewodnik dotyczący funkcji WYSZUKAJ.PIONOWO(), prawdopodobnie wypróbowałeś wiele formuł wyszukiwania. Ale ta metoda wyszukiwania ma jedno istotne ograniczenie – konieczność dodania kolumny pomocniczej.

Ale dobra wiadomość jest taka Dzięki INDEX() i MATCH() możesz wyszukiwać wiele warunków bez konieczności edytowania lub zmieniania arkusza roboczego.

Oto ogólna formuła wyszukiwania wielowarunkowego dla funkcji INDEX() MATCH():

{=ИНДЕКС(диапазон поиска, ПОИСКПОЗ(1,условие1=диапазон1)*(условвие2=диапазон2),0))}

Notatka: ta formuła musi być używana razem ze skrótem klawiaturowym CTRL+SHIFT+ENTER.

Załóżmy, że musisz znaleźć szukaną wartość na podstawie 2 warunków: Kupujący и Produkt.

Wymaga to następującej formuły:

=ИНДЕКС(С2:С10, ПОИСКПОЗ(1,(F1=A2:A10)*(F2=B1:B10),0))

W tym wzorze C2:C10 to zakres, w którym nastąpi wyszukiwanie, F1 – ten stan, A2:A10 — to zakres do porównania stanu, F2 – warunek 2, V2: V10 – zakres do porównania warunku 2.

Nie zapomnij nacisnąć kombinacji pod koniec pracy z formułą CTRL+SHIFT+ENTER – Excel automatycznie zamknie formułę nawiasami klamrowymi, jak pokazano na przykładzie:

Jeśli nie chcesz używać formuły tablicowej do swojej pracy, dodaj kolejną funkcję INDEX() do formuły i naciśnij ENTER, będzie to wyglądać jak w przykładzie:

Jak działają te formuły

Ta formuła działa w taki sam sposób, jak standardowa formuła INDEX() PODAJ.POZYCJĘ(). Aby wyszukać wiele warunków, po prostu utwórz wiele warunków Fałsz i Prawda, które reprezentują prawidłowe i nieprawidłowe poszczególne warunki. A następnie te warunki dotyczą wszystkich odpowiednich elementów tablicy. Formuła konwertuje argumenty Fałsz i Prawda odpowiednio na 0 i 1 i generuje tablicę, gdzie 1 to pasujące wartości znalezione w ciągu. MATCH() znajdzie pierwszą wartość pasującą do 1 i przekaże ją do formuły INDEX(). A to z kolei zwróci już pożądaną wartość w określonym wierszu z żądanej kolumny.

Formuła bez tablicy zależy od zdolności funkcji INDEX() do samodzielnego ich obsługi. Drugi INDEX() w formule pasuje do fałszu (0), więc przekazuje całą tablicę z tymi wartościami do formuły PODAJ.POZYCJĘ(). 

To dość długie wyjaśnienie logiki tej formuły. Więcej informacji znajdziesz w artykule „INDEX MATCH z wieloma warunkami".

ŚREDNIA, MAX i MIN w INDEKSIE i DOPASOWANIU

Excel ma swoje własne specjalne funkcje do znajdowania średnich, maksimów i minimów. Ale co, jeśli chcesz uzyskać dane z komórki powiązanej z tymi wartościami? W tym przypadku AVERAGE, MAX i MIN muszą być używane w połączeniu z INDEX i MATCH.

DOPASOWANIE DO INDEKSU i MAX

Aby znaleźć największą wartość w kolumnie D i wyświetlić ją w kolumnie C, użyj formuły: 

=ИНДЕКС(С2:С10, ПОИСКПОЗ(МАКС(D2:D10),D2:D10,0))

DOPASOWANIE INDEKSU i MIN

Aby znaleźć najmniejszą wartość w kolumnie D i wyświetlić ją w kolumnie C, użyj następującej formuły:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(МИН(D2:D10),D2:D10,0))

WYSZUKAJ INDEKS i WĘŻ

Aby znaleźć średnią wartość w kolumnie D i wyświetlić tę wartość w C:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(СРЗНАЧ(D2:D10),D2:D10,-1))

W zależności od sposobu zapisu danych, trzecim argumentem funkcji MATCH() jest 1, 0 lub -1:

• jeśli kolumny są posortowane rosnąco, ustaw 1 (wtedy formuła obliczy wartość maksymalną, która jest mniejsza lub równa wartości średniej);
• jeśli sortowanie jest malejące, to -1 (formuła wypisze minimalną wartość, która jest większa lub równa średniej);
• jeśli tablica przeglądowa zawiera wartość dokładnie równą średniej, ustaw ją na 0. 

 W naszym przykładzie populacja jest posortowana w porządku malejącym, więc podajemy -1. A wynikiem jest Tokio, ponieważ liczba ludności (13,189 000) jest najbliższa średniej (XNUMX).

WYSZUKAJ.PIONOWO() może również wykonywać takie obliczenia, ale tylko jako formuła tablicowa: WYSZUKAJ.PIONOWO z wartościami ŚREDNIA, MIN i MAX.

INDEX MATCH i ESND/IFERROR

Prawdopodobnie już zauważyłeś, że jeśli formuła nie może znaleźć żądanej wartości, zgłasza błąd # N / A. Możesz zastąpić standardowy komunikat o błędzie czymś bardziej informacyjnym. Na przykład ustaw argument w formule W XIX wieku:

=ЕСНД(ИНДЕКС(С2:С10,ПОИСКПОЗ(F1,A2:A10,0)),значение не найдено)

Dzięki tej formule, jeśli wprowadzisz dane, których nie ma w tabeli, formularz poda Ci określony komunikat.

Jeśli chcesz wyłapać wszystkie błędy, to z wyjątkiem W XIX wieku mogą być stosowane JEŻELI BŁĄD:

=JEŻELIBŁĄD(INDEKS(C2:C10;PODZIEL(F1,A2:A10,0)), "Coś poszło nie tak!")

Pamiętaj jednak, że maskowanie błędów w ten sposób nie jest dobrym pomysłem, ponieważ standardowe błędy zgłaszają naruszenia w formule.

Mamy nadzieję, że nasz przewodnik dotyczący korzystania z funkcji INDEX MATCH() okazał się pomocny.