Автоматизированный априорный анализ статистической совокупности в среде MS Excel

 

Этап 3. Расчет описательных параметров выборочной совокупности

              с использованием инструмента   Мастер функций

Алгоритм 3.1. Расчет выборочного стандартного отклонения σ_n для признака

              Среднегодовая стоимость основных производственных фондов

1. Установить курсор в ячейку В83;
2. Вставка =>Функция;
3. Статистические =>СТАНДОТКЛОНП=>ОК;
4. Число 1<= диапазон ячеек табл.1, содержащих значения первого признака.

Алгоритм 3.2. Расчет выборочного стандартного отклонения σ_n для признака

Выпуск  продукции

1. Установить курсор в ячейку D83;
2. Вставка =>Функция;
3. Статистические =>СТАНДОТКЛОНП=>ОК;
4. Число 1<= диапазон ячеек табл.1, содержащих значения второго признака.

Алгоритм 3.3. Расчет выборочной дисперсии σ²_n для признака

        Среднегодовая стоимость основных производственных фондов

 

1. Установить курсор в ячейку В84;
2. Вставка =>Функция;
3. Статистические =>ДИСПР=>ОК;
4. Число1<= диапазон ячеек табл.1, содержащий значения первого признака.

Алгоритм 3.4. Расчет выборочной дисперсии σ²_n по признаку

Выпуск  продукции

1. Установить курсор в ячейку D84;
2. Вставка =>Функция;
3. Статистические => ДИСПР=>ОК;
4. Число1<= диапазон ячеек табл.1, содержащих значения второго признака.

Алгоритм 3.5. Расчет выборочного среднего линейного  отклонения по

                  признаку

Среднегодовая стоимость основных производственных фондов

1. Установить курсор в ячейку В85;
2. Вставка =>Функция;
3. Статистические =>СРОТКЛ=>ОК;
4. Число1<= диапазон ячеек табл.1, содержащих значения первого признака.

Алгоритм 3.6. Расчет выборочного среднего линейного  отклонения          по признаку

Выпуск  продукции

1. Установить курсор в ячейку D85;
2. Вставка =>Функция;
3. Статистические => СРОТКЛ =>ОК;
4. Число1<= диапазон ячеек табл.1, содержащих значения второго признака.

Алгоритм 3.7. Расчет коэффициента вариации по признаку

         Среднегодовая стоимость основных производственных фондов

1. Установить курсор в ячейку В86;
2. В активизированную ячейку ввести формулу =B83/B48*100.

Алгоритм 3.8. Расчет коэффициента вариации по признаку

Выпуск  продукции

1. Установить курсор в ячейку D86;
2. В активизированную ячейку ввести формулу =D83/D48*100.

Алгоритм 3.9. Расчет выборочного коэффициента асимметрии Пирсона As_п по  признаку

Среднегодовая стоимость основных производственных фондов

1. Установить курсор в ячейку В87;
2. В активизированную ячейку ввести формулу =(B48-B51)/B83.

Алгоритм 3.10. Расчет выборочного коэффициента асимметрии Пирсона As_п по признаку

Выпуск  продукции

1. Установить курсор в ячейку D87;
2. В активизированную ячейку ввести формулу =(D48-D51)/D83

 

В результате работы алгоритмов 3.1 - 3.10 Excel осуществляет вывод значений выборочных параметров σ_n, σ²_n, , и Аs_n в соответствующие ячейки рабочего листа Табл.5.

 

			Таблица 5
	Выборочные показатели вариации и асимметрии
По столбцу "Среднегодовая  стоимость основных производственных фондов, млн.руб."		По столбцу "Выпуск продукции, млн.руб"
Стандартное отклонение	142,6856685	Стандартное отклонение	170,2117505
Дисперсия	20359,2	Дисперсия	28972,04
Среднее линейное отклонение	114,8	Среднее линейное отклонение	131,12
Коэффициент вариации, %	17,1910444	Коэффициент вариации, %	20,50743982
Коэффициент асимметрии	-0,21025237	Коэффициент асимметрии	0,015275091

 

Задание 3

       Построение  и графическое изображение интервального  вариационного ряда распределения  единиц совокупности  по признаку

 Среднегодовая стоимость основных производственных фондов

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

     Выполнение Задания 3 заключается  в решении двух статистических  задач:

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

2. Построение  гистограммы и кумуляты сформированного  интервального ряда.

Алгоритмы выполнения Задания 3

 

     Выполнение задания  осуществляется в три этапа:

1. Построение промежуточной таблицы.
2. Генерация выходной таблицы и графиков.
3. Приведение выходной таблицы и диаграммы к виду, принятому в статистике.

 

