시정·예방 조치(CAPA) 관리 엑셀 대장 (ISO 9001)
지적 사항은 조치를 했다고 끝나지 않아요. 조치가 효과가 있었는지 확인해야 종결이에요. 이 대장은 지적이 제기된 날부터 확인된 종결까지 따라가요. 기한은 심각도에서, 유효성 확인일은 완료일에서 나오고, 상태는 저절로 정해져요: Open, Overdue, Waiting to verify, Verify now, Not effective, Closed. 출처, ISO 조항, 근본 원인, 월별 요약이 시스템이 계속 실패하는 곳을 보여줘요.
- 시트:
- 5
- 지원 버전:
- Microsoft 365용 Excel, Excel 2016 이후 버전, Mac용 Excel, 웹용 Excel
- 업데이트:
이 템플릿으로 할 수 있는 일
- 지적마다 출처, ISO 조항, 부서, 심각도, 원인 범주, 근본 원인, 조치를 한 행에
- 직접 정하지 않으면 심각도로 정해지는 기한(중대 30, 경미 60, 관찰 90일)
- 유효성 확인일과 6단계 자동 상태, 경과 일수, 기한 내 완료 여부
- 상태·출처·조항·원인·월별 요약과 월별 발생 대 종결 차트
구성
통합 문서에는 시트가 5개 있어요. 입력하는 시트는 몇 개뿐이고 나머지는 계산돼요.
| 시트 | 용도 | 입력하는 것 | 자동 계산되는 것 |
|---|---|---|---|
| Start Here | 설정과 결과 | 기준일(기본 오늘), 심각도별 완료 일수, 확인까지 대기 일수 | 지적 수, 진행, 지연, 확인 대기, 효과 없음, 종결, 기한 내 %, 평균 종결 일수, 가장 오래된 미결 |
| Log | 지적마다 한 행(200) | 제기일, 출처, 조항, 부서, 지적, 심각도, 원인 범주, 근본 원인, 조치, 담당자, 조치 완료일, 목표일(선택), 유효?, 확인자, 종결일 | ID, 기한, 확인 예정일, 상태, 경과 일수, 기한 내? |
| Summary | 분류별 요약 | 없음 | 상태, 출처, ISO 조항, 근본 원인별, 월별 발생·종결·미결 |
| Lists | 드롭다운 | 출처, 조항, 심각도, 원인 범주, 부서 | 없음 |
| Checks | 데이터 검사 6개 | 없음 | OK 또는 Review |
시작하기 전에
- 시스템에서 중대, 경미, 관찰이 무엇이고 각각 얼마나 걸려도 되는지 합의하세요.
- 근본 원인 분석 방법을 하나로(5 Why나 특성요인도) 정하고 범주 목록은 짧게.
- 유효성은 누가 어떻게 확인할지 정하세요: 후속 심사, 데이터 추세, 표본 점검.
단계별 사용법
규칙 설정
Start Here B7–B10: 심각도별 완료 일수와 확인 전 대기 기간.
지적 기록
Log 탭: 제기일, 출처, ISO 조항, 부서, 한 줄 설명, 심각도, 담당자. ID와 기한이 나와요.
근본 원인 찾기
범주(방법, 설비, 자재, 사람, 측정, 환경)를 고르고 근본 원인을 한 줄로 적으세요.
조치
시정 조치를 적고 완료일을 넣으세요. 확인 예정일이 나와요.
확인
확인 예정일 이후 유효?에 Yes나 No와 확인자를 넣으세요. No면 Not effective로 다시 열려요.
종결과 검토
종결일을 넣으세요. Start Here와 Summary 탭이 미결, 기한 내 비율, 반복 원인을 보여줘요.
수식 지도
통합 문서의 모든 수식을 시트와 열별로 정리했어요. 복사된 수식 열은 한 번만 세고, 적용 범위를 함께 표시해요.
| 시트 | 셀 | 함수 | 계산 내용 | 읽는 값 | 쓰이는 곳 |
|---|---|---|---|---|---|
| Log | A5 204행까지 아래로 복사됨 | IF, TEXT, COUNTIF | 지적 ID | B5 | 회의와 심사 보고서에서 쓰는 고정 번호예요. |
| Log | N5 204행까지 아래로 복사됨 | IF, INDEX, MATCH | 기한 | M5, G5, B5 | Overdue와 기한 내 지표를 정해요. |
| Log | S5 204행까지 아래로 복사됨 | IF | 상태 | R5, P5, L5, O5, N5 | Start Here와 Summary의 모든 집계가 이 열을 읽어요. |
| Log | T5 204행까지 아래로 복사됨 | IF | 경과 일수 | R5, B5 | 평균 종결 일수와 가장 오래된 미결에 쓰여요. |
| Log | U5 204행까지 아래로 복사됨 | IF | 기한 내 조치? | L5, N5 | Start Here B20이 기한 내 비율이에요. |
| Summary | D21 30행까지 아래로 복사됨 | IF, COUNTIFS | ISO 조항별 미결 지적 | — | 다음 심사 전에 챙길 규격 부분을 보여줘요. |
| Summary | D41 48행까지 아래로 복사됨 | COUNTIFS, EOMONTH | 월말 미결 | — | 경영 검토용 미결 추세예요. |
| Start Here | B22 | IFERROR, INDEX, MATCH, MAXIFS | 가장 오래된 미결 지적 | — | 보통 검토 회의의 첫 안건이에요. |
모든 수식 설명
아래 수식은 파일에서 그대로 가져온 것이라 여기서 읽는 내용이 셀에 들어 있는 내용과 정확히 같아요. 엑셀에서 셀을 클릭하면 수식 입력줄에서 볼 수 있어요.
=IF(B5="", "", "CA-"&TEXT(B5,
"yy")&"-"&TEXT(COUNTIF($B$5:B5, "<>"),
"000"))제기 연도와 누적 번호로 ID를 만듭니다: CA-26-001.
부분별 설명
TEXT(B5, "yy")- 두 자리 연도.
COUNTIF($B$5:B5, "<>")- 이 행까지의 지적 수.
TEXT(…, "000")- 앞에 0을 채운 세 자리.
예시 데이터로 보면: B5 = 2026년 3월 6일 → A5 결과 CA-26-001
어떻게 쓰이나: 회의와 심사 보고서에서 쓰는 고정 번호예요.
바꾸는 방법: 다른 접두어를 쓰면 "CA-"를 바꾸세요.
배우기: 엑셀 텍스트 함수와 날짜 함수 사용법 →=IF(B5="", "", IF(M5<>"",
M5, B5+INDEX('Start Here'!$B$7:$B$9, MATCH(G5, Lists!$C$5:$C$7, 0))))목표일을 직접 넣었으면 그 날짜, 아니면 제기일 + 심각도별 허용 일수.
부분별 설명
M5<>""- 직접 넣은 목표일 우선.
MATCH(G5, Lists!$C$5:$C$7, 0)- 심각도의 위치(중대, 경미, 관찰).
INDEX('Start Here'!$B$7:$B$9, …)- 그 심각도의 일수.
B5+…- 제기일에 더함.
예시 데이터로 보면: M5 = —, G5 = Major, B5 = 2026년 3월 6일 → N5 결과 2026년 4월 5일
어떻게 쓰이나: Overdue와 기한 내 지표를 정해요.
바꾸는 방법: 일수는 Start Here에서.
배우기: 엑셀 INDEX와 MATCH 함수 사용법 →=IF(B5="", "", IF(R5<>"",
"Closed", IF(P5="No", "Not effective", IF(L5<>"", IF('Start Here'!$B$6>=O5, "Verify now", "Waiting to verify"), IF('Start Here'!$B$6>N5, "Overdue", "Open")))))지적의 생애를 따라갑니다: 종결일이 있으면 Closed, 확인 결과 No면 Not effective, 조치가 끝났으면 날짜에 따라 Verify now나 Waiting to verify, 아니면 Overdue나 Open.
부분별 설명
R5<>""- Closed.
P5="No"- Not effective.
L5<>"", 'Start Here'!$B$6>=O5- 조치 완료, 확인일 도래: Verify now.
'Start Here'!$B$6>N5- 기한 지남: Overdue.
예시 데이터로 보면: R5 = 2026년 6월 15일, P5 = Yes, L5 = 2026년 4월 6일, O5 = 2026년 6월 5일, N5 = 2026년 4월 5일 → S5 결과 Closed
어떻게 쓰이나: Start Here와 Summary의 모든 집계가 이 열을 읽어요.
바꾸는 방법: 바꿀 것 없음.
배우기: 엑셀 IF 함수 사용법 →=IF(B5="", "", IF(R5<>"", R5, 'Start Here'!$B$6)-B5)제기일부터 종결일까지, 미결이면 기준일까지의 일수.
부분별 설명
IF(R5<>"", R5, 'Start Here'!$B$6)- 끝 날짜.
-B5- 제기일을 뺌.
예시 데이터로 보면: R5 = 2026년 6월 15일, B5 = 2026년 3월 6일 → T5 결과 101
어떻게 쓰이나: 평균 종결 일수와 가장 오래된 미결에 쓰여요.
바꾸는 방법: 바꿀 것 없음.
배우기: 엑셀 TODAY, NOW 함수 사용법: 오늘 날짜와 현재 시간 →=IF(L5="", "", IF(L5<=N5, "On time", "Late"))조치 완료일을 기한과 비교합니다.
부분별 설명
L5<=N5- 기한까지 완료면 On time, 아니면 Late.
예시 데이터로 보면: L5 = 2026년 4월 6일, N5 = 2026년 4월 5일 → U5 결과 Late
어떻게 쓰이나: Start Here B20이 기한 내 비율이에요.
바꾸는 방법: 바꿀 것 없음.
배우기: 엑셀 IF 함수 사용법 →=IF(A21="", "", COUNTIFS(Log!$D$5:$D$204,
A21, Log!$S$5:$S$204, "Open")+COUNTIFS(Log!$D$5:$D$204,
A21, Log!$S$5:$S$204, "Overdue"))이 조항의 지적 중 Open이나 Overdue를 셉니다.
부분별 설명
COUNTIFS(Log!$D$5:$D$204, A21, Log!$S$5:$S$204, "Open")- Open.
+COUNTIFS(…, "Overdue")- 지연 포함.
예시 데이터로 보면: D21 결과 2
어떻게 쓰이나: 다음 심사 전에 챙길 규격 부분을 보여줘요.
바꾸는 방법: 바꿀 것 없음.
배우기: 엑셀 COUNTIF, COUNTIFS 함수 사용법 →=COUNTIFS(Log!$B$5:$B$204,
"<="&EOMONTH(A41, 0))-COUNTIFS(Log!$R$5:$R$204,
"<="&EOMONTH(A41, 0))월말까지 제기된 지적 − 월말까지 종결된 지적.
부분별 설명
COUNTIFS(Log!$B$5:$B$204, "<="&EOMONTH(A41, 0))- 월말까지 제기.
-COUNTIFS(Log!$R$5:$R$204, "<="&EOMONTH(A41, 0))- 월말까지 종결.
예시 데이터로 보면: D41 결과 12
어떻게 쓰이나: 경영 검토용 미결 추세예요.
바꾸는 방법: 바꿀 것 없음.
배우기: 엑셀 텍스트 함수와 날짜 함수 사용법 →=IFERROR(INDEX(Log!$A$5:$A$204,
MATCH(MAXIFS(Log!$T$5:$T$204, Log!$L$5:$L$204, ""), Log!$T$5:$T$204, 0))&" ("&MAXIFS(Log!$T$5:$T$204,
Log!$L$5:$L$204, "")&" days)",
"")조치를 안 한 지적 중 경과 일수가 가장 긴 것.
부분별 설명
MAXIFS(Log!$T$5:$T$204, Log!$L$5:$L$204, "")- 조치 미완료 중 최대 경과 일수.
MATCH(…, Log!$T$5:$T$204, 0)- 그 행.
INDEX(Log!$A$5:$A$204, …)&" ("&…&" days)"- ID와 경과 일수.
예시 데이터로 보면: B22 결과 CA-26-004 (237 days)
어떻게 쓰이나: 보통 검토 회의의 첫 안건이에요.
바꾸는 방법: 바꿀 것 없음.
배우기: 엑셀 INDEX와 MATCH 함수 사용법 →팁과 한계
- 용량: 지적 200건. 해마다 새 파일을 쓰고 미결은 옮기세요.
- B6은 =TODAY()라 상태가 저절로 바뀌어요. 심사일 기준으로 보려면 날짜를 직접 넣으세요.
- 근본 원인은 문제를 다시 쓴 게 아니라 원인으로 적으세요: "심사 미실시"가 아니라 "대리 심사원 미지정".
- Not effective는 원인이 틀렸거나 조치가 약했다는 뜻이에요. 기존 조치를 연장하지 말고 새 조치를 올리세요.
- 조항 목록은 9001:2015의 주요 조항이에요. 14001, 45001이나 자체 시스템이라면 Lists 탭에서 고치세요.
이 템플릿에 쓰인 기능 배우기
도움이 되었나요? 잘못된 내용이 있나요? 의견 보내기 · admin@arumsoft.com