Rumus dan Fungsi HLOOKUP dan VLOOKUP dengan contoh

Fungsi HLOOKUP dan VLOOKUP

Fungsi HLOOKUP dan VLOOKUP digunakan untuk membaca suatu tabel secara Horizontal (HLOOKUP) atau secara Vertikal (VLOOKUP). Bentuk umum penulisan fungsi ini adalah :

=HLOOKUP (Lookup_value,Table_array,Row_index_num,…….)

=VLOOKUP (Lookup_value,Table_array,Col_index_num,…….)

Dari rumus diatas, dapat dilihat bahwa bedanya hanya pada nomor indeksnya saja, kalau kita pakai HLOOKUP, maka digunakan nomor indeks baris (Row_index_num), kalau VLOOKUP menggunakan Nomor index kolom (Col_index_num). Nomor indeks adalah angka untuk menyatakan posisi suatu kolom/baris dalam tabel yang dimulai dengan nomor 1 untuk kolom/baris pertama dalam range data tersebut, Contoh :

Rumus :

=VLOOKUP(D13,$C$4:$E$8,2,0)

Dan sel F15 dengan rumus =C13*VLOOKUP(D13,$C$4:$E$8,3), setelah itu lakukan Copy Rumus sampai data terakhir

D13 = Lookup_value / Kolom yang sama antara tabel utama dan tabel pembantu (Tabel Kode Barang)

$C$4:$E$8 = Table array/tabel pembantu

2 = Col_index_num / Setelah diberi rumus vlookup maka data terpisah berdasarkan kolom à untuk mengisi nama barang berarti ada di kolom ke 2

Kalau menggunakan VLOOKUP maka jangan lupa mengisi range lookupnya 0

Tips dan trik cara Menghitung Cicilan Beli Sepeda Motor dalam Exel

Tips Menghitung Cicilan Beli Sepeda Motor

Kali ini mari kita belajar bagaimana menghitung cicilan dan DP Total untuk beli sepeda motor..

Saya punya susunan excel cell seperti gambar di bawah ini :

Sisa Hutang B4 diisi formula =B2-B3

DP Total : =B8+B7

Menghitung cicilan dapat menggunakan rumus =-PMT(RATE,NPER,PV,[FV],[TYPE])

Saya berikan tanda minus (-) karena pmt akan menghasilkan hasil negatif,maka dari itu saya berikan minus supaya hasilnya positif.

Rate = bunga dalam periode

nper = periode dalam bulan

pv = sisa hutang

Rumus cicilannya adalah : =-PMT(B6/12,B5,B4)

Rate saya bagi dengan 12 untuk bunga per bulan atau per cicilan.

Hasilnya adalah sebagai berikut :

Nah mudah bukan untuk menghitung cicilan motor.. Berguna banget buat kawan-kawan yang hendak beli motor baru/bekas dengan kredit.. pakai rumus ini akan membantu menilai apakah cukup tinggi cicilannya atau kemungkinan bisa ditawar lagi.

Cara Ke 2 :

Petama kita buat simulasi perhitungan kredit kendaraan seperti gambar dibawah ini

MEMBUAT SIMULASI DAN TABEL KREDIT KENDARAAN MENGGUNAKAN RUMUS MS EXCEL

Pada artikel sebelumnya sudah dibahas Membuat Format Cicilan Pinjaman Uang Dengan Rumus Excel, belajar office kali ini kita akan membahas membuat format cicilan kendaraan. Nah sobat-sobat yang tentunya mengidamkan dan mempunyai impian ingin mempunyai kendaraan pribadi namun memiliki dana yang baru cukup untuk membayar down payment/DP terlebih dahulu, kemudian sisanya akan diangsur setiap bulannya.

Berikut ini belajar office akan memberikan contoh simulasi perhitungan angsuran kredit kendaraan menggunakan rumus excel dan cara membuatnya, supaya kita bisa tahu hitung-hitungannya dan sekaligus sarana untuk belajar mendalami rumus-rumus excel. Sumber hitungan ini saya ambil dari percobaan pada contohsimulasi kredit kendaraan di bank bca.

Langsung saja langkah-langkahnya sebagai berikut :

Petama kita buat simulasi perhitungan kredit kendaraan seperti gambar dibawah ini

Simulasi kredit kendaraan

Masukan data :

