Применение интерполяции в Microsoft Excel

Главное условие, при котором можно применять интерполяцию – это то, что искомое значение должно быть внутри массива данных, а не выходить за его предел. Например, если мы имеем набор аргументов 15, 21 и 29, то при нахождении функции для аргумента 25 мы можем использовать интерполяцию. А для поиска соответствующего значения для аргумента 30 – уже нет. В этом и является главное отличие этой процедуры от экстраполяции.

Прежде всего, рассмотрим применения интерполяции для данных, которые расположены в таблице. Для примера возьмем массив аргументов и соответствующих им значений функции, соотношение которых можно описать линейным уравнением. Эти данные размещены в таблице ниже. Нам нужно найти соответствующую функцию для аргумента 28. Сделать это проще всего с помощью оператора ПРЕДСКАЗ.

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

• X;
• Известные значения y;
• Известные значения x.

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

В поле «Известные значения y» нужно указать координаты диапазона таблицы, в котором содержатся значения функции. Это можно сделать вручную, но гораздо проще и удобнее установить курсор в поле и выделить соответствующую область на листе.

Аналогичным образом устанавливаем в поле «Известные значения x» координаты диапазона с аргументами.

Интерполяция данных: соединяем точки так, чтобы было красиво

Как построить график по n точкам? Самое простое — отметить их маркерами на координатной сетке. Однако для наглядности их хочется соединить, чтобы получить легко читаемую линию. Соединять точки проще всего отрезками прямых. Но график-ломаная читается довольно тяжело: взгляд цепляется за углы, а не скользит вдоль линии. Да и выглядят изломы не очень красиво. Получается, что кроме ломаных нужно уметь строить и кривые. Однако тут нужно быть осторожным, чтобы не получилось вот такого:

Немного матчасти

Восстановление промежуточных значений функции, которая в данном случае задана таблично в виде точек P₁&nbsp. &nbspP_n, называется интерполяцией. Есть множество способов интерполяции, но все они могут быть сведены к тому, что надо найти n&nbsp–&nbsp1 функцию для расчёта промежуточных точек на соответствующих сегментах. При этом заданные точки обязательно должны быть вычислимы через соответствующие функции. На основе этого и может быть построен график:

Функции f_i могут быть самыми разными, но чаще всего используют полиномы некоторой степени. В этом случае итоговая интерполирующая функция (кусочно заданная на промежутках, ограниченных точками P_i) называется сплайном.

В разных инструментах для построения графиков — редакторах и библиотеках — задача «красивой интерполяции» решена по-разному. В конце статьи будет небольшой обзор существующих вариантов. Почему в конце? Чтобы после ряда приведённых выкладок и размышлений можно было поугадывать, кто из «серьёзных ребят» какие методы использует.

Ставим опыты

Самый простой пример — линейная интерполяция, в которой используются полиномы первой степени, а в итоге получается ломаная, соединяющая заданные точки.
Давайте добавим немного конкретики. Вот набор точек (взяты почти с потолка):

Результат линейной интерполяции этих точек выглядит так:

Однако, как отмечалось выше, иногда хочется получить в итоге гладкую кривую.

Что есть гладкость? Бытовой ответ: отсутствие острых углов. Математический: непрерывность производных. При этом в математике гладкость имеет порядок, равный номеру последней непрерывной производной, и область, на которой эта непрерывность сохраняется. То есть, если функция имеет гладкость порядка 1 на отрезке [a;&nbspb], это означает, что на [a;&nbspb] она имеет непрерывную первую производную, а вот вторая производная уже терпит разрыв в каких-то точках.
У сплайна в контексте гладкости есть понятие дефекта. Дефект сплайна — это разность между его степенью и его гладкостью. Степень сплайна — это максимальная степень использованных в нём полиномов.
Важно отметить, что «опасными» точками у сплайна (в которых может нарушиться гладкость) являются как раз P_i, то есть точки сочленения сегментов, в которых происходит переход от одного полинома к другому. Все остальные точки «безопасны», ведь у полинома на области его определения нет проблем с непрерывностью производных.
Чтобы добиться гладкой интерполяции, нужно повысить степень полиномов и подобрать их коэффициенты так, чтобы в граничных точках сохранялась непрерывность производных.

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

