Dopasowanie indeksu: lepsza alternatywa dla Vlookup

WYSZUKAJ.PIONOWO to jedna z tych funkcji, dzięki którym Excel jest świetny. Chociaż wielu może początkowo nie uważać tego za intuicyjne, wkrótce staje się ono drugą naturą i niezbędne. Niektórzy nawet uważają, że zmienia życie. WYSZUKAJ.PIONOWO ma jednak swoje problemy, po pierwsze, czyta tylko od lewej do prawej. Jeśli chcesz wyszukać wartość po lewej stronie kolumny odniesienia, musisz zmienić kolejność arkusza, aby użyć tej funkcji.

WYSZUKAJ.PIONOWO sprawdza również wiele kolumn, gdy w rzeczywistości używasz tylko dwóch z nich. standardowa składnia odwołuje się tylko do względnych wartości kolumn, jeśli zmodyfikujesz tabelę, ponownie nie masz szczęścia i musisz trochę reorganizować i przepisywać funkcje, co może być bardzo nużące w dużych skoroszytach. Powoduje to również problemy z kopiowaniem funkcji do innych kolumn.

Istnieje jednak alternatywa, INDEKS + DOPASOWANIE. Nie tylko replikuje funkcjonalność, ale ma również własne korzyści i bonusy. Być może wcześniej tego nie słyszałeś (zwłaszcza jeśli nie czytasz o Excelu dla zabawy), ale pod koniec tego artykułu będziesz mógł zastosować to w praktyce i czerpać korzyści.

Podział INDEKSU + DOPASUJ

Coś, co wielu użytkowników przeocza, to fakt, że funkcje programu Excel można łączyć ze sobą i łączyć, aby stworzyć coś jeszcze potężniejszego niż części składowe. Każda funkcja przyjmuje argument, jeśli potrzebujesz trochę więcej mocy lub rozszerzonej funkcjonalności, te argumenty mogą być utworzone z innych funkcji.

INDEKS

=INDEKS(tablica; numer wiersza)

Funkcja INDEKS pobiera tablicę użytkownika, zestaw wartości, takich jak kolumna, i zwraca wartość komórki w tej konkretnej pozycji. Na przykład mam kolumnę danych zapisującą pozycje z wyścigu, który odbył się w pewnym momencie w przeszłości. Chcę wiedzieć, który kierowca skończył na trzecim miejscu, żeby to znaleźć mogę po prostu wpisać,

=INDEKS(B2:B6;3)

a to zwróci Rusty Shackleford.

Prosty, ale użyteczny. Mamy teraz możliwość zwracania wartości w tablicy.

MECZ

=MATCH(wartość wyszukiwania, tablica wyszukiwania, typ dopasowania)

MATCH() to kolejna prosta funkcja, powiązana z funkcją INDEX, którą właśnie omówiliśmy. Jednak zamiast zwracać wartość, zamiast tego zwraca liczbę oznaczającą względną pozycję wartości w tablicy. PODAJ.POZYCJĘ wymaga następujących argumentów; wartość, tablicę do przeszukania i typ dopasowania. Typ dopasowania będzie zwykle ustawiony na 0, ale zależy od Twojego zastosowania. 0 oznacza, że ​​chcesz tylko dokładnego dopasowania, 1 lub -1 oznacza, że ​​nie ma dokładnego dopasowania, które chcesz następnej najbliższej wartości.

Korzystając z naszego przykładu wyścigu powyżej, znam kierowcę, którego muszę znaleźć, ale nie znam jego pozycji w końcowej klasyfikacji wyścigu. Ponieważ pozycja jest względna, muszę się upewnić, że pokrywam wszystkie wyniki, zaczynając od pierwszej pozycji.

=PODZIEL("Pstrąg Kilgore", B2:B6, 0)

To zwróci wartość 4. Ponieważ startowaliśmy od góry, wiemy, że Trout zajął w tym wyścigu czwartą pozycję.

DOPASOWANIE DO INDEKSU

