W tym artykule dowiemy się, jak używać funkcji INDEKS i DOPASUJ do wyszukiwania wartości w programie Excel.
Funkcja INDEX & MATCH zastępująca funkcję przeglądania
Zwykle używamy funkcji WYSZUKAJ.PIONOWO, aby znaleźć wartość znając indeks wiersza i numer kolumny. W przypadku funkcji WYSZUKAJ.PIONOWO musimy mieć numer kolumny i kryteria dopasowania wiersza oraz najważniejszą rzecz, aby dowiedzieć się, że wartość znajduje się po prawej stronie dopasowania. Ale dane nie zawsze to potwierdzają. Wiele razy musimy po prostu wyszukać wartości, nie znając indeksu wiersza lub kolumny, po prostu mając wartości do dopasowania. Na przykład: znalezienie wynagrodzenia pracownika posiadającego identyfikator pracownika. W tym będziemy szukać identyfikatora pracownika dla indeksu wiersza i wynagrodzenia dla indeksu kolumny. Dowiedzmy się, jak możemy wykonać to wyszukiwanie za pomocą INDEX & MATCH.
Jak rozwiązać problem?
Aby formuła zrozumiała, najpierw musimy trochę zrewidować następujące funkcje
- funkcja INDEKS
- Funkcja PODAJ.POZYCJĘ
Teraz stworzymy formułę korzystając z powyższych funkcji. Funkcja dopasowania zwróci indeks wartości wyszukiwania1 w polu nagłówka wiersza. A inna funkcja MATCH zwróci indeks wartości wyszukiwania 2 z pola nagłówka kolumny. Numery indeksów zostaną teraz podane do funkcji INDEX, aby pobrać wartości pod wartością wyszukiwania z danych tabeli.
Formuła ogólna:
| = INDEKS ( dane , PODAJ.POZYCJĘ ( szukana_wartość1, nagłówki_wierszów, 0 , PODAJ.POZYCJĘ ( szukana_wartość2, nagłówki_kolumn, 0 )) ) |
dane : tablica wartości wewnątrz tabeli bez nagłówków
lookup_value1 : wartość do wyszukania w row_header.
wiersze_nagłówki : Tablica indeksów wierszy do wyszukania.
lookup_value1 : wartość do wyszukania w nagłówku_kolumny.
kolumny_nagłówki : kolumna Tablica indeksów do wyszukania.
Przykład:
Powyższe stwierdzenia mogą być trudne do zrozumienia. Zrozummy to, używając wzoru w przykładzie
Tutaj mamy listę wyników uzyskanych przez uczniów wraz z ich listą przedmiotów. Musimy znaleźć wynik dla konkretnego ucznia (Gary) i przedmiotu (nauki społeczne), jak pokazano na poniższym zrzucie ekranu.
Wartość Student 1 musi być zgodna z tablicą Row_header, a wartość Temat 2 musi być zgodna z tablicą Column_header.
Użyj formuły w komórce J6:
| = INDEKS ( tabela , PODAJ.POZYCJĘ ( J5 , wiersz , 0 , PODAJ.POZYCJĘ ( J4, kolumna ) ) ) |
Wyjaśnienie:
- Funkcja DOPASUJ dopasowuje wartość Studenta w komórce J4 do tablicy nagłówków wiersza i zwraca jej pozycję 3 jako liczba.
- Funkcja PODAJ.POZYCJĘ dopasowuje wartość Temat w komórce J5 do tablicy nagłówków kolumn i zwraca jej pozycję 4 jako liczba.
- Funkcja INDEX pobiera numer indeksu wiersza i kolumny, wyszukuje dane w tabeli i zwraca pasującą wartość.
- Argument typu MATCH jest ustalony na 0. Ponieważ formuła wyodrębni dokładne dopasowanie.
Tutaj wartości formuły są podawane jako odwołania do komórek, a wiersz_nagłówek, tabela i nagłówek_kolumny jako nazwane zakresy.
Jak widać na powyższym zrzucie, otrzymaliśmy wynik uzyskany przez ucznia Gary w temacie Studia społeczne jak 36 . I to dowodzi, że formuła działa dobrze, a w przypadku wątpliwości zapoznaj się z poniższymi uwagami dla zrozumienia.
Teraz użyjemy przybliżonego dopasowania z nagłówkami wierszy i nagłówkami kolumn jako liczb. Dopasowanie przybliżone przyjmuje tylko wartości liczbowe, ponieważ nie ma możliwości zastosowania do wartości tekstowych
Tutaj mamy cenę wartości zgodnie z wysokością i szerokością produktu. Musimy znaleźć cenę dla określonej wysokości (34) i szerokości (21) jak pokazano na poniższym zrzucie ekranu.
Wartość Height1 musi być zgodna z tablicą Row_header, a wartość Width2 musi być zgodna z tablicą Column_header.
Użyj formuły w komórce K6:
| = INDEKS ( dane , PODAJ.POZYCJĘ ( K4, Wysokość , 1 , PODAJ.POZYCJĘ ( K5 , Szerokość ) ) ) |
Wyjaśnienie:
- Funkcja PODAJ.POZYCJĘ dopasowuje wartość wysokości w komórce K4 do tablicy nagłówka wiersza i zwraca jej pozycję 3 jako liczba.
- Funkcja PODAJ.POZYCJĘ dopasowuje wartość Szerokość w komórce K5 do tablicy nagłówków kolumn i zwraca jej pozycję 2 jako liczba.
- Funkcja INDEX pobiera numer indeksu wiersza i kolumny, wyszukuje dane w tabeli i zwraca pasującą wartość.
- Argument typu MATCH jest ustalony na 1. Ponieważ formuła wyodrębni przybliżone dopasowanie.
Tutaj wartości formuły są podane jako odwołania do komórek, a wiersz_nagłówek, dane i nagłówek_kolumny podane jako nazwane zakresy, jak wspomniano w powyższej migawce.
Możesz także wykonać wyszukiwanie dokładnych dopasowań za pomocą funkcji INDEKS i PODAJ.POZYCJĘ w programie Excel. Dowiedz się więcej o tym, jak wykonywać wyszukiwanie z uwzględnieniem wielkości liter za pomocą funkcji INDEKS I PODAJ.POZYCJĘ w programie Excel. Możesz także wyszukać częściowe dopasowania za pomocą symboli wieloznacznych w programie Excel.
Jak widać na powyższym zrzucie, otrzymaliśmy Cenę uzyskaną według wzrostu (34) & Szerokość (21) jak 53.10. I to dowodzi, że formuła działa dobrze, a w przypadku wątpliwości zapoznaj się z poniższymi uwagami, aby uzyskać więcej zrozumienia.
Uwagi:
- Funkcja zwraca błąd #NA, jeśli argumentem tablicy wyszukiwania funkcji PODAJ.POZYCJĘ jest tablica 2D, która jest polem nagłówka danych…
- Funkcja dopasowuje dokładną wartość, ponieważ argument typu dopasowania do funkcji PODAJ.POZYCJĘ wynosi 0.
- Wartości wyszukiwania można podać jako odwołanie do komórki lub bezpośrednio za pomocą symbolu cudzysłowu ( " ) w formule jako argumentów.
Mam nadzieję, że ten artykuł o tym, jak używać funkcji INDEKS i DOPASUJ do wyszukiwania wartości w programie Excel, jest objaśniający. Więcej artykułów na temat obliczania wartości i powiązanych formuł programu Excel znajdziesz tutaj. Jeśli podobały Ci się nasze blogi, podziel się nimi ze znajomymi na Facebooku. A także możesz śledzić nas na Twitterze i Facebooku. Chcielibyśmy usłyszeć od Ciebie, daj nam znać, jak możemy ulepszyć, uzupełnić lub unowocześnić naszą pracę i uczynić ją lepszą dla Ciebie. Napisz do nas na e-mail.
Użyj INDEKSU i DOPASUJ, aby wyszukać wartość : Formuła INDEKS-POZYCJA służy do dynamicznego i precyzyjnego wyszukiwania wartości w danej tabeli. Jest to alternatywa dla funkcji WYSZUKAJ.PIONOWO i eliminuje wady funkcji WYSZUKAJ.PIONOWO.
Użyj funkcji WYSZUKAJ.PIONOWO z co najmniej dwóch tabel przeglądowych : Aby wyszukać z wielu tabel, możemy zastosować podejście IFERROR. Aby wyszukać z wielu tabel, przyjmuje błąd jako przełącznik do następnej tabeli. Inną metodą może być podejście Jeśli.
Jak wykonać wyszukiwanie z uwzględnieniem wielkości liter w programie Excel? : Funkcja WYSZUKAJ.PIONOWO programu Excel nie rozróżnia wielkości liter i zwróci pierwszą dopasowaną wartość z listy. INDEX-MATCH nie jest wyjątkiem, ale można go zmodyfikować, aby rozróżniać wielkość liter. Zobaczmy, jak…
Wyszukaj często pojawiający się tekst z kryteriami w programie Excel : Wyszukiwanie najczęściej pojawia się w tekście w zakresie, w którym używamy funkcji INDEX-MATCH z funkcją MODE. Oto metoda.
Popularne artykuły :
Jak korzystać z funkcji IF w programie Excel? : Instrukcja IF w programie Excel sprawdza warunek i zwraca określoną wartość, jeśli warunek jest PRAWDA, lub zwraca inną określoną wartość, jeśli jest FAŁSZ.
Jak korzystać z funkcji WYSZUKAJ.PIONOWO w programie Excel : Jest to jedna z najczęściej używanych i popularnych funkcji programu Excel, która służy do wyszukiwania wartości z różnych zakresów i arkuszy.
Jak korzystać z funkcji SUMA.JEŻELI w programie Excel : To kolejna ważna funkcja deski rozdzielczej. Pomaga to zsumować wartości w określonych warunkach.
Jak korzystać z funkcji LICZ.JEŻELI w programie Excel : Policz wartości z warunkami za pomocą tej niesamowitej funkcji. Nie musisz filtrować danych, aby zliczyć określone wartości. Funkcja Countif jest niezbędna do przygotowania deski rozdzielczej.