Кривая, действительно, гладкая. Но если предположить, что это график некоторого процесса или явления, который нужно показать заинтересованному лицу, то такой метод, скорее всего, не подходит. Проблема в ложных экстремумах. Появились они из-за слишком сильного искривления, которое было призвано обеспечить гладкость интерполяционной функции. Но зрителю такое поведение совсем не кстати, ведь он оказывается обманут относительно пиковых значений функции. А ради наглядной визуализации этих значений, собственно, всё и затевалось.
Так что надо искать другие решения.

Другое традиционное решение, кроме кубических сплайнов дефекта 1 — полиномы Лагранжа. Это полиномы степени n&nbsp–&nbsp1, принимающие заданные значения в заданных точках. То есть членения на сегменты здесь не происходит, вся последовательность описывается одним полиномом.
Но вот что получается:

Гладкость, конечно, присутствует, но наглядность пострадала так сильно, что… пожалуй, стоит поискать другие методы. На некоторых наборах данных результат выходит нормальный, но в общем случае ошибка относительно линейной интерполяции (и, соответственно, ложные экстремумы) может получаться слишком большой — из-за того, что тут всего один полином на все сегменты.

В компьютерной графике очень широко применяются кривые Безье, представленные полиномами k-й степени.
Они не являются интерполирующими, так как из k&nbsp+&nbsp1 точек, участвующих в построении, итоговая кривая проходит лишь через первую и последнюю. Остальные k&nbsp–&nbsp1 точек играют роль своего рода «гравитационных центров», притягивающих к себе кривую.
Вот пример кубической кривой Безье:

Как это можно использовать для интерполяции? На основе этих кривых тоже можно построить сплайн. То есть на каждом сегменте сплайна будет своя кривая Безье k-й степени (кстати, k&nbsp=&nbsp1 даёт линейную интерполяцию). И вопрос только в том, какое k взять и как найти k&nbsp–&nbsp1 промежуточную точку.
Здесь бесконечно много вариантов (поскольку k ничем не ограничено), однако мы рассмотрим классический: k&nbsp=&nbsp3.
Чтобы итоговая кривая была гладкой, нужно добиться дефекта 1 для составляемого сплайна, то есть сохранения непрерывности первой и второй производных в точках сочленения сегментов (P_i), как это делается в классическом варианте кубического сплайна.
Решение этой задачи подробно (с исходным кодом) рассмотрено здесь.
Вот что получится на нашем тестовом наборе:

Стало лучше: ложные экстремумы всё ещё есть, но хотя бы не так сильно отличаются от реальных.

Думаем и экспериментируем

Можно попробовать ослабить условие гладкости: потребовать дефект 2, а не 1, то есть сохранить непрерывность одной только первой производной.
Достаточное условие достижения дефекта 2 в том, что промежуточные контрольные точки кубической кривой Безье, смежные с заданной точкой интерполируемой последовательности, лежат с этой точкой на одной прямой и на одинаковом расстоянии:

В качестве прямых, на которых лежат точки C_{i&nbsp–&nbsp1} (2) , P_i и C_i (1) , целесообразно взять касательные к графику интерполируемой функции в точках P_i. Это гарантирует отсутствие ложных экстремумов, так как кривая Безье оказывается ограниченной ломаной, построенной на её контрольных точках (если эта ломаная не имеет самопересечений).

Методом проб и ошибок эвристика для расчёта расстояния от точки интерполируемой последовательности до промежуточной контрольной получилась такой:

Первая и последняя промежуточные контрольные точки равны первой и последней точке графика соответственно (точки C₁ (1) и C_{n&nbsp–&nbsp1} (2) совпадают с точками P₁ и P_n соответственно).
В этом случае получается вот такая кривая:

