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

Чтобы выполнить интерполяцию для входных значений, которые лежат между значениями индекса в таблице, необходимо следующее:
Номер строки наименьшего значения вертикального индекса, которое больше (или равно) вертикального входного значения 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 (минус один) символом включена в загружаемый лист.
Рабочий лист, содержащий этот набор расчетов, можно скачать по этой ссылке.