OFFSET 함수로 월별 누계 구하기

엑셀의 OFFSET 함수는 어떤 셀로부터 행과 열을 이동한 후 동적으로 참조를 구해줍니다.

이번 글에서는 참조를  동적으로 구하는 기능을 이용하여 월 단위 판매실적 누계를 구하는 방법을 알려드리겠습니다.

 

OFFSET 함수를 사용하지 않고 누계 구하기

아래와 같이 월별 판매실적 데이터가 있을 때 서울 지역의 7월까지 판매실적 누계를 구해야 한다면

다음과 같이 간단하게 SUM함수만으로도 해결할 수 있습니다.
=SUM(C5:C11) 

만약 8월까지 구해야 한다면 합계 범위를 [C5:C11] 에서 [C5:C12]로 바꿔주면 되긴하지만 계속 수식을 수정해야 문제가 있습니다.
=SUM(C5:C12)

 

OFFSET 함수로 누계 구하기

판매월이 바뀌더라도 수식을 변경하지 않고 누계를 구해야 한다면 동적으로 참조를 구해주는 OFFSET 함수를 사용하면 됩니다.

7월까지의 서울지역 판매실적 누계를 구하려면 [D22] 셀에 다음 수식을 입력합니다.

=SUM(OFFSET(C5,0,0,D20,1))  => 7,551,000

 

수식 계산 과정을 살펴보겠습니다.

=SUM(OFFSET(C5,0,0,D20,1))

위 수식의 OFFSET 함수의 인수는 다음과 같습니다.

• 첫번째 인수 C5 : 1월 실적의 위치
• 두번째 인수 0 : 아래로 0칸(즉 현재 위치 그대로)
• 세번째 인수 0 : 오른쪽으로 0칸(즉 현재 위치 그대로)

OFFSET(C5,0,0,D20,1) 에서 두번째,세번째 인수가 0이므로 아래로, 오른쪽으로 이동하지 않습니다.
따라서 참조 위치는 C5그대로이며,

OFFSET 함수는 이 위치로부터

• 네번째 인수 [D20] 셀의 값 : 높이가 7칸(월을 입력받는데 7이면 7칸이므로 7월까지 참조)
• 다섯번째 인수 1 : 너비가 1칸(서울지역만 포함)인

범위를 반환합니다.  반환된 범위를 SUM하면 7월까지의 누적판매실적은 7,551,000원이 됩니다.

 

7월까지 전체 지역의 판매실적 누계 구하기

위에서 사용한 수식을 약간 바꾸면, 7월까지의 전체 지역 판매실적 누계는 다음과 같이 구할 수 있습니다.

=SUM(OFFSET(C5,0,0,D20,3))  => 17,356,000

서울지역 판매실적누적을 구하는 수식과 거의 동일한데 마지막 인수인 너비만 다릅니다.

서울,경기,충청지역 3군데를 포함해야 하므로 마지막 인수인 너비는 3칸이 되어야 합니다.

 

---

관련 글