Как сделать регрессионный анализ в excel пример






Как сделать регрессионный анализ в excel пример

страница 1

Линейный регрессионный анализ средствами MS excel
СОДЕРЖАНИЕ


  • Применение пакета анализа для построения линейной регрессии. Оценка качества линейной модели.

  • Применение пакета анализа для организации выборки из совокупности. Сглаживание данных.

обозначения

x,y, – случайные величины, xi,yj – их выборочные значения, N – число выборок, k – число интервалов группирования;

M - выборочное среднее, S – выборочное среднеквадратичное отклонение, S2 – выборочная дисперсия;

rxy – коэффициент корреляции;

а – константа, b – коэффициент линейного уравнения регрессии, ŝ2 – остаточная дисперсия;

N{M,S} – нормальное распределение, UN{M,S} – равномерное распределение со средним M и среднеквадратичным отклонением S.


Практические задания по линейному регрессионному анализу
Основные возможности в примерах и заданиях и их реализация

Применение пакета анализа MS Excel для построения линейной регрессии


Пример 1. Проведите в рамках простой линейной регрессионной модели анализ связи между индексом биржевой активности брокера и стажем его работы c использованием средств Анализ данных и Мастер диаграмм MS Excel.
Применение. Простой линейный регрессионный анализ используется для построения и оценки качества линейной модели зависимости переменной у от х (одного регрессора). На первом этапе анализа строится собственно линейная модель
ŷ=bх+а , где ŷ –значения зависимой переменной, полученные в результате расчета модели.
Коэффициент b имеет смысл показателя степени связи между изменчивостью влияющей переменной х и изменчивостью зависимой переменной y. Линейное уравнение показывает, как изменяется в среднем переменная y при изменении влияющей переменной на единицу ее измерения, а знак коэффициента b показывает направление этого изменения. Параметры линейного уравнения находятся по методу наименьших сумм квадратов отклонений измерений yi
(y-yi)2=(y-a-bxi)2→min

В предположении о нормальности распределения влияющей переменной x для линейной модели оценки свободного члена а и коэффициента пропорциональности b рассчитываются по формулам:


b=rxySy/Sx, где rxy – коэффициент корреляции, Sy, Sx – среднеквадратичные отклонения признаков y, x;
a=My-bMx, где My, Mx средние значения признаков y, x.
Расчетные значения a и b подставляются в уравнение линейной регрессии и тем самым находятся значения ŷ зависимой переменной по модели.
Второй этап регрессионного анализа – проверка качества построенной модели, которая включает следующие компоненты:
значимость оценок параметров модели, информативность, адекватность и анализ остатков.
Начинают с проверки гипотезы о значимости рассчитанных коэффициента b и свободного члена а. Проверяется нулевая гипотеза о не значимости полученных оценок H0: a=0, b=0. Для размеров выборки N<30 можно воспользоваться критерием Стьюдента c расчетными статистиками
ta=|a|(N-2)/Sост для а и tb=|b|(N-2) Sx/Sост, где Sост – остаточное среднеквадратичное отклонение, обусловленное различием истинных значений yi от значений по модели ŷ

Sост=(ŷ-yi)2/(N-2)


Расчетные значения сравниваются с критическими T{,v}с уровнем значимости =0,05 и числом степеней свободы v=N-2. Если расчетные значения больше критических, нулевая гипотеза отклоняется и оценки признаются значимыми.

В заключение об информативности модели входит оценка значимости коэффициента (квадрата) множественной корреляции


