저는 Excel 2010을 사용하고 있지만 이 문제는 모든 Excel 버전에서 동일하게 해결될 것이라고 생각합니다.
본질적으로 슬라이딩 누적 값을 제공하여 합계하고 싶은 데이터가 있습니다. 예를 들어, 1000개의 행이 있는 테이블에서 현재 행 값과 이전 X 행의 합계를 표시하는 열을 원합니다. 여기서 X는 다른 셀에 지정하려는 숫자입니다. 예를 들어 마지막 6행 누적 값, 마지막 5행 또는 8행 등이 있습니다.
ADDRESS, INDIRECT, INDEX 등을 사용하여 다양한 조합을 시도했지만 아무것도 작동하지 않는 것 같습니다.
다음은 창 크기가 2인 원하는 결과를 보여주는 예입니다.
|A |B
1 |Value |Cumulative
2 | 1 |
3 | 2 |3
4 |15 |17
5 |10 |25
6 |11 |21
7 | 8 |19
창 크기 3의 경우
|A |B
1 |Value |Cumulative
2 | 1 |
3 | 2 |
4 |15 |18
5 |10 |27
6 |11 |36
7 | 8 |29
앞서 말했듯이 스프레드시트의 다른 셀(예: "WindowSize")에 창 크기를 지정하고 싶습니다.
창 크기가 유효한 데이터를 초과하는 정도라면 공백이나 0 값을 반환하면 좋겠지만 필수는 아닙니다. #Refs를 무시하거나 이를 설명하기 위해 열의 시작 부분을 수동으로 조정해도 괜찮습니다.
나는 이것이 Visual Basic에 의존하지 않고도 수식을 사용하여 수행될 수 있다고 믿고 싶습니다. 그럴 수 있나요?
고마워요, 요시
답변1
Index 메소드는 다음과 같습니다.
=IF(ROW(1:1)<$E$1,"",SUM(INDEX(A:A,(ROW()-$E$1)+1):INDEX(A:A,ROW())))
INDEX는 참조하는 데이터가 변경될 때만 다시 계산한다는 점에서 비휘발성 함수입니다.
둘 다OFFSET() 및 INDIRECT()휘발성 기능입니다. 휘발성 함수는 Excel이 다시 계산할 때마다 계산합니다. 그래서 그 수가 많으면 불필요한 계산으로 인해 계산 속도가 느려집니다.
답변2
이것은 셀에서 작동합니다 B2.
=SUM(A2:INDIRECT(ADDRESS(ROW(A2)-($D$2-1),1,4)))
창 값은 셀에 있습니다 $D$2.
답변3
오프셋 기능을 사용하겠습니다.
=sum(offset(A2,0,0,D1))
여기서 D1에는 행 수가 있습니다.