Berbagi Ilmu

Assalamualaikum wr. wb...
Di part ke 4 saya akan membahas tentang bagaimana cara membuat rumus VLOOKUP dan HLOOKUP.

Untuk para pemula atau yang bagi yang belum memahami cara menggunakan VLOOKUP dan HLOOKUP mungkin terlihat sangat sulit. Tapi setelah kalian memahaminya, tidak sesulit yang kita bayangkan.
Sebelum kita membahas contoh soal VLOOKUP dan HLOOKUP, kita harus tau dulu apa itu VLOOKUP dan HLOOKUP.
VLOOKUP yaitu fungsi yang digunakan jika pembacaan tabel disusun secara vertikal, sedangkan HLOOKUP yaitu fungsi yang digunakan jika pembacaan tabel disusun secara horizontal.

Nah.. sekarang kita langsung saja ke contoh soalnya.

 Pada tabel diatas, kita melihat ada 3 tabel yang berbeda.
Tabel 1 untuk penginputan menggunakan rumus VLOOKUP. Mengapa menggunakan VLOOKUP? Karena jenis motor mengarah dari atas ke bawah.
Sedangkan tabel 2 untuk penginputan menggunakan rumus HLOOKUP karena jenis motor mengarah dari kiri ke kanan.

Keterangan soal :

• Harga beli per unit diambil dari : tabel 1 kolom ke 2.
• Harga jual per unit diambil dari :  tabel 1 kolom ke 3.
• Discount per unit diambil dari : tabel 2 baris ke 2 dikali tabel 1 kolom ke 3.
• Pajak per unit diambil dari : tabel ke 2 baris ke 1.
• Harga bayar diambil dari : Harga Jual per unit - Discount per unit.
• Total penjualan diambil dari  : Harga bayar per unit * Jumlah terjual.
• Laba bersih diambil dari : Harga bayar perunit - Harga beli per unit * Jumlah terjual.

Penyelesaian :
1. Harga beli per unit diambil dari tabel 1 kolom ke 2. Maka cara pengerjaan seperti dibawah ini.
Setelah mengetik rumusnya kemudian enter. Setelah di enter kemudian di drag sampai D17.
- C9 dari baris pertama dari kolom keterangan.
- Sudah tau belum mengapa ada $B$4:$D$6 ? Itu diambil dari tabel 1 dari B4:D6 kemudian tekan F4.
- 2 dari tabel 1 kolom ke 2.
- FALSE  jika nilai kunci sama.

 2. Harga jual per unit diambil dari :  tabel 1 kolom ke 3.
Pada soal nomor 2 hampir sama dengan nomor 1, yang berbeda hanyalah letak kolom pada tabel 1 berada di kolom ke 3.

3. Discount per unit diambil dari : tabel 2 baris ke 2 dikali tabel 1 kolom ke 3.

Untuk part ke 3 saya akan menjelaskan bagaimana cara penggunaan fungsi IF pada excel.
Soal Pertama.



Keterangan Soal :
1. Yudisium Nilai : Nilai Praktek dikali dua ditambah nilai teori dan hasilnya dibagi tiga.

2. Yudisium Huruf :

• Jika yudisium nilai lebih kecil 60 maka yudisium huruf adalah D.
• Jika yudisium nilai lebih kecil 70 maka yudisium huruf adalah C.
• Jika yudisium nilai lebih kecil 80 maka yudisium huruf adalah B.
• Jika yudisium nilai sama dengan 80 maka yudisium huruf adalah A.

3. Keterangan :

• Jika yudisium huruf A maka keterangan adalah Sangat Memuaskan
• Jika yudisium huruf B maka keterangan adalah Memuaskan
• Jika yudisium huruf C maka keterangan adalah Cukup Memuaskan
• Jika yudisium huruf D maka keterangan adalah Kurang Memuaskan

 Silahkan kerjakan !

Penyelesaian :
1. Soal pertama, yaitu mencari yudisium nilai sesuai soal di atas. Nilai praktek dikali dua ditambah nilai teori dan hasilnya dibagi tiga. Maka rumusnya =((D8*2)+C8)/3
Perhatikan rumus dibawah !
Setelah itu, drag kolom nilai dari E8 sampai E14. 
 

