Поиск и подсчет самых частых значений
Необходимость поиска наибольших и наименьших значений в любом бизнесе очевидна: самые прибыльные товары или ценные клиенты, самые крупные поставки или партии и т.д.
Но наравне с этим, иногда приходится искать в данных не топовые, а самые часто встречающиеся значения, что хоть и звучит похоже, но, по факту, совсем не то же самое. Применительно к магазину, например, это может быть поиск не самых прибыльных, а самых часто покупаемых товаров или самое часто встречающееся количество позиций в заказе, минут в разговоре и т.п.
В такой ситуации задачу придется решать немного по-разному, в зависимости от того, с чем мы имеем дело — с числами или с текстом.
Поиск самых часто встречающихся чисел
Предположим, перед нами стоит задача проанализировать имеющиеся данные по продажам в магазине, с целью определить наиболее часто встречающееся количество купленных товаров. Для определения самого часто встречающегося числа в диапазоне можно использовать функцию МОДА (MODE) :
Т.е., согласно нашей статистике, чаще всего покупатели приобретают 3 шт. товара.
Если существует не одно, а сразу несколько значений, встречающихся одинаково максимальное количество раз (несколько мод), то для их выявления можно использовать функцию МОДА.НСК (MODE.MULT) . Ее нужно вводить как формулу массива, т.е. выделить сразу несколько пустых ячеек, чтобы хватило на все моды с запасом и ввести в строку формул =МОДА.НСК(B2:B16) и нажать сочетание клавиш Ctrl+Shift+Enter.
На выходе мы получим список всех мод из наших данных:
Т.е., судя по нашим данным, часто берут не только по 3, но и по 16 шт. товаров. Обратите внимание, что в наших данных только две моды (3 и 16), поэтому остальные ячейки, выделенные «про запас», будут с ошибкой #Н/Д.
Частотный анализ по диапазонам функцией ЧАСТОТА
Если же нужно проанализировать не целые, а дробные числа, то правильнее будет оценивать не количество одинаковых значений, а попадание их в заданные диапазоны. Например, нам необходимо понять какой вес чаще всего бывает у покупаемых товаров, чтобы правильно выбрать для магазина тележки и упаковочные пакеты подходящего размера. Другими словами, нам нужно определить сколько чисел попадает в интервал 1..5 кг, сколько в интервал 5..10 кг и т.д.
Для решения подобной задачи можно воспользоваться функцией ЧАСТОТА (FREQUENCY) . Для нее нужно заранее подготовить ячейки с интересующими нас интервалами (карманами) и затем выделить пустой диапазон ячеек (G2:G5) по размеру на одну ячейку больший, чем диапазон карманов (F2:F4) и ввести ее как формулу массива, нажав в конце сочетание Ctrl+Shift+Enter:
Частотный анализ сводной таблицей с группировкой
Альтернативный вариант решения задачи: создать сводную таблицу, где поместить вес покупок в область строк, а количество покупателей в область значений, а потом применить группировку — щелкнуть правой кнопкой мыши по значениям весов и выбрать команду Группировать (Group) . В появившемся окне можно задать пределы и шаг группировки:
. и после нажатия на кнопку ОК получить таблицу с подсчетом количества попаданий покупателей в каждый диапазон группировки:
Минусы такого способа:
- шаг группировки может быть только постоянным, в отличие от функции ЧАСТОТА, где карманы можно задать абсолютно любые
- сводную таблицу нужно обновлять при изменении исходных данных (щелчком правой кнопки мыши — Обновить), а функция пересчитывается автоматически «на лету»
Поиск самого часто встречающегося текста
Если мы имеем дело не с числами, а с текстом, то подход к решению будет принципиально другой. Предположим, что у нас есть таблица из 100 строк с данными о проданных в магазине товарах, и нам нужно определить, какие товары покупались наиболее часто?
Самым простым и очевидным решением будет добавить рядом столбец с функцией СЧЁТЕСЛИ (COUNTIF) , чтобы подсчитать количество вхождений каждого товара в столбце А:
Затем, само-собой, отсортировать получившийся столбец по убыванию и посмотреть на первые строчки.
Или же добавить к исходному списку столбец с единичками и построить по получившейся таблице сводную, подсчитав суммарное количество единичек для каждого товара:
Если исходных данных не очень много и принципиально не хочется пользоваться сводными таблицами, то можно использовать формулу массива:
Как найти наиболее повторяющиеся значение в Excel
Допустим у нас есть таблица регистра составленных заказов клиентов. Необходимо узнать с какого города поступило наибольшее количество заказов, а с какого – наименьшее. Для решения данной задачи будем использовать формулу с поисковыми и вычислительными функциями.
Поиск наиболее повторяющегося значения в Excel
Чтобы наглядно продемонстрировать работу формулы для примера воспользуемся такой схематической таблицей регистра заказов от клиентов:
Теперь выполним простой анализ наиболее часто и редко повторяющихся значений таблицы в столбце «Город». Для этого:
- Сначала находим наиболее часто повторяющиеся названия городов. В ячейку E2 введите следующую формулу:
- Обязательно после ввода формулы нажмите комбинацию горячих клавиш CTRL+SHIFT+Enter, так как ее нужно выполнить в массиве.
- Для вычисления наиболее редко повторяющегося названия города вводим весьма похожую формулу:
Результат поиска названий самых популярных и самых редких городов клиентов в регистре заказов, отображен на рисунке:
Если таблица содержит одинаковое количество двух самых часто повторяемых городов или два самых редко повторяющихся города в одном и том же столбце, тогда будет отображаться первый из них.
Принцип действия поиска популярных по повторению значений:
Если посмотреть на синтаксис формул то можно легко заметить, что они отличаются только одним из названием функций: =МАКС() и =МИН(). Все остальные аргументы формулы – идентичны. Функция =СЧЕТЕСЛИ() подсчитывает, сколько раз каждое название города повторяется в диапазоне ячеек C2:C16. Таким образом в памяти создается условный массив значений.
Функция МАКС или МИН выбирает из условного массива наибольшее или наименьшее значение. Функция =ПОИСКПОЗ() возвращает номер позиции на которой в столбце C название города соответственного наибольшему или наименьшему количеству повторений. Полученное значение будет передано в качестве аргумента для функции =ИНДЕКС(), которая возвращает конечный результат в ячейку.
Как посчитать наиболее часто встречающееся значение в excel
Получение наиболее часто используемого текста из списка ячеек является обычной задачей для большинства из нас. В некоторых случаях вам также может понадобиться найти и извлечь 3 или n наиболее часто встречающихся текстов из списка, как показано ниже. Чтобы решить эту проблему, эта статья познакомит вас с некоторыми быстрыми приемами.
Найдите и извлеките первые 3 или n наиболее часто встречающихся текстов или чисел с помощью формул
Вы можете выполнить эту задачу, используя приведенные ниже формулы, сделайте следующее:
Формула 1: нормальная формула + формула массива
1. Примените приведенную ниже формулу, чтобы получить наиболее часто встречающееся значение:
Заметки:
1. В этой формуле A2: A18 — это список данных, из которого вы хотите найти значения.
2. Если вы используете версии Excel, предшествующие Microsoft 365, нажмите Shift + Ctrl + Enter чтобы получить результат. Для пользователей Microsoft 365 и более новых версий Excel вы можете просто ввести формулу в ячейку результата, а затем нажать Enter .
2. Затем вы должны использовать следующую формулу массива, чтобы получить второе наиболее частое значение, введите эту формулу под первой ячейкой формулы, а затем нажмите Shift + Ctrl + Enter вместе, чтобы получить правильный результат, см. снимок экрана:
3. После извлечения второго наиболее часто встречающегося значения перетащите эту ячейку формулы вниз, чтобы получить другие значения n по мере необходимости. Смотрите скриншот:
4. Наконец, вы можете использовать следующую формулу, чтобы получить количество вхождений извлекаемого текста, см. снимок экрана:
Формула 2: формула массива
Фактически, вы также можете использовать формулу одного массива, чтобы получить результат, сделайте следующее:
1. Введите или скопируйте приведенную ниже формулу в пустую ячейку, где вы хотите получить результат, затем нажмите Shift + Ctrl + Enter вместе, чтобы получить первый результат, см. снимок экрана:
2. Затем перетащите эту формулу вниз, чтобы получить другие значения. Если вы хотите получить первые 3 наиболее часто встречающихся значения, вам просто нужно перетащить вниз к двум другим ячейкам, см. снимок экрана:
3. Кроме того, вы также можете получить количество вхождений извлеченного текста по формуле ниже:
Найдите и извлеките первые 3 или n наиболее часто встречающихся текстов или чисел с помощью сводной таблицы.
Если вы знакомы со сводной таблицей, вы можете быстро получить таблицу, в которой данные расположены от наиболее часто встречающихся до наименее часто встречающихся. Пожалуйста, выполните следующие действия:
1. Выберите список данных, который вы хотите использовать, затем нажмите Вставить > PivotTable, см. снимок экрана:
2. В выскочившем Создать сводную таблицу диалоговом окне выберите место, куда вы хотите поместить сводную таблицу из Выберите, где вы хотите разместить отчет сводной таблицы раздел, см. снимок экрана:
3, Затем нажмите OK кнопка, а Поля сводной таблицы отображается панель. Перетащите имя столбца в Ряды и Ценности области отдельно, и вы получите сводную таблицу, как показано ниже:
4. Щелкните любую ячейку в числовом столбце, затем щелкните правой кнопкой мыши и выберите Сортировать > Сортировать от большего к меньшему, см. снимок экрана:
5. И затем количество значений в сводной таблице сортируется от большего к меньшему, и вы можете сразу увидеть 3 наиболее часто встречающихся текста, см. снимок экрана:
Больше относительных статей:
- Найдите общие значения в 3 столбцах
- В общем случае вам может потребоваться найти и выбрать одинаковые значения между двумя столбцами в Excel, но пытались ли вы когда-нибудь найти общие значения среди трех столбцов, что означает, что значения существуют в 3 столбцах одновременно, как показано на следующем снимке экрана . В этой статье я расскажу о некоторых методах выполнения этой задачи в Excel.
- Найдите самый частый текст с критериями
- В этой статье рассказывается о том, как найти наиболее часто встречающийся текст на основе определенных критериев из диапазона ячеек в Excel. В то же время я также представлю формулу для извлечения наиболее часто встречающегося текста в столбце.
- Сортировка данных по наиболее частому значению
- Предположим, у вас есть длинный список данных на вашем листе, и теперь вы хотите отсортировать этот список по частоте встречаемости каждого слова. То есть сначала указывается наиболее распространенное значение (например, четыре раза встречается в столбце), а затем следуют слова, которые встречаются три раза, дважды и один раз, как показано на следующих снимках экрана. Как бы вы могли решить эту задачу в Excel?
- Найдите наименьшее или наиболее распространенное/частое значение
- Когда вы работаете с книгой Excel, иногда вам может потребоваться найти наименее распространенное или частое значение в столбце или строке рабочего листа. За исключением поиска по одному вручную, существует формула, которая объединяет функции Index, Match, Min и Countif и может быстро возвращать наименее часто встречающееся значение.
Лучшие инструменты для офисной работы
Превратите часы в минуты с Kutools for Excel!
Готовы ускорить свои задачи в Excel? Используйте силу Kutools for Excel — ваш лучший инструмент для экономии времени. Оптимизируйте сложные задачи и скользите по своим данным, как профессионал. Испытайте Excel с молниеносной скоростью!
Зачем тебе Kutools for Excel
���� Более 300 мощных функций: Kutools содержит более 300 расширенных функций, упрощающих вашу работу в более чем 1500 сценариях.
�� Превосходная обработка данных: объединяйте ячейки, удаляйте дубликаты и выполняйте расширенные преобразования данных — и все это без особых усилий!
���� Эффективные пакетные операции: Зачем прилагать дополнительные усилия, если можно работать с умом? С легкостью импортируйте, экспортируйте, комбинируйте и корректируйте данные.
�� Настраиваемые диаграммы и отчеты: доступ к множеству дополнительных диаграмм и создание информативных отчетов, рассказывающих историю.
���� Мощная панель навигации: Получите преимущество благодаря надежному диспетчеру столбцов, диспетчеру рабочих листов и пользовательскому избранному.
. Семь типов раскрывающихся списков: Упростите ввод данных с помощью раскрывающихся списков различных функций и типов.
�� Удобный: простой инструмент для начинающих и мощный инструмент для экспертов.
Как подсчитать частоту текста в Excel
Вы можете использовать функцию СЧЁТЕСЛИ(диапазон, критерии) , чтобы подсчитать, как часто конкретный текст встречается в столбце Excel.
В следующих примерах показано, как использовать эту функцию на практике.
Пример 1: Подсчет частоты одного конкретного текста
Предположим, у нас есть следующий столбец в Excel, в котором показаны названия различных команд НБА:
Если мы хотим подсчитать, как часто название команды «Шершни» появляется в столбце, мы можем использовать следующую формулу:
На следующем снимке экрана показано, как использовать эту формулу на практике:
Мы видим, что «Шершни» появляются 3 раза.
Пример 2: Подсчет частоты нескольких текстов
Если мы хотим подсчитать частоту появления нескольких разных текстов, мы можем использовать функцию UNIQUE() , чтобы получить массив каждого уникального текста, который появляется в столбце:
Затем мы можем использовать функцию COUNTIF() , чтобы подсчитать, как часто встречается название каждой команды:
- Название команды «Маверикс» встречается 3 раза.
- Название команды «Шпоры» встречается 2 раза.
- Название команды «Лейкерс» встречается 3 раза.
Дополнительные ресурсы
Следующие учебные пособия предлагают дополнительную информацию о том, как подсчитывать частоты в Excel: