여러 시트·여러 파일을 한 표로 합치기 — 붙여넣기·파워 쿼리·파이썬

한 줄로

월별 시트 3개짜리 파일 두 개, 모두 27줄에 금액 합계 1,526,850원을 한 표로 합쳐 봤습니다. 복사·붙여넣기는 머리글이 여섯 번 딸려 와 전체 33줄이 됐고(맨 위 한 줄을 빼도 표 안에 머리글 5줄이 남습니다), 붙일 줄을 한 칸 잘못 잡은 경우에는 머리글이 없어지고 자료가 27줄에서 22줄로 줄면서 294,850원이 사라졌습니다. 파워 쿼리와 파이썬은 둘 다 27줄·1,526,850원으로 원본과 맞았습니다. 합친 뒤에는 줄 수와 합계를 원본과 맞대 보는 것이 이 일의 절반입니다.

이런 분께

• 월별로 시트를 나눠 쓰다가 분기·연간으로 한 번에 봐야 하는 분
• 지점별로 받은 파일 여러 개를 피벗 하나에 넣어야 하는 분
• 붙여넣기로 합쳤는데 피벗에 「거래처」라는 항목이 생겨 당황하신 분

실측 환경

엑셀 창을 띄워 메뉴를 눌러 본 것은 아닙니다. 같은 일을 하는 명령을 COM으로 부르고 결과 숫자를 읽었습니다.

합칠 것 — 먼저 원본을 세어 둡니다

열은 날짜·거래처·품목·수량·금액 다섯 개로 여섯 시트가 모두 같습니다. 시트별 줄 수는 3~6으로 서로 다르게 뒀습니다. 다만 1월과 5월이 둘 다 5줄, 2월과 6월이 둘 다 4줄이라 줄 수만으로는 어느 시트가 빠졌는지 가릴 수 없습니다. 어느 줄이 어디서 왔는지 되짚으려면 출처를 열로 적어 두고 출처별 줄 수와 합계를 대조해야 합니다.

합치기 전에 이 두 숫자를 적어 두십시오. 27과 1,526,850이 이 글에서 계속 나오는 기준입니다.

방법 1 — 복사·붙여넣기

가장 먼저 손이 가는 방법이고, 시트가 서너 개면 이쪽으로도 됩니다. 대신 두 군데서 미끄러집니다.

머리글까지 통째로 붙였을 때

시트마다 표 전체를 잡아 차례로 붙이면 머리글이 여섯 번 딸려 옵니다.

「금액」이 글자로 들어앉은 행: 1, 7, 12, 19, 23, 29  (모두 6줄)
=COUNTIF(B:B,"거래처")  ->  6

여기서 헷갈리는 것은 합계가 멀쩡하다는 점입니다. =SUM(E:E) 는 1,526,850으로 맞게 나옵니다. 「금액」이라는 글자는 숫자가 아니라서 SUM 이 그냥 건너뛰기 때문입니다. AVERAGE 도 56550으로 정상값이 나옵니다.

어긋나는 것은 줄을 세는 쪽입니다.

SUM 을 COUNTA 로 나눠 평균을 내던 습관이 있으면 여기서 8,836원쯤 어긋난 평균을 그대로 보고하게 됩니다. 「거래처」라는 글자가 거래처 열에 여섯 번 앉아 있으니 거래처 목록을 뽑는 쪽도 성할 리 없는데, 피벗 테이블을 실제로 만들어 확인하지는 않았습니다.

붙일 줄을 한 칸 잘못 잡았을 때

붙일 자리를 「마지막 줄 다음」이 아니라 「마지막 줄」로 잡으면 그 줄에 덮어써집니다. 이번에는 붙일 행을 End(xlUp).Row + 1 이 아니라 End(xlUp).Row 로 잡아 그 상황을 만들었습니다.

첫 칸의 21은 맨 윗줄을 머리글로 치고 뺀 값입니다. 여기서는 그 머리글까지 자료에 덮여 없어졌으니, 실제로 남은 자료는 22줄로 보셔야 합니다.

여섯 번 붙인 결과 숫자로 남은 금액 칸이 27개에서 22개로, 합계가 294,850원 줄었습니다. 첫 번째 붙여넣기는 머리글 자리에 내려앉아 머리글까지 없앴습니다. 줄이 통째로 빈 자리가 남는 것이 아니라 값만 덮여 사라지는 것이라, 합계를 원본과 맞대 보지 않으면 그대로 넘어갑니다.

붙여넣기로 하실 거라면 시트 하나를 붙일 때마다 =COUNTA(A:A) 를 찍어 두시는 편이 낫습니다. 늘어난 만큼이 방금 붙인 시트의 줄 수와 같아야 하고, 이 표라면 머리글까지 28에서 끝나야 합니다.

