Power Query와 반복 업무 자동화

손으로 반복하는 순간, 자동화가 시작됩니다

지난 장까지 우리는 데이터를 정리하고, 피벗테이블로 요약하고, 차트와 대시보드로 보여 주는 흐름을 배웠습니다. 이제 실무에서 자주 만나는 다음 장면을 떠올려 보겠습니다.

월요일 아침, 거래처에서 CSV 파일이 도착합니다. 파일을 열어 보니 필요 없는 열이 있고, 날짜는 텍스트처럼 보이며, 고객명 앞뒤에는 공백이 섞여 있습니다. 어떤 행은 비어 있고, 어떤 상품 코드는 기준표에 없는 값입니다. 우리는 매번 같은 순서로 열을 지우고, 필터를 걸고, 공백을 제거하고, 날짜 형식을 바꾸고, 피벗테이블을 새로 고칩니다.

한두 번은 괜찮습니다. 그러나 매주, 매월 같은 작업을 반복하고 있다면 이야기가 달라집니다. 그때부터 엑셀 실력은 "함수를 얼마나 많이 아는가"보다 "반복되는 과정을 얼마나 안정적으로 줄이는가"로 바뀝니다.

이 장의 주인공은 Power Query입니다. Power Query는 데이터를 가져오고, 정리하고, 합치고, 원하는 형태로 바꾼 뒤 엑셀에 다시 불러오는 도구입니다. 중요한 점은 한 번 처리한 과정이 적용된 단계로 기록된다는 것입니다. 다음 달에 새 파일이 들어와도 같은 작업을 다시 손으로 하지 않고, 새로 고침만 하면 됩니다. Microsoft의 Power Query 설명에서도 이 도구의 핵심을 여러 데이터 원본에 연결하고 원하는 형태로 변환하는 기능으로 소개합니다.¹

이 장에서는 Power Query를 이용해 반복되는 데이터 정리 업무를 줄이고, 매크로 기록기를 곁들여 화면 조작과 서식 작업까지 자동화하는 기초를 배웁니다. 목표는 거창한 프로그래밍이 아닙니다. 매번 똑같이 하는 일을 엑셀이 대신 기억하게 만드는 것입니다.

Power Query는 '데이터 정리 레시피'입니다

Power Query를 처음 보면 별도의 프로그램처럼 느껴질 수 있습니다. 하지만 원리는 생각보다 단순합니다. 요리 레시피와 비슷합니다.

원본 데이터가 재료라면, Power Query의 각 변환 작업은 조리 단계입니다. "필요 없는 열 제거", "날짜 형식으로 변경", "고객명 공백 제거", "중복 행 삭제", "상품 기준표와 병합" 같은 작업이 순서대로 기록됩니다. 마지막으로 완성된 결과를 엑셀 시트, 피벗테이블, 데이터 모델 등에 올려놓습니다.

이때 가장 중요한 개념은 원본은 그대로 둔다는 점입니다. Power Query는 원본 파일 자체를 직접 고쳐 버리는 방식이 아니라, 원본을 읽어 온 뒤 정리 과정을 적용해 결과를 만듭니다. 그래서 실무에서는 원본 파일을 보관하고, 정리 규칙은 Power Query에 저장하며, 보고서는 정리된 결과를 바라보게 만드는 구조가 안전합니다.

Power Query 편집기 화면은 크게 다음 요소로 이해하면 됩니다.

Power Query를 배울 때 "어떤 버튼을 눌러야 하지?"보다 먼저 잡아야 할 생각은 이것입니다. 내가 매번 반복하던 데이터 정리 절차를 단계로 남긴다. 그 단계가 쌓이면 내 업무만의 자동화 흐름이 됩니다.

데이터 가져오기: CSV, 엑셀 파일, 폴더까지 연결하기

Power Query 자동화는 데이터를 가져오는 순간부터 시작됩니다. 엑셀의 일반적인 열기와 다른 점은, 파일을 한 번 열고 끝나는 것이 아니라 연결을 만든다는 데 있습니다. 연결이 만들어지면 나중에 원본이 바뀌었을 때 새로 고침으로 다시 읽어 올 수 있습니다.

엑셀에서 Power Query를 시작하는 대표 경로는 데이터 탭 → 데이터 가져오기입니다. 버전과 환경에 따라 메뉴 이름이 조금 다를 수 있지만, 기본 흐름은 비슷합니다.

CSV 파일 가져오기

CSV는 쉼표로 구분된 텍스트 파일입니다. ERP, 쇼핑몰 관리자, 광고 플랫폼, 회계 시스템에서 내려받는 파일이 CSV인 경우가 많습니다. CSV를 그냥 더블클릭해서 열면 한글 인코딩, 날짜 형식, 앞자리 0이 있는 코드값 등에서 문제가 생길 수 있습니다.

Power Query로 CSV를 가져올 때는 데이터 → 텍스트/CSV에서를 선택합니다. 미리 보기 창에서 구분 기호와 파일 원본을 확인한 뒤, 바로 불러오기보다 데이터 변환을 눌러 Power Query 편집기에서 정리하는 습관을 들이세요. Excel Campus의 Power Query 예제에서도 CSV를 가져온 뒤 편집기에서 불필요한 열 제거, 빈 행 제거, 정렬 등을 수행하고 결과를 불러오는 흐름을 반복 업무 자동화의 기본으로 설명합니다.²

CSV를 가져온 뒤 가장 먼저 확인할 것은 열 이름과 데이터 형식입니다. 날짜처럼 보이지만 텍스트일 수 있고, 금액처럼 보이지만 쉼표 때문에 숫자로 인식되지 않을 수 있습니다. 이 단계에서 형식을 바로잡아야 뒤의 피벗테이블과 차트가 흔들리지 않습니다.

엑셀 통합 문서 가져오기

다른 엑셀 파일에서 데이터를 가져올 수도 있습니다. 예를 들어 지점별 매출 파일, 인사팀이 보내 준 사원 명부, 상품 마스터 파일을 현재 보고서 파일에 연결할 수 있습니다.

데이터 → 데이터 가져오기 → 파일에서 → Excel 통합 문서에서를 선택하면 파일 안의 시트, 표, 이름 정의된 범위 등을 선택할 수 있습니다. 가능하면 원본 파일 안의 데이터를 일반 범위가 아니라 엑셀 표로 만들어 두는 것이 좋습니다. 앞 장에서 배운 표 기능이 여기서 다시 힘을 발휘합니다. 표는 새 행이 추가되어도 범위가 자동 확장되므로 Power Query가 데이터를 안정적으로 읽어 오기 쉽습니다.

여기서 중요한 실무 원칙이 있습니다. 원본 파일의 열 이름을 함부로 바꾸지 않는 것입니다. Power Query의 단계는 열 이름을 기준으로 작동하는 경우가 많습니다. 예를 들어 "매출금액" 열을 숫자로 바꾸는 단계가 있는데, 다음 달 파일에서 열 이름이 "매출액"으로 바뀌면 해당 단계에서 오류가 날 수 있습니다.

폴더의 여러 파일 가져오기

Power Query의 진짜 매력은 폴더 연결에서 드러납니다. 매달 2026_01_매출.csv, 2026_02_매출.csv, 2026_03_매출.csv처럼 같은 구조의 파일이 쌓인다면, 한 파일씩 복사해서 붙여넣을 필요가 없습니다.

데이터 → 데이터 가져오기 → 파일에서 → 폴더에서를 선택하면 폴더 안의 파일 목록을 Power Query가 읽어 옵니다. 여기서 결합을 선택하면 같은 구조의 여러 파일을 하나의 표로 합칠 수 있습니다. 여러 CSV나 엑셀 파일을 폴더에서 가져와 결합하고, 이후 새 파일을 폴더에 넣은 뒤 새로 고침만으로 결과를 갱신하는 방식은 Power Query 자동화의 대표적인 사용 사례입니다.³

예를 들어 월별매출 폴더에 1월부터 12월까지의 CSV를 넣어 두고, Power Query로 폴더를 연결해 하나의 매출 원장으로 합친다고 해 보겠습니다. 다음 달에 13번째 파일이 들어오면 해야 할 일은 파일을 같은 폴더에 넣고 새로 고침을 누르는 것입니다. 복사, 붙여넣기, 마지막 행 찾기, 수식 범위 확장 같은 작업이 사라집니다.

적용된 단계: 엑셀이 기억하는 작업 순서

Power Query 편집기 오른쪽에는 적용된 단계가 있습니다. 이곳은 자동화의 심장입니다. 버튼을 클릭할 때마다 Power Query는 그 행동을 하나의 단계로 기록합니다.

예를 들어 CSV를 가져와 다음 작업을 했다고 해 보겠습니다.

1. 상위 2행 제거

2. 첫 행을 머리글로 사용

3. 불필요한 열 제거

4. 주문일 열을 날짜 형식으로 변경

5. 고객명 열의 앞뒤 공백 제거

6. 금액 열의 오류를 0으로 바꿈

7. 중복 주문번호 제거

이 작업들은 모두 적용된 단계에 순서대로 쌓입니다. 다음번에 같은 구조의 CSV가 들어오면 Power Query는 이 레시피를 다시 실행합니다. 이 덕분에 Power Query는 단순한 정리 도구가 아니라 반복 가능한 데이터 처리 흐름이 됩니다. Resourceful Finance Pro의 Power Query 자동화 설명에서도 각 변환이 적용된 단계에 기록되며, 이후 새로 고침으로 원본 데이터를 다시 가져와 같은 변환을 적용할 수 있다는 점을 핵심으로 다룹니다.⁴

적용된 단계는 나중에 수정할 수 있습니다. 단계 오른쪽의 톱니바퀴 아이콘이 있으면 설정을 다시 열 수 있고, 단계 이름을 바꾸거나 삭제할 수도 있습니다. 다만 아무 단계나 지우면 뒤 단계가 함께 깨질 수 있습니다. 예를 들어 "고객명" 열을 제거한 뒤 뒤쪽 단계에서 고객명 공백 제거를 하려고 하면 오류가 납니다.

실무에서는 단계 이름을 조금 더 친절하게 바꾸는 습관이 좋습니다. 기본 이름인 "변경된 유형", "제거된 열", "필터링된 행"만으로는 몇 주 뒤에 봤을 때 의미를 파악하기 어렵습니다. "주문일_날짜형식변환", "불필요한배송열제거", "취소주문제외"처럼 바꾸면 나중에 오류를 찾기가 훨씬 쉽습니다.

현업 데이터 청소의 기본 기술

이제 실무에서 가장 자주 만나는 지저분한 데이터를 Power Query로 정리해 보겠습니다. 여기서 배우는 작업들은 화려하지 않지만, 거의 모든 자동화의 바탕이 됩니다.

불필요한 행과 열 제거

시스템에서 내려받은 파일에는 분석에 필요 없는 열이 많습니다. 배송 상세 주소, 내부 메모, 임시 코드, 빈 열처럼 보고서에 쓰지 않는 정보가 섞여 있을 수 있습니다. 이때 Power Query에서 열 머리글을 선택하고 열 제거를 실행하면 됩니다.

반대로 필요한 열만 선택하고 다른 열 제거를 사용할 수도 있습니다. 실무에서는 이 방식이 더 안전할 때가 많습니다. 원본 파일에 새 열이 추가되어도 내가 지정한 핵심 열만 남길 수 있기 때문입니다.

행 제거도 자주 씁니다. 파일 위쪽에 보고서 제목, 출력일, 담당자 이름이 있고 실제 머리글이 5행부터 시작하는 경우가 있습니다. 이때 상위 행 제거를 사용합니다. 중간중간 빈 행이 섞여 있다면 빈 행 제거를 적용합니다. 단, 데이터 안에서 일부 열만 비어 있는 정상 행까지 지우지 않도록 미리 보기를 확인해야 합니다.

데이터 형식 지정

Power Query에서 데이터 형식은 매우 중요합니다. 날짜는 날짜로, 금액은 숫자로, 코드값은 텍스트로 지정해야 합니다. 데이터 형식이 잘못되면 피벗테이블에서 합계가 안 나오거나, 날짜별 그룹화가 되지 않거나, 앞자리 0이 사라질 수 있습니다.

특히 주의할 열은 상품 코드, 사원번호, 거래처 코드입니다. 이 값들은 숫자처럼 보이지만 계산 대상이 아니라 식별자입니다. 00123이라는 상품 코드를 숫자로 바꾸면 123이 되어 다른 코드가 될 수 있습니다. 이런 열은 텍스트로 지정합니다.

날짜는 지역 설정에 따라 해석이 달라질 수 있습니다. 01/05/2026이 1월 5일인지 5월 1일인지 파일을 만든 시스템과 사용자 환경에 따라 달라질 수 있습니다. 날짜 오류가 자주 생긴다면 로캘을 사용하여 형식 변경을 검토합니다. 로캘은 국가나 지역의 날짜·숫자 표기 규칙을 함께 지정하는 기능입니다.

공백 제거와 대소문자 통일

엑셀에서 가장 얄미운 오류 중 하나는 눈에 보이지 않는 공백입니다. 서울, 서울 , 서울은 사람 눈에는 거의 같아 보이지만 엑셀에는 다른 값입니다. 이 상태로 피벗테이블을 만들면 서울이 여러 항목으로 나뉘고, 찾기 함수나 병합도 제대로 작동하지 않습니다.

Power Query에서는 텍스트 열을 선택한 뒤 변환 → 서식 → 잘라내기를 사용합니다. 여기서 잘라내기는 셀 앞뒤의 불필요한 공백을 제거하는 기능입니다. 보이지 않는 특수 문자까지 정리하고 싶다면 정리 기능을 함께 사용할 수 있습니다. 여러 Power Query 데이터 정리 자료에서도 Trim, Clean, 대소문자 통일은 병합 실패와 피벗 항목 분리를 줄이는 기본 정리 작업으로 자주 소개됩니다.⁵

고객명, 지역명, 부서명처럼 표기 방식이 들쭉날쭉한 열은 대소문자 통일도 중요합니다. 영문 데이터라면 모두 대문자, 모두 소문자, 각 단어 첫 글자 대문자 중 하나로 맞춥니다. 예를 들어 SEOUL, Seoul, seoul이 섞인 데이터를 하나로 맞추면 요약과 병합이 훨씬 안정됩니다.

열 분할과 병합

한 열에 여러 정보가 붙어 있는 데이터도 흔합니다. 예를 들어 홍길동/영업1팀, 서울-강남, A001_노트북_온라인처럼 하나의 셀 안에 여러 정보가 구분 기호로 들어 있습니다. 분석하려면 이 값을 나누어야 합니다.

Power Query에서는 해당 열을 선택하고 열 분할 → 구분 기호 기준을 사용합니다. 슬래시, 쉼표, 하이픈, 공백 같은 구분 기호를 기준으로 열을 나눌 수 있습니다. 이름과 성, 지역과 세부지역, 상품 코드와 상품명처럼 분리할 수 있습니다.

반대로 여러 열을 하나로 합칠 수도 있습니다. 예를 들어 시도 열과 시군구 열을 합쳐 지역 열을 만들 수 있습니다. 열을 병합할 때는 구분 기호를 넣을지 결정해야 합니다. 서울강남구보다 서울 강남구처럼 공백을 넣는 편이 읽기 좋을 때가 많습니다.

빈 값, 오류, 중복 처리

빈 값은 상황에 따라 다르게 처리해야 합니다. 지점명처럼 위 행과 같은 값이 반복되어 생략된 경우에는 채우기 → 아래로를 사용할 수 있습니다. 반면 매출금액이 빈 값이라면 0으로 봐도 되는지, 누락으로 보고 제외해야 하는지 업무 기준이 필요합니다.

오류 처리도 마찬가지입니다. 금액 열에 오류가 생겼다고 무조건 0으로 바꾸면 실제 문제를 숨길 수 있습니다. 분석용 임시 보고서라면 0으로 대체할 수 있지만, 정산이나 회계 자료라면 오류 행만 따로 분리해 확인하는 것이 안전합니다.

중복 제거는 강력하지만 조심해야 합니다. Power Query의 중복 제거는 선택한 열을 기준으로 같은 값을 중복으로 판단합니다. 주문번호가 같은 행을 하나만 남길 것인지, 주문번호와 상품코드가 모두 같은 경우만 중복으로 볼 것인지 먼저 정해야 합니다. 중복 제거 전에 날짜나 우선순위 열로 정렬하면 어떤 행을 남길지도 어느 정도 통제할 수 있습니다.

추가와 병합: 여러 표를 하나의 분석 데이터로 만들기

Power Query를 쓰다 보면 "합친다"는 말이 두 가지 의미로 쓰입니다. 하나는 아래로 이어 붙이는 추가(Append)이고, 다른 하나는 옆으로 연결하는 병합(Merge)입니다. 두 기능의 차이를 이해하면 찾기 함수와 복사·붙여넣기 작업이 크게 줄어듭니다.

추가(Append): 같은 구조의 데이터를 아래로 붙입니다

추가는 같은 열 구조를 가진 데이터를 세로로 이어 붙이는 기능입니다. 1월 매출, 2월 매출, 3월 매출처럼 열 구성이 같은 파일을 하나의 매출 원장으로 만들 때 사용합니다.

예를 들어 세 파일이 모두 다음 열을 가진다고 해 보겠습니다.

이 경우 Power Query에서 세 쿼리를 추가하면 하나의 긴 표가 됩니다. 여기서 중요한 것은 열 이름이 같아야 한다는 점입니다. 어떤 파일은 매출금액, 어떤 파일은 금액으로 되어 있으면 Power Query는 서로 다른 열로 판단할 수 있습니다. 그래서 원본 양식을 통일하거나, Power Query 초반 단계에서 열 이름을 표준화해야 합니다.

폴더 가져오기는 이 추가 작업을 자동화하는 대표적인 방법입니다. 같은 폴더에 같은 구조의 파일을 넣어 두고 결합하면, 새 파일이 들어올 때마다 전체 원장이 자동으로 길어집니다.

병합(Merge): 기준 열로 다른 표의 정보를 붙입니다

병합은 기준 열을 이용해 다른 표의 열을 가져오는 기능입니다. 함수로 비유하면 XLOOKUP이나 VLOOKUP에 가깝습니다. 하지만 Power Query 병합은 결과가 새로 고침 때마다 다시 만들어지므로, 원본 데이터가 추가되어도 수식 범위를 고칠 필요가 없습니다.

예를 들어 매출 원장에는 상품코드만 있고, 상품 기준표에는 상품코드와 상품명, 카테고리, 원가가 있다고 해 보겠습니다. 상품코드를 기준으로 두 표를 병합하면 매출 원장에 상품명과 카테고리를 붙일 수 있습니다.

병합할 때는 조인 종류를 선택합니다. 처음에는 왼쪽 외부 조인만 이해해도 충분합니다. 왼쪽 외부 조인은 첫 번째 표의 모든 행을 유지하고, 두 번째 표에서 일치하는 값이 있으면 정보를 붙이는 방식입니다. 매출 원장을 기준으로 상품 기준표를 붙일 때 일반적으로 이 방식을 많이 사용합니다. Microsoft Power Query 문서에서도 병합은 기존 쿼리들을 공통 열을 기준으로 결합하는 작업으로 설명됩니다.⁶

병합 후에는 새로 생긴 열 오른쪽의 확장 아이콘을 눌러 가져올 열을 선택합니다. 이때 필요 없는 열까지 모두 가져오지 말고, 보고서에 필요한 열만 선택하는 것이 좋습니다. 열이 많아질수록 파일이 무거워지고 관리가 어려워집니다.

추가와 병합을 함께 쓰는 실무 흐름

