Aritmatika Floating-Point dapat memberikan hasil yang akurat di Excel

Terjemahan Artikel Terjemahan Artikel
ID Artikel: 78113 - Melihat produk di mana artikel ini berlaku.
Perbesar semua | Perkecil semua

Pada Halaman ini

Ringkasan

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

Ikhtisar

Microsoft Excel dirancang di sekitar spesifikasi IEEE 754 menyimpan dan menghitung angka floating-point. IEEE adalah Institute of Electrical and Electronics Engineers, sebuah badan internasional yang, antara lain, menentukan standar untuk peranti penangkap lunak komputer dan peranti penangkap keras. Spesifikasi 754 adalah spesifikasi yang sangat luas diadopsi yang menggambarkan angka floating-point bagaimana harus disimpan dalam komputer biner. Hal ini populer karena memungkinkan angka floating-point akan disimpan dalam jumlah yang wajar dari ruang dan perhitungan terjadi relatif cepat. 754 Standar digunakan dalam unit titik mengambang dan pemroses data numerik dari hampir semua hari ini PC berbasis mikroprosesor yang menerapkan floating-point matematika, termasuk prosesor Intel, Motorola, berjemur, dan MIPS.

Ketika menyimpan nomor, nomor biner dapat mewakili setiap nomor atau nomor pecahan. Sebagai contoh, sebagian kecil 1/10 dapat diwakili dalam sistem angka desimal sebagai 0.1. Namun, jumlah yang sama dalam format biner menjadi desimal biner berulang
0001100110011100110011 (dan sebagainya)
dan dapat diulang jauh. Jumlah ini tidak dapat diwakili dalam sejumlah ruang terbatas (terbatas). Oleh karena itu, jumlah ini dibulatkan ke bawah oleh sekitar - 2.8E-17 ketika disimpan.

Namun, ada beberapa keterbatasan IEEE 754 spesifikasi yang jatuh ke dalam tiga kategori Umum:
  • keterbatasan maksimum/minimum
  • presisi
  • mengulangi Bilangan Biner

Informasi lebih lanjut

Keterbatasan maksimum/Minimum

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

Kasus-kasus yang mana kami mematuhi IEEE 754

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

Kasus-kasus yang mana kami tidak mematuhi IEEE 754

  • Denormalized nomor: sejumlah denormalized diindikasikan oleh eksponen 0. Dalam hal ini, seluruh nomor di mantissa dan mantissa memiliki no 1 terkemuka implisit. Sebagai hasilnya, Anda kehilangan presisi, dan semakin kecil jumlah, lebih presisi hilang. Nomor pada akhir kecil Rentang ini memiliki hanya satu digit presisi.
    Contoh: Sejumlah menormalkan memiliki 1 terkemuka implisit. Misalnya, jika mantissa mewakili 0011001, nomor menormalkan menjadi 10011001 karena terkemuka 1 tersirat. Sejumlah denormalized tidak memiliki implisit memimpin satu, sehingga dalam contoh kami dari 0011001, denormalized nomor tetap sama. Dalam hal ini, nomor menormalkan memiliki delapan angka yang signifikan (10011001) sementara nomor denormalized memiliki lima angka yang signifikan (11001) dengan terkemuka angka nol yang menjadi tidak signifikan.

    Denormalized nomor pada dasarnya adalah solusi untuk memungkinkan nomor yang lebih kecil daripada batas bawah normal untuk disimpan. Microsoft tidak melaksanakan Bagian ini opsional spesifikasi karena denormalized nomor pada dasarnya memiliki sejumlah variabel angka yang signifikan. Ini dapat memungkinkan kesalahan nyata untuk masuk ke dalam perhitungan.
  • Positif/negatif Infinities: Infinities terjadi ketika Anda membagi dengan 0. Excel tidak mendukung infinities, sebaliknya, hal itu memberikan #DIV/0! kesalahan dalam kasus ini.
  • Tidak-a-nomor (NaN): NaN digunakan untuk mewakili tidak valid operasi (seperti infinity/tak terhingga, infinity-infinity, atau akar kuadrat dari -1). NaNs memungkinkan program untuk melanjutkan masa operasi tidak valid. Excel sebaliknya segera menghasilkan kesalahan seperti #NUM! atau #DIV/0!.

Presisi

Sejumlah floating-point disimpan dalam biner dalam tiga bagian dalam kisaran 65-bit: tanda, eksponen dan mantissa.
Perkecil tabel iniPerbesar tabel ini
1 Masuk sedikit11 Bit eksponen1 Tersirat Bit52 Bit Mantissa
Tanda toko tanda nomor (positif atau negatif), eksponen yang menyimpan daya 2 yang nomor dibesarkan atau diturunkan (kekuatan maksimum/minimum 2 adalah +1,023 dan-1,022), dan mantissa menyimpan jumlah aktual. Area penyimpanan terbatas untuk mantissa batas seberapa dekat dua berdekatan angka floating point dapat (yaitu presisi).

