Penjelasan Logika IF, IF (AND), IF (OR), dan IF (XOR)

IF

Fungsi IF sederhana menggunakan logika : TRUE jika syarat terpenuhi, FALSE jika syarat tidak terpenuhi.

Formulanya = IF(logical_test, [value_if_true], [value_if_false])

(Syarat, Output bila syarat terpenuhi, Output bila syarat tidak terpenuhi)

Contoh : Jika nilai siswa lebih besar dari 69 (Syarat) maka siswa lulus (Output TRUE), jika syarat tidak terpenuhi maka siswa tidak lulus (Output FALSE).

Screenshot 2018-06-21 23.05.57.png

 

IF (AND)

Fungsi IF dikombinasikan dengan AND menggunakan logika : TRUE jika SEMUA SYARAT TERPENUHI, FALSE jika SEBAGIAN atau SEMUA syarat tidak terpenuhi.

Formulanya: =IF(AND(logical_test1, logical_test2, …),[value_if_true],[value_if_false])

((Syarat 1, Syarat 2, …, Syarat n), Output bila SEMUA syarat terpenuhi, Output bila SEBAGIAN atau SEMUA syarat tidak terpenuhi)

Contoh : Jika nilai Siswa lebih dari besar dari 69 DAN persentase kehadiran Siswa di kelas lebih dari  75% maka siswa Lulus (Output TRUE), jika kedua syarat atau salah satu syarat tidak terpenuhi maka siswa tidak lulus (Output FALSE).

Screenshot 2018-06-21 23.08.24.png

 

IF (OR)

Fungsi IF dikombinasikan dengan OR menggunakan logika : TRUE jika SEBAGIAN, ATAU SEMUA SYARAT terpenuhi, FALSE jika SEMUA SYARAT tidak terpenuhi

Formulanya: =IF(OR(logical_test1, logical_test2, …),[value_if_true],[value_if_false])

((Syarat 1, Syarat 2, …., Syarat n), Kondisi bila SEBAGIAN ATAU SEMUA syarat terpenuhi, kondisi bila SEMUA syarat tidak terpenuhi)

Contoh : Jika hasil test menunjukan mata menderita miopi, presbiopi, astigmatisme maka akan diresepkan lensa kacamata yang sesuai. Jika salah satu saja hasil test menunjukkan adanya kelainan mata, maka akan diresepkan lensa, demikian pula jika ditemukan kombinasi kelainan mata, tapi jika tidak ada ditemukan kelainan mata sama sekali maka tidak diresepkan lensa.

Screenshot 2018-06-21 23.06.57.png

 

IF (XOR)

Fungsi IF dikombinasikan dengan XOR (exclude OR) menggunakan logika : TRUE jika HANYA SALAH SATU SYARAT TERPENUHI, FALSE jika SEMUA syarat terpenuhi atau SEMUA syarat tidak terpenuhi.

Formulanya: =IF(XOR(logical_test1, logical_test2, …),[value_if_true],[value_if_false])

((Syarat 1, Syarat 2, …., Syarat n), Kondisi bila SALAH SATU syarat terpenuhi, kondisi bila LEBIH DARI SATU syarat terpenuhi ATAU SEMUA syarat terpenuhi ATAU SEMUA syarat TIDAK terpenuhi)

Contoh : Jika suara perolehan Paslon 1, Paslon 2, atau Paslon 3 lebih dari 50% maka Pilkada cukup 1 putaran. Jika ada 2 paslon yang memperoleh 50% maka pilkada 2 putaran, jika semua paslon tidak ada yang memperoleh lebih dari 50% maka pilkada 2 putaran.

Screenshot 2018-06-21 23.09.32.png

Berikut file LogikaIF.xls  terlampir agar anda bisa mencoba untuk lebih memahami.

Semoga bermanfaat.

(Penulis : Ida Bagus Tresna Dwipayana)

 

 

 

Advertisements

CTRL+T : Excel Table Shortcut

Perhatikan tabel berikut :

Screenshot 2018-06-20 16.44.03.png

Tabel di atas sudah kita gunakan dalam postingan Search Box Dinamis

Tabel di atas adalah tabulasi data dalam bentuk range. Agar lebih optimal, maka tabulasi data tersebut kita ubah ke bentuk ‘EXCEL TABLE’.

Format ‘excel table’ menyediakan fitur SORT, SEARCH, FILTER, dan TOTAL ROW yang membuat pekerjaan menjadi lebih efisien.

Cara-nya :

  1. Letakan kursor pada tabel, lalu tekan CTRL+A untuk menselect seisi table beserta header kolomnya.
  2. tekan CTRL+T, maka akan muncul dialogScreenshot 2018-06-20 17.02.46.png
  3. Centang ‘My Table has headers’, klik OK
  4. Excel Table anda sudah jadi, dan terlihat seperti pada gambar di bawah ini :Screenshot 2018-06-20 17.06.00.png
  5. Perhatikan ada dropdown box di sudut kanan bawah tiap header kolom. Coba klik salah satu dropdown box tersebut, misal pada kolom pengarang, anda akan melihat tampilan seperti gambar di bawah :Screenshot 2018-06-20 17.10.39.png
  6. Ketik sebuah kata pada kotak search misalnya, ‘Agung’, maka excel akan memfilter isi tabel dan hanya menampilkan baris dengan pengarang yang namanya mengandung kata ‘Agung, sedangkan baris lain yang tidak memenuhi kriteria akan excel hide dari tampilan.Screenshot 2018-06-20 17.18.20.png
  7. Sekarang silahkan coba sendiri fitur SORT, dan FILTER menggunakan multiple selection (silahkan check atau uncheck data yang diinginkan).
  8. Excel Table juga punya fitur TOTAL, yang perlu di aktifkan dengan cara mencentang opsi TOTAL ROWScreenshot 2018-06-20 17.27.33.png
  9. Setelah total row diaktifkan maka pada bagian bawah Excel Table akan muncul row (baris) seperti pada gambar :Screenshot 2018-06-20 17.32.37.png
  10. Pilih SUM maka total harga buku yang muncul dalam excel table akan ditampilkan pada baris terakhir. Total Row ini bersifat dinamis, menyesuaikan dengan perubahan isi tabel. Anda juga bisa mencoba yang lain seperti AVERAGE untuk mengetahui rata-rata harga buku, MAX untuk mengetahui Harga termahal, jadi silahkan dieksplor sesuka anda.

Semoga Bermanfaat.

(Penulis : Ida Bagus Tresna Dwipayana)

 

Membuat ‘Search Box’ Dinamis

Excel memiliki table yang dilengkapi dengan fitur filtering. Fitur ini bagus tapi kurang flexible. Jika tujuan utama kita hanya untuk mencari tahu letak suatu value dalam sebuah tabel, maka anda perlu mencoba metode ‘SEARCH BOX’ dalam postingan ini.

Metode ‘SEARCH BOX’ mengkombinasikan fungsi IF, ISBLANK, SEARCH dan fitur Conditional Formatting.

Perhatikan table berikut :

Screenshot 2018-06-19 23.51.29.png

Tabel di atas berisi data buku, dalam kasus ini kita akan membuat semacam search box untuk menemukan record dalam tabel yang memuat kata, suku kata, atau frasa yang kita cari.

Caranya :

  1. Tambah (insert) 2 row di atas judul kolom.
  2. Pada cell B1 ketik ‘CARI:’, lalu beri warna pada cell C1 sebagai tanda letak kata kunci pencarian nanti diketik.
  3. Pada Name Box ganti nama cell C1 menjadi ‘KATA_KUNCI’
  4. Blok seluruh data dalam table, klik icon CONDITIONAL FORMATTING pada tab HOME.
  5. Pada dropdown option CONDITIONAL FORMATTING pilihlah NEW RULE
  6. Pada SELECT A RULE TYPE pilihlah USE A FORMULA TO DETERMINE WHICH CELLS TO FORMAT
  7. pada kotak FORMAT VALUES WHERE THIS FORMULA IS TRUE ketiklah formula berikut : =IF(ISBLANK(kata_kunci),0,SEARCH(kata_kunci,$A4&$B4&$C4))
  8. Klik tombol FORMAT lalu pilihlah warna highlight yang ingin digunakan pada palet, lalu klik OK

Untuk lebih jelasnya perhatikan gambar di bawah ini :

Screenshot 2018-06-20 00.17.19.png

Screenshot 2018-06-20 00.18.30.png

Screenshot 2018-06-20 00.22.15.png

Screenshot 2018-06-20 00.23.10.png

Sekarang kita coba hasilnya, dengan mengetikan sebuah kata kunci, misalnya ‘Batu’ , maka hasilnya tampak pada gambar di bawah ini:

Screenshot 2018-06-20 00.28.01.png

Kita coba masukan kata kunci lain, misalnya ‘One’ :

Screenshot 2018-06-20 00.30.30.png

Kita coba pakai kata kunci lain, misalnya ‘Pustaka’ :

Screenshot 2018-06-20 00.31.51.png

Baris yang memuat kata kunci yang anda ketik pada kotak pencarian akan di-highlight oleh fitur conditional formatting, sehingga memudahkan anda untuk tahu dimana letak record yang related dengan kata kunci tersebut.

Kelebihan dari metode ini adalah anda bisa mengetik kata kunci apapun yang anda tahu terkait suatu pencarian, dalam kasus ini, kata kunci itu bisa berupa penggalan judul, pengarang, ataupun penerbit-nya.

File terkait tutorial ini silahkan didownload pada link SearchBoxDinamis.xls

Selamat mencoba, semoga bermanfaat.

(Penulis : Ida Bagus Tresna Dwipayana)

Cara Melakukan ‘Reverse Lookup’

Perhatikan tabel berikut :

Screenshot 2018-06-17 13.12.52.png

Pada kasus ini kita akan belajar tentang cara mencari tahu TANGGAL LAHIR, JENIS KELAMIN, GOL. DARAH, ALAMAT, dan PEKERJAAN seseorang, hanya dengan menggunakan NAMA orang itu sebagai referensi-nya.

Jika pada kasus Pizza kita menggunakan multiple criteria (Crust, Size, dan Topping) untuk mengetahui Harga Pizza (many to one), maka pada kasus Data Personal ini kita menggunakan konsep yang sebaliknya (one to many), dengan mengkombinasikan fungsi MATCH dan INDEX.

Screenshot 2018-06-17 13.44.26.png

Caranya :

  1. Block seluruh data dalam tabel (range A2:F9) lalu pada Name Box ketik nama range tersebut menjadi ‘DATA’.
  2. Select cell C11 lalu pada Name Box ketik nama cell tersebut dengan kata ‘NAMA’
  3. Pada cell C12 ketikan formula berikut : =INDEX(DATA,MATCH(NAMA,DATA,0),2)
  4. Pada cell C13 ketikan formula berikut : =INDEX(DATA,MATCH(NAMA,DATA,0),3)
  5. Pada cell C14 ketikan formula berikut : =INDEX(DATA,MATCH(NAMA,DATA,0),4)
  6. Pada cell C15 ketikan formula berikut : =INDEX(DATA,MATCH(NAMA,DATA,0),5)
  7. Pada cell C16 ketikan formula berikut : =INDEX(DATA,MATCH(NAMA,DATA,0),6)

Hasil :

Screenshot 2018-06-17 14.07.38.png

Dengan mengganti nama individu pada cell C11, maka data TANGGAL LAHIR, JENIS KELAMIN, GOLONGAN DARAH, ALAMAT, dan PEKERJAAN dalam cell C12, C13, C14, C15, dan C16 akan ikut menyesuaikan secara otomatis.

Screenshot 2018-06-17 15.18.43.png

Perhatikan tabel di atas, pada cell A5 dan A6 terdapat nama yang sama, jika pada cell c11 kita isi dengan nama CITA ataupun cITA maka pencarian (indexing) akan berhenti pada cell A5 (baris yang lebih atas) dan menampilkan hasil pencarian berupa data dari kolom B5, C5, D5, E5, dan F5. Saran saya, gunakanlah referen yang tidak akan ada value yang sama, misalnya nomor identitas, Nama boleh saja digunakan selama anda yakin tidak ada nama yang persis sama di bawah kolom NAMA.

Berikut link untuk mendownload file excel dari postingan ini : ReverseLookup.xls

Selamat menerapkan, semoga bermanfaat.

(Penulis : Ida Bagus Tresna Dwipayana)

Solusi ‘Look-up’ Multi Kriteria

Fungsi ‘look-up’ (hlookup, vlookup) pada Excel tidak bisa dipakai untuk melakukan pencarian yang melibatkan lebih dari satu kriteria.

Perhatikan tabel ini :

Screenshot 2018-06-16 22.57.11.png

Untuk memunculkan harga Pizza pada cell G5 berdasarkan jenis CRUST, SIZE, dan TOPPING, kita bisa memakai kombinasi fungsi INDEX, dan MATCH.

Screenshot 2018-06-17 10.26.50.png

Caranya :

 

  1. Blok seluruh data di bawah kolom CRUST yakni (A2:A10), pada Name Box ganti nama range tersebut menjadi ‘CRUST’ (perhatikan gambar di atas)
  2. Blok seluruh data di bawah kolom SIZE yakni (B2:B10), pada Name Box ganti nama range tersebut menjadi ‘SIZE’
  3. Blok seluruh data di bawah kolom TOPPING yakni (C2:C10), pada Name Box ganti nama range tersebut menjadi ‘TOPPING’
  4. Blok seluruh data di bawah kolom HARGA yakni (D2:D10), pada Name Box ganti nama range tersebut menjadi ‘HARGA’
  5. Letakan kursor pada cell G5, lalu ketik formula berikut :
=INDEX(HARGA,MATCH(1,INDEX((G2=CRUST)*(G3=SIZE)*(G4=TOPPING),0,1),0))

Sekarang kita uji formula tadi untuk mencari harga Pizza dengan CRUST Thin, SIZE L, dan TOPPING Tuna.

Screenshot 2018-06-17 08.37.32.png

Caranya :

  1. Ketik ‘Thin’ pada cell G2
  2. Ketik ‘L’ pada cell G3
  3. Ketik ‘Tuna’ pada cell G4

Maka pada cell G5 akan muncul harga pizza dengan tiga kombinasi (kriteria) tadi.

Screenshot 2018-06-17 08.39.12.png

Formula yang mengkombinasikan fungsi INDEX dan MATCH ini bisa dipakai untuk kasus – kasus pencarian yang melibatkan lebih dari 1 kriteria, kita tinggal sesuaikan range data-nya. Penamaan range data itu sendiri bersifat opsional untuk mencegah salah range, kalau mau Anda bisa langsung ketik range-nya, misalnya anda bisa langsung mengetik A2:A10 ketimbang mengetik CRUST pada formula.

Berikut link untuk mendownload file excel dari postingan ini : IndexMatchMultipleLookup.xls

Selamat menerapkan, semoga bermanfaat.

(Penulis : Ida Bagus Tresna Dwipayana)