엑셀 재고관리 양식 — 시트 3개로 만드는 법 (2026년)
엑셀 재고관리 양식은 시트 3개면 시작할 수 있습니다. 「재료」 시트에 재료 목록과 1개당 원가를, 「입고」 시트에 산 기록을, 「사용」 시트에 쓴 기록과 버린 기록을 적고, 지금 재고는 입고 합계에서 사용 합계를 빼서 구합니다.
아래에 칸 구성과 수식, 예시 숫자를 정리했습니다. 엑셀로 버티기 어려워지는 순간도 함께 적었습니다.
엑셀 재고관리 양식 — 시트 3개 칸 구성
재고 장부의 기본은 "무엇이 있고, 얼마나 들어왔고, 얼마나 나갔나" 세 가지입니다. 한 시트에 다 넣으면 줄이 금방 엉키므로 나눠 적습니다.
| 시트 | 넣을 칸 |
|---|---|
| 재료 | 재료 이름 · 재고 세는 단위(개·팩·포) · 한 번 사면 몇 개 · 구매가 · 배송비 · 1개당 원가 · 안전 재고 · 거래처 · 보관 장소 |
| 입고 | 날짜 · 재료 이름 · 수량 · 금액 · 배송비 · 유통기한 · 거래처 · 결제 여부(외상/결제 완료) |
| 사용 | 날짜 · 재료 이름 · 수량 · 구분(사용/폐기) · 사유 |
재료 관리 목록 — 재료마다 지금 재고와 안전 재고가 보인다
재료 이름은 세 시트에서 글자 하나까지 똑같이 적어야 합니다. 「우유1L」과 「우유 1L」은 엑셀이 다른 재료로 봅니다. 재료 시트의 이름 칸을 목록으로 만들어 두고(데이터 유효성 검사), 입고·사용 시트에서는 골라서 넣게 하면 이 실수가 줄어듭니다.
재고 계산 수식과 1개당 원가
지금 재고는 재료 시트에 칸 하나를 더 만들어 구합니다. 재료 시트의 재료 이름이 A열, 입고·사용 시트의 재료 이름이 B열, 수량이 C열이라면 다음과 같습니다.
=SUMIF(입고!B:B, A2, 입고!C:C) - SUMIF(사용!B:B, A2, 사용!C:C)
1개당 원가는 (구매가 + 배송비) ÷ 한 번 사면 들어오는 개수입니다. 배송비도 원가에 넣어야 실제로 쓴 돈과 맞습니다.
| 재료 | 한 번 살 때 | 구매가 | 배송비 | 1개당 원가 |
|---|---|---|---|---|
| 우유 1L | 1박스 = 12개 | 18,000원 | 0원 | 1,500원 |
| 케이크 상자 | 1박스 = 100개 | 15,000원 | 3,000원 | 180원 |
| 밀가루 | 1포 | 18,000원 | 0원 | 18,000원 |
예를 들어 우유를 두 박스(24개) 받고, 9개를 쓰고, 1개를 버렸다면 지금 재고는 24 − 9 − 1 = 14개, 재고 금액은 14 × 1,500원 = 21,000원입니다.
재고 세는 단위는 처음에 정하고 바꾸지 않기
엑셀 장부가 어긋나는 가장 흔한 이유는 단위가 섞이는 것입니다. 어떤 날은 우유를 "1박스", 어떤 날은 "12개"로 적으면 합계가 뜻 없는 숫자가 됩니다. 재료마다 재고 숫자 1이 무엇인지(1개인지 1박스인지)를 재료 시트에 적어 두고 그 단위로만 적습니다. 낱개로 꺼내 쓰는 재료는 「개」로 세는 편이 맞습니다.
반 포만 쓴 날처럼 애매할 때는 소수로 적지 말고, 새 포를 뜯는 날 1을 적는 식으로 규칙을 정해 두면 장부가 깔끔합니다.
엑셀이 힘들어지는 순간 — 유통기한과 선입선출
시트 3개 양식은 "몇 개 남았나"까지는 잘 보여 줍니다. 어려워지는 것은 같은 재료를 다른 날 다른 값에 샀을 때입니다.
밀가루를 9월 2일 18,000원, 9월 20일 21,000원에 한 포씩 사고 9월 25일에 한 포를 썼다고 해 봅니다. 먼저 산 것부터 쓴다고 보면(선입선출) 이번에 쓴 재료값은 18,000원이고, 남은 1포는 21,000원짜리입니다. SUMIF 수식은 "1포 남음"만 알려 줄 뿐, 남은 것이 어느 날 얼마에 산 것인지는 알려 주지 않습니다.
유통기한도 같습니다. 구매 건마다 유통기한이 다르므로 어느 건이 몇 개 남았는지를 따로 따라가야 합니다. 엑셀로 하려면 구매 건마다 남은 수량 칸을 두고 쓸 때마다 먼저 산 줄부터 손으로 깎아야 해서, 재료가 수십 가지가 되면 적는 시간이 크게 늘어납니다.
재고 사용 기록
엑셀 재고 장부를 오래 쓰는 요령
- 산 날 바로, 쓴 날 바로 적습니다. 몰아서 적으면 빠지는 줄이 생깁니다.
- 버린 것도 「사용」 시트에 구분을 「폐기」로 적습니다. 버린 재료도 쓴 돈입니다.
- 한 달에 한 번 실제 수를 세어 장부와 맞춰 봅니다. 차이가 나면 빠뜨린 기록부터 찾습니다.
- 지금 재고가 J열, 안전 재고가 H열이라면
=IF(J2<H2,"부족","")칸을 하나 두어 주문할 재료가 눈에 띄게 합니다.
자주 묻는 질문
엑셀 재고관리 양식은 어떤 시트로 시작하면 되나요? 재료 · 입고 · 사용 세 시트면 됩니다. 지금 재고는 입고 합계에서 사용 합계를 빼서 구하고, 재료 이름은 세 시트에서 똑같이 적습니다.
재고를 박스로 세야 하나요, 개로 세야 하나요? 낱개로 꺼내 쓰는 재료는 개로 세는 편이 맞습니다. 박스로 세면 한 박스를 다 써야 1을 뺄 수 있습니다. 어느 쪽이든 정한 뒤에는 바꾸지 않아야 합계가 맞습니다.
쓴 재료를 매번 적기 번거로운데 방법이 있나요? 조금씩 덜어 쓸 때마다 적지 말고 포대·팩·통을 새로 뜯을 때 한 번 적으면 됩니다. 한 달에 한 번 실제 수를 세어 맞춰 보면 그 사이 차이도 잡힙니다.
올내로 재고 장부 줄이기
올내는 재료를 등록할 때 정한 재고 세는 단위를 저장한 뒤에는 바꾸지 못하게 막아 두어 단위가 섞이지 않습니다. 구매를 넣으면 재고가 늘고 지출 「식자재」에도 한 줄 저절로 적히며, 영수증 사진을 올리면 품목·수량·금액을 읽어 옵니다. 쓴 재료는 카드를 눌러 [빼기]를 누르면 먼저 산 구매 건부터 빠지고, 안전 재고 아래로 떨어지면 다이어리에 알림이 남습니다. 올내 기능 보기