Ⅰ. 문제
아래와 같이 A열이 일련번호이고, 1행이 구분자인데
행과 열을 바꾼 다음 데이터를 10개씩 쌓으려고 하는 것입니다.
Ⅱ. VBA와 장단점 비교
VBA로 만들어 놓으면 만들기까지는 힘들지만 향후 열의 개수가 변동되더라도 자유롭게 적용할 수가 있는데
파워 쿼리 UI를 이용하면 그렇지 못한 불편한 점은 있지만,
이와 같은 형태의 데이터 처리는 VBA보다는 파워 쿼리 UI를 이용하는 것이 훨씬 쉽네요.
물론 M언어를 이용하는 것은 VBA만큼 어렵습니다.
Ⅲ. 처리 순서
1. 데이터 - '테이블/범위에서' 명령 실행
데이터 - '테이블/범위에서' 명령을 실행해서 파워 쿼리 편집기를 실행합니다.
다른 사람이 만든 쿼리를 볼 때는 오른쪽에 쿼리 및 연결 창이 안보이므로
데이터 탭에서 '쿼리 및 연결' 명령을 누릅니다.
그러면 오른쪽에 쿼리 및 연결 창이 표시됩니다.
2. 머리글을 첫 행으로 사용
BH-1 등이 데이터가 될 수 있도록 '첫 행을 머리글로 사용'옆의 콤보 버튼을 누른 후 '머리글을 첫 행으로 사용'을 클릭합니다.
그러면 머리글이 첫 행으로 이동하고 머리글이 Column1, 2, 3 식으로 생성됩니다.
3. 행/열 바꿈
원하는 포맷으로 만들기 위해 변환 탭의 '행/열 바꿈'을 누릅니다.
4. 표를 복제해서 3개 만들기
10개씩 잘라서 세로로 쌓기 위해 표를 복제해서 3개를 만듭니다.
5. 표1에서 3까지 10개씩 자르고, 열 이름 통일
- 표1에서 Column1부터 Column11(No가 있어서 11개임)까지 선택한 후 마우스 오른쪽 버튼 누른 후 '다른 열 제거' 클릭
- 표1 (2)를 클릭하고, Column1, Column12부터 Coloumn21까지 선택한 후 다른 열 제거 클릭
→ 열을 선택할 때 Column1을 클릭하고, Column12을 클릭한 다음 Column21에서 Ctrl+Shift를 누른 상태에서 마우스 왼쪽 버튼을 클릭하면, 떨어져 있는 범위를 쉽게 연속적으로 선택 가능합니다.
- 표1 (3)를 클릭하고, Column1, Column22부터 Coloumn25까지 선택한 후 다른 열 제거 클릭
- 쿼리 추가를 하려면 열 이름이 같아야 하므로
표1 (2)를 선택한 후 '머리글을 첫 행으로 사용'을 클릭합니다.
그러면 머리글이 Column1, Column12에서 Column1, 2, 3식으로 생성됩니다.
이제 Column12, 13 등 1행은 필요 없으므로 홈 탭의 '상위 행 제거'명령을 클릭한 후
행 수에 1을 입력하고 확인 버튼을 누릅니다.
그러면 Column 12, 13 등 1행이 없어집니다.
- 표1 (3)도 마찬가지로 작업해서 머리글을 Column1,2,3 식으로 바꿉니다.
6.. 정렬을 위해 표별로 인덱스 열을 추가
- 표1을 선택하고 인덱스열을 클릭합니다.
그러면 맨 오른쪽에 0부터 1 간격으로 인덱스가 추가됩니다.
표1 (2)와 표1 (3)에도 인덱스열을 추가합니다.
7. 쿼리 추가
표1, 표1 (2)와 표1 (3)을 세로로 쌓기 위해
표1을 클릭해서 선택한 다음
홈 탭의 '쿼리를 새 항목으로 추가' 명령을 누르고,
'2개의 테이블'에서 '3개 이상의 테이블로 바꾸고,
표1 (2)와 표1 (3)을 더블 클릭해서 오른쪽 '추가할 테이블'에 추가합니다.
그리고, 확인 버튼을 누르면 No가 1행에 있고, 떨어져서 38행에 있으므로
인덱스 열 기준으로 오름차순으로 정렬합니다.
그러면 No, BH-1식으로 3개씩 표시됩니다.
8. Column2가 공백인 데이터 제거하기
Column2의 콤보 버튼을 누르면 (비어 있음)이란 데이터가 있는데 체크를 해제하고,
확인 버튼을 누릅니다.
그러면 No는 3개이지만, BH1-1과 BH1-2는 2개씩, BH1-3은 1개만 표시됩니다.
그리고, 맨 오른쪽에 있는 인덱스 열은 필요 없으므로 제거합니다.
9. 연결만 만들기
'연결만 만들기'를 하지 않으면 표1, 표1 (2), 표1 (3)과 추가1이란 4개 시트가 만들어지므로
'연결만 만들기'를 하는 것이 좋습니다.
홈 탭에서 '닫기 및 로드' 아래 버튼을 누른 후
'닫기 및 다음으로 로드'를 누르고
아래 화면이 나오면 '연결만 만들기'를 클릭하고 확인 버튼을 누릅니다.
그러면 '쿼리 및 연결'창에 쿼리가 4개 만들어지는데 모두 '연결 전용'으로 표시됩니다.
10. 추가1을 표로 만들기
추가 1에서 마우스 오른쪽 버튼을 누른 후
'다음으로 로드'를 누르면
다시 위와 같은 '데이터 가져오기' 창이 열리는데
표와 '새 워크시트'를 선택한 다음 확인 버튼을 누릅니다.
그러면 아래와 같이 표가 만들어집니다.
11. 표 형태를 범위로 바꾸기(X)
범위로 변환하기 위해서는 '테이블 디자인' 탭에서 '범위로 변환' 명령을 누르면 됩니다.
그러면 '쿼리 정의가 영구히 제거되고 표가 정상 범위로 변환된다'라고 하는데,
그러면 원본 데이터 변경 시 '새로 고침'이 안되므로 '취소'를 누르는 것이 좋습니다.
'Excel - 파워 쿼리' 카테고리의 다른 글
| 파워 쿼리와 VLookup 비교(2) (0) | 2024.04.18 |
|---|---|
| 파워 쿼리와 VLookup 비교(1) (0) | 2024.04.17 |
| (파워 쿼리) 변환의 추출과 열 추가의 추출(2) (0) | 2023.07.11 |
| (파워 쿼리) 변환의 추출과 열 추가의 추출(1) (0) | 2023.07.10 |
| 데이터 탭의 텍스트 나누기와 파워 쿼리의 열 분할 (0) | 2023.07.07 |