[엑셀] 엑셀의 한계, 숫자 자릿수 제한_countif 함수 오류에 대해

 

 

평소 업무에서 엑셀을 많이 사용하며, 엑셀만큼 훌륭한 프로그램이 없다고 생각했었다. 메모리를 많이 먹어서 계산이 느린 경우는 종종 있지만, 내가 원하는 계산은 뭐든지 막힘 없이 다 해주었다.

 

예전에 근무한 직장에서는 MS WORD나 한컴의 한글로 작업하는 문서 작업을 엑셀로 하기도 했다.

 

15여 년에 걸친 직장생활 중 처음으로 엑셀의 한계를 발견했다.

엑셀이 처리할 수 있는 숫자의 자릿수가 15개로 제한이 있다. 

 

15자리 숫자는 123,456,789,012,345 이런 숫자이며, 100조 단위이다. 일상생활과 업무에서 100조까지의 숫자를 처리할 일이 없으니 몰랐다. 

 

그런데, 15자리 이상의 긴 숫자로 구성된 코드번호의 중복값을 처리하면서 문제가 발생했다. 

 

아마 평생 100조 이상의 숫자를 엑셀로 처리할 일은 없겠지만, 16자리 숫자로 구성된 코드는 업무에서 얼마든지 등장할 수 있다. 매일 사용하는 신용카드 번호도 16자리 숫자로 구성되어 있다.

 

 

엑셀의 숫자 자릿수 제한, countif 오류

 

위 그림에서와 같이 22자리로 구성된 코드를 다루던 중 이상한 현상이 발견되었다. 모든 코드는 중복 값이 없다. "중복된 항목 제거"를 했기 때문이다.

 

그런데, B열의 값(코드)을 COUNTIF 함수로 확인하는 과정에서 "5"라는 숫자가 나타났다. 눈으로 봐도 끝자리가 1, 2, 3, 4, 5로 모두 다른데, 완벽한 엑셀은 나에게 "5"개가 중복이라는 결과를 보여주었다. 컴퓨터를 껐다가 켜서 다시 해봤지만, 결과는 바뀌지 않았다.

 

검색 결과, 엑셀이 처리할 수 있는 숫자 자릿수가 15개 라는 것을 알게 되었다. 

 

 

엑셀의 숫자 자릿수 제한, countif 오류

 

COUNTIF 함수가 15자리까지는 잘 작동을 하는데, 16자리부터는 모두 "같은 값"으로 인식을 한다.

 

그 이유는 엑셀이 숫자 15자리를 초과하는 숫자는 모두 0으로 바꿔버리기 때문이다. 위 16자리 코드의 끝자리가 2001, 2002, 2003, 2004, 2005로 보이지만, 엑셀은 모두 "2000"으로 인식하기 때문에 COUNTIF 함수가 모두 같다는 결과를 반환하는 것이다.

 

 

엑셀의 숫자 자릿수 제한, countif 오류

 

위 그림에서 19~23번 행에서는 B열(코드)의 값이 "텍스트" 형식이라 끝자리가 2001, 2002, 2003, 2004, 2005로 보이지만, 이를 숫자로 바꾸면 26~30번 행과 같이 끝자리가 모두 "2000"이 돼버린다. 

 

 

 

 

해결방법

 

해결방법에는 여러가지가 있을 수 있다. 대전제는 15자리를 초과하는 숫자를 연산할 수는 없다. 1,000조 단위 숫자를 더하고, 빼고, 곱하고, 나누고 할 수 없다는 것이다. 연산을 하지 않고 위 예시와 같이 "텍스트(코드)"로만 사용하려면 다양한 방법이 있다.

 

 

첫 번째 방법은 긴 숫자를 자르는 것이다.

 

엑셀의 숫자 자릿수 제한, countif 오류 해결 방법

 

22자리의 텍스트인 숫자를 LEFT와 RIGHT 함수를 이용해서 자른 후, COUNTIFS 함수로 카운트를 해보면 다른 값으로 인식한다. 

 

 

 

두 번째 방법은 텍스트인 숫자를 텍스트로 바꿔주는 방법이다.

 

엑셀에서 숫자를 텍스트로 표시 형식을 바꿔도 숫자의 성격을 가지고 있기 때문에 이런 문제가 발생한다. 그럼 숫자를 완전히 텍스트로 바꿔버리면 이런 문제를 해결할 수 있다.

 

엑셀의 숫자 자릿수 제한, countif 오류 해결 방법

 

위 그림에서와 같이 22자리 텍스트 숫자의 중간이나 끝부분에 "_"를 추가하면 더 이상 숫자의 성격을 갖지 못한 "텍스트"가 되면서, 15자리 한계를 벗어나게 된다.

 

"_" 말고 문자라면 뭐든지 추가해도 된다.

 

 

 

또 다른 엑셀의 한계가 궁금하다.

 

2020.07.30. 코리.