엑셀 재고관리 양식 만들기: SUMIFS 함수로 현재고 계산하는 방법

엑셀 재고관리의 기본은 품목별 기초재고와 입출고 기록을 정리한 뒤, SUMIFS 함수로 입고와 출고를 따로 더해 현재고를 구하는 것입니다. 계산식은 기초재고 + 입고 합계 - 출고 합계 하나면 됩니다.

자재가 들어오고 나갈 때마다 재고 숫자를 직접 고쳐 적으면, 나중에는 그 숫자에 어떤 기록이 반영됐는지 알 수 없게 됩니다. 그래서 입출고를 한 건씩 쌓아 두는 표와 품목별 결과를 보는 표를 나눠 두는 편이 좋습니다. 숫자가 이상해 보일 때 언제든 원래 기록으로 돌아가 확인할 수 있기 때문입니다.

이 글에서는 작은 예시로 두 표를 만들고, SUMIFS 수식을 넣은 뒤, 결과를 원래 기록과 맞춰 보는 과정까지 차례로 따라가 보겠습니다.

재고관리 양식 구성: 입출고 시트와 재고현황 시트

양식은 시트 두 장으로 구성합니다. 입출고가 생길 때마다 한 줄씩 쌓는 입출고 시트와, 품목별 결과를 한눈에 보는 재고현황 시트입니다. 각 시트에 들어갈 항목은 다음과 같습니다.

엑셀 재고관리 양식 만들기: 예시 데이터 입력하기

이제 볼트와 너트 두 품목으로 가상 예시를 만들어 보겠습니다. 기초재고는 2026년 10월 31일 업무를 마친 시점의 수량이고, 집계 기간은 11월 1일부터 11월 30일까지입니다. 수량 단위는 모두 ‘개’이며, 반품·폐기나 재고 조사 후 수량 조정은 없다고 가정합니다.

엑셀 파일 하나에 입출고와 재고현황 시트를 만들고 아래 셀 위치대로 입력하면, 뒤에 나오는 수식을 고치지 않고 그대로 쓸 수 있습니다. 표의 ‘행’ 열은 위치를 알려 주는 안내용이니, 그 열을 뺀 나머지를 복사해 붙여 넣으면 같은 양식이 바로 만들어집니다.

1. 입출고 시트: 입고·출고를 한 줄씩 기록하기

입출고 시트의 A1:D8에 다음과 같이 입력합니다. 날짜는 2026-11-02처럼 하이픈으로 구분해 적고, 수량은 숫자로 입력합니다.

같은 품목이라도 입고와 출고를 한 칸에 덮어쓰지 말고, 생길 때마다 새 행으로 남겨야 합니다. 2행(10월 30일)과 8행(12월 1일)은 일부러 집계 기간 밖에 넣은 기록입니다. 수식이 이 두 줄을 제대로 걸러내는지는 뒤에서 확인하겠습니다.

2. 재고현황 시트: 기초재고와 집계 기간 입력하기

재고현황 시트의 A1:F3에는 제목과 입력값을 준비합니다. D~F열은 다음 단계에서 수식으로 채웁니다.

같은 시트의 H1:I2에는 집계 기간을 적습니다.

기초재고 100개와 50개는 10월 31일 마감 기준의 수량입니다. 10월 30일에 들어온 볼트 25개는 이미 100개 안에 포함돼 있으므로, 집계 시작일을 11월 1일로 정해 같은 수량을 두 번 더하지 않도록 합니다.

3. 데이터 유효성 검사로 입력 실수 막기

SUMIFS는 대소문자는 구분하지 않지만, 글자가 조금이라도 다르면 다른 값으로 봅니다. 입고 뒤에 공백이 하나만 붙어도 그 행은 합계에서 빠집니다. 구분과 품목코드를 직접 타이핑하지 않고 목록에서 고르게 해 두면 이런 실수를 미리 막을 수 있습니다.

