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 dan harus mendownload service pack 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!]

excel 2003 vs excel 2007

Banyak pertanyaan diajukan kepada saya mengenai Excel 2007.

"Apakah saya harus upgrade ke Excel 2007?"
"Apa kelebihan Excel 2007 dibanding Excel 2003?"
"Apa sih bedanya Excel 2003 dan Excel 2007?"
"Kapan saya harus memutuskan untuk tidak upgrade ke Excel 2007?"



Di bawah ini saya akan memaparkan perbedaan mendasar Excel 2003 dan Excel 2007. Klasifikasi poin yang saya buat berdasarkan pengamatan sendiri, bukan mengacu pada standar tertentu.

1. Jumlah baris

Excel 2003 hanya menyediakan 65,536 baris, sedangkan Excel 2007 menyediakan 1,048,576 baris. Apakah ini sebuah kelebihan Excel 2007? Belum tentu! Ini tergantung pekerjaan Anda, berapa banyak baris yang Anda butuhkan. Jangan berkata bahwa Anda selalu berurusan dengan file database dengan jumlah baris sangat banyak. Jika ingin membuat database, gunakan Microsoft Access yang menyediakan banyak baris, bukan Microsoft Excel.

2. Jumlah kolom

Excel 2003 menyediakan 256 kolom, sedangkan Excel 2007 menyediakan 16,384 kolom. Jika Anda masih menganggap ini kelebihan Excel 2007, berarti Anda tidak membaca nomor 1 :)

3. Bug aneh itu :D

Buka Excel 2007 Anda, coba kalikan 77.1 dengan 850. Jika hasilnya 100,000, artinya Excel 2007 Anda masih belum di patch. Untuk memperbaikinya, dapat mendownload programnya di sini. Hei, saya berusaha untuk fair. Pada perbandingan di bawah ini saya membandingkan Excel 2003 dengan patch terakhirnya dengan Excel 2007 SP 1.

4. Kecepatan macro

Ada yang pernah memperhatikan perbedaan kecepatan Macro Excel 2003 dan Excel 2007? Saya melakukan pada beberapa program yang saya buat dan mendapatkan macro di Excel 2007 bekerja lebih lambat. Sebagai buktinya, coba Anda download games ini, kemudian jalankan di Excel 2003. Setelah itu jalankan di Excel 2007. Coba bandingkan.

5. Record macro

Record Macro di Excel 2007 memang tidak menunjukkan masalah yang signifikan.... sampai Anda mencoba merekam aktivitas Anda membuat Chart. Buatlah sebuah aktivitas pembuatan chart yang rumit, rekam dengan Excel 2003. Lakukan proses yang sama di Excel 2007 dan bandingkan hasilnya.

6. Pivot table

Excel 2007 mempunyai fitur pivot table yang benar-benar baru, benar-benar canggih, benar-benar keren... kecuali saat Anda harus mengirimkan pekerjaan Anda ke boss Anda yang menggunakan Excel 2003 dengan compatibility pack :D hahaha.... Bahkan dengan melakukan save as sebagai Excel 2003, Anda akan menemui masalah. Anyway, jika dipandang dari jumlah data yang dapat diolah, Excel 2007 jauh lebih baik. Dengan jumlah kolom sebanyak 16,384 kolom, Excel 2007 dapat memberikan pivot table yang lebih baik dibanding Excel 2003 yang hanya mampu menampilkan 255 kolom.

7. Macro pada workbook yang terproteksi

Protect workbook 2007 Anda, jika workbook tersebut mengandung macro... maka saya belum menemukan cara untuk mengaktifkan macronya.

8. List / tabel

Harus diakui, Excel 2007 memiliki berbagai format tabel yang menarik dan siap pakai.

9. Formula

Excel 2007 memiliki banyak tambahan formula. Sayangnya, jika Anda melakukan save as ke versi Excel yang lebih rendah dan mengirimkannya ke teman Anda yang menggunakan Excel 2003, nampaknya mempelajari formula-formula ajaib di XL-mania akan lebih berguna untuk menggantikan formula baru seperti SUMIFS.

10. Penampilan grafik / chart

