엑셀 따라 하기

엑셀 재고관리 만들기 (입출고·현재고 자동)

품목표와 입출고 기록 두 장으로 현재고가 자동 계산되는 재고 장부를 만듭니다. 안전재고 미달 표시와 월별 요약까지.

2026-11-01 작성. 이 글의 수식 블록은 모두 테스트 통합문서에서 다시 계산해 확인했습니다.

구조: 품목표와 입출고 기록

  • 품목: 품목마다 한 줄. 기초재고(시작할 때 있던 수량)와 단가를 적고, 총입고·총출고·현재고는 수식으로.
  • 입출고: 들어오고 나간 것을 한 줄씩. 날짜, 구분(입고/출고), 품목코드, 수량, 단가.
  • 월 요약: 연도를 넣으면 월별 입고·출고 수량. 품목코드를 넣으면 그 품목만.

현재고 = 기초재고 + 총입고 - 총출고 입니다. 수량을 직접 고치지 않고 기록을 더해서 구하므로 언제 무엇이 빠졌는지 추적할 수 있습니다.

1단계. 품목 시트

시트 이름을 품목 으로 하고 1행에 품목코드, 품목명, 단위, 안전재고, 단가, 기초재고, 총입고, 총출고, 현재고, 재고금액, 상태를 적습니다. 2~201행에 품목 200개까지 씁니다. A~F 는 직접 입력합니다. 품목코드는 겹치지 않게 합니다. 품목코드에 * ? ~ 기호를 쓰거나 < > = 로 시작하지 마세요. SUMIFS 가 이를 와일드카드·비교 조건으로 읽어 다른 품목까지 더합니다 (예: A?2 는 A12·AB2 도 셉니다).

2단계. 입출고 시트

새 시트 입출고 의 1행에 날짜, 구분, 품목코드, 품목명, 수량, 단가, 금액을 적고 2~2001행을 씁니다. B열은 데이터 유효성 검사 목록 입고,출고, C열은 목록 원본 ='품목'!$A$2:$A$201 로 두면 오타가 줄어듭니다.

품목명은 코드로 찾아 오고, 품목표에 없는 코드면 '코드 없음'이 뜹니다.

D2 품목명=IF(C2="","",IFERROR(INDEX('품목'!$B$2:$B$201,MATCH(C2,'품목'!$A$2:$A$201,0)),"코드 없음"))
G2 금액 (수량 x 단가)=IF(OR(E2="",F2=""),"",E2*F2)

3단계. 품목별 총입고·총출고·현재고

품목 시트 G2~K2 에 아래 수식을 넣고 201행까지 채웁니다. SUMIFS 가 입출고 시트에서 같은 코드, 같은 구분의 수량을 날짜와 관계없이 모두 더합니다.

G2 총입고=IF($A2="","",SUMIFS('입출고'!$E$2:$E$2001,'입출고'!$C$2:$C$2001,$A2,'입출고'!$B$2:$B$2001,"입고"))
H2 총출고=IF($A2="","",SUMIFS('입출고'!$E$2:$E$2001,'입출고'!$C$2:$C$2001,$A2,'입출고'!$B$2:$B$2001,"출고"))
I2 현재고 (기초 + 입고 - 출고)=IF($A2="","",F2+G2-H2)
J2 재고금액 (현재고 x 단가)=IF($A2="","",I2*E2)
K2 상태=IF($A2="","",IF(I2<0,"음수 확인",IF(AND(D2<>"",I2<D2),"부족","")))

현재고가 안전재고보다 적으면 '부족', 0보다 적으면 '음수 확인'이 뜹니다. A2:K201 에 조건부 서식(수식 =$K2<>"", 채우기 색)을 걸면 상태가 뜬 줄에 그 색이 칠해집니다. 재고금액은 평균단가·선입선출이 아니라 품목표 단가를 곱한 단순 값입니다.

4단계. 월 요약

새 시트 월 요약 의 B1 에 연도, B2 에 품목코드(비우면 전체)를 적고, A5:A16 에 1~12 를 적습니다.

B5 입고 수량=IF($B$2="",SUMIFS('입출고'!$E$2:$E$2001,'입출고'!$B$2:$B$2001,"입고",'입출고'!$A$2:$A$2001,">="&DATE($B$1,$A5,1),'입출고'!$A$2:$A$2001,"<"&DATE($B$1,$A5+1,1)),SUMIFS('입출고'!$E$2:$E$2001,'입출고'!$B$2:$B$2001,"입고",'입출고'!$A$2:$A$2001,">="&DATE($B$1,$A5,1),'입출고'!$A$2:$A$2001,"<"&DATE($B$1,$A5+1,1),'입출고'!$C$2:$C$2001,$B$2))
C5 출고 수량=IF($B$2="",SUMIFS('입출고'!$E$2:$E$2001,'입출고'!$B$2:$B$2001,"출고",'입출고'!$A$2:$A$2001,">="&DATE($B$1,$A5,1),'입출고'!$A$2:$A$2001,"<"&DATE($B$1,$A5+1,1)),SUMIFS('입출고'!$E$2:$E$2001,'입출고'!$B$2:$B$2001,"출고",'입출고'!$A$2:$A$2001,">="&DATE($B$1,$A5,1),'입출고'!$A$2:$A$2001,"<"&DATE($B$1,$A5+1,1),'입출고'!$C$2:$C$2001,$B$2))

16행까지 채웁니다. 선택한 연도의 날짜만 더하므로 날짜를 텍스트로 넣은 기록은 빠집니다. 전체 품목 합계는 단위가 다른 수량을 그대로 더한 값이라, 수량을 볼 때는 품목코드를 넣고 보세요.

실사로 수량을 맞출 때

세어 본 수량과 현재고가 다르면 차이만큼 입고 또는 출고로 한 줄 적습니다. 사유를 남기려면 입출고 시트에 비고 열을 하나 더 만드세요. 현재고 칸을 직접 고치면 수식이 지워집니다.

다른 엑셀 가이드

알아 두실 점