<퍼온글> 파워쿼리

[푸드비즈]

엑셀 2016 버전부터 파워쿼리는 이미 설치되어 있음 (없는 경우 구글 검색 - ms에서 다운로드하면 자동설치됨)

[1장. 웹에서 데이터 가져와 엑셀에 붙이기]

1. 인터넷 사이트 url 복사

2. 엑셀 - 데이터 - 데이터가져오기

3. 기타 원본 - 웹 - url 붙이기

4. 로드(원본그대로) 또는 데이터 변환 > 파워쿼리 편집창에서 수정 가능

홈 - 첫행을 머리글로 사용

변환 > 열분할 > 사용자지정 > 특수문자를 사용하여 분할 > 줄바꿈

변환 > 날짜 > 년 > 년

5. 홈 > 닫기 및 로드 > 엑셀 시트에 반영

6. 데이터 > 모두 새로고침 : 웹에 반영되는 데이터가 자동 새로고침

활용) 나는 인터넷 사이트에서 데이터를 끌어오는 경우는 많이 없지만..

이건 정말 신세계였다.. ㅋㅋㅋㅋ 만약에 위키피디아에서 대한민국 아카데미 후보작 페이지를 경로 설정해놓으면,

수상작이 추가될때마다 해당 엑셀 리스트에도 영화가 추가됨. 매일 업데이트 되는 자료를 수집하는 경우 아주 유용할 듯!

[2장. 일정한 시간 간격으로 엑셀파일 자동 업데이트]

파일이 열려있는 동안 매 30초마다 자동 업데이트하기

Alt + f11 = vba 실행 단축키

통합문서 선택 > 오른쪽단추 > 삽입 > 모듈 > 코드 붙여넣기

Public Schedule As Date ​ Sub RefreshAllData() ThisWorkbook.RefreshAll Call AutoRefresh End Sub ​ Sub AutoRefresh() Schedule = Now + TimeSerial(0, 0, 30) Application.OnTime Schedule, "RefreshAllData" End Sub

2개 매크로가 들어가있다. ​ ​ ​ ​ 30초 마다 반복되도록 설정 ​

다른이름으로 저장 : 매크로를 포함한 파일

활용) 주식투자하시는 분들 , 네이버 금융에서 '외국인 순매도' 탭의 순위를 매번 새로고침해서 본다고 해보자..

해당 경로를 설정해두고 자동 새로고침을 걸어두면, 30초마다 변경된 순위가 엑셀에 자동 업데이트 된다... OMG....

[3장. 특정 폴더 내, 파일이름 모두 가져오기]

1. 데이터 > 데이터 가져오기 > 파일 > 폴더 > 가져올 폴더 지정

2. 해당 파일에 있는 모든 파일 그대로 가져올 거면 로드 , 아니면 데이터변환 선택

4. 폴더 안에 하위폴더가 있는 경우, folder path 에서 필터링

5. 확장자 별로 선택해야 하는 경우, Extension 에서 필터링(xlsx, ppt..)

6. Name 열 선택 (+추가할 열 ctrl 누른 채 선택) > 마우르 오른쪽 단추 > 다른 열 제거

7. 닫기 및 로드

8. 데이터 > 모두 새로고침

*만약 처음 폴더의 위치가 바꼈을 경우, 쿼리및 연결 더블클릭 > 홈 > 데이터 원본설정 > 원본변경 > 파일 경로 새로 지정

활용) 이 기능의 진짜 꿀팁은 경로 설정해 둔 폴더에 파일들이 추가 또는 삭제 되면,

엑셀파일에서도 자동으로 파일이름이 업데이트 된다는 것이다.. 엑셀 취합이 많은 나로서는 아주 유용하게 쓸 수 있을 듯..

진짜 알면 알 수록 신세계다.

[4장. 특정 폴더 내, 헤더가 같은 엑셀 파일 통합]

1. 데이터 가져오기 > 파일에서 > 폴더에서 > 폴더 선택 > 데이터변환 > folder path 하위폴더 포함여부 체크

2. content(내용) 열 선택 > 오른쪽 단추 > 다른 열 제거 > 파일병합 (화살표2개) 아이콘 클릭

3. Sheet1 선택 후 확인

4. 정수를 실수(소수점까지)로 바꾸고 싶은 경우 열 선택 > 오른쪽 단추 > 형식변경 > 10진수

5. 모든 파일의 content 가 헤더 아래로 이어 붙여짐

활용) 폴더 내 개별 파일에서 내용을 수정할 경우 통합 엑셀파일에서도 추가되거나 삭제됨(모두 새로고침하면)

[5장. 같은 엑셀파일에 나눠져 있는 여러 시트를 하나의 시트로 통합]

*헤더 양식(순서)은 모두 동일해야함

1. 위의 2번까지 동일

2. sheet 를 선택하지 않고, 매개변수1 폴더를 선택

