엑셀 오류 검사와 통제: 대사 검사, 오류 요약, 시트 보호

통합 문서를 다른 사람에게 넘기기 전에, 그 문서는 스스로 옳다는 걸 보여 줄 수 있어야 해요. 합계가 맞는다고 알려 주는 검사, 어떤 숫자든 원본까지 거슬러 올라가는 추적 경로, 읽는 사람보다 먼저 오타를 잡아내는 표시 같은 것들이죠. 이 글에서는 엑셀의 감사 도구와 수식 몇 개로 작은 비용 보고서에 이런 통제 장치를 더해요.

예시에는 비용 항목 네 개와 누군가 직접 입력한 요약 합계가 있어요. 금액 네 개를 더하면 12,550인데 요약 합계는 12,300이에요.

fx

	A	B	C	D	E
1	분류	금액		점검	결과
2	마케팅	4,200		차이
3	출장	3,800		합계 일치
4	소프트웨어	2,950		음수 금액 없음
5	사무용품	1,600		실패한 점검 수
6	요약 합계	12,300

숫자가 어디서 왔는지 찾기

숫자가 이상해 보이면 먼저 그 숫자가 무엇으로 이루어졌는지 확인하세요. 셀을 선택하고 참조되는 셀 추적(Trace Precedents), 즉 수식› then 수식 분석› then 참조되는 셀 추적을 클릭하면, 그 셀이 읽는 셀에서 셀 자신으로 파란 화살표가 그려져요. 같은 그룹의 참조하는 셀 추적(Trace Dependents)은 반대로, 선택한 셀을 읽는 수식을 보여 줘요.

B6에서 해 보세요. 화살표가 하나도 나타나지 않아요. B6에는 수식이 아니라 직접 입력한 숫자가 들어 있기 때문이에요. 이것만으로도 중요한 발견이에요. 참조되는 셀이 없는 합계는 손으로 입력한 것이고, 금액이 바뀌어도 따라 바뀌지 않아요. 다 봤으면 연결선 제거를 클릭하세요.

다른 방법도 있어요.

