Arytmetyka liczby zmiennoprzecinkowej może dać nieprawidłowe wyniki w aplikacji Excel

Podsumowanie

W tym artykule omówiono sposób w jaki aplikacja Microsoft Excel przechowuje i dokonuje obliczeń na liczbach zmiennoprzecinkowych. Może to mieć wpływ na wyniki niektórych liczb lub formuł z powodu zaokrąglania lub obcięcia danych.

Omówienie

Aplikacja Microsoft Excel działa zgodnie ze specyfikacją IEEE 754 dotyczącą sposobu przechowywania i dokonywania obliczeń na liczbach zmiennoprzecinkowych. IEEE to Instytut Inżynierów Elektrycznych i Elektroniki, międzynarodowa organizacja, która określa między innymi standardy dotyczące oprogramowania komputerowego i sprzętu. Specyfikacja "754" to bardzo szeroko przyjęta specyfikacja opisująca sposób przechowywania liczb zmiennoprzecinkowych na komputerze binarnym. Jest to popularne, ponieważ umożliwia stosunkowo szybkie obliczania na liczbach zmiennoprzecinkowych i ich przechowywanie w rozsądnej ilości miejsca. Standard "754" jest używany w jednostkach zmiennoprzecinkowych i numerycznych procesorach danych niemal wszystkich dzisiejszych mikroprocesorów w komputerach osobistych, które implementują procesory matematyczne oparte na liczbach zmiennoprzecinkowych, w tym procesory firm Intel, Motorola, Sun i MIPS.

Gdy liczby są przechowywane, odpowiadająca im liczba dwójkowa może reprezentować każdą liczbę lub liczbę ułamkową. Na przykład ułamek 1/10 może być reprezentowany w systemie liczb dziesiętnych jako 0,1. Jednak ta sama liczba w formacie binarnym staje się następującą nieskończoną postacią dziesiętną:

0001100110011100110011 (i tak dalej)

To może się powtarzać w nieskończoność. Ta liczba nie może być reprezentowana przez skończoną (ograniczoną) ilość miejsca. Dlatego ta liczba jest zaokrąglana w dół o około -2,8E-17, gdy jest przechowywana.

Istnieją jednak pewne ograniczenia specyfikacji IEEE 754 podzielone na trzy ogólne kategorie:

• Ograniczenia maksymalne/minimalne
• Dokładność
• Nieskończone liczby binarne

Więcej informacji

Ograniczenia maksymalne/minimalne

Wszystkie komputery mają maksymalną i minimalną liczbę, którą mogą przetworzyć. Ponieważ liczba bitów pamięci, w której liczba jest przechowywana, jest skończona, oznacza to, że maksymalna lub minimalna liczba, która może być przechowywana, również jest skończona. W aplikacji Excel maksymalna liczba, która może być przechowywana, to 1,79769313486232E+308, a minimalna przechowywana liczba dodatnia to 2,2250738585072E-308.

Przypadki, w których przestrzegamy standardu IEEE 754

