Общая формула
=INDEX(rng1,MODE(IF(rng2=criteria,MATCH(rng1,rng1,0))))
Резюме
Чтобы найти наиболее часто встречающийся текст в диапазоне на основе заданных вами критериев, вы можете использовать формулу массива, основанную на нескольких функциях Excel: ИНДЕКС, ПОИСКПОЗ, РЕЖИМ и ЕСЛИ. В показанном примере формула в G5:
=INDEX(supplier,MODE(IF(client=F5,MATCH(supplier,supplier,0))))
где «поставщик» - именованный диапазон C5: C15, а «клиент» - именованный диапазон B5: B15.
Примечание: это формула массива, и ее нужно вводить с помощью Ctrl + Shift + Enter.
Объяснение
Работая изнутри наружу, мы используем функцию ПОИСКПОЗ для сопоставления текстового диапазона с самим собой, давая ПОИСКПОЗ одинаковый диапазон для значения поиска и массива поиска с нулем для типа соответствия:
MATCH(supplier,supplier,0)
Поскольку значение поиска представляет собой массив с 10 значениями, ПОИСКПОЗ возвращает массив из 10 результатов:
(1;1;3;3;5;1;7;3;1;5;5)
Каждый элемент в этом массиве представляет собой первую позицию, в которой имя поставщика появляется в данных. Этот массив передается в функцию IF, которая используется для фильтрации результатов только для клиента A:
IF(client=F5,(1;1;3;3;5;1;7;3;1;5;5))
IF возвращает отфильтрованный массив функции MODE:
(1;FALSE;3;FALSE;5;1;FALSE;FALSE;1;5;FALSE)
Обратите внимание, что в массиве остаются только позиции, связанные с клиентом A. MODE игнорирует значения FALSE и возвращает наиболее часто встречающееся число в функцию INDEX в качестве номера строки:
=INDEX(supplier,1)
Наконец, с именованным диапазоном «поставщик» в качестве массива ИНДЕКС возвращает «коричневый», наиболее часто встречающийся поставщик для клиента А.
