[엑셀] 엑셀자동화 월별매출현황 자동합계 구하기(SUM과 OFFSET함수 활용)

오늘은 영업기획/관리직들의 매출현황 엑셀자동화에 대해서 배워보겠습니다.

배워두시면 여러 분의 시간을 아낄 수 있습니다.

 

엑셀 자동화 중에서 함수로도 구현이 가능한 간단한 부분의 하나로 봐주시면 됩니다.

지난 번 텍스트 자동화와 함께 사용하면 더욱 편할 것 같습니다.

2022.08.30 - [엑셀(Excel)] - [엑셀] 엑셀 text함수를 통한 엑셀 자동화 첫걸음 !! (난이도 쉬움 주의)

[엑셀] 엑셀 text함수를 통한 엑셀 자동화 첫걸음 !! (난이도 쉬움 주의)

 

영업 관리하시는 경우에는 월초에 항상 마감데이터를 지표화 시켜서 입력하는데요

데이터를 넣고 일일이 합계를 더하는 것도 은근한 노가다가 들어갑니다.

더욱이 자동화를 시켜야 하는 이유 중 하나는 사람이 실수로 놓치는 경우가 발생하는 것을 방지해주기 때문입니다.

 

팀에 엑셀을 아주 잘하는 차장님이 SUM과 OFFSET함수 그리고 셀서식을 사용해서 마감데이터를 한 번에 만드는 모습을 보고 참고하였습니다. 

 

결과 미리보기

여러 분들도 결과적으로는 이런 형태로 만들 수 있습니다. 

offset함수 활용 미리보기

 

일반적인 매출현황 집계표

매출현황 집계표 예시

보통 월별로 데이터를 정렬한 뒤 끝에 몇월 합계처럼 보여주는 형태를 사용하고 계시리라 생각됩니다.

월이 변경될 때마다 =SUM의 범위를 매번 번거롭게 바꿔주어야 하는게 은근히 귀찮습니다.

 

이럴 때 "3월 합계" 부분의 숫자만 5로 바꿔주시면 자동으로 "5월 합계"(5월 누적합계)가 계산될 수 있도록 수식을 배워보겠습니다.

 

수식 결과만 궁금하신 분들께선 맨 아래로 내려주시면 확인 가능합니다. 

OFFSET함수 기본

먼저 OFFSET함수에 대해서 알아보겠습니다.

=OFFSET(reference, rows, cols, [height], [width]) 이라고 입력하면 괄호안에 영어가 나옵니다.

 - reference의 경우 참조할 셀을 물어보는 겁니다. 

 - rows : 행을 어떻게 움직일지 (양수이면 아래로 음수이면 위로)

 - cols : 열을 어떻게 움직일지 (양수이면 오른쪽으로 음수이면 왼쪽으로)

 - [height] : [ ] 는 생략해도 무관한 명령어라는 뜻입니다. height는 영어로 높이라는 뜻이니 열을 어디까지 포함할지

 - [width] : [ ] 는 생략해도 무관한 명령어라는 뜻입니다. width는 영어로 너비라는 뜻이니 행을 어디까지 포함할지

 

그림으로 한번 보겠습니다.

체스나 장기의 말을 움직인다고 생각하시면 편할 것 같습니다. 

 

엑셀 OFFSET함수 설명

현재 엑셀 E8셀을 기준으로 OFFSET 함수를 작성하였습니다.

 

오른쪽으로 한칸 이동하는 명령어는 =OFFSET(E8, 0, 1)입니다.

=OFFSET(E8, 0, 1) 뜻은 E8셀에서 rows(행)는 움직이지 말고 cols(columns, 열)은 오른쪽으로 한 칸 움직여라는 뜻입니다. 

아래로 한칸 이동하는 명령어는 =OFFSET(E8,1,0)입니다. 물론 0은 생략가능하여 =OFFSET(E8,1,)처럼 작성해주셔도 됩니다. 위와 마찬가지로, E8셀에서 rows(행)은 아래로 한 칸 움직이고 열은 0으로 움직이지 말라는 뜻입니다. 

 

음수의 경우 반대로 이동합니다.

 

위 표에서 A, B, C, D 도 각각 구해보시길 바랍니다. 

 

• A : =OFFSET($E$8,-1,-1)
• B : =OFFSET($E$8,-1, 1)
• C : =OFFSET($E$8, 1,-1)
• D : =OFFSET($E$8, 1, 1)

OFFSET함수 응용

다음은 height와 width에 대해 알아보겠습니다.

=OFFSET(reference, rows, cols, [height], [width])

[ ] 부분은 offset함수를 실행하는 데 필수적인 변수는 아니므로 입력을 필요하면 하고 아니면 안해도 된다라는 말입니다.

 - height : 반환되는 참조의 높이(단위: 행 수)입니다. height는 양수여야 합니다.

 - width : 반환되는 참조의 너비(단위: 열 수)입니다. width는 양수여야 합니다.

배열로 진행되다 보니 OFFSET만 사용하실 때는 Ctrl + Shift + Enter 로 눌러주셔야 합니다. 

 

OFFSET함수 다른 함수와 결합하여 엑셀 자동화

OFFSET과 SUM함수 결합

저희는 사실 세로보다는 월별을 가로로 두는 경우가 많을 것 같습니다.

그렇다면 width 부분만 살펴보도록 하겠습니다. 

 

현재 offset함수를 sum으로 감싸주었습니다. 

보통 한 줄에 대해서만 쓸 예정이므로 

=OFFSET($B$4, 0, 0, 1, 변경될 숫자) 로 작성해주시면 됩니다. 

=OFFSET($B$4, 0, 0, 1, 변경될 숫자)

여기서 앞에 row 와 col이 0인 경우 생략가능하며, height와 width도 1인 경우는 생략가능합니다.

따라서, =OFFSET($B$4, , , , 변경될 숫자) 이런 식으로 작성도 가능합니다. 

 

예시의 경우에는 N3셀에 숫자를 변경하면 자동으로 값이 바뀌도록 해두었습니다. 

다만 N3셀은 현재 숫자 6만 들어가있음에도 불구하고 "6월 합계"처럼 보이는데요. 이 부분은 셀 서식에서 변경 가능합니다.

자동화 완성

셀서식으로 조금 더 완벽하게 자동화 구현

셀서식 단축키는 Ctrl + 1 입니다.

 

N3셀 클릭 ▶셀서식(Ctrl + 1) ▶ 표시형식 ▶ 사용자 지정 ▶ 0월 합계 입력 

 

셀서식으로 숫자를 문자처럼 보이게

 

※ 참고자료  :  OFFSET함수 - MS공식문서

OFFSET 함수 - Microsoft 지원

 

※ 파일도 업로드 해둘테니 참고하셔서 자동화 하시길 바랍니다. 

엑셀 OFFSET함수_upload1.xlsx

0.01MB