Как найти топ-5 или n наиболее часто встречающихся текстовых значений и чисел в Excel?
Для большинства из нас типичной задачей является поиск наиболее часто встречающегося текста в списке ячеек. В некоторых случаях может также понадобиться найти и извлечь топ-3 или N самых популярных текстовых значений из списка — как показано на снимке экрана ниже. В этой статье мы представим несколько быстрых приёмов для решения подобных задач.
Поиск и извлечение топ-3 или n наиболее часто встречающихся текстовых значений или чисел с помощью формул
Вы можете выполнить эту задачу с помощью приведённых ниже формул — просто следуйте инструкциям:
Формула 1: обычная формула + формула массива
1. Используйте приведённую ниже формулу, чтобы найти значение, встречающееся чаще всего:
Примечания:
1. В этой формуле A2:A18 — это список данных, из которого необходимо найти значения.
2. Если вы используете версии Excel, предшествующие Microsoft 365, нажмите комбинацию клавиш Ctrl + Shift + Enter, чтобы получить результат. Пользователям Microsoft 365 и более новых версий Excel достаточно ввести формулу в ячейку результата и нажать клавишу Enter.
2. Затем используйте следующую формулу массива, чтобы получить второе по частоте значение: введите эту формулу в ячейку под первой и нажмите клавиши Ctrl + Shift + Enter одновременно, чтобы получить правильный результат (см. снимок экрана):
3. После извлечения второго по частоте значения перетащите ячейку с формулой вниз, чтобы получить остальные n значений по мере необходимости (см. снимок экрана):
4. Наконец, вы можете воспользоваться следующей формулой, чтобы определить количество вхождений значения «Извлечь текст» (см. снимок экрана):
Формула 2: формула массива
На самом деле вы можете добиться того же результата с помощью всего одной формулы массива. Следуйте инструкциям:
1. Введите или скопируйте приведённую ниже формулу в пустую ячейку, где вы хотите получить результат, затем одновременно нажмите клавиши Ctrl + Shift + Enter, чтобы получить первый результат (см. снимок экрана):
2. Затем перетащите эту формулу вниз, чтобы получить остальные значения. Если вам нужны три самых частых значения, просто протяните формулу ещё на две ячейки вниз (см. снимок экрана):
3. Аналогично вы можете определить количество вхождений значения «Извлечь текст» с помощью приведённой ниже формулы:
Поиск и извлечение топ-3 или n наиболее часто встречающихся текстовых значений или чисел с помощью Сводная таблица
Если вы уже умеете работать со сводными таблицами, вы сможете быстро создать таблицу, в которой данные будут упорядочены от самых частых к самым редким. Выполните следующие действия:
1. Выделите список данных, который вы хотите использовать, затем нажмите Вставка > Сводная таблица (см. снимок экрана):
2. В появившемся диалоговом окне Создание сводной таблицы выберите место размещения сводной таблицы в разделе Выберите место для размещения отчёта сводной таблицы (см. снимок экрана):
3. Затем нажмите кнопку ОК, после чего отобразится область Поля сводной таблицы. Перетащите название столбца в области Строки и Значения по отдельности — и вы получите сводную таблицу, как показано на снимке экрана ниже:
4. Щёлкните любую ячейку в столбце с числами, щёлкните правой кнопкой мыши и выберите Сортировка > Сортировать от наибольшего к наименьшему (см. снимок экрана):
5. После этого значения в сводной таблице будут отсортированы от наибольшего к наименьшему, и вы сразу увидите топ-3 самых часто встречающихся текстов (см. снимок экрана):
Другие связанные статьи:
- Поиск общих значений в трёх столбцах
- В общем случае вам может понадобиться найти и выделить одинаковые значения в двух столбцах Excel, но пробовали ли вы когда-нибудь находить общие значения сразу в трёх столбцах — то есть такие, которые присутствуют одновременно во всех трёх, как показано на следующем снимке экрана? В этой статье я расскажу о нескольких способах выполнения этой задачи в Excel.
- Поиск наиболее часто встречающегося текста по заданным критериям
- В этой статье рассказывается, как найти наиболее часто встречающийся текст в диапазоне ячеек Excel на основе заданных критериев. Также приведена формула для извлечения самого распространённого текстового значения в столбце.
- Сортировка данных по частоте встречаемости значений
- Предположим, у вас на листе есть длинный список данных, и вы хотите отсортировать его по частоте встречаемости каждого слова: сначала — слово, которое встречается чаще всего (например, четыре раза в столбце), затем — слова, встречающиеся три, два и один раз, как показано на следующих снимках экрана. Как выполнить такую сортировку в Excel?
- Поиск наименее или наиболее часто встречающегося значения
- При работе с книгой Excel иногда возникает необходимость найти наименее часто встречающееся значение в столбце или строке листа. Вместо утомительного ручного перебора значений воспользуйтесь формулой, объединяющей функции ИНДЕКС, ПОИСКПОЗ, МИН и СЧЁТЕСЛИ, — она мгновенно вернёт нужный результат.
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал 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 и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек