엑셀 재고 수량 안 맞을 때: 입고·출고·현재고를 SUMIFS로 계산하는 초보 정리법

엑셀 재고 수량 안 맞을 때 가장 먼저 확인할 것은 “입고는 더하고, 출고는 빼는 구조가 제대로 되어 있는지”입니다. 실무에서 재고표를 만들다 보면 필터로 입고만 보고, 다시 출고만 보고, 계산기로 빼다가 어느 순간 현재고가 맞지 않는 일이 자주 생깁니다.

특히 초보자분들이 많이 헷갈리는 부분은 원본 입출고 내역과 요약 재고표를 같은 표처럼 생각하는 지점입니다. 원본은 거래가 발생한 기록이고, 재고표는 그 기록을 품목별로 모아 계산한 결과입니다. 오늘은 작은 입출고표를 기준으로, 품목코드별 입고합계·출고합계·현재고를 SUMIFS 함수로 차근차근 계산해 보겠습니다.

입출고 내역은 거래가 한 줄씩 쌓이는 표입니다

먼저 엑셀 파일에 아래처럼 입출고 내역이 있다고 가정해 보겠습니다. 시트 이름은 꼭 같을 필요는 없지만, 설명을 쉽게 하기 위해 하나의 시트 안에서 작업하겠습니다.

입출고 원본 표는 A4:F11 범위에 있습니다. 제목 행은 4행이고, 실제 데이터는 5행부터 11행까지입니다.

예시 데이터는 아래처럼 입력되어 있다고 보겠습니다.

여기서 중요한 점은 출고 수량을 음수로 입력하지 않았다는 것입니다. 초보자용 재고표에서는 B열 구분이 출고인지 확인한 뒤, 출고합계에서 따로 빼는 방식이 실수도 적고 이해하기 쉽습니다.

재고현황표는 품목별로 모아서 보는 결과표입니다

이번에는 오른쪽에 재고현황표를 만들겠습니다. H4:L7 범위에 표를 만들고, H열에는 품목코드를 직접 입력합니다. 품목명은 처음에는 직접 입력해도 되고, 나중에 VLOOKUP이나 XLOOKUP으로 가져와도 됩니다.

정리하면 원본 데이터 범위는 A5:F11, 품목코드 조건 셀은 H5, H6, H7, 결과를 표시할 셀은 J5:L7입니다. 이처럼 “어디에서 가져오고, 어떤 조건으로 계산하고, 어디에 결과를 표시할지”를 먼저 정하면 함수가 훨씬 덜 어렵습니다.

입고합계는 SUMIFS로 품목코드와 구분을 동시에 봅니다

J5 셀에 P-100의 입고합계를 계산해 보겠습니다. J5 셀을 클릭한 뒤 아래 수식을 입력합니다.

=SUMIFS($E$5:$E$11,$C$5:$C$11,$H5,$B$5:$B$11,"입고")

이 수식의 뜻은 어렵지 않습니다. E5:E11 수량 범위에서 합계를 구하되, C5:C11 품목코드가 H5와 같고, B5:B11 구분이 입고인 행만 더하라는 뜻입니다.

여기서 달러 표시($)가 붙은 범위가 보입니다. $E$5:$E$11처럼 고정해 두면 J5 수식을 아래로 복사해도 원본 범위가 밀리지 않습니다. 반대로 $H5는 열 H는 고정하고 행 번호는 아래로 내려가게 둔 형태입니다. J5 수식을 J7까지 복사하면 H6, H7의 품목코드를 차례로 보게 됩니다.

J5 결과는 P-100의 입고 50과 20을 더한 70이 나와야 합니다. J6은 P-200 입고 30, J7은 P-300 입고 15가 나오는지 확인합니다.

출고합계도 같은 방식으로 구분만 바꿉니다

이번에는 K5 셀에 출고합계를 계산합니다. 방금 수식과 거의 같고, 마지막 조건만 “출고”로 바꿉니다.

=SUMIFS($E$5:$E$11,$C$5:$C$11,$H5,$B$5:$B$11,"출고")

