파워쿼리 기초 완성, 2주 특별 챌린지 - 1주차

오늘의 강의: 파워쿼리 초보자도 실무에 바로 쓰는 핵심 기능 배우기!

 

 

 

🔥 미션 목표 :

• 파워쿼리를 사용하기 전에 꼭 알아야 할 올바른 데이터 구조를 이해합니다.

• 파워쿼리 편집기의 화면구성을 이해하고, 다양한 기본 기능을 활용해 데이터를 가공하는 방법을 알아봅니다.

• 열 피벗해제 기능으로 가로 방향의 데이터를 '세로방향 블록쌓기' 규칙이 지켜진 데이터로 변환하는 방법을 알아봅니다.

• 파워쿼리의 '파일 병합' 기능으로 특정 폴더 안의 엑셀 파일을 상황별로 편리하게 취합하는 방법을 알아봅니다.

엑셀 공부는 함수가 아니라 '데이터'를 먼저 알아야 한다.

엑셀: 데이터를 관리하면서 동시에 보고서까지 작성해야 해서 어렵다

보기 쉬운 표(=서식)와 관리하기 쉬운 표(=데이터)는 다르다!
데이터 구조만 변경해도 작업 속도와 난이도를 90% 이상 줄일 수 있다.
매크로가 있어서 한 번만 만들면 알아서 해 준다
기본 기능만 사용해도 충분하다.
파워쿼리는 '임시로 만든 데이터'에서 작업하므로, 원본에는 영향을 주지 않아 마음껏 수정/변경해도 괜찮다!

엑셀 데이터 관리 제1규칙: 세로방향 블록쌓기

Before: 사원 필드가 가로로 누적되어 있다. 잘못된 데이터!

 

After: 잘못 정리되어 있던 데이터를 데이터 원본으로 돌렸다. (피벗 해제, 언피벗!)

 

 

* 올바른 데이터 관리 규칙 7가지 *

1. 데이터는 표준화해서 일관성 있게 관리하기 -> 데이터 유효성 검사

2. 원본 데이터에는 '셀 병합' 절대 사용 금지

3. 하나의 셀에는 '한 개' 값만 작성하기 -> 쉼표, 줄바꿈 절대 사용 금지!

4. 머리글은 반드시 '한 줄'로 관리하기 -> 여러 줄 머리글은 100% 잘못된 데이터!

5. 각 행을 대표하는 '대표값(고유값)'을 꼭 함께 관리하기! -> 생활에서 학번, 사번을 쓰는 이유

6. 원본 데이터에는 '집계' 데이터를 포함하지 않기 -> 피벗테이블 결과가 2배로 뻥튀기 되는 이유!

7. 데이터는 반드시 '세로방향 블록쌓기' 규칙을 지켜서 관리하기 -> 엑셀 데이터 관리 핵심 규칙!

 

 

파워쿼리: 잘못된 데이터 구조를 올바른 데이터 구조로 바꾸는 법!

→ 파워 쿼리로 데이터를 가공하려면 원본 데이터는 반드시 '표' 형태여야 한다. (ctrl + t)

 

* 파워쿼리 편집기 *

1. 쿼리 목록 (왼쪽) - 더블클릭 또는 F2 - 이름바꾸기 / 마우스 우클릭 - 쿼리 그룹

2. 쿼리 설정 -> 이게 바로 매크로다

3. 수식입력줄: 엑셀과 동일 (M함수) M = Mapping (800개) cf. 엑셀 함수는 580개

4. 탭

[홈] - 전반적인 쿼리 변경

[변환] - 선택된 필드를 변환

[열 추가] - 선택된 필드를 참고해서 열을 추가

[보기] - 설정 변경

5. 필드마다 데이터 형식 지정 (날짜, 숫자, 문자 등등) -> 세로방향 블록쌓기

6. 머리글 밑에 막대 -> 데이터 미리보기 '열 품질'

7. 필터 (상위 1,000개 행을 기준으로 한 열 프로파일링 -> 확대 가능)

8. 파워쿼리 사용시 주의사항

 1) 파워쿼리 편집기를 실행하는 동안에는 시트 편집 불가

 2) '적용된 단계'를 삭제할 경우 기록 복구는 안됨. 원본에는 영향 x

 

✍ 스스로 점검하기 :

• 아래 그림과 같이 데이터를 관리할 시 발생가능 한 문제점을 '①, ②, ③' 각 항목별로 자유롭게 정리합니다.

문제점 ① 머리글이 두 줄로 되어 있다.

문제점 ② 집계데이터가 원본에 포합되어 있다. 데이터 쓰면 더블됨

문제점 ③ 셀 병합이 되어 있다.

 

• 금일 실습 파일의 [입고내역 숙제] 시트의 데이터를 올바른 구조로 피벗해제 한 결과물의 스크린샷을 첨부합니다.

 

[숙제] 입고내역

Before: 데이터가 되어야 할 날짜가 가로로 누적되어 관리되고 있다

머리글이 여러 행인 데이터는 100% 잘못된 데이터: ①열 합치기 → ②행/열 전환 → ③피벗해제 → ④ 열 분할

After:

• 오늘 수업에서 배운 내용 중, 가장 기억에 남는 내용 2가지를 간략히 정리합니다.

① 원본 데이터를 가지고 필터를 걸거나 피벗 테이블을 여러 개 만들어 데이터를 분석해왔는데, 모든 데이터를 한 데 모아 파워쿼리로 손쉽게 데이터 분석을 할 수 있다는 점이 놀라웠다. 왜 이 강의를 이제서야 접하게 되었을까...

② 행/열 전환과 피벗 해제의 차이점이 생소하면서도 신박했다.