Как построить вариационный ряд в excel
Как построить вариационный ряд в 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.
Построим полигон:
2) Название ряда диаграммы «ряд 1» можно либо изменить, воспользовавшись той же опцией «выбрать данные» вкладки «Конструктор», либо просто удалить.
Рис.1.3. Пример 1. Построение полигона частот
Как построить вариационный ряд в excel
1. Построение вариационного ряда
Нужно выделить ячейки содержащие результаты эксперимента, и воспользоваться операцией сортировка по возрастанию (либо с панели инструментов, либо через главное меню Данные>Сортировка), и в появившемся окне сообщения – «обнаружены данные выходящие за пределы выделенного диапазона» выбрать действие – «сортировать в пределах указанного выделения»
2. Построение группировочного статистического ряда
Добавьте и заполните, введя соответствующие формулы, две таблицы:
Таблица №1
Для вычисления минимального и максимального элемента воспользуйтесь встроенными в Excel статистическими формулами (главное меню – вставка – функция…) МИН и МАКС.
Таблица №2
начало промежутка
конец промежутка
Середина промежутка
Абсолютная частота
Относи-тельная частота
Накопленная частота
Для вычисления абсолютной частоты нужна статистическая функция ЧАСТОТА. При её использовании нужно выполнить следующие действия:
а) выделить весь диапазон ячеек, в которых будет располагаться результат подсчёта частот (т.е. это ячейки под заголовком Абсолютная частота в количестве равном числу промежутков)
b) не снимая выделения, поставить курсор в строку формул и нажать на кнопку вставка функции (чуть левее курсора) или Главное меню – вставка – формула.
с) выбрать функцию ЧАСТОТА
d) ввести Массив_данных – диапазон, содержащий элементы выборки (в файле 2.xls это ячейки) B2:B101
e) ввести Массив_интервалов – диапазон ячеек под заголовком Начало промежутка начиная со строчки, соответствующей промежутку под номером 2 до строчки, соответствующей последнему промежутку.
f) нажмите на кнопку ОК и после закрытия окна для ввода аргументов функции ЧАСТОТА поставьте курсор обратно в строку формул.
g) Нажмите на три кнопки Ctrl+Shift+Enter (сначала на первые две, а потом, не отпуская их, нажмите на Enter).
Примечание. Формулу вычисления абсолютной частоты необходимо ввести как формулу массива. Нажатие комбинации клавиш CTRL+SHIFT+ENTER позволяет определить формулу как формулу массива. Если формула не будет введена как формула массива, единственное значение будет равно 1.
В результате изначально выделенный диапазон будет содержать абсолютные частоты попадания во все промежутка. Проверьте, что сумма всех абсолютных частот равна общему числу элементов выборки (100).
3. Построение гистограммы группировочного статистического ряда
В качестве элементов группировочного ряда надо взять середины промежутков и приведённые частоты.
Для построения гистограммы выполните следующие действия:
Как построить вариационный ряд в excel
Готовый шаблон построение вариационных рядов в Excel на файле.
При работе в Excel для построения вариационных рядов могут быть использованы следующие функции:
— СЧЁТ(массив данных) – для определения объема выборки. Аргументом является диапазон ячеек, в котором находятся выборочные данные.
— СЧЁТЕСЛИ(диапазон; критерий) – может быть использована для построения атрибутивного или вариационного ряда. Аргументами являются диапазон массива выборочных значений признака и критерий – числовое или текстовое значение признака или номер ячейки, в которой оно находится. Результатом является частота появления этого значения в выборке.
— ЧАСТОТА(массив данных; массив интервалов) – для построение вариационного ряда. Аргументами являются диапазон массива выборочных данных и столбец интервалов. Если требуется построить дискретный ряд, то здесь указываются значения варианты, если интервальный – то верхние границы интервалов (их еще называют «карманами»). Поскольку результатом является столбец частот, введение функции следует завершить нажатием сочетания клавиш CTRL+SHIFT+ENTER. Заметим, что задавая массив интервалов при введении функции, последнее значение в нем можно и не указывать – в соответствующий «карман» будут помещены все значения, не попавшие в предыдущие «карманы». Иногда это помогает избежать ошибки, состоящей в том, что наибольшее выборочное значение не помещается автоматически в последний «карман»
Кроме того, для сложных группировок (по нескольким признакам) используют инструмент «сводные таблицы». Для построения атрибутивных и вариационных рядов их тоже можно использовать, но это излишне усложняет задачу. Также для построения вариационного ряда и гистограммы существует процедура «гистограмма» из надстройки «Пакет анализа» (чтобы использовать надстройки в Excel, их нужно сначала загрузить, по умолчанию они не устанавливаются)
Элективный курс. Статистическая обработка экспериментальных данных в электронных таблицах Excel.
Создание вариационного ряда, вариационной кривой, определение среднего значения и среднеквадратичного отклонения.
Для селекционера, например, важно знать, сколько зерен содержит колос выведенного (выводимого) им нового сорта пшеницы. В этой ситуации совершенно ясно, что подсчетом количества зерен только в одном колосе не обойтись. Для определения числа зерен надо воспользоваться достаточно большим количеством колосьев, скажем не менее сотни. Приведем пример математической обработки результатов селекции.
Все поле пшеницы, которое вырастил селекционер можно на математическом языке назвать генеральной совокупностью. Подсчитать количество зерен в колосьях всей генеральной совокупности, очевидно, не представляется возможным, но из всей генеральной совокупности можно выбрать, скажем, сто колосьев и подсчитать количество зерен в них. Эти сто колосьев будут называться выборкой из генеральной совокупности, и они с определенной точностью будут отражать число зерен во всем поле (генеральной совокупности). Чтобы по данным выборки иметь возможность судить обо всей генеральной совокупности, она должна быть отобрана случайно. Так в нашем случае селекционер ни в коем случае не должен отдавать предпочтение тем или иным колосьям (по размерам, внешнему виду, месту произрастания на поле и т.п.) в процессе их выборки. Наиболее целесообразно в данной ситуации совершать выбор колосьев из непрозрачного мешка наугад. У всех выбранных колосьев производится подсчет числа зерен, и результаты фиксируются в виде ряда чисел, с которыми в дальнейшем и предстоит совершать математические действия. В данном примере можно предложить следующую их последовательность.
2.1 Создание вариационного ряда.
Вариационным рядом называется ранжированный в порядке возрастания или убывания ряд вариантов с соответствующими им весами (частотами или частностями). Вариационный ряд будет дискретным, если любые его варианты отличаются на постоянную величину, и непрерывным, если варианты могут отличатся один от другого на сколь угодно малую величину.
Иными словами в вариационном ряду полученные значения располагаются в порядке их увеличения и, если значение повторяется, то рядом записывается число его повторений. Т.е. в данном примере по числу зерен в колосьях ряд может выглядеть так (таб. 2):
mathdialogue
mathdialoguemathdialogue
В математике нет символов для неясных мыслей. Анри Пуанкаре.
Задание 1. Построение вариационного ряда для дискретного признака.
Изучение плодовитости серебристо-черных лисиц, которое дало следующие результаты (число щенков на самку):
1) Для дискретного признака (такова плодовитость) построение вариационного ряда обычно не представляет сложности, достаточно подсчитать встречаемость конкретных значений. Результат оформите в виде таблицы (таблицу назовите):
Частота встречаемости, а
3) Все операции выполните с помощью функций Excel.
Задание 2. Построение вариационного ряда для непрерывных признаков.
Составьте вариационный ряд для непрерывного признака – по данным о весе 63 взрослых землероек (г):
9.2 11.6 8.1 9.1 10.1 9.6 9.3 9.7 9.9 9.9 9.6 7.6 10.0 9.7 8.4 8.6 9.0 8.8 8.8 9.3 11.9 9.3 9.2 10.2 11.2 8.1 10.3 9.2 9.8 9.9 9.3 9.1 9.4 9.6 7.3 8.3 8.8 9.2 8.0 8.6 8.8 9.0 9.5 9.1 8.5 8.8 9.7 11.5 10.5 9.8 10.0 9.4 8.7 10.0 7.9 8.6 8.7 9.1 8.2 9.2 9.4 8.8 9.8
1) рассчитайте пределы размаха изменчивости значений по следующей формуле:
2) изучаемый признак непрерывен (таковы размерно-весовые характеристики), поэтому для построения вариационного ряда сначала весь диапазон изменчивости признака разбейте на серию равных интервалов ( классов вариант)
3) вычислите величину классового интервала, воспользовавшись формулой:
4) Установите границы классов (х min + dx ), центр каждого классового интервала, затем подсчитайте, сколько вариант попало в каждый интервал. Удобнее всего такие подсчёты оформлять в виде таблицы:
Центр классового интервала
5) постройте гистограмму, по аналогии с заданием 1, назовите её.
6) Все операции выполните с помощью функций Excel.
1. На листе Excel данные размещают в столбце (например, в блоке A2:A64). Далее следует определить объем выборки n, введя формулу в ячейку A1 и задав мышью диапазон: A1=СЧЁТ(A2:A64).
2. Рассчитаем пределы размаха изменчивости значений, лимит:
3. Найдем число классов вариационного ряда:
4. Найдем длину интервала dx (допустимо округление):
5. Установим границы классов. Для расчетов на листе Excel удобно к значениям предыдущей границы прибавлять значение ширины интервала: D4=D3+$D$2; далее формулу следует ввести еще в семь ячеек, удобнее всего с помощью приема «автозаполнение»: D5 =D4+0.7 … (блок D5:D11).
6. Вычислить центральное значение признака в каждом классе. На листе Excel вычисления аналогичны рассмотренным в п. 4; исходным берется значение центра первого интервала:
7. можно заполнять таблицу 1.