Formuła ogólna
(=INDEX(range1,MATCH(1,(A1=range2)*(B1=range3)*(C1=range4),0)))
Podsumowanie
Aby wyszukać wartości za pomocą INDEKSU i PODAJ.POZYCJĘ, używając wielu kryteriów, możesz użyć formuły tablicowej. W pokazanym przykładzie formuła w H8 to:
(=INDEX(E5:E11,MATCH(1,(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11),0)))
Uwaga: jest to formuła tablicowa i należy ją wprowadzić za pomocą Ctrl + Shift + Enter, z wyjątkiem Excel 365.
Wyjaśnienie
To bardziej zaawansowana formuła. Aby uzyskać podstawowe informacje, zobacz Jak używać indeksu i dopasowania.
Zwykle formuła INDEX MATCH jest skonfigurowana z ustawieniem PODAJ.POZYCJĘ, aby przeglądać zakres z jedną kolumną i zapewniać dopasowanie na podstawie podanych kryteriów. Bez konkatenacji wartości w kolumnie pomocniczej lub w samej formule nie ma możliwości podania więcej niż jednego kryterium.
Ta formuła pozwala obejść to ograniczenie, używając logiki boolowskiej do tworzenia tablicy jedynek i zer do reprezentowania wierszy pasujących do wszystkich trzech kryteriów, a następnie używając funkcji PODAJ.POZYCJĘ, aby dopasować pierwsze znalezione 1. Tymczasowa tablica jedynek i zer jest generowana za pomocą tego fragmentu:
(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11)
Tutaj porównujemy produkt w H5 ze wszystkimi artykułami, rozmiar w H6 ze wszystkimi rozmiarami, a kolor w H7 ze wszystkimi kolorami. Początkowy wynik to trzy tablice wyników TRUE / FALSE w następujący sposób:
(TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE)*(FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE)*(TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Wskazówka: użyj F9, aby zobaczyć te wyniki. Po prostu wybierz wyrażenie na pasku formuły i naciśnij klawisz F9.
Operacja matematyczna (mnożenie) przekształca wartości TRUE FALSE na 1 i 0:
(1;1;1;0;0;0;1)*(0;0;1;0;0;1;0)*(1;0;1;0;0;0;1)
Po pomnożeniu mamy pojedynczą tablicę taką jak ta:
(0;0;1;0;0;0;0)
który jest przekazywany do funkcji PODAJ.POZYCJĘ jako tablica przeglądowa, z wartością wyszukiwania 1:
MATCH(1,(0;0;1;0;0;0;0))
W tym momencie formuła jest standardową formułą INDEX MATCH. Funkcja MATCH zwraca 3 do INDEX:
=INDEX(E5:E11,3)
a INDEX zwraca wynik końcowy w wysokości 17,00 USD.
Wizualizacja tablicowa
Tablice wyjaśnione powyżej mogą być trudne do wizualizacji. Poniższy obrazek przedstawia podstawową ideę. Kolumny B, C i D odpowiadają danym w przykładzie. Kolumna F jest tworzona przez pomnożenie trzech kolumn razem. Jest to tablica przekazana do MATCH.
Wersja bez macierzy
Możliwe jest dodanie kolejnego INDEKSU do tej formuły, unikając konieczności wprowadzania jako formuły tablicowej za pomocą Ctrl + Shift + Enter:
=INDEX(rng1,MATCH(1,INDEX((A1=rng2)*(B1=rng3)*(C1=rng4),0,1),0))
Funkcja INDEKS może obsługiwać tablice natywnie, więc drugi INDEKS jest dodawany tylko po to, aby „złapać” tablicę utworzoną za pomocą operacji logiki logicznej i ponownie zwrócić tę samą tablicę do funkcji PODAJ.POZYCJĘ. Aby to zrobić, INDEX jest skonfigurowany z zerowymi wierszami i jedną kolumną. Trik zerowy powoduje, że INDEX zwraca kolumnę 1 z tablicy (która i tak jest już jedną kolumną).
Dlaczego miałbyś chcieć wersję bez macierzy? Czasami ludzie zapominają wprowadzić formułę tablicową za pomocą Ctrl + Shift + Enter, a formuła zwraca niepoprawny wynik. Tak więc formuła inna niż tablicowa jest bardziej „kuloodporna”. Jednak kompromis jest bardziej złożoną formułą.
Uwaga: W programie Excel 365 nie jest konieczne wprowadzanie formuł tablicowych w specjalny sposób.
