Wystąpił brzydki błąd obliczeniowy, który pojawił się w programie Excel. Wygląda na to, że problem wnika głęboko w silnik obliczeniowy programu Excel i nie będzie łatwy do naprawienia.
U podstaw problemu leży prosty fakt: Excel przechowuje 15 cyfr dokładności w jednej komórce. Możesz mieć liczby składające się z 20 cyfr, ale wszelkie cyfry między ostatnią znaczącą cyfrą a miejscem dziesiętnym muszą mieć wartość zero.
Widziałem ostatnio dwa przypadki, w których silnik obliczeniowy programu Excel zwracał nieprawidłowe wyniki. Kiedy zagłębiłem się w problem i spojrzałem na podstawowy plik XML, byłem zaskoczony, widząc, że program Excel potajemnie przechowuje 17 cyfr w XML.
Problem polega na tym, że Excel wyświetla tylko 15 cyfr. Więc myślisz, że masz numer zapisany jako 0,123456789012345, ale tak naprawdę jest zapisany jako 0,12345678901234567.
Nie możesz zobaczyć tych dwóch ostatnich cyfr. Większość funkcji programu Excel ignoruje te dwie ostatnie cyfry. Gdyby * wszystkie * funkcje ignorowały dwie ostatnie cyfry, nie mielibyśmy problemu. Ale jak dotąd sortowanie, RANK i FREQUENCY używają wszystkich 17 cyfr.
Poniżej znajduje się dobrze znana sztuczka dotycząca rankingu komórek. Jeśli chcesz, aby każda ranga pojawiała się dokładnie raz, możesz połączyć RANK i COUNTIF. Na poniższym obrazku Claire, Flo, Ivana i Lucy mają remis na 115%. Stosując formułę POZYCJA + LICZ.JEŻELI, powinny one zająć pozycje 5, 6, 7 i 8.
Ale formuła zawodzi. Dwa wiersze są klasyfikowane jako 7. To się nigdy nie zdarza. Cztery formuły w kolumnie D zapewniają, że 115% w B6, B9, B12 i B15 jest takie samo. =B6=B15Formuła donosi, że obie komórki zawierają te same dane.
Kiedy próbowałem wyodrębnić problem, spójrz tylko na funkcję RANK. Powinien zgłaszać 4-punktowy remis na 4 miejscu dla osób ze 115%. Ale jakoś Lucy w rzędzie 15 wyprzedza pozostałą trójkę.
Aby to rozgryźć, wysłałem prośbę o pomoc do innych MVP Excela. Jan Karel Pieterse otworzył plik Excela i przejrzał XML. W XML widać, że przechowują 17 cyfr precyzji. Cztery komórki, które wyglądają jak remis w programie Excel, nie są powiązane w kodzie XML. Jeden ze 115% jest przechowywany jako 1,1500000000000001, a pozostałe to 1,1499999999999999.
Jak dotąd, sortowanie, ranking i funkcja FREQUENCY używają dodatkowych cyfr. Dlaczego to jest problemem? Ponieważ liczymy, że RANK i COUNTIF będą używać tej samej liczby cyfr. Gdy jedna funkcja używa 15 cyfr, a druga 17 cyfr, masz problem.
Na razie wydaje się, że rozwiązaniem jest konwersja wszystkich odpowiedzi za pomocą =ROUND(A4,15).
W każdy piątek sprawdzam błąd lub inne podejrzane zachowanie w programie Excel. Ten błąd obliczeniowy jest trudny do wykrycia i kwalifikuje się jako duża ryba.
Myśl dnia Excela
Poprosiłem moich znajomych z programu Excel Master o radę dotyczącą programu Excel. Dzisiejsza myśl do rozważenia:
„Za każdym razem, gdy scalasz komórki, mordujesz kotka”
Szilvia Juhasz
