RumusHitung.Com

Hai sobat, bertemu lagi dengan RumusHitung.com. Semoga kalian tetap semangat dan sehat selalu. Kali ini RumusHitung akan membahas rumus – rumus dalam Excel. Berbagai belahan dunia sudah banyak yang menggunakan Ms Excel untuk melakukan perhitungan, karena dengan adanya Ms Excel kita bisa menghitung dengan mudah dan cepat.

Dalam pembelajaran Excel, RumusHitung akan memberikan penjelasan lengkap tentang rumus – rumus Excel. Ada banyak rumus excel yang bisa kalian pelajari disini.

Fungsi Excel

Ketika membuat pengujian dengan fungsi Excel, ketahui dulu operator logika berikut :

Fungsi Excel Logika

Fungsi ini digunakan untuk memaparkan hasil dari kondisi dua atau lebih yang ditetapkan berdasarkan syarat nilai tertentu. Dalam Ms Excel ada 11 fungsi logika yang perlu dipelajari, antara lain :

1. AND

Merupakan fungsi logika yang digunakan untuk meminta lebih dari kondisi disaat yang bersamaan. AND menampilkan TRUE atau FALSE. Misalkan, untuk menguji apakah angka di B7 lebih dari 70 dan kurang dari 97, maka rumusnya =AND(B7>70;B7<97). Bernilai TRUE jika semua kondisi mengevaluasi BENAR, bernilai FALSE jika semua kondisi mengevaluasi SALAH.

Penulisan rumus : =AND(logical1;[logical2];….)

Kondisi :

• logical1 – kondisi pertama yang dievaluasi.
• logical2 – kondisi kedua yang dievaluasi.

Contoh :

B7 adalah nilai 75, B7 lebih dari 70 dan kurang dari 97, maka gunakan rumus berikut :

=AND(B7>70;B7<97)

Fungsi AND juga bisa disematkan dalam fungsi IF. Masukkan AND sebagai logika untuk fungsi IF seperti gambar di bawah :

=IF(AND(B7>70;B7<97);”BENAR”;”SALAH”)

Seperti contoh gambar di atas bahwa rumus fungsi AND jika disematkan dalam fungsi IF akan mengganti tampilan TRUE menjadi BENAR dan FALSE menjadi SALAH.

2. FALSE

Fungsi FALSE adalah fungsi untuk menampilkan nilai logika FALSE tanpa harus memasukkan kondisi terlebih dahulu ke dalam sel. Microsoft memberi label fungsi FALSE sebagai fungsi kompatibilitas. Dalam Excel, fungsi FALSE untuk kompatibilitas dengan aplikasi spreadsheet lainnya.

Penulisan rumus : =FALSE()

Contoh :

Jika B7 adalah 75, maka B7 lebih dari 70 dan itu dianggap TRUE atau benar. Tetapi jika ditulis B7 kurang dari 70, padahal B7 itu bernilai 75, maka otomatis akan FALSE atau salah. Perhatikan contoh gambar berikut :

Pada gambar 1, B7 bernilai 75 namun rumusnya B7 kurang dari 70 dengan FALSE, maka akan tetap FALSE. Dengan penulisan rumus sebagai berikut :

=IF(B7<70;FALSE())

Pada gambar 2, sel B7 bernilai 75 dan rumusnya B7 kurang dari 70 dengan TRUE, maka masih tetap FALSE. Karena 75 kurang dari 70 sudah tentu salah. Dengan penulisan rumus sebagai berikut :

=IF(B7<70;TRUE())

3. IF

Fungsi IF untuk menjalankan pengujian logika dan menentukan kondisi suatu nilai untuk hasil yang BENAR dan nilai lainnya untuk hasil yang SALAH. Misal, pada C8 bernilai 90, untuk lulus nilai harus lebih dari sama dengan 70, maka penulisan rumusnya adalah =IF(C8>=70;”LULUS”;”GAGAL”). LULUS jika nilainya lebih dari sama dengan 70, dan GAGAL jika nilai kurang dari 70.

