Rumus Excel IF Bertingkat, Sederhana, Gabungan OR AND, dan Konversi

287

A. Dasar Rumus Excel IF

Rumus Excel IF adalah fungsi (function) yang digunakan untuk menampilkan suatu nilai (output) berupa TRUE atau FALSE dengan melakukan uji pemenuhan kondisi-kondisi yang ada terhadap sel atau range tertentu. Output TRUE apabila data memenuhi kondisi yang ada, sedangkan output FALSE apabila data tidak memenuhi kondisi yang ada. Rumus Excel IF merupakan salah satu penerapan dari Computing Intelligence menggunakan operasi logika matematika. Sebelum mempelajari rumus Excel IF bertingkat, perlu diketahui sistematika dasar formula IF.

Artikel terkait: Rumus Fungsi TRUE dan FALSE pada Excel beserta Contoh

=IF(logical_test, [value_if_true], [value_if_false])
  1. logical test adalah ekspresi logika yang diujikan dengan data bersangkutan.
  2. [value_if_true] adalah output yang ditampilkan ketika data memenuhi logical_test (true). Nilai variabel teks (string) dapat ditulis dengan tanda "petik" dan nilai variabel numerik (angka) ditulis "tanpa tanda petik".
  3. [value_if_false] adalah output yang ditampilkan ketika data tidak memenuhi logical_test (false). Nilai variabel teks (string) dapat ditulis dengan tanda "petik" dan nilai variabel numerik (angka) ditulis "tanpa tanda petik".
  4. Jika [value_if_true] atau [values_if_false] dikosongkan, maka output default yang ditampilkan adalah teks TRUE atau FALSE.

PENTING: Penggunaan koma (,) atau semicolon (;) dalam FORMULA disesuaikan dengan versi Microsoft Excel dan konfigurasi bahasa pada komputer anda.

Berikut beberapa operasi logika dan aritmatika yang dapat digunakan, untuk penjelasan mengenai operasi logika yang lebih lengkap anda dapat membaca artikel "Cara Membuat Operasi Logika Pada Excel".

Simbol  Fungsi
 +  Penjumlahan
 –  Pengurangan
 *  Perkalian
 /  Pembagian
 %  Persen
 ^  Perpangkatan
 =  Sama dengan
 <  Kurang dari
 <=  Kurang dari atau sama dengan
 >  Lebih dari
 >=  Lebih dari sama dengan
 <>  Tidak sama dengan

Dari sistematika di atas dapat dibuat formula Excel IF sederhana atau tunggal dan bertingkat atau majemuk.


B. Rumus Excel IF Sederhana/Tunggal

Rumus Excel IF Tunggal hanya memuat satu operasi logika dengan output pasangan tunggal. Misalkan diketahui nilai praktikum Microsoft Excel 10 siswa adalah 60, 75, 85, 79, 90, 45, 77, 68, 90, 50. Akan ditentukan siswa yang lulus praktikum harus mencapai nilai 76 dengan menggunakan Microsoft Excel. sehingga dapat digunakan formula berikut dengan teknik Autofill.

PENTING: Penggunaan koma (,) atau semicolon (;) dalam FORMULA disesuaikan dengan versi Microsoft Excel dan konfigurasi bahasa pada komputer anda.

Baca Juga: Cara Menggunakan Autofill pada Excel

=IF(sel_nilai>=76,"LULUS","TIDAK LULUS")

Cara Menggunakan IF Pada Excel

B1. Rumus Excel IF AND

Rumus Excel AND digunakan untuk menggabungkan dua atau lebih operasi logika dengan nilai kebenaran benar saat semua operasi logika terpenuhi. Berikut sistematika formula AND pada Excel.

=AND(logical1, [logical2], [logical2],...)

