У меня в ячейке следующая формула M8:
{=SUM((MOD(ROW(M$16:$M977)-ROW($M$16),4)=0)*(M$16:$M977))}
с помощью этой формулы получаем общее количество часов в неделю в столбце M из каждой четвертой строки, начиная с M16, например m16+m20+m24+m28 и т. д. Я продолжаю добавлять новые данные сверху, поэтому, когда я открываю электронную таблицу, у меня сверху будет самая актуальная информация. Когда добавляю четыре новые строки для моих новых данных, формула выглядит так:
{=SUM((MOD(ROW(M$20:$M977)-ROW($M$20),4)=0)*(M$20:$M977))}
Как мне сделать так, чтобы добавление часов всегда начиналось с М16, потому что именно там будет мое новое общее количество часов на новую неделю?
решение1
Поместите значение M16 в конец файла или в ячейку, которую вы не перемещаете! Фактически, каждый раз, когда вы добавляете строку, автоматически каждая ссылка на ячейку изменяется!
решение2
Во-первых, вам следует изменить это на SUMPRODUCTформулу. Это не требует ввода в виде формулы массива и - IMHO - это проще в использовании.
=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))