Indeks dopasowuje wiersze i kolumny wielu kryteriów
Wszyscy codziennie używamy funkcji WYSZUKAJ.PIONOWO do pobierania danych, a także zdajemy sobie sprawę, że funkcja WYSZUKAJ.PIONOWO może pobierać dane od lewej do prawej, więc wartość wyszukiwania powinna zawsze znajdować się po lewej stronie kolumn wyników. Mamy jednak kilka alternatyw, których można użyć jako alternatywy dla funkcji WYSZUKAJ.PIONOWO w programie Excel. Dzięki zaawansowanej technologii możemy użyć tej formuły INDEKS + DOPASUJ, aby dopasować wiele kryteriów dla wierszy i kolumn. W tym specjalnym artykule szczegółowo omówimy tę technikę.
Jak używać formuły INDEX + MATCH, aby dopasować wiele kryteriów?
Tutaj wyjaśniamy, jak używać formuły indeks + dopasowanie, aby dopasować wiele kryteriów dla wierszy i kolumn z przykładami.
Możesz pobrać ten szablon Excel z dopasowaniem indeksu do wielu kryteriów tutaj - Szablon programu Excel z dopasowaniem indeksu do wielu kryteriówPrzykład # 1 - INDEKS + PODAJNIKA Formuła
Nie większość funkcji wyszukiwania użytkowników programu Excel poza funkcją WYSZUKAJ.PIONOWO może być tak wiele. W każdym razie, zanim przejdziemy do poziomu zaawansowanego, zróbmy proste wprowadzenie do tej formuły.
Na przykład spójrz na poniższą strukturę danych w programie Excel.
Mamy nazwy „przedstawicieli handlowych” i odpowiadające im wartości sprzedaży. Z drugiej strony mamy rozwijaną listę „Przedstawiciel handlowy” w komórce D2.
Na podstawie wyboru dokonanego z rozwijanej listy kwota sprzedaży musi pojawić się w komórce E2.
Problem polega na tym, że nie możemy zastosować formuły WYSZUKAJ.PIONOWO, ponieważ wartość wyszukiwania „Przedstawiciel handlowy” znajduje się po prawej stronie kolumny wyników „Sprzedaż”, więc w takich przypadkach możemy użyć formuły wartości wyszukiwania kombinacji INDEKS + PODAJ.
INDEKS szuka wspomnianej wartości numeru wiersza w zakresie A2: A11 iw tym zakresie musimy podać, z którego wiersza ma pochodzić wartość sprzedaży. Ta wartość wiersza jest oparta na nazwie „Przedstawiciel handlowy” wybranej z listy rozwijanej w programie Excel, więc funkcja PODAJ.POZYCJĘ szuka numeru wiersza „Przedstawiciel handlowy” z zakresu B2: B11 i zwraca numer wiersza dopasowanej wartości .
Przykład # 2 - Wiele kryteriów w INDEKS + Formuła dopasowania
Teraz mamy strukturę danych podobną do poniższej.
Mamy miesięczne wartości sprzedaży „Przedstawiciel handlowy”. Z tej tabeli potrzebujemy dynamicznych wyników, tak jak w komórce A15 Utworzyłem listę rozwijaną „Przedstawiciel handlowy”, aw komórce B14 utworzyłem listę rozwijaną „Miesiąc”.
Na podstawie wyboru dokonanego w tych dwóch komórkach nasza formuła musi pobrać dane z powyższej tabeli.
Na przykład, jeśli wybiorę „Rep 8” i „Kwi”, wtedy musi pokazać wartość sprzedaży „Rep 8” za miesiąc „Kwi”.
Dlatego w takich przypadkach musimy dopasować zarówno wiersze, jak i kolumny. Wykonaj poniższe czynności, aby zastosować formułę w celu dopasowania zarówno wierszy, jak i kolumn.
Krok 1: Otwórz funkcję INDEKS w komórce B15.
Krok 2: Pierwszym argumentem funkcji INDEKS jest „Tablica”, czyli z jakiego zakresu komórek potrzebujemy wyniku. W tym przypadku potrzebujemy wartości sprzedaży, więc wybierz zakres komórek od B2 do G11.
Krok 3: Kolejny argument funkcji INDEKS, z którego wiersza wybranego zakresu potrzebujemy wyniku. W takim przypadku musimy uzyskać numer wiersza „Przedstawiciel handlowy” na podstawie wyboru dokonanego w komórce rozwijanej komórki A15. Aby dynamicznie pobrać numer wiersza na podstawie otwartej funkcji MATCH.
Krok 4: WYSZUKAJ WARTOŚĆ funkcji PODAJ.POZYCJĘ to „Przedstawiciel handlowy”, więc wybierz komórkę A15 jako odniesienie.
Krok 5: Tablica wyszukiwania będzie w głównej tabeli nazwami przedstawicieli handlowych. Wybierz zakres od A2 do A11.
Krok 6: Typ dopasowania funkcji MATCH będzie dokładny, więc wprowadź zero jako wartość argumentu.
Krok 7: Kolejnym argumentem funkcji INDEKS jest „Numer kolumny” czyli z zaznaczonego zakresu komórek, z których kolumny potrzebujemy wyniku. Zależy to od miesiąca, który wybieramy z rozwijanej listy komórki B14. Aby uzyskać numer kolumny, automatycznie otwórz inną funkcję MATCH.
Krok 8: Ta wartość wyszukiwania czasu będzie nazwą miesiąca, więc wybierz komórkę B14 jako odniesienie.
Krok 9: Tablica odnośników będzie obejmowała miesięczny zakres komórek w tabeli głównej, tj. Od B1 do G1.
Krok 10: Ostatni argument to typ dopasowania, wybierz „Dokładne dopasowanie” jako kryterium. Zamknij dwa nawiasy i naciśnij klawisz Enter, aby uzyskać wynik.
Jak widać powyżej, wybraliśmy „Rep 6” i „Kwi” jako miesiąc, a nasza formuła zwróciła wartość sprzedaży z miesiąca „Kwi” dla „Rep 6”.
Uwaga: żółta komórka to odniesienie dla Ciebie.
Rzeczy do zapamiętania
- Połączenie INDEKS + PODAJ.POZYCJĘ może być potężniejsze niż formuła WYSZUKAJ.PIONOWO.
- INDEX & MATCH może dopasować zarówno nagłówki wierszy, jak i kolumn i zwrócić wynik ze środkowej tabeli.
- PODAJ.POZYCJĘ może zwrócić numer wiersza i numer kolumny nagłówków tabeli zarówno wierszy, jak i kolumn.
