엑셀 함수 > 찾기 및 참조영역 함수 > OFFSET 함수로 월별 누계 구하기

OFFSET 함수로 월별 누계 구하기

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

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

실습용 엑셀 파일 다운로드 : 엑셀-OFFSET-함수로-월별누계구하기.xlsx

 

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

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

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

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

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

 

OFFSET 함수로 누계 구하기

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

OFFSET 함수로 누계 구하기

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

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

 

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

OFFSET 함수로 누계 구하기 과정

=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월까지의 전체 지역 판매실적 누계는 다음과 같이 구할 수 있습니다.

OFFSET 함수로 전체 지역의 판매실적 구하기

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

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

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

 


관련 글

엑셀 애드인 엑셀토이박스 XLToybox(엑셀토이박스) 2.0.0 버전 2024.9.24 배포(심플간트 추가) - 엑셀 유틸리티 프로그램 XLToybox(엑셀토이박스)에 심플간트(Simple Gantt) 기능을 추가한 2.0.0 버전을 배포합니다. 2.0.0 버전 추가 내용 심플간트(Simple Gantt) 엑셀파일에 간트차트를 간단히…

XLToybox(엑셀토이박스) 2.0.0 버전 2024.9.24 배포(심플간트 추가) 더 보기 »

동적범위에 이름정의 엑셀 동적 범위에 이름 정의하기 - 오늘은 이름으로 지정한 범위가 계속 변하는 경우에 필요한 '동적 범위에 이름을 정의'하는 방법을 알아 보겠습니다.   1. 고정된 범위에 이름…

엑셀 동적 범위에 이름 정의하기 더 보기 »

OFFSET 함수 - 행과 열 이동 후 참조구하기 - 엑셀에서 OFFSET 함수는 이름만 봐도 왠지 어려워 보이는 함수입니다. OFFSET이라는 단어가 주는 모호함 때문일 것입니다. OFFSET을 영어 사전에서 찾아보면 '상쇄하다',…

OFFSET 함수 - 행과 열 이동 후 참조구하기 더 보기 »

 

엑셀웍스 책 출간 안내

그동안 엑셀웍스에 올린 '엑셀 강좌', '함수 사용법', 여러 '실무 예제'에서 핵심만 뽑아 '된다! 엑셀 수식 & 함수' 책으로 출간하였습니다.

 자세히 보기

온라인 구매처: 예스24   교보문고   알라딘 

댓글 남기기

Scroll to Top