• Ctrl+[를 누르면 현재 수식이 참조하는 셀이 선택돼요. 다른 시트에 있어도 돼요.
• Ctrl+`를 누르면 시트 전체에서 결과 대신 수식이 보여요. 다시 누르면 돌아와요.
• 작은 시트 아이콘이 달린 검은 점선 화살표는 다른 시트를 읽는 수식이라는 뜻이에요. 화살표를 두 번 클릭하면 이동 대화 상자가 열려요. 참조를 고르고 확인을 클릭하면 그 위치로 이동해요.

이 도구들은 중급 과정의 수식 감사 글에서 더 자세히 다뤄요. 여기서 기를 습관은 하나예요. 파일을 보내기 전에 가장 믿음이 안 가는 셀을 추적해 보는 거예요.

수식 계산으로 한 단계씩 따라가기

추적은 수식이 어떤 셀을 쓰는지 알려 줘요. 수식 계산(Evaluate Formula)은 그 셀들을 어떻게 계산하는지 보여 줘요. E3에 다음 검사 수식이 있다고 해 볼게요(아래에서 직접 만들어요).

=IF(SUM(B2:B5)=B6,"OK","Mismatch")복사

E3을 클릭하고 수식› then 수식 분석› then 수식 계산을 클릭하세요. 계산을 클릭할 때마다 밑줄 친 부분이 그 값으로 바뀌어요. 계속 클릭하면 수식이 IF(12550=12300,"OK","Mismatch")가 되고, IF(FALSE,"OK","Mismatch")가 된 다음, 마지막에 "Mismatch"가 돼요. 짐작하는 대신 차이가 정확히 어디서 생기는지 눈으로 확인한 거예요.

엑셀이 문제를 짚어 주게 하기

엑셀은 백그라운드에서 수식을 검사하고, 의심스러운 셀의 왼쪽 위 모서리에 작은 초록색 삼각형을 표시해요. 그 셀을 선택하고 옆에 나타나는 경고 아이콘을 클릭하면 텍스트로 저장된 숫자나 일관성 없는 수식 같은 이유가 나오고, 바로 고치거나 무시할 수 있어요.

시트에서 표시된 셀을 차례로 모두 살펴보려면 수식› then 수식 분석› then 오류 검사를 클릭하세요. 오류 검사(Error Checking) 대화 상자가 문제를 하나씩 보여 주고 계산 단계 표시, 오류 무시, 수식 입력줄에서 편집 같은 단추를 제공해요. 다음을 누르면 다음 문제로 넘어가요. #N/A 같은 오류 값이 나온 셀이라면 오류 검사 옆의 화살표를 클릭하고 오류 추적을 고르세요. 오류를 일으킨 셀로 화살표가 그려져요.

초록색 삼각형이 전혀 안 보인다면 백그라운드 검사가 꺼져 있을 수 있어요. 파일› then 옵션› then 수식의 오류 검사 항목에서 켜고, 같은 화면의 오류 검사 규칙 목록도 확인해 보세요. 영역의 다른 수식과 일치하지 않는 수식 같은 규칙이 수식 열 한가운데에 숫자를 덮어쓴 셀을 잡아내요.

대사 검사 추가하기

대사 검사는 같아야 할 두 합계, 예를 들어 세부 행의 합계와 다른 곳에서 온 요약 수치를 비교해서 그 결과를 눈에 보이는 셀에 보여 줘요. 검사는 검사 대상 숫자 옆에 따로 작은 블록을 만들어 레이블과 함께 두세요.

1. 차이를 보여 주세요

   E2에 세부 합계에서 요약 합계를 빼세요. 결과는 250이에요.

   =SUM(B2:B5)-B6복사

   맞다, 틀리다만이 아니라 차이를 보여 주면 합계가 얼마나, 어느 쪽으로 어긋났는지 알 수 있어요.

2. 통과와 실패로 바꾸세요

   E3에 합계가 같으면 OK, 다르면 Mismatch를 표시하세요. 결과는 Mismatch예요.

   =IF(SUM(B2:B5)=B6,"OK","Mismatch")복사

3. 실패가 눈에 띄게 하세요

   E3:E4를 선택하고 조건부 서식(Conditional Formatting)의 홈› then 스타일› then 조건부 서식› then 셀 강조 규칙› then 같음을 고르세요. Mismatch를 입력하고 진한 빨강 텍스트가 있는 연한 빨강 채우기를 고른 다음 확인을 클릭하세요.

E3fx=IF(SUM(B2:B5)=B6,"OK","Mismatch")

	A	B	C	D	E
1	분류	금액		점검	결과
2	마케팅	4,200		차이	250
3	출장	3,800		합계 일치	Mismatch
4	소프트웨어	2,950		음수 금액 없음	OK
5	사무용품	1,600		실패한 점검 수	1
6	요약 합계	12,300

해결 방법은 B6에 입력한 12,300을 =SUM(B2:B5)로 바꾸는 거예요. B6가 다른 시스템에서 가져온 수치여야 한다면 어느 쪽이 틀렸는지 찾아내야 하고요. 검사가 문제를 고쳐 주지는 않아요. 누군가 반드시 들여다보게 만들 뿐이에요.

원 단위 아래 소수가 있는 금액은 여러 번 더하다 보면 아주 작은 차이가 생길 수 있어서, 정확히 =로 비교하면 똑같아 보이는 합계도 Mismatch가 나올 수 있어요. 이럴 땐 차이를 반올림해서 비교하세요. =IF(ROUND(SUM(B2:B5)-B6,2)=0,"OK","Mismatch")

숨겨진 행과 필터된 행 조심하기

SUM은 범위 안의 모든 셀을 더해요. 필터로 숨겨진 행도, 직접 숨긴 행도 포함해요. 합계가 틀려 보이는 대표적인 원인이죠. 목록을 마케팅과 출장만 보이게 필터링하면 보이는 금액은 4,200과 3,800인데, =SUM(B2:B5)는 여전히 12,550이에요.

SUBTOTAL을 쓰면 숨겨진 행을 뺄 수 있어요. 첫 번째 인수로 계산 방식과 무엇을 무시할지 정해요.

9와 109는 모두 합계, 1과 101은 평균, 3과 103은 비어 있지 않은 셀 개수예요. 엑셀 표의 요약 행은 100번대 번호를 써서 늘 화면에 보이는 것과 일치해요. SUBTOTAL은 범위 안에 있는 다른 SUBTOTAL 결과를 건너뛰기 때문에 부분합이 두 번 더해지지도 않아요.

합계마다 어느 쪽이 필요한지 정하세요. 사람들이 필터링하는 목록 아래의 합계는 보이는 것과 맞도록 SUBTOTAL을 쓰세요. 원본 합계와 비교하는 대사 검사는 누군가 필터를 켜 둔 채로 두어도 검사가 실패하지 않도록 SUM을 쓰세요. 어떤 합계인지 레이블을 붙여 두면 읽는 사람도 헷갈리지 않아요.

오류 요약 만들기

검토자가 수식을 하나하나 살펴볼 필요는 없어야 해요. 실패한 검사와 오류 값을 한곳에서 세어 보여 주세요.

실패한 검사는 COUNTIF로 세요. E5에 다음을 입력하면 1이 나와요.

=COUNTIF(E3:E4,"Mismatch")복사

계산 범위 안의 #REF!, #N/A, #DIV/0! 같은 오류 값은 SUMPRODUCT 안에 ISERROR를 넣어 세요. --는 TRUE와 FALSE를 1과 0으로 바꿔서 더할 수 있게 해 줘요.

=SUMPRODUCT(--ISERROR(B2:H200))복사

#N/A만 세려면 ISERROR 대신 ISNA를 쓰세요. #N/A는 대개 조회 함수가 값을 못 찾았다는 뜻이에요. 오류 개수를 세는 수식은 검사하는 범위 밖에 두세요. 범위 안에 두면 자기 자신을 참조하는 순환 참조가 돼요.

마지막으로 시트 맨 위에 상태 셀을 하나 두세요. 예를 들어 G2에 오류 개수가 있다면 =IF(AND(E5=0,G2=0),"발송 가능","오류 확인 필요")처럼요. 검토자는 이 셀부터 읽어요.

완성된 결과물 보호하기

보고서 검사가 끝났다면 누군가 실수로 수식 위에 값을 입력하지 못하게 막으세요. 모든 셀은 기본으로 잠금(Locked) 상태지만, 잠금은 시트를 보호해야 효과가 생겨요.

1. 입력 셀의 잠금을 해제하세요

   사람들이 계속 입력해야 하는 셀(예: B2:B5)만 선택하세요. Ctrl+1을 누르고 보호 탭에서 잠금의 체크를 해제한 다음 확인을 클릭하세요. 수식 입력줄에 수식이 보이지 않게 하려면 수식 셀에서 숨김에 체크하세요.

2. 시트를 보호하세요

   시트 보호(Protect Sheet), 즉 검토› then 보호› then 시트 보호를 클릭하세요. 원하면 암호를 넣고, 필터를 써야 하는 목록이라면 자동 필터 사용처럼 사람들이 계속 해야 하는 동작에 체크하세요. 확인을 클릭하세요.

이제 수식, 검사, 합계는 읽기 전용이고 입력 셀만 열려 있어요. 시트 보호는 실수를 막아 줄 뿐, 작정하고 우회하려는 사람까지 막는 보안 장치는 아니에요.

자주 하는 실수

• 수식 합계 옆에 직접 입력한 합계. 입력한 숫자는 절대 업데이트되지 않아요. 대사 검사를 하거나 수식으로 바꾸세요.
• 아무도 안 보는 검사. 숨긴 시트에 있는 검사는 없는 것과 같아요. 요약 상태를 읽는 사람이 가장 먼저 보는 곳에 두고, 실패는 강조하세요.
• 소수가 있는 금액을 정확히 비교하기. 비교하기 전에 차이를 반올림하세요.
• 필터된 목록 아래의 SUM. 숨겨진 행까지 더해요. 합계가 보이는 것과 맞아야 한다면 SUBTOTAL(9,…)이나 SUBTOTAL(109,…)를 쓰세요.
• 모든 수식을 IFERROR로 감싸기. 검사로 잡아내야 할 문제를 바로 그 함수가 숨겨요.
• 입력 셀의 잠금을 풀기 전에 시트 보호하기. 사람들이 숫자를 입력할 수 없어요. 검토› then 보호› then 시트 보호 해제를 클릭하고 입력 셀의 잠금을 푼 다음 다시 보호하세요.

연습

연습

보내기 전에 보고서 감사하기

1. 곧 보낼 보고서를 하나 골라, 세부 행의 합계와 같아야 하는 요약 수치를 찾으세요.
2. 차이와 OK/Mismatch 검사로 이루어진 검사 블록을 추가하고, 실패하면 빨간색으로 강조되게 하세요.
3. 가장 믿음이 안 가는 셀에 참조되는 셀 추적을 실행해서 원본까지 따라가 보세요.
4. 계산 영역의 오류 개수를 SUMPRODUCT와 ISERROR로 세고, 맨 위에 상태 셀을 하나 두세요.
5. 필터를 쓰는 목록이 있다면 그 합계가 SUM인지 SUBTOTAL인지, 그게 원하는 동작인지 확인하세요.
6. 검사에서 발견한 문제를 고치고, 입력 셀의 잠금을 푼 다음 시트를 보호하세요.

비용 데이터로 검사 블록을 직접 만들어 보세요. B6의 요약 합계는 손으로 입력한 값이에요.

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

1. 1.E2에 금액 네 개의 합계에서 B6에 직접 입력한 요약 합계를 빼 보세요.힌트 보기
2. 2.E3에 금액 네 개의 합계가 B6의 요약 합계와 같으면 OK, 다르면 Mismatch를 표시해 보세요.힌트 보기
3. 3.E4에 B2:B5에 음수 금액이 하나도 없으면 OK, 있으면 Mismatch를 표시해 보세요.힌트 보기
4. 4.E5에 E3:E4의 점검 중 Mismatch가 몇 개인지 세어 보세요.힌트 보기

E2fx수식 입력 셀 E2Enter ↵

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

	A	B	C	D	E
1	분류	금액		점검	결과
2	마케팅	4,200		차이
3	출장	3,800		합계 일치
4	소프트웨어	2,950		음수 금액 없음
5	사무용품	1,600		실패한 점검 수
6	요약 합계	12,300

실력 점검

• 수식이 어떤 셀에 의존하는지, 어떤 수식이 특정 셀에 의존하는지 추적할 수 있나요?
• 수식 계산으로 수식의 계산 과정을 단계별로 따라갈 수 있나요?
• 세부 합계와 요약 합계를 비교하는 대사 검사를 추가할 수 있나요?
• 합계에 SUM 대신 SUBTOTAL을 써야 할 때를 설명할 수 있나요?
• 문제를 숨기지 않고 드러내는 오류 요약을 만들 수 있나요?