실무에서는 추가와 병합을 함께 쓰는 경우가 많습니다. 월별 매출 파일을 폴더에서 모두 추가해 하나의 원장을 만들고, 상품 기준표와 병합해 상품명과 카테고리를 붙인 다음, 지점 기준표와 다시 병합해 지역 담당자를 붙입니다. 마지막으로 이 정리된 데이터를 피벗테이블이나 대시보드의 원본으로 사용합니다.

흐름을 말로 쓰면 복잡해 보이지만 구조는 단순합니다.

1. 폴더의 월별 파일을 가져와 하나로 추가합니다.

2. 매출 원장의 공백, 형식, 오류, 중복을 정리합니다.

3. 상품 기준표, 지점 기준표 같은 참조 표와 병합합니다.

4. 필요한 열만 남기고 보고서용 결과를 로드합니다.

5. 새 파일이 들어오면 새로 고침으로 정리 결과와 보고서를 갱신합니다.

이 흐름이 만들어지면 보고서 업무가 완전히 달라집니다. 예전에는 데이터 정리에 대부분의 시간을 쓰고 마지막에 급하게 분석했다면, 이제는 정리 과정은 새로 고침에 맡기고 결과 해석에 시간을 쓸 수 있습니다.

새로 고침으로 보고서까지 갱신하기

Power Query 자동화의 완성은 새로 고침입니다. 데이터를 가져오고 정리하는 쿼리를 만들었다면, 이 결과를 엑셀 시트나 피벗테이블의 원본으로 로드할 수 있습니다. 이후 원본 파일이 바뀌면 데이터 → 모두 새로 고침을 실행합니다.

여기서 중요한 것은 보고서의 흐름을 한 방향으로 만드는 것입니다.

원본 파일은 Power Query로 들어오고, Power Query 결과는 정리된 표로 로드되며, 피벗테이블과 차트는 그 정리된 표를 원본으로 삼습니다. 이렇게 연결하면 새 파일이 들어왔을 때 Power Query가 먼저 갱신되고, 피벗테이블과 차트가 이어서 갱신됩니다.

자동화 파일을 설계할 때는 시트를 역할별로 나누면 관리하기 쉽습니다. 예를 들어 원본안내, 정리결과, 기준표, 피벗, 대시보드처럼 구분할 수 있습니다. 원본 데이터 자체를 파일 안에 모두 붙여 넣지 않고 외부 폴더나 파일과 연결할 수도 있습니다.

다만 새로 고침이 항상 마법처럼 되는 것은 아닙니다. 원본 파일 위치가 바뀌거나, 파일 이름 규칙이 깨지거나, 열 이름이 달라지면 오류가 날 수 있습니다. 그래서 자동화는 "한 번 만들면 끝"이 아니라 "규칙을 정하고 지키는 시스템"에 가깝습니다.

쿼리 이름, 단계 이름, 로드 위치 관리하기

Power Query를 처음 배울 때는 기능을 실행하는 데 집중합니다. 하지만 실무에서 오래 쓰는 파일은 이름 관리가 더 중요합니다. 이름이 엉망이면 만든 사람도 한 달 뒤에 이해하기 어렵고, 다른 사람은 거의 손댈 수 없습니다.

쿼리 이름은 내용과 역할이 드러나게 짓습니다. 표1, 쿼리1, Sheet1 같은 이름은 피하세요. 대신 원본_월별매출_폴더, 기준_상품마스터, 정리_매출원장, 보고서용_카테고리별매출처럼 이름만 봐도 흐름이 보이게 합니다.

단계 이름도 중요한 단계만 바꿔 두면 좋습니다. 모든 단계를 장황하게 바꿀 필요는 없지만, 업무 기준이 들어간 단계는 반드시 의미를 남깁니다. 예를 들어 필터링된 행보다 취소주문제외, 제거된 열보다 개인정보열제거가 훨씬 낫습니다.

로드 위치도 관리해야 합니다. 모든 쿼리를 시트에 표로 불러오면 파일이 복잡해집니다. 중간 과정으로만 쓰는 쿼리는 연결만 만들기로 두고, 최종 결과만 시트나 데이터 모델에 로드하는 편이 깔끔합니다.

Power Query를 잘 쓰는 사람은 클릭을 빨리하는 사람이 아니라, 나중에 봐도 이해되는 흐름을 만드는 사람입니다.

오류가 났을 때 추적하는 법

자동화 파일에서 오류가 나면 당황하기 쉽습니다. 하지만 Power Query 오류는 대부분 원인이 있습니다. 차분히 적용된 단계를 위에서 아래로 확인하면 어느 순간부터 깨졌는지 찾을 수 있습니다.

가장 흔한 오류는 열 이름 변경입니다. 예를 들어 원본 파일에서 주문일 열이 주문 날짜로 바뀌면, 주문일을 날짜로 바꾸는 단계에서 오류가 납니다. 이때 해당 단계로 이동하면 미리 보기 영역에 어떤 열을 찾지 못했는지 표시됩니다.

두 번째는 데이터 형식 오류입니다. 금액 열에 숫자가 있어야 하는데 -, 확인중, N/A 같은 텍스트가 들어 있으면 숫자 변환 단계에서 오류가 생길 수 있습니다. 이런 경우 오류를 제거할지, 다른 값으로 대체할지, 오류 행을 별도로 보관할지 결정해야 합니다.

세 번째는 원본 경로 오류입니다. 폴더 위치가 바뀌거나, 파일명이 달라지거나, 네트워크 드라이브 연결이 끊기면 Power Query가 원본을 찾지 못합니다. 공유 파일에서는 특히 이 문제가 자주 발생합니다.

오류를 추적할 때는 다음 순서가 좋습니다.

1. 쿼리 편집기에서 오류가 나는 쿼리를 엽니다.

2. 적용된 단계를 위에서 아래로 하나씩 클릭합니다.

3. 어느 단계부터 오류가 나타나는지 확인합니다.

4. 열 이름, 데이터 형식, 필터 조건, 원본 경로를 점검합니다.

5. 수정 후 닫기 및 로드로 결과를 확인합니다.

오류를 완전히 피할 수는 없습니다. 하지만 단계 이름과 쿼리 이름을 잘 관리해 두면 오류를 찾는 시간이 크게 줄어듭니다. 이것이 자동화 문서의 유지보수 능력입니다.

실습: 월별 매출 파일 자동 정리 흐름 만들기

이제 하나의 실습 시나리오로 Power Query 자동화 흐름을 정리해 보겠습니다. 상황은 다음과 같습니다.

매월 각 지점에서 같은 양식의 매출 CSV 파일을 보내옵니다. 파일에는 주문일, 지점명, 상품코드, 수량, 매출금액, 고객명, 배송메모가 있습니다. 우리는 모든 파일을 하나로 합치고, 상품 기준표에서 상품명과 카테고리를 붙여 월별·카테고리별 매출 보고서를 만들고 싶습니다.

먼저 폴더를 하나 만듭니다. 예를 들어 C:Excel_AutomationSales라는 폴더에 월별 CSV 파일을 넣습니다. 파일 구조는 같아야 합니다. 열 이름도 가능하면 동일하게 맞춥니다.

엑셀에서 새 통합 문서를 열고 데이터 → 데이터 가져오기 → 파일에서 → 폴더에서를 선택합니다. 폴더를 지정한 뒤 결합 및 변환을 선택하면 Power Query 편집기가 열립니다. 여기서 불필요한 배송메모 열을 제거하고, 주문일은 날짜 형식으로, 수량과 매출금액은 숫자 형식으로 바꿉니다. 지점명과 고객명에는 공백 제거를 적용합니다.

그다음 상품 기준표를 가져옵니다. 기준표에는 상품코드, 상품명, 카테고리가 들어 있다고 하겠습니다. 이 표도 Power Query로 불러온 뒤 상품코드 열을 텍스트 형식으로 지정합니다. 매출 원장 쿼리와 상품 기준표 쿼리의 상품코드 형식이 같아야 병합이 안정적으로 됩니다.

이제 매출 원장 쿼리에서 쿼리 병합을 선택하고, 상품코드를 기준으로 상품 기준표를 연결합니다. 조인 종류는 왼쪽 외부 조인을 선택합니다. 병합 결과에서 상품명과 카테고리만 확장해 가져옵니다.

마지막으로 정리된 결과를 정리_매출원장이라는 이름으로 바꾸고, 엑셀 시트에 표로 로드합니다. 이 표를 원본으로 피벗테이블을 만듭니다. 행에는 카테고리, 열에는 월, 값에는 매출금액 합계를 배치하면 월별 카테고리 매출 보고서가 완성됩니다.

다음 달에는 새 CSV 파일을 같은 폴더에 넣고 모두 새로 고침을 누릅니다. Power Query가 새 파일을 포함해 다시 결합하고, 정리 단계를 적용하고, 상품 기준표와 병합합니다. 피벗테이블까지 새로 고치면 보고서가 갱신됩니다.

이것이 Power Query의 실무적 가치입니다. 한 번 고생해서 흐름을 만들면, 그다음부터는 반복 작업이 버튼 하나로 줄어듭니다.

매크로 기록기: 클릭과 서식을 기억시키는 도구

Power Query가 데이터 정리에 강하다면, 매크로는 엑셀 화면에서 반복하는 동작을 자동화하는 데 유용합니다. 매크로는 엑셀에서 사용자가 수행한 작업을 기록해 두었다가 나중에 다시 실행하는 기능입니다. 기록된 매크로는 VBA라는 언어로 저장됩니다.

예를 들어 매주 보고서 시트에서 같은 서식을 적용한다고 해 보겠습니다. 제목 행을 굵게 만들고, 열 너비를 자동 맞춤하고, 테두리를 넣고, 인쇄 방향을 가로로 바꾸고, PDF로 저장합니다. 이런 작업은 Power Query보다 매크로가 더 어울립니다. 매크로는 셀 선택, 서식 지정, 시트 이동, 파일 저장 같은 화면 조작 반복에 강하기 때문입니다.

