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

엑셀 표 정리 순서 — 병합 셀 풀고, 빈 행 지우고, 문자 날짜를 날짜로

한 줄로

남이 만든 표를 물려받으면 부서 칸이 병합돼 있고, 팀 사이에 빈 행이 끼어 있고, 입사일이 2024.03.05 같은 글자로 들어 있습니다. 이 상태로는 정렬 버튼이 「이 작업을 수행하려면 병합하려는 모든 셀의 크기가 동일해야 합니다」로 막히고, 날짜 조건으로 세면 0건이 나옵니다. 병합 해제 → 빈 행 삭제 → 날짜 변환 순서로 12행짜리 더미 표를 정리했더니 9행이 됐고, 금액 합계 10,100은 전후가 같았습니다.

이런 분께

• 다른 팀에서 받은 표를 정렬·필터·피벗에 걸어야 하는 분
• 정렬을 누를 때마다 병합 셀 오류가 떠서 매번 병합을 풀었다 다시 하는 분
• 날짜로 기간을 뽑으려는데 계속 0건이 나오는 분

실측 환경

아래 절차에 적은 메뉴 이름은 한국어판 Excel 16.0 기준으로 썼지만, 이번 시험에서 화면을 띄워 메뉴를 눌러 본 것은 아닙니다. 같은 일을 하는 명령을 COM으로 부르고 그 결과 숫자를 읽었습니다. 화면 이름이 쓰시는 판과 다를 수 있습니다.

정리 전 표

부서를 세로로 병합해 두고(A2:A4, A6:A7, A9:A11) 팀이 바뀌는 자리에 빈 행을 넣었습니다(5·8·12행). 입사일은 점 표기·짧은 표기·붙임표 표기를 섞어 넣었고, 아홉 칸 모두 글자로 들어간 것을 ISTEXT 로 확인했습니다.

정리 전 숫자부터 세어 뒀습니다.

부서가 9행에 적혀 있는 것처럼 보이지만 실제 값은 4칸에만 들어 있습니다. 병합은 왼쪽 위 칸에만 값을 두고 나머지를 비워 두기 때문입니다.

정렬부터 걸어 봤더니 이렇게 막혔습니다.

Microsoft Excel: 이 작업을 수행하려면 병합하려는 모든 셀의 크기가 동일해야 합니다.

이 상태에서는 날짜 집계도 안 됩니다.

글자는 MIN·MAX가 아예 세지 않아서 0이 나옵니다. 「하반기 입사자가 한 명도 없다」가 아니라 날짜로 읽히는 값이 하나도 없다는 뜻입니다.

1단계 — 병합을 풀고 값을 채웁니다

메뉴로 하실 때는 이 순서입니다.

1. 부서 열(A2:A13)을 선택하고 홈 탭 > 병합하고 가운데 맞춤을 한 번 더 눌러 병합을 풉니다.
2. 같은 범위를 선택한 채 Ctrl+G > 옵션 > 빈 셀을 고르고 확인을 누릅니다. 빈 칸만 선택됩니다.
3. 그대로 = 를 치고 위쪽 방향키를 눌러 바로 위 칸을 가리킨 다음, Ctrl+Enter를 누릅니다. 선택된 빈 칸 전부에 위 값이 들어갑니다.
4. 부서 열을 복사한 뒤 같은 자리에 값으로 붙여넣어 수식을 값으로 바꿉니다.

병합을 푼 직후 이동 옵션이 잡은 빈 칸은 $A$3:$A$5,$A$7:$A$8,$A$10:$A$12 였습니다. Ctrl+Enter로 한꺼번에 채우고 나니 A3에는 =A2, A5에는 =A4 가 들어갔고 =COUNTA(A2:A13) 는 4에서 12로 올라갔습니다.

12가 된 것은 빈 행 자리(5·8·12행)의 부서 칸까지 위 값으로 채워졌기 때문입니다. 표에 없어야 할 행에 「영업1팀」 같은 글자가 붙은 셈인데, 다음 단계에서 이름 열을 기준으로 지우면 같이 없어집니다. 빈 행을 지울 때 부서 열이 아니라 이름 열을 기준으로 잡아야 하는 이유가 여기 있습니다.

값으로 바꾼 뒤 A3을 다시 읽으니 수식이 아니라 값 「영업1팀」이 돌아왔습니다. 그 상태로 빈 행 세 줄을 지우자 부서가 영업1팀 3행·영업2팀 2행·지원팀 3행·기획팀 1행으로 남고 금액 합계는 10,100 그대로였습니다. 4번을 건너뛰고 수식인 채로 행을 지우면 어떻게 되는지는 이번에 돌려 보지 않았습니다.

2단계 — 빈 행을 지웁니다

1. 이름 열(B2:B13)을 선택합니다. 표 전체가 아니라 반드시 값이 다 차 있어야 하는 열 하나만 고릅니다.
2. Ctrl+G > 옵션 > 빈 셀 > 확인.
3. 마우스 오른쪽 > 삭제 > 행 전체.

