이번 글에서는 실습을 위해 두 개의 데이터 세트를 준비했습니다. 첫 번째는 판매 정보 데이터 세트이고, 두 번째는 영업사원의 담당 지역 정보 데이터 세트입니다. 판매 정보 데이터 세트에는 주문 날짜, 품목, 영업사원, 수량, 단가, 수수료, 총 비용 항목이 포함되어 있습니다.
영업사원 지역 데이터 세트에는 영업사원 이름과 해당 영업사원의 판매 지역이 기록되어 있습니다. 이제 파워 쿼리(Power Query)를 활용해 이 두 개의 테이블을 하나로 합치는 방법을 단계별로 알아보겠습니다.
1단계 – 데이터 세트를 엑셀 테이블로 변환하기
- 첫 번째 데이터 세트에서 B4부터 H17까지의 셀 범위를 선택합니다.
- 리본 메뉴에서 [삽입] 탭으로 이동합니다.
- 테이블 그룹에서 [테이블]을 선택합니다.
- [테이블 만들기] 대화 상자가 나타납니다.
- 앞서 범위를 선택했기 때문에 해당 범위가 자동으로 입력되어 있습니다.
- [테이블에 머리글 포함] 옵션에 체크합니다.
- [확인]을 클릭합니다.
- 아래와 같이 테이블이 생성됩니다.
- 테이블 내 임의의 셀을 선택하면 리본 메뉴에 [테이블 디자인] 탭이 활성화됩니다.
- [테이블 디자인] 탭을 클릭한 뒤 속성 그룹에서 테이블 이름을 Sales_Data로 지정합니다.
- 같은 방식으로 두 번째 데이터 세트에서 B4부터 C9까지의 셀 범위를 선택합니다.
- 리본 메뉴에서 [삽입] 탭으로 이동합니다.
- 테이블 그룹에서 [테이블]을 선택합니다.
- [테이블 만들기] 대화 상자가 나타나면 선택한 범위가 자동으로 입력됩니다.
- [테이블에 머리글 포함]에 체크하고 [확인]을 클릭합니다.
- 아래 스크린샷과 같이 두 번째 테이블이 생성됩니다.
- 테이블 내 아무 셀이나 클릭해 [테이블 디자인] 탭을 연 뒤, 속성 그룹에서 테이블 이름을 Region으로 지정합니다.
2단계 – 엑셀 파워 쿼리로 두 테이블 간 연결 만들기
- 첫 번째 테이블의 아무 셀이나 선택합니다.
- 리본 메뉴에서 [데이터] 탭으로 이동합니다.
- 데이터 가져오기 및 변환 그룹에서 [테이블/범위에서] 옵션을 선택합니다.
- 이렇게 하면 Sales_Data 테이블이 파워 쿼리 편집기로 불러와집니다.
- 리본 메뉴에서 [홈] 탭을 선택합니다.
- 닫기 그룹의 [닫기 및 로드] 드롭다운을 클릭합니다.
- 드롭다운 목록에서 [닫기 및 다음으로 로드...]를 선택합니다.
- [데이터 가져오기] 대화 상자가 나타납니다.
- 통합 문서에서 이 데이터를 보려는 방법 선택 섹션에서 [연결만 만들기]를 선택합니다.
- [확인]을 클릭합니다.
- 그러면 테이블 이름으로 된 연결이 생성되며, 통합 문서 오른쪽에 쿼리 창이 나타납니다.
- 같은 방법으로 두 번째 테이블의 아무 셀이나 선택합니다.
- 리본 메뉴에서 [데이터] 탭으로 이동합니다.
- 데이터 가져오기 및 변환 그룹에서 [테이블/범위에서] 옵션을 선택합니다.
- Region 테이블이 파워 쿼리 편집기로 열립니다.
- [홈] 탭에서 [닫기 및 로드] 드롭다운을 클릭하고, [닫기 및 다음으로 로드...]를 선택합니다.
- [데이터 가져오기] 대화 상자가 나타나면 [연결만 만들기]를 선택한 후 [확인]을 클릭합니다.
- 두 번째 연결 역시 테이블 이름으로 생성되어 오른쪽 쿼리 창에 표시됩니다.
3단계 – 파워 쿼리에서 두 테이블을 하나로 병합하기
- 리본 메뉴에서 [데이터] 탭을 선택합니다.
- 데이터 가져오기 및 변환 그룹의 [데이터 가져오기] 드롭다운을 클릭합니다.
- 쿼리 결합 옵션에서 [병합]을 선택합니다.
- [병합] 대화 상자가 나타납니다.
- 첫 번째 드롭다운에서 Sales_Data 테이블을, 두 번째 드롭다운에서 Region 테이블을 선택합니다.
- 두 테이블 모두에서 영업사원(Sales Rep.) 열을 선택해 연결 기준을 설정합니다.
- [확인]을 클릭합니다.
- 아래 스크린샷처럼 파워 쿼리 편집기에서 병합된 결과를 확인할 수 있습니다.
4단계 – 병합된 테이블을 엑셀로 가져오기
- 열 머리글의 양쪽 화살표 아이콘을 클릭합니다.
- 필터 옵션이 나타나면 Region 항목을 선택합니다.
- [확인]을 클릭합니다.
- 해당 열에 지역 정보가 표시됩니다. 아래 스크린샷을 참고하세요.
- 필요하다면 지역 열을 영업사원 열 옆으로 끌어다 놓을 수 있습니다.
- 머리글을 클릭한 상태로 드래그하여 원하는 위치에 배치합니다.
- 리본 메뉴에서 [홈] 탭을 선택합니다.
- [닫기 및 로드] 드롭다운에서 [닫기 및 다음으로 로드...]를 선택합니다.
- [데이터 가져오기] 대화 상자가 나타납니다.
- 통합 문서에서 이 데이터를 보려는 방법 선택 섹션에서 [테이블]을 선택합니다.
- 데이터를 배치할 위치로 [새 워크시트]를 지정합니다.
- [확인]을 클릭합니다.
- 최종적으로 아래 스크린샷과 같은 결과를 얻을 수 있습니다.
파워 쿼리로 여러 열을 기준으로 테이블 조인하는 방법
- 앞서 설명한 절차대로 두 테이블 간 연결을 먼저 만듭니다.
- 리본 메뉴에서 [데이터] 탭으로 이동합니다.
- 데이터 가져오기 및 변환 그룹의 [데이터 가져오기] 드롭다운을 클릭합니다.
- 쿼리 결합 옵션에서 [병합]을 선택합니다.
- 병합 대화 상자에서 Ctrl 키를 누른 상태로 조인 기준이 될 여러 개의 열을 차례로 클릭합니다.
- 이후 병합 작업을 계속 진행하면 됩니다.
엑셀에서 병합된 테이블 새로 고침하는 방법
통합 문서 오른쪽에 표시되는 쿼리 창에서 새로 고침을 실행할 수 있습니다.
만약 쿼리 창이 사라졌다면 쉽게 다시 불러올 수 있습니다. 리본 메뉴에서 [데이터] 탭으로 이동한 뒤, 쿼리 및 연결 그룹에서 [쿼리 및 연결]을 선택하면 됩니다.
또한 [데이터] 탭의 쿼리 및 연결 그룹에서 [모두 새로 고침]을 선택해 병합된 테이블을 갱신할 수도 있습니다.
또는 병합된 테이블의 아무 셀이나 클릭하면 리본 메뉴에 [쿼리] 옵션이 나타나는데, 이때 로드 그룹의 [새로 고침]을 선택하면 됩니다.
실습용 통합 문서 다운로드
관련 글
- 엑셀에서 왼쪽 조인(Left Join) 수행하는 방법
- 엑셀에서 왼쪽 외부 조인(Left Outer Join) 수행하는 방법
- 엑셀에서 내부 조인(Inner Join) 수행하는 방법
- 엑셀에서 전체 외부 조인(Full Outer Join) 만드는 방법
- 엑셀에서 교차 조인(Cross Join) 만드는 방법
<< 파워 쿼리 엑셀로 돌아가기 | 엑셀 배우기