창고 순환 실사 엑셀 템플릿
순환 실사는 연 1회 전체 재고 조사를 매일 몇 개 로케이션으로 바꾸는 방식이고, 실사가 정직하고 후속 조치가 일관될 때만 통해요. 이 통합 문서는 전산 수량 없는 블라인드 실사표를 인쇄하고, 실사마다 전산과 비교해 ABC 등급별 허용 오차 안의 작은 차이는 받아들이고, 나머지는 재실사를 요청하고, 조정할 행을 알려줘요. 그리고 일치율, 정확도, 순·총 차이, 재고 손실을 구역별, 사유별, 월별로 목표와 비교해요.
- 시트:
- 6
- 지원 버전:
- Microsoft 365용 Excel, Excel 2016 이후 버전, Mac용 Excel, 웹용 Excel
- 업데이트:
이 템플릿으로 할 수 있는 일
- 날짜별 블라인드 실사표: 전산 수량 없이 로케이션과 품목만
- ABC 등급별 허용 오차: 행마다 Match, Within tolerance, Recount, Adjust
- 전체와 구역·사유·월별 일치율, 정확도, 순·총 차이, 재고 손실
- 목표 대비 정확도 추세, 목표 미달 구역 강조
구성
통합 문서에는 시트가 6개 있어요. 입력하는 시트는 몇 개뿐이고 나머지는 계산돼요.
| 시트 | 용도 | 입력하는 것 | 자동 계산되는 것 |
|---|---|---|---|
| Start Here | 설정과 결과 | 인쇄할 실사일, A·B·C 품목 허용 오차, 정확도 목표 | 실사 행, 일치율, 정확도, 순·총 차이, 재고 손실과 비율, 재실사 대기, 조정할 행, 가장 약한 구역 |
| Counts | 실사 기록(600) | 날짜, 구역, 로케이션, 품목, 설명, 등급, 단가, 전산 수량, 실사, 재실사, 실사자, 사유 | 최종 수량, 차이 수량·금액·%, 허용 오차, 결과, 월, 인쇄 번호 |
| Count Sheet | 인쇄용 블라인드 실사표 | 실사자 이름 | 고른 날짜에 예정된 로케이션과 품목(전산 수량 없음) |
| Summary | 구역·사유·월별 | 없음 | 행 수, 일치율, 정확도, 순·총 차이, 재고 손실, 목표 대비 6개월 추세 |
| Lists | 드롭다운 | 구역, 등급, 사유, 실사자 | 없음 |
| Checks | 데이터 검사 6개 | 없음 | OK 또는 Review |
시작하기 전에
- 품목을 금액 기준 A, B, C로 나누고(ABC 재고 분석 템플릿이 해줘요) 등급별 실사 주기를 정하세요.
- 실사하는 동안 로케이션 이동을 멈추거나 그날 첫 피킹 전에 세세요.
- 팀이 조정에 쓸 사유 코드를 합의하세요.
단계별 사용법
허용 오차와 목표 설정
Start Here B7–B10: A, B, C 품목에 허용할 차이와 목표 정확도.
실사 일정 입력
Counts 탭: 날짜, 구역, 로케이션, 품목, 설명, 등급, 단가, 실사 시점의 전산 수량.
블라인드 실사표 인쇄
Start Here B6에서 날짜를 고르고 Count Sheet 탭을 인쇄하세요. 로케이션은 나오지만 전산 수량은 절대 안 나와요.
실사 입력
I열: 실사자가 센 수량. 결과가 Match, Within tolerance, Recount 중 하나로 나와요.
재실사와 조정
Recount 행은 다시 세서 J열에 넣으세요. 그래도 다르면 Adjust: 조정을 반영하고 사유를 고르세요.
정확도 검토
Start Here와 Summary 탭이 구역·사유·월별 일치율, 정확도, 재고 손실을 보여줘요.
수식 지도
통합 문서의 모든 수식을 시트와 열별로 정리했어요. 복사된 수식 열은 한 번만 세고, 적용 범위를 함께 표시해요.
| 시트 | 셀 | 함수 | 계산 내용 | 읽는 값 | 쓰이는 곳 |
|---|---|---|---|---|---|
| Counts | K5 604행까지 아래로 복사됨 | IF | 최종 수량 | I5, J5 | 차이는 이 값으로 재요. |
| Counts | M5 604행까지 아래로 복사됨 | IF | 차이 금액 | L5, G5 | 순·총 차이와 재고 손실 합계가 모두 이 열에서 나와요. |
| Counts | O5 604행까지 아래로 복사됨 | IF, INDEX, MATCH | 행의 허용 오차 | F5 | 차이 %와 비교해 결과를 정해요. |
| Counts | P5 604행까지 아래로 복사됨 | IF, ABS | 결과 | I5, L5, N5, O5, J5 | 일치율과 정확도가 이 결과를 셉니다. |
| Counts | T5 604행까지 아래로 복사됨 | IF, COUNTIF | 인쇄 번호 | A5 | Count Sheet가 번호마다 찾아요. |
| Count Sheet | B7 66행까지 아래로 복사됨 | IFERROR, INDEX, MATCH | 블라인드 실사 행 | A7 | 인쇄해서 실사자에게 줘요. |
| Summary | D5 9행까지 아래로 복사됨 | IF, COUNTIFS | 구역별 정확도 | B5 | 목표 미달이면 빨강, 가장 낮은 구역은 Start Here에. |
| Summary | F5 9행까지 아래로 복사됨 | IF, SUMIFS | 구역별 총 차이 | — | 서로 상쇄돼도 오류가 얼마나 큰지 보여줘요. |
| Start Here | B20 | IFERROR, SUMPRODUCT | 실사 금액 대비 재고 손실 % | — | 월별, 사업장별로 비교할 수 있는 비율이에요. |
모든 수식 설명
아래 수식은 파일에서 그대로 가져온 것이라 여기서 읽는 내용이 셀에 들어 있는 내용과 정확히 같아요. 엑셀에서 셀을 클릭하면 수식 입력줄에서 볼 수 있어요.
=IF(I5="", "", IF(J5<>"", J5, I5))재실사가 있으면 재실사, 없으면 첫 실사.
부분별 설명
I5=""- 아직 안 셌으면 빈칸.
IF(J5<>"", J5, I5)- 재실사가 우선.
예시 데이터로 보면: I5 = 291, J5 = — → K5 결과 291
어떻게 쓰이나: 차이는 이 값으로 재요.
바꾸는 방법: 바꿀 것 없음.
배우기: 엑셀 IF 함수 사용법 →=IF(K5="", "", L5*G5)초과·부족 수량 × 단가.
부분별 설명
L5- 최종 − 전산 수량.
*G5- 단가.
예시 데이터로 보면: L5 = 0, G5 = 15.2 → M5 결과 0
어떻게 쓰이나: 순·총 차이와 재고 손실 합계가 모두 이 열에서 나와요.
바꾸는 방법: 바꿀 것 없음.
배우기: 엑셀 기본 수식 작성법: 더하기, 빼기, 곱하기, 나누기 →=IF(F5="", "", INDEX('Start Here'!$B$7:$B$9,
MATCH(F5, {"A", "B", "C"}, 0)))이 품목 등급의 허용 오차를 Start Here에서 찾습니다.
부분별 설명
MATCH(F5, {"A","B","C"}, 0)- 고정 목록에서 등급의 위치: 1, 2, 3.
INDEX('Start Here'!$B$7:$B$9, …)- 그 위치의 허용 오차.
예시 데이터로 보면: F5 = B → O5 결과 2.0%
어떻게 쓰이나: 차이 %와 비교해 결과를 정해요.
바꾸는 방법: 허용 오차는 Start Here에서.
배우기: 엑셀 INDEX와 MATCH 함수 사용법 →=IF(A5="", "", IF(I5="", "Pending",
IF(L5=0, "Match", IF(ABS(N5)<=O5, "Within tolerance", IF(J5="", "Recount", "Adjust")))))실사 없으면 Pending, 정확히 같으면 Match, 차이 %가 허용 오차 안이면 Within tolerance, 아니고 재실사가 없으면 Recount, 재실사로도 다르면 Adjust.
부분별 설명
I5=""- Pending.
L5=0- Match.
ABS(N5)<=O5- Within tolerance.
J5=""- Recount.
"Adjust"- 조정 반영.
예시 데이터로 보면: I5 = 291, L5 = 0, N5 = 0, O5 = 2.0%, J5 = — → P5 결과 Match
어떻게 쓰이나: 일치율과 정확도가 이 결과를 셉니다.
바꾸는 방법: 바꿀 것 없음.
배우기: 엑셀 IF 함수 사용법 →=IF(A5='Start Here'!$B$6,
COUNTIF($A$5:A5, 'Start Here'!$B$6),
"")고른 날짜에 예정된 행에 1, 2, 3… 번호를 매겨 Count Sheet가 순서대로 나열하게 합니다.
부분별 설명
A5='Start Here'!$B$6- 인쇄 날짜에 예정됨.
COUNTIF($A$5:A5, 'Start Here'!$B$6)- 이 행까지 그런 행의 수.
예시 데이터로 보면: A5 = 2026년 5월 4일 → T5 결과 —
어떻게 쓰이나: Count Sheet가 번호마다 찾아요.
바꾸는 방법: 바꿀 것 없음.
배우기: 엑셀 COUNTIF, COUNTIFS 함수 사용법 →=IFERROR(INDEX(Counts!$B$5:$B$604,
MATCH(A7, Counts!$T$5:$T$604, 0)),
"")인쇄 번호 1(아래로 2, 3 …)인 행을 찾아 구역을 돌려주고, 다음 열은 로케이션, 품목, 설명을 돌려줘요. 전산 수량은 가져오지 않아요.
부분별 설명
MATCH(A7, Counts!$T$5:$T$604, 0)- 이 번호의 행.
INDEX(Counts!$B$5:$B$604, …)- 구역.
IFERROR(…, "")- 마지막 행 뒤로는 빈칸.
예시 데이터로 보면: A7 = 1 → B7 결과 A - Fast pick
어떻게 쓰이나: 인쇄해서 실사자에게 줘요.
바꾸는 방법: 바꿀 것 없음.
배우기: 엑셀 INDEX와 MATCH 함수 사용법 →=IF(N(B5)=0, "", (COUNTIFS(Counts!$B$5:$B$604, A5, Counts!$P$5:$P$604, "Match")+COUNTIFS(Counts!$B$5:$B$604, A5, Counts!$P$5:$P$604, "Within tolerance"))/B5)일치하거나 허용 오차 안인 행 ÷ 구역의 실사 행.
부분별 설명
COUNTIFS(Counts!$B$5:$B$604, A5, Counts!$P$5:$P$604, "Match")- 정확히 일치.
+COUNTIFS(…, "Within tolerance")- 받아들인 차이.
/B5- 실사 행 수.
예시 데이터로 보면: B5 = 86 → D5 결과 1
어떻게 쓰이나: 목표 미달이면 빨강, 가장 낮은 구역은 Start Here에.
바꾸는 방법: 바꿀 것 없음.
배우기: 엑셀 COUNTIF, COUNTIFS 함수 사용법 →=IF(A5="", "", SUMIFS(Counts!$M$5:$M$604,
Counts!$B$5:$B$604, A5, Counts!$M$5:$M$604,
">0")-SUMIFS(Counts!$M$5:$M$604,
Counts!$B$5:$B$604, A5, Counts!$M$5:$M$604,
"<0"))상계하지 않은 초과 + 부족: 양의 차이 − 음의 차이.
부분별 설명
SUMIFS(…, ">0")- 초과 금액.
-SUMIFS(…, "<0")- 부족 금액을 양수로.
예시 데이터로 보면: F5 결과 942.29
어떻게 쓰이나: 서로 상쇄돼도 오류가 얼마나 큰지 보여줘요.
바꾸는 방법: 바꿀 것 없음.
배우기: 엑셀 SUMIF, SUMIFS 함수 사용법 →=IFERROR(B19/SUMPRODUCT(N(+Counts!$H$5:$H$604),
N(+Counts!$G$5:$G$604)),
0)부족 금액 ÷ 실사한 모든 행의 전산 금액.
부분별 설명
B19- 재고 손실(부족분).
SUMPRODUCT(N(+Counts!$H$5:$H$604), N(+Counts!$G$5:$G$604))- 행마다 전산 수량 × 단가. N(+…)은 빈칸을 0으로 바꿔요.
예시 데이터로 보면: B20 결과 0.1%
어떻게 쓰이나: 월별, 사업장별로 비교할 수 있는 비율이에요.
바꾸는 방법: 바꿀 것 없음.
배우기: 엑셀 필수 함수 SUM, AVERAGE, COUNT, MIN, MAX 사용법 →팁과 한계
- 용량: 실사 600행, 인쇄 한 장에 60행. 해마다 새 파일을 쓰거나 Ctrl+H로 범위를 늘리세요.
- 블라인드로 세세요. 전산 수량을 보면 그 숫자를 "찾게" 돼요.
- 일치율(정확히 일치)은 엄격한 지표이고, 허용 오차 내 정확도는 대부분 창고가 보고하는 지표예요. 둘 다 보세요.
- 허용 오차는 전산 수량 대비 %예요. 아주 적은 로케이션은 1개 차이도 5%를 넘을 수 있고, A 품목 허용 오차 0이면 모든 불일치가 재실사예요.
- 순 차이가 0에 가까워도 서로 상쇄된 큰 오류가 숨어 있을 수 있어요. 총 차이와 재고 손실을 보세요.
이 템플릿에 쓰인 기능 배우기
도움이 되었나요? 잘못된 내용이 있나요? 의견 보내기 · admin@arumsoft.com