수식이 걸린 엑셀 데이터, 파이썬으로 불렀더니 NaN이 뜨는 이유와 해결법

실무에서 사용하는 엑셀 파일은 단순한 숫자의 나열이 아닙니다. 단가와 수량을 곱하는 간단한 수식부터, 복잡한 조건부 합계(SUMIFS)까지 다양한 수식들이 거미줄처럼 얽혀있죠. 파이썬 자동화를 처음 배우시는 분들은 이 수식이 걸린 엑셀 파일을 판다스로 호기롭게 불러왔다가, 숫자가 있어야 할 자리에 'NaN(빈칸)'이 가득한 것을 보고 멘붕에 빠지곤 합니다.

"엑셀을 열면 분명히 숫자가 잘 보이는데, 왜 파이썬은 빈칸이라고 할까요?"

이것은 파이썬에 에러가 난 것이 아니라, 엑셀과 파이썬이 '수식'을 대하는 근본적인 방식이 다르기 때문입니다. 오늘은 이 치명적인 오류의 원인을 파헤치고, 진정한 의미의 '데이터 자동화'를 위해 수식을 어떻게 다뤄야 하는지 그 해답을 제시해 드립니다.

1. 파이썬은 엑셀의 수식을 '계산'하지 못합니다

가장 먼저 머릿속에 박아두어야 할 핵심 개념입니다. 판다스(Pandas)는 엑셀 프로그램이 아닙니다. 따라서 SUM, VLOOKUP 같은 엑셀 고유의 함수 엔진을 가지고 있지 않습니다.

우리가 pd.read_excel()로 파일을 불러올 때, 파이썬은 엑셀 파일의 껍데기를 살짝 들춰보고 '가장 마지막에 엑셀이 계산해 둔 결과값(캐시 데이터)'만을 사진 찍듯이 찰칵 찍어옵니다. 즉, 파이썬 안에서 수량 데이터를 10에서 20으로 바꾼다고 해서, 총합계 수식이 자동으로 업데이트되어 바뀌지 않는다는 뜻입니다. 파이썬이 가져온 것은 살아 숨 쉬는 수식이 아니라 '박제된 숫자 텍스트'일 뿐입니다.

2. ERP에서 다운받은 파일이 무조건 NaN을 뱉는 이유

그런데 왜 숫자가 안 나오고 NaN이 뜨는 걸까요? 보통 회사 사내 시스템(ERP 등)에서 다운로드한 엑셀 파일에서 이런 일이 잦습니다.

시스템이 엑셀 파일을 만들어낼 때는 컴퓨터의 리소스를 아끼기 위해 수식(예: =A1*B1)만 텍스트로 적어놓고, 실제 결과값은 계산하지 않은 채(캐시를 비워둔 채) 파일을 내보냅니다. 사용자가 엑셀을 더블클릭해서 여는 바로 그 순간! 엑셀 프로그램이 1초 만에 수식을 쫙 계산해서 화면에 보여주는 것이죠.

하지만 엑셀 프로그램 없이 백그라운드에서 파이썬 판다스로 이 파일을 곧바로 읽어버리면 어떻게 될까요? 파이썬은 계산된 결과값이 저장된 흔적(캐시)이 없으니 "어? 여기 아무 값도 없네?"라며 정직하게 NaN을 뱉어내는 것입니다.

3. 해결책: 가장 바보 같은 방법과 가장 현명한 방법

이 문제를 해결하는 방법은 두 가지가 있습니다.

가장 바보 같은 방법 (반쪽짜리 자동화): 사람이 직접 엑셀 파일을 더블클릭해서 엽니다. 엑셀이 수식을 계산해서 화면에 숫자를 띄워주면, 그 상태로 '저장(Ctrl+S)'을 누르고 닫습니다. 이제 계산된 결과값(캐시)이 파일에 저장되었으므로 파이썬으로 불러오면 정상적으로 숫자가 읽힙니다. 하지만 매번 사람이 파일을 열고 닫아야 한다면 이건 파이썬을 쓰는 의미가 없습니다.

가장 현명한 방법 (수식을 파이썬으로 옮기기): 진정한 해결책은 엑셀 원본 파일에 걸려있는 모든 수식을 지워버리는(무시하는) 것입니다. 엑셀은 오직 순수한 원본 데이터(Raw Data)만 담아두는 창고 역할만 해야 합니다. 연산은 파이썬에게 맡기세요.

예를 들어 엑셀에서 단가(A열) * 수량(B열) = 매출액(C열) 이라는 수식을 썼다면, 판다스에서는 단 한 줄로 이 수식을 완벽하게 대체할 수 있습니다. df['매출액'] = df['단가'] * df['수량']

이렇게 파이썬 내부에서 열(Column) 단위로 직접 사칙연산을 수행하면, 속도는 엑셀보다 수백 배 빠르고 NaN 에러가 발생할 일도 절대 없습니다. 조건부 수식(IF)이나 합계(SUM) 역시 파이썬에 훨씬 더 강력하고 안전한 문법들이 준비되어 있습니다.

엑셀의 함수 의존도를 낮추세요

파이썬 자동화의 최종 목표는 엑셀이라는 무거운 껍데기를 벗어던지는 것입니다. 엑셀 파일에 복잡한 수식이 덕지덕지 붙어있을수록 데이터의 오류 확률은 높아지고 파이썬과의 호환성은 떨어집니다. 오늘부터는 원본 데이터만 파이썬으로 가볍게 불러온 뒤, 모든 계산과 가공을 파이썬 스크립트 안에서 처리하는 '진짜 코더'의 마인드셋을 장착해 보시기 바랍니다.

▶ 핵심 요약

• 파이썬 판다스는 엑셀의 수식(함수)을 직접 계산할 수 없으며, 엑셀 프로그램이 미리 계산해 둔 결과값(캐시)만 읽어올 수 있다.

• 시스템에서 다운받은 엑셀 파일 등 결과값이 미리 저장되지 않은 파일을 판다스로 바로 읽으면 수식의 결과가 빈칸(NaN)으로 출력된다.

• 엑셀 파일 내의 수식에 의존하지 말고, 순수 데이터만 불러온 뒤 파이썬의 열(Column) 연산 기능을 활용해 직접 계산하는 것이 가장 안전하고 빠른 데이터 전처리 방법이다.

데이터를 필터링하고 연산까지 마쳤다면 이제 예쁘게 저장해서 상사에게 보고할 일만 남았습니다. 다음 11편에서는 '파이썬으로 가공한 데이터를 하나의 엑셀 파일 안에 여러 개의 시트(Sheet)로 나누어 깔끔하게 저장하는 법'을 다루겠습니다.

여러분은 엑셀을 사용하면서 수식이 너무 많이 걸려있어 파일이 열리는 데만 수 분이 걸리거나, 키보드 방향키만 눌러도 '계산 중...' 이 뜨면서 멈춰버렸던 끔찍한 경험이 있으신가요?