Функция ОТРЕЗОК в Excel для прогнозирования явления

Функция ОТРЕЗОК в Excel на основе значений x и y находит точку пересечения линейного графика с осью Y. Точка пересечения определяется на оптимальной линии регрессии, коэффициентами которой являются «известные значения x» и «известные значения y». К функции обращаются чаще всего, если необходимо найти значение зависимой переменной при равной нулю независимой переменной.

Особенности и синтаксис функции ОТРЕЗОК

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

1. Заданные значения y. Массив зависимых переменных, данных или результатов наблюдений.
2. Заданные значения x. Массив независимых переменных, данных или результатов наблюдений.

Оба аргумента обязательные. Способы выражения – числа, содержащие числа имена, массивы или ссылки.

Особенности работы функции ОТРЕЗОК:

1. Функция игнорирует пустые ячейки, ячейки с текстовыми и логическими значениями. Нулевые значения учитываются.
2. Если аргументы содержат разное количество значений либо данные вовсе отсутствуют, появляется ошибка #Н/Д.
3. Точка пересечения графика линейной регрессии с осью Y имеет уравнение Где x и y рассчитаны с помощью функции СРЗНАЧ (средние значения выборок множества данных x и y).



Примеры функции ОТРЕЗОК в Excel

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

Некоторые статистические функции Excel позволяют получать регрессии (линии тренда) сразу на основе исходных табличных данных (без построения диаграммы). Самый простой вариант моделирования исследуемого процесса – линейная регрессия. Модель хороша в том случае, когда значения характеристик растут или уменьшаются с постоянной скоростью.

Для построения линейной регрессии в Excel можно использовать статистическую функцию ОТРЕЗОК. Она определяет отрезок, отсекаемый регрессией на оси ординат.

Имеются наблюдения, выраженные числовыми значениями, о количестве оказанных предприятием услуг с 1 по 11 число текущего месяца.

Необходимо составить прогноз на 12-14 число.

Получим ряды данных для линейной регрессии с помощью функции ОТРЕЗОК.

! Сама функция не является регрессией. Она лишь определяет необходимые параметры регрессии.

Ставим курсор в ячейку Е2 и вызываем «Мастер функций». В категории «Статистические» находим функцию ОТРЕЗОК. Известные значения y – В2:В12. Известные значения x – А2:А12.

Мы получили значение свободного члена b уравнения линейной регрессии y = mx + b. Для построения линейной регрессии и выполнения поставленной задачи нужно найти другие неизвестные. В частности, угловой коэффициент регрессии m. Воспользуемся встроенной функцией НАКЛОН.

Аргументы заполняем аналогично:

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

Абсолютные ссылки на ячейки D2 и E2 не позволяют адресу меняться при копировании формулы. Так как количество услуг не может быть дробным числом, на вкладке «Формат ячеек» ставим 0 десятичных знаков для числового формата.

Преимущества данного метода регрессионного анализа:

• простой и однотипный процесс построения рядов данных для функции ОТРЕЗОК (и функции НАКЛОН);
• стандартный способ построения линии тренда;
• возможность составления прогноза исследуемого явления на нужное число шагов вперед или назад.

Существенный недостаток метода – с помощью встроенной функции ОТРЕЗОК можно создать только линейный тип линии тренда. Отсутствие других типов часто не позволяет получить максимально реальный прогноз и подобрать точную модель исследуемого явления.