AI 해결 노트 · 2026-09-18 · 실측 2026-09-18

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

한 줄로

월별 시트 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 에 그대로 있습니다.

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

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!

확인하지 않은 것

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

함께 보기

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