Размещено на http://www.allbest.ru/

Методические рекомендации

к проведению занятия по курсу «Автоматизация управления предпринимательской деятельностью»

(очное отделение)

Составил доцент кафедры А.В. Гармашов

Работа: Анализ данных для моделирования временных рядов с использованием надстройки Excel Пакет анализа.

Цель работы: научиться проводить анализ экономических процессов с помощью табличного процессора Excel XP.

План занятия

1. Основные сведения о табличном процессоре Excel XP.

2. Работа с формулами и функциями. Система адресации.

3. Надстройка «Пакет анализа».

4. Статистический анализ временных рядов.

5. Использование Мастера диаграмм.

Основные вопросы

1. Требования к исходной информации.

2. Этапы построения прогноза по временным рядам.

3. В чем заключается предварительный анализ данных?

4. Что такое модели кривых роста?

5. Какие виды кривых роста наиболее часто используются при анализе временных рядов?

6. Как оценить качество построенных моделей?

7. Как произвести установку надстройки «Пакет анализа»?

8. Перечислите основные элементы интерфейса надстройки «Пакет анализа».

9. Какие системы адресации используются при работе с надстройкой «Пакет анализа»?

10. Охарактеризуйте основные направления применения надстройки «Пакет анализа».

При решении задач рекомендуется использовать стандартную офисную программу Excel. Пакет анализа в Excel -- это надстройка, которая предоставляет широкие возможности для проведения статистического анализа.

еxcel надстройка пакет анализ диаграмма

Установка Пакета анализа

Ни в одном меню стандартной конфигурации программы Excel не найдется указания на Пакет анализа. Даже после установки с компакт-диска Excel он не появится в меню Сервис до тех пор, пока вы не выполните следующие действия:

1) выбрать команду Сервис =>Надстройки;

2) в диалоговом окне Надстройки (рис. 1) установить флажок Пакет анализа, а затем нажать кнопку ОК;

3) выбрать команду Сервис =>Анализ данных.

4) Если в меню отсутствует команда Анализ данных, то необходимо выполнить установку Пакета анализа с компакт-диска Excel. После этого в нижней части меню Сервис появится новая команда Анализ данных, которая предоставляет доступ к средствам анализа.

5) Для активизации надстройки Пакет анализа следует установить соответствующий флажок.

Рис. 1. Установка Пакета анализа.

Пример 1. Проверка наличия тренда..

Один из способов проверки обнаружения тренда основан на сравнении средних уровней ряда: временной ряд разбивают на две примерно равные по числу уровней части, каждая из которых рассматривается как некоторая самостоятельная выборочная совокупность, имеющая нормальное распределение. Если временной ряд имеет тенденцию к тренду, то средние, вычисленные для каждой совокупности, должны существенно (значимо) различаться между собой. Если же расхождение незначительно, несущественно (случайно), то временной ряд не имеет тенденции. Таким образом, проверка наличия тренда в исследуемом ряду сводится к проверке гипотезы о равенстве средних двух нормально распределенных совокупностей.

Определим наличие основной тенденции (тренда) по данным табл. 1 (рис. 2).

Таблица 1

Урожайность ячменя в одной из областей Среднего Поволжья, ц/га

Годы	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15
Урожайность	14,1	9,3	19,4	19,7	5,4	24,5	13,8	24,5	14,7	16,6	5,6	16,2	25,3	11,9	18,5

Рис. 2. График урожайности ячменя.

Решение

1. Делим исходный временной ряд на две примерно равные по числу уровней части: п₁=7, п₂=8 (п₁ + п₂ = п=15).

2. Для каждой из этих частей вычисляем средние значения: Y₁ = 15,13; Y₂ = 16,66 и дисперсии: S²_y1 = 42,15; S²_y2 = 41,22.

3. Проверяем гипотезу о равенстве (однородности) дисперсий обеих частей ряда с помощью F-критерия Фишера. Для вычисления F-критерия большую дисперсию делят на меньшую: Fрасч = S²_y1/ S²_y2 = 42,15 / 41,22 = 1,022,F_Kp(0,05; 6,7) =3,86.

