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

 

 

КАФЕДРА СТАТИСТИКИ

 

Инструкция

по выполнению лабораторных работ

 

Москва, 2013 г.


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

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

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

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

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

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

· подготовить персональную папку с Рабочим и Отчетным файлами; · сформировать индивидуальный вариант исходных данных и записать его в… 1.1. Подготовка персональной папки студента с Рабочим и Отчетным файлами

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

Для того, чтобы сформировать индивидуальные исходные данные, необходимо ввести номер варианта в ячейку E2созданного Рабочего файла Лаб.хls,в… Структура Листа 1 Рабочего файла stat_lab.xls (распределение памяти для результативных таблиц)   A B C Таблица…

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

Задание 1

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

Выполнение Задания 1 заключается в решении двух задач: 1. Построение диаграммы рассеяния изучаемых признаков. 2. .Визуальный анализ диаграммы рассеяния, выявление и фиксация аномальных значений признаков, их удаление из…

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

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

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

1. Выделить мышью исходные данные (B4:C35);

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

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

 
 

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

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

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

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

Задание 2

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

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

Выполнение Задания 2 заключается в автоматизированном решении двух статистических задач: 1. Расчет описательных показателей выборочной и генеральной совокупностей по… 2. Оценка средней и предельной ошибок выборки для средней величины признака, а также границ, в которых эта средняя…

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

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

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

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

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

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

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

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

OK;

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

Результат работы алгоритма 2.1 для демонстрационного примера представлен в табл.3–ДП.

 

  A B C D
      Таблица 3-ДП
  Описательные статистики  
Среднегодовая стоимость основных производственных фондов, млн руб. Выпуск продукции, млн руб.
46 Столбец1 Столбец2
       
Среднее 203,2333333 Среднее 144,6666667
Стандартная ошибка 8,804737927 Стандартная ошибка 7,071772174
Медиана 206,5 Медиана 148,5
Мода Мода
Стандартное отклонение 48,22553575 Стандартное отклонение 38,73369141
Дисперсия выборки 2325,702299 Дисперсия выборки 1500,298851
Эксцесс 0,18281271 Эксцесс -0,602481285
Асимметричность -0,185105228 Асимметричность 0,218561586
Интервал Интервал
Минимум Минимум
Максимум Максимум
Сумма Сумма
Счет Счет
Уровень надежности (95,4%) 18,356223 Уровень надежности (95,4%) 14,74331526

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

1. Сервис=>Анализ данных=>Описательная статистика=>OK; 2. Входной интервал<=диапазон ячеек таблицы, выделенный для значений… 3. Итоговая статистика– НЕ активизировать (снять флажок);

OK;

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

Результат работы алгоритма 2.1 для демонстрационного примера представлен в табл.4–ДП.

  A B C D
    Таблица 4-ДП
Предельные ошибки выборки  
Среднегодовая стоимость основных производственных фондов, млн руб. Выпуск продукции, млн руб.
67 Столбец1 Столбец2
       
Уровень надежности (68,3%) 8,965032289 Уровень надежности (68,3%) 7,200517087

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

Вычисление показателей для обоих признаков осуществляется с использованием соответствующих статистических функций СТАНДОТКЛОНП, ДИСПР, СРОТКЛ… 1. В ячейках (В83 – В84), выделенных для значений выборочных показателей… 1.1. Перед именами функций поставить знак равенства «=»;

Enter;

2. В ячейках (D83 – D84), выделенных для значений выборочных показателей признака Выпуск продукции:

2.1. Перед именами функций поставить знак равенства «=»;

Enter;

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

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

1. В ячейке В85,выделенной для значений коэффициента вариации по признаку Среднегодовая стоимость основных производственных фондов, перед формулой поставить знак равенства «=»;

Enter;

3. В ячейке D85, выделенной для значений коэффициента вариации по признаку Выпуск продукции, перед формулой поставить знак равенства «=»;

Enter.

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

  A B C D
      Таблица 5-ДП
