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

↑

▷ Содержание

⇐ ПредыдущаяСтр 11 из 11

▷ Похожие статьи

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

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

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

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

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

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 столбцов. Структура результата представлена в таблице:

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

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

- среднеквадратическое отклонение зависимой переменной 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) Дисперсионный анализ

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

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

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

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

.

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

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

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

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

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

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

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

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

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

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] Аргумент Массив или Ссылка — это абсолютная ссылка на диапазон всех значений случайной величины

Познавательные статьи:



Алгоритмические операторы Matlab

Конструирование и порядок расчёта дорожной одежды

Исследования учёных: почему помогают молитвы?

Почему терпят неудачу многие предприниматели?