excel 2003 juga salah hitung kok

Postingan saya yang ini ternyata menuai kontroversi. Banyak yang mengirim e-mail kepada saya mengenai kebenaran hal ini.

Di postingan kali ini saya akan membuktikan Excel 2003 salah hitung juga, terutama untuk perkalian keramat itu... = 77.1 * 850. Logika pembuktian ini secara sederhana seperti ini, saya mencoba membuktikan bahwa jika angka romawi untuk 8 adalah VIII maka jika 8 itu terdiri dari 5 + 3, maka bilangan romawi hasil penjumlahan keduanya adalah VIII juga.

Fungsi excel yang digunakan untuk pembuktian ini adalah fungsi =DEC2HEX( ) yang merupakan fungsi untuk mengubah bilangan desimal menjadi heksadesimal.

Untuk lebih jelasnya, silahkan download file terlampir. Uhmmm... untuk mendownloadnya dan mendapatkan tips dan trik excel lain dari XL-mania... jangan lupa Anda harus menjadi member XL-mania... :)

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 :)

seberapa pintar microsoft excelmu?

Bukan, maksud saya bukan seberapa pintar kemampuan Microsoft Excelmu :) Tapi... seberapa pintar program Microsoft Excel yang terpasang di komputermu?

Coba buka sebuah sheet di Excel, ketik 40000.223 [untuk yang menggunakan tanda , sebagai pemisah desimal, ketik 40000,223] di mana saja. Nah, inilah yang membuat saya agak2 sebal hari ini... arahkan kursor kembali ke cell tempat Anda mengetik tadi, dan lihat hasil ketikan Anda.

Lihat hasilnya di Excel 2003:

Lihat hasilnya di Excel 2007:

Saya jadi termotivasi untuk mengupas tuntas tentang bug-bug Excel di tulisan berikutnya :D [yaaa... bug-bug - jamak dan bukan bug -tunggal. Tidak ada attachment untuk artikel ini karena kalian bisa membuatnya sendiri... hohohoho...]

lookup dengan banyak kriteria

Pertanyaan:

"Tempat belajar excel paling OK ya XL-maniaaa... Gitu kata orang-orang! Langsung tanya nih boss, gimana ya caranya cari data berdasarkan banyak kriteria atau lookup berdasarkan banyak kriteria? Umumnya sih saya sering cari data berdasarkan dua kriteria, tapi sekarang saya perlu rumus untuk cari data berdasar 3 kriteria. Lagi pusing-pusing mikir, temen saya tanya, gimana caranya lookup berdasarkan 4 kriteria... Pusing... Pusing... Pusing... Contoh data terlampir." - Obama Mc Cain, Amerika.

Jawab:
Pertanyaan: "lookup berdasar banyak kriteria" dan "mencari data berdasar banyak kriteria" adalah salah satu topik yang cukup populer di XL-mania. Pertanyaan ini sering muncul lebih dari 3kali per minggu sehingga terkadang perlu difilter. Anyway, mulai sekarang member XL-mania dapat menemukan artikelnya di sini :) hehehe... Untuk contoh data seperti terlampir, formula yang dapat digunakan di F14 adalah:

{=INDEX($F$3:$F$11,MATCH(B14&C14&D14&E14,$B$3:$B$11&$C$3:$C$11&$D$3:$D$11&$E$3:$E$11))}

kemudian dicopy ke range F15:F16. Formula ini adalah formula array sehingga untuk mengakhirinya harus menekan Ctrl+Shift+Enter.

Penjelasan:

1. Dasar formula ini sama dengan formula "vlookup yang bisa nengok ke kiri"
2. =INDEX($F$3:$F$11,...) berfungsi untuk memberikan nilai pada baris yang ditunjuk.
3. Sedangkan ...MATCH(...,...))... berfungsi untuk mencari pada baris berapa data yang sesuai
4. Pada fungsi MATCH, data yang dicari adalah gabungan dari kolom B, C, D, dan E, sehingga data yang dicari dituliskan sebagai ...B14&C14&D14&E14...
5. Formula ini bukanlah satu-satunya cara, masih banyak cara lain di sini

File:

Anda dapat mendownload materi tips ini di sini

[harus memiliki yahoo id dan mengikuti mailing list XL-mania, tempat belajar microsoft excel!]