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

260

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 ini dapat ditulis dengan tanda "petik".
  3. [value_if_false] adalah output yang ditampilkan ketika data tidak memenuhi logical_test (false). Nilai variabel ini dapat ditulis dengan 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 diatas 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

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?

Nah, 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.

Nah, karena Microsoft Excel merupakan software komputer dan harus didikte untuk mengerjakan suatu hal. Juri tersebut harus membuat kriteria penilaian dalam bentuk logika matematika sederhana (bagian A di atas) berikut:

Ini diselesaikan dengan menggunakan logika AND.

=AND(kriteria bawah, kriteria atas)

Misal sel data awal yang ingin dieksekusi A1

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…

260 KOMENTAR

  1. Kak Mau Tanya juga dong,
    saya mau buat rumus seperti ini,
    Jika ukuranya kurang dari 1 Meter harganya 18 ribu
    Jika Ukuranya lebih dari 1 Meter tapi kurang dari 10 Meter Harganya 16 ribu
    Jika Ukuranya lebih dari 10 Meter tapi kurang dari 100 Meter Harganya 15 ribu
    Jika Ukuranya lebih dari 100 Meter tapi kurang dari 500 Meter Harganya 14 ribu
    Jika Ukuranya lebih dari 500 Meter tapi kurang dari 1000 Meter Harganya 12 ribu

    Mohon di buatkan rumusnya ya kak,
    untuk donasinya kemana ?

    • Terima kasih atas komentarnya kak MugMug…
      =IF(A1<=1,18000,IF(AND(A1>1,A1<=10),16000,IF(AND(A1>10,A1<=100),15000,IF(AND(A1>100,A1<=500),14000,IF(AND(A1>500,A1<=1000),12000, "Ukuran>1000")))))

      A1 = ukuran
      Namun pertanyaan kakak masih rancu, sehingga rumus tersebut saya buat untuk

      <=1 18rb >1-10 16rb
      >10-100 15rb
      >100-500 14rb
      >500-1000 12rb
      >1000 Ukuran lebih

      Semoga dapat membantu pekerjaannya kak 🙂

  2. maaf admin saya mau nanya jika hari Biasa makan tidak dikurangin dan jika hari libur lebih dari 8 jam dikurangin 45 menit rumus nya giman admin

    • Terima kasih kak imam atas komentarnya…
      Tapi saya masih belum paham maksud pertanyaanya. Bisa dijelaskan ulang kak 🙂

    • Jadi gini admin, contoh
      adi Lembur keterangan hari biasa tidak pengurangan waktu istrahat, sedangkan jika Adi lembur Keterangan hari libur lebih dari 7 jam maka ada pengurangan waktu istirahat 45 menit.

    • Nama ket
      A B C D E F G
      no Nama Ket hari Start Date Lembur awal 1 Lembur akhir Total
      1 SUBYARNO Biasa 24-Okt-18 0:00 2:00 2:00
      2 SUBYARNO Libur 24-Okt-18 8:00 18:00 10:00

      jadi di kolom total nya jika lebih dari 7 jam maka ada pengurangan waktu istrahat 45 menit admin untuk ket hari Libur.

    • Baik kakak Imam,

      =IF(A3="Biasa","Tidak Pengurungan Waktu Istirahat",IF(AND(A3="Libur",B3>7),"Dikurangan 45 menit","Tidak Pengurangan Waktu Istirahat"))
      Kolom A= keterangan
      Kolom B= jam

      Semoga membantu 🙂

  3. agan saya minta tolong nih,bagai mana membedakan waktu siang dan malam jika parameter nya jam 07:00 dan 19:00. bila diatas jam 07:00 s/d 19:00 (day) dan bila jam 19:00 s/d 07:00 night.
    itu bentuk rumus nya gimana gan? tolong ya gan makasih....

    • Mohon maaf kak Wahyu, saya tidak bisa membalas dengan cepat… karena situs Advernesia kemarin masih ada perbaikan…

      =IF(OR(CONVERT(A1,"hr","mn")>CONVERT(TIME(19,0,0),"hr","mn"),CONVERT(A1,"hr","mn")

  4. NAMA PLAT KENDARAAN KETERLAMBATAN PEMBAYARAN PAJAK PROPINSI
    ADRIANO SEMARANG 3 JAWA TENGAH
    DIANA BANDUNG 2 JAWA BARAT
    TIARA JAKARTA 4 DKI JAKARTA
    ARMAN SEMARANG 1 JAWA TENGAH
    TONY JAKARTA 2 DKI JAKARTA
    STARK BANDUNG 3 JAWA BARAT
    SCARLET SEMARANG 3 JAWA TENGAH
    JOE JAKARTA 6 DKI JAKARTA
    RAZER BANDUNG 2 JAWA BARAT
    NITA JAKARTA 3 DKI JAKARTA

    JIKA PLAT KENDARAAN DARI SEMARANG ATAU JAKARTA DAN KETERLAMBATAN PEMBAYARAN PAJAK >=3 TAHUN, MAKA PROGRAM PEMUTIHAN "IKUT PEMUTIHAN", JIKA TIDAK "BAYAR DENDA"

    GIMANA ITU MAS RUMUSNYA MOHON SOLUSINYA

    • Mohon maaf kak Imam Sugiarto, saya tidak dapat merespon dengan cepat.
      Kemarin situs Advernesia ada perbaikan,
      Kasus ini memuatnya fungsi AND menggandeng fungsi OR
      =IF(AND(OR(A1="Jakarta",A1="Semarang"),B1>=3),"Pemutihan","Bayar Denda")
      Terima kakak 🙂

  5. Gan mau minta tolong, kalau misalkan saya punya data
    Mandiri 100.000
    BCA 200.000
    Mandiri 150.000
    Mandiri 300.000
    BCA 250.000
    BCA 100.000

    Data ini berlanjut sampai jumlahnya ratusan
    saya mau menjumlahkan berapa yg masuk di mandiri di cell tersendiri, dan berapa yg masuk di bca di cell tersendiri

    Mandiri =
    BCA =

    kira2 bagaimana ya gan rumusnya ?

    • Terima kasih kak Erick Lauwanto...
      Kakak dapat menggunakan fungsi SUMIF untuk mempermudah membuat formulanya
      Misalnya
      =SUMIF(A1:A10,"=Mandiri",B1:B10)
      Untuk lebih lanjut silakan membaca artikel berikut Fungsi SUMIF
      Semoga bermanfaat 🙂

  6. 100
    100
    100
    100
    70
    70
    70
    120
    75
    75
    70
    100
    100
    70
    Hanya ada 2 kategori 100 dan 70
    Untuk mencari rumus selain hasil 100 dan 70 hasilnya dikategorikan lain-lain gimana?

    • =IF(OR(A1=70,A1=100),A1,"lain-lain")

      Semoga bermanfaat kakak Seviq F. 😉
      Jangan sungkan jika ada masukkan lainnya 🙂

    • =IF(OR(A1=70,A1=100),A1,"lain-lain")
      Rumus di atas mendapay input dari sel A1 sebagai logical test nya, misalnya
      Jika A1 isinya 100 maka dikeluarkan angka 100,
      Jika A1 isinya 70 maka dikeluarkan 70
      namun jika A1 isinya bukan 70 atau 100, maka hasilnya "Lain-lain"
      Seperti itu yang saya tangkap permasalahannya…

      Bisa dijelaskan lebih rinci lagi kakak 😉

    • 100
      100
      100
      100
      70
      70
      70
      120
      75
      75
      70
      100
      100
      70
      Selain angka 100 dan 70 itu cara menghitung jumlahnya dengan rumus seperti apa?
      Yang bukan 100 dan 70, 120 75 dll misal ini datanya

    • Untuk menghitung jumlahnya kakak bisa menggunakan rumus SUMIFS namun penggunaan lebih rumit dari rumus IF bertingkat untuk kasus ini.
      Untuk mempermudah kakak dapat menggunakan PIVOT TABLE... silakan mengunjungi tautan berikut

      Pivot Table

      semoga bermanfaat 😉

  7. Mohon bantuan untuk menulis rumus :
    1. 3 menit - 15 menit = 0,1%
    2. 16 menit - 30 menit =0,25%
    3. 31 menit - 60 menit = 0,5%
    4. 1 jam seterusnya = 1%

    • Wah itu pakai formula matematika kak halida, 1 jam seterusnya itu maksud berlanjut atau 1% saja tetap?

  8. Izin bertanya, kalo rumus yang di gunakan apa bila ada 4 kolom (Nilai, Kehadiran, Tugas, UTS, UAS) misal ada kolom tugas kosong karena belum mengumpulkan, kalo dari 4 kolom tersebut ada yang kosong maka nilai nya T (Tunda) bagaimana yah rumusnya. ??

    • Terima kasih kak Asep Surya Somantri,
      Kaka bisa menggunakan fungsi IF OR dengan fungsi ISBLANK
      misal
      =IF(OR(ISBLANK(A1),ISBLANK(B1),ISBLANK(C1),ISBLANK(D1)),"Tunda","Nilai Jika Tidak Kosong")
      Argumen if_false bisa disesuaikan dengan kebutuhan.

      Semoga membantu pekerjaannya 🙂

    • Kalo untuk menampilkan data, lebih baik menggunakan PivotTable. Kakak dapat mengurutkan data dari tanggal tertentu hingga tertentu.
      Semoga bermanfaat 🙂

  9. Mohon dibantu, jika saya punya kondisi sebagai berikut:
    Ada 2 kondisi penjualan T1 dan T2 . Kemudian jika kategori "T1" maka berlaku margin sebagai berikut: harga 0-50% = 45%; 51-100 = 30%; 101-300 = 25%; 301-1000=20% jika kategori "T2" maka berlaku margin sebagai berikut: harga 0-50% = 30%; 51-100 = 25%; 101-300 = 20%; 301-1000=10%
    Bagaimana menentukan rumusnya?

    • Terima kasih atas komentarnya pak Retno, bapak bisa menggunakan fungsi IF dengan gabungan AND
      =IF(A2="T1",(IF(AND(B2>=0,B2<=50),0.45,IF(AND(B2>=51,B2<=100),0.3,IF(AND(B2>=101,B2<=300),0.25,IF(AND(B2>=301,B<=1000),0.2,"Harga salah"))))),IF(A2="T2",IF(AND(B2>=0,B2<=50),0.3,IF(AND(B2>=51,B2<=100),0.25,IF(AND(B2>=101,B2<=300),0.2,IF(AND(B2>=301,B2<=1000),0.1,"Harga Salah")))),"Kondisi Salah"))

  10. Tolong bantuannya min, soal saya begini, jika total bayar >2.500.000 maka "Excelent" jika total bayar 2.000.000 sd 2.500.000 maka "Good" jika total bayar dibawah 2.000.000 maka "Fine". Lalu rumusnya bagaimana ya min terimakasihh

    • Terima kasih kakak Diah,
      Kakak dapat menggunakan rumus berikut
      =IF(A1>2500000,"Excelent",IF(A1<2000000,"FINE","GOOD"))
      Dengan A1 adalah sel Total Bayar, semoga dapat membantu pekerjaanya kakak ;)

  11. Min
    Kalau misal jika Hutang = Pembayaran berarti "Lunas", Jika Hutang belum terbayar berarti "Kalian Setor", dan jika saya yang berhutang "saya setor".
    Rumusnya gimana Min?

    • Thanks kak Hery, atas komentarnya.
      Kakak bisa menggunakan rumu If bertingkat biasa
      =IF(A1="Pembayaran","Lunas",IF(A1="Belum Bayar","Kalian Setor",IF(A1="Saya Berhutang","Saya Setor","Input Hutang")))

      Semoga membantu pekerjaanya 🙂

  12. Min Kalau Misalnya Kan Ada Total Harga. Kalau Total Harga Nya >50000 Maka Ada Diakon 25% Kalau Harganya50000 Maka Diskonnya Menjadi 35% Dan Apabila Referensi Sel Tertentu Menyatakan "No" Dan Total Harga >50000 Maka Diskonnya Tetap 25%

    • Sebelumnya, maksud total harga dan harga itu bagaimana kak? Dan pernyataan total harga > 50000 maka diskon 25% dengan total harga >50000 dan sel tertentu ="No", menyebabkan logika IF mengeluarkan output yang sama terhadap ke dua pernyataan.

      Terima kasih kak, semoga bermanfaat 🙂

  13. Min Tolong Bantuannya saya baru belajar Excel
    1.Data sbb:
    Nama : umur : Kapasitas ( Kg )
    Andi :18 : 1300
    Toni :19 :1100
    Dika : 23 : 2100
    Juned : 26 : 2400
    Murtejo : 37 : 1970
    Adi : 38 : 1875
    Zulham :42 :1600
    Kirno : 46 : 1300
    Juki : 50 : 1000
    Leman : 51 : 900

    2.Isiannya

    50 Thn
    Andi
    Toni
    Dika
    Juned
    Murtejo
    Adi
    Zulham
    Kirno
    Juki
    Leman

    tolong Min,rumus yang tepat untuk isian kapasitas ( Kg ),apabila datanya saya masukan maka kolom 2.isian otomatis terisi sesuai umurnya.
    Terima Kasih sebelumnya

  14. min saya mau buat persamaan grafik fungsi sembarang jadi semisal di kolom b1 itu x^1 b2 itu x^2 dan x^3 nah semisal nanti kalau dibuat persamaan f(x) = 4 * (pencet b1) otomatis kalau saya pencet b1 bakal jadi persamaan kayak gini f(x) = 4 * x^1 apakah bisa min? tolong bantuannya

  15. min tolong bantuannya, saya ada tugas grafik fungsi dan disuruh manipulasi tanda dan suku menggunakan if . rumusnya yang digunakan yang mana ya min

  16. Saya mencoba membantu,
    B1 isinya =A1^2
    C1 isinya =A1^3

    Dengan D1 adalah nilai variabel x1, D2 = x2. D3=x3, dst

    C1 adalah fungsi f(x) isinya = 4 * B1
    C2 adalah fungsi y(x) misalnya, isinya = 4 * B2

    Bisa dibuat tabel grafiknya untuk seterusnya dengan melakukan autofill ke bawah

    Semoga membantu 🙂

1 2 3 4 5 6

AYO BERKOMENTAR

Tulis komentar
Masukkan nama Anda