Построить статистический ряд в excel

Для случайной выборки объемом n=50 с несовпадающими числами выполнить следующую последовательность действий:

1.Вывести на лист Excel исходные статистические данные.

2. Построить вариационный ряд.

3. Вычислить статистические характеристики.

4. Построить интервальный статистический ряд.

5.Построить гистограмму частот.

6. Составить статистическую функцию распределения статистического ряда.

7. Составить и постоить статистическую функцию распределения группированного статистического ряда.

В качестве примера рассмотрим следующую выборку

Содержание

Порядок выполнения работы
1.Ввод исходных статистических данных.
2. Построение вариационного ряда.
3. Вычисление статистических характеристик.
4. Построение интервального статистического ряда.

Порядок выполнения работы

1.Ввод исходных статистических данных.

Вводим данные в первый столбец таблицы (рис.1).

2. Построение вариационного ряда.

Производим сортировку данных в порядке возрастания. Для этого:

а) выделяем первый столбец;

б) на ленте во вкладке «Данные» выбираем «Сортировка и фильтр» (рис.2)

3. Вычисление статистических характеристик.

На ленте во вкладке «Данные» выбираем «Анализ данных» меню «Описательная статистика» нажимаем ОК.

В пункт «Входной интервал» вводим диапазон ячеек с исходными данными $A$1:$A$50, а в пункте «Выходной интервал» обозначим первую ячейку для записи результаов $C$1. Ставим флажок напротив пункта «Итоговая статистика» и нажимаем ОК.(рис.4)

На рабочем листе появляется таблица с вычисленными значениями числовых характеристик выборки (рис.5)

Здесь «Среднее»означает математическое ожидание выборки, а «Стандартная ошибка» — погрешность ее значения. «Дисперсия выборки» означает исправленную выборочную дисперсию, а «Стандартное отклонение» — исправленное среднее квадратичное отклонение. Положительное значение «Асимметричности» означает, что «длинная часть» кривой лежит правее моды. Отрицательное значение «Эксцесса» означает, что кривая имеет более низкую и «плоскую» вершину, чем нормальная кривая. «Интервал» равен разности Xmax−Xmin. «Сумма» дает результат суммирования всех элементов выборки. «Счет» задает общее число элементов выборки.

4. Построение интервального статистического ряда.

Длину интервала группировки определяем по формуле

Необходимые данные имеем в таблице: Xmax – в ячейке D13, Xmin– в ячейке D12, число элементов выборки n — в ячейке D15.

В ячейку С16 вводим слово «Интервал», в ячейку D16 вводим формулу

в ячейке D16 появится значение числа h. В ячейку C17 вводим букву h. В ячейку D17 вводим формулу

В ячейке D17 получаем округленное до одного знака после запятой значение интерала h.

Проведем формирование интервалов. Для этого от Xmin отступим влево примерно на h/2 и получим начальную точку отсчета. Последовательно прибавляя к ней целое число отрезков h, получим все граничные точки интервалов.

В ячейку F1 вводим формулу

В этой ячейке появляется значение начальной точки отсчета. В ячейку F2 вводим формулу

В этой ячейке появляется значение второй граничной точки первого интервала. Возвращаемся в ячейку F2, ставим курсор в правый нижний угол рамки и двигаем его вниз, не отпуская левую кнопку мыши. В результате такой процедуры (протяжка) столбец F заполнят граничные точки интервалов. Самый нижний интервал должен включать Xmax (рис.6).

Проведем подсчет числа вариант, попавших в каждый интервал, определим относительные частоты и серединные точки этих интервалов.

Для этого на ленте во вкладке «Данные» выбираем «Анализ данных» меню «Гистограмма». (рис. 7)

В пункт «Входной интервал» вводим диапазон ячеек с исходными данными $A$1:$A$50, в пункт «Интервал карманов» — диапазон ячеек с границами интервалов $F$1:$F$9. Отметим точкой пункт «Выходной интервал» и введем в него адрес первой ячейки для записи результатов $Н$1. Появится таблица из двух столбцов с обозначениями «Карман» и «Частота» (рис.8).

Определим относительные частоты р_i * , значения серединных точек интервалов

и высоты прямоугольников

в ячейку J1 введем заголовок «Относительная частота»;

В ячейку J3 введем формулу

и протягиваем её вниз до ячейки J10. В результате к таблице из двух столбцов добавится третий столбец (рис.8). В этой таблице частота появления случайной величины в каждом интервале записана в одной строке с концом интервала;

в ячейку K1 введем заголовок столбца Х*;

в ячейку К3 введем формулу

Протягиваем эту формулу до ячейки К10. В результате в четвертом столбце таблицы (рис.8) появятся значения серединных точек интервалов;

в ячейку L1 введем заголовок столбца У_i;

в ячейку L3 введем формулу

Протягиваем её вниз до ячейки L10.

В результате в пятом столбце таблицы (рис.8) появятся значения У_i.

Описание изменений варьирующего признака осуществляют с помощью рядов распределения.

Статистический ряд распределения — это упорядоченное распределение наблюдаемой статистической совокупности на отдельные группы по конкретному варьирующему признаку.

