엑셀 전년대비 계산: 늘었다 줄었다까지는 쉽고, 왜인지는 두 줄이 더 필요합니다

작년보다 12% 늘었습니다.

보고서에 이 문장을 쓰고 나면 다음 줄이 안 나옵니다. 12%가 좋은 숫자인지, 다음 달에 무엇을 더 해야 하는지가 이 숫자에는 없습니다.

증감률은 결과만 말해 줍니다. 다음 행동은 그 안을 열어야 보입니다.

엑셀 전년대비 증가율 수식부터 맞춥니다

올해 값이 C2, 작년 값이 B2면 이렇게 씁니다.

=C2/B2-1

셀 서식을 백분율로 바꾸면 12%로 표시됩니다. =(C2-B2)/B2와 결과가 같은데 앞의 것이 짧습니다.

증감액도 같이 둡니다. 비율만 있으면 규모가 안 보입니다. 작은 거래처가 200% 늘어도 금액은 몇십만 원에 그칩니다.

=C2-B2

보고서에는 증감액과 증감률을 나란히 놓습니다.

분모가 0이거나 음수면 수식이 거짓말을 합니다

엑셀 전년대비 계산은 여기서 가장 자주 틀립니다.

작년이 0이면 #DIV/0!이 뜹니다. 오류를 감추려고 IFERROR로 0을 반환하게 만들면 신규 거래처가 증감 0으로 보입니다.

=IF(B2=0,"신규",C2/B2-1)

작년이 음수인 경우도 있습니다. 반품이 판매보다 많았던 달이 그렇습니다. 이때 비율을 계산하면 부호가 뒤집혀 개선을 악화로 표시합니다.

=IF(B2<=0,"비교불가",C2/B2-1)

비교불가로 두고 금액만 봅니다. 이 두 줄이 없으면 보고서에 설명 못 하는 숫자가 섞입니다.

엑셀 목표달성률은 기간을 맞춰야 합니다

달성률 수식 자체는 간단합니다.

=실적/목표

월 중간에 보면 어긋납니다. 15일에 50%면 잘 가고 있는데도 절반밖에 못 했다고 읽힙니다. 경과일 기준 목표를 하나 더 둡니다.

=목표 * DAY(TODAY()) / DAY(EOMONTH(TODAY(),0))

이 값과 비교하면 지금 속도로 월말에 목표를 넘는지 보입니다. 영업일 기준으로 봐야 하면 NETWORKDAYS로 바꿉니다.

달성률을 조건부 서식으로 색만 칠해 두는 곳이 많은데, 색은 지금 상태를 보여주고 경과일 기준선은 남은 기간에 무엇을 해야 하는지를 보여줍니다.

엑셀 전년대비 계산 다음에 증감액을 수량과 단가로 나눕니다

이 글에서 가장 값어치가 큰 부분이 여기입니다.

매출은 수량에 단가를 곱해 나옵니다. 그래서 증감액도 두 숫자로 나뉩니다.

수량 차이 = (올해 수량 - 작년 수량) × 작년 단가
단가 차이 = (올해 단가 - 작년 단가) × 올해 수량

두 숫자를 더하면 증감액이 됩니다. 엑셀에서는 이렇게 넣습니다.

=(C_수량-B_수량)*B_단가
=(C_금액/C_수량-B_금액/B_수량)*C_수량

같은 12% 증가라도 안이 다릅니다. 두 숫자를 보면 갈립니다. 수량이 늘어서 12%면 잘 팔린 것이고, 단가가 올라서 12%면 물량은 그대로 있었습니다. 물량이 안 늘었는데 가격만 올린 상태가 몇 달 이어지면 다음 인상에서 이탈이 납니다.

다음 달에 할 일이 달라집니다. 수량이 문제면 채널과 노출을 보고, 단가가 문제면 할인 정책과 제품 구성을 봅니다.

품목별로 나눠 보면 원인이 좁혀집니다

전체 증감액을 품목별로 다시 나눕니다.

=SUMIFS(올해금액,품목,$A2)-SUMIFS(작년금액,품목,$A2)

증감액이 큰 순서로 정렬하면 상위 다섯 개가 전체 변동의 대부분을 설명하는 경우가 많습니다. 열 페이지짜리 분석보다 이 다섯 줄이 회의에서 더 자주 쓰입니다.

거래처로 나눠도 같은 방식으로 갑니다. 거래처별 집계는 거래처별 매출 합계에 적어 뒀습니다.

매달 같은 표로 뽑으면 비교가 쌓입니다

이번 달에 새로 만든 표는 다음 달에 또 만들게 됩니다.

전년 동월 · 당월 · 증감액 · 수량 차이 · 단가 차이 · 목표 · 달성률 일곱 열을 고정해 두고 원본만 갈아 끼웁니다. 반년쯤 지나면 수량 차이와 단가 차이가 어떻게 움직여 왔는지가 한 화면에 남습니다.

이 표는 재무에서 쓰는 차이 분석과 같은 구조입니다. 재무 AI 도구들이 자동으로 만들어 주는 것도 결국 이 두 숫자이고, 손으로 한 번 만들어 보면 자동화된 결과를 검증할 수 있게 됩니다. 전체 순서는 엑셀 매출 관리 일곱 단계에 있습니다.

자주 받는 질문

Q. 전년 동월이 없는 신규 품목은 어떻게 표시하나요

A. 증감률은 신규로 두고 증감액에는 올해 금액을 그대로 넣습니다. 신규 품목의 금액 합계를 따로 한 줄 두면 성장이 기존 품목에서 왔는지 신제품에서 왔는지 나뉩니다.

Q. 엑셀 전년대비 계산을 월 누계로도 봐야 하나요

A. 둘 다 봅니다. 당월만 보면 한 달 특이 요인에 흔들리고, 누계만 보면 최근 변화가 묻힙니다. 열을 두 개 두고 나란히 놓는 편이 낫습니다.

Q. 수량 차이와 단가 차이 계산에서 기준 연도를 어느 쪽으로 잡나요

A. 수량 차이에 작년 단가를, 단가 차이에 올해 수량을 쓰는 조합이 일반적입니다. 반대로 잡아도 합계는 같지만 두 숫자의 크기가 달라지므로 한 번 정하면 계속 같은 방식으로 씁니다.

Q. 엑셀 목표달성률이 100%를 넘으면 그래프가 깨집니다

A. 조건부 서식 데이터 막대를 쓰면 최댓값이 자동으로 잡혀 100% 초과가 눌립니다. 최댓값을 1.2 같은 고정값으로 지정하면 초과분이 보입니다.

증감액을 수량 차이와 단가 차이로 나눠 적은 달부터 회의가 짧아집니다. 그 두 숫자부터 만들어 두려 합니다.