Как видно, ложных экстремумов уже нет. Однако если сравнивать с линейной интерполяцией, местами ошибка очень большая. Можно сделать её ещё меньше, но тут в ход пойдут ещё более хитрые эвристики.

К текущему варианту мы пришли, уменьшив гладкость на один порядок. Можно сделать это ещё раз: пусть сплайн будет иметь дефект 3. По факту, тем самым формально функция не будет гладкой вообще: даже первая производная может терпеть разрывы. Но если рвать её аккуратно, визуально ничего страшного не произойдёт.
Отказываемся от требования равенства расстояний от точки P_i до точек C_{i&nbsp–&nbsp1} (2) и C_i (1) , но при этом сохраняем их все лежащими на одной прямой:

Эвристика для вычисления расстояний будет такой:

Результат получается такой:

В результате на шестом сегменте ошибка уменьшилась, а на седьмом — увеличилась: кривизна у Безье на нём оказалась больше, чем хотелось бы. Исправить ситуацию можно, принудительно уменьшив кривизну и тем самым «прижав» Безье ближе к отрезку прямой, которая соединяет граничные точки сегмента. Для этого используется следующая эвристика:

Результат следующий:

На этом было принято решение признать цель достигнутой.
Может быть, кому-то пригодится код.

А как люди-то делают?

Обещанный обзор. Конечно, перед решением задачи мы посмотрели, кто чем может похвастаться, а уже потом начали разбираться, как сделать самим и по возможности лучше. Но вот как только сделали, не без удовольствия ещё раз прошлись по доступным инструментам и сравнили их результаты с плодами наших экспериментов. Итак, поехали.

MS Excel

Это очень похоже на рассмотренный выше сплайн дефекта 1, основанный на кривых Безье. Правда, в отличие от него в чистом виде, тут всего два ложных экстремума — первый и второй сегменты (у нас было четыре). Видимо, к классическому поиску промежуточных контрольных точек тут добавляются ещё какие-то эвристики. Но ото всех ложных экстремумов они не спасли.

LibreOffice Calc

В настройках это названо кубическим сплайном. Очевидно, он тоже основан на Безье, и вот тут уже точная копия нашего результата: все четыре ложных экстремума на месте.

Есть там ещё один тип интерполяции, который мы тут не рассматривали: B-сплайн. Но для нашей задачи он явно не подходит, так как даёт вот такой результат 🙂

Highcharts, одна из самых популярных JS-библиотек для построения диаграмм

Тут налицо «метод касательных» в варианте равенства расстояний от точки интерполируемой последовательности до промежуточных контрольных. Ложных экстремумов нет, зато есть сравнительно большая ошибка относительно линейной интерполяции (седьмой сегмент).

amCharts, ещё одна популярная JS-библиотека

Картина очень похожа на экселевскую, те же два ложных экстремума в тех же местах.

Coreplot, самая популярная библиотека построения графиков для iOS и OS X

Есть ложные экстремумы и видно, что используется сплайн дефекта 1 на основе Безье.
Библиотека открытая, так что можно посмотреть в код и убедиться в этом.

aChartEngine, вроде как самая популярная библиотека построения графиков для Android

Больше всего похоже на кривую Безье степени n&nbsp–&nbsp1, хотя в самой библиотеке график называется «cubic line». Странно! Как бы то ни было, тут не только присутствуют ложные экстремумы, но и в принципе не выполняются условия интерполяции.

Интерполировать в Excel

Интерполяция — это метод, который используется для оценки или определения значения между двумя известными значениями на линии или кривой. Этот вид прогнозирования используется во многих видах анализа, таких как инвестиции в рост, прогнозирование чисел, установление стратегий, страховые решения, движения цен, акции и рынки акций и т. Д.

Линейная интерполяция означает оценку будущего значения определенной переменной на основе текущих данных. В MS-Excel создается прямая линия, которая соединяет два известных значения, и, таким образом, будущее значение рассчитывается с использованием простой математической формулы или функции FORECAST.

