Rumus Excel IF Bagian Dua

A. Membuat Drop Down List Item

Terdapat kasus kita memerlukan drop down list pada beberapa cell dari excel. Isi salah satu drop down list sangat bergantung pada isi drop down list lainnya. Lihat gambar bawah:

Jika kita memilih item Bola pada gambar diatas, maka drop down list pada cell B2 akan langsung menampilkan pilihan List Item Bola (lihat gambar bawah) :

Jadi Drop Down List pada kolonm B2 bergantung nilainya dengan kolom A2.

Untuk membuat list diatas caranya sangat mudah.

1. Pertama-tama buatlah kerangka seperti dibawah ini :

2. Klik cell A2 dan pilih Data –> Data Validation –> Data Validation…

3. Pilih tab setting, pilih allow : “List” dan ketik pada source : “Bola, Buku”

Jika regional setting adalah Indonesia maka harus diketik “Bola;Buku” (titik koma)

Cell A2 pun berubah menjadi drop down list

4. Define LIST untuk bola dan buku

Block cell D2 sampai D4, klik tab formula –> Define Name

Dari window yang muncul, beri nama “ListBola” dan klik OK (lakukan hal yang sama dengan buku, block cell F2:F4 dan nama listnya adalah  “ListBuku”)

5. Klik cell B2 dan pilih Data –> Data Validation –> Data Validation…

6. Pilih Allow : List dan masukkan rumus seperti dibawah ini pada Source

=if(A2="Bola",ListBola, ListBuku)

7. SELESAI! ^^

B. Mencari Nilai Maximum

Pernah dapat kasus seperti dibawah? Dimana kita diminta untuk mencari maximum dari jenis item “A”?

Caranya sebenarnya gampang, tinggal gunakan rumus array dibawah ini :

=MAX(IF(A2:A7="A",B2:B7,))

pada saat rumus selesai diketik, tekan CTRL – SHIFT – ENTER agar menginstruksikan excel untuk melakukan array formula. Jika benar, hasilnya adalah seperti dibawah ini :

Logika dari rumus adalah pertama-tama excel akan mengecek apakah nilai cell A2 sampai A7 adalah A, jika A maka masukkan variabel ke function MAX(). Hasil dari pengecekan adalah MAX(1,3,5). Karena 5 adalah yang terbesar maka akan ditampilkan.

C. RUMUS EXCEL IF untuk Conditional Formatting

Dengan tabel dibawah, bagaimana jika kita ingin agar item dengan jumlah > 4 diwarnai cellnya dengan warna hijau seperti dibawah?

Caranya gampang

1. Block cell B2 : B7 dan pilih Home –> Conditional formatting –> new rule

2. Sebuah windows akan muncul

Klik Use a formula to determine which cells to format, masukkan formulanya sebagai berikut:

=IF(B2>4,TRUE,FALSE)

(yang memiliki regional setting Indonesia harus menggunakan titik koma”;” bukan koma “,” sebagai pemisah variable)

Kemudian klik tombol format dan pilih warna hijau pada tab fill.

Klik OK

Setelah itu cell yang bernilai >4 akan berwarna hijau. Jika kalian ada pertanyaan kenapa harus cell B2 bukan yang lain? Saya memilih cell teratas karena EXCEL akan menyadari dan mengadjust nilainya untuk cell dibawahnya secara otomatis.

D. RUMUS EXCEL IF UNTUK FILTER TABEL

Sekarang terdapat kasus dimana kita ingin melakukan filter tabel excel dibawah sehingga record yang ditampilkan hanya Item A dengan jumlah > 2

Pertama-tama klik cell disebelah kanan (dalam contoh ini E1) dan ketik “DUMMY”. Nama sebenarnya terserah tetapi excel hanya dapat berjalan jika ada nama fieldnya. Kemudian klik cell E2 dan ketik rumus :

=IF(AND(A2="A",B2>2),TRUE, FALSE)

Kemudian block kolom A dan B dan pilih tab Data –> Advanced

Dari jendela yang muncul, masukkan range E1:E2 untuk Criteria Range dan klik OK.

Dan filter tabel excel pun selesai ^^