Mantissa dan eksponen yang baik disimpan secara terpisah. Sebagai Hasilnya, jumlah presisi mungkin dapat bervariasi tergantung pada ukuran angka (mantissa) dimanipulasi. Dalam hal Excel, meskipun Excel dapat menyimpan nomor 1.79769313486232E308-2.2250738585072E-308, dapat hanya melakukannya dalam 15 digit presisi. Batasan ini adalah akibat langsung dari benar-benar mengikuti spesifikasi IEEE 754 dan bukan pembatasan Excel. Tingkat presisi ditemukan dalam program spreadsheet lain sebagai Yah.

Angka Floating-Point terwakili dalam bentuk berikut, mana eksponen adalah eksponen biner:
X = sebagian kecil * 2 ^ (eksponen - bias)
Ini bagian fraksional menormalkan nomor, dinormalisasi karena eksponen yang disesuaikan sehingga sedikit terkemuka selalu 1. Ini cara, tidak memiliki akan disimpan, dan Anda mendapatkan lebih sedikit presisi. Ini sebabnya ada sedikit tersirat. Hal ini mirip dengan notasi ilmiah, mana Anda memanipulasi eksponen yang memiliki satu digit ke kiri dari titik desimal; kecuali dalam biner, Anda selalu dapat memanipulasi eksponen sehingga sedikit yang pertama adalah 1, karena ada hanya 1s dan 0s.

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

Contoh menggunakan jumlah sangat besar

Masukkan yang berikut ke buku kerja baru:
   A1: 1.2E+200
   B1: 1E+100
   C1: =A1+B1
Nilai yang dihasilkan dalam 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 menjadi kenyataan. Hal ini disebabkan oleh IEEE spesifikasi menyimpan hanya 15 angka yang signifikan presisi. Mampu menyimpan perhitungan di atas, Excel akan memerlukan minimal 100 digit presisi.

Contoh menggunakan jumlah yang sangat kecil

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

Mengoreksi kesalahan presisi

Excel menawarkan dua metode dasar untuk mengkompensasi pembulatan kesalahan: fungsi bulat danPresisi seperti yang ditampilkan atau Mengatur presisi seperti yang ditampilkan Workbook pilihan.

Metode 1: Fungsi bulat

Contoh berikut menggunakan data di atas, menggunakan fungsi bulat untuk memaksa sejumlah untuk lima digit. Hal ini memungkinkan Anda untuk berhasil membandingkan hasil ke nilai lain.
   A1: 1.2E+200
   B1: 1E+100
   C1: =ROUND(A1+B1,5)
hasil dalam 1.2E + 200.
   D1: =IF(C1=1.2E+200, TRUE, FALSE)
hasil nilai benar.

Metode 2: Presisi seperti yang ditampilkan

Dalam beberapa kasus, Anda mungkin dapat mencegah pembulatan kesalahan dari mempengaruhi pekerjaan Anda dengan menggunakanPresisi seperti yang ditampilkanpilihan. Opsi ini memaksa nilai angka di lembar kerja harus nilai tampilan. Mengaktifkan opsi ini, Ikuti langkah berikut:
  1. Pada Excel 2003 dan versi sebelumnya, klik pilihan pada menu alat .
  2. Pada perhitungan tab, klik untuk memilih Presisi seperti yang ditampilkankotak centang.
  1. Dalam Excel 2007, klik tombol tekan Microsoft Office, klikExcel pilihan, lalu klik LanjutanKategori.
  2. Dalam Ketika menghitung buku iniBagian, pilih buku kerja Anda inginkan, dan kemudian pilih Set presisi seperti yang ditampilkan kotak centang.
  1. Dalam Excel 2013 dan 2010, klik File, dan kemudian Pilihan, lalu klik LanjutanKategori.
  2. Dalam Ketika menghitung buku iniBagian, pilih buku kerja Anda inginkan, dan kemudian pilih Mengatur presisi seperti yang ditampilkankotak centang.

Misalnya, jika Anda memilih format angka menampilkan dua angka desimal dan kemudian menyalakanPresisi sebagai ditampilkan pilihan, Semua akurasi luar dua desimal akan hilang ketika Anda menyimpan buku kerja Anda. Opsi ini mempengaruhi buku kerja aktif termasuk semua lembar kerja. Anda tidak dapat membatalkan pilihan ini dan memulihkan data hilang. Itu dianjurkan bahwa Anda menyimpan buku kerja Anda sebelum mengaktifkan opsi ini.

Mengulangi Bilangan Biner dan perhitungan dengan hasil mendekati nol

Masalah lain yang membingungkan dengan menyimpan angka floating point dalam biner adalah bahwa beberapa nomor, yang terbatas, tidak berulang angka desimal dasar 10, terbatas, mengulang angka biner. Contoh yang paling umum ini adalah nilai 0.1 dan varian daripadanya. Meskipun angka-angka ini dapat direpresentasikan dengan sempurna dalam basis 10, jumlah yang sama dalam format biner menjadi berulang biner nomor berikut ketika disimpan di mantissa:
000110011001100110011 (dan sebagainya)
Spesifikasi IEEE 754 membuat ketentuan khusus untuk jumlah apapun; Toko apa yang bisa di mantissa dan memotong sisanya. Hal ini mengakibatkan kesalahan tentang - 2.8E-17, atau 0.000000000000000028 ketika disimpan.

