Заполнение формулы в Excel до разреженного диапазона

Question

Я создаю таблицу Excel из справочной таблицы топливного бака, где значения объема были даны для каждого пятого сантиметра. Мне нужно заполнить пустые ячейки значениями интерполяции, чтобы потом использовать функцию VLookUp, чтобы получить громкость как функцию звучания (первый столбец) и обрезки (ось x). Мне удалось вставить четыре пустых строки между каждым заданным значением. Затем я заполняю ячейки между ними путем интерполяции значений верхнего и нижнего рядов. Затем я копирую диапазон интерполяции и вставляю его в пустой диапазон. Это повторяется сотни раз. Поэтому мне интересно, может ли быть более простой способ, так как мое общее количество танков составляет около 20, а глубина (ось у) составляет около 10000 см. Проблема в том, что если я копирую и вставляю только формулу, пустая ячейка назначения все равно перезаписывается на пустое значение. В противном случае я мог бы повторно использовать формулы из первого бака.

...   
...  
125     475.4   474.9   474.3   473.7   473.1   472.4   471.8   471.2 ...    
126     475.4   474.9   474.3   473.7   473.1   472.4   471.8   471.2 ...   
127     474.7   474.1   473.5   472.9   472.3   471.6   471.0   470.4 ...   
128     473.9   473.3   472.8   472.1   471.5   470.9   470.3   469.6 ...   
129     473.2   472.6   472.0   471.3   470.7   470.1   469.5   468.8 ...   
130     472.4   471.8   471.2   470.6   469.9   469.3   468.7   468.0 ...  
131                                 
132                                 
133                                 
134                                 
135     469.3   468.7   468.0   467.4   466.8   466.2   465.5   464.9 ...  
136                                 
137                                 
138                             
139                             
140     466.1   465.5   464.9   464.3   463.6   463.0   462.4   461.8 ...  
141                             
142                             
143                             
144                             
145     463.0   462.4   461.8   461.2   460.5   459.9   459.3   458.6 ...  
146                             
147                             
148                             
149  
...  

Спасибо за внимание.

Answer 1

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

Это решение включает воссоздание всего столбца в другом месте с помощью формулы OFFSET, а затем необходимо скопировать-вставить Специальные -> Значения обратно в исходный столбец.

Предполагая, что ваши данные находятся в ячейках B1:B16. Теперь создайте две вспомогательные колонки. Столбец слева с повторяющейся последовательностью 0 1 2 3 4. Все, что вам нужно сделать, это выбрать этот диапазон и просто вставить его во все соответствующие строки ниже. Последовательность должна повториться снова в вставленных ячейках. Аналогично столбец справа с повторяющейся последовательностью 0 4 3 2 1.

Теперь в D1 поместите следующую формулу и перетащите ее вниз до предполагаемой длины столбца.

=IF(ISNUMBER(B1),B1,OFFSET(B1,-VALUE(A1),0)-(((OFFSET(B1,-VALUE(A1),0)-OFFSET(B1,VALUE(C1),0))/5)*A1))

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

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

Предположения -

1. Между всеми применимыми ячейками имеются точные промежутки в 4 строки.

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

3. Значения всегда в порядке убывания сверху вниз.

Изучите это решение и посмотрите, подходит ли оно вам.