요즘 회사에서 데이터 다루다 보면 엑셀 파일이 수십 개, 아니 수백 개씩 쌓이는 일 흔하잖아요. 월별 매출 보고서나 지역별 판매 데이터 같은 거요. 이거 하나하나 열어서 복사하고 붙여넣기(복붙) 하고 있으면 진짜 현타 오거든요. 저도 예전엔 ‘노가다’가 답인 줄 알았는데, 파워쿼리라는 기능을 알고 나서부터는 업무 시간이 확 줄었어요. 오늘은 엑셀 파워쿼리로 대용량 데이터 합치기, 이거 하나로 칼퇴하는 방법 풀어볼게요.
복붙은 그만, 파워쿼리가 답인 이유
솔직히 말해서 아직도 VLOOKUP이나 단순 복사로 데이터를 합치고 계신다면, 오늘 이 글이 진짜 구세주가 될 거예요. 엑셀 좀 다룬다는 분들도 파워쿼리는 낯설어하는 경우가 꽤 있더라고요. 근데 이게 한번 써보면 절대 예전으로 못 돌아가요.
가장 큰 장점은 ‘자동화’예요. 매달 똑같은 양식의 파일이 들어오잖아요? 폴더에 파일만 넣어두고 ‘새로 고침’ 버튼 하나만 누르면 끝나요. 진짜라니까요. 게다가 엑셀 시트 행 개수 제한이 100만 행 정도인데, 파워쿼리는 그 이상의 대용량 데이터도 가볍게 처리해요. 엑셀이 버벅거려서 강제 종료되는 악몽에서 벗어날 수 있다는 거죠.
폴더 내 모든 파일 한방에 합치는 법
거창한 코딩이나 매크로(VBA) 같은 거 몰라도 돼요. 마우스 클릭 몇 번이면 되거든요. 제가 자주 쓰는 ‘폴더에서 가져오기’ 기능을 기준으로 설명해드릴게요.
1. 데이터 준비하기
가장 먼저 해야 할 건 합치고 싶은 엑셀 파일들을 하나의 폴더에 몰아넣는 거예요. 이때 파일들의 열 제목(헤더)은 같아야 작업이 수월해요. 예를 들어 A열이 ‘날짜’, B열이 ‘품명’이라면 모든 파일이 이 형식을 따르는 게 좋죠.
2. 데이터 가져오기
엑셀 상단 메뉴에서 [데이터] 탭을 클릭하세요. 거기 보면 [데이터 가져오기] > [파일에서] > [폴더에서]라는 메뉴가 있어요. 이걸 선택하고 아까 파일들을 모아둔 폴더 경로를 지정해주면 됩니다. 그러면 창이 하나 뜨는데, 여기서 파일 리스트가 쭉 보일 거예요.
3. 결합 및 로드
창 하단에 보면 [결합] 버튼이 있어요. 여기서 [결합 및 데이터 변환]을 누르세요. 이게 핵심이에요. 단순히 파일 목록만 가져오는 게 아니라, 그 안에 있는 내용물(시트 데이터)을 싹 다 긁어와서 하나로 묶어주는 과정이거든요.
데이터 가공, 쿼리 편집기에서 해결
[결합 및 데이터 변환]을 누르면 파워쿼리 편집기라는 새로운 창이 뜰 거예요. 엑셀이랑 비슷하게 생겼는데 기능은 훨씬 강력해요. 여기서 불필요한 행을 지우거나, 날짜 형식을 바꾸거나 하는 작업을 할 수 있어요.
재밌는 건 여기서 작업한 내용이 ‘단계’로 기록된다는 점이에요. 마치 포토샵 히스토리처럼요. 나중에 원본 데이터가 바뀌어도 이 단계들이 자동으로 적용되니까, 매번 똑같은 작업을 반복할 필요가 없죠.
아래 표를 보면 기존 방식이랑 파워쿼리가 어떻게 다른지 확 체감되실 거예요.
| 구분 | 기존 방식 (복사/붙여넣기/VBA) | 파워쿼리 (Power Query) |
|---|---|---|
| 난이도 | 단순 반복은 쉽지만 지루함, VBA는 어려움 | 마우스 클릭 위주라 배우기 쉬움 |
| 처리 속도 | 데이터 많으면 엑셀 멈춤 현상 발생 | 대용량 데이터도 메모리 효율적으로 처리 |
| 자동화 | 매번 수작업 하거나 복잡한 코드 수정 필요 | ‘새로 고침’ 버튼 하나로 자동 업데이트 |
| 유지보수 | 담당자 바뀌면 인수인계 헬게이트 | 작업 단계가 기록되어 파악하기 쉬움 |
대용량 데이터 다룰 때 꿀팁
데이터가 100만 행이 넘어가면 엑셀 시트에 다 뿌려줄 수가 없어요. 이럴 땐 데이터를 시트로 로드하지 말고 ‘연결만 만들기’ 기능을 쓰세요. 그리고 나서 피벗 테이블로 바로 연결하면, 시트에는 데이터가 없어도 결과 분석은 가능해요. 이 방법 쓰면 파일 용량도 획기적으로 줄어들고 속도도 엄청 빨라져요.
저도 얼마 전에 3년 치 판매 데이터 500만 건을 이 방식으로 분석했는데, 버벅임 없이 돌아가는 거 보고 감탄했잖아요. 예전 같으면 엑셀 쪼개서 작업하느라 밤샜을 텐데 말이죠.
마무리하며
처음엔 메뉴 위치도 낯설고 ‘쿼리’라는 단어 때문에 어렵게 느껴질 수 있어요. 근데 딱 한 번만 성공해보세요. 그 희열은 말로 다 못해요. 상사한테 “이거 데이터 취합하는 데 반나절 걸립니다”라고 말해놓고, 파워쿼리로 5분 만에 끝낸 뒤에 커피 한잔하는 여유, 우리도 이제 누려야죠.
지금 당장 흩어져 있는 파일들 한 폴더에 모으고 파워쿼리 한번 돌려보세요. 여러분의 퇴근 시간이 달라집니다.