파워 쿼리를 사용하여 엑셀에서 파일과 테이블을 병합하는 방법

만약 당신이 월요일마다 이메일 첨부 파일에서 데이터를 '마스터' 시트로 복사하는 데 시간을 보내고 있다면, 당신은 엑셀에서 작업하는 것이 아니라 수작업을 하는 것입니다. 파워 쿼리가 그것을 해결해 줍니다. 이 세 가지 핵심 데이터 통합 워크플로우를 마스터하면 몇 시간 동안의 복사-붙여넣기를 단일 새로 고침 클릭으로 바꿀 수 있습니다.

데이터를 덧붙이고, 병합하고, 통합하는 경우

복사-붙여넣기 광기를 한 번에 끝내자

데이터 정리는 전형적인 파워 쿼리 사용 사례이지만, 진정한 힘은 개별 테이블에 대해 생각하는 것을 멈추고 시스템 수준에서 생각하기 시작할 때 발휘됩니다. 대부분의 엑셀 사용자는 일주일의 많은 시간을 데이터를 복사-붙여넣기 하거나 최신 CSV를 찾아다니는 데 보냅니다.

Power Query는 세 가지 통합 워크플로우를 통해 이러한 수작업을 없애는 데 도움을 줍니다:

1. 테이블 추가하기: 이것은 세로로 쌓는 방식입니다. 동일한 헤더를 가진 여러 테이블—예를 들어 월별 점수—가 있을 때 사용하며, 이를 하나의 긴 마스터 목록으로 합치고자 할 때 사용합니다.
2. 관계형 병합: 이것은 수평 조인입니다. 이는 사람 이름과 같은 공통 데이터 포인트를 기반으로 한 테이블에서 다른 테이블로 관련 정보를 가져오는 데 사용됩니다.
3. 폴더 통합: 이것은 궁극적인 자동화 도구입니다. Excel을 드라이브의 폴더로 지정하면, 안에 있는 모든 파일을 자동으로 가져와 정리하고 하나의 테이블로 쌓아줍니다.

이것들을 숙달하면 Excel을 정적인 계산기에서 동적인 데이터 허브로 바꿀 수 있습니다.

워크플로 1: 단일 통합 문서에서 테이블 추가

여러 시트를 하나의 마스터 목록으로 합치기

Power Query의 추가(Append) 기능을 사용하면 여러 로컬 테이블을 하나의 연속된 데이터 세트로 통합할 수 있습니다.

시나리오: 연간 각 월에 해당하는 12개의 별도 시트가 있는 워크북이 있으며, 이를 결합하여 연간 요약 보고서를 만들어야 합니다.

시작하기 전에, 워크북이 준비되어 있는지 확인하세요:

1. 출력을 위해 빈 워크시트(예: 'Summary'라는 이름의)를 만드세요.
2. 각 범위를 Excel 테이블(Ctrl+T)로 서식 지정하세요.
3. 각 테이블에 이름을 지정하세요 (JanSales, FebSales 등).
4. 테이블 간에 헤더가 일치하는지 확인하세요.

설정을 확인했으면, 이 방법을 사용하여 그것들을 쌓으세요:

1. 데이터 탭을 열고 데이터 가져오기를 클릭하세요.
2. 다른 소스에서 위로 마우스를 가져간 후 빈 쿼리를 클릭하세요.
3. Power Query 편집기의 수식 입력줄에 = Excel.CurrentWorkbook()를 입력하고 Enter 키를 누릅니다. 이렇게 하면 모든 테이블과 이름 있는 범위가 로드됩니다.
4. 이름 열의 필터 화살표를 클릭한 후, 텍스트 필터 > 끝나는 문자를 선택합니다.
5. 첫 번째 값 필드에 Sales를 입력한 다음, 확인을 클릭하십시오. 이렇게 하면 Sales 테이블만 필터링됩니다.
6. 내용 열에서 확장 아이콘(두 개의 화살표)을 클릭하고, 원래 열 이름을 접두사로 사용의 선택을 해제한 후, 확인을 클릭합니다.