Penulisan rumus : =IF(logical_test;[value_if_true];[value_if_false]

Kondisi :

• logical_test – nilai atau logika yang dapat di evaluasi sebagai TRUE atau FALSE.
• value_if_true – (opsional) nilai yang akan di tentukan saat logical_test mengevaluasi TRUE.
• value_if_false – (optional) nilai yang akan ditentukan saat logical_test mengevaluasi FALSE.

Fungsi IF digunakan untuk menjalankan tes logika dan bereaksi berbeda tergantung apakah hasilnya BENAR (TRUE) atau SALAH (FALSE).

Pada contoh gambar 1, nilai di C5 lebih dari sama dengan 70 dengan evaluasi jika benar LULUS dan jika salah GAGAL. Maka C5 lebih dari sama dengan 70 berganti LULUS dan lainnya GAGAL. Dengan penulisan rumusnya :

=IF(C5>=70;”LULUS”;”GAGAL”)

Pada contoh gambar 2, nilai di C5 kurang dari 70 dengan evaluasi jika benar GAGAL dan jika salah LULUS. Maka C5 kurang dari 70 berganti GAGAL dan lainnya LULUS. Dengan rumus :

=IF(C5<70;”GAGAL”;”LULUS”)

Dari kedua contoh di atas, hasil keduanya sama tetapi rumusnya berbeda. Jika C5 bernilai 72 dan lebih dari sama dengan 70 maka value_if_true adalah LULUS dan value_if_false adalah GAGAL. Dan jika C5 bernilai 72 dan kurang dari 70 maka value_if_true adalah GAGAL dan value_if_false adalah LULUS.

Contoh gambar 3 merupakan rumus fungsi IF dengan opsional nilai yang ditentukan lebih dari satu, seperti contoh di atas dengan opsionalnya adalah A,B,C, dan D. Opsional ini untuk menentukan nilai apakah TRUE atau FALSE. Rumus dari contoh gambar 3, yaitu :

=IF(C5<50;”D”;IF(C5<70;”C”;IF(C5<90;”B”;”A”)))

Hingga 64 fungsi IF dapat digunakan pada fungsi excel lain. Namun, secara umum harus mempertimbangkan fungsi lain, seperti VLOOKUP atau HLOOKUP untuk skenario yang lebih kompleks, karena dapat menangani lebih banyak kondisi dengan cara yang jauh lebih efisien.

4. IFERROR

Fungsi Excel IFERROR menggantikan hasil eksekusi saat rumus mengalami kesalahan (error), dan hasil eksekusi standar saat rumus tidak mengalami kesalahan. IFERROR adalah cara efektif untuk memeriksa kesalahan tanpa menggunakan IF yang bertingkat yang lebih rumit.

Penulisan rumus : =IFERROR(value;value_if_error)

Kondisi :

• value – nilai, referensi atau rumus untuk memeriksa kesalahan.
• value_if_error – nilai yang digantikan jika ditemukan kesalahan.

Terdapat jenis kesalahan (error) pada pesan Excel, antara lain :

• #N/A – disebabkan karena sel yang dicari pada tabel lookup tidak ada atau tidak tersedia. Kondisi ini sering terjadi pada pemakaian fungsi Excel VLOOKUP
• #NAME? – disebabkan karena kesalahan dalam pengetikan formula, periksa pada formula untuk mengatasinya.
• #DIV/0! – disebabkan karena pada formula terdapat operasi pembagian dalam matematika, kondisi ini bisa terjadi jika sel pembagi atau penyebut kosong atau tidak mempunyai nilai.
• #NULL! – disebabkan karena kesalahan dalam menuliskan alamat sel. Contoh : A2 – C2 D2 ( terdapat spasi yang membuat error).
• #NUM! – disebabkan pada suatu formula yang memakai invalid number. Contoh saat melakukan perhitungan tanggal menggunakan rumus DATE.
• #REF! – disebabkan karena referensi pada sel telah dihapus, sehingga digunakan formula, tidak bisa dieksekusi.
• #VALUE! – disebabkan pemakaian tipe data yang salah pada formula. Contohnya dengan menggunakan teks pada formula namun memakai operasi matematika.

Contoh :

Pada gambar di atas, bahwa terdapat kesalahan dalam formula, yaitu operasi pembagian dalam matematika. Disebabkan karena sel pembagi tidak memiliki nilai (nol) atau kosong. Dengan adanya kesalahan itu terdapat pemberitahuan berupa #DV/0!. Untuk mengetahui kesalahan, bisa menggunakan fungsi IFERROR, seperti gambar di atas. Penulisan rumusnya seperti :

=IFERROR(B4/C4;”SALAH”)

Contoh rumus ini untuk mengetahui kesalahan atau memeriksa kesalahan pada formula. Untuk opsional value_if_error misalnya “SALAH”. Saat mengecek dengan menggunakan IFERROR, kita tahu kesalahan mana yang harus diperbaiki.

Jika bernilai standar, maka tidak ada kesalahan. Namun jika tidak bernilai, maka itu kesalahannya. Seperti contoh gambar di atas saat melakukan pengecekan menggunakan IFERROR, terdapat satu kesalahan yang tidak memiliki nilai dengan keterangan SALAH, dan yang lainnya adalah bernilai angka.

5. IFNA

Fungsi excel IFNA mengganti hasil kustom saat rumus menghasilkan kesalahan #N/A, dan hasil standar saat tidak ada kesalahan. Fungsi IFNA adalah cara efektif untuk menangani atau memeriksa kesalahan #N/A secara khusus tanpa menangkap kesalahan lainnya.

Penulisan rumus : =IFNA(value;value_if_na)

Kondisi :

• value – nilai atau rumus untuk memeriksa kesalahan.
• value_if_na – nilai yang akan diganti atau dihasilkan jika ditemukan kesalahan #N/A.

Gunakan fungsi IFNA untuk memeriksa kesalahan #N/A yang mungkin muncul dalam formula, terutama dalam melakukan pencarian menggunakan MATCH, VLOOKUP,HLOOKUP, dan lain – lain.

Contoh :

Pada gambar 1, kita mencari data yang bernama Lisa dari tabel data tersebut. Untuk mencari data bernama Lisa, harus menggunakan rumus fungsi IFNA dengan VLOOKUP. Bisa dilihat bahwa Lisa tercantum pada data tersebut. Penulisan rumusnya seperti :

=IFNA(VLOOKUP(“Lisa”;C14;D17;2;FALSE);”Tidak Ada”)

Gambar 2 merupakan contoh pencarian data yang bernama Shinta. Bisa dilihat bahwa Shinta tidak tercantum dalam data tabel tersebut. Oleh karena itu, saat menggunakan rumus IFNA terdapat kesalahan #N/A dengan keterangan Tidak Ada. Penulisan rumus seperti :

=IFNA(VLOOKUP(“Shinta”;C14;D17;2;FALSE);”Tidak Ada”)

Contoh gambar berikut merupakan pemeriksaan data menggunakan fungsi IFNA. Bisa dilihat perbedaan rumus pada formula.

6. IFS

Fungsi Excel IFS adalah fungsi yang menjalankan beberapa tes dan mengganti nilai sesuai dengan hasil TRUE pertama. Gunakan fungsi IFS untuk mengevaluasi beberapa kondisi tanpa menggunakan pernyataan IF bertingkat. Fungsi IFS memungkinkan rumusnya yang pendek dan mudah dibaca.

Penulisan rumus : =IFS(test1;value1,[test2;value2];….]

Kondisi :

• test1 – tes logika pertama.
• value1 – hasil saat test1 TRUE.
• test2;value2 – (opsional) pasangan tes atau tes logika kedua dan hasil saat test2 TRUE.

Perhatikan gambar di bawah :

Contoh gambar di atas merupakan tabel perbandingan nilai dengan menggunakan rumus IFS. Misal opsional pengganti nilainya adalah A, B, C, D. Penulisan rumus seperti :

=IFS(D14<60;”D”;D14<70;”C”;D14<90;”B”;D14>=90;”A”)

IFS merupakan fungsi baru, tersedia pada Microsoft 365 dan Excel 2019.

Gunakan fungsi IFS untuk menguji beberapa kondisi dan mengganti nilai yang sesuai dengan hasil TRUE pertama. Berbeda dengan IF, fungsi IFS dapat menguji beberapa kondisi pada saat bersamaan tanpa membuat beberapa pernyataan IF bertingkat. Oleh karena itu, rumus IFS lebih pendek dan efektif serta lebih mudah dibaca dan ditulis.

7. NOT

Fungsi excel NOT adalah fungsi yang menggantikan kebalikan dari nilai logika yang diberikan. Saat di berikan TRUE, maka NOT akan mengganti FALSE. Saat diberikan FALSE, NOT akan mengganti TRUE.

Penulisan rumus : =NOT(logical)

Kondisi :

• logical – nilai yang dapat di evaluasi sebagai TRUE atau FALSE.

Contoh :

Pada gambar tersebut bisa dilihat bahwa fungsi NOT merupakan pengganti TRUE atau FALSE jika logika BENAR atau SALAH. Misal, sel E7 kolomnya kosong(ISBLANK), jadi saat menggunakan rumus NOT akan menjadi FALSE. Ada tambahan NOT kemudian ISBLANK, menjadi tidak kosong. Padahal E7 kosong, berarti hasilnya FALSE atau SALAH. Penulisan rumus seperti :

=NOT(ISBLANK(E7))

Pada gambar 2, misal sel D6 kolomnya tidak kosong (NOTBLANK) maka saat menggunakan rumus NOT akan menjadi TRUE. Tambahan NOT dan ISBLANK menjadi tidak kosong. Jadi, hasilnya TRUE atau BENAR karena D6 terdapat tulisan dan tidak kosong. Penulisan rumus seperti :

=NOT(ISBLANK(D6))

8. OR

Fungsi OR adalah fungsi logika untuk menguji beberapa kondisi secara bersamaan. OR mengganti TRUE atau FALSE. Misalnya, untuk menguji F6 untuk “merah” atau “kuning”, gunakan =OR(F6=”merah”;F6=”kuning”). Fungsi OR dapat juga digunakan untuk pengujian logika dalam fungsi IF untuk menghindari IF bertumpuk, dan dapat juga digabungkan dengan fungsi AND.

Penulisan rumus : =OR(logical1;[logical2];….)

Kondisi :

• logical1 – kondisi pertama atau nilai logika pertama untuk dievaluasi atau dieksekusi.
• logical2 – (opsional) kondisi kedua atau nilai logika kedua untuk dieksekusi.

Gunakan fungsi OR untuk menguji beberapa ketentuan secara bersamaan, sampai total 255 ketentuan.

Misalnya, untuk menguji apakah nilai di A4 atau nilai A5 lebih besar dari 75, gunakan rumus :

=OR(A4>75;A5>75)

Jika memasukkan OR sebagai rumus larik, kita dapat menguji semua nilai dalam suatu rentang terhadap suatu kondisi. Misal, rumus array ini akan mengganti TRUE jika ada sel di B1 : B99 lebih besar dari 20 :

={OR(B1:B99>20)}

Catatan : rumus ini adalah rumus array dan harus dimasukkan dengan shortcut CTRL + SHIFT +ENTER, kecuali Excel 365.

9. SWITCH

Fungsi excel SWITCH adalah fungsi untuk membandingkan satu nilai dengan daftar nilai, dan mengganti hasil yang sesuai dengan kecocokan pertama. SWITCH dapat mengganti nilai default opsional jika tidak ada kecocokan yang ditemukan.

Penulisan rumus :

=SWITCH(expression;val1/result1;[val2/result2];…;[default])

Kondisi :

• exspression – nilai atau ekspresi yang akan dicocokkan.
• val1/result1 – nilai pertama dan pasangan hasil.
• val2/result2 – (optional) nilai kedua dan pasangan hasil.
• default – (opsional) nilai default untuk digunakan apabila tidak ada kecocokan yang ditemukan.

Contoh :

Pada gambar di atas merupakan contoh menggunakan fungsi SWITCH. Misal, SWITCH melakukan pencocokan sesuai dalam tabel kanan untuk mencocokan pada tabel bagian kiri.

SWITCH tidak menyertakan operator logika seperti lebih besar dari (>), lebih kecil dari (<) dalam logika yang digunakan untuk melakukan pencocokan. Jika ingin digunakan operator logikanya, gabungkan SWITCH dengan membuat rumus untuk dicocokkan dengan TRUE seperti :

=SWITCH(TRUE;A1>=1000;”Emas”;A1>=500;”Silver”;”Perunggu”)

10. TRUE

Fungsi excel TRUE mengganti nilai TRUE. TRUE di definisikan sebagai fungsi kopatibilitas, dan tidak diperlukan dalam banyak kasus.

Penulisan rumus : =TRUE()

Dalam fungsi TRUE, sebagai contoh gambar di atas bahwa sel D5 bernilai 56. Dirumuskan dengan fungsi TRUE dengan membandingkan operator logika lebih besar dari 60 maka hasilnya FALSE karena D5>60 itu SALAH.

Contoh gambar 2 menunjukkan sel D6 yang bernilai 67. Dirumuskan dengan fungsi TRUE dengan operator lebih besar dari 60 maka hasilnya TRUE karena D6>60 itu BENAR.

Jika memasukkan TRUE atau memberikan TRUE sebagai hasil dalam rumus, cukup gunakan kata TRUE secara langsung ke dalam rumus dan Excel akan menafsirkan sebagai nilai logika TRUE. Misal, rumus ini sangat identik secara fungsional :

=IF(D6>60;TRUE())

=IF(D6>60;TRUE)

Perhatikan juga bahwa ekspresi logika itu sendiri akan secara otomatis manghasilkan hasil TRUE dan FALSE. Misalnya, rumus di sel D6 :

Bisa dilihat contoh gambar di atas, bahwa tanpa rumus pun juga bisa dan Excel secara otomatis menafsirkan. Begitupun pada sel D5 dengan operator logika lebih besar dari 60 maka menghasilkan FALSE.

11. XOR

Fungsi XOR melakukan apa yang di sebut “OR eksklusif”. Dengan dua pernyataan logika, XOR mengganti TRUE jika salah satu pernyataan BENAR, tetapi mengganti FALSE jika kedua pernyataan itu BENAR. Jika tidak ada yang BENAR, XOR juga mengganti FALSE.

Penulisan rumus : =XOR(logocal1;[logical2];…)

Kondisi :

• logical1 – ekspresi, konstanta, atau referensi yang mengeksekusi TRUE atau FALSE.
• logical2 – (opsional) ekspresi, konstanta, referensi yang dieksekusi ke TRUE atau FALSE.

Contoh :

Dengan lebih dari dua logika,XOR hanya mengganti TRUE ketika jumlah logika TRUE ganjil, seperti yang diperlihatkan dalam contoh berikut :

Contoh gambar di atas terdapat tabel untuk mencari jumlah angka TRUE ganjil. Penulisan rumus seperti :

=XOR(D4:H4)

Demikian mengenai rumus excel, semoga menambah wawasan, pemahaman, dan pengetahuan. Selamat tinggal dan terima kasih.