Лабораторная работа №1

Автоматизированный априорный анализ

статистической совокупности

в среде MS Excel


 

Задание 1. Выявление и удаление из выборки аномальных единиц наблюдения

Задание 2. Оценка описательных статистических параметров совокупности

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


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

1. Подготовительный этап

На данном этапе  студент должен проделать следующие обязательные действия, связанные с организацией индивидуальной рабочей среды:

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

·  сформировать индивидуальный вариант исходных данных и записать его в отчетный файл;


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

Для выполнения расчетов обобщающих показателей и подготовки отчета по лабораторной работе студент формирует персональную папку с именем ФИО, содержащую два файла: расчетный с именем Лаб. хls и отчетный с именем Отчет.doc.

Для этого необходимо выполнить следующие действия:

а) создание персональной папки и рабочего файла:

1.     Загрузить файл с исходными данными и макетами таблиц по следующему алгоритму:

1.     На рабочем столе активизировать Мой компьютер;

2.     В диалоговом окне выбрать файл

Datadisk на “primary” (E:)\Преподаватели\Кафедра статистики\ Априорный анализ\stat_lab.xls

2.     Сохранить файл с исходными данными в качестве рабочего файла по алгоритму:

1.               Файл=>Сохранить как…;

2.     В диалоговом окне Сохранение документа выбрать путь: Datadisk на “primary” (Е:)\Статистика\ Работы студентов\<Специальности>\ <Номер группы>;

3. Выбрать пиктограмму «Создать папку»  и в появившимся диалоговом окне записать в поле «Имя» свою фамилию.

4. Сохранить файл в созданной папке под именем Лаб. хls.

б) создание отчетного файла:

1.                Загрузить файл с форматом отчета из директории

Datadisk на “primary” (E:)\Преподаватели\Кафедра статистики\ Априорный анализ Формат отчета.doc

2.     Сохранить файл по алгоритму:

1.     Файл=>Сохранить как…;

2.     В диалоговом окне Сохранение документа выбрать путь:

 Datadisk на “primary” (Е:)\Статистика\ Работы студентов\ <Специальности>\ <Номер группы>\<Ф.И.О.>;

3.     Сохранить файл в указанной папке под именем Отчет.doc

2.                      Формирование индивидуальных исходных данных

Номер варианта исходных данных соответствует номеру компьютера, на котором работает студент.

2.1. Для того, чтобы сформировать индивидуальные исходные данные, необходимо ввести номер варианта в ячейку E2 созданного рабочего файла Лаб. хls, в результате чего Excel сформирует исходные данные варианта.

Расположение макетов результативных таблиц в рабочем файле на Листе 1 персональной папки студента

A

B

C

D

E

1

Таблица 1

2

Исходные данные

3

Номер предприятия

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

Выпуск продукции, млн. руб.

4




5




6




7




8




9




10




11




12




13




14




15




16




17




18




19




20




21




22




23




24




25




26




27




28




29




30




31




32




33




34




35




36








A

B

C

D

37

Таблица 2

38

Аномальные единицы наблюдения

39

Номер предприятия

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

Выпуск продукции, млн. руб.

40

 

 

 

41

 

 

 

42



43


Таблица 3

44

Описательные статистики

45

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

 

По столбцу "Выпуск продукции", млн. руб.

46

Столбец1

Столбец2

47

 

 

 

48

Среднее

 

Среднее

49

Стандартная ошибка

 

Стандартная ошибка

50

Медиана

 

Медиана

51

Мода

 

Мода

52

Стандартное отклонение

 

Стандартное отклонение

53

Дисперсия выборки

 

Дисперсия выборки

54

Эксцесс

 

Эксцесс

55

Асимметричность

 

Асимметричность

56

Интервал

 

Интервал

57

Минимум

 

Минимум

58

Максимум

 

Максимум

59

Сумма

 

Сумма

60

Счет

 

Счет

61

Уровень надежности(95,4%)

 

Уровень надежности(95,4%)

62



63



64

 

Таблица 4а

65

Предельные ошибки выборки

66

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

 

По столбцу "Выпуск продукции", млн. руб.

 

67

Столбец1

Столбец2

68

 

 

69

Уровень надежности(68,3%)

Уровень надежности(68,3%)

70






71








A

B

C

D

72


Таблица 4б

73

Предельные ошибки выборки

74

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

 

По столбцу "Выпуск продукции", млн. руб.

 

75

Столбец1

Столбец2

76

 

 

77

Уровень надежности(99,7%)

Уровень надежности(99,7%)

78



79



80


Таблица 5

81

Выборочные показатели вариации и асимметрии

82

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

 

По столбцу "Выпуск продукции", млн. руб.

 

83

Стандартное отклонение

СТАНДОТКЛОНП(B4:B33)

Стандартное отклонение

СТАНДОТКЛОНП(C4:C33)

84

Дисперсия

ДИСПР(B4:B33)

Дисперсия

ДИСПР(C4:C33)

85

Среднее линейное отклонение

СРОТКЛ(B4:B33)

Среднее линейное отклонение

СРОТКЛ(C4:C33)

86

Коэффициент вариации, %

B83/B48*100

Коэффициент вариации, %

D83/D48*100

87

Коэффициент асимметрии

(B48-B51)/B83

Коэффициент асимметрии

(D48-D51)/D83

88



89

Таблица 6

90

Карман

Частота

91

 

 

92

 

 

93

 

 

94

 

 

95

 

 

 

96

 

 

97

 

 

98



99

Таблица 7

100

Интервальный ряд распределения предприятий  по стоимости основных производственных фондов

101

Группы предприятий по среднегодовой стоимости основных фондов, млн. руб.

Число предприятий в группе

Накопленная частость группы, %

102

 

 

103

 

 

104

 

 

104

 

 

106

 

 

107

 

 

 

 


2. Этап выполнения статистических расчетов

Задание 1

Выявление и удаление из выборки аномальных единиц наблюдения

Первичные данные выборочной совокупности могут содержать аномальные значения изучаемых признаков (см. п. 2 раздела II – «Теоретические основы лабораторной работы»). Задание 1 заключается в их выявлении и исключении из дальнейшего рассмотрения с целью обеспечения устойчивости данных статистического анализа.

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

Этап 1. Построение диаграммы рассеяния изучаемых признаков

1.     Выделить мышью оба столбца исходных данных в диапазоне B4:C35;

2.     Вставка=>Диаграмма=>Точечная=>Готово.

В результате выполнения этих действий на рабочем листе Excel появится диаграмма рассеяния.

Рис. 1. Аномальные значения признаков на диаграмме рассеяния.

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

1.     Найти на графике точку, соответствующую аномальному наблюдению. Если таких точек нет, то перейти к действию 7, если есть - к действиям 2 - 6.

2.     Подвести курсор к точке на диаграмме рассеяния, соответствующей аномальному наблюдению. После непродолжительного времени возле точки автоматически появится надпись, содержащая значения признаков (X,Y). этого наблюдения.

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

3.    

В исходных данных визуально найти в табл.1 строку, соответствующую выявленной  аномальной единице наблюдения (предприятию) и скопировать её в табл.2.;

4.     Выделить мышью всю адресную строку (вместе с ее номером!) с данными, подлежащими удалению.

Для демонстрационного примера это адресная строка с номером 34, содержащая значения 31, 330 и 53:

А

В

С

D

E

32

29

167

114

33

30

205

133

34

31

330

53

35

5.     Правка=>Удалить.

6.     Выполнять действия 1-5 до полного удаления всех аномальных наблюдений.

7.     Переместить диаграмму рассеяния в область ячеек, начиная с ячейки F4.

Для демонстрационного примера табл.2 имеет следующий вид.

Таблица 2-ДП

Аномальные единицы наблюдения

Номер предприятия

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

млн.руб.

Выпуск продукции, млн. руб.

12

50,00

150,00

31

30,00

53,00

Задание 2

Оценка описательных статистических параметров

совокупности

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

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

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

2.     Оценка средней и предельной ошибок выборки для средней величины признака, а также границ, в которых эта средняя будет находиться в генеральной совокупности при заданных уровнях надежности.

 

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


Выполнение задания включает три этапа:

1.Расчет описательных параметров выборочной и генеральной совокупностей с использованием инструмента Описательная статистика.

2.Оценка предельных ошибок выборки для различных уровней надежности в режиме Описательная статистика.

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

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

стей с использованием инструмента ОПИСАТЕЛЬНАЯ СТАТИСТИКА

Алгоритм 1.1. Расчет описательных статистик

1.Сервис=>Анализ данных=>Описательная статистика=>OK;

2.Входной интервал<= диапазон ячеек таблицы, выделенный для значений признаков Стоимость основных фондов и Выпуск продукции (B4:С33);

3.Группирование =>по столбцам;

4.Итоговая статистика - Активизировать;

5.Уровень надежности - Активизировать;

6.Уровень надежности <= 95,4 (или 95.4);

7.Выходной интервал <= адрес  ячейки  заголовка  первого  столбца  табл.3 (А46);

8.OK;

9.При появлении окна с сообщением "Выходной интервал накладывается на имеющиеся данные" =>ОК.

В результате указанных действий Excel осуществляет вывод таблицы описательных статистик в заданный диапазон рабочего файла (для демонстрационного примера это таб.3-ДП).

A

B

C

D

43


Таблица 3-ДП

44

Описательные статистики

45

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

 

 Выпуск продукции,  млн.руб

 

46

Столбец1

 

Столбец2

 

47

 

 

 

 

48

Среднее

203,2333333

Среднее

144,6666667

49

Стандартная ошибка

8,804737927

Стандартная ошибка

7,071772174

50

Медиана

206,5

Медиана

148,5

51

Мода

167

Мода

114

52

Стандартное отклонение

48,22553575

Стандартное отклонение

38,73369141

53

Дисперсия выборки

2325,702299

Дисперсия выборки

1500,298851

54

Эксцесс

0,18281271

Эксцесс

-0,602481285

55

Асимметричность

-0,185105228

Асимметричность

0,218561586

56

Интервал

204

Интервал

144

57

Минимум

94

Минимум

80

58

Максимум

298

Максимум

224

59

Сумма

6097

Сумма

4340

60

Счет

30

Счет

30

61

Уровень надежности(95,4%)

18,356223

Уровень надежности(95,4%)

14,74331526

Этап 2. Оценка предельных ошибок выборки для различных уровней надежности в режиме Описательная статистика.

Алгоритм 2.1. Расчет предельной ошибки выборки при P=0,683

1.     Сервис=>Анализ данных=>Описательная статистика=>OK;

2.     Входной интервал<= диапазон ячеек таблицы, выделенный для значений признаков Стоимость основных фондов и Выпуск продукции (В4:С33);

3.     Итоговая статистика – Не активизировать;

4.     Уровень надежности – Активизировать;

5.     Уровень надежности<=  68,3 (или 68.3);

6.     Выходной интервал <= адрес ячейки, выделенной для предельной ошибки выборки при P=0,683 (А67);

7.     OK;

8.     При появлении окна с сообщением "Выходной интервал накладывается на имеющиеся данные" =>ОК.

Алгоритм 2.2. Расчет предельной ошибки выборки при P=0,997

1.     Сервис=>Анализ данных=>Описательная статистика=>OK;

2.     Входной интервал<= диапазон ячеек таблицы, выделенный для значений признаков Стоимость основных фондов и Выпуск продукции после удаления аномальных значений (В4:С33);

3.     Итоговая статистика – НЕ активизировать;

4.     Уровень надежности – Активизировать;

5.     Уровень надежности <= 99,7 (или 99.7);

6.     Выходной интервал <= адрес ячейки, выделенный для предельной ошибки выборки при P=0,997 (А75);

7.     OK;

8.     При появлении окна с сообщением "Выходной интервал накладывается на имеющиеся данные" =>ОК.

В результате работы алгоритмов 2.1 и 2.2 Excel выводит в соответствующие ячейки табл.4 рабочего файла значения предельных ошибок выборки при P=0,683 и P=0,997 (для демонстрационного примера табл.4а-ДП и табл.4б-ДП).

A

B

C

D

64

 

Таблица 4а-ДП

65

Предельные ошибки выборки

66

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

 

 Выпуск продукции,  млн.руб

 

67

Столбец1

 

Столбец2

 

68

 

 

 

 

69

Уровень надежности(68,3%)

8,965032289

Уровень надежности(68,3%)

7,200517087

70



71



72


Таблица 4б-ДП

73

Предельные ошибки выборки

74

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

 

 Выпуск продукции, млн.руб

 

75

Столбец1

 

Столбец2

 

76

 

 

 

 

77

Уровень надежности(99,7%)

28,52208432

Уровень надежности(99,7%)

22,90831186

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

Алгоритм 3.1. Расчет значений выборочных параметров σn, σ2n, .

Вычисление показателей для обоих признаков осуществляется с использованием соответствующих статистических функций СТАНДОТКЛОНП, ДИСПР, СРОТКЛ инструмента Мастер функций.

В макете табл.5. приведены их имена вместе с аргументами. Для выполнения вычислений следует ввести знак равенства «=» перед именами функций.

Алгоритм 3.2. Расчет коэффициентов вариации  и  асимметрии Пирсона AsП для обоих признаков.

В макете табл.5. приведены расчетные формулы коэффициента вариации

  и коэффициента асимметрии Пирсона   .

         Для выполнения вычислений следует ввести знак равенства «=» перед формулами.

В результате работы алгоритмов 3.1 - 3.2 Excel осуществляет вывод значений выборочных параметров σn, σ2n, ,  и Аsn в соответствующие ячейки рабочего листа (для демонстрационного примера табл.5-ДП).

A

B

C

D

 

80


Таблица 5-ДП

 

81

Выборочные показатели вариации и асимметрии

82

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

 

 Выпуск продукции,  млн.руб

 

 

83

Стандартное отклонение

47,41496482

Стандартное отклонение

38,08265864

 

84

Дисперсия

2248,178889

Дисперсия

1450,288889

 

85

Среднее линейное отклонение

36,66888889

Среднее линейное отклонение

32,35555556

 

86

Коэффициент вариации, %

23,33030908

Коэффициент вариации, %

26,32441841

 

87

Коэффициент асимметрии

0,764175055

Коэффициент асимметрии

0,805265907

 

 

Задание 3

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

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

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

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

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

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

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

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

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

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

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

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

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

1.     Сервис=>Анализ данных=>Гистограмма=>ОК;

2.     Входной интервал<= диапазон ячеек, выделенный для столбца значений первого признака (В4:В33);

!!! Внимание.  Здесь возможен  ошибочный захват мышью столбца второго признака. Необходимо проконтролировать правильность задания входных данных!

3.     Интервал карманов оставить незаполненным;

4.     Выходной интервал <= адрес заголовка первого столбца первичной промежуточной табл.6 (А90).

5.     OK;

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

1.     Выделить курсором верхнюю левую ячейку табл.6 (A91) и нажать клавишу [Delete];

2.     Ввести в последнюю ячейку табл.6 (A96) вместо "Еще" значение хmax первого признака из табл.3-Описательные статистики (Термин "Максимум").

Для демонстрационного примера построение промежуточной таблицы (алгоритмы 1.1 – 1.2) приведено на рис.11:

Таблица 6

Таблица 6

90

Карман

90

Карман

91

94,0

91

92

134,8

Преобразуется в

92

134,8

93

175,6

93

175,6

94

216,4

94

216,4

95

257,2

95

257,2

96

Ещё

96

298





а) первичная

б) итоговая

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

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

1.     Сервис=>Анализ данных=>Гистограмма=>ОК;

2.     Входной интервал<= диапазон ячеек, выделенный для столбца значений первого признака (В4:В33);

!!!Внимание! Здесь возможен  ошибочный   захват мышью столбца  второго признака. Необходимо проконтролировать правильность задания входных данных!

3.     Интервал карманов <= диапазон карманов итоговой промежуточной табл.6 с верхними границами (А92:А96);

4.     Выходной интервал <= адрес заголовка первого столбца выходной  табл.7 (А101);

5.     Интегральный процент - Активизировать;

6.     Вывод графика  - Активизировать;

7.     ОК;

8.     При появлении сообщения о наложении данных  - ОК.

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

A

B

C

99

Таблица 7-ДП

100

Интервальный ряд распределения предприятий  по стоимости основных производственных фондов

101

Карман

Частота

Интегральный %

102

134,8

3

10,00%

103

175,6

6

30,00%

104

216,4

11

66,67%

104

257,2

6

86,67%

106

298

4

100,00%

107

Ещё

0

100%

Столбиковая диаграмма и кумулята приведены ниже:

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

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

1.     Заменить названия столбцов выходной табл.7 следующим образом:

Название столбца в выходной таблице

Название столбца в результативной таблице

Карман

Группы предприятий по стоимости основных фондов

Частота

Число предприятий в группе

Интегральный %

Накопленная частость группы

2.     Строки первого столбца привести к виду «нижняя граница интервала  -  верхняя граница интервала», учитывая совпадение верхних границ предыдущего интервала с нижней границей последующего интервала;

3.     Строку 107, содержащую термин «Еще», выделить мышью и очистить, нажав  клавишу [Delete];

4.     Добавить и заполнить итоговую строку 108 (ячейки А108:В108).

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

A

B

C

99

Таблица ДП

100

Интервальный ряд распределения предприятий  по стоимости основных производственных фондов

101

Группы предприятий по среднегодовой

стоимости основных фондов, млн. руб.

Число предприятий

в группе

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

группы, %

102

94 - 134,8

3

10,005

103

134,8 - 175,6

6

30,00%

104

175,6 - 216,4

11

66,67%

104

216,4 - 257,2

6

86,67%

106

257,2 - 298

4

100,00%

107


108

Итого

30

 

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

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

2.     Исключить зазоры, выполнив следующие  действия:

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

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

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

2.4. ОК;

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

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

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