Harga OTR, uang muka, jangka waktu, suku bunga pertahun adalah untuk input data-datanya :

Bunga perbulan : untuk perhitungan ini saya gunakan suku bunga perbulan supaya lebih mudah. untuk suku perbulan ini merupakan pembagian dari suku bunga pertahun dibagi 12 bulan jadi rumusnya Pada cell C8 =C7/12
Sisa cicilan adalah pengurangan dari harga OTR dikurang uang muka. Rumus pada cell C11=C4-C5
Cilan tiap bulan merupakan sisa cicilan ditambah bunga cicilan dibagi jangka waktunya. Rumusnya pada cell C12 =(C11+(C11*C8*C6))/C6 , maka ketemu berapa cicilannya .

Pembayaran pertama

Namun pada pembayaran pertama DP akan ada tambahan biaya seperti asuransi, administrasi polis administrasi dan cicilan pertama yang jumlahnya lumayan besar.

Uang muka sama ambil data dari input data diatas rumusnya pada cell C15 ketikan=C5Ansuran pertama data diambil dari perhitungan yang sudah dibuat pada cell C16 ketikan=C12
Asuransi All Risk persentasenya tergantung dari jasa asuransinya Rumusnya merupakan perhitungan dari persentase dikali harga kendaraan OTR Rumusnya pada cell C17 ketikan=C4*D17
Administrasi polis dan administrasi lain inputnya menyesuaikan.
Pembayaran pertama kita jumlah semuanya, pada cell 20 ketikan =SUM(C15:C19)

Pada perthitungan ini saya buat lengkap dengan tabelnya angsuran perbulan yang sudah menyatu dengan rumus simulasi perhitungannya. Rumus yang digunakan prinsipnya sama dengan yang diatas.

Tabel kredit kendaraan bermotor

Tips dan cara menggerjakan,mengambil dan me-remove kata dari suatu kalimat pada MS.Exel

Tips dan cara menggerjakan,mengambil dan me-remove kata dari suatu kalimat pada MS.Exel seperti Contoh dibawah ini :

Muda2xan cukup jelas gambarnya, rumus2xnya adalah
1. Ambil 1 Kata Pertama
    =LEFT(A1;SEARCH(" ";A1;1)-1) atau
    =TRIM(LEFT(SUBSTITUTE(A1;" ";REPT(" ";100));100))
2. Ambil 2 Kata Pertama
    =TRIM(LEFT(SUBSTITUTE(A1;" ";REPT(" ";100));200)) atau
  =LEFT(A1;(FIND("|";SUBSTITUTE(A1;" ";"|";2)))-1)
3. Ambil 3 Kata Pertama
    =TRIM(LEFT(SUBSTITUTE(A1&" ";" ";REPT(" ";255);3);255))
4. Ambil 4 Kata Pertama
    =TRIM(LEFT(SUBSTITUTE(A1&" ";" ";REPT(" ";255);4);255))
5. Ambil 1 Kata Terakhir
    =TRIM(RIGHT(SUBSTITUTE(TRIM(A1);" ";REPT(" ";255));255))
6. Ambil 2 Kata Terakhir
    =TRIM(RIGHT(SUBSTITUTE(A1;" ";REPT(" ";60));120))
7. Ambil 3 Kata Terakhir
    =TRIM(RIGHT(SUBSTITUTE(TRIM(A1);" ";REPT(" ";60));180))
8. Hapus 1 Kata Pertama
    =RIGHT(A1;LEN(A1)-FIND(" ";A1))
9. Hapus 2 Kata Pertama
    =MID(A1;1+FIND("~";SUBSTITUTE(A1;" ";"~";2));255)
10. Hapus 3 Kata Pertama
      =MID(A1;1+FIND("~";SUBSTITUTE(A1;" ";"~";3));255)
11. Hapus Kata Terakhir
     =LEFT(TRIM(A1);FIND("~";SUBSTITUTE(A1;" ";"~";LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1);" ";""))))-1)

Turitorial Mengedal Rumus dasar MICROSOFT OFFICE EXCEL 2007

MENGOLAH LEMBAR KERJA

Menyisipkan atau Menambah Lembar Kerja Baru

Klik tab Home, klik Insert lalu klik pada pilihan Insert Sheet, maka sheet akan bertambah

Memberi atau Mengganti Nama Sheet

