Pengoperasian "HLOOKUP" dan "VLOOKUP"

FUNGSI HLOOKUP dan VLOOKUP

Fungsi Vlookup dan Hlookup adalah untuk pembacaan tabel dimana dua atau lebih tabel mempunyai data yang saling dihubungkan. Jika tabel disusun secara Vertikal kita menggunakan rumus fungsi Vlookup sedangkan jika tabel disusun secara Horizontal maka kita gunakan rumus fungsi Hlookup. 
1. VLOOKUP

Ada contoh kriteria penilaian. Untuk menggunakan fungsi VLOOKUP, maka 

harus dirubah dahulu menjadi tabel dan tabel harus dibuat memanjang ke bawah (vertikal). Untuk pengisian ANGKA pada tabel, hanya menggunakan nilai terendahnya. Jadinya akan seperti ini:

Setelah sudah menjadi tabel seperti ini, tinggal melakukan fungsi VLOOKUP seperti ini:

Menggunakan rumus: =VLOOKUP(B4; lalu mendrag semua tabel yang sudah disiapkan tadi((JANGAN LUPA DIKUNCI MENGGUNAKAN F4));2((Kenapa 2? Karena Huruf pada tabel itu tabel ke 2), jadinya seperti ini:

Sehingga rumusnya adalah: =VLOOKUP(B4;$K$5:$M$14;2)

Setelah dimasukkan rumus, nilai akan keluar dan drag ke bawah:

HASILNYA SEPERTI INI ^^

Menggunakan rumus: =VLOOKUP(B4; lalu mendrag semua tabel yang sudah disiapkan tadi((JANGAN LUPA DIKUNCI MENGGUNAKAN F4));3((Kenapa 3? Karena Bobot pada tabel itu tabel ke 3).

Untuk mengisi Bobot, juga sama caranya tetapi rumusnya seperti ini:

=VLOOKUP(B4;$K$5:$M$14;3)

HASILNYA SEPERTI INI ^^

2. HLOOKUP

Sebenarnya unuk HLOOKUP, cara pengerjaannya sama seperti VLOOKUP. Hanya saja tabel harus di transpose terlebih dahulu.

Dengan kriteria yang sama: 

Untuk tabel yang sudah kita buat tadi masih berbentuk horizontal, maka harus di transpose dahulu menjadi vertikal:

SETELAH DI TRANSPOSE, MAKA TABEL AKAN BERUBAH SEPERTI INI:

((Untuk cara mentraspose klik kanan pada tabel yang masih berbentuk horizontal kemudian pilih "paste special", lalu pilih "transpose" kemudian klik OK"))

Setelah itu, cara untuk menghitung Huruf dan Bobot pun sama hanya saja sekarang tabel bukan di tarik ke bawah, tetapi ke kanan.

Masukkan rumus: =HLOOKUP(B4;$J$5:$S$7;2)

Cara menghitungnya pun sama seperti fungsi HLOOKUP, dan jika kita lihat, hasilnya pun juga sama. 

FUNGSI LEFT, MIDDLE, RIGHT

• Rumus LEFT berfungsi untuk mengambil beberapa karakter text dari data di Microsoft Excel yang dimulai dari kiri.
• Rumus MID berfungsi untuk mengambil beberapa karakter text dari data di Microsoft Excel yang dimulai dari tengah.
• Rumus RIGHT berfungsi untuk mengambil beberapa karakter text dari data di Microsoft Excel yang dimulai dari kanan

Contoh penggunaannya adalah:

Disini ada contoh kata yaitu MALANG. Untuk menggunakan fungsi ini, kita harus menghitung per karakter(huruf). 

• Untuk LEFT : Menggunakan rumus =LEFT(A6;4). Kenapa harus 4? Karena karakter yang akan diambil adalah 4 dari kiri.

• Untuk MIDDLE : Menggunakan rumus =MID(A6;2;3). Kenapa 2 dan 3? Karena karakter yang akan diambil adalah 2 dari kiri dan 3 dari kanan.

• Untuk RIGHT : Menggunakan rumus =RIGHT(A6;4). Kenapa 4? Karena karakter yang  akan diambil adalah 4 dari kanan.

Jadi, untuk kata MALANG, jika menggunakan fungsi ini, maka penghitungan karakternya adalah: MALA, ALA, LANG.

CONTOH STUDI KASUS PENGGUNAAN FUNGSI HLOOKUP DAN VLOOKUP

Ada contoh penyelesaian studi kasus, dan ini kriterianya:

Dan kita harus menyelesaikannya, dengan harus mencari hasil-hasil yang ada pada kolom-kolom yang sudah disediakan:

BAGAIMANA CARANYA?

1. Menghitung gaji pokok, tunjangan, dan trasnport adalah dengan menggunakan fungsi VLOOKUP. Caranya dengan:

Untuk menghitung besarnya gaji pokok, tunjangan, dan transport menggunakan rumus: 

• GAJI POKOK=VLOOKUP(B12;$A$4:$D$6;2)
• TUNJANGAN=VLOOKUP(B12;$A$4:$D$6;3)
• TRANSPORT=VLOOKUP(B12;$A$4:$D$6;4)

((JANGAN LUPA UNTUK MENGUNCI SETELAH MENDRAG TABEL BERBENTUK VERTIKAL DENGAN F4))

2. Menghitung Total Gaji

Untuk menghitung total gaji caranya sangat mudah yaitu dengan menggunakan rumus, =SUM.

Masukkan rumus: =SUM(D12:F12) dan tinggal drag ke bawah untuk total gaji selanjutnya.

3. Menghitung Besarnya Pajak

Dengan cara menggunakan fungsi VLOOKUP. Jika menggunakan fungsi VLOOKUP, maka hasil pajak akan berbentuk desimal, maka langsung dikalikan dengan total gaji. Masukkan rumus: =HLOOKUP(B12;$H$4:$K$5;2)*G12 ((jangan lupa dikunci menggunakan F4))

Maka, hasilnya akan seperti ini.

4. Menghitung Gaji Bersih

Caraanya snagat mudah hanya melakukan pengurangan dari total gaji-pajak. 

Masukkan rumus: =G12-H12

Setelah semua selesai, maka tampilannya akan seperti ini:

MUDAH BUKAN? SELAMAT MENCOBA!!

Fungsi IF, TEXT, OR, AND, IF BERTINGKAT PADA EXCEL

Bagaimana cara mengoperasikan fungsi-fungsi pada Microsoft Excel ?
Fungsi IF atau Rumus IF merupakan salah satu fungsi Excel dalam kategori atau kelompok logical yang sering digunakan untuk melakukan uji logika tertentu pada rumus microsoft excel.

Rumus Excel IF akan menghasilkan sebuah nilai tertentu jika kondisi yang kita tentukan untuk di evaluasi terpenuhi (TRUE) dan akan menghasilkan nilai lainnya apabila uji logika atau kondisi itu tidak terpenuhi (FALSE).

Dengan kata lain fungsi IF excel ini akan melakukan evaluasi terhadap uji logika tertentu kemudian menghasilkan nilai TRUE atau FALSE. Nilai TRUE adalah nilai dimana kondisi tersebut terpenuhi dan nilai FALSE adalah nilai untuk kondisi yang tidak terpenuhi.


1. FUNGSI IF

Untuk fungsi ini, dimasukkan rumus ini: =IF(D4>=60;"Lulus";"Tidak lulus")
Maka, untuk nilai yang ada di bawah 60, maka dia otomatis TIDAK LULUS, dan jika nilai ada di atas 60, maka dia otomatis LULUS.

2. FUNGSI TEXT

Untuk fungsi TEXT, dimasukkan rumus ini: =IF(C5="HUJAN";"BANJIR";"KEKERINGAN")

Maka, jika Hujan maka akan terjadi Banjir. Tetapi jika Kemarau akan terjadi Kekeringan. 

3. FUNGSI OR

Untuk fungsi OR, dimasukkan rumus: =IF(OR(G5="SMU/SMK";G5="D1");"Tes Tahap 1";"Gagal")

Agar masuk tes tahap 1, maka pendidikan yang harus menjadi persyaratan adalah SMU/SMK atau D1 saja. Jika pendidikan selain SMK/SMU atau D1, maka dia berstatus GAGAL.

4. FUNGSI AND

Untuk fungsi AND, dimasukkan rumus: =IF(AND(K5>=$N$18;L5<=$O$18);"Tes Tahap 1";"Gagal")

Dalam fungsi ini, dibutuhkan tabel pembantu untuk mempermudah. Persyaratan awalnya agar dapat masuk ke Tes Tahap 1 adalah: Masa Kerja MINIMAL 5 tahun, dan Usia Maksimal 30 tahun.

Jika salah satu nama masa kerjanya kurang dari 5 tahun, dan usianya melebihi 30 tahun, maka dia dinyatakan GAGAL dan tidak bisa masuk di TES TAHAP 1.

5. FUNGSI IF BERTINGKAT 

Fungsi IF BERTINGKAT, juga dibutuhkan tabel pembantu untuk mempermudah. Syarat awalnya adalah: Jika nilai <60, maka dia mendapatkan E. Jika nilai <75, maka dia mendapatkan D. Jika nilai <85, maka dia mendapatkan C. Jika nilai <95, maka dia mendapatkan B. Selain itu, maka dia mendapatkan A.

Dalam contohnya, dimasukkan rumus:

=IF(C5<60;"E";IF(C5<75;"D";IF(C5<85;"C";IF(C5<95;"B";"A"))))

Jika ada salah satu nama yang mendapatkan nilai 87, maka secara otomatis dia akan mendapatkan B. 

5. STUDI KASUS 1

Pada studi kasus ini, kita dipertemukan dengan cara menghitung potongan dan totalnya. Diperlukan tabel bantuan untuk harga per item. Di contoh tersebut, harga per item adalah 5000. 

Untuk menghitung potongan, bisa menggunakan rumus: =IF(B5<20;"5%";IF(B5<50;"10%";IF(B5<100;"20%"))) 

Dengan syarat awalnya: jika membeli barang <20 mendapatkan diskon 5%. Jika membeli barang <50 mendapatkan diskon 10%. Jika membeli barang <100 mendapatkan diskon 20%.

Untuk menghitung Total Sebelum Diskon, menggunakan rumus:

Karena harga per item sama, maka untuk perkaliannya harus dikunci menggunakan F4.

Untuk menghitung Total Setelah Diskon, menggunakan rumus:

Jika dituliskan manual =Total Sebelum Diskon -(Potongan*Total Sebelum Diskon) lalu klik enter.

CONTOH STUDI KASUS 2

Bagaimana Penyelesaiannya?

Buat kolom-kolom yang diperlukan untuk menyelesaikan permasalahan ini. 

1. Menghitung NILAI AKHIR :

Dimasukkan rumus: =(D5*10%)+(E5*20%)+(F5*30%)+(G5*40%)

Jika ditulis manual= (Nilai tugas*10%)+(Nilai Quiz*20%)+(Nilai UTS*30%)+(Nilai UAS*40%) lalu klik enter.

2. Menghitung Predikat Nilai

Dimasukkan rumus: =IF(H5<50;"E";IF(H5<60;"D";IF(H5<65;"C";IF(H5<70;"C+";IF(H5<80;"B";IF(H5<85;"B+";"A")))))) lalu klik enter.

Predikat nilai, akan menyesuaikan dari jumlah nilai akhir. 

3. Menuliskan Keterangan

Untuk menentukan LULUS/TIDAK LULUS, harus memenuhi syarat awal: Kehadiran minimal 14 dan nilai akhir>=60. Jika kehadiran kurang dari 14 dan nilai akhir <=60, maka secara otomatis dia TIDAK LULUS.

Dimasukkan rumus: =IF(AND(H5>=$M$14;C5>=$M$15);"LULUS";"TIDAK LULUS")

Dan jangan lupa dikunci menggunakan F4.

SEMOGA MEMBANTU!

Cara Membuat Nota Pembelian di Microsoft Excel

BAGAIMANA CARA MEMBUAT NOTA PEMBELIAN DI MICROSOFT EXCEL?

1. Buat dahulu kolom-kolom yang diperlukan untuk membuat nota. Banyak sedikitnya, tergantung keperluan kalian.

2. Untuk menuliskan tanggal nota, menggunakan rumus =NOW() lalu klik enter, maka akan muncul seperti ini :

3. Setelah itu, isi nama-nama barang, jumlah, dan harga satuan. Dan jangan lupa memberi titik di nominal harganya dengan mengklik "NUMBER", lalu centang "USE 1000 SEPARATOR", begini contohnya:

4. Jangan lupa menambahkan satuan, dengan cara klik "CURRENCY", nanti hasilnya akan seperti ini :

5. Setelah itu, buat tabel pembantu sebagai bantuan untuk menghitung total harga. Tabel pembantu ini mempermudah dalam perhitungan total harga. Mengapa menggunakan tabel pembantu? Karena jika tidak menggunakan tabel pembantu, total harga tidak akan muncul ketika mengalikan harga satuan dengan jumlahnya (karena di dalam "JUMLAH" sudah tersisipkan kata". Seperti ini :

Ketika ini di enter, maka hasilnya akan memunculkan "VALUE!", seperti ini:

6. Maka, untuk solusinya harus membuat tabel pembantu, seperti ini:

7. Setelah tabel pembantu sudah siap, maka penghitungan total harga sudah bisa dilakukan dengan cara mengalikan jumlah dan harga satuan yang ada di tabel pembantu. Contohnya seperti ini:

Kemudian, enter dan drag ke bawah untuk hasil selanjutnya:

8. Kalau sudah selesai seperti ini, maka tabel pembantu harus di "HIDE" agar tidak terlihat di sheet. Caranya adalah; tabel pembantu ini terletak di I dan J, maka klik "I", kemudian klik kanan, lalu klik "HIDE", seperti ini :

Lakukan hal yang sama pada kolom "J", setelah keduanya sudah di hide maka tidak akan muncul di sheet, seperti ini:

Tabel pembantu sudah berhasil di hide dan tidak muncul di sheet lagi.

9. Tambahkan total harga, untuk menghitung keseluruhan harga:

10. Menghitung total harga menggunakan rumus =SUM(.....), seperti ini:

 

Kemudian enter, maka hasil total harga akan muncul:

11. Setelah tabel sudah siap, jangan lupa untuk mendesign tabel agar terlihat bagus:

TARAAA~~~

Nota Pembelian sudah jadi. Jadi itu tadi adalah cara untuk membuat nota pembelian di microsoft excel, semoga bermanfaat <3

MICROSOFT EXCEL

Macam-Macam Cells Dalam Microsoft Excel

1. .      Cell bebas

Adalah alamat Cell yang tidak tetap dan akan berubah secara otomatis bila didrag ke cell lain. Contohnya adalah ː

Untuk menghitung jumlah medali negara China, menggunakan rumus =SUM(E8 ː G8) lalu klik enter. Untuk negara yang dibawahnya, cukup drag ke bawah dan hasilnya otomatis mengikuti rumus yang di atas. Nanti, akan menjadi seperti ini ː

2.       Cell semi absolute

Alamat cell yang salah satu cellnya bernilai tetap/absolute/tidak berubah. Maka jika di drag(diseret) yang berubah hanya cell yang relatif,sedangkan yang sudah di kunci/diabsolutekan tidak berubah. Cell yang sudah diabsolutekan ditandai dengan adanya lambang $. Contohnya adalah ː

Untuk menghitung pertamax ketik rumus =A5*D3 lalu klik f4 2 kali kemudian klik enter. Untuk hasil selanjutnya, drag ke bawah maka hasilnya akan mengikuti.

 3.  Cell Absolute

Alamat cell yang semua cellnya bernilai tetap/absolute/tidak berubah jadi,jika di drag(diseret) nilainya akan tetap seperti hasil pertama. Contohnya adalah ː

                                                                                    

Untuk perhitungan pertamax, maka ketik rumus =A10*D9 lalu klik f4 kemudian klik enter. Untuk yang lainnya, drag ke bawah dan hasilnya akan mengikuti.

Memahami perhitungan excel sederhana yaitu Penjumlahan(+), Pengurangan(-), Perkalian(*), Pembagian ( ̸ ), dan Perpangkatan(^)

•   Penjumlahan

Untuk perhitungan penjumlahan, disini kita butuh salah satu cell yang harus dikunci. Dalam contoh ini, ketik rumus =A6+B4 lalu klik f4 dan enter. Untuk hasil selanjutnya, drag ke bawah dan hasilnya akan mengikuti.

• .       Pengurangan

Untuk penghitungan pengurangan disini kita butuh salah satu cell yang harus dikunci. Dalam contoh ini, ketik rumus =A4-B1 lalu klik f4 kemudian klik enter. Untuk hasil selanjutnya, drag ke bawah dan hasilnya akan mengikuti.

• .       Perkalian

Untuk penghitungan perkalian disini kita butuh salah satu cell yang harus dikunci. Dalam contoh ini, ketik rumus =A6*C4 lalu klik f4 kemudian klik enter. Untuk hasil selanjutnya, drag ke bawah dan hasilnya akan mengikuti.

• .       Pembagian

Untuk penghitungan pembagian perkalian disini kita butuh salah satu cell yang harus dikunci. Dalam contoh ini, ketik rumus =A6  ̸  E4 lalu klik f4 kemudian klik enter. Untuk hasil selanjutnya, drag ke bawah dan hasilnya akan mengikuti.

• .       Perpangkatan

Untuk perhitungan perpangkatan disini kita butuh salah satu cell yang harus dikunci. Dalam contoh ini, ketik rumus =A7^G4 lalu klik f4 kemudian klik enter. Untuk hasil selanjutnya, drag ke bawah dan hasilnya akan mengikuti.