이제 모든 데이터가 보이므로, 데이터 유형을 설정하고 결과를 로드할 준비가 되었습니다:

1. Date 헤더에서 ABC123 아이콘을 클릭하고 Date를 선택하세요.
2. 분할된 닫기 및 로드 버튼의 아래쪽 절반을 클릭하고 닫기 및 로드 위치를 클릭합니다.
3. 테이블과 기존 워크시트를 선택한 후, 빈 요약 시트에서 셀 A1을 클릭하고 확인을 클릭합니다.
4. 홈 탭의 드롭다운 메뉴를 사용하여 금액 열을 회계 형식으로 지정하십시오.

이제 요약 시트에 단일 통합 마스터 테이블이 있습니다.

가장 좋은 점은? 손이 거의 가지 않는다는 것입니다. 새 테이블을 추가한 후 데이터 탭에서 모두 새로 고침을 클릭하면 자동으로 포함됩니다.

워크플로 2: 공통 열을 사용하여 테이블 연결

관계형 병합을 사용하여 관련 있지만 다른 데이터셋을 결합하기

Power Query에서 관계형 병합은 공통 연결을 찾아 한 테이블의 특정 데이터 포인트를 다른 테이블로 가져올 수 있게 해줍니다.

시나리오: 직원 이름, 나이, 위치를 포함하는 한 개의 테이블(AgeData)과 동일한 이름과 해당 부서 및 직급이 포함된 다른 테이블(DeptData)이 있습니다. 이 테이블들은 동일한 워크북의 서로 다른 워크시트에 있으며, 이를 하나의 테이블로 결합하고자 합니다.

먼저, 연결을 생성하여 두 테이블을 모두 Power Query에 불러와야 합니다:

1. 첫 번째 테이블(AgeData)의 셀을 선택하고 데이터 > 테이블/범위에서를 클릭하세요.
2. Power Query 편집기에서 분할된 닫기 및 로드 버튼의 아래쪽 절반을 클릭한 다음 다음으로 닫기 및 로드를 클릭합니다.
3. 연결만 만들기를 선택하고 확인을 클릭하세요.
4. 두 번째 테이블(DeptData)에 대해서도 이 단계를 반복한 후, 데이터 탭에서 쿼리 및 연결을 클릭하여 오른쪽 창에서 이 두 연결을 모두 확인하세요.

이제 두 테이블이 모두 로드되었으므로 병합을 수행할 준비가 되었습니다:

1. 데이터 탭에서 데이터 가져오기 > 쿼리 결합 > 병합을 클릭합니다.
2. 병합 대화 상자에서, 위쪽 드롭다운에서 하나의 테이블을 선택하고 아래쪽 드롭다운에서 다른 테이블을 선택합니다.
3. 두 테이블 미리보기에서 일치하는 브릿지 열(이 경우 직원 이름)을 클릭하여 강조 표시하십시오.
4. Join Kind이 Left Outer로 설정되어 있는지 확인한 후, OK를 클릭하세요. 이렇게 하면 첫 번째 테이블의 모든 행이 유지되고 두 번째 테이블의 일치하는 데이터가 추가됩니다.

이 시점에서 파워 쿼리 편집기가 열립니다. 첫 번째 테이블의 모든 열이 정상적으로 표시되지만, 두 번째 테이블의 데이터는 모든 셀에 단순히 "Table"이라고 표시되는 하나의 새 열로 압축됩니다.

이제 압축된 데이터를 볼 수 있으므로, 그것을 풀 준비가 되었습니다:

1. 새 테이블 열의 머리글에서 확장 아이콘을 클릭하세요.
2. 중복된 열(예: 직원 이름)의 선택을 해제하고, 열 머리글을 깔끔하게 유지하기 위해 원래 열 이름을 접두사로 사용 선택을 해제합니다. 그런 다음 확인을 클릭합니다.
3. 마지막으로, 분할된 닫기 및 불러오기 버튼의 상단 절반을 클릭하여 병합된 결과를 새 워크시트로 이동합니다.

