(Rumus if gabungan (IF-AND/IF-OR) - Diantara fungsi logika excel, Fungsi IF merupakan salah satu fungsi microsoft excel yang sering kita gunakan.
Dalam kasus-kasus rumus excel tertentu, fungsi IF perlu kita gabungkan bersama fungsi lain misalnya dengan fungsi OR dan fungsi AND. Hal ini kita dibutuhkan untuk memenuhi sebuah kondisi atau kriteria tertentu.
Misalnya dalam kasus nilai ujian, seorang anak dinyatakan lulus jika beberapa mapel memenuhi kriteria nilai tertentu.
Contoh lain misal dalam kasus penerimaan pegawai, seorang calon pegawai dinyatakan lulus administrasi jika memenuhi beberapa syarat tertentu.
Dua contoh kasus diatas jika kita menggunakan excel sebagai aplikasi pengolah datanya maka akan lebih mudah jika kita menggunakan rumus gabungan IF-AND atau IF-OR.
Untuk lebih memahami bagaimana menggunakan fungsi-fungsi excel tersebut ke dalam rumus excel, kali ini kita pelajari bersama cara menggunakan rumus gabungan tersebut. Namun, sebelum itu ada baiknya anda membaca tutorial atau cara menggunakan fungsi-fungsi tersebut pada artikel belajar excel sebelumnya. Sehingga rumus gabungan IF-OR, IF-AND maupun gabungan ketiganya akan lebih mudah kita fahami.
- Rumus Gabungan IF-OR
- Rumus Gabungan IF-AND
- Rumus Gabungan IF, OR dan AND
Cara Menggunakan Rumus IF-OR Di Excel
Bagaimana cara menggunakan rumus IF-OR Excel?
Fungsi IF digunakan untuk menjalankan sebuah rumus excel tertentu jika kondisi atau syarat tertentu terpenuhi dan menjalankan sebuah rumus excel lainnya jika kondisinya tidak terpenuhi.
Sintak atau cara penulisan fungsi IF adalah :
IF(Tes_Logika; Jika_Bernilai_TRUE; Jika_bernilai_FALSE)Sedangkan Fungsi OR digunakan untuk menghasilkan nilai logika TRUE (Benar) jika salah satu syarat atau kriteria yang disebutkan bernilai TRUE (Benar).
Sintaks Fungsi OR adalah:
OR(Uji_Logika1; Uji_Logika2; ...)Dalam rumus gabungan ini fungsi OR digunakan untuk mengisi uji logika atau tes_logika pada fungsi IF sehingga jika digabung sintaksnya akan menjadi:
IF(OR(Uji_Logika1; Uji_Logika2; ...);Jika_Bernilai_TRUE; Jika_bernilai_FALSE)Untuk melakukan uji logika tentunya diperlukan operator logika yang digunakan pada excel. Tentang operator logika ini sudah saya jelaskan pada artikel yang lain, sehingga tidak perlu saya bahas lagi.
Agar lebih mudah difahami kita langsung ke contoh soal saja.
Ada data pelamar kerja, Dari lamaran dan hasil test wawancara yang masuk yang diterima hanya yang memiliki ijasah S1 atau yang nilai wawancaranya diatas 80. Tentukan siapa saja yang akan diterima.
Data lamaran pekerjaan tersebut sebagai berikut:
Dari data diatas pelamar yang LULUS adalah yang pada kolom C berisi S1 atau pada kolom D berisi nilai lebih dari 80. Pelamar yang tidak memenuhi syarat dinyatakan TIDAK LULUS.
Dari data diatas masukkan rumus excel berikut pada sel E2:
=IF(OR(C2="S1";D2>80);"LULUS";"TIDAK LULUS")Copy-Paste atau tarik kebawah menggunakan fitur autofill rumus pada sel E2 diatas sampai pada sel E8. Hasil akhir pada masing-masing sel pada kolom E adalah sebagai berikut:
Hasil dari rumus diatas yang memenuhi kriteria adalah pelamar dengan nomor urut 3,4 dan 7.
Cara Menggunakan RumusIF-AND Di Excel
Bagaimana cara menggunakan rumus IF-AND Excel?
Pada fungsi OR nilai logika TRUE (Benar) jika didapatkan jika salah satu kriteria yang diuji berniai benar. Sedangkan fungsi AND akan menghasilkan nilai logika TRUE (Benar) jika semua syarat yang diuji bernilai TRUE (Benar).
Lebih jelasnya tentang perbedaan fungsi AND dan OR silahkan dibaca pada artikel berikut:Beda Fungsi OR dan AND pada Microsoft Excel.
Sintaks Fungsi AND adalah:
AND(Uji_Logika1; Uji_Logika2; ...)Jika digabungkan dengan fungsi IF, sintaksnya menjadi:
IF(AND(Uji_Logika1; Uji_Logika2; ...); Jika_Bernilai_TRUE; Jika_bernilai_FALSE)Sebagai contoh kasus akan kita gunakan data sebelumnya, hanya saja syarat pelamar yang LULUS adalah harus memiliki ijasah SMK dan nilai wawancara diatas 75.
Untuk Syarat rumus excel yang kita gunakan pada sel E2 adalah
=IF(AND(C2="SMK";D2>75);"LULUS";"TIDAK LULUS")Copy paste atau tarik kebawah menggunakan fitur autofill rumus pada sel E2 sampai pada sel E8. Hasil akhir pada masing-masing sel pada kolom E adalah sebagai berikut:
Setelah selesai maka hasil akhirnya didapatkan bahwa yang LULUS memenuhi kriteria adalah pelamar nomor urut 2 saja.
Mungkin anda bertanya kenapa yang S1 dan nilainya diatas 75 tidak LULUS? Hal ini sebab sesuai syarat yang diinginkan, hanya pelamar dengan Ijasah SMK saja dengan nilai diatas 75 yang diterima (LULUS). Jika bermaksud mengikutkan pelamar berijasah S1 lanjutkan ke rumus gabungan dibawah.
CATATAN:
- Jika anda masih pemula (baru belajar excel), dalam memahami rumus-rumus excel ini perhatikan cara peletakan tanda kurung ( ) serta pemisah argument (;).
- Tanda titik koma (;) digunakan jika setting komputer anda memakai default Bahasa Indonesia. Jika masih memakai setting Bahasa Inggris ganti dengan tanda koma (,).
- Untuk merubah setting default regional setting silahkan dibaca pada artikel sebelumnya: Cara Mengatur Regional Setting Komputer dan Perbedaannya Dalam Belajar Rumus Excel
Cara Menggunakan RumusIF-AND-OR di Excel
Ketiga fungsi diatas juga bisa digunakan secara bersama-sama dalam kasus-kasus tertentu jika diperlukan.
Misalkan dari data pelamar sebelumnya syarat pelamar yang diterima adalah yang berijasah SMK atau S1 dan nilai wawancaranya diatas 75, maka rumus excelnya menjadi:
=IF(AND(OR(C2="SMK";C2="S1");D2>75);"LULUS";"TIDAK LULUS")Lebih lengkapnya pada masing-masing sel:
Dengan rumus diatas maka yang memenuhi kriteria adalah pelamar nomor 2 dan 7
Sekarang jika kita menghendaki syarat yang diterima:
- Jika lulusan SD nilai lebih dari 75
- Jika lulusan MI nilai lebih dari 80
- Jika lulusan SMK nilai lebih dari atau sama dengan 80
- Jika lulusan S1 lebih dari atau sama dengan 90
Bagaimana rumus excelnya?
Apabila syaratnya seperti diatas maka rumus excel pada sel E2 adalah:
=IF(OR(AND(C2="SD";D2>75);AND(C2="MI";D2>80);AND(C2="SMK";D2>=80);AND(C2="S1";D2>=90));"LULUS";"TIDAK LULUS")Untuk masing-masing sel lainnya:
Dengan rumus diatas pelamar yang diterima adalah nomor urut 2,3 dan 7.
Contoh file materi ini bisa anda download pada link dibawah ini:
Dropbox
GDrive
*Jika link mati / tidak dapat diakses silahkan lapor via kontak yang tersedia
Untuk contoh penggunaan fungsi IF dan VLookup bisa anda baca pada laman berikut: Rumus IF gabungan dengan fungsi VLookup
Adapun gabungan fungsi IF dengan fungsi Min atau Max (MAX-IF, MIN-IF) bisa anda baca pada tutorial excel berikut: Rumus Excel Max-if dan Min-IF, Mencari nilai tertinggi/terendah berkriteria
Dari penjelasan diatas semoga sudah tidak lagi pertanyaan mengenai rumus gabungan fungsi I-FOR, IF-AND maupun IF-OR-AND dan IF-AND-OR.
Jika kamu terbiasa menggunakan excel untuk mengolah data saat bekerja, maka pasti kamu familiar dengan rumus IF excel atau fungsi IF, bukan?
Rumus IF di excel adalah salah satu fungsi yang paling sederhana dan banyak digunakan terutama untuk mengevaluasi dan menganalisis data dalam kondisi tertentu.
Nah, bila kamu belum menguasai betul penggunaan rumus IF excel tersebut, maka ulasan berikut bisa membantumu.
Apa itu rumus IF excel?
Rumus IF di Excel digunakan untuk mengevaluasi data berdasar kondisi tertentu - EKRUT
Rumus IF excel terbagi ke dalam jenis tunggal dan majemuk. Penulisan rumus juga harus mengikuti ketentuan-ketentuan sebagai berikut:
Rumus IF excel tunggal
Rumus if excel tunggal hanya terdiri dari satu If dengan satu logical_test. Rumus ini juga memiliki dua kondisi yaitu kondisi 1 (value if TRUE) dan kondisi 2 (value if FALSE). Berikut rumusnya:
=IF(logical_test;value_if_true;value_if_false)
Aturan penulisan rumus IF excel
Untuk menggunakan rumus IF excel, kamu bisa menggunakan formula fungsi IF berikut ini.
= IF(logical_test,[value_IF_true],[value_IF_false]
Dalam syntax tersebut dapat dipahami bahwa rumus IF memiliki 3 argumen, yaitu:
- Logical_test. Ini adalah kondisi yang akan diuji dan dievaluasi sebagai TRUE atau FALSE
- Value_IF_true adalah nilai yang dihasilkan bila sesuai dengan argumen logical test atau dengan kata lain kondisi terpenuhi.
- Value_IF_false. Nilai yang dihasilkan jika tidak sesuai dengan argumen logical test, atau dengan kata lain nilai bila kondisi tidak terpenuhi.
Untuk menggunakan rumus IF di excel tersebut, kamu juga akan banyak menggunakan beberapa tanda berikut ini untuk membantu pengujian.
- = (sama dengan)
- > (lebih dari)
- >= (lebih besar dari atau sama dengan)
- < (kurang dari)
- <= (kurang dari atau sama dengan)
- <> (tidak sama dengan)
Rumus IF excel untuk value_if_false
value_if_false, merupakan hasil yang diberikan excel jika dalam pembuktian logical test adalah salah (dalam hal ini adalah pernyataan logika alternative pilihan kedua)
Meskipun value_if_false merupakan logika alternative, amun, dalam penggunaan rumus IF excel tunggal maupun bertingkat sering terjadi kesalahan pada argument value_if_false ini.
Baca juga: Rumus microsoft excel untuk mempercepat pekerjaan
Cara menggunakan rumus IF excel
Nilai TRUE akan dihasilkan bila kondisi data terpenuhi - EKRUT
Untuk menggunakan rumus IF excel, kamu bisa menggunakan formula fungsi IF berikut ini.
= IF(logical_test,[value_IF_true],[value_IF_false]
Dalam syntax tersebut dapat dipahami bahwa rumus IF memiliki 3 argumen, yaitu:
- Logical_test. Ini adalah kondisi yang akan diuji dan dievaluasi sebagai TRUE atau FALSE
- Value_IF_true adalah nilai yang dihasilkan bila sesuai dengan argumen logical test atau dengan kata lain kondisi terpenuhi.
- Value_IF_false. Nilai yang dihasilkan jika tidak sesuai dengan argumen logical test, atau dengan kata lain nilai bila kondisi tidak terpenuhi.
Untuk menggunakan rumus IF di excel tersebut, kamu juga akan banyak menggunakan beberapa tanda berikut ini untuk membantu pengujian.
- = (sama dengan)
- > (lebih dari)
- >= (lebih besar dari atau sama dengan)
- < (kurang dari)
- <= (kurang dari atau sama dengan)
- <> (tidak sama dengan)
Contoh penggunaan rumus IF di excel
Gunakan rumus IF di excel dengan fungsi logika lainnya untuk perbandingan data yang lebih kompleks - EKRUT
Selain formula di atas, pada dasarnya rumus IF excel juga bisa digunakan dengan mengkombinasikannya bersama fungsi logika lainnya yang ada di excel. Misalnya menggabungkan rumus IF dengan fungsi logika seperti AND, OR, dan lain-lain.
Agar kamu semakin mengerti bagaimana cara menggunakan rumus IF di excel tersebut, berikut ada beberapa contoh yang bisa kamu perhatikan.
1. Contoh penggunaan rumus if di excel sederhana
Kamu memiliki daftar skor hasil tes calon karyawan yang ingin dievaluasi dan disaring berdasarkan kategori lulus atau gagal. Untuk menyaring kandidat, kamu menentukan passing score tes tersebut di angka minimum 80.
Dalam contoh kasus ini, maka rumus if yang bisa kamu gunakan adalah:
=IF(C2>=80, "Lulus", "Gagal")
Jika dibaca rumus di atas menjelaskan bahwa bila nilai di kolom C6 lebih tinggi atau sama dengan 80, maka hasilnya adalah Lulus. Sebaliknya, jika tidak sesuai maka hasilnya Gagal.
Dari sini, kamu bisa langsung mengevaluasi hasil skor tes dan mendapatkan data berupa nama-nama calon karyawan yang lulus tes.
Baca juga: 8 Tools gratis untuk membuat infografis
2. Contoh penggunaan rumus IF dan fungsi OR
Fungsi AND dan OR bisa digunakan dalam rumus IF di excel bila kamu ingin melakukan lebih dari satu perbandingan. Fungsi OR akan menghasilkan nilai TRUE jika satu atau semua kondisi terpenuhi, dan menghasilkan nilai FALSE hanya jika tidak ada kondisi yang terpenuhi.
Misalnya, kamu ingin melihat apakah salah satu dari tiga tim penjualan kamu memenuhi syarat untuk menerima bonus mingguan.
Untuk memenuhi syarat, satu tim setidaknya harus menjual setidaknya 4 sistem komputer atau setidaknya menghasilkan penjualan senilai Rp 13.000.000
Maka, rumus IF dan fungsi OR yang bisa digunakan untuk contoh kasus ini adalah
=IF(OR(B2>=4, C2> 13000000), "Yes","No")
Dari hasil rumus IF Excel ini diketahui bahwa tim satu dan enam memenuhi dua kriteria yang ada sehingga nilai yang dihasilkan adalah TRUE. Sementara tim dua dan delapan tetap dianggap menghasilkan nilai TRUE karena memenuhi salah satu kriteria yakni total penjualan.
Bedahalnya dengan tim tiga, empat lima dan tujuh yang tidak memenuhi satu kriteria apa pun sehingga nilai yang dihasilkan FALSE.
3. Contoh penggunaan rumus IF excel dengan fungsi AND
Berbeda dengan fungsi OR, fungsi AND bila digunakan dalam rumus IF di excel akan mengeluarkan hasil TRUE jika setiap kondisi terpenuhi, dan sebaliknya akan menghasilkan nilai FALSE jika tidak.
Misalnya, ada dua kriteria yang harus dipenuhi untuk menilai siapa kandidat yang akan diterima di perusahaanmu. Kandidat tersebut harus memiliki skor tes pengetahuan di atas 75 dan nilai wawancara di atas 80.
Dalam penggunaan rumus IF dan fungsi AND kamu bisa menuliskannya dalam formula berikut
=IF(AND(B2>75,C2>80),"DITERIMA","TIDAK DITERIMA")
Dari hasil ini diketahui bahwa Ezra, Ruth, Mika dan Sista diterima karena memenuhi dua kriteria yang diharuskan. Sementara Budi, David, Reni dan Hani tidak memenuhi kedua kriteria yang ada sehingga tidak diterima.
4. Contoh penggunaan rumus IF dan fungsi NOT
Pada dasarnya fungsi NOT digunakan untuk mengembalikan nilai logika terbalik dan untuk mengetahui apakah suatu kondisi tertentu tidak terpenuhi.
Kamu dapat menggunakan rumus IF dan fungsi NOT misalnya untuk menentukan bonus yang diterima karyawan. Misalnya kamu ingin memberikan bonus sebesar 5% dari ekstra sales yang dihasilkan karyawan.
Namun, tidak semua karyawan berhasil mendapatkan penjualan di luar target, maka tuliskan kata “none” pada data karyawan tersebut, lalu gunakan rumus IF dan fungsi NOT serta ISTEXT.
Fungsi ISTEXT akan memeriksa apakah cell tersebut berisi teks dan menghasilkan nilai TRUE jika ada, dan FALSE bila tidak.
Bentuk rumus if yang bisa kamu tulis untuk contoh kasus ini adalah
=IF(NOT(ISTEXT(B2)), B2*5%,0)
Dari data tersebut kamu bisa melihat dengan jelas siapa saja yang berhak mendapatkan bonus beserta nilai bonus yang akan diterima masing-masing karyawan.
Baca juga: Begini fungsi dan cara membuat Google Form
5. Contoh penggunaan rumus if excel bertingkat
Rumus IF excel juga bisa digunakan secara bertingkat ketika kamu memiliki beragam kondisi yang harus dipenuhi. Nilai FALSE akan digantikan oleh fungsi IF lainnya untuk melakukan pengujian lebih lanjut.
Rumus if ini bisa kamu gunakan misalnya ketika membuat peringkat berdasarkan rentang skor hasil penilaian karyawan sebagai berikut
- Nilai 90 - 100 = A
- Nilai 80-89 = B
- Nilai 70-79 = C
- Nilai 60-69 = D
- Nilai 0-59 = E
Maka, rumus if excel yang bisa kamu gunakan untuk contoh ini adalah
=IF(B2>=0,IF(B2<=59,"E",IF(B2<=69,"D",IF(B2<=79,"C",IF(B2<=89,"B",IF(B2<=100,"A",))))))
Contoh penggunaan rumus IF excel untuk tingkat Lanjut
Pada fungsi IF excel tingkat lanjut, kita dapat menggunakan fungsi sebagai salah satu argumen dalam rumus yang menggunakan fungsi yang disebut bertumpuk, dan kita akan merujuk ke fungsi tersebut sebagai fungsi bertumpuk.
Misalnya, dengan menumpuk fungsi AVERAGE dan SUM dalam argumen fungsi IF, rumus berikut ini adalah menjumlahkan sekumpulan angka (G2: G5) hanya jika rata-rata kumpulan angka lainnya (F2: F5) lebih besar dari 50. Jika tidak, mengembalikan 0.
Fungsi AVERAGE dan SUM ditumpuk dalam fungsi IF.
Kamu bisa mengumpulkan hingga 64 tingkat fungsi dalam sebuah rumus.
Berikut ini memperlihatkan contoh penggunaan fungsi IF excel bertumpuk untuk menetapkan peringkat huruf ke Skor uji numerik.
Agar rumus menunjukkan hasil, pilih datanya, tekan F2, lalu tekan Enter. Jika perlu, Anda dapat menyesuaikan lebar kolom untuk melihat semua data.
Nilai: 45 90
78
' = IF (A2>89, "A", IF (A2>79, "B", IF (A2>69, "C", IF (A2>59, "D", "F"))))
Menggunakan kondisi IF bertumpuk untuk menetapkan nilai huruf ke Skor dalam sel A2.
= IF (A2>89, "A", IF (A2>79, "B", IF (A2>69, "C", IF (A2>59, "D", "F"))))
Itulah beberapa contoh penggunaan rumus if di excel yang bisa kamu terapkan pada saat melakukan evaluasi data. Pastikan untuk menuliskan rumus IF excel dengan tepat sehingga tidak ada kesalahan dalam analisa data yang ada.
Jika kamu ingin jadi seorang Product Manager yang mahir menganalisa data, kamu bisa memulainya dengan belajar menguasai pengolahannya dengan rumus IF excel. Lalu, skills apa lagi yang dibutuhkan?. Simak penjelasan lengkapnya dalam video ini.
Rekomendasi video:
Last update: 7 April 2021
Sumber:
- corporatefinanceinstitue.com
- howtogeek.com
- xelplus.com