파워 쿼리와 VLookup 비교(2)

파워 쿼리는 엑셀에 추가된 기능으로 다양하게 데이터를 검색 및 가공할 수 있는 기능을 가지고 있으며, VBA 대신 M언어가 있어서 프로그램할 수 있는 구조로 되어 있습니다.

그러나 엑셀과 구조가 많이 다르기 때문에 처음 접하게 되면 많이 낯설고, 원하는 데이터를 추출하고 가공하기가 많이 힘듭니다

그렇지만 간단하게 VLookup 기능처럼 사용하는 것을 해보겠습니다.

VLookup 기능의 장, 단점 및 활용에 대해서는 아래 글을 참고 바랍니다.

 

https://lsw3210.tistory.com/472

 

1. 파워 쿼리 편집기로 데이터 보내기

매출자료.xlsx

0.01MB

 

 

데이터 탭에서 테이블/범위에서를 클릭하면

 

표 만들기 대화상자가 표시되면서 데이터 범위와 머리글 포함에 체크가 되는데 맞으므로 확인 버튼을 누릅니다.

 

그러면 파워 쿼리 편집기가 열리면서 쿼리 이름은 표1이라고 명명되고, 엑셀처럼 열 이름이 아니라 머리글이 표시되고, 레코드가 연결된 데이터베이스 모양으로 표시됩니다.

 

데이터베이스에서 지점명 등은 필드(파워 쿼리에서는 열로 표기), 1,2,3행은 레코드라고 합니다.

이제 닫기 및 로드 아래 '닫기 및 다음으로 로드'를 누릅니다. 그러면 엑셀로 돌아오는데,

 

표시할 방법으로 표, 피벗 테이블 보고서, 피벗 차트, 연결만 만들기가 있는데, 연결만 만들기를 클릭하고 확인 버튼을 누릅니다.

 

그러면 엑셀 오른쪽에 쿼리 및 연결 창이 열리면서 표1이 표시되는데, "연결 전용입니다"라고 주석이 표시됩니다.

 

이제 지점명과 매출일자에 A지점, 1/5이라고 입력하고,

여기에 커서를 놓은 상태에서 데이터 - 테이블/범위에서를 선택합니다.

 

그러면 위와 똑같이 표를 만드는데, 범위가 F2셀까지 하는 것이 맞지만 G2셀까지 포함되더라도 문제없으므로 확인 버튼을 누릅니다. 그러면 파워 쿼리 편집기에 표2가 생성됩니다.

그런데 날짜가 날짜 시간으로 표시되므로

 

달력 표시를 누른 후 날짜로 변경합니다.

 

그러면 '현재 전환 바꾸기', '새 단계 추가', '취소' 등 버튼이 표시됩니다. 새 단계를 추가해도 되지만, 현재 전환 바꾸기로 하는 것이 좋습니다.

 

표1의 날짜/시간도 날짜도 날짜를 바꾸는데, 현재 전환 바꾸기를 선택합니다.

 

파워 쿼리 편집기 오른쪽을 보면 아래와 같이 속성에 이름과 적용된 단계가 표시됩니다. 위에 있는 버튼 중 '새 단계 추가'의 단계'가 이 적용된 단계를 말합니다.

 

2. 두 개의 표 병합하기

두 개의 표 병합하기가 SQL로 보면 Join이고, Python으로 보면 Merge에 해당합니다.

병합하기 메뉴는 홈 텝에서 결합 그룹에 있습니다.

 

쿼리 병합 오른쪽 콤보 상자를 누르면

'쿼리 병합'과 '쿼리를 새 항목으로 병합' 2개가 있는데, 쿼리 병합은 기존 표를 변경시키는 것이고, '쿼리를 새 항목으로 병합'은 쿼리를 새로 만들어 거기서 작업을 하는 것을 말합니다.

 

표2 쿼리가 선택된 상태에서 '쿼리를 새 항목으로 병합'을 클릭합니다. 그러면 병합 창이 열리는데 위에는 표2가 선택되어 있고, 아래에 콤보 박스가 있고 비어 있는데,

 

콤보 상자를 누른 다음 표1을 클릭합니다. 그러면 아래와 같이 표2와 표1이 표시되는데, 두 개의 일치 열(필드)이 지점명과 매출일자이므로 표2와 표1에서 각각 지점명을 클릭하고, Ctrl 키를 누른 상태에서 매출일자를 눌러 2개를 모두 순서대로 선택합니다(필드명은 달라도 되는데, 순서는 맞아야 합니다).

 

표1 아래를 보면 조인 종류가 있는데, '왼쪽 외부'가 선택되어 있습니다. 왼쪽이 없는데 왼쪽이라고 표시되는 이유는 SQL에서 사용되는 용어가 Left Outer Join이라 그런 것이고, 위 병합 창을 기준으로 설명하면 '왼쪽'은 '위'가 됩니다.

그리고, 왼쪽 외부를 누르면

 

'오른쪽 외부'와 '오른쪽 앤티'가 있는데 오른쪽은 병합 창을 기준으로 하면 '아래'가 됩니다.

 

그리고, 괄호 안을 보면 설명이 첫 번째(위)의 모두, 두 번째(아래)의 일치되는 행이라고 되어 있어서 맞고,

그 아래 '선택 영역은 첫 번째 테이블에서 1/1개의 테이블과 일치합니다'라고 표시됩니다.

 

확인 버튼을 누르면 아래와 같이 병합(검색) 결과가 표시되는데, 매출액은 null이고 표1에 Table(우리말로는 표)이라고 표시됩니다. 역시 표2 만들 때 F2셀까지 지정하는 것이 맞았네요.

 

매출액 필드에서 마우스 오른쪽 버튼을 누르고, 제거를 누릅니다.

 

이제 표1아래 Table을 처리해야 하는데, 표1의 오른쪽에 있는 확장아이콘을 누르면

 

지점명, 매출일자, 매출액이 보이는데, 우리는 표1의 매출액만 필요하므로 나머지 체크는 없애고, 매출일자의 체크만 남겨둡니다. 그리고, 아래를 보면 '원래 열 이름을 접두사로 사용'에 체크되어 있는데 여기서 열 이름이 표1이므로 굳이 남겨둘 필요가 없으므로 마찬가지로 체크를 없애고 확인 버튼을 누릅니다.

 

그러면 A지점의 1/5일 자 매출액 32000이 구해졌습니다.

 

3. 병합 결과 엑셀로 보내기

닫기 및 로드 아래 '닫기 및 다음으로 로드'를 누르고, 연결만 만들기를 클릭합니다.

그러면 쿼리 및 연결 창에 표1, 표2, 병합1 쿼리 3개가 연결 전용으로 만들어졌습니다.

이때 표로 만들면 표2로 표로 만들어지기 때문에 연결만 전용으로 만든 것입니다.

 

이제 병합1에서 마우스 오른쪽 버튼을 누르고, '다음으로 로드'를 누릅니다.

 

그러면 데이터 가져오기 창이 표시되는데, 이번에는 표를 선택하고, 기존 워크시트를 선택한 다음 i1셀을 클릭하고, 확인 버튼을 누릅니다.

 

그러면 i열부터 일치하는 데이터가 표시됩니다.

 

다음 편에서는 VLookup함수를 이용하면 여러 개의 데이터가 있더라도 하나만 표시되는 문제점이 있는데, 파워 쿼리에서 중복되는 데이터를 뽑아내는 방법을 해보겠습니다.

 

매출자료(파워쿼리)1.xlsx

0.02MB