빈 셀로 잡힌 자리는 $B$5,$B$8,$B$12 세 곳이었고, 행을 지운 뒤 표는 머리글 포함 10행(데이터 9행)이 됐습니다. 금액 합계는 10,100 그대로였습니다. 지운 것이 정말 빈 행뿐이라면 합계는 움직이지 않습니다. 합계가 바뀌었다면 무엇을 지웠는지부터 되짚어 보셔야 합니다.

표 전체를 선택한 상태로 이동 옵션을 쓰면 표 안의 빈 셀이 모두 잡히고, 그대로 행 전체 삭제를 누르면 다른 칸에 값이 든 행까지 같이 없어질 수 있습니다. 열 하나만 골라서 쓰는 이유입니다.

3단계 — 문자 날짜를 날짜로 바꿉니다

여기서 DATEVALUE부터 떠올리기 쉬운데, 점으로 찍은 날짜는 받아 주지 않았습니다. 다섯 가지 표기를 한 칸씩 넣어 봤습니다.

점으로 찍은 두 가지가 다 오류였습니다. 그래서 텍스트 나누기를 씁니다.

1. 입사일 열(C2:C10) 하나만 선택합니다.
2. 데이터 탭 > 텍스트 나누기.
3. 1단계에서 「구분 기호로 분리됨」을 고르고 다음.
4. 2단계에서 구분 기호 체크를 전부 해제하고 다음.
5. 3단계에서 열 데이터 서식을 「날짜」로 고르고 오른쪽 목록에서 「년월일」을 고른 뒤 마침.

점·붙임표가 섞인 아홉 칸이 한 번에 바뀌었습니다.

변환 전: 2024.03.05, 2024.3.7, 2024-03-11, 2024.04.02, 2024.04.15, 2024.05.01, 2024.05.20, 2024.12.01, 2025.01.09
변환 후: 2024-03-05, 2024-03-07, 2024-03-11, 2024-04-02, 2024-04-15, 2024-05-01, 2024-05-20, 2024-12-01, 2025-01-09
내부 값: 45356, 45358, 45362, 45384, 45397, 45413, 45432, 45627, 45666

엑셀은 날짜를 일련번호로 갖고 있어서, 제대로 바뀌었으면 내부 값이 저런 다섯 자리 숫자가 됩니다. 보이는 날짜와 일련번호를 같이 확인하시는 편이 확실합니다(45356이 2024-03-05입니다). 열 너비가 모자라 ####### 으로 보이는 것이라면 너비만 넓히면 됩니다.

바꾸고 나서 다시 세어 보면 이렇습니다.

순서를 바꾸면 어떻게 되나

날짜를 안 바꾼 채 정렬부터 하면 이렇게 됩니다.

글자로 정렬하니 3월 7일(2024.3.7)이 12월 1일 뒤인 일곱째 줄로, 붙임표로 적은 3월 11일(2024-03-11)이 여덟째 줄로 갔습니다. 앞자리부터 글자를 맞춰 보기 때문에 「3」과 「03」이 같은 자리에 서지 못합니다. 이 표에서 나온 차례가 그대로 규칙이라고 볼 수는 없지만, 표기가 섞이면 날짜 차례가 깨진다는 것은 분명합니다. 날짜로 바꾼 뒤 같은 정렬을 걸면 홍길동·김철수·이영희·박민수·정수진·최동해·한여름·오세라·서지훈 차례로 제대로 섭니다.

그럼 1단계와 2단계를 바꾸면 어떻게 되는지도 돌려 봤습니다. 병합을 그대로 둔 채 빈 행 세 줄부터 지웠더니 삭제 자체는 됐고, 병합 구간이 A2:A4 · A5:A6 · A7:A9로 따라 줄었으며 금액 합계도 10,100 그대로였습니다. 이어서 병합을 풀고 보니 값이 빈 A열 행은 3·4·6·8·9행이었습니다. 이 표에서는 순서를 바꿔도 결과가 같았습니다.

순서를 지키라고 적은 이유는 다른 데 있습니다. 병합이 남아 있는 한 정렬 버튼이 앞의 오류로 막히고, 날짜를 안 바꾼 채 정렬하면 위에서 본 대로 차례가 어긋납니다. 병합 해제 → 빈 행 삭제 → 날짜 변환으로 가면 매 단계 끝에 행 수와 합계를 세어 확인할 수 있습니다.

파일이 여러 개일 때 — 파이썬

같은 모양의 파일이 매달 쌓이면 손으로 하는 편이 더 오래 걸립니다. openpyxl이 병합 구간을 알려 주고, 나머지는 pandas가 합니다. 아래 코드는 파일 이름을 짧게 줄여 옮긴 것입니다. 실제로 돌린 파일은 E2_26_정리표.xlsx → E2_26_파이썬정리후.xlsx 이고 전문은 원자료\excel-clean-data-table\E2_26_python.py 에 있습니다.

import openpyxl, pandas as pd

wb = openpyxl.load_workbook("정리표.xlsx")
ws = wb["원본"]
print([str(r) for r in ws.merged_cells.ranges])   # ['A2:A4', 'A9:A11', 'A6:A7']

# 1단계 병합 해제 + 값 채우기
for rng in list(ws.merged_cells.ranges):
    v = ws.cell(rng.min_row, rng.min_col).value
    ws.unmerge_cells(str(rng))
    for r in range(rng.min_row, rng.max_row + 1):
        for c in range(rng.min_col, rng.max_col + 1):
            ws.cell(r, c).value = v
wb.save("정리후.xlsx")

df = pd.read_excel("정리후.xlsx", sheet_name="원본", usecols="A:D", nrows=12)

# 2단계 빈 행 삭제
df = df.dropna(subset=["이름"]).copy()

# 3단계 문자 날짜 -> 날짜 (점을 붙임표로 바꿔 넘긴다)
s = df["입사일"].astype(str).str.strip().str.replace(".", "-", regex=False)
df["입사일"] = pd.to_datetime(s, format="mixed", errors="coerce")
print(df["입사일"].isna().sum())      # 0 = 변환 실패 없음

df = df.sort_values("입사일").reset_index(drop=True)
df.to_excel("정리후.xlsx", sheet_name="정리후", index=False)

돌린 결과는 COM으로 한 쪽과 같았습니다. 12행 → 9행, 합계 10,100 → 10,100, 변환 실패 0건, 2024-07-01 이후 입사 2건입니다. 부서별 합계는 영업1팀 3,500 / 영업2팀 2,900 / 지원팀 3,100 / 기획팀 600이고 더하면 10,100입니다.

errors="coerce" 를 넣으면 못 읽은 값이 오류로 멈추지 않고 빈 날짜(NaT)로 남습니다. 행이 지워지는 것은 아니고, 날짜 칸만 비는 것입니다. 그래서 변환 뒤 isna().sum() 을 꼭 찍어 보셔야 합니다. 이 숫자가 0이 아니면 못 읽은 값이거나 원래 비어 있던 값이니 어느 쪽인지 확인하십시오.

실패한 것

• 처음에는 정렬을 COM의 Range.Sort 에 이름 붙인 인수(Key1=, Order1=, Header=)로 넘겼습니다. 위아래가 아니라 좌우로 정렬돼서 머리글이 부서·이름·입사일·실적금액에서 실적금액·입사일·부서·이름으로 뒤바뀌었습니다. 매크로 기록과 같은 Sort 개체 방식(SortFields.Add → SetRange → Orientation = 1 → Apply)으로 바꿔 다시 돌렸습니다. 같은 호출을 나중에 다시 재현해 07_순서·정렬_보탬시험.txt 에 남겼습니다.
• 문자 날짜 정렬을 처음 잴 때, 이미 날짜로 바꾼 값을 복사해 놓고 재는 바람에 정렬 결과가 정상으로 나왔습니다. 변환 전 글자를 따로 보관했다가 다시 넣고 재측정했습니다. 로그에 남은 「문자 시트에 되돌려 놓은 입사일」 줄이 그 재측정입니다.
• 날짜 변환 직후 열 너비를 안 넓혀서 화면과 로그가 전부 ####### 이었습니다. 열 너비 자동 맞춤을 넣고 다시 읽었습니다.
• GPT 사실 대조에서 「빈 행을 먼저 지우면 부서 값이 어느 칸에 남을지 예측하기 어려워진다」가 돌려 보지 않은 추측으로 잡혔습니다. 실제로 순서를 바꿔 돌려 보니 이 표에서는 결과가 같았고, 그 문장은 위와 같이 고쳤습니다.

확인하지 않은 것

• 엑셀 화면을 띄워 메뉴를 직접 눌러 보지는 않았습니다. 이 글의 메뉴 이름과 마법사 단계는 한국어판 Excel 16.0 기준으로 적은 것이고, 숫자는 같은 일을 하는 COM 명령의 결과입니다.
• 가로로 병합된 칸(A1:C1 같은 머리글 병합)은 시험 표에 넣지 않았습니다. 세로 병합만 다뤘습니다.
• 통합 문서 옵션의 1904 날짜 체계에서도 같은 일련번호가 나오는지는 바꿔 보지 않았습니다. 기본 설정에서만 확인했습니다.
• 파워 쿼리로 같은 세 단계를 했을 때 결과가 같은지는 비교하지 않았습니다.
• 셀에 든 글자가 2024.13.05 처럼 날짜가 될 수 없는 값일 때 텍스트 나누기가 어떻게 처리하는지는 넣어 보지 않았습니다.
• 두 자리 연도(24.03.05)를 어느 세기로 읽는지 확인하지 않았습니다.
• 행이 수만 개일 때 이동 옵션으로 빈 행을 지우는 데 걸리는 시간은 재지 않았습니다. 12행짜리로만 했습니다.
• pandas의 format="mixed" 가 점 표기를 붙임표로 바꾸지 않고도 읽는지는 시험하지 않았습니다. 바꿔서 넘긴 경우만 확인했습니다.

함께 보기

• 엑셀 수식이 계산되지 않을 때
• 엑셀 두 명단 비교 — 공통·한쪽에만 있는 항목 찾기
• 엑셀 중복값 찾기·지우기