Excel 2007 memiliki penampilan yang menakjubkan untuk grafik / chart. Bahkan warna defaultnya juga sangat menarik dibandingkan versi Excel yang lebih lama.

11. Refresh grafik / chart

Cobalah buat sebuah chart yang rumit dan berwarna-warni. Masukkan data dengan formula =RAND( ) sehingga chart berubah saat menekan F9. Bandingkan kecepatannya antara Excel 2007 dan Excel 2003. Ternyata untuk penampilan yang indah di Excel 2007, terdapat pengorbanan kecepatan refresh chart.

12. Drag drop data pada grafik / chart

Di Excel 2003, jika Anda melakukan seleksi pada sebuah area, kemudian melakukan drag-drop seleksi tersebut ke sebuah chart, maka terdapat fitur penambahan data otomatis. Di Excel 2007, Anda tidak akan menemukan fitur ini.

13. Membuka / menutup file

Di Excel 2007, saat membuka atau menutup file, seringkali terjadi sedikit delay. Artinya, jika dibandingkan dengan Excel 2003, dengan spesifikasi komputer yang sama dan file yang sama, performa Excel 2007 dalam aktivitas ini lebih lambat dibanding pendahulunya.

14. Export atau save as ke .dbf

Di Excel 2007 Anda tidak menemukan fitur untuk export atau save as ke format .dbf.

15. Kompatibilitas

Saya tidak bicara bahwa Excel 2003 tidak bisa membuka format .xlsx dari Excel 2007 *dooohhh*. Maksud saya di sini adalah ada beberapa format yang tidak bisa dibuka oleh Excel 2007 seperti: WK1 (1-2-3); WK4 (1-2-3); WJ3 (1-2-3 Japanese) (.wj3); WKS (1-2-3); WK3,(1-2-3); WK1,FMT(1-2-3); WJ2 (1-2-3 Japanese) (.wj2); WJ3, FJ3 (1-2-3 Japanese) (.wj3); DBF 2 (dBASE II); WQ1 (Quattro Pro/DOS); WK3,FM3(1-2-3); Microsoft Excel Chart (.xlc); WK1,ALL(1-2-3); WJ1 (1-2-3 Japanese) (.wj1); WKS (Works Japanese) (.wks). Selain itu ada beberapa format yang dapat dibuka, tapi tidak dapat disimpan: Microsoft Excel 2.1 Worksheet; Microsoft Excel 2.1 Macro; Microsoft Excel 3.0 Worksheet; Microsoft Excel 3.0 Macro; Microsoft Excel 4.0 Worksheet; Microsoft Excel 4.0 Macro; Microsoft Excel 97- Excel 2003 & 5.0/95 Workbook; Microsoft Excel 4.0 Workbook; DBF 3 (dBASE III); DBF 4 (dBASE IV).

16. Jumlah kondisi pada conditional formatting

Jumlah kondisi pada conditional formatting di Excel 2003 hanya 3 kondisi [6 kondisi kalau pakai jurus XL-mania]. Di Excel 2007? Buaaannyyyyakkkkkk kondisi.

17. Variasi pada conditional formatting

Jika di Excel 2003, conditional formatting hanya mengubah warna font, cell, border, dan lain-lain, di Excel 2007 mungkin "dan lain-lain" nya itu lebih indah :D Anda bisa menambahkan data bar, icon sets, color scales, atau kombinasinya. Sungguh indah!

18. Akibat pemakaian conditional formatting

Akibat dari nomor 17? Jika Anda menggunakan terlalu banyak kondisi pada Excel 2007... Performa Excel 2007 dalam hal kecepatan akan sangat menurun drastis. Bahasa sederhananya? Excel 2007 Anda jadi lambat!

19. Toolbar

Di Excel 2003, kustomisasi toolbar sangat fleksibel, sedangkan di Excel 2007 kurang. Jika di Excel 2003 semua command yang dapat dimunculkan atau dihilangkan, di Excel 2007 Anda hanya dapat mengubah menu di toolbar quick access, sedangkan di menu besar seperti pita ituuuhhh... Anda hanya dapat menyembunyikannya.

20. Pallet warna

Excel 2007 memberi lebih banyak pilihan warna dan kustomisasi pallet warna yang lebih mudah. Masalahnya... mungkin karyawan wanita Anda akan lebih lama memilih warna-warna itu sambil berteriak "...iiihhh lucuuuuuuu...." :D :D :D

