W programie Excel, gdy wpisuję 22222.09482, wtedy widzę liczbę 22222.0948199999 na pasku formuły

Czy mógłbyś mi pomóc - ponieważ mam dziwną sytuację, gdy po wpisaniu liczby 22222.09482 w komórce widzę inną liczbę 22222.0948199999 na pasku formuły. Poniżej znajduje się migawka problemu.

Widzę to samo, gdy wprowadzam następujące liczby:

22222.09482
33333.09482
44444.09482
55555.09482

ale kiedy wprowadzę 11111.09482 i 66666.09482, 77777.09482 .. do 99999.09482, to pokażą się poprawnie. Nie jestem pewien, czy jest to związane z zaokrąglaniem? Nie ustawiłem żadnych profili zaokrąglania. Czy możesz mi pomóc w rozwiązaniu problemu.

To jest błąd.

Excel używa zwykłej reprezentacji podwójnej precyzji IEEE, zgodnie z innymi odpowiedziami. Jego precyzja wynosi 53 znaczące cyfry binarne, co odpowiada mniej więcej 16 cyfrom dziesiętnym.

Wyświetlenie pierwszych 15 cyfr po przecinku jest zawsze „bezpieczne” . W tym sensie, że każdą „przedstawioną” liczbę dziesiętną podaną 15 cyframi można bezpiecznie odróżnić od liczb uzyskanych przez zmianę 15. cyfry dziesiętnej na jedną. Na przykład 15-cyfrowe liczby:

22222.09481 99999
22222.09482 00000
22222.09482 00001

odwzorować na trzy odrębne liczby podwójnej precyzji. Żadne z tych trzech nie będzie „sąsiadami” w reprezentacji podwójnej precyzji, w tym konkretnym przypadku.

Tak więc mylące dwa pierwsze na ekranie użytkownika jest błędem programu Excel.

W rzeczywistości w tej dziedzinie (między 16384 a 32768) absolutna precyzja wynosi ^2–38 , a następujące liczby są reprezentatywne:

...
22222.09481 99998 96571 9714760780334472656250000
22222.09481 99999 00209 9502831697463989257812500 <-- the one closest to what Excel showed to the user
22222.09481 99999 03847 9290902614593505859375000
22222.09481 99999 07485 9078973531723022460937500
22222.09481 99999 11123 8867044448852539062500000
22222.09481 99999 14761 8655115365982055664062500
22222.09481 99999 18399 8443186283111572265625000
22222.09481 99999 22037 8231257200241088867187500
22222.09481 99999 25675 8019328117370605468750000
22222.09481 99999 29313 7807399034500122070312500
22222.09481 99999 32951 7595469951629638671875000
22222.09481 99999 36589 7383540868759155273437500
22222.09481 99999 40227 7171611785888671875000000
22222.09481 99999 43865 6959682703018188476562500
22222.09481 99999 47503 6747753620147705078125000
22222.09481 99999 51141 6535824537277221679687500
22222.09481 99999 54779 6323895454406738281250000
22222.09481 99999 58417 6111966371536254882812500
22222.09481 99999 62055 5900037288665771484375000
22222.09481 99999 65693 5688108205795288085937500
22222.09481 99999 69331 5476179122924804687500000
22222.09481 99999 72969 5264250040054321289062500
22222.09481 99999 76607 5052320957183837890625000
22222.09481 99999 80245 4840391874313354492187500
22222.09481 99999 83883 4628462791442871093750000
22222.09481 99999 87521 4416533708572387695312500
22222.09481 99999 91159 4204604625701904296875000
22222.09481 99999 94797 3992675542831420898437500
22222.09481 99999 98435 3780746459960937500000000 <-- the one closest to what the user types
22222.09482 00000 02073 3568817377090454101562500
22222.09482 00000 05711 3356888294219970703125000
22222.09482 00000 09349 3144959211349487304687500
22222.09482 00000 12987 2933030128479003906250000
22222.09482 00000 16625 2721101045608520507812500
22222.09482 00000 20263 2509171962738037109375000
22222.09482 00000 23901 2297242879867553710937500
22222.09482 00000 27539 2085313796997070312500000
22222.09482 00000 31177 1873384714126586914062500
22222.09482 00000 34815 1661455631256103515625000
22222.09482 00000 38453 1449526548385620117187500
22222.09482 00000 42091 1237597465515136718750000
22222.09482 00000 45729 1025668382644653320312500
22222.09482 00000 49367 0813739299774169921875000
22222.09482 00000 53005 0601810216903686523437500
22222.09482 00000 56643 0389881134033203125000000
22222.09482 00000 60281 0177952051162719726562500
22222.09482 00000 63918 9966022968292236328125000
22222.09482 00000 67556 9754093885421752929687500
22222.09482 00000 71194 9542164802551269531250000
22222.09482 00000 74832 9330235719680786132812500
22222.09482 00000 78470 9118306636810302734375000
22222.09482 00000 82108 8906377553939819335937500
22222.09482 00000 85746 8694448471069335937500000
22222.09482 00000 89384 8482519388198852539062500
22222.09482 00000 93022 8270590305328369140625000
22222.09482 00000 96660 8058661222457885742187500
22222.09482 00001 00298 7846732139587402343750000
...