이제 단 한 개의 수식도 작성하지 않고 두 개의 별도 목록을 성공적으로 결합했습니다.

이것은 동적 연결이기 때문에 병합 과정을 수동으로 반복할 필요가 없습니다. 항상 그렇듯이, 원본 데이터가 변경되면 원본 테이블과 열 구조가 변경되지 않은 경우 데이터 > 모두 새로 고침을 클릭하기만 하면 됩니다.

워크플로 3: 폴더에서 여러 파일 병합

외부 문서를 자동으로 통합하기

From Folder 커넥터는 디렉토리의 모든 파일에서 데이터를 가져옵니다. 주간 내보내기나 월간 명세서와 같은 반복 보고서에 이상적입니다.

시나리오: 동료들이 매주 Excel 파일을 공유 폴더에 업로드합니다. 모든 워크북에는 SalesData라는 이름의 워크시트 탭이 있으며, 매주 월요일마다 이 모든 데이터를 하나의 요약 시트로 합쳐야 합니다.

연결하기 전에, 들어오는 파일과 폴더가 표준화되어 있는지 확인하십시오:

1. 각 파일에는 같은 이름(예: SalesData)을 가진 워크시트가 포함되어야 합니다.
2. 열 머리글은 깔끔한 통합을 위해 일관되어야 합니다.
3. 모든 파일은 동일한 전용 폴더에 저장되어야 합니다.

이제 Power Query 편집기를 보안 게이트로 사용하여 올바른 파일만 통과하도록 하면서 Excel이 데이터 소스를 가리키도록 해야 합니다:

1. 새 Excel 통합 문서의 데이터 탭에서 데이터 가져오기 > 파일에서 > 폴더에서를 클릭합니다.
2. 보고서가 있는 폴더로 이동한 후, 열기를 클릭하세요.
3. 미리보기 창에서 데이터 변환을 클릭합니다. 바로 병합을 클릭하고 싶을 수 있지만, 데이터 변환을 클릭하면 보고서가 필요한 파일만 선택하도록 확인할 기회를 가질 수 있습니다.
4. 이제 폴더에 있는 모든 파일 목록이 표시됩니다. 확장자 또는 이름 열의 필터 화살표를 클릭하여 데이터 세트의 일부가 아닌 항목의 선택을 취소하세요.

이제 Power Query에 이러한 파일을 어떻게 열고 쌓을지 알려줄 수 있습니다:

1. Content 열의 헤더에서 파일 결합 버튼을 클릭하세요.
2. 파일 결합 대화 상자에서 목록에서 SalesData 워크시트를 선택합니다. 이렇게 하면 Power Query가 각 파일에서 해당 특정 워크시트를 추출하도록 지시합니다. 확인을 클릭합니다.
3. 쿼리 창에서 샘플 파일 변환을 선택합니다. 여기서 수행하는 모든 정리 작업—예를 들어 날짜 및 금액 열에 올바른 형식을 적용하는 것—은 폴더의 모든 파일에 적용됩니다.
4. 목록 하단에 있는 메인 쿼리(보통 폴더 이름으로 지정됨)를 다시 클릭하여 최종 누적 결과를 확인합니다. 필요하다면 Source 열을 제거할 수 있지만, 각 행이 어디에서 왔는지 볼 수 있도록 나는 보이게 두는 것을 선호합니다.
5. 최종 표를 워크북에 로드하려면 닫기 및 로드를 클릭하세요.

이제 자동화된 데이터 파이프라인을 구축했습니다.

다음 주에는 새 파일을 열어 복사-붙여넣기하는 대신, 그냥 그 폴더에 저장하고 데이터 > 모두 새로 고침을 클릭하세요.

Power Query는 수동 통합의 필요성을 제거합니다. 파일을 폴더에 넣고 모두 새로 고침을 클릭하기만 하면 됩니다. 변환 없이 단순한 범위만 결합해야 하는 경우, Microsoft 365용 Excel과 웹용 Excel에서 VSTACK 및 HSTACK을 사용할 수 있습니다.