Формула Excel: поиск наиболее часто встречающегося текста с критериями
В некоторых случаях вам может потребоваться найти наиболее часто встречающийся текст на основе критерия в Excel. В этом руководстве вводится формула массива для выполнения этой задачи и дается объяснение аргументов этой формулы.
Общая формула:
| =INDEX(rng_1,MODE(IF(rng_2=criteria,MATCH(rng_1,rng_1,0)))) |
аргументы
| Rng_1: the range of cells that you want to find the most frequent text. |
| Rng_2: the range of cells that contain the criteria you want to use. |
| Criteria: the condition you want to find text based on. |
Возвращаемое значение
Эта формула возвращает наиболее часто встречающийся текст с определенным критерием.
Как работает эта формула
Пример: есть ряд ячеек со списком продуктов, инструментов и пользователей, теперь вы хотите найти наиболее часто используемый инструмент для каждого продукта. Используйте приведенную ниже формулу в ячейке G3:
| =INDEX($C$3:$C$12,MODE(IF($B$3:$B$12=F3,MATCH($C$3:$C$12,$C$3:$C$12,0)))) |
Нажмите Shift + Ctrl + Ввод вместе, чтобы получить правильный результат. Затем перетащите маркер заполнения вниз, чтобы применить эту формулу.
объяснение
MATCH($C$3:$C$12,$C$3:$C$12,0): МАТЧ функция возвращает расположение lookup_value в строке или столбце. Здесь формула возвращает результат массива {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 функция используется для установки условия. Здесь эта формула видна как 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, и объясняет аргументы формулы. - Проверьте, содержит ли ячейка что-либо из
Предположим, что в Excel есть список значений в столбце E, вы хотите проверить, содержат ли ячейки в столбце B все значения в столбце E, и вернуть TRUE или FALSE. - Проверить, содержит ли ячейка номер
Иногда вам может потребоваться проверить, содержит ли ячейка числовые символы. В этом руководстве представлена формула, которая вернет ИСТИНА, если ячейка содержит число, и ЛОЖЬ, если ячейка не содержит числа.
Лучшие инструменты для работы в офисе
Kutools for Excel - Помогает вам выделиться из толпы
Kutools for Excel Имеет более 300 функций, Гарантия того, что то, что вам нужно, находится на расстоянии одного клика...
Office Tab - Включить чтение и редактирование с вкладками в Microsoft Office (включая Excel)
- Одна секунда для переключения между десятками открытых документов!
- Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
- Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
- Добавляет эффективные вкладки в Office (включая Excel), как в Chrome, Edge и Firefox.
