Terdapat ralat pengiraan hodoh yang telah muncul di Excel. Nampaknya masalahnya masuk ke dalam mesin pengiraan Excel dan tidak akan mudah diselesaikan.
Inti masalahnya adalah fakta mudah: Excel menyimpan 15 digit ketepatan dalam satu sel. Anda boleh mempunyai nombor yang mempunyai 20 digit, tetapi sebarang digit antara digit penting terakhir dan tempat perpuluhan mestilah sifar.
Baru-baru ini saya melihat dua kes di mana mesin pengiraan Excel memberikan hasil yang salah. Semasa saya mencari masalah dan melihat XML yang mendasari, saya terkejut melihat Excel secara diam-diam menyimpan 17 digit dalam XML.
Masalahnya ialah Excel hanya akan memaparkan 15 digit. Oleh itu, anda berpendapat bahawa anda mempunyai nombor yang disimpan sebagai 0.123456789012345 tetapi ia benar-benar disimpan sebagai 0.12345678901234567.
Anda tidak dapat melihat dua digit terakhir. Dan kebanyakan fungsi Excel mengabaikan dua digit terakhir. Sekiranya * semua * fungsi mengabaikan dua digit terakhir, kita tidak akan menghadapi masalah. Tetapi setakat ini, pengisihan, RANK, dan FREQUENCY menggunakan semua 17 digit.
Di bawah ini adalah helah yang terkenal untuk peringkat sel. Sekiranya anda memerlukan setiap peringkat untuk muncul sekali, anda boleh menggabungkan RANK dan COUNTIF. Dalam gambar di bawah, Claire, Flo, Ivana, dan Lucy terikat pada 115%. Dengan menggunakan formula RANK + COUNTIF, mereka harus berada di kedudukan 5, 6, 7, dan 8.
Tetapi formula itu gagal. Dua baris diberi peringkat 7. Itu tidak pernah berlaku. Empat formula di lajur D memastikan bahawa 115% di B6, B9, B12, dan B15 adalah sama. The =B6=B15formula melaporkan bahawa kedua-dua sel mengandungi data yang sama.
Semasa saya cuba mengasingkan masalah, lihat hanya fungsi RANK. Seharusnya melaporkan keputusan 4-arah pada peringkat ke-4 untuk orang-orang dengan 115%. Tetapi entah bagaimana, Lucy di Row 15 berada di kedudukan mendahului daripada tiga yang lain.
Untuk mengetahuinya, saya menghantar permintaan bantuan kepada Excel MVP yang lain. Jan Karel Pieterse membuka fail Excel dan melihat ke dalam XML. Dalam XML, anda dapat melihat bahawa mereka menyimpan 17 digit ketepatan. Keempat sel yang kelihatan seperti tali leher di Excel tidak terikat dalam XML. Salah satu dari 115% disimpan sebagai 1.1500000000000001 dan yang lain adalah 1.149999999999999999.
Setakat ini, pengisihan, pemeringkatan, dan fungsi FREQUENCY menggunakan digit tambahan. Mengapa itu menjadi masalah? Kerana kita bergantung pada RANK dan COUNTIF untuk kedua-duanya menggunakan bilangan digit yang sama. Dengan satu fungsi menggunakan 15 digit dan yang lain menggunakan 17 digit, anda menghadapi masalah.
Buat masa ini, penyelesaiannya seolah-olah menukar semua jawapan anda menggunakan =ROUND(A4,15).
Setiap hari Jumaat, saya memeriksa bug atau tingkah laku amis lain di Excel. Kesalahan pengiraan ini sukar dikesan dan memenuhi syarat sebagai ikan besar.
Pemikiran Excel Hari Ini
Saya telah meminta nasihat rakan Excel saya mengenai Excel. Pemikiran hari ini untuk merenungkan:
"Setiap kali anda menggabungkan sel anda membunuh anak kucing"
Szilvia Juhasz