방법 2 — 파워 쿼리

머리글을 알아서 한 줄로 정리해 주고, 만들어 둔 쿼리를 다음에 다시 쓸 수 있습니다. 다만 아래 쿼리는 파일 두 개를 이름으로 못박아 두었으니 파일이 늘면 쿼리도 손봐야 합니다. 새 자료를 넣고 새로 고침을 눌렀을 때 결과가 따라 늘어나는지는 이번에 돌려 보지 않았습니다.

이번에는 아래 M 코드를 쿼리로 넣고 표로 실었습니다. 파일 안의 시트를 모두 훑어 이어 붙이고, 그 결과를 다시 두 파일 사이에서 이어 붙이는 코드입니다.

let
    한파일 = (경로 as text) as table =>
        let
            책 = Excel.Workbook(File.Contents(경로), true),
            시트만 = Table.SelectRows(책, each [Kind] = "Sheet"),
            이어붙임 = Table.Combine(시트만[Data])
        in
            이어붙임,
    결과 = Table.Combine({ 한파일("C:\\...\\X2_44_매출_1분기.xlsx"),
                           한파일("C:\\...\\X2_44_매출_2분기.xlsx") })
in
    결과

가운데 두 줄의 파일 경로는 길어서 줄여 옮겼습니다. 실제로 넣은 값은 임시 폴더의 전체 경로이고 03_파워쿼리_로그.txt 에 그대로 있습니다.

화면에서 이 쿼리를 넣는 순서

2026-09-28 보강 — 공식 도움말 대조, 이 화면 절차는 직접 실행하지 않음. 아래는 Windows 데스크톱 Excel에서 위 M 코드를 넣는 절차입니다. 새 코드나 새 실측 결과를 추가한 것은 아닙니다.

1. 합칠 원본 파일 두 개를 저장해 두고, 결과를 담을 통합 문서를 엽니다. 이 코드의 전제는 위 시험처럼 각 시트의 첫 줄이 머리글이고 열 이름이 같은 것입니다.
2. 「데이터 → 데이터 가져오기 → 기타 원본에서 → 빈 쿼리」를 선택합니다.
3. 열린 파워 쿼리 편집기에서 「홈 → 고급 편집기」를 누릅니다. 기본으로 들어 있는 코드를 모두 지우고, 위 코드의 let부터 마지막 결과까지 붙여넣습니다.
4. 한파일(...) 두 곳에서 큰따옴표 안의 경로 전체를 각각 실제 원본 파일의 전체 경로로 바꿉니다. 줄임표 ...를 남기지 말고 파일명과 .xlsx까지 넣습니다. 큰따옴표는 유지하되, 경로 구분자는 실제 Windows 경로 그대로 입력합니다.
5. 「완료」를 누르고 미리보기에서 머리글과 자료를 확인합니다. 경로나 쿼리 오류가 있으면 먼저 고친 뒤 진행합니다.
6. 편집기의 「홈 → 닫기 및 로드 → 닫기 및 로드」로 시트에 불러옵니다. 불러온 표의 자료 줄 수와 금액 합계를 원본과 대조합니다. 이 글의 원래 더미 자료 기준은 27줄·1,526,850원입니다.

메뉴 경로는 Microsoft의 쿼리 만들기·로드·편집과 고급 편집기에서 코드 입력하기를 대조했습니다. 새 달 자료를 추가한 뒤 새로 고침하는 시험은 이번 보강에서도 하지 않았습니다.

원래 시험에서 읽은 결과

Excel.Workbook(..., true) 의 두 번째 인자는 시트의 첫 줄을 머리글로 삼으라는 뜻입니다. 이 쿼리가 돌아온 결과는 머리글 다섯 개에 자료 27줄이었습니다. 붙여넣기에서 머리글 여섯 줄이 섞여 들던 일이 여기서는 생기지 않았습니다. [Kind] = "Sheet" 로 걸러 둔 것은 이름 정의 범위나 표가 같이 잡히는 것을 막기 위해서입니다.

아래는 2026-09-18 원래 시험에서 COM으로 단계를 밟은 결과입니다.

성공  wb.Queries.Add("합치기", M)                    -> '합치기'
성공  ws.ListObjects.Add(OLEDB Mashup) + CommandText -> '표_ExternalData_1'
성공  lo.QueryTable.Refresh(BackgroundQuery=False)   -> True
표 범위 = $A$1:$E$28
머리글  = ['날짜', '거래처', '품목', '수량', '금액']
마지막 행 = 28  (머리글 뺀 줄 = 27)
=SUM(E:E) -> 1,526,850  / =COUNT(E:E) -> 27
원본과 차이: 줄 0 / 금액 0

