엑셀 유통기한/재고관리용 함수 추천

엑셀의 TODAY, DATEDIF 등을 써서 “유통기한·재고 관리 + 간이 POS(판매기록)용” 함수/서식을 한 번에 굴러가게 설계해드릴게요. 아래 둘 중 편한 방식으로 쓰면 됩니다.

---

A안) 한 시트로 끝내는 “간단 버전” (소상공인용)

1) 표 구조 (A:L)

|A 분류|B 상품명|C 바코드/코드|D 제조일|E 입고일|F 유통기한|G 남은일수|H 상태|I 재고|J 안전재고|K 발주수량|L 비고|

표로 만들기: 범위를 잡고 Ctrl+T → 머리글 포함 체크

2) 핵심 보조셀

• N1: 임박 기준(일) → 예: 3
• N2: 오늘 → =TODAY() (선택)

3) 함수 (2행부터 입력 후 아래로 복사)

• 남은일수(G2)음수면 이미 지난 상태(경과일), 양수면 남은 일수.
• =IF(F2="","",F2-TODAY())
• 상태(H2)
• =IF(F2="","", IF(G2<0,"폐기", IF(G2<=$N$1,"임박","정상")))
• 발주수량(K2) — 간단 로직(안전재고까지 보충)
• =MAX(0,J2-I2)
• (선택) 경과일(제조일 기준, M2 같은 여분열)
• =IF(D2="","",DATEDIF(D2,TODAY(),"D"))

참고: DATEDIF는 지나간 일수 계산에 유용하지만, “남은일수”는 단순 뺄셈이 더 깔끔합니다(음수 = 경과).

4) 조건부 서식 (색상 자동표시)

적용 범위: =$A$2:$L$1000 (표 크기에 맞게 조정)

1. 폐기(빨강/흰 글자)
   수식:
2. =$H2="폐기"
3. 임박(노랑/검정 글자)
   수식:
4. =$H2="임박"
5. 정상(연두색 아주 옅게) — 선택
   수식:
6. =$H2="정상"
7. 저재고(오렌지) — 선택
   수식:
8. =$I2<=$J2

색상은 “셀 강조 규칙 → 수식 사용 → 서식”으로 지정하세요.

5) 데이터 유효성(오입력 방지)

• F열(유통기한) 날짜만 허용 & 제조/입고 이후만 허용(선택):
  • 데이터 → 데이터 유효성 → 사용자 지정 → 수식:
  • =AND(ISNUMBER(F2),F2>=MAX(D2,E2))
• I열(재고) 0 이상:
• =AND(ISNUMBER(I2),I2>=0)

6) “임박/폐기 대시보드” (Microsoft 365 사용 시)

• 임박/폐기 목록을 다른 영역에 자동 필터:
  • 임박 목록:
  • =FILTER(A2:L1000, H2:H1000="임박")
  • 폐기 목록:
  • =FILTER(A2:L1000, H2:H1000="폐기")
• 아이콘 집합(신호등)도 가능: G열(남은일수)에 아이콘 집합 → 값 기준 직접 설정(예: ≤0 빨강, ≤$N$1 노랑, 그 외 녹색)

---

B안) “정석 3시트 구조” (입·출고를 자동 반영하는 간이 POS)

시트 구성

1. 마스터 (상품 카드 + 현재고/유통기한 모아보기)
   |A 분류|B 상품명|C 코드|D 최초등록일|E 안전재고|F 현재고|G 가장빠른유통기한|H 남은일수|I 상태|J 발주수량|
2. 입고
   |A 날짜|B 코드|C 수량(+)|D 유통기한|E 비고|
3. 판매
   |A 날짜시간|B 코드|C 수량(-)|D 비고|

바코드 스캐너를 쓰면 판매!B(코드)에 커서를 두고 스캔 → 수량 입력.

마스터 시트 함수

• 현재고(F2)
• =SUMIFS(입고!$C:$C,입고!$B:$B,$C2) -SUMIFS(판매!$C:$C,판매!$B:$B,$C2)
• 가장빠른 유통기한(G2)
• =IFERROR(MINIFS(입고!$D:$D,입고!$B:$B,$C2),"")
• 남은일수(H2)
• =IF(G2="","",G2-TODAY())
• 상태(I2)3일 기준을 다른 셀(예: 마스터!N1)로 빼고 싶다면 H2<=$N$1로 바꾸면 됩니다.
• =IF(G2="","", IF(H2<0,"폐기", IF(H2<=3,"임박","정상")))
• 발주수량(J2)
• =MAX(0,E2-F2)

조건부 서식(마스터에 적용)

• =$I2="폐기" → 빨강, =$I2="임박" → 노랑, =$F2<=$E2 → 저재고 오렌지.

장점

• 판매/입고 기록만 적으면 마스터의 현재고·유통기한·상태가 자동 갱신.
• 가장 가까운 유통기한 로트 기준으로 관리(선입선출 도움).
• 필터/정렬로 “오늘 임박·폐기 상품” 즉시 확인.

더 고도화(로트별 재고 차감/FIFO 자동 차감)는 매크로(VBA) 또는 파워쿼리/파워피봇이 필요합니다. “간이 POS” 용도라면 위 구조가 가장 깔끔하고 튼튼합니다.

---

자주 쓰는 응용 팁

• 오늘 입고분만 보기(입고 시트)
• =FILTER(입고!A:E, INT(입고!A:A)=TODAY())
• 일별 판매 요약(피벗테이블 추천)
  • 판매 시트를 피벗으로 → 행: 날짜(일), 값: 수량 합계, 코드/상품명 추가.
• 월말 자동발주리스트(마스터에서 필터)“임박/폐기이거나 저재고”인 품목만 모음.
• =FILTER(A:J, (I:I<>"정상") + (F:F<=E:E))

---

입력 예시 체크리스트

• 날짜열은 날짜 형식, 코드/바코드는 텍스트 형식(앞자리 0 보존)
• 유통기한은 제조·입고일 이후만 허용(데이터 유효성 수식 참고)
• 표(Ctrl+T) 사용으로 자동확장되게 만들기
• 머리글에 필터 켜서 “임박/폐기/저재고”만 빠르게 보기

---

유통기한_재고관리_간이POS_정석버전.xlsx

0.07MB