엑셀의 혁명 파워쿼리(1) – 폴더 내 파일 100개 한 번에 취합하기

(Mode 2에서 작성 예정)

파일 100개를 언제 다 열어서 복사하시겠습니까?

안녕하세요. 여러분의 야근을 칼퇴로 바꿔드리는 엑셀 팩토리 공장장 직장인 엑셀 꿀팁 저장소입니다.

지난 포스팅에서는 매크로 기록을 이용해 반복되는 서식 작업을 버튼 하나로 해결하는 법을 배웠습니다. 서식은 해결되었는데, 데이터 취합 업무는 여전히 막막하신가요?

월말이 되면 각 지점에서 보내온 매출 보고서 파일 30개가 바탕화면에 쌓입니다. ‘강남점.xlsx’, ‘홍대점.xlsx’, ‘부산점.xlsx’…
이걸 하나로 합치기 위해 여러분은 지금까지 이렇게 하셨을 겁니다.

1. 파일을 연다.
2. 제목을 뺀 나머지 데이터를 드래그해서 복사(Ctrl+C)한다.
3. 취합 파일에 붙여넣기(Ctrl+V)한다.
4. 파일을 닫고 다음 파일을 연다. (무한 반복)

그러다 실수로 데이터를 빼먹거나 중복해서 붙여넣기도 하죠. 이제 이 지루한 ‘복붙 지옥’에서 탈출할 시간입니다. 엑셀 파워쿼리(Power Query)를 사용하면, 폴더 안에 파일이 10개든 1,000개든 단 1분 만에 하나로 합칠 수 있습니다.

파워쿼리(Power Query)란? 엑셀 속의 ‘데이터 용광로’

파워쿼리는 엑셀 2010/2013 버전에서는 별도로 설치해야 했지만, 엑셀 2016 버전부터는 기본 기능으로 내장되었습니다. (데이터 탭에 있습니다.)

이 녀석의 역할은 ETL입니다.

• Extract (추출): 외부 데이터(파일, 웹, DB)를 가져와서,
• Transform (변환): 사용자가 원하는 형태(청소, 병합, 계산)로 가공한 뒤,
• Load (로드): 엑셀 시트에 표 형태로 뿌려주는 도구입니다.

가장 큰 장점은 “과정을 기억한다”는 점입니다. 한 번만 취합 규칙을 만들어두면, 다음 달에 새로운 파일이 들어와도 ‘새로 고침’ 버튼 하나면 모든 과정이 자동으로 수행됩니다.

준비물: 똑같은 양식의 파일들이 담긴 ‘폴더’

파워쿼리로 파일을 합치려면 한 가지 전제 조건이 필요합니다. “합치려는 파일들의 머리글(헤더) 구조가 같아야 한다”는 것입니다.

예를 들어 모든 파일이 1행에 [날짜, 지점, 품목, 금액] 순서로 되어 있어야 합니다. 어떤 파일은 [날짜]가 먼저 오고, 어떤 파일은 [지점]이 먼저 온다면 파워쿼리가 헷갈려 합니다. (물론 이것도 해결할 수 있지만, 기초 단계에서는 양식이 같은 파일을 준비해 주세요.)

실습을 위해 바탕화면에 [매출취합]이라는 폴더를 만들고, 그 안에 1월~3월 엑셀 파일을 넣어두고 시작합시다.

실전 1단계: 폴더 통째로 데이터 가져오기

이제 엑셀을 켜고 빈 시트에서 시작합니다.

1. 상단 메뉴 [데이터] 탭을 클릭합니다.
2. 가장 왼쪽에 있는 [데이터 가져오기] > [파일에서] > [폴더에서]를 선택합니다.
3. 탐색기 창이 뜨면 아까 만든 [매출취합] 폴더를 선택하고 [열기]를 누릅니다.

여기까지 하면 엑셀이 해당 폴더 안에 있는 파일 리스트(이름, 확장자, 만든 날짜 등)를 보여주는 미리 보기 창을 띄웁니다.

파워쿼리 지원 데이터 원본에 대한 자세한 정보는 마이크로소프트 파워쿼리 공식 소개를 참고하세요.

실전 2단계: 마법의 버튼 ‘결합 및 변환’

미리 보기 창 하단에 버튼이 여러 개 있습니다. 여기서 선택을 잘해야 합니다.

