Ch11_데이터 분석 수행

학습목표

Excel에서는 단순히 수식을 계산하는 것뿐만 아니라 서로 다른 데이터 원본을 가져와 정리하고, 여러 범위의 데이터를 하나의 결과로 통합하며, 조건이 달라졌을 때 결과가 어떻게 변하는지 분석할 수 있다.

이 장에서는 모의고사 성적 데이터를 이용하여 외부 CSV 데이터를 가져오고 변환한 다음, 3월과 6월의 데이터를 통합한다. 이어서 시나리오 관리자를 이용하여 여러 가지 학습 조건을 비교하고, 목표값 찾기를 이용하여 원하는 결과를 얻기 위한 입력값을 역으로 계산하여 본다.

• 텍스트/CSV 데이터 가져오기와 Power Query 변환
• 여러 범위의 데이터 통합
• 시나리오 관리자
• 목표값 찾기
• MOS 365 Expert형 종합실습

---

Section 01. 데이터를 가져오기 및 변환하기

1. Raw Data란?

Raw Data(원시 데이터)란 아직 분석에 적합하도록 정리되지 않은 최초의 데이터를 의미한다. 현업에서 데이터를 전달받으면 처음부터 깔끔한 Excel 표로 되어 있는 경우보다 다음과 같은 문제가 포함되어 있는 경우가 많다.

• 실제 데이터 위에 보고서 제목이 들어 있다.
• 첫 번째 행이 열 이름으로 설정되어 있지 않다.
• 숫자와 문자가 같은 열에 섞여 있다.
• 이름 앞뒤에 불필요한 공백이 있다.
• 날짜가 텍스트로 저장되어 있다.
• 동일한 데이터가 두 번 이상 존재한다.
• 결측값이 미응시, 없음, - 등 여러 표현으로 기록되어 있다.

이러한 데이터를 바로 분석하면 함수, 정렬, 피벗 테이블 등의 결과가 잘못될 수 있다. 따라서 일반적으로 다음 과정이 필요하다.

Raw Data → 가져오기 → 변환 → 정제 → Excel로 로드 → 분석

Excel 365에서는 이러한 과정을 Power Query로 수행할 수 있다. Microsoft는 Power Query를 여러 데이터 원본에 연결한 후 데이터를 변환하고 모양을 정리하는 기능으로 설명하고 있다.

---

2. 실습 데이터 살펴보기

이번 장에서 사용하는 데이터는 다음 두 파일이다.

• Ch11_3월_모의고사_raw.csv
• Ch11_6월_모의고사_raw.csv

각 파일에는 다음과 같은 열이 들어 있다.

Ch11_6월_모의고사_raw.csv

0.00MB

Ch11_3월_모의고사_raw.csv

0.00MB

필드	설명
학번	학생을 구분하는 고유 번호
이름	학생 이름
학과	소속 학과
반	A/B반
국어	국어 점수
수학	수학 점수
영어	영어 점수
탐구1	탐구 과목 1
탐구2	탐구 과목 2
응시일	시험 응시 날짜

그러나 실습을 위하여 일부 데이터에는 의도적으로 문제가 들어 있다. 예를 들어 이름과 학과에 불필요한 공백이 있으며, 일부 학생은 특정 과목에 미응시 값이 들어 있다. 동일한 학번의 데이터도 한 행씩 중복되어 있다.

🧪 실습 01. CSV 파일 가져오기

문제

Ch11_3월_모의고사_raw.csv 파일을 Power Query로 가져와 보자.

작업 순서

1. [데이터] 탭을 선택한다.
2. [데이터 가져오기] → [파일에서] → [텍스트/CSV에서]를 선택한다.
3. Ch11_3월_모의고사_raw.csv를 선택한다.
   CSV 파일을 가져올 때 Excel은 구분 기호, 머리글, 데이터 형식 등을 자동으로 탐지할 수 있다.
4. 미리 보기 화면에서 [데이터 변환]을 선택한다.

바로 [로드]하지 않는 것이 중요하다. 현재 데이터에는 정리하여야 할 항목이 있기 때문이다.

 

 

---

3. 불필요한 첫 번째 행 제거하기

원시 파일의 가장 위에는 다음과 같은 제목이 들어 있다.

2026학년도 3월 모의고사 원시데이터

