Oc-windows.ru

IT Новости из мира ПК
3 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

График остатков в excel

Регрессионный анализ в Microsoft Excel

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

Подключение пакета анализа

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

    Перемещаемся во вкладку «Файл».

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

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

Теперь, когда мы перейдем во вкладку «Данные», на ленте в блоке инструментов «Анализ» мы увидим новую кнопку – «Анализ данных».

Виды регрессионного анализа

Существует несколько видов регрессий:

  • параболическая;
  • степенная;
  • логарифмическая;
  • экспоненциальная;
  • показательная;
  • гиперболическая;
  • линейная регрессия.

О выполнении последнего вида регрессионного анализа в Экселе мы подробнее поговорим далее.

Линейная регрессия в программе Excel

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

Общее уравнение регрессии линейного вида выглядит следующим образом: У = а0 + а1х1 +…+акхк . В этой формуле Y означает переменную, влияние факторов на которую мы пытаемся изучить. В нашем случае, это количество покупателей. Значение x – это различные факторы, влияющие на переменную. Параметры a являются коэффициентами регрессии. То есть, именно они определяют значимость того или иного фактора. Индекс k обозначает общее количество этих самых факторов.

    Кликаем по кнопке «Анализ данных». Она размещена во вкладке «Главная» в блоке инструментов «Анализ».

Открывается небольшое окошко. В нём выбираем пункт «Регрессия». Жмем на кнопку «OK».

Открывается окно настроек регрессии. В нём обязательными для заполнения полями являются «Входной интервал Y» и «Входной интервал X». Все остальные настройки можно оставить по умолчанию.

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

В поле «Входной интервал X» вводим адрес диапазона ячеек, где находятся данные того фактора, влияние которого на переменную мы хотим установить. Как говорилось выше, нам нужно установить влияние температуры на количество покупателей магазина, а поэтому вводим адрес ячеек в столбце «Температура». Это можно сделать теми же способами, что и в поле «Количество покупателей».

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

После того, как все настройки установлены, жмем на кнопку «OK».

Разбор результатов анализа

Результаты регрессионного анализа выводятся в виде таблицы в том месте, которое указано в настройках.

Одним из основных показателей является R-квадрат. В нем указывается качество модели. В нашем случае данный коэффициент равен 0,705 или около 70,5%. Это приемлемый уровень качества. Зависимость менее 0,5 является плохой.

Ещё один важный показатель расположен в ячейке на пересечении строки «Y-пересечение» и столбца «Коэффициенты». Тут указывается какое значение будет у Y, а в нашем случае, это количество покупателей, при всех остальных факторах равных нулю. В этой таблице данное значение равно 58,04.

Значение на пересечении граф «Переменная X1» и «Коэффициенты» показывает уровень зависимости Y от X. В нашем случае — это уровень зависимости количества клиентов магазина от температуры. Коэффициент 1,31 считается довольно высоким показателем влияния.

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

Отблагодарите автора, поделитесь статьей в социальных сетях.

Остатки, Стандартизированные остатки, График остатков, График подбора, График нормальной вероятности

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

Читать еще:  Webcam запись видео

После щелчка на ОКв отдельном листе появятся результаты предварительных расчетов в виде табл.2.

Выводимые программой Microsoft Excel результаты предварительных расчетов коэффициентов регрессии, регрессионной статистики и дисперсионного анализа

Регрессионная статистика
Множественный R0,998893
R-квадрат0,997788
Нормированный R-квадрат0,992258
Стандартная ошибка0,002129
Наблюдения
Дисперсионный анализ
dfSSMSFЗначимость F
Регрессия0,0040880,000818180,42290,005521
Остаток9,06E-064,53E-06
Итого0,004097
КоэффициентыСтандартная ошибкаt-статистикаP-ЗначениеНижние 95%Верхние 95%Нижние 95,0%Верхние 95,0%
Y-пересечение-1,407070,219814-6,401180,023546-2,35285-0,46129-2,35285-0,46129
Т0,0061850,0006369,7329430,0103920,0034510,008920,0034510,00892
C-0,001870,001448-1,290210,326024-0,00810,004361-0,00810,004361
n-0,043950,009819-4,47560,04647-0,0862-0,0017-0,0862-0,0017
V5,81E-053,09E-051,8803420,200808-7,5E-050,000191-7,5E-050,000191
P0,0025910,0014841,7460130,222924-0,003790,008977-0,003790,008977

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

В верхней части табл.2 приведены данные регрессионной статистики. Множественный R=0,998893 и стандартная ошибка — 0,002129, что свидетельствует о высокой степени совпадения вычисленных значений Х (по приведенному ниже уравнению регрессии) с экспериментальными значениями Х.

Оценка адекватности уравнения приведена в дисперсионном анализе. Высокая величина F=180,4229 и низкое значение Значимость F =0,005521, что меньше уровня значимости 0,05, указывают на адекватность уравнения регрессии.

В нижней части табл.2 выведены итоги расчета свободного коэффициента регрессии (Y-пересечение) и коэффициентов регрессии, стоящих перед аргументами Т, С, n, V и Р. Исходя из результатов расчетов уравнение регрессии выглядит следующим образом:

Х=-1,40707 +0,006185*Т-0,00187*С-0,04395*n+5,81E-05*V+0,002591*P (15)

Проверим значимость вычисленных коэффициентов регрессии. Значимость коэффициентов определяется по величине Р-значений, приведенных для каждого коэфициента в отдельном столбце. Коэффициенты уравнения значимы в том случае, если Р-значение меньше 0,05 (уровня значимости). Из анализа величин Р-значений видно, что условию значимости отвечают коэффициенты Y-пересечение, и коэффициенты, стоящие перед аргументами Т и n. Исходя из этого, в уравнении регрессии могут быть отброшены другие аргументы: С, V и Р. То есть, степень превращения Х для приведенных экспериментальных данных зависит только от температуры Т и соотношения реагентов n.

3.Уточненные вычисления коэффициентов уравнения регрессии, вывод графиков подбора и остатков.

Для нахождения уточненного уравнения регрессии выполняют повторные вычисления, принимая в расчет в табл.1 исходных экспериментальных данных только значимые столбцы аргументов Т и n и функцию Х (столбцы №1, 3, 6).

Расчёты коэффициентов регрессии в программе Microsoft Excel выполняют аналогично вышеизложенным. Результаты расчетов приведены в табл.3,4 и на рис.2,3.

Выводимые программой Microsoft Excel результаты уточненных расчетов коэффициентов регрессии, регрессионной статистики и дисперсионного анализа

Регрессионная статистика
Множественный R0,995075
R-квадрат0,990174
Нормированный R-квадрат0,986243
Стандартная ошибка0,002837
Наблюдения
Дисперсионный анализ
dfSSMSFЗначимость F
Регрессия0,0040570,002028251,92239,57E-06
Остаток4,03E-058,05E-06
Итого0,004097
КоэффициентыСтандартная ошибкаt-статистикаP-ЗначениеНижние 95%Верхние 95%Нижние 95,0%Верхние 95,0%
Y-пересечение-1,070120,108296-9,88140,000181-1,3485-0,79173-1,3485-0,79173
Т0,0054860,00029318,708058,03E-060,0047320,006240,0047320,00624
n-0,030390,009116-3,333320,020704-0,05382-0,00695-0,05382-0,00695

Величины расхождений расчетных и экспериментальных

значений (остатков) величины Х

№ наблюденияРасчетная (предсказанная величина) X, доляОстатки
0,8195780,000422
0,8080140,001986
0,871398-0,0014
0,850046-4,6E-05
0,862538-0,00254
0,8619460,003054
0,818682-0,00368
0,8527970,002203

Рис.2

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

Анализ адекватности уравнения и значимости коэффициентов регрессии проводят аналогично вышеизложенному (см. табл.3). Значение множественного коэффициента R=0,995075, близкое к 1,0 и низкая величина стандартной ошибки — 0,002837, свидетельствуют о высокой степени совпадения вычисленных значений Х (по приведенному ниже уравнению 16) с экспериментальными значениями Х. Уравнение (16) адекватно, поскольку значимость F составляет 9,57E-06, что значительно меньше 0,05.

Вычисленные коэффициенты, стоящие перед аргументами Т и n, значимы, так как величины Р-значений меньше 0,05.

Таким образом, уточненное уравнение регрессии представляет собой:

Х=-1,07012 +0,005486*Т -0,03039*n (16)

Следует отметить, что найденное уравнение регрессии (16) применимо только для диапазона изменения температуры Т=349..360 о С и диапазона изменения соотношения реагентов n=0,85..1,21. При расширении диапазона аргументов оно может стать некорректным.

Величины расхождений расчетных и экспериментальных значений (остатков) Х приведены в табл.4. Из анализа данных табл.4 следует, что максимальная величина остатка не превышает 0,003054, что показывает высокую степень совпадения расчетных и экспериментальных значений.

Графики подбора расчетных и экспериментальных значений Х по аргументам Т и n приведены на рис.2,3. Приведенные на рис.2,3 графики также подтверждают высокую степень совпадения расчетных и экспериментальных значений.

5. Оптимизация процесса по найденному уравнению регрессии

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

Из анализа полученного линейного уравнения регрессии следует, что повышение температуры процесса и снижение соотношения реагентов до минимального значения приведет к увеличению степени превращения Х. Подставляя в уравнение (16) максимальное значение Т=360 о С и минимальное значение n=0,85, вычислим максимальное значение степени превращения:

Читать еще:  Текст не вмещается в ячейку excel

Х= -1,07012 +0,005486*360 -0,03039*0,85= 0,879

что является решением поставленной задачи.

Приложение

Вычисление критерия Стьюдента с использованием подпрограммы “Обратное распределение Стьюдента”

Подпрограмма Обратное распределение Стьюдента вычисляет значение t-распределения Стьюдента, приводимого в статистических таблицах для заданного уровня значимости альфа (a=1-P) и числа степеней свободы f=n-1.

где: альфа a=(1-Р); Р-вероятность.

  • Если любой из аргументов не является числом, то функция СТЬЮДРАСПОБР вычисляет значение ошибки #ЗНАЧ!.
  • Если вероятность 1, то функция СТЬЮДРАСПОБР вычисляет значение ошибки #ЧИСЛО!.
  • Если степень_свободы не целое число, то оно усекается.
  • Если степени_свободы t) = вероятность, где X — это случайная величина, соответствующая t-распределению и P(|X| > t) = P(X t).

· Одностороннее t-значение может быть получено при замене аргумента альфа на 2*альфа.

Пример: Для уровня значимости a=0,05 и числа степеней свободы равного 10 двухстороннее значение t вычисляется следующим выражением:

=СТЬЮДРАСПОБР(0,05;10) равно 2,28139.

Одностороннее значение для той же доверительной вероятности a=0,05 и числа степеней свободы 10 t может быть вычислено выражением:

Корреляционно-регрессионный анализ в 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

Коэффициент корреляции обозначается r. Варьируется в пределах от +1 до -1. Классификация корреляционных связей для разных сфер будет отличаться. При значении коэффициента 0 линейной зависимости между выборками не существует.

Рассмотрим, как с помощью средств Excel найти коэффициент корреляции.

Для нахождения парных коэффициентов применяется функция КОРРЕЛ.

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

Ставим курсор в любую ячейку и нажимаем кнопку fx.

  1. В категории «Статистические» выбираем функцию КОРРЕЛ.
  2. Аргумент «Массив 1» — первый диапазон значений – время работы станка: А2:А14.
  3. Аргумент «Массив 2» — второй диапазон значений – стоимость ремонта: В2:В14. Жмем ОК.

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

Для корреляционного анализа нескольких параметров (более 2) удобнее применять «Анализ данных» (надстройка «Пакет анализа»). В списке нужно выбрать корреляцию и обозначить массив. Все.

Полученные коэффициенты отобразятся в корреляционной матрице. Наподобие такой:

Корреляционно-регрессионный анализ

На практике эти две методики часто применяются вместе.

  1. Строим корреляционное поле: «Вставка» — «Диаграмма» — «Точечная диаграмма» (дает сравнивать пары). Диапазон значений – все числовые данные таблицы.
  2. Щелкаем левой кнопкой мыши по любой точке на диаграмме. Потом правой. В открывшемся меню выбираем «Добавить линию тренда».
  3. Назначаем параметры для линии. Тип – «Линейная». Внизу – «Показать уравнение на диаграмме».
  4. Жмем «Закрыть».

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

Регрессионный анализ данных в Excel

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

Включение функции анализа в программе

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

  1. Открываем меню “Файл”.
  2. Щелкаем по пункту “Параметры”.
  3. В нижней части содержимого подраздела “Надстройки” выбираем значение “Надстройки Excel” для параметра “Управление”, после чего кликаем “Перейти”.
  4. В окне управления надстройками выбираем “Пакет анализа” и щелкаем OK.
  5. Переходим во вкладку “Данные”, чтобы проверить, появилась ли функция “Анализ данных” в группе инструментов “Анализ”.

Линейный регрессионный анализ

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

В рамках данной статьи мы рассмотрим линейную регрессию. В общем виде ее функция выглядит так:

В данном уравнении:

  • Y – переменная, влияние на которую нужно найти;
  • X – факторы, влияющие на переменную;
  • A – коэффициенты регрессии, определяющие значимости факторов;
  • N – общее количество факторов.

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

Наша задача – выяснить, как температура влияет на осадки. Приступи к ее выполнению.

  1. Щелкаем по кнопке “Анализ данных”.
  2. В открывшемся окошке отмечаем пункт “Регрессия”, после чего щелкаем OK.
  3. Перед нами появится окно, в котором нужно настроить параметры регрессии:
    • в поле “Входной интервал_Y” пишем координаты диапазона ячеек, в которых находятся переменные, влияние на которые нам нужно выяснить. У нас это столбец “Количество осадков, мм”. Координаты диапазона можно указать как вручную, используя клавиши на клавиатуре, так и выделив его в самой таблице с помощью зажатой левой кнопки мыши.
    • в поле “Входной интервал_X” указываем координаты диапазона ячеек с данными, влияние которых нам нужно найти. В нашем случае – это столбец “Среднесуточная температура”.
    • Остальные параметры не являются обязательными и, чаще всего, остаются незаполненными. У нас есть возможность установить метки, значения уровня надежности в процентах, константу-ноль, график нормальной вероятности и т.д. Пожалуй, самым важным здесь является способ вывода результатов анализа. Доступны следующие варианты: на новом листе (по умолчанию), в новой книге или в указанном диапазоне на этом же листе. Мы оставим все как есть и жмем кнопку OK.

Анализ полученных результатов

После корректного заполнения всех параметров и нажатия кнопки OK отобразятся результаты анализа (в зависимости от выбранного способа). В нашем случае – на отдельном листе.

Ключевым показателем здесь является R-квадрат (коэффициент детерминации), значение которого характеризует качество модели. Приемлемым считается значение не менее 0,5 (или 50%).

Также следует обратить внимание на ячейку, расположенную на пересечении строки “Y-пересечение” и столбца “Коэффициенты”. Здесь показывается, каким будет значение Y (количество осадков), если все остальные факторы будут равны нулю.

Ячейка на пересечении строки “Переменная X 1” и столбца “Коэффициенты” содержит значение, характеризующее степень зависимости Y от X. Коэф. 0,89 в нашем случае говорит о достаточно сильной связи между переменными.

Заключение

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

Ссылка на основную публикацию
Adblock
detector