• Niedopełnienie: Niedopełnienie występuje, gdy jest generowana liczba, która jest za mała, aby można było ją reprezentować. W przypadku standardu IEEE i aplikacji Excel wynikiem jest 0 (z wyjątkiem tego, że standard IEEE ma koncepcję liczby -0, a aplikacja Excel nie).
• Przepełnienie: Przepełnienie występuje, gdy liczba jest zbyt duża, aby można było ją reprezentować. Aplikacja Excel korzysta z własnej specjalnej reprezentacji w tym przypadku (#NUM!).

Przypadki, w których nie przestrzegamy specyfikacji IEEE 754

• Liczby nieznormalizowane: Liczba nienormalizowana jest wskazywana przez wykładnik 0. W takim przypadku cała liczba jest przechowywana w mantysie, a mantysa nie ma niejawnej wiodącej liczby 1. W wyniku tego tracona jest dokładność, a im mniejsza jest liczba, tym większa precyzja jest utracona. Liczby na małym końcu tego zakresu mają dokładność tylko co do jednej cyfry.

  Przykład: Liczba znormalizowana ma niejawną liczbę wiodącą 1. Jeśli na przykład mantysa reprezentuje 0011001, znormalizowana liczba staje się 10011001 z powodu dorozumianej liczby wiodącej 1. Liczba nienormalizowana nie ma niejawnej wiodącej jedynki, więc w naszym przykładzie liczba nienormalizowana 0011001 pozostaje niezmieniona. W tym przypadku liczba znormalizowana ma osiem cyfr znaczących (10011001), podczas gdy liczba nienormalizowana ma pięć cyfr znaczących (11001), których zera wiodące będą nieznaczne.

  Liczby nienormalizowane to po prostu obejście umożliwiające przechowywania liczb mniejszych od normalnego dolnego limitu. Firma Microsoft nie implementuje tej opcjonalnej części specyfikacji, ponieważ liczby nienormalizowane ze względu na swój charakter mają zmienną liczbę cyfr znaczących. Może to spowodować wprowadzanie do obliczeń istotnych błędów.

• Dodatnie/ujemne nieskończoności: nieskończoności występują w przypadku dzielenia przez 0. Aplikacja Excel nie obsługuje nieskończoności, a raczej zwraca błąd #DIV/0! w takich przypadkach.

• Błąd Not-a-Number (Nie jest liczbą — NaN): Błąd NaN jest używany do reprezentowania nieprawidłowych operacji (takich jak nieskończoność/nieskończoność, nieskończoność-nieskończoność lub pierwiastek kwadratowy liczby -1). Błędy NaN umożliwiają programowi kontynuowanie operacji po wystąpieniu nieprawidłowej operacji. Aplikacja Excel zamiast tego natychmiast generuje błąd, taki jak #NUM! lub #DIV/0!.

Dokładność

Liczba zmiennoprzecinkowa jest przechowywana w postaci binarnej w trzech częściach w zakresie 65-bitowym: znaku, wykładnika i mantysy.

Znak przechowuje znak liczby (dodatni lub ujemny), wykładnik przechowuje wielokrotność 2. potęgi, do której liczba jest podniesiona lub obniżona (maksymalna/minimalna potęga liczby 2 to +1.023 i -1.022), a mantysa przechowuje rzeczywistą liczbę. Skończony obszar magazynowania dla mantysy ogranicza, jak blisko dwie sąsiadujące ze sobą liczb zmiennoprzecinkowe mogą się znajdować (jest to reprezentowane przez dokładność).

Mantysa i wykładnik są przechowywane jako oddzielne składniki. W związku z tym możliwa dokładność może różnić się w zależności od rozmiaru liczby (mantysy) na której przeprowadzane są operacje. W przypadku aplikacji Excel, nawet jeśli ona przechowuje liczby od 1,79769313486232E308 do 2,2250738585072E-308, to jest to dokonane z dokładnością do 15 cyfr. To ograniczenie jest bezpośrednim wynikiem ścisłego przestrzegania specyfikacji IEEE 754 i nie jest ograniczeniem aplikacji Excel. Ten poziom dokładności można znaleźć również w innych aplikacjach arkuszy kalkulacyjnych.

Liczby zmiennoprzecinkowe są reprezentowane w następującej formie, gdzie wykładnik jest wykładnikiem binarnym:

X = ułamek * 2^(wykładnik — odchylenie)

Ułamek to znormalizowana część ułamkowa liczby, znormalizowana, ponieważ wykładnik jest dostosowywany tak, aby bit wiodący zawsze był liczbą 1. Dzięki temu nie trzeba go przechowywać i dodatkowo zapewniony jest jeden bit precyzji. Dlatego istnieje właśnie bit dorozumiany. Przypomina to notację naukową, w której wykładnik ma mieć jedną cyfrę po lewej stronie separatora dziesiętnego; z wyjątkiem danych binarnych, gdzie zawsze można manipulować wykładnikiem, aby pierwszy bit był cyfrą 1, ponieważ są tylko cyfry 1 i 0.

Odchylenie to wartość odchylenia, aby nie trzeba było przechowywać ujemnych wykładników. W przypadku liczb o pojedynczej liczbie dokładności ich odchylenie wynosi 127 i 1.023 (dziesiętne) dla liczb o podwójnej liczbie dokładności. Aplikacja Excel przechowuje liczby przy użyciu podwójnej liczby precyzji.

Przykład użycia bardzo dużych liczb

W nowym skoroszycie wprowadź następujące dane:

A1: 1.2E+200
B1: 1E+100
C1: =A1+B1 

Wynikiem jest wartość w komórce C1 i jest to 1,2E+200, która jest taką samą wartością jak ta w komórce A1. W rzeczywistości jeśli porównasz komórki A1 i C1 przy użyciu funkcji IF, na przykład IF(A1=C1), wynikiem będzie PRAWDA (TRUE). Jest to spowodowane specyfikacją IEEE przechowującą tylko 15 cyfr znaczących precyzji. Aby można było przechować powyższe obliczenie, aplikacja Excel potrzebowałaby co najmniej 100 cyfr dokładności.

Przykład użycia bardzo małych liczb

W nowym skoroszycie wprowadź następujące dane:

A1: 0.000123456789012345
B1: 1
C1: =A1+B1 

Wynikiem jest wartości w komórce C1 i jest to 1,00012345678901 zamiast 1,000123456789012345. Jest to spowodowane specyfikacją IEEE przechowującą tylko 15 cyfr znaczących precyzji. Aby można było przechować powyższe obliczenie, aplikacja Excel wymagałoby co najmniej 19 cyfr dokładności.

Poprawianie błędów dokładności

Aplikacja Excel oferuje dwie podstawowe metody kompensacji w przypadku błędów zaokrąglania: funkcję ROUND i opcje skoroszytu Dokładność jak wyświetlono lub Ustaw dokładność jak wyświetlono.

Metoda nr 1: Funkcja ROUND

W poniższym przykładzie (z zastosowaniem poprzednich danych) użyto funkcji ROUND w celu wymuszenia liczby składającej się z pięciu cyfr. Umożliwia to pomyślne porównanie wyniku z inną wartością.

A1: 1.2E+200
B1: 1E+100
C1: =ROUND(A1+B1,5) 

Wynik to 1,2E+200.

D1: =IF(C1=1.2E+200, TRUE, FALSE)

Powoduje to, że wartość wynosi TRUE (PRAWDA).

Metoda nr 2: Dokładność jak wyświetlono

W niektórych przypadkach można zapobiec wpływaniu błędów zaokrąglania na pracę za pomocą opcji Dokładność jak wyświetlono. Ta opcja wymusza, to że wartości każdej liczby w arkuszu są wyświetlonymi wartościami. Aby zmienić tę opcję, wykonaj następujące czynności.

1. W menu Plik kliknij polecenie Opcje, a następnie kliknij kategorię Zaawansowane.
2. W sekcji Podczas obliczania w tym skoroszycie wybierz wymagany skoroszyt, a następnie zaznacz pole wyboru Ustaw dokładność jak wyświetlono.

Jeśli na przykład wybierzesz format liczb zawierający dwa miejsca dziesiętne, a następnie włączysz opcję Dokładność jak wyświetlono, podczas zapisywania skoroszytu zostanie utracona cała dokładność wykraczająca poza dwa miejsca dziesiętne. Ta opcja ma wpływ na aktywny skoroszyt, łącznie ze wszystkimi jego arkuszami. Nie można cofnąć tej opcji i odzyskać utraconych danych. Zalecamy zapisanie skoroszytu przed włączeniem tej opcji.

Nieskończone liczby binarne i obliczenia, które mają wyniki bliskie zera

Innym powodującym zamieszanie problemem wpływającym na przechowywanie liczb zmiennoprzecinkowych w formacie binarnym jest to, że niektóre liczby, które są skończone i nie nieskończone o podstawie dziesiętnej 10, są liczbami nieskończonymi, niekończącymi się w postaci binarnej. Najpopularniejszym przykładem jest wartość 0,1 i jej odmiany. Liczby te mogą być przedstawiane idealnie przy podstawie 10, jednak ta sama liczba w formacie binarnym staje się następującą nieskończoną liczbą binarną, jeśli jest przechowywana w mantysie:

000110011001100110011 (i tak dalej)

Specyfikacja IEEE 754 nie zezwala na żadne wyjątki dla jakichkolwiek liczb. Przechowuje wszystko w mantysie, i obcina resztę. W wyniku tego następuje błąd o wartości -2,8E-17 lub 0,000000000000000028 podczas przechowywania.

Nawet typowe ułamki dziesiętne, takie jak 0,0001, nie mogą być dokładnie reprezentowane przez dane binarne. (0,0001 to nieskończony ułamek binarny, który ma okres 104 bitów). Jest to podobne do przyczyny, dla których ułamka 1/3 nie można dokładnie przedstawić w postaci dziesiętnej (jest to liczba nieskończona 0,33333333333333333333).

Rozważmy na przykład następujący prosty przykład w aplikacji Microsoft Visual Basic for Applications:

   Sub Main()
      MySum = 0
      For I% = 1 To 10000
         MySum = MySum + 0.0001
      Next I%
      Debug.Print MySum
   End Sub

Spowoduje to wydrukowanie liczby 0,999999999999996 jako wynik. Mały błąd reprezentujący 0,0001 w postaci binarnej ulega propagacji do sumy.

Przykład: Dodawanie liczby ujemnej

1. W nowym skoroszycie wprowadź następujące dane:

   A1: =(43.1-43.2)+1

2. Kliknij prawym przyciskiem myszy komórkę A1, a następnie kliknij polecenie Formatuj komórki. Na karcie "Liczba" kliknij pozycję "Naukowa" w obszarze "Kategoria". Ustaw opcję Miejsca dziesiętne na 15.

Zamiast wyświetlania wartości 0,9, aplikacja Excel wyświetli liczbę 0,899999999999999. Ponieważ (43,1-43,2) jest najpierw obliczana, wartość -0,1 jest przechowywana tymczasowo, a błąd z przechowywania liczby -0,1 jest wprowadzany do obliczeń.

Przykład, gdy wartość osiąga wartość zero

1. W aplikacji Excel 95 lub starszej wprowadź w nowym skoroszycie następujące dane:

   A1: =1.333+1.225-1.333-1.225

2. Kliknij prawym przyciskiem myszy komórkę A1, a następnie kliknij polecenie Formatuj komórki. Na karcie "Liczba" kliknij pozycję "Naukowa" w obszarze "Kategoria". Ustaw opcję Miejsca dziesiętne na 15.

Zamiast wyświetlenia wartości 0, aplikacja Excel 95 wyświetla liczbę -2,22044604925031E-16.

W aplikacji Excel 97 wprowadzono jednak optymalizację, która próbuje rozwiązać ten problem. Jeśli operacja dodawania lub odejmowania spowoduje, że wartość będzie równa lub bardzo bliska zeru, aplikacja Excel 97 lub jej późniejsze wersje będzie kompensować wszelkie błędy wprowadzone w wyniku przekonwertowania operandu na i z wartości binarnej. Powyższy przykład w przypadku wykonania go w aplikacji Excel 97 i jej nowszych wersjach poprawnie wyświetla w notacji naukowej 0 lub 0,000000000000000E+00.

Aby uzyskać więcej informacji na temat liczb zmiennoprzecinkowych i specyfikacji IEEE 754, zobacz następujące witryny internetowe:

• https://www.ieee.org
• https://steve.hollasch.net/cgindex/coding/ieeefloat.html