엑셀에서 수식오류로 #NAME? 이 된 셀을 파이썬에서 처리하는 방법?

 

엑셀에서 가끔 작업을 하다 보면, 수식이 잘못들어가서 #NAME? 이라는 값이 나온다.

몇줄 안된다면, 일일이 수작업으로 수정할 수 있겠으나...

수만건, 수십만건의 데이터를 다 일일이 손 볼 수는 없는 노릇...

게다가, 맨 앞에 들어간 값 때문에 벌어진 일인데 그 값이 똑같다는 보장도 없고.

 

1. #NAME? 이 발생하는 원인은?

 엑셀은 수식 기반이라, 수식이 잘못 들어갈 경우 위 값을 반환한다.

특히, 텍스트로 구성되어 있음에도 위 수식오류가 발생하는 경우에는

글의 맨 앞글자가 [ =, -, + ]중에 하나로 시작될 경우가 대부분이다.

 

2. 엑셀에서 수식자체를 복사하는 방법은?

일단 수식 자체가 복사되게 해야하는데, 이를 위해

상단 [수식] - "수식표시" 메뉴 클릭하면 수식 그 자체가 보여지는데

이 상태로 복사하면 = 을 포함한 수식 자체가 복사된다.

 

만약 위 방식대로 하지 않고, 그대로 #NAME? 상태로 복사하면, 해당 값이 텍스트 그대로 복사되어 데이터로써 가치가 없어진다.

 

3. 이제 파이썬에서 어떻게 하면 되나?

정규 표현식을 이용하면 가장 쉽다.

아래 예시를 참고해보자.

 

파이썬에서 위 수식 자체를 복사해왔으면 절반은 성공한 셈.

그 다음엔 정규표현식과 applymap의 도움을 받아 재빠르게 모든 행에 대해 작업을 해보자.

 

=, - , + 로 시작하는 텍스트는 또다시 엑셀로 가져왔을 때 수식오류가 발생하므로, 

위 세가지로 시작할 경우, 삭제하고 뒤에 있는 텍스트만 남겨주는 방법이 가장 좋을듯 하다.

import pandas as pd
import re 				# 정규표현식

df = pd.read_clipboard(sep='\t', dtype='object') # 복사한 데이터를 데이터프레임으로 가져오자

# df에서 모든 행에 대해 아래와 같은 반복작업을 수행
# 정규표현식에서 ^= 가 의미하는 것은, =로 시작하는 값을 찾아라, 그리고 대체값을 넣으면 되는데 이경우엔 빈칸이다.
# 값이 =로 시작하지 않을때는 원래 셀의 값(x) 그대로를 반환한다

df = df.applymap(lambda x:re.sub("^=", "", x)) 

df = df.applymap(lambda x:re.sub("^-", "", x))  # - 로 시작하면, -를 지우기
df = df.applymap(lambda x:re.sub("^+", "", x))  # + 로 시작하면, +를 지우기