3. data 선택 후 다른 열 제거

4. 확장 아이콘 클릭 > 모든열 선택(자동선택) > 원래 열 이름을 접두사로 사용 언체크 > 확인

5. 헤더 중복 삭제 필요 , 변환 > 첫행을 머리글로 사용

6. 1번 열 드롭다운 선택 > 열 이름/null 언체크

7. 마지막으로 데이터 형식 확인 (날짜, 텍스트, 정수/실수)

활용) 원본 엑셀파일에 시트 추가되거나, 내용 추가/수정되면 통합엑셀파일에서 새로고침하면 반영됨

[필터링 & 솔팅(우선순위) 정렬 한번에]

1. 가져올 엑셀 열기 > 삽입 > 표 로 지정 (표디자인 - 없음)

2. 데이터 > 테이블에서 > 파워쿼리 창으로 불러오기

3. 열 별 형식 변경(정수/날짜/텍스트 .. 에러뜰 경우 히스토리 삭제)

4. 필터 아이콘 > 텍스트필터 > 아무거나 선택 > 및(and) , 또는(or) 조건 설정 > 확인

5. 우선순위 sorting > 드롭다운 버튼 누른 후 차례대로 오름차순/내림차순 선택 > 헤더에 우선순위 1,2, 숫자가 생김

6. 닫기 및 로드 > 새로운 시트로 결과 가져옴 (원본은 건들지 않음)

활용) 원본 데이터가 바뀌거나 추가되면 쿼리 데이터도 자동으로 바뀐다.

[데이터 교차로 합치기]

첫번째 표(GOODS)와 두번째 표(BOX)가 합쳐짐

1. 로우 엑셀파일에서 데이터 표로 지정 > 표삽입 단축키 : ctrl + T , 머리글포함, 표이름 수정

2. 첫번째 표 선택 > 데이터 > 테이블범위에서 > 닫기및로드 드롭다운 > 2번째 닫기 및 다음으로 로드 > 연결만 만들기 *쿼리에 기억만 시켜놓기

3. 쿼리연결

- 복사 : 원본 > 쿼리편집창 > 편집(히스토리) > 변환 > 워크시트로 불러오기

- 복제 : 원본 > 쿼리편집창 > 편집(히스토리) > 변환

- 참조 : 원본 > 쿼리편집창 > 변환 *편집을 따로 안했을때는 복제=참조

4. 쿼리연결창 goods 오른쪽 단추 클릭 > 참조 > 메뉴 - 열추가 > 사용자 지정 열 > =box > 확인

5. box 표 > 확장아이콘 클릭 > 확인

6. 데이터 형식 (텍스트/숫자) 확인

7. 닫기 및 다음으로 로드

활용) 이건 재고관리나 상품 주문하는 쪽에서는 잘 쓸 수 있을 것 같은데.. 그 외에는 아직 잘 모르겠다.

[교대로 행 지우기 - 특정 행 반복해서 삭제]

1. 로우 엑셀파일 > 데이터 > 테이블범위에서

2. 홈 > 행제거 > 교대로 행 삭제

- 제거할 첫번째 행 : 시작행(3행부터 지움)

- 제거할 행수 : 시작행부터 몇개를 지울지 (3,4행 2개를 지움)

- 유지할 행수 : 5,6행 2개는 유지 (그 다음 7,8행부터 삭제 / 9,10행 유지 - 반복됨)

*히스토리 창 > 설정에서 수정 가능

활용) 지우려는 행이 규칙적으로 있는 경우는 매우 유용한데,

현실에서는 데이터가 항상 규칙적으로 들어가있는게 아니니 활용도는 조금 더 고민해봐야 할 듯..

그래도 규칙적으로 특정 행을 지워야 할 때는 노가다 하지 않고 한번에 삭제 가능하니 꿀팁은 꿀팁이다.

[텍스트 추출]

왼쪽 로우데이터에서 텍스트 추출

1. 로우 엑셀파일 > 데이터 > 테이블범위에서

2. 열 추가 탭 > 열복제 (같은 열 2개로 복사됨)

3. 변환 탭 > 추출 > 구분기호 사이 텍스트 > 기호 입력 > 고급