매크로 기록은 보통 다음 흐름으로 시작합니다.

1. 개발 도구 탭을 표시합니다.

2. 매크로 기록을 클릭합니다.

3. 매크로 이름과 바로 가기 키를 지정합니다.

4. 자동화하고 싶은 작업을 실제로 수행합니다.

5. 기록 중지를 누릅니다.

6. 매크로 목록에서 실행하거나 단추에 연결합니다.

Developer 탭이 보이지 않는다면 엑셀 옵션에서 리본 메뉴 사용자 지정을 열고 개발 도구를 체크하면 됩니다. 여러 매크로 입문 자료에서도 초보자는 먼저 기록기로 단순 반복 작업을 저장한 뒤, 필요할 때 VBA 편집기에서 조금씩 수정하는 방식으로 시작하라고 권합니다.⁷

매크로를 만들 때는 너무 많은 일을 한 번에 기록하지 않는 것이 좋습니다. 기록기는 내가 클릭한 불필요한 이동까지 그대로 저장할 수 있습니다. 처음에는 "보고서 서식 적용", "피벗테이블 새로 고침", "PDF 저장"처럼 작은 단위로 나누어 만드는 편이 안전합니다.

매크로를 단추에 연결하면 더 편합니다. 삽입에서 도형을 하나 만들고, 오른쪽 클릭 후 매크로 지정을 선택하면 해당 도형을 버튼처럼 사용할 수 있습니다. 보고서 시트 위에 보고서 서식 적용, 전체 새로 고침, PDF 저장 같은 버튼을 만들어 두면 다른 사용자도 쉽게 실행할 수 있습니다.

Power Query와 매크로의 역할 차이

Power Query와 매크로는 둘 다 자동화 도구지만 역할이 다릅니다. 이 차이를 이해해야 적절한 도구를 고를 수 있습니다.

Power Query는 데이터를 가져오고 정리하고 결합하는 데 강합니다. 여러 파일을 합치고, 열을 나누고, 오류를 처리하고, 기준표와 병합하고, 새로 고침으로 결과를 갱신하는 일에 적합합니다.

매크로는 엑셀 화면에서 반복되는 조작을 실행하는 데 강합니다. 서식 지정, 시트 보호, 인쇄 설정, 파일 저장, 버튼 실행, 여러 시트 순회 같은 작업에 유용합니다. 여러 자동화 비교 자료에서도 Power Query는 데이터 변환, VBA 매크로는 데스크톱 엑셀 안의 반복 조작과 파일 처리에 적합하다고 구분합니다.⁸

처음 자동화를 시작하는 사람은 자꾸 한 도구로 모든 것을 해결하려고 합니다. 하지만 실무에서는 둘을 나누어 쓰는 편이 좋습니다. 데이터 정리와 결합은 Power Query에 맡기고, 최종 보고서의 버튼 실행과 서식 마무리는 매크로에 맡깁니다. 이렇게 역할을 나누면 파일이 단순해지고 오류도 줄어듭니다.

자동화 파일을 공유할 때 확인할 것

자동화 파일은 혼자 쓸 때보다 공유할 때 더 신중해야 합니다. 내 컴퓨터에서는 잘 되던 파일이 팀원 컴퓨터에서는 새로 고침이 안 되거나, 매크로가 차단되거나, 원본 경로를 찾지 못할 수 있습니다.

먼저 파일 형식을 이해해야 합니다. 일반 엑셀 통합 문서인 .xlsx는 VBA 매크로를 저장할 수 없습니다. 매크로를 포함하려면 .xlsm 형식으로 저장해야 합니다. VBA 자동화 자료에서도 매크로가 들어 있는 통합 문서는 매크로 사용 통합 문서 형식으로 저장해야 한다고 안내합니다.⁹

Power Query만 사용하는 파일이라면 보통 .xlsx로 저장할 수 있습니다. 그러나 매크로 버튼, 기록된 매크로, VBA 코드가 들어가면 .xlsm이 필요합니다. 확장자가 바뀌면 사용자들이 "위험한 파일인가?"라고 느낄 수 있으므로, 파일 안이나 별도 안내문에 매크로의 용도를 적어 두는 것이 좋습니다.

매크로 보안 경고도 중요합니다. 엑셀은 보안상 인터넷에서 받은 매크로 파일이나 신뢰되지 않은 위치의 파일을 차단할 수 있습니다. 사용자는 출처가 확실한 파일에서만 콘텐츠 사용을 눌러야 합니다. 조직에서는 신뢰할 수 있는 위치를 지정하거나, IT 정책에 따라 서명된 매크로만 허용할 수 있습니다.

Power Query 공유에서는 외부 데이터 연결 권한을 확인해야 합니다. 원본이 내 로컬 PC의 C:Users내이름Downloads 폴더에 있으면 다른 사람은 새로 고침할 수 없습니다. 공유 폴더, SharePoint, OneDrive, 네트워크 드라이브처럼 팀원이 접근할 수 있는 위치를 사용해야 합니다. 단, 클라우드 경로는 동기화 상태와 권한 설정도 확인해야 합니다.

자동화 파일을 공유하기 전에는 다음을 점검하세요.

1. 파일 형식이 적절한가? 매크로가 있으면 .xlsm인가?

2. 원본 파일이나 폴더 경로를 다른 사용자도 접근할 수 있는가?

3. Power Query 새로 고침에 필요한 권한이나 로그인 정보가 있는가?

4. 매크로 보안 경고가 뜰 때 사용자에게 안내할 설명이 있는가?

5. 쿼리 이름, 단계 이름, 버튼 이름이 이해하기 쉬운가?

6. 원본 양식이 바뀌면 어디를 수정해야 하는지 적어 두었는가?

자동화는 만든 사람의 컴퓨터에서 한 번 성공하는 것으로 끝나지 않습니다. 다른 사람이 안전하게 실행할 수 있어야 진짜 업무 자동화입니다.

자동화 설계의 좋은 습관

Power Query와 매크로를 배웠다고 해서 모든 파일을 복잡하게 만들 필요는 없습니다. 자동화는 반복이 충분히 많고, 규칙이 안정적이며, 실수 비용이 큰 업무에 먼저 적용하는 것이 좋습니다.

예를 들어 한 번만 처리할 20행짜리 목록이라면 손으로 정리하는 편이 빠를 수 있습니다. 그러나 매주 들어오는 5만 행의 주문 데이터, 매월 쌓이는 지점별 파일, 매일 갱신하는 대시보드라면 자동화의 효과가 큽니다.

좋은 자동화 파일에는 몇 가지 공통점이 있습니다.

첫째, 원본과 결과가 분리되어 있습니다. 원본은 되도록 건드리지 않고, Power Query가 정리 결과를 만듭니다. 둘째, 기준표가 명확합니다. 상품, 지점, 부서, 담당자처럼 반복해서 참조하는 정보는 별도 기준표로 관리합니다. 셋째, 새로 고침 흐름이 단순합니다. 사용자가 어떤 순서로 무엇을 눌러야 하는지 한눈에 알 수 있습니다.

넷째, 실패했을 때 확인할 곳이 있습니다. 오류 행을 별도로 로드하거나, 병합되지 않은 상품코드 목록을 검증용 시트로 만들면 좋습니다. 다섯째, 설명이 있습니다. 자동화 파일의 첫 시트에 원본 위치, 새로 고침 방법, 주의 사항을 적어 두면 인수인계가 쉬워집니다.

자동화는 멋진 기술을 보여 주는 일이 아닙니다. 다음 달의 나, 그리고 내 파일을 이어받을 동료가 덜 고생하게 만드는 일입니다.

작은 자동화부터 시작하세요

Power Query와 매크로를 한꺼번에 완벽히 익히려고 하면 부담스럽습니다. 처음에는 내 업무에서 가장 지겨운 작업 하나를 고르세요.

매번 CSV에서 필요 없는 열을 지우고 있다면 Power Query로 가져와 열 제거 단계를 기록합니다. 매번 고객명 공백 때문에 피벗 항목이 갈라진다면 공백 제거와 대소문자 통일을 적용합니다. 매번 여러 월별 파일을 복사해서 붙여넣고 있다면 폴더 가져오기를 시도합니다. 매번 보고서 서식을 손으로 맞추고 있다면 매크로 기록기로 서식 적용을 저장합니다.

처음 자동화는 작아야 합니다. 작게 성공해야 다음 자동화를 만들 힘이 생깁니다. 그리고 자동화가 하나둘 쌓이면 엑셀을 대하는 태도가 바뀝니다. 예전에는 "이번 달도 또 해야 하네"라고 생각하던 일이 "이번에는 새로 고침하면 되겠네"로 바뀝니다.

엑셀의 실무 능력은 데이터를 예쁘게 꾸미는 데서 끝나지 않습니다. 반복되는 정리 과정을 시스템으로 만들고, 보고서가 안정적으로 갱신되게 하고, 다른 사람도 안전하게 실행할 수 있게 만드는 데까지 나아갑니다. Power Query와 매크로는 그 출발점입니다.

다음 장에서는 여기서 한 걸음 더 나아가 AI를 엑셀 학습과 실무 프로젝트에 활용하는 방법을 다룹니다. 지금까지 배운 표, 함수, 피벗테이블, 차트, 대시보드, Power Query 자동화가 하나의 종합 프로젝트로 연결될 것입니다.

참고

¹Use Power Query to transform data

²Excel Clean Up with Power Query: Say Goodbye to Messy Data Forever

³Automating Routine Excel Tasks Using Power Query

⁴Automate Report Delivery with Excel's Power Query

⁵7 Power Query Tricks to Automate Your Data Cleaning Forever

⁶Use Power Query to transform data

⁷How to Automate Repetitive Tasks in Excel Using Macros & VBA

⁸The top 5 best ways to automate your Microsoft Excel spreadsheets

⁹Excel VBA Automation: Data Cleaning, Reporting & File Handling