K5에는 P-100 출고 12가 나와야 합니다. K6에는 P-200 출고 5, K7에는 P-300 출고 3이 나와야 정상입니다.

SUMIFS가 초보자에게 좋은 이유는 조건을 눈으로 읽을 수 있기 때문입니다. “수량을 더한다, 품목코드를 맞춘다, 구분을 맞춘다”처럼 업무 흐름 그대로 수식이 만들어집니다. 처음에는 길어 보여도, 한 번 익히면 매출합계, 비용합계, 담당자별 건수에도 같은 구조로 쓸 수 있습니다.

현재고는 입고합계에서 출고합계를 빼면 됩니다

이제 L5 셀에 현재고를 계산합니다. 현재고는 이미 계산된 입고합계 J5에서 출고합계 K5를 빼면 됩니다.

=J5-K5

L5 결과는 70 - 12이므로 58입니다. 수식을 L7까지 복사하면 각 품목의 현재고가 자동으로 계산됩니다.

초보자분들이 여기서 자주 하는 실수는 원본의 출고 수량을 음수로 바꾸고, 또 현재고에서 한 번 더 빼는 것입니다. 예를 들어 출고 수량을 -12로 입력해 놓고 현재고를 입고합계 - 출고합계로 계산하면 오히려 더해지는 문제가 생깁니다. 처음에는 출고 수량을 양수로 입력하고, 구분 열로 입고와 출고를 나누는 방식을 추천합니다.

SUMIFS 결과가 0이면 수식보다 원본 글자를 먼저 봅니다

재고표를 만들다 보면 분명 P-100 데이터가 있는데 입고합계가 0으로 나오는 경우가 있습니다. 이때 수식을 처음부터 다시 만들기보다, 조건에 쓰인 글자가 정확히 같은지 확인하는 것이 빠릅니다.

가장 흔한 원인은 B열 구분 값입니다. 눈으로는 “입고”처럼 보이지만 실제로는 “입고 ”처럼 뒤에 공백이 붙어 있을 수 있습니다. 또는 누군가 “입 고”처럼 중간에 띄어쓰기를 넣었을 수도 있습니다.

확인 방법은 간단합니다. B열의 값을 하나 클릭하고 수식 입력줄을 봅니다. 셀 안에서는 잘 안 보이던 앞뒤 공백이 수식 입력줄에서는 더 잘 보입니다. 여러 행을 한 번에 정리하려면 찾기 및 바꾸기를 사용할 수 있습니다. 메뉴에서 홈 > 찾기 및 선택 > 바꾸기를 누른 뒤, 잘못 입력된 값을 올바른 값으로 바꿉니다.

품목코드도 마찬가지입니다. H5에는 P-100이라고 입력했는데, 원본 C열에는 P-100 뒤에 공백이 있으면 서로 다른 값으로 인식됩니다. 엑셀은 사람이 보기에는 비슷해 보여도, 한 글자라도 다르면 다른 값으로 계산합니다.

수량이 숫자인지 확인해야 합계가 제대로 나옵니다

또 하나 자주 만나는 문제는 E열 수량이 숫자가 아니라 문자로 들어간 경우입니다. 특히 다른 시스템에서 내려받은 파일은 수량이 왼쪽 정렬로 들어오거나, 셀 왼쪽 위에 초록색 표시가 생기기도 합니다.

수량이 문자로 들어가면 SUMIFS가 예상과 다르게 계산될 수 있습니다. E5:E11 범위를 선택한 뒤, 메뉴에서 홈 > 표시 형식이 일반 또는 숫자인지 확인합니다. 숫자로 보이는데도 계산이 이상하면, 빈 셀에 숫자 1을 입력하고 복사한 다음 E5:E11 범위를 선택해 선택하여 붙여넣기 > 곱하기를 실행하면 숫자로 바뀌는 경우가 많습니다.

초보자 입장에서는 셀 서식과 실제 값이 헷갈릴 수 있습니다. 셀 서식은 겉모습이고, 실제 값은 엑셀이 계산에 사용하는 내용입니다. 그래서 “겉으로는 50인데 계산이 안 되는” 상황이 생깁니다.

품목명이 자주 바뀐다면 VLOOKUP으로 가져와도 됩니다

재고현황표의 I열 품목명은 처음에는 직접 입력해도 됩니다. 다만 품목이 많아지면 코드와 품목명이 어긋나는 실수가 생깁니다. 이럴 때는 별도의 품목마스터를 만들어 두고 품목명을 가져오면 편합니다.

예를 들어 N4:O7 범위에 품목마스터가 있다고 해보겠습니다. N열은 품목코드, O열은 품목명입니다.

I5 셀에는 아래 수식을 입력할 수 있습니다.

=VLOOKUP(H5,$N$5:$O$7,2,FALSE)

뜻은 H5의 품목코드를 N5:O7 범위 첫 번째 열에서 찾고, 두 번째 열인 품목명을 가져오라는 의미입니다. 마지막 FALSE는 정확히 일치하는 코드만 찾겠다는 뜻입니다. 품목코드는 비슷한 값이 많기 때문에 재고표에서는 FALSE를 넣는 습관이 좋습니다.

만약 코드가 마스터에 없으면 #N/A 오류가 나올 수 있습니다. 그럴 때는 코드가 빠진 것인지, 오타인지 확인하는 신호로 보면 됩니다. 오류를 무조건 숨기기보다 초반에는 원인을 찾는 용도로 두는 편이 안전합니다.

안전재고보다 적은 품목은 IF로 표시해 두면 찾기 쉽습니다

재고표가 조금 익숙해졌다면 M열에 상태를 추가해 볼 수 있습니다. 예를 들어 M4에 “상태”라고 입력하고, 품목별 안전재고 기준을 20개로 본다면 M5 셀에 아래 수식을 넣습니다.

=IF(L5<20,"발주필요","정상")

이 수식은 L5 현재고가 20보다 작으면 “발주필요”, 그렇지 않으면 “정상”이라고 표시합니다. 재고 담당자는 모든 숫자를 하나씩 읽기보다, 상태 열에서 발주필요만 빠르게 확인할 수 있습니다.

안전재고가 품목마다 다르다면 별도 기준표를 만들고 VLOOKUP으로 기준 수량을 가져온 뒤 비교할 수도 있습니다. 하지만 처음 연습할 때는 고정 기준 20처럼 단순한 방식으로 시작하는 것이 좋습니다.

실무에서 확인하는 순서는 이렇게 잡으면 덜 헤맵니다

재고 수량이 맞지 않을 때는 무작정 수식을 바꾸기보다 순서를 정해 확인해야 합니다. 먼저 원본 입출고 내역에서 B열 구분이 입고와 출고로 통일되어 있는지 봅니다. 그다음 C열 품목코드에 공백이나 오타가 없는지 확인합니다.

그 후 E열 수량이 숫자인지 확인하고, 마지막으로 SUMIFS의 범위 행 번호가 서로 같은지 봅니다. 예를 들어 합계 범위는 $E$5:$E$11인데 조건 범위가 $C$5:$C$10처럼 한 행 짧으면 오류가 나거나 계산이 맞지 않습니다. SUMIFS에서는 합계 범위와 조건 범위의 크기가 같아야 합니다.

필터로 눈에 보이는 행만 세는 방식은 빠르게 확인할 때는 좋지만, 보고서용 재고표에는 수식으로 기준을 남기는 편이 안전합니다. 나중에 누가 파일을 열어도 어떤 기준으로 현재고를 계산했는지 수식에서 바로 확인할 수 있기 때문입니다.

오늘 예제의 핵심은 단순합니다. 원본에는 입고와 출고를 거래별로 쌓고, 재고현황표에서는 SUMIFS로 입고합계와 출고합계를 따로 구한 뒤 현재고를 계산합니다. 이 구조만 잡히면 월별 재고, 창고별 재고, 담당자별 출고량도 같은 방식으로 확장할 수 있습니다.