Частота в экселе как сделать
Добавил пользователь Валентин П. Обновлено: 05.10.2024
Функция ЧАСТОТА() — Подсчет ЧИСЛОвых значений в MS EXCEL
Функция ЧАСТОТА( ) , английская версия FREQUENCY(), вычисляет частоту попадания значений в заданные пользователем интервалы и возвращает соответствующий массив чисел.
Функцией ЧАСТОТА() можно воспользоваться, например, для подсчета количества результатов тестирования, попадающих в определенные интервалы (См. Файл примера )
Синтаксис функции
ЧАСТОТА(массив_данных;массив_интервалов)
Массив_данных — массив или ссылка на множество ЧИСЛОвых данных, для которых вычисляются частоты.
Функция ЧАСТОТА() вводится как формула массива после выделения диапазона смежных ячеек, в которые требуется вернуть полученный массив распределения (частот). Т.е. после ввода формулы необходимо вместо нажатия клавиши ENTER нажать сочетание клавиш CTRL+SHIFT+ENTER.
Пусть в диапазоне А2:А101 имеется исходный массив чисел от 1 до 100.
Подсчитаем количество чисел, попадающих в интервалы 1-10; 11-20; . 91-100.
Сформируем столбце С массив верхних границ диапазонов (интервалов). Для наглядности в столбце D сформируем текстовые значения соответствующие границам интервалов (1-10; 11-20; . 91-100).
Для ввода формулы выделим диапазон Е2:Е12, состоящий из 11 ячеек (на 1 больше, чем число верхних границ интервалов). В Строке формул введем =ЧАСТОТА($A$2:$A$101;$C$2:$C$11) . После ввода формулы необходимо нажать сочетание клавиш CTRL+SHIFT+ENTER. Диапазон Е2:Е12 заполнится значениями:
- в Е2 — будет содержаться количество значений из А2:А101, которые меньше или равны 10;
- в Е3 — количество значений из А2:А101, которые меньше или равны 20, но больше 10;
- в Е11 — количество значений из А2:А101, которые меньше или равны 100, но больше 90;
- в Е12 — количество значений из А2:А101, которые больше 100 (таких нет, т.к. исходный массив содержит числа от 1 до 100).
Примечание. Функцию ЧАСТОТА() можно заменить формулой = СУММПРОИЗВ(($A$5:$A$104>C5)*($A$5:$A$104 Похожие задачи
Глава 16. Функция массива ЧАСТОТА
Это глава из книги: Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel.
Знакомство с функциями массива началось в главе 9. Мы узнали о функциях: ТРАНСП, МОДА.НСК и ТЕНДЕНЦИЯ. Настоящая заметка знакомит с четвертой функцией массива – ЧАСТОТА. Эта функция очень простая, но весьма мощная и универсальная. Она находит массу применений. Основная задача функции ЧАСТОТА – подсчитать, сколько чисел попадают в диапазон (рис. 16.1).
Рис. 16.1. Функция ЧАСТОТА подсчитывает, сколько результатов попали в тот или иной диапазон; диапазоны в D5:D10 не являются частью формулы; они показаны для иллюстрации
- Первый диапазон включает все значения, которые меньше или равны первой границе.
- Далее диапазоны формируются так, что нижняя граница не входит в диапазон, а верхняя – входит.
- Последний диапазон включает все значения, которые больше, чем последняя граница.
- Функция возвращает вертикальный массив. Если вам нужен горизонтальный массив, используйте функцию ТРАНСП (рис. 16.2).
- Если аргумент массив_карманов содержит N значений, диапазон введения функции ЧАСТОТА должен содержать N+1 ячеек.
- Функция ЧАСТОТА игнорирует пустые ячейки и текст.
- Если массив_интервалов содержит дубли, во все диапазоны-дубли, кроме первого, функция вернет 0.
- После того, как функция введена с помощью Ctrl+Shift+Enter, результирующий массив становится единым блоком и отдельные ячейки нельзя ни удалить, ни отредактировать. Но вы можете удалить все значения.
- Функция ЧАСТОТА может использоваться внутри больших формул массивов, возвращая вертикальный массив.
Рис. 16.2. Используйте функцию массива ТРАНСП, если нужно получить горизонтальный массив
Сравнение функций СЧЁТЕСЛИ, СЧЁТЕСЛИМН и ЧАСТОТА
Когда ваша цель – подсчет числа вхождений между нижней и верхней границами, вы должны рассмотреть, будут ли значения границ входить в диапазоны. Если у вас есть категории, подобные показанным на рис. 16.3, использовать функцию ЧАСТОТА гораздо проще, чем функции СЧЁТЕСЛИ или СЧЁТЕСЛИМН. Вы видите, что вам придется создать три разные формулы, если вы все же решите использовать СЧЁТЕСЛИ или СЧЁТЕСЛИМН вместо функции ЧАСТОТА. В данном примере ваш выбор однозначен – функция ЧАСТОТА.
Рис. 16.3. Функции СЧЁТЕСЛИ и СЧЁТЕСЛИМН сложнее, чем ЧАСТОТА; Чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке
Однако, если диапазоны включает нижнюю границу, но не верхнюю (рис. 16.4) функция ЧАСТОТА не подойдет. Кроме того, вы можете предусмотреть введение нижней и верхней границ для всех диапазонов, так что формулы примут одинаковый вид. В этом примере, вы отметаете функцию ЧАСТОТА, и скорее всего, предпочтете СЧЁТЕСЛИМН.
Рис. 16.4. СЧЁТЕСЛИ и СЧЁТЕСЛИМН более гибки по сравнению с функцией ЧАСТОТА при задании различных условий по вхождению границ в диапазоны
В следующей главе вы используете полученные знания о функции ЧАСТОТА для построения формул подсчета уникальных элементов в списке.
Частотный анализ по интервалам функцией ЧАСТОТА (FREQUENCY)
Для решения подобной задачи можно воспользоваться функцией ЧАСТОТА (FREQUENCY) . Ее синтаксис прост:
=ЧАСТОТА( Данные ; Карманы )
- Карманы — диапазон с границами интервалов, попадание в которые нас интересует
- Данные — диапазон с исходными числовыми значениями, которые мы анализируем
Обратите внимание, что эта функция игнорирует пустые ячейки и ячейки с текстом, т.е. работает только с числами.
Для использования функции ЧАСТОТА нужно:
- заранее подготовить ячейки с интересующими нас интервалами-карманами (желтые F2:F5 в нашем примере)
- выделить пустой диапазон ячеек (G2:G6) по размеру на одну ячейку больший, чем диапазон карманов (F2:F5)
- ввести функцию ЧАСТОТА и нажать в конце сочетание Ctrl+Shift+Enter, т.е. ввести ее как формулу массива
Во всех предварительно выделенных ячейках посчитается количество попаданий в заданные интервалы. Само-собой, для реализации подобной задачи можно использовать и другие способы (функцию СЧЁТЕСЛИ, сводные таблицы и т.д.), но этот вариант весьма хорош.
Кроме того, с помощью функции ЧАСТОТА можно легко подсчитывать количество уникальных чисел в наборе с помощью простой формулы массива:
Функция ЧАСТОТА
Функция частота Вычисляет частоту возникновения значений в диапазоне значений и возвращает вертикальный массив чисел. Функцией ЧАСТОТА можно воспользоваться, например, для подсчета количества результатов тестирования, попадающих в интервалы результатов. Поскольку данная функция возвращает массив, ее необходимо вводить как формулу массива.
Аргументы функции ЧАСТОТА описаны ниже.
Примечание: Если у вас установлена текущая версия Office 365, можно просто ввести формулу в верхней левой ячейке диапазона вывода и нажать клавишу ВВОД, чтобы подтвердить использование формулы динамического массива. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши CTRL+SHIFT+ВВОД для подтверждения. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.
Функция ЧАСТОТА пропускает пустые ячейки и текст.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.
Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).
KT Богомолов / МУ / ЗАДАНИЕ_1_СТАТИСТИКА / Дополнительные материалы / Построение гистограмм в Excel_2014
Построение гистограмм в Microsoft Excel
Перед построением гистограммы выполняется группировка данных по близким признакам. При группировании по количественному признаку все множество значений признака делится на
Для определения оптимального количества интервалов может быть использована формула Стерджесса:
n = 1 + (3,322 × lgN )
где N — количество наблюдений. В этом случае величина интервала:
h = ( V max — V min )/ n
Поскольку количество групп не может быть дробным числом, то полученную по этой формуле величину округляют до целого большего числа.
Нижнюю границу первого интервала принимают равной минимальному значению x min . Верхняя граница первого интервала соответствует значению ( x min + h ). Для последующих групп
границы определяются аналогично, то есть последовательно прибавляется величина интервала h .
В Excel для построения гистограмм используются статистическая функция ЧАСТОТА в сочетании с мастером построения обычных диаграмм и процедура Гистограмма из пакета анализа .
Функция ЧАСТОТА (массив_данных, двоичный_массив) вычисляет частоты появления случайной величины в интервалах значений и выводит их как массив цифр, где
• Массив_данных — массив исходных данных, для которых вычисляются частоты;
• Массив_интервалов — это массив интервалов, по которым группируются значения выборки .
Перед вызовом функции ЧАСТОТА необходимо выделить столбец c числом ячеек, равным числу интервалов n , в который будут выведены результаты выполнения функции.
Вызвать Мастер функций (кнопка f x ):
и функцию ЧАСТОТА .
В поле Массив_данных ввести диапазон данных наблюдений А3:А102 (с листа ‘Расчетные данные’) . В поле Массив_интервалов ввести диапазон интервалов с того же листа ([‘Расчетные данные’!F16:F23] – в данном примере).
При завершении ввода данных нажать комбинацию клавиш Ctrl+Shift+Enter.
В предварительно выделенном столбце (C5:C12 – в данном примере) должен появиться массив
Столбец Накопленные частоты получается последовательным суммированием относительных частот (в процентном формате) в направлении от первого интервала к последнему.
В завершении с помощью Мастера диаграмм строится диаграмма абсолютных и накопленных частот с выбором типа диаграммы соотвественно гистограмма и график.
(Excel 2007. 2010):
В появившемся списке выбрать инструмент Гистограмма и щелкнуть на кнопке ОК. Появится окно гистограммы, где задаются следующие параметры:
Интервал карманов : (необязательный параметр) – адреса ячеек, содержащие границы интервалов. Это поле предлагается оставить пустым, предоставив Excel самому вычислить границы интервалов (карманов – в терминах Excel).
Метки – флажок, включаемый, если первая строка во входных данных содержит заголовки. Если заголовки отсутствуют, то флажок следует выключить.
Выходной интервал: / Новый рабочий лист: / Новая рабочая книга.
Включенный переключатель Выходной интервал требует ввода адреса верхней ячейки, начиная с которой будут размещаться вычисленные относительные частоты j .
В положении переключателя Новый рабочий лист: открывается новый лист, в котором начиная с ячейки А1 размещаются частности j .
В положении переключателя Новая рабочая книга открывается новая книга, на первом листе которой начиная с ячейки А1 размещаются частности j .
Парето ( отсортированная гистограмма ) – устанавливается, чтобы представить j в порядке их убывания. Если параметр выключен, то j приводятся в порядке следования интервалов.
Интегральный процент – устанавливается в активное состояние для расчета выраженных в процентах накопленных относительных частот (аналог значений столбца Накопленные частоты ).
Вывод графика – устанавливается в активное состояние для автоматического создания встроенной диаграммы на листе, содержащем частоты.
Как правило, гистограммы изображаются в виде смежных прямоугольных областей. Поэтому столбики гистограммы следует расширить до соприкосновения друг с другом. Для этого необходимо щелкнуть мышью на диаграмме, далее на панель инструментов Диаграмма , раскрыть список инструментов и выбрать элемент Ряд ‘Частота’ , после чего щелкнуть на кнопке Формат ряда . В появившемся одноименном диалоговом окне необходимо активизировать закладку Параметры и в поле Ширина зазора установить значение 0 ((Excel 2003):
В Excel 2007. 2010 встать на любой столбик гистограммы и правой кнопкой мыши выбрать
Формат ряда данных:
Для построения теоретической кривой нормального распределения по эмпирическим данным необходимо найти теоретические частоты.
В Excel для вычисления значений нормального распределения используются функция НОРМРАСП, которая вычисляет значения вероятности нормальной функции распределения для указанного среднего и стандартного отклонения.
Функция имеет параметры:
НОРМРАСП (х; среднее; стандартное_откл; интегральная) , где:
х — значения выборки, для которых строится распределение; среднее — среднее арифметическое выборки; стандартное_откл — стандартное отклонение распределения;
интегральный — логическое значение, определяющее форму функции. Если интегральная имеет значение ИСТИНА(1), то функция НОРМРАСП возвращает интегральную функцию распределения; если это аргумент имеет значение ЛОЖЬ (0), то вычисляет значение функция плотности распределения.
Для получения абсолютных значений плотностей распределения (теоретических частот) достаточно найденные значения вероятности умножить на величину интервала h и количество наблюдений N = 100 по каждой строке.
Для завершения выполнения задания необходимо внести полученные значения теоретических частот на рисунок с гистограммой, добавив ряд в закладке Исходные данные и выбрав тип диаграммы
– график ((Excel 2003):
В Excel 2007. 2010 находясь в обласи гистограммы по правой кнопке мыши выбрать Выбрать данные (или по одноименной кнопке на вкладке Конструктор ):
Excel 2016 обзавелся новыми типами графиков. Причем, это не какие-нибудь дизайнерские новшества, а самые настоящие статистические диаграммы.
На первый взгляд и в более ранних версиях Excel можно изобразить частоты с помощью диаграмм. Можно, но для этого предварительно необходимо числовые данные сгруппировать. То есть для каждой категории (интервала, группы, года и т.д.) нужно посчитать частоту. Теперь появилась возможность изобразить распределение данных буквально в один клик без предварительных расчетов и группировок.
Строится такая диаграмма в один клик. Выделяем ряд данных и нажимаем кнопку гистограммы частот.
Собственно, все. Тут же появляется соответствующая диаграмма.
Возникает вопрос: как Excel делит данные на интервалы? Справка Excel говорит, что с помощью формулы.
Количество интервалов получается достаточным для того, чтобы визуально прикинуть, каков характер распределения анализируемых данных.
Интервалы легко перестроить под свои потребности. Можно, например, задать нижнюю и верхнюю границу, за пределами которых данные будут объединены в один интервал.
При выборе опции выхода за нижнюю и верхнюю границы, судя по той же справке, их значения рассчитываются, как расстояние ±3σ от средней арифметической.
Однако рассчитываемые автоматически значения легко изменить в окне настроек.
Это был пример, когда данные разбиваются на интервалы. Такой вариант группировки установлен по умолчанию (см. окно параметров настройки оси выше).
Проведем эксперимент. С помощью функции СЛУЧМЕЖДУ смоделируем равномерно распределенную выборку в пределах, скажем, от 0 до 200. Пусть выборка состоит из 100 значений. Теперь изобразим гистограмму частот.
Как видно, частоты примерно одинаковы.
Пост в продолжение предыдущего Как считать Хи квадрат в excel.
Еще одной нудной операцией по вычислению Хи квадрат в excel может быть вычисление ожидаемых частот. Напомню, что нам необходимо по каждой ячейке перемножить суммы соответствующих строки и столбца и разделить на общую сумму по таблице. Помимо того, что ручной способ вычисления может показаться громоздким, он еще чреват ошибками.
Для оптимизации процесса существуют функции массива. В отношении расчета ожидаемых частот мы можем воспользоваться этой функцией так:
– Приведите примеры непрерывных случайных величин (рост дерева), дискретных случайных величин (количество учеников в классе).
– Какие статистические характеристики случайных величин мы знаем (мода, медиана, среднее выборочное значение, размах ряда).
– Какие приемы используются для наглядного представления статистических характеристик случайной величины (полигон частот, круговые и столбчатые диаграммы, гистограммы).
- Рассмотрим, применение инструментов Excel для решения статистических задач на конкретном примере.
Пример. Проведена проверка в 100 компаниях. Даны значения количества работающих в компании (чел.):
- моду
- медиану
- размах ряда
- построить полигон частот
- построить столбчатую и круговую диаграммы
- раскрыть смысловую сторону каждой характеристики
1. Занести данные в EXCEL, каждое число в отдельную ячейку.
| 23 | 25 | 24 | 25 | 30 | 24 | 30 | 26 | 28 | 26 |
| 32 | 33 | 31 | 31 | 25 | 33 | 25 | 29 | 30 | 28 |
| 23 | 30 | 29 | 24 | 33 | 30 | 30 | 28 | 26 | 25 |
| 26 | 29 | 27 | 29 | 26 | 28 | 27 | 26 | 29 | 28 |
| 29 | 30 | 27 | 30 | 28 | 32 | 28 | 26 | 30 | 26 |
| 31 | 27 | 30 | 27 | 33 | 28 | 26 | 30 | 31 | 29 |
| 27 | 30 | 30 | 29 | 27 | 26 | 28 | 31 | 29 | 28 |
| 33 | 27 | 30 | 33 | 26 | 31 | 34 | 28 | 32 | 22 |
| 29 | 30 | 27 | 29 | 34 | 29 | 32 | 29 | 29 | 30 |
| 29 | 29 | 36 | 29 | 29 | 34 | 23 | 28 | 24 | 28 |
2. Для расчета числовых характеристик используем опцию Вставка – Функция. И в появившемся окне в строке категория выберем - статистические, в списке: МОДА
В поле Число 1 ставим курсор и мышкой выделяем нашу таблицу:
Нажимаем клавишу ОК. Получили Мо = 29 (чел) – Фирм у которых в штате 29 человек больше всего.
Используя тот же путь вычисляем медиану.
Вставка – Функция – Статистические – Медиана.
В поле Число 1 ставим курсор и мышкой выделяем нашу таблицу:
Нажимаем клавишу ОК. Получили Ме = 29 (чел) – среднее значение сотрудников в фирме.
Размах ряда чисел – разница между наименьшим и наибольшим возможным значением случайной величины. Для вычисления размаха ряда нужно найти наибольшее и наименьшее значения нашей выборки и вычислить их разность.
Вставка – Функция – Статистические – МАКС.
В поле Число 1 ставим курсор и мышкой выделяем нашу таблицу:
Нажимаем клавишу ОК. Получили наибольшее значение = 36.
Вставка – Функция – Статистические – МИН.
В поле Число 1 ставим курсор и мышкой выделяем нашу таблицу:
Нажимаем клавишу ОК. Получили наименьшее значение = 22.
36 – 22 = 14 (чел) – разница между фирмой с наибольшим штатом сотрудников и фирмой с наименьшим штатом сотрудников.
Для построения диаграммы и полигона частот необходимо задать закон распределения, т.е. составить таблицу значений случайной величины и соответствующих им частот. Мы ухе знаем, что наименьшее число сотрудников в фирме = 22, а наибольшее = 36. Составим таблицу, в которой значения xi случайной величины меняются от 22 до 36 включительно шагом 1.
| xi | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29 | 30 | 31 | 32 | 33 | 34 | 35 | 36 |
| ni |
Чтобы сосчитать частоту каждого значения воспользуемся
Вставка – Функция – Статистические – СЧЕТЕСЛИ.
В окне Диапазон ставим курсор и выделяем нашу выборку, а в окне Критерий ставим число 22
Нажимаем клавишу ОК, получаем значение 1, т.е. число 22 в нашей выборке встречается 1 раз и его частота =1. Аналогичным образом заполняем всю таблицу.
| xi | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29 | 30 | 31 | 32 | 33 | 34 | 35 | 36 |
| ni | 1 | 3 | 4 | 5 | 11 | 9 | 13 | 18 | 16 | 6 | 4 | 6 | 3 | 0 | 1 |
Для проверки вычисляем объем выборки, сумму частот (Вставка – Функция – Математические - СУММА). Должно получиться 100 (количество всех фирм).
Чтобы построить полигон частот выделяем таблицу – Вставка – Диаграмма – Стандартные – Точечная (точечная диаграмма на которой значения соединены отрезками)
Нажимаем клавишу Далее, в Мастере диаграмм указываем название диаграммы (Полигон частот), удаляем легенду, редактируем шкалу и характеристики диаграммы для наибольшей наглядности.
Для построения столбчатой и круговой диаграмм используем тот же путь (выбирая нужный нам тип диаграммы).
Диаграмма – Стандартные – Круговая.
Диаграмма – Стандартные – Гистограмма.
4. Сегодня на уроке мы научились применять компьютерные технологии для анализа и обработки статистической информации.
Читайте также: