XL-mania adalah komunitas yahoogroups Microsoft Excel terbesar di dunia. Melalui XL-mania kita dapat belajar Excel bersama. Fungsi yang dipelajari dari fungsi keuangan, tanggal dan waktu, matematika, statistik, kondisi, lookup, referensi, database, logika, teks, informasi, serta cara membuat user defined function. Selain itu, di XL-mania juga dibahas mengenai penggabungan beberapa fungsi Excel menjadi sebuah formula yang unik, array, dan megaformula. Fitur-fitur Excel yang dibahas juga luas, dari conditional formatting, goal seek, chart, pivot table, macro / VBA, business model, financial modeling, solver, dan lain-lain. Dapatkan tips dan trik excel gratis dari XL-mania! Ingat, malu bertanya, kerja manual!

November 19, 2008

bug aneh di excel 2007 ituhhhh...


Kali ini saya akan membahas bug terkenal di Excel 2007 itu. Bagi yang belum tahu, cobalah Anda lakukan perkalian seperti = 425 * 154.20 atau = 850 * 77.1. Jika hasilnya adalah 100,000, artinya Excel 2007 Anda belum di-patch atau belum kebagian service pack dan harus mendownload paket di sini.

Untuk Anda yang sudah tahu, atau tidak mengalami bug dashyat ini... Artikel ini tidak menjadi "basi" untuk Anda, karena saya akan menjelaskan mengapa ini terjadi :) Saya coba jelaskan dengan bahasa yang sederhana.Hal pertama yang harus dimengerti adalah Excel menyimpan data dalam bentuk data biner. Artinya bilangan seperti 77.1 misalnya, akan disimpan oleh Excel sebagai:

0100 0000 0101 0011 0100 0110 0110 0110 0110 0110 0110 0110 0110 0110 0110 0110

yang artinya "7", "7", ".", dan "1". Setelah disimpan ke dalam data biner, di dalam Excel ada satu "alat" untuk menampilkan data tersebut ke monitor Anda. "Alat" inilah yang salah menampilkan data biner yang bernilai 65,535 sebagai 100,000. "Alat" ini juga salah mengubah data jika nilai suatu angka mendekati 65,535. Perhitungan = 77.1 * 850 menampilkan 100,000. Perhitungan = 77.1 * 850 + 2 menampilkan 65,537. Tapi perhitungan = 77.1 * 850 + 1 menampilkan 100,001.

Salah satu hal yang memperkuat bahwa hal ini hanya kesalahan tampilan adalah bahwa Anda masih mendapatkan grafik yang benar jika menggunakan angka 100,000 "gadungan" ini ke dalam chart. Begitu pula jika Anda memiliki macro "terbilang", maka fungsi buatan anda sendiri [UDF] itu akan menyebutkan kalimat terbilang yang benar, bukan "seratus ribu".

Ada yang mungkin bertanya-tanya, "Kalau benar bahwa itu hanya tampilan, artinya jika kita mengetik 65,535 harusnya akan ditampilkan sebagai 100,000 dong? Kayak posting yang ini nihhhh...."

Hmmm.... harusnya Anda melihat lagi ke atas dan lihat kata "mendekati" saya bold. Jadi yang bermasalah adalah angka seperti 65,534.999999, bukan 65,535 sendiri. Mengapa itu dapat terjadi? Baca lagi! Kita perhatikan lagi angka biner dari 77.1:

0100 0000 0101 0011 0100 0110 0110 0110 0110 0110 0110 0110 0110 0110 0110 0110

Dapat Anda lihat bahwa 0110 adalah angka yang berulang. Apa yang sebenarnya terjadi? Bilangan biner untuk 77.1 memiliki desimal yang berulang. Seperti 1 / 3 yang bilangan desimalnya 0.3333333333... dan 1 / 6 yang bilangan desimalnya 0.16666666666... Jika Anda mengalikan kembali 1 / 3 dengan 3, Anda akan mendapat angka 1. Tapi jika Anda mengalikan kembali 0.3333333333 dengan 3, maka Anda mendapat 0.9999999999. Hal yang serupa juga terjadi dengan bilangan biner itu, hasil operasi matematika dengan bilangan tersebut tidak terlalu akurat dan ada selisih yang sangat kecil. Artinya, dari awal 77.1 memang disimpan sebagai bilangan biner yang nilainya tidak benar-benar tepat 77.1!

"Mengapa menggunakan data biner dan bukan desimal dalam menyimpan data?"

Ada banyak alasan untuk ini, tapi yang utama adalah karena standar floating point yang digunakan Excel adalah IEEE-754, suatu standar yang umum digunakan.

"Jadi, angka berapa saja yang salah ditampilkan?"

OK, jika saya bilang "mendekati", mungkin pertanyaannya seberapa dekat? Angka yang salah ditampilkan adalah antara 65534.99999999995 dan 65535.99999999995. Sebagai catatan, Anda tidak dapat memasukkan angka ini di Excel langsung karena Excel akan membulatkan menjadi 15 bilangan saja. Contohnya jika Anda menginput 65534.99999999997 maka Excel akan mengubahnya menjadi 65534.9999999999. Begitu pula jika Anda menginput 65534.99999999998 maka hasilnya juga 65534.9999999999.

"Mengapa masalah floating point ini hanya terjadi di Excel 2007 dan tidak di Excel 2003?"

Hmmm... Anda yakin begitu? Excel 2003 juga salah hitung lho :) mau bukti? Di Excel 2003 SP 2, coba Anda ketikkan =DEC2HEX(850*77.1,4), maka hasilnya adalah FFFE dan bukan FFFF. Tapi jika =DEC2HEX(TRUNC(850*77.1),4), maka hasilnya adalah FFFF. Bagaimana dengan =DEC2HEX(SQRT(850*77.1)^2,4) ? Hasilnya juga FFFF!

Sementara sampai sini dulu. In a nut shell, "salah hitung" di Excel 2007 sebenarnya hanya kesalahan display / tampilan saja. "Alat" untuk melakukan tampilan itu kurang benar. Tapi metode perhitungannya sudah "aneh" dari dulu karena masalah floating point ini. Di postingan berikutnya saya akan tunjukkan bahwa semua versi excel bisa salah hitung! :D :D :D

Untuk Anda yang senang melihat-lihat "alat" apa yang saya bicarakan di atas, silahkan download file ini. Saya pribadi lebih senang penjelasan yang sederhana :)

10 comments:

  1. terimakasih banyak infonya...
    berarti saya harus lebih berhati-hati menampilkan angka hasil hitungan.

    saya sering melakukan hitungan dengan metode least-square yang menggunakan perkalian-matrix dan invers-matrix dengan menggunakan UDF vba-excel.

    Apakah dengan menampilkan hasil hitungan ke 15 digit saja atau lebih kecil, dapat mencegah kesalahan display akibat floating point ini?

    salam:zainul

    ReplyDelete
  2. Oooo... udah salah dari dulu thooo...

    ReplyDelete
  3. Berarti mesin tetaplah alat bantu yg jg bisa salah. Dan otak manusia kadang2 bisa ngitung lebih akurat. Terus klo menjawab soal integral tak tentu yg kompleks apakah komputer bisa ya?? Wah, tambah bingung dah....

    ReplyDelete
  4. Kok gwe masi ga percaya ya excel 2003 bisa salah itung.

    ReplyDelete
  5. wewwwwww......
    baru tau.....gw....
    gw pengguna excel yg standar2 aja sih...
    pengen juga expert......wish me luck...

    ReplyDelete
  6. kalau kalkulator koq gak salah ya? padahal khan pk biner jg?

    ReplyDelete
  7. Saya sudah download paket yang di http://support.microsoft.com/kb/943075/id-id
    Tapi waktu saya install hasil download-an tersebut tidak bisa dan keluar pesan Hotfix for office (KB943075) yang isinya The expected version of the product was not found on the system.
    Kenapa ya, mohon bantuan dan pencerahannya.... :-)
    Apa yang harus saya lakukan berikutnya...
    sebelumnya saya ucapkan terimakasih

    ReplyDelete
  8. angka yang bsa salah kalau dikalikan ternyata banyak jg ya... untung liat infonya disini kalo ga bsa salah itung itu hhahahahaha

    ReplyDelete
  9. ya gan milik aku juga keluar 100.000

    dan ini yang mau saya tanyakan kenapa saat penulisan angka 1000000 menjadi 10.00.000
    padahal kan seharusnya 1.000.000

    kok aneh ya bos mohon bimbinganya ya bos




    jangan lupa kunjungin balik
    Pallet murah berkualitas untuk perusaha'an anda
    ingin jual pulsa,atau di pakai sendiri?

    ReplyDelete

Silahkan berkomentar tentang XL-mania...