오빠두 엑셀 - 파워쿼리 기초 완성, 4주 특별 챌린지 1주차 스터디 노트

오빠두님의 엑셀 강의를 파워쿠리 기초 완성, 4주 특별 챌린지 1주차 스터디 노트를 작성 합니다. 

 

1주차: 파워쿼리 입문자를 위한, 기본기 다지기: 데이터 가공 & 정규화 실습 

 

강의 목표: 파워쿼리를 통해 우리가 만들어야 하는 문서가 무엇을 목표로 하는지 정하고 그에 맞는 데이터 가공 방법을 확인 함 

 

1주차 강의 중요 POINT

 - 데이터는 표준화해서 일관성 있게 관리하기

 -  데이터에는 ‘셀 병합’을 절대로 사용하지 않습니다.

 -  셀에는 한 개의 값만 작성합니다. / 값을 구분하는 “쉼표, 줄바꿈” 사용하지 않기!

 -  머리글은 반드시 ‘ 한 줄’로 관리해야 한다. / 동일한 값을 나타내는 ‘ 반복되는 그룹이 있으면 안됩니다.!

 -  각 행에는 항상 고유한 값이 있어야 합니다.

 -  원본 데이터에는 소계(중간 합산)등의 별도의 합산 값이 없어야 한다.

 -  데이터는 반드시 세로형식의 블록쌓기 규칙을 지켜서 관리 하여야 한다.

 

파워 쿼리란?

 - 불량을 찾기 위해 데이터의 구조를 자동으로 변경하는 것

 

1주차 강의 단계별 Process 노트

1. 파워쿼리의 수정

  1) 파워쿼리의 수정은 적용된 단계의 편집, 변경된 단계를 클릭하여 확인이 가능 하나 삭제 시 복구는 불가 함

2. NULL 값을 채우는 방법

  1) 변환 -> 채우기 -> 아래로 / 순서대로 하면 NULL 값이 자동으로 채워짐

그림 1. NULL 값 채우기

 

3. 머리글 만들기

  1) 홈 -> 행 제거 -> 상위 행 제거 -> 1 입력 후 ENTER -> 첫 행을 머리글로 사용 / 자동으로 머리글 완성

그림 2. 첫 행을 머리글로 사용

 

4. 값 변경하기

  1) 변환 -> 값 바꾸기 -> 찾을 값(변경을 원하는 단어) -> 바꿀 항목(변경 후 단어 입력) -> ENTER

그림 3. 값 바꾸기

 Tip. 데이터 형식이 정수와, 문자 2가지 복합형식일 경우, 값 바꾸기가 실행 안될 수 있음 이에 내가 원하는 데이터 형식이 정수일 경우(임의의 문자를 삭제 해야 될 경우) / 데이터 형식을 문자로 변경 후 1)항의 방식으로 값 변환 후 다시 정수로 변경 해야 함

 만약, 내가 원하는 데이터 형식이 문자일 경우(임의의 정수를 삭제해야 될 경우) / 데이터 형식을 정수로 변경 후 1)항의 방식으로 값 변환 후 다시 문자로 변경 해야 함

 

즉, 값 바꾸기를 희망 하는 데이터 형식으로 변경 해야 올바를 값 바꾸기 가능

 

5. 열 분할

  1) 분할을 원하는 열 클릭 -> 홈 -> 열분할 -> 구분 기호, 문자 수, 위치별 등(원하는 기준 선택) -> 확인(Power Query 편집기가 자동 선택 함) -> 정렬 확인 -> 추가된 열의 머리글 수정 -> 추가된 열의 값 정리(불필요한 문자 등 제거, 제거는 4항의 값 변경 하기를 따름)

그림 4. 열 분할 1

 

그림 5. 열 분할 2

6. 열 추가(단가를 추가 할 경우)

1)     열 추가 -> 사용자 지정 열 클릭 -> 새 열 이름에 원하는 열의 머리글 입력 -> 사용자 지정 열 수식에 원하는 수식 입력(본 항에서는 단가를 추가 함으로 ‘금액/수량’ 수식을 입력 함 -> ‘구문 오류가 검색되지 않았습니다.’ 확인

그림 6. 열 추가

 

7. 엑셀에서 호출 하기

  1) 파일 -> 닫기 및 다음으로 로드 -> 연결만 만들기 -> 확인 -> 쿼리 및 연결에서 원하는 쿼리를 우클릭 하여 다음으로 로드 -> 원하는 셀을 클릭 하고 확인

그림 7. 데이터 가져오기

Tip. 닫기 및 로드로 저장 할 경우 만든 쿼리를 시트가 추가 되며 새로운 표가 입력 됨 이는 나중에 해당 파일의 시트와 표가 너무 많아 지는 문제가 발생 함

 

8. 적용 해보기

  1) 원래 있던 표에서 새로운 값을 입력해보면 자동으로 데이터가 생성 됨을 확인 할 수 있음

  2) 원래 표에서 값 입력 후 쿼리의 새로고침 버튼 클릭 필요

  3) 데이터는 원래 입력 하던 기준으로 입력 필요(예: 제품에 품명과, 지역을 같이 입력 ‘즉, 사과(영천)’과 같이 값을 입력 했다면, 해당 표에도 동일 기준으로 입력이 필요 함

그림 8. 자동화 작동 확인 하는 방법

Tip. 엑셀에서 파워커리의 새로고침은 다음과 같이 구분된다.

  1) 모두 새로 고침: ctrl + alt + F5  - 모든 쿼리 값을 새로 고침 함

  2) 새로 고침: alt + F5 – 내가 선택한 쿼리만 새로 고침 함

  3) 빠른 실행 도구 모음에 등록하여 마우스로 사용 가능

 

9. 열 피벗 해제

  1) 설명: 데이터의 구조가 잘못된 경우 사용 한다.

  2) 정상적인 열을 선택 -> 변환의 열 피벗 해제(다른 열 피벗 해제) 선택

그림 9. 열 피벗 해제 1

그림 10. 열 피벗 해제 2