II. Параметры статистической взаимосвязи случайных величин

1. Создать файл на основе шаблона «Excel_Lab_10.xltx» лист «Экономические показатели».
2. Оценить взаимосвязь показателей, получив результаты вычислений с помощью инструментов Ковариация и Корреляция.

 

III. Сглаживание экспериментальных данных

1. Используя исходные данные п. II.1, постройте для показателя ХХХ новый ряд методом скользящего среднего с периодом осреднения N. Для улучшения наглядности графика отформатируйте ось значений (см. таблицу ниже).

 

1. Постройте для показателя ХХХ новые ряды методом экспоненциального сглаживания с факторами затухания D₁ и D₂. Для улучшения наглядности графиков отформатируйте ось значений. Оцените влияние фактора затухания на значения ряда.

Вариант
XXX	Средняя зарплата	Вклады населения	Объем розничного товарооборота
N
D1	0,22	0,26	0,29
D2	0,4	0,43	0,47

 

Контрольные вопросы:

1. Поясните заполнение диалога и результат работы одного из инструментов надстройки «Анализ данных»: генерация случайных чисел, описательная статистика, ранг и персентиль, гистограмма.
2. По каким функциям вычисляются плотность распределения вероятности и интегральная функция для нормального закона, биномиального закона, закона Пуассона? Назовите параметры этих законов.
3. Назовите функции для вычисления статистических характеристик случайных величин и поясните смысл этих характеристик.

 

Тема 11. Средства регрессионного анализа в Excel.

На оглавление

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

Теоретические сведения

Регрессионный анализ — это статистический метод, позволяющий найти уравнение, которое наилучшим образом описывает статистическую зависимость между сериями значений каких-либо величин. В электронных таблицах Excel реализованы три способа регрессионного анализа: 1) инструмент Регрессия из надстройки «Анализ данных» (вкладка Данные—Анализ —Анализ данных); 2) трендовые модели; 3) статистические функции. Если надстройка «Анализ данных» не отображается, то необходимо используя кнопку Офис открыть Параметры Excel – Надстройки и выбрать из списка неактивных надстроек приложений Анализ данных и нажать кнопку Перейти.

Функция ЛИНЕЙН возвращает коэффициенты линейной регрессии вида и дополнительную регрессионную статистику. В данной формуле: m_i — коэффициенты при независимых переменных x_i, n — количество независимых переменных, b — константа.

Аргументы функции:

1) известные значения Y — диапазон зависимой переменной;

2) известные значения X — диапазон п независимых переменных;

3) конст = 1, чтобы константа b вычислялась обычным образом;

4) статистика = 1, чтобы выводилась дополнительная регрессионная статистика.

Функция вводится как табличная. Для получения результата выделяется 5 строк (чтобы выводилась дополнительная регрес­сионная статистика) и п + 1 столбцов. Структура результата представлена в таблице:

тп	тп-1	…	т1	b
s [ тп ]	s [ тп-1 ]	…	s [ т1 ]	s [ b ]
R^2	s [ y ]	#н/д	#н/д	#н/д
F	df	#н/д	#н/д	#н/д
SSreg	SSresid	#н/д	#н/д	#н/д

В первой строке таблицы выводятся значения коэффициентов m_i и b; во второй — среднеквадратические отклонения коэффициентов при независимых переменных s [ т_i ] и константы s [ b ]; затем располагаются следующие величины:

- коэффициент детерминированности R², который изменяется в пределах [0; 1]. Это величи­на, характеризующая степень взаимосвязи между зависимой и независимыми переменны­ми. Качественную оценку взаимосвязи можно провести по шкале Чеддока;

R^2	0,1—0,3	0,3—0,5	0,5—0,7	0,7—0,9	0,9—0,99
Характеристика силы связи	слабая	умеренная	заметная	высокая	весьма высокая

- среднеквадратическое отклонение зависимой переменной s [ y ];

- F-статистика, используемая для оценки достоверности полученного уравнения;

- число степеней свободы df;

- регрессионная SS_reg и остаточная SS_resid суммы квадратов.

Функция ЛГРФПРИБЛ определяет параметры экспоненциального уравнения регрессии вида и дополнительную регрессионную статистику.

