Кодирование функции Excel для заполнения пробелов в данных

Question

В Excel в настоящее время у меня есть следующая точечная диаграмма и соответствующий набор данных для одной из серий:

Я пытаюсь создать какую-то функцию, которая позволит мне интерполировать точки для пробелов в данных, которые у меня есть в настоящее время; конечная цель - создать плавную кривую для точечной диаграммы. Концептуально, функция, которую я пытаюсь создать, имеет вид:

Дано:

n is an integer, x = 3 + 20n, y = 3 + 20(n + 1)    

=IF(B(3 + 20n) < cell < B(3 + 20(n+1)), add (y-x)/19 to previous value, do nothing)

То есть у меня есть точки данных в столбцах 23, 43, 63, ... и т.д. и я пытаюсь заполнить пробелы, добавив уникальный интервал для каждого бина в предыдущей ячейке. Этот интервал получается путем взятия нижней и верхней границ каждого бина и деления на 19. Другими словами, каждая корзина должна выглядеть так:

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

Answer 1

Сначала добавьте дополнительный столбец. В этом столбце вы можете использовать эту формулу:

=IF(MOD((ROW()-3),20)=0,B3,INDIRECT("B"&(QUOTIENT((ROW()-3),20)*20+3))+  
(INDIRECT("B"&(QUOTIENT((ROW()-3),20)*20+23))-INDIRECT("B"&  
(QUOTIENT((ROW()-3),20)*20+3)))/20*(ROW()-(QUOTIENT((ROW()-3),20)*20+3)))

Как работает эта формула:

MOD(ROW()-3),20)=0

Это проверяет, является ли строка 23,43,63, ... Если это так, он занимает соседнюю ячейку, если нет, то имеет место следующая формула:

INDIRECT("B"&(QUOTIENT((ROW()-3),20)*20+3))

При этом используется функция косвенного вычисления для вычисления адреса первой ячейки, строка которой меньше текущей строки, а также 23,43,63. Он вычитает 3 из текущей строки, а затем делит ее на 20 и округляет до меньшего целого числа. Затем он умножается на 20 и добавляет 3, чтобы получить правильный номер строки. Затем он соединяется с B, чтобы сформировать адрес нужной ячейки.

+(INDIRECT("B"&(QUOTIENT((ROW()-3),20)*20+23))-INDIRECT("B"&  
(QUOTIENT((ROW()-3),20)*20+3)))/20*(ROW()-(QUOTIENT((ROW()-3),20)*20+3))

Это просто интерполяционная формула вида:

 y_int = y_start + [(y_end-y_start)/(x_end-x_start)]*(x_int-x_start)

y_start это тот, который я объяснил выше. Тогда у вас есть y_end, который почти такой же, только вы добавляете 23 вместо 3, чтобы получить следующее значение. Затем вы делите на 20, что является разницей между двумя следующими значениями. X_int-x_start - это следующая формула:

ROW()-(QUOTIENT((ROW()-3),20)*20+3)

Это берет текущую строку и вычитает 23,43,63 .., что ниже ее, используя тот же метод, что и я, чтобы найти y_start.