VLOOKUP 함수는 데이터를 합칠 때 활용되기도 합니다. VLOOKUP과 IF함수 및 MATCH 함수 조합 혹은 INDEX-MATCH 함수 조합을 활용하면 VLOOKUP만 썼을 때의 한계를 어느 정도 극복할 수 있습니다.
하지만, 여러 데이터를 하나로 합치거나 데이터 양이 많은 경우에는 파워쿼리의 '쿼리 병합'을 사용하는 것이 VLOOKUP 함수를 사용하는 것보다 더 효율적입니다. 참조해야 할 데이터가 많으면 VLOOKUP 함수를 여러 번 활용해야 하고, 데이터 양이 많으면 계산 시간이 늘어나기 때문입니다. 샘플 파일을 통해 파워쿼리로 한 파일 내 여러 데이터를 합치는 과정을 살펴보겠습니다.
(파워쿼리에 대한 간단한 소개글 참고)
샘플 파일 상황
▲샘플 파일에는 각 시트에 '주문 개요', '고객세부정보', '상품분류', '상품제조사' 표가 있습니다. 주문 개요만 봐서는 특정 상품의 주요 소비연령 및 성별이라던지, 제조사의 어떤 제품이 인기가 많은지 등의 정보를 알 수 없는 상황입니다. 4개의 데이터를 하나의 로데이터로 합친다면 더 다양하게 분석을 할 수 있을 텐데요.
만약에 주문 개요를 기준으로 두고 엑셀 함수로 데이터들을 합치게 된다면 VLOOKUP-MATCH 함수 한 번, INDEX-MATCH 함수 조합을 세 번 사용해야 합니다. 그리고 행과 열이 많아지면 많아질수록 계산속도는 느려져 데이터를 합칠 때 심할 때는 '응답 없음'이 뜰 수도 있습니다. 하지만 파워쿼리를 사용하면 더 직관적이고, 빠르게 합칠 수 있습니다.
각 표마다 쿼리 생성하기
▲현재 모든 데이터는 '표'로 설정되어 있습니다(단축키 Ctrl + T). ①데이터 표 안에 아무 셀이나 클릭 후, ② 데이터 탭 → '테이블/범위에서' 버튼을 클릭하면 파워쿼리 편집기가 실행됩니다.
▲테이블/범위에서 가져올 경우 파워쿼리 편집기가 실행됨과 동시에 ③ 테이블 형태의 쿼리가 자동으로 생성됩니다. 쿼리가 생성이 된 상태이니, ④ 좌상단의 닫기 및 로드 → '닫기 밑 다음으로 로드' 버튼을 클릭하신 후, ⑤ '연결만 만들기'를 선택하시면 '출력용'이 아닌 '연결 편집용' 쿼리가 생성됩니다.
▲동일하게 나머지 표에서도 쿼리를 각각 만들어주면 위의 사진처럼 4개의 쿼리가 생성이 됩니다.
'쿼리 병합하기'로 데이터 합친 후 출력하기
1) 쿼리 병합하기
▲파워쿼리 편집기에서 ① 기준 데이터의 쿼리('주문 개요')를 선택 후, ② 홈 탭 → '쿼리 병합' 버튼을 클릭하면 병합 옵션 화면이 생성됩니다. ③ 병합할 기준 열(VLOOKUP할 열)을 각각 클릭하고 확인을 누릅니다.
▲병합할 기준 열을 클릭 후 확인 버튼을 누르면, 위의 사진처럼 '고객세부정보'라는 열이 생깁니다. 각 셀의 'Table'은 주문개요의 '고객명'과 일치하는 '고객세부정보' 쿼리 내 데이터를 의미합니다. 열의 우측 상단의 버튼을 클릭하면 고객세부정보에서 끌고 올 데이터를 선택할 수 있습니다. '주문 개요'에 이미 고객명이 있으니 고객명은 제외하고 확인을 누르도록 하겠습니다.
▲확인 버튼을 누르면, 위의 사진처럼 고객명 별로 성별, 나이, 지역 등 고객세부정보 데이터가 병합됩니다. 동일한 방법으로 상품분류와 상품제조자 쿼리도 병합해주면 4개의 데이터가 병합됩니다.
2) 병합된 데이터 출력하기
▲현재 주문개요는 '연결용 쿼리'입니다. 따라서 병합된 쿼리를 출력하기 위해서는 ① 주문개요 쿼리에 마우스 우클릭 → '참조' 버튼을 클릭해서, ② 주문개요 쿼리의 결과값을 불러오는 새로운 쿼리를 만들어야 합니다.
처음부터 주문개요 쿼리를 출력용 쿼리로 만드는 방법도 있지만, 후에 어떤 쿼리가 추가될지 몰라서 저는 원본 데이터 쿼리와 출력용 쿼리를 분리하는 편입니다.
참조를 통해 출력용 쿼리('주문개요(2)')를 만들었으면, ③ 닫기 및 다음으로 로드를 클릭 후 → 데이터 가져오기에서 '표'를 선택하시면 아래의 사진처럼 새로은 시트에 병합된 로데이터가 생성됩니다.
▲파워쿼리 편집기를 처음 사용할 때 낯설고 어려울 수도 있지만, '쿼리 병합' 기능은 직관적인 편이라 금방 익숙해질 수 있을 것입니다. 파워쿼리는 병합해야 할 데이터 종류가 많을 때 일반 엑셀 함수보다 더 효과적으로 데이터를 합칠 수 있습니다. 또, 원본 데이터 표가 바뀌어도 데이터 탭에서 '새로고침' 버튼만 누르면 자동적으로 업데이트가 되어 일반 엑셀 함수를 사용할 때보다 더 편리합니다.
(파워쿼리 완성본 파일에서 고객명이나 상품명을 바꿔서 실험해 보세요)
추후에는 파워쿼리를 통해 여러 파일의 데이터를 병합하거나 취합하는 방법을 소개해 드리겠습니다.
<관련 글>
[파워쿼리] 한 개의 파일 내 여러 데이터 합치기 (VLOOKUP 상위호환)
'엑셀, VBA, 파워쿼리 > 파워쿼리' 카테고리의 다른 글
| [파워쿼리] 파워쿼리의 필터 기능(기본 사용법, And/Or 조건 추가) (5) | 2023.10.18 |
|---|---|
| [파워쿼리] 쿼리추가로 여러 데이터를 하나의 표로 합치기 (2) | 2023.10.12 |
| [파워쿼리] 파워쿼리로 다중조건 VLOOKUP 손쉽게 하기 (2) | 2023.10.07 |
| [파워쿼리- 쿼리 생성하는 5가지 방법] #5 빈 쿼리로 쿼리 생성하기 (3) | 2023.10.07 |
| [파워쿼리- 쿼리 생성하는 5가지 방법] #4 폴더에서 가져오기 (여러 파일 취합하기) (1) | 2023.10.07 |
댓글