엑셀 거래처별 매출 합계: SUMIF로 끝나고, 안 끝나는 세 경우가 있습니다

거래처별 합계를 다 더했는데 총매출과 안 맞습니다.

수식은 맞습니다. 범위도 맞습니다. 그런데 몇십만 원이 비고, 어디서 빠졌는지 안 보입니다.

엑셀 거래처별 매출 합계에서 시간이 가는 곳은 수식이 아니고 이 차이입니다. 수식은 한 줄이고 차이 찾기는 매달 합니다.

엑셀 거래처별 매출 합계는 조건이 하나면 SUMIF로 끝납니다

거래처 코드가 A열에, 금액이 D열에 있다고 보면 이렇게 씁니다.

=SUMIF($A:$A,$F2,$D:$D)

F2에 조회할 거래처 코드를 두고 아래로 끌면 거래처별 합계가 나옵니다. 이름 대신 코드로 거는 이유는 표기가 흔들려도 한 줄로 모이기 때문입니다. 코드를 붙이는 방법은 거래처 관리에 적었습니다.

조건이 둘 이상이면 SUMIFS로 갑니다

거래처와 월을 같이 걸면 거래처가 행, 월이 열인 표가 한 번에 채워집니다.

=SUMIFS($D:$D,$A:$A,$F2,$B:$B,">="&G$1,$B:$B,"<"&EDATE(G$1,1))

G1에 그 달 1일을 넣어 두면 됩니다. 날짜를 텍스트로 비교하면 안 잡히는 경우가 많아서 이렇게 시작일과 다음 달 1일로 구간을 잡습니다.

매달 같은 모양의 보고서를 낸다면 이 엑셀 판매집계 수식으로 틀을 고정해 두는 편이 낫습니다. 피벗은 원본 범위가 바뀌면 다시 잡아야 하고, 열 순서가 바뀌면 서식이 흐트러집니다.

피벗은 이번 달만 뒤져 볼 때 씁니다

이번 달에 어디가 빠졌는지 이리저리 잘라 봐야 하면 피벗이 빠릅니다.

원본을 표(Ctrl+T)로 만들고 피벗을 만들면 줄이 늘어도 범위가 따라옵니다. 행에 거래처 코드, 값에 금액을 넣고, 값 표시 형식을 총합계 비율로 바꾸면 상위 거래처가 몇 퍼센트를 차지하는지 바로 보입니다.

집계 결과를 확인하는 용도로도 씁니다. 피벗으로 낸 숫자와 SUMIFS 표의 숫자가 같은지 대조하면 수식 범위 실수를 잡습니다.

부분합이 총합과 안 맞는 세 경우

여기가 이 글의 본론입니다.

① 코드가 안 붙은 줄. 미매칭으로 남은 줄은 어느 거래처 합계에도 안 들어갑니다. =SUMIF(A:A,"미매칭",D:D)로 그 금액이 얼마인지 먼저 봅니다. 대개 차이의 대부분이 여기서 나옵니다

② 취소·반품 줄의 부호. 반품이 양수로 들어와 있으면 매출이 부풀고, 취소 줄이 상태 열에만 표시되고 금액은 그대로면 두 번 세어집니다. =SUMIF(상태범위,"취소",금액범위)로 얼마가 섞였는지 확인합니다

③ 숫자로 보이는 텍스트. 채널에서 받은 금액 열이 텍스트면 SUMIF가 그 줄을 0으로 셉니다. =COUNT(D:D)와 =COUNTA(D:D)를 비교해서 개수가 다르면 텍스트가 섞인 것입니다

엑셀 거래처별 매출 합계를 낼 때 이 세 검산을 한 번에 붙여 두면 매달 어디서 벌어졌는지 십 초 만에 압니다.

검산 한 줄을 표 위에 고정합니다

집계표 맨 위에 이 줄을 둡니다.

=SUM(집계표범위)-SUM($D:$D)

0이 아니면 보고서를 내지 않습니다. 이 한 칸이 있는 것과 없는 것의 차이가 큽니다. 없으면 몇 달에 한 번씩 틀린 숫자가 그대로 나갑니다.

엑셀 매출 관리에서 매달 사람이 봐야 하는 숫자를 하나만 고르면 이 차액입니다.

집계보다 앞 단계에 시간이 걸립니다

수식을 쓰는 데는 십 분이 걸리고 차이를 찾는 데 두 시간이 갑니다. 그래서 엑셀 자동화를 붙일 곳도 집계가 아니라 그 앞입니다.

파워 쿼리에서 파일을 읽을 때 형식 변환과 코드 붙이기까지 단계로 저장해 두면, 다음 달에는 새로 고침 한 번에 검산이 0으로 나오는 표가 나옵니다. 전체 순서는 엑셀 매출 관리 일곱 단계에 정리해 뒀습니다.

자주 받는 질문

Q. SUMIF가 0만 반환합니다

A. 조회하는 값과 범위의 값이 실제로 다른 경우가 대부분입니다. 공백이나 형식 차이가 흔한 원인이고, SUMIF가 0이 나오는 경우에 진단 순서를 적어 뒀습니다.

Q. 거래처가 많아 수식이 느립니다

A. 범위를 $D:$D 같은 열 전체 대신 실제 데이터 범위나 표 이름으로 바꿉니다. 줄이 수만 개면 SUMIFS 여러 개보다 피벗 한 번이 빠릅니다.

Q. 월별 표에서 빈 달이 0으로 나옵니다

A. 정상입니다. 거래가 없던 달과 데이터가 안 들어온 달을 구분하려면 건수 표를 하나 더 만들어 옆에 둡니다. 금액이 0인데 건수도 0이면 거래가 없던 달입니다.

Q. 세금계산서 발행액과 이 집계가 안 맞습니다

A. 기준이 다릅니다. 주문일 기준 집계와 발행일 기준 발행액은 월말·월초에서 어긋납니다. 어느 기준으로 보고할지 먼저 정하고, 두 숫자의 차이를 매달 같은 방식으로 설명해 두면 다음 달에 다시 안 헤맵니다.

집계 합계와 원본 합계의 차액이 0인 달만 보고서를 냅니다. 그 한 칸부터 만들어 두려 합니다.