Aby rozwinąć tę kwestię, spróbuj wpisać 22222.09482jedną komórkę, a 22222.0948199999drugą (pięć końcowych dziewiątek) w innej komórce. Excel powinien wybrać dwóch przedstawicieli IEEE wskazanych strzałką powyżej. I myślę, że tak, ponieważ można obliczyć różnicę tych dwóch komórek, aby uzyskać 9.82254E-11. Ale oba są pokazane w ten sam sposób.

Jeśli program Excel pokazał pierwsze 17 cyfr, pomocne byłoby wybranie dokładnie tego, jaki numer IEEE jest „pod” liczbą dziesiętną. W tym wypadku:

22222.0948199999 --> 22222.09481 99999 00
22222.09482      --> 22222.09481 99999 98

Ale wyświetlenie 15 cyfr zaokrąglonych w niewłaściwy sposób jest mylące i nieprzydatne.

Zanim ktokolwiek twierdzi, że jest to celowe, to dlaczego 8.7nie wykazuje tego samego zachowania? Najbliższy numer podwójnej precyzji 8.7to:

8.69999999999999 93

powinien więc wyglądać, 8.69999999999999jakby było to zamierzone. Ale tak nie jest.

Excel przechowuje liczby w binarnym 64-bitowym formacie zmiennoprzecinkowym IEEE 754 . Kluczem jest „przechowywanie” - zmiana z dziesiętnej na dwójkową ma miejsce za każdym razem, gdy liczba jest przechowywana, a nie tylko wtedy, gdy jest używana do obliczeń.

Fajny artykuł na ten temat znajduje się w artykule Understanding Floating Point Precision, czyli „Dlaczego Excel wydaje mi się pozornie błędne odpowiedzi?”

Możliwe jest stworzenie programu do obsługi arkuszy kalkulacyjnych, który obsługiwałby naprawdę duże liczby z wieloma cyframi znaczącymi. Ale to nie jest zbyt praktyczne. Program Excel mógł zostać zaprojektowany do korzystania z formatu dziesiętnego 128 IEEE 754 , który pozwala na zapisanie 34 cyfr dziesiętnych - to więcej niż wystarcza do zapisania 22222,09482. Zamiast tego wykorzystuje o wiele bardziej popularny format Double Precision binary64 , który ma 53 bity precyzji, czyli nieco mniej niż 16 cyfr. Chociaż może się wydawać, że to wystarczy dla liczby zawierającej tylko 10 cyfr, konwersja z dziesiętnej na binarną nieco komplikuje rzeczy - tzn. 2222209482 może być przechowywany w 100% poprawnie jako liczba binarna64, ale 22222.09482 nie może.

