Функция линейн в excel пример

Функция линейн в excel пример

Функция ЛИНЕЙН() специально создана для оценки параметров линейной регрессии, а также для вывода регрессионной статистики (коэффициента детерминации, стандартных ошибок, F -статистики и др.).

Функция ЛИНЕЙН() может использоваться для простой регрессии (в этом случае прогнозируемая переменная Y зависит от одной контролируемой переменной Х) и для множественной регрессии (Y зависит от нескольких Х).

Рассмотрим функцию на примере простой регрессии (оценивается наклон и сдвиг линии регрессии). Использование функции в случае множественной регрессии рассмотрено в соответствующей статье про множественную регрессию .

Функция ЛИНЕЙН() возвращает несколько значений, поэтому для вывода результатов потребуется несколько ячеек. Часто функцию вводят как формулу массива : нажатием клавиш CTRL + SHIFT + ENTER , но, как будет показано ниже, для вывода результатов вычислений это не обязательно.

Функция работает в 2-х режимах. В простейшем случае, когда 4-й аргумент функции опущен или установлен ЛОЖЬ, функция возвращает только 2 значения — это оценки параметров модели: наклона a и сдвига b.

Для того, чтобы вычислить оценки:

  • выделите 2 ячейки в одной строке,
  • в Строке формул введите, например, = ЛИНЕЙН(C23:C83;B23:B83)
  • нажмите CTRL+SHIFT+ENTER.

В левой ячейке будет рассчитано значение наклона , в правой – сдвига .

Примечание : В справке MS EXCEL результат функции ЛИНЕЙН() соответствующий наклону обозначается буквой m, а сдвиг – буквой b.

Примечание : Без формул массива можно обойтись. Для этого нужно использовать функцию ИНДЕКС() , которая выведет нужное значение. Например, чтобы вывести величину сдвига линии регрессии введите формулу = ИНДЕКС(ЛИНЕЙН(C23:C83;B23:B83);1;2) . Если 4-й аргумент функции опущен или установлен ЛОЖЬ, то функция ЛИНЕЙН() в возвращает массив значений вида 1х2 (т.е. 2 ячейки, расположенные в одной строке). Поэтому, для вывода величины сдвига прямой линии регрессии, первый аргумент функции ИНДЕКС() , который является номером строки, должен быть равен 1, а второй аргумент, номер столбца, должен быть равен 2. Чтобы вывести значение наклона линии регрессии формулу =ЛИНЕЙН(C23:C83;B23:B83) достаточно ввести просто как обычную формулу и нажать ENTER . Конечно, можно использовать и формулу =ИНДЕКС(ЛИНЕЙН(C23:C83;B23:B83);1;1) .

Теперь о втором, более сложном режиме функции. Этот режим нужно использовать, если требуется вывести дополнительную статистику (4-й аргумент функции должен быть установлен ИСТИНА). В этом случае функция ЛИНЕЙН() возвращает 10 значений в диапазоне 5х2 ячеек (5 строк и 2 столбца). Как и в более простом режиме, в первой строке возвращаются оценки параметров модели: наклона и сдвига .

Чтобы ввести функцию как формулу массива выполните следующие действия:

  • выделите диапазон 5х2 ячеек (2 столбца и 5 строк),
  • в Строке формул введите формулу ЛИНЕЙН($C$23:$C$83;$B$23:$B$83;;ИСТИНА)
  • чтобы ввести формулу нажмите одновременно комбинацию клавиш CTRL + SHIFT + ENTER

Примечание : Чтобы обойтись без формул массива нужно использовать функцию ИНДЕКС() , которая выведет нужное значение. Например, чтобы вывести коэффициент детерминации R 2 введите формулу = ИНДЕКС(ЛИНЕЙН(C23:C83;B23:B83;;ИСТИНА);3;1) . 3 – это номер строки диапазона 5х2, а 1 – это номер столбца. В файле примера на листе Линейный в диапазоне Q 26: R 30 показано как вывести все значения, возвращаемые функцией ЛИНЕЙН() без формул массива .

Итак, установив 4-й аргумент равным ИСТИНА и введя функцию тем или иным способом, функция выведет:

  • в строке 1: оценки параметров модели (наклон и сдвиг).
  • в строке 2: Стандартные ошибки для наклона и сдвига . Ошибки обозначаются se и seb;
  • в строке 3: коэффициент детерминации и стандартную ошибку регрессии . Обозначаются R 2 и SEy;
  • в строке 4: значение F-статистики и число степеней свободы . Обозначаются F и df;
  • в строке 5: Суммы квадратов SSR, SSE определяющие изменчивость объясненную и необъясненную моделью (см. в статье Простая линейная регрессия разделы про коэффициент детерминации и статью про F-тест ). В справке MS EXCEL SSR, SSE обозначаются как ssreg (Regression Sum of Squares) и ssresid (Residuals Sum of Squares) соответственно.
Читайте также:  Какой лазерный гравер лучше выбрать

Примечание : Разобраться в значениях, возвращаемых функцией ЛИНЕЙН() , можно лишь разобравшись в теории линейной регрессии.

В файле примера также приведены формулы, позволяющие сделать расчеты без функции ЛИНЕЙН() – см. диапазон Q 34: R 38 . Альтернативные формулы помогают разобраться в алгоритме расчета вышеуказанных статистических показателей.

Рассмотрим результаты эксперимента, приведенные в исследованном выше примере.

Исследуем характер зависимости в три этапа:

— Построим график зависимости.

— Построим линию тренда (в данном случае это прямая ).

— Получим числовые характеристики коэффициентов этого уравнения.

Решение

Построение графика зависимости.

1. Выделим интервал А1:В25.

2. Вызовем Мастер диаграмм, нажав соответствующую кнопку на панели инструментов.

3. Используя мышь, выделим область для встроенной диаграммы.

4. На 1 шаге в диалоговом окне Мастера диаграмм интервал А1:В25 должен быть указан, если это не так укажите. Нажмите Шаг>.

5. На 2 шаге выберите тип диаграммы XY-точечная.Нажмите Шаг>.

6. На 3 шаге выберите первый тип автоформата. Нажмите Шаг>

7. На 4 шаге укажите следующие параметры:

8. Отвести 1 столбец для данных по оси Х; отвести 1 строку для текста легенды. Нажмите Шаг>.

9. На 5 шаге в окне «Название диаграммы: » введите заголовок «Линейная аппроксимация»; в окне «Категорий [X]:» введите «x»; в окне «Значений [Y]:» введите «y». Нажмите Закончить.

Построение линии тренда

Для построения линии тренда выполним следующую последовательность действий:

Дважды щелкнем по диаграмме. Диаграмма активизируется.

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

Вставляем линию тренда, воспользуемся меню Вставка – Линия тренда.

Появиться диалоговое окно «Линия тренда» выберем на вкладке «Тип» (Рис.2) линейный тип и перейдем к вкладке «Параметры».

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

На диаграмме появится линия тренда с соответствующим уравнением. Также изменится легенда. При желании текстовое поле с уравнением и значением , а также название координат x и y, можно оттащить в более удобное место, как это сделано на Рис 4.

Для построения квадратичной аппроксимации на четвертом шаге в диалоговом окне «Линия тренда» выберем на вкладке «Тип» (Рис.2) полиномиальный тип степень 2. Результат представлен на рис.5.

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

Сравнивая результаты, полученные при помощи функции ЛИНЕЙН видим что они полностью совпадают с вычислениями, проведенными выше. Это указывает на то, что вычисления верны.

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

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

Для построения числовых характеристик необходимо создать табличную формулу, которая будет занимать 5 строк и 2 столбца. Этот интервал может располагаться в произвольном месте на рабочем листе. В этот интервал требуется ввести функцию ЛИНЕЙН. Для этого выполняем следующую последовательность действий:

— Выделите область A65:B69.

— Вызовите Мастер функций.

— Выберите функцию Линейн.

— Определим аргументы функции.

— В качестве изв_знач_уукажите В1:В25.

— В качестве изв_знач_хукажите А1:А25.

— Третье поле Константаоставьте пустым.

— В четвертом поле статнаберите истина.

— Нажмите кнопку Закончить.

— Установите курсор в строку формул.

Нажмите комбинацию клавиш Ctrl+Shift+Enter, это обеспечит ввод табличной формулы!

Читайте также:  Как вести съемку экрана компьютера

В результате должны заполниться все ячейки интервала A65:B69(см. табл.9).

Поясним назначение некоторых величин, расположенных в табл.9.

Величины, расположенные в ячейках A67 и B67 характеризуют соответственно наклон и сдвиг.

A69 — коэффициент детерминированности.

A70 — F-наблюдаемое значение.

B68 — число степеней свободы.

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

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

Рассмотрим назначение функции ЛИНЕЙН.

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

или ,

где зависимое значение y является функцией независимого значения x. Значения m — это коэффициенты, соответствующие каждой независимой переменной x, а b — это постоянная. Заметим, что y, x и m могут быть векторами.

Функция ЛИНЕЙН возвращает массив . ЛИНЕЙН может также возвращать дополнительную регрессионную статистику.

Синтаксис

ЛИНЕЙН(известные_значения_y;известные_значения_x;конст; статистика)

Известные_значения_y — это множество значений y, которые уже известны для соотношения .

— Если массив известные_значения_y имеет один столбец, то каждый столбец массива известные_значения_x интерпретируется как отдельная переменная.

Известные_значения_x — это множество значений x, которые уже известны для соотношения .

— Массив известные_значения_x может содержать одно или несколько множеств переменных.

— Если используется только одна переменная, то известные_значения_y и известные_значения_x могут быть массивами любой формы при условии, что они имеют одинаковую размерность.

— Если используется более одной переменной, то известные_значения_y должны быть вектором (то есть интервалом высотой в одну строку или шириной в один столбец).

Конст— это логическое значение, которое указывает, требуется ли, чтобы константа b была равна 0.

— Если констимеет значение ИСТИНА или опущена, то b вычисляется обычным образом.

— Если констимеет значение ЛОЖЬ, то b полагается равным 0 и значения m подбираются так, чтобы выполнялось соотношение y = mx .

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

— Если статистикаимеет значение ИСТИНА, то функция ЛИНЕЙН возвращает дополнительную регрессионную статистику, так что возвращаемый массив будет иметь вид:

. (17)

— Если статистика имеет значение ЛОЖЬ или опущена, то функция ЛИНЕЙН возвращает только коэффициенты m и постоянную b.

Дополнительная регрессионная статистика приведена в табл. 10

Величина Описание
Стандартные значения ошибок для коэффициентов .
= #Н/Д, если конст имеет значение ЛОЖЬ.
Коэффициент детерминированности.
Стандартная ошибка для оценки y.
F-статистика, или F-наблюдаемое значение. F-статистика используется для определения того, является ли наблюдаемая взаимосвязь между зависимой и независимой переменными случайной или нет.
Степени свободы. Степени свободы полезны для нахождения F-критических значений в статистической таблице. Для определения уровня надежности модели нужно сравнить значения в таблице с F-статистикой, возвращаемой функцией ЛИНЕЙН.
Регрессионная сумма квадратов.
Остаточная сумма квадратов

В табл.11 показано, в каком порядке возвращается дополнительная регрессионная статистика.

Точность аппроксимации с помощью прямой, вычисленной функцией ЛИНЕЙН, зависит от степени разброса данных. Чем ближе данные к прямой, тем более точной является модель, используемая функцией ЛИНЕЙН. Функция ЛИНЕЙН использует метод наименьших квадратов для определения наилучшей аппроксимации данных. Когда имеется только одна независимая переменная, тогда x, m и b вычисляются по следующим формулам:

, (18)

Эти формулы могут быть использованы для нахождения решения системы (4), вместо m и b следует подставить и .

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

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

1. В главном меню последовательно выберите пункты Сервис/Анализ данных/Корреляция. Щелкните по кнопке ОК;

2. Заполните диалоговое окно ввода данных и параметров вывода (рис. 1.1):

Читайте также:  Телефон для частного дома

Входной интервал – диапазон, содержащий анализируемые данные, это может быть одна или несколько строк столбцов;

Группирование – по столбцам (или по строкам);

Метки – флажок, который указывает, содержит ли первая строка названия столбцов или нет;

Выходной интервал – достаточно указать левую верхнюю ячейку будущего диапазона;

Щелкните по кнопке ОК.

3. Результаты вычислений — матрица парных коэффициентов корреляции – представлены на рис. 1.2.

Рис.1.1 Диалоговое окно вводов параметров инструмента Корреляция

Рис. 1.2. Матрица коэффициентов парной корреляции

Встроенная статистическая функция ЛИНЕЙН определяет параметры линейной регрессии Порядок вычисления следующий:

1) введите исходные данные или откройте существующий файл, содержащий анализируемые данные;

2) выделите область пустых ячеек 5 х (P+1) (5 строк, (P+1) столбца) для вывода результатов регрессионной статистики или область 1 х (P+1) – для получения только оценок коэффициентов регрессии;

3) активизируйте мастер функций любым из способов:

a) в главном меню выберите Вставка /Функция;

Рис. 1. 3. Диалоговое окно «Мастер функций»

b) на панели инструментов Стандартная щелкните по кнопке Вставка функции;

4) в окне Категория (Рис.1.3) выберите Статистические, в окне Функция – ЛИНЕЙН. Щелкните по кнопке ОК;

5) заполните аргументы функции (рис.1.4) по исходным данным (рис. 1.2):

Pис. 1.4. Диалоговое окно ввода аргументов функции ЛИНЕЙН

Известные значения у – диапазон, содержащий данные результативного признака;

Известные значения х — диапазон, содержащий данные факторов независимого признака:

Константа – логическое значение, которое указывает на наличие или отсутствие свободного члена в уравнении. Если Константа =1 (Истина), то свободный член рассчитывается , если Константа = 0 , то свободный член равен 0.;

Статистика — логическое значение, которое указывает, выводить дополнительную информацию по регрессионному анализу или нет. Если Статистика =1 (Истина), то дополнительная информация выводится, если Статистика = 0, то выводятся только оценки параметров уравнения.

6) нажмите на комбинацию клавиш + ;

7) данные регрессионного анализа будут выводиться в следующем порядке:

Значение коэффициента b р . . . Значение коэффициента b1 Значение коэффициента а
Стандартная ошибка коэффициента b p (m b p) . . . Стандартная ошибка коэффициента b1 (mb1) Стандартная ошибка коэффициента а (m a)
Коэффициент детерминации R 2 . . . Стандартная ошибка результата (m )
Критерий Фишера F-критерий . . . Остаточное число степеней свободы (df)
Факторная сумма квадратов — SS факт. . . . Остаточная сумма квадратов — SS ост.

Для данных вышеприведенного примера результат вычисления множественной регрессии ЛИНЕЙНпредставлен на рис.1.5.

Рис. 1.5. Результат вычисления функции ЛИНЕЙН

Множественная линейная регрессия имеет вид:

.

Множественный коэффициент детерминации R 2 = 0,644. Это говорит о том, что доля вариации результата у за счет анализируемых факторов х1 и х2 составляет 64,4 %. Доля вариации от неучтенных в анализе факторов 35,6 %.

Расчетное значение критерия Фишера — F расч.= 7,242. Табличное значение критерия Фишера при df факт.= 2 и df ост. = 8 составляет — F табл. = 4,46. Следовательно, полученное уравнение множественной регрессии линейного вида статистически значимо с вероятностью ошибки 5%.

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

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

Рис.1.6. Результат применения функции ЛИНЕЙН для получения нелинейной регрессии в виде полинома второго порядка

Дата публикования: 2014-10-17 ; Прочитано: 5072 | Нарушение авторского права страницы

studopedia.org — Студопедия.Орг — 2014-2020 год. Студопедия не является автором материалов, которые размещены. Но предоставляет возможность бесплатного использования (0.002 с) .

Ссылка на основную публикацию
Установить gvlk ключ что это
В связи с недавним выходом окончательной RTM версии пакета Microsoft Office 2016, корпоративные заказчики уже могут начинать переход на новую...
Топ вай фай адаптеров для пк
На заре развития интернета люди пользовались только проводным трафиком. После этого в «моду» начали входить модемы, которые подключались к беспроводному...
Топ дешевых наушников с хорошим звуком
Проводные наушники должны умереть! Так решил мобильный рынок и производители смартфонов, стремительно избавляющиеся от устаревшего 3,5 мм джека. Стоит ли...
Установить openal32 dll для windows 7
Данная библиотека задействуется во многих процессах во время работы компьютера. Например, она используется в играх, мультимедиа и различных программах. Иногда...
Adblock detector