Как построить вариационный ряд в excel
Порядок действий и необходимые для этого встроенные функции MathCad
1. Считывание файла содержащего выборочные данные.
В ASC-II файле содержатся выборочные данные, необходимо считать их и поместить в матрицу Х.
READPRN() – функция считывает данные структурированного в форме столбцов ASC-II файла и помещает их в матрицу.
Пример:
2. Построение вариационного ряда
sort(X) – функция упорядочивающая выборочные значения Х в порядке возрастания
3. Построение группировочного статистического ряда
1) Функции для нахождения минимального и максимального элементов выборки
min(X) – наименьшее значение элементов Х
max(X) – наибольшее значение элементов Х
2) Функция для построения группировочного статистического ряда hist()
hist(int, Х) – возвращает вектор, представляющий частоты, с которыми величины, содержащиеся в векторе Х, попадают в интервалы, представляемые вектором int.
Элементы в Х и int и должны быть вещественными. Кроме того, элементы int должны быть расположены в порядке возрастания. Возвращаемый результат — вектор, содержащий на один элемент меньше, чем int.
Mathcad интерпретирует int как набор точек, определяющих последовательность интервалов в гистограмме. Значения в int должны быть расположены в порядке возрастания.
Результатом функции hist(int, Х) является вектор, в котором i-элемент содержит число значений в Х, удовлетворяющих условию:
Mathcad игнорирует данные, меньшие, чем первое значение в int, или большие, чем последнее значение в int.
Таким образом, для того чтобы сгруппировать вариационный ряд, необходимо определить вектор int содержащий границы интервалов:
Удобней нумерацию элементов начинать с единицы, т.е. необходимо определить ORIGIN?1, и определить вектор inti , где i = 1 .. n+1. Тогда каждую границу интервала можно задать как
inti := min(x) + h*(i - 1)
4. Построение гистограммы группировочного статистического ряда
Определите середины интервалов Δi
Постройте гистограмму. Для построения гистограммы нужно построить график функции XY-Plot (по оси X отложить середины интервалов, а по оси Y - абсолютные частоты), затем двойным щелчком левой кнопки мышки щелкнуть по графику и войти в диалоговое окно настройки параметров. На второй закладке Traces (след) и в пятом столбце Тип устанавливаем позицию bar
8. Определите относительные частоты и постройте полигон.
Готовый шаблон построение вариационных рядов в Excel на файле.
При работе в Excel для построения вариационных рядов могут быть использованы следующие функции:
- СЧЁТ(массив данных) – для определения объема выборки. Аргументом является диапазон ячеек, в котором находятся выборочные данные.
- СЧЁТЕСЛИ(диапазон; критерий) – может быть использована для построения атрибутивного или вариационного ряда. Аргументами являются диапазон массива выборочных значений признака и критерий – числовое или текстовое значение признака или номер ячейки, в которой оно находится. Результатом является частота появления этого значения в выборке.
- ЧАСТОТА(массив данных; массив интервалов) – для построение вариационного ряда. Аргументами являются диапазон массива выборочных данных и столбец интервалов. Если требуется построить дискретный ряд, то здесь указываются значения варианты, если интервальный – то верхние границы интервалов (их еще называют «карманами»). Поскольку результатом является столбец частот, введение функции следует завершить нажатием сочетания клавиш CTRL+SHIFT+ENTER. Заметим, что задавая массив интервалов при введении функции, последнее значение в нем можно и не указывать – в соответствующий «карман» будут помещены все значения, не попавшие в предыдущие «карманы». Иногда это помогает избежать ошибки, состоящей в том, что наибольшее выборочное значение не помещается автоматически в последний «карман»
Кроме того, для сложных группировок (по нескольким признакам) используют инструмент «сводные таблицы». Для построения атрибутивных и вариационных рядов их тоже можно использовать, но это излишне усложняет задачу. Также для построения вариационного ряда и гистограммы существует процедура «гистограмма» из надстройки «Пакет анализа» (чтобы использовать надстройки в Excel, их нужно сначала загрузить, по умолчанию они не устанавливаются)
Любой ряд распределения характеризуется двумя элементами:
- варианта (хi) – это отдельные значения признака единиц выборочной совокупности. Для вариационного ряда варианта принимает числовые значения, для атрибутивного – качественные (например, х=«государственный служащий»);
- частота (ni) – число, показывающее, сколько раз встречается то или иное значение признака.
Вариационный ряд называется интервальным, когда определены границы «от» и «до» для непрерывно варьируемого признака. Интервальный ряд также строят если множество значений дискретно варьируемого признака велико.
Интервальный ряд может строиться как с интервалами равной длины (равноинтервальный ряд) так и с неодинаковыми интервалами, если это диктуется условиями статистического исследования. Например, может рассматриваться ряд распределения доходов населения со следующими интервалами: <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. Построение равноинтервального ряда
По полученному вариационном ряду построим гистограмму: выделим столбец частот и выберем на вкладке «Вставка» «Гистограмма». Получив гистограмму, изменим в ней подписи горизонтальной оси на значения в диапазоне интервалов, для этого выберем опцию «Выбрать данные» вкладки «Конструктор». В появившемся окне выберем команду «Изменить» для раздела «Подписи горизонтальной оси» и введем диапазон значений варианты, выделив его «мышью».
Для селекционера, например, важно знать, сколько зерен содержит колос выведенного (выводимого) им нового сорта пшеницы. В этой ситуации совершенно ясно, что подсчетом количества зерен только в одном колосе не обойтись. Для определения числа зерен надо воспользоваться достаточно большим количеством колосьев, скажем не менее сотни. Приведем пример математической обработки результатов селекции.
Все поле пшеницы, которое вырастил селекционер можно на математическом языке назвать генеральной совокупностью. Подсчитать количество зерен в колосьях всей генеральной совокупности, очевидно, не представляется возможным, но из всей генеральной совокупности можно выбрать, скажем, сто колосьев и подсчитать количество зерен в них. Эти сто колосьев будут называться выборкой из генеральной совокупности, и они с определенной точностью будут отражать число зерен во всем поле (генеральной совокупности). Чтобы по данным выборки иметь возможность судить обо всей генеральной совокупности, она должна быть отобрана случайно. Так в нашем случае селекционер ни в коем случае не должен отдавать предпочтение тем или иным колосьям (по размерам, внешнему виду, месту произрастания на поле и т.п.) в процессе их выборки. Наиболее целесообразно в данной ситуации совершать выбор колосьев из непрозрачного мешка наугад. У всех выбранных колосьев производится подсчет числа зерен, и результаты фиксируются в виде ряда чисел, с которыми в дальнейшем и предстоит совершать математические действия. В данном примере можно предложить следующую их последовательность.
2.1 Создание вариационного ряда.
Вариационным рядом называется ранжированный в порядке возрастания или убывания ряд вариантов с соответствующими им весами (частотами или частностями). Вариационный ряд будет дискретным, если любые его варианты отличаются на постоянную величину, и непрерывным, если варианты могут отличатся один от другого на сколь угодно малую величину.
Иными словами в вариационном ряду полученные значения располагаются в порядке их увеличения и, если значение повторяется, то рядом записывается число его повторений. Т.е. в данном примере по числу зерен в колосьях ряд может выглядеть так (таб. 2):
Читайте также: