Wyniki arytmetycznych obliczeń zmiennoprzecinkowych w programie Excel mogą być niedokładne

Tłumaczenia artykułów Tłumaczenia artykułów
Numer ID artykułu: 78113 - Zobacz jakich produktów dotyczą zawarte w tym artykule porady.
Ten artykuł został opublikowany wcześniej pod numerem PL78113
Rozwiń wszystko | Zwiń wszystko

Na tej stronie

Streszczenie

W tym artykule omówiono sposób przechowywania liczb zmiennoprzecinkowych i wykonywania obliczeń przy użyciu tych liczb przez program Microsoft Excel. Może to wpływać na wyniki uzyskane dla niektórych liczb lub formuł na skutek zaokrąglania i/lub obcinania danych.

Omówienie

Program Microsoft Excel został zaprojektowany zgodnie ze specyfikacją IEEE 754 dotyczącą przechowywania liczb zmiennoprzecinkowych i wykonywania obliczeń przy użyciu tych liczb. IEEE (Institute of Electrical and Electronics Engineers) jest organizacją międzynarodową, która między innymi określa standardy dotyczące oprogramowania i sprzętu komputerowego. Powszechnie przyjmowana specyfikacja 754 opisuje zalecany sposób przechowywania liczb zmiennoprzecinkowych na komputerze wykorzystującym system binarny. Jej popularność wynika z możliwości przechowywania liczb zmiennoprzecinkowych przy efektywnym wykorzystaniu miejsca i względnie szybkiego wykonywania obliczeń. Standard 754 jest używany w jednostkach zmiennoprzecinkowych i procesorach numerycznych w większości współczesnych mikroprocesorów komputerów osobistych, które implementują zasady arytmetyki zmiennoprzecinkowej, z procesorami Intel, Motorola, Sun i MIPS włącznie.

Podczas przechowywania liczb odpowiednia liczba binarna może reprezentować każdą liczbę (również ułamkową). Na przykład ułamek 1/10 można reprezentować w systemie liczb dziesiętnych jako 0,1. Ta sama liczba w formacie binarnym jest jednak ułamkiem okresowym
0001100110011100110011 itd.
i może być nieskończenie powtarzana. Ta liczba nie może być reprezentowana w skończonej (ograniczonej) ilości pamięci. Jest ona zatem zaokrąglana w dół o około -2,8E-17 podczas przechowywania.

Istnieją jednak pewne ograniczenia specyfikacji IEEE 754, które można podzielić na trzy ogólne kategorie:
  • ograniczenia wartości maksymalnej/minimalnej
  • precyzja
  • powtarzanie (okresowość) liczb binarnych

Więcej informacji

Ograniczenia wartości maksymalnej/minimalnej

Dla wszystkich komputerów określana jest liczba maksymalna i minimalna, która może być obsługiwana przez dany komputer. Liczba bitów pamięci używanej do przechowywania liczby jest skończona, dlatego maksymalna lub minimalna liczba, którą można przechowywać, jest również skończona. W przypadku programu Excel maksymalną liczbą, którą można przechowywać, jest 1,79769313486232E+308, a minimalna przechowywana liczba dodatnia to 2,2250738585072E-308.

