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

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

Смысл выходной статистической информации функции ЛИНЕЙН

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

Общий синтаксис вызова функции ЛИНЕЙН имеет следующий вид:

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

Для работы с функцией необходимо заполнить как минимум 1 обязательный и при необходимости 3 необязательных аргумента:

аргументы.
  1. Известные_значения_y − это множество значений y, которые уже известны для соотношения y=mx+b.
  2. Известные_значения_x − это множество известных значений x. Если этот аргумент опущен, то предполагается, что это массив {1; 2; 3; ...} такого же размера, как и известные_значения_y.
  3. Конст − это логическое значение, которое указывает, требуется ли, чтобы константа b была равна 0. Если в функции ЛИНЕЙН аргумент константа имеет значение ЛОЖЬ, то b полагается равным 0 и значения m подбираются так, чтобы выполнялось соотношение y = mx.
  4. Статистика − это логическое значение, которое указывает, требуется ли выдать дополнительную статистику по регрессии.


Примеры использования функции ЛИНЕЙН в 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 = часы подготовки к тесту.
функция ЛИНЕЙН.

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

  1. Выделите диапазон В3:D7 (число столбцов = число переменных +1; число строк всегда равно 5).
  2. Наберите формулу =ЛИНЕЙН(D14:D24;B14:C24;1;1). Для аргумента известные_значения_х, выделите оба столбца значений x из диапазона В14:С24.
  3. Введите функцию с помощью клавиш Ctrl+Shift+Enter.
  4. Обратите внимание, что несмотря на то, что значения х1 указаны в диапазоне В14:С24 до значений х2, наклон сначала указан для х2.
выполнить множественную регрессию.

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

ЕСЛИОШИБКА.

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

Распределение статистик в таблице.

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

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


en ru