📊 엑셀 파워쿼리 고급과정

실습 중심의 50분 집중 강의

🎯 실습 파일 다운로드

강의에 필요한 모든 실습 파일을 다운로드하세요!

📥 실습파일 다운로드 (.zip)

파일을 생성하고 있습니다...

ZIP 파일이 성공적으로 다운로드되었습니다!

🎓 강의 목차

🎯 강의 소개

소요시간: 5분

📚 학습 목표

💡 수강 전 준비사항

• Microsoft Excel 2016 이상 버전 (Office 365 권장)

• 기본적인 엑셀 사용 경험

• 실습 파일 다운로드 완료

🔥 파워쿼리란?

파워쿼리는 Excel의 강력한 데이터 연결 및 변환 도구입니다. 다양한 소스에서 데이터를 가져와서 정제, 변환, 결합할 수 있으며, 이 모든 과정을 자동화할 수 있습니다.

✨파워쿼리의 주요 장점

자동화: 한 번 설정하면 반복 작업 불필요

다양한 연결: Excel, CSV, 웹, 데이터베이스 등 연결

직관적 인터페이스: 코딩 없이 GUI로 작업

실시간 새로고침: 데이터 소스 변경 시 자동 업데이트

🚀 1강: 파워쿼리 시작하기

소요시간: 10분

1파워쿼리 실행 방법

데이터 탭 → 데이터 가져오기 클릭

또는 Alt + A + Q + Q 단축키 사용

2파워쿼리 편집기 화면 구성

🎛️ 리본 메뉴:

  • 홈: 기본 변환 기능
  • 변환: 고급 데이터 변환
  • 열 추가: 새 열 생성 기능
  • 보기: 화면 설정 및 표시 옵션

📊 주요 패널:

  • 쿼리 창: 왼쪽, 생성된 쿼리 목록
  • 데이터 미리보기: 중앙, 데이터 확인
  • 쿼리 설정: 오른쪽, 적용된 단계 확인

3첫 번째 쿼리 만들기 실습

1. 데이터 → 테이블/범위에서 클릭

2. 실습 파일의 "매출데이터" 시트 범위 선택

3. 확인 클릭하여 파워쿼리 편집기 진입

4. 데이터 미리보기에서 열과 행 확인

💡 실습 팁

파워쿼리 편집기에서는 원본 데이터가 변경되지 않습니다. 모든 변환은 임시로 적용되며, "닫기 및 로드" 버튼을 눌러야 실제 워크시트에 적용됩니다.

📥 2강: 다양한 소스에서 데이터 가져오기

소요시간: 10분

1파일에서 데이터 가져오기

🔹 Excel 파일 연결

1. 데이터 → 데이터 가져오기 → 파일에서 → Excel 통합 문서에서

2. 파일 선택 후 가져오기 클릭

3. 탐색기에서 원하는 시트 선택

4. 데이터 변환 클릭

🔹 CSV/텍스트 파일 연결

1. 텍스트/CSV에서 선택

2. 구분 기호 자동 감지 확인

3. 인코딩 설정 (한글: UTF-8 또는 EUC-KR)

2웹에서 데이터 가져오기

1. 데이터 가져오기 → 기타 원본에서 → 웹에서

2. URL 입력: 예) https://example.com/data.html

3. 확인 클릭 후 테이블 선택

4. 데이터 변환으로 편집기 진입

3폴더에서 여러 파일 결합

1. 폴더에서 선택

2. 폴더 경로 지정

3. 결합 → 데이터 변환

4. 파일별로 분리된 데이터를 하나로 통합

📂

실습 파일 구성

• 주문데이터.xlsx(주문내역.csv,반품내역.csv) - 주문 정보

• 고객정보.csv - 고객 마스터

• 제품목록.txt - 제품 정보

• monthly_sales/ - 월별 매출 폴더

⚠️ 주의사항

웹 데이터 연결 시 사이트의 로봇 배제 표준(robots.txt)을 확인하고, 과도한 요청으로 서버에 부하를 주지 않도록 주의하세요.

🔄 3강: 기본 데이터 변환 기법

소요시간: 10분

1열 관리 기본 기능

