Halo sobat.. Ketika membuat sebuah rumus pada Microsoft Excel, kita tidak bisa lepas sepenuhnya dari yang namanya error. Ya error tidak selalu bisa dihindari. Ia sering hadir meski tidak diinginkan. Ia sering datang meski tidak diundang. Jangan khawatir, kita tidak perlu menghindar dan berlari. Error tidak mungkin dibuang selamanya, Temani saja dia. Tapi ingat, jangan sampai kamu terbawa error juga. Seperti pembahasan kali ini: Menjumlahkan Tanpa Dipengaruhi Oleh Error Dengan Fungsi Aggregate.
Di excel, cobalah mengetik sebuah rumus. Rumus SUM misalnya. Kemungkinan besar sobat sudah tahu. Fungsi SUM biasanya menjumlahkan data pada referensi berupa range sel.
Apa yang akan terjadi kalau salah satu sel dalam referensi itu ada nilai error? Anggaplah error #N/A, #REF!, #DIV/0! dan sebagainya. ? Maka rumus yang kita buat juga akan menghasilkan nilai error.
Seperti diperlihatkan dalam gambar contoh rumus di bawah ini.
Perhatikan gambar di atas. Kenapa rumus =SUM(C2:C6)menghasilkan nilai error? Coba sobat telusuri pada referensi yang digunakan oleh rumus SUM, Yaitu range C2:C6. Oh.. ternyata ada salah satu sel berisi nilai error #N/A. Akibatnya rumus yang menggunakan referensi tersebut menjadi error. Padahal cuman satu sel saja. Nila Setitik Rusak Susu Sebelanga.
Apakah ada solusi untuk hal ini? Tentu saja. Excel sudah menyediakan seperangkat fungsi yang memungkinkan user bisa berdamai dengan error.
Dalam kasus ini, sobat bisa menggunakan fungsi AGGREGATE.
Caranya: ketik rumus =AGGREGATE(9;6;C2:C6)
Seperti terlihat dalam gambar di bawah ini
Keterangan argument rumus;
Argument ke-1: Angka 9 merupakan konstanta untuk memberitahu fungsi AGGREGATE supaya memproses data dalam referensi dengan fungsi SUM.
Argument ke-2: Angka 7 merupakan konstanta untuk memberitahu fungsi AGGREGATE supaya tidak menghiraukan keberadaan nilai error dalam referensi yang akan diproses dalam fungsi AGGREGATE.
Argument ke-3: range C2:C6 merupakah referensi sel-sel berisi data yang akan diproses penjumlahan.
Sampsi sini mudah-mudahan sobat sudah bisa memahami bagaimana membuat rumus penjumlahan bebas dari error.
Contoh rumus yang dijelaskan di atas dapat digunakan sebagai alternatif rumus SUM. Sobat bisa berexperiment dengan memanfaatkan fungsi AGGREGATE untuk menghitung rata - rata (AVERAGE), nilai maksimal (MAX), nilai minimal (MIN) dan sebagainya. Caranya: sobat tinggal atur saja argument-argument fungsi yang digunakan. Mudah bukan?
Kamu juga mungkin tertarik dengan pembahasan dalam link di bawah ini?
Pernahkan anda mengalami kasus rumus excel yang tidak berfungsi, tidak bekerja atau tidak mengkalkulasi dengan benar padahal sudah dituliskan dengan benar?
Bagi user excel yang belum mengetahui akar permasalahannya mungkin akan merasa sedikit frustasi dan bertanya-tanya, kenapa ini?
Tenang… Coretan berikut akan mengupas tuntas beberapa permasalahan yang menyebabkan rumus excel tidak menjalankan fungsinya untuk mengkalkulasi dengan benar.
Silahkan di cek juga tutorialnya dalam video berikut:
Biasanya kita baru menyadari rumus excel yang tidak bekerja ketika merubah salah satu data pada referensi sel tetapi nilai dalam cell lainnya yang terkait tidak ikut berubah. Dengan kata lain hasil kalkulasinya tidak terupdate serta merta secara otomatis
Kasus ini juga dapat ditandai ketika mengcopy rumus dari sebuah sel ke sel lainnya. Nilai dalam sel yang di-paste tetap sama dengan sel yang di-copy meskipun seharusnya hasilnya berbeda.
Apakah penyebabnya?
Setidaknya ada 2 hal yang paling sering menyebabkan rumus excel tidak berfungsi atau tidak mengkalkulasi dengan benar, yaitu:
1.Automatic Calculation tidak aktif
2.Adanya Circular Reference
Baiklah mari kita telaah satu persatu dari kedua masalah tersebut yang menyebabkan rumus excel tidak tidak bekerja semestinya.
Rumus Excel Tidak Berfungsi Karena Automatic Calculation Tidak Aktif.
Update nilai hasil rumus dalam excel sebenarnya melalui sebuah proses kalkulasi yang secara default biasanya terupdate otomatis jika ada perubahan nilai dalam referensi rumus. Namun proses kalkulasi ini tidak selamanya dilakukan secara otomatis.
Nah hal inilah yang kadang tidak disadari atau tidak diketahui oleh pengguna excel, sehingga sebagian user (mungkin) merasa sedikit frustasi begitu menghadapi kasus ini.
Dalam excel sebenarnya ada 3 pilihan setting kalkulasi yaitu:
Automatic : Semua nilai dari rumus excel akan dikalkulasi otomatis setiap kali ada perubahan. Update otomatis terjadi pada semua sheet dan semua file excel yang sudah dibuka
Automatic Except For Data Table: Update otomatis hanya pada sel yang ada keterkaitan rumus dengan sel yang dirubah nilainya
Manual: Rumus excel tidak akan dikalkulasi sebelum dieksekusi secara manual (menekan F9)
Dari ketiga hal di atas, penyebab kenapa rumus excel tidak bekerja dengan benar adalah karena calculation setting-nya di set manual. Jadi, sebenarnya rumus excel tersebut bukan tidak berfungsi, tetapi diberhentikan sementara proses kalkulasinya sebelum diperintahkan oleh user secara manual.
Pertanyaanya: Jika default setting kalkulasi adalah Automatic. Lalu kenapa setting kalkulasi di excel anda tiba-tiba bisa berubah menjadi manual?
Pertama: Hal ini bisa disebabkan karena anda membuka file excel dimana author atau pembuat file tersebut sengaja men-setting kalkulasi manual. Jika file tersebut dibuka pertama kali maka rumus excel dalam file lainnya yang dibuka berikutnya juga tidak dapat terkalkulasi secara otomatis meskipun tidak di-setting manual.
Kedua: Setting kalkulasi excel berubah menjadi manual bisa juga disebabkan karena adanya prosedur macro yang dijalankan untuk merubah calculation setting menjadi manual. Macro ini bisa berjalan, baik dengan sepengetahuan pengguna (misalnya dengan menekan tombol tertentu) maupun tidak.
Berikut contoh macro yang bisa merubah secara diam-diam setting kalkulasi menjadi manual pada saat sebuah file yang mengandung code macro tersebut dibuka. Jadi hati-hatilah dalam menggunakan file macro excel.
Private Sub Workbook_Open() Application.Calculation = xlCalculationManual End Sub
Kedua hal penyebab perubahan seting kalkulasi menjadi manual secara tiba-tiba seringkali tidak disadari. Pengguna excel biasanya baru menyadarinya ketika menemukan nilai rumus excel tidak terupdate seketika ketika ada perubahan pada salah satu sel yang terkait ke rumus.
Bagaimana Cara Mengatasinya?
Untuk mengakali masalah ini, mungkin beberapa user yang menyarankan untuk memperbaiki satu persatu sel yang nilai nya “tidak nyambung” dengan rumusnya. Caranya dengan menekan F2 dilanjutkan menekan ENTER.
Cara tersebut memang dapat menghasilkan kalkulasi yang benar. Tapi ingat “hanya untuk satu sel saja” per satu kali proses, bagaimana jika ada ribuan rumus dalam lembar kerja excel anda? Cara tersebut tentunya bukan solusi yang tepat karena tidak menyelesaikan akar masalahnya, dan hanya buang-buang waktu saja.
Karena akar permasalahannya adalah seting kalkulasi, maka solusi yang tepat adalah mengatur settingan excel dengan benar, yaitu: Ubah calculation setting menjadi Automatic
Untuk excel 2003 dapat dilakukan melalui menu Tools –> Options… . Kemudian pilih tab Calculation dan pilih Automatic. Lalu tekan OK sesuai ilustrasi berikut:
Sedangkan untuk Excel 2007, 2010, 2013 dan 2016, seting kalkulasi dapat dilakukan melalui Tab Formulas, kemudian klik Calculation Options dan pilih Automatic.
Perhatikan ilustrasi berikut menggunakan excel 2007, ini mirip dengan excel 2010 s.d 2016
Merubah setting kalkulasi manual menjadi otomatis juga dapat dilakukan menggunakan macro. Berikut contoh code macro-nya:
Sub setKalkulasiOtomatis()
Application.Calculation = xlCalculationAutomatic
End Sub
Kita juga dapat mempertahankan proses kalkulasi secara manual jika dikehendaki. Hal ini dapat dilakukan dengan menekan F9. Cara ini juga sangat efektif untuk memastikan bahwa penyebab rumus tidak terupdate pada excel adalah Memang karena seting kalkulasi manual. Jika ada penyebab lain maka F9 tidak akan berfungsi. Seandainya ini terjadi maka perlu dicari lagi penyebab lainnya.
Rumus Excel Tidak Berfungsi Karena Circular Reference
Kasus yang satu ini pun cukup sering dialami oleh pengguna excel biasanya terjadi karena kesalahan yang tidak disengaja ketika mengetik atau meng-copy rumus. Jika anda menerima “pesan cinta” seperti ilustrasi berikut pada saat bekerja dengan excel, maka dipastikan bahwa spreadsheet anda sedang mengalami masalah circular reference.
Microsoft Office Excel cannot calculate a formula. Cell references in the formula refer to the formula’s result, creating a circular reference. Try one of the following:
If you accidentally created the circular reference, click OK. This will display the Circular Referece toolbar and help for using I to correct your formula
For more information about circular reference and how to work with them, click help
To continue leaving the formula as its, click Cancel
Circular Reference terjadi jika formula dalam sebuah sel mengambil referensi dari sel itu sendiri baik secara langsung maupun secara tidak langsung.
Contoh : jika pada sel A2 dituliskan rumus =SUM(A1:A20) maka dapat dikatakan bahwa rumus tersebut bersifat circular reference karena sel A2 merupakan bagian dari range A1:A20.
Meskipun masalahnya hanya pada satu sel dan terletak dalam sheet yang berbeda.Kadang-kadang hal ini bisa menyebabkan problem kalkulasi pada bagian lainnya dalam sebuah file excel.
Lalu bagaimana cara mengatasi masalah Circular Reference?
Untuk mengatasi masalah ini tentunya kita harus langsung bertindak pada akar masalahnya yaitu sel – sel yang memiliki rumus circular reference.
Pada saat kita membuat kesalahan dengan membuat rumus circular reference, biasanya excel langsung memberitahu kita dengan “pesan cinta” seperti ilustrasi di atas. Jika begini, kita bisa langsung memperbaiki rumus pada saat itu juga.
Namun bagaimana jika rumus circular reference ini dibiarkan dari awal, dan kita tidak tahu lagi dimana posisi selnya? Jika kita ingin memecahkan problem ini dengan tuntas, maka mau tidak mau, biang kerok rumus circular reference harus ditemukan. Bagaimana caranya?
Menemukan Circular Reference pada Excel 2003:
klik menu View – Toolbars – Circular Reference
Jika circular reference tidak ditemukan dalam list toolbars, maka kita harus memunculkannya melalui customize toolbar dengan cara:
Klik menu View –> Toolbars –> Customize –> kemudian aktifkan ceklist circular reference
Setelah langkah diatas, maka akan muncul tool bar Circular Reference:
Dengan menggunakan bantuan toolbars inilah kita dapat menemukan biang kerok sel-sel yang memiliki rumus circular reference. Caranya: klik kotak list yang akan menunjukan kepada kita sel-sel yang bermasalah circular reference, pergi ke semua sel bermasalah tersebut dan perbaiki rumusnya.
Menemukan Circular Reference pada Excel 2007, 2010, 2013 dan 2016
Untuk excel versi ini caranya lebih simple lagi: Masuk ke tab Formulas –> klik Error Checking – Circular References –> dijumpai list sel circular –> klik dan perbaiki rumus dalam sel-sel tersebut.
Ketahui akar masalah dan perbaki. Inilah apa yang ingin disampaikan penulis dari artikel excel ini. Dan untuk mengetahui akar masalah excel, kita harus memahami cara kerja excel itu sendiri. Dari coretan singkat ini diharapkan dapat menambah pemahaman kita tentang cara kerja rumus atau formula excel.
Harapan spesifik dari tulisan ini adalah pembaca dapat memahami kenapa rumus excel tidak berfungsi meskipun sudah dituliskan dengan benar. Dari inilah maka kita bisa mencari solusi yang tepat, cepat dan tuntas untuk memecahkan masalahnya.
