Какой самый простой способ интерполировать и искать в таблице x, y в Excel?

Question

Я хотел бы сделать поиск и интерполяцию на основе данных x, y для следующей таблицы. Я бы хотел, чтобы уравнение было максимально простым, чтобы уменьшить количество возможных ошибок. Полная таблица составляет около 50 строк х 30 столбцов. У меня есть около 20 из этих таблиц. Вот выдержка из одного:

   A       B      C    D 
1        0.1    0.2   0.3 
2  2.4   450    300   50
3  2.3   500    375   52
4  2.1   550    475   55
5  1.8   600    600   60

Например, уравнение должно найти значение для х = 2,27 и у = 0,15

Answer 1

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

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

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

• Номер строки наименьшего значения вертикального индекса, которое больше (или равно) вертикального входного значения vnum . Поскольку значения индекса в столбце 1 таблицы расположены в порядке убывания, это можно получить с помощью:

  =MATCH(vnum,vrng,-1)

где vnum - это входное значение, а vrng - вертикальный диапазон индексов в первом столбце таблицы. Третий аргумент -1 функции MATCH указывает, что будет выполнен поиск "меньше чем". Такое использование функции MATCH требует, чтобы вертикальный диапазон индексов находился в порядке убывания.

• Наибольшее значение вертикального индекса, которое меньше (или равно) вертикального входного значения (vnum). Этого нельзя получить с помощью функции MATCH поскольку значения индекса по вертикали не сортируются в порядке возрастания, требуемом для MATCH . Вместо этого используется следующая формула массива.

  =MIN(IFERROR(1/(vnum>=vrng)*ROW(INDIRECT("1:"&ROWS(vrng))),ROWS(vrng)))

Ключевым элементом в этой формуле является vnum>=vrng , который создает логический массив, в котором первый TRUE находится в позиции строки, которая содержит наибольшее значение вертикального индекса, которое меньше входного значения. (Использование «> =» может показаться нелогичным; это необходимо, поскольку индексы в столбце расположены в порядке убывания.) В оставшейся части формулы эта позиция строки преобразуется в номер строки.

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

Соответствующие номера столбцов и значения столбцов для горизонтального диапазона индекса строятся аналогичным образом.

Остальные шаги работают через арифметику интерполяции.

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

Настройка этих вычислений для нескольких таблиц может быть еще более упрощена с помощью их одноформальной версии. Чтобы использовать его, необходимо установить именованные диапазоны vnum , hnum , vrng , hrng , datarng , validvnum и validhnum . Таблицы должны быть на отдельных листах или в отдельных рабочих книгах. Если в отдельных листах, имена для каждого листа должны быть установлены, чтобы иметь область листа.

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

Рабочий лист, содержащий этот набор расчетов, можно скачать по этой ссылке.

Answer 2

На самом деле у вас есть трехмерная таблица - x и y - независимые переменные, и вам нужно найти z .

Я знаю решение для 2-го стола, но для 3-го оно должно быть похожим.

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

1. Создайте для него линейный разброс (XY) (Insert => Scatter);
2. Создайте линию тренда полинома или скользящей средней, отметьте «Показать уравнение на графике» (щелкните правой кнопкой мыши на серии => Добавить линию тренда);
3. Скопируйте уравнение в ячейку и замените x желаемым значением х

На снимке экрана ниже A12:A16 содержит x , B12:B16 содержит y , а C12 содержит формулу, которая вычисляет y для любого x .

Что касается вашего случая (трехмерные данные), в Excel также есть возможность создавать трехмерные диаграммы, но я не знаю, есть ли у них трехмерные тренды.

Answer 3

Используйте функции индекса и соответствия Excel в сочетании, чтобы достичь своего результата. Используя приведенные выше данные, поместили точно в excel и вывели следующую формулу. Формула вводится в ячейку D8.

=INDEX($B$3:$D$6,MATCH(E2,$A$3:$A$6,-1),MATCH(F2,$B$2:$D$2,-1))

Answer 4

Вот формула, которую вы ищете: учитывая две точки (x1, y1) и (x2, y2), для интерполяции между ними используйте формулу:

y = (y2-y1)/(x2-x1) * (x-x1) + y1

где х - вход, а у - выход. Учитывая следующую таблицу

Формула выглядит следующим образом:= IF(ISERROR(MATCH(O3, R $ 4:R $ 10)), S $ 4, IF(ISERROR(INDEX(R $ 4:R $ 10, MATCH(O $ 3, R $ 4:R $ 10) +1)), S $ 10,(ИНДЕКС (R $ 4:S $ 10, MATCH(О3, R $ 4:R $ 10) +1,2) -index (R $ 4:S $ 10, MATCH(О3, R $ 4:R $ 10), 2))/(ИНДЕКС (R $ 4:S $ 10, MATCH(О3, R $ 4:R $ 10) + 1,1) -index (R $ 4:S $ 10, MATCH(О3, R $ 4:R $ 10), 1)) * (Индекс O3 (R $ 4:S $ 10, MATCH(O3, R $ 4:R $ 10), 1)) + INDEX (R $ 4:S $ 10, MATCH(O3, R $ 4:R $ 10), 2)))

Функции iferror обрабатывают конечные точки. Они сохраняют значение в конечной точке, а не экстраполируют.

Answer 5

Я написал код VBA для билинейной интерполяции, который вы можете найти здесь: https://github.com/DanGolding/Linear-and-bilinear-interpolation-in-Excel

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

Answer 6

Это решено с тремя объединенными функциями; Прогноз, смещение и совпадение.

=FORECAST($B$9,OFFSET(B3:D3,0,MATCH($B$9,$B$2:$D$2,1)-1,1,2),OFFSET($B$2:$D$2,0,MATCH($B$9,$B$2:$D$2,1)-1,1,2))

=FORECAST(B10,OFFSET(E3:E6,MATCH(B10,A3:A6,-1)-1,0,2),OFFSET(A3:A6,MATCH(B10,A3:A6,-1)-1,0,2))

Answer 7

Лучший ответ на эту проблему найден в этом видео https://www.youtube.com/watch?v=LFUd5qF8nyE

Окончательная формула

=FORECAST(C3,OFFSET(interpol_array,MATCH($C3,lookup_array,1)-1,0,2),OFFSET(lookup_array,MATCH($C3,lookup_array,1)-1,0,2))

Где "interpol_array" определяется как столбец, из которого вы хотите интерполировать значения, а "lookup_array" - это столбец, в котором вы ищете значения.

В этом ответе также говорится то же самое https://stackoverflow.com/questions/42877228/vlookup-and-interpolating