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

Задание 1.

Расчёт и анализ показателей ряда динамики выпуска продукции за шестилетний период.

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

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

Задача 2. Расчет средних показателей ряда динамики: средний уровень ряда динамики; средний абсолютный прирост; средний темп роста и средний темп прироста.

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

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

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

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

2. Enter;

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

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

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

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

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

абсолютного значения 1% прироста.

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

	A	B	C	D	E	F	G	H	I
	Таблица 3.2–ДП
	Показатели динамики выпуска продукции
	Годы	Выпуск продукции, млн. руб.	Абсолютный прирост, тыс. тонн	Темп роста, %	Темп прироста, %	Абсолютное значение 1 % прироста
	цепной	базисный	цепной	базисный	цепной	базисный
	1-й	3020,00
	2-й	3260,00	240,00	240,00	107,9	107,9	7,9	7,9	30,2
	3-й	3650,00	390,00	630,00	112,0	120,9	12,0	20,9	32,6
	4-й	3530,00	-120,00	510,00	96,7	116,9	-3,3	16,9	36,5
	5-й	3765,00	235,00	745,00	106,7	124,7	6,7	24,7	35,3
	6-й	4077,00	312,00	1 057,00	108,3	135,0	8,3	35,0	37,65

Задача 2. Расчет средних показателей ряда динамики: средний уровень ряда динамики; средний абсолютный прирост; средний темп роста и средний темп прироста

Алгоритм 1.2. Расчёт средних показателей ряда динамики

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

2. Enter;

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

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

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

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

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

	A	B	C	D
	Таблица 3.3-ДП
	Средние показатели ряда динамики
	Средний уровень ряда динамики, млн. руб.,	3550,33
	Средний абсолютный прирост,млн. руб.,	211,40
	Средний темп роста,%,	106,2
	Средний темп прироста%,	6,2

Задание 2.

Прогноз показателя выпуска продукции на 7-ой год методом экстраполяции.

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

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

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

Алгоритмы выполнения Задания 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. В поле Минимальное значение – ввести минимальное (или несколько ниже) значение признака «Выпуск продукции»;

13. ОК.

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

Алгоритм 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) выполнить для линии тренда:

парабола;

Полином 3-го порядка.

Результат работы алгоритмов 2.2 – 2.3 для демонстрационного примера представлен на рис.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. Стандартные погрешности – НЕ активизировать;

9. ОК.

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

В результате указанных действий осуществляется вывод значений скользящей средней, начиная с ячейки, указанной в поле Выходной интервал диалогового окна инструмента Скользящее среднее, и графика. Для демонстрационного примера они представлены в табл.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-ой год по месяцам») и осей Х (« Месяцы» ) и У ( «Выпуск продукции. млн. руб.»;

8. Готово.

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

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

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

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

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

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

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

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

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

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

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

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

7. ОК;

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

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

10. ОК;

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

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

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

1. Снять заливку области построения;

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

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

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