그러나 이 행은 실제 데이터의 열 이름이 아니다. 따라서 제거하여야 한다.

🧪 실습 02. 상위 행 제거

Power Query 편집기에서

[홈] → [행 제거] → [상위 행 제거] 를 선택한다.

제거할 행 수에 1을 입력한다. 이제 실제 필드 이름이 첫 번째 행으로 올라온다.

---

4. 첫 행을 머리글로 사용하기

현재 첫 번째 데이터 행에는 학번 / 이름 / 학과 / 반 / 국어 / 수학 … 이 들어 있다. 이 행을 데이터가 아니라 열 머리글로 변경하여야 한다.

🧪 실습 03. 머리글 승격

[홈] → [첫 행을 머리글로 사용] 

을 선택한다. Microsoft에서는 이 작업을 첫 번째 데이터 행을 열 이름으로 승격(Promote)하는 과정으로 설명한다.

---

5. 데이터 형식 확인하기

Power Query에서는 각 열에 데이터 형식이 존재한다. 예를 들어 다음과 같이 설정하는 것이 적절하다.

열	데이터 형식
학번	텍스트
이름	텍스트
학과	텍스트
반	텍스트
국어	정수
수학	정수
영어	정수
탐구1	정수
탐구2	정수
응시일	날짜

Power Query는 CSV나 텍스트 파일의 값을 검사하여 데이터 형식을 자동으로 판단할 수 있다. 그러나 자동 판단이 항상 원하는 결과와 일치하는 것은 아니므로 직접 확인하는 습관이 필요하다.

---

6. 미응시 데이터 처리하기

점수 열에는 일부 미응시 값이 존재한다. 이 상태에서 열 전체를 정수로 변경하면 오류가 발생할 수 있다. 따라서 먼저 미응시를 결측값으로 처리한다.

🧪 실습 04. 값 바꾸기

해당 점수 열을 선택한다.

[변환] → [값 바꾸기]

찾을 값=미응시, 바꿀 값은 비워 둔다. 그 후 해당 열의 데이터 형식을 정수로 변경한다.

---

7. 불필요한 공백 제거하기

이름이나 학과가 다음과 같이 입력되어 있다고 가정하여 보자.

" 김민준 "
"소프트웨어학과 "

화면에서는 큰 차이가 없어 보이지만 Excel은 서로 다른 문자열로 인식할 수 있다. 따라서 텍스트를 정리할 필요가 있다.

🧪 실습 05. 공백 제거

이름, 학과 열을 선택한다.

[변환] → [텍스트] → [형식] → [공백 제거] 를 실행한다. 또는 오른쪽 마우스 > 변환 > 공백제거

---

8. 중복 데이터 제거하기

동일한 학생이 두 번 입력되어 있으면 평균이나 인원수를 계산할 때 결과가 왜곡된다. 학번은 학생을 구분하는 고유 값이므로 학번을 이용하여 중복 데이터를 제거한다.

🧪 실습 06. 중복 제거

학번 열을 선택한다.

[홈] → [행 제거] → [중복 제거] 를 실행한다.

Power Query는 중복 제거, 오류 처리, 값 바꾸기 등 다양한 데이터 정제 기능을 제공한다.

---

9. 변환 결과 로드하기

모든 변환이 끝났으면 

[홈] → [닫기 및 로드] 를 선택한다.

Power Query의 중요한 특징은 원본 데이터를 직접 수정하는 것이 아니라 변환 과정을 단계로 기록한다는 점이다. 따라서 원본 CSV가 변경되더라도 쿼리를 새로 고치면 같은 변환 작업을 다시 수행할 수 있다.

---

Section 02. 데이터 통합하기

1. Consolidate란?

Excel의 통합(Consolidate) 기능은 여러 범위에 존재하는 데이터를 하나의 결과 영역에 집계하는 기능이다.

예를 들어

• 1월 매출
• 2월 매출
• 3월 매출

이 각각 다른 시트에 있을 때 이를 하나의 분기 보고서로 만들 수 있다. 통합에서 사용할 수 있는 대표적인 계산 방법은 다음과 같다.

• 합계
• 개수
• 평균
• 최대값
• 최소값

MO-211 공개 기술 개요에는 여러 범위의 데이터를 Consolidate 기능으로 요약하는 능력이 평가 항목으로 명시되어 있다.

