회사 업무를 하다 보면 주별로, 월별로 보고하는 경우가 있습니다. 사실 개인적으로는 피벗테이블로 보여주는 게 편하기도 하고 내용적으로도 상관이 없다고 생각하지만, 보고 받는 사람이 윗선일수록 디자인적인 요소도 신경쓸 수밖에 없습니다. 이 때문에 피벗테이블로 바로 보고가 가능하지만, 여러 함수를 써가면서 피벗테이블보다는 더 이쁜 보고서를 만들고는 하는데요.
이번 글에서는 상품별 계획대비 실적을 월별로 보고하는 상황을 가정하여, OFFSET함수로 동적 범위를 설정하여 연 누적 실적 구하는 방법을 소개해 드리겠습니다.
OFFSET 함수로 범위를 반환하는 방법에 대해서는 이전 글을 참고해 주세요.
[엑셀 함수] OFFSET 함수로 범위 반환하기
OFFSET 함수의 기본적인 개념에 대해서는 이전 글을 참고해 주시길 바랍니다. [엑셀 함수] OFFSET 함수의 개념 이해하기 OFFSET 함수는 기준 위치(셀/범위)에서 지정한 행과 열만큼 이동한 후, 설정한
writingpjt.tistory.com
1. 예시 파일 가정 상황 : 로데이터 > 피벗테이블 > 보고서용 테이블
A, B, C 상품의 계획 대비 실적을 월별로 보고하는 상황이기 때문에, 상품별 계획과 실적에 대한 데이터가 필요합니다. 매월 실적 데이터를 복사/붙여넣기를 했던, 파워쿼리나 매크로를 활용했던 상품별 월별 계획과 실적이 통합된 데이터가 있다고 예시 파일의 '로데이터 시트'에 있다고 가정해봅시다.
예시 파일은 위의 사진처럼 로데이터를 원본으로 한 피벗테이블을 매월 업데이트하고, 업데이트된 피벗테이블의 내용을 설정된 월에 맞게 자동적으로 실적과 계획, 그리고 달성률이 계산되게끔 설정되어 있습니다.
즉, 흐름을 요약하자면 '① 매월 로데이터 업데이트 ▶ ② 피벗테이블 업데이트 ▶ ③ 월별 보고서 자동 업데이트'입니다.
2. 보고서 영역 수식 분석해보기
보고서 영역은 크게 당월 수치와 연 누적 수치로 나눌 수 있는데요. 요약하자면 당월 수치를 불러올 때에는 OFFSET 함수를,
연 누적 수치를 계산할 때는 SUM(OFFSET()) 두 가지 함수를 섞은 수식을 사용합니다.
1) 당월 수치 불러오기: OFFSET 함수 사용
A 상품을 당월 실적 셀 S9에 있는 수식을 분석해 보겠습니다.
OFFSET(INDEX($B$7:$B$16,MATCH($R9,$B$7:$B$16,0),1),1,$S$2,1,1)
- 우선 OFFSET 함수의 시작점 부분에 INDEX($B$7:$B$16,MATCH($R9,$B$7:$B$16,0),1) 수식이 있습니다. INDEX($B$7:$B$16,MATCH($R9,$B$7:$B$16,0),1)은 피벗테이블 첫 번째 열에서 A 상품(R9셀) 값이 있는 셀을 찾는 수식입니다. INDEX와 MATCH 혼합 수식에 대해서는 이 글(VLOOKUP 활용법#2)을 참고해 주세요.
- 수식을 보면 시작 지점에서 밑으로 1칸 이동하는 것을 알 수 있는데요. 이는 현재 피벗테이블을 기준으로, '실적' 필드가 항상 상품의 바로 1행 아래에 있기 때문입니다. 만약에 계획이 상품 1행 아래 있고, 실적이 2행 아래에 있으면, OFFSET(INDEX($B$7:$B$16,MATCH($R9,$B$7:$B$16,0),1),2,$S$2,1,1)로 수식을 수정해야 할 것입니다.
- 그리고 기준 월이 기입된 S2셀 값만큼 열 이동을 하는데요(위의 사진에서는 7칸 오른쪽 이동).
- 기준 월의 당월(7월) 값만 필요하기 때문에 1행1열(1칸)의 범위만 반환합니다.
그 결과, 피벗테이블 상 "A"값이 있는 셀에서 1칸 아래, 7칸 오른쪽으로 이동해 '80'이란 숫자를 반환합니다.
A 상품을 당월 계획 셀 T9에 있는 수식은 아래와 같습니다.
OFFSET(INDEX($B$7:$B$16,MATCH($R9,$B$7:$B$16,0),1),2,$S$2,1,1)
실적 셀과 다른 점은 아래로 2칸 이동한다는 것입니다. 이는 위에서 설명드렸 듯이, 피벗테이블에서 '계획' 필드가 상품 이름보다 두 행 아래에 있기 때문입니다.
2) 연 누적 수치 불러오기: SUM(OFFSET()) 수식 사용
연 누적 수치를 계산하려면 1월부터 기준 월까지의 값을 더해야기 때문에 SUM 함수와 OFFSET 함수를 혼합해야 합니다.
A상품의 누적 실적 셀 V9에는 아래의 수식이 있습니다.
SUM(OFFSET(INDEX($B$7:$B$16,MATCH($R9,$B$7:$B$16,0),1),1,1,1,$S$2))
당월 수치를 불러올 때와 비교해서 OFFSET 함수 부분에서 달라진 점은 열 이동 부분과 반환할 열의 개수 부분입니다.
- 당월 수치를 불러올 때와 달리 누적 수치를 구할 때는 시작점에서 1칸 아래 이동 후 오른쪽으로 1칸 이동합니다. 왜냐하면, 연 누적 수치를 계산하기 위해서는 1월 값부터 필요하기 때문입니다.
- 시작 점에서 아래로 1칸, 오른쪽으로 1칸 이동 후, '1행*기준 월 값(S2셀 값) 열' 범위만큼 반환합니다. 위의 사진의 경우, 기준 월이 7월이기 때문에 1행7열 범위(C8:I8)를 반환합니다.
- 이후 SUM 함수로 반환한 범위의 값들을 더해주면 기준 월의 연 누적 수치가 계산됩니다.
샘플 파일에서 S2셀에 기준 월을 바꿔가면서 테스트해보시면, 각 상품의 월 및 연 누적 실적과 계획이 자동 계산되는 것을 확인할 수 있습니다. 또한, 'X월 실적 보고'나 'X월 실적 현황' 같은 부분도 자동적으로 바뀌도록 수식을 짜 놓았으니, 확인해보세요.
'엑셀, VBA, 파워쿼리 > 엑셀 스프레드시트' 카테고리의 다른 글
| [엑셀 팁] '새 창' 기능으로 하나의 엑셀파일을 여러 개 창으로 보기 (0) | 2022.10.27 |
|---|---|
| [엑셀 팁] '수식 표시 기능'으로 셀에 입력된 수식 확인하기 (단축키 Ctrl + ' ) (0) | 2022.10.25 |
| [엑셀 팁] '선택하여 붙여넣기'로 여러셀 숫자 단위 한번에 바꾸기 (1) | 2022.10.22 |
| [엑셀 함수] OFFSET 함수로 범위 반환하기 (0) | 2022.10.09 |
| [엑셀 함수] OFFSET 함수의 개념 이해하기 (0) | 2022.10.05 |
댓글