Anda dapat menggunakan 255 operasi logika menggunakan fungsi IF pada Microsoft Excel. Fundamental logika AND pada Excel sama dengan teori matematika modern.

  • Saat semua operasi logika bernilai benar/terpenuhi, output yang ditampilkan adalah TRUE.
  • Saat salah satu operasi logika tidak terpenuhi/salah, output yang ditampilkan adalah FALSE. Contoh formula AND dengan 3 syarat
    =AND(A1>0,A1=1,B2<1)

Untuk menggunakan Rumus AND pada IF, anda tinggal memasukkan formula AND sebagai logical_test. Misalkan suatu sanggar akan memilih kandidat perempuan dari 10 orang penari untuk mewakili sanggar tersebut pada olimpiade tari. Pemilihan kandidat harus berumur lebih dari 7 tahun. Berikut dapat digunakan formula Excel untuk kasus ini

=IF(AND(sel_gender="Perempuan",sel_umur>7),"Terpilih","Tidak Terpilih")

Cara Menggunakan IF AND pada Microsoft Excel

B2. Rumus Excel IF OR

Rumus Excel OR juga digunakan untuk menggabungkan dua atau lebih operasi logika dengan nilai kebenaran salah saat semua operasi logika tidak terpenuhi. Sama halnya dengan AND, anda dapat menggunakan 255 operasi logika dengan formula OR.

=OR(logical1,[logical2],...)
  • Saat salah satu operasi logika terpenuhi, output yang dihasilkan TRUE.
  • Saat semua operasi logika tidak terpenuhi, output bernilai FALSE. Contoh formula OR dengan 2 syarat
    =OR(A2>0,A3=1)

Misalkan akan dihitung bonus penjualan dari 5 orang sales perusahaan kopi pada tabel di bawah. Setiap sales yang datang tepat waktu atau dapat menjual lebih dari target 15 kopi akan mendapat bonus Rp10.000,00. Sales yang datang terlambat dan tidak mampu menjual 15 kopi akan mendapat denda Rp1.000,00 per kopi kurangnya dari target.

=IF(OR(kedatangan="Tepat Waktu",penjualan>=15),10000,(15-penjualan)*1000)

Cara Menggunakan IF OR Pada Microsoft Excel


C. Rumus Excel IF Bertingkat /Majemuk /Nested IF

Rumus IF bertingkat secara umum penggunaannya sama saja dengan IF tunggal. IF bertingkat memanipulasi variabel [value_if_false] sebagai nested IF (percabangan IF).Berikut sistematikanya

PENTING: Penggunaan koma (,) atau semicolon (;) dalam FORMULA disesuaikan dengan versi Microsoft Excel dan konfigurasi bahasa pada komputer anda.

=IF(logical_test1, [value_if_true], logical_test2,[value2_if_true],...,[logical_testN],[valueN_if_true],[valueN_if_false])

Misalkan diketahui pengelompokan nilai mahasiswa yaitu 1 = Kurang, 2 = Cukup, 3 = Baik, dan 4 = Sempurna. Akan dilakukan pengelompokan nilai suatu mata kuliah dari 5 mahasiswa pada tabel berikut.

=IF(nilai=1,"Kurang",IF(nilai=2,"Cukup",IF(nilai=3,"Baik",IF(nilai=4,"Sempurna"))))

Cara Menggunakan IF Bertingkat di Excel

Baca juga: Penerapan Fungsi AND pada Rumus IF AND Bertingkat (BAGIAN D)

Anda juga dapat membuat rumus excel IF bertingkat dengan menggunakan gabungan OR dan AND seperti pada langkah formula IF sederhana di atas.


D. Rumus IF Bertingkat dengan Gabungan AND (Kasus: Membuat Konversi Nilai A B C D E)

Jenis kasus IF bertingkat untuk konversi nilai A B C D E merupakan kasus terpopuler, dengan memahaminya pengguna dapat membuat formula yang lebih kompleks. Kasus dasar ini dapat disesuaikan dengan permasalahan yang lebih nyata pada data yang akan dikelola.

Contoh:

Misalkan seorang juri mempunyai kriteria penilaian yaitu:

A untuk 80-100,
B untuk 60-79,
C untuk 40-59,
D untuk 20-39,
E kurang dari 20

Bagaimana juri dapat membuat kriteria penilaian dengan Microsoft Excel?

Penyelesaian:

Sekilas kriteria yang dibuat juri tersebut tidak ada masalah dan dapat diterima secara langsung. Namun apa yang terjadi jika seorang peserta mendapat nilai 79,8?

Clue: Kriteria di atas masih ambigu karena hanya bisa diterima untuk angka bulat.

Sehingga juri tersebut harus merevisi kriteria penilaiannya, karena dia beranggapan 79,8 kurang dari 80 maka peserta tersebut mendapat nilai B.

Kriteria penilaian tersebut diperbaiki dahulu ke bentuk logika matematika sederhana menggunakan logika AND.

=AND(kriteria bawah, kriteria atas)

Misal sel data awal yang ingin dieksekusi A1, berikut perbaikan aturan penilaian yang dibuat.

Kriteria Awal Revisi AND
A untuk 80-100, A >= 80 dan A <=100 AND(A1 >= 80, A1 <=100)
B untuk 60-79, B >= 60 dan B < 80 AND(A1 >= 60, A1 <80)
C untuk 40-59, C >= 40 dan C < 60 AND(A1 >= 40, A1 <60)
D untuk 20-39, D >= 20 dan D < 40 AND(A1 >= 20, A1 <40)
E kurang dari 20 E < 20 A1 < 20

Bagaimana formula IF nya?

=IF(AND(A1 >= 80, A1 <=100),"A",IF(AND(A1 >= 60, A1 <80),"B",IF(AND(A1 >= 40, A1 <60),"C",IF(AND(A1 >= 20, A1 <40),"D",IF(A1 < 20,"E","Nilai Salah")))))

PENTING: Penggunaan koma (,) atau semicolon (;) dalam FORMULA disesuaikan dengan versi Microsoft Excel dan konfigurasi bahasa pada komputer anda.

Tutorial lainnya: Daftar Isi Tutorial Excel


Sekian artikel Rumus Excel IF Bertingkat dan Sederhana Serta Gabungan OR AND. Nantikan artikel menarik lainnya dan mohon share dan juga menyukai halaman Advernesia. Terima kasih…

287 KOMENTAR

  1. 2 PCS 3 PCS 6 PCS 11 PCS Rp550,000
    KALO INI DIPAKAI IF BERTINGKAT BAGAIMANA ,KOK GA BISA YA .
    * JIKA HARGA TOTAL LEBIH BESAR SAMA DENGAN 500000 HADIAHNYA PAYUNG CANTIK
    MOHON CARANYA
    TERIMAKASIH

    • Hai kak Ratih,
      Kalo hadiahnya dengan 1 logika saja, pakai rumus IF biasa saja kak
      =IF(harga>=500000,"Payung Cantik","Tidak Dapat Hadiah"
      Semoga membantu 🙂

  2. Tolong bantuanya dong
    Jika HARGA BAYAR lebih besar dari 5 jt maka pajak = 15% dari HARGA BAYAR
    Jika HARGA BAYAR lebih dari 3jt maka pajak = 10% dari HARGA BAYAR
    Selain itu maka pajak =5% dari HARGA BAYAR

    • Hai kak Herdi
      =IF(harga>=5000000,15%*bayar,IF(AND(harga>=3000000,harga<5000000),10%*bayar,5%*bayar))
      Semoga membantu :)

  3. Kak mohon bantuan rumusnya kalau misalnya nilai pada kolom A lebih besar dari 0, maka hasilnya adalah kolom A dikurangi kolom B, apabila nilai kolom A sama dengan 0, maka hasilnya adalah angka pada kolom B.

    • Halo Kak Pingkan,
      Itu dapat menggunakan rumus =IF(A>0,A-B,IF(A=0,B,"Input Salah"))
      Semoga membantu 🙂

  4. Saya tidak tahu harus bagaimana, tetapi tanpa mengurangi rasa hormat saya kpd admin saya benar benar terbantu dengan konten yg ada di laman ini. Saya berterima kasih sekali. Good luck admin~

    • Terima kasih Banyak kak Oii atas doanya
      Saya sangat senang, tulisan ini dapat membantu pekerjaan kakak 🙂

  5. maaf master barangkali bisa bantu
    jika kolom g6 kelas 4 nilai di atas 60 naik, jika di bawah 60 tidak naik, jika kolom g6 kelas 5 jika nilai di atas 60 naik, jika bawah 60 tidak naik dan jika g6 kelas 6 nilai diatas 60 lulus dan jika di bawah 60 tidak lulus

    • Hai kak Jumadi
      Berhubung kolom logika nilai sama jadi saya sederhanakan logikanya, catatan kolom H6 adalah nilai siswa
      =IF(H6>=60,IF(G6="kelas 6","Lulus","Naik"),IF(G6="Kelas 6","Tidak Lulus","Tidak Naik"))
      Semoga membantu ya 🙂

  6. Siang, mohon bantuannya. saya mau menghitung insentif yang di dapat.
    - Tagihan/capaian di atas 40jt akan mendapat insentif 150rb untuk kelipatan 10jt.
    jadi tiap kelipatan 10jt dia dapat tambahan insentif 150rb lagi.
    itu rumusnya bagaimana ya?

    • Hai kak Wiwi, dapat menggunakan rumus excel if biasa
      =IF(A1>=40000000,TRUNC((A1-40000000)/10000000+1,0)*150000,0)
      Semoga membantu 🙂

  7. Haloo,
    mau tanya apabila hasil if function tersebut adalah angka, kenapa tidak bisa menggunakan data bars ya min ??

    • Umumnya bisa kak Gio,
      Saya coba di Microsoft Office 2016, bisa kok
      Coba set ulang sel/range yang berisi formula IF ...
      Semoga membantu 🙂

    • Saya menggunakan Excel 2013 min,
      bisa dibantu maksud set ulang sel/range yang berisi formula IF ?
      misal, sy punya data:

      Progress|Bobot
      Periksa| = IF(A1="Periksa";"10";IF(A1="Validasi Berkas";"25";IF(A1="Aset";"100")))
      Validasi Berkas|= IF(A2="Periksa";"10";IF(A2="Validasi Berkas";"25";IF(A2="Aset";"100")))
      Aset|= IF(A3="Periksa";"10";IF(A3="Validasi Berkas";"25";IF(A3="Aset";"100")))
      .
      .
      dst.

      Kemudian kolom Bobot akan menghasilkan angka min, nah saya mau pakai menu "Data Bars" untuk tau urutan progress, tp tidak bisa, bagaimana ya min. Apakah hasil IF function td bukan angka sesungguhnya ?? terimakasih. 🙂

    • Hai kak Gio, berdasarkan formula yang kakak buat, Keluaran True/False nya berupa string karena berada dalam tanda petik ("x"), coba hapus tanda petik-nya untuk mengubah keluaran ke numerik (misalnya "10" ubah ke 10 saja).
      Semoga membantu 🙂

  8. Halo Min,
    saya mau tanya, jika logika IF A1 sama dengan kosong, maka warna Cell merah bisa tidak ya ? hehe

    terimakasih min.

    • Hai kak Gio, berdasarkan formula di atas, saran saya buat untuk false = 0 atau nilai lainnya /teks juga bisa (warna merah)
      IF(A1="Periksa",10,IF(A1="Validasi Berkas",25,IF(A1="Aset",100,0)))
      Jika, 0 buat bar warna merah, melalui "Conditional Formatting Rules Manager"
      Semoga rekomendasi saya membantu 🙂

  9. intinya si nyari rumus untuk ini kak 1 -15 maka prosentasenya 12.50%
    6-30 maka prosentasenya 25%
    31-45 maka prosentasenya 37.50%
    46-60 maka prosentasenya 50%
    61-75 maka prosentasenya 62.50%
    76-90 maka prosentasenya 75%
    91-105 maka prosentasenya 87.50%
    106-120 maka prosentasenya 100%
    mohon mantuan rumus excel nya ya kak..

    • Hai kak Sulis, saya mencoba membantu
      1 - 15 dengan 6 - 31 tidak singkron, jadi saya ganti 1 - 5
      =IF(AND(A1>=1,A1<6),12.5%,IF(AND(A1>=6,A1<31),25%,IF(AND(A1>=31,A1<46),37.5%,IF(AND(A1>=46,A1<61),50%,IF(AND(A1>=61,A1<76),62.5%,IF(AND(A1>=76,A1<91),75%,IF(AND(A1>=91,A1<106),87.5%,IF(AND(A1>=106,A1<=120),100%, ))))))))
      Dengan A1 adalah sel yang diuji
      Semoga membantu 🙂

    • alhamdulillah kak sukses tapi maaf menggu kembali mau tnya jika pada sel yg di uji data nya bukan hnya angka tapi ada hurufnya bagaimana ya kak ?? sama masukan rumus dari kakak hasilnya gak bisa (false).. 1hari ,6hari di sel yg di ujikan ,,jdi 6 hari prosentase nya ikut yg rentang 1-15 = 12,5%,gimna ya kak??

    • Hai Kak Sulis, untuk itu perlu dilakukan konversi nilai teks ke numerik
      misalnya A1=1hari, maka harus diubah ke bentuk numerik
      =IF(ISNUMBER(A1),A1,VALUE(REPLACE(A1,FIND("hari",A1,1),4,"")))
      Semoga membantu 🙂

    • =IF(AND(A1>=1,A1<16),12.5%,IF(AND(A1>=16,A1<31),25%,IF(AND(A1>=31,A1<46),37.5%,IF(AND(A1>=46,A1<61),50%,IF(AND(A1>=61,A1<76),62.5%,IF(AND(A1>=76,A1<91),75%,IF(AND(A1>=91,A1<106),87.5%,IF(AND(A1>=106,A1<=120),100%, ))))))))
      Baik kak, Semoga membantu 🙂

  10. Terlampir data sebagai berikut :
    NIK Karyawan; Nama Karyawan; Status Karyawan Status Keluarga
    N11012007; Budi; SKU-H; TK
    N11022007; Andi; Karyawan Harian; K0
    N11032007; Santoso; SKU-B; K0
    N11042007; Abdul; SKU-H; K1
    N11052007; Agus; Karyawan Tetap; K2
    N11062007; Ali; SKU-H; K0
    N11072007; Andre; Karyawan Harian; TK
    N11082007; Iyan; SKU-B; K1
    N11092007; Alex; SKU-B; K3

    Ketentuan:
    TK : 0,6 Kg / hari
    K0 : 0,9 Kg / hari
    K1 : 1,2 Kg / hari
    K2 : 1,5 Kg / hari
    K3 : 1,8 kg / hari
    Untuk yang berhak mendapatkan catu beras adalah karyawan dengan status SKU-H dan SKU-B

    Kak saya mau tanya, mohon bantu untuk merumuskan berapa kg beras yang diperoleh karyawan selama 25 hari sesuai dengan ketentuan diatas.

    Terima kasih.

    • Ha Kak Heri itu menggunakan Fungsi VLOOKUP dan SUMIFS
      Tentukan terlebih dahulu dengan kolom tambahan di tabel data karyawan dengan VLOOKUP
      Misalnya
      kolom Karyawan A1, B1, C1, D1
      Kolom Catu beras F1 G1
      Kolom Tambahan E1 = VLOOKUP(D1,range catu,2)
      Kemudian dapat dihitung dihitung
      SUMIFS(range E,range D,"SKU-H",range D,"SKU-B")
      Semoga membantu 🙂

  11. mohon bantuannya :
    A1 : pisang
    A2 : jeruk
    A3 : anggur
    dan seterusnya sampai A100, lalu
    B1 : 1000
    B2 : 1500
    B3 : 2000
    dan seterusnya sampai B100
    Saya mohon bantuan rumus :
    Jika kolom D1 di isi jeruk maka E1 otomatis 1500, dst sampai D100 dan E100

    • Hai, Kak Edwin Sigit Gunawan
      Permasalahan tersebut lebih cocok diselesaikan dengan formula INDEX dengan MATCH
      Misalnya C1 = Jeruk
      =INDEX(B1:B3,MATCH(C1,A1:A3,0))
      Semoga membantu 🙂

  12. Member Type Poin
    EMAS INDIVIDU 4
    PERAK INDIVIDU 2
    EMAS GROUP 3
    PERAK GROUP 1
    Kalau data diatas dibuat dalam 1 rumus bagaimana kak? mohon bantuannya.

    • Hai, Kak Dani, maaf atas keterlambatan balasannya
      Saya mencoba membantu
      =IF(member="EMAS",IF(type="INDIVIDU",4,IF(type="GROUP",3,"Kesalahan")),IF(member="PERAK",IF(type="INDIVIDU",2,IF(type="GROUP",1,"Kesalahan")))
      Semoga membantu 🙂

  13. mau tanya kak, gimana misalnya ada dikolom A adalah tanggal 1 s/d 31 lalu dikolom b ada nama (misalnya adi,budi,cici) setiap hari nama mereka bertiga ada disetiap tanggal dan dikolom c adalah hasil penjualan mereka, pertanyaan saya rumus apa yang digunakan untuk menentukan jumlah hasil penjualan mereka selama sebulan penuh lebih atau tidak dari target

    • Hai, Kak Subjek
      Itu dapat menggunakan formula COUNTIFS dengan kriteria tanggal
      Semoga membantu 🙂

  14. mohon bantuannya :
    C D E F G
    tgl terima pk kembali dari pk bendahara kembali dari bendahara
    9/14/2020 9/15/2020 9/16/2020 9/17/2020 9/18/2020

    mohon bantuannya kak, saya bingung pakai rumus if yg mana,

    jadi jika diisi sampai tgl terima maka keterangan terima, jika sampai pk maka keterangannya pk, jika sampai bendahara maka keterangannya bendahara, jika sampai kembali bendahara maka keterangannya kembali dari bendahara

    • Hai, Kak Eko
      Saya mencoba membantu
      =IF(NOT(ISBLANK(G1)),"kembali dari bendahara",IF(NOT(ISBLANK(F1)),"bendahara",IF(NOT(ISBLANK(E1)),"kembali dari pk",IF(NOT(ISBLANK(D1)),"pk",IF(NOT(ISBLANK(C1)),"terima","kosong")))))
      Semoga membantu 🙂

  15. Kak Maaf mau bertanya.
    saya ingin membuat logika, jika total penjualan lebih dari 100.000.000 masuk Deal Besar, lebih dari 40.000.000 Deal Sedang dan Dibawah 40.000.000 Deal kecil. Kemudian saya masukkan rumus seperti ini tidak bisa kak.
    =IF(AND(D2>100000000;D2>4000000;D2>0);"Deal Besar";IF(AND(D2>100000000;D2>4000000;D2>0);"Deal Sedang";IF(AND(D2>100000000;D2>4000000;D2>0);"Deal Kecil";"Tidak Deal")))
    kira-kira bagaimana solusinya? apakah ada kesalahan dalam memasukkan rumus?

    • Hai, Kak Gebri A.Y
      Kakak dapat melakukan fungsi logika dari kecil ke besar atau besar ke kecil, saya coba bantu dengan fungsi logika besar ke kecil
      =IF(D2>=100000000,"DEAL BESAR",IF(D2>=40000000,"DEAL SEDANG",IF(D2<40000000,"DEAL KECIL","TIDAK DEAL")))
      Semoga Membantu :)

1 4 5 6

AYO BERKOMENTAR

Tulis komentar
Masukkan nama Anda