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. Hallo kak mohon bantuan nya , saya udh pusing sekali huhuhu...

    Jika gaji pokok kurang dari 1250000 maka akan di bayarkan iuran sebesar 0.3% dari gaji pokok , tapi jika gaji pokok lebih dari 1250000 maka akan di bayarkan 0,3% di kali 1250000

    Gaji pokok

    1jt
    1,2jt
    2,5j
    4jt
    terimakasih kak

  2. Mohon batuan nya kak

    Jika gaji pokok kurang dari 1250000 maka akan di bayarkan iuran sebesar 0.3% dari gaji pokok , tapi jika gaji pokok lebih dari 1250000 maka akan di bayarkan 0,3% di kali 1250000

    Tabel Gaji pokok

    1jt
    1,2jt
    2,5j
    4jt

    Terimakasih

  3. Rumus Tambah Kurang Penjumlahan :
    ada 4 kolom
    1. harga awal
    2. tambah
    3. kurang
    4. harga akhir

    kolom 2 dan 3 pengen terisi otomatis, dengan kondisi jika harga awal > harga akhir maka muncul di kolom tambah(dengan muncul nominal penambahan), jika harga awal < harga akhir maka muncul dikolom kurang (nominal pengurangan)

    Mohon bantuannya, Terima Kasih

  4. Tolong Bantuannya ya:
    Tgl Jatuh Tempo Tgl Bayar
    5 Agustus 5 Agustus 2021
    12 Agustus 12 Agustus 2021
    dll 19 Agustus 2021
    26 Agustus 2021
    Jika jatuh tempo adalah kurang dari sama dengan 12 agustus dan lebih dari 5 agustus maka tgl bayar adalah 12 agustus, dst.
    tolong ya ini gimana rumusnya huhu

  5. Mohon dibantu,
    Bagaimana rumus exel untuk menghitung usia seseorang sehingga diperoleh hasil xx tahun ( tahun penuh)
    Format data telah tanggal diinput mm/dd/yy

    misalkan :
    Pada tanggal 12 Juli 2021, ingin diketahui umur Y
    Tanggal lahir di in put di cell A1
    Y lahir 12 Juli 1986 ( data di in 7/12/86 --> hasil 12-Jul-1986
    Apabila menggunakan rumus =today(), pada kolom B1, bagaimana agar hasilnya dapat berupa bilangan tahun penuh ? karena antara 1986 sd 2021 terdapat tahun kabisat

    terima kasih

  6. Mohon infonya min, Jika Pencapaian 60% maka 0,5 x DP
    Jika Pencapaian 70% maka 0,6 x DP
    Jika Pencapaian 80% maka 0,75 xDP
    Jika Pencapaian 90%maka 1 x DP
    Jika Pencapaian 100%maka 1,5 x DP
    Rumus IF nya bagaimana min?

  7. Min mohon bantuannya, jika pencapaian 60% maka 0.5 x DP, jika 70% maka 0.6 x DP, jika 80% maka 0.75 xDP, jika 90% maka 1 x DP, jika 100% maka 1,5 x DP, Jika 120% maka 2 x DP. Rumus IF nya bagaimana min?

  8. Mohon bantuannya kak,

    jika 100 adalah senilai 10.000
    jika 250 adalah senilai 20.000
    jika 500 adalah senilai 40.000
    jika 1000 adalah 75.000

  9. selamat siang,
    salama kenal,
    Mas saya mau tanya,
    gimana rumus korelasi antara stok barang masuk dan keluar dan kita harus update datanya setiap hari jadi tanggalnya otomatis mengikuti stok barang masuk dan keluar juga.
    contohnya: barang masuk 5
    barang keluar 7
    dan untuk stok akhirnya gimana dan misalnya untuk tanggal 1 itu kemudian tanggal 2 berubah lagi tergantung barang yang keluar.
    mohon pencerahannya mas.
    terima kasih

  10. maaf ka izin bertanya?
    bagaimana rumus apabila dalam 7 hari siswa bebas mengabsen bisa di hari ke 1 atau keberapa dalam 7 hari tersebut.. nah bgaimana rumus di excel merekap hasilnya apabila ada yang mengabsen 2x atau lebih dalam satu minggu tersebut tp terekap 1x absen..

  11. Halo Kak kalo kita mau cari data
    1. < 5mio
    2. 20 - 50 mio
    3. 50 -100 mio
    4. 100 - 200 mio
    5. 200
    Tabel di sebelahnya angkanya 40.000.000

    rumus nya gmn kak

  12. Halo kak, maaf mau bertanya.
    Untuk menghitung poin lembur berdasarkan durasi, bisa menggunakan rumus apa ya? Saya coba menggunakan if bertingkat tapi ada beberapa cell yg hasilnya salah
    Contoh:
    durasi=poin
    1:00 s/d 1:59 = 10 poin
    2:00 s/d 2:59 = 20 poin
    3:00 s/d 3:59 = 30 poin

  13. Mohon bantuan nya min,,,untuk kilometer harian kendaraan ,jika kendaraan mencapai kelipatan 5000 Km harus service gmn .min terima kasih

  14. min mau tanyak apabila membuat rumus keliapatan gimna min
    untuk contoh
    5 butir menjadi 1 pck
    10 butir menjadi 2 pck
    15 butir menjadi 3 pck dan setiap kelipatan 5 bertambah 1 pck
    mohon bantuanya

  15. Mau nanya, jika di kolom A1 berupa total harga dan kolom A2 status "cash" atau "bon" , kolom A3 (cash) mengikuti kolom A2 , jika kolom A2 cash maka hasilnya A1 jika bon hasilnya 0, demikian juga kolom A4 (bon) mengikutin kolom A2, jika kolom A2 bon maka hasilnya A1 jika bon hasilnya 0.
    ini rumusnya gmn ya? Mohon bantuannya.. terima kasih

  16. min minta bantuan.
    jika A1=SKT dan jika B1 500 maka C1 adalah 5jt dikali kelipatan B1
    jadi min,, kalo B1 500 C1 itu 5jt kali 1, klo B1 1000 C1 5jt kali 2
    gmn ya rumusnya juga dengan syart klo A1 nya SKT

1 7 8 9 10

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

Tulis komentar
Masukkan nama Anda