Sekarang kita akan mempelajari tentang fungsi fungsi dalam excel, emang kelihatannya ribet yak, tapi semoga aja pada ngerti entri kali ini tentang fungsi fungsi formula pada excel xD, ada IF, VLOOKUP, dan HLOOKUP (dipilih dari tingkatan yang susah menurut saya wkwkwk)
Nyok mulai!
Microsoft Excel menyajikan tabel secara vertikal dan horisontal. Dalam fungsi pembacaan tabel, pada program Ms. Excel juga secara horizontal (HLOOKUP) dan secara vertikal (VLOOKUP).
• Fungsi HLOOKUP dan VLOOKUP
Perintah ini sering dikenal dengan fungsi pembacaan tabel. Untuk menggunakan perintah ini harus ada 2 tabel atau lebih. Salah satu tabel berfungsi sebagai tabel pembaca dan tabel
lainnya sebagai tabel yang dibaca (tabel sumber/data source). Antara tabel pembaca dan tabel sumber harus memiliki data yang sama sebagai kunci pembacaan.
A. Fungsi =VLOOKUP (Vertikal Lookup)
Fungsi ini DIGUNAKAN untuk membaca tabel sumber yang bentuknya tegak lurus (vetikal). Bentuk umum dari fungsi ini adalah sebagai berikut. =VLOOKUP(Lookup value, Table array, Col_index_num, range lookup)
Keterangan:
Lookup Value : kunci pembacaaan antara kedua tabel.
Table array : range tabel sumber.
Col index_num : nomor kolom dari tabel sumber.
Range Lookup : selalu bernilai 0 (boleh tidak dituliskan).
B. Fungsi = HLOOKUP (Horizontal Lookup)
Fungsi ini digunakan untuk membaca tabel sumber yang
bentuknya melintang (horizontal). Bentuk umum dari fungsi
ini adalah sebagai berikut.
= HLOOKUP(Lookup value, Table array, Row_index_num,
Range Lookup)
Keterangan:
Lookup Value : kunci pembacaan antara kedua tabel.
Table array : range table sumber.
Col index_num : nomor baris dari tabel sumber.
Range Lookup : selalu bernilai 0 (boleh tidak dituliskan).
contoh penggunaan :
permasalahan yang kita akan
selesaikan adalah pengisian nilai dari data di atas. kita gunakan referensi dr
tabel yg pertama . kita dapat menggunakan fungsi vlookup (vertical lookup).
Pemakaian VLOOKUP untuk kasus di atas adalah pada sel D8 akan kita masukkan rumus =VLOOKUP(C8,$A$2:$B$5,2), di mana C8 adalah data kunci yang akan dicocokkan, $A$2:$B$5 adalah area pencarian data termasuk kunci pencarian dan data yang akan diambil, dan 2 adalah menunjukkan kolom ke-2 dari range tersebut adalah data yang diambil.Atau dengan cara lain, yaitu menggunakan function wizard sebagai berikut.
1) Letakkan kursor pada sel C8.
2) Klik menu Formulas, kemudian pilih Logical.
3) Klik pada VLookup.
1) Letakkan kursor pada sel C8.
2) Klik menu Formulas, kemudian pilih Logical.
3) Klik pada VLookup.
4) Pada menu VLOOKUP Wizard pilih atau ketik sel C8
5) Klik tombol Browse pada Cell
Range , Blok A1 hingga B5 , judul kolom
tidak perlu dipilih.
tidak perlu dipilih.
6) Tambahkan tanda $ untuk Range,
sehingga menjadi $A$2:$B$5, tanda
$ ini untuk menjadikan sel absolut agar jika dicopy ke sel di bawahnya
tidak berubah referensinya.
7) Ketik 2 di mana akan mengembalikan ke angka 2 pada Col_index_num,
untuk lebih jelasnya bisa dilihat ilustrasi pada gambar berikut.
$ ini untuk menjadikan sel absolut agar jika dicopy ke sel di bawahnya
tidak berubah referensinya.
7) Ketik 2 di mana akan mengembalikan ke angka 2 pada Col_index_num,
untuk lebih jelasnya bisa dilihat ilustrasi pada gambar berikut.
8) Klik OK, kemudian copykan ke sel
di bawahnya. Hasil akhirnya seperti
pada gambar berikut.
pada gambar berikut.
Sebagai pedoman dalam pemakaian VLOOKUP ini adalah kunci pencarian harus berada di kolom paling kiri dari table_array dan kunci pencarian tersebut harus dalam keadaan sudah terurut. Pemakaian HLOOKUP sama dengan VLOOKUP, perbedaannya hanya dalam hal penyusunan datanya, yaitu jika VLOOKUP datanya disusun secara vertikal, sedangkan jika HLOOKUP datanya disusun secara horisontal.
Fungsi Logika dalam Microsoft Excel
digunakan untuk menguji suatu kondisi dengan menggunakan syarat tertentu dan
oleh Microsoft Excel akan dikatakan TRUE (Nilai 1) jika syaratnya
terpenuhi atau FALSE (Nilai 0) jika syaratnya tidak terpenuhi.
Syarat yang digunakan untuk menguji suatu kondisi bisa hanya satu syarat, salah satu syarat diantara beberapa syarat (Fungsi OR), dua syarat atau lebih wajib terpenuhi (Fungsi AND), atau bukan dengan syarat tertentu (Fungsi NOT).
Fungsi Logika yang kan kita gunakan adalah Fungsi IF, dengan bentuk umum sebagai berikut :
Syarat yang digunakan untuk menguji suatu kondisi bisa hanya satu syarat, salah satu syarat diantara beberapa syarat (Fungsi OR), dua syarat atau lebih wajib terpenuhi (Fungsi AND), atau bukan dengan syarat tertentu (Fungsi NOT).
Fungsi Logika yang kan kita gunakan adalah Fungsi IF, dengan bentuk umum sebagai berikut :
=IF(logical_test;value_if_true;value_if_false)
Langsung ke contoh kasus, Misalnya kita punya tampilan data sebagai berikut :
Anggap saja kita sedang melakukan perekrutan pegawai baru dengan data awal pemohon adalah sebagaimana yang tersebut pada kolom Nama, Pendidikan dan Jurusan.
Cara 1, Cara 2, Cara 3, Cara 4 dan Cara 5 adalah cara-cara yang kita gunakan untuk menguji kondisi pemohon tersebut dengan menggunakan syarat-syarat yang telah di tetapkan, dan akan menghasilkan nilai yang kita tentukan jika syaratnya terpenuhi ataupun tidak terpenuhi.
Cara 1 : Menggunakan Fungsi IF Tunggal
Berdasarkan kondisi data pemohon tersebut kita akan menerima pegawai dengan syarat hanya mereka yang memiliki pendidikan S1 yang akan diterima (Lulus), maka fungsi yang kita gunakan adalah :
=IF(C5="S1";"Lulus";"Gagal")
Artinya : Jika nilai Cell C5 memiliki nilai S1 maka akan dinyatakan Lulus dan jika nilai Cell C5 bukan S1 akan dinyatakan Gagal.
Cara 2 : Menggunakan Fungsi OR
Syarat yang kita gunakan adalah hanya kepada pemohon yang mempunyai pendidikan S1 dan D3 yang kan kita terima (Lulus) dan selain dari itu akan dinyatakan Gagal. fungsi yang kita gunakan :
=IF(OR(C5="S1";C5="D3");"Lulus";"Gagal")
Artinya : Jika Cell C5 mengandung nilai S1 dan D3 maka akan dinyatakan Lulus dan bila tidak dinyatakan Gagal.
Cara 3 : Menggunakan Fungsi AND
Syarat yang digunakan adalah pemohon yang kita terima adalah pemohon yang memiliki pendidikan S1 dengan jurusan Akuntansi, Fungsi yang kita gunakan :
=IF(AND(C5="S1";D5="Akuntansi");"Lulus";"Gagal")
Artinya : Jika Cell C5 memiliki nilai S1 dan Cell D5 meiliki nilai Akuntansi maka dinyatakan Lulus dan jika tidak dinyatakan Gagal.
Cara 4 : Menggunakan Fungsi NOT
Syaratnya adalah hanya pemohon yang meiliki jurusan Selain Akuntansi yang akan kita terima (Lulus). Fungsi yang kita gunakan :
=IF(NOT(D5="Akuntansi");"Lulus";"Gagal")
Artinya : Jika Cell D5 memiliki nilai Akuntansi maka akan dinyatakan Gagal, dan dinyatakan lulus jika Cell D5 bukan Akuntansi.
Cara 5 : Menggunakan Fungsi IF Bersarang
Syarat adalah jika pemohon memiliki pendidikan S1 akan langsung dinyatakan Lulus, jika berpendidikan D3 akan dipertimbangkan dan selain dari itu akan langsung dinyatakan Gagal.
=IF(C5="S1";"Lulus";IF(C5="D3";"Dipertimbangkan";"Gagal"))
Artinya : Jika Cell C5 memiliki nilai S1 maka akan dinyatakan gagal, Jika bukan S1 dan memiliki nilai D3 akan dinyatakan Dipertimbangkan dan jika bukan S1 atau D3 akan dinyatakan Gagal.
Berikut ini adalah referensi tambahan berkaitan dengan masalah fungsi logika (if) pada ms.excel :
Fungsi Logika ( IF ) Pada Excel
Dalam pelajaran TIK, Jika Nilai TIK kalian kurang dari 75 maka
harus remidi dan jika mendapatkan nilai 75, 76,77 ke atas berarti kalian lulus.
PERINTAH LOGIKA
“JIKA NILAI TIK < 75 MAKA REMIDI”
“JIKA NILAI TIK >= 75 MAKA LULUS”
Dalam Excel perintah logika dikenal dengan fungsi
logika IF, yaitu suatu fungsi yang digunakan untuk menyelesaikan suatu masalah
yang dihadapkan dengan suatu kondisi tertentu. Kondisi tersebut membentuk suatu
pernyataan yang baku dan menuntut jawaban "Benar" dan
"Salah".
Rumusnya,
Perlu diingat tentang operasi logika / operator pembanding,
digunakan sebagai syarat dari kondisi.
Untuk
setiap nilai benar dan salah yang diberikan, jika bertype Karakter, maka harus diapit tanda petik dua ( " " )
sedangkan yang bertype Numerik / Angka maka diketikkan biasa saja.
IF Tunggal
Fungsi logika IF yang hanya memiliki satu kondisi / syarat, sehingga dipastikan memiliki dua hasil yang akan ditampilkan yaitu hasil yang sesuai syarat (kondisi) atau hasil yang tidak sesuai syarat (kondisi) akibat dari satu kondisi / syarat tersebut, sehingga hanya membutuhkan satu IF (Tunggal)
IF Tunggal
Fungsi logika IF yang hanya memiliki satu kondisi / syarat, sehingga dipastikan memiliki dua hasil yang akan ditampilkan yaitu hasil yang sesuai syarat (kondisi) atau hasil yang tidak sesuai syarat (kondisi) akibat dari satu kondisi / syarat tersebut, sehingga hanya membutuhkan satu IF (Tunggal)
Rumus seperti di atas ....
Contoh :
Dalam pelajaran TIK, Jika Nilai TIK kalian kurang dari 75
maka harus remidi dan jika mendapatkan nilai 75, 76 ke atas berarti kalian
lulus.
Berarti, jika isi sel B3 (nilai) < 75
(kondisi) maka Remidi (Hasil sesuai kondisi) dan jika isi sel B3 (nilai) >=
75 (selain < 75) maka Lulus (Hasil tidak sesuai kondisi).
Jawab :
Rumus IF dapat juga dimasukkan melalui menu
dengan langkah – langkah sebagai berikut :
- Klik Formulas
- Klik Logical
- Klik / pilih IF
- Kotak Function Arguments aktif, isi kondisi (syarat) dan nilai (hasil) yang ditentukan.
- Klik OK
IF Majemuk/Bertingkat
Fungsi logika IF yang memiliki lebih dari satu
kondisi / syarat, sehingga dipastikan memiliki lebih dari dua hasil yang akan
ditampilkan yaitu hasil yang sesuai syarat (kondisi) pertama, kedua dan
seterusnya dan yang terakhir hasil yang tidak sesuai syarat (kondisi) semuanya,
akibat dari satu kondisi / syarat tersebut, sehingga membutuhkan lebih dari
satu IF (Majemuk).
Yang perlu diingat dalam IF majemuk adalah
Jumlah IF = Hasil – 1, tutup kurung yang terakhir sebanyak jumlah IF, susunan
IF yang terakhir seperti IF tunggal.
Rumus
Ditentukan nilai TIK,
< 50 adalah Gagal, >= 50 sampai < 75 adalah Remidi, >=
75 adalah Lulus
Artinya, nilai / hasil ada tiga yaitu, gagal, remidi dan lulus
(jumlah IF ada dua, tanda kurung tutup terakhir ada dua). Kondisi / syaratnya
cukup dua yaitu < 50 (hasilnya gagal) dan >= 75 (hasilnya lulus), selain
dua kondisi / syarat tersebut (tidak memenuhi syarat semuanya) hasilnya
ditempatkan pada nilai salah (remidi).
Menggabungkan Fungsi
Logika IF dan Fungsi String
Fungsi logika IF dimana pada bagian kondisi / syarat
digunakan juga fungsi string untuk mengambil beberapa teks yang
sesuai dengan ketentuan.
Misal,
Ketentuan
TUJUAN
diperoleh dari NOMOR PENGIRIMAN, jika :
MD
= Madiun,
SB
= Surabaya,
ML =
Malang
JENIS
PAKET diperoleh dari NOMOR PENGIRIMAN, jika :
K
= Kilat,
B
= Biasa
Rumus
yang digunakan adalah,
Tujuan
(sel B3)
Rumus yang digunakan adalah,
Jenis Paket (sel C3)
Hasil setelah rumus dimasukkan,