Klik kanan salah satu sheet yang akan diberi nama, klik pada pilihan Rename lalu ketikkan nama sesuai yang dikehendaki

Menyisipkan Baris atau Kolom

Letakkan kursor pada baris/kolom yang akan disisipkan
Klik tab Home, klik Insert
Klik pada pilihan Insert Sheet Row/Insert Sheet Columns atau Insert Cell jika ingin menyisipkan sel baru

Mengubah Lebar Kolom/Baris

Klik kolom/baris yang akan dilebarkan
Klik tab Home, klik bagian Format
Klik pada pilihan Column Width untuk merubah lebar kolom atau klik Row Height untuk merubah lebar baris
Atau klik pada pilihan AutoFit Column Width/AutoFit Row Height untuk merubah lebar kolom/baris secara otomatis

Menghapus Kolom/Baris

Sorot kolom yang akan dihapus
Klik tab Home, klik pada pilihan Delete

Lalu klik pada pilihan Delete Sheet Columns/Delete Sheet Rows

Membuat Catatan (Komentar) pada Sel

Klik kanan pada sel yang akan diberi catatan
Klik Insert Comment, lalu tuliskan catatan yang dikehendaki

Memberi Sel Pembanding

Sorot table yang akan diberi nama, lalu klik tab Formulas

Klik pada pilihan Define Name, beri nama table pada kotak isian Name lalu Ok

Rumus dan Fungsi Microsoft Exel

RUMUS DAN FUNGSI

Bekerja dengan Rumus dan Fungsi

Operator	Keterangan
= (sama dengan)	Digunakan paling depan sebelum melakukan perhitungan
^ (perpangkatan)	Simbol untuk melakukan perpangkatan
/ (pembagian)	Simbol untuk melakukan pembagian
* (perkalian)	Simbol untuk melakukan perkalian
+ (penjumlahan)	Simbol untuk melakukan penjumlahan
- (pengurangan)	Simbol untuk melakukan pengurangan
; (titik koma)	Simbol untuk pembatas antar rumus
, (koma)	Simbol untuk pembatas antar rumus
( (kurung buka)	Simbol awal dari sekelompok rumus
) (kurung tutup)	Simbol akhir dari sekelompok rumus

Fungsi Statistik

SUM	Menjumlahkan data dalam suatu range
AVERAGE	Menghitung nilai rata-rata dari suatu range
MAX/MIN	Menghasilkan nilai terbesar/terkecil dari suatu range
COUNT	Menghitung jumlah data dari suatu range

Fungsi Matematika

ABS	Menghasilkan nilai absolute
UNT	Membulatkan bilangan ke bilangan genap terdekat
ROUND	Membulatkan bilangan decimal yang ditentukan
EVEN	Membulatkan bilangan genap terdekat ke atas
ROUNDOWN	Membulatkan suatu bilangan ke bawah
ROUNDUP	Membulatkan suatu bilangan ke atas
SQRT	Menghasilkan akar kuadrat
COUNTA	Menghitung sel tak kosong pada suatu range
COUNTIF	Menghasilkan jumlah data dalam dalam suatu range menurut criteria
RANK	Mengurutkan posisi tingkatan dari suatu bilangan
MOD	Menghasilkan sisa pembagian

Fungsi String

Left (teks,n karakter)	Untuk mengambil huruf sebanyak n karakter dari kiri
Right (teks, posisi awal, n karakter)	Untuk mengambil huruf sebanyak n karakter dari kanan
Mid (teks, posisi awal, n karakter)	Mengambil huruf dari posisi tertentu
Lower (teks)	Mengubah huruf kecil menjadi huruf besar
Upper (teks)	Mengubah huruf besar menjadi huruf kecil

Fungsi Logika

Bentuk Umum Fungsi Logika

=IF (A,B,C)

A, Persyaratan yang akan diuji

B, Apabila kondisi uji benar

C, Apabila kondisi uji salah

Fungsi Tabel

Fungsi Vlookup, digunakan untuk pembacaan table yang disusun secara vertical

Bentuk Umum Fungsi Vlookup

=Vlookup(Lookup_value, table_array, col index_num, range_lookup)

Fungsi Hlookup, digunakan untuk pembacaan table yang disusun secara horizontal

Bentuk Umum Fungsi Hlookup

=Hlookup(Lookup_value, table_array, row index_num, range_lookup)