복붙 노가다 끝! 여러 시트 데이터 → 피벗테이블 완벽 자동화 | 초보자도 OK! 10분 완벽 정리

여러 시트 데이터를 표로 만들고 파워쿼리로 한 번에 취합하기

먼저 각 지점별 판매 데이터가 들어 있는 시트들을 준비해야 해요. 예를 들어 강남점, 홍대입구점처럼 지점마다 별도 시트로 나뉘어 있다고 생각하면 됩니다. 이 상태에서 바로 파워쿼리를 쓰려면 먼저 범위를 표로 바꿔야 해요.

범위를 선택한 뒤 삽입 탭 → 표를 클릭하고, 머리글 포함 옵션을 체크하세요. 테이블 디자인 탭에서 표 이름을 ‘강남점’처럼 명확하게 바꿔두는 게 나중에 관리하기 편해요. 이렇게 표로 바꾸는 이유는 파워쿼리가 표를 인식해야 자동으로 데이터를 불러올 수 있기 때문입니다.

표 변경이 번거롭다면 Microsoft 365 사용자라면 VSTACK 함수를 대안으로 쓸 수도 있어요. 하지만 오늘은 파워쿼리 중심으로 진행할게요. 모든 시트를 표로 바꿨다면 데이터 탭 → 데이터 가져오기 → 빈 쿼리를 클릭해 파워쿼리 편집기를 엽니다.

편집기에서 수식 입력줄에 =Excel.CurrentWorkbook()을 입력하면 현재 통합문서에 있는 모든 표가 한 번에 나타나요. [Contents] 옆 확장 버튼을 클릭하고 ‘원래 열 이름을 접두사로 사용’ 옵션은 꼭 체크 해제하세요. 그러면 시트 이름이 별도 열로 정리되면서 데이터가 깔끔하게 쌓입니다.

시트 이름 열이 지점명과 겹친다면 우클릭해서 제거하면 돼요. 쿼리 이름을 ‘시트취합’으로 바꾼 뒤 닫기 및 로드 → 연결만 만들기를 선택하면 끝입니다. 이 과정 하나로 여러 시트 데이터가 하나의 쿼리로 합쳐지는 거예요.

피벗테이블 보고서 만들고 새 시트 추가 시 자동 업데이트 확인

이제 만든 쿼리를 피벗테이블로 연결해 볼게요. 쿼리 및 연결 패널에서 ‘시트취합’을 우클릭 → 다음으로 로드 → 피벗테이블 보고서를 선택하고 새 워크시트에 출력합니다.

피벗테이블 필드에서 지점명을 행으로, 금액을 값으로 끌어다 놓으면 각 지점별 합계가 바로 나타나요. 여기서 가장 큰 장점은 자동 업데이트 기능입니다. 나중에 잠실새내점이나 수원정자점 같은 새 시트를 추가하고, 기존 피벗테이블을 우클릭해서 새로 고침만 하면 데이터가 실시간으로 반영돼요.

직접 복붙할 필요 없이 새 시트만 추가하면 끝나니, 매달 지점 데이터가 늘어나는 회사에서 특히 유용합니다. 단, 새로 추가한 시트도 반드시 표로 먼저 변환해야 자동 인식된다는 점 잊지 마세요.

파워쿼리 병합으로 VLOOKUP까지 자동 적용하는 실전 팁

제품 코드만 있는 데이터에 제품명과 카테고리를 추가하고 싶다면 파워쿼리 병합 기능을 쓰면 됩니다. 먼저 제품 목록 시트를 별도 표로 만든 뒤, 데이터 탭 → 테이블/범위에서를 클릭해 파워쿼리로 불러옵니다.

시트취합 쿼리를 선택한 상태에서 홈 탭 → 쿼리 병합을 클릭하고, 제품 목록을 아래쪽에 추가합니다. 병합 기준은 양쪽 모두 ‘제품코드’ 열로 선택하세요. 일치하는 행이 203개처럼 표시되면 성공입니다.

확인 후 제품명과 제품 카테고리만 확장하고, 원래 열 이름 접두사는 체크 해제합니다. 이렇게 하면 VLOOKUP 수식 없이도 자동으로 정보가 들어와요.

마지막으로 시트취합 쿼리의 첫 단계에서 ‘원본’을 클릭해 제품목록 시트를 제외하는 설정을 꼭 해주세요. 그래야 실제 판매 데이터만 취합되고 보고서가 깔끔해집니다. 모든 설정이 끝나면 다시 피벗테이블을 새로 고침하면 제품명과 카테고리가 필드에 추가돼 원하는 분석이 가능해져요.