A frequency distribution table is an organized representation of the frequency of each element in a dataset/record. It helps us to visualize data in terms of class range and the number of time each element belong to that class interval. The table usually consists of two columns, the first is the class interval and the second one is the frequency itself.
Calculate Frequency Distribution in Excel
Suppose, we want to distribute marks obtained by 40 students in a class using a frequency distribution table then a sampling frequency distribution table will look like this:
|
Marks |
Frequency |
|---|---|
|
0-20 |
5 |
|
21-40 |
7 |
|
41-60 |
13 |
|
61-80 |
9 |
|
81-100 |
6 |
In this article, we are going to see how we can calculate such a frequency distribution table inside Microsoft Excel.
Method 1: Using Pivot Table
Microsoft Excel has a powerful tool named Pivot table which helps users to analyze large amounts of data interactively by aggregating individual records of a table into different groups. A pivot table can be used to create a frequency distribution table as:
Step 1: Select the desired range of cells and then go to the Insert tab and select Pivot Table from the menu.
Step 2: A Create Pivot Table dialog box will pop up on the screen. Since we want to create the pivot table in the same worksheet. We can choose the Existing Worksheet radio check box in the dialog box and select the desired cell where we want to place the table (in the image shown below cell D1 is selected).
Step 3: A pivot table will become visible on the screen as shown in the image given below.
Step 4: From the right-hand side PivotTable Fields prompt, drag the Scores field name into the Values field and the Rows field one by one.
Step 5: We can notice the changes made in our pivot table. Now, right-click on the Sum of Scores field inside the Values section and select the Field Settings option from the menu.
Step 6: A Pivot Table Field dialog box will appear on the screen. Select the Count option from the list under the Summarise by tab and click OK.
Step 7: After hitting the OK button, we can notice the changes made to our pivot table. Now for each element, we have its frequency inside the table.
Step 8: Under the Row Labels column inside the pivot table select any record and right-click with the mouse to open a list of options. From the options menu, select the Group option.
Step 9: A dialog box named Grouping will appear on the screen. Fill out the starting and ending values in the respective fields. Since we want to divide our data into intervals of 10 hence we fill 10 in the “By” field and then click OK.
Step 10: On clicking the OK button, we can notice our data values are distributed across an interval and hence our final frequency distribution table is created.
Method 2: Using the COUNTIFS() function
We can even use the in-built COUNTIFS() function to create a frequency distribution table.
The syntax for the COUNTIFS() function is given as:
=COUNTIFS(range1,criteria1,range2,criteria2,….)
Where range1 and range2 are the cell range of the records and criteria1 and criteria2 are the logical expressions.
Step 1: Create a class Interval column manually assigning the appropriate values as per requirement.
Step 2: Now, click on the desired cell where you want to find out the frequency in the class interval (here, cell D2). Type the formula
=COUNTIFS(A2:A16,”>=10″,A2:A16,”<=19″)
And hit enter, it will generate the output 1 as only 18 is in the class range 10-19. Here, A2:A16 is the range as our data is stored in this cell we give A2:A16 as the range1 and range2 values, criteria1 is >=10 and criteria2 is <=19.
Step 3: Similarly, we can use the same COUNTIFS() formula for the next cells. Let’s say we want to find the count of records between the interval 20-29, then the formula will be,
=COUNTIFS(A2:A16,”>=20″,A2:A16,”<=29″)
It will generate 3 as the output as three numbers 25, 27 and 29 lie within this class interval.
Step 4: We can use the same formula for finding the values for all class intervals and our values will be shown as:
17 авг. 2022 г.
читать 2 мин
Распределение частоты описывает, как часто разные значения встречаются в наборе данных. Это полезный способ понять, как значения данных распределяются в наборе данных.
К счастью, легко создать и визуализировать частотное распределение в Excel, используя следующую функцию:
=ЧАСТОТА(массив_данных,массив_бинов)
куда:
- data_array : массив необработанных значений данных
- bins_array: массив верхних пределов для бинов
В следующем примере показано, как использовать эту функцию на практике.
Пример: частотное распределение в Excel
Предположим, у нас есть следующий набор данных из 20 значений в Excel:
Во-первых, мы укажем Excel, какие верхние пределы мы хотели бы использовать для интервалов нашего частотного распределения. Для этого примера мы выберем 10, 20 и 30. То есть мы найдем частоты для следующих интервалов:
- от 0 до 10
- с 11 до 20
- от 21 до 30
- 30+
Далее мы будем использовать следующую функцию =FREQUENCY() для вычисления частот для каждого бина:
=ЧАСТОТА( A2:A21 , C2:C4 )
Вот результаты:
Результаты показывают, что:
- 6 значений в наборе данных находятся в диапазоне от 0 до 10.
- 7 значений в наборе данных находятся в диапазоне 11-20.
- 5 значений в наборе данных находятся в диапазоне 21-30.
- 2 значения в наборе данных больше 30.
Затем мы можем использовать следующие шаги для визуализации этого частотного распределения:
- Выделите частоты в диапазоне D2:D5 .
- Нажмите на вкладку « Вставка », затем нажмите на диаграмму под названием « Двухмерный столбец » в группе « Диаграммы ».
Появится следующая диаграмма, отображающая частоты для каждого бина:
Не стесняйтесь изменять метки осей и ширину полос, чтобы сделать диаграмму более эстетичной:
Вы можете найти больше учебников по Excel здесь .
Написано
Замечательно! Вы успешно подписались.
Добро пожаловать обратно! Вы успешно вошли
Вы успешно подписались на кодкамп.
Срок действия вашей ссылки истек.
Ура! Проверьте свою электронную почту на наличие волшебной ссылки для входа.
Успех! Ваша платежная информация обновлена.
Ваша платежная информация не была обновлена.
- Распределение частот в Excel
Распределение частот Excel (Содержание)
- Распределение частот в Excel
- Формула частоты в Excel
- Как сделать распределение частот в Excel?
Распределение частот в Excel
В Excel частота — это встроенная функция, которая подпадает под статистическую категорию. Распределение частоты может быть определено как список данных или графика, который дает частоту различных результатов. В Excel это встроенная функция, которая часто используется для получения данных о различных результатах потока и затем возвращает вертикальный массив числа, имеющего еще один элемент, т.е. функция частоты обычно возвращает значение массива, которое должно быть обновлено как формула массива на основе данные.
Формула частоты в Excel
Ниже приведена формула частоты в Excel:
Функция частоты имеет два аргумента:
- Массив данных: набор значений массива, в котором он используется для подсчета частот. Если значения массива данных равны нулю (то есть значения NULL), то функция частоты в Excel возвращает массив нулевых значений.
- Массив бинов: набор значений массива, который используется для группировки значений в массиве данных. Если значения массива bin равны нулю (т. Е. Значения NULL), он возвращает количество элементов массива из массива данных.
Как сделать распределение частот в Excel?
Распределение частот в Excel очень простое и удобное в использовании. Давайте разберемся с работой распределения частот Excel на некотором примере.
Вы можете скачать этот шаблон Excel для распределения частот здесь — Шаблон Excel для распределения частот
В Excel мы можем найти «частотную функцию» в меню формул, которая подпадает под статистическую категорию, выполнив следующие шаги следующим образом.
- Перейдите в меню « Формула» .
- Нажмите на Дополнительные функции .
- В категории « Статистика » выберите « Функция частоты», как показано на скриншоте ниже.
- Мы получим диалоговое окно Frequency Function, как показано ниже.
Где массив данных — это массив или набор значений, где мы хотим подсчитать частоты, а Bins_array — это массив или набор значений, где мы хотим сгруппировать значения в массиве данных.
Пример № 1
В этом примере мы увидим, как найти частоту с помощью доступной базы данных студентов.
Давайте рассмотрим приведенный ниже пример, который показывает оценку студентов, которая показана ниже.
Теперь, чтобы вычислить частоту, мы должны сгруппировать данные с оценками учащихся, как показано ниже.
Теперь с помощью функции частоты мы сгруппируем данные, выполнив следующие шаги.
- Создайте новый столбец с именем Частота.
- Используйте частотную формулировку в столбце G, выбрав от G3 до G9.
- Здесь нам нужно выбрать весь столбец частоты, тогда только функция частоты будет работать правильно, иначе мы получим значение ошибки.
- Как показано на скриншоте выше, мы выбрали столбец в качестве массива данных и массив бинов в качестве меток ученика = FREQUENCY (F3: F9, C3: C22) и перейдем к сочетаниям клавиш CTRL + SHIFT + ENTER .
- Так что мы получим значения во всем столбце.
- Как только мы нажмем CTRL + SHIFT + ENTER, мы увидим открывающую и закрывающую скобки, как показано ниже.
Теперь, используя распределение частот Excel, мы сгруппировали оценки учеников по меткам, которые показывают, что ученики набрали баллы: 0-10, у нас 1 студент, 20-25, у нас 1 студент, 50-55, у нас 1 студент, 95-100. 1 студент, как показано ниже.
Пример № 2
Распределение частот Excel с использованием сводной таблицы
В этом примере мы увидим, как добиться превосходного распределения частоты, используя графические данные с доступной базой данных продаж.
Один из самых простых способов сделать распределение частоты Excel — это использовать сводную таблицу, чтобы мы могли создавать графические данные.
Рассмотрите ниже данные о продажах, которые имеют год распродажи. Теперь мы увидим, как использовать это с помощью сводной таблицы, выполнив следующие шаги.
- Создайте сводную таблицу для вышеуказанных данных о продажах. Для создания сводной таблицы нам нужно перейти в меню вставки и выбрать сводную таблицу.
- Перетащите вниз Продажи в метках строк. Перетащите вниз те же продажи в ценности.
- Убедитесь, что мы выбрали параметр поля поворота для подсчета, чтобы получить числа подсчета продаж, показанные ниже.
- Нажмите на номер продажи ярлыка строки и щелкните правой кнопкой мыши, затем выберите вариант группы.
- Так что мы получим диалоговое окно группировки, как показано ниже:
- Измените номера группировки, начиная с 5000 и заканчивая 18000, и сгруппируйте их по 1000, а затем нажмите кнопку ОК.
- После этого мы получим следующий результат, где данные о продажах были сгруппированы по 1000, как показано ниже:
Мы видим, что данные о продажах были сгруппированы по 1000 со значениями от минимума до максимума, которые могут быть показаны более профессионально при отображении в графическом формате.
- Зайдите в меню вставки и выберите столбчатую диаграмму.
- Таким образом, вывод будет следующим:
Пример № 3
Распределение частот Excel с использованием гистограммы
Используя сводную таблицу, мы сгруппировали данные о продажах, теперь мы увидим, как сделать исторические данные о продажах с помощью распределения частоты в Excel.
Рассмотрим приведенные ниже данные о продажах для создания гистограммы с именем продавца с соответствующими значениями продаж. Где CP — это не что иное, как Consumer Pack, а Tins — это значения диапазона, то есть, сколько банок было распродано конкретным продавцам.
Мы можем найти гистограмму в группе анализа данных в меню данных, которое представляет собой не что иное, как надстройки. Мы увидим, как применить гистограмму, выполнив следующие шаги.
- Зайдите в меню данных справа вверху, мы можем найти анализ данных. Нажмите на анализ данных, который выделен, как показано ниже.
- Так что мы получим диалоговое окно ниже. Выберите опцию «Гистограмма» и нажмите «ОК».
- Мы получим диалоговое окно ниже гистограммы.
- Укажите входной диапазон и диапазон бункера, как показано ниже.
- Убедитесь, что у нас есть галочка для всех опций, таких как метка, совокупный процент, выходной график, а затем нажмите кнопку ОК.
- На графике ниже мы получили вывод, который показывает совокупный процент вместе с частотой.
Мы можем отобразить вышеупомянутую гистограмму более профессионально, отредактировав данные о продажах следующим образом.
- Щелкните правой кнопкой мыши по гистограмме и выберите «Выбор данных».
- Мы получим диалоговое окно для изменения диапазонов. Нажмите на редактировать.
- Так что мы можем редактировать диапазоны, которые нам нужно дать. Измените значение Bins, что нам нужно, чтобы указать диапазон, чтобы мы получили соответствующий результат, а затем нажмите кнопку ОК.
- Таким образом, результат будет таким, как показано ниже.
Что нужно помнить о распределении частот в Excel
- В Excel Распределение частот при группировании мы можем потерять некоторые данные, поэтому убедитесь, что мы группируем должным образом.
- При использовании частотного распределения Excel убедитесь, что классы должны быть одинакового размера с верхним и нижним предельными значениями.
Рекомендуемые статьи
Это было руководство по распределению частот в Excel. Здесь мы обсуждаем формулу частоты в Excel и как сделать распределение частоты в Excel вместе с практическими примерами и загружаемым шаблоном Excel. Вы также можете просмотреть наши другие предлагаемые статьи —
- Руководство к функции ЧАСТОТА В Excel
- Примеры функции GETPIVOTDATA в Excel
- Что нужно знать о сводной таблице в Excel
- Как использовать функцию SUBTOTAL в Excel?
- Примеры макросов Excel
Гистограмма распределения — это инструмент, позволяющий визуально оценить величину и характер разброса данных. Создадим гистограмму для непрерывной случайной величины с помощью встроенных средств MS EXCEL из надстройки Пакет анализа и в ручную с помощью функции
ЧАСТОТА()
и диаграммы.
Гистограмма (frequency histogram) – это
столбиковая диаграмма MS EXCEL
, в каждый столбик представляет собой интервал значений (корзину, карман, class interval, bin, cell), а его высота пропорциональна количеству значений в ней (частоте наблюдений).
Гистограмма поможет визуально оценить распределение набора данных, если:
- в наборе данных как минимум 50 значений;
- ширина интервалов одинакова.
Построим гистограмму для набора данных, в котором содержатся значения
непрерывной случайной величины
. Набор данных (50 значений), а также рассмотренные примеры, можно взять на листе
Гистограмма AT
в
файле примера.
Данные содержатся в диапазоне
А8:А57
.
Примечание
: Для удобства написания формул для диапазона
А8:А57
создан
Именованный диапазон
Исходные_данные.
Построение гистограммы с помощью надстройки
Пакет анализа
Вызвав диалоговое окно
надстройки Пакет анализа
, выберите пункт
Гистограмма
и нажмите ОК.
В появившемся окне необходимо как минимум указать:
входной интервал
и левую верхнюю ячейку
выходного интервала
. После нажатия кнопки
ОК
будут:
- автоматически рассчитаны интервалы значений (карманы);
- подсчитано количество значений из указанного массива данных, попадающих в каждый интервал (построена таблица частот);
-
если поставлена галочка напротив пункта
Вывод графика
, то вместе с таблицей частот будет выведена гистограмма.
Перед тем как анализировать полученный результат —
отсортируйте исходный массив данных
.
Как видно из рисунка, первый интервал включает только одно минимальное значение 113 (точнее, включены все значения меньшие или равные минимальному). Если бы в массиве было 2 или более значения 113, то в первый интервал попало бы соответствующее количество чисел (2 или более).
Второй интервал (отмечен на картинке серым) включает значения больше 113 и меньше или равные 216,428571428571. Можно проверить, что таких значений 11. Предпоследний интервал, от 630,142857142857 (не включая) до 733,571428571429 (включая) содержит 0 значений, т.к. в этом диапазоне значений нет. Последний интервал (со странным названием
Еще
) содержит значения больше 733,571428571429 (не включая). Таких значений всего одно — максимальное значение в массиве (837).
Размеры карманов одинаковы и равны 103,428571428571. Это значение можно получить так:
=(МАКС(
Исходные_данные
)-МИН(
Исходные_данные
))/7
где
Исходные_данные –
именованный диапазон
, содержащий наши данные.
Почему 7? Дело в том, что количество интервалов гистограммы (карманов) зависит от количества данных и для его определения часто используется формула √n, где n – это количество данных в выборке. В нашем случае √n=√50=7,07 (всего 7 полноценных карманов, т.к. первый карман включает только значения равные минимальному).
Примечание
:
Похоже, что инструмент
Гистограмма
для подсчета общего количества интервалов (с учетом первого) использует формулу
=ЦЕЛОЕ(КОРЕНЬ(СЧЕТ(
Исходные_данные
)))+1
Попробуйте, например, сравнить количество интервалов для диапазонов длиной 35 и 36 значений – оно будет отличаться на 1, а у 36 и 48 – будет одинаковым, т.к. функция
ЦЕЛОЕ()
округляет до ближайшего меньшего целого
(ЦЕЛОЕ(КОРЕНЬ(35))=5
, а
ЦЕЛОЕ(КОРЕНЬ(36))=6)
.
Если установить галочку напротив поля
Парето (отсортированная гистограмма)
, то к таблице с частотами будет добавлена таблица с отсортированными по убыванию частотами.
Если установить галочку напротив поля
Интегральный процент
, то к таблице с частотами будет добавлен столбец с
нарастающим итогом
в % от общего количества значений в массиве.
Если выбор количества интервалов или их диапазонов не устраивает, то можно в диалоговом окне указать нужный массив интервалов (если интервал карманов включает текстовый заголовок, то нужно установить галочку напротив поля
Метка
).
Для нашего набора данных установим размер кармана равным 100 и первый карман возьмем равным 150.
В результате получим практически такую же по форме
гистограмму
, что и раньше, но с более красивыми границами интервалов.
Как видно из рисунков выше, надстройка
Пакет анализа
не осуществляет никакого
дополнительного форматирования диаграммы
. Соответственно, вид такой гистограммы оставляет желать лучшего (столбцы диаграммы обычно располагают вплотную для непрерывных величин, кроме того подписи интервалов не информативны). О том, как придать диаграмме более презентабельный вид, покажем в следующем разделе при построении
гистограммы
с помощью функции
ЧАСТОТА()
без использовании надстройки
Пакет анализа
.
Построение гистограммы распределения без использования надстройки Пакет анализа
Порядок действий при построении гистограммы в этом случае следующий:
- определить количество интервалов у гистограммы;
- определить ширину интервала (с учетом округления);
- определить границу первого интервала;
- сформировать таблицу интервалов и рассчитать количество значений, попадающих в каждый интервал (частоту);
- построить гистограмму.
СОВЕТ
: Часто рекомендуют, чтобы границы интервала были на один порядок точнее самих данных и оканчивались на 5. Например, если данные в массиве определены с точностью до десятых: 1,2; 2,3; 5,0; 6,1; 2,1, …, то границы интервалов должны быть округлены до сотых: 1,25-1,35; 1,35-1,45; … Для небольших наборов данных вид гистограммы сильно зависит количества интервалов и их ширины. Это приводит к тому, что сам метод гистограмм, как инструмент
описательной статистики
, может быть применен только для наборов данных состоящих, как минимум, из 50, а лучше из 100 значений.
В наших расчетах для определения количества интервалов мы будем пользоваться формулой
=ЦЕЛОЕ(КОРЕНЬ(n))+1
.
Примечание
: Кроме использованного выше правила (число карманов = √n), используется ряд других эмпирических правил, например, правило Стёрджеса (Sturges): число карманов =1+log2(n). Это обусловлено тем, что например, для n=5000, количество интервалов по формуле √n будет равно 70, а правило Стёрджеса рекомендует более приемлемое количество — 13.
Расчет ширины интервала и таблица интервалов приведены в
файле примера на листе Гистограмма
. Для вычисления количества значений, попадающих в каждый интервал, использована
формула массива
на основе функции
ЧАСТОТА()
. О вводе этой функции см. статью
Функция ЧАСТОТА() — Подсчет ЧИСЛОвых значений в MS EXCEL
.
В MS EXCEL имеется диаграмма типа
Гистограмма с группировкой
, которая обычно используется для построения
Гистограмм распределения
.
В итоге можно добиться вот такого результата.
Примечание
: О построении и настройке макета диаграмм см. статью
Основы построения диаграмм в MS EXCEL
.
Одной из разновидностей гистограмм является
график накопленной частоты
(cumulative frequency plot).
На этом графике каждый столбец представляет собой число значений исходного массива, меньших или равных правой границе соответствующего интервала. Это очень удобно, т.к., например, из графика сразу видно, что 90% значений (45 из 50) меньше чем 495.
СОВЕТ
: О построении
двумерной гистограммы
см. статью
Двумерная гистограмма в MS EXCEL
.
Примечание
: Альтернативой
графику накопленной частоты
может служить
Кривая процентилей
, которая рассмотрена в
статье про Процентили
.
Примечание
: Когда количество значений в выборке недостаточно для построения полноценной
гистограммы
может быть полезна
Блочная диаграмма
(иногда она называется
Диаграмма размаха
или
Ящик с усами
).
Рассмотренные в лабораторной работе 2 распределения вероятностей СВ
опираются на знание закона распределения СВ. Для практических задач такое
знание – редкость. Здесь закон распределения обычно неизвестен, или известен с
точностью до некоторых неизвестных параметров. В частности, невозможно
рассчитать точное значение соответствующих вероятностей, так как нельзя
определить количество общих и благоприятных исходов. Поэтому вводится статистическое
определение вероятности. По этому определению вероятность равна отношению
числа испытаний, в которых событие произошло, к общему числу произведенных
испытаний. Такая вероятность называется статистической частотой.
Связь
между эмпирической функцией распределения и функцией распределения
(теоретической функцией распределения) такая же, как связь между частотой события
и его вероятностью.
Для
построения выборочной функции распределения весь диапазон изменения случайной
величины X (выборки)
разбивают на ряд интервалов (карманов) одинаковой ширины. Число интервалов
обычно выбирают не менее 3 и не более 15. Затем определяют число значений
случайной величины X, попавших
в каждый интервал (абсолютная частота, частота интервалов).
Частота интервалов – число, показывающее сколько раз значения,
относящиеся к каждому интервалу группировки, встречаются в выборке. Поделив эти
числа на общее количество наблюдений (n), находят относительную частоту (частость) попадания
случайной величины X в заданные
интервалы.
По
найденным относительным частотам строят гистограммы выборочных функций
распределения. Гистограмма распределения частот – это графическое
представление выборки, где по оси абсцисс (ОХ) отложены величины интервалов, а
по оси ординат (ОУ) – величины частот, попадающих в данный классовый интервал.
При увеличении до бесконечности размера выборки выборочные функции
распределения превращаются в теоретические: гистограмма превращается в график
плотности распределения.
Накопленная частота интервалов – это число, полученное
последовательным суммированием частот в направлении от первого интервала к
последнему, до того интервала
включительно, для которого определяется накопленная частота.
В Excel для построения выборочных функций распределения
используются специальная функция ЧАСТОТА
и процедура Гистограмма из пакета анализа.
Функция ЧАСТОТА (массив_данных,
двоичный_массив) вычисляет частоты появления случайной величины в интервалах
значений и выводит их как массив цифр, где
•
массив_данных
— это массив или ссылка на
множество данных, для которых
вычисляются частоты;
•
двоичный_массив
— это массив интервалов, по
которым группируются значения выборки.
Процедура
Гистограмма из Пакета анализа выводит
результаты выборочного распределения в виде таблицы и графика. Параметры диалогового окна Гистограмма:
•
Входной диапазон — диапазон исследуемых данных
(выборка);
•
Интервал карманов — диапазон ячеек или набор граничных
значений, определяющих выбранные интервалы (карманы). Эти значения должны быть
введены в возрастающем порядке. Если
диапазон карманов не был введен, то набор интервалов, равномерно распределенных между минимальным и
максимальным значениями данных, будет создан
автоматически.
•
выходной диапазон предназначен для ввода ссылки на левую верхнюю ячейку выходного диапазона.
•
переключатель
Интегральный процент позволяет установить режим включения в
гистограмму графика интегральных
процентов.
•
переключатель
Вывод графика позволяет установить режим автоматического создания встроенной диаграммы на листе, содержащем
выходной диапазон.
Пример 1. Построить эмпирическое распределение веса
студентов в килограммах для следующей
выборки: 64, 57, 63, 62, 58, 61, 63, 70, 60, 61, 65, 62, 62, 40, 64, 61, 59, 59, 63, 61.
Решение
1. В ячейку А1 введите слово Наблюдения,
а в диапазон А2:А21 — значения веса
студентов (см. рис. 1).
2.
В
ячейку В1 введите названия интервалов Вес, кг. В диапазон В2:В8 введите
граничные значения интервалов (40, 45,
50, 55, 60, 65, 70).
3.
Введите
заголовки создаваемой таблицы: в ячейки С1 — Абсолютные частоты, в ячейки D1 — Относительные
частоты, в ячейки E1 — Накопленные частоты.(см. рис. 1).
4.
С
помощью функции Частота заполните столбец абсолютных частот, для этого
выделите блок ячеек С2:С8. С
панели инструментов Стандартная
вызовите Мастер функций (кнопка fx). В появившемся диалоговом окне
выберите категорию Статистические и функцию
ЧАСТОТА, после чего нажмите кнопку ОК. Указателем мыши в рабочее поле Массив_данных
введите диапазон данных наблюдений (А2:А8). В рабочее поле Двоичный_массив
мышью введите диапазон интервалов (В2:В8). Слева на клавиатуре последовательно нажмите комбинацию клавиш Ctrl+Shift+Enter. В столбце C должен появиться массив абсолютных частот (см. рис.1).
5.
В
ячейке C9 найдите общее количество
наблюдений. Активизируйте ячейку С9, на
панели инструментов Стандартная нажмите кнопку Автосумма.
Убедитесь, что диапазон суммирования указан правильно и нажмите клавишу Enter.
6.
Заполните столбец относительных частот. В ячейку введите формулу
для вычисления относительной частоты: =C2/$C$9.
Нажмите клавишу Enter. Протягиванием (за правый
нижний угол при нажатой левой кнопке мыши) скопируйте введенную формулу в диапазон и получите массив относительных частот.
7.
Заполните
столбец накопленных частот. В ячейку D2 скопируйте значение относительной
частоты из ячейки E2. В ячейку D3 введите формулу: =E2+D3. Нажмите клавишу Enter. Протягиванием (за правый нижний угол при нажатой левой кнопке мыши) скопируйте введенную формулу
в диапазон D3:D8. Получим массив накопленных
частот.
Рис. 1. Результат вычислений из
примера 1
8.
Постройте диаграмму относительных и накопленных частот. Щелчком указателя
мыши по кнопке на панели инструментов вызовите Мастер диаграмм. В появившемся диалоговом окне выберите закладку Нестандартные
и тип диаграммы График/гистограмма. После
редактирования диаграмма будет иметь такой вид, как на рис. 2.
Рис. 2
Диаграмма относительных и накопленных частот из примера 1
Задания для самостоятельной работы
1. Для данных из примера 1 построить выборочные функции распределения, воспользовавшись процедурой Гистограмма из пакета Анализа.
2. Построить выборочные функции распределения
(относительные и накопленные частоты) для роста
в см. 20 студентов: 181, 169, 178, 178, 171, 179, 172, 181, 179, 168, 174, 167, 169, 171, 179, 181, 181,
183, 172, 176.
3. Найдите распределение по абсолютным частотам для
следующих результатов тестирования в
баллах: 79, 85, 78, 85, 83, 81, 95, 88, 97, 85 (используйте границы интервалов 70, 80, 90).
4. Рассмотрим любой из критериев оценки качеств педагога-профессионала,
например, «успешное решение задач обучения и воспитания». Ответ на этот вопрос
анкеты типа «да», «нет» достаточно груб. Чтобы уменьшить относительную ошибку
такого измерения, необходимо увеличить число возможных ответов на конкретный
критериальный вопрос. В табл. 1 представлены возможные варианты ответов.
Обозначим
этот параметр через х. Тогда в процессе ответа на вопрос величина х
примет дискретное значение х, принадлежащее определенному интервалу значений.
Поставим в соответствие каждому из ответов определенное числовое значение
параметра х (см. табл. 1).
Табл. 1 Критериальный вопрос: успешное решение задач обучения и воспитания
|
№ п/п |
Варианты ответов |
Х |
|
1 |
Абсолютно неуспешно |
0,1 |
|
2 |
Неуспешно |
0,2 |
|
3 |
Успешно в очень |
0,3 |
|
4 |
В определенной |
0,4 |
|
5 |
В среднем успешно, |
0,5 |
|
6 |
Успешно с |
0,6 |
|
7 |
Успешно, но |
0,7 |
|
8 |
Достаточно успешно |
0,8 |
|
9 |
Очень успешно |
0,9 |
|
10 |
Абсолютно успешно |
1 |
При проведении анкетирования в каждой отдельной
анкете параметр х принимает случайное значение, но только в пределах числового
интервала от 0,1 до 1.
Тогда в результате измерений мы получаем
неранжированный ряд случайных значений (см. табл. 2).
Таблица 2.
Результаты опроса ста учителей
Сгруппируйте полученную выборку, рассчитайте среднее
значение выборки, стандартное отклонение, абсолютную и относительную частоту
появления параметра, а также постройте график плотности вероятности f(x)=
где
W(x) – относительная частота наступления события;
— стандартное
отклонение;
=3,14.
Постройте график функции f(x) и сравните его с
нормальным распределением Гаусса.
Решение математических задач
средствами Excel: Практикум/ В.Я. Гельман. – СПб.: Питер, 2003 — с. 168-172
При анализе данных периодически возникает задача подсчитать количество значений, попадающих в заданные интервалы «от и до» (в статистике их называют «карманы»). Например, подсчитать количество звонков определенной длительности при разборе статистики по мобильной связи, чтобы понимать какой тариф для нас выгоднее:
Для решения подобной задачи можно воспользоваться функцией ЧАСТОТА (FREQUENCY). Ее синтаксис прост:
=ЧАСТОТА(Данные; Карманы)
где
- Карманы — диапазон с границами интервалов, попадание в которые нас интересует
- Данные — диапазон с исходными числовыми значениями, которые мы анализируем
Обратите внимание, что эта функция игнорирует пустые ячейки и ячейки с текстом, т.е. работает только с числами.
Для использования функции ЧАСТОТА нужно:
- заранее подготовить ячейки с интересующими нас интервалами-карманами (желтые F2:F5 в нашем примере)
- выделить пустой диапазон ячеек (G2:G6) по размеру на одну ячейку больший, чем диапазон карманов (F2:F5)
- ввести функцию ЧАСТОТА и нажать в конце сочетание Ctrl+Shift+Enter, т.е. ввести ее как формулу массива
Во всех предварительно выделенных ячейках посчитается количество попаданий в заданные интервалы. Само-собой, для реализации подобной задачи можно использовать и другие способы (функцию СЧЁТЕСЛИ, сводные таблицы и т.д.), но этот вариант весьма хорош.
Кроме того, с помощью функции ЧАСТОТА можно легко подсчитывать количество уникальных чисел в наборе с помощью простой формулы массива:
Ссылки по теме
- Как подсчитать количество уникальных элементов в списке
- Как сделать список без повторений
- Частотный анализ данных с помощью сводных таблиц и формул
Перейти к содержанию
На чтение 2 мин. Просмотров 220 Опубликовано 22.05.2021
Распределение частот – это сводка подсчета того, как часто значения встречаются в диапазоне значений, например, на следующем снимке экрана, столбец B содержит числа баллов, D2: E6 – это диапазоны оценок, которые вы указали сами, а столбец Частота получает значение, основанное на диапазонах. В этой статье я расскажу о том, как рассчитать частотное распределение на основе определенных вами диапазонов.
Рассчитайте частотное распределение с помощью функции частоты в Excel
Найти и вернуть наиболее частое/частое значение в списке:
С помощью Kutools for Excel Функция “Найти наиболее распространенное значение ” позволяет быстро найти и извлечь наиболее часто встречающееся значение, которое отображается в столбце Excel без каких-либо формул.
Kutools for Excel : с более чем 200 удобными надстройками Excel, бесплатно попробуйте без ограничений в течение 60 дней. Загрузите и бесплатную пробную версию прямо сейчас!
->
Вычислить частотное распределение с помощью функции частоты в Excel
Вычислить частотное распределение с помощью функции частоты в Excel Фактически, есть встроенная функция частоты в Excel, который может помочь вам рассчитать, как часто значения встречаются в указанном вами диапазоне, сделайте следующее:
1 . После указания полос баллов выберите ячейки рядом с полосами, в которые вы хотите поместить результат распределения частот, см. Снимок экрана:
2 . Затем введите эту формулу: = ЧАСТОТА ($ B $ 2: $ B $ 16, $ E $ 2: $ E $ 6) ( B2: B16 – это значения списка, которые вы хотите использовать, – это диапазоны, которые вы указали) в строке формул, см. снимок экрана :
3 . Затем нажмите одновременно клавиши Ctrl + Shift + Enter , и все частотные распределения для каждого диапазона будут рассчитаны одновременно, см. Снимок экрана:
Excel 2016 обзавелся новыми типами графиков. Причем, это не какие-нибудь дизайнерские новшества, а самые настоящие статистические диаграммы.
Так, «ящик с усами» применяется для анализа выборки. Диаграмма Парето пригодится при анализе вклада отдельных элементов в общую сумму. В этой заметке рассмотрим еще одну новую диаграмму из Excel 2016 – гистограмму частот.
На первый взгляд и в более ранних версиях Excel можно изобразить частоты с помощью диаграмм. Можно, но для этого предварительно необходимо числовые данные сгруппировать. То есть для каждой категории (интервала, группы, года и т.д.) нужно посчитать частоту. Теперь появилась возможность изобразить распределение данных буквально в один клик без предварительных расчетов и группировок.
Строится такая диаграмма в один клик. Выделяем ряд данных и нажимаем кнопку гистограммы частот.
Собственно, все. Тут же появляется соответствующая диаграмма.
Возникает вопрос: как Excel делит данные на интервалы? Справка Excel говорит, что с помощью формулы.
Количество интервалов получается достаточным для того, чтобы визуально прикинуть, каков характер распределения анализируемых данных.
Интервалы легко перестроить под свои потребности. Можно, например, задать нижнюю и верхнюю границу, за пределами которых данные будут объединены в один интервал.
При выборе опции выхода за нижнюю и верхнюю границы, судя по той же справке, их значения рассчитываются, как расстояние ±3σ от средней арифметической.
Однако рассчитываемые автоматически значения легко изменить в окне настроек.
Это был пример, когда данные разбиваются на интервалы. Такой вариант группировки установлен по умолчанию (см. окно параметров настройки оси выше).
Распределение частот можно получить и по имеющимся категориям (должен быть указан соответствующий столбец). Выбираем в настройках «По категориям» и получаем новые частоты.
Проведем эксперимент. С помощью функции СЛУЧМЕЖДУ смоделируем равномерно распределенную выборку в пределах, скажем, от 0 до 200. Пусть выборка состоит из 100 значений. Теперь изобразим гистограмму частот.
Как видно, частоты примерно одинаковы.
А теперь смоделируем нормальную выборку, со средней 100 и стандартным отклонением 30.
Отчетлива видна характерная конфигурация нормального распределения.
Поделиться в социальных сетях:
Функция ЧАСТОТА используется для определения количества вхождения определенных величин в заданный интервал и возвращает данные в виде массива значений. Используя функцию ЧАСТОТА, мы узнаем, как посчитать частоту в Excel.
Пример использования функции ЧАСТОТА в Excel
Пример 1. Студенты одной из групп в университете сдали экзамен по физике. При оценке качества сдачи экзамена используется 100-бальная система. Для определения окончательной оценки по 5-бальной системе используют следующие критерии:
- От 0 до 50 баллов – экзамен не сдан.
- От 51 до 65 баллов – оценка 3.
- От 66 до 85 баллов – оценка 4.
- Свыше 86 баллов – оценка 5.
Для статистики необходимо определить, сколько студентов получили 5, 4, 3 баллов и количество тех, кому не удалось сдать экзамен.
Внесем данные в таблицу:
Для решения выделим области из 4 ячеек и введем следующую функцию:
Описание аргументов:
- B3:B20 – массив данных об оценках студентов;
- D3:D5 – массив критериев нахождения частоты вхождений в массиве данных об оценках.
Выделяем диапазон F3:F6 жмем сначала клавишу F2, а потом комбинацию клавиш Ctrl+Shift+Enter, чтобы функция ЧАСТОТА была выполнена в массиве. Подтверждением того что все сделано правильно будут служить фигурные скобки {} в строке формул по краям. Это значит, что формула выполняется в массиве. В результате получим:
То есть, 6 студентов не сдали экзамен, оценки 3, 4 и 5 получили 3, 4 и 5 студентов соответственно.
Пример определения вероятности используя функцию ЧАСТОТА в Excel
Пример 2. Известно то, что если существует только два возможных варианта развития событий, вероятности первого и второго равны 0,5 соответственно. Например, вероятности выпадения «орла» или «решки» у подброшенной монетки равны ½ и ½ (если пренебречь возможностью падения монетки на ребро). Аналогичное расчетное распределение вероятностей характерно для следующей функции СЛУЧМЕЖДУ(1;2), которая возвращает случайное число в интервале от 1 до 2. Было проведено 20 вычислений с использованием данной функции. Определить фактические вероятности появления чисел 1 и 2 соответственно на основании полученных результатов.
Заполним исходную таблицу случайными значениями от 1-го до 2-ух:
Для определения случайных значений в исходной таблице была использована специальная функция:
=СЛУЧМЕЖДУ(1;2)
Для определения количества сгенерированных 1 и 2 используем функцию:
=ЧАСТОТА(A2:A21;1)
Описание аргументов:
- A2:A21 – массив сгенерированных функцией =СЛУЧМЕЖДУ(1;2) значений;
- 1 – критерий поиска (функция ЧАСТОТА ищет значения от 0 до 1 включительно и значения >1).
В результате получим:
Вычислим вероятности, разделив количество событий каждого типа на общее их число:
Для подсчета количества событий используем функцию =СЧЁТ($A$2:$A$21). Или можно просто разделить на значение 20. Если заранее не известно количество событий и размер диапазона со случайными значениями, тогда можно использовать в аргументах функции СЧЁТ ссылку на целый столбец: =СЧЁТ(A:A). Таким образом будет автоматически подсчитывается количество чисел в столбце A.
Вероятности выпадения «1» и «2» — 0,45 и 0,55 соответственно. Не забудьте присвоить ячейкам E2:E3 процентный формат для отображения их значений в процентах: 45% и 55%.
Теперь воспользуемся более сложной формулой для вычисления максимальной частоты повторов:
Формулы в ячейках F2 и F3 отличаются только одним лишь числом после оператора сравнения «не равно»: <>1 и <>2.
Интересный факт! С помощью данной формулы можно легко проверить почему не работает стратегия удвоения ставок в рулетке казино. Данную стратегию управления ставками в азартных играх называют еще Мартингейл. Дело в том, что количество случайных повторов подряд может достигать 18-ти раз и более, то есть восемнадцать раз подряд красные или черные. Например, если ставку в 2 доллара 18 раз удваивать – это уже более пол миллиона долларов «просадки». Это уже провал по любым техникам планирования рисков. Так же следует учитывать, что кроме «черные» и «красные» иногда выпадает еще и «зеро», что окончательно уничтожает все шансы. Так же интересно, что сумма всех чисел в рулетке от 0 до 36 равна 666.
Как посчитать неповторяющиеся значения в Excel?
Пример 3. Определить количество уникальных вхождений в массив числовых данных, то есть не повторяющихся значений.
Исходная таблица:
Определим искомую величину с помощью формулы:
В данном случае функция ЧАСТОТА выполняет проверку наличия каждого из элементов массива данных в этом же массиве данных (оба аргумента совпадают). С помощью функции ЕСЛИ задано условие, которое имеет следующий смысл:
- Если искомый элемент содержится в диапазоне значений, вместо фактического количества вхождений будет возвращено 1;
- Если искомого элемента нет – будет возвращен 0 (нуль).
Полученное значение (количество единиц) суммируется.
В результате получим:
То есть, в указанном массиве содержится 8 уникальных значений.
Скачать пример функции ЧАСТОТА в Excel
Функция ЧАСТОТА в Excel и особенности ее синтаксиса
Данная функция имеет следующую синтаксическую запись:
Описание аргументов функции (оба являются обязательными для заполнения):
- массив_данных – данные в форме массива либо ссылка на диапазон значений, для которых необходимо определить частоты.
- массив_интервалов — данные в формате массива либо ссылка не множество значений, в которые группируются значения первого аргумента данной функции.
Примечания 1:
- Если в качестве аргумента массив_интервалов был передан пустой массив или ссылка на диапазон пустых значений, результатом выполнения функции ЧАСТОТА будет являться число элементов, входящих диапазон данных, которые были переданы в качестве первого аргумента.
- При использовании функции ЧАСТОТА в качестве обычной функции Excel будет возвращено единственное значение, соответствующее первому вхождению в массив_интервалов (то есть, первому критерию поиска частоты вхождения).
- Массив возвращаемых данной функцией элементов содержит на один элемент больше, чем количество элементов, содержащихся в массив_интервалов. Это происходит потому, что функция ЧАСТОТА вычисляет также количество вхождений величин, значения которых превышают верхнюю границу интервалов. Например, в наборе данных 2,7, 10, 13, 18, 4, 33, 26 необходимо найти количество вхождений величин из диапазонов от 1 до 10, от 11 до 20, от 21 до 30 и более 30. Массив интервалов должен содержать только их граничные значения, то есть 10, 20 и 30. Функция может быть записана в следующем виде: =ЧАСТОТА({2;7;10;13;18;4;33;26};{10;20;30}), а результатом ее выполнения будет столбец из четырех ячеек, которые содержат следующие значения: 4,2, 1, 1. Последнее значение соответствует количеству вхождений чисел > 30 в массив_данных. Такое число действительно является единственным – это 33.
- Если в состав массив_данных входят ячейки, содержащие пустые значения или текст, они будут пропущены функцией ЧАСТОТА в процессе вычислений.
Примечания 2:
- Функция может использоваться для выполнения статистического анализа, например, с целью определения наиболее востребованных для покупателей наименований продукции.
- Данная функция должна быть использована как формула массива, поскольку возвращаемые ей данные имеют форму массива. Для выполнения обычных формул после их ввода необходимо нажать кнопку Enter. В данном случае требуется использовать комбинацию клавиш Ctrl+Shift+Enter.
=ЧАСТОТА(массив_данных;массив_интервалов)