EMBED MSPhotoEd.3
ВСЕРОССИЙСКИЙ ЗАОЧНЫЙ ФИНАНСОВО-ЭКОНОМИЧЕСКИЙ ИНСТИТУТ
КАФЕДРА СТАТИСТИКИ
Лабораторная работа №1
Автоматизированный априорный анализ статистической совокупности в среде MS Excel
Вариант № 6
Исполнитель: Ржавичева Татьяна Владимировна
Специальность: финансы и кредит
Группа: дневная III курс
№ зачетной книжки: 08ФФД40351
Руководитель: Леонова Светлана Николаевна
Орел 2009г.
Алгоритмы выполнения Задания 1
Задача 1. Построение диаграммы рассеяния изучаемых признаков.
Выделить мышью исходные данные (табл.1) (В4:С35);
Вставка Диаграмма Готово.
EMBED Excel.Chart.8 \s
Рис 1. Аномальные значения признаков на диаграмме рассеяния.
Задача 2. Визуальный анализ диаграммы рассеяния, выявление и фиксация аномальных значений признаков, их удаление из первичных данных.
Точки соответствующие аномальным наблюдениям:
Две единицы наблюдения с аномальными значениями признаков, имеет следующий вид.
Алгоритмы выполнения Задания 2
Этап 1. Расчет описательных параметров выборочной и генеральной совокупностей с использованием инструмента Описательная статистика
Алгоритм 1.1. Расчет описательных статистик
Сервис=>Анализ данных=>Описательная статистика=>OK;
Входной интервал<= диапазон ячеек таблицы, выделенный для значений признаков Стоимость основных фондов и Выпуск продукции (B4:С33);
Группирование =>по столбцам;
Итоговая статистика - Активизировать;
Уровень надежности - Активизировать;
Уровень надежности <= 95,4 (или 95.4);
Выходной интервал <= адрес ячейки заголовка первого столбца табл.3 (А46);
OK;
При появлении окна с сообщением "Выходной интервал накладывается на имеющиеся данные" =>ОК.
Результат работы алгоритма 2.1 представлен в табл.3
Этап 2. Оценка предельных ошибок выборки для различных уровней надежности в режиме Описательная статистика.
Алгоритм 2.1. Расчет предельной ошибки выборки при P=0,683
Сервис=>Анализ данных=>Описательная статистика=>OK;
Входной интервал<= диапазон ячеек таблицы, выделенный для значений признаков Стоимость основных фондов и Выпуск продукции (В4:С33);
Итоговая статистика – НЕ активизировать (снять флажок);
Уровень надежности – Активизировать;
Уровень надежности<= 68,3 (или 68.3);
Выходной интервал <= адрес ячейки, выделенной для предельной ошибки выборки при P=0,683 (А67);
OK;
При появлении окна с сообщением "Выходной интервал накладывается на имеющиеся данные" =>ОК.
Этап 3. Расчет описательных параметров выборочной совокупности с использованием инструмента Мастер функций
Алгоритм 3.1. Расчет значений выборочных параметров EMBED Equation.3
Вычисление показателей для обоих признаков осуществляется с использованием соответствующих статистических функций СТАНДОТКЛОНП, ДИСПР, СРОТКЛ инструмента Мастер функций. В макете табл.5. приведены их имена вместе с адресами аргументов.
В ячейках (В83 – В84), выделенных для значений выборочных показателей признака Среднегодовая стоимость основных производственных фондов:
1.1. Перед именами функций поставить знак равенства «=»;
1.2. Enter;
В ячейках (D83 – D84), выделенных для значений выборочных показателей признака Выпуск продукции:
2.1. Перед именами функций поставить знак равенства «=»;
2.2. Enter;
Алгоритм 3.2. Расчет коэффициентов вариации EMBED Equation.3 для обоих признаков
В макете табл.5. приведены расчетные формулы коэффициента вариации EMBED Equation.3 .
В ячейке В85, выделенной для значений коэффициента вариации по признаку Среднегодовая стоимость основных производственных фондов, перед формулой поставить знак равенства «=»;
Enter;
В ячейке D85, выделенной для значений коэффициента вариации по признаку Выпуск продукции, перед формулой поставить знак равенства «=»;
Enter.
В результате работы алгоритмов 3.1-3.2 осуществляется вывод выборочных параметров EMBED Equation.3 и EMBED Equation.3 в соответствующие ячейки рабочего листа. Табл. 5.
Задание 3. Построение и графическое изображение интервального вариационного ряда распределения единиц совокупности по признаку
Алгоритмы выполнения Задания 3
Этап 1. Построение промежуточной таблицы.
Алгоритм 1.1. Расчет нижних границ интервалов
Сервис=>Анализ данных=>Гистограмма=>ОК;
Входной интервал<= диапазон ячеек, выделенный для столбца значений первого признака (В4:В33);
Интервал карманов оставить незаполненным;
Выходной интервал <= адрес заголовка первого столбца первичной промежуточной табл.6 (А90);
OK.
Алгоритм 1.2. Переход от нижних границ к верхним
Выделить курсором верхнюю левую ячейку табл.6 (A91) и нажать клавишу [Delete];
Ввести в последнюю ячейку табл.6 (A96) вместо "Еще" значение хmax первого признака из табл.3 – Описательные статистики (Термин "Максимум").
а) первичная Преобразуется в б) итоговая
Рис.2. Схема перехода от нижних границ интервалов к верхним
Этап 2. Генерация выходной таблицы и графиков
Алгоритм 2.1. Построение выходной таблицы, столбиковой диаграммы и кумуляты
Сервис=>Анализ данных=>Гистограмма=>ОК;
Входной интервал<= диапазон ячеек, выделенный для столбца значений первого признака (В4:В33);
Интервал карманов <= диапазон карманов итоговой промежуточной табл.6 с верхними границами (А92:А96);
Выходной интервал <= адрес заголовка («Карман») первого столбца выходной табл.7 (А102);
Интегральный процент – Активизировать;
Вывод графика – Активизировать;
ОК;
При появлении сообщения о наложении данных – ОК.
Выходная таблица имеет следующий вид:
Столбиковая диаграмма и кумулята приведены ниже:
Этап 3. Приведение выходной таблицы и диаграммы к виду, принятому в статистике.
Алгоритм 3.1. Преобразование выходной таблицы в результативную
Строку 102, содержащую термин «Карман», выделить мышью (вместе с ее номером);
Правка => Удалить;
Строку 107, содержащую термин «Еще», выделить мышью и очистить, нажав клавишу [Delete];
Строки первого столбца привести к виду «нижняя граница интервала - верхняя граница интервала», учитывая совпадение верхних границ предыдущего интервала с нижней границей последующего интервала (нижняя граница первого интервала равна хmin первого признака из табл.3 – Описательные статистики – Термин "Минимум").
Добавить и заполнить итоговую строку 108 (ячейки А108:В108).
Excel-формат результативной таблицы выглядит следующим образом.
Алгоритм 3.2. Преобразование столбиковой диаграммы в гистограмму
Осуществив «захват мышью», переместить график, расположив его вслед за табл.7.
Исключить зазоры, выполнив следующие действия:
Нажать правую кнопку мыши на одном из столбиков диаграммы;
Формат рядов данных=>Параметры;
Ширина зазора<= 0;
ОК;
Подвести курсор на угол поля графика и, используя прием "захват мышью", установить визуально соотношение ширины и высоты фигуры гистограммы в пропорции 1 : 0,6.
По окончании работы алгоритма 3.2 выполнить настройку диаграммы:
Изменить название осей (алгоритм 3.3);
Изменить текст легенды (алгоритм 3.4).
Алгоритм 3.3. Изменение названия осей
Выделить мышью построенную диаграмму;
Диаграмма => Параметры диаграммы;
В появившемся диалоговом окне Параметры диаграммы выбрать вкладку Заголовки и в поле Ось Х ввести заголовок оси – "Среднегодовая стоимость основных производственных фондов", а в поле Ось Y – "Число предприятий в группе";
ОК.
Алгоритм 3.4. Изменение текста легенды
Выделить мышью построенную диаграмму;
Диаграмма => Исходные данные;
В появившемся диалоговом окне Исходные данные выбрать вкладку Ряд, в поле Ряд выбрать заголовок "Интегральный %" и в поле Имя ввести – "Накопленная частость ";
ОК.
Гистограмма и кумулята выглядят следующим образом
ПРИЛОЖЕНИЕ
табл. 1. Выборочные исходные данные
EMBED MSPhotoEd.3
ВСЕРОССИЙСКИЙ ЗАОЧНЫЙ ФИНАНСОВО-ЭКОНОМИЧЕСКИЙ ИНСТИТУТ
КАФЕДРА СТАТИСТИКИ
Лабораторная работа №2
Автоматизированный корреляционно-регрессионный анализ взаимосвязи статистических данных в среде MS Excel
Вариант № 6
Исполнитель: Ржавичева Татьяна Владимировна
Специальность: финансы и кредит
Группа: дневная III курс
№ зачетной книжки: 08ФФД40351
Руководитель: Леонова Светлана Николаевна
Орел 2009г.
Задание 1 Построение аналитической группировки для выявления корреляционной зависимости результативного признака от факторного и оценка тесноты взаимосвязи признаков
Алгоритмы выполнения Задания 1
Задача 1. Построение аналитической группировки предприятий по признаку Среднегодовая стоимость основных производственных фондов.
Алгоритм 1.1. Ранжирование исходных данных по факторному признаку
Выделить исходные данные (вместе с заголовком) табл. 2.1 (А3:С33);
Данные => Сортировка;
Сортировать по <= заголовок столбца, по которому выполняется сортировка, т.е. Среднегодовая стоимость основных производственных фондов;
По возрастанию/по убыванию – устанавливается в положение по возрастанию;
Затем и В последнюю очередь по – НЕ активизировать;
Идентифицировать поля по подписям/обозначениям столбцов листа – устанавливается в положение подписям;
ОК.
В результате указанных действий в таблице 2.1 размещаются данные, ранжированные по возрастанию признака Среднегодовая стоимость основных производственных фондов.
Алгоритм 1.2. Выделение групп предприятий с помощью заливки контрастным цветом
Из всего диапазона отсортированных данных A4:C33 выделить мышью диапазон ячеек первой группы, для чего необходимо отсчитать в ранжированном ряду количество строк, соответствующее числу предприятий первой группы (графа 3 табл.2.2);
Нажать на панели инструментов кнопку для выбора цвета заливки;
Выбрать цвет заливки по собственному усмотрению;
Выполнить действия 1–3 для всех групп, выбирая контрастные цвета для цветовой заливки очередной группы.
Результаты работы алгоритмов 1.1 и 1.2 представлены в табл.2.1(из приложения)
Алгоритм 1.3. Расчет суммарных групповых значений результативного признака
В ячейке (D41), выделенной для суммарного значения результативного признака Выпуск продукции первой группы, перед формулой поставить знак равенства «=»;
В качестве аргумента функции СУММ() указать диапазон ячеек из табл. 2.1 с результативными значениями уi первой группы (визуально легко определяется по цвету заливки диапазона);
Enter;
Выполнить действия 1–3 поочередно для всех групп, используя цветовые заливки диапазонов.
Алгоритм 1.4. Расчет средних групповых значений результативного признака
В таблице 2.2 приведены формулы для расчета средних групповых значений результативного признака Выпуск продукции.
В ячейке (Е41), выделенной для среднего значения результативного признака Выпуск продукции первой группы, перед формулой поставить знак равенства «=»;
Enter;
Выполнить действия 1–2 поочередно для всех групп;
В ячейках (C46, D46 и E46), выделенных для расчета итоговых сумм:
Перед формулой поставить знак равенства «=»;
Enter.
Результаты работы алгоритмов 1.3 и 1.4 приведены в табл. 2.2
Задача 2. Оценка тесноты связи изучаемых признаков на основе эмпирического корреляционного отношения
Задача решается в два этапа:
Расчет внутригрупповых дисперсий результативного признака.
Расчет эмпирического корреляционного отношения.
Алгоритм 2.1. Расчет внутригрупповых дисперсий результативного признака
В ячейке, выделенной для внутригрупповых дисперсий первой группы (D52), перед формулой поставить знак равенства «=»;
В качестве аргумента функции ДИСПР() указать диапазон ячеек из табл. 2.1 со значениями yi первой группы – визуально легко определяется по цвету заливки диапазона;
Enter;
Выполнить действия 1–3 поочередно для всех групп, используя цветовые заливки диапазонов.
Для расчета итоговой суммы в табл. 2.3 (в ячейке C57) перед формулой необходимо поставить знак равенства «=»;
Enter.
Результат работы алгоритма 2.1 представлен в табл.2.3
Алгоритм 2.2. Расчет эмпирического корреляционного отношения
В ячейке, выделенной для общей дисперсии (А63), перед формулой поставить знак равенства «=»;
Enter;
В ячейке, выделенной для средней из внутригрупповых дисперсий (В63), перед формулой поставить знак равенства «=»;
Enter;
В ячейке, выделенной для значения межгрупповой (факторной) дисперсии (С63), перед формулой поставить знак равенства «=»;
Enter;
В ячейке, выделенной для эмпирического корреляционного отношения (D63), перед формулой поставить знак равенства «=»;
Enter.
Результат работы алгоритма 2.2 представлен в табл.2.4.
Задание 2 Построение однофакторной линейной регрессионной модели связи изучаемых признаков с помощью инструмента Регрессия надстройки Пакет анализа
Алгоритм выполнения Задания 2
Алгоритм 1. Расчет параметров уравнения линейной регрессии и проверки адекватности модели исходным данным
Сервис => Анализ данных => Регрессия => ОК;
Входной интервал Y <= диапазон ячеек таблицы со значениями признака Y – Выпуск продукции (С4:С33);
Входной интервал X – диапазон ячеек таблицы со значениями признака X – Среднегодовая стоимость основных производственных фондов (В4:В33);
Метки в первой строке/Метки в первом столбце – НЕ активизировать;
Уровень надежности <= 68,3 (или 68.3);
Константа–ноль – НЕ активизировать;
Выходной интервал <= адрес ячейки заголовка первого столбца первой выходной результативной таблицы (А75);
Новый рабочий лист и Новая рабочая книга – НЕ активизировать;
Остатки – Активизировать;
Стандартизованные остатки – НЕ активизировать;
График остатков – НЕ активизировать;
График подбора – НЕ активизировать;
График нормальной вероятности – НЕ активизировать;
ОК.
В результате указанных действий осуществляется вывод четырех выходных таблиц на Лист 2 Рабочего файла, начиная с ячейки, указанной в поле Выходной интервал диалогового окна инструмента Регрессия (они имеют следующий вид).
Задание 3 Построение однофакторных нелинейных регрессионных моделей связи признаков с помощью инструмента Мастер диаграмм и выбор наиболее адекватного нелинейного уравнения регрессии
Алгоритмы выполнения Задания 3
Алгоритм 1. Построение уравнений регрессионных моделей для различных видов нелинейной зависимости признаков с использованием средств инструмента Мастер диаграмм
Выделить мышью диаграмму рассеяния признаков, расположенную начиная с ячейки Е4, и увеличить диаграмму на весь экран, используя прием "захват мышью";
Диаграмма => Добавить линию тренда;
В появившемся диалоговом окне Линия тренда выбрать вкладку Тип и задать вид регрессионной модели – полином 2-го порядка;
Выбрать вкладку Параметры и выполнить действия:
Переключатель Название аппроксимирующей кривой: автоматическое/другое – установить в положение другое и ввести имя тренда– полином 2-го порядка;
Поле Прогноз вперед на – НЕ активизировать;
Поле Прогноз назад на – НЕ активизировать;
Флажок Пересечение кривой с осью Y в точке –
НЕ активизировать;
Флажок Показывать уравнение на диаграмме – Активизировать;
Флажок Поместить на диаграмму величину достоверности аппроксимации R2 – Активизировать;
ОК;
Установить курсор на линию регрессии и щелкнуть правой клавишей мыши;
В появившемся диалоговом окне Формат линии тренда выбрать по своему усмотрению тип, цвет и толщину линии;
ОК;
Выделить уравнение регрессии и индекс детерминации R2 и с помощью приема "захват мышью" вынести их за корреляционное поле. При необходимости уменьшить размер шрифта.
Действия 2 – 4 (в п.4 шаги 1–11) выполнить поочередно для следующих видов регрессионных моделей:
– полином 3-го порядка,– степенная функция.
По окончании работы алгоритма 1 выполнить следующие действия:
Присвоить полученной диаграмме заголовок "Диаграмма 2.1" и удалить линии сетки по оси Y (алгоритм 2);
Снять заливку области построения (алгоритм 3);
При необходимости изменить масштаб шкалы осей диаграммы (алгоритм 4).
Алгоритм 2. Присвоение полученной диаграмме заголовка "Диаграмма 2.1" и удаление линий сетки по оси Y
Выделить мышью построенную диаграмму;
Диаграмма => Параметры диаграммы;
В появившемся диалоговом окне Параметры диаграммы выбрать вкладку Заголовки и в поле Название диаграммы ввести заголовок диаграммы "Диаграмма 2.1";
Выбрать вкладку Линии сетки, в полях Ось Х и Ось Y все флажки – Не активизировать;
ОК.
Алгоритм 3. Снятие заливки области построения
Выделить мышью Область построения диаграммы;
Формат => Выделенная область построения;
В появившемся диалоговом окне Формат области переключатель Заливка установить в положение Обычная;
ОК.
Алгоритм 4. Изменение масштаба шкалы осей диаграммы
Выделить на полученной диаграмме ось, для которой необходимо изменить масштаб (подвести курсор к требуемой оси и щелкнуть левой клавишей мыши);
Формат => Выделенная ось;
В появившемся диалоговом окне Формат оси выбрать вкладку Шкала;
В поле Минимальное значение – ввести минимальное (или несколько ниже) значение признака, соответствующего форматируемой оси;
В поле Максимальное значение – ввести максимальное (или несколько выше) значение признака, соответствующего форматируемой оси;
ОК.
Выделить диаграмму и с помощью приема "захват мышью" переместить ее в конец Рабочего файла.
В результате указанных действий для выбранных видов моделей регрессии осуществляется вывод на диаграмму рассеяния 3-х уравнений регрессии, их графиков и значений соответствующих индексов детерминации R2 (Диаграмма 2.1 приведена на рис. 2.1).
ПРИЛОЖЕНИЕ
табл.2.1
рис. 2.1
EMBED MSPhotoEd.3
ВСЕРОССИЙСКИЙ ЗАОЧНЫЙ ФИНАНСОВО-ЭКОНОМИЧЕСКИЙ ИНСТИТУТ
КАФЕДРА СТАТИСТИКИ
Лабораторная работа №3
Автоматизированный анализ динамики социально-экономических явлений в среде MS Excel
Вариант № 6
Исполнитель: Ржавичева Татьяна Владимировна
Специальность: финансы и кредит
Группа: дневная III курс
№ зачетной книжки: 08ФФД40351
Руководитель: Леонова Светлана Николаевна
Орел 2009г
Задание 1.Расчёт и анализ показателей ряда динамики выпуска продукции за шестилетний период.
Алгоритмы выполнения Задания 1
Задача 1. . Расчет цепных и базисных показателей динамики: абсолютный прирост (сокращение); темп роста (снижения); темп прироста (сокращения) и абсолютное значение 1 % прироста
Алгоритм 1.1. Расчёт цепных и базисных показателей динамики, характеризующих изменение отдельных уровней ряда динамики
В ячейке, выделенной для значения абсолютного прироста цепного за первый год (С26), перед формулой поставить знак равенства «=»;
Enter;
Установить курсор в правом нижнем углу ячейки (С26) с формулой – образцом (курсор примет форму черного крестика) и, удерживая левую клавишу мыши в нажатом состоянии, переместить курсор до нижней клетки графы. Отпустить клавишу мыши (формула – образец размножилась на всю графу).
Выполнить действия 1–2 поочередно для всех аналитических показателей ряда динамики табл.3.2:
абсолютного прироста базисного;
темпа роста цепного и базисного;
темпа прироста цепного и базисного;
абсолютного значения 1% прироста.
Результат работы алгоритма 1.1 в табл. 3.2
Задача 2. Расчет средних показателей ряда динамики: средний уровень ряда динамики; средний абсолютный прирост; средний темп роста и средний темп прироста
Алгоритм 1.2. Расчёт средних показателей ряда динамики
В ячейке, выделенной для значения средний уровень ряда динамики (E34), перед формулой поставить знак равенства «=»;
Enter;
Выполнить действия 1–2 поочередно для всех средних показателей ряда динамики табл.3.3:
среднего абсолютного прироста;
среднего темпа роста;
среднего темпа прироста.
Результат работы алгоритма 1.2 в табл. 3.3
Задание 2. Прогноз показателя выпуска продукции на 7-ой год методом экстраполяции.
Алгоритмы выполнения Задания 2
Задача 1. Прогнозирование выпуска продукции предприятием на год вперёд с использованием среднего абсолютного прироста и среднего темпа роста.
Алгоритм 2.1. Расчёт выпуска продукции на год вперёд с использованием среднего абсолютного прироста и среднего темпа роста
В ячейке, выделенной для значений прогнозируемого выпуска продукции по среднему абсолютному приросту (Е41), перед формулой поставить знак равенства «=».
Enter;
Выполнить действия 1–2 для прогнозируемого выпуска продукции по значению среднего темпа роста (табл.3.4).
Результат работы алгоритма 2.1 в табл. 3.4
Задача 2. Прогнозирование выпуска продукции предприятием на год вперёд с использованием аналитического выравнивания ряда динамики по прямой, параболе и полиному 3-го порядка.
Алгоритм 2.2. Построение графика динамики выпуска продукции за 6 лет с использованием средств инструмента МАСТЕР ДИАГРАММ
Выделить мышью ячейки, содержащие выпуск продукции за 6 лет (диапазон ячеек B7:B12);
Вставка=>Диаграмма=>Точечная;
В появившемся диалоговом окне Мастер диаграмм (шаг 1 из 4) выбрать вид точечной диаграммы, на которой значения соединены отрезками;
Далее;
В появившемся диалоговом окне Мастер диаграмм (шаг 2 из 4) выбрать вкладку Ряд и задать имя ряда 1 – «Исходные данные»;
Далее;
В появившемся диалоговом окне Мастер диаграмм (шаг 3 из 4) выбрать вкладку Заголовки и задать названия диаграммы («Прогнозирование выпуска продукции на 7-ой год») и осей Х («Годы») и У («Выпуск продукции. млн. руб.»;;
Готово;
Выделить на полученной диаграмме ось Y (подвести курсор к требуемой оси и щелкнуть левой клавишей мыши);
Формат => Выделенная ось;
В появившемся диалоговом окне Формат оси выбрать вкладку Шкала;
В поле Минимальное значение – ввести минимальное (или несколько ниже) значение признака «Выпуск продукции»;
ОК.
Выделить диаграмму и с помощью приема "захват мышью" переместить ее в конец Рабочего файла.
Алгоритм 2.3. Нахождение тренда ряда динамики выпуска продукции методом аналитического выравнивания и прогнозирование его на год вперед с помощью инструмента Мастер диаграмм
Выделить мышью диаграмму «Прогнозирование выпуска продукции на 7-ой год», расположенную в конце Рабочего файла;
Диаграмма => Добавить линию тренда;
В появившемся диалоговом окне Линия тренда выбрать вкладку Тип и задать вид линии тренда – линейная;
Выбрать вкладку Параметры и выполнить действия:
Переключатель Название аппроксимирующей кривой: автоматическое/другое – установить в положение другое и ввести имя тренда– Прямая;
В поле Прогноз вперед на…едициц ввести значение «1»;
Поле Прогноз назад на…единиц – НЕ активизировать;
Флажок Пересечение кривой с осью Y в точке – НЕ активизировать;
Флажок Показывать уравнение на диаграмме – Активизировать;
Флажок Поместить на диаграмму величину достоверности аппроксимации R2 – Активизировать;
ОК;
Установить курсор на линию тренда, щелкнуть правой клавишей мыши и выбрать меню Формат линии тренда;
В появившемся диалоговом окне Формат линии тренда выбрать вкладку Вид и задать по своему усмотрению тип, цвет и толщину линии;
ОК;
Выделить уравнение линии тренда и индекс детерминации R2 и с помощью приема "захват мышью" вынести их за корреляционное поле. При необходимости уменьшить размер шрифта.
Действия 1 – 4 (в п.4 шаги 1–11) выполнить для линии тренда:
парабола;
полином 3-го порядка.
Результат работы алгоритмов 2.2 – 2.3 представлен на рис.3.1.
Рис.3.1. График динамики выпуска продукции за 6 лет и прогноз выпуска на год вперед
Задание 3. Выявление тенденции развития изучаемого явления (тренда) по данным о выпуске продукции по месяцам за 6-ой год методами скользящей средней и аналитического выравнивания.
Алгоритмы выполнения Задания 3
Алгоритм 3.1. Нахождение значений скользящей средней с помощью инструмента СКОЛЬЗЯЩЕЕ СРЕДНЕЕ надстройки ПАКЕТ АНАЛИЗА
Сервис => Анализ данных => Скользящее среднее => ОК;
Входной интервал <= диапазон ячеек табл. 3.5 со значениями признака – Выпуск продукции (В47:В58);
Метки в первой строке– НЕ активизировать;
Интервал – НЕ активизировать;
Выходной интервал <= адрес первой ячейки третьего столбца выходной результативной таблицы (С47);
Новый рабочий лист и Новая рабочая книга – НЕ активизировать;
Вывод графика –Активизировать;
Стандартные погрешности – НЕ активизировать;
ОК.
Выделить диаграмму и с помощью приема "захват мышью" переместить ее в конец Рабочего файла.
В результате указанных действий осуществляется вывод значений скользящей средней, начиная с ячейки, указанной в поле Выходной интервал диалогового окна инструмента Скользящее среднее, и графика. Они представлены в табл.3.5 и на рис.3.2.
Рис.3.2. График сглаживания ряда динамики выпуска продукции за 6-ой год, сгенерированный в режиме «скользящее среднее» Пакета анализа
Алгоритм 3.2. Приведение выходной таблицы к виду, принятому в статистике
Ячейку С47, содержащую термин " #Н/Д", выделить мышью и очистить, нажав клавишу [Delete];
Ячейки результативной таблицы (С49:С58), содержащие значения «Скользящее среднее», вырезать с помощью инструмента ;
Вставить в табл. 3.5, начиная с ячейки С48, с помощью инструмента ;
Отформатировать таблицу по образцу с помощью инструмента .
Полученная результативная таблица выглядит следующим образом.
График сглаживания ряда динамики выпуска продукции методом скользящей средней представлен на рис. 3.3.
Рис.3.3. График сглаживания скользящей средней ряда динамики выпуска продукции за 6-ой год
Задача 2. Аналитическое выравнивание по прямой и параболе
Алгоритм 3.3. Построение графика динамики выпуска продукции по месяцам за 6-ой год с использованием средств инструмента МАСТЕР ДИАГРАММ
Выделить мышью столбец данных в диапазоне B47:В58 (табл. 3.5);
Вставка=>Диаграмма=>Точечная;
В появившемся диалоговом окне Мастер диаграмм (шаг 1 из 4) выбрать вид точечной диаграммы, на которой значения соединены отрезками;
Далее;
В появившемся диалоговом окне Мастер диаграмм (шаг 2 из 4) выбрать вкладку Ряд и ввести имя ряда «Исходные данные»;
Далее;
В появившемся диалоговом окне Мастер диаграмм (шаг 3 из 4) выбрать вкладку Заголовки и задать названия диаграммы («Выпуск продукции за 6-ой год по месяцам») и осей Х («Месяцы») и У («Выпуск продукции. млн. руб.»;
Готово.
Выделить диаграмму и с помощью приема "захват мышью" переместить ее в конец Рабочего файла.
Алгоритм 3.4. Сглаживание ряда динамики выпуска продукции методом аналитического выравнивания с помощью инструмента Мастер диаграмм
Выделить мышью диаграмму «Выпуск продукции за 6-ой год по месяцам», расположенную в конце Рабочего файла;
Диаграмма => Добавить линию тренда;
В появившемся диалоговом окне Линия тренда выбрать вкладку Тип и задать вид линии тренда – линейная;
Выбрать вкладку Параметры и выполнить действия:
Переключатель Название аппроксимирующей кривой: автоматическое/другое – установить в положение другое и ввести имя тренда - Прямая;
Поле Прогноз вперед на – НЕ активизировать;
Поле Прогноз назад на – НЕ активизировать;
Флажок Пересечение кривой с осью Y в точке – НЕ активизировать;
Флажок Показывать уравнение на диаграмме – Активизировать;
Флажок Поместить на диаграмму величину достоверности аппроксимации R2 – Активизировать;
ОК;
Установить курсор на линию тренда, щелкнуть правой клавишей мыши и выбрать меню Формат линии тренда;
В появившемся диалоговом окне Формат линии тренда выбрать вкладку Вид и задать по своему усмотрению тип, цвет и толщину линии;
ОК;
Выделить уравнение линии тренда и индекс детерминации R2 и с помощью приема "захват мышью" вынести их за корреляционное поле. При необходимости уменьшить размер шрифта.
Действия 1 – 4 (в п.4 шаги 1–11) выполнить для линии тренда парабола.
По окончании работы алгоритма 2.4 выполнить следующие действия:
Снять заливку области построения;
При необходимости изменить масштаб шкалы осей диаграммы.
Результат работы алгоритмов 3.3 – 3.4 представлен на рис.3.4.
Рис. 3.4. График сглаживания по прямой и параболе ряда динамики выпуска продукции за 6-ой год
