Floating-point aritmatika dapat memberikan hasil yang akurat di Excel

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

Sekilas pandang

Microsoft Excel dirancang di sekitar spesifikasi IEEE 754 sehubungan dengan menyimpan dan menghitung angka floating-point. IEEE adalah Institute of Electrical and Electronics Engineers, internasional tubuh antara lain, menentukan standar untuk perangkat lunak komputer dan perangkat keras. Spesifikasi 754 adalah spesifikasi sangat luas diadopsi yang menjelaskan bagaimana angka floating-point harus disimpan dalam komputer biner. Populer karena hal itu memungkinkan angka floating-point akan disimpan dalam jumlah yang wajar ruang dan perhitungan terjadi relatif cepat. 754 Standar digunakan dalam Unit titik mengambang dan prosesor data numerik dari hampir semua hari ini Berbasis PC mikroprosesor yang menerapkan floating-point matematika, termasuk Intel, Motorola, matahari dan MIPS prosesor.

Ketika menyimpan nomor, nomor biner dapat mewakili setiap angka atau bilangan pecahan. Sebagai contoh, sebagian kecil 1/10 dapat direpresentasikan dalam sistem bilangan desimal sebagai 0.1. Namun, nomor yang sama dalam format biner menjadi ganda berulang desimal dan dapat jauh diulang. Nomor ini tidak dapat mewakili dalam terbatas () jumlah terbatas ruang. Oleh karena itu, jumlah ini bulat oleh sekitar - 2.8E-17 ketika disimpan.

Namun, ada beberapa keterbatasan spesifikasi IEEE 754 yang jatuh ke dalam tiga umum Kategori:

• maksimum/minimum keterbatasan
• presisi
• mengulangi bilangan biner

Maksimum/Minimum keterbatasan

Semua komputer telah maksimum dan jumlah minimal yang dapat ditangani. Karena jumlah bit memori di mana nomor terbatas, maka nomor maksimum atau minimum yang dapat disimpan adalah juga terbatas. Untuk Excel, jumlah maksimum yang dapat disimpan adalah 1.79769313486232E + 308 dan minimum bilangan positif yang dapat disimpan adalah 2.2250738585072E-308.

Kasus-kasus di mana kita mematuhi IEEE 754

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

Kasus-kasus di mana kita tidak mematuhi IEEE 754

• Denormalized nomor: nomor denormalized ditunjukkan oleh eksponen 0. Dalam hal ini, seluruh nomor di dalam mantissa dan dalam mantissa memiliki 1 terkemuka tidak ada implisit. Sebagai hasilnya, Anda kehilangan presisi, dan yang lebih kecil jumlah, presisi yang lebih hilang. Nomor pada akhir kecil kisaran ini memiliki hanya satu digit presisi.
  Contoh: Sejumlah dinormalkan memiliki 1 terkemuka implisit. Sebagai contoh, jika mantissa mewakili 0011001, nomor dinormalkan menjadi 10011001 karena dari 1 terkemuka yang tersirat. Sejumlah denormalized tidak memiliki implisit memimpin satu, sehingga dalam contoh dari 0011001, denormalized nomor tetap sama. Dalam kasus ini, nomor dinormalkan memiliki delapan angka yang signifikan (10011001) sementara nomor denormalized memiliki lima angka yang signifikan (11001) dengan terkemuka nol menjadi tidak signifikan.
  
  Denormalized nomor pada dasarnya adalah solusi untuk memungkinkan nomor 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 sah operasi (seperti infinity/tak terhingga, tak terhingga-infinity, atau akar kuadrat dari -1). Dwarves memungkinkan program untuk terus melewati operasi tidak sah. Unggul sebagai gantinya segera menghasilkan kesalahan seperti #NUM! atau #DIV/0!.

Presisi

Floating-point nomor biner dalam tiga bagian dalam kisaran 65-bit: tanda, dengan eksponen, dan dalam mantissa. Tanda toko tanda nomor (positif atau negatif), dengan eksponen toko kekuatan 2 yang nomor mengangkat atau menurunkan (kekuatan maksimum/minimum 2 adalah +1,023 dan-1,022), dan toko-toko mantissa jumlah aktual. Wilayah terbatas penyimpanan untuk batas-batas mantissa seberapa dekat nomor dua berdekatan mengambang titik dapat (yaitu presisi).

Dalam mantissa dan dengan eksponen disimpan secara terpisah. Sebagai Hasilnya, jumlah presisi mungkin bervariasi tergantung pada ukuran angka (mantissa) sedang dimanipulasi. Dari Excel, meskipun Excel dapat menyimpan nomor 1.79769313486232E308-2.2250738585072E-308, dapat hanya melakukannya dalam 15 angka ketelitian. Pembatasan ini adalah akibat langsung dari benar-benar mengikuti spesifikasi IEEE 754 dan tidak pembatasan Excel. Tingkat presisi ditemukan di program spreadsheet lainnya baik.

Angka floating-point diwakili dalam bentuk berikut, di mana eksponen adalah eksponen biner: Sebagian kecil adalah bagian fraksional dinormalkan nomor, dinormalisasi karena dengan eksponen diatur sehingga sedikit terkemuka selalu 1. Ini cara, tidak harus disimpan, dan Anda mendapatkan satu lebih sedikit presisi. Ini sebabnya ada sedikit tersirat. Hal ini sama dengan notasi ilmiah, di mana Anda memanipulasi eksponen memiliki satu digit ke kiri dari titik desimal; kecuali dalam biner, Anda selalu dapat memanipulasi dengan eksponen sehingga pertama sedikit adalah 1, karena ada hanya 1s dan 0s.

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

Contoh menggunakan jumlah sangat besar

Masukkan yang berikut ke buku kerja baru: Nilai yang dihasilkan dalam sel C1 akan 1.2E + 200, nilai yang sama sebagai sel A1. Bahkan jika Anda membandingkan sel A1 dan C1 menggunakan fungsi jika, untuk contoh IF(A1=C1), hasilnya akan BENAR. Hal ini disebabkan oleh spesifikasi IEEE menyimpan hanya 15 angka yang signifikan presisi. Untuk dapat menyimpan perhitungan di atas, Excel akan memerlukan minimal 100 angka ketelitian.

Contoh menggunakan jumlah sangat kecil

Masukkan yang berikut ke buku kerja baru: Nilai yang dihasilkan dalam sel C1 akan 1.00012345678901 sebaliknya dari 1.000123456789012345. Hal ini disebabkan oleh spesifikasi IEEE menyimpan hanya 15 digit presisi. Untuk dapat menyimpan perhitungan di atas, Excel akan memerlukan setidaknya 19 angka ketelitian.

Mengoreksi kesalahan presisi

Excel menawarkan dua metode dasar untuk mengkompensasi rounding errors: fungsi BULAT dan Presisi yang ditampilkan atau Mengatur presisi yang ditampilkan buku kerja pilihan.

Metode 1: Fungsi BULAT

Contoh berikut menggunakan data-data diatas, menggunakan PUTARAN fungsi untuk memaksa sejumlah untuk lima digit. Hal ini memungkinkan Anda untuk berhasil membandingkan hasil nilai lain.

Metode 2: Presisi yang ditampilkan

Dalam beberapa kasus, Anda dapat mencegah pembulatan kesalahan dari mempengaruhi kerja Anda dengan menggunakan Presisi yang ditampilkanpilihan. Pilihan ini memaksa nilai setiap angka di lembar kerja harus ditampilkan nilai. Untuk mengaktifkan opsi ini, Ikuti langkah berikut:

1. Pada Excel 2003 dan versi sebelumnya, klik Opsi pada Alat menu.
2. Pada Perhitungan tab, klik untuk memilih Presisi yang ditampilkankotak centang.

1. Di Excel 2007, klik tombol Microsoft Office, klikExcel pilihan, lalu klik LanjutanKategori.
2. Dalam Ketika menghitung buku kerja inibagian, pilih buku kerja yang Anda inginkan, dan kemudian pilih Set presisi yang ditampilkan kotak centang.

Sebagai contoh, jika Anda memilih nomor format menampilkan dua angka desimal dan kemudian Hidupkan Presisi sebagai ditampilkan pilihan, semua akurasi luar dua desimal akan hilang ketika Anda menyimpan buku kerja Anda. Pilihan ini mempengaruhi buku kerja yang aktif termasuk semua lembar kerja. Anda tidak dapat membatalkan pilihan ini dan memulihkan data yang hilang. Itu dianjurkan bahwa Anda menyimpan buku kerja Anda sebelum untuk mengaktifkan opsi ini.

Mengulangi bilangan biner dan perhitungan dengan hasil dekat dengan nol

Masalah lain yang membingungkan dengan menyimpan angka floating point di biner adalah bahwa beberapa nomor, yang terbatas, tidak berulang angka desimal dasar 10, terbatas, mengulangi angka biner. Contoh paling umum ini adalah nilai 0.1 dan variannya. Meskipun angka-angka ini dapat mewakili sempurna dalam basis 10, nomor yang sama dalam format biner menjadi Setelah mengulangi nomor biner ketika disimpan di dalam mantissa: Spesifikasi IEEE 754 membuat tidak ada ketentuan khusus untuk setiap nomor; toko apa yang bisa di dalam mantissa dan memotong sisanya. Ini mengakibatkan kesalahan tentang - 2.8E-17, atau 0.000000000000000028 ketika disimpan.

Bahkan umum pecahan desimal, seperti 0,0001 desimal, tidak dapat diwakili tepat dalam biner. (0,0001 adalah sebagian kecil biner berulang dengan periode bit 104). Ini sangat mirip mengapa sebagian kecil 1/3 tidak dapat tepat diwakili dalam desimal (berulang 0.33333333333333333333).

Ini menjelaskan mengapa contoh sederhana di Microsoft Visual Basic untuk aplikasi akan mencetak 0.999999999999996 keluaran. Kesalahan kecil dalam mewakili 0,0001 biner menjalar ke jumlah.

Contoh menambahkan angka negatif

1. Masukkan yang berikut ke buku kerja baru:

      A1: =(43.1-43.2)+1

2. Klik kanan sel A1, dan kemudian klik Format Sel. Pada Nomor tab, klik Ilmiah di bawah Kategori. Menetapkan Desimal 15.

Alih-alih 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 nilai mencapai nol

1. Dalam Excel 95 atau sebelumnya, masukkan berikut ke baru buku kerja:

      A1: =1.333+1.225-1.333-1.225

2. Klik kanan sel A1, dan kemudian klik Format Cells. Pada Nomor tab, klik Ilmiah di bawah Kategori. Menetapkan Desimal 15.

Alih-alih menampilkan 0, Excel 95 menampilkan -2.22044604925031E-16.

Excel 97, namun, memperkenalkan optimasi yang mencoba untuk memperbaiki masalah ini. Harus penambahan ataupun pengurangan operasi menghasilkan nilai pada atau sangat dekat dengan nol, Excel 97 dan kemudian akan mengkompensasi kesalahan diperkenalkan sebagai akibat dari mengubah concatenates ke dan dari ganda. 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 Basis Pengetahuan Microsoft: Untuk informasi lebih lanjut tentang angka floating-point dan spesifikasi IEEE 754, silakan lihat berikut situs World Wide Web:

Untuk informasi lebih lanjut tentang cara bekerja sekitar kesalahan ini, klik nomor artikel di bawah ini untuk melihat artikel di Basis Pengetahuan Microsoft: