Примеры формул для расчета частоты повторений в Excel

Квартили являются популярным инструментом служащим для деления данных на равные диапазоны. Поэтому Excel предлагает специально предназначенную функцию для этой цели – КВАРТИЛЬ. Однако иногда приходится разделять данные на самостоятельно определенные диапазоны. Функция ЧАСТОТА позволяет сегментировать данные в Excel на любое количество сегментов и долей по условию пользователя.

Как посчитать частоту в Excel по формуле

Ниже на рисунке представлен список фактур с их целыми числовыми значениями сумм. Допустим, что в данном примере нам необходимо проверить: как часто клиенты делают покупки на суммы в границах от 1 и до 100, от 101 и до 200 и т.д.

Функция ЧАСТОТА считает все фактуры, которые содержат значения, определенные в диапазоне:

Функция ЧАСТОТА является массивной функцией. Это значит, что, вводя ее в строку формул, необходимо для подтверждения ввода нажать комбинацию клавиш CTRL+SHIFT+Enter, а не просто клавишу Enter как обычно. Excel помещает тогда формулу внутрь фигурных скобок ({}), которые означают что введена формула массива.

Функция ЧАСТОТА имеет 2 аргумента:

1. Массив данных – исходный диапазон ячеек для разделения на части.
2. Массив интервалов – диапазон чисел, определяющих верхние границы для этих же частей.

Сначала введите в столбец E границы пределов. Значения в столбце D не имеют влияние на результат вычисления формулы. Они необходимы лишь для того, чтобы было наглядно видно где находиться нижние границы частей делений данных.

Чтобы ввести функцию ЧАСТОТА в столбце F, сначала выделите диапазон ячеек F2:F11. Не снимая выделения вводите формулу. Несмотря на то, что формула будет введена только в одной ячейке, нажатие на комбинацию клавиш CTRL+SHIFT+Enter позволит заполнить все выделенные ячейки диапазона формулой массива.

Результат возвращаемый с помощью функции ЧАСТОТА отображает, что большая часть клиентов совершает покупки на сумму в границах от 200 и до 300 во время одного визита.

Если попробовать убрать одну с ячеек в диапазоне, в котором введена функция ЧАСТОТА, Excel выдаст сообщение «Нельзя изменять часть массива». Excel воспринимает эти и другие функции массива в диапазоне ячеек как одно целое. Можно изменять диапазон целиком, но не его отдельные ячейки. Если нужно изменить границы делений данных придется удалить и заново ввести формулу всю массива.



Расчет частоты в Excel с помощью функции СЧЁТЕСЛИМН

Для создания графика частот можно воспользоваться функцией СЧЁТЕСЛИМН. Данная функция не является массивной, поэтому введение изменений для границ диапазона, а также увеличение или уменьшение их количества будет простым заданием. В случае с данными представленными на рисунке ранее функция СЧЁТЕСЛИМН используется в следующей формуле:

В отличии от функции ЧАСТОТА функция СЧЁТЕСЛИМН требует указать нижнюю границу предела (столбец D) и считает все значения, которые больше чем нижняя граница и меньше от верхнего предела или равно ему. Данную функцию не нужно вводить как формулу массива. Достаточно лишь скопировать ее в очередные нижние ячейки ровно столько раз сколько есть определенных пределов границ.