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
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:
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
{ 22 comments… read them below or add one }
Nie jestem bankierem ani matematykiem, ale algorytm bankierski polega w skrócie na tym, że jeżeli przed „piatką” jest liczba parzysta (lub zero) to zaokrąglamy liczbę w dół, a jak nieparzysta to w górę. Jest to według mnie o wiele lepszy algorytm, bo statystycznie rzecz ujmując o wiele lepiej się zachowuje przy większej ilości liczb, co sam powyżej udowodniłeś.
W czasach kiedy studiowałem, a jeszcze nie było PC-tów, taki właśnie sposób zaokrąglania obliczeń „inżynierskich” był od nas studentów wymagany.
Podzielam Twoją opinię, że za brak konsekwencji, MS powinien dostać MINUSA.
Cześć,
> (…) Jest to według mnie o wiele lepszy algorytm,
Tyle, że otrzymany wynik nie jest tym czego się można spodziewać. Pewien jestem, że większość osób (na pewno moja księgowa) zaokrąglając np. 35,545 do dwóch miejsc po przecinku oczekuje w wyniku 35,55 a nie 35,54. Algorytm bankierski nie jest gorszy (sam twierdzisz, że jest lepszy), jest po prostu bardziej …. niespodziewany
Wczoraj przeglądałem sobie swoje pierwsze „programy” w VBA i przypomniały mi się moje problemy z aplikacją wystawiająca faktury. Po prostu dawała mi błędne wyniki. Księgowa się wzburzyła, większość faktur musiałem poprawiać, ale najgorsze było to, że przez długi czas nie mogłem dojść do tego – dlaczego. Później, jednym z problemów (nie jedynym) okazało się zaokrąglanie i jego „niespodziewane” wyniki. Stąd właśnie ten tekst
Warto po prostu z opisanych różnic i niekonsekwencji zdawać sobie sprawę.
W ogóle dzięki Bodek za komentarz. Postaram się w przyszłości dostarczyć Ci więcej tematów skłaniających do podyskutowania.
Marcin
No cóż, jeżeli weźmiemy pod uwagę nasze intuicyjne oczekiwania, to jako, że 5 leży w „drugiej połówce” liczb od 0 do 9 (czyli 10-ciu cyfr), może się wydawać, że zaokrąglanie w takim przypadku w górę jest prawidłowe lub co najmniej oczekiwane.
Ale my nie mamy 10-ciu cyfr, tylko 9, bo jak mamy na końcu 0, to przecież nie zoakrąglamy.
Zatem
dla liczb 1,2,3,4 – w dół (4 liczby)
dla liczb 6,7,8,9 – w górę (też 4 liczby)
pozostaje 5 – dół/góra?
Przyjmując, że przy 5 zawsze zaokrąglamy w górę, mamy sytuację, w której w 5 na 9 przypadków wystąpienia dowolnej liczby (od 1 do 9) zaokrąglamy „do góry” a w 4 na 9 „na dół” – ewidentne zafałszowanie wyników.
Uwzględniając natomiast parzystość/nieparzystość poprzedzającej 5 liczby (zero traktujemy jako liczbę parzystą), mamy przed piątką juz 10 cyfr, czyli możliwy jest równy podział metod zaokrąglania, czyli statystycznie mamy rozkład 50 na 50%, ponieważ:
dla 0,2,4,6,8 – w dół
dla 1,3,5,7,9 – w górę
czyli 5 na 10 i 5 na 10 przypadków.
Zapewniam, że można się do tego przyzwyczaić i traktować właśnie taka metodę zaokrąglania jako zupełnie naturalną, kwestia przyzwyczajenia.
Odnośnie księgowej – albo miała program zaokrąglający jak Excel na formułach, albo sama robiła to ręcznie metodą „standardową”. Zapewniam natomiast, że jak wylosujesz np: 1000 lub wiecej cyfr z piątką na ostatnim miejscu i zaokrąglisz obiema metodami, to algorytm bankierski zawsze da sumę bliższą rzeczywistej sumie, bo to po prostu statystyka.
pozdrawiam i co złego to nie ja
Dopiszę jeszcze uwagę odnośnie zera, napisałem, że „nie zokrąglamy”, to znaczy spotkałem się z dwoma rodzajami zachowań. Niektórzy jak mają 0,5 to obcianją do zera (czyli w dół) inni zokrąglają w górę – w tym drugim przypadku jeszcze bardziej pogłębiają rozrzut i nieprawidłowośc wyników, ponieważ:
dla 1,2,3,4 – w dół (4 liczby)
dla 0,5,6,7,8,9 – w górę (6 liczb)
Co by było weselej trzeba pamiętać, że dwa algorytmy zaokrąglania to nie jedyny „problem” na jaki trzeba zwrócić uwagę pracując z liczbami w Excelu.
Spróbuj sobie Bodek wpisać do komórki liczbę 145,145 i następnie zaokrąglić ją do dwóch miejsc po przecinku przy pomocy funkcji VBA (algorytm bankierski). Zaokrągli Ci do 145,15 a nie 145,14 czego byś się spodziewał korzystając z algorytmu bankierskiego.
Tu z kolei dokładność „gubi” się prawdopodobnie gdzieś po drodze, w trakcie konwersji liczby na format binarny „obrabiany” przez komputer. Dodam, że dzieje sie tak kiedy zmienna przekazywana do funkcji VBA zdefiniowana jest jako Double. Kiedy zmienisz sobie na Currency, zaokrągla właściwie do 145,14. Ot – warto być czujnym
> Ale my nie mamy 10-ciu cyfr, tylko 9, bo jak mamy na końcu 0,
> to przecież nie zoakrąglamy.
zaokraglamy, zaokraglamy
trywialnie – do liczby rownej zaokraglanej
Troche nie rozumiem calego problemu… Moim zdaniem kwestia zaokraglania to tylko kwestia prezentacji danych. Rozumiem, ze kiedys nie bylo komputerow i zaokraglanie bylo potrzebne, bo w istotny sposob skracalo czas potrzebny na zsumowanie wielu liczb. Ale dzis… Dzis komputery sa w stanie zapamietac liczby z dokladnoscia do wielu miejsc po przecinku i dodawac je za nas… Wydaje mi sie w zwiazku z tym, ze najlepsze rozwiazanie to wykonywanie wszystkich operacji na „prawdziwych” liczbach i tylko prezentacja ostatecznych wynikow w zaokraglonej formie.
W komencie nr 4 od Bodka jest błąd logiczny:)
„…dla 1,2,3,4 – w dół (4 liczby)
dla 0,5,6,7,8,9 – w górę (6 liczb)”
Te cyfry (1,2, …,9) oznaczają przecież cyfrę po przecinku, przez którą zaokrąglimy w górę (gdy jest to 5-9) lub w dół (1-4), jak po przecinku jest 0 czyli 1,0 to jak możemy to zaokrąglic w górę? 1,0 =2?
O ile pamiętam jeszcze studia to jest norma na zaokrąglanie i działa ona tak jak algorytm który nazywacie bankierskim – po parzystych w jedną stronę a po nieparzystych w drugą.
Prawdopodobnie zwyczaje bankierskie są oparte właśnie na prawie wynikającym z PN.
Nie sądzę żeby miały osobne źródło w Prawie Bankowym.
Dlatego zaokrąglenia z „5″ zawsze w dół są raczej w Polsce „nielegalne”.
No to się zrobiła dyskusja, faktem jest natomiast, że posty pisałem jeszcze w stanie niedopicia porannej kawy, a admin nie przewidział opcji typu edycja komenta po kliknięciu, zatem jak je teraz czytam, to sam bym sie czepił swojego uzasadnienia.
ma oczywiście rację, moja poranna nadinterpretacja, sorki:)
Zapewniam natomiast, że nie będę umierał za bankierski lub jakiś tam inny algorytm zaokrąglania, a who (kom.
Jasiu skoro tak mówisz podaj mi nazwę komputera który zapamięta dla mnie liczbę Pi bez zaokrąglania jej… :>
Każdy sposób zaokrąglania jest „legalny” w zależności od zastosowań. Ba czasem zaokrąglenie 1,9 do 1 lub 1,1 do 2 jest również legalne nawet w finansach (vide nasze polskie PITy)
Witam!
Ja mam trochę inne pytanie co do tego arkusza. Jak zrobiłeś to, że arkusz kończy się na kolumnie „J” i wierszu nr 26 ??
>Jak zrobiłeś to, że arkusz kończy się na kolumnie “J” i wierszu nr 26 ??
Ukryłem pozostałe wiersze i kolumny
Marcin
W kolumnach z różnymi formułami powinne być także różne wyniki. Wiersze 9,11,12 zawierają takie same wyniki.
Czy tak powinno być?
>W kolumnach z różnymi formułami powinne być także różne wyniki
Niekoniecznie. W niektórych przypadkach zaokrąglanie z poziomy VBA i skoroszytu daje takie same wyniku. Wczytaj się jeszcze raz dokładnie w to co napisałem, a na pewno zrozumiesz. W razie problemów – pisz.
Marcin
Mam problem naczytałam się tego tu troche ale i tak dalej nie iwem co mam zrobic. Problem tkwi w tym: gdy w danej komórce wprowadzam formułę aby liczył przez % i wynik pokazywał do 2 miejsc po przecinku, owszem tak robi ale i tu pojawia się problem zliczając ręcznie poszczególne widoczne kwoty wychodzę na inne.Pojawiają się groszowe sprawy lecz gdy to mni sie kumuluje i takich sum niedokładnych mam dużo wynik odbiega od rzeczywistości. Gdy mam odwołania z jednej zakładki do drugiej i arkuszy błedy cdochodzą nawet do tys. Do tej pory takie kwoty wbijałam z ręki ale ile można?. Ponoć jest jakaś funkcji licz jak widzisz. Jets czy jej nie ma?? Poradzisz coś mi na to?
Bardzo ciekawe są Wasze wywody. Dużo można się nauczyć… Ale! Punkt widzenia zależy od punktu siedzenia. W księgowości przyjęta jest zasada, że jeśli coś zaokrąglamy, to od 5 w górę. Już w szkole nas uczulali na to! Chociaż niejednokrotnie chciałoby się inaczej. Jestem pewna, że stąd te niezgodności z księgowością.
Pozdrawiam.
A co powiecie na zaokrąglenia wielkich liczb w Excelu? Spróbujcie np. dodać do siebie:
1 000 000 000 000 000 000,25 + 1 000 000 000 000 000 000,20. W tym przypadku Excel nie wykona poprawnie prostego dodawania. Czekam na Wasze komentarze.
Odpowiadajac Patrycji:
„jest jakaś funkcji licz jak widzisz. Jets czy jej nie ma?? Poradzisz coś mi na to?”
Mozna sprobowac z funkcja zaokr(wartosc;ilosc miejsc po przecinku). Ang odpowiednik to round. Jako wartosc nalezy podac dzielenie jakie wykonujemy aby otrzymac %. W ten sposob Excel „zapomni” o pierwotnym dzieleniu i zapamieta liczbe tylko z iloscia miejsc po przecinku, ktora podamy w funkcji. Dalsze operacje na otrzymanych wynikach powinny byc zgodne z oczekiwaniami tak dlugo jak wyswietlana ilosc miejsc po przecinku bedzie rowna zdefiniowanej w funkcji.
Witam, tak, czytam te wszystkie komentarze i powiem ze w geodezji obowiazuje zaokraglanie w przypadku piatki zalezne od poprzedzajacej liczby, parzysta/nieparzysta? I doszedl ktos moze do takiej formuły ? Bo włączając opcje systemu bankierskiego (VBA) nie jest to wystarczajace, bo wiele razy sa potrzebne wyniki do 5 miejsc po przecinku. Prosze o komentarze.
Innymi słowy aby VBA liczył zaokrąglenia standardowo zamiast formuły
a= round(wartość, liczba cyfr) należy zastosować
a = format(wartość, „0.00″) jedyny kłopot to to że to tylko 2 miejsca po przecinku. Jak się do takiej formuły wpisze „0,000″ to jóż całkiem wychodzi bzdura.
{ 1 trackback }