---

2. 이번 실습의 구조

3월_모의고사와 6월_모의고사 시트에는 다음과 같은 학과별 평균표가 준비되어 있다. 3월과 6월의 구조가 동일하다. 이 두 범위를 통합하여 두 시험을 함께 반영한 학과별 평균을 계산한다.

🧪 실습 07. 3월과 6월 데이터 통합하기

통합_실습 시트의 A12 셀을 선택한다.

① 통합 실행

[데이터] → [데이터 도구] → [통합] 을 선택한다.

② 함수 설정

함수: 평균

③ 첫 번째 참조 영역 추가

3월_모의고사!$M$2:$S$5 [추가]를 클릭한다.

④ 두 번째 참조 영역 추가

6월_모의고사!$M$2:$S$5 [추가]를 클릭한다.

⑤ 레이블 사용

다음 두 항목을 선택한다.

☑ 첫 행
☑ 왼쪽 열

⑥ 확인

3월과 6월 데이터를 함께 반영한 결과표가 생성된다.

⚠️ 통합에서 중요한 두 가지 방식

위치 기준 통합

• 각 데이터의 셀 위치가 동일할 때 사용한다.
• 예를 들어 모든 지점의 매출표가 정확히 A1 구조라면 위치를 기준으로 통합할 수 있다.

범주 기준 통합

• 행이나 열의 이름을 기준으로 데이터를 연결한다.

이번 예제처럼

• 소프트웨어학과
• 경영학과
• 디자인학과

라는 레이블을 기준으로 데이터를 결합할 때 사용한다. 

---

Section 03. 시나리오 관리자

1. What-If Analysis란?

What-If Analysis는 우리말로 흔히 가상 분석이라고 표시된다. 현재 데이터에서 하나 이상의 입력값을 변경하였을 때 결과가 어떻게 달라지는지를 분석하는 방법이다.

대표적인 도구에는 다음이 있다.

• 시나리오 관리자
• 목표값 찾기
• 데이터 표

이 가운데 MO-211에서는 Goal Seek와 Scenario Manager를 이용한 What-If 분석이 명시적으로 포함되어 있다.

---

2. Scenario Manager란?

시나리오 관리자는 하나 이상의 입력 셀에 대하여 여러 값의 조합을 저장하고 비교하는 기능이다. 
예를 들어 다음 세 가지 학습 상황을 생각할 수 있다.

시나리오	추가학습	오답개선	컨디션
보수적	+2	+1	-2
기준	+5	+3	0
집중학습	+8	+5	+1

이번 예제에서는 다음 계산식을 사용한다.

예상 점수 = 6월 평균 + 추가 학습 효과 + 오답 개선 효과 + 컨디션 보정
이 계산식은 Excel 기능을 학습하기 위한 교육용 가정식이다. 실제 학생의 향후 성적을 예측하는 검증된 통계 모델이 아니다.

---

🧪 실습 08. 시나리오 추가하기

시나리오_관리자 시트를 선택한다. 현재 주요 셀은 다음과 같다. 

B4	6월 평균
B5	추가 학습 효과
B6	오답 개선 효과
B7	컨디션 보정
B8	예상 점수

B8에는 다음 수식이 들어 있다. 

=B4+B5+B6+B7

① 시나리오 관리자 실행

[데이터] → [가상 분석] → [시나리오 관리자] 를 선택한다.

② [추가] 선택 

• 시나리오 이름: 보수적
• 변경 셀: B5:B7

③ 값 입력

순서대로 2, 1, -2 를 입력한다.

---

3. 기준 시나리오 추가

1. 다시 [추가]를 선택한다.
2. 시나리오 이름: 기준
3. 변경 셀:B5:B7
4. 값:5 , 3, 0

 

4. 집중학습 시나리오 추가

• 시나리오 이름:집중학습
• 변경 셀:B5:B7
• 값:8,5,1

5. 시나리오 표시하기

• 시나리오 관리자에서 보수적을 선택하고, [표시] 를 클릭한다.
• B5의 값이 해당 시나리오 값으로 변경되고 B8의 결과도 자동으로 변경된다.
• 같은 방법으로 기준, 집중학습, 시나리오를 표시하여 결과를 비교한다.

