Ch12_수식 문제 해결

엑셀에서 수식을 작성하다 보면 계산 결과가 예상과 다르게 나타나는 경우가 있다. #VALUE!, #REF!와 같이 오류 메시지가 표시되는 경우도 있지만, 더 찾기 어려운 경우는 오류 메시지는 나타나지 않으면서 잘못된 셀을 참조하여 계산 결과만 틀린 경우이다. 예를 들어 할인금액을 계산하면서 같은 행의 할인율을 사용하여야 하는데 위쪽 행의 할인율을 잘못 참조하여도 Excel은 정상적인 수식으로 판단한다. 이러한 경우에는 수식 자체만 보는 것보다 수식이 어느 셀에서 값을 가져오고 있는지를 추적하는 것이 효과적이다.

Excel 365에서는 이를 위하여 참조되는 셀 추적, 참조하는 셀 추적, 화살표 제거 기능을 제공한다. 

---

Section 01. 수식 추적

이번 Section에서는 Ch12_Section01_수식추적_종합실습.xlsx 파일을 이용하여 정상적인 계산 흐름을 먼저 확인하고, 이어서 잘못된 수식을 직접 찾아 수정한다.

1. 수식 추적이란?

수식 추적은 수식과 셀 사이의 참조 관계를 화살표로 표시하여 계산의 흐름을 확인하는 기능이다. 
예를 들어 다음과 같은 판매 데이터가 있다고 가정한다.

셀	항목	값 또는 수식
B2	판매수량	10
C2	단가	15,000
D2	판매금액	=B2*C2
E2	할인율	10%
F2	할인금액	=D2*E2
G2	최종금액	=D2-F2

G2의 최종금액은 단순히 G2 하나에서 계산되는 것이 아니다.

B2 ─┐
    ├─→ D2 ───────┐
C2 ─┘             │
                  ├─→ G2
E2 ─────→ F2 ─────┘

이처럼 하나의 계산 결과에는 여러 셀이 연결되어 있을 수 있다. 수식 추적 기능을 이용하면 이러한 관계를 화살표로 직접 확인할 수 있다.

수식 추적 메뉴

Excel 365에서는 다음 위치에서 수식 추적 기능을 사용할 수 있다.

[수식] 탭 → [수식 분석] 그룹

주요 기능은 다음과 같다.

기능	역할
참조되는 셀 추적	현재 수식이 어떤 셀의 값을 사용하고 있는지 확인한다.
참조하는 셀 추적	현재 셀의 값을 어떤 수식이 사용하고 있는지 확인한다.
화살표 제거	화면에 표시된 추적 화살표를 제거한다.

Microsoft에서는 수식에 데이터를 제공하는 셀을 선행 셀(Precedent), 해당 셀의 값을 참조하여 계산하는 셀을 종속 셀(Dependent)이라고 설명한다.

참조되는 셀 추적

참조하는 셀 추적

연결설 제거

🔎 참조되는 셀 추적( Trace Precedents ) 화살표를 읽는 방법

수식 추적에서는 화살표의 방향이 중요하다.

원본 데이터 ─────→ 수식 결과

따라서 화살표가 현재 선택한 셀을 향하여 들어온다면, 해당 셀들이 현재 수식의 계산에 사용되고 있다는 의미이다. 예를 들어

B2 ────→
         D2
C2 ────→

D2는 B2와 C2를 참조하고 있다.

🔎 참조하는 셀 추적(Trace Dependents)

참조하는 셀 추적은 현재 선택한 셀의 값을 어떤 수식이 사용하고 있는지 확인한다. 예를 들어 D2에는 판매금액이 들어 있다고 하자.

=B2*C2

D2를 선택한 후

[수식] → [수식 분석] → [참조하는 셀 추적]

을 선택한다. D2를 이용하는 수식이 F2와 G2에 있다면 다음과 같이 표시된다.

        ┌────→ F2
D2 ─────┤
        └────→ G2

즉,