• 로드(Load): 이걸 누르면 데이터를 합치는 게 아니라, 그냥 ‘파일 목록’만 엑셀로 가져옵니다. (실패!)
• 데이터 변환(Transform Data): 파일 목록을 먼저 편집하고 싶을 때 씁니다.
• 결합(Combine): 우리가 원하는 기능입니다. 파일을 하나로 합쳐줍니다.

[결합] 버튼 옆의 화살표를 누르고 [결합 및 데이터 변환]을 클릭하세요.
그러면 [파일 병합] 창이 뜹니다. 여기서 ‘매개 변수 파일(샘플 파일)’로 [첫 번째 파일]이 선택된 상태에서 확인을 누릅니다.
(※ 이 과정은 “첫 번째 파일의 구조를 기준으로 나머지 파일들도 다 똑같이 생겼다고 가정하고 합치겠다”는 뜻입니다.)

실전 3단계: 불필요한 데이터 다듬고 엑셀로 로드하기

확인을 누르면 엑셀 창이 사라지고, 회색 배경의 새로운 창이 뜹니다. 바로 [파워쿼리 편집기]입니다.

화면 가운데를 보면 데이터가 이미 합쳐져 있을 겁니다. [Source.Name]이라는 열이 자동으로 생겼는데, 여기에 파일명(1월.xlsx, 2월.xlsx…)이 적혀 있어서 데이터의 출처를 알 수 있습니다.

간단한 청소 작업

만약 합쳐진 데이터에 불필요한 열이 있다면, 열 제목을 클릭하고 [Del] 키를 눌러 삭제하세요. 파워쿼리 편집기에서는 원본 데이터(엑셀 파일)는 건드리지 않고, 오직 ‘미리 보기’ 상태에서만 작업을 수행하므로 안심하고 지우셔도 됩니다.

작업이 끝났다면 좌측 상단의 [닫기 및 로드] 버튼을 누릅니다. 파워쿼리 창이 닫히고, 엑셀 시트에 초록색 표 형태로 깔끔하게 취합된 데이터가 쏟아져 들어옵니다.

혁명의 순간: 파일 추가하고 ‘새로 고침’ 누르기

여기까지는 그냥 “복사 붙여넣기를 대신해 줬네?” 정도입니다. 진짜 혁명은 이제부터입니다.

상황 발생: 4월이 되어 ‘4월_이태원점.xlsx’ 파일이 새로 생겼습니다. 예전 같으면 파일을 열고, 복사하고, 맨 아래에 붙여넣었겠죠?

파워쿼리 방식:

1. ‘4월_이태원점.xlsx’ 파일을 아까 그 [매출취합] 폴더에 넣습니다.
2. 취합된 엑셀 표 위에서 마우스 우클릭을 합니다.
3. [새로 고침(R)]을 누릅니다.

끝입니다. 엑셀이 폴더를 다시 스캔해서, 새로운 4월 파일을 감지하고, 기존 데이터 아래에 자동으로 이어 붙입니다. 파일이 100개가 추가되어도 마찬가지입니다. 단 1초 만에 업데이트가 끝납니다.

주의사항: 파워쿼리 취합 시 에러가 나는 이유

너무나 편리하지만 가끔 에러가 날 때가 있습니다. 대표적인 원인 2가지만 기억하세요.

• 열 이름 불일치: 1월 파일엔 “매출액”이라고 되어있는데, 2월 파일엔 “금액”이라고 되어있으면 합치지 못하고 null 값으로 비워둡니다. 원본 파일들의 제목을 통일해야 합니다.
• 파일 잠김: 원본 엑셀 파일을 누군가 열어놓은 상태라면(특히 공유 폴더 등에서) 파워쿼리가 접근하지 못해 에러가 날 수 있습니다. 파일을 모두 닫고 새로 고침 하세요.

마무리하며

오늘은 엑셀 파워쿼리를 이용해 폴더 내의 모든 파일을 한 방에 취합하는 기술을 익혔습니다. 이제 단순 취합 업무는 파워쿼리에게 맡기세요. 여러분은 데이터가 모이면 ‘새로 고침’ 버튼만 누르면 됩니다.

그런데 이렇게 합친 데이터가 항상 예쁜 표 모양일까요? 가끔은 가로로 길게 늘어진 크로스탭 형태의 표를 세로로 길게 펴야 할 때가 있습니다. 다음 시간에는 파워쿼리의 또 다른 필살기, 열 피벗 해제(Unpivot)로 데이터 구조를 뜯어고치는 법에 대해 알아보겠습니다. 엑셀 꿀팁 저장소였습니다!