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

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

Формулы массива в Excel

Под массивом обычно понимают набор данных, объединенных в группу. Массивы бывают одномерные (элементы массива образуют строку или столбец) или двумерные (матрица). Легко сообразить, что почти в любой таблице Excel при желании можно найти один или несколько таких массивов:

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

Пример 1. Классика жанра — товарный чек

Задача: рассчитать общую сумму заказа. Если идти классическим путем, то нужно будет добавить столбец, где перемножить цену и количество, а потом взять сумму по этому столбцу. Если же применить формулу массива, то все будет гораздо красивее:

выделяем ячейку С7
вводим с клавиатуры =СУММ(
выделяем диапазон B2:B5
вводим знак умножения (звездочка)
выделяем диапазон C2:C5 и закрываем скобку функции СУММ — в итоге должно получиться так:

Т.е. Excel произвел попарное умножение элементов массивов B2:B5 и C2:C5 и образовал новый массив стоимостей (в памяти компьютера), а затем сложил все элементы этого нового массива.

Обратите внимание на фигурные скобки, появившиеся в формуле — отличительный признак формулы массива. Вводить их вручную с клавиатуры бесполезно — они автоматически появляются при нажатии Ctrl + Shift + Enter.

Пример 2. Разрешите Вас. транспонировать?

При работе с таблицами часто возникает необходимость поменять местами строки и столбцы, т.е. развернуть таблицу на бок, чтобы данные, которые раньше шли по строке, теперь располагались в столбцах и наоборот. В математике такая операция называется транспонированием. При помощи формулы массива и функции ТРАНСП (TRANSPOSE) это делается на раз.

Допустим, имеем двумерный массив ячеек, который хотим транспонировать.

Выделяем диапазон ячеек для размещения транспонированной таблицы. Поскольку исходный массив ячеек был 8 строк на 2 столбца, то надо выделить диапазон пустых ячеек размером 2 строки на 8 столбцов.
вводим функцию транспонирования =ТРАНСП(
в качестве аргумента функции выделяем наш массив ячеек A1:B8

жмем Ctrl + Shift + Enter и получаем "перевернутый массив" в качестве результата:

Редактирование формулы массива

Если формула массива расположена не в одной ячейке (как в Примере 1), а в нескольких ячейках (как в Примере 2), то Excel не позволит редактировать или удалить одну отдельно взятую формулу (например в ячейке D10) и выдаст предупреждающее сообщение Невозможно изменить часть массива.

Для редактирования формулы массива необходимо выделить весь диапазон (A10:H11 в нашем случае) и изменить формулу в строке формул (или нажав F2). Затем необходимо повторить ввод измененной формулы массива, нажав сочетание клавиш Ctrl + Shift + Enter.

Excel также не позволит свободно перемещать ячейки, входящие в формулу массива или добавлять новые строки-столбцы-ячейки в диапазон формулы массива (т.е. в диапазон A10:H11 в нашем случае)

Пример 3. Таблица умножения

Вспомните детство, школу, свою тетрадку по математике. На обороте тетради на обложке было что? Таблица умножения вот такого вида:

При помощи формул массива она вся делается в одно движение:

выделяем диапазон B2:K11
вводим формулу =A2:A11*B1:K1
жмем Ctrl + Shift + Enter, чтобы Excel воспринял ее как формулу массива

и получаем результат:

Пример 4. Выборочное суммирование

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

В данном случае формула массива синхронно пробегает по всем элементам диапазонов C3:C21 и B3:B21, проверяя, совпадают ли они с заданными значениями из ячеек G4 и G5. Если совпадения нет, то результат равенства ноль, если совпадение есть, то единица. Таким образом суммы всех сделок, где заказчик не ANTON и товар не Boston Crab Meat умножаются на ноль и суммируются только нужные заказы.

Интерполировать в 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: специфические особенности, порядок действий и примеры