D2 값을 F2와 G2에서 사용하고 있다. 라는 의미이다.

기능	질문	화살표 방향
참조되는 셀 추적	이 수식은 어디에서 값을 가져왔는가?	다른 셀 → 현재 셀
참조하는 셀 추적	이 셀은 어디에서 사용되고 있는가?	현재 셀 → 다른 셀

---

🧪 실습 01. 판매금액의 원본 찾기

02_수식추적_실습 시트에서 F5 셀을 선택한다. F5에는 무선 마우스의 판매금액을 계산하는 다음 수식이 입력되어 있다.

=D5*E5

다음 순서로 실행한다.

1. F5 셀을 선택한다.
2. [수식] 탭을 선택한다.
3. [참조되는 셀 추적]을 클릭한다.

화면에는 D5와 E5에서 F5 방향으로 화살표가 표시된다.

D5 수량 ───────┐
               ├────→ F5 판매금액
E5 단가 ───────┘

이 화살표의 의미는 다음과 같다.

F5의 판매금액은 D5의 수량과 E5의 단가를 이용하여 계산되고 있다.

따라서 참조되는 셀 추적은 현재 결과가 어디에서 만들어졌는지를 찾는 기능이라고 이해하면 쉽다.

할인금액의 참조 관계 추적하기

판매금액보다 한 단계 복잡한 계산을 살펴보자. H5에는 할인금액이 계산되어 있다.

수식은 다음과 같다.

=F5*G5

F5에는 판매금액이 있고 G5에는 할인율이 있다. 따라서 계산 관계는 다음과 같다.

F5 판매금액 ────┐
                ├────→ H5 할인금액
G5 할인율 ──────┘

🧪 실습 02. 할인금액의 원본 찾기

1. H5 셀을 선택한다.
2. [수식] → [참조되는 셀 추적]을 선택한다.
3. 표시되는 화살표를 확인한다.

화살표가 다음 두 셀에서 H5로 연결되는지 확인한다.

F5 → H5
G5 → H5

여기에서 중요한 것은 현재 선택한 셀을 향하여 들어오는 화살표이다. 참조되는 셀 추적에서는 항상 다음과 같이 읽으면 된다.

원본 데이터 → 현재 수식

🧪 실습 03. 최종금액 계산 흐름 전체 추적하기

이번에는 I5를 이용한다.

Step 1. 첫 번째 단계 확인

• I5 셀을 선택한다.
• 다음 메뉴를 실행한다.

[수식] → [참조되는 셀 추적]

화면에는 다음 관계가 표시된다.

F5 ─────→ I5
H5 ─────→ I5

I5의 최종금액은 판매금액 F5와 할인금액 H5를 사용한다.

Step 2. 한 단계 더 추적하기

• I5를 선택한 상태에서 [참조되는 셀 추적]을 다시 한 번 클릭한다.
• 이번에는 F5와 H5가 어떤 셀로부터 만들어졌는지도 나타난다.

D5 ──┐
     ├──→ F5
E5 ──┘

F5 ──┐
     ├──→ H5
G5 ──┘

F5, H5 ──→ I5

이러한 과정을 이용하면 복잡한 수식에서도 계산의 시작점까지 거슬러 올라갈 수 있다.

🔑 수식 오류를 찾는 기본 사고과정

결과가 이상하다
      ↓
이 셀은 어떤 셀을 참조하는가?
      ↓
참조되는 셀 추적
      ↓
그 앞 단계도 정상인가?
      ↓
다시 참조되는 셀 추적

📌 Section 01 종합 문제

마지막에는 학생들에게 메뉴를 알려주지 않고 다음 문제를 수행하도록 하면 좋다.

문제
02_수식추적_실습과 03_오류찾기 시트를 이용하여 다음 작업을 수행하시오.