Może widzisz, dokąd teraz zmierzamy, a może przeskoczyłeś, aby dostać się do dobrych rzeczy. Używając INDEX + MATCH możemy połączyć te dwie proste funkcje, aby stworzyć coś podobnego, ale bardziej elastycznego niż nasza zaufana WYSZUKAJ.PIONOWO. Nie martw się, jeśli musisz przeczytać to kilka razy, na początku może to być trochę trudne. Kiedy już zrozumiesz, co się dzieje, naprawdę łatwo jest odbudować to z komponentów.

INDEKS zwraca określoną wartość. Jest to podstawowa funkcja WYSZUKAJ.PIONOWO, biorąc pod uwagę zestaw parametrów; możesz szybko znaleźć potrzebną wartość. Teraz brakuje nam tylko sposobu na znalezienie pozycji w rzędzie. Jeśli musisz znaleźć to ręcznie, tracisz użyteczność funkcji. Używając PODAJ.POZYCJĘ w drugim argumencie, możemy wziąć jego wynik, pozycję względną, i wepchnąć to do funkcji Index, rozwiązując ten problem. Teraz mamy coś, co robi dokładnie to samo, co WYSZUKAJ.PIONOWO, tylko trochę więcej wpisując.

Wprowadzanie funkcji w praktykę

Korzystając z poniższej tabeli, chcemy dowiedzieć się, jaki typ produktu jest powiązany z określonym identyfikatorem produktu.

Aby użyć INDEX MATCH, zaczynamy od INDEX, ponieważ ostatecznie chcemy zwrócić określoną wartość, a nie pozycję. Wewnątrz INDEKSU dołączamy MATCH, aby zwrócić pozycję naszej funkcji INDEX.

=INDEKS(A2:A6;PODZIEL(8316;B2:B6,0))

To, co się tutaj dzieje, polega na tym, że przeszukam kolumnę A pod kątem wartości wiersza w kolumnie B, dostarczonej przez funkcję Match.

Wewnątrz funkcji dopasowania określam, że muszę przejrzeć kody ID produktu w B2:B6 i odesłać pozycję o wartości 8316. W tym przypadku jest to pozycja 4, która jest odpowiednikiem wpisania

=INDEKS(A2:A6;4)

Excel uruchamia funkcję i znajduje pozycję w tablicy o określonym numerze identyfikacyjnym. Zajmuje tę pozycję i szuka tego samego wiersza w naszej kolumnie A, zwracając typ ciężarówki dla określonego identyfikatora. Tak więc ostatecznie otrzymujemy te same wyniki z nieco większą elastycznością danych wejściowych.

Korzyści funkcji

Siła INDEX + MATCH pochodzi z mniejszego wyboru danych. Jeśli masz 15 kolumn, ale używasz tylko cyfr 1 i 15, po co wciągać wszystko inne? Może to przyspieszyć działanie dużych zestawów danych.

Druga rozszerzona funkcjonalność, którą indeks może odczytać z lewej lub prawej strony, bez względu na to, gdzie umieścisz kolumny, INDEKS jest obojętny na to, czy znajduje się po lewej czy prawej stronie początkowej tablicy, w przeciwieństwie do funkcji WYSZUKAJ.PIONOWO. Jeśli spojrzysz ponownie na powyższy przykład, to właśnie zrobiliśmy, pracując od prawej do lewej. Funkcja ta może również służyć jako kontrola zapewnienia jakości, ponieważ musisz ręcznie określić dwie potrzebne kolumny, zamiast wprowadzać względny numer kolumny gdzieś w wybranym miejscu.

Potrzebujesz więcej materiałów wizualnych? Obejrzyj nagranie ekranu przykładu dopasowania indeksu, w którym wstawiamy końcowe adresy URL do szablonu tekstu reklamy.

Wniosek

Śmiało i baw się z INDEX + MATCH. To bardzo potężna funkcja, która może zwiększyć Twoje możliwości wyszukiwania. Gdy kliknie, jestem pewien, że zostaniesz sprzedany i nigdy się nie odwrócisz. Zawsze trzeba trochę się nauczyć, kiedy próbujesz czegoś nowego, zobowiąż się do korzystania z funkcji Index Match przez jakiś czas. Na początku będzie to niezgrabne, ale wkrótce zobaczysz korzyści.

———-
Post zaktualizowany przez Jennę Kelly (poprzednia data publikacji: 2/3/14)