Выборочные показатели вариации  
Среднегодовая стоимость основных производственных фондов, млн руб. Выпуск продукции, млн руб.
Стандартное отклонение 47,41496482 Стандартное отклонение 38,08265864
Дисперсия 2248,178889 Дисперсия 1450,288889
Коэффициент вариации, % 23,33030908 Коэффициент вариации, % 26,32441841
           

Задание

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

Для того чтобы выявить структуру совокупности и тип закономерности распределения ее единиц по варьирующему признаку, строят и анализируют… Выполнение Задания 3 заключается в решении двух статистических задач: 1. Построение интервального ряда распределения единиц выборочной совокупности по признаку Среднегодовая стоимость…

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

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

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

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

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

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

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

2. Входной интервал<= диапазон ячеек, выделенный для столбца значений первого признака (В4:В33); Внимание! Здесь возможен ошибочный захват мышью столбца второго признака.… 3. Интервал карманов оставить незаполненным;

OK.

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

2. Ввести в последнюю ячейку табл.6 (A96) вместо "Еще" значение хmax первого признака из табл.3 – Описательные статистики (Термин… Для демонстрационного примера построение промежуточной таблицы (алгоритмы 1.1… Рис.2. Схема перехода от нижних границ интервалов к верхним

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

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

2. Входной интервал<=диапазон ячеек, выделенный для столбца значений первого признака (В4:В33); Внимание! Здесь возможен ошибочный захват мышью столбца второго признака.… 3. Интервал карманов <=диапазон карманов итоговой промежуточной табл.6 с верхними границами (А92:А96);

ОК;

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

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

  A B C
    Таблица 7-ДП
Интервальный ряд распределения предприятий по признаку Среднегодовая стоимость основных производственных фондов
Группы предприятий по признаку Среднегодовая стоимость основных производственных фондов, млн руб. Число предприятий в группе Накопленная частость группы, %
Карман Частота Интегральный %
134,8 10,00%
175,6 30,00%
216,4 66,67%
257,2 86,67%
100,00%
Ещё 100%

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

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

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

2. Правка => Удалить; 3. Строку 107, содержащую термин «Еще», выделить мышью и очистить, нажав… 4. Строки первого столбца привести к виду «нижняя граница интервала - верхняя граница интервала», учитывая совпадение…

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

2. Исключить зазоры, выполнив следующие действия: 1. Нажать правую кнопку мыши на одном из столбиков диаграммы; 2. Формат рядов данных=>Параметры;

Алгоритм 3.3. Изменение названия осей

1. Выделить мышью построенную диаграмму;

2. Диаграмма => Параметры диаграммы;

3. В появившемся диалоговом окне Параметры диаграммывыбрать вкладку Заголовкии в поле Ось Хввести заголовок оси – "Среднегодовая стоимость основных производственных фондов", а в поле Ось Y"Число предприятий в группе";

ОК.

Алгоритм 3.4. Изменение текста легенды

1. Выделить мышью построенную диаграмму;

2. Диаграмма => Исходные данные;

3. В появившемся диалоговом окне Исходные данные выбрать вкладку Ряд, в поле Рядвыбрать заголовок "Интегральный %" и в поле Имя ввести – "Накопленная частость ";

ОК.

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


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

Автоматизированный корреляционно-регрессионный анализ взаимосвязи статистических данных в среде MS Excel

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

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

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

Для выполнения ЛР-2 выделяется Лист 2 Рабочего файла, сформирован­ного в персональной папке студента при выполнении ЛР-1, и используется следующая информация из ЛР-1 (Лист 1 Рабочего файла):

· исходные данные – Таблица 1 (А4:С33), полученная после удаления аномальных значений признаков);

· интервальный ряд распределения единиц совокупности по факторному признаку ХСреднегодовая стоимость основных производственных фондов, представленный табл. 7(А102:В106);

· диаграмма рассеяния признаков, расположенная начиная с ячейкиF4.

Компьютерное выполнение ЛР-2 включает два этапа:

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

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

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

На данном этапе студент должен скопировать необходимую информацию ЛР-1 из Листа 1 в Лист 2 Рабочего файла персональной папки в соответствии с… Лист 1 Лист 2 Номер таблицы Содержимое таблицы … ►Внимание!Табл.7 копируется на Лист 2 Рабочего файла дважды!

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

Задание 1

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

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

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

Задача 2. Оценка тесноты связи изучаемых признаков на основе эмпирического корреляционного отношения.

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

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

Задача решается в три этапа:

1. Ранжирование единиц совокупности по возрастанию факторного признака ХСреднегодовая стоимость основных производственных фондов алгоритм 1.1.

2. Распределение предприятий по группам в соответствии с интервальным рядом распределения факторного признака – алгоритм 1.2.

3. Расчет суммарных и средних групповых значений результативного признака Y – Выпуск продукции алгоритмы 1.3 и 1.4.

Алгоритм 1.1. Ранжирование исходных данных по факторному признаку

2. Данные => Сортировка; 3. Сортировать по <= заголовок столбца, по которому выполняется сортировка,… 4. По возрастанию/по убыванию – устанавливается в положение по возрастанию;

ОК.

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

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

2. Нажать на панели инструментов кнопку для выбора цвета заливки; 3. Выбрать цвет заливки по собственному усмотрению; ►Внимание!Цвет желательно брать контрастный, чтобы четко отделить одну группу от другой.

Алгоритм 1.3. Расчет суммарных групповых значений результативного признака

2. В качествеаргумента функции СУММ() указать диапазон ячеек из табл. 2.1 с результативными значениями уi первой группы (визуально легко… ►Внимание!Здесь возможен ошибочный захват мышью столбца значений первого…

Enter;

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

Алгоритм 1.4. Расчет средних групповых значений результативного признака

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

1. В ячейке (Е41), выделенной для среднего значения результативного признакаВыпуск продукции первойгруппы, перед формулой поставить знак равенства «=»;

Enter;

3. Выполнить действия 1–2 поочередно для всех групп;

4. В ячейках (C46, D46иE46), выделенных для расчета итоговых сумм:

Перед формулой поставить знак равенства «=»;

Enter.

Результаты работы алгоритмов 1.3 и 1.4 для демонстрационного примера приведены в табл. 2.2–ДП.

  A B C D E
Таблица 2.2–ДП
Зависимость выпуска продукции от среднегодовой стоимости основных производственных фондов
Номер группы Группы предприятий по признаку Среднегодовая стоимость основных производственных фондов, млн руб. Число предприятий Выпуск продукции, млн руб.
Всего В среднем на одно предприятие
94 – 134,8 331,00 110,33
134,8 – 175,6 640,00 106,67
175,6 – 216,4 1590,00 144,55
216,4 – 257,2 973,00 162,17
257,2 – 298 806,00 201,50
Итого   4340,00 144,67

Задача 2. Оценка тесноты связи изучаемых признаков на основе эмпирического корреляционного отношения

Задача решается в два этапа:

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

2. Расчет эмпирического корреляционного отношения.

Алгоритм 2.1. Расчет внутригрупповых дисперсий результативного признака

2. В качествеаргумента функции ДИСПР() указать диапазон ячеек из табл. 2.1 со значениями yi первой группы – визуально легко определяется по цвету… ►Внимание!Здесь возможен ошибочный захват мышью столбца значений первого… 3. Enter;

Enter.

Результат работы алгоритма 2.1 для демонстрационного примера представлен в табл.2.3–ДП.

  A B C D
Таблица 2.3–ДП
Показатели внутригрупповой дисперсии
Номер группы Группы предприятий по признаку Среднегодовая стоимость основных производственных фондов, млн руб. Число предприятий Внутригрупповая дисперсия
94 – 134,8 60,22
134,8 – 175,6 784,56
175,6 – 216,4 821,16
216,4 – 257,2 123,47
257,2 – 298 472,25
Итого    

Алгоритм 2.2. Расчет эмпирического корреляционного отношения

1. В ячейке, выделенной для общей дисперсии (А63), перед формулой поставить знак равенства «=»;

Enter;

3. В ячейке, выделенной для средней из внутригрупповых дисперсий (В63), перед формулой поставить знак равенства «=»;

Enter;

►Примечание. В случае если при выполнении вычисления в ячейке В63 выдается сообщение "Ошибка в формуле", то разделительный знак «,» между аргументами функции СУМПРОИЗВ(Д1,Д2) необходимо заменить на знак «;».

5. В ячейке, выделенной для значения межгрупповой (факторной) дисперсии (С63), перед формулой поставить знак равенства «=»;

Enter;

7. В ячейке, выделенной для эмпирического корреляционного отношения (D63), перед формулой поставить знак равенства «=»;

Enter.

Результат работы алгоритма 2.2 для демонстрационного примера представлен в табл.2.4–ДП.

  A B C D
Таблица 2.4–ДП
Показатели дисперсий и эмпирического корреляционного отношения
Общая дисперсия Средняя из внутригрупповых дисперсий Межгрупповая дисперсия Эмпирическое корреляционное отношение η
1450,288889 551,6853535 898,6035354 0,787148735

Задание 2

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

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

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

2. Входной интервал Y <= диапазон ячеек таблицы со значениями признакаY–Выпуск продукции (С4:С33); 3. Входной интервал X – диапазон ячеек таблицы со значениями… 4. Метки в первой строке/Метки в первом столбце–НЕ активизировать;

ОК.

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

  А В
Регрессионная статистика
Множественный R 0,753661673
R–квадрат 0,568005917
Нормированный R-квадрат 0,552577557
Стандартная ошибка 25,90882817
Наблюдения

 

  А B C D E F
Дисперсионный анализ
df SS MS F Значимость F
Регрессия 24713,1801 24713,1801 36,81570256 1,52606E-06
Остаток 18795,48657 671,2673773    
Итого 43508,66667      

 

  A B C D E F G H I
Коэффициенты Стандартная ошибка t-статистика P-Значение Нижние 95% Верхние 95% Нижние 68,3% Верхние 68,3%
Y-пересечение 21,64454934 20,81975413 1,039615992 0,307412837 -21,0028 64,29193 0,432468 42,85664
Переменная X 1 0,605324507 0,099763508 6,067594462 1,52606E-06 0,400968 0,809681 0,503681 0,706968

 

  A B C
ВЫВОД ОСТАТКА
 
Наблюдение Предсказанное Y Остатки
78,54505301 31,45494699
86,4142716 14,5857284
102,7580333 17,24196671
116,680497 -35,68049696
 
202,0312525 17,96874754

Задание 3

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

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

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

1. Выделить мышью диаграмму рассеяния признаков, расположенную начиная с ячейки Е4, и увеличить диаграмму на весь экран, используя прием "захват мышью";

2. Диаграмма => Добавить линию тренда;

3. В появившемся диалоговом окне Линия трендавыбрать вкладку Типи задать вид регрессионной модели – полином 2-го порядка;

4. Выбрать вкладку Параметрыи выполнить действия:

1. Переключатель Название аппроксимирующей кривой: автоматическое/другое– установить в положение другое и ввести имя тренда– полином 2-го порядка;

2. Поле Прогноз вперед наНЕ активизировать;

3. ПолеПрогноз назад наНЕ активизировать;

4. ФлажокПересечение кривой с осью Y в точке

НЕ активизировать;

5. ФлажокПоказывать уравнение на диаграммеАктивизировать;

6. ФлажокПоместить на диаграмму величину достоверности аппроксимации R2Активизировать;

7. ОК;

8. Установить курсор на линию регрессии и щелкнуть правой клавишей мыши;

9. В появившемся диалоговом окне Формат линии трендавыбрать по своему усмотрению тип, цвет и толщину линии;

10. ОК;

11. Выделить уравнение регрессии и индекс детерминации R2и с помощью приема "захват мышью" вынести их за корреляционное поле. При необходимости уменьшить размер шрифта.

5. Действия 2 – 4 (в п.4 шаги 1–11) выполнить поочередно для следующих видов регрессионных моделей:

– полином 3-го порядка,
– степенная функция
.

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

1. Присвоить полученной диаграмме заголовок "Диаграмма 2.1" и удалить линии сетки по оси Y (алгоритм 2);

2. Снять заливку области построения (алгоритм 3);

3. При необходимости изменить масштаб шкалы осей диаграммы (алгоритм 4).

Алгоритм 2. Присвоение полученной диаграмме заголовка "Диаграмма 2.1" и удаление линий сетки по оси Y

1. Выделить мышью построенную диаграмму;

2. Диаграмма => Параметры диаграммы;

3. В появившемся диалоговом окне Параметры диаграммывыбрать вкладку Заголовкии в поле Название диаграммы ввести заголовок диаграммы "Диаграмма 2.1";

4. Выбрать вкладку Линии сетки, в полях Ось Х и Ось Y все флажки – Не активизировать;

ОК.

Алгоритм 3. Снятие заливки области построения

1. Выделить мышью Область построения диаграммы;

2. Формат => Выделенная область построения;

3. В появившемся диалоговом окне Формат области переключатель Заливка установить в положение Обычная;

ОК.

Алгоритм 4. Изменение масштаба шкалы осей диаграммы

2. Формат => Выделенная ось; 3. В появившемся диалоговом окне Формат оси выбрать вкладку Шкала; 4. В полеМинимальное значение– ввести минимальное (или несколько ниже) значение признака, соответствующего…

ОК.

Выделить диаграмму и с помощью приема "захват мышью" переместить ее в конец Рабочего файла.

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

Рис 2.1.Уравнения регрессии и их графики


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

Автоматизированный анализ динамики социально-экономических явлений в среде MS Excel

I. Постановка задачи

Полученные два ряда динамики представлены в формате электронных таблиц процессора Excel, годовые данные - в диапазоне ячеек A6:B12, а данные за 6-ой… Таблица 3.1-ДП Исходные данные демонстрационного примера A B C D Е …

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

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

На данном этапе студент должен записать в Отчетный файл ЛР3 индивидуальный вариант исходных данных. На Листе 3 Рабочего файла персональной папки студента заготовлены макеты… Расположение исходных данных и макетов результативных таблиц в Рабочем файле на Листе 3 персональной папки студента …

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

Расчёт и анализ показателей ряда динамики выпуска продукции за шестилетний период. Выполнение Задания 1 заключается в решении двух задач: Задача 1. Расчет цепных и базисных показателей динамики: абсолютный прирост (сокращение); темп роста (снижения); темп…

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

Задача 1.. Расчет цепных и базисных показателей динамики: абсолютный прирост (сокращение); темп роста (снижения); темп прироста (сокращения) и абсолютное значение 1 % прироста

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

1. В ячейке, выделенной для значения абсолютного прироста цепного за первый год (С26), перед формулой поставить знак равенства «=»;

Enter;

3. Установить курсор в правом нижнем углу ячейки (С26) с формулой – образцом (курсор примет форму черного крестика) и, удерживая левую клавишу мыши в нажатом состоянии, переместить курсор до нижней клетки графы. Отпустить клавишу мыши (формула – образец размножилась на всю графу).

4. Выполнить действия 1–2 поочередно для всех аналитических показателей ряда динамики табл.3.2:

Абсолютного прироста базисного;

Темпа роста цепного и базисного;

Темпа прироста цепного и базисного;

Результат работы алгоритма 1.1 для демонстрационного примера приведены в табл. 3.2–ДП.   A B C D … Задача 2.Расчет средних показателей ряда динамики: средний уровень ряда… Алгоритм 1.2. Расчёт средних показателей ряда динамики

Среднего абсолютного прироста;

Среднего темпа роста;

Среднего темпа прироста.

Задание 2. Прогноз показателя выпуска продукции на 7-ой год методом экстраполяции. Выполнение Задания 2 заключается в решении двух задач:

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

Задача 1.Прогнозирование выпуска продукции предприятием на год вперёд с использованием среднего абсолютного прироста и среднего темпа роста.

Алгоритм 2.1. Расчёт выпуска продукции на год вперёд с использованием среднего абсолютного прироста и среднего темпа роста

1. В ячейке, выделенной для значений прогнозируемого выпуска продукции по среднему абсолютному приросту (Е41), перед формулой поставить знак равенства «=».

2. Enter;

3. Выполнить действия 1–2 для прогнозируемого выпуска продукции по значению среднего темпа роста (табл.3.4).

Результат работы алгоритма 2.1 для демонстрационного примера приведен в табл. 3.4 – ДП.

  A B C D E
Таблица 3.4–ДП
Прогноз выпуска продукции на год вперед
По среднему абсолютному приросту, млн. руб., 4288,40
По среднему темпу роста, %, 4329,77

Задача 2. Прогнозирование выпуска продукции предприятием на год вперёд с использованием аналитического выравнивания ряда динамики по прямой, параболе и полиному 3-го порядка.

Алгоритм 2.2. Построение графика динамикивыпуска продукции за 6 лет с использованием средств инструмента МАСТЕР ДИАГРАММ

1. Выделить мышью ячейки, содержащие выпуск продукции за 6 лет (диапазон ячеек B7:B12);

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

3. В появившемся диалоговом окне Мастер диаграмм (шаг 1 из 4) выбрать вид точечной диаграммы, на которой значения соединены отрезками;

4. Далее;

5. В появившемся диалоговом окне Мастер диаграмм (шаг 2 из 4) выбрать вкладку Ряд и задать имя ряда 1 – «Исходные данные»;

6. Далее;

7. В появившемся диалоговом окне Мастер диаграмм (шаг 3 из 4) выбрать вкладку Заголовки и задать названия диаграммы («Прогнозирование выпуска продукции на 7-ой год») и осей Х («Годы»)и У («Выпуск продукции. млн. руб.»;;

8. Готово;

9. Выделить на полученной диаграмме ось Y (подвести курсор к требуемой оси и щелкнуть левой клавишей мыши);

10. Формат => Выделенная ось;

11. В появившемся диалоговом окне Формат оси выбрать вкладку Шкала;

12. В полеМинимальное значение– ввести минимальное (или несколько ниже) значение признака «Выпуск продукции»;

ОК.

Выделить диаграмму и с помощью приема "захват мышью" переместить ее в конец Рабочего файла.

Алгоритм 2.3. Нахождение тренда ряда динамики выпуска продукции методом аналитического выравнивания и прогнозирование его на год вперед с помощью инструмента Мастер диаграмм

1. Выделить мышью диаграмму «Прогнозирование выпуска продукции на 7-ой год», расположенную в конце Рабочего файла;

2. Диаграмма => Добавить линию тренда;

3. В появившемся диалоговом окне Линия трендавыбрать вкладку Типи задать вид линии тренда – линейная;

4. Выбрать вкладку Параметрыи выполнить действия:

1. Переключатель Название аппроксимирующей кривой: автоматическое/другое– установить в положение другое и ввести имя тренда– Прямая;

2. В поле Прогноз вперед на…едицицввести значение «1»;

3. ПолеПрогноз назад на…единицНЕ активизировать;

4. ФлажокПересечение кривой с осью Y в точкеНЕ активизировать;

5. ФлажокПоказывать уравнение на диаграммеАктивизировать;

6. ФлажокПоместить на диаграмму величину достоверности аппроксимации R2Активизировать;

7. ОК;

8. Установить курсор на линию тренда, щелкнуть правой клавишей мыши и выбрать меню Формат линии тренда;

9. В появившемся диалоговом окне Формат линии трендавыбрать вкладку Вид и задать по своему усмотрению тип, цвет и толщину линии;

10. ОК;

11. Выделить уравнение линии тренда и индекс детерминации R2и с помощью приема "захват мышью" вынести их за корреляционное поле. При необходимости уменьшить размер шрифта.

5. Действия 1 – 4 (в п.4 это шаги 1–11) выполнить поочередно для линии тренда:

парабола (полином 2-го порядка);

Степенная.

Результаты работы алгоритмов 2.2.1 – 2.2.2 для демонстрационного примера представлены на рис.3.1.

Рис.3.1. График динамики выпуска продукции за 6 лет и прогнозирование выпуска продукции на год вперед

Задание 3.

Выявление тенденции развития изучаемого явления (тренда) по данным о выпуске продукции по месяцам за 6-ой год методами скользящей средней и аналитического выравнивания.

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

Задача 1. Расчет скользящей средней, полученной на основе трёхчленной скользящей суммы.

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

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

Задача 1.Расчет скользящей средней, полученной на основе трёхчленной скользящей суммы

Алгоритм 3.1. Нахождение значений скользящей средней с помощью инструмента СКОЛЬЗЯЩЕЕ СРЕДНЕЕ надстройки ПАКЕТ АНАЛИЗА

1. Сервис => Анализ данных => Скользящее среднее => ОК;

2. Входной интервал <= диапазон ячеек табл. 3.5 со значениями признака–Выпуск продукции (В47:В58);

3. Метки в первой строкеНЕ активизировать;

4. ИнтервалНЕ активизировать;

5. Выходной интервал <=адрес первой ячейки третьего столбца выходной результативной таблицы (С47);

6. Новый рабочий листиНовая рабочая книгаНЕ активизировать;

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

8. Стандартные погрешности – НЕ активизировать;

ОК.

Выделить диаграмму и с помощью приема "захват мышью" переместить ее в конец Рабочего файла.

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

 

 

    A B C
Таблица 3.5 -ДП
Выпуск продукции за 6-ой год
Месяцы Выпуск продукции, млн. руб. Скользящее среднее
январь 175,00 #Н/Д
февраль 241,00 #Н/Д
март 300,00 238,67
апрель 270,00 270,33
май 330,00 300,00
июнь 310,00 303,33
июль 366,00 335,33
август 341,00 339,00
сентябрь 420,00 375,67
октябрь 441,00 400,67
ноябрь 453,00 438,00
декабрь 430,00 441,33
         

Рис.3.2. График сглаживания ряда динамики выпуска продукции за 6-ой год, сгенерированный в режиме «скользящее среднее» Пакета анализа

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

1. Ячейку С47, содержащую термин " #Н/Д", выделить мышью и очистить, нажав клавишу [Delete];

2. Ячейки результативной таблицы (С49:С58), содержащие значения «Скользящее среднее», вырезать с помощью инструмента ;

3. Вставить в табл. 3.5, начиная с ячейки С48, с помощью инструмента ;

4. Отформатировать таблицу по образцу с помощью инструмента .

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

    A B C
Таблица 3.5-ДП
Выпуск продукции за 6-ой год
Месяцы Выпуск продукции, млн. руб. Скользящее среднее
январь 175,00  
февраль 241,00 238,67
март 300,00 270,33
апрель 270,00 300,00
май 330,00 303,33
июнь 310,00 335,33
июль 366,00 339,00
август 341,00 375,67
сентябрь 420,00 400,67
октябрь 441,00 438,00
ноябрь 453,00 441,33
декабрь 430,00  
         

График сглаживания ряда динамики выпуска продукции методом скользящей средней представлен на рис. 3.3.

Рис.3.3. График сглаживания скользящей средней ряда динамики выпуска продукции за 6-ой год

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

Алгоритм 3.3. Построение графика динамики выпуска продукции по месяцам за 6-ой год с использованием средств инструмента МАСТЕР ДИАГРАММ

1. Выделить мышью столбец данных в диапазоне B47:В58 (табл. 3.5);

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

3. В появившемся диалоговом окне Мастер диаграмм (шаг 1 из 4) выбрать вид точечной диаграммы, на которой значения соединены отрезками;

4. Далее;

5. В появившемся диалоговом окне Мастер диаграмм (шаг 2 из 4) выбрать вкладку Ряд и ввести имя ряда «Исходные данные»;

6. Далее;

7. В появившемся диалоговом окне Мастер диаграмм (шаг 3 из 4) выбрать вкладку Заголовки и задать названия диаграммы («Выпуск продукции за 6-ой год по месяцам») и осей Х («Месяцы»)и У («Выпуск продукции. млн. руб.»;

Готово.

Алгоритм 3.4. Сглаживание ряда динамики выпуска продукции методом аналитического выравнивания с помощью инструмента Мастер диаграмм 1. Выделить мышью диаграмму «Выпуск продукции за 6-ой год по месяцам»,… 2. Диаграмма => Добавить линию тренда;