줄 수와 합계가 한 번에 맞았습니다. 머리글도 다섯 개 그대로입니다.

다만 날짜 열이 숫자로 들어옵니다

실어 온 표를 보면 날짜 열이 46025, 46029 처럼 다섯 자리 숫자입니다. 값은 멀쩡합니다.

파워 쿼리로 합친 표   A2  Text='46025'  Value2=46025  표시 형식='G/표준'
원본 시트            A2  Text='#######' Value2=46025  표시 형식='yyyy-mm-dd'
=TEXT(A2,"yyyy-mm-dd")  ->  2026-01-03

원본과 같은 46025를 들고 있는데 표시 형식이 「일반」으로 실려서 숫자로 보이는 것입니다. 날짜 열을 잡고 표시 형식만 yyyy-mm-dd 로 주면 2026-01-03으로 돌아옵니다. 값 자체는 46025 그대로입니다. 이런 칸이 왜 생기고 무엇을 조심해야 하는지는 같은 날짜인데 비교가 안 맞을 때 에 적어 뒀습니다.

방법 3 — 파이썬 (파일이 매달 쌓일 때)

같은 취합을 코드로 돌려 두려면 이쪽입니다. sheet_name=None 으로 읽으면 시트 이름을 열쇠로 삼은 딕셔너리가 돌아옵니다. 이번에는 두 파일의 여섯 시트가 그대로 읽혀 27줄로 모였습니다. 아래 코드는 파일 이름을 짧게 줄여 옮긴 것이고, 실제로 돌린 파일은 X2_44_매출_1분기.xlsx · X2_44_매출_2분기.xlsx 에서 X2_44_합치기_파이썬.xlsx 입니다. 전문은 원자료 폴더의 X2_44_python.py 에 있습니다.

import os, pandas as pd

frames = []
for p in ("매출_1분기.xlsx", "매출_2분기.xlsx"):
    시트들 = pd.read_excel(p, sheet_name=None)      # None = 모든 시트
    for 이름, df in 시트들.items():
        df = df.copy()
        df["출처"] = "%s / %s" % (os.path.basename(p), 이름)
        frames.append(df)

합 = pd.concat(frames, ignore_index=True)
print(len(합), int(합["금액"].sum()))       # 27 1526850
print(합.isna().sum().to_dict())           # 빈 칸 0
합.to_excel("합친표.xlsx", sheet_name="합친표", index=False)

출처 열을 붙여 두면 나중에 어느 시트에서 온 줄인지 되짚을 수 있고, 시트별로 다시 세어 원본과 맞대 볼 수 있습니다.

X2_44_매출_1분기.xlsx / 1월   5행    233,500
X2_44_매출_1분기.xlsx / 2월   4행    175,700
X2_44_매출_1분기.xlsx / 3월   6행    350,200
X2_44_매출_2분기.xlsx / 4월   3행    147,050
X2_44_매출_2분기.xlsx / 5월   5행    307,500
X2_44_매출_2분기.xlsx / 6월   4행    312,900

거래처별로 묶어도 더하면 1,526,850으로 돌아옵니다.

열 이름 한 글자가 어긋나면

concat 은 열 이름을 보고 줄을 세웁니다. 이름이 한 글자라도 다르면 같은 열로 안 봅니다. 7월 파일만 머리글을 금액 (뒤에 공백 하나)으로 적어 두고 합쳐 봤습니다.

7월 파일의 열 이름 : ['날짜', '거래처', '품목', '수량', '금액 ', '출처']
합친 뒤 열 이름    : ['날짜', '거래처', '품목', '수량', '금액', '출처', '금액 ']
줄 수              : 29      <- 7월 2줄이 더해져 맞다
sum('금액')        : 1,526,850   <- 7월 74,000원이 빠졌다
sum('금액 ')       : 74,000
'금액' 열 빈 칸    : 2줄

줄 수만 보면 29로 맞습니다. 합계는 7월치가 통째로 빠졌고, 빠진 금액은 옆에 새로 생긴 금액 열에 따로 앉아 있습니다. 합친 뒤 열 이름을 찍어 보고 isna().sum() 을 확인하면 바로 드러납니다.

고치는 방법은 읽어 올 때 앞뒤 공백을 떼는 것입니다.

df.columns = df.columns.str.strip()

떼고 다시 합치니 열이 여섯 개로 정리되고 29줄에 합계 1,600,850이 됐습니다.

세 방법 검산

1a는 합계만 보면 통과합니다. 줄 수를 같이 봐야 32와 27의 차이가 드러납니다. 1c는 반대로 줄 수를 세다 보면 21이 눈에 띕니다. 두 숫자를 같이 봐야 둘 다 걸립니다.