Bahkan umum pecahan desimal, seperti desimal 0,0001, tidak dapat diwakili tepatnya di biner. (0,0001 adalah sebagian kecil biner berulang dengan periode 104 bit). Hal ini mirip dengan mengapa sebagian kecil 1/3 tidak persis diwakili di desimal (berulang 0.33333333333333333333).

Ini menjelaskan mengapa contoh sederhana 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
akan mencetak 0.999999999999996 sebagai output. Kesalahan kecil dalam mewakili 0,0001 dalam biner menjalar dengan jumlah.

Contoh menambahkan angka negatif

  1. Masukkan yang berikut ke buku kerja baru:
       A1: =(43.1-43.2)+1
  2. Klik kanan-atas sel A1, dan kemudian klik Format Sel-sel. Pada tab nomor , klik ilmiah di bawah kategori. Mengatur Desimal 15.
Daripada menampilkan 0.9, Excel menampilkan 0.899999999999999. Karena (43,1-43.2) dihitung pertama, 0.1 disimpan sementara dan kesalahan menyimpan 0.1 diperkenalkan ke dalam perhitungan.

Contoh ketika mencapai nilai nol

  1. Dalam Excel 95 atau sebelumnya, masukkan berikut ke yang baru buku kerja:
       A1: =1.333+1.225-1.333-1.225
  2. Klik kanan-atas sel A1, dan kemudian klik Format Cells. Pada tab nomor , klik ilmiah di bawah kategori. Mengatur Desimal 15.
Daripada menampilkan 0, Excel 95 menampilkan - 2.22044604925031E-16.

Excel 97, bagaimanapun, memperkenalkan optimasi yang mencoba untuk memperbaiki masalah ini. Harus penambahan atau pengurangan operasi menghasilkan nilai di atau sangat dekat dengan nol, Excel 97 dan kemudian akan mengkompensasi kesalahan diperkenalkan sebagai akibat dari mengkonversi operan ke dan dari biner. Contoh di atas ketika dilakukan di Excel 97 dan kemudian dengan benar menampilkan 0 atau 0.000000000000000E + 00 dalam notasi ilmiah. Untuk informasi lebih lanjut, klik nomor artikel berikut untuk melihat artikel di dalam Pangkalan Pengetahuan Microsoft:
172911Hasil salah peningkatan 10 untuk daya sangat besar/sangat kecil
214373 Hasil salah peningkatan 10 untuk daya sangat besar/sangat kecil
Untuk informasi lebih lanjut tentang angka floating-point dan IEEE 754 spesifikasi, silakan lihat situs Waring Wera Wanua berikut:
http://www.IEEE.org

http://Steve.hollasch.net/cgindex/coding/ieeefloat.html

Referensi

Untuk informasi lebih lanjut tentang cara kerja di sekitar kesalahan ini, klik nomor artikel berikut ini untuk melihat artikel di Pangkalan Pengetahuan Microsoft:
214118Cara untuk memperbaiki kesalahan pembulatan di floating-point aritmatika

Properti

ID Artikel: 78113 - Kajian Terakhir: 07 Juni 2013 - Revisi: 3.0
Berlaku bagi:
  • Microsoft Excel 2013
  • Microsoft Excel 2010
  • 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 for Mac 2011
  • Microsoft Excel 2008 for Mac
  • Microsoft Excel 2004 for Mac
  • Microsoft Excel X for Mac
  • Microsoft Excel 2001 for Mac
  • Microsoft Excel 98 for Macintosh
Kata kunci: 
kbinfo kbmt KB78113 KbMtid
Penerjemahan Mesin
PENTING: Artikel ini diterjemahkan oleh perangkat lunak penerjemahan mesin Microsoft, dan mungkin telah diedit oleh Masyarakat Microsoft melalui teknologi CTF dan bukan oleh seorang penerjemah profesional. Microsoft menawarkan baik artikel yang diterjemahkan oleh manusia maupun artikel hasil editan terjemahan oleh mesin/komunitas, sehingga Anda dapat mengakses semua artikel di Sentra Pengetahuan yang kami miliki dalam berbagai bahasa. Namun artikel hasil editan mesin atau bahkan komunitas tidak selalu sempurna. Artikel ini dapat mengandung kesalahan dalam hal kosa kata, sintaksis atau tatabahasa, sangat mirip dengan penutur asing yang membuat kekeliruan ketika berbicara dalam bahasa Anda. Microsoft tidak bertanggung jawab atas ketidakakuratan, kesalahan atau kerugian apa pun akibat dari kekeliruan dalam penerjemahan isi atau penggunaannya oleh pelanggan kami. Microsoft juga akan senantiasa memperbarui perangkat lunak penerjemahan mesin dan alat untuk menyempurnakan Editan Hasil Penerjemahan Mesin.
Klik disini untuk melihat versi Inggris dari artikel ini: 78113

Berikan Masukan

 

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