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 вариационный ряд и построим таблицу.
Дискретный вариационный ряд распределения затрат труда на 1 ц зерна
Построим полигон распределения частот с помощью Мастера диаграмм. Выберем точечную диаграмму, соединим полученные точки отрезками, а крайние точки с осью абсцисс в точках, отстоящих от крайних на расстоянии шага.
Р
ис. 1. Полигон распределения сельскохозяйственных предприятий по затратам труда на 1 ц зерна
Рассмотрим построение интервального вариационного ряда.
Рис. 2. Построение интервального вариационного ряда
На листе Excel в ячейку А1 записываем условное обозначение факторного признака – х, в ячейки А2:А31 – значения факторного признака – урожайности озимой пшеницы. Произведём сортировку данных, для чего выделяем диапазон данных, выбираем Данные – Сортировка и в появившемся окне «Сортировка диапазона» указываем «по возрастанию», нажимаем ОК. Данные в ячейках А2:А31 расположатся в ранжированном порядке по возрастанию признака. По формуле Стерджесса определяем количество групп (интервалов). Для вычисления десятичного логарифма lg30 выбираем Мастер функций – Математические – LOG10. В появившемся окне в поле Число записываем число 30, десятичный логарифм которого необходимо найти. Нажатием ОК получаем этот логарифм 1,477121. . Подставляя числовые данные в формулу (1), получим число групп (интервалов) 5,9, округляем до 6. По формуле (2) определяем величину интервалов – шаг с такой же точностью, с которой даны исходные данные (в данном случае с точностью до десятых: (30-20)/6≈1,7. Следовательно, совокупность надо разбить на 6 интервалов. Получаем шаг 1,7. Озаглавим следующие столбцы в Excel словами «Интервалы», «Частоты», «Накопленные частоты», «Середины интервалов». В ячейку В2 вписываем минимальное значение признака Хmin=20, в ячейку В3 формулу =В2+1,7, т.е. минимальное значение плюс шаг. Копируем эту формулу на 5 строк вниз. В результате в этих шести строках (В3:В8) получим верхние границы всех интервалов. Нижними границами интервалов будут данные в соседних верхних ячейках, т.е. для первого интервала нижней границей будет содержание ячейки В2, для второго В3 и для шестого В7.
Для расчёта частот выберем Сервис — Анализ данных – Гистограмма и нажмём ОК. В появившемся окне «Гистограмма» в поле «Входной интервал» копируем исходные данные (ячейки А2:А31), в поле «Интервал карманов» — верхние границы интервалов (ячейки В3:В8), в поле «Выходной интервал» ячейки частот (С3:С8), нажимаем ОК. В ячейки D3:D8 будут записаны частоты для всех шести интервалов. Накопленные частоты подсчитываем нарастающим итогом.
Для построения диаграммы необходимо найти середины интервалов. Для этого вводим формулу расчёта середины интервала: 
, рассчитаем середину первого интервала. Копируем формулу для остальных пяти групп.
Для построения диаграммы выделяем массив частот и середин интервалов. Далее в Мастере диаграмм выбираем вид диаграммы — гистограмму определённого вида. Нажимаем кнопку Далее. В появившемся окне выбираем вкладку Ряд, удаляем ряд 1, а в поле «Подписи оси х» копируем середины интервалов. Нажимаем далее, в появившемся окне выбираем вкладку Заголовки. В поле «ось х (категорий)» вписываем название факторного признака (в данном случае урожайность, ц/га), в поле «Ось у (значений)» вписываем частоты. Нажимаем Далее, Готово. Появится диаграмма, состоящая из столбиков, отделённых друг от друга некоторым зазором. Щёлкаем правой кнопкой мыши на одном из столбиков диаграммы. В раскрывающемся списке элементов щёлкаем по кнопке Формат рядов данных. В появившемся диалоговом окне активизируем вкладку Параметры и в поле Ширина зазора устанавливаем значение 0. Нажимаем ОК, в результате чего гистограмма принимает стандартный вид.
Как построить вариационный ряд в Excel
— дискретным, когда изучаемый признак характеризуется определенным числом (как правило целым).
— интервальным, когда определены границы «от» и «до» для непрерывно варьируемого признака. Интервальный ряд также строят если множество значений дискретно варьируемого признака велико.
Рассмотрим пример построения дискретного вариационного ряда.
Пример 1. Имеются данные о количественном составе 60 семей.
Построить вариационный ряд и полигон распределения
Решение .
Алгоритм построения вариационного ряда:
1) Откроем таблицы Excel.
2) Введем массив данных в диапазон А1:L5. Если вы изучаете документ в электронной форме (в формате Word, например), для этого достаточно выделить таблицу с данными и скопировать ее в буфер, затем выделить ячейку А1 и вставить данные – они автоматически займут подходящий диапазон.
3) Подсчитаем объем выборки n – число выборочных данных, для этого в ячейку В7 введем формулу =СЧЁТ(А1:L5). Заметим, что для того, чтобы в формулу ввести нужный диапазон, необязательно вводить его обозначение с клавиатуры, достаточно его выделить.
4) Определим минимальное и максимальное значение в выборке, введя в ячейку В8 формулу =МИН(А1:L5), и в ячейку В9: =МАКС(А1:L5).
Рис.1.1 Пример 1. Первичная обработка статистических данных в таблицах Excel
5) Далее, подготовим таблицу для построения вариационного ряда, введя названия для столбца интервалов (значений варианты) и столбца частот. В столбец интервалов введем значения признака от минимального (1) до максимального (6), заняв диапазон В12:В17.
6) Выделим столбец частот, введем формулу =ЧАСТОТА(А1:L5;В12:В17) и нажмем сочетание клавиш CTRL+SHIFT+ENTER
Рис.1.2 Пример 1. Построение вариационного ряда
7) Для контроля вычислим сумму частот при помощи функции СУММ (значок функции S в группе «Редактирование» на вкладке «Главная»), вычисленная сумма должна совпасть с ранее вычисленным объемом выборки в ячейке В7.
Построим полигон:
1) выделив полученный диапазон частот, выберем команду «График» на вкладке «Вставка». По умолчанию значениями на горизонтальной оси будут порядковые числа — в нашем случае от 1 до 6, что совпадает со значениями варианты (номерами тарифных разрядов).
2) Название ряда диаграммы «ряд 1» можно либо изменить, воспользовавшись той же опцией «выбрать данные» вкладки «Конструктор», либо просто удалить.
Анализ вариационных рядов в Excel
В реальных социально-экономических системах нельзя проводить активные эксперименты, поэтому данные обычно представляют собой наблюдения за происходящим процессом, например: курс валюты на бирже в течение месяца, урожайность пшеницы в хозяйстве за 30 лет, производительность труда рабочих за смену и т.д. Результаты наблюдений — это в общем случае ряд чисел, расположенных в беспорядке, который для изучения необходимо упорядочить (проранжи- ровать).
Операция, заключающаяся в расположении значений признака по возрастанию, называется ранжированием опытных данных.
После операции ранжирования опытные данные можно сгруппировать так, чтобы в каждой группе признак принимал одно и то же значение, которое называется вариантом (х,). Число элементов в каждой группе называется частотой варианта («,).
Размахом вариации называется число
где хтах — наибольший вариант;
x min — наименьший вариант.
Сумма всех частот равна определенному числу л, которое называется объемом совокупности:
Отношение частоты данного варианта к объему совокупности называется относительной частотой, или частостью, этого варианта:
Последовательность вариант, расположенных в возрастающем порядке, называется вариационным рядом (вариация — изменение).
Вариационные ряды бывают дискретными и непрерывными. Дискретным вариационным рядом называется ранжированная последовательность вариант с соответствующими частотами и (или) частостями.
Пример 1. В результате тестирования группа из 24 человек набрала баллы: 4, 0, 3, 4, 1, 0, 3, 1, 0, 4, 0, 0, 3, 1, 0, 1, 1, 3, 2, 3, 1, 2, 1, 2. Построить дискретный вариационный ряд.
Решение. Проранжируем исходный ряд, подсчитаем частоту и частость вариант: 0, 0, 0, 0, 0, 0, 1, 1, 1, 1, 1, 1, 1, 2, 2, 2, 3, 3, 3, 3, 3, 4, 4, 4.
В результате получим дискретный вариационный ряд (табл. 3.10).
Ранжированный ряд успеваемости
Балл, х.
Число студентов, л,
Относительная частота, А
В Excel проранжируем исходный ряд. Для этого введем все данные в диапазон А1 :А24 и воспользуемся кнопкой Щ (Сортировка по возрастанию).
Подсчитаем частоту и частость вариант. Построим таблицу в диапазоне D2:G7 (рис. 3.13).
Рис. 3.13. Контекстное меню строки состояния
Рассмотрим два варианта подсчета частот:
- 1) выделим диапазон, в котором находятся нули. Щелкнем в нижней правой части окна Excel правой кнопкой мыши и выберем в контекстном меню вид итога, который по умолчанию будет появляться в итоговой строке при выделении произвольного диапазона (см. рис. 3.13) — количество. Таким образом, последовательно выделяя диапазоны с одинаковыми значениями вариант, мы получим все частоты;
- 2) выполним команду Сервис — Анализ данных — Гистограмма. Заполним диалоговое окно в соответствии с рис. 3.14.
Рис. 3.14. Диалоговое окно инструмента пакета анализа «Гистограмма»
В результате получим таблицу с частотами вариантов и соответствующий график (рис. 3.15).
Рис. 3.15. Результаты применения инструмента «Гистограмма)
Найдем объем выборки, заполнив все частоты вариант в диапазоне ЕЗ:Е7, выделим его левой кнопкой мыши и щелкнем по кнопке ? (автосумма).
В ячейку F3 введем формулу «=ЕЗ/$Е$8», за маркер заполнения (крест в правом нижнем углу ячейки) с помощью мыши скопируем до F7 и выберем кнопку автосумма, в результате получим частоты вариантов и их сумму (1). В ячейку G3 введем частоту варианта 0 — цифру 6 (или ссылку на ячейку, ее содержащую — ЕЗ), в ячейку G4 введем формулу «=G3+E4» и скопируем ее до ячейки G7, в результате получим накопленные частоты. Таким образом, мы получили дискретный вариационный ряд. Естественно, частоты необходимо округлить, но таким образом, чтобы их сумма равнялась 1. Для этого выделим левой кнопкой мыши диапазон частот (F3:F7), щелкнув по правой кнопке, откроем контекстное меню и выполним команду Формат ячеек — Числовой — Число знаков 3 — ОК. Преобразовав обозначения, получим дискретный вариационный ряд, представленный в табл. 3.11.
Графическое изображение вариационных рядов в Excel
Excel значительно упрощает построение вариационных рядов.
В Excel открываем файл с данными ПРИМЕР 2.2. Вызываем диалоговое окно Анализ данных, в котором выбирается режим Гистограмма (см. рис. 2.3).
Инструмент «Гистограмма» применяется для вычисления выборочных и интегральных частот попадания данных в указанные интервалы значений. При этом рассчитываются числа попаданий для заданного диапазона ячеек.
Перед нами вкладка Гистограмма. В столбце А — исходные данные измерений. В столбце С — интервал карманов. Заполняем вкладку. Входной интервал — вводим ссылку на ячейки с исходными данными (столбец А). Интервал карманов — вводим ссылку на ячейки, содержащие интервал карманов (столбец Q. В группе Параметры вывода выбираем местоположение выходных данных. Гистограмму можно расположить на том же листе, новом листе в текущей книге или в новой книге.
Установите один или несколько флажков:
Интегральный процент. Отображает суммарные проценты и добавляет в таблицу гистограммы строку суммарных процентов.
Вывод графика. Отображает встроенную таблицу гистограммы.
Нажмите кнопку ОК.
Рис. 2.19. Диалоговое окно гистограммы
В результате работы программы получим результат (рис. 2.20-2.22). Если вы захотите настроить гистограмму, можно изменить подписи и, щелкнув в любом месте гистограммы, использовать кнопки форматирования и макета справа от диаграммы.
Рис. 2.20. Результаты расчетов
Рис. 2.21. Гистограмма
Рис. 2.22. Диалоговое окно форматирования области построения
Используя кнопки форматирования, приводим диаграмму (гистограмму) к виду, показанному на рис. 2.23.
Щелкаем правой кнопкой мыши по полю графика гистограммы и выбираем вкладку Изменить тип диаграммы, на появившейся вкладке (рис. 2.24) выбираем и указываем, какой график, нажимаем ОК.