표의 「머리글 뺀 줄 수」는 마지막 행에서 1을 뺀 값이라, 머리글이 살아 있는 줄에서만 자료 줄 수와 같습니다. 1c는 머리글이 덮여 없어졌으니 21이 아니라 옆 칸의 22가 실제로 남은 자료 줄 수입니다.

합친 표에서 꼭 확인하실 것은 세 가지입니다. 머리글을 뺀 줄 수가 원본 시트들의 줄 수 합과 같은지, 금액 합계가 시트별 합계의 합과 같은지, 그리고 금액 열에서 숫자인 칸 수(=COUNT(E:E))가 줄 수와 같은지입니다. 셋째 숫자가 줄 수보다 적으면 금액 열에 숫자가 아닌 것이 그만큼 들어 있다는 뜻입니다. 글자일 수도 있고 빈 칸이나 오류 값일 수도 있으니, 어느 쪽인지는 그 칸을 찾아 보셔야 합니다.

실패한 것

• Workbook.Queries 를 COM으로 다룰 수 있을지 확신이 없어 단계마다 성공·실패를 따로 찍게 만들어 놓고 돌렸습니다. 쿼리 추가·표로 싣기·새로 고침이 모두 통과했고 멈추지 않았습니다.
• 리본 명령의 한국어 이름을 CommandBars.GetLabelMso 로 읽어 보려 했습니다. 스물세 개 중 일곱 개만 읽혔습니다. 「데이터」 탭, 「모두 새로 고침」, 「새로 고침 상태」, 「붙여넣기」, 「선택하여 붙여넣기...」, 「필터」, 「피벗 테이블」은 읽혔고, 나머지 열여섯 개는 모두 0x80070057 로 돌아왔고, 파워 쿼리 관련 이름이 거기 전부 들어 있습니다. 제가 짐작한 idMso 이름이 틀린 것인지 이 판에 그 이름이 없는 것인지는 가리지 못했습니다.
• 결과 파일 세 개를 한꺼번에 열어 놓고 Application.Evaluate 에 통합 문서 이름 없이 시트만 적어 넘겼더니, 파일 하나만 열었을 때와 다른 값이 돌아왔습니다. 다른 파일을 열기 전에 재도록 순서를 바꿔 다시 쟀고, 두 경우를 08_환경·실패재현_로그.txt [2] 에 나란히 남겼습니다.

(가) 붙여넣기 파일 하나만 열어 둔 상태
  =COUNTA('1a_머리글째로'!B:B)            -> 33.0
  =COUNTIF('1a_머리글째로'!B:B,"거래처")   -> 6.0
(나) 결과 파일 세 개를 모두 열어 둔 상태  (ActiveWorkbook = X2_44_합치기_파이썬.xlsx)
  =COUNTA('1a_머리글째로'!B:B)            -> 1.0
  =COUNTIF('1a_머리글째로'!B:B,"거래처")   -> -2146826265   <- #REF!

확인하지 않은 것

• 2026-09-18 원래 시험에서는 화면에서 파워 쿼리를 만드는 메뉴를 누르지 않았습니다. 쿼리는 M 코드를 COM으로 밀어 넣어 만들었습니다. 위의 화면 절차는 2026-09-28 공식 도움말과 대조해 보강한 것이며, 직접 눌러 실행한 결과는 아닙니다.
• 파워 쿼리 편집기 화면에서 열 이름을 바꾸거나 형식을 지정하는 단계는 넣지 않았습니다. 시트를 이어 붙이는 데까지만 했습니다.
• 폴더 하나를 통째로 읽는 방식(Folder.Files)은 시험하지 않았습니다. 파일 두 개를 이름으로 지정한 경우만 확인했습니다.
• 새 달 시트를 넣고 새로 고침을 눌렀을 때 결과가 따라 늘어나는지는 돌려 보지 않았습니다. 한 번 실어 온 결과만 쟀습니다.
• 시트마다 열 순서가 다르거나 열 개수가 다른 경우는 넣어 보지 않았습니다. 여섯 시트가 모두 같은 다섯 열이었습니다.
• 빈 시트, 숨겨진 시트가 섞여 있을 때 [Kind] = "Sheet" 가 어떻게 거르는지 확인하지 않았습니다.
• 줄이 수만 개일 때 세 방법이 각각 얼마나 걸리는지 재지 않았습니다. 27줄로만 했습니다.
• xls(97-2003) 파일이나 CSV가 섞였을 때는 시험하지 않았습니다.

함께 보기

• 같은 날짜인데 비교가 안 맞을 때 — 숨은 시간과 표시 형식
• 엑셀 표 정리 순서 — 병합 셀·빈 행·문자 날짜 고치기
• 엑셀 중복값 찾기·지우기
• 엑셀 두 명단 비교 — 공통·한쪽에만 있는 항목 찾기