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

Обновлено: 21.11.2024

Интерполяция – это процесс оценки точек данных в рамках существующего набора данных. Вот как выполнить интерполяцию в Excel.

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

Excel — надежный калькулятор с визуализацией данных. Таким образом, для различных математических и статистических анализов он вам всегда пригодится. Хотя прямой функции для интерполяции не существует, вы можете использовать различные функции для оценки новых точек данных из заданной таблицы данных значений X и Y.

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

1. Использование математического уравнения

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

Например, вы хотите узнать значение Y, когда X равно 15 из этой таблицы. Вот как:

    Создайте таблицу значений X1, X2, Y1 и Y2, проанализировав таблицу.

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

2. Использование метода подбора кривой

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

Чтобы определить значение Y по заданному значению X, равному 15, можно выполнить следующие действия:

  1. Выберите таблицу и вставьте точечную диаграмму.
  2. Нажмите правой кнопкой мыши линию тренда и выберите "Формат линии тренда".
  3. Установите флажки рядом с Отображать значение R-квадрата на диаграмме и Отображать уравнение на диаграмме.
  4. Также выберите линейную линию тренда.
  5. После того как на точечной диаграмме появится уравнение, подставьте в него значение X, чтобы найти значение Y.
  6. Следующая формула показывает значение Y.

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

3. Использование функций Excel: наклон и точка пересечения

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

Вот как вы можете интерполировать точки данных с помощью этого метода:

  1. Переименуйте две ячейки с помощью слов "Наклон" и "Отрезок".
  2. Примените функцию НАКЛОН под заголовком "Наклон" и выберите соответствующие данные из таблицы.
  3. Аналогичным образом примените функцию INTERCEPT и заполните значение intercept.
  4. Теперь примените формулу Y = Slope * X + Y, чтобы определить значение Y.
  5. Вы можете создать формулу Excel для приведенного выше уравнения, где вы можете просто изменить значение X, чтобы найти другие неизвестные точки данных.

Вы можете выполнять интерполяцию быстрее, если создадите ячейки для значения X, наклона и точки пересечения. Однако этот процесс может не дать точных данных для нелинейной интерполяции.

4. Использование функции Excel: ПРОГНОЗ

Это популярная функция Excel для прогнозирования эффективности путем анализа набора реальных точек данных. Вы найдете FORECAST и FORECAST.LINEAR в Excel 2016 и более поздних версиях. Для получения надежных результатов рекомендуется использовать FORECAST.LINEAR.

Функция использует три аргумента для получения прогнозируемой точки данных. Эти аргументы представляют собой заданное значение X, известные значения Y и значения X из таблицы. Посмотрите на синтаксис ниже:

Вы можете быстро определить значение Y, выполнив следующие действия:

  1. Ячейка, в которую вы хотите ввести значение Y, должна иметь знак равенства (=).
  2. Теперь введите Прогноз и выберите функцию ПРОГНОЗ.ЛИНЕЙНАЯ в раскрывающемся меню формулы.
  3. Нажмите на значение X и поставьте запятую.
  4. Затем выберите диапазон ячеек с известным значением Y и поставьте запятую.
  5. Выберите известный диапазон ячеек X и закройте формулу скобками.
  6. Нажмите Enter, чтобы получить интерполированное значение Y.
  7. Вы можете продолжать изменять значение X в диапазоне заданной таблицы, чтобы получить значения Y.
  8. Формула ПРОГНОЗА в этом руководстве выглядит следующим образом:

Функции FORECAST.LINEAR обеспечивают высокоточный прогноз данных для линейных и слегка нелинейных точек данных. Он использует первое и последнее значения для заданного значения X и учитывает весь набор данных.

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

5. Функция РОСТА для нелинейной интерполяции

Когда у вас есть таблица реальных данных в экспоненциальной форме, FORECAST.LINEAR не даст точных интерполированных точек данных. В Excel есть еще одна отличная функция для прогнозирования данных, и это РОСТ. Как и другие упомянутые выше функции, эту формулу легко применять.

Помимо знакомых аргументов, таких как известный диапазон ячеек X, известные значения Y и заданное значение X, РОСТ использует дополнительное логическое значение. Обычно вам нужно ввести единицу для этой логической константы. Вот как выглядит синтаксис:

