엑셀 Rank 함수 오류

Question

최근 출시된 2010 버전을 비롯해 엑셀 모든 버전에서 Rank 함수 오류가 발생합니다. 이 오류는 심각한 것이지만, 널리 알려져 있지는 않은 것 같습니다.

오류를 재현하기 위해 아래와 같은 경우의 예를 들겠습니다. A1:D1 셀에 각기 아래와 같이 입력합니다.

0	48.4	=SUM(A1:B1)	=RANK(C1,$C$1:$C$2)
0.2	48.2	=SUM(A2:B2)	=RANK(C2,$C$1:$C$2)

 0 + 48.4와 0.2 +48.2 는 모두 48.4이므로 Rank함수의 결과가 1이 되어야 하나, 1과 2가 됩니다.

즉 아래와 같이 표시됩니다.

0	48.4	48.4	2
0.2	48.2	48.4	1

A1셀과 A2셀의 값을 각기

1, 1.2로 바꾸기 시작해 15와 15.2로 바꿀 때까지 모두 오류가 발생합니다. 16과 16.2는 정상적인 값을 반환합니다. 하지만, 1000000과 1000000.2를 입력하면 역시나 잘못된 값을 반환합니다.

음수의 경우에는 -1, 0.8의 경우나 -10000000, -9999999.8의 경우 모두 잘못된 결과를 반환합니다. -100000000와 -99999999.8의 경우는 올바른 결과를 되돌립니다. 

C열의 값을 사용자가 직접 입력하면 제대로 된 값을 물론 반환하지만, 사용자가 순위를 구하고자 하는 데이터가 수식을 이용해 결과값을 반환하는 경우 위와 같은 문제가 발생하는 것은 매우 치명적인 문제입니다.

이 경우 아래와 같이 sumproduct 함수나 countif 함수를 사용하면 해결할 수 있지만, 많은 사용자가 순위를 구할 때 Rank 함수를 이용하므로 순위를 구할 때 매우 조심해야 하는 문제가 발생하고 있습니다.

=SUMPRODUCT(N($C$1:$C$2>C1))+1

=COUNTIF($C$1:$C$2,">"& C1)+1

---

엑셀의 모든 것 - MagicSheet & 엑사모

Answer 1

안녕하십니까? latinum님

Microsoft Office의 Excel 사이트를 방문해 주셔서 감사합니다.

알려주신 Excel에서 Rank함수 사용시 문제가 나타나는 문제에 대한 유용한 정보 감사드립니다.

해당 문제는 부동소수점 때문에 나타나는 알려진 문제입니다.

해당 값들은 보이기에는 48.4로 보이나 실제로 소수점 아래에서 차이가 나기 때문에 발생하는 문제입니다.

해당 문제의 해결을 위해서는 =rounddown(sum(a1:b1),2) 이렇게 소수점 아래 내용을 버리면 정상적으로 사용하실 수 있습니다.

보다 자세한 내용은 참고 자료를 확인하시기 바랍니다.

[참고 자료]

http://support.microsoft.com/kb/125056/ko

http://support.microsoft.com/kb/78113/ko

http://office.microsoft.com/ko-kr/excel/HP052092931042.aspx?pid=CH062528291042

http://office.microsoft.com/ko-kr/excel/HP052090291042.aspx?pid=CH062528311042

보다 좋은 글이나 다른 궁금증이 있다면 또 게시해 주시면 감사하겠습니다.

Answer 2

안녕하세요?

말씀하신 것처럼 Rank 함수의 문제가 부동소수점 오류에 의해 발생하는 것은 맞습니다. 부동소수점 오류는 말씀하신 것처럼 알려진 문제입니다.

하지만 제가 제기하는 문제의 초점은 '일관성'입니다. 0.2+42.8을 엑셀 내부에서는 48.400000000000006라고 반환하지만 48.4로 표시되며, Len함수로 문자열 길이를 확인해도 4를 반환합니다. 셀서식을 변경해 소수점 자리를 표시하도록 해도 48.4를 반환합니다. 그러나, 실제 내부에 저장된 데이터는 48.400000000000006입니다. 이 값을 복사해, 선택하여 값으로 붙여넣기하더라도, 내부적으로 저장된 48.400000000000006라는 값을 가지고 있지만, 수식 입력줄에는 여전히 48.4라고만 표시되어 있습니다.(수식입력줄에 표시된 값이 실제값이라고 생각하는 일반 사용자의 입장에서 이는 매우 심각한 오류입니다) F2키를 눌러 편집모드로 변경한 뒤 Enter를 눌러야만 실제로 48.4라는 데이터로 변경됩니다.

=EXACT(0.2+48.2,48.4)라는 수식의 결과값은 True를 반환하는 데에서 알 수 있듯이 =0.2+42.8이란 수식의 결과값이 엑셀 내장 함수마다 조금씩 다른 결과값을 가져오(거나 다르게 처리하)도록 설계되어 있는 것 같습니다. Rank 함수의 예는 부동소수점과 관련한 이런 일관성의 문제에 대한 해결을 요구하고 있다고 하겠습니다.

그럼...행운이 있으시길...!

---

엑셀의 모든 것 - MagicSheet & 엑사모

Answer 3

안녕하십니까? latinum 님

Microsoft Office의 Excel사이트를 방문해 주셔서 감사합니다.

해당 문의 사항에 대해서는 저희가 Office 사용 불편 사항을 기재하는 별도의 MSRM 사이트에 따로 등록하여 개선될 수 있도록 노력하겠습니다.

제가 문제에 대해 더 알아야 할 것이 있다면 응답 주시면 감사하겠습니다.

사용에 불편을 드려 죄송합니다.