Lidtracker.ru

Лид Трэкер
3 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

РЕГРЕССИОННЫЙ АНАЛИЗ В EXCEL

РЕГРЕССИОННЫЙ АНАЛИЗ В EXCEL

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

Результат анализа позволяет выделять приоритеты. И основываясь на главных факторах, прогнозировать, планировать развитие приоритетных направлений, принимать управленческие решения.

· линейной (у = а + bx);

· параболической (y = a + bx + cx 2 );

· экспоненциальной (y = a * exp(bx));

· степенной (y = a*x^b);

· гиперболической (y = b/x + a);

· логарифмической (y = b * 1n(x) + a);

· показательной (y = a * b^x).

Рассмотрим на примере построение регрессионной модели в Excel и интерпретацию результатов. Возьмем линейный тип регрессии.

Задача. На 6 предприятиях была проанализирована среднемесячная заработная плата и количество уволившихся сотрудников. Необходимо определить зависимость числа уволившихся сотрудников от средней зарплаты.

Модель линейной регрессии имеет следующий вид:

Где а – коэффициенты регрессии, х – влияющие переменные, к – число факторов.

В нашем примере в качестве У выступает показатель уволившихся работников. Влияющий фактор – заработная плата (х).

В Excel существуют встроенные функции, с помощью которых можно рассчитать параметры модели линейной регрессии. Но быстрее это сделает надстройка «Пакет анализа».

Активируем мощный аналитический инструмент:

1. Нажимаем кнопку «Офис» и переходим на вкладку «Параметры Excel». «Надстройки».

2. Внизу, под выпадающим списком, в поле «Управление» будет надпись «Надстройки Excel» (если ее нет, нажмите на флажок справа и выберите). И кнопка «Перейти». Жмем.

3. Открывается список доступных надстроек. Выбираем «Пакет анализа» и нажимаем ОК.

После активации надстройка будет доступна на вкладке «Данные».

Теперь займемся непосредственно регрессионным анализом.

1. Открываем меню инструмента «Анализ данных». Выбираем «Регрессия».

2. Откроется меню для выбора входных значений и параметров вывода (где отобразить результат). В полях для исходных данных указываем диапазон описываемого параметра (У) и влияющего на него фактора (Х). Остальное можно и не заполнять.

3. После нажатия ОК, программа отобразит расчеты на новом листе (можно выбрать интервал для отображения на текущем листе или назначить вывод в новую книгу).

В первую очередь обращаем внимание на R-квадрат и коэффициенты.

R-квадрат – коэффициент детерминации. В нашем примере – 0,755, или 75,5%. Это означает, что расчетные параметры модели на 75,5% объясняют зависимость между изучаемыми параметрами. Чем выше коэффициент детерминации, тем качественнее модель. Хорошо – выше 0,8. Плохо – меньше 0,5 (такой анализ вряд ли можно считать резонным). В нашем примере – «неплохо».

Коэффициент 64,1428 показывает, каким будет Y, если все переменные в рассматриваемой модели будут равны 0. То есть на значение анализируемого параметра влияют и другие факторы, не описанные в модели.

Коэффициент -0,16285 показывает весомость переменной Х на Y. То есть среднемесячная заработная плата в пределах данной модели влияет на количество уволившихся с весом -0,16285 (это небольшая степень влияния). Знак «-» указывает на отрицательное влияние: чем больше зарплата, тем меньше уволившихся. Что справедливо.

Создание модели линейной регрессии в Excel

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

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

Ключевые вынос

  • Линейная регрессия моделирует отношения между зависимой и независимой переменной (ами).
  • Регрессионный анализ может быть достигнут, если переменные являются независимыми, нет гетероскедастичности, и условия ошибки переменных не коррелируют.
  • Моделирование линейной регрессии в Excel стало проще с помощью пакета анализа данных.

Важные соображения

Есть несколько критических предположений о вашем наборе данных, которые должны быть верны, чтобы продолжить регрессионный анализ:

  1. Переменные должны быть действительно независимыми (используя критерий хи-квадрат).
  2. Данные не должны иметь различные отклонения ошибок (это называется гетероскедастичностью (также пишется гетероскедастичность)).
  3. Условия ошибки каждой переменной должны быть некоррелированными. Если нет, это означает, что переменные последовательно коррелируют.

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

Вывод регрессии в Excel

Первым шагом в запуске регрессионного анализа в Excel является повторная проверка того, что установлен бесплатный плагин Excel для анализа данных. Этот плагин позволяет легко вычислять статистику. это нетребуется для построения графика линейной регрессии, но это упрощает создание таблиц статистики. Чтобы проверить, установлен ли он, выберите «Данные» на панели инструментов. Если опция «Анализ данных» является опцией, эта функция установлена ​​и готова к использованию. Если он не установлен, вы можете запросить эту опцию, нажав кнопку Office и выбрав «Параметры Excel».

Используя Data Analysis ToolPak, для создания регрессионного вывода достаточно нескольких щелчков мышью.

Независимая переменная входит в диапазон X.

С учетом доходности S & P 500, скажем, мы хотим знать, можем ли мы оценить силу и соотношение доходности акций Visa (V). Запас Visa (V) возвращает данные, заполняет столбец 1 как зависимую переменную. S & P 500 возвращает данные, заполняющие столбец 2 как независимую переменную.

  1. Выберите «Данные» на панели инструментов. Появится меню «Данные».
  2. Выберите «Анализ данных». Откроется диалоговое окно «Анализ данных — Инструменты анализа».
  3. В меню выберите «Регрессия» и нажмите «ОК».
  4. В диалоговом окне «Регрессия» щелкните поле «Диапазон ввода Y» и выберите данные зависимой переменной (доходность Visa (V)).
  5. Щелкните поле «Input X Range» и выберите данные независимых переменных (S & P 500 возвращает).
  6. Нажмите «ОК» для запуска результатов.

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

Интерпретировать результаты

Используя эти данные (то же самое из нашей статьи R-квадрат), мы получаем следующую таблицу:

R 2 Значение, также известное как коэффициент детерминации, измеряет долю вариации в зависимой переменной, объясняемой независимой переменной, или насколько регрессионная модель соответствует данным. R 2 значение варьируется от 0 до 1, а более высокое значение указывает на лучшее соответствие. Значение p или значение вероятности также колеблется от 0 до 1 и указывает, является ли тест значимым. В отличие от R 2 значение, меньшее значение p является благоприятным, поскольку оно указывает на корреляцию между зависимой и независимой переменными.

Читать еще:  Как сделать обложку для книги в Adobe Photoshop

Диаграмма регрессии в Excel

Мы можем построить регрессию в Excel, выделив данные и представив их в виде точечной диаграммы. Чтобы добавить линию регрессии, выберите «Макет» в меню «Инструменты диаграммы». В диалоговом окне выберите «Линия тренда», а затем «Линейная линия тренда». Добавить R 2 выберите «Дополнительные параметры трендовой линии» в меню «Трендовая линия». Наконец, выберите «Показать значение R в квадрате на графике». Визуальный результат суммирует силу взаимосвязи, хотя и за счет того, что не дает столько деталей, сколько таблица выше.

Поисковая геохимия

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

Регрессия бывает парной – между двумя переменными, а бывает множественная, где есть одна зависимая и множество независимых переменных.

Так же, множество видов регрессионного анализа можно разделить на три группы: линейные методы, нелинейные, непараметрические.

Линейная регрессия означает, что ищется зависимость определяемая линейной функций вида y = ax + b, где:

  • y – зависимая переменная (например, содержание серебра в руде),
  • a – коэффициент, который задает наклон прямой на графике (например, если коэффициент отрицательный, то чем меньше будет компонента x, то тем больше будет компонента y.)
  • x – независимая переменная (например, содержание золота в руде)
  • b – константа, некоторое число. Оно определяет высоту прямой. Например, константа определит сколько будет серебра в руде, если содержание золота равно нулю.

Нелинейные методы определяются функциями всевозможного вида: логарифмическими (y = ln(x)), и полиномиальными, и другими.

Непараметрические методы тоже определяют зависимость между переменными, но не могут выдать её в качестве функции.

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

В первой части хочется остановится на самом простом способе – использовании регрессионного анализа в MS Excel. Рассмотрим на примере определение закономерностей между плотностью горной породы и содержанием полезных компонентов в ней. Используем парную и множественную регрессию. Линейные и нелинейные методы.

image

Шаг 1. Составим базу данных. В данном случае имеем: группирующую переменную “Порода”, зависимую “Плотность”, независимые “М1-М8”.

image

Шаг 2. Включим надстройку “Анализ данных” для Excel. Для этого правой клавишей мышки щелкаем в любом месте на ленте и открываем вкладку “Настройка ленты”

image

В открывшемся окне переходим во вкладку “Надстройки”. Внизу в строке “Управление” выбираем “Надстройки Excel” — “Перейти”.

image

В новом окне выбираем “Пакет анализа” и активируем кнопкой ОК. Надстройку “Анализ данных” можно будет найти на ленте во вкладке “Данные”.

image

Шаг 3. Определим вид распределения данных. Для линейных методов необходимо, чтобы данные были распределены нормально. То есть имелось среднее значение от котого равновероятно отходят остальные значения.

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

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

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

image

Выбираем данные, ставим галочки (кроме Парето) и жмём ОК.

image

Шаг 4. Анализуем гистограммы.

Массивные руды показывают моду на классе 4.38, но из-за хвоста слева, идёт снижение среднего до 4.30. Такой вид гистограммы называется правоскошенным. В роговиках было отобрано значительно меньше проб, но тем не менее среднее находится на уроне 3.2-3.5. Разница серьезная. Более того, исследователь, опробовавший километры керна, знает, что плотность 2.72 (как и 3.16) не характерна для массивных руд. Даже теоретически. Следовательно, на лицо наличие прослоев немассивных руд в выборке.

Фактически у нас ненормальное распределение, но учитывая особенности выборки мы можем пренебречь этим фактом. Ведь ничего другого нам не остаётся. Исправить правоскошенное распределение с помощью логарифмирования не получится. Минимальное значение плотности для массивных руд также не известно, поскольку накладывается ряд факторов: влажность, пористость, состав, допустимый процент силикатного вещества.

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

image

Используем формулу =log10(), что бы получить десятичные логарифмы независимых переменных. Они у нас являются логнормальными величинами априори. Все отсутствующие значения вместо логарифма будут иметь значение #ЧИСЛО!, а значит их необходимо предварительно очистить. Данную операцию легко сделать через инструмент Фильтр.

В надстройке Анализ данных выбираем строку Корреляция.

image

Инструмент запросил непрерывную область данных, поэтому пришлось переместить столбец Плотность.

image

Рассмотрим полученную таблицу и отметим, что Плотность тесно коррелирует с тремя переменными: М1, М3 и М4.

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

Шаг 6. Построим точечные графики распределения.

image

Выделим зависимую Плотность и первый элемент М1 – на вкладке “Вставка” выберем “Точечная”.

image

image

К сожалению, в построенном графике место зависимой переменной занимает М1, а не Плотность. Необходимо поменять их местами. Щёлкаем мышкой на саму диаграму, и в ленте открываем панель “Выбрать данные”

Находим кнопку “Изменить данные” и в открывшемся окне, просто меняем X и Y местами с помощью вырезать-вставить.

image

Теперь получен правильный график. В глаза явно бросаются пять значений-выбросов. К сожеланию опять, в Excel нельзя выбрать пробы прямо на графике и исключить их из анализа. Поэтому с помощью фильтра ищем эти значения и удаляем.

image

Шаг 8. Один раз щёлкаем левой клавишей мышки на любую точку на графике, нажимаем правую клавишу и в новом меню выбираем “Добавить линию тренда”. Повторяя операцию можно добавлять другие линии тренда.

image

На данном графике построено две линии тренда. Черная – линейная, красная – полиномиальная. Еще можно построить Экспоненциальную. И всё. Как видите, возможности Excel сильно ограничены.

Главное, что на графике показаны уравнения зависимости и коэффициенты детерминации R2. Если взять квадратный корень из R2, то получите коэффициент корреляции R.

Читать еще:  Sumatra PDF - обзор, где скачать

Можно заметить, что R для линейной модели составляет 0.77, в то же время корреляционный анализ выше выдавал значение 0.72. Разница в 5% произошла по причине исключения выбросов после построения графика. Поэтому данные всегда стоит проверять на графиках.

Получив уравнения на графиках, мы применили составили парную регрессию между Плотностью и М1. В дополнение хочется отметить, что возможно стоит проверить кусочно-линейные функции – разбить данные на еще две субвыборки по значению log10(M1) = 0.2 и составить уравнения для разных элементов.

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

image

Шаг 9. Произведем рассчет множественной линейной регресии с помощью инструмента Регрессия в пакете Анализ данных. Множественная нелинейная регрессия не реализована в надстройке Анализ данных. Предварительно необходимо удалить все пробы с отсутствующими значениями. Функция множетственной корреляции в Excel не умеет автоматически исключать такие пробы.

image

На что обратить внимание в сухой таблице?

Начнём по порядку.

  • Множественный R – коэффициент корреляции, равен 0.77. Простая парная рассмотренная выше регрессия выдала такой же результат;
  • Нормированный R-квадрат – доля объяснённой дисперсии. 0.60 – означает 60%, что в общем, маловато;
  • Стандартная ошибка. Это мера дисперсии остатков, или как предсказанные значения отклоняются от среднего. Много это или мало? Для этого надо определить Относительную Стандартную ошибку. Среднее значение плотности в выборке составляет 4.23. Поделив 0.206 на 4.23 мы получим 0.05 или 5%. Таким образом, в среднем плотность определена нами с погрешностью 5% (50% данных с погрешностью +/-5%, еще 17% с погрешностью +/-10%, а 99,5% всех данных определены с погрешностью +/- 15%). Для сравнения, стандартная ошибка полиномиальной модели в парной регресии выше (расчёт не показан) составила 0.198. Близкое значение;
  • Дисперсионный анализ показывает Значимость F сильно меньше 0.001, что является высокозначимым результатом – Регрессия состоялась и она значима. Не так что бы можно было медаль вешать, а просто статистически это говорит, что результат наврядли является случайностью;
  • Коэффициенты – это и есть параметры уравнения вида y = ax + b. Выглядит оно так: Плотность = 3.6132 + 1.4403* log10(M1)… на этом моэно остановится, потому что коэффициенты M3 и M4 по сути равны нулю и не значимы. Их P-значение сильно выше 0.05;
  • Вывод остатка. Для интересующихся, можно рассмотреть каждое отдельное значение.

image

Дополнительно в опциях инструмента Регрессии было выбрано построение графиков остатков. Выберем один график с М1.

В данном случае, наглядно показано, что остатки концентрируются плотным пучком с центром в середине. То есть при высоком значении М1 могут быть ошибки одинаковые по модулю ошибки, но разные по знаку. Это плюс. Значит, систематической ошибки нет. Фиолетовым обведен тот самый “Хвост”, то есть уравнение регрессии говорит, что при таких низких содержаниях М1, не может быть высокой плотности, а поскольку функция линейная, то и расположение точек этих остатков выглядит линейно.

Подводя итог, хочется вспомнить слова Ейнштейна: “Всё относительно”. Велика или мала относительная стандартная ошибка в 5%, должно быть определено экономическими факторами. В данном случае мы констатируем факт наблюдения.

В остальном, я надеюсь эта статья покажет путь начинающим исследователям.

Как построить модель в Excel

Урок № 1. Задача о попадании точки в заданную фигуру. 2 часа.

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

Учащиеся должны уметь: строить чертеж в Word, строить математическую модель фигуры, строить компьютерную модель в Excel.

Решение задачи о попадании точки в фигуру на примерах с использованием логических функций Excel. 40 мин.

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

Домашнее задание: построить математическую и компьютерную модель (программа на Паскале) для заданной фигуры.

Рисунок 1

Математическая модель: рис. 2

Компьютерная модель:

формула в Excel:

Рассмотрим еще один пример: рис 3. Разделим фигуру на две части.

Рисунок 3

Математическая модель: 1 часть: рис. 4 2 часть: рис. 5

формула в Excel:

Значения координат точки можно задать случайными числами. Для этого использовать встроенную функцию СЛЧИС(), которая выдает случайное число на отрезке[0;1] .

Для вставки объекта Excel в документ Word необходимо:

сохранить решение задачи в Excel;

в документе Word установить курсор на место вставки;

Вставка — Объект — создать из файла — Обзор — Найти файл с решением задачи — Вставить.

Учащимся выдаются заранее подготовленные карточки с различными фигурами.

Цель урока: построить имитационную модель игры.

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

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

Разбор задачи «Кубики» и задачи о проверке знания таблицы умножения — объяснение у доски (40 мин).

Самостоятельная работа: задача «Домино» — работа за компьютером (40 мин).

Задача «Кубики».

Смоделируйте игру «Кубики»: двое игроков бросают игральный кубик. Определить результат игры.

Выходные параметры : результат — кто победил.

