엑셀 재고관리 만들기: 입출고를 한 표에 쌓아야 재고가 스스로 맞습니다

품목별로 시트를 하나씩 만들었더니 품목이 80개가 됐습니다.

새 품목이 생기면 시트를 복사하고, 전체 재고를 보려면 80장을 열어 숫자를 옮겨 적습니다. 한 시트에서 수식을 고치면 나머지 79장은 그대로 남습니다.

엑셀 재고관리 만들기는 품목별로 나누는 것에서 시작하면 안 됩니다. 입출고를 한 표에 쌓는 것부터 시작합니다.

엑셀 재고관리 만들기는 표 하나에서 시작합니다

입출고 시트에 여섯 열만 둡니다.

일자        품목코드   구분    수량   단가    비고
2026-09-01  P001      입고    100   8000   9월 발주분
2026-09-03  P001      출고     12          주문 A1023
2026-09-05  P002      입고     50   5200

구분에는 입고 · 출고 · 조정 셋만 씁니다. 반품은 입고로 넣고 비고에 반품이라 적거나, 구분에 반품을 추가해도 됩니다. 종류를 늘릴수록 수식이 길어지므로 셋에서 시작합니다.

품목이 몇 개든 이 표 하나에 쌓입니다. 새 품목이 생겨도 시트를 안 만듭니다.

엑셀 재고 함수는 SUMIFS 두 줄입니다

현재고는 입고 합계에서 출고 합계를 뺀 값입니다.

=SUMIFS(수량범위,품목범위,$A2,구분범위,"입고")
 -SUMIFS(수량범위,품목범위,$A2,구분범위,"출고")
 +SUMIFS(수량범위,품목범위,$A2,구분범위,"조정")

조정을 더하는 이유는 실사 차이를 여기에 넣기 때문입니다. 조정 수량에 음수를 넣으면 줄어듭니다.

특정 날짜 기준 재고가 필요하면 날짜 조건을 하나씩 더 붙입니다.

=SUMIFS(수량범위,품목범위,$A2,구분범위,"입고",일자범위,"<="&$B$1)

월말 재고를 이렇게 뽑아 두면 매출 관리의 월별 표와 나란히 놓입니다.

안전재고를 넘으면 눈에 띄게 만듭니다

현재고 옆에 안전재고 열을 두고 조건부 서식을 겁니다. 수식 규칙으로 =$C2<$D2를 넣고 채우기 색을 지정하면 부족한 품목만 색이 칠해집니다.

안전재고를 감으로 정하면 계속 안 맞습니다. 최근 판매량에서 뽑습니다.

=AVERAGE(최근30일_일평균출고) * 리드타임일수 * 1.5

리드타임은 발주하고 입고될 때까지 걸리는 날입니다. 1.5는 여유분이고 품목에 따라 조정합니다. 계절을 타는 품목은 작년 같은 달 판매량으로 봐야 맞습니다.

발주 시점은 재고가 아니라 소진일로 봅니다

재고 30개가 많은지 적은지는 그 자체로 모릅니다. 하루에 몇 개가 나가는지를 알아야 판단이 됩니다.

=현재고 / 최근30일_일평균출고

며칠 뒤에 떨어지는지가 나옵니다. 이 숫자가 리드타임보다 작으면 지금 발주해야 하는 품목입니다. 소진일 오름차순으로 정렬하면 오늘 발주할 목록이 위로 올라옵니다.

품목이 80개여도 이 정렬 하나면 오늘 볼 것이 다섯 줄로 줄어듭니다.

실사 차이는 지우지 말고 조정 줄로 남깁니다

장부 재고와 실제 재고가 다를 때 장부 숫자를 고쳐 맞추면 차이가 없어진 것처럼 보입니다.

조정 줄을 추가합니다. 일자 · 품목 · 조정 · 차이수량 · 사유를 적습니다. 이렇게 두면 어느 품목에서 차이가 반복되는지 쌓입니다.

=SUMIFS(수량범위,품목범위,$A2,구분범위,"조정")

같은 품목에서 매달 조정이 나오면 파손이거나, 출고 기록이 빠지고 있거나, 세는 방식이 다릅니다. 원인을 찾을 단서가 조정 줄 안에 있습니다. 장부를 고쳐 맞추면 그 단서가 사라집니다.

엑셀 재고관리 양식을 받아 쓸 때 확인할 것

인터넷에서 받은 양식은 대개 품목별 시트 구조이거나 현재고 한 칸만 있는 구조입니다. 둘 다 나중에 막힙니다.

받아 쓸 때 세 가지만 확인합니다.

① 입출고가 한 표에 쌓이는가

② 현재고가 수식으로 계산되는가, 아니면 손으로 적는 칸인가

③ 과거 시점 재고를 뽑을 수 있는가

엑셀 재고관리 양식 셋 다 되는 것이면 그대로 쓰고, 안 되면 위 여섯 열로 새로 만드는 편이 빠릅니다.

엑셀 자동화는 출고 기록에서 시작합니다

수식보다 먼저 막히는 곳은 출고를 제때 적는 일입니다. 주문이 나갈 때 적어야 하는데 나중에 몰아서 적게 되고, 그러면 며칠씩 재고가 안 맞습니다.

엑셀 자동화를 붙인다면 주문 데이터에서 출고 줄을 만드는 부분입니다. 채널 주문 파일에 품목코드와 수량이 이미 있으므로 구분 열에 출고를 붙여 입출고 표로 옮기면 됩니다. 파일을 표준 열로 맞추는 방법은 주문서 취합에 적어 뒀습니다.

자주 받는 질문

Q. 창고가 여러 곳이면 어떻게 하나요

A. 창고 열을 하나 추가하고 SUMIFS 조건에 넣습니다. 시트를 창고별로 나누면 이동 처리가 어려워집니다. 창고 간 이동은 출고 한 줄과 입고 한 줄로 적습니다.

Q. 단가가 매입할 때마다 다르면 재고 금액은 어떻게 계산하나요

A. 총평균으로 시작합니다. 입고 금액 합계를 입고 수량 합계로 나눈 단가에 현재고를 곱합니다. 선입선출이 필요해지면 그때는 엑셀 수식보다 재고 프로그램이 맞습니다.

Q. 엑셀 재고관리 만들기에서 줄이 몇만 개가 되면 느려지지 않나요

A. 느려집니다. 그때는 지난 연도분을 마감하고 기초재고 한 줄로 요약해 별도 시트에 보관합니다. 현재 시트에는 올해분만 두면 속도가 돌아옵니다.

Q. 바코드나 스캐너 없이도 되나요

A. 됩니다. 품목코드를 드롭다운으로 고르게 만들면 오타가 대부분 없어집니다. 스캐너는 입력 속도를 올려 주는 것이고, 표 구조가 잘못돼 있으면 스캐너가 있어도 재고는 안 맞습니다.

장부 재고와 실사 재고의 차이를 조정 줄로 남깁니다. 그 숫자부터 쌓아 두려 합니다.