Примеры для интерполяции в Excel

Давайте разберемся, как интерполировать в Excel с некоторыми примерами.

Вы можете скачать этот шаблон Интерполировать Excel здесь — Шаблон Интерполировать Excel

Пример № 1 — Использование простой математической формулы

Допустим, у нас есть простой набор данных из двух известных значений x и y, и мы хотим интерполировать значение (т.е. найти соответствующее значение y для значения x) следующим образом:

Итак, простая формула, которая используется для интерполяции этого значения:

Поэтому, когда мы применяем эту формулу к данному набору данных, мы получаем интерполированное значение y как:

Таким образом, мы можем видеть на скриншоте выше, что мы интерполировали значение с двумя известными значениями x и y. Могут быть моменты, когда становится трудно запомнить формулу. Таким образом, функция ПРОГНОЗ может быть использована в таких случаях.

Пример №2 — Использование функции FORECAST

Теперь допустим, что мы хотим интерполировать то же значение в примере 1 с помощью функции FORECAST.

Функция ПРОГНОЗ оценивает значение на основе существующих значений вместе с линейным трендом. Он имеет следующий синтаксис:

ПРОГНОЗ (x, known_y's, known_x's)

• x: Это значение или точка данных, соответствующее значение которой мы хотим интерполировать или предсказать.
• known_y's: это известный диапазон значений y.
• known_x's: это известный диапазон значений x.

Итак, давайте теперь посмотрим на скриншот ниже, что происходит, когда мы применяем эту функцию FORECAST для интерполяции заданного значения x:

Таким образом, мы можем видеть на скриншоте выше, что функция FORECAST также хорошо работает для этого.

Пример № 3 — Использование функции прогноза

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

В этом случае продажи являются линейными (что также можно проверить вручную или с помощью линейного графика). Теперь давайте посмотрим, как мы используем функцию FORECAST, когда known_y's и known_x's вычисляются с использованием функций OFFSET и MATCH:

Давайте сначала посмотрим синтаксис функции OFFSET и функции MATCH:

Функция OFFSET возвращает ячейку или диапазон ячеек с указанным количеством строк и столбцов, в зависимости от высоты и ширины в указанных строках и столбцах. Он имеет следующий синтаксис:

OFFSET (ссылка, строки, столбцы, (высота), (ширина))

• ссылка: это отправная точка, откуда начинается отсчет строк и столбцов.
• row: это число строк, смещаемых ниже начальной ячейки ссылки.
• cols: это число столбцов, которые должны быть смещены вправо от начальной ссылочной ячейки.
• высота: опционально; Из возвращенной ссылки это высота строк.
• ширина: необязательно; Из возвращенной ссылки это ширина столбцов.

Функция MATCH возвращает относительное положение искомого значения в строке, столбце или таблице, которое соответствует указанному значению в указанном порядке. Он имеет следующий синтаксис:

MATCH (lookup_value, lookup_array, (match_type))

• lookup_value: это значение, которое необходимо сопоставить или просмотреть из lookup_array.
• lookup_array: это массив или диапазон ячеек, в которых нужно искать lookup_value.
• match_type: необязательно; это может принимать значения 1, 0, -1.

Значение по умолчанию для этого match_type равно 1. Для значения 1 функция MATCH найдет наибольшее значение, которое меньше или равно lookup_value, и значение должно быть в порядке возрастания. Для значения 0 функция MATCH находит первое значение, которое точно равно lookup_value. Для значения -1 функция найдет наименьшее значение, которое больше или равно lookup_value, и значение должно быть в порядке убывания.

Теперь, если мы хотим оценить продажи, скажем, на 28 дней, мы используем эти функции следующим образом:

Таким образом, первая функция OFFSET, используемая в качестве второго параметра в функции FORECAST, используется для выбора known_y's (зависимых значений, то есть продаж).