① 무선 마우스의 판매금액이 어떤 셀을 이용하여 계산되는지 확인한다.
② 할인금액을 계산하는 셀의 원본 셀을 확인한다.
③ 최종금액 셀에서 참조 관계를 두 단계까지 추적한다.
④ 판매금액 셀이 다른 어떤 계산에 사용되는지 확인한다.
⑤ 화면의 추적 화살표를 모두 제거한다.
⑥ 03_오류찾기 시트에서 H6의 잘못된 참조를 찾아 정상적으로 수정한다.
⑦ I8의 잘못된 참조를 찾아 정상적으로 수정한다.

이 종합 문제를 완료하면 학생은 참조되는 셀 추적 → 다단계 추적 → 참조하는 셀 추적 → 화살표 제거 → 잘못된 참조 발견 및 수정까지 Section 01의 내용을 한 흐름으로 학습하게 된다.

---

Section 02. 오류 검사

수식이 복잡해질수록 오류는 단순히 #DIV/0!이나 #REF!처럼 눈에 보이는 형태로만 나타나지 않는다. 계산 결과는 숫자로 정상적으로 표시되지만 참조한 셀이 잘못되어 결과 자체가 틀리는 경우도 있다.

첨부한 파일의 03_오류찾기 시트가 바로 이러한 상황을 연습하도록 구성되어 있다. 특히 H6과 I8은 오류 값이 나타나지는 않지만 주변 수식과 다른 셀을 참조하고 있어 잘못된 계산 결과를 만든다.

Excel 365에서는 이러한 문제를 찾기 위하여 [수식] 탭 → [수식 분석] 그룹 → [오류 검사] 기능을 제공한다. 오류 검사는 오류 값뿐 아니라 주변 수식과 일치하지 않는 수식 등 여러 규칙을 이용하여 문제 가능성이 있는 셀을 찾아낸다. 다만 Microsoft도 오류 검사 규칙만으로 모든 수식 오류를 찾아낼 수 있는 것은 아니라고 명시하고 있다. 따라서 Expert 수준에서는 오류 검사 + 수식 표시 + 수식 추적을 함께 사용하는 방법을 익혀야 한다.

---

1. 오류 검사란?

오류 검사(Error Checking)는 워크시트에서 잘못 작성되었거나 다른 수식과 패턴이 다른 수식을 자동으로 찾아주는 기능이다. Excel은 일반적으로 다음과 같은 두 종류의 문제를 발견한다.

구분	예	특징
명확한 수식 오류	#DIV/0!, #REF!, #VALUE!	계산 결과 자체가 오류로 표시
잠재적 수식 오류	=F6*G5	숫자는 나오지만 주변 수식 패턴과 다름

첨부 파일에서 중요하게 살펴볼 것은 두 번째 유형이다.

03_오류찾기 시트의 H열

정상적인 패턴은 다음과 같다.

H5 = F5*G5
H6 = F6*G6
H7 = F7*G7
H8 = F8*G8

그러나 실제 H6에는 다음과 같은 수식이 들어 있다.

=F6*G5

수식 자체의 문법에는 문제가 없다. 따라서 #VALUE! 등의 오류도 나타나지 않는다. 하지만 Excel이 주변 수식과 비교하면

H5 : F5 × G5
H6 : F6 × G5  ← 패턴 불일치
H7 : F7 × G7

와 같이 H6만 다른 행을 참조한다.

이것이 오류 검사에서 매우 중요하게 다루는 '일관성 없는 수식'이다. Microsoft에서도 주변 수식과 참조 패턴이 일치하지 않는 경우 Excel이 오류 검사 표시를 할 수 있다고 설명한다.

메뉴 위치

[수식] → [수식 분석] → [오류 검사]

 

오류 검사	워크시트의 수식 오류를 순차적으로 검사
오류 추적	오류가 발생한 원인을 화살표로 추적
순환 참조	자기 자신을 직접 또는 간접적으로 참조하는 수식 확인

💻 실습하기 01. H6의 잘못된 할인율 찾기

첨부 파일에서 다음 시트를 선택한다.

03_오류찾기