Negatywne skutki stosowania specyfikacji IEEE 754

  • Niedopełnienie: Niedopełnienie występuje wówczas, gdy generowana jest liczba, która nie może być reprezentowana, ponieważ jest zbyt mała. W przypadku specyfikacji IEEE i programu Excel wynik jest równy 0 (jednak w specyfikacji IEEE uwzględniono koncepcję liczby -0, w przeciwieństwie do programu Excel).
  • Przepełnienie: Przepełnienie występuje wówczas, gdy liczba nie może być reprezentowana, ponieważ jest zbyt duża. Program Excel używa własnej specjalnej reprezentacji liczb, w tym wypadku (#LICZBA!).

Ograniczenia zakresu zastosowania specyfikacji IEEE 754

  • Liczby nieznormalizowane: Liczba nieznormalizowana jest zapisywana z wykorzystaniem wykładnika potęgi 0. W takim przypadku cała liczba jest przechowywana w mantysie bez niejawnej wiodącej cyfry 1. Rezultatem jest utrata precyzji, tym większa, im mniejsza jest liczba. W przypadku mniejszych liczb w tym zakresie precyzja jest tylko jednocyfrowa.
    Przykład: Znormalizowana liczba zawiera niejawną wiodącą cyfrę 1. Jeżeli na przykład mantysa reprezentuje wartość 0011001, znormalizowana wartość wynosi 10011001 ze względu na niejawną wiodącą cyfrę 1. Nieznormalizowana liczba nie zawiera niejawnej wiodącej cyfry 1, dlatego w przedstawionym przykładzie dla 0011001 uzyskujemy taką samą liczbę nieznormalizowaną. W tym wypadku znormalizowana liczba zawiera osiem cyfr znaczących (10011001), podczas gdy nieznormalizowana liczba zawiera pięć cyfr znaczących (11001) z nieznaczącymi wiodącymi cyframi zero.

    Liczby nieznormalizowane są przede wszystkim rozwiązaniem alternatywnym, umożliwiającym przechowywanie liczb mniejszych niż normalny limit dolny. Firma Microsoft nie implementuje tej opcjonalnej części specyfikacji, ponieważ liczby nieznormalizowane ze względu na ich charakter zawierają zmienną liczbę cyfr znaczących. Może to być przyczyną znacznego błędu w obliczeniach.
  • Nieskończoność dodatnia/ujemna: Nieskończoność występuje podczas dzielenia przez 0. Program Excel nie obsługuje koncepcji nieskończoności i w takich wypadkach zgłasza błąd #DZIEL/0!.
  • Koncepcja nieliczby (NaN): Koncepcja nieliczby jest używana do reprezentowania nieprawidłowych operacji (takich jak dzielenie lub odejmowanie wartości nieskończonych albo pierwiastek kwadratowy z -1). Koncepcja nieliczby umożliwia kontynuację programu po wykryciu nieprawidłowej operacji. Program Excel w takim wypadku natychmiast generuje błąd, taki jak #LICZBA! lub #DZIEL/0!

Precyzja

Liczba zmiennoprzecinkowa jest przechowywana w formacie binarnym w trzech częściach, w zakresie 65-bitowym: znak, wykładnik potęgi i mantysa.
Zwiń tę tabelęRozwiń tę tabelę
1 bit znaku11 bitów wykładnika potęgi1 bit niejawny52 bity mantysy
Bit znaku określa znak liczby (dodatnia lub ujemna), wykładnik potęgi określa potęgę 2, do której liczba jest podnoszona lub obniżana (maksymalna/minimalna potęga 2 to +1023 i -1022), a mantysa określa wartość liczby. Skończony obszar przechowywania mantysy ogranicza odstęp między sąsiednimi liczbami zmiennoprzecinkowymi (tzn. precyzję).

Mantysa i wykładnik potęgi są przechowywane jako oddzielne składniki. Maksymalna precyzja jest zatem zależna od manipulowanej wartości liczby (mantysy). Program Excel może przechowywać liczby z zakresu od 1,79769313486232E308 do 2,2250738585072E-308, jednak zachowuje tylko precyzję 15-cyfrową. To ograniczenie jest bezpośrednim rezultatem ścisłego przestrzegania specyfikacji IEEE 754 i nie jest związane z programem Excel. Ten poziom precyzji występuje również w innych programach obsługujących arkusze kalkulacyjne.

Liczby zmiennoprzecinkowe są reprezentowane w następującym formacie, gdzie wykładnik potęgi jest binarnym wykładnikiem potęgi:
X = wartość ułamkowa * 2^(wykładnik potęgi - obciążenie)
wartość ułamkowa jest częścią ułamkową liczby znormalizowaną, ponieważ wykładnik potęgi jest zawsze dostosowywany w celu uzyskania wiodącego bitu równego 1. Nie musi zatem być przechowywany, a precyzja może być o jeden bit większa. Jest to przyczyną stosowania bitu niejawnego. Przypomina to notację matematyczną, w której manipulowany jest wykładnik potęgi w celu uzyskania jednej cyfry po lewej stronie przecinka dziesiętnego; w formacie binarnym można jednak zawsze manipulować wykładnikiem potęgi, tak aby pierwszy bit był równy 1, ponieważ dostępne są tylko cyfry 1 i 0.

Wartość obciążenia jest używana w celu uniknięcia konieczności przechowywania ujemnych wykładników potęgi. Obciążenie wynosi 127 dla liczb pojedynczej precyzji 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

Wprowadź następujące wartości w nowym skoroszycie:
   A1: 1,2E+200
   B1: 1E+100
   C1: =A1+B1
Wynik w komórce C1 będzie równy 1,2E+200, podobnie jak w komórce A1. W rzeczywistości wynik porównania komórek A1 i C1 przy użyciu funkcji LUB, na przykład LUB(A1=C1), będzie równy PRAWDA. Przyczyną jest przechowywanie tylko 15 cyfr znaczących, określających precyzję zgodnie ze specyfikacją IEEE. Aby umożliwić przechowywanie powyższych obliczeń, program Excel wymaga co najmniej precyzji 100-cyfrowej.

Przykład użycia bardzo małych liczb

Wprowadź następujące wartości w nowym skoroszycie:
   A1: 0,000123456789012345
   B1: 1
   C1: =A1+B1
Wynik w komórce C1 będzie równy 1,00012345678901 zamiast 1,000123456789012345. Przyczyną jest przechowywanie tylko 15 cyfr znaczących, określających precyzję zgodnie ze specyfikacją IEEE. Aby umożliwić przechowywanie powyższych obliczeń, program Excel wymaga co najmniej precyzji 19-cyfrowej.

Korygowanie błędów związanych z precyzją

Program Excel oferuje dwie podstawowe metody kompensacji błędów związanych z zaokrąglaniem: funkcja ZAOKR i opcje skoroszytu Dokładność jak wyświetlono lub Ustaw dokładność zgodnie z wyświetlaną.

Metoda 1: Funkcja ZAOKR

W następującym przykładzie wykorzystano powyższe dane i funkcję ZAOKR do wymuszenia wyświetlania pięciu cyfr w liczbie. Umożliwia to pomyślne porównanie wyniku z inną wartością.
   A1: 1,2E+200
   B1: 1E+100
   C1: =ZAOKR(A1+B1,5)
Rezultatem jest wyświetlenie wartości 1,00012.
   D1: =JEŻELI(C1=1,00012; PRAWDA; FAŁSZ)
Rezultatem jest wyświetlenie wartości PRAWDA.

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

W niektórych wypadkach można wyeliminować wpływ błędów związanych z zaokrąglaniem na dane użytkownika przy użyciu opcji Dokładność jak wyświetlono. Ta opcja wymusza zgodność każdej liczby przechowywanej w arkuszu z wyświetlaną wartością. Aby włączyć tę opcję, wykonaj następujące kroki:
  1. W programie Excel 2003 i starszych wersjach programu Excel kliknij polecenie Opcje w menu Narzędzia.
  2. Na karcie Przeliczanie kliknij, aby zaznaczyć pole wyboru Dokładność jak wyświetlono.
  1. W programie Excel 2007 kliknij przycisk Microsoft Office, kliknij łącze Opcje programu Excel, a następnie kliknij kategorię Zaawansowane.
  2. W sekcji Podczas obliczania w tym skoroszycie wybierz żądany skoroszyt, a następnie zaznacz pole wyboru Ustaw dokładność zgodnie z wyświetlaną.
Jeżeli na przykład zostanie wybrany format liczby umożliwiający wyświetlanie dwóch miejsc dziesiętnych, a następnie zostanie włączona opcja Dokładność jak wyświetlono, cała dokładność przekraczająca dwa miejsca dziesiętne zostanie utracona podczas zapisywania skoroszytu. Ta opcja dotyczy aktywnego skoroszytu ze wszystkimi arkuszami włącznie. Nie można cofnąć tej opcji i odzyskać utraconych danych. Zalecane jest zapisanie skoroszytu przed włączeniem tej opcji.

Powtarzanie (okresowość) liczb binarnych i obliczenia z wynikami zbliżonymi do wartości zero

Innym uciążliwym problemem związanym z przechowywaniem liczb zmiennoprzecinkowych w formacie binarnym jest fakt, że niektóre liczby, które są skończonymi ułamkami nieokresowymi w przypadku podstawy dziesiętnej 10, są nieskończonymi ułamkami okresowymi w formacie binarnym. Najbardziej powszechnym przykładem jest wartość 0,1 i jej odmiany. Te liczby mogą być optymalnie reprezentowane przy użyciu podstawy 10, jednak w formacie binarnym są następującą binarną liczbą okresową w przypadku zapisania w mantysie:
000110011001100110011 itd.
Specyfikacja IEEE 754 nie wyróżnia żadnych liczb; wszystkie liczby są zapisywane zgodnie z możliwościami w mantysie, a pozostałość jest obcinana. Powoduje to błąd wynoszący około -2,8E-17 lub 0,000000000000000028 przy zapisywaniu.

Nawet powszechnie występujące ułamki dziesiętne, takie jak wartość dziesiętna 0,0001, nie mogą być precyzyjnie reprezentowane w formacie binarnym. (0,0001 jest okresowym ułamkiem binarnym z okresem 104 bitów). Podobne ograniczenie obowiązuje w przypadku ułamka 1/3, którego nie można precyzyjnie reprezentować w formacie dziesiętnym (powtarzana wartość 0,33333333333333333333).

Wyjaśnia to, dlaczego prosty przykład kodu języka Microsoft Visual Basic for Applications
   Sub Main()
      MojaSuma = 0
      For I% = 1 To 10000
         MojaSuma = MojaSuma + 0,0001
      Next I%
      Debug.Print MySum
   End Sub
powoduje wyświetlenie wyniku 0,999999999999996. Niewielki błąd w reprezentowaniu wartości 0,0001 w formacie binarnym jest dodawany do sumy.

Przykład dodawania liczby ujemnej

  1. Wprowadź następujące wartości w nowym skoroszycie:
       A1: =(43,1-43,2)+1
  2. Kliknij prawym przyciskiem myszy komórkę A1, a następnie kliknij polecenie Formatuj komórki. Na karcie Liczby w obszarze Kategoria kliknij pozycję Naukowe. Ustaw wartość Miejsca dziesiętne równą 15.
Zamiast wartości 0,9 program Excel wyświetla wartość 0,899999999999999. Wartość (43,1-43,2) jest obliczana w pierwszej kolejności, dlatego wartość -0,1 jest przechowywana tymczasowo, a błąd związany z przechowywaniem wartości -0,1 jest wprowadzany do obliczeń.

Przykład wartości zbliżonej do zera

  1. W programie Excel 95 lub starszej wersji programu Excel wprowadź następujące wartości w nowym skoroszycie:
       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 Liczby w obszarze Kategoria kliknij pozycję Naukowe. Ustaw wartość Miejsca dziesiętne równą 15.
Zamiast wartości 0 program Excel 95 wyświetla wartość -2,22044604925031E-16.

Program Excel 97 optymalizowano jednak w celu skorygowania tego problemu. Jeżeli wynik operacji dodawania lub odejmowania jest równy lub zbliżony do wartości zero, program Excel 97 i nowsze wersje programu Excel będą kompensować wszystkie błędy wprowadzane na skutek konwertowania argumentu operacji do/z formatu binarnego. W powyższym przykładzie w przypadku programu Excel 97 i nowszych wersji programu Excel prawidłowo wyświetlana jest wartość 0 lub 0,000000000000000E+00 w notacji matematycznej. 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 Incorrect result raising 10 to very large/very small power
214373 Incorrect result raising 10 to very large/very small power
Aby uzyskać więcej informacji dotyczących liczb zmiennoprzecinkowych i specyfikacji IEEE 754, odwiedź następujące witryny w sieci Web:
http://www.ieee.org

http://stevehollasch.com/cgindex/coding/ieeefloat.html

Materiały referencyjne

Aby uzyskać więcej informacji dotyczących sposobu obejścia tych problemów, kliknij następujący numer artykułu w celu wyświetlenia tego artykułu z bazy wiedzy Microsoft Knowledge Base:
214118 How to correct rounding errors in floating-point arithmetic

Właściwości

Numer ID artykułu: 78113 - Ostatnia weryfikacja: 13 maja 2010 - Weryfikacja: 7.0
Informacje zawarte w tym artykule dotyczą:
  • Microsoft Office Excel 2007
  • Microsoft Office Excel 2003
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Excel 97 Standard Edition
  • Microsoft Excel 95 Standard Edition
  • Microsoft Excel 2004 for Mac
  • Microsoft Excel X for Mac
  • Microsoft Excel 2001 for Mac
  • Microsoft Excel 98 for Macintosh
Słowa kluczowe: 
kbinfo KB78113

Przekaż opinię

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com