엑셀 비용 배부 합계 안 맞을 때: 부서코드 앞자리 0·배부율 100%·ROUND 오차 체크리스트

엑셀 비용 배부 합계가 원장 금액과 몇 원씩 안 맞거나, 특정 부서만 SUMIFS 결과가 0으로 나오는 경우가 꽤 많습니다. 특히 공통비를 프로젝트별로 나누는 파일에서는 부서코드 앞자리 0, 귀속월 날짜 형식, 배부율 합계 100%, ROUND 반올림 차이가 한꺼번에 얽혀서 원인을 찾기 어려워집니다.

이번 예시는 월말에 자주 만드는 부서별 공통비 프로젝트 배부표입니다. 원장에는 비용이 한 줄씩 들어 있고, 별도 배부율표를 기준으로 프로젝트별 배부액을 계산한 뒤, 직접비와 합쳐 월별 프로젝트 원가를 만드는 상황으로 정리해 보겠습니다.

어떤 파일 구조에서 문제가 생기나?

예시 파일에는 시트가 3개 있다고 가정하겠습니다. 첫 번째 시트는 비용원장, 두 번째 시트는 배부율, 세 번째 시트는 월배부입니다. 실제 회사 파일에서는 시트명이 조금 달라도 괜찮지만, 열의 의미는 아래처럼 맞춰 두면 수식이 훨씬 안정적으로 돌아갑니다.

비용원장 시트의 예시 데이터는 아래와 같습니다. A열 전표일은 날짜처럼 보이지만, 어떤 행은 실제 날짜이고 어떤 행은 20260803 같은 8자리 숫자로 들어와 있을 수 있습니다. E열 부서코드도 0012가 12로 바뀌어 있으면 SUMIFS 조건에서 바로 어긋납니다.

SUMIFS가 0으로 나올 때 제일 먼저 볼 열은?

월배부 시트에서 I2에는 기준월을 입력합니다. 예를 들어 I2에는 2026-08-01, J2에는 배부할 부서코드인 0012를 입력해 둡니다. 그런데 원장 A열이 텍스트 날짜이거나 E열 부서코드가 숫자 12로 들어가 있으면, 눈으로는 맞아 보여도 조건 합계가 0이 나올 수 있습니다.

그래서 비용원장 시트에 보조열을 먼저 만듭니다. H열은 귀속월, I열은 정리부서코드, J열은 정리프로젝트코드로 사용합니다. 원본을 억지로 고치기보다 보조열에서 기준을 통일하는 방식이 실무에서는 더 안전합니다.

비용원장 시트 H5에 아래 수식을 넣고 H5000까지 내려 복사합니다. 실제 날짜와 8자리 숫자 날짜가 섞여 있을 때를 대비해 IFERROR, DATE, LEFT, MID, TEXT를 함께 사용합니다.

=IFERROR(DATE(LEFT(TEXT(A5,"yyyymmdd"),4),MID(TEXT(A5,"yyyymmdd"),5,2),1),DATE(LEFT(A5,4),MID(A5,5,2),1))

비용원장 시트 I5에는 부서코드를 4자리로 맞추는 수식을 넣습니다. 원본 E열이 12로 들어와도 0012로 정리됩니다.

=IFERROR(TEXT(E5,"0000"),E5)

프로젝트코드도 앞자리 0이 있는 코드 체계를 쓰는 회사라면 J5에 정리해 두는 것이 좋습니다. 프로젝트코드가 P0001처럼 문자와 숫자가 섞여 있다면 아래처럼 빈칸은 빈칸으로 두고, 값이 있는 경우만 정리합니다.

=IF(F5="","",TEXT(F5,"00000"))

다만 프로젝트코드가 P0001처럼 알파벳이 포함된 코드라면 TEXT 함수보다 원본 코드 체계를 그대로 맞추는 편이 낫습니다. 이 경우에는 F열을 텍스트 형식으로 맞추고, 코드 앞뒤 공백이 없는지 확인하세요.

배부율표는 100%인지 COUNTIFS와 SUMIFS로 확인한다

배부율 시트는 A4:D200 범위를 사용합니다. A열 귀속월은 2026-08-01 같은 월 첫날 날짜, B열 부서코드는 0012, C열 프로젝트코드는 P0001, D열 배부율은 35%가 아니라 0.35로 입력합니다.

월배부 시트 K3에는 현재 기준월과 부서코드의 배부율 합계를 확인하는 수식을 넣습니다. 결과가 1이 아니면 배부액이 원장 금액과 맞을 수 없습니다.

=SUMIFS(배부율!$D$4:$D$200,배부율!$A$4:$A$200,$I$2,배부율!$B$4:$B$200,$J$2)

중복으로 같은 프로젝트가 두 번 들어간 경우도 확인해야 합니다. 월배부 시트 A5에 프로젝트코드가 있고, C5에 해당 프로젝트의 배부율을 가져올 예정이라면, 먼저 배부율표에서 중복 건수를 확인하는 열을 하나 만들어도 좋습니다. 배부율 시트 E4에 아래 수식을 넣으면 같은 월, 같은 부서, 같은 프로젝트가 몇 번 등록됐는지 바로 보입니다.

=COUNTIFS($A$4:$A$200,A4,$B$4:$B$200,B4,$C$4:$C$200,C4)

E열 결과가 2 이상이면 같은 조건의 배부율이 중복된 것입니다. 이 상태에서 SUMIFS로 배부율을 가져오면 의도보다 큰 비율이 적용될 수 있으니, 먼저 배부율표부터 정리해야 합니다.

월배부 시트에서 공통비와 직접비를 나누어 계산하기

월배부 시트의 조건 셀은 I2와 J2입니다. I2에는 기준월, J2에는 부서코드를 입력합니다. 결과표는 A5:G30 범위를 사용하고, A열에는 프로젝트코드를 입력해 둡니다.

먼저 월배부 시트 K2에 공통비 풀을 계산합니다. 비용원장 G열이 Y인 행만 공통비로 보고, H열 귀속월과 I열 정리부서코드가 조건과 맞는 금액을 합산합니다.

=SUMIFS(비용원장!$D$5:$D$5000,비용원장!$H$5:$H$5000,$I$2,비용원장!$I$5:$I$5000,$J$2,비용원장!$G$5:$G$5000,"Y")

월배부 시트 B5에는 프로젝트명을 가져옵니다. Microsoft 365나 Excel 2021 이후 버전에서는 XLOOKUP을 쓰면 편하고, 혹시 파일을 여러 사람이 같이 쓰는 환경이라면 VLOOKUP 대체식까지 IFERROR로 감싸 두면 좋습니다.

=IFERROR(XLOOKUP($A5,프로젝트마스터!$A$4:$A$200,프로젝트마스터!$B$4:$B$200),IFERROR(VLOOKUP($A5,프로젝트마스터!$A$4:$B$200,2,0),"프로젝트코드 확인"))

월배부 시트 C5에는 해당 프로젝트의 배부율을 가져옵니다. 같은 월, 같은 부서, 같은 프로젝트 조건을 모두 걸어야 다른 달의 배부율이 섞이지 않습니다.

=SUMIFS(배부율!$D$4:$D$200,배부율!$A$4:$A$200,$I$2,배부율!$B$4:$B$200,$J$2,배부율!$C$4:$C$200,$A5)

D5에는 공통비 풀에 배부율을 곱한 뒤 원 단위로 반올림합니다. 회사 기준이 십 원 단위라면 ROUND의 마지막 인수를 -1로 바꾸면 됩니다.

=ROUND($K$2*$C5,0)

F5에는 직접비를 계산합니다. 비용원장에서 공통비여부가 N이고, 정리프로젝트코드가 A5 프로젝트와 같은 금액만 합산합니다.

=SUMIFS(비용원장!$D$5:$D$5000,비용원장!$H$5:$H$5000,$I$2,비용원장!$J$5:$J$5000,$A5,비용원장!$G$5:$G$5000,"N")

ROUND 때문에 1원, 2원 차이 날 때 어떻게 맞출까?

배부율 합계가 정확히 1이어도 프로젝트별로 ROUND를 각각 적용하면 합계가 공통비 풀과 1원 또는 몇 원 차이 날 수 있습니다. 이 차이는 수식 오류라기보다 반올림 방식 때문에 생기는 정상적인 차이입니다. 다만 보고서에서는 원장 금액과 결과표 합계가 맞아야 하므로 조정 규칙을 정해야 합니다.

월배부 시트 K4에는 D열의 반올림 배부액 합계를 넣습니다.

=SUM($D$5:$D$30)

K5에는 공통비 풀과 반올림 배부액 합계의 차이를 계산합니다.

=$K$2-$K$4

K6에는 조정 차이를 반영할 프로젝트코드를 지정합니다. 보통 배부율이 가장 큰 프로젝트에 차이를 몰아주는 방식이 실무에서 설명하기 쉽습니다. 아래 수식은 C5:C30 중 배부율이 가장 큰 행의 프로젝트코드를 가져옵니다.

=INDEX($A$5:$A$30,MATCH(MAX($C$5:$C$30),$C$5:$C$30,0))

이제 월배부 시트 E5에 조정후 배부액을 계산합니다. D5의 반올림 배부액에, K6에 지정된 프로젝트일 때만 K5 차이를 더합니다.

=D5+IF($A5=$K$6,$K$5,0)

마지막으로 G5에는 조정후 배부액과 직접비를 합산합니다.

=E5+F5

실무에서 자주 놓치는 확인표

수식이 맞는데도 결과가 틀릴 때는 대부분 아래 항목 중 하나입니다. 특히 전월 파일을 복사해서 이번 달 파일을 만들 때, I2 기준월만 바꾸고 배부율표의 귀속월은 그대로 두는 실수가 많습니다.

여러 달 파일을 반복 점검한다면 VBA로 색상 표시하기

매월 같은 검사를 반복한다면 간단한 VBA로 빈 귀속월, 부서코드 자리수 오류, 공통비여부 입력 오류를 색상으로 표시할 수 있습니다. 아래 코드는 비용원장 시트의 H:J열과 월배부 시트의 K3, K5를 점검용으로 칠합니다. 수식을 바꾸지는 않고 셀 색상만 변경합니다.

실행 전에는 파일을 반드시 다른 이름으로 저장해 두세요. 매크로 실행 후에는 일반적인 실행 취소가 되지 않을 수 있습니다. 되돌리려면 저장하지 않고 닫거나, 홈 탭에서 채우기 색을 없음으로 바꾸면 됩니다.

Sub 비용배부_점검색상표시()
    Dim ws As Worksheet
    Dim wr As Worksheet
    Dim lastRow As Long
    Dim r As Long

    Set ws = Worksheets("비용원장")
    Set wr = Worksheets("월배부")

    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ws.Range("H5:J" & lastRow).Interior.Pattern = xlNone
    wr.Range("K3:K5").Interior.Pattern = xlNone

    For r = 5 To lastRow
        If ws.Cells(r, "A").Value <> "" Then
            If ws.Cells(r, "H").Value = "" Then
                ws.Cells(r, "H").Interior.Color = RGB(255, 230, 153)
            End If

            If Len(ws.Cells(r, "I").Text) <> 4 Then
                ws.Cells(r, "I").Interior.Color = RGB(255, 199, 206)
            End If

            If ws.Cells(r, "G").Value <> "Y" And ws.Cells(r, "G").Value <> "N" Then
                ws.Cells(r, "G").Interior.Color = RGB(255, 199, 206)
            End If
        End If
    Next r

    If Round(wr.Range("K3").Value, 6) <> 1 Then
        wr.Range("K3").Interior.Color = RGB(255, 199, 206)
    End If

    If wr.Range("K5").Value <> 0 Then
        wr.Range("K5").Interior.Color = RGB(255, 230, 153)
    End If

    MsgBox "비용 배부 점검이 끝났습니다. 표시된 셀을 확인하세요.", vbInformation
End Sub

이 코드는 시트명이 정확히 비용원장, 월배부일 때 바로 실행됩니다. 시트명이 다르면 코드의 Worksheets 안 이름만 본인 파일에 맞게 바꾸면 됩니다. 점검 대상 행은 A열 마지막 데이터 행까지이므로, 원장 아래쪽에 불필요한 메모가 있으면 먼저 지워 두는 편이 좋습니다.

적용 전에 확인할 것

이 방식의 핵심은 원본 데이터를 바로 믿지 않고, 귀속월과 코드를 보조열에서 먼저 통일하는 것입니다. 그다음 SUMIFS로 공통비 풀과 직접비를 나누고, 배부율표는 COUNTIFS로 중복을 잡고, 마지막에 ROUND 차이를 한 프로젝트에 반영하면 원장 합계와 결과표 합계를 안정적으로 맞출 수 있습니다.

파일을 고칠 때는 먼저 월배부 K2, K3, K4, K5 네 칸만 봐도 방향이 잡힙니다. K2는 원장 공통비 합계, K3은 배부율 100% 여부, K4는 반올림 배부액 합계, K5는 조정해야 할 차이입니다. 이 네 값이 설명 가능하게 맞으면 프로젝트별 원가 보고서도 훨씬 덜 흔들립니다.