Zaokrąglanie – excelowe dziwactwa i niekonsekwencje

by Marcin

Zaokrąglanie – excelowe dziwactwa i niekonsekwencje

Jednym z „dziwactw” Excela, które może powodować nieco zamieszania, jeżeli się o nim nie wie, jest stosowanie przez program dwóch różnych algorytmów zaokrąglania liczb.

Konsekwencje istnienia „dziwactwa” na rysunku poniżej. Wszystko, co zrobiłem to zaokrągliłem każdą z liczb, a następnie zsumowałem. W pierwszym przypadku (kolumna I) zaokrąglałem przy pomocy excelowskiej funkcji ZAOKR, w drugim (kolumna J) przy wykorzystaniu napisanej w VBA funkcji użytkownika, która jednak nie robi nic innego jak tylko zaokrągla wskazaną liczbę, ale przy użyciu dostępnej z poziomy VBA funkcji Round(). Odpowiedni kod (dla bardzo dociekliwych) poniżej:

Public Function ZAOKR_MOJE(ByVal  liczba As Double, ByVal dokladnosc As Integer) As double
ZAOKR_MOJE = Round(liczba, dokladnosc)
End Function

Zaokrąglanie – excelowe dziwactwa i niekonsekwencje

Jak widzisz na rysunku, wyniki zaokrąglania (oraz sumy) SĄ ZUPEŁNIE RÓŻNE !

Dlaczego ?

W funkcji ZAOKR, dostępnej z poziomu skoroszytu, zastosowano algorytm standardowy, w którym „połówki” (piątki) zaokrąglane są ZAWSZE w górę. Czyli 2,5 zostaje zaokrąglone do 3; 10,345 do 10,35 etc. Jest to dokładnie takie zachowanie, jakiego nauczyliśmy się w szkole i jakiego oczekiwałaby większość z nas.

W przypadku funkcji dostępnych w VBA (oprócz Round() są to także CByte(), CInt(), CLng() oraz CCur()) zastosowano algorytm zwany gaussowskim lub bankierskim. „Połówki” zaokraglane tu są raz w górę, a raz w dół, zawsze do najbliższej liczby parzystej. Dlatego 5,5, tak jak w przypadku funkcji skoroszytowej, zaokrąglone zostanie do 6. Zupełnie jednak inaczej VBA postąpi np. z liczbą 2,5 – zaokraglona ona zostanie do 2, a nie do 3 !

Czy algorytm bankierski jest zły ?

Samo zastosowanie algorytmu bankierskiego nie jest niczym niezwykłym, a tym bardziej złym. W zależności od zastosowań ma on bowiem swoje poważne zalety. Zaokrąglanie standardowe (zawsze w górę) powoduje kumulowanie błędu w przypadku wykonywania późniejszych operacji arytmetycznych na dużych zbiorach danych. Mały przykład poniżej:

Zaokrąglanie – excelowe dziwactwa i niekonsekwencje

Zrobiłem tu dokładnie to samo, co w pierwszym przypadku. Zaokrągliłem każdą z liczb, a następnie zsumowałem. Otrzymane sumy porównałem jednak z sumą, którą bym otrzymał gdybym nie zaokrąglał liczb. Jak widzisz algorytm bankierski zastosowany w VBA sprawia, że otrzymana suma jest o wiele bliższa sumie „oryginalnej”.Wszystko wynika z faktu, że algorytm bankierski, zaokrąglając raz w górę a raz w dół , „amortyzuje” błędy wynikające z zaokrąglania.

Niekonsekwencji ciąg dalszy

Można by się spodziewać, że w Excelu dokonano prostego podziału i funkcje „skoroszytowe” korzystają z jednego algorytmy, a funkcje VBA z drugiego. Byłoby to wciąż dziwne, ale przynajmniej miałoby jakąś wewnętrzną spójność. Niestety programiści Microsoft, z jakiegoś powodu, pozostali bardzo niekonsekwentni i np. w innych funkcjach VBA, takich jak FormatNumber czy Format (w trakcie formatowania liczb przy pomocy tych funkcji, w zależności od wybranego formatu, może także mieć miejsce zaokrąglanie) zastosowali algorytm standardowy.

Dlatego też funkcja VBA Round(2.5, 0) zwróci jako wynik 2, ale już funkcja FormatNumber(2.5, 0) jako wynik zwróci 3. I gdzie tu sens ?

Podsumowanie

Warto o tych różnicach pamiętać przygotowując swoje makra wykonujące operacje zaokrąglania na liczbach. Jak widzisz, wynik zwrócony przez makro może być całkowicie różny od tego jakiego się spodziewasz.

Jak już wspomniałem, nie jest tak, że jeden algorytm jest lepszy od drugiego. Każdy jest dobry w zależności od zastosowania. Algorytm standardowy daje wyniki, których „się spodziewamy”, w przeciwieństwie do algorytmu bankierskiego, który mimo wszystko zaskakuje nieco wynikami.

Szkoda, że te dwa algorytmy zostały zaimplementowane jednocześnie, w dodatku w dosyć chaotyczny sposób. Wydaje mi się, że bylibyśmy bliżej normalności gdyby funkcja VBA Round() zwracała taki sami wynik jak, pełniąca taką sama rolę, funkcja „skoroszytowa” ZAOKR (w angielskiej wersji excela przecież nawet nazywa się ona tak samo – ROUND).

Marcin

Download

{ 33 comments… read them below or add one }


Fatal error: Cannot assign by reference to overloaded object in /home/klient.dhosting.pl/antiquus/excelblog.pl/public_html/wp-content/themes/thesis_182/lib/classes/comments.php on line 176