Связь: если х>у, то победил первый игрок, иначе если х=у, то — ничья, иначе — победил второй игрок. Можно связь представить в виде блок-схемы.

Очки, выпавшие у первого и второго игрока, выводятся только после введения имен игроков. Очистка таблицы производится клавишей F9.

В ячейке первого игрока формула:

В ячейке второго игрока формула:

В ячейке результата формула:

=ЕСЛИ(ИЛИ(ЕПУСТО(B2);ЕПУСТО(B4));»»;ЕСЛИ(B3>B5;»выиграл первый»;ЕСЛИ(B3<B5;»выиграл второй»;»ничья»)))

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

Входные параметры: х,у — сомножители, р — ответ, вводимый учеником.

Связь: если р=х*у, то результат — сообщение: ответ правильный, иначе — результат: сообщение об ошибке. Связь также можно представить в виде блок-схемы.

Для вычисления сомножителей применяются формулы:

Для проверки результата используется формула:

Смоделируйте выбор наугад двух костей домино из полного набора костей этой игры (0-0, 0-1, . 6-6). Определить, можно ли приставить эти кости одна к другой в соответствии с правилами домино.

Выходные параметры: ответ: можно приставить кости одну к другой или нет.

Связь: если х1=х2 или х1=у2 или у1=х2 или у1=у2, то ответ: можно, иначе — ответ: нельзя. Связь можно представить в виде блок-схемы.

Читать еще:  Антивирус McAfee обзор преимуществ и недостатков

Для получения значений «костей» домино используются формулы:

Для определения результата используется формула:

Урок № 3. Моделирование биоритмов. 2 часа.

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

Учащиеся должны знать: понятие модели, биоритмов.

Постановка задачи. 5 мин.

Математическая модель. 5 мин.

Построение компьютерной модели в среде Excel. 20 мин.

Анализ результатов моделирования. 10 мин.

Построение суммарных биоритмов. 20 мин.

Оформление работы. 20 мин.

Домашнее задание: построить биоритмы на текущий месяц членам своей семьи.

Постановка задачи.

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

Физический биоритм характеризует жизненные силы человека. Периодичность ритма составляет 23 дня.

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

Третий биоритм характеризует мыслительные способности, интеллектуальное состояние человека. Его цикличность — 33 дня.

Физический цикл F(x)=sin

Эмоциональный цикл F(x)=sin

Интеллектуальный цикл F(x)=sin, где х — возраст человека в днях.

Компьютерная модель.

Рисунок 6

Формулы для расчета кривых:

В ячейке А3 находится дата рождения, в ячейке В3 — первое число расчетного периода.

Физическое состояние Эмоциональное состояние Интеллект. состояние

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

Выбрать день для похода в цирк.

Выбрать дни, когда ответы на уроках будут наиболее (наименее) удачными.

Как вы думаете, что будет показывать график, если сложить все три биоритма? Можно ли будет по нему что-либо определить?

Построить модель физической, эмоциональной и интеллектуальной совместимости двух друзей.

Выделить рассчитанные три столбца своих биоритмов, скопировать и вставить в другие столбцы только значения . Ввести дату рождения друга. Провести расчет суммарных биоритмов. По суммарным столбцам построить диаграмму совместимости. Максимальные значения по оси Y на диаграмме указывают на степень совместимости: если они превышают 1,5 , то вы с другом в хорошем контакте.

Что показывают суммарные графики одноименных биоритмов? Что можно по ним определить?

Какая из трех кривых показывает наилучшую (наихудшую) совместимость с другом?

Выбрать наиболее благоприятные дни для совместного участия с другом в командной игре, например в футбольном матче. Можно ли вообще вам с другом выступать в соревнованиях единой командой? Ответ обоснуйте.

Определите дни, когда вам не следует общаться. Что можно ожидать в эти дни?

Спрогнозировать результат совместного с другом разгадывания кроссворда в указанные дни месяца, например, 10-го, 15-го и 21-го.

В какой области совместной деятельности вы с другом могли бы преуспеть?

Не закрывая Excel, открыть документ Word. Скопировать в него обе диаграммы (собственных и суммарных биоритмов). Ответы на вопросы оформить в виде списка с ответами по собственным и суммарным биоритмам. Сохранить текстовый файл на учительском компьютере (файл — сохранить как — мое сетевое окружение — соседние компьютеры — Teacher — Мои документы).

Лабораторная работа №5, Парная регрессия и корреляция, MS Excel

