엑셀 모델 테스트와 감사 추적 남기기

보고서가 나가면 사람들은 만든 사람을 믿기 때문에 그 보고서를 믿어요. 하지만 이 방식은 오래가지 못해요. 내가 휴가를 간 사이 다른 사람이 파일을 새로 고치고, 행 하나가 소리 없이 사라지니까요. QA 계층은 이런 믿음을 증거로 바꿔 줘요. 원본 행은 모두 세고, 합계는 모두 원본과 맞춰 보고, 문제는 모두 목록으로 보여 주고, 변경 사항은 모두 기록해요. 그러면 만든 사람에게 묻지 않아도 누구나 결과를 확인할 수 있어요.

점검은 전용 시트에 모으기

모든 테스트를 QA라는 시트 하나에 모으세요. 점검 하나가 한 행이에요. 레이블, 수식, 그리고 OK나 Mismatch로 나오는 결과로 구성돼요. 맨 위에는 전체 상태를 두어서 검토하는 사람이 통합 문서가 배포해도 되는 상태인지 한눈에 알 수 있게 하세요.

아래 예시는 가져온 주문 8건과, 원본 시스템 보고서에서 옮겨 적은 Summary Total이에요. 주문 O4는 Product ID 없이 들어왔어요. 실제 통합 문서라면 데이터는 별도 시트에 있겠지만, 여기서는 둘을 함께 볼 수 있도록 점검 옆에 두었어요.

fx

	A	B	C	D	E	F
1	Order ID	Product ID	Revenue		점검	결과
2	O1	P1	120		원본 행 수(내보내기)	8
3	O2	P2	340		불러온 행 수
4	O3	P3	95		행 수 점검
5	O4		210		매출 점검
6	O5	P2	180		Product ID 누락
7	O6	P1	260
8	O7	P3	150
9	O8	P1	300
10	Summary Total		1,655

빠진 행이 없다는 걸 증명하기: 레코드 수

가져오기가 제대로 됐다는 가장 간단한 증거는 개수예요. 원본이 보냈다고 하는 행 수와 실제로 들어온 행 수를 비교하는 거죠. 원본 행 수는 통합 문서 밖에서 가져오세요. 예를 들어 내보내기 파일의 바닥글에 찍힌 행 수나 원본 시스템 자체의 보고서를 보고, 입력값으로 적어 두세요(여기서는 F2).

1. 불러온 행 수를 세세요

   F3에 Order ID의 개수를 세세요. COUNTA는 비어 있지 않은 셀을 모두 세기 때문에 텍스트 ID에도 쓸 수 있어요. 결과는 8이에요.

   =COUNTA(A2:A9)복사

   ID처럼 항상 채워져 있어야 하는 열을 세세요. Product ID를 세면 7이 나와서, 데이터 자체의 문제를 가져오기 탓으로 돌리게 돼요.

2. 원본 행 수와 비교하세요

   F4에서 두 개수를 비교하세요. 결과는 OK예요.

   =IF(F3=F2,"OK","Mismatch")복사

데이터가 원본 가져오기, 정리된 스테이징 표, 모델처럼 여러 단계를 거친다면 단계마다 행 수를 세세요. 8행이 들어와서 7행만 모델에 도달했다면 어느 단계에서 한 행이 빠졌는지 개수가 알려 줘요. 필터에 걸려 빠진 행이 더는 미스터리가 아니게 되죠.

보고서를 원본과 대사하기

레코드 수는 행이 도착했다는 걸 증명하고, 통제 합계는 값이 제대로 도착했다는 걸 증명해요. 불러온 데이터의 합계를 다른 곳에서 얻은 합계와 비교하세요. 원본 시스템의 보고서, 내보내기 파일의 바닥글, 지난달 마감 수치 같은 것들이에요.

F5에서 Revenue 합계를 C10의 Summary Total과 비교하세요. 120 + 340 + 95 + 210 + 180 + 260 + 150 + 300은 1,655라서 결과는 OK예요.

=IF(SUM(C2:C9)=C10,"OK","Mismatch")복사

같은 점검을 파이프라인 처음뿐 아니라 끝에서도 하세요. 대시보드의 합계도 원본 합계와 맞아야 해요. 그래야 새로 고치지 않은 피벗 테이블이나 누군가 걸어 두고 잊은 필터를 잡아낼 수 있어요.

표 전체가 아니라 예외만 나열하기

검토하는 사람이 5,000행을 훑으며 빈칸을 찾게 하면 안 돼요. 예외 보고서는 규칙을 어긴 행만 나열하기 때문에 점검할 것은 "이 목록이 비어 있나?" 하나뿐이에요.

F6에 Product ID가 없는 주문을 나열하세요. 결과는 O4예요. 더 있다면 아래 셀로 분산돼요.

=FILTER(A2:A9,B2:B9="","None")복사

세 번째 인수 "None"은 모든 행이 통과했을 때 FILTER가 보여 줄 값이에요. 이 인수가 없으면 결과가 비었을 때 #CALC! 오류가 나서, 깨끗한 점검이 아니라 고장 난 점검처럼 보여요.