Этап 1. Построение промежуточной таблицы.

Алгоритм 1.1. Расчет нижних границ интервалов

1. Сервис =>Анализ данных =>Гистограмма =>ОК;
2. Входной интервал<= диапазон ячеек В4:В33;
3. Интервал карманов оставить незаполненным;
4. Выходной интервал <= адрес заголовка первого столбца первичной промежуточной табл.6 ( А90 ).
5. OK;

Алгоритм 1.2.  Переход от нижних границ к верхним

1. Выделить курсором верхнюю левую ячейку табл.6 и нажать клавишу [Delete];
2. Ввести в ячейку с именем "Еще" значение х_max первого признака из табл.3-Описательные статистики.

	Таблица 6		Таблица 6
Карман	Частота	Карман	Частота
530	1		1
650	3	650	3
770	5	770	5
890	11	890	11
1010	7	1010	7
Еще	3	1130	3

 

Рис. 2. Схема перехода от нижних границ интервалов к верхним

 

Этап 2. Генерация выходной таблицы и графиков

Алгоритм 2.1. Построение выходной таблицы, столбиковой диаграммы  и кумуляты.

1. Сервис=>Анализ данных=>Гистограмма=>ОК;
2. Входной интервал<= диапазон ячеек В4:В33;
3. Интервал карманов <= диапазон карманов итоговой промежуточной табл.6 с верхними границами  ( А92:А96);
4. Выходной интервал <= адрес заголовка первого столбца выходной  табл.7     ( А101 );
5. Интегральный процент - Активизировать;
6. Вывод графика  - Активизировать;
7. ОК;
8. При появлении сообщения о наложении данных  - ОК.

Выходная  таблица имеет следующий вид:

		Таблица 7
Интервальный ряд  распределения предприятий  по стоимости основных производственных фондов
Группы предприятий  по стоимости основных фондов	Число преприятий в группе	Накопленная частость группы
530-650	4	13,33%
650-770	5	30,00%
770-890	11	66,67%
890-1010	7	90,00%
1010-1130	3	100,00%
итого	30	100,00%

 

 

Этап 3. Приведение выходной таблицы и диаграммы к виду, принятому в статистике.

Алгоритм 3.1. Преобразование выходной таблицы в результативную.

 

1. Заменить названия столбцов выходной табл.7;
2. Строки первого столбца привести к виду «нижняя граница интервала  -  верхняя граница интервала», учитывая совпадение верхних границ предыдущего интервала с нижней границей последующего интервала;
3. Строку с именем «Еще» выделить мышью и очистить, нажав  клавишу [Delete];
4. Добавить и заполнить строку с именем «Итого».

		Таблица 7
Интервальный ряд  распределения предприятий  по стоимости основных производственных фондов
Группы предприятий  по стоимости основных фондов	Число преприятий в группе	Накопленная частость группы
530-650	4	13,33%
650-770	5	30,00%
770-890	11	66,67%
890-1010	7	90,00%
1010-1130	3	100,00%
итого	30	100,00%

 

Таблица 7. Интегральный ряд распределения  после редактирования

 

Алгоритм 3.2. Преобразование столбиковой диаграммы в гистограмму.

 

1. Осуществив «захват мышью», переместить график, расположив его вслед за табл.7;
2. Исключить зазоры, выполнив следующие  действия:

2.1. Нажать  правую кнопку мыши на одном  из столбиков диаграммы.;

2.2. Формат рядов данных=>Параметры;

2.3. Ширина зазора<= 0;

2.4. ОК;

3. Используя  "захват мышью" за угол  поля графика, установить соотношение  ширины  и  высоты фигуры гистограммы в пропорции 1 : 0,62.

 

Рис. 3. Гистограмма и кумулята

 

 

 

 

3. Выводы о статистических  свойствах изучаемой совокупности

 

Статистический анализ выборочной совокупности

 

Задача 1.

Любая изучаемая  совокупность может содержать единицы  наблюдения, значения признаков которых резко выделяются из основной массы значений. Такие нетипичные значения признаков могут быть обусловлены воздействием каких-либо сугубо случайных обстоятельств, возникать в результате ошибок наблюдения или же быть объективно присущими наблюдаемому явлению. В любом случае они являются аномальными для совокупности, так как нарушают статистическую закономерность изучаемого явления. Следовательно, статистическое изучение совокупности без предварительного выявления и анализа возможных аномальных наблюдений может не только исказить значения обобщающих показателей (средней, дисперсии, среднего квадратического отклонения и др.), но и привести к серьезным ошибкам в выводах о статистических свойствах совокупности, сделанных на основе полученных оценок показателей.

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

 

Задача 2.

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