Ada kesalahan kalkulasi jelek yang telah muncul di Excel. Tampaknya masalah tersebut masuk jauh ke dalam mesin kalkulasi Excel dan tidak akan mudah diperbaiki.
Inti masalahnya adalah fakta sederhana: Excel menyimpan 15 digit presisi dalam satu sel. Anda dapat memiliki angka yang memiliki 20 digit, tetapi digit apa pun antara digit signifikan terakhir dan tempat desimal harus nol.
Saya telah melihat dua kasus baru-baru ini di mana mesin kalkulasi Excel mengembalikan hasil yang salah. Ketika saya menggali masalah dan melihat XML yang mendasarinya, saya terkejut melihat bahwa Excel secara diam-diam menyimpan 17 digit dalam XML.
Masalahnya adalah Excel hanya akan menampilkan 15 digit. Jadi Anda mengira bahwa Anda memiliki nomor yang disimpan sebagai 0.123456789012345 tetapi sebenarnya disimpan sebagai 0.12345678901234567.
Anda tidak dapat melihat dua digit terakhir itu. Dan sebagian besar fungsi Excel mengabaikan dua digit terakhir tersebut. Jika * semua * fungsi mengabaikan dua digit terakhir, kami tidak akan mendapat masalah. Namun sejauh ini, pengurutan, PERINGKAT, dan FREQUENCY menggunakan 17 digit semuanya.
Di bawah ini adalah trik terkenal untuk menentukan peringkat sel. Jika Anda ingin setiap peringkat muncul tepat satu kali, Anda dapat menggabungkan RANK dan COUNTIF. Pada gambar di bawah, Claire, Flo, Ivana, dan Lucy terikat pada 115%. Menggunakan rumus RANK + COUNTIF, mereka harus diberi peringkat 5, 6, 7, dan 8.
Tapi rumusnya gagal. Dua baris diberi peringkat 7. Itu tidak pernah terjadi. Empat rumus di kolom D pastikan 115% di B6, B9, B12, dan B15 adalah sama. The =B6=B15Rumus melaporkan bahwa kedua sel berisi data yang sama.
Saat saya mencoba mengisolasi masalahnya, lihat saja fungsi RANK. Ini harus melaporkan dasi 4 arah di urutan ke-4 untuk orang-orang dengan 115%. Tapi entah bagaimana, Lucy di Baris 15 berada di depan tiga lainnya.
Untuk mengetahuinya, saya mengirimkan permintaan bantuan ke MVP Excel lainnya. Jan Karel Pieterse membuka file Excel dan melihat ke dalam XML. Dalam XML, Anda dapat melihat bahwa mereka menyimpan presisi 17 digit. Empat sel yang terlihat seperti dasi di Excel tidak terikat dalam XML. Salah satu dari 115% disimpan sebagai 1.1500000000000001 dan yang lainnya adalah 1.1499999999999999.
Sejauh ini, pengurutan, pemeringkatan, dan fungsi FREQUENCY menggunakan digit ekstra. Kenapa itu jadi masalah? Karena kami mengandalkan RANK dan COUNTIF untuk menggunakan jumlah digit yang sama. Dengan satu fungsi menggunakan 15 digit dan yang lainnya menggunakan 17 digit, Anda mendapat masalah.
Untuk saat ini, solusinya tampaknya mengubah semua jawaban Anda menggunakan =ROUND(A4,15).
Setiap Jumat, saya memeriksa bug atau perilaku mencurigakan lainnya di Excel. Kesalahan perhitungan ini sulit dideteksi dan dikualifikasikan sebagai ikan besar.
Pemikiran Excel Hari Ini
Saya telah meminta saran dari teman-teman Master Excel saya tentang Excel. Hari ini pemikiran untuk direnungkan:
"Setiap kali Anda menggabungkan sel, Anda membunuh anak kucing"
Szilvia Juhasz
