Posted in
RUMUS FUNGSI LOGIKA MICROSOFT EXCEL
A. FUNGSI HLOOKUP
Fungsi HLOOKUP digunakan untuk mencari data (value) secara Horisontal sesuai dengan kriteria yang kita inginkan. Pada Fungsi HLOOKUP apabila data tidak ditemukan pada kolom pertama, pencarian akan terus dilakukan pada kolom kedua dan seterusnya. Ada dua pilihan dalam pencarian menggunakan HLOOKUP jika kita masukan nilai TRUE pada Range_lookup maka jika data tidak ditemukan maka akan dipilih data yang paling mendekati, tetapi jika kita pilih FALSE pada Range_lookup maka jika data tidak ditemukan maka akan dihasilkan nilai error (#NA).
Contoh :
Tabel Gaji Pegawai PT Maju Mundur
Grade | 1 | 2 | 3 | 4 | 5 | 6 | 7 |
Gaji Pokok | 1,000,000 | 1,500,000 | 2,000,000 | 2,500,000 | 3,000,000 | 3,500,000 | 4,000,000 |
Potongan Pensiun | 17,5 | 26,25 | 35 | 43,75 | 52,5 | 61,25 | 70 |
Data Penjualan Bulan Agustus 2008
Marketer | Fredy | Bambang | Yopi |
Penjualan | 50 | 60 | 70 |
Bonus | 0 | 100000 | 200000 |
Berdasarkan dari kedua data tersebut dapat dicari rincian gaji masing pegawai :
No | Nama | Grade | Gaji Pokok | Pensiun | Bonus | Jumlah |
1 | Fredy | 7 | 4,000,000 | 70 | 0 | 3,930,000 |
2 | Bambang | 3 | 2,000,000 | 35 | 100 | 2,065,000 |
3 | Yopi | 5 | 3,000,000 | 52,5 | 200 | 3,147,500 |
Untuk mendapatkan GAJI POKOK kita gunakan formula sebagai berikut :
1. | =HLOOKUP(C20;$B$6:$H$8;2;FALSE) |
2. | =HLOOKUP(C21;$B$6:$H$8;2;FALSE) |
3. | =HLOOKUP(C22;$B$6:$H$8;2;FALSE) |
Untuk mendapatkan POTONGAN PENSIUN kita gunakan formula sebagai berikut :
1. | =HLOOKUP(C20;$A$6:$H$8;3;FALSE) |
2. | =HLOOKUP(C21;$A$6:$H$8;3;FALSE) |
3. | =HLOOKUP(C22;$A$6:$H$8;3;FALSE) |
Untuk mendapatkan BONUS kita gunakan formula sebagai berikut :
1. | =HLOOKUP(B20;$B$12:$D$14;3;FALSE) |
2. | =HLOOKUP(B21;$B$12:$D$14;3;FALSE) |
3. | =HLOOKUP(B22;$B$12:$D$14;3;FALSE) |
B. FUNGSI VLOOKUP
Fungsi VLOOKUP digunakan untuk membaca suatu data secara vertikal, lalu mengambil nilai yang diinginkan pada table tersebut berdasarkan kunci tertentu.
Bentuk umum penulisan fungsi ini adalah sbb:
=VLOOKUP(Lookup_value, Table_array, Col_index_num, range_lookup)
Keterangan :
· Lookup_value adalah sel referensi/nilai yang hendak dijadikan kunci dalam pencarian data.
· Table_array adalah tabel/range yang merupakan table yang menyimpan data yang hendak kita cari.
Catatan Penting !
ü Usahakan Table_array sudah disort pada field kunci nya (baik secara ascending ataupun descending)
ü Huruf besar ataupun huruf kecil dianggap sama saja atau diabaikan pada field kunci di Table_array.
· Col_index_num adalah nomor kolom yang hendak kita ambil nilainya. Col_index_num harus berisi nilai sama dengan atau lebih besar dari 1. Jika kurang dari 1, VLOOKUP akan mengembalikan nilai error = #VALUE!. Jika col_index_num lebih besar dari jumlah kolom pada table_array nya, VLOOKUP akan mengembalikan nilai error = #REF!.
· Range_lookup adalah nilai logika TRUE / FALSE yang mana VLOOKUP akan mencari data secara tepat atau secara kira-kira atau pendekatan.
ü Jika range_lookup kita isi TRUE, maka yang akan digunakan adalah metode pendekatan atau kira-kira. Jika nilai yang di cari tidak ada, maka yang akan diambil adalah nilai yang lebih besar pada sell berikutnya dimana nilainya lebih kecil atau sama dengan nilai lookup_value (field kunci). Jika tidak ada nilai yang lebih kecil atau sama dengan nilai lookup_value (field kunci), maka VLOOKUP akan mengembalikan nilai error = #N/A.
ü Adapun jika range_lookup di isi FALSE, maka yang akan digunakan adalah metode tepat. Pada metode tepat ini, jika VLOOKUP tidak menemukan data yang di cari, maka ia akan mengembalikan nilai error = #N/A. Maka untuk mengatasi hal ini, gunakan rumus IF(logical_test,value_if_true,value_if_false) dan ISERROR(Value).
CONTOH :
Dengan mengacu pada gambar diatas, yaitu range A4:C12 kita akan mencoba contoh-contoh penggunaan rumus VLOOKUP sbb:
1. VLOOKUP(1,A4:C12,1,TRUE) = 0.946
2. VLOOKUP(1,A4:C12,2) = 2.17
3. VLOOKUP(1,A4:C12,3,TRUE) = 100
4. VLOOKUP(.746,A4:C12,3,FALSE) = 200
5. VLOOKUP(0.1,A4:C12,2,TRUE) = #N/A, karena 0.1 kurang dari nilai terkecil pada kolom A.
6. VLOOKUP(2,A4:C12,2,TRUE) = 1.71
7. VLOOKUP(2,A4:C12,2,FALSE) = #N/A
8.
IF(ISERROR(VLOOKUP(2,A4:C12,2,FALSE)),0,(VLOOKUP(2,A4:C12,2,FALSE)) = 0
Fungsi ini akan menghasilkan nilai TRUE (benar), jika semua argumen benar, dan akan menghasilkan nilai FALSE (salah) jika salah satu atau lebih argumen bernilai FALSE.
Syntax AND(logika1,logikal,…)
Logika1, logika2, … dengan range logika 1 sampai 30 kondisi yang bisa menghasilkan nilai TRUE atau FALSE.
Catatan:
Argumen harus bisa dievaluasi ke nilai value seperti TRUE atau FALSE, atau argumen merupakan sekumpulan array, atau menunjuk ke refernsi kumpulan cell yang berisi nilai-nilai logical.
Jika terdapat array atau referensi kumpulan cell bersisi text atau bernilai kosong maka nilainya akan diabaikan.
Jika range tertentu mengandung nilai yang bukan logical values, fungsi AND akan menghasilkan nilai error berupa #VALUE!
Contoh 1 :
Penjelasan :
- Pada baris A2 akan menghasilkan nilai TRUE karena semua argumen bernilai TRUE
- Pada baris A3 akan menghasilkan nilai FALSE karena salah satu argumen bernilai FALSE
- Pada baris A4 akan menghasilkan nilai TRUE karena semua argumen bernilai TRUE
Contoh 2 :
Penjelasan :
- Pernyataan pertama akan bernilai TRUE karena cell A2 yang bernilai 50 berada pada kisaran angka 1 dan 100
- Pernyataan kedua akan menghasilkan nilai Nilai diluar batas range karena cel A3 yang bernilai 104 berada di luar kisaran angka 1 dan 100
- Pernyataan ketiga akan bernilai sama dengan cell A2 jika nilai A2 berada pada kisaran angka 1 dan 100, dan akan menghasilkan Nilai diluar batas range jika diluar kisaran angka 1 dan 100.
Fungsi ini akan menghasilkan nilai “TRUE” bila salah satu atau semua pernyataan bernilai benar, dan akan bernilai “FALSE”, bila semua pernyataan bernilai salah.
Dari fungsi diatas akan menghasilkan nilai “TRUE”, karena: Penyataan ke-1, “1+1=2 (TRUE), Penyataan ke-2, “2+2=4 (TRUE).
Dari fungsi diatas akan menghasilkan nilai “TRUE”, karena:
Penyataan ke-1, “1+1=4 (FALSE), Penyataan ke-2, “2+2=4 (TRUE).
Penyataan ke-1, “1+1=4 (FALSE), Penyataan ke-2, “2+2=4 (TRUE).
Dari fungsi diatas akan menghasilkan nilai “FALSE”, karena:
Penyataan ke-1, “1+1=4 (FALSE), Penyataan ke-2, “2+2=2 (FALSE).
Fungsi IF merupakan fungsi logika, yang artinya suatu nilai mempunyai syarat dan dapat bernilai benar jika syarat terpenuhi serta bernilai salah jika syarat tidak terpenuhi. Secara umum rumus fungsi IF dalam Microsoft Excel adalah sebagai berikut :
IF(Logical_Test;Value_if_True;Value_if_false) atau
IF(Syarat;Hasil_Jika_Syarat_Terpenuhi;Hasil_Jika_Syarat_Tidak_Terpenuhi)
Perhatikan salah satu contoh kasus dibawah ini:
Terdapat daftar nilai siswa-siswi yang mempunyai keterangan lulus dan tidak lulus, dengan syarat jika nilai lebih kecil atau sama dengan 50 maka siswa dinyatakan “Tidak Lulus”.
Data keterangan diisi menggunakan rumus fungsi IF. sell D3 menggunakan rumus berikut:
=IF(C3<=50;”Tidak Lulus”;”Lulus”)