Так как,F_pacч < F_Kp, то нет оснований отвергать нулевую гипотезу. По данным наблюдения дисперсии генеральных совокупностей равны S²₁ = S²₂, исправленные выборочные дисперсии (S²_y1 и S²_y2) различаются незначимо (расхождение между ними -- величина случайная).

4. Тогда можно проверить основную гипотезу о равенстве средних значений с использованием t-критерия Стьюдента:

t_кр(0,05, 13) = 2,16.

Так как |t_расч| < t_кр, то нет оснований отвергать нулевую гипотезу о равенстве средних, расхождение между вычисленными средними незначимо. Отсюда вывод: тренд урожайности ячменя отсутствует.

Решение задачи с помощью Пакета анализа Excel

1. Гипотезу о равенстве дисперсий проверим с помощью F-теста, который можно найти среди инструментов Анализа данных (рис. 3).



Рис. 3. Вызов надстройки Excel Анализ данных.

2. Вводим данные для выполнения F-теста, указывая интервал для первой и второй переменных (рис. 4). Результат выполнения теста приведен в табл. 2. Анализируя результаты выполнения двухвыборочного F-теста для проверки гипотезы о равенстве дисперсий, приходим к выводу, что исправленные выборочные дисперсии (S²_y1 и S²_y2) различаются незначимо.

Таблица 2

Результат выполнения двухвыборочного F-теста для дисперсии

	Переменная 1	Переменная 2
Среднее Дисперсия Наблюдения df-- число степеней свободы F P(F <= f) одностороннее F критическое одностороннее	15,129 42.146 7 6 1.022 0.481 3.866	16.663 41.220 8 7

Рис. 4. Ввод данных для двухвыборочного F-теста.

3. Выбираем инструмент анализа Двухвыборочный t-тест с одинаковыми дисперсиями (рис. 5). Вводим данные. Результат выполнения t-теста приведен в табл. 3, анализируя который убеждаемся, что тренда нет.

Рис. 5. Ввод данных для двухвыборочного t-теста с одинаковыми дисперсиями

Таблица 3

Результат выполнения t-теста

Двухвыборочный t-тест с одинаковыми дисперсиями	Переменная 1	Переменная 2
Среднее Дисперсия Наблюдения Объединенная дисперсия Гипотетическая разность средних df-- число степеней свободы t-статистика P(T <= t) одностороннее t критическое одностороннее P(T <= t) двустороннее t критическое двустороннее	15,129 42.146 7 41,647 0.000 13 -0,459 0,327 1,771 0,654 2,160	16.663 41.220 8

Пример 2. На основании данных, приведенных в табл. 4, требуется:

Таблица 4

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

/	1	2	3	4	5	6	7	8	9
Y	41	46	49	48	65	55	61	59	65

1) построить линейную модель Y(t) = а₀ + а_{t, параметры которой оценить МНК;

2) оценить адекватность построенной модели на основе исследования:

* случайности остаточной компоненты по критерию пиков;

* независимости уровней ряда остатков по d-критерию (в качестве критических значений следует использовать уровни d₁ = 1,08 и d₁ = 1,36) и по первому коэффициенту автокорреляции, критический уровень которого r(1) = 0,36;

* нормальности распределения остаточной компоненты по RS-критерию с критическими уровнями 2,7-3,7;

3) для оценки точности модели используйте среднеквадратическое отклонение и среднюю по модулю относительную ошибку;

4) построить точечный и интервальный прогнозы на два шага вперед (для вероятности Р= 70% используйте коэффициент равный 1,12);

5) отобразить на графике фактические данные, результаты расчетов и прогнозирования.

Решение

1. Ввод исходных данных.

Результат показан на рис. 6.

Рис. 6. Ввод исходных данных

2. Оценка параметров модели с помощью надстройки EXCEL Анализ данных.

Построим линейную однопараметрическую модель регрессии Y от t. Для проведения регрессионного анализа выполните следующие действия:

* выберите команду Сервис => Анализ данных;

* в диалоговом окне Анализ данных выберите инструмент Регрессия (рис. 7), а затем щелкните на кнопке ОК;

* в диалоговом окне Регрессия в поле Входной интервал Y введите адрес одного диапазона ячеек, который представляет зависимую переменную. В поле Входной интервал X введите адрес диапазона, который содержит значения независимой переменной t (рис. 8);

* если выделены и заголовки столбцов, то установите флажок Метки в первой строке;