- 시작 구분 기호 스캔 : 입력 끝부터(뒤에서부터) *로우데이터에 괄호(가 2개 이기 때문에, 뒷 괄호(뒤에서부터)로 선택

- 건너뛸 시작 구분 기호의 수 : 없으면 0

- 종료 구분 기호 스캔 : 시작구분기호 ( 에서부터 입력의 끝 (뒷방향으로) 까지

[한 셀에 텍스트가 여러 줄 입력되어 있을 때]

1. 파워쿼리 편집기 > 변환 > 열 분할 > 구분 기호 기준

2. 사용자지정 > 고급옵션 > 행 > 특수문자 드롭다운 > 줄 바꿈 선택 > 확인

3. 로우데이터 줄바꿈 삭제하기 : 변환 > 값바꾸기 > 고급옵션 > 특수문자를 사용하여 바꾸기 > 캐리지리턴 > 확인

활용) 엑셀파일로 열면 텍스트 깨지는 파일들에 사용하면 좋을 듯.

[엉망진창 데이터 - 테이블화 시키기 1]

정리된듯 정리 안 된 반정형 데이터..

1. 수정할 값("이름") 맞추기 : 파워쿼리 편집창 > 열추가 > 조건열 > 열1 이 "이름" 이면 열2의 값("김기덕")을 출력하고,

그렇지 않으면 null(빈칸)을 출력한다.

2. 공란 채우기 : 열 선택 오른쪽 단추 > 채우기 > 아래로

3. 열1 필터링 드롭다운 : "이름" 언체크

4. 변환 탭 > 첫 행을 머리글로 사용

정형화 결과

활용) 로우데이터 양식이 거지 같아도 그대로 추가하면 쿼리 데이터가 자동으로 업데이트 되니

매번 번거로운 작업을 하지 않아도 됨

[행-열 바뀐 크로스 테이블을 정형화 하기2]

- 가로(열) > 세로(행)로 변경 : 열 피벗 해제

- 세로(행) > 가로(열)로 변경 : 피벗 열

1. 파워쿼리 편집기 > 가로 행을 세로 열로 바꿀 열 선택 > 변환 > 열피벗 해제 (*또는 1열에서 오른쪽 단추 > 다른열 피벗해제)

2. size / qty 분리하기 : 해당 열 오른쪽 단추 > 열분할 > 구분기호기준 > 공백 > 맨오른쪽 구분기호에서 분할 > 확인

3. size / qty 행을 열로 변경 : 피벗 열 > 값열 : 기준값이 되는 열 선택 > 고급옵션 : 집계 안함(가로 -세로 변경만 할 거기 때문)

(좌) 원본 -----------> (우) 완성

활용) 솔직히 저 정도로 원본데이터를 개뼉다구처럼 주는 경우는 없기 때문에..

만약 있다면.. 진짜 노가다로 한 땀 한 땀 하기엔 골때릴것 같은데 파워쿼리로 하면 1분이면 끝날 듯.

[피벗테이블 단점 보완]

"지역구" 총 합계 피벗테이블에서 하위 데이터(동 이름) 중복값은 제거 하고 하나의 셀에 불러오기

(좌) 파워쿼리 / (우) 엑셀 피벗

1. 데이터 > 파워쿼리 편집기 > 그룹화 > 고급

2. 순서대로 그룹화 추가, 집계추가 > 모든 행 (필수설정)

3. 열추가 > 사용자 지정 열 > 수식입력 > 파워쿼리 m함수 참조

https://docs.microsoft.com/ko-kr/powerquery-m/power-query-m-function-reference

파워 쿼리 M 함수 참조 - PowerQuery M

다음에 대해 자세히 알아보세요. 파워 쿼리 M 함수 참조

docs.microsoft.com

- List.Distinct : 중복된 항목이 제거된 목록을 가지고 옴.

- Text.Combine : 텍스트를 결합함

"발신지_동"의 데이터를 중복값 제거하고 쉼표(, )로 구분하여 가져옴

["중복된 항목 제거" 단점 보완]

파워쿼리로 해당 열에서 "중복되지 않은 값"만 추출하기 또는 "중복된 값"만 가져오기

1. 파워쿼리 편집창 > 그룹화 > 기본 > 행 카운트 > 1 필터링 (중복되지 않은 항목)

2. 해당 파워쿼리 편집창 복제 > 필터링 모두 선택> 열추가 > 조건열 > 1과 같지 않음 > True / False > 확인

3. True 만 필터링 > True /False 열 제거 > 닫기 및 로드 (중복된 항목만 가져옴)

[인덱스번호로 테이블 만들기]

똑같은 줄 간격으로 나열된 데이터를 정형화하여 테이블화 시키기

1. 파워쿼리 편집창 > 열추가 > 인덱스 열 > 0부터

2. 열추가 > 표준 > 모듈로 > 나눌 값(반복되는 행) 입력 > 확인

3. 모듈로 열 선택 > 변환 > 피벗 열 (세로방향 열 데이터가 가로방향 행 데이터로 전환)

*고급옵션 > 값집계함수 : 집계안함으로 선택

4. 1~3열 데이터 위로 채우기

5. 0열 null 값 필터링 제외 > 인덱스 열 제거

6. 닫기 및 로드

오늘의 깨달음 하나 .. 피벗(pivot)의 뜻이 단순히 합치다 라고 알고 있었는데 원래 뜻은 "축의 회전"이 맞다.

세로 열 -> 가로 행으로 바꾸는 것이 진짜 피벗이다. (그 반대 - 가로를 세로로 바꾸는 건 열피벗 해제)