Функция ЛГРФПРИБЛ для аппроксимации данных таблиц в Excel

Функция ЛГРФПРИБЛ в Excel предназначена для определения значений, на основе которых может быть построена экспоненциальная кривая, аппроксимирующая имеющиеся числовые данные, и возвращает массив значений. Для корректной работы рассматриваемой функции ее следует вводить как формулу массива.

Методы аппроксимации табличных данных в Excel

Функция ЛГРФПРИБЛ возвращает данные, необходимые для построения кривой, описываемой следующим уравнением:

F(x) = b*СТЕПЕНЬ(m;x)

Если имеется две и более переменных, это уравнение переписывается следующим образом:

F(x1,x2,…xn)=b*СТЕПЕНЬ(m1;x1)*СТЕПЕНЬ(m2;x2)*…*СТЕПЕНЬ(mn;xn)

Возвращаемые рассматриваемой функцией данные представляют собой следующий массив:

{mn;mn-1;...;m1;b}

То есть, имеем массив оснований, возводимых в степени (известные значения переменных x), и коэффициент b.

Пример 1. В таблице приведены данные, характеризующие динамику курса доллара на протяжении 10 лет (с 2006 по 2016 год). Необходимо спрогнозировать курс доллара на 2019 год на основании имеющихся данных.

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

Пример 1.

Для расчета тренда (коэффициент, используемый для предсказания последующих значений курса) используем функцию:

=ЛГРФПРИБЛ(B2:B12;A2:A12)

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

  • B2:B12 – известные данные зависимой переменной (значения курса);
  • A2:A12 – известные данные независимой переменной (года).

Результат расчетов:

ЛГРФПРИБЛ.

Для предсказания курса на 2019 год используем формулу:

Результат вычислений:

спрогнозирован курс доллара.

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



Прогнозирование финансовых результатов методом аппроксимации в Excel

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

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

Пример 2.

Определим коэффициенты достоверности аппроксимации для линейной и экспоненциальной функций с помощью следующих функций (вводить как формулы массива CTRL+SHIFT+Enter):

коэффициенты достоверности аппроксимации. Прогнозирование финансовых результатов.

Поскольку обе функции возвращают результат в виде массива данных, в котором в третьей строке первого столбца содержится искомое значение R^2, используем функцию ИНДЕКС для возврата желаемого результата.

Чем ближе значение R^2 к 1, тем выше точность аппроксимации. Как видно, наибольшую точность обеспечивает экспоненциальная функция. Однако разница не является существенной, поэтому использование функции ЛИНЕЙН является допустимым в данном случае.

Правила метода аппроксимации по функции ЛГРФПРИБЛ в Excel

Функция имеет следующую синтаксическую запись:

=ЛГРФПРИБЛ(известные_значения_y;[известные_значения_x];[конст];[статистика])

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

  • известные_значения_y – обязательный, принимает ссылку на диапазон ячеек или массив данных - числовые значения, которые характеризуют состояние зависимой переменной y из указанного выше уравнения;
  • [известные_значения_x] – необязательный, принимает ссылку на диапазон ячеек или массив чисел, которые являются уже известными значениями независимой переменной x. Если явно не указан, по умолчанию принимается массив значений {1;2;…N}, где N – количество элементов в массиве, характеризующем известные_значения_y;
  • [конст] – необязательный, принимает данные логического типа, интерпретируемые следующим образом: ИСТИНА или явно не указан – функция вычисляет значение коэффициента b из приведенного выше уравнения, ЛОЖЬ – значение данного коэффициента принимается равным 1;
  • [статистика] – необязательный, принимает логические значения ИСТИНА (функция возвращает дополнительные данные на основе проведенного регрессионного анализа) или ЛОЖЬ (значение по умолчанию) – функция возвращает только значения коэффициентов m и b.

Примечания:

  1. Точность вычислений рассматриваемой функцией зависит от степени близости графика, построенного на основе имеющихся значений, к экспоненциальной кривой.
  2. В качестве первого или второго аргументов могут быть введены константы массивов, при этом необходимо соблюдать требования к размерностям.
  3. Если аргумент известные_значения_y указан в виде ссылки на диапазон ячеек, формирующих строку или столбец, каждая строка или столбец соответственно будут интерпретированы как отдельная переменная.
  4. Если данная функция используется для расчетов с указанием только одной переменной x, первый и второй аргументы могут быть указаны в виде ссылок на диапазоны любой формы. Если по условию имеются две и более переменных x, первый и второй аргументы должны быть указаны в виде векторов данных. Размеры массивов должны совпадать в любом случае.
  5. Если требуется определить будущие значения переменных (предсказать), можно использовать функцию РОСТ.

en ru