Чтобы интерполировать данные в Excel с помощью функции РОСТ, выполните следующие действия:

  1. Вставьте функцию РОСТ в любую ячейку, где вы хотите получить интерполированное значение для Y.
  2. Введите диапазон ячеек для известных значений Y и поставьте запятую.
  3. Повторите описанный выше шаг, чтобы ввести диапазон ячеек для известных значений X.
  4. Теперь введите значение данного X и поставьте запятую.
  5. Введите 1 в качестве последнего аргумента и закройте формулу.
  6. Нажмите Enter, чтобы сгенерировать интерполированное значение Y.
  7. Вы можете использовать ссылки на ячейки вместо фактических значений, чтобы найти больше значений, просто изменив заданные данные.
  8. Окончательная формула будет выглядеть следующим образом:

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

Интерполяция данных и прогнозирование стали проще

Теперь вы можете эффективно интерполировать данные для предстоящих задач на работе или в школе.

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

Чтобы выполнить линейную интерполяцию в Excel, используйте функцию ПРОГНОЗ для прямой интерполяции между двумя парами значений x и y.

В приведенном ниже примере формула для интерполяции и поиска значения y, соответствующего значению x, равному 1,4, выглядит следующим образом:

=ПРОГНОЗ(F2,C3:C4,B3:B4)

Этот простой метод работает, когда есть только две пары значений x и y. Если пар больше 2, расчет усложняется.

Линейная интерполяция в Excel

В Excel нет функции линейной интерполяции , но функцию ПРОГНОЗ можно использовать для линейной интерполяции, когда есть только две пары значений x и y.

Он имеет следующий синтаксис:

=ПРОГНОЗ(x,известно_ys,известно_xs)

  • x — входное значение
  • known_ys – это известные значения y.
  • known_x – это известные значения x.

Функция ПРОГНОЗ использует линейную регрессию для оценки значения y, которое соответствует входному значению x. Когда есть только две точки данных, результат линейной регрессии такой же, как и линейная интерполяция.

Однако, если количество значений x и значений y больше 2, результатом функции ПРОГНОЗ НЕ будет интерполированное значение y.

Для интерполяции между значениями x и y в большом наборе данных (более 2 пар значений) используйте либо XLOOKUP, либо INDEX и MATCH, чтобы извлечь пару значений x и y для интерполяции между ними.

Линейная интерполяция предполагает, что изменение y при заданном изменении x является линейным. В большинстве случаев линейная интерполяция в Excel дает достаточно точные результаты. Однако, если вам нужна еще большая точность, вы можете рассмотреть более продвинутый метод, такой как кубические сплайны.

Интерполяция в Excel с помощью XLOOKUP

Если вы являетесь подписчиком Microsoft 365 или используете Excel 2021, Excel для Интернета или Excel для устройств iOS или Android, вы можете использовать функцию XLOOKUP для извлечения значений. Если вы используете более старую версию Excel, используйте описанный ниже метод ИНДЕКС/ПОИСКПОЗ.

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

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

В приведенной ниже таблице приведена зависимость плотности воздуха от температуры с шагом 20 градусов Цельсия.Чтобы получить данные при любой температуре от 0 до 100 C, нам придется выполнить интерполяцию.

Чтобы оценить плотность при температуре 53 градуса Цельсия, используйте функцию XLOOKUP, чтобы найти в таблице значения x1 = 40, y1 = 1,127, x2 = 60 и y2 = 1,067, а затем введите эти значения в функцию ПРОГНОЗ для выполнения интерполяции. .

Используйте XLOOKUP для поиска значений для интерполяции в Excel

XLOOKUP имеет следующий синтаксис:

=XLOOKUP(искомое_значение, искомый_массив, возвращаемый_массив, [if_not_found], [match_mode], [search_mode])

  • lookup_value – это значение, которое нужно найти.
  • lookup_array — это массив, в котором ищется искомое_значение
  • return_array – это массив, из которого возвращается результат
  • if_not_found — значение, которое будет возвращено, если ничего не найдено (необязательно)
  • match_mode сообщает функции, что делать, если точное совпадение не найдено (необязательно)
  • search_mode сообщает функции, как искать в массиве (необязательно)

Для интерполяции в Excel используйте функцию XLOOKUP, чтобы найти значения x по обе стороны от входного значения x, а также соответствующие значения y.