H6의 할인금액이 다른 데이터와 비교하여 정상인지 확인한다.

① H6 선택

H6에는 다음 수식이 입력되어 있다.

=F6*G5

여기에서

• F6 : 판매금액
• G5 : P001의 할인율

을 참조하고 있다. 그러나 H6은 P002 상품의 할인금액이므로 G6을 사용하여야 한다.

• Excel의 백그라운드 오류 검사가 활성화되어 있다면 문제가 있다고 판단한 셀의 왼쪽 위에 삼각형 표시가 나타날 수 있다.

 

③ 주변 수식과 비교한다

✅ 수정

=F6*G6

3. 오류 검사와 수식 추적을 함께 사용한다

오류 검사에서 문제 가능성이 발견되었다고 하여 수식을 곧바로 수정하기보다는 왜 문제가 발생하였는지 확인하는 과정이 중요하다. 앞 Section에서 학습한 참조되는 셀 추적을 사용하면 훨씬 쉽게 원인을 발견할 수 있다.

💻 실습하기 02. H6을 수식 추적으로 검증하기

H6을 선택한다.

[수식] → [참조되는 셀 추적]

현재 잘못된 수식은

=F6*G5

이므로 화살표는

F6 ───────┐
          ├──→ H6
G5 ───────┘

형태로 나타난다. 여기에서 G5에서 H6으로 연결된 화살표가 이상하다는 것을 바로 확인할 수 있다. 수식을

=F6*G6

으로 수정한 뒤 다시 추적하면

F6 ───────┐
          ├──→ H6
G6 ───────┘

가 되어 정상적인 참조 구조를 확인할 수 있다.

📌 핵심 요점

오류 검사는 "문제가 있을 가능성이 있는 셀"을 찾는 기능이고, 수식 추적은 "왜 문제가 발생하였는가"를 분석하는 기능이다. 이 두 기능을 연결하여 사용하는 것이 중요하다.

---

3. 오류 검사에서 사용하는 주요 규칙

Excel 365에서는 오류 검사 규칙을 사용자가 설정할 수 있다.

[파일] → [옵션] → [수식] → [오류 검사]

 

오류 검사 대상	의미
오류 결과를 생성하는 수식	#DIV/0!, #REF!, #VALUE! 등
영역 내 다른 수식과 일치하지 않는 수식	주변 수식의 패턴과 다름
영역의 셀을 생략한 수식	합계 범위 일부가 빠짐
빈 셀을 참조하는 수식	계산에서 예상하지 않은 빈 셀 참조
잠금 해제된 수식 셀	보호 대상이 되어야 할 수식 셀이 잠금 해제
테이블의 일관되지 않은 계산 열 수식	같은 열에서 일부 수식만 다름

이 가운데 MOS Expert 학습에서 특히 중요하게 볼 부분은 주변 수식과 일치하지 않는 수식과 범위를 생략한 수식이다.

---

5. 오류 검사에서 발견되는 대표적인 오류 값

#DIV/0!

• 0 또는 빈 셀로 나누려고 할 때 발생한다.

=A1/B1

B1이 0이면

#DIV/0!

이 발생한다.

#REF!

• 수식에서 참조하던 셀이 삭제된 경우 발생한다.

예를 들어

=A1+B1

에서 B열을 삭제하면

=A1+#REF!

형태가 될 수 있다.

#VALUE!

• 수식에 사용할 수 없는 데이터 형식이 들어간 경우 발생할 수 있다. 
• 예를 들어 숫자 계산에 문자 데이터가 잘못 사용되는 경우이다.

#NAME?

• Excel이 함수 이름이나 정의된 이름을 인식하지 못할 때 발생한다.

예를 들어

=SUUM(A1:A10)

처럼 SUM의 철자를 잘못 작성한 경우이다.

#N/A

• 조회 함수 등에서 찾으려는 값을 찾지 못할 때 자주 발생한다.

=XLOOKUP(A2,D2:D10,E2:E10)

