엑셀 데이터 자동화가 어려운 14가지 이유와 극복 방법

"교육 때 배운 대로 했는데요, 제 파일로 하면 숫자가 이상하게 나와요. 그런데 오류는 안 납니다."

이 조직은 이미 AI 활용 기초 교육을 한 차례 받은 상태였습니다. 그런데 현업에 돌아가서 자기 설문 데이터를 올리면 결과가 제각각이었습니다. 오류가 났다면 차라리 나았을 텐데, 오류는 안 나고 숫자만 달랐습니다.

사전 조사에서 확인한 것

실제로 쓰시는 설문 파일 열두 개를 받아 봤습니다. 열어 보니 원인이 명확했습니다.

① 사람이 보라고 만든 표였다 — 중간에 그룹별 소계 행이 있고, 구획을 나누려고 빈 줄을 넣었고, 본부 이름이 행으로 끼어 있었습니다. 사람 눈에는 읽기 좋은 표입니다.

② 그런데 AI 는 그걸 데이터로 센다 — 챗GPT 는 엑셀을 올리면 내부적으로 파이썬 판다스로 읽습니다. 소계 행도, 본부 이름 행도 응답자 한 명으로 셉니다. 응답자가 20명인데 22명이 됩니다.

③ 아무도 그걸 모른다 — 이게 핵심이었습니다. 오류 메시지가 없으니 결과를 그대로 믿습니다. 그리고 그 숫자가 정책 보고서로 올라갑니다.

담당 과장님이 하신 말씀이 이 과정의 목표가 됐습니다. "틀린 걸 알아채는 법을 알려주세요."

설계 — 고치는 법보다 알아채는 법을 먼저

프롬프트 작성법을 더 가르치는 방향으로 가지 않았습니다. 원인이 거기 있지 않았기 때문입니다.

대신 일부러 망가뜨린 파일 열네 개를 준비했습니다. 각각 한 가지 문제만 심어 두고, 참가자가 직접 올려서 무슨 일이 생기는지 보게 하는 구성입니다. 문제를 섞으면 무엇 때문에 틀렸는지 못 가리므로 반드시 하나씩입니다.

핵심 판단은 열네 가지를 세 무리로 나눈 것이었습니다.

셋째 무리에 시간을 가장 많이 배정했습니다. 나머지 둘은 언젠가 알게 되지만, 셋째는 겪어 보지 않으면 평생 모릅니다.

진행

1단계 — 기준선부터 잡았다

문제 없는 파일을 먼저 올렸습니다. 1행이 필드명이고 2행부터 데이터가 시작하는 표입니다.

파일을 올리면 챗GPT 는 늘 같은 일부터 합니다.

data = pd.read_excel(file_path, sheet_name='Sheet1')
data.head()          # 상위 5행을 보여준다

이 다섯 줄이 대화 전체의 출발점이라는 것을 먼저 못 박았습니다. 여기서 표가 이상하면 그 뒤 분석은 전부 어긋납니다. 파일을 올린 직후 이 다섯 줄만은 눈으로 확인한다 — 이 습관 하나가 이 교육의 절반입니다.

2단계 — AI 가 알아서 고치는 것들

헤더가 비어 있거나, 빈 열이 섞여 있거나, 칼럼명이 2단이거나, 병합 셀이 있는 경우입니다.

처음에는 잘못 읽습니다. 칼럼명이 Unnamed: 0, 1, 2… 가 되고 진짜 필드명이 첫 번째 데이터가 됩니다. 그런데 챗GPT 가 멈추지 않고 스스로 다시 짭니다.

# 첫 행이 헤더인 것 같아 다시 읽습니다
df = pd.read_excel(file_path, sheet_name='Sheet1', header=1)

여기서 놓치기 쉬운 대목을 함께 짚었습니다. 정리한 결과를 df_cleaned 같은 새 변수에 담는 경우가 있는데, 그 뒤 분석에서 원본 df 를 부르면 정리 전 데이터로 분석하게 됩니다. 코드를 눈으로 따라가야 하는 이유가 이것입니다.

3단계 — 사람이 먼저 고쳐야 하는 것들

여기서부터 반응이 달라졌습니다.

소계 행이 가장 컸습니다. 12행에 'A그룹' 소계, 23행에 'B그룹' 소계가 있는 파일을 올리니 count 가 22 로 나왔습니다. 소계 행에 평균값이 들어 있으니 평균에 평균이 섞여 들어갑니다.

그룹 이름 행도 같습니다. 2행에 'A본부', 8행에 'B본부' 를 넣으면 그것도 사람으로 셉니다. 숫자 칼럼은 비어 있어 통계는 멀쩡한데, 이름 칼럼의 고유값을 세면 본부 이름까지 응답자로 셉니다.

여기서 원칙을 하나 정리했습니다 — 분류는 행이 아니라 칼럼이어야 합니다. '부서' 라는 열을 만들어 각 행에 값을 넣는 것이 맞습니다.

결측치도 이 무리입니다. 비어 있는 칸이 있으면 판다스는 그 행을 빼고 평균을 냅니다. 숫자만 보면 문제가 없어 보이는데, 점수를 짜게 주는 응답자가 1번 문항을 비우고 2번에만 답했다면 두 문항 비교가 서로 다른 사람 집단의 비교가 됩니다.

찾는 것 자체는 간단합니다.

df.isnull().sum()          # 칼럼별 결측치 개수

다만 그 행을 버릴지 평균으로 채울지는 데이터의 성격을 아는 사람이 정해야 합니다. AI 는 방법을 제안하지만 어느 쪽이 맞는지는 모릅니다.

4단계 — 조용히 틀리는 것들

가장 오래 다룬 구간입니다.

서식이 문자인 숫자. 값은 4 인데 서식이 '문자' 인 칸이 하나 있으면 엑셀과 챗GPT 의 계산 결과가 달라집니다.

엑셀은 문자를 빼고 셌고 판다스는 숫자로 읽어 포함했습니다. 어느 쪽도 오류를 띄우지 않습니다. 두 화면을 나란히 놓지 않으면 영영 모릅니다. 이 표를 띄웠을 때가 3시간 중 반응이 가장 컸습니다.

'4점' 같은 진짜 문자. 한 칸만 이래도 그 칼럼 전체가 문자형이 됩니다. 기초통계량이 평균·최댓값이 아니라 count, unique, top, freq 로 나옵니다. 챗GPT 가 오류를 만나면 스스로 고치는데, 그 처리가 4점 을 결측치로 만들어 한 사람의 응답을 조용히 지웁니다.

이상치. 1~5 척도인데 33 이 들어 있는 파일입니다. 판다스는 그대로 받아들입니다. 평균 5.65, 최댓값 33 이 나오는데 아무도 이상하다고 하지 않습니다.

# 이상치 확인
df['score3'].describe()
#   mean   5.65      ← 5점 만점인데 평균이 5.65
#   max    33.0      ← 여기가 원인입니다

# 중앙값으로 대체
median = df['score3'].median()
df.loc[df['score3'] > 5, 'score3'] = median

여기서 가장 실용적인 프롬프트가 나왔습니다. 데이터 전처리 해줘 한 줄이면 이상치를 지목하고 처리 방안까지 제안합니다. 다만 먼저 말을 걸어야 합니다. 가만두면 33 은 끝까지 살아남습니다.

이 방식으로 안 되는 것

• AI 가 옳은 처리를 골라주지 않습니다. 결측치를 버릴지 채울지는 조사 설계를 아는 사람이 정합니다
• 문제가 겹치면 첫 번째만 고칩니다. 헤더를 바로잡느라 소계 행은 못 봅니다. 실제 업무 파일은 대개 문제가 둘 이상입니다
• 원본 엑셀은 바뀌지 않습니다. 전처리는 읽어 들인 데이터에서만 일어납니다. 다음에 같은 파일을 올리면 같은 문제가 반복됩니다
• 점검은 자동화되지 않습니다. 5분 점검은 사람이 눈으로 합니다

운영 준비 — 망가진 파일을 미리 만들었다

이 과정의 준비는 대부분 실습 파일 제작이었습니다. 열네 가지 상황을 각각 하나씩만 심은 파일을 만들어야 합니다. 문제가 섞이면 무엇 때문에 틀렸는지 못 가립니다.

원본은 이 기관이 실제로 쓰는 설문 양식을 바탕으로 했습니다. 낯선 데이터로 하면 "그렇구나" 로 끝나는데, 자기 조직 양식이 망가지는 걸 보면 다음 주에 자기 파일을 다시 봅니다.

진행 중에 나온 질문

"그럼 처음부터 표를 다르게 만들어야 하나요?"

절반만 맞습니다. 보고용 표와 분석용 표는 목적이 다르니 둘 다 있어야 합니다. 소계와 병합이 들어간 보고용 표는 사람이 읽기 좋고, 그건 그것대로 필요합니다.

권해 드린 방식은 원본 시트를 분석용으로 유지하고 보고용은 따로 만드는 것입니다. 순서를 거꾸로 하면 매번 되돌리는 작업이 생깁니다.

교육이 끝났을 때 남은 것

참가자들이 자기 부서 설문 파일을 점검해 정리한 상태로 나갔습니다. 열두 개 중 아홉 개에서 문제가 나왔습니다. 소계 행이 가장 많았습니다.

그리고 팀에 5분 점검 순서가 자리에 붙었습니다. 다섯 줄짜리입니다.

두 달 뒤 과장님이 전해 주신 변화는 이것이었습니다.

"이제 결과가 이상하면 파일부터 봅니다."

전에는 프롬프트를 바꿔 가며 다시 물어봤다고 합니다. 원인이 파일에 있으니 몇 번을 물어도 같은 답이 나왔던 것이죠. 어디를 볼지 아는 것만으로 헤매는 시간이 사라졌습니다.

다룬 도구

• 챗GPT 파일 업로드 — 엑셀을 판다스로 읽어 분석합니다
• head() 확인 습관 — 올린 직후 다섯 줄로 구조를 검증합니다
• 데이터 전처리 해줘 프롬프트 — 이상치·결측치를 먼저 지목하게 합니다
• 5분 점검 목록 — 소계·그룹행·병합·문자 혼입·헤더

비슷한 교육을 검토 중이시라면

이 과정은 3시간이었습니다. AI 기초 교육을 이미 받은 조직에 특히 맞습니다. 배웠는데 현업에서 안 되는 이유가 대개 여기 있기 때문입니다.

• 설문·조사 데이터를 다루는가 — 척도와 결측치가 있는 데이터일수록 효과가 큽니다
• 엑셀 표를 사람이 읽기 좋게 만들어 왔는가 — 소계와 병합이 많을수록 얻는 게 큽니다
• 결과를 보고서로 올리는가 — 조용히 틀리는 숫자의 대가가 큰 조직일수록 급합니다

쓰시는 조사 양식 한두 개만 보내주시면 어떤 문제가 들어 있는지 먼저 확인해 알려 드립니다.