2. Soal kedua, yaitu mencari yudisium huruf.

• Jika yudisium nilai lebih kecil 60 maka yudisium huruf adalah D.
• Jika yudisium nilai lebih kecil 70 maka yudisium huruf adalah C.
• Jika yudisium nilai lebih kecil 80 maka yudisium huruf adalah B.
• Jika yudisium nilai sama dengan 80 maka yudisium huruf adalah A.

Cara menentukan rumusnya harus menggunakan IF.
=IF(E8<60,"D",IF(E8<70,"C",IF(E8<80,"B", IF(E8>=80,"A"))))
Perhatikan rumus dibawah!
Setelah itu, drag kolom yudisium huruf dari F8 sampai F14.
 

















3. Soal ketiga, yaitu mencari kolom keterangan.

• Jika yudisium huruf A maka keterangan adalah Sangat Memuaskan
• Jika yudisium huruf B maka keterangan adalah Memuaskan
• Jika yudisium huruf C maka keterangan adalah Cukup Memuaskan
• Jika yudisium huruf D maka keterangan adalah Kurang Memuaskan

 Cara menentukan rumusnya juga menggunakan IF.
Maka rumusnya, =IF(F8="A","SANGAT MEMUASKAN", IF(F8="B","MEMUASKAN", IF(F8="C", "CUKUP MEMUASKAN","KURANG MEMUASKAN")))

Setelah itu, drag kolom keterangan dari G8 sampai G14.






















Selanjutnya untuk soal kedua.

 Keterangan Soal:

1.  Isilah gaji pokok dengan : Jika jabatannya DIREKTUR maka gajinya 1.200.000, jika jabatannya STAF maka gajinya 800.000, jika jabatannya SEKRETARIS maka gajinya 700.000 dan jika jabatannya KARYAWAN maka gajinya 500.000.
2.  Isilah tunjangan keluarga dengan 15% dari gaji pokok jika status nikah dan tidak mendapat tunjangan jika status belum menikah.
3. Karyawan akan mendapatkan upah lembur jika lebih dari jam wajib kerja yaitu 160 jam. Dengan ketentuan harga lembur per jam adalah :
• Jika jabatannya Direktur maka upah lemburnya 10000/jam.
• Jika jabatannya Staf atau Sekretaris maka upah lemburnya 7000/jam.
• Jika jabatannya Karyawan maka upah lemburnya 50000/jam. 

Penyelesaian :
1. Soal Pertama. Isilah gaji pokok dengan : Jika jabatannya DIREKTUR maka gajinya 1.200.000, jika jabatannya STAF maka gajinya 800.000, jika jabatannya SEKRETARIS maka gajinya 700.000 dan jika jabatannya KARYAWAN maka gajinya 500.000.

Pada soal pertama menggunakan logika IF. Perhatikan rumus pada gambar dibawah!
Setelah menyelesaikan rumus pada baris pertama kolom gaji maka langsung saja di drag.

 2. Soal Kedua. Isilah tunjangan keluarga dengan 15% dari gaji pokok jika status nikah dan tidak mendapat tunjangan jika status belum menikah.

Pada soal kedua juga menggunakan logika IF. Perhatikan rumus pada gambar dibawah ini pada kolom tunjangan.

















3. Soal ketiga.
Karyawan akan mendapatkan upah lembur jika lebih dari jam wajib kerja yaitu 160 jam. Dengan ketentuan harga lembur per jam adalah :

• Jika jabatannya Direktur maka upah lemburnya 10000/jam.
• Jika jabatannya Staf atau Sekretaris maka upah lemburnya 7000/jam.
• Jika jabatannya Karyawan maka upah lemburnya 50000/jam. 