에서 A2에 해당하는 값을 찾지 못하면 #N/A가 발생할 수 있다.

💻 실습하기 05. 첨부 파일에서 오류를 한 번에 찾아보기

03_오류찾기 시트를 연다.

STEP 1

`Ctrl + ``를 눌러 수식 표시로 변경한다. H열을 살펴본다.

H5 =F5*G5
H6 =F6*G5   ← 이상
H7 =F7*G7
H8 =F8*G8

H6의 패턴이 다르다는 것을 발견할 수 있다.

STEP 2

I열을 살펴본다.

I5 =F5-H5
I6 =F6-H6
I7 =F7-H7
I8 =F8-H7   ← 이상
I9 =F9-H9

I8의 참조가 잘못되었다.

STEP 3

H6을 다음과 같이 수정한다.

=F6*G6

I8은 다음과 같이 수정한다.

=F8-H8

STEP 4

`Ctrl + ``를 다시 눌러 계산 결과 화면으로 돌아간다.

STEP 5

H6과 I8을 각각 선택하여

[참조되는 셀 추적]

을 실행한다. 정상적인 관계가 다음과 같이 나타나는지 확인한다.

F6 ──┐
     ├─→ H6
G6 ──┘

그리고 

F8 ──┐
     ├─→ I8
H8 ──┘

이면 수정이 완료된 것이다.

---

Section 03. 조사식 창

1. 조사식 창의 이해

엑셀에서 수식을 작성하다 보면 하나의 셀만 확인하는 것으로는 계산 과정을 파악하기 어려운 경우가 있다. 특히 여러 시트에 데이터가 나누어져 있거나, 한 셀의 결과가 다른 셀의 계산에 연속적으로 사용되는 경우에는 중요한 셀의 값을 계속 확인할 필요가 있다.

조사식 창(Watch Window)은 이러한 상황에서 특정 셀의 값, 수식, 셀 주소, 시트 위치를 한곳에서 계속 확인할 수 있도록 하는 기능이다. 조사식 창을 열려면 다음 메뉴를 이용한다.

[수식] → [수식 분석] → [조사식 창]

조사식 창은 수식을 직접 수정하여 주는 기능은 아니다. 대신 중요한 셀을 등록하여 두고, 다른 셀이나 다른 시트에서 작업하는 동안 해당 셀의 변화 상태를 계속 확인할 수 있도록 한다.

2. 조사식 창에서 확인할 수 있는 정보

조사식 창에 셀을 등록하면 다음과 같은 항목이 표시된다.

항목	설명
통합 문서	해당 셀이 포함된 엑셀 파일
시트	셀이 위치한 워크시트
이름	정의된 이름이 있는 경우 표시
셀	등록한 셀의 주소
값	현재 계산된 결과
수식	해당 셀에 입력된 수식

따라서 조사식 창은 단순히 결과값만 확인하는 기능이 아니라, 수식의 구조와 계산 결과를 동시에 관찰하는 도구라고 할 수 있다.

💻 실습하기. 판매금액 계산 과정을 조사식 창으로 확인하기

첨부한 실습 파일의 03_오류찾기 시트를 이용하여 조사식 창의 기능을 살펴보자.

다음과 같은 계산 구조가 있다고 가정한다.

수량 × 단가
    ↓
판매금액
    ↓
할인금액
    ↓
최종금액

6행을 기준으로 다음 셀을 조사식에 등록한다.

F6 : 판매금액
G6 : 할인율
H6 : 할인금액
I6 : 최종금액

---

 

3. 조사식 추가하기

먼저 F6 셀을 선택한다. 그 다음

[수식] → [수식 분석] → [조사식 창]

을 클릭한다. 화면에 조사식 창이 나타나면 [조사식 추가]를 클릭한다. 선택한 셀이 다음과 같이 표시된다.

='03_오류찾기'!$F$6

[추가]를 클릭하면 F6 셀이 조사식 목록에 등록된다. 같은 방법으로 G6, H6, I6도 추가한다. 
조사식 창에는 다음과 같이 표시될 수 있다.

