Функция НАКЛОН для определения наклона линейной регрессии в Excel

Функция НАКЛОН в 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 используем следующую формулу:

=НАКЛОН(B3:B14;A3:A14)

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

=ОТРЕЗОК(B3:B14;A3:A14)

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

y=1121,02x+17286,14

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

=$D$3*A15+$E$3

Описание аргументов:

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

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

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

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

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

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

=КОРРЕЛ(B2:G2;B3:G3)

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

=НАКЛОН(B3:G3;B2:G2)

=ОТРЕЗОК(B3:G3;B2:G2)

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

=B6*H2+B7

Полученное значение:

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

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

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

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

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

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

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

=НАКЛОН(известные_значения_y;известные_значения_x)

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

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

Примечания:

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