Для расчета параметров уравнения линейной регрессии строим расчетную таблицу 2.

Б4353, 1

Б4353, 2

Б4353, 3

По формулам находим параметры регрессии

Б4353, 4

Получено уравнение регрессии:

Б4353, 5

.

Параметр регрессии позволяет сделать вывод, что с увеличением среднедушевого прожиточного минимума на 1 ден.ед. среднедневная заработная плата возрастает в среднем на 0,89 ден.ед.

После нахождения уравнения регрессии заполняем столбцы 7 – 10 таблицы 2.

2. Рассчитать линейный коэффициент парной корреляции, коэффициент детерминации и среднюю ошибку аппроксимации.

Тесноту линейной связи оценит коэффициент корреляции:

Б4353, 6

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

Б4353, 7

Это означает, что 67,6% вариации заработной платы (y) объясняется вариацией фактора x – среднедушевого прожиточного минимума.

Качество модели определяет средняя ошибка аппроксимации:

Б4353, 8

Б4353, 9

Качество построенной модели оценивается как хорошее, так как не превышает 10%.

3. Оценить статистическую значимость уравнения регрессии в целом и отдельных параметров регрессии и корреляции с помощью -критерия Фишера и t-критерия Стьюдента.

Оценку статистической значимости уравнения регрессии в целом проведем с помощью F-критерия Фишера. Фактическое значение F-критерия по формуле составит

Б4353, 10

Б4353, 11

Табличное значение критерия при пятипроцентном уровне значимости и степенях свободы k1=1 и k2=12-2=10 составляет Fтабл=4,96. Так как , то уравнение регрессии признается статистически значимым.

Оценку статистической значимости параметров регрессии и корреляции проведем с помощью t-статистики Стьюдента и путем расчета доверительного интервала каждого из параметров.

Б4353, 12

Табличное значение t-критерия для числа степеней свободы df=n-2=12-2=10 и уровня значимости составит tтабл=2,23.

Б4353, 13

Определим стандартные ошибки (остаточная дисперсия на одну степень свободы

Б4353, 14

Б4353, 15

Фактические значения t-статистики превосходят табличное значение:

Б4353, 16

Поэтому параметры a, b и rxy не случайно отличаются от нуля, а статистически значимы.

Рассчитаем доверительные интервалы для параметров регрессии a и b. Для этого определим предельную ошибку для каждого показателя:

Б4353, 17

Б4353, 18

Б4353, 19

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

4. Выполнить прогноз заработной платы y при прогнозном значении среднедушевого прожиточного минимума x, составляющем 107% от среднего уровня.

Полученные оценки уравнения регрессии позволяют использовать его для прогноза. Если прогнозное значение прожиточного минимума составит: Б4353, 20ден.ед., тогда индивидуальное прогнозное значение заработной платы составит: Б4353, 21ден.ед.

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

Ошибка прогноза составит:

Б4353, 22

Предельная ошибка прогноза, которая в 95% случаев не будет превышена, составит:

Б4353, 23

Доверительный интервал прогноза:

Б4353, 24

Б4353, 25

Выполненный прогноз среднемесячной заработной платы является надежным () и находится в пределах от 140,16 ден.ед. до 185,0 ден.ед.

6. На одном графике отложить исходные данные и теоретическую прямую.

Б4353, 26

7. Проверить вычисления в MS Excel.

Выбираем Сервис®Анализ данных®Регрессия. Заполняем диалоговое окно ввода данных и параметров вывода. Получаем следующие результаты:

Б4353, 27

Б4353, 28

Б4353, 29

Б4353, 30

Фактическое значение F-критерия Фишера:

Б4353, 31

Остаточная дисперсия на одну степень свободы:

Б4353, 32

Корень квадратный из остаточной дисперсии (стандартная ошибка):

Б4353, 33

Стандартные ошибки для параметров регрессии:

Б4353, 34

Фактические значения t-критерия Стьюдента:

Б4353, 35

Б4353, 36

Как видим, найдены все рассмотренные выше параметры и характеристики уравнения регрессии, за исключением средней ошибки аппроксимации (значение t-критерия Стьюдента для коэффициента корреляции совпадает с tb). Результаты «ручного счета» от машинного отличаются незначительно (отличия связаны с ошибками округления).

голоса
Рейтинг статьи
Ссылка на основную публикацию
ВсеИнструменты
Adblock
detector