Статистические ряды могут быть как вариационными, так и атрибутивными (рис. 3.6).

Рис. 3.6. Статистические ряды распределения

Статистические ряды распределения состоят из двух элементов: вариант и частот.

Каждое отдельное значение признака, которое он принимает в статистическом ряду распределения, называется вариантой.

Частоты — это числовые значения, показывающие, как часто встречаются те или иные варианты в ряду распределения. Частоты могут быть выражены в абсолютных величинах, долях единицы или процентах к итоговому значению наблюдаемого признака.

Следовательно, некоторая переменная величина х может принимать различные значения х,, х₂, х₃, . х_п. Каждое из этих значений имеет свою частоту повторений /_р/₂, . /„? Такой

двойной ряд ранжированных значений признака называется вариационным рядом, или рядом распределения.

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

Количество сотрудников фирмы, находящихся в очередном отпуске но месяцам года (данные условные)

Значение признака (варианты)

Повторяемость значений признака (частоты)

Ряды распределения показывают закономерность изменения изучаемого признака. В данном случае мы наблюдаем некоторую закономерность распределения отпусков сотрудников фирмы по месяцам. Нетрудно заметить, что наиболее привлекательным является летний период времени. Чтобы выразить ту или иную закономерность более наглядно, принято изображать вариационные ряды графически в виде гистограммы, полигона частот, кумуляты или огивы.

Гистограмма — это способ графического изображения интервальных распределений вариант при непрерывном варьировании признака. Гистограмма распределения применяется только для изображения интервального вариационного ряда.

Для построения гистограммы воспользуемся данными о распределении месячной заработной платы рабочих, приведенными в табл. 3.8. С этой целью построим оси координат и, выбрав соответствующий масштаб, отложим по оси абсцисс границы заработной платы каждой группы рабочих (величины интервалов). Одному значению ординаты будут соответствовать два значения абсциссы, поэтому на графике отмечается не точка, а линия, соединяющая два значения по оси абсцисс. По оси ординат откладывают частоты, т.е. количество рабочих, чья заработная плата не выходила за пределы установленных границ.

Таким образом, гистограмма представляет собой ряд прямоугольников, основанием которых являются границы интервалов групп, а высоты указывают частоты, соответствующие каждой группе (рис. 3.7). Графически гистограмма показывает распределение вариант при непрерывном варьировании признака. Прямоугольники соответствуют границам заработной платы х, а их высота — количеству рабочих/, получающих данную зарплату.

Рис. 3.7. Гистограмма

Если исходные данные представлены в виде дискретного вариационного ряда, то может быть построен график прерывистого варьирования, представляющий собой ломаную кривую, которая называется полигоном распределения.

Полигон распределения можно построить и для интервального вариационного ряда. Для этого по вертикальной оси откладывают те же частоты, что и при построении гистограммы, а по горизонтальной — середины интервалов.

На рис. 3.8 изображен полигон распределения заработной платы рабочих по соответствующим группам, который представляет собой многоугольную фигуру.

Рис. 3.8. Полигон распределения частот

Для того чтобы полигон распределения частот образовывал замкнутую многоугольную фигуру с горизонтальной осью графика, необходимо ввести дополнительные средние точки на нижнем и верхнем концах распределения для прилегающих несуществующих групп, частоты которых равны нулю (рис. 3.9). Если возникает необходимость, можно, имея полигон распределения частот, построить гистограмму, и наоборот. Кроме того, гистограмма и полигон частот могут быть совмещены на одном графике (см. рис. 3.9).

Рис. 3.9. Гистограмма и полигон распределения частот

Преобразованной формой вариационного ряда является ряд накопленных частот (кумулятивный ряд). Кумулятивный ряд позволяет графически представить данные вариационного ряда в виде кумуляты и огивы. Накопленные частоты получаются в результате последовательного суммирования (кумуляции) всех значений частот, либо от минимального значения варианты к максимальному, либо, наоборот, от максимального к минимальному. Расчет накопленных частот наглядно показан в табл. 3.12.

Приведенные в табл. 3.12 сведения о накопленных частотах позволяют построить огиву и кумуляту. Накопленные частоты получают последовательным суммированием (кумуляцией) всех частот вариационного ряда в направлении от минимальной варианты до конца ряда, и наоборот. Построенный график называется огивой (рис. 3.10). При построении огивы по оси абсцисс

Месячная заработная плата рабочих (данные условные)

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

Будущие экономисты помимо серьезной математической подготовки должны обладать практическими навыками использования математических понятий и методов. Планирование экономики и управление процессами в ней связаны с необходимостью оперативной обработки большого объема различной информации. Для этого необходимо иметь практические навыки в решении задач математической статистики. Рассмотрим одну из основных задач математической статистики о первичной статистической обработке данных и ее решение с помощью среды Excel. Статистические данные заданы в виде выборки . Рассматривается два случая – случайная величина, выборочные значения которой известны, является дискретной и непрерывной.

Исследуемая дискретная случайная величина задана выборкой значений . Требуется:

· построить статистическое распределение выборки (статистический ряд) и гистограмму;

· определить основные выборочные характеристики;

· проверить гипотезу о виде распределения случайной величины с помощью критерия согласия Пирсона.

В диапазон ячеек А1:АN ввести выборочные значения .

2. Построение вариационного ряда.

· Скопировать содержимое ячеек А1:АN в ячейки В1:ВN.

· Упорядочить выборочные значения по неубыванию, используя кнопку сортировки по возрастанию.

3. Построение статистического ряда и гистограммы выборки.

· В ячейки С1:СК ввести различные выборочных значений.

· В меню Сервис выделить строку Анализ данных.

· В открывшемся диалоговом меню выделить процедуру Гистограмма и нажать кнопку OK.

· В поле Входной интервал диалогового окна Гистограмма ввести ссылку на диапазон А1:АN, в котором находятся значения исследуемой выборки.

· В поле Интервал карманов ввести ссылку на диапазон С1:СК, в котором помещены различные выборочные значения.

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

· Установить флажок Вывод графика.

· Составить таблицу статистического ряда по следующему образцу:

частота выборочного значения

накопленная относительная частота

Первые столбцы заполнить копированием, а относительные и накопленные частоты вычислить с использованием формул.

4. Определение выборочных характеристик.

· В меню Сервис выделить строку Анализ данных.

· В открывшемся окне Анализ данных выделить процедуру Описательная статистика.

· На экране появится диалоговое окно Описательная статистика. В поле ввода Входной интервал ввести ссылку на диапазон ячеек, содержащий статистические данные А1:АN.

· Установить флажок Итоговая статистика.

5. Проверка гипотезы о виде распределения случайной величины с помощью критерия согласия Пирсона.

Заполнить таблицу 2:

частота выборочного значения

теоретическая вероятность выборочного значения

теоретическая частота значения

Σ=n

Σ’≈n

Если проверяется гипотеза о распределении Пуассона, то теоретические вероятности вычислить с помощью функции

ПУАССОН.

Если проверяется гипотеза о биномиальном распределении случайной величины, то теоретические вероятности вычислить с помощью функции

БИНОМРАСП,

при этом вероятность успеха в одном испытании определить по формуле , где — выборочное среднее.

В случае других распределений, воспользоваться справкой о статистических функциях библиотеки встроенных функций.

Значение является наблюдаемым значением случайной величины . Число степеней свободы этой случайной величины равно при проверке гипотезы о распределении Пуассона и , если проверяется гипотеза о биномиальном распределении.

Критическое значение случайной величины определить с помощью функции ХИ2РАСП.

Полученное наблюдаемое значение сравнить с :

· если , то гипотеза о виде распределения принимается,

· если , то гипотеза отвергается с уровнем значимости

Исследуемая непрерывная случайная величина задана выборкой значений . Требуется:

· определить основные выборочные характеристики;

· построить статистическое распределение выборки (статистический ряд) и гистограмму;

· проверить гипотезу о виде распределения случайной величины с помощью критерия согласия Пирсона.

В диапазон ячеек А1:АN ввести выборочные значения .

2. Определение выборочных характеристик.

· В меню Сервис выделить строку Анализ данных.

· В открывшемся окне Анализ данных выделить процедуру Описательная статистика.

· Установить флажок Итоговая статистика.

3. Построение статистического ряда и гистограммы выборки.

· В ячейки С1:СК ввести правые концы разбиения отрезка, содержащего выборочные значения. Предварительно вычислить размах выборки (разность между максимальным и минимальным выборочным значением) и длину отрезка разбиения.

· В меню Сервис выделить строку Анализ данных.

· В открывшемся диалоговом меню выделить процедуру Гистограмма и нажать кнопку OK.

· В поле Интервал карманов ввести ссылку на диапазон С1:СК.

· Установить флажок Вывод графика.

· Составить таблицу 3 статистического ряда по следующему образцу:

правый конец интервала разбиения

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

Σ=n

Проверка гипотезы о виде распределения случайной величины с помощью критерия согласия Пирсона.

Заполнить таблицу 4:

правый конец интервала разбиения

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

теоретическая вероятность попадания в интервал

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

Σ=n

Σ’≈n

Если проверяется гипотеза о нормальном распределении случайной величины, то теоретические вероятности вычислить с помощью функции

НОРМРАСП(; среднее; станд. отклонение;1).

Данная функция вычисляет значение функции распределения в точке . Теоретические вероятности находятся как разность значений функции распределения на концах интервала разбиения.

Если проверяется гипотеза о показательном распределении случайной величины, то теоретические вероятности вычислить с помощью функции

ЭКСПРАСП.

Теоретические вероятности находятся как разность значений функции распределения на концах интервала разбиения. В качестве берется оценка .

Значение является наблюдаемым значением случайной величины . Число степеней свободы этой случайной величины равно при проверке гипотезы о показательном распределении и , если проверяется гипотеза о нормальном распределении.

Критическое значение случайной величины определить с помощью функции ХИ2РАСП. Полученное наблюдаемое значение сравниться с :

· если , то гипотеза о виде распределения принимается,

· если , то гипотеза отвергается с уровнем значимости