Поиск и подсчет самых частых значений

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

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

В такой ситуации задачу придется решать немного по-разному, в зависимости от того, с чем мы имеем дело - с числами или с текстом.

Поиск самых часто встречающихся чисел

Предположим, перед нами стоит задача проанализировать имеющиеся данные по продажам в магазине, с целью определить наиболее часто встречающееся количество купленных товаров. Для определения самого часто встречающегося числа в диапазоне можно использовать функцию МОДА (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), чтобы подсчитать количество вхождений каждого товара в столбце А:

Подсчет количества текстовых значений в списке

Затем, само-собой, отсортировать получившийся столбец по убыванию и посмотреть на первые строчки.

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

Сводная таблица для подсчета количества вхождений каждого товара

Если исходных данных не очень много и принципиально не хочется пользоваться сводными таблицами, то можно использовать формулу массива:

Поиск самого частого значения формулой массива

Давайте разберем ее по кусочкам:

  • СЧЁТЕСЛИ(A2:A20;A2:A20) – формула массива, которая ищет по очереди количество вхождений каждого товара в диапазоне A2:A100 и выдаст на выходе массив с количеством повторений, т.е., фактически, заменяет собой дополнительный столбец
  • МАКС – находит в массиве вхождений самое большое число, т.е. товар, который покупали чаще всего
  • ПОИСКПОЗ – вычисляет порядковый номер строки в таблице, где МАКС нашла самое большое число
  • ИНДЕКС – выдает из таблицы содержимое ячейки с номером, который нашла ПОИСКПОЗ

Ссылки по теме



Не задан ID пользователя.

27.07.2016 19:36:11
Как переделать последний вариант для работы с закрытыми книгами? Вся беда в функции счётесли(), она не работает с закрытой книгой. есть у нее обходной путь?
Не задан ID пользователя.

20.09.2016 21:38:13
Дополнение к  МОДА.НСК()
когда ток одно максимально он все заполняет им, что бы обойти можно дополнить примерно таким способом
=ЕСЛИ(СЧЁТ(МОДА.НСК(B2:B16))=1;I2:I3;МОДА.НСК(B2:B16))
Не задан ID пользователя.

25.09.2016 15:19:21
Подскажите пожалуйста как мне вывести значение ячейки в столбце с тем что ячейка наприм. 10 по счету сверху вниз но сам список может изменяться фильтрами (т.е. считать надо только видимые ячейки). СМЕЩ фиксировано считает и на фильтры не реагирует. МОДА. тож. считает все без внимания на отфильтрованные результаты