Należy pamiętać, że zazwyczaj arkusze kalkulacyjne są używane do danych finansowych, które zwykle nie wymagają tak wielu cyfr precyzji lub do modelowania „co jeśli” w różnych scenariuszach, w których nie jest wymagany bardzo wysoki poziom precyzji. Z pewnością istnieją inne narzędzia (i prawdopodobnie inne programy do obsługi arkuszy kalkulacyjnych, ale ostatnio nie szukałem), które albo domyślnie, albo przy specjalnych ustawieniach konfiguracji mogą używać większego formatu liczbowego, ale Excel nie jest jednym z nich.

Dla tych, którzy podkreślają, że LibreOffice radzi sobie z tym lepiej, wygląd może być mylący. Zobacz ten post, aby uzyskać więcej informacji. Wydaje się, że LibreOffice traktuje duże liczby nieco inaczej, ale ma tę samą podstawową 64-bitową reprezentację zmiennoprzecinkową z podobnymi problemami.

Podczas wykonywania obliczeń program Excel musi znaleźć dobrą wewnętrzną reprezentację binarną dla liczb, których używa. W twoim przypadku wykorzystuje liczbę zmiennoprzecinkową i faktycznie ten format danych ma (bardzo dobre) przybliżenie twojej liczby, ale nie ma ścisłego dopasowania. Jeśli więc nie powiesz wyraźnie Excelowi, którego formatu wyjściowego użyć, zrobi to „najlepiej”, uzyskując wynik, który jest bliższy wewnętrznie obliczonej wartości, ale nie jest dokładnie tym, co wpisujesz.

Żeby było jasne: zrozumienie, że wprowadzony tekst reprezentuje liczbę, a przekształcenie sekwencji cyfr na liczbę spełnia już definicję „obliczenia” z góry.

EDYTOWAĆ

Nie wyjaśniłem wystarczająco jasno, że uważam, że wybór użycia 64-bitowej reprezentacji zmiennoprzecinkowej jest naprawdę dobry: Excel nie jest narzędziem dla naukowców, w którym błąd zaokrąglenia w 11. cyfrze po przecinku powoduje duży wpływ, ale księgowi nie chcą, aby ich szybkość przetwarzania zmniejszyła się o milion razy, aby uwzględnić źródło niedokładnych obliczeń, które mogą przejawiać się w liczbach, których nigdy nie używają.

Jeśli użyjesz programu arkusza kalkulacyjnego do tego, do czego został zaprojektowany, i użyjesz jawnego formatowania wyjściowego, aby upewnić się, że te efekty nigdy nie znajdą się w widocznej sferze, nic ci nie będzie.

Kiedy wprowadzę 11111.09482 i 66666.09482, 77777.09482 .. do 99999.09482, to pokażą się poprawnie. Nie jestem pewien, czy jest to związane z zaokrąglaniem? Nie ustawiłem żadnych profili zaokrąglania. Czy możesz mi pomóc w rozwiązaniu problemu.

Niektóre liczby mogą być reprezentowane poprawnie, a niektóre nie.

Ustaw wyświetlaną precyzję odpowiednio do swoich obliczeń i użyj funkcji round () .

• Wyjaśnienie:

  • Wikipedia - „ Precyzja numeryczna w Microsoft Excel ”

  • Przewodnik po obliczeniach numerycznych Oracle - „ Co każdy informatyk powinien wiedzieć o arytmetyki zmiennoprzecinkowej ”

• Rozwiązanie:

  • Wsparcie Microsoft Office - „ Ustaw precyzję zaokrąglania ”:

  Często można zapobiec wpływowi błędów zaokrąglania zmiennoprzecinkowego na pracę, ustawiając opcję Precyzja jako wyświetlana przed zastosowaniem formatu liczbowego do danych. Ta opcja wymusza, aby wartość każdej liczby w arkuszu była zgodna z dokładnością wyświetlaną w arkuszu.

  1. Kliknij Plik> Opcje.
     W programie Excel 2007: Kliknij obraz przycisku Microsoft Office Button Obraz przycisku Office, a następnie kliknij Opcje programu Excel.
     

  2. Kliknij opcję Zaawansowane, a następnie w obszarze Podczas obliczania tego skoroszytu zaznacz pole wyboru Ustaw dokładność zgodnie z wyświetlaniem, a następnie kliknij przycisk OK.

  3. Kliknij OK.

  4. W arkuszu wybierz komórki, które chcesz sformatować.

  5. Na karcie Narzędzia główne kliknij przycisk uruchamiania okna dialogowego Obraz przycisku obok numeru.
     
     

  6. W polu Kategoria kliknij Liczba.

  7. W polu Miejsca dziesiętne wprowadź liczbę miejsc dziesiętnych, które chcesz wyświetlić.

  Wskazówka: Aby zminimalizować wszelkie skutki niedokładności arytmetycznej pamięci zmiennoprzecinkowej, możesz także użyć funkcji OKRĄGŁY, aby zaokrąglić liczby do liczby miejsc dziesiętnych wymaganej w obliczeniach.

• Journal of Accountancy - „ Wystąpiły błędy obliczeniowe programu Excel ”:

  Niektóre liczby nieparzyste tworzą powtarzające się dziesiętne liczby binarne, a gdy te powtarzające się cyfry są odcinane po 15 miejscach, liczba binarna nie przelicza się dokładnie na zamierzoną wartość liczbową. Na przykład we wszystkich wersjach programu Excel formuła 22.26 - 21.29 powinna dawać 0,97, ale zamiast tego daje 0,970000000000002. Spróbuj i pamiętaj, aby zwiększyć szerokość kolumny i miejsca dziesiętne, aby zobaczyć problem z obliczeniami.

  Takie błędy są zwykle uważane za nieistotne lub nieistotne, ponieważ rzadko przejawiają się w znaczące błędy obliczeniowe; niemniej jednak oto dwie miary, które można podjąć, aby wyeliminować potencjalne błędy zmiennoprzecinkowe:

  1. Funkcja ROUND. Użyj funkcji OKRĄGŁEJ programu Excel, aby zaokrąglić obliczone wartości do pożądanego miejsca po przecinku, eliminując w ten sposób możliwość wystąpienia 15-cyfrowych anomalii. Na przykład formuła = OKRĄGŁY (-21,29 + 22,26) dokładnie daje 0,97.

  2. Precyzja. Możesz włączyć opcję Precyzja wyświetlanego programu Excel, aby wymusić obcięcie wszystkich formuł i zaokrąglenie obliczonych wartości na podstawie widocznych cyfr.

  Aby włączyć tę opcję w programach Excel 2013, 2010 i 2007, wybierz Plik (lub Kula pakietu Office), Opcje (lub Opcje programu Excel), Zaawansowane, aw sekcji Podczas obliczania tego skoroszytu zaznacz pole Ustaw dokładność jako wyświetlane i następnie kliknij OK.

  W programach Excel 2003, 2002 i 2000 z menu Narzędzia wybierz Opcje, a na karcie Obliczenia w obszarze Opcje skoroszytu zaznacz pole Precyzja zgodnie z wyświetleniem, a następnie kliknij przycisk OK.

Jak zapewne wiesz, komputery wewnętrznie działają tylko przy użyciu zer i jedynek (inaczej bitów) i mają stałą liczbę bitów reprezentujących wartość (zwykle obecnie 64 bity). Oznacza to, że liczba różnych wartości, które można przedstawić, wynosi 2 do 64 potęgi. To ogromna liczba, jasne, ale liczba możliwych wartości jest skończona, więc nie wszystkie liczby mogą być reprezentowane. Kiedy napotka liczbę, której nie może dokładnie przedstawić, zostanie automatycznie zastąpiona przez najbliższą, którą może reprezentować. Właśnie to widzisz.

Komputery wykonują obliczenia matematyczne w trybie binarnym i prawie zawsze używają liczb zmiennoprzecinkowych dla wartości niecałkowitych. Jedyne wartości ułamkowe, które można dokładnie przedstawić w postaci zmiennoprzecinkowej, muszą być sumą pewnej kombinacji mocy ułamkowej o wartości 2 (1/2, 1/4, 1/8, 1/16, 1/32, ...) kończącej się przy zaprojektowanym limicie precyzji (zwykle 53 bity). Wartości te nie zawsze mają uporządkowaną lub dokładną reprezentację dziesiętną, i odwrotnie, nie wszystkie wartości ułamkowe, które można dokładnie przedstawić w postaci dziesiętnej, będą miały dokładną reprezentację w postaci binarnej. Na przykład: 0,1. Nie można go przedstawić jako sumę ułamkowych potęg 2, które nie trwają wiecznie.

