셀에 다음 수식이 있습니다 M8.
{=SUM((MOD(ROW(M$16:$M977)-ROW($M$16),4)=0)*(M$16:$M977))}
이 공식을 사용하면 M16+m20+m24+m28 등과 같이 M16을 시작으로 네 번째 행마다 M 열의 주당 총 시간을 얻을 수 있습니다. 맨 위에 새 데이터를 계속 추가하므로 스프레드시트를 열 때 최신 정보가 맨 위에 표시됩니다. 새 데이터에 대해 4개의 새 행을 추가하면 수식은 다음과 같습니다.
{=SUM((MOD(ROW(M$20:$M977)-ROW($M$20),4)=0)*(M$20:$M977))}
M16에 항상 추가를 시작하도록 하려면 어떻게 해야 합니까? M16이 새 주의 새로운 총 시간이 될 것이기 때문입니다.
답변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))