Обща формула
=INDEX(rng1,MODE(IF(rng2=criteria,MATCH(rng1,rng1,0))))
Обобщение
За да намерите най-често срещания текст в диапазон въз основа на критерии, които предоставяте, можете да използвате формула за масив, базирана на няколко функции на Excel INDEX, MATCH, MODE и IF. В показания пример формулата в G5 е:
=INDEX(supplier,MODE(IF(client=F5,MATCH(supplier,supplier,0))))
където "доставчик" е наименованият диапазон C5: C15, а "клиент" е наименованият диапазон B5: B15.
Забележка: това е формула на масив и трябва да се въведе с control + shift + enter.
Обяснение
Работейки отвътре навън, използваме функцията MATCH, за да съпоставим текстовия диапазон срещу себе си, като даваме MATCH същия диапазон за справочна стойност и масив за търсене, с нула за тип на съвпадението:
MATCH(supplier,supplier,0)
Тъй като справочната стойност е масив с 10 стойности, MATCH връща масив от 10 резултата:
(1;1;3;3;5;1;7;3;1;5;5)
Всеки елемент в този масив представлява първата позиция, при която името на доставчика се появява в данните. Този масив се подава във функцията IF, която се използва за филтриране на резултатите само за клиент А:
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)
Забележете, че в масива остават само позиции, свързани с клиент А. MODE игнорира FALSE стойности и връща най-често срещаното число на функцията INDEX като номер на реда:
=INDEX(supplier,1)
И накрая, с посочения диапазон "доставчик" като масив, INDEX връща "Браун", най-често срещаният доставчик за клиент А.
