엑셀 부가세 계산 1원 차이? 결제금액에서 공급가액·부가세 나누는 초보 정산법

엑셀 부가세 계산을 하다 보면 결제금액은 맞는데 공급가액과 부가세 합계가 1원씩 안 맞는 일이 자주 생깁니다. 특히 쇼핑몰 정산표, 카드매출 내역, 거래처별 매출 보고서를 만들 때 “부가세 포함 금액에서 공급가액을 어떻게 나누지?”라는 질문이 꼭 나옵니다.

초보자분들이 가장 많이 헷갈리는 지점은 10%를 어디에 곱하느냐입니다. 부가세 별도 금액에 10%를 더하는 상황과, 이미 부가세가 포함된 결제금액에서 부가세를 떼어내는 상황은 계산식이 다릅니다. 오늘은 실제 정산표처럼 작은 표를 만들어 놓고, 어느 셀에 어떤 수식을 넣는지 차근차근 정리해 보겠습니다.

결제금액만 있는 매출 정산표에서 공급가액과 부가세를 나누는 상황

아래처럼 카드사나 쇼핑몰에서 내려받은 매출 자료가 있다고 가정해 보겠습니다. A열은 주문일, B열은 거래처, C열은 품목, D열은 결제금액입니다. 여기서 D열 결제금액은 이미 부가세가 포함된 금액입니다.

우리가 채워야 할 곳은 E열 공급가액, F열 부가세, G열 검산입니다. 데이터는 4행부터 9행까지 있고, H2 셀에는 부가세율인 10%를 입력해 둡니다.

예시 데이터는 아래처럼 입력해 보겠습니다. 실제 파일에서는 4행부터 데이터가 시작되는 경우가 많으니, 표 제목은 3행에 두고 계산식은 4행부터 넣는 방식으로 설명하겠습니다.

부가세 포함 금액에는 10%를 바로 곱하면 안 됩니다

가장 흔한 실수가 D4 셀의 결제금액에 바로 10%를 곱해서 부가세를 구하는 방식입니다. 예를 들어 결제금액 165,000원에 10%를 곱하면 16,500원이 나옵니다. 얼핏 맞는 것처럼 보이지만, 이 계산은 공급가액이 165,000원이고 부가세를 별도로 더할 때의 계산입니다.

지금 D열의 결제금액은 이미 부가세가 들어간 총액입니다. 따라서 공급가액은 결제금액을 1.1로 나누어야 합니다. H2 셀에 10%를 넣어 두었다면, 1.1 대신 1+H2라고 쓰면 됩니다.

먼저 H2 셀에 10%를 입력하세요. 숫자 10을 입력하는 것이 아니라, 셀에 직접 10%라고 입력하는 것이 안전합니다. 만약 H2에 10을 입력하면 엑셀은 10%가 아니라 1000%처럼 계산해 버릴 수 있습니다.

E4 셀에 공급가액 수식을 넣고 아래로 복사합니다

E4 셀을 클릭한 뒤 아래 수식을 입력합니다. 여기서 D4는 같은 행의 결제금액이고, $H$2는 세율이 들어 있는 셀입니다. H2 앞뒤에 붙은 달러 표시($)는 수식을 아래로 복사해도 H2 셀이 움직이지 않게 고정하는 뜻입니다.

=IF(D4="","",ROUND(D4/(1+$H$2),0))

이 수식은 D4가 비어 있으면 빈칸으로 두고, 결제금액이 있으면 부가세 포함 금액을 1.1로 나눈 뒤 원 단위로 반올림합니다. ROUND 함수는 반올림 함수입니다. ROUND(계산값,0)이라고 쓰면 소수점 없이 원 단위 금액으로 정리됩니다.

수식을 넣은 뒤 E4 셀의 오른쪽 아래 모서리에 마우스를 올리면 작은 십자 모양이 보입니다. 그것을 더블클릭하거나 E9까지 끌어내리면 E5:E9에도 같은 방식으로 공급가액이 계산됩니다.

F4 셀의 부가세는 결제금액에서 공급가액을 빼는 방식이 안전합니다

부가세도 D4를 기준으로 직접 계산할 수는 있습니다. 하지만 실무 정산표에서는 공급가액과 부가세를 따로 반올림하다가 합계가 1원씩 틀어지는 경우가 있습니다. 그래서 초보자에게 가장 안전한 방식은 부가세 = 결제금액 - 공급가액입니다.

F4 셀에 아래 수식을 입력합니다.

=IF(D4="","",D4-E4)

이렇게 하면 E4에서 반올림한 공급가액을 기준으로 부가세가 계산됩니다. 결과적으로 E열 공급가액과 F열 부가세를 더했을 때 D열 결제금액과 정확히 맞습니다. F4 수식도 F9까지 복사합니다.

G열에서 1원 차이를 바로 확인해 둡니다

정산표는 계산만 하는 것보다 검산 열을 하나 만들어 두는 것이 좋습니다. 나중에 행이 많아지면 어느 줄에서 금액이 틀어졌는지 찾기 어렵기 때문입니다. G4 셀에는 아래 수식을 넣습니다.

=IF(D4="","",IF(D4=E4+F4,"일치","확인"))

이 수식은 D4 결제금액과 E4+F4가 같으면 “일치”라고 표시하고, 다르면 “확인”이라고 표시합니다. G열에 “확인”이 보이면 해당 행의 결제금액, 공급가액, 부가세 중 하나를 다시 봐야 합니다.

초보자분들은 여기서 IF 함수가 어렵게 느껴질 수 있습니다. IF는 조건을 보고 결과를 나누는 함수입니다. 위 수식을 말로 풀면 “D4가 비어 있으면 빈칸으로 두고, 그렇지 않으면 D4와 E4+F4가 같은지 비교해서 같으면 일치, 다르면 확인이라고 표시해 줘”라는 뜻입니다.

거래처별 월 매출 합계까지 같이 보고 싶을 때

정산 업무에서는 한 줄 계산만 끝내지 않고, 거래처별 월 매출 합계도 같이 확인하는 경우가 많습니다. 예를 들어 I2 셀에는 거래처명, J2 셀에는 기준월, K2 셀에는 해당 거래처의 월 결제금액 합계를 표시한다고 해보겠습니다.

K2 셀에는 SUMIFS 함수를 사용합니다. SUMIFS는 조건을 여러 개 걸어서 합계를 구하는 함수입니다. 여기서는 거래처가 I2와 같고, 주문일이 J2 이상이면서 다음 달 1일보다 작은 금액만 더합니다.

=SUMIFS($D$4:$D$9,$B$4:$B$9,$I$2,$A$4:$A$9,">="&$J$2,$A$4:$A$9,"<"&DATE(YEAR($J$2),MONTH($J$2)+1,1))

이 수식에서 $D$4:$D$9는 더할 결제금액 범위입니다. $B$4:$B$9는 거래처 범위이고, $A$4:$A$9는 주문일 범위입니다. 실제 파일에서 데이터가 500행까지 있다면 $D$4:$D$500, $B$4:$B$500, $A$4:$A$500처럼 끝 행만 바꾸면 됩니다.

DATE(YEAR($J$2),MONTH($J$2)+1,1)은 기준월의 다음 달 1일을 만드는 부분입니다. J2가 2026-07-01이면 다음 달 1일인 2026-08-01을 만들어 줍니다. 그래서 7월 1일부터 8월 1일 전까지, 즉 7월 전체 매출만 합산됩니다.

계산이 이상할 때는 이 순서로 확인하세요

첫째, H2 셀에 10이 아니라 10%가 들어갔는지 확인합니다. 셀을 클릭했을 때 수식 입력줄에 10% 또는 0.1로 보이면 정상입니다. 그냥 10이라고 들어가 있으면 세율 계산이 크게 틀어집니다.

둘째, D열 결제금액이 숫자인지 확인합니다. 금액이 왼쪽 정렬되어 있거나 초록색 삼각형 표시가 보이면 텍스트로 들어간 숫자일 수 있습니다. 이 경우 SUM이나 SUMIFS 합계가 이상하게 나오거나 0으로 보일 수 있습니다.

셋째, 공급가액과 부가세를 각각 따로 ROUND로 계산하고 있지 않은지 봅니다. 공급가액도 반올림하고 부가세도 별도 반올림하면 둘을 더했을 때 결제금액과 1원 차이가 날 수 있습니다. 그래서 공급가액을 먼저 구하고, 부가세는 결제금액에서 공급가액을 빼는 방식이 실무에서는 편합니다.

넷째, 수식을 아래로 복사했을 때 H2가 H3, H4처럼 내려가지 않았는지 확인합니다. 세율 셀은 $H$2처럼 고정해야 합니다. 만약 수식에 H2만 써두면 복사 과정에서 참조 셀이 움직여 결과가 달라질 수 있습니다.

별도 금액에서 부가세를 더하는 경우는 수식이 다릅니다

한 가지 더 구분해 두면 좋습니다. 지금까지는 D열 결제금액이 부가세 포함 금액인 경우였습니다. 반대로 견적서나 발주서처럼 공급가액이 먼저 있고 부가세를 더해야 하는 경우에는 계산 방식이 달라집니다.

예를 들어 D4가 공급가액이라면 부가세는 아래처럼 계산합니다.

=ROUND(D4*$H$2,0)

그리고 합계금액은 공급가액과 부가세를 더하면 됩니다.

=D4+E4

정리하면, 이미 총액에 세금이 들어 있으면 총액 ÷ 1.1로 공급가액을 나누고, 세금이 빠진 금액에서 세금을 붙이는 상황이면 공급가액 × 10%를 사용합니다. 이 둘을 섞어 쓰면 정산표 금액이 계속 어긋납니다.

실무 파일에 적용할 때 바꾸면 되는 셀 주소

내 파일에 바로 적용할 때는 세 곳만 먼저 확인하면 됩니다. 결제금액이 들어 있는 열, 세율이 들어 있는 셀, 데이터가 시작되는 행입니다. 예시에서는 결제금액이 D열, 세율이 H2, 첫 데이터가 4행이었습니다.

만약 내 파일에서 결제금액이 F열이고 세율이 M1 셀이라면 공급가액 수식의 D4를 F4로, $H$2를 $M$1로 바꾸면 됩니다. 데이터가 2행부터 시작한다면 첫 수식도 E2나 해당 결과 열의 2행에 넣으면 됩니다.

부가세 계산은 어려운 함수보다 상황 구분이 더 중요합니다. 결제금액이 부가세 포함인지, 공급가액에서 부가세를 더해야 하는지 먼저 확인한 뒤 수식을 넣으면 1원 차이 때문에 정산표를 다시 뜯어보는 시간을 많이 줄일 수 있습니다.