상품을 판매하거나 사무용품을 관리하다 보면 재고가 부족한 사실을 뒤늦게 발견하는 경우가 있습니다.
특히 여러 상품을 관리할 때는 수량을 하나씩 확인해야 하므로 시간이 많이 걸립니다.
예를 들어 복사용지가 12개 남아 있는데 최소 20개를 유지해야 한다면, 부족한 수량을 바로 알 수 있어야 합니다.
이번에는 상품별 재고 수량을 입력하면 발주 필요 여부와 부족 수량이 자동으로 표시되는 엑셀 관리표를 만들어 보겠습니다.
완성된 실습 파일도 함께 제공합니다.
1. 재고 관리에서 가장 중요한 숫자
재고 관리표에는 현재재고와 안전재고라는 두 가지 기준이 필요합니다.
현재재고는 지금 보유한 수량이고, 안전재고는 부족 상황에 대비해 유지하려는 최소 수량입니다.
예를 들어 A4 복사용지의 현재재고가 12개이고 안전재고가 20개라면 8개가 부족합니다.
기본 계산은 다음과 같습니다.
부족수량 = 안전재고 − 현재재고
다만 재고가 충분한 상품에서 음수가 표시되지 않도록 MAX 함수를 사용할 수 있습니다.
2. 상품별 재고 관리표 작성하기
먼저 엑셀에 다음 항목을 입력합니다.
| 열 | 관리 항목 |
|---|---|
| A | 상품코드 |
| B | 상품명 |
| C | 상품분류 |
| D | 현재재고 |
| E | 안전재고 |
| F | 발주단위 |
| G | 부족수량 |
| H | 권장발주수량 |
| I | 관리상태 |
| J | 관리메모 |
10행부터 상품 정보를 입력합니다.
현재재고와 안전재고는 직접 수정하고, 부족수량과 관리상태는 수식으로 계산하는 방식입니다.
3. 부족수량 자동 계산하기
G10 셀에 다음 수식을 입력합니다.
=IF(B10="","",MAX(0,E10-D10))
이 수식은 상품명이 입력되어 있을 때 안전재고에서 현재재고를 뺀 결과를 계산합니다.
MAX 함수는 계산 결과가 음수라면 0을 표시합니다.
예를 들어 현재재고가 12개이고 안전재고가 20개라면 부족수량은 8개입니다.
반대로 현재재고가 30개라면 부족수량은 0개로 표시됩니다.
4. IF 함수로 재고 상태 자동 구분하기
이제 상품의 재고 상태를 자동으로 표시하겠습니다.
I10 셀에 다음 수식을 입력합니다.
=IF(B10="","",IF(COUNT(D10:E10)<2,"입력 확인",IF(D10=0,"품절",IF(D10<E10,"발주 필요","정상"))))
이 수식은 다음 기준으로 판단합니다.
- 현재재고 또는 안전재고가 숫자로 입력되지 않았다면 '입력 확인'
- 현재재고가 0개라면 '품절'
- 현재재고가 안전재고보다 적다면 '발주 필요'
- 그 외에는 '정상'
예를 들어 복사용지가 12개이고 안전재고가 20개라면 '발주 필요'라고 표시됩니다.
수량을 25개로 변경하면 자동으로 '정상'으로 바뀝니다.
5. 조건부 서식으로 부족 상품 강조하기
문자만 표시하는 것보다 색상을 함께 적용하면 재고 상태를 더 빠르게 확인할 수 있습니다.
엑셀에서 I10:I39 범위를 선택합니다.
이후 홈 → 조건부 서식 → 셀 강조 규칙 → 텍스트 포함 메뉴를 이용합니다.
'품절'이라는 문자가 포함된 셀은 빨간색, '발주 필요'는 노란색 계열로 설정합니다.
'정상'은 연한 초록색으로 표시하면 구분하기 쉽습니다.
이번 실습 파일에는 이러한 색상 구분을 미리 적용했습니다.
6. 권장발주수량까지 자동 계산하기
상품은 필요한 수량만큼 낱개로 구매할 수 없는 경우가 있습니다.
예를 들어 복사용지가 8개 부족하지만 한 번에 10개 단위로 주문해야 한다면 10개를 발주해야 합니다.
이때 ROUNDUP 함수를 사용합니다.
H10 셀에 다음 수식을 입력합니다.
=IF(B10="","",IF(OR(F10="",F10<=0),"발주단위 확인",IF(G10=0,0,ROUNDUP(G10/F10,0)*F10)))
부족수량을 발주단위로 나눈 뒤 올림 계산을 하여 구매해야 할 수량을 구하는 방식입니다.
부족수량이 8개이고 발주단위가 10개라면 권장발주수량은 10개입니다.
단, 실제 발주에서는 최소 주문수량과 납기, 보관 공간도 함께 고려해야 합니다.
7. 재고 관리에서 자주 발생하는 실수
재고 자동 계산표를 사용할 때는 세 가지를 확인하는 것이 좋습니다.
첫째, 상품마다 안전재고 기준을 다르게 설정해야 합니다.
둘째, 현재재고는 실제 입출고 내역을 반영하여 수정해야 합니다.
셋째, 계산된 권장발주수량은 참고값으로 사용해야 합니다.
이번 파일은 재고 부족 여부를 계산하는 학습용 관리표입니다. 판매 예약 수량이나 입고 예정 수량, 유통기한 등은 별도로 관리해야 합니다.
8. 재고 부족 자동 경고표 무료 다운로드
이번 실습을 직접 따라 할 수 있도록 엑셀 파일을 준비했습니다.
파일에는 다음 기능이 포함되어 있습니다.
- 상품별 현재재고와 안전재고 입력
- 재고 부족수량 자동 계산
- 품절·발주 필요·정상 상태 표시
- 발주단위를 반영한 권장발주수량 계산
- 등록 상품과 부족 상품 현황 확인
상품명을 변경하거나 현재재고를 수정하면서 자동 계산 결과를 확인해 보세요.
첨부 파일: GS10_재고부족_자동경고_관리표.xlsx
마무리
이번에는 IF, MAX, ROUNDUP 함수와 조건부 서식을 이용하여 재고 관리표를 만들어 보았습니다.
함수를 각각 외우는 것보다 여러 기능을 연결해 실제 관리 업무에 적용하는 것이 중요합니다.
재고 부족 상품이 자동으로 표시되면 관리자는 어떤 상품을 먼저 확인해야 하는지 쉽게 판단할 수 있습니다.
처음에는 적은 수의 상품으로 연습한 뒤 실제 업무에 맞춰 관리 항목을 확장해 보시기 바랍니다.
다음 글에서는 월별 매출 자료를 자동으로 집계하고 차트로 확인하는 매출 대시보드 만들기를 다루겠습니다.
'엑셀 실무 마스터' 카테고리의 다른 글
| 엑셀 중복 데이터 찾기와 제거 방법 | COUNTIF·UNIQUE 함수 실무 예제 (0) | 2026.11.04 |
|---|---|
| 엑셀 드롭다운 목록 만들기 | 데이터 유효성 검사로 입력 오류 줄이는 방법 (0) | 2026.11.03 |
| 월별 매출 자동 집계 대시보드 만들기 | 엑셀 SUMIFS 함수와 차트 실무 예제 (0) | 2026.11.01 |
| 엑셀 DATEDIF 함수 사용법｜근속연수·재직기간·날짜 차이 자동 계산하기 (0) | 2026.11.01 |
| 직원 연차·휴가 관리표 만들기 | 엑셀 SUMIF·IF 함수 실무 예제 (0) | 2026.10.30 |
