Floating-Point aritmatika akan memberikan hasil yang tidak akurat dalam Excel

Ringkasan

Artikel ini membahas bagaimana Microsoft Excel menyimpan dan menghitung angka floating-point. Hal ini dapat mempengaruhi hasil dari beberapa nomor atau formula karena pemotongan mengumpulkan atau data.

Gambaran Umum

Microsoft Excel dirancang di sekitar spesifikasi IEEE 754 untuk menentukan cara menyimpan dan menghitung angka floating-point. IEEE adalah Institute listrik dan elektronik insinyur, badan internasional yang, antara lain, menentukan standar untuk perangkat lunak komputer dan perangkat keras. Spesifikasi 754 adalah spesifikasi sangat luas angkat yang menjelaskan bagaimana floating-point nomor akan disimpan di komputer biner. Hal ini populer karena memungkinkan angka floating-point akan disimpan dalam jumlah yang wajar ruang dan perhitungan terjadi relatif cepat. 754 standar digunakan di unit floating-point dan prosesor data numerik hampir semua kini PC berbasis mikroprosesor yang menerapkan floating-point matematika, termasuk prosesor Intel, Nokia, Sun dan MIPS.

Ketika angka disimpan, nomor biner dapat mewakili nomor atau nomor pecahan setiap. Sebagai contoh, pecahan 1/10 dapat ditunjukkan dalam sistem angka desimal sebagai 0.1. Namun, nomor yang sama dalam format biner menjadi berulang biner desimal berikut ini:
0001100110011100110011 (dan seterusnya)
Hal ini dapat jauh diulangi. Angka ini tidak dapat ditunjukkan dalam sejumlah ruang terbatas (terbatas). Oleh karena itu, angka ini bulat oleh kira-kira - 2.8E-17 saat disimpan.

Namun, ada beberapa batasan spesifikasi IEEE 754 yang jatuh ke dalam tiga kategori Umum:
  • Batasan maksimum minimum
  • Presisi
  • Berulang nomor biner

Informasi lebih lanjut

Batasan maksimum Minimum

Semua komputer memiliki maksimum dan angka minimum yang dapat ditangani. Karena jumlah bit memori disimpan nomor terbatas, maka jumlah maksimum atau minimum yang dapat disimpan juga terbatas. Untuk Excel, jumlah maksimum yang dapat disimpan 1.79769313486232E + 308 dan nomor positif minimum yang dapat disimpan 2.2250738585072E-308.

Kasus di mana kami mematuhi IEEE 754

  • Underflow: Underflow terjadi ketika angka yang dihasilkan yang terlalu kecil untuk diwakili. IEEE dan Excel, hasilnya adalah 0 (dengan pengecualian IEEE memiliki konsep - 0, dan Excel tidak).
  • Overflow: Overflow terjadi ketika angka terlalu besar untuk diwakili. Excel menggunakan representasi khusus untuk kasus ini (#NUM!).

Kasus di mana kami tidak mematuhi IEEE 754

  • Denormalized nomor: nomor denormalized ditunjukkan oleh eksponen 0. Dalam hal ini, nomor seluruh disimpan dalam mantissa dan mantissa memiliki awalan tidak implisit 1. Akibatnya, Anda kehilangan presisi, dan jumlah yang lebih kecil, presisi lain hilang. Angka di akhir kecil kisaran ini memiliki hanya satu digit presisi.
    Contoh: Nomor menormalkan memiliki 1 terkemuka implisit. Misalnya, jika mantissa mewakili 0011001, nomor menormalkan menjadi 10011001 karena 1 terkemuka tersirat. Nomor denormalized tidak memiliki salah satu terkemuka implisit sehingga kami contoh 0011001, nomor denormalized tetap sama. Dalam hal ini, nomor menormalkan memiliki signifikan delapan digit (10011001) sementara nomor denormalized memiliki signifikan lima digit (11001) dengan awalan nol yang signifikan.

    Nomor denormalized yang pada dasarnya penyelesaian untuk mengizinkan angka yang lebih kecil daripada batas normal akan disimpan. Microsoft tidak dapat menjalankan opsional bagian dari spesifikasi karena denormalized angka dengan sifatnya memiliki sejumlah variabel angka yang signifikan. Ini dapat memungkinkan signifikan galat untuk masuk ke dalam penghitungan.
  • Positif negatif Infinities: Infinities terjadi saat Anda membagi dengan 0. Excel tidak mendukung infinities, sebaliknya, memberikan #DIV/0! kesalahan dalam hal ini.
  • Tidak-a-nomor (NaN): NaN yang digunakan untuk mewakili operasi tidak valid (misalnya tanpa batas/tanpa batas, tanpa batas-tanpa batas, atau akar -1). NaNs memungkinkan program untuk melanjutkan masa operasi tidak valid. Excel alih-alih segera menghasilkan galat seperti #NUM! atau #DIV/0!.

Presisi

Floating-point nomor biner dalam tiga bagian berada dalam kisaran 65-bit: tanda, eksponen dan mantissa.
1 tanda Bit11 bit eksponen1 tersirat Bit52 bit Mantissa
Tanda toko tanda nomor (positif atau negatif), eksponen yang menyimpan daya 2 yang nomor dinaikkan atau diturunkan (daya maksimum minimum 2 adalah +1,023 dan-1,022), dan mantissa menyimpan jumlah aktual. Area penyimpanan terbatas untuk mantissa batas bagaimana tutup dua berdekatan terapung titik nomor dapat (yaitu, presisi).

Mantissa dan eksponen kedua disimpan secara terpisah. Sebagai hasilnya, jumlah presisi mungkin bervariasi tergantung pada ukuran angka (mantissa) dimanipulasi. Untuk Excel, meskipun Excel dapat menyimpan nomor 1.79769313486232E308-2.2250738585072E-308, ini hanya dapat melakukannya dalam 15 digit presisi. Pembatasan ini akibat langsung dari mengikuti spesifikasi IEEE 754 dan tidak dibatasi untuk Excel. Tingkat presisi ditemukan di program spreadsheet lainnya juga.

Angka Floating-Point ditunjukkan dalam bentuk berikut ini, di mana eksponen adalah eksponen biner:
X = pecahan * 2 ^ (eksponen - bias)
Pecahan adalah bagian pecahan menormalkan nomor normal karena eksponen yang disesuaikan sehingga bit terkemuka selalu 1. Dengan cara ini, itu tidak perlu disimpan, dan Anda mendapatkan lebih sedikit presisi. Itulah mengapa ada bit tersirat. Ini sama dengan notasi ilmiah, di mana Anda manipulasi eksponen memiliki satu angka di sebelah kiri desimal; kecuali biner, Anda dapat selalu manipulasi eksponen sehingga bit pertama adalah 1, karena ada hanya 1s dan 0s.

Bias adalah nilai bias digunakan untuk menghindari untuk menyimpan eksponen negatif. Bias untuk nomor presisi tunggal adalah 127 dan 1,023 (desimal) untuk nomor presisi ganda. Excel menyimpan angka menggunakan presisi ganda.

Contoh menggunakan angka-angka yang sangat besar

Masukkan berikut ini ke buku kerja baru:
   A1: 1.2E+200
B1: 1E+100
C1: =A1+B1
Nilai yang dihasilkan di sel C1 akan 1.2E + 200, nilai yang sama seperti sel A1. Bahkan jika Anda membandingkan sel A1 dan C1 menggunakan fungsi jika, misalnya IF(A1=C1), hasilnya akan benar. Hal ini disebabkan oleh spesifikasi IEEE menyimpan hanya 15 angka yang signifikan presisi. Agar dapat menyimpan perhitungan di atas, Excel akan memerlukan minimal 100 digit presisi.

Contoh menggunakan angka-angka yang sangat kecil

Masukkan berikut ini ke buku kerja baru:
   A1: 0.000123456789012345
B1: 1
C1: =A1+B1
Nilai yang dihasilkan di sel C1 akan 1.00012345678901 alih-alih 1.000123456789012345. Hal ini disebabkan oleh spesifikasi IEEE menyimpan hanya 15 angka yang signifikan presisi. Agar dapat menyimpan perhitungan di atas, Excel akan memerlukan setidaknya 19 digit presisi.

Memperbaiki galat presisi

Excel menawarkan dua metode dasar untuk mengganti mengumpulkan kesalahan: fungsi bulat dan tepat seperti ditampilkan atau
Opsi buku kerja ditetapkan presisi sesuai ditampilkan .

Metode 1: Fungsi bulat

Menggunakan data sebelumnya, contoh berikut menggunakan fungsi bulat untuk memaksa angka lima digit. Hal ini memungkinkan Anda berhasil membandingkan hasil ke nilai yang lain.
   A1: 1.2E+200
B1: 1E+100
C1: =ROUND(A1+B1,5)
Hal ini mengakibatkan 1.2E + 200.
   D1: =IF(C1=1.2E+200, TRUE, FALSE) 
Hal ini mengakibatkan nilai benar.

Metode 2: Presisi seperti ditampilkan

Dalam beberapa kasus, Anda dapat mencegah mengumpulkan kesalahan dari mempengaruhi pekerjaan Anda dengan menggunakan opsi presisi sebagai ditampilkan . Opsi ini memaksa nilai setiap angka di lembar kerja untuk nilai yang ditampilkan. Untuk mengaktifkan opsi ini, ikuti langkah-langkah berikut.



 
  1. Pada File menu, klik opsi, dan kemudian klik kategori lanjut .
  2. Di bagian ketika menghitung buku kerja ini , pilih buku kerja yang Anda inginkan, dan kemudian pilih kotak centang terletak tepat seperti ditampilkan .

Sebagai contoh, jika Anda memilih format angka yang menunjukkan dua angka desimal, dan kemudian Anda mengaktifkan opsi presisi sebagai ditampilkan , Semua akurasi luar dua angka desimal akan hilang ketika Anda menyimpan buku kerja Anda. Opsi ini mempengaruhi buku kerja yang aktif, termasuk semua lembar kerja. Anda tidak dapat membatalkan opsi ini dan memulihkan data yang hilang. Kami sarankan Anda menyimpan buku kerja Anda sebelum Anda mengaktifkan opsi ini.

Berulang nomor biner dan penghitungan yang memiliki hasil di dekat nol

Masalah lain yang membingungkan yang mempengaruhi penyimpanan terapung titik angka dalam format biner adalah bahwa beberapa angka yang terbatas, non-berulang angka desimal dasar 10, terbatas, berulang nomor biner. Contoh yang paling umum ini adalah nilai 0.1 dan variasi. Meskipun angka-angka ini dapat mewakili sempurna di dasar 10, nomor yang sama dalam format biner menjadi nomor biner berulang apabila disimpan dalam mantissa:
000110011001100110011 (dan lain-lain)
Spesifikasi IEEE 754 membuat alokasi tidak khusus untuk setiap angka. Toko apa yang dapat di mantissa dan memotong lainnya. Hal ini menghasilkan galat tentang - 2.8E-17, atau 0.000000000000000028 saat disimpan.

Pecahan desimal bahkan umum, seperti 0,0001 desimal, tidak dapat diwakili tepatnya dalam biner. (0,0001 adalah pecahan biner berulang yang memiliki masa 104 bits). Ini sama dengan mengapa pecahan 1 3 tidak tepat ditunjukkan dalam desimal (0.33333333333333333333 berulang).

Sebagai contoh, pertimbangkan contoh sederhana berikut di Microsoft Visual Basic untuk aplikasi:
   Sub Main()      MySum = 0
For I% = 1 To 10000
MySum = MySum + 0.0001
Next I%
Debug.Print MySum
End Sub
Ini akan mencetak 0.999999999999996 sebagai output. Galat kecil dalam mewakili 0,0001 biner menjalar ke jumlah.

Contoh: Menambahkan angka negatif

  1. Masukkan berikut ini ke buku kerja baru:
       A1: =(43.1-43.2)+1 
  2. Klik kanan sel A1, dan kemudian klik Format sel. Pada tab nomor , klik ilmiah di bawah kategori. Set desimal ke 15.
Dan tidak menampilkan 0.9, Excel menampilkan 0.899999999999999. Karena (43,1-43.2) dihitung terlebih dahulu, -0.1 disimpan sementara dan galat menyimpan -0.1 diperkenalkan ke perhitungan.

Contoh saat mencapai nilai nol

  1. Dalam Excel 95 atau lebih lama, masukkan berikut ini ke buku kerja baru:
       A1: =1.333+1.225-1.333-1.225 
  2. Right-clickcell A1, dan kemudian klik Format sel. Pada tab nomor , klik ilmiah di bawah kategori. Set desimal ke 15.
Dan tidak menampilkan 0, Excel 95 menampilkan - 2.22044604925031E-16.

Excel 97, namun, memperkenalkan optimasi yang mencoba untuk memperbaiki masalah ini. Harus operasi penambahan atau pengurangan, maka nilai pada atau sangat dekat nol, Excel 97 dan kemudian akan mengganti kesalahan memperkenalkan hasil konversi operan ke dan dari biner. Contoh di atas saat dilakukan di Excel 97 dan kemudian dengan benar menampilkan 0 atau 0.000000000000000E + 00 notasi ilmiah. Untuk informasi selengkapnya, klik nomor artikel berikut ini untuk melihat artikel di Pangkalan Pengetahuan Microsoft:
Hasil salah 172911 peningkatan 10 untuk daya yang sangat besar/sangat kecil
Hasil salah 214373 peningkatan 10 untuk daya yang sangat besar/sangat kecil
Untuk informasi lebih lanjut tentang angka floating-point dan spesifikasi IEEE 754, lihat situs World Wide Web berikut ini:

Referensi

Untuk informasi selengkapnya tentang cara mengatasi galat ini, klik nomor artikel berikut ini untuk melihat artikel di Pangkalan Pengetahuan Microsoft:
214118 cara untuk memperbaiki kesalahan Border di floating-point aritmatika
Catatan Artikel ini juga berlaku untuk Microsoft Excel untuk Mac untuk Office 365.
Properti

ID Artikel: 78113 - Tinjauan Terakhir: 29 Jan 2017 - Revisi: 1

Tanggapan