여러 규칙을 한 번에 테스트하려면 조건을 더하세요. FILTER 안에서 +는 "또는"이라는 뜻이에요. 다음 수식은 Product ID가 비어 있거나 Revenue가 0 이하인 주문을 세 열 모두 돌려줘요.

=FILTER(A2:C9,(B2:B9="")+(C2:C9<=0),"None")복사

예시 데이터에서는 O4, 0, 210 한 행이 나와요. FILTER는 빈 셀을 0으로 표시하기 때문에 가운데의 0이 바로 빠진 Product ID예요.

F6fx=FILTER(A2:A9,B2:B9="","None")

	A	B	C	D	E	F
1	Order ID	Product ID	Revenue		점검	결과
2	O1	P1	120		원본 행 수(내보내기)	8
3	O2	P2	340		불러온 행 수	8
4	O3	P3	95		행 수 점검	OK
5	O4		210		매출 점검	OK
6	O5	P2	180		Product ID 누락	O4
7	O6	P1	260
8	O7	P3	150
9	O8	P1	300
10	Summary Total		1,655

QA 시트는 모든 점검을 통과할 때만 통과로 바뀌는 전체 상태로 마무리하세요.

=IF(AND(F4="OK",F5="OK",F6="None"),"PASS","FAIL")복사

여기서는 FAIL이 나와요. 행 수와 합계는 괜찮지만 O4에 아직 Product ID가 없기 때문이에요. 원본을 고치고 새로 고치면 상태가 PASS로 바뀌어요.

수식 열이 일관된지 테스트하기

가장 흔한 조용한 오류 중 하나는 수식으로 채워진 열에서 누군가 셀 하나를 값으로 덮어쓴 경우예요. 다른 숫자와 똑같아 보이지만 다시는 업데이트되지 않아요. 아래 시트에서 C5는 2 × 90 = 180이어야 하는데, 누군가 175를 입력했어요.

fx

	A	B	C	D
1	수량	단가	금액	수식?
2	4	25	100	TRUE
3	3	40	120	TRUE
4	5	12	60	TRUE
5	2	90	175	FALSE
6	6	15	90	TRUE
7	1	200	200	TRUE
8	합계		745

찾는 방법은 세 가지예요.

• 상수 찾기. C2:C7을 선택하고 홈› then 편집› then 찾기 및 선택› then 이동 옵션을 고른 다음, 이동 옵션(Go To Special) 대화 상자에서 상수를 선택하고 확인을 누르세요. 엑셀이 범위 안에서 수식이 아니라 값이 입력된 셀만 선택해요. 여기서는 C5예요.
• 셀마다 테스트하기. =ISFORMULA(C2)는 수식이면 TRUE, 직접 입력한 값이면 FALSE를 돌려줘요. D열처럼 도우미 열에 쓰거나, 조건부 서식(Conditional Formatting)의 수식을 사용하여 서식을 지정할 셀 결정 규칙에 =NOT(ISFORMULA(C2))를 넣어 덮어쓴 셀에 색을 칠하세요.
• 합계를 따로 다시 계산하기. =SUMPRODUCT(A2:A7,B2:B7)은 수량과 단가로 합계를 직접 계산해요. 결과는 750인데 =SUM(C2:C7)은 745예요. 5만큼의 차이가 어느 행이 틀렸다는 증거예요.