Вторая функция OFFSET, используемая в качестве третьего параметра в функции FORECAST, используется для выбора известных_х (независимых значений, то есть количества дней).

Функция MATCH, используемая в качестве параметра в функции OFFSET, используется для генерации позиции значения, которая должна быть спрогнозирована, и, таким образом, для вычисления количества строк. Столбцы в функции MATCH, т. Е. Второй параметр в ней должен быть 0, так как зависимое значение требуется для того же выбранного столбца.

Таким образом, в течение 28 дней мы оценили или прогнозировали продажи фирмы как 1120. Аналогично, мы можем оценить продажи фирмы за другое количество дней, используя эту функцию ПРОГНОЗ.

Что нужно помнить о интерполяции в Excel

• Процесс извлечения простой функции из набора данных дискретных значений, так что функция проходит через все заданные значения и, таким образом, может использоваться для прогнозирования значений между заданными значениями, называется интерполяцией.
• Он используется для определения того, какие данные могут существовать вне собранных данных.
• Линейная интерполяция не является точным методом в MS Excel, однако, это экономит время и быстро.
• Линейная интерполяция может даже использоваться для прогнозирования значений осадков, географических точек данных и т. Д.
• Если данные не являются линейными, то для таких интерполяций в таких случаях могут использоваться другие методы: полиномиальная интерполяция, сплайн-интерполяция и т. Д.
• Функция FORECAST может даже использоваться для экстраполяции или прогнозирования будущих значений.

Рекомендуемые статьи

Это руководство по интерполяции в Excel. Здесь мы обсуждаем, как интерполировать в Excel вместе с практическими примерами и загружаемым шаблоном Excel. Вы также можете просмотреть наши другие предлагаемые статьи —

Графический метод: подготовка

Интерполяция в Excel, пример которой представлен выше, далеко не единственный способ, позволяющий выяснить промежуточные неизвестные значения функции Y(X) по дискретному набору уже известных. В частности, может быть применен графический метод. Он может оказаться полезным, если в таблице к одному из аргументов не указано соответствующее значение функции, как в той, что представлена ниже (см. ячейку с адресом B9).

Интерполяция в Excel в таком случае начинается с построения графика. Для этого:

• во вкладке «Вставка» выделяют табличный диапазон;
• в блоке инструментов «Диаграммы» выбирают значок «График»;
• в появившемся списке выбирают тот, который лучше подходит для решения конкретной задачи.

Так как в ячейке B9 пусто, график получился разорванный. Кроме того, на нем присутствует дополнительная линия X, в которой нет необходимости, а на горизонтальной оси вместо значений аргумента указаны пункты по порядку.

Как рассчитать значения полинома в Excel?

Есть 3 способа расчета значений полинома в Excel:

• 1-й способ с помощью графика;
• 2-й способ с помощью функции Excel =ЛИНЕЙН;
• 3-й способ с помощью Forecast4AC PRO;

1-й способ расчета полинома — с помощью графика

Выделяем ряд со значениями и строим график временного ряда.

На график добавляем полином 6-й степени.

Затем в формате линии тренда ставим галочку «показать уравнение на диаграмме»

После этого уравнение выводится на график y = 3,7066x 6 — 234,94x 5 + 4973,6x 4 — 35930x 3 — 7576,8x 2 + 645515x + 5E+06 . Для того чтобы последний коэффициент сделать читаемым, мы зажимаем левую кнопку мыши и выделяем уравнение полинома

Нажимаем правой кнопкой и выбираем «формат подписи линии тренда»

В настройках подписи линии тренда выбираем число и в числовых форматах выбираем «Числовой».

Получаем уравнение полинома в читаемом формате:

y = 3,71x 6 — 234,94x 5 + 4 973,59x 4 — 35 929,91x 3 — 7 576,79x 2 + 645 514,77x + 4 693 169,35

Из этого уравнения берем коэффициенты a, b, c, d, g, m, v, и вводим в соответствующие ячейки Excel

