Как интерполировать промежуточные значения для произвольных данных в Excel

Question

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

A    B
--   ---
1    2.9
2    5.06
3    7
4    8.84
5    10.87
6    13.24
7    16.22
8    20.25
9    36.7

Выше представлены девять измерений нелинейно возрастающей физической переменной в B, например, напряжения, а A представляет собой ровно каждое из девяти минут, в течение которых было выполнено измерение.

Я хочу создать вторую таблицу, столбцы E и F, с количеством строк, которое является «следующим целым числом» для самого высокого значения в столбце B. В этом случае B9=36.7, таким образом, будет 37 строк. Столбец F1:F37 будет содержать целые числа от 1 до 37, столбец E должен иметь числовые значения, которые соответствуют F, в том же отношении, что и между столбцами A и B. Другими словами, интерполировать значения столбца E, соответствующие значениям столбца F.

Например, A3 = 3 и B3 = 7. В этом случае F7 = 7 и E7 = 3, поскольку B уже включает целое число 7 и имеет соответствующее значение в столбце A. Однако F8 = 8, что является промежуточным значением, не содержащимся в столбце B. Таким образом, E8 будет находиться между 3 и 4, на основе исходных данных, и его необходимо интерполировать.

Идея в том, что при построении графика A1:B9 будет иметь ту же форму, что и E1:F37. В этом примере я расширю таблицу данных до 37 целочисленных результатов, которые могли бы возникнуть в ходе исходных измерений, и посмотрю, в какое время (в столбце E, с десятичными знаками) эти значения могли бы возникнуть.

Что я пробовал

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

1. Я создал столбец (K), содержащий разницу между элементами столбца B. K5 = B5-B4. Это смещение Y для каждого приращения X.
2. Столбец E будет содержать столько последовательных целых чисел (37), начиная с 1, каково следующее целое значение наибольшего элемента в B. В этом случае B9 содержит 36,7, то есть 37.
3. На F1:F37 я ввожу следующую формулу.

Ячейка F1 содержит:

=IF(E1>$B$9,$A$9+(E1-$B$9)/$K$9,IF(E1>$B$8,$A$8+(E1-$B$8)
    /$K$9,IF(E1>$B$7,$A$7+(‌​E1-$B$7)/$K$8,IF(E1>$B$6,$A$6+(E1-$B$6)
    /$K$7,IF(E1>$B$5,$A$5+(E1-$B$5)/$K$6,IF(E1‌​>$B$4,$A$4+
    (E1-$B$4)/$K$5,IF(E1>$B$3,$A$3+(E1-$B$3)/$K$4,IF(E1>$B$2,$A$2+
    (E1-$B$2‌​)/$K$3,IF(E1>$B$1,$A$1+(E1-$B$1)/$K$2,E1/$K$1)))))))))  

Работает довольно хорошо. Но это не автоматизированная формула; нужно ввести столько же "ЕСЛИ", сколько элементов в столбцах A+B (X+Y). Я протестировал диаграммы рассеивания с линиями из A1:B9 и E1:F37 (перевернутыми для правильной последовательности X/Y), и они сгенерировали точно такую ​​же форму кривой, так что это работает.

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

Answer 1

Короткий ответ

Интерполяция основана на уравнении, связывающем значения X и Y. Если вы знаете фактическое уравнение, вы можете напрямую вычислить любые промежуточные значения, которые вам нужны. Если вы не знаете, вы интерполируете с помощью аппроксимации. Качество аппроксимации определяет, насколько точными будут ваши промежуточные значения. Линейная интерполяция будет грубой, если вы аппроксимируете кривую с ограниченным количеством точек. Существует несколько других подходов, которые дадут вам лучшие результаты, и встроенные инструменты анализа, которые выполнят большую часть работы.

Длинный ответ

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

Уравнение

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

В вашем другом вопросе вы использовали данные, основанные на уравнении Y=2^X. Когда у вас есть фактическое уравнение, вы можете точно интерполировать. Выберите новое значение для или , Xи Yуравнение даст вам другое значение. Если вы не знаете фактическое уравнение, вам нужно найти то, которое его аппроксимирует. Я буду использовать этот ответ, чтобы сосредоточиться на подходах к интерполяции. Они обычно используют встроенные инструменты анализа, которые выполняют большую часть работы. Если вам нужны более подробные сведения о механике использования конкретного инструмента или более автоматизированного подхода, мы можем расширить это в другом ответе.

