No Image

Функция наклон в excel

СОДЕРЖАНИЕ
6 просмотров
11 марта 2020

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

Описание

Возвращает наклон линии линейной регрессии для точек данных в аргументах известные_значения_y и известные_значения_x. Наклон определяется как частное от деления расстояния по вертикали на расстояние по горизонтали между двумя любыми точками прямой; иными словами, наклон — это скорость изменения значений вдоль прямой.

Синтаксис

Аргументы функции НАКЛОН описаны ниже.

Известные_значения_y Обязательный. Массив или диапазон ячеек, содержащих зависимые числовые точки данных.

Известные_значения_x Обязательный. Множество независимых точек данных.

Замечания

Аргументы могут быть либо числами, либо содержащими числа именами, массивами или ссылками.

Если аргумент, который является массивом или ссылкой, содержит текст, логические значения или пустые ячейки, эти значения игнорируются; ячейки, содержащие нулевые значения, учитываются.

Если аргументы известные_значения_y и известные_значения_x пусты или количество содержащихся в них точек не совпадает, функция НАКЛОН возвращает значение ошибки #Н/Д.

Уравнение наклона линии регрессии имеет следующий вид:

где x и y — выборочные средние значения СРЗНАЧ(массив1) и СРЗНАЧ(массив2).

Основной алгоритм, используемый в функциях НАКЛОН и ОТРЕЗОК, отличается от основного алгоритма функции ЛИНЕЙН. Разница между алгоритмами может привести к различным результатам при неопределенных и коллинеарных данных. Например, если точки данных аргумента известные_значения_y равны 0, а точки данных аргумента известные_значения_x равны 1, то справедливо указанное ниже.

Функции НАКЛОН и ОТРЕЗОК возвращают значение ошибки #ДЕЛ/0!. Алгоритм функций НАКЛОН и ОТРЕЗОК используется для поиска только одного ответа, а в данном случае их может быть несколько;

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

Пример

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

Линейная регрессия (функции ЛИНЕЙН, НАКЛОН, ОТРЕЗОК, ПРЕДСКАЗ, ТЕНДЕНЦИЯ)

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

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

ЛИНЕЙН(известные_y, [известные_x], [константа], [статистика])

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

Пример 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 введена табличная формула <=ЛИНЕЙН(y;x;1;1)>. Для того чтобы ввести табличную формулу, надо выделить блок ячеек E2:F6, ввести формулу и нажать комбинацию клавиш Ctrl Shift Enter . Фигурные скобки вводить вручную не надо.

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

  1. В E2 записан коэффициент m, в F2 — коэффициент b.
  2. В E3:F3 стандартные отклонения для этих коэффициентов.
  3. В E4 записан так называемый коэффициент детерминации R2. Этот коэффициент лежит на отрезке [0; 1]. Считается, что чем ближе этот коэффициент к 1, тем лучше регрессионное уравнение описывает зависимость. Иногда к такой интерпретации надо относиться с осторожностью.
  4. В F4 находится стандартная ошибка для оценки у.
  5. В E5 записано значение F-статистики, а в F5 — количество степеней свободы.
  6. В E6:F6 записана регрессионная сумма квадратов (10) и остаточная сумма квадратов (14).

Функция НАКЛОН

Функция НАКЛОН вычисляет коэффициент m — тангенс угла наклона прямой регрессии. Например: =НАКЛОН(y;x)

Функция ОТРЕЗОК

Функция ОТРЕЗОК вычисляет коэффициент b — отрезок, отсекаемый прямой на оси ординат. Например: =ОТРЕЗОК(y;x)

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

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

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

x — точка данных, для которой предсказывается значение.

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

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

ТЕНДЕНЦИЯ ( известные_y ;[известные_x];[новые_x];[константа])

Новые_x — новые значения x, для которых функция ТЕНДЕНЦИЯ возвращает соответствующие значения y.

Пример 2.

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

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

Читайте также:  Ростелеком услуга заблокирована как разблокировать

Функция НАКЛОН в Excel предназначена для определения угла наклона прямой, используемой для аппроксимации данных методом линейной регрессии, и возвращает значение коэффициента a из уравнения y=ax+b. Для определения наклона используются две любые точки на прямой. При этом вычисляется частное от деления длины отрезка, полученного при проецировании этих двух точек на ось Ординат (OY), на длину отрезка, образованного проекциями этих же двух точек на ось Абсцисс (OX).

Фактически, функция НАКЛОН вычисляет значение, которое характеризует скорость изменения данных вдоль линии регрессии. Зная наклон (коэффициент a) и значение коэффициента b можно рассчитать приближенные будущие значения какого-либо свойства y, которое меняется при изменении характеристики x.

Примеры использования функции НАКЛОН в Excel

Для расчета наклона линии регрессии используется уравнение:

  • x_ср – среднее значение для диапазона известных значений независимой переменной;
  • y_ср – среднее значение для диапазона известных значений зависимой переменной.

Функция НАКЛОН не может быть использована для анализа коллинеарных данных и будет возвращать код ошибки #ДЕЛ/0! в отличие от функции ЛИНЕЙН, которая использует иной алгоритм расчета и возвращает как минимум одно полученное значение.

Пример 1. Определить наклон аппроксимирующей прямой для показателей средней пенсии на протяжении нескольких лет.

Вид исходной таблицы данных:

Для нахождения наклона используем следующую формулу:

  • B3:B13 – ссылка на диапазон ячеек, содержащих данные о средней пенсии, характеризующие зависимую переменную y;
  • A3:A13 – диапазон ячеек с данными об отчетных периодах (годах), характеризующие независимую переменную x.

В результате вычислений получим:

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

Прогноз объема продаж по линейно регрессии в Excel

Пример 2. В таблице Excel содержатся данные о прибыли за продажи некоторого продукта компании на протяжении последних нескольких дней. Рассчитать коэффициенты a и b уравнения прямой y=ax+b, аппроксимирующей данные. На основе полученного уравнения спрогнозировать данные о продажах для трех последующих дней.

Вид таблицы с данными:

Для нахождения коэффициента a используем следующую формулу:

Коэффициент b рассчитывается с помощью следующей функции:

Искомое уравнение имеет вид:

Для определения последующих значений y достаточно лишь подставить требуемое значение x. Выполним расчет предполагаемой прибыли для 13-го дня:

  • D3 – полученное значение коэффициента a;
  • A15 – новое значение x;
  • E3 – значение коэффициента b.

Используем функцию автозаполнения чтобы получить значения для остальных дней:

Анализ корреляции спроса и объема производства в Excel

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

Читайте также:  Поменять пароль на вай фай роутер билайн

Вид таблицы данных:

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

Полученное значение (0,983) свидетельствует о том, что между двумя числовыми диапазонами существует сильная прямая взаимосвязь. Поэтому целесообразно использовать аппроксимирующую прямую, для нахождения коэффициентов уравнения которой используем формулы:

Для нахождения спроса на товары за июль при условии, что будет произведено, например, 2000 единиц продукции, используем полученное уравнение:

Альтернативным использованию функции НАКЛОН вариантом нахождения наклона в Excel является графический метод. Построим график на основе имеющихся данных, при этом для значений X выберем диапазон ячеек со значениями числа произведенных товаров, а для Y – с числом купленных товаров:

Отобразим на графике линию тренда:

В меню «Формат линии тренда» установим флажок напротив пункта «показывать уравнение на диаграмме»:

График примет следующий вид:

Как видно, найденные коэффициенты a и b соответствуют отображаемым на графике.

Особенности использования функции НАКЛОН в Excel

Функция имеет следующий синтаксис:

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

  • известные_значения_y – аргумент, принимающий массив числовых значений или ссылку на диапазон ячеек, которые содержат числа, характеризующие значения зависимой переменной y, которые определены для известных значений x;
  • известные_значения_x – аргумент, который может быть указан в виде массива чисел или ссылки на диапазон ячеек, содержащих числовые значения, которые характеризуют известные значения независимой переменной x.
  1. В качестве аргументов должны быть переданы массивы чисел либо ссылки на диапазоны ячеек с числовыми значениями или текстовыми строками, которые могут быть преобразованы к числам. Строки, не являющиеся текстовыми представлениями числовых данных, а также логические ИСТИНА и ЛОЖЬ в расчете не учитываются.
  2. Если в качестве аргументов были переданы массивы, содержащие разное количество элементов, или ссылки на диапазоны с разным количеством ячеек, функция НАКЛОН вернет код ошибки #Н/Д. Аналогичный код ошибки будет возвращен в случае, если оба аргумента принимают пустые массивы или ссылки на диапазоны пустых ячеек.
  3. Если оба аргумента ссылаются на нечисловые данные, функция НАКЛОН вернет код ошибки #ДЕЛ/0!.
  4. Если в диапазоне, переданном в качестве любого из аргументов, содержатся пустые ячейки, они игнорируются в расчете. Однако ячейки, содержащие значение 0 (нуль) будут учтены.
Комментировать
6 просмотров
Комментариев нет, будьте первым кто его оставит

Это интересно
Adblock detector