Arytmetykę zmiennopozycyjną może dać niedokładnych wyników w programie Excel

Streszczenie

W tym artykule omówiono, jak program Microsoft Excel przechowuje i oblicza dane liczb zmiennoprzecinkowych. To może wpłynąć na wyniki niektórych liczb lub formuł z powodu obcięcie danych lub zaokrąglenie.

Omówienie

Program Microsoft Excel został zaprojektowany wokół ze specyfikacją IEEE 754 do określenia sposobu przechowuje i oblicza dane liczb zmiennoprzecinkowych. IEEE jest Institute of Electrical and Electronics Engineers, organ międzynarodowy, który, między innymi, określa standardy dotyczące oprogramowania i sprzętu komputerowego. Specyfikacja 754 jest bardzo powszechnie przyjętego specyfikacji, która opisuje liczb zmiennopozycyjnych powinny być przechowywane w binarnym komputera. Jest to popularny, ponieważ liczb zmiennoprzecinkowych mają być przechowywane w odpowiednią ilość miejsca i obliczeń występują stosunkowo szybko. 754 standard jest używany w jednostkach zmiennoprzecinkowych i przetwarzających dane liczbowe prawie wszystkich komputerowych mikroprocesory dzisiejszą, które implementują matematyczne na liczbach zmiennoprzecinkowych, w tym procesorów Intel, Motorola, Sun i MIPS.

Podczas przechowywania liczb, odpowiednia liczba binarna może reprezentować każdy numer lub ułamkową. Na przykład frakcji 1/10 może być reprezentowana w systemie dziesiętnym jako 0,1. Jednak ten sam numer w formacie binarnym staje się natomiast następujące okresową dziesiętny:
0001100110011100110011 (itd.)
To może być nieskończenie powtarzana. Ten numer nie może być reprezentowana w skończona (ograniczona) ilość miejsca. W związku z tym, liczba ta jest zaokrąglana w dół przy około - 2, 8E-17 podczas przechowywania.

Istnieją jednak pewne ograniczenia ze specyfikacją IEEE 754, które dzielą się na trzy ogólne kategorie:
  • Maksymalne i minimalne ograniczenia
  • Precyzja
  • Liczby binarne

Więcej informacji

Maksymalne i minimalne ograniczenia

Wszystkie komputery mają maksymalną i minimalną liczbę, która może być obsługiwane. Ponieważ skończoną liczbę bitów pamięci, w którym przechowywany jest numer, wynika że maksymalnej lub minimalnej liczby, które mogą być przechowywane również jest ograniczone. Dla programu Excel, maksymalna liczba, która może być przechowywana jest 1, 79769313486232e + 308 i minimalna liczba dodatnia, która może być przechowywana jest 2, 2250738585072e-308.

Przypadki, w których stosujemy IEEE 754

  • Niedopełnienie: Niedopełnienie występuje podczas generowania numeru jest zbyt mały, aby być reprezentowana. IEEE i Excel wynikiem jest 0 (z wyjątkiem, że IEEE ma pojęcie - 0, a program Excel nie obsługuje).
  • Przepełnienie: Przepełnienie występuje, gdy liczba jest zbyt duża, aby być reprezentowana. Program Excel używa własnej specjalnej reprezentacji w tym przypadku (#NUM!).

Przypadki, w których firma Microsoft nie są zgodne z IEEE 754

  • Liczby nieznormalizowane: Nieznormalizowana liczba jest wskazywany przez wykładnik 0. W takim przypadku cały numer jest przechowywany w mantysy i mantysy ma nie niejawną wiodącą cyfrę 1. W rezultacie utratę precyzji i im mniejsza liczba, większą dokładność jest tracone. Liczby małych koniec tego zakresu mają tylko jedną cyfrę precyzji.
    Przykład: Znormalizowana liczba ma niejawny 1 wiodące. Na przykład jeśli mantysy reprezentuje wartość 0011001, znormalizowane 10011001 ze względu 1 wiodące. Nieznormalizowana liczba nie ma niejawny jeden wiodących, więc w naszym przykładzie 0011001 Nieznormalizowana liczba pozostaje taki sam. W takim przypadku znormalizowana liczba zawiera osiem cyfr znaczących (10011001), podczas gdy liczba nieznormalizowane ma pięć cyfr znaczących (11001) z wiodącymi.

    Nieznormalizowane liczby są w zasadzie sposób obejścia problemu zezwalanie na liczby mniejsze niż normalny limit dolny mają być przechowywane. Microsoft nie implementuje tej opcjonalnej części specyfikacji, ponieważ liczby nieznormalizowane ze swej natury mają zmienną liczbę cyfr znaczących. Umożliwia to poważny błąd w obliczeniach.
  • Plus/minus nieskończoności: Nieskończoności występuje podczas dzielenia przez 0. Program Excel nie obsługuje nieskończoności, raczej daje wartość błędu #DIV/0! Błąd w tych przypadkach.
  • Nie nieliczbowych (NaN): NaN jest używana do reprezentowania nieprawidłowych operacji (takich jak dzielenie, nieliczby lub pierwiastek kwadratowy-1). Wartości NaN Zezwalanie programowi na nadal nieprawidłowej operacji. Program Excel zamiast natychmiast generuje błąd, taki jak #NUM! lub #DIV/0!.

Precyzja

Liczba zmiennoprzecinkowa jest przechowywany w formacie binarnym na trzy części, w zakresie 65-bitowe: znak, wykładnik i mantysy.
Bit 1 znakuWykładnik 11 bit1 Bit niejawny52 bity mantysy
Znak przechowuje znak liczby (dodatniej lub ujemnej), wykładnik potęgi potęgą liczby 2, do której numer podniesiony lub obniżony (maksymalna i minimalna moc 2 jest +1,023 i-1,022), i mantysy przechowuje rzeczywistą liczbą. Może być skończony obszar przechowywania mantysy ogranicza sąsiadujące liczby zmiennoprzecinkowe (precyzja).

Mantysy i wykładnik są przechowywane jako oddzielne składniki. W rezultacie maksymalna precyzja mogą się różnić w zależności od wielkości liczby (mantysy) manipulacji. W przypadku programu Excel program Excel może przechowywać liczby od 1, 79769313486232E308 do 2, 2250738585072e-308, to tylko to zrobić w ciągu z dokładnością do 15 cyfr. Ograniczenie to jest bezpośrednio w wyniku ściśle zgodnie ze specyfikacją IEEE 754 i nie jest to ograniczenie programu Excel. Ten poziom precyzji występuje w innymi programami arkuszy kalkulacyjnych.

Liczby zmiennoprzecinkowe są reprezentowane w następującej formie, jeżeli wykładnik jest wykładnikiem potęgi, binarne:
X = ułamek * 2 ^ (wykładnik - bias)
Ułamek jest znormalizowana część ułamkową liczby, znormalizowane ponieważ wykładnik potęgi jest ustawione tak, aby wiodącego bitu zawsze jest równy 1. Dzięki temu nie ma być przechowywana, a otrzymasz jeden bit większa dokładność. Dlatego istnieje dorozumianych bit. Jest to podobne do notacji naukowej, gdzie manipulować wykładnik w celu uzyskania jednej cyfry po lewej stronie przecinka dziesiętnego; z wyjątkiem w formacie binarnym, można zawsze manipulować wykładnik tak, że pierwszy bit ma wartość 1, ponieważ istnieją tylko 1s i 0s.

BiAS jest używana w celu uniknięcia konieczności przechowywania wykładników ujemnych wartości odchylenia. Jest obliczana dla pojedynczej precyzji liczb 127 i 1023 (dziesiętnie) dla liczb podwójnej precyzji. Program Excel przechowuje liczby z podwójną precyzją.

Przykład użycia bardzo dużych liczb

Wpisz do nowego skoroszytu:
   A1: 1.2E+200
B1: 1E+100
C1: =A1+B1
Wynik w komórce C1 wyniesie 1.2E + 200, taką samą wartość jak komórki A1. W rzeczywistości, jeśli porównanie komórki A1 i C1 za pomocą funkcji Jeżeli, na przykład lub(a1=C1), wynikiem będzie wartość TRUE. Jest to spowodowane przez specyfikację IEEE przechowywania tylko 15 cyfr znaczących. Aby można było przechowywać powyższych obliczeń, program Excel wymaga co najmniej 100 cyfr precyzji.

Przykład użycia bardzo małych liczb

Wpisz do nowego skoroszytu:
   A1: 0.000123456789012345
B1: 1
C1: =A1+B1
Wynik w komórce C1 będzie równy 1,00012345678901 zamiast 1,000123456789012345. Jest to spowodowane przez specyfikację IEEE przechowywania tylko 15 cyfr znaczących. Aby można było przechowywać powyższych obliczeń, program Excel wymaga co najmniej 19 cyfr precyzji.

Poprawianie błędów precyzji

Program Excel oferuje dwie podstawowe metody, aby skompensować błędy zaokrąglania: funkcja ZAOKR i Dokładność jak wyświetlono lub
Opcja Ustaw dokładność zgodnie z wyświetlaną skoroszyt.

Metoda 1: Funkcja ZAOKR

Przy użyciu poprzednich danych, poniższy przykład używa funkcji ZAOKR do wymuszenia numeru do pięciu cyfr. Dzięki temu można pomyślnie porównać wynik na inną wartość.
   A1: 1.2E+200
B1: 1E+100
C1: =ROUND(A1+B1,5)
Powoduje to 1.2E + 200.
   D1: =IF(C1=1.2E+200, TRUE, FALSE) 
Skutkuje to wartość TRUE.

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

W niektórych przypadkach, może mieć możliwość zapobieżenia błędów wynikających z pracy za pomocą opcji Dokładność jak wyświetlono zaokrąglania. Ta opcja wymusza wartości każdej liczby w arkuszu z wyświetlaną wartością. Aby włączyć tę opcję, wykonaj następujące kroki.



 
  1. W menu plik kliknij polecenie Opcje, a następnie kliknij kategorię Zaawansowane .
  2. W sekcji podczas obliczania w tym skoroszycie skoroszyt, który chcesz zaznaczyć, a następnie zaznacz pole wyboru Ustaw dokładność zgodnie z wyświetlaną .

Na przykład jeśli wybierzesz format liczbowy, który zawiera dwa miejsca po przecinku, a następnie włącz opcję Dokładność jak wyświetlono , wszystkie dokładność po przecinku zostaną utracone podczas zapisywania skoroszytu. Opcja ta dotyczy aktywnego skoroszytu, w tym wszystkie arkusze. Nie można cofnąć tej opcji i odzyskać utracone dane. Zaleca się zapisanie skoroszytu przed włączeniem tej opcji.

Powtarzające się liczb binarnych i obliczeń, które mają wyniki w pobliżu zera

Inny problem mylące, który wpływa na przechowywania liczb zmiennoprzecinkowych w formacie binarnym jest to kilka liczb, które są ograniczone, niepowtarzająca się liczby dziesiętne podstawie 10, są nieskończone, powtarzając liczb binarnych. Najbardziej typowym przykładem tego jest wartość 0,1 i jego odmiany. Chociaż numery te mogą reprezentować doskonale o podstawie 10, ten sam numer w formacie binarnym staje się następujące powtarzanej liczba dwójkowa po zapisaniu go w mantysy:
000110011001100110011 (itd.)
Ze specyfikacją IEEE 754 sprawia, że nie specjalne świadczenia dla dowolną liczbę. Przechowuje, co może w mantysy i obcina reszty. To powoduje błąd około - 2, 8E-17 lub 0,000000000000000028 po zapisaniu go.

Nie można przedstawić nawet wspólne ułamki dziesiętne, takie jak wartość dziesiętna 0,0001, dokładnie w formacie binarnym. (0,0001 jest powtarzanej frakcji binarne, który ma okres 104 bity). Jest to podobne do Dlaczego ułamek 1/3 nie może dokładnie reprezentowany w zapisie dziesiętnym (powtarzana wartość 0,33333333333333333333).

Na przykład rozważmy następujący prosty przykład w programie 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 0.999999999999996 jako dane wyjściowe. Mały błąd w celu reprezentowania 0,0001 w binarnym jest dodawany do sumy.

Przykład: Dodawanie liczb ujemnych

  1. Wpisz do nowego skoroszytu:
       A1: =(43.1-43.2)+1 
  2. Kliknij prawym przyciskiem myszy komórkę A1, a następnie kliknij przycisk Formatuj komórki. Na karcie liczby w obszarze kategoriikliknij polecenie Naukowy . Ustaw miejsc dziesiętnych do 15.
Zamiast wyświetlać 0,9 program Excel wyświetla wartość 0,899999999999999. (43,1-43,2) jest obliczana po raz pierwszy, 0,1 jest czasowo przechowywana i błąd związany z przechowywaniem -0,1 jest wprowadzany do obliczeń.

Przykład kiedy osiągnie wartość zero

  1. W programie Excel 95 lub starszym wprowadź następujące wartości w nowym skoroszycie:
       A1: =1.333+1.225-1.333-1.225 
  2. Prawo clickcell A1, a następnie kliknij polecenie Formatuj komórki. Na karcie liczby w obszarze kategoriikliknij polecenie Naukowy . Ustaw miejsc dziesiętnych do 15.
Zamiast wyświetlania 0, wyświetla Excel 95 - 2, 22044604925031e-16.

Program Excel 97, jednak wprowadzane optymalizacji, który próbuje rozwiązać tego problemu. Powinno spowodować operacji dodawania lub odejmowania w wartość o lub bardzo bliskie zeru, Excel 97 i później będą kompensować wszystkie błędy wprowadzane na skutek konwertowania argumentu do i z binary. W przykładzie powyżej w przypadku wykonywane w programie Excel 97 i później prawidłowo Wyświetla 0 lub 0.000000000000000e + 00 w notacji naukowej. Aby uzyskać więcej informacji, kliknij następujące numery artykułów w celu wyświetlenia tych artykułów z bazy wiedzy Microsoft Knowledge Base:
172911 podnoszenie 10 do potęgi bardzo dużych/bardzo małych niepoprawny wynik.
214373 podnoszenie 10 do potęgi bardzo dużych/bardzo małych niepoprawny wynik.
Aby uzyskać więcej informacji na temat liczby zmiennoprzecinkowe i ze specyfikacją IEEE 754 zobacz następujące witryny sieci Web World Wide:

Powiązane artykuły

Aby uzyskać więcej informacji dotyczących sposobu obejścia tych błędów kliknij następujący numer artykułu w celu wyświetlenia tego artykułu z bazy wiedzy Microsoft Knowledge Base:
Jak skorygować błędy zaokrąglania w arytmetykę zmiennopozycyjną 214118
Uwaga W tym artykule również stosuje się do programu Microsoft Excel dla komputerów Macintosh dla usługi Office 365.
Właściwości

Identyfikator artykułu: 78113 — ostatni przegląd: 16.02.2017 — zmiana: 2

Microsoft Excel 2010, Microsoft Excel 2013, Excel for Office 365, Microsoft Excel 2011 for Mac, Excel for Mac for Office 365

Opinia