시나리오 관리자에서 표시(Show)는 저장된 시나리오 값을 실제 변경 셀에 적용하는 기능이다.

---

6. 시나리오 요약 보고서 만들기

시나리오가 여러 개라면 하나씩 표시하는 것보다 보고서를 만드는 것이 효율적이다.

🧪 실습 09. 요약 보고서

시나리오 관리자에서 [요약] 을 선택한다.

• 보고서 유형: 시나리오 요약
• 결과 셀: B8 을 지정한다.
• [확인]을 클릭한다.

Excel은 새로운 워크시트를 생성하여 다음 정보를 보여 준다.

• 현재 값
• 보수적 시나리오
• 기준 시나리오
• 집중학습 시나리오
• 각 입력값
• 예상 점수 결과

---

Scenario Manager 핵심 정리

기능	역할
추가	새로운 시나리오 생성
편집	기존 시나리오 수정
삭제	시나리오 제거
표시	선택한 시나리오 값을 현재 워크시트에 적용
병합	다른 워크시트의 시나리오 가져오기
요약	시나리오 비교 보고서 작성

⚠️ MOS Expert 포인트

• 변경 셀과 결과 셀을 구분하여야 한다.
• 변경 셀은 사용자가 가정하는 입력값이며 결과 셀은 입력값에 따라 계산되는 수식 셀이다.

---

Section 04. 목표값 찾기

1. Goal Seek란?

시나리오 관리자가 입력값을 바꾸었을 때 결과가 어떻게 되는지 확인하는 기능이라면, 목표값 찾기는 반대로 원하는 결과가 나오려면 입력값이 얼마가 되어야 하는가? 를 계산하는 기능이다.

예. 현재 예상 점수가 79점이라고 가정한다.

학생은

“예상 점수를 90점으로 만들려면 추가 학습 효과가 몇 점이어야 하는가?”

를 알고 싶다. 이때 목표값 찾기를 사용할 수 있다.

---

🧪 실습 10. 목표점수 90점 만들기

목표값_찾기 시트를 선택한다. B8에는 =B4+B5+B6+B7 수식이 입력되어 있다.

 

① 목표값 찾기 실행

[데이터] → [가상 분석] → [목표값 찾기]

 

② 다음과 같이 입력한다.

• 수식 셀: B8
• 설정 값: 90
• 값을 바꿀 셀: B5

③ [확인]

Excel은 B5의 값을 반복적으로 변경하면서 B8이 90이 되는 값을 찾는다.

---

목표값 찾기의 구조

목표값 찾기는 다음 세 가지 요소로 구성된다.

수식 셀	원하는 결과가 계산되는 셀
설정 값	만들고 싶은 목표 결과
값을 바꿀 셀	Excel이 변경할 입력 셀

중요한 조건

• 수식 셀에는 반드시 수식이 있어야 한다. 
  예를 들어 B8에 단순히 80이라는 숫자를 입력하여 두었다면 목표값 찾기를 제대로 사용할 수 없다.
• 또한 목표값 찾기는 한 번에 하나의 입력 셀을 변경하여 결과를 찾는 기능이다. 
  여러 입력값의 조합을 비교하려면 시나리오 관리자가 더 적절하다.

---

Scenario Manager와 Goal Seek 비교

구분	시나리오 관리자	목표값 찾기
목적	여러 가정 비교	목표 결과를 만드는 입력값 계산
변경 셀	여러 개 가능	한 개
입력값	사용자가 지정	Excel이 계산
결과 비교	여러 시나리오	하나의 목표값
보고서	시나리오 요약 가능	별도 보고서 없음
대표 질문	조건이 달라지면 결과가 어떻게 되는가?	결과를 90으로 만들려면 무엇을 얼마로 변경하여야 하는가?

---

MOS 365 Expert 출제 유형 분석

현재 공개된 MO-211 기술 개요에서 고급 수식 및 매크로 영역은 전체 시험의 약 25~30%이며, 이 영역 안에 데이터 분석 기능이 포함된다. 공개 기술 목록에는 다음 작업이 제시되어 있다.

• 여러 범위를 Consolidate 기능으로 요약
• Goal Seek를 이용한 가상 분석
• Scenario Manager를 이용한 가상 분석
• FILTER를 이용한 데이터 필터링
• SORTBY를 이용한 데이터 정렬
• PMT를 이용한 재무 계산
• NPER 등을 이용한 예측 계산

따라서 시험 대비에서는 기능의 정의를 외우는 것보다 주어진 워크북에서 요구하는 결과를 실제로 만들어 내는 연습이 중요하다.

---

🧩 종합실습. 모의고사 데이터 분석 프로젝트

문제 상황

교수자는 3월과 6월 모의고사 결과를 비교하여 학생의 성적 변화를 분석하려고 한다. 3월과 6월 데이터는 CSV 파일로 전달받았다. 원본 데이터에는 불필요한 제목 행, 공백, 미응시 학생, 중복 데이터가 포함되어 있다. 원본 데이터를 정리한 다음 두 시험의 결과를 통합하고, 향후 학습 조건에 따른 예상 결과를 시나리오로 비교하여 보자.

---

문제 1. 외부 데이터 가져오기

Ch11_3월_모의고사_raw.csv를 Power Query로 가져온다. 다음 조건에 맞게 데이터를 변환하라.

1. 보고서 제목이 포함된 첫 번째 행을 제거한다.
2. 실제 필드명이 들어 있는 행을 머리글로 사용한다.
3. 학번은 텍스트 형식으로 설정한다.
4. 국어·수학·영어·탐구1·탐구2는 정수 형식으로 설정한다.
5. 응시일은 날짜 형식으로 설정한다.

---

문제 2. 데이터 정제

다음 조건으로 데이터를 정리하라.

1. 이름 앞뒤의 불필요한 공백을 제거한다.
2. 학과 앞뒤의 불필요한 공백을 제거한다.
3. 미응시를 null 값으로 변경한다.
4. 학번을 기준으로 중복 데이터를 제거한다.
5. 변환된 데이터를 새 워크시트로 로드한다.

---

 

문제 3. 6월 데이터 변환

Ch11_6월_모의고사_raw.csv에도 문제 1과 문제 2에서 수행한 것과 동일한 데이터 변환 작업을 수행하라.

---

문제 4. 데이터 통합

3월과 6월의 학과별 성적 요약표를 하나의 결과표로 통합하라.

조건

• 함수: 평균
• 3월 참조 범위: M2:S5
• 6월 참조 범위: M2:S5
• 첫 행 레이블 사용
• 왼쪽 열 레이블 사용

결과는 통합_실습 시트의 A12 셀부터 표시하라.

---

문제 5. 시나리오 관리자

시나리오_관리자 시트에서 다음 셀을 변경 셀로 설정한다.

• B5:B7

다음 시나리오를 작성하라.

보수적	2	1	-2
기준	5	3	0
집중학습	8	5	1

각 시나리오를 표시하여 B8 결과의 변화를 확인하라.

---

문제 6. 시나리오 요약 보고서

• 시나리오 관리자를 이용하여 시나리오 요약 보고서를 작성하라. 
• 결과 셀은 B8 로 지정한다.

---

문제 7. 목표값 찾기

목표값_찾기 시트에서 예상 점수를 90점으로 만들고자 한다.

다음 조건으로 목표값 찾기를 수행하라.

• 수식 셀: B8
• 설정 값: 90
• 값을 바꿀 셀: B5

Excel이 계산한 B5의 결과를 확인한다.

---

🚩 Expert Challenge

이번에는 다음 문제를 설명 없이 스스로 수행하여 본다.

문제: 학생의 예상 점수를 95점으로 만들고자 한다.

현재의

• 6월 평균
• 오답 개선 효과
• 컨디션 보정

은 그대로 유지한다. 추가 학습 효과만 변경할 수 있다. 어떤 Excel 기능을 사용하여야 하는가?

정답

• 목표값 찾기
• 수식 셀:B8
• 설정 값: 95
• 변경 셀: B5

 

🚩 Expert Challenge 2

교수자는 다음 세 가지 학습 계획을 한 번에 비교하려고 한다.

• 최소학습
• 정상학습
• 집중학습

각 계획마다

• 학습 효과
• 오답 개선 효과
• 컨디션

세 가지 값이 모두 달라진다. 목표값 찾기는 하나의 변경 셀을 역산하는 기능이므로 이 문제에는 적절하지 않다.

어떤 Excel 기능을 사용하여야 하는가?

정답

시나리오 관리자