ЛГРФПРИБЛ имеет такие же аргументы, правила ввода и аналогичную структуру результата с функцией ЛИНЕЙН, но в отличие от ЛИНЕЙН во второй строке таблицы результата вместо среднеквадратических отклонений коэффициентов вычисляются их натуральные логарифмы, т.е. ln s [ т_i ] и ln s [ b ].

Функция FРАСП возвращает F-распределение вероятности и используется, чтобы определить, имеют ли два множества данных различные степени разброса результатов. В регрессионном анализе с помощью этой функции оценивается достоверность уравнения — b _F.

При заполнении аргументов функции FРАСП используются данные полученные с помощью функции ЛИНЕЙН или ЛГРФПРИБЛ:

1) X = F;

2) Степени_свободы1 (числитель степеней свободы) = n;

3) Степени_свободы2 (знаменатель степеней свободы) = df.

Тогда b _F = 1 – FРАСП (F; n; df)

 

Функция СТЬЮДРАСП возвращает вероятность для t-распределения Стьюдента. В регрессионном анализе с помощью двустороннего распределения Стьюдента оценивается достоверность коэффициентов — b _t.

При заполнении аргументов функции СТЬЮДРАСП используются данные полученные с помощью функции ЛИНЕЙН или ЛГРФПРИБЛ:

1) X = t, причем значение t-статистики предварительно вычисляется для каждого коэффициента по формулам:

a) для линейной и полиномиальной регрессии

b) для экспоненциальной регрессии

2) Степени_свободы = df;

3) Хвосты = 2.

Тогда b _t = 1 – СТЬЮДРАСП (| t |; df; 2)

 

Инструмент Регрессия используется для нахождения коэффициентов линейной регрессии и оценки их достоверности. При заполнении диалога Регрессия следует:

1) Входной интервал Y — указать диапазон значений зависимой переменной (1 столбец);

2) Входной интервал X — указать диапазон значений независимых переменных (до 16 столбцов);

3) Установить флажки Остатки, График остатков;

4) Выходной интервал — указать верхнюю левую ячейку для вывода результата.

 

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

1) Вывод итогов — содержит значения среднеквадратического отклонения Y — s[ y ], коэффициента корреляции Пирсона R, коэффициента детерминированности R²;

2) Дисперсионный анализ

	df	SS	MS	F	ЗначимостьF
Регрессия	n	SSreg	SSreg /n	MSreg/MSresid	1 – b F
Остаток	df	SSresid	SSresid /df

 

3) Параметры модели

	Коэффи­циенты	Стандартная ошибка	t-статистика	P-значение	Нижние 95%	Верхние 95%
Y-пересечение	b	s [ b ]	tb	1 – b tb	нижняя граница доверительного интервала для b и mi при уровне значимости 95%	верхняя граница доверительного интервала для b и mi при уровне значимости 95%
Переменная Х1	m1	s [ m1 ]	tm1	1 – b tm1
…	…	…	…	…
Переменная Хn	mn	s [ mn ]	tmn	1 – b tm1

4) Вывод остатков — содержит расчетные (предсказанные) значения Y и остатки (разность между расчетным и фактическим Y).

Примечание. Смысл буквенных обозначений в таблицах Дисперсионный анализ и Параметры модели пояснен на странице выше при рассмотрении статистических функций. Смысл параметров Значимость F и Р-значение — это вероятность того, что уравнение регрессии и коэффициенты не достоверны, т.е. Значимость F = FРАСП (F; n; df) и
Р-значение = СТЬЮДРАСП (| t |; df; 2).

Инструмент Регрессия и функция ЛИНЕЙН могут также использоваться для нахождения коэффициентов полиномиальной регрессии. Например, чтобы получить уравнение зависимости y = f (х₁, х₂) в виде полинома 2-й степени, нужно предварительно в смежных с х₁ и х₂ столбцах вычислить х₁², х₂², х₁× х₂ и рассматривать их как отдельные переменные. Таким образом, полиномиальная регрессия двух независимых переменных приводится к линейной регрессии пяти переменных:

.

В случае парной регрессии, если имеется одна зависимая и одна независимая переменная, применим регрессионный анализ по диаграмме, который заключается в построении линий тренда. Порядок его выполнения:

1) По исходным данным построить диаграмму. Если независимая переменная (х) является временным рядом или ее значения меняются на фиксированный шаг, то тип диаграммы выбирается Гистограмма, График, С областями. Если значения х меняются на произвольный шаг, то строится Точечная диаграмма.

2) Выполнить команду вкладка Работа с диаграммами – Анализ –Линия тренда – Дополнительные параметры линии тренда или через контекстное меню выбрать команду Добавить линию тренда.

3) В диалоге Параметры линии тренда выбрать способ аппроксимации (линейный, экспоненциальный, полиномиальный, логарифмический, степенной) и задать:

a) имя линии тренда;

b) на сколько шагов делать прогноз вперед и назад (если это требуется);

c) установить флажки Показывать уравнение на диаграмме и Поместить на диаграмму величину R². (см. рисунок ниже).

Пример 11.1. Определить, используя соответствующую функцию, уравнение линейной зависимости затрат на ремонт от возраста оборудования и дополнительную регрессионную статистику по данным, расположенным в диапазоне А3:В12. Спрогнозировать по полученному уравнению величину затрат на ремонт для данного возраста оборудования.

Решение:

1) Для вычисления коэффициентов линейной регрессии и дополнительной регрессионной статистики используется функция ЛИНЕЙН, которая возвращает массив результатов. Необходимо поэтому:

a) выделить 2 столбца, так как одна независимая переменная, и 5 строк (E2:F6);

b) вставить функцию ЛИНЕЙН и заполнить ее аргументы. Диапазон зависимой переменной — В3:В12; диапазон независимой переменной А3:А12;

c) не нажимая кнопку ОК, нажать комбинацию клавиш Ctrl + Shift + Enter. Диапазон E2:F6 будет заполнен данными (см. рисунок), по которым можно составить линейное уравнение —

2) Для прогнозирования затрат на ремонт (Y _пр) нужно подставить имеющиеся значения возраста оборудования (Х) в полученное уравнение (см. формулу и значения на рисунке в столбце С).

Пример 11.2. Оценить степень взаимосвязи, достоверность уравнения и коэффициентов, найденных в примере 4.1.

Решение:

1) Из результатов предыдущего примера видно, что R² = 0,889. По шкале Чеддока это соответствует высокой силе связи между переменными.

2) Для оценки достоверности уравнения используется величина F = 64,04 (ячейка Е5) и df = 8 (ячейка F5). Результат вычисления достоверности уравнения и формула приведены на рисунке в ячейках F8 и G8.

3) t-статистика для коэффициентов вычисляется в ячейках E11:F11 как отношение значения коэффициента к его среднеквадратическому отклонению.

4) Для оценки достоверности коэффициентов используется t-статистика и df. Результат вычисления достоверности коэффициентов и формула приведены на рисунке в ячейках Е12:G12.

5) Из полученных результатов следует, что уравнение и коэффициенты имеют высокую достоверность, так как значения b_F и b_t близки к 1.

Пример 11.3. Построить линейную трендовую модель зависимости затрат на ремонт от возраста оборудования по исходным данным примера 11.1.

Решение:

1) Выделить диапазон А3:В12 и построить точечную диаграмму зависимости затрат на ремонт от возраста оборудования с помощью мастера диаграмм.

2) Выделить диаграмму, выполнить команду вкладка Работа с диаграммами – Анализ –Линия тренда – Дополнительные параметры линии тренда или через контекстное меню выбрать команду Добавить линию тренда.

3) В открывшемся окне выбрать тип аппроксимации — линейная и задать параметры линии тренда, как показано на рисунке:

4)В результате на диаграмме появится линия тренда, коэффициент детерминированности R² и линейное уравнение, совпадающее с полученным в примере 11.1.

Пример 11.4. Определить уравнение линейной регрессии, оценить степень взаимосвязи, достоверность уравнения и коэффициентов (исходные данные примера 11.1), используя инструмент Регрессия.

Решение:

1) Выполнить команду вкладка Данные—Анализ —Анализ данных —Регрессия и заполнить открывшийся диалог:

2) После нажатия ОК, начиная с ячейки А17, будут выведены 4 таблицы, которые более компактно представлены на рисунке:

3) столбцы b_F и b_t с помощью инструмента не выводятся и вычислены дополнительно по формулам b_F = 1 – Значимость F и b_t = 1 – Р-значение.

Практические задания

На оглавление

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

I. Создать файл на основе шаблона «Excel_Lab_10.xltx». На листе «Задача 1» даны производительность и цена различных моделей оборудования. Требуется провести регрессионный анализ данных по соответствующим функциям:

1. Определить коэффициенты, дополнительную регрессионную статистику и составить уравнения линейной и экспоненциальной регрессии, устанавливающие зависимость цены (экономического параметра) от производительности (технического параметра).

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

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

Вариант	1	2	3
Пр1
Пр2
Пр3

 

II. На листе «Задача 2» приведены некоторые экономические показатели по Беларуси.

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

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

 

IV. На листе «Задача 3» построить трендовые модели изменения курса доллара:

а) линейную;

b) экспоненциальную;

с) полиномиальную (2-я степень).

Для каждой линии тренда вывести на диаграмме уравнение, R², сделать прогноз вперед на 3 периода.

По полученным уравнениям вычислить курс доллара вперед на 3 периода (на начало ноября, декабря, января).

Контрольные вопросы:

1. Поясните правила ввода и интерпретации результатов функций ЛИНЕЙН и ЛГРФПРИБЛ?
2. Для чего в регрессионном анализе применяются коэффициент детерминированности, F-статистика, t-статистика?
3. Кратко охарактеризуйте содержание таблиц, которые выводит инструмент «Регрессия»?
4. Как проводить регрессионный анализ данных по диаграмме?

Литература

На оглавление

1. Microsoft Office 2007: все программы пакета: Word, Excel, Access, PowerPoint, Publisher, Outlook, OneNote, InfoPath, Groove / Тихомиров А. Н. [и др.]. - Санкт-Петербург: Наука и техника, 2009. - 599 с.

2. Microsoft Office Excel 2007: [русская версия: перевод с английского] / Кёртис Д. Фрай. - Москва: ЭКОМ, 2009. - 479 с.

3. Excel 2007 для менеджеров и экономистов: логистические, производственные и оптимизационные расчеты / Александр Трусов. - Санкт-Петербург: Питер: Питер Пресс, 2009. – 254 с.

4. Графики, вычисления и анализ данных в Excel 2007 / Серогодский В. В. [и др.]. - Санкт-Петербург: Наука и техника, 2009. - 333 с.

5. Функции в Excel 2007: справочник пользователя / Минько А. А.. - Москва: Эксмо, 2008. - 480 с.

6. Экономическая информатика / под ред. П.В. Конюховского и Д.Н. Колесова. – СПб: Питер, 2001. – 560 с.

7. Гарнаев А.Ю. Excel, VBA, Internet в экономике и финансах. – СПб.: BHV–Петербург, 2001. – 816 с.

8. Додж М., Кината К., Стинсон К. Эффективная работа с Excel 7.0: пер. с англ. – СПб: Питер, 1996. – 1031 с.

9. Обработка и анализ экономической информации в Microsoft Excel: Пособие для студентов экономических специальностей / Д.П. Подкопаев, В.И. Яшкин. – Мн.: Издательство БГУ, 2001. – 50 с.

10. Бизнес-анализ с помощью Microsoft Excel / Карлберг Конрад. – 2-е изд. – М.: Вильямс, 2003. – 446 с.

11. Использование макросов в Excel / С. Роман. – 2-е изд. – СПб: Питер, 2004. – 507 с.

12. Гетц К., Гилберт М. Программирование на Visual Basic и VBA. Руководство разработчика: пер с англ. – К.: Издательская группа BHV, 2001. – 912 с.

 

[1] Аргумент Интегральная = 0 для вычисления плотности распределения вероятности, Интегральная = 1 для вычисления интегральной функции распределения.

[2] Аргумент Массив или Ссылка — это абсолютная ссылка на диапазон всех значений случайной величины