Перейти к содержимому

Kutools для Office — один пакет. Пять инструментов. Выполняйте больше.

Как найти 5 или n самых часто встречающихся текстов или чисел в Excel?

Author Xiaoyang Last modified

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


Поиск и извлечение трех или n наиболее часто встречающихся текстов или чисел с помощью формул

Вы можете выполнить эту задачу, используя приведенные ниже формулы, сделайте следующее:

Формула 1: Обычная формула + Массивная формула

1. Пожалуйста, примените следующую формулу, чтобы получить значение, которое встречается чаще всего:

=ЕСЛИОШИБКА(ИНДЕКС($A$2:$A$18;МОДА(MATCH($A$2:$A$18;$A$2:$A$18;0)+{0;0});""))

Примечания:

1. В этой формуле A2:A18 — это список данных, из которого вы хотите найти значения.

2. Если вы используете версии Excel до Microsoft 365, вы должны нажать Ctrl + Shift + Enter, чтобы получить результат. Для пользователей Microsoft 365 и более новых версий Excel вы можете просто ввести формулу в ячейку результата, а затем нажать клавишу Enter .

A screenshot of the array formula result for extracting the most frequent value

2. Затем вы должны использовать следующую массивную формулу, чтобы получить второе по частоте значение, введите эту формулу под первой формулой, а затем нажмите клавиши Ctrl + Shift + Enter вместе, чтобы получить правильный результат, см. скриншот:

=ЕСЛИОШИБКА(ИНДЕКС(A$2:A$18;МОДА(ЕСЛИ(СЧЁТЕСЛИ(C$2:C2;A$2:A$18)=0;MATCH(A$2:A$18;A$2:A$18;0)+{0;0})));"")
Примечание: В этой формуле A2:A18 — это список данных, из которого вы хотите найти значения, C2 — это ячейка формулы самого часто встречающегося значения, которое вы только что извлекли.

A screenshot of the array formula used to extract the second most frequent value in Excel

3. После извлечения второго по частоте значения перетащите эту ячейку формулы вниз, чтобы получить другие n значений по мере необходимости. См. скриншот:

A screenshot showing how to drag the formula down to extract other frequent values in Excel

4. Наконец, вы можете использовать следующую формулу, чтобы получить количество появлений извлеченного текста, см. скриншот:

=СЧЁТЕСЛИ($A$2:$A$18; C2)

A screenshot of the formula used to count the occurrences of a text in Excel


Формула 2: Массивная формула

На самом деле, вы также можете использовать одну массивную формулу, чтобы получить результат, сделайте следующее:

1. Введите или скопируйте приведенную ниже формулу в пустую ячейку, где вы хотите получить результат, затем нажмите клавиши Ctrl + Shift + Enter вместе, чтобы получить первый результат, см. скриншот:

=ИНДЕКС($A$2:$A$18;СОВПАД(МИН(МОДА(ЕСЛИ(НЕ(СЧЁТЕСЛИ(C$1:C1;$A$2:$A$18));(СЧЁТЕСЛИ($A$2:$A$18;"<"& $A$2:$A$18)+1)*{1;1})));СЧЁТЕСЛИ($A$2:$A$18;"<"& $A$2:$A$18)+{1};0))
Примечание: В формуле A2:A18 — это список данных, из которого вы хотите найти значения, C1 — это ячейка над вашей формулой.

A screenshot of the array formula for extracting the top frequent values

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

A screenshot of the array formula result showing the top frequent values in Excel

3. Также вы можете получить количество появлений извлеченного текста с помощью следующей формулы:

=СЧЁТЕСЛИ($A$2:$A$18; C2)

A screenshot of the PivotTable option in Excel to extract frequent values


Поиск и извлечение трех или n наиболее часто встречающихся текстов или чисел с помощью сводной таблицы

Если вы знакомы со сводными таблицами, вы можете быстро получить таблицу, которая упорядочивает данные от наиболее часто встречающихся до наименее часто встречающихся. Выполните следующие шаги:

1. Выберите список данных, который вы хотите использовать, затем нажмите Вставка > Сводная таблица, см. скриншот:

A screenshot of the PivotTable option in Excel ribbon

2. В появившемся диалоговом окне Создание сводной таблицы выберите место, куда вы хотите поместить сводную таблицу, в разделе Выберите, где разместить отчет сводной таблицы, см. скриншот:

A screenshot of the 'Create PivotTable' dialog box in Excel

3. Затем нажмите кнопку ОК, отобразится панель Полей сводной таблицы. Перетащите имя столбца в области Строки и Значения по отдельности, и вы получите сводную таблицу, как показано на скриншоте ниже:

A screenshot of the PivotTable fields pane showing values for creating a PivotTable