Чтобы найти первое значение x из таблицы данных, используйте следующую формулу:

=XLOOKUP(F3,B5:B10,B5:B10,,-1,1)

F3 — это искомое_значение, а массив B5:B10 — это и искомый_массив, и возвращаемый_массив. Если для параметра match_mode установлено значение -1, функция возвращает следующий меньший элемент в возвращаемом массиве, а если для параметра search_mode установлено значение 1, поиск начинается с начала массива.

Результирующее значение x1 равно 40, что является максимальной температурой, меньшей 53, значения x.

Очень похожая формула используется для нахождения значения x2:

=XLOOKUP(F3,B5:B10,B5:B10,,1,1)

Единственное отличие состоит в том, что для параметра match_mode установлено значение 1 (а не -1), поскольку x2 – это следующее по величине значение в возвращаемом массиве.

Результирующее значение x2 – 60 – это самая низкая температура, превышающая 53 в массиве поиска.

Далее найдите соответствующие значения y y1 и y2 с помощью XLOOKUP. Для y1 F3 — это искомое_значение, а B5:B10 — это искомый_массив, как и раньше. Чтобы вернуть значение y, которое соответствует значению x, меньшему искомого_значения, C5:C10 — это return_array, а match_mode — -1.

=XLOOKUP(F3,B5:B10,C5:C10,,-1,1)

y1 равно 1,127.

Последним значением для поиска является y2. Формула лишь немного отличается от формулы для y1. match_mode имеет значение 1, чтобы вернуть следующее наибольшее значение y:

=XLOOKUP(F3,B5:B10,C5:C10,,1,1)

Использовать ПРОГНОЗ для интерполяции Excel

Если известны 2 пары значений x и y, можно использовать FORECAST для интерполяции между ними, чтобы оценить значение y для заданного значения x по следующей формуле:

=ПРОГНОЗ(F3,F6:F7,F4:F5)

Оценка для y равна 1,088.

Предотвращение ошибок интерполяции Excel

Ошибка может возникнуть, если значение x равно одному из значений x в массиве поиска. Это приводит к тому, что x1 равен x2, а y1 равен y2. В этом случае ПРОГНОЗ не может рассчитать уклон и возвращает ошибку.

Ошибку можно предотвратить, заключив функцию ПРОГНОЗ в функцию ЕСЛИ, которая проверяет, равны ли значения x и x1.

Если x и x1 равны, y равно y1.

=ЕСЛИ(F3=F4,F6,ПРОГНОЗ(F3,F6:F7,F4:F5))

Эта формула правильно возвращает результат 1.127, если x равно 40.

Использование функций ИНДЕКС/ПОИСКПОЗ для интерполяции в Excel

Если вы не являетесь подписчиком Microsoft 365 или используете более старую версию Excel, функция XLOOKUP не включена.

Не беспокойтесь!

Для получения одинаковых результатов можно использовать функции ИНДЕКС и ПОИСКПОЗ. Синтаксис этих функций объясняется здесь, поэтому, если вам нужно освежить в памяти, обязательно ознакомьтесь с ним.

ПОИСКПОЗ возвращает расположение значения (n) в столбце или строке данных.

ИНДЕКС возвращает фактическое значение в n-й позиции строки или столбца данных.

Используя эти функции вместе, мы можем извлечь значения x1, y1, x2 и y2, необходимые для интерполяции.

Найдите значение x1 по этой формуле:

=ИНДЕКС(B5:B10,ПОИСКПОЗ(F3,B5:B10,1))

Формула возвращает 40, поскольку для аргумента типа_поиска в функции ПОИСКПОЗ задано значение 1. Это указывает функции ПОИСКПОЗ на необходимость возврата позиции из массива температур, которая меньше искомого_значения, если точное совпадение не найдено.

Найдите значение x2 по этой формуле:

=ИНДЕКС(B5:B10,ПОИСКПОЗ(F3,B5:B10,1)+1)

Формула для x2 возвращает 60, потому что результат, возвращаемый функцией ПОИСКПОЗ, увеличивается на 1, чтобы сообщить Excel о необходимости поиска следующей строки в массиве.

Для y1 и y2 формулы очень похожи. Единственное отличие состоит в том, что аргументом массива для функции ИНДЕКС является столбец C.

Для y1 формула такова:

=ИНДЕКС(C5:C10,ПОИСКПОЗ(F3,B5:B10,1))

Для y2 формула такова:

=ИНДЕКС(C5:C10,ПОИСКПОЗ(F3,B5:B10,1)+1)

Наконец, используйте функцию ПРОГНОЗ, как описано выше, чтобы вернуть оценочное значение y:

=ПРОГНОЗ(F3,F6:F7,F4:F5)

Как интерполировать в Excel с помощью формулы интерполяции

Также можно выполнять линейную интерполяцию в Excel без использования функции ПРОГНОЗ. Вместо этого используйте приведенную ниже формулу интерполяции, где y — возвращаемое значение, а x — независимая переменная:

Эту формулу можно использовать в Excel для линейной интерполяции значения y:

=F6+(F3-F4)*(F7-F6)/(F5-F4)

[Подсказка] Используйте именованные ячейки, чтобы упростить понимание формулы:

Как интерполировать график в Excel?

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

Преимущество настройки формул, как показано выше, заключается в том, что вы можете правильно интерполировать между ЛЮБОЙ парой известных значений x и y.

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

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

Функции Excel, формулы, диаграммы, форматирование, создание информационной панели Excel и др.

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

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

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

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

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

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

Excel Advanced Training (16 курсов, более 23 проектов) 16 онлайн-курсов | 23 практических проекта | 140+ часов | Поддающийся проверке сертификат об окончании | Пожизненный доступ
4,8 (10 121 оценка)

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

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

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

ПРОГНОЗ(x, известные_y, известные_x)

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

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

Итак, на снимке экрана выше мы видим, что функция ПРОГНОЗ также хорошо работает для этого.

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

В этом случае продажи являются линейными (что также можно проверить вручную или с помощью линейного графика). Теперь давайте посмотрим, как мы используем функцию ПРОГНОЗ, когда известные_y и известные_x рассчитываются с использованием функций СМЕЩ и ПОИСКПОЗ:

Давайте сначала посмотрим на синтаксис функций СМЕЩ и ПОИСКПОЗ:

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

СМЕЩЕНИЕ (ссылка, строки, столбцы, [высота], [ширина])

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

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

ПОИСКПОЗ (искомое_значение, искомый_массив, [тип_сопоставления])

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

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

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

Поэтому первая функция OFFSET, используемая в качестве второго параметра в функции ПРОГНОЗ, используется для выбора известных_y (зависимых значений, т. е. продаж).

Вторая функция СМЕЩ, используемая в качестве третьего параметра в функции ПРОГНОЗ, используется для выбора известных_x (независимых значений, т. е. количества дней).

Функция ПОИСКПОЗ, используемая в качестве параметра в функции СМЕЩ, используется для создания положения значения, которое необходимо прогнозировать, и, таким образом, для расчета количества строк. Столбцы в функции ПОИСКПОЗ, т. е. второй параметр в ней должен быть равен 0, так как зависимое значение требуется для того же выбранного столбца.

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

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

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

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

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

Многие люди хотят интерполировать данные, оцифрованные с помощью Dagra, в Microsoft Excel. К сожалению, в Excel нет функции интерполяции, но есть простой способ.

Общие сведения об интерполяции

Интерполяция – это метод оценки значения функции между двумя известными значениями. Часто некоторые отношения измеряются экспериментально или прослеживаются с помощью Dagra в диапазоне значений. Интерполяцию можно использовать для оценки функции для нетабличных точек.

Например, предположим, что у нас есть табличные данные по тепловому сопротивлению транзистора для скорости воздуха от 0 до 1800 футов в минуту с шагом 200 футов в минуту. Интерполяцию можно использовать для оценки теплового сопротивления при нетабличных значениях, таких как 485 футов в минуту.

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

Уравнения линейной интерполяции

Линейная интерполяция предполагает оценку нового значения путем соединения двух соседних известных значений прямой линией.

Если известны два значения (x1, y1) и (x2, y2), то значение y для некоторой точки x равно:

Реализация интерполяции с помощью Microsoft Excel

Приведенное выше уравнение линейной интерполяции может быть реализовано непосредственно в Microsoft Excel при условии, что табличные значения монотонны по x, то есть значения x отсортированы и никакие два не равны. Онлайновая микроволновая энциклопедия содержит полную 6-строчную реализацию вместе с хорошим объяснением того, как она работает.

Однако вот более простая реализация для Excel:
=ПРОГНОЗ(НовыйX,СМЕЩ(ИзвестныйY,ПОИСКПОЗ(НовыйX ,ИзвестныйX,1)-1,0,2), СМЕЩ(ИзвестныйX,ПОИСКПОЗ(НовыйX,ИзвестныйX ,1)-1,0,2))
Чтобы использовать это:

  1. Скопируйте приведенную выше формулу в Excel и замените KnownX и KnownY ссылкой на ячейку для табличных значений x и y, а NewX — на значение x для интерполяции, ИЛИ
  2. Определите имена для диапазонов KnownX и KnownY ( Insert→Name→Define… в Excel 2003) и замените NewX на x- значение для интерполяции.

Вы можете загрузить пример линейной интерполяции Excel.

Рабочий пример линейной интерполяции в Microsoft Excel.

Автор:Пол Мартинсен
Платформы:Windows 8
Категория:Примеры Dagra
Дата:26 марта 2017 г.

Это уравнение работает точно так же, как прямая реализация уравнения линейной интерполяции. Основное отличие состоит в том, что для описанного здесь простого подхода требуются только две функции поиска, в то время как для прямой реализации требуется 6 (по одной для каждого члена уравнения). Это делает его значительно быстрее.

Как работает реализация Excel

Простую реализацию проще всего понять, анализируя снаружи и работая внутри. Вот полное уравнение:
=ПРОГНОЗ(НовыйX,СМЕЩ(ИзвестныйY ,ПОИСКПОЗ(НовыйX,ИзвестныйX,1)-1,0,2), СМЕЩ(ИзвестныйX,ПОИСКПОЗ(НовыйX,ИзвестноX,1)-1,0,2))
Вкратце уравнение состоит из 3-х частей:

  1. функция ПРОГНОЗ для расчета линейной интерполяции,
  2. два вызова функции ПОИСКПОЗ, чтобы найти ближайшее табличное значение x, но меньшее, чем новое значение x, и
  3. два вызова функции OFFSET для ссылки на табличные значения x и y чуть выше и чуть ниже нового значения x.

Более подробно, функция ПРОГНОЗ выполняет фактическую интерполяцию, используя приведенное выше уравнение линейной интерполяции. Его синтаксис: ПРОГНОЗ(НовыйX, известная_пара_y, известная_пара_x) .

Первый параметр, NewX, — это просто интерполируемое значение. Следующие два параметра, known_y_pair и known_x_pair, являются значениями по обе стороны от NewX . То есть и на диаграмме выше.

Функция ПОИСКПОЗ используется для поиска табличного значения x чуть ниже NewX . Его синтаксис: ПОИСКПОЗ(искомое_значение, искомая_таблица,тип_сопоставления) . ПОИСКПОЗ возвращает относительное положение элемента в отсортированном массиве. Таким образом, lookup_value — это интерполируемое значение, lookup_table — это массив значений KnownX, а match_type — это значение от 1 до найти наибольшее значение в массиве, которое меньше или равно NewX .

Функция ПОИСКПОЗ возвращает индекс, но для функции ПРОГНОЗ требуются два диапазона ячеек: один для известной_x_пары и один для известной_пары_у . Таким образом, функция OFFSET используется дважды для создания этих диапазонов. Его синтаксис: OFFSET(ссылка,смещение_строки, смещение_столбца, счетчик_строки,счетчик_столбца) . Он принимает начальную точку reference и создает ссылку на ячейку с заданным смещением и размером. Чтобы получить диапазон известная_пара_y, ссылка устанавливается на таблицу значений KnownY; для диапазона known_x_pair параметру reference присваивается массив значений KnownX. Если табличные значения расположены вертикально, row_offset является результатом функции ПОИСКПОЗ меньше 1, а row_count равен 2; column_offset равно 0, а column_count равно 1. Это дает нам ссылку на массив ячеек высотой 2 ячейки и шириной 1 ячейка. Если табличные значения расположены горизонтально, строка и столбец меняются местами в функции OFFSET.

Читайте также: