Как использовать функцию ЧАСТОТА в Excel

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

Что делает функция ЧАСТОТА?

Функция массива Excel ЧАСТОТА позволяет вычислить частотное распределение набора данных. Вы предоставляете числовой набор данных (это фактические ячейки, которые вы используете в качестве источника), список порогов ячеек (это категории, по которым вы сортируете данные), а затем нажимаете Ctrl+Shift+Enter.

Итак, как вы могли бы его использовать? Ну, вот быстрый пример. Скажем, вы учитель и у вас есть электронная таблица, в которой показаны числовые оценки всех ваших учеников. Вы можете использовать функцию ЧАСТОТА, чтобы выяснить, сколько учеников получили оценки A, B, C, D или F. Числовые результаты тестов представляют собой набор данных, а буквенные оценки формируют ваши пороговые значения.

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

Если округлить баллы до ближайшей десятой доли процента, будут применяться следующие диапазоны:

F <= 59,9 < D <= 69,9 < C <= 79,9 < B <= 89,9 < A

Excel присвоит диапазону C оценку 79,9, а диапазону A — 98,2 балла. Excel будет просматривать список результатов тестов, классифицировать каждую оценку, подсчитывать общее количество оценок, попадающих в каждый диапазон, и возвращать массив с пятью ячейками, показывающими общее количество оценок в каждом диапазоне.

Функция FREQUENCY требует в качестве входных данных два массива: «массив_данных» и «массив_бинов». Массивы — это просто списки значений. «Data_array» должен содержать значения — например, числовые оценки учащихся, — которые Excel может сравнить с рядом пороговых значений, определенных в «Bins_array», — например, буквенные оценки в том же примере.

Давайте посмотрим на пример

В нашем примере мы рассчитаем частотное распределение и частотный процент набора из 18 чисел от 0 до 10. Это простое упражнение, в котором мы собираемся определить, сколько из этих чисел попадает между единицей и двумя, между двумя и три и так далее.

В нашем простом примере электронной таблицы у нас есть два столбца: Data_array и Bins_array.

Столбец «Data_array» содержит числа, а столбец «Bins_array» содержит пороговые значения бинов, которые мы будем использовать. Обратите внимание, что мы оставили пустую ячейку в верхней части столбца «Bins_array» для учета количества значений в результирующем массиве, который всегда будет содержать на одно значение больше, чем «Bins_array».

Мы также собираемся создать третий столбец, куда будут помещаться наши результаты; мы называем его «Результаты».

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

Появится окно «Аргументы функции». Щелкните в поле «Data_array», а затем выделите ячейки в столбце «Data_array» (вы также можете ввести номера ячеек, если хотите).

Если вы получаете сообщение об ошибке, в котором говорится, что вы не можете редактировать только часть массива, это означает, что вы не выбрали все ячейки массива. Нажмите «ОК», а затем нажмите клавишу Esc.

Чтобы отредактировать формулу массива или удалить массив, необходимо сначала выделить все ячейки массива.

Теперь щелкните поле «Bins_array», а затем выберите заполненные ячейки в столбце «Bins_array».

Нажмите кнопку «ОК».

Вы увидите, что только первая ячейка столбца «Результаты» имеет значение, остальные пустые.

Чтобы увидеть другие значения, щелкните внутри панели «Формула», а затем нажмите Ctrl+Shift+Enter.

В столбце «Результаты» теперь будут отображаться недостающие значения.

Вы можете видеть, что Excel нашел четыре значения, которые были меньше или равны единице (выделены красным), а также нашел количество каждого из наших других диапазонов чисел. Мы добавили столбец «Описание результата» в нашу электронную таблицу, чтобы мы могли объяснить логику, используемую Excel для расчета каждого результата.

Как вычислить процент частоты

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

Чтобы вычислить процент частоты каждого бина, мы можем изменить формулу массива, используя панель функций Excel. Выделите все ячейки в столбце «Результаты», а затем добавьте следующее в конец формулы на панели функций:

 /СЧЕТ(B3:B20)

Окончательная формула должна выглядеть так:

 =ЧАСТОТА(B3:B20;C3:C20)/ЧИСЛО(B3:B20)

Теперь снова нажмите Ctrl+Shift+Enter.

Новая формула делит каждый элемент массива результатов на общее количество значений в столбце «Data_array».

Результаты не форматируются автоматически в процентах, но это достаточно легко изменить. Перейдите в меню «Главное» и нажмите кнопку «%».

Теперь значения будут отображаться в процентах. Так, например, теперь вы можете видеть, что 17% чисел в столбце «Data_array» попали в диапазон 1-2.

Лучше всего то, что теперь, когда формула находится в столбце «Результаты», вы можете изменить любые значения в столбцах «Массив_данных» и «Массив_бинов», и Excel автоматически обновит результаты с обновленными значениями.

Обход меню формул и использование панели функций

Если вы предпочитаете печатать и знаете, как называть столбцы и ячейки, вы всегда можете не копаться в меню «Формулы», просто вводя функции непосредственно в панель функций Excel, а затем нажимая Ctrl+Shift+Enter.

Чтобы рассчитать частотное распределение, используйте следующий синтаксис:

 {=ЧАСТОТА( Массив_данных , Массив_бинов )}

Чтобы рассчитать процент частоты, используйте этот синтаксис:

 {=ЧАСТОТА( массив_данных , массив_бинов )/COUNT( массив_данных )}

Просто помните, что это формула массива, поэтому вы должны нажать Ctrl+Shift+Enter, а не просто Enter. Наличие {фигурных скобок} вокруг формулы указывает на то, что она была введена как формула массива.