Pada soal ketiga di kolom upah lembur selain menggunakan logika IF juga menggunakan AND. Mengapa menggunakan AND juga? Karena menggunakan 2 kondisi dalam satu masalah. Dan rumusnya cukup panjang. Semoga dapat dipahami rumusnya.
=IF(AND(D5>160,C5="direktur"),((D5-160)*10000),IF(AND(D5>160,C5="staf"),((D5-160)*7000),IF(AND(D5>160,C5="sekretaris"),((D5-160)*7000),IF(AND(D5>160,C5="karyawan"),((D5-160)*5000),0))))



4. Untuk menghitung Total Gaji cukup tarik dari baris pertama kolom gaji sampai baris pertama kolom upah lembur.
Perhatikan tabel dibawah ini!


Sekian dulu sharing ilmu Excelnya ya. Next time kita sambung lagi. :)

Untuk part 2 saya akan memberi contoh excel yang agak sedikit rumit tapi masih dibilang masih sangat mudah..

Contoh soalnya seperti ini:
Keterangan soal :
1. Carilah total harga masing-masing buku dengan rumus Harga buku * jumlah !
2. Carilah total harga keseluruhan dari masing-masing buku !
3. Carilah rata-rata, maksimum, dan minimumnya !

Pada soal diatas saya sudah memberikan jawabannya pada warna orange.Tapi bagaimana rumus dari jawaban di atas?
Mari kita langsung saja bahas jawaban diatas!

1. Pada tahap pertama kita mencari total harga dari buku Ms. Excel.
 Caranya yaitu dengan mengketik rumus seperti contoh dibawah =SUM(C12*$E$6).
Yang di pertanyakan adalah mengapa di kolom total menggunakan simbol string($)?
 Alasannya yaitu karena letak harga dari buku Ms. Excel berada diluar tabel maka kita harus menggunakan String. Sehingga jawaban saat di drag merata ke bawah. Caranya yaitu dengan setelah kita mengklik harganya kemudian langsung menekan tombol F4. Jadi muncul lah string seperti rumus yang ada dibawah ini.

Setelah kita mendapatkan jawaban di baris pertama pada 01 juli 2009 dari rumus yang telah dibuat, maka langsung saja kita drag sampai tanggal 07 juli 2009.

Untuk mencari total harga buku APSI dan STATISTIK sama dengan cara mencari total harga buku MS. Excel.
Untuk total harga APSI rumusnya =SUM(E12*$E$7)
Untuk total harga STATISTIK rumusnya =SUM(G12*$E$8)

























2. Untuk mencari total keseluruhan Ms. Excel yaitu dengan cara seperti dibawah ini dengan rumus =SUM(D12:D18)
Total keseluruhan APSI =SUM(F12:F18)
Total keseluruhan STATISTIKA =SUM(H12:H18)
























3. Untuk mencari rata-rata, max dan min.
Rata-rata yaitu rata-rata dari seluruh total harga dari tanggal 1 juli sampai 7 juli.
Max yaitu mencari mana harga tertinggi dari tanggal 1 juli sampai 7 juli
Min yaitu mencari mana harga terendah dari tanggal 1 juli sampai 7 juli.

• Rata-Rata
• Rata-rata menggunakan AVERAGE

•  Maksimal
• Maksimal menggunakan rumus MAX

•  Minimal
• Minimal menggunakan rumus MIN. 

 Gampang dan simpel kan. Apabila kamu belum paham silahkan bertanya di kolom komentar dibawah..

Klik Menentukan Rumus Fungsi IF

Assalamualaikum Wr. Wb.

Microsoft Excel merupakan sebuah software yang sangat dibutuhkan dalam kegiatan pelajaran dan pekerjaan.
Pada dasarnya masih banyak para kaum muda yang masih bingung dan merasa sulit ketika belajar Ms, Excel.
Jadi disini saya akan mengajarkan anda bagaimana cara penulisan rumus pada excel sebagai pemula.

Di Part I saya akan mengajarkan anda beberapa rumus simple yaitu rumus operator hitung, diantaranya :

• + (Plus)               : Penjumlahan
• -  (Minus)            : Pengurangan
• *  (Multiply)        : Perkalian
• /   (Devide)          : Pembagian
• ^ (Exponentiate)  : Perpangkatan

2. Untuk penyelesaian pengurangan :