파워 쿼리에는 표를 쿼리로 만들어 두 개 이상의 표를 결합, 다시 말해 열을 기준으로 아래에 데이터를 추가하거나, 행을 기준으로 오른쪽으로 데이터를 병합하는 기능이 있습니다.
1. 쿼리 추가
가. 의미
Vstack함수 사용 시 열의 위치를 기준으로 세로로 데이터를 추가함에 따라 열 이름이 불일치하는 문제가 있었는데,
이를 해결할 수 있는 기능이 파워 쿼리의 추가 기능입니다.
나. 실행 방법
위 2개는 열의 위치가 맞으므로 문제가 없으므로, 세 번째 열의 위치가 다른 것으로 파워 쿼리의 추가 기능을 실습해 보겠습니다.
① 먼저 A20부터 B23셀까지의 데이터 영역 중 아무 데나 커서를 넣고,
데이터 탭, 데이터 가져오기 및 변환 그룹의 '테이블/범위에서'를 클릭합니다.
② 이미 표가 만들어져 있다면 표를 만드는 절차가 생략되는데,
범위로 되어 있으므로 아래와 같이 표를 만드는 창이 생깁니다.
범위 및 '머리글 포함'에 체크해야 하는 것이 맞으므로 확인 버튼을 누릅니다.
③ 그러면 파워 쿼리 편집기가 실행되면서 왼쪽에는 표1, 오른쪽에는 표가 보입니다.
④ 위 화면에서 홈 탭, 닫기 그룹의 닫기 및 로드의 아래 부분을 누른 후 '닫기 및 다음으로 로드...'를 클릭합니다.
⑤ 그러면 파워 쿼리 편집기가 닫히면서 '데이터 가져오기' 창이 열리는데, '연결만 만들기'를 선택하고 확인 버튼을 누릅니다.
⑥ 그러면 워크 시트 오른쪽에 '쿼리 및 연결 창이 표시되고, 쿼리 목록에 '표1'이 표시됩니다.
⑦ 이번에는 오른쪽 데이터 영역을 선택한 후 위와 같은 절차를 실행해서 표2 쿼리 만드는데, 닫기 및 로드를 아직 누르면 안 됩니다.
⑧ 표1을 클릭한 다음 홈 탭, 결합 그룹에서 쿼리 추가 오른쪽의 콤보 상자 버튼을 누른 다음 '쿼리를 새 항목으로 추가'를 누릅니다.
⑨ 그러면 '두 테이블의 행을 단일 테이블에 연결합니다'란 설명이 나오고, 아래에 첫 번째 테이블이 표1로 되어 있고, 두 번째 테이블을 선택하라고 하므로 표2를 선택하고 확인 버튼을 누릅니다.
⑩ 그러면 왼쪽에 추가1 쿼리가 추가되고, 오른쪽에 표1에 표2가 세로로 추가되어 표시되는데, 지점명과 판매량의 위치가 맞게 표시되고, 판매량이 없는 것은 null로 표시됩니다.
이제 닫기 및 로드 윗부분을 누르면
엑셀에 '추가1' 시트가 새로 생기면서 추가1 쿼리 표가 표시됩니다.
오른쪽 쿼리 및 연결 창에도 표2와 추가1 쿼리가 추가됐습니다.
2. 쿼리 병합
이번에는 Hstack시트를 선택한 다음,
① 데이터 탭의 테이블/범위에서를 누르면 표를 생성하는 단계가 나오고, 확인 버튼을 누르면
아래와 같이 표4 쿼리가 추가됩니다.
위 화면 오른쪽에는, 아래와 같이 쿼리 설정 아래 적용된 단계가 표시되어 단계명 왼쪽의 X표시를 눌러 단계를 뒤로 돌릴 수 있습니다.
② 닫기 및 다음으로 로드를 선택한 다음 '연결만 만들기'를 체크하고 확인 버튼을 누릅니다.
그리고, 오른쪽 데이터 영역을 쿼리로 만드는 절차를 진행합니다.
표5 쿼리가 추가됩니다.
③ 표4를 클릭한 다음 홈 탭, 결합 그룹의 쿼리 병합 아래 '쿼리를 새 항목으로 병합'을 클릭합니다.
④ 그러면 병합 창이 표시되면서, '병합된 테이블을 만들 테이블 및 일치하는 열을 선택하라'는 설명이 표시됩니다.
아래 콤보 상자를 누른 후 표5를 선택하고, 일치하는 열을 지정하기 위해 위의 성명과 아래의 성명을 클릭합니다.
그러면 아래 부분에 '첫 번째 테이블에서 총 4개 중 3개 성명이 일치한다'는 메시지가 표시됩니다.
확인 버튼을 누릅니다.
쿼리 추가의 경우는 일치하는 열을 선택하는 단계가 없는 것이 다른 점입니다.
⑤ 그러면 아래와 같이 성명을 기준으로 테이블이 결합되는데, 표5의 항목이 보이지 않으므로, 빨간 선으로 표시된 펼침 아이콘을 누릅니다.
⑥ 표5의 열 이름이 표시되는데, 성명은 중복되므로 체크를 해제하고 확인 버튼을 누릅니다.
⑦ 표5.성별과 표5.출신 열이 표시되는데,
열 이름을 변경하기 위해 마우스 오른쪽 버튼을 누른 후 '이름 바꾸기' 메뉴를 눌러서,
"표5."을 제외한 성별과 출신으로 두 개 다 변경합니다.
⑧ 이제 닫기 및 로드 명령을 누르면
병합1 시트가 추가되면서 작업한 결과가 표로 표시되고, 오른쪽 쿼리 및 연결 창에는 표4, 표5, 병합1 쿼리가 추가됐습니다.
3. 데이터 변경 시 새로 고침
피벗 테이블처럼 원본 데이터가 수정되는 경우 바로 수정되지 않고 '새로 고침'명령을 눌러야 합니다.
예를 들어 vstack 시트에서 G22셀에 판매량이 비어 있었는데 554라고 입력해도
추가1 시트를 가면 여전히 빈 셀로 되어 있습니다.
따라서, 쿼리 탭, 로드 그룹의 새로 고침 윗 부분을 눌러야
비로서 값이 반영됩니다.
열 또는 행의 수가 일치하는 경우는 특별히 문제가 없으므로 각자 해보기 바랍니다.
'Excel' 카테고리의 다른 글
| 중간값에 해당하는 값이 2개일 때 2개 모두 표시하기 (0) | 2023.05.30 |
|---|---|
| 조건부 서식 - 둘 이상 조건에 맞는 줄에 색칠하기 (0) | 2023.05.25 |
| 엑셀 Vstack, Hstack 함수 (2) | 2023.05.22 |
| 엑셀 Filter 함수 (0) | 2023.05.19 |
| Unique 함수와 데이터 유효성 검사의 문제점 (2) | 2023.05.17 |