2.2. Методика построения вариационных рядов и их графиков с помощью электронных таблиц Excel

Построим дискретный вариационный ряд по затратам труда на 1 ц зерна.

Открываем лист Excel, в ячейку А1 записываем условное обозначение результативного признака – у, а в ячейки А2:А31 значения затрат труда на 1 ц зерна. В ячейки В2:В3 введём наименьшее и следующее за ним значения признака 0,7 и 0,8; выделим обе ячейки (В2 и В3). Щёлкнем мышью правый нижний угол выделительной рамки и потянем вниз до значения 1,5 (наибольшее значение признака). В ячейках В2:В10 получим варианты признака в ранжированном порядке. Для определения частот проделаем следующие шаги:

1.Поставим курсор в ячейку С2.

2.Выберем Вставка, Функция.

Выберем в категории Статистические функции функцию Частота и нажмём ОК.

3.В поле данных укажем ячейки А2:А31 а в поле интервалов В2:В10.

4.Нажмём кнопку ОК.

5.Выделим ячейки С2:С10.

6.Нажмём F2, а затем комбинацию клавиш Shift+Ctrl+Enter.

В ячейках С2:С10 появятся частоты.

Вычислим накопленные частоты, которые потребуются для дальнейших расчётов, путём последовательного суммирования локальных частот (нарастающим итогом). Так, первая плюс вторая частоты дают накопленную частоту второго варианта (1+2=3); прибавляя к ней третью частоту получим накопленную частоту третьего варианта (3+4=7) и т.д.

Скопируем полученный в Excel вариационный ряд и построим таблицу.

Таблица 2

Дискретный вариационный ряд распределения затрат труда на 1 ц зерна

Варианты	Частоты	Накопленные частоты
0,7	1	1
0,8	2	3
0,9	4	7
1,0	5	12
1,1	6	18
1,2	4	22
1,3	3	25
1,4	3	28
1,5	2	30

Построим полигон распределения частот с помощью Мастера диаграмм. Выберем точечную диаграмму, соединим полученные точки отрезками, а крайние точки с осью абсцисс в точках, отстоящих от крайних на расстоянии шага.

Рис. 1. Полигон распределения сельскохозяйственных предприятий по затратам труда на 1 ц зерна

В ячейку М57 вводим число единиц наблюдения (в нашем случае n=30) и в ячейках М58 и М59 автоматически получаем число и величину интервалов. В ячейке М60, которая участвует в формуле подсчета верхней границы диапазона, подсчитывается, с помощью математической формулы ОКРВВЕРХ значение величины интервала с округлением в большую сторону (таблица 2). Подсчет числа диапазонов, т.е. определение нижней и верхней границы диапазона осуществляется автоматически. В интервальном ряду распределения (таблица 3) значения вариантов определяются автоматически, а значения частот – подсчитываются визуально, после сортировки данных по возрастанию (диапазон А56:А86), и разбиению данного диапазона на интервалы, с соответствующими границами. В демонстрационном примере все интервалы выделены черными рамками. Если при сортировке данных появится диалоговое окно Обнаружены данные вне указанного диапазона, то в нем необходимо установить переключатель в положение: сортировать в пределах указанного выделения и нажать на кнопку Сортировка…, после чего действовать по описанному выше алгоритму.

Гистограмма строится автоматически. Но, если интервалов будет больше (или меньше), то необходимо добавить или уменьшить диапазон в таблице 2 и, соответственно, изменится количество точек для построения диаграммы. Данная диаграмма построена с помощью Мастера диаграмм по данным таблицы 3, и с использованием Нестандартного типа.

Рассмотрим построение интервального вариационного ряда.

Для построения нам необходимы следующие показатели см. пункт 2.1.

1. Первым шагом будет сортировка данных. Копируем выборку на новый лист. Выделяем диапазон данных и выбираем меню «Данные» → «Сортировка», по возрастанию. Выбираем максимальное и минимальное значение признака. По формуле Стерджесса определяем количество групп. В зависимости от выборки и целей исследования можно определить количество групп аналитически. По формуле (2) определяем шаг интервалов. Теперь мы имеем все необходимые данные для построения интервального ряда.

2. В электронной таблице рядом с исходными данными создадим столбец интервалов (рис. 2). Для этого в ячейке слева ставим минимальное значение данной выборки (min=20). Строкой ниже вписываем формулу =B2+2, т.е. минимальное значение плюс шаг. Копируем эту формулу ещё на 3 строки вниз. В результате в последней строке мы получили максимальное значение выборки или верхнюю границу последнего интервала. Если значение не совпадает, значит, допущена ошибка в расчётах или округлении. Для электронной таблицы интервалы имеют следующий вид (рис. 2).

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

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

3. Для построения диаграммы необходимо найти середины интервалов. Для этого слева от накопленных частот вводим формулу расчёта середины интервала: (см. рис 2). Копируем формулу для всех пяти групп. Далее выбираем меню «Вставка» → «Диаграмма». Выбираем вид диаграммы будет гистограмма. Выделяем область частот и середины интервалов, нажимаем «ОК». Полученную диаграмму нужно редактировать. Щёлкаем правой кнопкой мыши на диаграмме и выбираем «Исходные данные». В появившемся окне выбираем закладку «Ряд». В поле «Значения» выделяем частоты интервального ряда. В поле «Подписи оси Х» выделяем значения середин интервалов. Если в поле «Ряд» два названия ряда, то лишний нужно удалить. Нажимаем «ОК». Далее щелкаем по гистограмме правой кнопкой мыши, выбираем «Формат рядов данных». В закладке параметры ставим нулевое значение ширины зазора.