Формула Excel: найти наиболее часто встречающийся текст с учётом критерия

Иногда возникает необходимость найти текст, встречающийся наиболее часто по определённому критерию в Excel. В этом руководстве представлена формула массива для решения такой задачи, а также подробно разобраны её аргументы.

Универсальная формула:

Аргументы

Возвращаемое значение

Эта формула возвращает наиболее часто встречающийся текст, соответствующий заданному критерию.

Как работает эта формула

Пример: у вас есть диапазон «Список ячеек продуктов, инструментов и пользователей». Чтобы найти самый часто используемый инструмент для каждого продукта, введите приведённую ниже формулу в ячейку G3:

Нажмите одновременно клавиши Shift + Ctrl + Enter, чтобы получить корректный результат, а затем протяните маркер заполнения вниз для применения этой формулы.

Пояснение

MATCH($C$3:$C$12,$C$3:$C$12,0): функция ПОИСКПОЗ возвращает позицию искомого значения в строке или столбце. В данном случае формула выдаёт массивный результат {1;2;3;4;2;1;7;8;9;7}, указывающий позицию каждого элемента в диапазоне $C$3:$C$12.

IF($B$3:$B$12=F3,MATCH($C$3:$C$12,$C$3:$C$12,0)): функция ЕСЛИ используется для задания условия. В данном случае формула интерпретируется как IF($B$3:$B$12=”KTE”,{1;2;3;4;2;1;7;8;9;7}), а массивный результат возвращает {1;FALSE;3;FALSE;FALSE;1;FALSE;FALSE;9;FALSE}.

MODE(IF($B$3:$B$12=F3,MATCH($C$3:$C$12,$C$3:$C$12,0))): функция МОДА определяет наиболее часто встречающееся значение в диапазоне. В данном случае формула ищет самое повторяющееся число среди результатов, возвращаемых функцией ЕСЛИ, что можно представить как MODE({1;FALSE;3;FALSE;FALSE;1;FALSE;FALSE;9;FALSE}) — и возвращает 1.

INDEX function: функция ИНДЕКС возвращает значение из таблицы или массива по указанной позиции. В данном случае формула INDEX($C$3:$C$12,MODE(IF($B$3:$B$12=F3,MATCH($C$3:$C$12,$C$3:$C$12,0)))) будет упрощена до INDEX($C$3:$C$12,1).

Примечание

Если существует два или более текста с одинаковой максимальной частотой, формула вернёт то значение, которое встречается первым.

Пример файла

Нажмите, чтобы скачать пример файла

Связанные формулы

• Проверка, содержит ли ячейка определённый текст
  Чтобы проверить, содержит ли ячейка хотя бы одно текстовое значение из диапазона A и при этом не содержит ни одного значения из диапазона B, используйте формулу массива, сочетающую функции СЧЁТ, ПОИСК и И в Excel.
• Проверка, содержит ли ячейка одно из нескольких значений, исключая другие
  В этом руководстве представлена формула для быстрой проверки: содержит ли ячейка хотя бы одно из заданных значений, но при этом не содержит других. Также подробно разобраны аргументы этой формулы.
• Проверка, содержит ли ячейка одно из значений
  Допустим, в Excel у вас есть список значений в столбце E. Нужно проверить, содержат ли ячейки в столбце B хотя бы одно из значений из столбца E, и вернуть ИСТИНА или ЛОЖЬ.
• Проверка, содержит ли ячейка число
  Иногда возникает необходимость проверить, содержит ли ячейка числовое значение. В этом руководстве представлена формула, которая возвращает ИСТИНА, если ячейка содержит число, и ЛОЖЬ — если нет.