Попробуйте найти фактическое уравнение

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

Самый простой способ сделать это — построить график данных и добавить линию тренда. В Excel есть несколько распространенных кривых, которые можно попробовать подогнать.

Давайте попробуем это с 2^Nданными из вашего другого вопроса. Если вы не распознали числовой шаблон и попробовали подход с линией тренда, вы увидите значки кривых разной формы. Экспоненциальная кривая имеет ту же общую форму, и это даст вам это:

Excel использует eвместо 2в качестве базы, что является просто переводом (e ^0,693 равно 2). Визуально вы можете видеть, что линия тренда точно следует за данными. R ² также говорит вам об этом. R ² — это статистическая мера того, какую часть вариации в данных вы учитываете с помощью вашего уравнения. Значение 1означает, что уравнение учитывает 100% вариации или идеально подходит.

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

Итак, эти данные не экспоненциальны. Мы можем попробовать полином, который описывает некоторые естественные процессы и способен имитировать множество кривых (позже я расскажу об этом подробнее):

Как аппроксимация процесса, лежащего в основе данных, это не очень подходит. В третьем порядке (уравнение, содержащее степени X до X^3) оно имеет больше основных точек перегиба, чем данные, и все равно не соответствует. Таким образом, базовое уравнение не выглядит как простая, общая кривая, что означает, что уравнение необходимо аппроксимировать.

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

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

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

Регрессия

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

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

• Он предназначен для ситуаций, когда ваша цель — смоделировать процесс, стоящий за данными. Данные предполагаются неточными, и регрессия предполагает, какими они должны быть на самом деле. Кривая, найденная регрессией, может не проходить ни через одну из фактических точек данных. В вашем случае данные даны и предполагаются точными. Кривая должна проходить через каждую точку.

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

Однако, вместо того, чтобы использовать его обычным образом, регрессию можно «злоупотреблять» как обходной путь для того, что вам нужно, и это обычно сработает. Когда вы пытаетесь смоделировать процесс, обычно оценивается самая простая формула (бритва Оккама). С другой стороны, с достаточно сложным уравнением вы можете подогнать что угодно. Вы всегда можете нарисовать каракули, которые пройдут через каждую точку. С Nточками вы можете найти N-1уравнение полинома порядка, которое пройдет через все точки (наихудший сценарий).

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

Вот анализ ваших данных с использованием полиномиальной регрессии с уравнениями последовательно более высокого порядка (первый снимок экрана включает порядки 3–5):

(Нажмите на изображение для читаемого размера.) Обратите внимание, что инструмент анализа включает в себя тип интерполяции, который вы хотите сделать; он сгенерировал промежуточные значения. Для каждого анализа значения a(n)являются коэффициентами уравнения, которое он нашел. a(0)— константа, a(1)— коэффициент для члена X^1 и т. д. Он показывает значение R ² подгонки. Он должен быть виртуальным, 1чтобы быть достаточно близким для вашей цели.

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

Когда мы доходим до полинома 6-го и 7-го порядка, это выглядит так:

Если бы мы перешли к полиному 8-го порядка для ваших 9 значений, это было бы идеально, но 7-й порядок, вероятно, достаточно близок. Для перспективы обратите внимание, что уравнение 7-го порядка имеет R ² .99999 и все еще не идеально.

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

Сплайны

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

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

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

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

Сплайны немного похожи на желе. Представьте себе большой кусок желе, и вы ограничиваете определенные места, где хотите. Остальная часть желе будет выпирать в нужных местах. Уравнение может определять определенные виды кривых. Если вы проведете кривую через определенные точки, произойдет то же самое. В случае сплайнов эффект ограничивается странной выпуклостью или неестественно выглядящим сегментом кривой; уравнения регрессии высокого порядка могут следовать дикому пути.

Вот как сплайны представляют кривую ваших данных:

Если сравнить это с кривыми регрессии высокого порядка, то сплайны более «чувствительны» к локальным изменениям.

Я сделал этот анализ с помощью LibreOffice Calc, в котором есть надстройка для анализа, включающая сплайны. Как вы видите, это также дает для сплайнов интерполированные результаты, которые вы ищете. У меня нет готового доступа к Excel Analysis Toolkit, поэтому я не знаю, включает ли Excel сплайны. Если нет, LO Calc будет работать в Windows, и он бесплатный.

Нижняя граница

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

Answer 2

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

Неточные данные

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

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

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

Использование Y как X

В вашей задаче вы хотите определить значения для Y(целые значения от 1 до 37 в этом примере) и найти связанные значения X. Это было достаточно легко сделать в вашей Y=2^Xзадаче, потому что это простое уравнение можно легко обратить в X=log(Y)/log(2), и вы можете напрямую вычислить любое нужное вам значение. Если уравнение не является чем-то простым, часто нет практического способа инвертировать его. «Злоупотребляемый» подход регрессии в моем предыдущем ответе дает вам уравнение высокого порядка, но оно «однонаправленное», часто непрактично для решения обратного уравнения.

Самый простой подход — просто перевернуть Xи Yначать с самого начала. Это даст вам уравнение, которое вы можете использовать с целыми значениями, которые вы вводите (анализ даст вам коэффициенты уравнения, как описано в предыдущем ответе).

Никогда не помешает проверить, сработает ли простая кривая. Вот обратные данные, и вы можете видеть, что полезного соответствия нет:

Итак, попробуйте полиномиальную подгонку. Однако это случай, подобный тому, что я описал в предыдущем ответе. Значения от 1 до 8 подходят хорошо, но 9 вызывает расстройство пищеварения. Полином 3-го порядка дает вам шишку:

Это становится все более "интересным" по мере увеличения порядка уравнения. К 7-му порядку вы получаете это:

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

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

Экстраполяция против интерполяции

В этом примере ваше первое Yзначение равно 2,9. Вы хотите получить значения для 1и 2, которые находятся за пределами диапазона данных. Это требует экстраполяции, а не интерполяции, что является совершенно другим требованием.

• Если уравнение известно, как в вашем Y=2^Xпримере, вы можете вычислить любое необходимое вам значение.

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

• Если вы принудительно подгоняете уравнение высокого порядка под данные, проекции за пределами диапазона данных обычно бессмысленны.

• Если вы используете сплайны, то нет оснований для проецирования за пределы диапазона данных.

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

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

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

Обратите внимание, что к 4-му или 5-му порядку точки с 1 по 8 довольно точны. Но как только вы выходите за пределы диапазона, уравнения могут вести себя совершенно по-другому.

Экстраполяция с использованием ограниченных данных

Один из способов улучшить ситуацию — подогнать только точки на этом конце и включить столько последовательных точек, сколько следует форме кривой на этом конце. Точка 9, очевидно, выпадает. До этого на кривой есть несколько перегибов, один из которых находится около точки 5 или 6, поэтому точки выше этой следуют другой кривой. Используя только точки с 1 по 5, вы приближаетесь к идеальному соответствию с полиномом 3-го порядка. Это уравнение спроецирует нулевую точку 0,12095 (сравните с таблицей выше), а для Xзначения 1, 0.3493.

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

Это проецирует нулевую точку -0,5138 и Xдля 1, -0.0071.

Этот диапазон возможных результатов указывает на уровень неопределенности за пределами диапазона ваших данных. Правильного ответа нет. И это было на «хорошо себя ведущем» конце вашей кривой. Значение Yдля Xравно . Вы хотите перейти к 37. Сплайны предполагают, что кривая асимптотическая при 9. Проецирование прямой линии в необработанных данных даст значение немного больше (то же самое с полиномом 4-го порядка). Полином 3-го порядка предполагает значение меньше (как и 5-й и 6-й порядки). Полином 7-го порядка предполагает значение существенно выше . Поэтому все, что находится за пределами диапазона данных, является предположением или чем-то, чем вы хотите, чтобы это было.36.79999

Собираем все вместе

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

У меня нет готового доступа к Excel 2013 или Analysis Toolkit. Я буду использовать LibreOffice Calc, чтобы проиллюстрировать это. Это не идентично, но достаточно близко, чтобы вы могли следовать ему в Excel. В LO Calc это на самом деле бесплатное расширение, которое нужно загрузить. Я используюCorelPolyGUI, который можно скачатьздесь. Насколько я помню, в Analysis Toolkit не было сплайнов. Если это все еще так и вы хотите сделать это в Excel, я наткнулся наэта бесплатная надстройка(который я не тестировал). Альтернативой может быть использование LO Calc, который работает в Windows и бесплатен.

Здесь я ввел значения X и Y (перевернутые) в столбцы A и B и открыл диалоговое окно анализа. Выделение значений X и нажатие кнопки X загружает диапазоны данных, и я выбрал полином.

На следующей вкладке я указываю, что хочу использовать 0степени 7(многочлен 7-го порядка со всеми порядками).

Чтобы указать вывод, я выбираю C1 и нажимаю «Столбцы», и он регистрирует столбцы, необходимые для вывода. Я выбираю, что хочу, чтобы он выводил исходные данные, рассчитанные результаты, и я выбрал, чтобы он добавлял три промежуточные точки между каждой исходной точкой данных. И я говорю ему, что хочу график результатов на новой диаграмме. Затем иду в меню «Рассчитать» и нажимаю «Рассчитать».

И вот оно. Если вы посмотрите на вычисленные значения, вы можете заметить проблему. Она станет очевидной на следующем шаге.

Здесь я добавил значения 1через 37. На этом этапе мы хотим иметь дело только с интерполяцией, поэтому я добавил формулу для вычисления только значений 3через 36. Формула просто расширяет коэффициенты, перечисленные в результатах (значения a(n)). Формула в I2:

=D$4+D$5*H3+D$6*H3^2+D$7*H3^3+D$8*H3^4+D$9*H3^5+D$10*H3^6+D$11*H3^7

Это просто каждый коэффициент, умноженный на соответствующую степень значения X. Перетащите это вниз, и вы получите результаты. Ну, не совсем; вам нужно посмотреть на него, чтобы увидеть, проходит ли он тест на разумность. Мы знали, что была проблема между 8и 9, но это оказывается половиной значений, которые вам нужны. Мы могли бы использовать значения от 3до 20, но нет смысла объединять так много значений из другого метода. Так что давайте просто используем сплайны для всего этого.

Откройте диалоговое окно анализа снова и измените метод на "сплайны" на вкладке ввода (здесь не показано). Дайте ему новый выходной диапазон и скажите, что нужно вычислить. Это все, что нужно.

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

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

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

Я добавил столбец с вашими числами 1через 37. Из предыдущего обсуждения у нас нет надежной основы для проецирования значений для 1и 2, поэтому я оставил их пустыми. Для 37я пошел с асимптотическим предположением и сделал его 9. Значения для 3через 36находятся с помощью линейной интерполяции (и это формула, которую вы можете адаптировать к другим данным). Формула в Q3:

=TREND(OFFSET($M$1,MATCH(P3,M$1:M$33)-1,2,2),OFFSET($M$1,MATCH(P3,M$1:M$33)-1,0,2),P3)

Функция TREND просто интерполирует, когда диапазон составляет две точки. Синтаксис:

TREND(Y_range, X_range, X_value)  

Функция OFFSET используется для каждого диапазона. В каждом случае она использует функцию MATCH для поиска первой строки диапазона, содержащей целевое значение. Значения -1таковы, потому что это смещения, а не местоположения; совпадение в первой строке является смещением 0от опорной строки. И обратите внимание, что Yстолбец смещен на 2, в данном случае, потому что я добавил дополнительный столбец для ручной настройки значения. Параметры OFFSET выбирают столбец, содержащий значения Y или X, и выбирают высоту диапазона 2, что дает вам значения ниже и выше целевого.

Результат:

Мастер анализа берет на себя всю тяжелую работу, и независимо от того, используете ли вы полиномиальную регрессию или сплайны, для получения результата требуется всего одна формула.