Как сделать корреляцию в экселе

Содержание:

Линейная регрессия в программе Excel
Разбор результатов анализа
- Помогла ли вам эта статья?
Решение эконометрики в Экселе
Недостаток R2{\displaystyle R^{2}} и альтернативные показатели
Пакет анализа Excel (программа «Регрессия»)
Основные задачи и виды регрессии
- Инструкция построения модели
Основные задачи и виды регрессии
- Инструкция построения модели
Анализ результатов регрессии для R-квадрата
Использование Пакета анализа 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 довольно просто составить таблицу регрессионного анализа. Но, работать с полученными на выходе данными, и понимать их суть, сможет только подготовленный человек.

Мы рады, что смогли помочь Вам в решении проблемы.

Помогла ли вам эта статья?

Метод линейной регрессии позволяет нам описывать прямую линию, максимально соответствующую ряду упорядоченных пар (x, y). Уравнение для прямой линии, известное как линейное уравнение, представлено ниже:

ŷ — ожидаемое значение у при заданном значении х,

x — независимая переменная,

a — отрезок на оси y для прямой линии,

b — наклон прямой линии.

На рисунке ниже это понятие представлено графически:

На рисунке выше показана линия, описанная уравнением ŷ =2+0.5х. Отрезок на оси у — это точка пересечения линией оси у; в нашем случае а = 2. Наклон линии, b, отношение подъема линии к длине линии, имеет значение 0.5. Положительный наклон означает, что линия поднимается слева направо. Если b = 0, линия горизонтальна, а это значит, что между зависимой и независимой переменными нет никакой связи. Иными словами, изменение значения x не влияет на значение y.

Часто путают ŷ и у. На графике показаны 6 упорядоченных пар точек и линия, в соответствии с данным уравнением

На этом рисунке показана точка, соответствующая упорядоченной паре х = 2 и у = 4

Обратите внимание, что ожидаемое значение у в соответствии с линией при х
= 2 является ŷ. Мы можем подтвердить это с помощью следующего уравнения:

ŷ = 2 + 0.5х =2 +0.5(2) =3.

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

Следующий шаг — определить линейное уравнение, максимально соответствующее набору упорядоченных пар, об этом мы говорили в предыдущей статье, где определяли вид уравнения по методу наименьших квадратов.

Решение эконометрики в Экселе

Как быстро сделать кроссворд в экселе

Задача 1. Парная регрессия.
Для исходных данных, приведенных ниже, рассчитайте

коэффициенты линейного регрессионного уравнения
рассчитайте остаточную дисперсию
вычислите значения коэффициентов корреляции и детерминации
рассчитайте коэффициент эластичности
рассчитайте доверительные границы уравнения регрессии (по уровню 0,95, t=2,44)
в одной системе координат постройте: уравнение регрессии, экспериментальные точки, доверительные границы уравнения регрессии

Отчет (pdf), Расчеты (xlsx)

Задача 2. Построить требуемое уравнение регрессии. Вычислить коэффициент детерминации, коэффициент эластичности, бета коэффициент и дать их смысловую нагрузку в терминах задачи. Проверить адекватность уравнения с помощью F теста. Найти дисперсии оценок и 95% доверительные интервалы для параметров регрессии. Данные взять из таблицы. Найти прогнозируемое значение объясняемой переменной для некоторого значения объясняющей переменной, не заданной в таблице. Построить уравнение линейной регрессии объема валового выпуска (в млн. руб.) от стоимости основных производственных фондов (млн. руб.).

Отчет (pdf), Расчеты (xlsx)

Задача 3. Множественная регрессия.
Построить требуемое уравнение регрессии. Вычислить коэффициент детерминации, частные коэффициенты эластичности, частные бета коэффициенты и дать их смысловую нагрузку в терминах задачи. Проверить адекватность уравнения с помощью F теста. Найти оценку матрицы ковариаций оценок параметров регрессии и 95% доверительные интервалы для параметров регрессии. Проверить наличие мультиколлинеарности в модели. Данные взять из таблицы.
Построить уравнение линейной регрессии себестоимости единицы товара (в сотнях руб.) от величины энерговооруженности (кВт) и производительности труда (тов/час).

Отчет (pdf), Расчеты (xlsx)

Задача 4. Трендовые модели
Проверить ряд на наличие тренда. Сгладить ряд методом простой скользящей средней $(m = 3)$, экспоненциальным сглаживанием $(\alpha = 0,3; \alpha = 0,8)$. Построить исходный и сглаженные ряды. На основании построенных рядов определить вид трендовой модели. Построить трендовую модель.
Сделать прогноз изучаемого признака на два шага вперед.
87; 77; 75; 74; 69; 66; 62; 61; 59; 57; 57; 52; 50; 48; 46; 43; 43; 41; 38; 35

Отчет (pdf), Расчеты (xlsx)

Задача 5.
По заданным статистическим данным постройте линейную модель множественной регрессии и исследуйте её.

Постройте линейную модель множественной регрессии.
Запишите стандартизованное уравнение множественной регрессии. На основе стандартизованных коэффициентов регрессии и средних коэффициентов эластичности ранжировать факторы по степени их влияния на результат.
Найдите коэффициенты парной, частной и множественной корреляции. Проанализируйте их.
Найдите скорректированный коэффициент множественной детерминации. Сравните его с нескорректированным (общим) коэффициентом детерминации.
С помощью F-критерия Фишера оценить статистическую надежность уравнения регрессии и коэффициента детерминации $R^2_{y x_1 x_2}$.
С помощью частных F-критериев Фишера оценить целесообразность включения в уравнение множественной регрессии фактора $x_1$ после $x_2$ и фактора $x_2$ после $x_1$.
Составьте уравнение линейной парной регрессии, оставив лишь один значащий фактор.

Отчет (pdf), Расчеты (xlsx)

Задача 6. По данным опроса 15 женщин, находящихся в роддоме, исследовать
зависимость веса новорожденного (у) от среднего числа сигарет (х), выкуриваемых матерью в день, с учетом числа уже имеющихся у матери детей (z).

Может пригодиться: примеры решений по эконометрике, лабораторные по статистике в Excel

Заказать решение задач по эконометрике просто

Оценим уже сегодня

Недостаток R2{\displaystyle R^{2}} и альтернативные показатели

Коэффициент корреляция в excel

Основная проблема применения (выборочного) R2{\displaystyle R^{2}} заключается в том, что его значение увеличивается (не уменьшается) от добавления в модель новых переменных, даже если эти переменные никакого отношения к объясняемой переменной не имеют! Поэтому сравнение моделей с разным количеством факторов с помощью коэффициента детерминации, вообще говоря, некорректно. Для этих целей можно использовать альтернативные показатели.

Скорректированный (adjusted) R2{\displaystyle R^{2}}

Для того, чтобы была возможность сравнивать модели с разным числом факторов так, чтобы число регрессоров (факторов) не влияло на статистику R2{\displaystyle R^{2}} обычно используется скорректированный коэффициент детерминации, в котором используются несмещённые оценки дисперсий:

Radj2=1−s2sy2=1−SSres(n−k)SStot(n−1)=1−(1−R2)(n−1)(n−k)⩽R2{\displaystyle R_{adj}^{2}=1-{\frac {s^{2}}{s_{y}^{2}}}=1-{\frac {SS_{res}/(n-k)}{SS_{tot}/(n-1)}}=1-(1-R^{2}){(n-1) \over (n-k)}\leqslant R^{2}}

который даёт штраф за дополнительно включённые факторы, где n — количество наблюдений, а k — количество параметров.

Данный показатель всегда меньше единицы, но теоретически может быть и меньше нуля (только при очень маленьком значении обычного коэффициента детерминации и большом количестве факторов). Поэтому теряется интерпретация показателя как «доли». Тем не менее, применение показателя в сравнении вполне обоснованно.

Для моделей с одинаковой зависимой переменной и одинаковым объёмом выборки сравнение моделей с помощью скорректированного коэффициента детерминации эквивалентно их сравнению с помощью остаточной дисперсии s2=SSres(n−k){\displaystyle s^{2}=SS_{res}/(n-k)} или стандартной ошибки модели s{\displaystyle s}. Разница только в том, что последние критерии чем меньше, тем лучше.

Информационные критерии

AIC — информационный критерий Акаике — применяется исключительно для сравнения моделей. Чем меньше значение, тем лучше. Часто используется для сравнения моделей временных рядов с разным количеством лагов. AIC=2kn+ln⁡SSresn{\displaystyle AIC={2k \over n}+\ln {SS_{res} \over n}}, где k— количество параметров модели.
BIC или SC — байесовский информационный критерий Шварца — используется и интерпретируется аналогично AIC. BIC=kln⁡nn+ln⁡SSresn{\displaystyle BIC={k\ln {n} \over n}+\ln {SS_{res} \over n}}. Даёт больший штраф за включение лишних лагов в модель, чем AIC.

R2{\displaystyle R^{2}}-обобщённый (extended)

В случае отсутствия в линейной множественной МНК регрессии константы свойства коэффициента детерминации могут нарушаться для конкретной реализации. Поэтому модели регрессии со свободным членом и без него нельзя сравнивать по критерию R2{\displaystyle R^{2}}. Эта проблема решается с помощью построения обобщённого коэффициента детерминации Rextended2{\displaystyle R_{extended}^{2}}, который совпадает с исходным для случая МНК регрессии со свободным членом, и для которого выполняются четыре свойства, перечисленные выше. Суть этого метода заключается в рассмотрении проекции единичного вектора на плоскость объясняющих переменных.

Для случая регрессии без свободного члена:
Rextended2=1−Y′∗(I−P(X))∗YY′∗(I−π(X))∗Y{\displaystyle R_{extended}^{2}=1-{Y’*(I-P(X))*Y \over Y’*(I-\pi (X))*Y}},
где X — матрица nxk значений факторов, P(X)=X∗(X′∗X)−1∗X′{\displaystyle P(X)=X*(X’*X)^{-1}*X’} — проектор на плоскость X, π(X)=P(X)∗in∗in′∗P(X)in′∗P(X)∗in{\displaystyle \pi (X)={P(X)*i_{n}*i_{n}’*P(X) \over i_{n}’*P(X)*i_{n}}}, где in{\displaystyle i_{n}} — единичный вектор nx1.

Rextended2{\displaystyle R_{extended}^{2}}с условием небольшой модификации, также подходит для сравнения между собой регрессий, построенных с помощью: МНК, обобщённого метода наименьших квадратов (ОМНК), условного метода наименьших квадратов (УМНК), обобщённо-условного метода наименьших квадратов (ОУМНК).

Пакет анализа Excel (программа «Регрессия»)

Расчет параметров уравнения линейной регрессии, проверку их статистической значимости и построения интервальных оценок можно выполнить значительно быстрее автоматически при использовании Пакета анализа Excel (программа «Регрессия»)

Пусть исходные данные примера 2.1 (расходы на питание – личный доход) представлены в Excel.

Выбираем команду Анализ данных→Регрессия.

В диалоговом окне режимаРегрессиязадаются следующие параметры:

Входной интервал У– вводится ссылка на ячейки, содержащие данные по результативному признаку.

Входной интервал Х – вводится ссылка на ячейки, содержащие факторные признаки.

Метки – установите флажок в активное состояние, если выделены и заголовки столбцов.

Константа- ноль – установите флажок в активное состояние, если оцениваете регрессионное уравнение без свободного члена.

При необходимости задаются и другие параметры.

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

Результаты работы программы «Регрессия» полностью совпадают с полученными ранее расчетами.

При необходимости выводятся предсказанные значения

Коэффициенты регрессии, их стандартные ошибки и коэффициент детерминации составляют:

a= -1,75; b=0,775;

= 1,65;
=0,143;
= 0,907

Результаты регрессионного анализа принято записывать в виде:

ȳ= -1,75+0,775х ;

= 0,907,

где в скобках указаны стандартные ошибки коэффициентов регрессии.

Статическая значимость коэффициента

F – тесту. ПосколькуF= 0,0124 2

se_y — стандартная ошибка для оценки y;

F — F-статистика, используемая для определения того, является ли наблюдаемая взаимосвязь между зависимой и независимой переменными случайной или нет;

df — степени свободы, используемые для нахождения F-критических значений в статистической таблице (для определения уровня надежности модели нужно сравнить значения в таблице с F-статистикой функции ЛИНЕЙН);

ss_reg — регрессионая сумма квадратов;

ss_resid — остаточная сумма квадратов.

Характеристики выводятся на экран дисплея в виде приведенного ниже массива (таблицы):

Основные задачи и виды регрессии

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

Обычно регрессия представлена в виде простого уравнения, раскрывающего зависимости и силу связи между двумя группами переменных, где одна группа является зависимой или эндогенной, а другая — независимой или экзогенной. При наличии группы взаимосвязанных показателей зависимая переменная Y определяется исходя из логики рассуждений, а остальные выступают в роли независимых Х-переменных.

Основные задачи построения регрессионной модели заключаются в следующем:

Отбор значимых независимых переменных (Х1, Х2, …, Xk).
Выбор вида функции.
Построение оценок для коэффициентов.
Построение доверительных интервалов и функции регрессии.
Проверка значимости вычисленных оценок и построенного уравнения регрессии.

Регрессионный анализ бывает нескольких видов:

парный (1 зависимая и 1 независимая переменные);
множественный (несколько независимых переменных).

Уравнения регрессии бывает двух видов:

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

Инструкция построения модели

Чтобы выполнить заданное построение в Excel, необходимо следовать указаниям:

Для дальнейшего вычисления следует использоваться функцию «Линейн ()», указывая Значения Y, Значения Х, Конст и статистику. После этого определите множество точек на линии регрессии с помощью функции «Тенденция» — Значения Y, Значения Х, Новые значения, Конст. При помощи заданных параметров вычислите неизвестное значение коэффициентов, опираясь на заданные условия поставленной задачи.

Основные задачи и виды регрессии

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

Основные задачи построения регрессионной модели заключаются в следующем:

Отбор значимых независимых переменных (Х1, Х2, …, Xk).
Выбор вида функции.
Построение оценок для коэффициентов.
Построение доверительных интервалов и функции регрессии.
Проверка значимости вычисленных оценок и построенного уравнения регрессии.

Регрессионный анализ бывает нескольких видов:

парный (1 зависимая и 1 независимая переменные);
множественный (несколько независимых переменных).

Уравнения регрессии бывает двух видов:

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

Инструкция построения модели

Чтобы выполнить заданное построение в Excel, необходимо следовать указаниям:

КОРРЕЛЯЦИОННО-РЕГРЕССИОННЫЙ АНАЛИЗ В MS EXCEL

1. Создайте файл исходных данных в MS Excel (например, таблица 2)

2. Построение корреляционного поля

Для построения корреляционного поля в командной строке выбираем меню Вставка/ Диаграмма . В появившемся диалоговом окне выберите тип диаграммы: Точечная ; вид: Точечная диаграмма , позволяющая сравнить пары значений (Рис. 22).

Рисунок 22 – Выбор типа диаграммы

Рисунок 23– Вид окна при выборе диапазона и рядов Рисунок 25 – Вид окна, шаг 4

2. В контекстном меню выбираем команду Добавить линию тренда.

3. В появившемся диалоговом окне выбираем тип графика (в нашем примере линейная) и параметры уравнения, как показано на рисунке 26.

Нажимаем ОК. Результат представлен на рисунке 27.

Рисунок 27 – Корреляционное поле зависимости производительности труда от фондовооруженности

Аналогично строим корреляционное поле зависимости производительности труда от коэффициента сменности оборудования. (рисунок 28).

от коэффициента сменности оборудования

3. Построение корреляционной матрицы.

Для построения корреляционной матрицы в меню Сервис выбираем Анализ данных.

С помощью инструмента анализа данных Регрессия , помимо результатов регрессионной статистики, дисперсионного анализа и доверительных интервалов, можно получить остатки и графики подбора линии регрессии, остатков и нормальной вероятности. Для этого необходимо проверить доступ к пакету анализа. В главном меню последовательно выберите Сервис/ Надстройки . Установите флажок Пакет анализа (Рисунок 29)

Рисунок 30 – Диалоговое окно Анализ данных

После нажатия ОК в появившемся диалоговом окне указываем входной интервал (в нашем примере А2:D26), группирование (в нашем случае по столбцам) и параметры вывода, как показано на рисунке 31.

Результат расчетов представлен в таблице 4.

Анализ результатов регрессии для R-квадрата

В Excel данные полученные в ходе обработки данных рассматриваемого примера имеют вид:

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

В данном примере R-квадрат = 0,755 (75,5%), т. е. расчетные параметры модели объясняют зависимость между рассматриваемыми параметрами на 75,5 %. Чем выше значение коэффициента детерминации, тем выбранная модель считается более применимой для конкретной задачи. Считается, что она корректно описывает реальную ситуацию при значении R-квадрата выше 0,8. Если R-квадрата 2 (RI) представляет собой числовую характеристику доли общего разброса и показывает, разброс какой части экспериментальных данных, т.е. значений зависимой переменной соответствует уравнению линейной регрессии. В рассматриваемой задаче эта величина равна 84,8%, т. е. статистические данные с высокой степенью точности описываются полученным УР.

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

Значение t-статистики (критерий Стьюдента) помогает оценивать значимость коэффициента при неизвестной либо свободного члена линейной зависимости. Если значение t-критерия > t_кр, то гипотеза о незначимости свободного члена линейного уравнения отвергается.

В рассматриваемой задаче для свободного члена посредством инструментов «Эксель» было получено, что t=169,20903, а p=2,89Е-12, т. е. имеем нулевую вероятность того, что будет отвергнута верная гипотеза о незначимости свободного члена. Для коэффициента при неизвестной t=5,79405, а p=0,001158. Иными словами вероятность того, что будет отвергнута верная гипотеза о незначимости коэффициента при неизвестной, равна 0,12%.

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

Использование Пакета анализа EXCEL для построения множественной линейной регрессионной модели

Проведем множественный регрессионный анализ с помощью надстройки MS EXCEL Пакет анализа .

Эффективно использовать надстройку Пакет анализа могут только пользователи знакомые с теорией множественного регрессионного анализа .

В данной статье решены следующие задачи:

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

В надстройке Пакет анализа для построения линейной регрессионной модели (как простой , так и множественной ) имеется специальный инструмент Регрессия .

После выбора этого инструмента откроется окно, в котором требуется заполнить следующие поля (см. файл примера лист Надстройка ):

Входной интервалY : ссылка на массив значений переменной Y. Ссылку можно указать с заголовком. В этом случае, при выводе результатов надстройка использует Ваш заголовок (для этого в окне требуется установить галочку Метки );
Входной интервал Х : ссылка на значения переменных Х (нужно указать все столбцы со значениями Х). Ссылку рекомендуется делать на диапазон с заголовками (в окне не забудьте установить галочку Метки );
Константа-ноль : если галочка установлена, то надстройка подбирает плоскость регрессии с b =0;
Уровень надежности : Это значение используется для построения доверительных интервалов для наклона и сдвига . Уровень надежности = 1- альфа . Если галочка не установлена или установлена, но уровень значимости = 95%, то надстройка все равно рассчитывает границы доверительных интервалов, причем дублирует их. Если галочка установлена, а уровень надежности отличен от 95%, то рассчитываются 2 доверительных интервала : один для 95%, другой для введенного значения. Для демонстрации вышесказанного введем 90%;
Выходной интервал: диапазон ячеек, куда будут помещены результаты вычислений. Достаточно указать левую верхнюю ячейку этого диапазона;
Остатки : будут вычислены остатки модели , т.е. разница между наблюденными и предсказанными значениями Yi для всех наблюдений n;
Стандартизированные остатки : Вышеуказанные значения остатков будут поделены на значение их стандартного отклонения ;
График остатков : Для каждой переменной X _j будет построена точечная диаграмма : значения остатков и соответствующее значение Х _ji (при прогнозировании на основании значений 2-х переменных Х будет построено 2 диаграммы (j=1 и 2));
График подбора: Для каждой переменной X _j будут построены точечные диаграммы с двумя рядами данных : точки данных (X _ji ;Y _i ) и (X _ji ;Y _{iпредсказанное} );
График нормальной вероятности: Будет построена точечная диаграмма с названием График нормального распределения . По сути — это график значений переменной Y, отсортированных по возрастанию .

В результате вычислений будет заполнен указанный Выходной интервал.

Тот же результат можно получить с помощью формул (см. файл примера лист Надстройка , столбцы I:T).

Результаты вычислений, выполненных надстройкой, полностью совпадают с вычислениями сделанными нами в статье про множественную линейную регрессию с помощью функций ЛИНЕЙН() , ТЕНДЕНЦИЯ() и др. Использование альтернативных формул помогает разобраться с алгоритмом расчета показателей регрессии.

Отчет, сформированный надстройкой, состоит из следующих разделов: