엑셀 시트 합치기 방법: 폴더에 넣기만 하면 한 표로 모입니다

채널이 셋이면 파일도 셋으로 옵니다.

채널톡에서 하나, 카카오에서 하나, 전화 응대 기록에서 하나. 매주 월요일에 셋을 내려받아 한 시트에 붙여넣습니다.

복사와 붙여넣기가 느려서 문제가 되는 것이 아닙니다. 매주 돌아온다는 것이 문제입니다. 이 글은 엑셀 데이터 정리 여덟 단계의 두 번째 단계입니다.

엑셀 시트 합치기 방법은 세 가지가 있습니다

무엇을 고를지는 파일이 몇 번 오는지로 정합니다.

한 번만 합치면 되면 복사와 붙여넣기가 제일 빠릅니다. 도구를 배우는 시간이 더 듭니다.

365를 쓰고 시트가 같은 파일 안에 있으면 =VSTACK(채널톡!A2:F500,카카오!A2:F500,전화!A2:F500)으로 한 줄에 끝납니다. 시트가 늘면 수식을 고쳐야 합니다.

엑셀 데이터 합치기가 매주 돌아오고 파일이 따로 오면 파워 쿼리입니다. 처음 만드는 데 십 분쯤 걸리고 그다음부터는 새로 고침 한 번입니다.

파워 쿼리로 폴더를 통째로 읽습니다

폴더를 하나 만들고 이름을 상담원본이라고 붙입니다. 매주 내려받은 파일 셋을 여기에 넣습니다.

데이터 탭에서 데이터 가져오기, 파일에서, 폴더에서를 고르고 그 폴더를 지정합니다. 미리보기 창이 뜨면 결합 및 변환을 누릅니다. 예시 파일을 고르라고 하면 아무거나 하나 고릅니다.

이제 폴더 안의 모든 파일이 한 표로 붙습니다. 엑셀 데이터 합치기를 사람이 하는 단계가 여기서 없어집니다. 다음 주에 새 파일을 폴더에 넣고 데이터 탭의 모두 새로 고침을 누르면 새 줄이 아래에 이어집니다.

여기에 한 가지를 더합니다. 파워 쿼리 편집기에서 Source.Name 열을 남겨두면 각 줄이 어느 파일에서 왔는지 남습니다. 나중에 채널별로 세거나 특정 채널만 다시 볼 때 이 열이 필요합니다.

컬럼 이름이 다르면 먼저 맞춥니다

채널마다 열 이름이 다릅니다. 한쪽은 접수일시, 한쪽은 등록일, 한쪽은 날짜입니다.

파워 쿼리 편집기에서 각 열을 선택해 이름을 바꾸면 그 단계가 저장됩니다. 다음 주 파일에도 같은 이름 변경이 자동으로 적용됩니다.

그런데 채널이 열 이름을 바꾸면 그 단계가 대상을 못 찾아 쿼리가 멈춥니다. 이때는 오류가 뜨므로 바로 알 수 있고, 편집기에서 그 단계만 고치면 됩니다. 조용히 틀린 값을 내는 것보다 낫습니다.

여러 통장의 거래내역을 한 장부로 모을 때 계정 코드를 먼저 맞추듯, 채널별 파일도 컬럼 이름을 먼저 맞춥니다. 이름을 맞춰 두면 뒤에 오는 집계가 전부 같은 이름을 씁니다.

엑셀 시트 합치기 매크로를 쓰는 경우

파워 쿼리가 안 되는 상황이 있습니다. 파일이 xls 처럼 오래된 형식이거나, 한 파일 안에 시트가 수십 장 있고 그 시트들을 붙여야 하는 경우입니다.

시트를 순회해 붙이는 매크로는 짧습니다. 다만 시트 이름이나 열 위치가 바뀌면 깨지고, 깨질 때 오류 없이 엉뚱한 범위를 붙이기도 합니다.

그래서 기준은 하나입니다. 원본이 매번 같은 모양으로 오면 매크로도 괜찮고, 모양이 바뀌면 파워 쿼리가 낫습니다. 자세한 판단은 엑셀 자동화 매크로 편에 정리했습니다.

합치고 나서 바로 확인할 두 가지

붙였다고 끝나지 않습니다.

첫째, 줄 수를 셉니다. 원본 파일 셋의 행 수 합계와 합친 표의 행 수가 같아야 합니다. 머리글이 데이터로 섞여 들어가면 여기서 잡힙니다.

둘째, 날짜 열의 형식을 봅니다. 채널마다 표기가 다르면 합친 뒤에 텍스트와 날짜가 섞여 정렬이 엉킵니다. 파워 쿼리 편집기에서 날짜 형식으로 지정해 두면 다음 주에도 유지됩니다.

그다음이 중복 정리입니다. 합치기 전에 중복을 지우면 채널 간 중복이 안 잡히므로 순서가 이쪽입니다.

자주 받는 질문

Q. 파일마다 열 개수가 다른데 합쳐지나요

A. 합쳐집니다. 없는 열은 빈칸으로 채워집니다. 다만 열 이름이 조금이라도 다르면 별도 열로 붙으므로, 합치기 전에 이름을 통일하는 단계를 먼저 둡니다.

Q. 엑셀 데이터 통합 기능과 파워 쿼리는 무엇이 다른가요

A. 데이터 탭의 통합은 같은 항목끼리 값을 더하거나 평균 내는 집계 기능입니다. 엑셀 데이터 합치기처럼 줄을 이어 붙이는 것이 목적이면 파워 쿼리를 씁니다. 이름이 비슷해 헷갈리는데 하는 일이 다릅니다.

Q. 새로 고침을 눌러도 새 파일이 안 붙습니다

A. 파일이 폴더 안에 있는지, 확장자가 쿼리가 읽는 형식인지 봅니다. 임시 파일이 섞여 있으면 오류가 나기도 하므로 폴더에는 원본만 둡니다.

Q. 원본 파일을 지우면 합친 표도 사라지나요

A. 새로 고침을 하면 사라집니다. 쿼리는 폴더를 매번 다시 읽습니다. 지난 데이터를 남기려면 합친 결과를 값으로 복사해 별도 시트에 보관하거나, 원본 파일을 지우지 않고 하위 폴더로 옮깁니다.

한 번 만들어 두면 다음 주에는 파일을 폴더에 넣는 일만 남습니다. 그 차이가 매주 삼십 분입니다.