재고 실사 후 장부와 차이 날 때, 엑셀 장부에 차이를 맞추는 법 (실사일 기준 SUMIFS)

재고를 세어 보니 장부와 다를 때 적을 차이는 세어 본 수량 - 실사일까지의 장부 수량입니다. 현재고 칸이 날짜와 상관없이 적힌 기록을 모두 더하는 장부라면, 실사한 날보다 뒤의 기록(예: 1월 판매)이 이미 적혀 있을 때 그 칸은 실사일의 수량이 아니어서 비교 기준으로 쓸 수 없습니다. SUMIFS에 "<="&실사일 조건을 걸어 실사일까지의 수량을 따로 구하고, 그 값과의 차이를 조정 줄로 실사일 날짜에 적습니다. 아래 예시 장부에서 머그컵은 현재고와 비교한 차이가 9, 실사일 기준 차이가 -5였고, 9를 넣으면 12월 말 장부가 세어 본 수량보다 14개 많아졌습니다.

예시 장부

이 글을 위해 만든 샘플 장부입니다. 기록 시트는 날짜, 품목, 구분(입고·출고·조정), 수량 네 열이고, 실사표 시트는 품목별로 처음 재고, 실사일까지 장부 수량, 전체 합산 수량, 세어 본 수량, 차이를 둡니다. 품목은 머그컵·접시·유리컵 세 가지이고, 기록은 2026년 11월 1일부터 2027년 1월 30일까지 54줄입니다. 머그컵과 접시는 1월 기록이 있고 유리컵은 1월 기록이 없습니다. 처음 재고는 머그컵 50, 접시 60, 유리컵 60입니다. 실사일은 2026-12-31이고 세어 본 수량은 머그컵 29, 접시 46, 유리컵 0입니다.

장부 수량은 처음 재고에 입고를 더하고 출고를 빼고 조정은 부호대로 더한 값입니다. 실사일까지의 수량은 SUMIFS로 구합니다. 실사표 2행(머그컵)의 수식은 이렇습니다.

C2: =$B2+SUMIFS(기록!$D$2:$D$500,기록!$B$2:$B$500,$A2,기록!$C$2:$C$500,"입고",기록!$A$2:$A$500,"<="&$I$2)-SUMIFS(기록!$D$2:$D$500,기록!$B$2:$B$500,$A2,기록!$C$2:$C$500,"출고",기록!$A$2:$A$500,"<="&$I$2)+SUMIFS(기록!$D$2:$D$500,기록!$B$2:$B$500,$A2,기록!$C$2:$C$500,"조정",기록!$A$2:$A$500,"<="&$I$2)
F2: =E2-C2
G2: =E2-D2

C열이 실사일까지의 장부 수량이고 $I$2에 실사일이 있습니다. D열은 C열 수식에서 날짜 조건(,기록!$A$2:$A$500,"<="&$I$2)만 뺀 같은 수식이어서 전체 합산 수량이 됩니다. F열은 실사일 기준 차이, G열은 비교용으로 현재고(전체 합산)와 비교한 차이입니다.

조정 줄을 넣기 전

조정 줄이 없는 장부의 실사표입니다.

[A. 기록만 있는 장부(조정 줄 없음)]
품목 | 처음 재고 | 실사일까지 장부 | 전체 합산 | 세어 본 | 차이(실사일까지 기준) | 현재고 기준 차이
머그컵 | 50 | 34 | 20 | 29 | -5 | 9
접시 | 60 | 47 | 23 | 46 | -1 | 23
유리컵 | 60 | 2 | 2 | 0 | -2 | -2
숫자 칸 54, 글자 칸 0, 오류 표시 칸 0

머그컵의 12월 31일 장부는 34개, 전체 합산은 20개입니다. 그 사이 14개 차이는 2027년 1월 판매 기록(3개, 5개, 6개)입니다. 접시도 같은 이유로 47과 23 사이가 24개입니다. 유리컵은 1월 기록이 없어 둘이 같습니다. 세어 본 머그컵 29개와 비교할 값은 34이고, 차이는 29 - 34 = -5입니다. 전체 합산 20과 비교하면 9가 되어 부호까지 달라집니다.

차이를 맞춘 장부

실사일 기준 차이 -5, -1, -2를 구분 조정으로 실사일(2026-12-31) 날짜에 한 줄씩 넣었습니다.

[B. 조정 줄에 차이(세어 본 - 실사일까지)를 실사일로 넣음]
품목 | 처음 재고 | 실사일까지 장부 | 전체 합산 | 세어 본 | 차이(실사일까지 기준) | 현재고 기준 차이
머그컵 | 50 | 29 | 15 | 29 | 0 | 14
접시 | 60 | 46 | 22 | 46 | 0 | 24
유리컵 | 60 | 0 | 0 | 0 | 0 | 0
숫자 칸 57, 글자 칸 0, 오류 표시 칸 0

실사일까지의 장부 수량이 29, 46, 0으로 세어 본 수량과 같아졌고 실사일 기준 차이는 모두 0입니다. 전체 합산은 15, 22, 0이 되었습니다. 1월 판매 14개, 24개가 그대로 빠져 있어서 이 값은 세어 본 수량과 같지 않은 것이 맞습니다. 이때부터 오른쪽 현재고 기준 차이 열의 값은 쓰지 않습니다. 조정을 한 번 넣은 뒤에는 실사일까지의 수량만 세어 본 수량과 비교합니다.

현재고와 비교해 넣었을 때

같은 장부에 현재고 기준 차이 9, 23, -2를 조정 줄로 넣어 봤습니다.

[C. 조정 줄에 현재고 기준 차이를 실사일로 넣음]
품목 | 처음 재고 | 실사일까지 장부 | 전체 합산 | 세어 본 | 차이(실사일까지 기준) | 현재고 기준 차이
머그컵 | 50 | 43 | 29 | 29 | -14 | 0
접시 | 60 | 70 | 46 | 46 | -24 | 0
유리컵 | 60 | 0 | 0 | 0 | 0 | 0
숫자 칸 57, 글자 칸 0, 오류 표시 칸 0

전체 합산(현재고)은 29, 46, 0으로 세어 본 수량과 같아 보입니다. 그런데 12월 31일 장부는 머그컵 43, 접시 70입니다. 세어 본 수량보다 머그컵이 14개, 접시가 24개 많습니다. 오류 표시는 없습니다. 현재고 칸만 보면 맞아서, 월말 수량이나 12월 재고 표를 따로 보지 않으면 놓치기 쉽습니다. 유리컵은 1월 기록이 없어서 두 방식의 값이 같고 어긋나지 않았습니다. 실사일 이후 기록이 있는 품목에서만 어긋납니다.

세어 본 수량을 그대로 적었을 때

조정 줄에 차이 대신 세어 본 수량(29, 46, 0)을 그대로 적은 경우입니다.

[D. 조정 줄에 세어 본 수량을 그대로 넣음]
품목 | 처음 재고 | 실사일까지 장부 | 전체 합산 | 세어 본 | 차이(실사일까지 기준) | 현재고 기준 차이
머그컵 | 50 | 63 | 49 | 29 | -34 | -20
접시 | 60 | 93 | 69 | 46 | -47 | -23
유리컵 | 60 | 2 | 2 | 0 | -2 | -2
숫자 칸 57, 글자 칸 0, 오류 표시 칸 0

실사일까지의 장부 수량이 머그컵 63, 접시 93으로 커졌지만 오류 표시는 없습니다. 장부는 입력된 숫자를 더할 뿐이어서, 그 숫자가 차이인지 세어 본 수량인지 알 수 없습니다. 조정 줄에 적는 값은 세어 본 수량이 아니라 차이라는 점을 적어 두고, 조정 뒤에 실사일까지의 장부 수량이 세어 본 수량과 같아졌는지 확인하는 것으로 잡습니다. 위 표에서는 63과 29가 달라서 바로 드러납니다. 유리컵은 세어 본 수량이 0이라 0을 더한 셈이 되어 장부가 2로 남았고, 차이 -2도 그대로입니다.

수량이 글자로 들어갔을 때

조정 수량이 숫자가 아니라 글자로 들어가면 이번 실행의 SUMIFS는 그 줄을 더하지 않았습니다. 시험은 B 장부에서 유리컵 조정 수량 하나를, 마이너스 기호 자리에 유니코드 −(U+2212)를 쓴 글자 −2로 바꿔 넣은 것입니다. 화면에서는 -2와 비슷해 보입니다.

[E. B에서 유리컵 조정 수량을 글자 '−2'로 넣음]
품목 | 처음 재고 | 실사일까지 장부 | 전체 합산 | 세어 본 | 차이(실사일까지 기준) | 현재고 기준 차이
머그컵 | 50 | 29 | 15 | 29 | 0 | 14
접시 | 60 | 46 | 22 | 46 | 0 | 24
유리컵 | 60 | 2 | 2 | 0 | -2 | -2
숫자 칸 56, 글자 칸 1, 오류 표시 칸 0
글자로 표시된 줄: 58줄 유리컵 수량 '−2' 코드 ['0x2212', '0x32']

유리컵은 조정이 빠져서 B 장부의 0이 아니라 2로 돌아갔고, 실사일 기준 차이가 -2로 다시 나타났습니다. 오류 표시는 없습니다. 이 −2는 파이썬의 openpyxl로 글자로 써넣은 값입니다. 손으로 입력한 수량이 글자로 남았는지는 아래 방법으로 확인할 수 있습니다.

방법은 두 가지입니다. 실사표에서 숫자 칸 수를 =COUNT(기록!D2:D500), 글자 칸 수를 =SUMPRODUCT(--ISTEXT(기록!D2:D500))로 세어 두면 글자 칸이 1이라고 나옵니다. 줄까지 찾으려면 기록 시트 옆 열에 =IF(ISTEXT(D2),"글자","")를 아래로 채웁니다. 위 실행에서는 58줄이 "글자"로 표시되었습니다. 찾은 칸은 숫자로 다시 입력합니다.

세고 난 뒤 같은 날 나간 물건

"<="&실사일 조건은 실사일에 적힌 기록을 모두 포함합니다. 아침에 세고 그날 판 것까지 실사일 날짜로 적으면, 세고 난 뒤 나간 수량이 장부에서만 빠져서 차이가 틀어집니다. 머그컵을 29개로 센 뒤 같은 날 3개가 나갔고, 그 출고를 12월 31일로 적은 경우입니다.

[F. 머그컵을 세고 난 뒤 12/31에 출고 3을 적음, 기준일 12/31]
품목 | 처음 재고 | 실사일까지 장부 | 전체 합산 | 세어 본 | 차이(실사일까지 기준) | 현재고 기준 차이
머그컵 | 50 | 31 | 17 | 29 | -2 | 12
접시 | 60 | 47 | 23 | 46 | -1 | 23
유리컵 | 60 | 2 | 2 | 0 | -2 | -2
숫자 칸 55, 글자 칸 0, 오류 표시 칸 0

머그컵 차이가 -5가 아니라 -2로 나왔습니다. 세고 난 뒤 나간 3개만큼 줄어든 값입니다. 이 -2를 그대로 넣으면 장부는 29가 되는데, 29개에서 3개가 더 나갔으니 맞는 값은 26입니다.

문을 열기 전에 세었다면 기준일 칸을 전날로 둡니다. 세는 시점의 장부는 전날까지의 기록이기 때문입니다.

[G. F와 같은 기록, 기준일을 전날 12/30으로 둠]
품목 | 처음 재고 | 실사일까지 장부 | 전체 합산 | 세어 본 | 차이(실사일까지 기준) | 현재고 기준 차이
머그컵 | 50 | 34 | 17 | 29 | -5 | 12
접시 | 60 | 47 | 23 | 46 | -1 | 23
유리컵 | 60 | 2 | 2 | 0 | -2 | -2
숫자 칸 55, 글자 칸 0, 오류 표시 칸 0

머그컵이 34, 차이가 -5로 돌아왔습니다. 수식은 기준일까지의 기록만 더하므로 조정 줄의 날짜도 기준일로 적습니다. 아래는 -5, -1, -2를 12월 30일 날짜로 넣은 결과입니다.

[H. F에 G의 차이를 12/30 날짜 조정 줄로 넣음, 기준일 12/30]
품목 | 처음 재고 | 실사일까지 장부 | 전체 합산 | 세어 본 | 차이(실사일까지 기준) | 현재고 기준 차이
머그컵 | 50 | 29 | 12 | 29 | 0 | 17
접시 | 60 | 46 | 22 | 46 | 0 | 24
유리컵 | 60 | 0 | 0 | 0 | 0 | 0
숫자 칸 58, 글자 칸 0, 오류 표시 칸 0

세 품목 모두 세어 본 수량과 같아졌고 차이는 0입니다. 기준일을 12월 31일로 바꾸면 세고 난 뒤 나간 3개까지 들어간 장부가 나옵니다.

[I. H와 같은 기록, 기준일을 12/31로 바꿈]
품목 | 처음 재고 | 실사일까지 장부 | 전체 합산 | 세어 본 | 차이(실사일까지 기준) | 현재고 기준 차이
머그컵 | 50 | 26 | 12 | 29 | 3 | 17
접시 | 60 | 46 | 22 | 46 | 0 | 24
유리컵 | 60 | 0 | 0 | 0 | 0 | 0
숫자 칸 58, 글자 칸 0, 오류 표시 칸 0

머그컵 장부가 26으로 맞습니다. 장부 26이 세어 본 29와 3 다른 것은 세고 난 뒤 나간 3개 때문이라, 이 경우에는 차이가 0이 되지 않는 것이 맞습니다. 영업 중에 세어서 같은 날 세기 전 기록과 뒤 기록이 섞이면 날짜로는 가를 수 없습니다. 그때는 장부 값에 세고 난 뒤 나간 수량을 더하고 들어온 수량을 빼서 비교합니다. 위 예시라면 31에 3을 더한 34입니다.

정리

기준일을 한 칸에 적고, SUMIFS의 날짜 조건 "<="&기준일로 기준일까지의 장부 수량을 구합니다. 기준일은 보통 실사일이고, 문을 열기 전에 세었다면 전날입니다.
차이는 세어 본 수량 - 기준일까지의 장부 수량입니다. 현재고 칸의 값을 쓰지 않습니다.
차이를 조정 줄로 기준일 날짜에 넣고, 기준일까지의 장부 수량이 세어 본 수량과 같아졌는지 봅니다.
세고 난 뒤의 기록이 없는데 같아지지 않으면 조정 칸에 세어 본 수량을 그대로 적었거나 수량이 글자로 들어간 줄이 있는지 봅니다.

SUMIFS의 조건 인수를 Microsoft 문서는 "For example, criteria can be entered as 32, ">32", B4, "apples", or "32"."라고 설명합니다. 범위에 대해서는 "The Criteria_range argument must contain the same number of rows and columns as the Sum_range argument."라고 적습니다(SUMIFS function, Microsoft 지원). 위 수식은 범위를 모두 $2:$500으로 맞췄습니다. 날짜를 비교하는 조건을 칸과 &로 잇는 예는 같은 계열 함수인 COUNTIFS 문서에 있습니다. =COUNTIFS(A2:A7, "<" & A6,B2:B7,"<" & B4)이고, 앞 예시의 조건을 "using cell references instead of constants in the criteria"로 바꾼 것이라고 설명합니다(COUNTIFS function, Microsoft 지원).

이상은 Windows 11 Pro의 LibreOffice Calc 26.8.2.2에서 실행한 결과입니다. 엑셀 동작은 인용한 Microsoft 문서를 근거로 적었고, 글자 수량이 합계에서 빠지는 동작은 LibreOffice 결과입니다.