Как использовать функцию линейн и линейн в excel

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

Исходные данные

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

Примечание. Собранные в разные моменты времени значения одной и той же величины образуют временной ряд. Каждое значение такого временного ряда называется измерением. Например: данные о продажах за последние 5 лет по месяцам — временной ряд; продажи за январь прошлого года — измерение.

Составляющие прогноза

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

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

Эти три пункта в совокупность образуют регулярную составляющую временного ряда.

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

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

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

Виды моделей

Следующий вопрос, на который нужно ответить при построении прогноза: “А какие модели временного ряда бывают?”

Обычно выделяют два основных вида:

• Аддитивная модель: Уровень временного ряда = Тренд + Сезонность + Случайные отклонения
• Мультипликативная модель: Уровень временного ряда = Тренд X Сезонность X Случайные отклонения

Иногда также выделают смешанную модель в отдельную группу:

Смешанная модель: Уровень временного ряда = Тренд X Сезонность + Случайные отклонения

С моделями мы определились, но теперь возникает еще один вопрос: «А когда какую модель лучше использовать?»

Классический вариант такой: — Аддитивная модель используется, если амплитуда колебаний более-менее постоянная; — Мультипликативная – если амплитуда колебаний зависит от значения сезонной компоненты.

Пример:

Функция ROUNDUP — служба поддержки Microsoft

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Дополнительно.

В этой статье описаны синтаксис формулы и использование функции ОКРУГЛВВЕРХ в Microsoft Excel.

ОКРУГЛВВЕРХ(число, число_разрядов)

Синтаксис функции ОКРУГЛВВЕРХ имеет следующие аргументы:

Замечания

• ОКРУГЛВВЕРХ ведет себя как ОКРУГЛ, за исключением того, что всегда округляет число в большую сторону.

• Если num_digits больше 0 (ноль), то число округляется до указанного количества знаков после запятой.

• org/ListItem»>

  Если num_digits равно 0, то число округляется до ближайшего целого числа.

• Если num_digits меньше 0, то число округляется в большую сторону слева от десятичной точки.

Пример

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

Типы линий тренда Excel

При добавлении линии тренда в Excel у вас есть 6 различных вариантов на выбор. Кроме того, Microsoft Excel позволяет отображать на диаграмме уравнение линии тренда и значение R-квадрата:

• Уравнение линии тренда — это формула, которая находит линию, которая лучше всего соответствует точкам данных.
• Значение R-квадрата измеряет надежность линии тренда: чем ближе R2 к 1, тем лучше линия тренда соответствует данным.

Ниже вы найдете краткое описание каждого типа линии тренда с примерами графиков.

Линейная линия тренда

Линейную линию тренда лучше всего использовать с линейными наборами данных, когда точки данных на диаграмме напоминают прямую линию. Как правило, линейная линия тренда описывает непрерывный рост или падение во времени.

Например, следующая линейная линия тренда показывает устойчивый рост продаж в течение 6 месяцев. А значение R2, равное 0,9855, указывает на довольно хорошее соответствие оценочных значений линии тренда фактическим данным.

Экспоненциальная линия тренда

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

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

Хорошим примером экспоненциальной кривой является убыль всей популяции диких тигров на Земле.

Логарифмическая линия тренда

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

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

Полиномиальная линия тренда

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

Как правило, многочлен классифицируется по степени наибольшего показателя. Степень полиномиальной линии тренда также можно определить по количеству изгибов на графике. Как правило, линия тренда квадратичного полинома имеет один изгиб (холм или впадина), кубический полином имеет 1 или 2 изгиба, а полином четвертой степени имеет до 3 изгибов.

При добавлении полиномиальной линии тренда в диаграмму Excel вы указываете степень, вводя соответствующее число в поле «Порядок» на панели инструментов. Формат линии тренда панель, которая по умолчанию равна 2:

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

Линия тренда мощности

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

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

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

Обратите внимание на значение R-квадрата 0,9918, что означает, что наша линия тренда почти идеально соответствует данным

Линия тренда скользящей средней

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

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

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

Функция EXCEL ЛИНЕЙН()

Функция ЛИНЕЙН() может использоваться для простой регрессии (в этом случае прогнозируемая переменная 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 . Альтернативные формулы помогают разобраться в алгоритме расчета вышеуказанных статистических показателей.

Как решать задачи на линейную функцию « y = kx + b »

Построить график функции « y = 2x + 3 ». Найти по графику:

Используем правила, по которым мы строили график функции выше. Для построения графика функции « y = 2x + 3 » достаточно найти всего две точки.

Выберем два произвольных числовых значения для « x ». Для удобства расчетов выберем числа « 0 » и « 1 ».

Выполним расчеты и запишем их результаты в таблицу.

Точка	Координата по оси « Оx »	Координата по оси « Оy »
(·)A	y(0) = 2 · 0 + 3 = 3
(·)B	1	y(1) = 2 ·1 + 3 = 5

Отметим полученные точки на прямоугольной системе координат.

Соединим полученные точки прямой. Проведенная прямая будет являться графиком функции « y = 2x + 3 ».

Теперь работаем с построенным графиком функции « y = 2x + 3 ».

Требуется найти значение « y », соответствующее значению « x », которое равно −1; 2; 3; 5 .

Тему «Как получить координаты точки функции» с графика функции мы уже подробно рассматривали в уроке «Как решать задачи на функцию».

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

Запомните!

Чтобы найти значение « y » по известному значению « x » на графике функции необходимо:

1. провести перпендикуляр от оси « Ox » (ось абсцисс) из заданного числового значения « x » до пересечения с графиком функции;
2. из полученной точки пересечения перпендикуляра и графика функции провести еще один перпендикуляр к оси « Oy » (ось ординат);
3. полученное числовое значение на оси « Oy » и будет искомым значением.

По правилам выше найдем на построенном ранее графике функции « y = 2x + 3 » необходимые значения функции « y » для « x » равным −1; 2; 3; 5 .

Заданное значение « x »	Полученное с графика значение « y »
−1	1
2	7
3	9
5	13

Переходим ко второму заданию задачи. Требуется найти значение « x », если значение « y » равно 1; 4; 0; −1 .

Выполним те же действия, что и при решении предыдущего задания. Разница будет лишь в том, что изначально мы будем проводить перпендикуляры от оси « Oy » .

Заданное значение « y »	Полученное с графика значение « x »
−1	−2
−1,5
1	−1
4	0,5

Как проверить, проходит ли график через точку

Не выполняя построения графика функции « y = 2x −

1

3

», выяснить, проходит ли график через точки с координатами (0; −

1

3

) и (1; −2) .

Запомните!

Чтобы проверить принадлежность точки графику функции нет необходимости строить график функции.

Достаточно подставить координаты точки в формулу функции (координату по оси « Ox » вместо « x », а координату по оси « Oy » вместо « y ») и выполнить арифметические расчеты.

Как найти точки пересечения графика с осями

Найти координаты точек пересечения графика функции « y = −1,5x + 3 » с осями координат.

Для начала построим график функции « y = −1,5x + 3 » и на графике отметим точки пересечения с осями.

Выберем два произвольных числовых значения для « x » и рассчитаем значение « y » по формуле функции. Например, для x = 0 и x = 1 .

Точка	Координата по оси « Оx »	Координата по оси « Оy »
(·)A	y(0) = −1,5 · 0 + 3 = 3
(·)B	1	y(1) = −1,5 · 1 + 3 = 1,5

Отметим полученные точки на системе координат и проведем через них прямую. Тем самым мы построим график функции « y = −1,5x + 3 ».

Теперь найдем координаты точек пересечения графика функции с осями по формуле функции.

Запомните!

Чтобы найти координаты точки пересечения графика функции с осью « Oy » (осью ординат) нужно:

• приравнять координату точки по оси « Ox » к нулю (x = 0) ;
• подставить вместо « x » в формулу функции ноль и найти значение « y »;
• записать полученные координаты точки пересечения с осью « Oy » .

Подставим вместо « x » в формулу функции « y = −1,5x + 3 » число ноль.

Запомните!

Чтобы найти координаты точки пересечения графика функции с осью « Ox » (осью абсцисс) нужно:

• приравнять координату точки по оси « Oy » к нулю (y = 0) ;
• подставить вместо « y » в формулу функции ноль и найти значение « x »;
• записать полученные координаты точки пересечения с осью « Oy » .

Подставим вместо « y » в формулу функции « y = −1,5x + 3 » число ноль.

Чтобы было проще запомнить, какую координату точки нужно приравнивать к нулю, запомните «правило противоположности».

Если нужно найти координаты точки пересечения графика с осью « Ox » , то приравниваем « y » к нулю.

И наооборот. Если нужно найти координаты точки пересечениа графика с осью « Oy » , то приравниваем « x » к нулю.

Линейная функция (ЕГЭ 2023) | ЮКлэва

• приравнять координату точки по оси « Ox » к нулю (x = 0) ;
• подставить вместо « x » в формулу функции ноль и найти значение « y »;
• записать полученные координаты точки пересечения с осью « Oy » .

Если вы пока не можете сразу определить, основываясь на вышеприведенных рекомендациях, какой вид аппроксимации подойдет конкретно в вашем случае, то есть смысл попробовать все методы. После построения линии тренда и просмотра её уровня достоверности можно будет выбрать оптимальный вариант.
Не выполняя построения графика функции « y = 2x −

1

3

», выяснить, проходит ли график через точки с координатами (0; −

1

3

) и (1; −2) .

ЛИНЕЙН советы

1. Убедитесь, что у вас установлена ​​самая последняя версия Microsoft 365 для использования функции ЛИНЕЙН с динамическими массивами. Возможно, вам потребуется включить текущий канал предварительной оценки Office (предварительная версия), чтобы использовать функции динамического массива. На странице учетной записи:
2. Если вы используете версию, отличную от Microsoft 365, вам придется использовать устаревший метод CTRL + SHIFT + ENTER (CSE) для оценки формул массива.
3. При использовании устаревшего метода количество столбцов, выделяемых при вводе функции массива ЛИНЕЙН, всегда равно количеству столбцов. Икс переменные в ваших данных плюс 1. Число строк, которые нужно выбрать для массива, равно 5.
4. Если вы будете делиться своей версией Excel с динамическим массивом с кем-то, кто использует версию, отличную от Microsoft 365, используйте устаревший метод CSE, чтобы избежать проблем с совместимостью.

Узнайте, как создать экспоненциальную оценку в Excel?

Обратите внимание, что функция Excel для ЛОГАРИФМИЧЕСКОЙ ОЦЕНКИ применяется путем вычисления значений кривой возрастающей формы. Действительно, в регрессионном анализе соответствующие значения подобраны и соответствуют экспоненциальному графику через матрицу значений, формула которой представлена ​​следующим образом:. Y = б

м (в экспоненте от «х»)

Y = б. м (в экспоненте от «х»)

В этом смысле процесс такой же, как и в функции Excel для ЛИНЕЙНОЙ ОЦЕНКИ; за исключением того, что в этом случае ЛОГАРИФМИЧЕСКАЯ ОЦЕНКА возвращается: {mn, mn-1,…, m1, b}.

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

—>Информационные технологии —>

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

ЛИНЕЙН(известные_y, , , )

Пример 1

Даны x и y: (0, 3), (1, 1), (2, 6), (3, 3), (4, 7). Найти коэффициенты m и b прямой линии y = mx + b , наилучшим образом аппроксимирующей эти данные по критерию наименьших квадратов.

Подготовим таблицу как показано ниже. Ячейки E2:F6 не заполняйте, они будут заполнены автоматически.

1. В A2:A6 введены значения x, блоку присвоено имя х.
2. В В2:В6 введены значения y, блоку присвоено имя y.
3. В E2:F6 введена табличная формула . Для того чтобы ввести табличную формулу, надо выделить блок ячеек E2:F6, ввести формулу и нажать комбинацию клавиш Ctrl Shift Enter . Фигурные скобки вводить вручную не надо.

Пояснение к блоку статистических результатов функции Линейн.

В E2 записан коэффициент m, в F2 — коэффициент b. В E3:F3 стандартные отклонения для этих коэффициентов. В E4 записан так называемый коэффициент детерминации R2. Этот коэффициент лежит на отрезке . Считается, что чем ближе этот коэффициент к 1, тем лучше регрессионное уравнение описывает зависимость

Иногда к такой интерпретации надо относиться с осторожностью. В F4 находится стандартная ошибка для оценки у

В E5 записано значение F-статистики, а в F5 — количество степеней свободы. В E6:F6 записана регрессионная сумма квадратов (10) и остаточная сумма квадратов (14).

Функция ПРЕДСКАЗ

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

ПРЕДСКАЗ ( x ; известные_y ; известные_x )

Функция ТЕНДЕНЦИЯ

Возвращает значения в соответствии с линейным трендом. Аппроксимирует прямой линией (по методу наименьших квадратов) массивы «известные_y» и «известные_x». Возвращает значения y, соответствующие этой прямой для заданного массива «новые_x».

ТЕНДЕНЦИЯ ( известные_y ;;;)

Пример 2.

Постройте таблицу по образцу. В примечаниях показаны имена ячеек или формулы. В A5:A9 известныеX, в B5:B9 известныеY. Блоку A5:A12 присвоено имя Х. Рассчитайте Предсказ, Тенденцию и прямую mx+b.

Постройте диаграмму по образцу. На диаграмме видно, что прямая пересекает ось ординат в точке 2 (b=2), а наклон прямой равен 45° (m=1). Прямые Предсказ, Тенденция и mx+b слились в одну линию.

Примеры использования функции ЛИНЕЙН в Excel

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

1. Выделите диапазон D2:Е2, так как функция ЛИНЕЙН возвращает массив из двух значений, расположенных по горизонтали, но не по вертикали.
2. Введите известные значения y – баллы, которые студенты заработали на последнем тестировании (диапазон ячеек В2:В12).
3. Затем введите известные значения х – количество часов, которые студенты потратили на подготовку к тестам (диапазон А2:А12).
4. Опустите аргумент .
5. Опустите аргумент .
6. Введите формулу с помощью Ctrl+Shift+Enter.

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

Теперь, на примере решения второй задачи, разберем необходимость в отображении не только наклона и отрезка, но и дополнительной статистики. Для примера, на диапазоне А1:В6 выстроим таблицу с соотношением у и х соответствующих сумме заработка студентом денежных средств за период в 5 месяцев. Так как мы имеем лишь одну переменную х, то необходимо выделить диапазон состоящий из двух столбцов и пяти строк

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

Применительно к решаемой нами задаче, выделим диапазон Е2:F6, затем введем формулу аналогично предыдущей задаче, но в данном случае третьему и четвертому аргументу присвоим значение 1 соответствующее ИСТИНЕ. Для вывода параметров статистики функции ЛИНЕЙН необходимо нажат Ctrl+Shift+Enter, результат должен соответствовать следующему рисунку, на котором представлено обозначение дополнительных статистик:

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

В случае множественной регрессии, когда значения «y» зависят от двух переменных «х», функция ЛИНЕЙН возвращает 12 статистик. На рисунке с модифицированной таблицей от 1 примера, представленном ниже используются следующие обозначения:

• y = зависимая переменная;
• x1 = независимая переменная 1 = баллы за домашнее задание;
• x2 = независимая переменная 2 = часы подготовки к тесту.

Чтобы выполнить множественную регрессию:

Выделите диапазон В3:D7 (число столбцов = число переменных +1; число строк всегда равно 5).
Наберите формулу =ЛИНЕЙН(D14:D24;B14:C24;1;1)

Для аргумента известные_значения_х, выделите оба столбца значений x из диапазона В14:С24.
Введите функцию с помощью клавиш Ctrl+Shift+Enter.
Обратите внимание, что несмотря на то, что значения х1 указаны в диапазоне В14:С24 до значений х2, наклон сначала указан для х2.

Диапазон D5:D7 содержит ошибку #Н/Д – значащую, что формула не может обнаружить значения для данных ячеек. Визуально наличие ошибки отвлекает от сути решения, поэтому далее предложим вариант избавления от нее. Так, если дополнить формулу содержащую функцию ЛИНЕЙН функцией ЕСЛИОШИБКА, то можно значительно улучшить вид таблицы, результат которой представлен ниже:

Распределение статистик в таблице их значение представлено на следующем рисунке:

Скачать примеры функции ЛИНЕЙН в Excel

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

Функция EXCEL ЛИНЕЙН()

history 26 января 2019 г.

Статистический анализ

Функция ЛИНЕЙН() специально создана для оценки параметров линейной регрессии, а также для вывода регрессионной статистики (коэффициента детерминации, стандартных ошибок, 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 . Альтернативные формулы помогают разобраться в алгоритме расчета вышеуказанных статистических показателей.