• 구분(C열): 입출고 시트의 C2:C1000을 선택한 뒤 [데이터] 탭에서 [데이터 유효성 검사]를 엽니다. ‘제한 대상’을 ‘목록’으로 바꾸고 ‘원본’ 칸에 입고,출고를 입력합니다.

• 품목코드(B열): B2:B1000도 같은 방법으로 ‘목록’을 고르고, ‘원본’ 칸에 =재고현황!$A$2:$A$100을 입력합니다. 그러면 재고현황 시트에 등록한 품목코드만 고를 수 있습니다.

엑셀 SUMIFS 함수로 입출고 합계와 현재고 계산하기

SUMIFS는 여러 조건을 모두 만족하는 행만 골라 숫자를 더하는 함수입니다. 이 예시에서는 ‘품목코드가 같고, 구분이 입고이며, 날짜가 집계 기간 안에 있는 행’의 수량만 더합니다.

수식이 길어 보이지만 구조는 단순합니다. 맨 앞에 ‘무엇을 더할지’를 정하고, 그 뒤로 ‘어느 열에서 어떤 조건을 확인할지’를 짝지어 이어 붙이면 됩니다.

더할 범위와 조건을 확인할 범위는 모두 같은 크기여야 합니다. 이 예시에서는 모두 2행부터 1000행까지로 맞췄습니다.

1. 품목·기간 조건으로 입고 합계 구하기

재고현황 시트의 D2 셀에 아래 수식을 입력합니다.

=SUMIFS('입출고'!$D$2:$D$1000,'입출고'!$B$2:$B$1000,$A2,'입출고'!$C$2:$C$1000,"입고",'입출고'!$A$2:$A$1000,">="&$I$1,'입출고'!$A$2:$A$1000,"<="&$I$2)

2. 출고 합계와 현재고 수식 넣기

E2에는 D2의 수식에서 "입고"만 "출고"로 바꿔 입력합니다.

=SUMIFS('입출고'!$D$2:$D$1000,'입출고'!$B$2:$B$1000,$A2,'입출고'!$C$2:$C$1000,"출고",'입출고'!$A$2:$A$1000,">="&$I$1,'입출고'!$A$2:$A$1000,"<="&$I$2)

F2에는 현재고를 구하는 수식을 넣습니다.

=C2+D2-E2

마지막으로 D2부터 F2까지 선택해 3행으로 끌어 복사합니다. 수식 속 $는 바로 뒤에 오는 열 문자나 행 번호를 고정하는 기호입니다. 행 번호까지 고정한 입출고 범위($D$2:$D$1000 등)와 집계 기간 칸($I$1, $I$2)은 그대로 유지되고, 열만 고정한 $A2는 $A3으로 바뀌어 너트의 품목코드를 찾습니다.

3. 계산 결과를 입출고 기록과 검산하기

볼트(B001)의 입고 합계는 11월 2일 40개와 11월 20일 15개를 더한 55개이고, 현재고는 100 + 55 - 30 = 125개입니다. 10월 30일 입고 25개는 기초재고에 이미 들어 있으므로 더하지 않았습니다. 너트(N001)는 50 + 20 - 10 = 60개이며, 12월 1일 출고 5개는 집계 기간 밖이라 빠졌습니다.

이처럼 같은 품목이라도 날짜와 구분에 따라 더할 행만 골라내야 하기 때문에, 단순 합계 대신 SUMIFS를 씁니다. 결과가 예상과 다르다면 입출고 시트에서 해당 품목의 행을 하나씩 짚으며 날짜·구분·수량을 확인해 보세요.

SUMIFS 결과가 0이나 #VALUE! 오류로 나올 때

수식을 제대로 넣었는데도 합계가 0이거나 예상보다 작게 나오거나 #VALUE! 오류가 뜬다면, 다음 네 가지를 먼저 확인해 보세요.

• 수량이 글자로 저장된 경우: 다른 프로그램에서 복사해 붙여 넣은 숫자는 글자로 들어가는 경우가 있는데, SUMIFS는 이런 값을 더하지 않습니다. 셀 안의 숫자가 왼쪽에 붙어 있거나 셀 모서리에 녹색 삼각형이 보이면 숫자로 바꿔 주세요.

• 날짜가 글자로 저장된 경우: 2026.11.02처럼 점으로 구분해 입력하면 엑셀이 날짜로 알아보지 못하는 경우가 있습니다. 이런 값은 기간 조건을 통과하지 못하니 하이픈으로 다시 입력합니다. 날짜에 시간까지 함께 들어 있으면 종료일 당일의 기록이 빠질 수 있으니, 날짜만 입력하는 편이 안전합니다.

• 공백이나 오타: B001 뒤에 공백이 붙은 값은 B001과 다른 값으로 취급됩니다. 앞에서 설정한 드롭다운 목록을 쓰면 대부분 막을 수 있습니다.

• #VALUE! 오류: 더할 범위와 조건 범위의 크기가 서로 다르거나, 다른 엑셀 파일의 범위를 가져오는데 그 파일이 닫혀 있을 때 주로 나타납니다. 모든 범위가 같은 행에서 시작해 같은 행에서 끝나는지 확인하세요.

새 입출고 기록과 다음 달 재고는 어떻게 반영하나요?

예시 수식은 입출고 시트의 2행부터 1000행까지를 확인합니다. 따라서 새 기록이 이 범위 안에 있고 조건에 맞으면, 따로 손대지 않아도 합계에 자동으로 포함됩니다. 기록이 1000행을 넘길 것 같다면 수식 안의 범위를 모두 같은 크기로 함께 늘려 주세요.

기록이 빠르게 늘어난다면 입출고 범위를 엑셀의 ‘표’ 기능으로 바꾸고, 수식도 셀 주소 대신 표 이름과 열 이름으로 다시 작성하는 방법이 있습니다. 이렇게 하면 표에 행을 추가할 때 수식이 확인하는 범위도 함께 늘어납니다. 범위만 표로 바꾸고 수식을 그대로 두면 지금처럼 1000행까지만 확인한다는 점에 주의하세요. 또 표 바깥에 적은 기록이나 재고현황 시트에 새로 추가할 품목까지 알아서 반영되지는 않습니다.

달이 바뀌면 두 가지만 고치면 됩니다.

1. 11월 30일 현재고(F열)를 복사해 C열에 값만 붙여 넣고, 이를 12월의 기초재고로 씁니다. 붙여 넣을 때는 마우스 오른쪽 버튼의 [붙여넣기 옵션]에서 ‘값’을 고르세요. 수식째 붙여 넣으면 오류가 납니다.

2. I1과 I2를 각각 2026-12-01과 2026-12-31로 바꿉니다.

지난달 결과를 남겨 두고 싶다면, 고치기 전에 재고현황 시트를 복사해 두면 됩니다.

엑셀 재고표를 보고하기 전 점검할 5가지

수식이 정확해도 들어간 기록이 틀리면 결과도 틀립니다. 계산된 현재고를 보고하기 전에 다음 항목을 점검해 보세요.

• 품목코드와 단위: 같은 자재에 서로 다른 코드를 쓰거나 ‘개’와 ‘박스’를 섞어 적으면, 합계가 실제와 다른 의미가 됩니다.

• 기준일과 기간: 기초재고에 이미 들어간 입출고를 다시 더하고 있지 않은지, 집계 기간이 보고하려는 기간과 같은지 확인합니다.

• 기록 누락·중복: 같은 입고를 두 번 적거나 출고를 빠뜨려도 수식은 적힌 그대로 계산합니다.

• 그 밖의 수량 변동: 반품·폐기·창고 이동처럼 입고와 출고 외의 변동이 있다면, 구분 값과 계산 규칙을 따로 정해야 합니다.

• 장부와 실물: 현재고는 기록으로 계산한 수량입니다. 정기적으로 창고의 실제 수량과 맞춰 보세요.

장부 재고와 실물 재고가 다를 때 원인 찾는 법

양식을 쓰다 보면 결국 ‘숫자가 실제와 맞지 않는’ 순간을 만나게 됩니다. 창고에서 세어 보니 볼트가 장부보다 10개 적다고 해도, 10개라는 전체 차이만 봐서는 원인을 찾기 어렵습니다. 이럴 때는 납품서·출고증 같은 현장 기록과 입출고 시트를 품목별로 한 줄씩 맞춰 보세요. 한쪽에만 있는 기록과 양쪽에 있지만 수량이 다른 기록을 가려내면, 입력을 빠뜨렸는지, 두 번 적었는지, 단위를 헷갈렸는지가 드러납니다. 예를 들어 출고증에는 있지만 시트에는 빠진 출고 15개와, 납품서에는 20개인데 시트에는 15개로 적힌 11월 20일 입고를 찾았다면, 10개 차이는 −15개와 +5개가 합쳐진 결과라는 것을 알 수 있습니다.

파일이 하나이고 담당자가 한 명일 때는 이 대조가 어렵지 않습니다. 하지만 품목이 늘고 담당자가 여러 명이 되면 사정이 달라집니다. 회사 ERP(통합 관리 프로그램)에서 내려받은 자료와 현장에서 따로 쓰는 엑셀까지 함께 맞춰야 한다면 더욱 그렇습니다. 파일마다 품목코드 표기와 업데이트 시점이 다르면, 수식이 정확해도 매달 같은 대조를 손으로 반복하게 됩니다.

이미 재고 관리 프로그램을 쓰는데도 이런 작업이 남아 있다면, 재고 관리 프로그램을 써도 반복되는 엑셀 작업에서 그 이유와 점검할 부분을 확인해 보세요.

반복되는 재고 자료 대조, 어디서부터 줄일까요?

먼저 매달 만드는 재고 보고서 하나를 골라 보세요. 그 보고서를 위해 어떤 파일에서 자료를 가져오는지, 파일끼리 어떤 품목코드로 잇는지, 차이가 나면 누가 확인하는지를 적어 보면 반복 작업이 어디에 몰려 있는지 보입니다. 엑셀의 기존 기능이나 수식으로 해결되는 부분부터 정리하고, 그래도 여러 자료를 매번 다시 맞춰야 한다면 그때 자동화를 검토하면 됩니다.

이 글에서 품목코드 하나로 두 시트를 이었듯, 여러 자료를 맞추는 일도 결국 공통 기준을 세우는 데서 시작합니다. 조슈아앤컴퍼니는 배터리 소재 믹싱 장비를 조립하는 TSI와 5주간 프로젝트를 진행하며 이 작업을 했습니다.

문서마다 다르게 적혀 있던 작업과 자재를 하나의 ‘표준 키값’으로 묶어 상세일정, 일일 작업계획, ERP 자재 정보를 연결했습니다. 그 결과 그날 내려받은 ERP 데이터를 넣으면 작업계획 옆에 자재 입고 현황이 자동으로 표시되면서, 2시간 넘게 걸리던 자재 입고 확인을 따로 할 필요가 없어졌습니다. 월 생산계획을 만드는 시간도 1~2일에서 14초로 줄었습니다. 기존 엑셀과 ERP는 그대로 두고, TSI 생산관리팀·PI팀과 함께 실제 문서를 업무 순서대로 대조하며 기준을 세운 결과입니다.

자세한 과정은 TSI 생산관리 자동화 사례에서 확인할 수 있습니다.

지금 쓰고 있는 재고 자료와 매번 손으로 맞추는 작업을 알려 주시면, 어떤 자료와 기준부터 정리하면 좋을지 함께 살펴보겠습니다.

참고 자료

• Microsoft 지원 — SUMIFS 함수

• Microsoft 지원 — 여러 조건을 기반으로 값의 합계 구하기

• Microsoft 지원 — Excel 표의 구조적 참조

• Microsoft 지원 — SUMIF/SUMIFS 함수의 #VALUE! 오류