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

375

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…

375 DISKUSI PEMBACA

  1. Gan mau tanya, kalo di sel ada nama yg pake bapak, ibu, atau kak di depannya
    Dan ada juga yg cuma nama aja

    Cara ngilangin sebutan di awal namanya tanpa merubah nama yg laen gimana
    (Intinya cara ngilangin bapak, ibu, atau kak)

    🙏 Sebelumnya terimakasih gan

  2. PENCAPAIAN KOMISI
    < 1 Rp1.000.000
    ≥ 1 - 5 Rp1.500.000
    ≥ 6 - … Rp2.000.000

    untuk merumuskan ini bagaimana ya? supaya pas di tulis 1/2 langsung tertera jumlah nomoinalnya

  3. =if(a1="Appointment";"Saldo Cukup";if(a1="Transfer Payment":"Saldo Cukup")) .. nah contoh sepeti itu ka, formulanya ga ketemu g dpt2 mhn d bantu ka terimakasih

  4. maaf min pengin tanya saya punya data seperti di bawah ini
    D E F G
    6 81 75 88 91

    =(D6*10%)+(E6*20)+(F6*30%)+(G6*40%)
    Itu kalau tanpa IF
    tapi kalau pake IF si kayak gimana ya min

  5. Jika keterangannya:
    (Bagian) (Masuk) (Pulang)
    Shif 1 masuk jam 06:00 pulang jam 10:00
    Shif 2 masuk jam 10:00 pulang jam 14:00
    Shif 3 masuk jam 14:00 pulang jam 18:00
    Shif 4 masuk jam 18:00 pulang jam 22:00
    Rumus untuk jam masuk dan jam pulangnya gimana ya?

    Terima kasih sebelumnya ka

  6. mohon bantuan min:
    A2=nama siswa
    B2=70%
    C2=40%
    D2=30%
    bagimana rumus untuk E2 (logika mencari angka tertinggi)?
    logikanya:
    jika angka B2>C2 dan B2>D2 maka "visual"
    jika angka C2>B2 dan C2>D2 maka "auditori"
    jika angka D2>B2 dan D2>D2 maka "kinestetik"

    terima kasih

  7. Min bisa dibantu rumusnya untuk gaji kurir harian :

    *Paket terkirim sebelum jam 3 sore sejumlah 29 paket maka ongkos paket dihargai 2500 per paket
    *Paket terkirim setelah jam 3 sore sejumlah 29 paket maka ongkos paket dihargai 2700 per paket
    *Paket terkirim 30 paket pagi maupun sore maka penghasilannya 115000
    *Paket terkirim diatas 30 paket pagi maupun sore maka tambahan bonus 2200 per paket

  8. mohon bantuan min:
    A2=nama
    B2=70
    C2=50
    D2=30
    rumus utk E2 ?
    logika sebenarnya hanya mencari nilai tertinggi dari kolom B,C, D
    jika B2>C2 dan B2>D2 maka "visual"
    jika C2>B2 dan C2>D2 maka "auditori"
    jika D2>B2 dan D2>C2 maka "kinestetik"

    terima kasih

  9. Tolong bantu jika

    Nilai kurang dri 300 ribu tuliss 30000 ( tampil ) jika lebih dari 300000 maka harus tampil nilai sebenarnya ( yang tampil adalah sesuai isi dillon contoh 350.000 )

    Rumus apa ya ..aku g nemu2

  10. mohon bantuannya kak:
    1. jika hotel bintang 3-5, review bagus, score lebih dari 7, kamar lebih 100 atau hotel tersebut sudah ikut expo maka "prioritas tinggi"
    2. jika hotel bintang 3-5, review sedang, score lebih dari 5 kurang dari 7, kamar 50-100 atau hotel tersebut sudah ikut webinar maka "prioritas sedang"
    3. jika hotel bintang 0-3, review jelek, score kurang dari 5, kamar <50 atau hotel tersebut belum ikut apa2 maka "prioritas rendah"

    terima kasih

  11. mohon bantuannya kak:
    1. jika hotel bintang 3-5, review bagus, score lebih dari 7, kamar lebih 100 atau hotel tersebut sudah ikut expo maka "prioritas tinggi"
    2. jika hotel bintang 3-5, review sedang, score lebih dari 5 kurang dari 7, kamar 50-100 atau hotel tersebut sudah ikut webinar maka "prioritas sedang"
    3. jika hotel bintang 0-3, review jelek, score kurang dari 5, kamar <50 atau hotel tersebut belum ikut apa2 maka "prioritas rendah"

  12. Kak mau tanya, kalo mau masukin list harga. Misal
    1= 20000
    2= 40000
    3= 60000
    4= 80000
    Gimana ya, jadi kita input item yang kejual dengan satu angka aja untuk liat harga gitu

  13. Hai Advernesia saya punya kasus.
    Bagaimana rumus dari pembagian insentif berdasarkan poin kerja
    Data sbb
    Nama jmlpoin jmlinsentif
    A 100 ?
    B 100 ?
    C 80 ?
    D 40 ?
    E 20 ?

    Jumlah dana 4.000.000
    Jumlah seluruh poin kinerja 340(Poin A+B+C+D+E)

    Penjelasan cara perhitungannya adalah
    1. Tiap-tiap orang menerima insentif
    maksimal 1.000.000
    2. Jika tiap orang yang telah menerima maksimal 1.000.000, maka kelebihannya akan di tambahkan ke orang yang belum menerima maksimal
    3. Jumlah penambahan insentif yang belum menerima maksimal, SESUAI JUMLAH POIN MASING-MASING tanpa menyertakan poin yang telah menerima maximal
    4. Seluruh dana harus habis untuk di bagikan

    Mohon pencerahan dari Advernesia

  14. Mas Mau tanya, kalau misalnya
    Harga Jan = 100; Feb = 150; Mar = 200; Apr = 175; Mei = 120

    Saya mau buat di kolom :
    2 jan = harga x 2000 (volume)
    5 jan = harga x 1500
    20 Feb = harga x 1800
    30 Mei = harga x 5000

    Mohon seperti apa ya rumusnya

  15. maaf min minta tolong penulisan rumus nya seperti apa..
    contoh kasus
    jika di kolom B3=piutang maka hasil nya 0
    namun jika di kolom B3=cash maka hasilnnya sesuai dengan di bayarkan A3
    itu gimana min cara penulisan rumus nya...

  16. izin bertanya bagaimanakah rumusnya?? " jika 4 siswa maka 1 guru dan jika 4 guru maka 1 kepala sekolah " begitu seterusnya... terimakasih atas bantuan nya..

  17. Soal :
    Kolom pivot 1 : Kategori X
    Kolom pivot 2 : Kategori A, Kategori B, Kategori C, Kategori D
    Dicari : 1 nota harus ada kategori X dan salah satu kategori A atau B atau C atau D

    Pertanyaannya : pakai rumus excel apa ??

1 5 6 7 8 9 10

Ayo Berdiskusi Bersama! Belajar Lebih Indah dengan Saling Membantu :)

Tulis komentar
Masukkan nama Anda