Hai gaess... Pernah ngalamin engga sih sudah membuat rumus excel tapi setelah tekan enter ga berfungsi? Kalo penulis pernah dan baru kemarin-kemarin aja belum lama ini.. Singkat cerita, penulis diminta tolong rekan kerja sekantor untuk membuat rumus perkalian dengan sel yang terkunci. Setelah dibuat, rumus itu tidak berjalan. Penulis sempat merasa bingung karena sejak dahulu rumus tersebut seperti itu dan selalu benar. Untuk memastikan kembali, browsing rumus tersebut dan hasilnya masih sama. Akhirnya diputuskan untuk browsing tentang settingan excel dan ternyata benar, malasahnya adalah pada settingan excel.
Untuk mengatasi masalah ini ada 2 cara:
Tekan F2 dilanjutkan menekan enter
Cara ini akan membuat masalah baru karena data yang banyak harus menekan F2 dilanjutkan enter satu per satu.
Atur setting kalkulasi melalui Tab Formulas, kemudian klik Calculation Options dan pilih Automatic.
Dengan cara ini, rumus excel dengan data yang begitu banyak secara otomatis akan berfungsi dengan benar.
Selesai, hanya itu saja pengaturannya.
Fungsi VLOOKUP mempunyai beberapa kelemahan, salah satunya adalah fungsi VLOOKUP hanya akan mengembalikan nilai dari data yang pertama kali ditemukan didalam table_array. Jika ada beberapa data yang sama dalam table_array maka data ke-2, ke-3 dst tidak akan pernah bisa ditampilkan oleh fungsi VLOOKUP.
Misal ada data seperti gambar dibawah.
Ada nama orang tua dan nama anak. Satu orang tua mempunyai beberapa anak. Urutan anak sesuai dengan urutan pencatatannya.
Pertanyaannya siapakan nama anak ke-2 “Ahmad”?
Fungsi apakah yang akan digunakan untuk menyelesaikan pertanyaan ini?
Fungsi VLOOKUP tidak bisa digunakan untuk menyelesaikan pertanyaan ini, karena hasil fungsi VLOOKUP akan selalu menghasilkan nama anak pertama, karena memang inilah kelemahan dari fungsi VLOOKUP.
Solusi dengan fungsi COUNTIF dan kolom bantuan
Untuk bisa menghasilkan nama anak ke-2, ke-3 dst harus dibantu dengan kolom bantuan dan fungsi COUNTIF. Trik ini untuk menyiasati kelemahan fungsi VLOOKUP.
Kolom bantuan akan berisi data unik baru yang memungkinkan fungsi VLOOKUP untuk menampilkan data ke-2, ke-3 yang ditemukan. Kolom bantuan berisi nama orang tua yang digabung dengan data berapa kali nama orang tua tersebut muncul.
Untuk lebih jelasnya lihat gambar dibawah.
Cell A4 berisi nama “Ahmad” dan angka 1, artinya nama “Ahmad” dicell B4 baru pertama kali muncul.
Cell A8 berisi nama “Ahmad” dan angka 2, artinya nama “Ahmad” dicell B8 adalah nama “Ahmad” kedua yang muncul, setelah nama “Ahmad” dicell B4.
Cell A9 berisi nama “Ahmad” dan angka 3, artinya nama “Ahmad” dicell B9 adalah nama “Ahmad” ketiga yang muncul, setelah nama “Ahmad” dicell B4 dan B8.
dst…
Formula yang digunakan dikolom bantuan adalah sebagai berikut:
=B2&”|”&COUNTIF($B$2:B2,B2)
Perhatikan parameter range fungsi COUNTIF. Awal range menggunakan absolute reference dan akhir range menggunakan relative reference, dengan cara tersebut bisa menghitung berapa kali nama orang tua muncul pada posisi masing-masing nama.
Ketik formula tersebut dicell A2 kemudian kopi dan paste dirange A3:A10.
Hasilnya seperti gambar kedua diatas.
Setelah kolom bantuan berhasil dibuat, waktunya merangkai fungsi VLOOKUP.
Letakkan kursor dicell F4.
Ketik formula untuk fungsi VLOOKUP
Parameter lookup_value diisi dengan gabungan nama orangtua dan anak ke- berapa yang ingin dicari. Formula yang dihasilkan menjadi seperti berikut:
=VLOOKUP(F1&”|”&F2
Parameter table_array diisi dengan range A2:C10
Parameter col_index_num diisi dengan angka 3
Parameter range_lookup diisi dengan nilai FALSE
Formula yang dihasilkan menjadi seperti berikut:
=VLOOKUP(F1&”|”&F2,A2:C10,3,FALSE)
Jika sudah tekan tombol ENTER. Hasilnya seperti gambar dibawah.
Nama anak kedua “Ahmad” adalah “Khalid Ahmad”.
Dengan sedikit trik dan kolom bantuan kelemahan fungsi VLOOKUP bisa dihilangkan 😉
Home / Menu-Excel / Office-Button
Mengatasi Rumus Excel Tidak Berfungsi Otomatis - Adakalanya saat menggunakan rumus untuk menghitung angka hasilnya tidak otomatis muncul.
Kondisi tersebut bisa disebabkan oleh beberapa hal baik terkait dengan setting ataupun salah penggunaan rumus.
Secara default memang Excel akan setting otomatis untuk penghitungan pada pengolahan angka.
Tetapi ada kalanya hitungan tersebut berunah menajdi manual atai tidak secara otomatis seperti biasanya.
Untuk mengatasi hal tersebut mari kita membahasnya dalam artikel ini, pembahasan terdiri dari dua bagian yaitu penyebab rumus Excel tidak berfungsi serta solusi untuk mengatasinya.
Sebelum mencoba mengatasi rumus Excel yang tidak berfungsi hal pertama yang harus kita ketahui adalah penyebab dari tidak berfungsinya rumus tersebut.
Jika salah menganalisa penyebabnya maka kita juga mungkin akan salah dalam pengambilan solusinya sehingga setelah diperbaiki tetap saja rumus tersebut tidak berfungsi.
Secara umum menurut saya ada tiga penyebab kenapa sebuah rumus dalam Excel tidak berfungsi dengan benar.
Adapun penyebab tidak berfungsinya rumus Excel tersebut adalah sebagai berikut :
1. Error Circular Reference
Error Circular reference ini akan otomatis muncul jika terjadi penjumlahan atau pengurangan angka secara berulang.
Penjumlahan atau pengurangan secara berulang ini memang tidak direkomendasikan dalam Excel.
Error ini akan ditandai dengan muncul error Circular Reference warning serta tanda panah yang menunjukan adalah penghitungan berulang dalam rumus yang kita gunakan.
Untuk mengatasi rumus yang tidak berfungsi karena penyebab ini adalah mengaktifkan Circular Reference pada Excel Option.
Cara mengaktifkan Circular Reference dalam Excel Option ini sudah saya jelaskan dalam artikel sebelumnya.
2. Kesalahan Penggunaan Rumus
Kemungkinan penyebab tidak berfungsinya sebuah rumus Excel adalah kesalahan penggunaan rumus.
Kesalahan jenis ini biasanya ditandai dengan munculnya pesan kesalahan dengan kode tertentu.
Biasanya setiap kode memiliki penyebab error yang berbeda - beda sehingga solusinyapun berbeda - beda.
Beberapa pesan kesalahan yang sering muncul diantaranya adalah #REF!, #NUM!, #VALUE! dan lain - lain.
Untuk cara mengatasi masing - masing error sudah saya bahas dalam kategori Mengatasi Error Rumus dan silahkan untuk dipelajari.
3. Calculation Option Disetting Manual
Penyebab yang ketiga ini mungkin merupakan penyebab yang cukup sering dialami oleh pengguna Excel.
Secara default excel memasang opsi rumus pada Automatic sehingga setelah kita menggunakan rumus maka hasilnya akan otomatis muncul.
Jika Calculation Option disetting menjadi manual maka update atau perubahan angka pada cell tidak akan berpengaruh terhadap totalnya.
Misalnya kita menjumlahkan cell A1 dan A2 yang masing memiliki angka 10 dan 20.
Dengan rumus Excel SUM atau penjumlahan biasa maka akan didapatkan total 30 pada cell A3.
Nah,, jika setting Calculation Option adalah Manual kemudian kita mengubah angka 20 dalam cell A2 menjadi 30 maka total dalam cell A3 tetap akan 30 bukan 40.
Untuk cara mengatasi rumus Excel yang tidak berfungsi dengan penyebab ini mari kita membahasnya secara detail.
Untuk mengubah setting Manual menjadi Atomatic dalam Calculation Option terdapat dua cara yang bisa kita pilih.
Cara yang pertama adalah melalui menu Calucation Options yang terdapat dalam Tab Formulas.
Cara yang kedua adalah melalui menu Excel Option yang terdapat dalam kelopok Office Button.
Sebelum melihat langkah - langkahnya saya akan jelaskan menu yang muncul dalam Calculation Option sebagai berikut :
- Automatic : semua rumus Excel baik penjumlahan, pengurangan atau yang lainnya akan otomatis berubah jika ada update atau perubahan pada salah satu atau beberapa cell yang ada dalam area penjumlahan tersebut
- Automatic Except for Data Tables : update otomatis hanya silakukan pada rumus yang terkait dengan perubahan angka saja
- Manual : penjumlahan atau rumus tidak akan update kecuali dilakukan dengan menekan tombol F9
Silahkan sesuaikan pilihan berdasarkan tiga menu diatas dan jika ingin penghitungan rumus Excel dilakukan secara otomatis silahkan pilih menu Automatic.
Langkah - langkah untuk mengubah setting Calculation Option melalui Tab Formulas adalah sebagai berikut :
- Klik Tab Formulas
- Cari dan temukan kelompok Calculation disebelah kanan layar komputer
- Klik Calculation Options
- Klik Automatic
Jika kesulitan menemukan menu Calculation Options pada Tab Formulas silahkan perhatikan gambar berikut ini :
Dengan mengikuti keempat langkah diatas maka seluruh penghitungan Excel akan dilakukan secara otomatis.
Selanjutnya langkah - langkan mengubah setting Calculation Option melalui Excel Option adalah sebagai berikut :
- Klik menu Office Button disebelah kiri atas layar komputer
- Klik Excel Options
- Klik Formulas
- Klik pilihan Automatic pada kelompok menu Calculation Options
- Klik OK
Cara yang kedua juga sebenarnya hampir sama dengan cara yang pertama hanya saja posisi menunya yang berbeda.
Silahkan pilih dari kedua cara tersebut diatas mana yang kira - kira menurut kita paling mudah untuk diikuti.
Itulah pembahasan kita kali ini tentang mengatasi rumus Excel yang tidak berfungsi dan semoga artikel ini bermanfaat untuk semua pembaca.
Newer Posts Older Posts