W tym artykule dowiemy się, jak wyszukiwać wartości w tabeli 2d za pomocą funkcji INDEX-MATCH-MATCH w programie Excel.
Scenariusz:
Załóżmy, że musisz wykonać wiele wyszukiwań z tabeli zawierającej setki kolumn. W takich przypadkach użycie różnych formuł dla każdego wyszukiwania zajmie zbyt dużo czasu. Co powiesz na utworzenie dynamicznej formuły wyszukiwania, którą można wyszukać według podanego nagłówka. Tak, możemy to zrobić. Ta formuła nosi nazwę INDEKS PODAJ.POZYCJĘ lub powiedz formułę wyszukiwania 2D.
Jak rozwiązać problem?
Aby formuła zrozumiała, najpierw musimy trochę zrewidować następujące funkcje
- funkcja INDEKS
- Funkcja PODAJ.POZYCJĘ
Funkcja INDEX zwraca wartość pod danym indeksem w tablicy.
Funkcja MATCH zwraca indeks pierwszego wystąpienia wartości w tablicy (tablica jednowymiarowa).
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 PODAJ.POZYCJĘ zwróci indeks szukanej wartości2 w polu nagłówka kolumny. Numery indeksów zostaną teraz wprowadzone do funkcji INDEX, aby uzyskać wartości pod wartością wyszukiwania z danych tabeli 2D.
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ą podane 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 .
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. Przybliżone dopasowanie 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ść, 1 )) )
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.
Jak widać na powyższym zrzucie, mamy Cenę uzyskaną według wzrostu (34) & Szerokość (21) jak 53.10. To dowodzi, że formuła działa dobrze, a w przypadku wątpliwości zapoznaj się z uwagami poniżej, 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 zrozumiałeś, jak korzystać z funkcji Lookup w tabeli 2 D za pomocą funkcji INDEKS I DOPASUJ w programie Excel. Przeglądaj więcej artykułów w wartości wyszukiwania programu Excel tutaj. Zachęcamy do składania zapytań poniżej w polu komentarza. Z pewnością Ci pomożemy.
Użyj INDEKSU i DOPASUJ, aby wyszukać wartość : Funkcja INDEX & MATCH do wyszukiwania wartości zgodnie z wymaganiami.
Zakres SUMA z INDEKSEM w Excelu : Użyj funkcji INDEX, aby znaleźć SUMA wartości zgodnie z wymaganiami.
Jak korzystać z funkcji SUMA w programie Excel : Znajdź SUMA liczb za pomocą funkcji SUMA wyjaśnionej na przykładzie.
Jak korzystać z funkcji INDEKS w programie Excel? : Znajdź indeks tablicy za pomocą funkcji INDEX wyjaśnionej na przykładzie.
Jak korzystać z funkcji PODAJ.POZYCJĘ w programie Excel : Znajdź pozycję PODAJ.POZYCJĘ w tablicy przy użyciu wartości INDEX wewnątrz funkcji PODAJ.POZYCJĘ wyjaśnionej na przykładzie.
Jak korzystać z funkcji WYSZUKAJ w programie Excel : Znajdź wartość wyszukiwania w tablicy za pomocą funkcji WYSZUKAJ wyjaśnionej na przykładzie.
Jak korzystać z funkcji WYSZUKAJ.PIONOWO w programie Excel : Znajdź wartość wyszukiwania w tablicy za pomocą funkcji WYSZUKAJ.PIONOWO wyjaśnionej na przykładzie.
Jak korzystać z funkcji WYSZUKAJ.POZIOMO w programie Excel : Znajdź wartość wyszukiwania w tablicy za pomocą funkcji WYSZUKAJ.POZIOMO wyjaśnionej na przykładzie.
popularne artykuły
50 skrótów Excela, aby zwiększyć produktywność
Edytuj listę rozwijaną
Absolutne odniesienie w Excelu
Jeśli z formatowaniem warunkowym
Jeśli z symbolami wieloznacznymi
Przeglądaj według daty
Dołącz imię i nazwisko w programie Excel