* выберите параметры вывода. В данном примере -- Новая рабочая книга;

* в поле График подбора поставьте флажок;

* в поле Остатки поставьте необходимые флажки и нажмите кнопку ОК.

Рис 7. Выбор инструмента анализа данных Регрессия.



Рис. 8. Ввод исходных данных для Регрессии

Результат регрессионного анализа содержится в табл 5 и 6.

Таблица 5

Переменная	Коэффициенты	Стандартная ошибка	t-статистика
Y-пересечение	а0	40,5	3,37	12,01
t	а1	2,77	0,60	4,62

Таблица.6. Вывод остатка

Наблюдение	Предсказанное Y	Остатки
1	43,27	-2,27
2	46,03	-0,03
3	48,80	0,20
4	51,57	-3,57
5	54,33	10,67
6	57,10	-2,10
7	59,87	1,13
8	62,63	-3,63
9	65,40	-0,40
Сумма		0,00

Во втором столбце табл. 5 содержатся коэффициенты уравнения регрессии a₀, a₁; в третьем столбце - стандартные ошибки коэффициентов уравнения регрессии, а в четвертом - t-статистика, используемая для проверки значимости коэффициентов уравнения регрессии.

Уравнение регрессии зависимости у_t (прибыль коммерческого банка) от t (время) имеет вид Y(t) = 40,5 + 2,77t.

3. Оценка качества построенной модели.

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

* При проверке независимости (отсутствие автокорреляции) определяется отсутствие в ряду остатков систематической составляющей, например, с помощью d-критерия Дарбина-Уотсона по формуле:

Так как d' попало в интервал от d₁ до d₂, то по данному критерию нельзя сделать вывод о выполнении свойства независимости.

Необходимо вычислить коэффициент автокорреляции первого порядка по формуле:

т.е. фактическое значение больше табличного. Это означает, что в ряду динамики имеется автокорреляция, следовательно, модель по этому критерию неадекватна.

* Проверку случайности уровней ряда остатков проведем на основе критерия поворотных точек. Количество поворотных точек равно 6 (рис. 9). Неравенство выполняется (6 > 2). Следовательно, свойство случайности выполняется. Модель по этому критерию адекватна.

* Соответствие ряда остатков нормальному закону распределения определим при помощи RS-критерия:

RS=[е_max-е_min]/S_е,

где е_max -- максимальный уровень ряда остатков, е_max = 10,67;

е_min -- минимальный уровень ряда остатков, е_min = -3,63; S_е -- среднеквадратическое отклонение,

Расчетное значение попадает в интервал (2,7--3,7), следовательно, выполняется свойство нормальности распределения. Модель по этому критерию адекватна.

* Проверка равенства нулю математического ожидания уровней ряда остатков.

В нашем случае е= 0, поэтому гипотеза о равенстве математического ожидания значений остаточного ряда нулю выполняется.

В табл. 7 собраны данные анализа ряда остатков.

Таблица 7. Анализ ряда остатков

Проверяемое свойство	Используемые статистики	Граница	Вывод
	наименование	значение	нижняя	верхняя
Независимость	d-критерий Дарбина- Уотсона r(1)-коэффициент автокорреляции	d = 2,84 d = 4 - 2,84 = = 1,16 -0,44	0,98	1,36 0,36	нельзя сделать вывод по этому критерию abs[r(l)]>0,36 неадекватна abs[r(l)]>0,36 Нет
Случайность	Критерий пиков (поворотных точек)	6>2	2	адекватна
Нормальность	RS-критерий	3,28	2,6	2,7	адекватна
Среднее = 0 ?	t-статистика Стьюдента	0,000	-2,179	2,179	адекватна
	Вывод: модель статистически неадекватна

4. Построить точечный и интервальный прогнозы на два шага вперед (для вероятности 70% использовать t=1,12):