엑셀의 오류 검사(Error Checking)도 도움이 돼요. 셀 모서리의 초록색 삼각형은 그 셀의 수식이 주변 수식과 다르다는 뜻일 수 있어요. Ctrl+`를 눌러 결과 대신 수식을 표시하고 열을 훑어보세요. 패턴이 다른 셀이 눈에 띄어요.

아무도 덮어쓰면 안 되는 셀 잠그기

보호를 걸면 애초에 수식을 덮어쓸 수 없어요. 모든 셀은 기본적으로 잠겨 있지만, 잠금은 시트를 보호해야 효과가 생겨요.

1. 입력 셀의 잠금을 푸세요

   사람이 입력해야 하는 셀만 선택하세요. Ctrl+1을 누르고 보호 탭에서 잠금의 체크를 해제한 다음 확인을 누르세요.

2. 시트를 보호하세요

   검토› then 보호› then 시트 보호를 고르고 확인을 누르세요. 이제 수식, 머리글, 점검은 읽기 전용이 되고 입력 셀만 열려 있어요.

시트 보호(Protect Sheet)는 실수를 막아 줄 뿐, 작정하고 우회하려는 사람까지 막지는 못해요. 통합 문서를 안전하게 배포하는 방법은 별도 글에서 다뤄요.

변경 로그 남기기

버전 번호는 무언가 바뀌었다는 것만 알려 줘요. 변경 로그는 무엇이, 언제, 누구에 의해, 왜 바뀌었는지 알려 주고, 숫자가 달라져서 누군가 이유를 물을 때 필요한 게 바로 이거예요. 별도 시트에 표로 만들고, 로직이나 데이터 출처나 레이아웃이 바뀔 때마다 한 행씩 추가하세요.

날짜는 Ctrl+;로 입력해야 나중에 바뀌지 않아요. 일상적인 데이터 새로 고침은 여기가 아니라 문서화 시트에 기록하세요.

통합 문서를 OneDrive나 SharePoint에 저장했다면 파일› then 정보› then 버전 기록에서 이전 버전을 열거나 복원할 수도 있어요. 하지만 이건 안전망일 뿐 로그를 대신하지는 못해요. 파일이 바뀌었다는 건 보여 줘도 왜 바뀌었는지는 알려 주지 않으니까요.

배포할 때마다 검토 체크리스트 실행하기

매번 실행하는 짧고 구체적인 목록이 있으면 "괜찮은 것 같아요"가 누구나 확인할 수 있는 것으로 바뀌어요. 예를 들면 다음과 같아요.

1. QA 시트의 전체 상태가 PASS인가요?
2. 레코드 수와 통제 합계가 원본과 맞나요?
3. 예외 보고서가 비어 있거나, 남은 예외마다 설명이 붙어 있나요?
4. 수식 열에 직접 입력한 값이 없나요? (열마다 이동 옵션 › 상수로 확인)
5. 시트 보호가 켜져 있나요?
6. 이번 배포에 대한 행이 변경 로그에 있나요?

가능하면 만든 사람이 아닌 다른 사람에게 체크리스트를 맡기세요. 만든 사람 눈에 더는 보이지 않는 문제를 검토자는 찾아내요.

자주 하는 실수

• 절대 실패하지 않는 점검. 행을 지우거나 금액을 바꿔서 점검마다 한 번씩 일부러 실패하게 만들어 보세요. 상태가 계속 OK라면 점검이 잘못된 거예요.
• 비어 있을 수 있는 열 세기. 선택 입력 필드에 COUNTA를 쓰면 개수가 적게 나와요. ID 열을 세세요.
• 데이터를 자기 자신과 대사하기. 같은 셀의 SUM과 비교한 합계는 항상 맞아요. 통제 합계는 통합 문서 밖에서 가져와야 해요.
• 아무도 읽지 않는 예외 보고서. 예외 목록이 비어 있지 않으면 전체 상태가 FAIL이 되도록 연결하세요.
• 로그 없는 버전 번호. 설명하는 행이 없는 "v1.4"로는 아무도 변경을 추적할 수 없어요.

연습

연습

실제 통합 문서에 QA 시트 추가하기

1. 매출 내보내기 파일이나 근무 시간 다운로드처럼 다른 곳에서 데이터를 받아 오는 통합 문서를 하나 고르세요.
2. QA 시트를 추가하고, 원본의 행 수와 합계를 입력값으로 적으세요.
3. OK 또는 Mismatch를 돌려주는 레코드 수 점검과 대사 점검을 작성하세요.
4. 비어 있으면 안 되는 필수 필드 하나에 대해 FILTER로 예외 보고서를 추가하세요.
5. 전체 PASS/FAIL 상태를 추가한 다음, 데이터를 일부러 망가뜨려 점검마다 실패하는지 확인하세요.
6. 오늘 날짜로 변경 로그를 시작하고, 방금 추가한 QA 시트를 설명하는 행을 적으세요.

아래에서 점검 네 개를 작성해 보세요. 앞의 결과를 뒤에서 쓰니 순서대로 하세요.

직접 해 보기4개 중 0개 완료

1. 1.F3에 A2:A9의 Order ID를 세어 불러온 주문 수를 구해 보세요.힌트 보기
2. 2.F4에 F3의 불러온 행 수가 F2의 원본 행 수와 같으면 OK, 아니면 Mismatch를 표시해 보세요.힌트 보기
3. 3.F5에 C2:C9의 Revenue 합계와 C10의 Summary Total을 비교해서 같으면 OK, 다르면 Mismatch를 표시해 보세요.힌트 보기
4. 4.F6에 Product ID가 비어 있는 Order ID를 표시하고, 없으면 None을 표시해 보세요.힌트 보기

F3fx수식 입력 셀 F3Enter ↵

점선으로 표시된 셀을 클릭하고, 입력줄에 수식을 쓴 다음 Enter를 누르세요.

	A	B	C	D	E	F
1	Order ID	Product ID	Revenue		점검	결과
2	O1	P1	120		원본 행 수(내보내기)	8
3	O2	P2	340		불러온 행 수
4	O3	P3	95		행 수 점검
5	O4		210		매출 점검
6	O5	P2	180		Product ID 누락
7	O6	P1	260
8	O7	P3	150
9	O8	P1	300
10	Summary Total		1,655

실력 점검

• 가져오는 동안 빠진 행이 없음을 증명하는 레코드 수 점검을 작성할 수 있나요?
• 보고서 합계를 원본에서 가져온 합계와 대사할 수 있나요?
• 누락되거나 잘못된 값을 나열하고, 없을 때는 "None"을 표시하는 예외 보고서를 만들 수 있나요?
• 수식 열에서 값으로 덮어쓴 셀을 찾을 수 있나요?
• 변경 로그에는 버전 번호에 없는 어떤 정보가 담기는지 설명할 수 있나요?