Как сохранить номер ячейки в формуле при добавлении строк сверху

Question

У меня есть следующая формула в ячейке M8:

{=SUM((MOD(ROW(M$16:$M977)-ROW($M$16),4)=0)*(M$16:$M977))}

с помощью этой формулы a получим общее количество часов в неделю в столбце M из каждой четвертой строки, начиная с M16, например, m16+m20+m24+m28 и т. д. Я продолжаю добавлять новые данные сверху, поэтому при открытии электронной таблицы у меня будет самая актуальная информация сверху. При добавлении четырех новых строк для моих новых данных формула выглядит так:

{=SUM((MOD(ROW(M$20:$M977)-ROW($M$20),4)=0)*(M$20:$M977))}

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

Answer 1

Поместите значение M16 в конец файла или в ячейку, которую вы не перемещаете! Фактически, каждый раз, когда вы добавляете строку, автоматически изменяется каждая ссылка на ячейку!

Answer 2

Во-первых, вы должны изменить это на формулу SUMPRODUCT . Это не требует, чтобы вы вводили его как формулу массива и - ИМХО - его проще использовать.

=SUMPRODUCT(M16:M977*(MOD(ROW(M16:M977)-ROW(M16),4)=0))

---

Чтобы обойти поведение Excel при вставке строк, я рекомендую превратить ваши данные в таблицу. (Вставьте ленту> Таблица) Это делает ссылку на "все данные в этой области" действительно простой и обновляет ее так, как вам нужно, когда таблица становится больше или меньше. В этом случае формула будет выглядеть примерно так:

=SUMPRODUCT(Table1[Hours]*(MOD(ROW(Table1[Hours])-ROW(Table1[[#Headers],[Hours]])-1,4)=0))

---

Если по какой-то причине вы не можете превратить его в таблицу, я бы рекомендовал создать именованный диапазон и ссылаться на него. Формула для именованного диапазона будет иметь вид:

=OFFSET(Sheet3!$M$1,15,0,MATCH(9E+99,Sheet3!$M:$M)-15)

... и формула, ссылающаяся на это будет:

=SUMPRODUCT(rngHours*(MOD(ROW(rngHours)-ROW(OFFSET(rngHours,0,0,1,1)),4)=0))

... где rngHours - это любое имя, которое вы дали названному диапазону.

---

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

=SUMPRODUCT(OFFSET(Sheet3!$M$1,15,0,MATCH(9E+99,Sheet3!$M:$M)-15)*(MOD(ROW(OFFSET(Sheet3!$M$1,15,0,MATCH(9E+99,Sheet3!$M:$M)-15))-ROW(OFFSET(Sheet3!$M$1,15,0)),4)=0))