r2=S2ост/S2, где S2 – общая дисперсия признака y модели.
Значение r2 должно быть как можно ближе к 1. Для активного эксперимента оно должно быть порядка 0,96-0,97. Значимость проверяется по критерию Фишера со статистикой
Fr= Sr2/S2ост , где Sr2 – дисперсия, объясняемая моделью
которая сравнивается с критическим с уровнем значимости =0,05 , 1 числом степеней свободы m-1 (m - число членов модели, т.е.2), 2 числом степеней свободы N-m. Если расчетное значение больше критического, то модель информативна. Для количественной оценки уровня информативности используется параметр Бокса-Веца , который должен быть равен 2-3. На языке критерия Фишера это значит, что расчетная статистика должна примерно в 10 раз превосходить табличное значение.

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



  1. Запустите MS Excel: Пуск / Программы /Microsoft Excel / сохраните созданную при запуске книгу под именем Примеры_регрессия в вашу рабочую папку: Файл / Сохранить как / откройте вашу папку / введите в поле Имя файла название книги Примеры_регрессия / Сохранить.

  2. Переименуйте ярлычок листа Лист 1 в Регрессия_лин_1.

  3. На лист Регрессия_лин_1 скопируйте из рабочей книги Образцы_стат с листа Образец_4_1 массив исходных данных соотношения роста и веса пятидесяти 20-летних студентов.

  1. Проведите линейный регрессионный анализ: Сервис / Анализ данных / Регрессия / в одноименном окне укажите входные диапазоны A2:A52 для х и B2:B52 для y, установите флажок метки / установите все флажки в разделах Остатки и Нормальная вероятность, укажите начальную ячейку выходного диапазона D1 (резервируя не менее 7 столбцов под бланк результатов) / ОК..

  2. Проанализируйте полученные результаты: в бланке расчета модели прочитайте значения оценок свободного члена (столбец Коэффициенты, строка Y-пересечение) и коэффициента пропорциональности (столбец Коэффициенты, строка х).

  3. Оцените информативность построенной линейной модели посредством анализа коэффициента множественной корреляции Множественный R в таблице Регрессионная статистика бланка результатов. Его значимость оценивается по критерию Фишера, расчетное значение статистики которого приведено в таблице Дисперсионный анализ в столбце F строки Регрессия. Найдите критическое значение по расчетной формуле FРАПОБР(0,05; число членов модели-1(2-1); число выборок-число членов модели (50-2)). Если критическое значение меньше расчетного, коэффициент множественной корреляции считается значимым. Модель считается информативной ,если расчетная статистика не менее, чем в 10 раз больше табличного значения критерия Фишера.

  4. Оцените качественно адекватность построенной модели посредством анализа сравнительного графика кривой модели и исходной кривой (График подбора) и поведения остатков (разности между предсказанными моделью значениями у и входными значениями из выборки). Просмотрите График подбора и оцените визуально степень отклонения модели от реального графика. Просмотрите таблицу остатков Вывод остатка и сравните полученные в модели значения у с исходными. Распределение остатков для адекватной модели должно быть нормальным с нулевым средним и единичной дисперсией. Просмотрите График остатков и График нормального распределения для визуальной оценки принадлежности остатков к нормальному закону. Адекватными считаются остатки, сосредоточенные вблизи нулевой отметки с разбросом не более 1. Нормальный график не должен при этом иметь участков со спадом кривой. Для более уверенной оценки распределения постройте гистограмму по столбцу Остатки таблицы Вывод остатка и оцените нормальность поведения кривой плотности вероятности по критерию хи-квадрат.

  5. Используя построенную линейную модель, сформируйте расчетную таблицу соотношения роста и веса молодых мужчин для диапазона роста от 155 см до 195 см с интервалом в 1 см. С помощью средства Мастер диаграмм постройте график зависимости.

Задание 1. Создайте и сохраните в вашу рабочую папку книгу под именем Задание_регрессия. На листе Задание_1 проведите линейный регрессионный анализ по схеме Примера 1 для данных Образца 1.
Образец 1

Основные возможности в примерах и заданиях и их реализация
Применение пакета анализа для организации выборки из совокупности. Сглаживание данных.
Пример 2. Действуя по схеме из Примера 1, проведите линейный регрессионный анализ для данных ежемесячных продаж лекарственного препарата за 2000 г. Используя построенную модель, рассчитайте прогноз продаж на первые 3 месяца 2001 года.

  1. Переименуйте ярлычок листа Лист 2 в Регрессия_лин_2.

  2. На лист Регрессия_лин_2 скопируйте из рабочей книги Образцы_стат с листа Образец_4_2 массив исходных данных.

  1. Проведите линейный регрессионный анализ: Сервис / Анализ данных / Регрессия / в одноименном окне укажите входные диапазоны A2:A14 для х и B2:B14 для y, установите флажок метки / установите все флажки в разделах Остатки и Нормальная вероятность, укажите начальную ячейку выходного диапазона D1 / ОК..

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

Задание 2. В рабочей книге Задание_регрессия на листе Задание_2 проведите линейный регрессионный анализ и прогнозирование по схеме Примера 2 для данных Образца 2.
Образец 2

Пример 3. Проведите организацию выборки из совокупности с помощью средства Пакет анализа методами периодической и случайной выборки.

  1. Перейдите на Лист 2 и переименуйте его в Выборка. При помощи процедуры Генерация случайных чисел средства Пакет анализа сгенерируйте столбец 512 нормально с нулевым средним и ст. отклонением 2 распределенных чисел, разместив его начиная с ячейки А1.

  2. Проведите организацию периодической выборки: Сервис / Анализ данных / в окне Анализ данных выберите Выборка / ОК / в окне Выборка укажите входной интервал А1:А512 / снимите флажок Метки / в разделе Метод выборки включите переключатель Периодический и введите число 16 в поле Период / укажите начальную ячейку выходного интервала С1 / ОК.

  3. Проведите организацию случайной выборки: Сервис / Анализ данных / в окне Анализ данных выберите Выборка / ОК / в окне Выборка укажите входной интервал А1:А512 / снимите флажок Метки / в разделе Метод выборки включите переключатель Случайный и введите число 32 в поле Число выборок / укажите начальную ячейку выходного интервала Е1 / ОК.

  4. В результате проведенных процедур выборки должно получиться два столбца чисел по 32 сила в каждом. Сравните качество процедур выборки; постройте по трем столбцам описательную статистику, гистограммы распределения и сравните полученные результаты. Сделайте выводы, какая из процедур работает лучше с точки зрения воспроизведения характеристик генеральной совокупности.

Задание 3. На листе Задание_3 проведите организацию выборки из совокупности с помощью средства Пакет анализа методами периодической и случайной выборки по схеме Примера 2 для сгенерированных процедурой Генерация случайных чисел 256 точек данных распределения Пуассона с параметром интенсивности (лямбда) 4. Извлеките периодическую выборку с периодом 8 и случайную на 32 точки.

Пример 4. Проведите с помощью средства Пакет анализа экспоненциальное сглаживание данных выборки.


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

  2. Проведите экспоненциальное сглаживание случайной выборки: Сервис / Анализ данных / в окне Анализ данных выберите Экспоненциальное сглаживание/ ОК / в одноименном окне укажите входной интервал А1:А32 / снимите флажок Метки / введите число 0,4 в поле Фактор затухания / укажите начальную ячейку выходного интервала В1 / установите флажки Вывод графика, тандартные погрешности / ОК / просмотрите результаты в столбцах прогноза и стандартных погрешностей рядом с исходными данными / проведите анализ статистики сглаженных данных и сравните результаты с графиками на листе Выборка.

  3. Аналогично п.2 проведите сглаживание и анализ для периодической выборки.

  4. Сравните работу процедуры сглаживания для двух типов выборки, сделав дубликаты графиков и удалив с дубликатов линии фактических данных.

Задание 3. На листе Задание_3 проведите по схеме Примера 3 с помощью средства Пакет анализа экспоненциальное сглаживание данных выборок из Задания 3.


</30>
страница 1
Смотрите также:

Линейный регрессионный анализ средствами ms excel
139.91kb. 1 стр.


Лабораторная работа №1 Автоматизированный априорный анализ статистической совокупности в среде ms excel
1044.02kb. 11 стр.


Практическая работа №11 Excel 2007. Работа со списками
38.45kb. 1 стр.


     

скачать файл
 






Как сделать регрессионный анализ в excel пример

Как сделать регрессионный анализ в excel пример

Как сделать регрессионный анализ в excel пример

Как сделать регрессионный анализ в excel пример

Как сделать регрессионный анализ в excel пример

Похожие новости: