Загрузите образец книги
В этом руководстве показано, как использовать Функция ЧАСТОТА в Excel в Excel для создания гистограммы.
Обзор функции ЧАСТОТА
Функция ЧАСТОТА Вычисляет частоту значений, попадающих в указанные диапазоны. Возвращает вертикальный массив, содержащий количество каждого «ведра». Примечание. Это функция массива. Ниже приведены конкретные инструкции по использованию этой функции.
(Обратите внимание, как появляются входные данные формулы)
Функция ЧАСТОТА Синтаксис и входные данные:
| 1 | = ЧАСТОТА (массив_данных, массив_бинов) |
data_array - Массив чисел, частоту которых вы хотите посчитать.
bins_array - Массив интервалов или «сегментов», в которые вы хотите сгруппировать счетчики.
В статистическом анализе частота - это количество раз, когда значение данных встречается. Например, 8 учеников могут набрать 65 баллов на тесте. Оценка 65 будет иметь частоту 8. В Excel функция частоты - это количество раз, когда диапазон значений встречается. Вместо 65 вы можете подсчитать, сколько учеников набрали от 61 до 70 баллов.
Давайте посмотрим на пример результатов тестов учащихся. Есть 2 ученика, набравшие от 61 до 70 (Мэри и Пэм).
| 1 | {= ЧАСТОТА (C3: C7, E3: E6)} |
Чтобы использовать функцию ЧАСТОТА Excel, сначала настройте диапазон значений, которые вы хотите найти:
Значения диапазона просто означают:
Обратите внимание на две вещи выше. Первое значение диапазона начинается с 0, а максимальное - это значение, указанное слева.
Также есть дополнительное значение, которое я выделил красным (> 80). Когда он находит значение в указанном вами диапазоне, он попадает в эту категорию.
Как только это будет сделано, вам нужно выбрать диапазон с размером вашего диапазона значений (+ одна дополнительная строка). В нашем примере у нас есть четыре диапазона значений (50, 60, 70 и 80). Итак, выбираем пять (4 + 1 дополнительный ряд).
Введите диапазон результатов теста и диапазон значений в функцию:
| 1 | = ЧАСТОТА (C3: C7; E3: E6) |
Не нажимайте Enter, а CTRL + SHIFT + ENTER, потому что это формула массива. Когда все будет сделано правильно, вы должны увидеть фигурные скобки, окружающие его. Если вы используете Excel 2022 или Office 365, вы можете ввести формулу как обычно и даже выбрать только одну ячейку для ввода формулы. Диапазон автоматически уменьшится.
Это означает, что 0 учеников набрали тестовые баллы от 0 до 50. 1 ученик от 51 до 60 (Боб). 2 ученика от 61 до 70 (Мэри и Пэм). 1 студент от 71 до 80 лет (Ким). И, наконец, 1 ученик выше указанного диапазона> 80 (Джон).
Функция ЧАСТОТА игнорирует пустые ячейки или ячейки, содержащие нечисловые значения.
Важно: функция ЧАСТОТА игнорирует числа, хранящиеся в виде текста. Чтобы использовать функцию ЧАСТОТА с числами, сохраненными в виде текста, сначала используйте функцию ЗНАЧЕНИЕ, чтобы преобразовать числа, сохраненные в виде текста, в фактические числа.
Очень часто функция ЧАСТОТА используется для получения отдельного счетчика. Например, здесь пять тестов, но только для трех учеников (Джон появлялся трижды, а Боб и Ким появлялись один раз).
Поскольку функция ЧАСТОТА не работает с текстом, мы должны преобразовать учащихся в числа. Эти номера также должны указывать на то, являются ли они одними и теми же учащимися. В Excel есть функция ПОИСКПОЗ, которая определяет номер первой позиции того, что вы ищете, внутри диапазона.
| 1 | = ПОИСКПОЗ (B3: B7; B3: B7,0) |
Выше это означает, что вы ищете B3: B7 внутри диапазона B3: B7. Это формула массива, которая фактически возвращает несколько результатов, которые вы не видите. Если вы щелкните внутри строки формул и нажмите F9 для вычисления, вы увидите:
Это означает, что «Джон» находится в позиции 1 (из B3: B7), «Боб» в позиции 2 (из B3: B7), «Джон» в позиции 1 (возвращает номер первой позиции), «Джон» в позиции 1. И снова "Ким" на 5 позиции.
Нам нужно создать диапазон значений, как в нашем примере с результатами теста. Здесь мы хотим подсчитать, сколько позиций от 0 до 1 (Джон), от 1 до 2 (Боб) и от 4 до 5 (Ким). Функция СТРОКА в Excel помогает нам получить номер строки.
| 1 | = СТРОКА (B3: B7) |
Это снова дает нам формулу массива. СТРОКА (B3) дает нам строку 3, СТРОКА (B4) дает нам 4 и так далее. Вы можете щелкнуть внутри строки формул и снова нажать F9, чтобы вычислить снова. Это показывает:
Мы хотим, чтобы B3 был строкой 1, а не строкой 3, чтобы они были одинаковыми. Для этого просто вычтите начальную строку и плюс 1.
| 1 | = СТРОКА (B3: B7) -СТРОКА (B3) +1 |
Строка 3 минус 3 плюс 1 равна 1. Строка 4 минус 3 плюс 1 равна 2. И так далее. Щелкните внутри строки формул и нажмите F9, чтобы проверить.
Поэтому вместо использования тестовой оценки в качестве первого аргумента и диапазона значений в качестве второго аргумента FREQUENCY мы можем использовать функцию MATCH и функцию ROW, чтобы сделать то же самое.
| 1 | = ЧАСТОТА (ПОИСКПОЗ (B3: B7; B3: B7; 0); СТРОКА (B3: B7) -ROW (B3) +1) |
Нажмите F9 для расчета, и он покажет:
Это означает, что позиции от 0 до 1 появлялись 3 раза (Джон), позиции от> 1 до 2 появлялись один раз (Боб), позиции от> 2 до 3 и> от 3 до 4 появлялись 0 раз, а позиции от> 4 до 5 появлялись один раз (Ким). И, наконец, помните, что FREQUENCY имеет дополнительный диапазон значений для тех, кто> 5 (выше указанного вами диапазона).
Мы могли видеть, сколько раз каждый из студентов появлялся сейчас, но это не цель. Мы хотим знать, сколько здесь разных учеников. И мы могли видеть сверху, что до тех пор, пока значение> 0, на этой позиции находится ученик. Следовательно, мы хотим суммировать любые числа в массиве, которые> 0. Давайте сначала добавим функцию ЕСЛИ, чтобы проверить, больше ли число ЧАСТОТА нуля. Если это так, покажите как 1.
| 1 | = ЕСЛИ (ЧАСТОТА (ПОИСКПОЗ (B3: B7; B3: B7,0); СТРОКА (B3: B7) -ROW (B3) +1)> 0,1) |
Нажмите F9 для расчета, и он покажет:
Вы видите только положение Джона, Боба и Ким как одно целое.
Наконец, добавьте СУММ, чтобы подвести итог. Поскольку на этот раз нам нужен единственный ответ от массива, нажмите CTRL + SHIFT + ENTER для подтверждения. Это дает нам 3 разных ученика.
Функция ЧАСТОТА работает в Google Таблицах точно так же, как в Excel 2022 и Office 365. Вам не нужно нажимать CTRL + SHIFT + ВВОД или выбирать группу ячеек для передачи.
Примеры ЧАСТОТЫ в VBA
Вы также можете использовать функцию ЧАСТОТА в VBA. Тип:
| 1 | application.worksheetfunction.frequency (массив_данных, массив_бинов) |
Для аргументов функции (data_array и т. Д.) Вы можете либо ввести их непосредственно в функцию, либо определить переменные для использования вместо них.
Вернуться к списку всех функций в Excel