🔹 열 선택 및 제거

• 열 제거: 불필요한 열 삭제

• 다른 열 제거: 선택한 열만 유지

• Ctrl + 클릭: 여러 열 선택

🔹 열 이름 변경

1. 열 헤더 더블클릭 또는

2. 우클릭 → 이름 바꾸기

3. 변환 탭 → 이름 바꾸기

2데이터 정렬 및 필터링

🔹 정렬 기능

• 열 헤더 드롭다운 → 오름차순 정렬/내림차순 정렬

• 홈 탭 → 정렬 그룹

🔹 필터 적용

1. 열 헤더 드롭다운 클릭

2. 체크박스로 값 선택/해제

3. 텍스트 필터로 고급 조건 설정

4. 확인 클릭

3데이터 형식 변경

🔹 데이터 형식 감지 및 변경

• 변환 탭 → 데이터 형식

• 텍스트, 정수, 소수, 날짜, 날짜/시간 선택

• 로캘 사용: 지역별 형식 적용

📋 주요 데이터 형식 • ABC123 (텍스트) • 123 (정수) • 123.45 (10진수) • 2024-01-15 (날짜) • True/False (참/거짓)

4행 관리 기능

🔹 상위/하위 행 제거

• 홈 탭 → 행 감소

• 상위 행 제거: 헤더가 아닌 불필요한 행

• 하위 행 제거: 합계나 주석 행

🔹 중복 행 처리

• 중복 제거: 완전 중복 행 삭제

• 중복 유지: 중복된 행만 보기

💡 변환 단계 관리

오른쪽 쿼리 설정 패널에서 "적용된 단계"를 확인할 수 있습니다. 각 단계를 클릭하여 중간 결과를 확인하거나, X 버튼으로 단계를 삭제할 수 있습니다.

⚡ 4강: 고급 데이터 변환 및 결합

소요시간: 10분

1피벗 및 언피벗 변환

🔹 언피벗 (세로로 변환)

1. 변환할 열들 선택 (Ctrl + 클릭)

2. 변환 탭 → 언피벗 열

3. 선택한 열만 언피벗 또는 다른 열 언피벗

4. 결과: "특성" 열과 "값" 열로 분리

🔹 피벗 (가로로 변환)

1. 피벗 기준 열 선택

2. 변환 탭 → 피벗 열

3. 값 집계 방법 선택 (합계, 개수, 평균 등)

4. 고급 옵션에서 세부 설정

2테이블 결합 (조인)

🔹 쿼리 병합

1. 홈 탭 → 쿼리 병합

2. 병합할 테이블 선택

3. 조인 키 열 선택 (양쪽 테이블에서)

4. 조인 종류 선택:

  • 내부 조인: 양쪽에 모두 있는 데이터
  • 왼쪽 외부: 왼쪽 테이블 전체 + 매칭되는 오른쪽
  • 오른쪽 외부: 오른쪽 테이블 전체 + 매칭되는 왼쪽
  • 완전 외부: 양쪽 테이블 전체

5. 확인 → 새 열의 확장 버튼 클릭

🔹 쿼리 추가 (세로 결합)

1. 홈 탭 → 쿼리 추가

2. 추가할 테이블 선택

3. 열 이름이 같으면 자동으로 매칭

4. 다른 열 이름은 수동 매핑 필요

3사용자 지정 열 및 조건부 열

🔹 사용자 지정 열 만들기

1. 열 추가 탭 → 사용자 지정 열

2. 열 이름 입력

3. 수식 작성 (M 언어 사용):

// 예시: 매출액 계산 [수량] * [단가] // 텍스트 결합 [성] & " " & [이름] // 날짜 계산 Date.AddDays([주문일], 30)

🔹 조건부 열

1. 열 추가 탭 → 조건부 열

2. 조건 설정:

  • 열 선택 → 연산자 → 값
  • 출력 값 설정
  • 절 추가로 여러 조건

4그룹화 및 집계

1. 그룹화 기준 열 선택

2. 변환 탭 → 그룹화 기준

3. 집계 함수 선택:

  • 합계, 평균, 최댓값, 최솟값
  • 개수, 고유 개수
  • 표준 편차, 중앙값

4. 집계 추가로 여러 계산 가능

⚠️ M 언어 주의사항

사용자 지정 열에서 M 언어를 사용할 때는 대소문자를 구분합니다. 열 이름은 대괄호 []로 감싸고, 문자열은 따옴표 ""로 감싸세요.

🎯 5강: 종합 실습 프로젝트

소요시간: 12분
🚀

실습 시나리오

온라인 쇼핑몰 매출 분석 대시보드 만들기

여러 파일에 흩어진 데이터를 통합하여 종합 분석 리포트 생성

1데이터 소스 연결 (3분)

📊 연결할 파일들:

  • 주문데이터.xlsx(주문내역.csv,반품내역.csv) - 주문 정보
  • 고객정보.csv - 고객 마스터
  • 제품목록.txt - 제품 정보
  • monthly_sales/ - 월별 매출 폴더

실습 순서:

1. 각 데이터 소스별로 별도 쿼리 생성

2. 데이터 형식 및 인코딩 확인

3. 헤더 및 구조 검증

2데이터 정제 및 변환 (4분)

🧹 주문데이터 정제:

• 불필요한 열 제거: "메모", "임시컬럼"

• 날짜 형식 통일: "주문일자" → Date 형식

• 누락 데이터 처리: 빈 셀 → 0 또는 "미분류"

🧹 고객정보 정제:

• 중복 고객 제거

• 전화번호 형식 통일

• 지역코드 → 지역명 변환

🧹 제품목록 정제:

• 카테고리별 그룹화

• 가격 데이터 형식 변환

• 재고 상태 조건부 열 추가

3테이블 결합 및 관계 설정 (3분)

결합 순서:

1. 주문데이터 ←→ 고객정보 (고객ID 기준)

2. 결합결과 ←→ 제품목록 (제품코드 기준)

3. 월별매출 데이터 추가 결합

🔗 조인 설정 가이드 주문 ←[내부조인]→ 고객 - 키: 고객ID = 고객ID - 결과: 고객정보가 있는 주문만 결합테이블 ←[왼쪽외부]→ 제품 - 키: 제품코드 = 상품코드 - 결과: 모든 주문 + 매칭되는 제품정보

4고급 변환 및 계산 열 (2분)

📈 계산 열 생성:

매출액 계산:

[수량] * [단가] * (1 - [할인율])

고객 등급 분류:

if [총구매금액] >= 1000000 then "VIP" else if [총구매금액] >= 500000 then "Gold" else if [총구매금액] >= 100000 then "Silver" else "Bronze"

계절별 분류:

if Date.Month([주문일자]) in {12,1,2} then "겨울" else if Date.Month([주문일자]) in {3,4,5} then "봄" else if Date.Month([주문일자]) in {6,7,8} then "여름" else "가을"

💡 실무 팁

성능 최적화: 큰 데이터셋의 경우 필요한 열만 선택하고, 필터를 먼저 적용한 후 조인하세요.

오류 처리: try...otherwise 구문을 사용하여 계산 오류를 방지하세요.

📝 학습 확인 퀴즈

소요시간: 8분

❓ 퀴즈 1: 파워쿼리 기본 개념

파워쿼리에서 데이터 변환 단계를 확인할 수 있는 패널은?

❓ 퀴즈 2: 데이터 결합

두 테이블을 세로로 연결하는 기능은?

❓ 퀴즈 3: 고급 변환

가로 형태의 데이터를 세로로 변환하는 기능은?

📚학습 정리

오늘 학습한 주요 내용:

  • ✅ 파워쿼리 편집기의 모든 버튼과 패널 기능
  • ✅ 다양한 데이터 소스 연결 방법
  • ✅ 기본적인 데이터 정제 및 변환 기법
  • ✅ 테이블 조인 및 결합 실습
  • ✅ 고급 변환 및 계산 열 생성
  • ✅ 실무 프로젝트 완성

🎯 다음 학습 단계

파워피벗: 더 복잡한 데이터 모델링

파워BI: 고급 시각화 및 대시보드

M 언어: 사용자 지정 함수 작성

자동화: VBA와 파워쿼리 연동