Каждому периоду во временном ряду присваиваем порядковый номер, который будем подставлять в уравнение вместо X.

Рассчитаем значения полинома для каждого периода. Для этого вводим формулу полинома y = 3,71x 6 — 234,94x 5 + 4 973,59x 4 — 35 929,91x 3 — 7 576,79x 2 + 645 514,77x + 4 693 169,35 в первую ячейку и фиксируем ссылки на коэффициенты тренда (см. статью как зафиксировать ссылки)

Получаем формулу следующего вида:

= R2C8 *RC[-3]^6+ R3C8 *RC[-3]^5+ R4C8 *RC[-3]^4+ R5C8 *RC[-3]^3+ R6C8 *RC[-3]^2+ R7C8 *RC[-3]+ R8C8

в которой коэффициенты тренда зафиксированы и вместо «x» мы подставляем ссылку на номер текущего временного ряда (для первого значение 1, для второго 2 и т.д.)

Также «X» возводим в соответствующую степень (значок в Excel «^» означает возведение в степень)

=R2C8*RC[-3] ^6 +R3C8*RC[-3] ^5 +R4C8*RC[-3] ^4 +R5C8*RC[-3] ^3 +R6C8*RC[-3] ^2 +R7C8*RC[-3]+R8C8

Теперь протягиваем формулу до конца временного ряда и получаем рассчитанные значения полиномиального тренда для каждого периода.

2-й способ расчета полинома в Excel — функция ЛИНЕЙН()

Рассчитаем коэффициенты линейного тренда с помощью стандартной функции Excel =ЛИНЕЙН()

Для расчета коэффициентов в формулу =ЛИНЕЙН(известные значения y, известные значения x, константа, статистика) вводим:

• «известные значения y» (объёмы продаж за периоды),
• «известные значения x» (порядковый номер временного ряда),
• в константу ставим «1»,
• в статистику «0»

Получаем следующего вида формулу:

Теперь, чтобы формула Линейн() рассчитала коэффициенты полинома, нам в неё надо дописать степень полинома, коэффициенты которого мы хотим рассчитать.

Для этого в часть формулы с «известными значениями x» вписываем степень полинома:

• ^ <1:2:3:4:5:6>— для расчета коэффициентов полинома 6-й степени
• ^ <1:2:3:4:5>— для расчета коэффициентов полинома 5-й степени
• ^ <1_2>— для расчета коэффициентов полинома 2-й степени

Получаем формулу следующего вида:

Вводим формулу в ячейку, получаем 3,71 —- значение (a) для полинома 6-й степени y=ax^6+bx^5+cx^4+dx^3+gx^2+mx+v

Для того, чтобы Excel рассчитал все 7 коэффициентов полинома 6-й степени y=ax^6+bx^5+cx^4+dx^3+gx^2+mx+v, необходимо:

1. Установить курсор в ячейку с формулой и выделить 7 соседних ячеек справа, как на рисунке:

2. Нажать на клавишу F2

3. Затем одновременно — клавиши CTRL + SHIFT + ВВОД (т.е. ввести формулу массива, как это сделать читайте подробно в статье «Как ввести формулу массива»)

Получаем 7 коэффициентов полиномиального тренда 6-й степени.

Рассчитаем значения полиномиального тренда с помощью полученных коэффициентов. Подставляем в уравнение y=3,7* x ^ 6 -234,9* x ^ 5 +4973,5* x ^ 4 -35929,9 * x^3 -7576,7 * x^2 +645514,7* x +4693169,3 номера периодов X, для которых хотим рассчитать значения полинома.

Каждому периоду во временном ряду присваиваем порядковый номер, который будем подставлять в уравнение полинома вместо X.

Рассчитаем значения полиномиального тренда для каждого периода. Для этого вводим формулу полинома в первую ячейку и фиксируем ссылки на коэффициенты тренда (см. статью как зафиксировать ссылки)

Получаем формулу следующего вида:

= R2C8 *RC[-3]^6+ R3C8 *RC[-3]^5+ R4C8 *RC[-3]^4+ R5C8 *RC[-3]^3+ R6C8 *RC[-3]^2+ R7C8 *RC[-3]+ R8C8

в которой коэффициенты тренда зафиксированы и вместо «x» мы подставляем ссылку на номер текущего временного ряда (для первого значение 1, для второго 2 и т.д.)

Также «X» возводим в соответствующую степень (значок в Excel «^» означает возведение в степень)

=R2C8*RC[-3] ^6 +R3C8*RC[-3] ^5 +R4C8*RC[-3] ^4 +R5C8*RC[-3] ^3 +R6C8*RC[-3] ^2 +R7C8*RC[-3]+R8C8

Теперь протягиваем формулу до конца временного ряда и получаем рассчитанные значения полиномиального тренда для каждого периода.

2-й способ точнее, чем первый, т.к. коэффициенты тренда мы получаем без округления, а также этот расчет быстрее.

3-й способ расчета значений полиномиальных трендов — Forecast4AC PRO

Устанавливаем курсор в начало временного ряда

Заходим в настройки Forecast4AC PRO, выбираем «Прогноз с ростом и сезонностью», «Полином 6-й степени», нажимаем кнопку «Рассчитать».

Заходим в лист с пошаговым расчетом «ForPol6», находим строку «Сложившийся тренд»:

Копируем значения в наш лист.

Получаем значения полинома 6-й степени, рассчитанные 3 способами с помощью:

1. Коэффициентов полиномиального тренда выведенных на график;
2. Коэффициентов полинома рассчитанных с помощью функцию Excel =ЛИНЕЙН
3. и с помощью Forecast4AC PRO одним нажатием клавиши, легко и быстро.

Присоединяйтесь к нам!

Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа:

• Novo Forecast Lite — автоматический расчет прогноза в Excel .
• 4analytics — ABC-XYZ-анализ и анализ выбросов в Excel.
• Qlik Sense Desktop и QlikView Personal Edition — BI-системы для анализа и визуализации данных.

Тестируйте возможности платных решений:

• Novo Forecast PRO — прогнозирование в Excel для больших массивов данных.

Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.

Использование специальной функции НД

Теперь, когда вы знаете, как сделать интерполяцию в Excel графическим методом или посредством оператора «ПРЕДСКАЗ», решение многих практических задач для вас не составит большого труда. Однако это еще не все. Табличный процессор от Microsoft представляет возможность найти неизвестное значение функции с помощью функции НД.

Предположим, что график уже построен, на нем уже установлены корректные подписи шкалы. Попробуем ликвидировать разрыв. Для этого:

• выделяют в таблице ячейку, в которой отсутствует значение функции;
• выбирают значок «Вставить функцию»;
• в «Мастере функций» в окошке «Категории» находят строку «Полный алфавитный перечень» (в некоторых версиях процессора «Проверка свойств и значений»);
• нажимают на запись «НД» и жмут на кнопку «OK».

После этого в ячейке B9 появляется значение ошибки «#Н/Д». Однако обрыв графика автоматически устраняется.

Вы можете поступить даже проще: внести с клавиатуры в ячейку B9 символы «#Н/Д» (без кавычек).

График, построенный по результатам интерполяции, сглажен. Для этого после выделения графика в окне "Формат ряда данных" на вкладке "Вид" включаем флажок "Сглаженная линия".Литература

1. Мудров А.Е. Численные методы для ПЭВМ на языках Бейсик, Фортран и Паскаль. – Томск: МП “РАСКО”, 1991. – 272 с.: ил.

2. Калиткин Н. Н. Численные методы. – М.: Наука, 1978. – 512 с.

3. Любимов Е.Б. и др. Решение систем линейных алгебраических уравнений средствами программы Microsoft Excel. Методические указания. СПбГАСУ, — СПб., 2005. — 16 с.

4. Заварыкин В.М. и др. Численные методы. — М.: Просвещение, 1990. -176 с.