Обща формула
(=INDEX(rng1,MATCH(1,MMULT(--(rng2=critera),TRANSPOSE(COLUMN(rng2)^0)),0)))
Обобщение
За да търсите стойност чрез съвпадение в множество колони, можете да използвате формула на масив, базирана на MMULT, TRANSPOSE, COLUMN и INDEX. В показания пример формулата в H4 е:
(=INDEX(groups,MATCH(1,MMULT(--(names=G4),TRANSPOSE(COLUMN(names)^0)),0)))
където "names" е наименуваният диапазон C4: E7, а "groups" е наименуваният диапазон B4: B7. Формулата връща групата, към която принадлежи всяко име.
Забележка: това е формула на масив и трябва да се въведе с контрола shift enter.
Обяснение
Работейки отвътре навън, логическите критерии, използвани в тази формула, са:
--(names=G4)
където names е наименуваният диапазон C4: E7. Това генерира TRUE / FALSE резултат за всяка стойност в данните и двойното отрицателно принуждава TRUE FALSE стойности на 1 и 0, за да се получи масив като този:
(0,0,0;1,0,0;0,0,0;0,0,0)
Този масив е 4 реда по 3 колони, съответстващи на структурата на "имена".
Създава се втори масив с този израз:
TRANSPOSE(COLUMN(names)^0))
Функцията COLUMN се използва за създаване на числов масив с 3 колони и 1 ред, а TRANSPOSE преобразува този масив в 1 колона и 3 реда. Увеличаването до степен на нула просто преобразува всички числа в масива в 1. След това функцията MMULT се използва за извършване на матрично умножение:
MMULT((0,0,0;1,0,0;0,0,0;0,0,0),(1;1;1))
и полученото отива във функцията MATCH като масив, като 1 като търсеща стойност:
MATCH(1,(0;1;0;0),0)
Функцията MATCH връща позицията на първото съвпадение, което съответства на реда на първия съвпадащ ред, отговарящ на предоставените критерии. Това се подава в INDEX като номер на реда, като масивът е наречен "групи" от диапазона:
=INDEX(groups,2)
И накрая, INDEX връща "Bear", към която принадлежи Адам.
Буквално съдържа критерии
За да проверите за конкретни текстови стойности вместо точно съвпадение, можете да използвате функциите ISNUMBER и SEARCH заедно. Например, за да съчетаете клетки, които съдържат „ябълка“, можете да използвате:
=ISNUMBER(SEARCH("apple",data))
Тази формула е обяснена тук.