21. Jumlah warna di workbook

Di Excel 2003, jumlah warna maksimal di workbook hanya 56. Sedangkan di Excel 2007 4.3 milyar. Hmmmm... Apakah Anda mau menggantikan Photoshop dengan Excel 2007? Perlukah warna sebanyak itu untuk pekerjaan pada sebuah spreadsheet? :)

22. Penggunaan pattern pada format cell dan chart

Jika di Excel 2003 Anda dapat menggunakan pattern yang bermacam-macam pada cell dan chart, di Excel 2007 Anda tidak akan menemukannya. Ini dapat menjadi masalah bagi penggemar art hitam putih atau pengguna printer laser :)

23. Sorting

Di Excel 2003 Anda hanya dapat melakukan sort denga fitur yang disediakan berdasarkan 3 field. [Di sini kita abaikan macro dan teknik tricky lainnya yang mungkin tidak akan digunakan oleh newbie]. Di Excel 2007... 64 field! Nilai plus buat Excel 2007! Uhmmmm... Ada yang pernah melakukan sort berdasarkan 10 field?

24. Item pada autofilter

Di Excel 2003, jumlah item pada autofilter hanya 1,024, jauh lebih sedikit dibandingkan Excel 2007 yang mampu menampilkan sebanyak 32,768. Yang satu ini memang benar-benar kelebihan Excel 2007, karena banyak kasus di dunia nyata dimana kita harus memfilter lebih banyak dari 1,024 item.

25. Jumlah huruf yang ditampilkan dan dicetak

Excel 2007 memberi lebih banyak karakter yang ditampilkan dan dicetak dalam 1 cell, yaitu 32,768. Angka ini jauh di atas Excel 2003 yang hanya memberikan 1,024. Tapi... jika Anda memerlukan karakter sebanyak itu dalam satu cell, mengapa tidak gunakan Microsoft Word? :D :D :D

26. Cell style unik

Style unik cell dalam sebuah workbook yang diizinkan di Excel 2003 hanya 4,000, sedangkan di Excel 2007 sebanyak 65,536. Setidaknya, Anda akan lebih jarang menemukan pesan "too many different cell formats" di Excel 2007.

27. Panjang formula

Panjang formula maksimal di Excel 2007 adalah 8,192 karakter, jauh lebih panjang dari Excel 2003 yang hanya mampu mengakomodasi 1,024 karakter. Saya sendiri jarang membuat formula sepanjang ratusan karakter... Pastinya di Excel 2007 Anda dapat membuat formula "terbilang" dan bukan macro "terbilang". Hehehe...

28. Maksimum loop dalam sebuah formula

Sampai Excel 2003, jumlah nested IF dalam sebuah formula misalnya, hanya dibatasi sampai 7 buah, sedangkan di Excel 2007 sampai 64 buah. Anyway, jika Anda sudah menemukan kondisi di mana Anda harus menulis =IF( IF( IF( IF( IF( .... )))))... mungkin sudah saatnya Anda mengganti formula Anda dengan VLOOKUP, INDEX, MATCH, atau formula lainnya.

29. Jumlah argument dalam formula

Seiring dengan kemampuan mengeksekusi formula yang lebih panjang, Excel 2007 juga mampu mengakomodasi sampai 255 argumen pada sebuah formula. Angka ini jauh lebih banyak dibandingkan dengan Excel 2003 yang hanya mampu mengakomodasi 30 argumen.

30. Interface keseluruhan

Excel 2007 membingungkan untuk pertama kali. Itu saja komentar saya. Anyway... mungkin ini karena terbiasa dengan tampilan Excel versi sebelumnya. Jadi... mari belajar lagi... di XL-mania tentunya. Hehehe...

31. Stabilitas

Ada beberapa file saya yang sering gagal disimpan di Excel 2007 atau dibuka di Excel 2007. Ada yang mengalami hal ini? Ada juga beberapa file .xlsx yang jika di zip dengan winzip atau winrar, saat diextract lagi menjadi pecahan file. Apakah ada yang mengalami ini juga?

32. Format XML

