Обработка статистических данных в Excel

Построение рядов распределения

Любой ряд распределения характеризуется двумя элементами:

     - варианта (х_i) – это отдельные значения признака единиц выборочной совокупности. Для вариационного ряда варианта принимает числовые значения,  для атрибутивного – качественные (например, х=«государственный служащий»);

     - частота (n_i) – число, показывающее, сколько раз встречается то или иное значение признака.

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

Интервальный ряд может строиться как с интервалами равной длины (равноинтервальный ряд) так и с неодинаковыми интервалами, если это диктуется условиями статистического исследования. Например, может рассматриваться ряд распределения доходов населения со следующими интервалами: <5тыс р., 5-10 тыс р., 10-20 тыс.р., 20-50 тыс р., и т.д. Если цель исследования не определяет способ построения интервального ряда, то строится равноинтервальный ряд, число интервалов в котором определяется по формуле Стерджесса:

k=1+3,322lg(n), 

где k – число интервалов, n – объем выборки. (Конечно, формула обычно  дает число дробное, а в качестве числа интервалов выбирается ближайшее целое к полученному число.)

Пример 1. Имеются данные о выбросах загрязняющих веществ из 50 источников:

Составить равноинтервальный ряд, построить гистограмму

Решение

Алгоритм построения равноинтервального ряда:

1) Внесем массив данных в лист Excel, он займет диапазон А1:J5 

2) Подсчитаем объем выборки n – число выборочных данных, для этого в ячейку В7 введем формулу =СЧЁТ(А1:L5). Заметим, что для того, чтобы в формулу ввести нужный диапазон, необязательно вводить его обозначение с клавиатуры, достаточно его выделить.

3) Определим минимальное и максимальное значение в выборке, введя в ячейку В8 формулу =МИН(А1:L5), и в ячейку В9: =МАКС(А1:L5).

4) Поскольку  число интервалов в задаче не задано, вычислим число интервалов k по формуле Стерджесса. Для этого в ячейку В10 введем формулу =1+3,322*LOG10(B7).

Рис.1.1. Пример 1. Построение равноинтервального ряда

5) Полученное значение не является целым, оно равно примерно 6,64. Поскольку при k=7 длина интервалов будет выражаться целым числом (в отличие от случая k=6) выберем k=7, введя это значение в ячейку С10.

6) Длину интервала d вычислим в ячейке В11, введя формулу =(В9-В8)/С10.

7) Зададим массив интервалов, указывая для каждого из 7 интервалов верхнюю границу. Для этого в ячейке Е8 вычислим верхнюю границу первого интервала, введя формулу =B8+B11; в ячейке Е9 верхнюю границу второго интервала, введя формулу =E8+B11. Для вычисления оставшихся значений верхних границ интервалов зафиксируем номер ячейки В11 в введенной формуле при помощи знака $, так что формула в ячейке Е9 примет вид =E8+B$11, и скопируем содержимое ячейки Е9 в ячейки Е10-Е14. Последнее полученное значение равно вычисленному ранее в ячейке В9 максимальному значению в выборке.

Рис.1.2. Пример 1. Построение равноинтервального ряда

8) Теперь заполним массив «карманов» при помощи функции ЧАСТОТА. Поскольку результатом является столбец частот, введение функции следует завершить нажатием сочетания клавиш CTRL+SHIFT+ENTER.

Рис.1.3. Пример 1. Построение равноинтервального ряда

По полученному вариационном ряду построим гистограмму: выделим столбец частот и выберем на вкладке «Вставка» «Гистограмма». Получив гистограмму, изменим в ней подписи горизонтальной оси на значения в диапазоне интервалов, для этого выберем опцию «Выбрать данные»  вкладки «Конструктор». В появившемся окне выберем команду «Изменить» для раздела «Подписи горизонтальной оси» и введем диапазон значений варианты, выделив его «мышью».

Рис.1.4. Пример 1. Построение гистограммы

Рис.1.6. Пример 1. Построение гистограммы

Примечание: можно скачать готовый шаблон построение интервального ряда в Excel, построение гистограммы

По данной теме также смотрят: Построить дискретный вариационный ряд в Excel