Formuła ogólna
(=INDEX(rng1,MATCH(1,MMULT(--(rng2=critera),TRANSPOSE(COLUMN(rng2)^0)),0)))
Podsumowanie
Aby wyszukać wartość przez dopasowanie w wielu kolumnach, możesz użyć formuły tablicowej opartej na MMULT, TRANSPOSE, COLUMN i INDEX. W pokazanym przykładzie formuła w H4 to:
(=INDEX(groups,MATCH(1,MMULT(--(names=G4),TRANSPOSE(COLUMN(names)^0)),0)))
gdzie „nazwy” to nazwany zakres C4: E7, a „grupy” to nazwany zakres B4: B7. Formuła zwraca grupę, do której należy każda nazwa.
Uwaga: jest to formuła tablicowa i należy ją wprowadzić za pomocą klawisza Control Shift Enter.
Wyjaśnienie
Działając od wewnątrz, logiczne kryteria użyte w tej formule to:
--(names=G4)
gdzie nazwy to nazwany zakres C4: E7. To generuje wynik PRAWDA / FAŁSZ dla każdej wartości w danych, a podwójna ujemna wartość przekształca wartości PRAWDA FAŁSZ na 1 i 0, aby uzyskać taką tablicę:
(0,0,0;1,0,0;0,0,0;0,0,0)
Ta tablica ma 4 wiersze na 3 kolumny, odpowiadające strukturze „nazw”.
Druga tablica jest tworzona za pomocą tego wyrażenia:
TRANSPOSE(COLUMN(names)^0))
Funkcja COLUMN służy do tworzenia tablicy liczbowej z 3 kolumnami i 1 wierszem, a TRANSPOSE konwertuje tę tablicę na 1 kolumnę i 3 wiersze. Podniesienie do potęgi zera po prostu przekształca wszystkie liczby w tablicy na 1. Następnie funkcja MMULT jest używana do wykonywania mnożenia macierzy:
MMULT((0,0,0;1,0,0;0,0,0;0,0,0),(1;1;1))
a wynik przechodzi do funkcji MATCH jako tablica, z 1 jako wartością wyszukiwania:
MATCH(1,(0;1;0;0),0)
Funkcja PODAJ.POZYCJĘ zwraca pozycję pierwszego dopasowania, która odpowiada wierszowi pierwszego zgodnego wiersza spełniającego podane kryteria. To jest wprowadzane do INDEKSU jako numer wiersza, z nazwanym zakresem „grupy” jako tablicą:
=INDEX(groups,2)
Wreszcie INDEKS zwraca „Niedźwiedź”, grupę, do której należy Adam.
Dosłowne zawiera kryteria
Aby sprawdzić określone wartości tekstowe zamiast dokładnego dopasowania, możesz użyć jednocześnie funkcji ISNUMBER i SEARCH. Na przykład, aby dopasować komórki zawierające słowo „jabłko”, możesz użyć:
=ISNUMBER(SEARCH("apple",data))
Ta formuła została wyjaśniona tutaj.