Penggunaan XML di Excel 2007 memudahkan integrasi data yang lebih baik antar file dan antar sistem. Selain itu, dengan format ini recovery file yang rusak lebih mudah, dan memungkinkan ukuran file lebih kecil.

33. Business dashboard / KPI dashboard / Performance dashboard

Dengan format chart yang menakjubkan di Excel 2007, fasilitas yang lain, dan kemampuan Anda mengolah chart, saya yakin Anda tidak memerlukan Xcelsius lagi. Lupakan saja training Excel dan Xcelsius seharga 5 juta per orang yang iklannya sering dipasang di koran Kompas itu. Hehehe.

34. Hubungan dengan rekan kerja

"Pak, tolong dong formatnya diganti ke Excel yang bukan 2007..." Familiar dengan kalimat ini? Selama pooling di kanan bawah tersebut masih menunjukkan pengguna Excel 2007 yang lebih sedikit... Siapkan selalu compatibility pack.

Install Excel 2007? Pilihannya di tangan Anda, karena kebutuhan tiap orang berbeda-beda.

menjabarkan data sesuai jumlah yang ditentukan

Pertanyaan:

"Hai semuaaaaaa... Saya lagi belajar Excel nih. Kemaren liat-liat arsip XL-mania. Keren banget yang filteran data unik pake rumusnya. Penasaran aja, kalo misalnya kebalikannya bisa ga? Jadi ada data, di kanannya ada jumlahnya. Dari dua kolom ini bisa muncul satu kolom lagi, isinya daftar dari data pertama tadi, tapi udah sesuai jumlah di kanannya. Kalo bingung coba deh liat attachmentnya. Thanx yaaaaa..... Kalo ga bisa ga papa kok... Tapi kalo bisa... Ntar ada
hadiahnya!" - Mulan Mei, Medan.

Jawab:
Uhm... Kirim aja hadiahnya! Hehehe.... Di E3 tulis: =IF(E2="item",$B$3,OFFSET($B$2,--(COUNTIF($E2:E$3,E2)=ROUND(VLOOKUP(E2,$B$3:$C$9,2,FALSE),0))+MATCH(E2,$B$3:$B$9,0),,,)), kemudian copy ke range E4 dan E15.

Penjelasan:

1. =IF(E2="item",$B$3... berfungsi melihat apakah cell tersebut adalah cell teratas, jika ya maka dikeluarkan data paling atas, yaitu $B$3, jika tidak ke langkah ke-2.
2. ...(COUNTIF($E2:E$3,E2)... berfungsi untuk melihat sudah berapa kali data di atas cell yang bersangkutan muncul.
3. ...VLOOKUP(E2,$B$3:$C$9,2,FALSE)... berfungsi untuk melihat berapa kali data ini ingin ditampilkan sesuai tabel pertama.
4. ...ROUND(...,0)... berfungsi untuk berjaga-jaga seandainya nilai pada kolom C bukan integer. Fungsi ini dapat dihilangkan jika semua angka pada kolom C adalah integer (tidak mengandung desimal atau pecahan).
5. ...COUNTIF(...)=ROUND(...)... berfungsi untuk melihat berapa banyak data yang sudah teraktualisasi di kolom E. Jika sudah sesuai dengan yang diinginkan pada langkah 3, maka akan menghasilkan nilai TRUE, jika belum akan menghasilkan FALSE.
6. ...MATCH(E2,$B$3:$B$9,0)... berfungsi untuk mencari urutan ke berapakah data E pada kolom B.
7. ...--... mengubah logika TRUE menjadi 1 dan FALSE menjadi 0.
8. Artinya jika langkah 5 sudah menghasilkan TRUE, maka dia akan menambahkan angka yang dihasilkan pada langkah 6, sehingga data yang ditampilkan berikutnya bergeser 1 baris dari hasil langkah 6. Cara menggesernya adalah dengan fungsi pada langkah ke-9.
9. ...OFFSET($B$2,x,)... berfungsi untuk menampilkan data pada x baris setelah $B$2, dimana x sendiri merupakan operasi

File:

Anda dapat mendownload materi tips ini di sini

[harus belajar Excel di XL-mania, member XL-mania, daftar dulu ya di XL-maniaaaaaaaaa!]