4. Щелкните любую ячейку в столбце чисел, затем щелкните правой кнопкой мыши и выберите Сортировка > Сортировка от наибольшего к наименьшему, см. скриншот:

A screenshot showing how to sort PivotTable results from largest to smallest

5. И затем количество значений в сводной таблице будет отсортировано от наибольшего к наименьшему, и вы сможете сразу увидеть три наиболее часто встречающихся текста, см. скриншот:

A screenshot of the sorted PivotTable results showing the top frequent values in Excel


Больше связанных статей:

  • Найти общие значения в 3 столбцах
  • В общем случае вам может понадобиться найти и выбрать одинаковые значения между двумя столбцами в Excel, но пробовали ли вы когда-нибудь найти общие значения среди трех столбцов, что означает, что значения существуют одновременно в трех столбцах, как показано на следующем скриншоте. В этой статье я представлю некоторые методы для выполнения этой задачи в Excel.
  • Найти наиболее часто встречающийся текст с условиями
  • Эта статья расскажет о том, как найти наиболее часто встречающийся текст на основе определенных условий из диапазона ячеек в Excel. В то же время я также представлю формулу для извлечения наиболее часто встречающегося текста в столбце.
  • Сортировка данных по наиболее часто встречающемуся значению
  • Предположим, у вас есть длинный список данных на вашем листе, и теперь вы хотите отсортировать этот список по частоте, с которой каждое слово встречается. То есть самое распространенное значение (например, встречается четыре раза в столбце) должно быть указано первым, за ним следуют слова, которые встречаются три раза, два раза и один раз, как показано на следующих скриншотах. Как можно решить эту задачу в Excel?
  • Найти наименее или наиболее часто встречающееся / частое значение
  • Когда вы работаете в Excel, иногда вам может понадобиться найти наименее часто встречающееся или частое значение в столбце или строке рабочего листа. Кроме того, существует формула, которая комбинирует функции Индекс, Совпадение, Мин и Счётесли, которая может быстро вернуть наименее часто встречающееся значение.

Лучшие инструменты для повышения продуктивности в Office

🤖 Kutools AI Aide: Совершенно новый подход к анализу данных благодаря: Интеллектуальное выполнение |  Генерация кода  |  Создание пользовательских формул |  Анализ данных и построение диаграмм  |  Вызов Kutools Functions
Популярные функции: Поиск, выделение или отметка дубликатов | Удалить пустые строки | Объединить столбцы или адреса без потери данных | Округлить ...
Супер ПОИСК: VLOOKUP по нескольким критериям | VLOOKUP по нескольким значениям | Многолистовой поиск | Распознавание нечетких соответствий ...
Расширенный раскрывающийся список: Быстро создать раскрывающийся список | Зависимый раскрывающийся список | Множественный выбор в раскрывающемся списке ...
Менеджер столбцов: Добавить определённое количество столбцов | Переместить столбцы | Переключить видимость скрытых столбцов | Сравнить диапазоны и столбцы ...
Рекомендуемые функции: Сетка фокусировки | Дизайн листа | Улучшенная строка формулы | Управление книгой и листами | Библиотека автотекста | Выбор даты | Объединить данные | Зашифровать/расшифровать ячейки | Отправить письмо по списку | Супер фильтр | Специальный фильтр (фильтр жирный/курсив/зачеркнутый...) ...
Топ15 наборов инструментов:12 текстовых инструментов (Добавить текст, Удалить определенные символы, ...) |50+ типов диаграмм (Диаграмма Ганта, ...) |40+ полезных формул (Расчет возраста на основе даты рождения, ...) |19 инструментов для вставки (Вставить QR-код, Вставить изображение по пути, ...) |12 инструментов преобразования (Преобразовать в слова, Конвертация валюты, ...) |7 инструментов объединения и разделения (Расширенное объединение строк, Разделить ячейки, ...) | ... и многое другое
Используйте Kutools на предпочитаемом вами языке — поддерживает Английский, Испанский, Немецкий, Французский, Китайский и более40 других языков!

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


Office Tab добавляет вкладки в Office и делает вашу работу намного проще

  • Включите режим вкладок для редактирования и чтения в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
  • Открывайте и создавайте несколько документов во вкладках одного окна вместо новых отдельных окон.
  • Увеличьте свою продуктивность на50% и уменьшите количество щелчков мышью на сотни ежедневно!

Все надстройки Kutools. Один установщик

Пакет Kutools for Office включает надстройки для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro — идеально для команд, работающих в разных приложениях Office.

Excel Word Outlook Tabs PowerPoint
  • Комплексный набор — надстройки для Excel, Word, Outlook и PowerPoint плюс Office Tab Pro
  • Один установщик, одна лицензия — настройка занимает считанные минуты (MSI-совместимо)
  • Совместная работа — максимальная эффективность между приложениями Office
  • 30-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
  • Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек