이 글의 목적은 연구소에서 시약의 유통기한과 개봉 후 사용기한을 체계적으로 관리할 수 있는 엑셀 서식을 스스로 설계·운영할 수 있도록 상세 절차와 수식, 규칙, 점검 포인트를 제공하는 것이다.
1. 왜 시약 유통기한 관리가 중요한가
유통기한이 지난 시약은 시험·분석 결과의 신뢰성을 떨어뜨리며 안전사고 위험을 높인다. 특히 수분 흡수나 산화에 민감한 시약은 개봉 후 품질 저하가 빠르게 진행된다. 따라서 미개봉 유효기간과 개봉 후 사용기한을 분리하여 관리하고, 만료 임박 알림과 이력 추적을 구현하는 것이 핵심이다.
2. 서식 구성 개요
엑셀 파일은 최소 4개 시트로 구성한다.
- 재고(Inventory): 모든 시약의 마스터 데이터와 유효기간 계산, 상태 판정이 포함되도록 한다.
- 입고(Log_입고): 구매·입고 이력을 기록한다.
- 사용(Log_사용): 사용량과 사용자를 기록한다.
- 알림(Alert): 만료·임박·재고부족 항목을 자동 집계한다.
운영 성숙도에 따라 코드목록(Code), 라벨(Label), 폐기(Log_폐기), 점검(Checklist) 시트를 추가한다.
3. 필수 컬럼 설계
다음 표는 재고 시트의 권장 필수 컬럼이다.
| 컬럼 | 설명 | 형식 | 예시 |
|---|
| 품명 | 시약 정식명 | 텍스트 | Sodium chloride |
| CAS | 식별용 CAS 번호 | 텍스트 | 7647-14-5 |
| 규격 | 순도·등급 | 목록 | AR, GR, HPLC |
| 공급사 | 제조사/공급사 | 텍스트 | Merck |
| 카탈로그번호 | 주문 코드 | 텍스트 | 1.06404 |
| 로트번호 | LOT/배치 | 텍스트 | K12345 |
| 보관위치 | 실험실-캐비닛-선반 | 목록 | L2-CB3-S2 |
| 보관조건 | 온도·차광·건조 | 목록 | 2~8℃, RT, 냉동 |
| 용량 | 용기 총량 | 숫자 | 500 |
| 단위 | g, mL 등 | 목록 | g |
| 잔량 | 남은 수량 | 숫자 | 120 |
| 재주문점 | 보충 기준 | 숫자 | 100 |
| 제조일자 | 미개봉 기준일 | 날짜 | 2025-01-15 |
| 유효기간_개월 | 미개봉 유효기간 | 숫자 | 36 |
| 개봉일 | 최초 개봉일 | 날짜 | 2025-02-10 |
| 개봉후유효기간_개월 | 개봉 후 사용기한 | 숫자 | 6 |
| 유효만료일 | 실제 만료일(계산) | 날짜 | 자동계산 |
| D-일수 | 오늘 기준 남은 일수 | 숫자 | 자동계산 |
| 상태 | 정상/임박/만료 | 텍스트 | 자동계산 |
| 위험등급 | GHS 등급 | 목록 | Flam. Liq. 2 |
| 담당자 | 관리 책임자 | 목록 | 홍길동 |
| 식별코드 | 바코드/QR키 | 텍스트 | INV-2025-0001 |
4. 계산식 설계(엑셀 표 기반)
재고 범위를 표(Ctrl+T)로 변환하고 이름을 INV로 지정한다. 구조화 참조를 활용하면 가독성이 높아진다.
- 유효만료일:
=IF([@[개봉일]]<>"", EDATE([@[개봉일]], [@[개봉후유효기간_개월]]), EDATE([@[제조일자]], [@[유효기간_개월]]))로 설정한다.
- D-일수:
=IF([@[유효만료일]]="", "", [@[유효만료일]]-TODAY())로 설정한다.
- 상태:
=IF([@[유효만료일]]="", "검토필요", IF([@[유효만료일]]<TODAY(), "만료", IF([@[유효만료일]]<=TODAY()+30, "임박", "정상")))로 설정한다.
개봉 후 유효기간이 없을 경우는 개봉 후 컬럼을 0으로 두거나 IFERROR로 조건을 분기한다.
5. 조건부 서식 규칙
가시성과 신속한 판단을 위해 다음 규칙을 권장한다.
- 만료 행 강조: 수식
=$[상태]2="만료"를 사용하여 빨강 채우기와 흰색 글꼴로 표시한다.
- 임박(30일) 강조: 수식
=$[상태]2="임박"를 사용하여 노랑 채우기를 적용한다.
- 재고부족 강조: 수식
=$[잔량]2<=$[재주문점]2를 사용하여 주황 채우기를 적용한다.
- 데이터 오류 점검: 수식
=OR([@[제조일자]]="", [@[유효기간_개월]]="")에 회색 채우기를 부여한다.
6. 알림 시트 자동 집계
동적 배열을 사용할 수 있는 환경이면 알림 시트에 다음을 사용한다.
- 만료/임박 리스트:
=FILTER(INV, (INV[상태]<>"정상"))를 사용한다.
- 재고부족 리스트:
=FILTER(INV, (INV[잔량]<=INV[재주문점]))를 사용한다.
동적 배열을 사용할 수 없는 환경이면 자동필터와 고급필터를 사용하여 동일한 결과를 만들 수 있다.
7. 데이터 유효성 검사
오입력을 줄이기 위해 목록 기반 입력과 사용자 지정 규칙을 적용한다.
- 단위/보관위치/보관조건/담당자: Code 시트에 기준 목록을 두고 데이터 유효성 검사에서 목록 참조를 지정한다.
- 날짜 논리 검증:
=OR([@[유효만료일]]="", [@[유효만료일]]>=[@[제조일자]])를 사용자 지정으로 설정한다.
- 음수 잔량 방지:
=AND(ISNUMBER([@[잔량]]), [@[잔량]]>=0)를 적용한다.
8. 바코드·QR 연동 설계
식별코드를 INV-연도-일련번호 형태로 부여한다. 바코드 스캐너를 사용하면 검색과 기록이 빨라진다.
- 1D 바코드: Code128 폰트를 사용하여 식별코드를 라벨에 인쇄한다.
- QR: 라벨에 QR을 인쇄하고 스캔 시 엑셀의 검색 창 또는 공유 양식으로 연결되도록 한다.
- 검색:
Ctrl+F로 식별코드를 검색하면 해당 행으로 즉시 이동한다.
9. 라벨 설계
라벨에는 최소한 품명, 로트, 식별코드, 유효만료일, 보관조건을 표시한다. 컬러 밴드를 사용하여 상태를 시각화한다.
- 정상: 녹색 밴드로 표시한다.
- 임박: 노란색 밴드로 표시한다.
- 만료: 빨간색 밴드로 표시한다.
Word 혼합메일 또는 엑셀 인쇄영역을 사용하여 3×10 규격 라벨지에 출력한다.
10. 사용 로그 자동 집계
Log_사용 시트에 날짜, 식별코드, 사용자, 사용량, 단위, 비고 컬럼을 만든다. 재고 차감은 두 방식 중 선택한다.
- 수동 차감: 주기적으로 피벗테이블로 식별코드별 총 사용량을 합산하여 재고 시트 잔량에 반영한다.
- 수식 연동: 재고 잔량을
=[@[용량]] - SUMIFS(Log_사용[사용량], Log_사용[식별코드], [@[식별코드]])로 계산한다.
11. 개봉 후 사용기한 분리 관리
개봉 후 품질 저하가 있는 시약은 개봉후유효기간_개월을 0보다 큰 값으로 입력한다. 계산식은 이미 개봉일 존재 여부에 따라 분기되므로 만료일이 자동으로 재계산된다. 개봉일이 비어 있으면 미개봉 유효기간이 적용된다.
12. 재시험·연장 절차 기록
품질 부서 재시험으로 사용연장을 승인하는 경우 연장만료일과 승인자 컬럼을 추가한다. 상태 계산식을 다음처럼 수정한다.
=LET(d, IF([@[연장만료일]]<>"", [@[연장만료일]], [@[유효만료일]]), IF(d="", "검토필요", IF(d<TODAY(), "만료", IF(d<=TODAY()+30, "임박", "정상"))))로 설정한다.
13. 권한·버전 관리
중앙 저장소(예: 팀 공유 드라이브)를 사용하고 파일 이름에 버전과 날짜를 포함한다. 시트 보호와 입력 영역만 잠금 해제하여 무분별한 수식 변경을 방지한다. 월 1회 백업을 별도 경로에 보관한다.
14. 구축 절차 요약
- 필수 컬럼 정의 및 코드 목록 작성한다.
- 재고 시트를 표로 변환하고 계산식·상태 규칙을 구현한다.
- 입고·사용 로그 시트를 만든다.
- 알림 시트를 FILTER 또는 고급필터로 구현한다.
- 데이터 유효성 검사와 조건부 서식을 적용한다.
- 라벨 서식을 만들고 식별코드 체계를 확정한다.
- 운영 규정과 점검 체크리스트를 배포한다.
15. 운영 체크리스트
| 항목 | 방법 | 빈도 | 기록 |
|---|
| 입고 등록 | Log_입고 작성 후 재고 반영 | 수시 | 입고번호 |
| 개봉일 기록 | 라벨에 날짜 표기 및 재고 입력 | 개봉 즉시 | 개봉자 서명 |
| 만료 임박 검토 | Alert 시트 확인 | 매주 | 검토체크 |
| 재고부족 보충 | 재주문점 도달 시 구매요청 | 수시 | 구매요청서 |
| 폐기 처리 | 폐기 승인 후 Log_폐기 기록 | 수시 | 폐기증빙 |
| 정기 백업 | 버전 복제 | 월 1회 | 백업로그 |
16. 샘플 데이터
| 품명 | CAS | 규격 | 로트번호 | 보관위치 | 용량 | 단위 | 잔량 | 제조일자 | 유효기간_개월 | 개봉일 | 개봉후유효기간_개월 |
|---|
| Acetonitrile | 75-05-8 | HPLC | ACN2408 | L1-CB1-S1 | 1000 | mL | 300 | 2024-09-01 | 24 | 2025-02-10 | 6 |
| Sodium hydroxide | 1310-73-2 | AR | NAOH2503 | L2-CB3-S2 | 500 | g | 480 | 2025-03-10 | 36 | | 0 |
| Methanol | 67-56-1 | HPLC | MeOH2507 | L1-CB2-S3 | 1000 | mL | 50 | 2024-11-20 | 24 | 2025-03-03 | 6 |
| Hydrochloric acid 35% | 7647-01-0 | AR | HCl3502 | L3-CB2-S1 | 500 | mL | 500 | 2024-10-05 | 24 | | 0 |
| Sodium chloride | 7647-14-5 | GR | NACL2506 | L2-CB4-S1 | 1000 | g | 900 | 2025-01-15 | 36 | | 0 |
17. 품질·안전 연계 포인트
- 위험등급과 보관조건을 라벨과 재고 시트 모두에 명확히 표기한다.
- 만료 후 사용 금지 원칙을 문서화하고 예외는 승인 절차를 따른다.
- 산·염기·용매 등 유해물질은 전용 보관장과 누출대응 키트를 준비한다.
18. 자주 발생하는 오류와 예방
- 개봉일 미기록: 라벨에 개봉일 공란 체크박스를 추가하여 현장에서 필수 입력하도록 한다.
- 로트 혼동: 동일 품명의 다른 로트를 같은 위치에 섞어두지 않는다.
- 대체·소분 미추적: 소분 시 새로운 식별코드를 발행하고 원로트와 연결한다.
- 수식 훼손: 수식 컬럼은 잠금하고 입력은 테이블 끝행에만 허용한다.
19. 확장 아이디어
- 대시보드: 피벗차트로 상태별 개수, 임박 추세, 소비 상위 품목을 시각화한다.
- 폼 입력: Office 폼 또는 엑셀 양식 컨트롤로 사용기록 입력을 표준화한다.
- Power Query: 다중 파일의 로그를 병합하여 주간 리포트를 자동 생성한다.
20. FAQ
개봉 후 유효기간이 없는 시약은 어떻게 처리하나?
개봉후유효기간_개월을 0으로 두고 계산식이 미개봉 유효기간만 사용하도록 한다.
연장 승인 시 원래 만료일은 어떻게 보관하나?
연장만료일과 승인자를 추가하고 원래 만료일은 변경하지 않는다. 상태 계산에 연장만료일을 우선 적용한다.
잔량 차감은 자동으로 해야 하나?
초기에는 수동 차감이 단순하다. 사용량이 많아지면 SUMIFS 연동으로 전환한다.
바코드가 꼭 필요한가?
소량 품목은 필수는 아니다. 품목 수가 많거나 이동이 잦다면 도입 효과가 크다.
만료 임박 기준 30일은 고정인가?
운영 기준에 맞게 15일, 60일 등으로 변경하고 상태 계산식의 임계값만 수정한다.
다중 실험실이 공동으로 쓰면 어떻게 관리하나?
보관위치 코드에 실험실 구분을 포함하고 담당자 목록을 확장한다. 파일은 공동 드라이브에서 버전관리한다.
