Содержание:
Необходимость находить высшие и наименьшие ценности в любом бизнесе очевидна: самые прибыльные продукты или ценные клиенты, самые крупные поставки или поставки и так далее.
Но наряду с этим иногда приходится искать в данных не верхние, а наиболее часто встречающиеся значения, что хоть и звучит похоже, но на самом деле это совсем не одно и то же. Применительно к магазину, например, это может быть поиск наиболее часто покупаемых товаров, а не наиболее выгодных, или наиболее часто встречающегося количества позиций в заказе, минут в разговоре и т.п.
В такой ситуации задачу придется решать немного по-разному, в зависимости от того, с чем мы имеем дело – с числами или текстом.
Нахождение наиболее распространенных чисел
Предположим, перед нами стоит задача проанализировать имеющиеся данные о продажах в магазине, чтобы определить наиболее часто встречающееся количество приобретаемых товаров. Чтобы определить наиболее частое число в диапазоне, можно использовать функцию МОДА (РЕЖИМ):
То есть по нашей статистике чаще всего покупатели приобретают 3 шт. товары.
Если имеется не одно, а сразу несколько значений, встречающихся одинаковое максимальное количество раз (несколько режимов), то для их идентификации можно использовать функцию МОДА.НСК (РЕЖИМ.НЕСКОЛЬКО). Его необходимо вводить как формулу массива, т.е. выделить сразу несколько пустых ячеек, чтобы хватило на все режимы с запасом и ввести в строку формул =MODA.NSK(B2:B16) и нажать сочетание клавиш Shift + Ctrl + Enter.
На выходе мы получим список всех модов из наших данных:
То есть, судя по нашим данным, часто берут не только 3, но и 16 штук. товары. Обратите внимание, что в наших данных всего два режима (3 и 16), поэтому остальные ячейки, выделенные «про запас», будут с ошибкой #Н/Д.
Частотный анализ по диапазонам с функцией ЧАСТОТА
Если необходимо анализировать не целые, а дробные числа, то правильнее будет оценивать не количество одинаковых значений, а попадание их в заданные диапазоны. Например, нам необходимо понять, какого веса чаще всего приобретается товар, чтобы правильно подобрать для магазина тележки и упаковочные пакеты нужного размера. Другими словами, нам нужно определить, сколько чисел попадает в интервал 1..5 кг, сколько в интервал 5..10 кг и т.д.
Для решения подобной задачи можно использовать функцию ЧАСТОТЫ (ЧАСТОТА). Для него необходимо заранее подготовить ячейки с интересующими нас интервалами (карманами), а затем выделить пустой диапазон ячеек (G2:G5) на одну ячейку больше диапазона карманов (F2:F4) и ввести его как формула массива, нажав комбинацию в конце Shift + Ctrl + Enter:
Частотный анализ с помощью сводной таблицы с группировкой
Альтернативное решение проблемы: создайте сводную таблицу, где в области строк выставите вес покупок, а в области значений — количество клиентов, а затем примените группировку — щелкните правой кнопкой мыши по значениям веса и выберите команду группы (Группа). В появившемся окне можно задать лимиты и шаг группировки:
… и после нажатия на кнопку OK получим таблицу с подсчетом количества обращений покупателей в каждом диапазоне группировки:
Минусы сюда:
- шаг группировки может быть только постоянным, в отличие от функции ЧАСТОТЫ, где карманы можно указать абсолютно любые
- сводную таблицу необходимо обновлять при изменении исходных данных (по нажатию правой кнопки мыши – Обновить), а функция пересчитывается автоматически на лету
Поиск наиболее часто встречающегося текста
Если мы имеем дело не с числами, а с текстом, то подход к решению будет принципиально иным. Предположим, у нас есть таблица со 100 строками товаров, продаваемых в магазине, и мы хотим определить, какие товары покупаются чаще всего?
Самым простым и очевидным решением было бы добавить столбец рядом с функцией. COUNTIF (СЧЁТЕСЛИ)чтобы подсчитать количество вхождений каждого элемента в столбце A:
Затем, конечно же, отсортируйте полученный столбец по убыванию и посмотрите первые строки.
Или добавить в исходный список столбец с единицами и на основе полученной таблицы построить сводную таблицу, посчитав общее количество единиц по каждому товару:
Если исходных данных не так много и использовать сводные таблицы принципиально не хочется, то можно воспользоваться формулой массива:
Разберем по частям:
- СЧЁТЕСЛИ(A2:A20;A2:A20) — это формула массива, которая по очереди ищет количество вхождений каждого произведения в диапазоне A2:A100 и на выходе выдаёт массив с количеством повторений, т.е. фактически заменяет дополнительный столбец
- MAX – находит наибольшее число в массиве вхождений, т.е. наиболее покупаемый продукт.
- ПОИСКПОЗ – вычисляет порядковый номер строки таблицы, в которой MAX нашел наибольшее число.
- ИНДЕКС – возвращает из таблицы содержимое ячейки с числом, найденным методом ПОИСКПОЗ.
- Подсчет количества уникальных значений в списке
- Извлечение уникальных элементов из повторяющегося списка
- Группировка в сводных таблицах