<쿼리 생성하는 5가지 기초적인 방법>
1. 테이블/범위에서 가져오기
2. 엑셀 통합 문서(xlsx, xlsm, xlsb)에서 가져오기
3. 텍스트/CSV에서 가져오기
4. 폴더에서 가져오기
5. 빈쿼리
이번 글에서는 엑셀 파일에서 데이터를 가져와 쿼리를 생성하는 방법을 알아보겠습니다. 예시파일은 동일한 내용이지만 파일형식이 하나는 xlsx, 다른 하나는 xlsb입니다.
<예시파일>
엑셀 통합 문서에서 데이터를 가져와 쿼리를 생성하는 과정
1) 데이터 탭 > 데이터 가져오기 > 파일에서 > Excel 통합 문서에서
▲데이터 탭 > 데이터 가져오기 > 파일에서 > 'Excel 통합 문서에서'를 클릭합니다. 이후 '데이터 가져오기' 팝업창에서 데이터를 가져올 엑셀 파일을 선택합니다. 위의 사진처럼 우선 확장자 .xlsx 파일(일반 워크시트 파일)을 선택해 보겠습니다.
2) 탐색창에서 쿼리로 생성할 데이터 선택하기 (표 vs 범위)
▲파일을 선택하면 탐색창에서 쿼리를 생성할 수 있는 표나 워크시트들을 확인할 수 있습니다. 예시의 파일에는 4개의 표와 4개의 시트가 있는데, 모두 탐색창에서 확인하실 수 있습니다.
데이터가 표로 설정되어 있으면 그 표가 시트 내 어디에 위치해 있던 위의 왼쪽 사진처럼 데이터 범위가 정확하게 설정되어 있는 것을 볼 수 있습니다. 한편, 같은 데이터지만 시트에서 가져올 경우 탐색창에서는 A1셀부터 데이터로 인식하기 때문에, 위의 오른쪽 사진처럼 표 바로 위의 '고객세부정보'라는 셀이 데이터열의 이름으로 변환됩니다. 그래서 1행부터 데이터가 시작되지 않는다면, 시트(범위)에서 데이터를 가져올 경우 쿼리를 추가적으로 편집해야 하는 경우가 생길 수 있습니다.
시트에서 가져올 때 쿼리를 수정하는 경우는 추후에 살펴볼 예정이며, 우선은 표2를 선택해 보겠습니다.
3) 파워쿼리 편집기에서 생성된 쿼리 확인 및 편집하기
▲표2를 선택하면 위의 사진처럼 파워쿼리 편집기가 열리면서 쿼리가 생성됩니다.
이전 글에서도 말씀드렸듯이, '변경된 유형' 단계는 삭제하셔도 무방합니다. 오히려 '변경된 유형' 단계를 우클릭 후 삭제하시는 걸 추천드립니다.
▲화면 우측에 '적용된 단계' 중 '탐색' 단계의 톱니바퀴를 클릭하시면 앞서 보여드린 탐색창과 유사한 팝업창이 뜨면서 쿼리의 원본을 다른 것으로 변경할 수 있습니다. 이때 또 다른 '변경된 유형' 단계가 생기면서, 기존에 있던 '변경된 유형' 단계를 삭제하지 않으면 쿼리 출력값에 오류가 발생합니다.
한편, '원본' 단계의 톱니바퀴를 클릭하시면 원본 파일을 변경할 수 있습니다. 하지만 원본 파일을 변경할 경우 시트명이나 표 이름이 동일하지 않을 수 있기 때문에 '탐색' 단계부터 코딩 수정이 필요할 것입니다. 이는 추후에 엑셀 통합 문서에서 데이터를 가져올 때 발생하는 코딩을 해석하면서 설명드리겠습니다.
3) 닫기 및 로드 ▼ > 닫기 및 다음으로 로드
▲ 이전 글에서 말씀드렸듯이, 저는 '닫기 및 다음으로 로드'를 클릭하여 연결만 만들지 혹은 시트에 출력할지를 선택하는 것을 추천드립니다.
쿼리 생성 시 팁 #1. 연결시킬 파일의 데이터를 표(테이블)로 설정
예시 파일로 쿼리를 생성하는 과정에서 볼 수 있듯이, 데이터를 표로 설정하고 저장해야 정확한 범위의 데이터가 쿼리로 생성됩니다. 반면 시트(범위)에서 데이터를 가져올 경우에는 불필요한 행이나 열을 삭제해야 할 수도 있습니다.
쿼리 생성 시 팁 #2. xlsb 파일 경우 탐색창 목록에 표(테이블) 데이터가 없음
▲xlsb 파일 경우 확장자가 xlsb일 뿐 같은 파일인데도 위의 사진처럼 탐색창 목록에 시트만 있습니다. xlsb 파일이 용량을 덜 차지하고 속도도 빨라서 저도 많이 사용하는데, 파워쿼리의 데이터 원본으로는 부적격하다고 생각합니다. 그래서 저는 보통 xlsb 파일을 원본으로 쓰고자 할 때 매크로를 활용해서 xlsx 나 xlsm(매크로 사용 워크시트) 파일로 변환합니다. 이와 관련한 매크로는 추후에 소개해드리겠습니다.
예시 파일 xlsx와 xlsb 두 가지 버전 모두 다운로드하여서 쿼리를 직접 만들어보고 차이점을 느껴보시길 바랍니다.
관련 글
[파워쿼리] 한 개의 파일 내 여러 데이터 합치기 (VLOOKUP 상위호환)
'엑셀, VBA, 파워쿼리 > 파워쿼리' 카테고리의 다른 글
| [파워쿼리] 파워쿼리로 다중조건 VLOOKUP 손쉽게 하기 (2) | 2023.10.07 |
|---|---|
| [파워쿼리- 쿼리 생성하는 5가지 방법] #5 빈 쿼리로 쿼리 생성하기 (3) | 2023.10.07 |
| [파워쿼리- 쿼리 생성하는 5가지 방법] #4 폴더에서 가져오기 (여러 파일 취합하기) (1) | 2023.10.07 |
| [파워쿼리- 쿼리 생성하는 5가지 방법] #3 텍스트/CSV에서 가져오기 (1) | 2023.10.07 |
| [파워쿼리- 쿼리 생성하는 5가지 방법] #1 테이블/범위에서 가져오기 (0) | 2023.10.07 |
댓글