Y(t) = а₀ + а_{t= 40,5 + 2,77*10 =68,17

Y(t) = а₀ + а_{t= 40,5 + 2,77*11 =70,93.

Для построения интервального прогноза рассчитаем доверительный интервал. Примем значение уровня значимости б = 0,3, следовательно, доверительная вероятность равна 70%, а критерий Стьюдента при н = п -- 2 = 7 равен 1,12. Ширину доверительного интервала вычислим по формуле:



Далее вычисляем верхнюю и нижнюю границы прогноза (см. табл. 8).

Таблица 8

n + k	U (k)	Прогноз	Формула	Верхняя граница	Нижняя граница
10 11	U(1) = 6,42 U(2) = 6,79	68,17 70,93	Прогноз + U(1) Прогноз- U(2)	74,59 77,73	61,75 64,14

5. Отобразить на графике фактические данные, результаты расчетов и прогнозирования.

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

Выберем тип диаграммы -- точечная, на которой значения соединены отрезками.

В формате области построения укажем тип заливки -- обычная; рамка -- невидимая.

Далее на графике изобразить результаты прогнозирования. Для этого следует «кликнуть» правой кнопкой мышки и в появившемся меню выбрать Исходные данные. Затем последовательно нажать кнопки Ряд, Добавить и указать диапазоны размещения данных.

* В диалоговом окне Исходные данные в поле значения Y введите адрес диапазона ячеек, который представляет прогноз зависимой переменной. В поле значения X введите адрес диапазона, который содержит значения независимой переменной t.

* Аналогично вводятся данные для верхних и нижних границ прогноза.

Анализ временных рядов с помощью инструмента Мастер диаграмм

При анализе временных рядов широко применяются графические методы. Это объясняется тем, что табличное представление временного ряда и описательные характеристики не позволяют понять характер процесса, а по графику временного ряда можно сделать определенные выводы, которые потом могут быть проверены с помощью расчетов:

* наличие тренда и его характер;

* наличие сезонных и циклических компонент;

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

Так графический анализ ряда обычно задает направление его дальнейшего анализа. В Excel для этого можно использовать средство Мастер диаграмм.

Для создания диаграммы с помощью средства Мастер диаграмм необходимо выделить данные, которые будут отображены на диаграмме (это необязательная операция, однако она позволит сэкономить время при работе мастером). Сюда следует включить как числовые данные, так и подписи к рисункам. Excel автоматически распознает подписи и использует их при построении диаграммы Пример рабочего листа, соответствующая часть которого (ячейки А5:А17) будет выделена для Мастера диаграмм.

Работа Мастера диаграмм состоит из четырех основных шагов, выполнение которых рассмотрим на следующем примере.

Пример. Построить график временного ряда Индекс потребительских расходов, выделить тренд этого временного ряда и сделать прогноз на два шага вперед. Исходные данные по этому временному ряду за 16 месяцев приведены в табл. 9.

Таблица 9. Индекс потребительских расходов

1	2	3	4	5	6	7	8	9	10	11	12	13	14	15
100	98,4	101,2	103,5	104,1	107	107,4	108,5	108,3	109,2	110,1	110,7	110,3	111,8	112,3

Шаг 1. Выбор типа и вида диаграммы. Во вкладке Стандартные можно увидеть основные типы диаграмм. На вкладке Стандартные выделен тип График. Выбрав вид График с маркерами, необходимо нажать на кнопку Далее.

Шаг 2. Выбор и уточнение ориентации диапазона данных и ряда. На экране появилось диалоговое окно. Вкладка Диапазон данных позволяет выполнить следующие операции:

* выбрать (или изменить) диапазон данных листа. Если перед началом работы с Мастером диаграмм данные не были выделены, то, используя это поле, можно выбрать их сейчас;

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

* Вкладка Ряд позволяет следующие операции:

добавить и удалить ряды

присваивать рядам имена

выделять данные

изменять подписи категорий.

Шаг 3. Настройка диаграммы

Шаг 4. Выбор месторасположения диаграммы. На этом шаге определяется местоположение созданной диаграммы.

Индивидуальное задание

В задачах 1-10 с помощью надстройки «Пакет анализа» составить уравнение тренда и определить его коэффициенты. Вычислить прогнозируемое значение для ?t.

1. Реализация витамина С по годам по аптекоуправлению (тыс. упаковок): ?t=2

год	1998	1999	2000	2001	2002	2003	2004	2005
t	1	2	3	4	5	6	7	8
X(t)	16	18	11	14	26	35	12	7

2. Потребление сульфаниламидных препаратов, по данным аптеки, следующее (у.е.): ?t=1

год	1997	1998	1999	2000	2001	2002	2003	2004	2005
t	1	2	3	4	5	6	7	8	9
X(t)	12	19	27	30	35	40	47	46	50

3. Оптовый товарооборот в аптеке по годам (у.е.): ?t=2

год	1996	1997	1998	1999	2000	2001	2002	2003	2004
t	1	2	3	4	5	6	7	8	9
X(t)	32	34	36	41	44	50	50	53	56

4. Потребление (по одной аптеке) антибиотиков (у.е.): ?t=1

год	1998	1999	2000	2001	2002	2003	2004	2005
t	1	2	3	4	5	6	7	8
X(t)	30	36	48	32	44	52	46	56

5. Реализация аспирина по аптеке (у.е.): ?t=2

год	1998	1999	2000	2001	2002	2003	2004	2005
t	1	2	3	4	5	6	7	8
X(t)	32	36	31	20	16	10	12	10

6. Изменение числа работников, занятых в системе районного аптекоуправления (чел.): ?t=1

год	1998	1999	2000	2001	2002	2003	2004	2005
t	1	2	3	4	5	6	7	8
X(t)	28	36	32	28	22	24	26	20

7. Уменьшение дефицита спазмалитиков по аптекоуправлению (у.е.): ?t=2

год	1998	1999	2000	2001	2002	2003	2004	2005
t	1	2	3	4	5	6	7	8
X(t)	36	42	34	38	12	32	26	20

8. Заготовка лекарственного сырья по аптекоуправлению (у.е.): ?t=1

год	1998	1999	2000	2001	2002	2003	2004	2005
t	1	2	3	4	5	6	7	8
X(t)	46	52	44	48	32	42	36	30

9. Изменение потребления желчегонных препаратов по аптеке (у.е.): ?t=2

год	1998	1999	2000	2001	2002	2003	2004	2005
t	1	2	3	4	5	6	7	8
X(t)	0,0	1,7	1,5	1,7	1,5	2,1	2,5	3,6

10. Объем продажи витамина А по годам по данным аптекоуправления (тыс.упаковок): ?t=1

год	1998	1999	2000	2001	2002	2003	2004	2005
t	1	2	3	4	5	6	7	8
X(t)	16	18	11	14	26	35	12	7

11. Реализация витамина С по годам по аптекоуправлению (тыс. упаковок): ?t=2

год	1998	1999	2000	2001	2002	2003	2004	2005
t	1	2	3	4	5	6	7	8
X(t)	16	18	11	14	26	35	12	7

12. Потребление анальгетиков, по данным аптеки, следующее (тыс.рублей): ?t=1

год	1997	1998	1999	2000	2001	2002	2003	2004	2005
t	1	2	3	4	5	6	7	8	9
X(t)	13	16	25	31	33	46	47	43	50

13. Оптовый товарооборот в аптеке по годам (у.е.): ?t=1

год	1996	1997	1998	1999	2000	2001	2002	2003	2004
t	1	2	3	4	5	6	7	8	9
X(t)	35	36	36	41	47	50	57	58	60

14. Потребление (по одной аптеке) антибиотиков (у.е.): ?t=2

год	1998	1999	2000	2001	2002	2003	2004	2005
t	1	2	3	4	5	6	7	8
X(t)	40	46	58	52	54	52	56	66

15. Реализация парацетамола по аптеке (тыс. руб.): ?t=1

год	1998	1999	2000	2001	2002	2003	2004	2005
t	1	2	3	4	5	6	7	8
X(t)	32	34	31	34	46	50	52	50

Литература

1. А.И.Мишенин. Теория экономических информационных систем. М.: Финансы и Статистика, 1999.

2. Под ред. В.В. Дика. Информационные системы в экономике. М.: Финансы и статистика 1996г.

3. Т.И. Макарова. Информатика. С-Пб., Питер, 1998

4. Под ред. П.В. Конюховского и Д.Н. Колесова. Экономическая информатика. С-Пб., Питер, 2001

5. Под ред. В.П. Косарева. Компьютерные системы и сети. М.: Финансы и Статистика, 1999

6. И.В. Орлова. Экономико-математическое моделирование. М.: Вузовский Учебник, ВЗФЭИ 2004

Размещено на Allbest.ru