Po wprowadzeniu wartości dziesiętnej do arkusza kalkulacyjnego zostanie ona przekonwertowana i zapisana w postaci binarnej, a przypadki, takie jak opisane, staną się najbliższym przybliżeniem, które można przedstawić w postaci binarnej. Po wyświetleniu jest konwertowany z powrotem na dziesiętny, ponownie wymagając przybliżenia, co może nie konwertować z powrotem dokładnie na tę samą reprezentację, którą wprowadziłeś.

Dlaczego 53 bity (dawać lub brać)? Ponieważ typowy standard do przechowywania zmiennoprzecinkowego „podwójnej precyzji” wykorzystuje 64 bity, w których występuje mantysa (zwana także znaczeniem), wskaźnik znaku i wykładnik potęgi. Wykładnik zwykle przypisuje się 10 bitów, znak zajmuje jeden, pozostawiając 53 dla mantysy. To jest do przechowywania. Obliczenia są zwykle wykonywane przy użyciu 80 bitów i zaokrąglane do tyłu.

Są sytuacje, w których komputery będą działać w bazie 10, szczególnie podczas pracy z wartościami pieniężnymi, w których artefakty zaokrąglania są niedopuszczalne.

Jak wielu powiedziało powyżej, jest to wewnętrzny błąd reprezentacji. Excel dokonał wyboru 64-bitowych liczb zmiennoprzecinkowych o podwójnej precyzji. To daje 2 ⁶⁴ możliwe wartości. Domena liczb rzeczywistych zawiera nieskończoną liczbę wartości, więc gdy spróbujesz użyć takiej, która nie może być reprezentowana przez Excel, użyje najbliższej, którą można przedstawić.

Widziałem komentarze mówiące, że przy nieskończonej pamięci można przedstawić dowolną liczbę rzeczywistą. To prawda, ale nie ma czegoś takiego jak „nieskończona pamięć”, więc jest to kwestia sporna. Inni stwierdzili, że Excel mógł użyć większej reprezentacji wewnętrznej, na przykład 128-bitowej. To prawda, ale okazuje się, że komputery lepiej wykonują operacje matematyczne na liczbach reprezentowanych przez liczbę bitów odpowiadającą wielkości magistrali procesora. Tak więc komputer 32-bitowy będzie najszybszy w operacjach matematycznych na liczbach 32-bitowych, a komputer 64-bitowy będzie najszybszy w operacjach matematycznych na liczbach 64-bitowych. Jeśli i kiedy będzie komputer 128-bitowy, możemy spodziewać się, że Excel przejdzie do reprezentacji liczby 128-bitowej. Zapewni to nadal bardzo duży, ale ograniczony zestaw liczb, które mogą być reprezentowane.

Jeśli obawiasz się, jak wyglądają liczby w arkuszu kalkulacyjnym, to zastosowanie określonej precyzji (liczby miejsc po przecinku) zapewni spójne wyniki. Jeśli martwisz się różnicą między liczbą, którą wpisujesz, a rzeczywistą liczbą przechowywaną przez program Excel, masz rację. Różnica jest prawdziwa, a błąd zostanie przeniesiony przez wszelkie wykonane obliczenia. Obawiam się, że utknąłeś z tym błędem. Jest to ograniczenie programu Excel, a nie błąd, jak niektórzy stwierdzili. Prawdopodobnie nie zmieni się to w najbliższym czasie, więc jeśli jest to dla Ciebie niedopuszczalne, sugeruję poszukać innej aplikacji do arkuszy kalkulacyjnych, która może przedstawiać liczby z większą precyzją. Pamiętaj jednak, że jeśli znajdziesz taką aplikację, ograniczenie nadal istnieje. Tylko rozmiar błędu jest inny.