셀	값	수식
F6	712,000	=D6*E6
G6	15%	직접 입력
H6	106,800	=F6*G6
I6	605,200	=F6-H6

---

 

4. 원본 데이터를 변경하여 값의 변화를 관찰하기

조사식 창을 열어 둔 상태에서 D6의 수량을 변경하여 보자. 예를 들어 수량을

8 → 10

으로 변경한다. 단가 E6이 89,000이라면 판매금액 F6은 다음과 같이 변경된다.

10 × 89,000
= 890,000

조사식 창의 F6 값도 즉시 변경된다.

712,000 → 890,000

그 결과 H6의 할인금액도 자동으로 변경된다. 

890,000 × 15%
= 133,500

I6의 최종금액도 변경된다. 

890,000 - 133,500
= 756,500

조사식 창을 이용하면 이러한 변화 과정을 한곳에서 계속 확인할 수 있다.

---

💻 실습하기. 잘못된 수식 찾기

조사식 창은 수식 오류를 확인할 때 특히 유용하다. 예를 들어 H6에 다음과 같은 잘못된 수식이 입력되어 있다고 하자.

=F6*G5

정상 수식은 다음과 같아야 한다.

=F6*G6

조사식 창에는 수식 자체가 표시되므로 다음과 같이 오류를 발견할 수 있다.

셀	값	수식
F6	712,000	=D6*E6
G6	15%	직접 입력
H6	71,200	=F6*G5
I6	640,800	=F6-H6

H6의 수식을 보면 현재 행의 G6이 아니라 이전 행의 G5를 잘못 참조하고 있음을 확인할 수 있다.

5. 수식을 수정하고 변화 확인하기

H6 수식을 다음과 같이 수정한다.

=F6*G6

수식이 수정되면 조사식 창의 값도 즉시 변경된다.

수정 전
H6 = 71,200
수식 = F6*G5

수정 후
H6 = 106,800
수식 = F6*G6

이와 같이 조사식 창은 오류를 직접 찾아주는 기능이라기보다, 수정 전과 수정 후의 결과를 지속적으로 비교하고 검증하는 기능으로 활용할 수 있다.

6. 다른 시트에서도 계속 확인할 수 있다

조사식 창의 가장 큰 장점은 현재 작업 중인 시트와 관계없이 등록된 셀을 계속 확인할 수 있다는 점이다. 예를 들어 다음과 같이 여러 시트가 연결되어 있다고 하자.

[원본데이터]
      ↓
[판매계산]
      ↓
[월별요약]
      ↓
[대시보드]

• 대시보드 시트의 B5 셀에 총매출이 계산되어 있고, 원본데이터 시트에서 데이터를 수정한다고 가정한다.
• 대시보드의 B5를 조사식에 등록하여 두면 원본데이터 시트에서 작업하면서도 총매출 결과의 변화를 계속 확인할 수 있다.

즉,

작업은 다른 시트에서 하고, 중요한 결과는 조사식 창에서 확인한다.

이것이 조사식 창의 대표적인 활용 방법이다.

7. 조사식 목록에서 셀로 이동하기

조사식 창에서 등록된 셀을 더블 클릭하면 해당 셀이 있는 위치로 바로 이동할 수 있다. 예를 들어 조사식 목록에 다음 항목이 있다고 하자.

03_오류찾기   H6

• 이 항목을 더블 클릭하면 03_오류찾기 시트의 H6 셀로 바로 이동한다. 
• 여러 시트를 오가며 수식을 점검하는 경우 매우 편리하다.

8. 조사식 삭제하기

조사가 끝난 셀은 조사식 목록에서 제거할 수 있다. 제거할 항목을 선택한 후

[조사식 삭제]

를 클릭한다.

• 이때 해당 셀이나 수식이 삭제되는 것은 아니다. 
• 단지 조사식 창의 목록에서만 제거된다.

---