Menghitung biaya sewa kamar menggunakan fungsi vlookup di excel 2003

Berikut ini adalah contoh sederhana penggunaan fungsi vlookup untuk menghitung biaya sewa kamar. Dalam contoh ini saya membuat tabel kerja dan tabel referensi dibuat beda sheet.

Data baku yang saya peroleh dari teman adalah seperti di bawah ini

 1. Untuk memudahkan menyelesaikan masalah di atas, maka pada sheet1 dibuat tabel kerja sebagai berikut:
(Sel dengan background hijau adalah data input No urut, ID, dan nama pemesan)
(Sel dengan background coklat diisi dengan formula)

2. pada sheet2 buat tabel referensi seperti di bawah ini

3. Aktifkan sheet1. Buat formula berikut:
pada sel D4 ketik :
=VLOOKUP(B4,Sheet2!$A$4:$D$12,2,0)
pada sel E4 ketik:
=VLOOKUP(B4,Sheet2!$A$4:$D$12,3,0)
pada sel F5 ketik:
=VLOOKUP(B4,Sheet2!$A$4:$D$12,4,0)
pada sel H5 ketik:
=F4-G4

Hasilnya seperti gambar di bawah ini

Fungsi if , date, if(fungsi text) pada Excel 2003

Berikut ini kita akan mencoba menggunakan fungsi if ,fungsi date (tanggal) dan fungsi text (left), untuk menghitung biaya sewa cd. (rental cd).

Dalam contoh ini dibuat referensi tentang fungsi Fungsi if , date, text excel 2003

a. Biaya CD
untuk keping cd /vcd (film atau program) , 2500/hari
untuk keping dvd (film atau program) , 5000/hari

b. Biaya denda keterlambatan pengembalian CD
untuk keping cd /vcd (film atau program) , 1000/hari
untuk keping dvd (film atau program) , 2000/hari

c. Kode CD
CF cd film
CP cd program
DF dvd film
DP dvd program

d. limit sewa (batas sewa cd)
jika cd yang dipinjam  4 atau lebih maka limit 3 hari
jika cd yang dipinjam  3 maka limit 2 hari
jika cd yang dipinjam  1 atau 2 maka limit 1 hari

Untuk lebih jelasnya bisa buat tabel berikut:

1. Tabel data

kolom d,g,h,i,j ,k akan diisi formula
kolom e dan f dalam format date (english) anda bisa mengubah ke dalam format tanggal indonesia
kolom g diformat ke general atau number

2. Formula
Ketik formula berikut
a. sel D4
=IF(C4>3,3,IF(C4>2,2,1))
b. sel G4
=F4-E4
catatan: sel G4 d format ke dalam format number atau general
blok sel G4 sampai G7 pilih format - cell - number - general

c. sel H4
=IF(G4>D4,"denda","tidak")

d. sel I4
=IF(LEFT(B4,1)="C",C4*2500,C4*5000)

e. sel J4
=IF(LEFT(B4,1)="C",(G4-D4)*1000*C4,(G4-D4)*2000*C4)
f. sel K4
=I4+J4

Hasilnya seperti di bawah ini

Fungsi IF (AND) Pada Excel 2003

Penggabungan fungsi IF (AND) excel 2003 digunakan jika menginginkan minimal 2 syarat pernyataan atau lebih untuk logika bernilai benar. Sebagai contoh menggunakan fungsi IF(AND) di excel 2003 untuk menghitung tunjangan hari raya pegawai berdasarkan jenis kelamin dan usia, kedua sayarat ini (jenis kelamin dan usia) mempengaruhi besar tunjangan THR yang akan diterima.

Sebagai referensi:
Jika Jenis kelamin laki-laki dan usia >=40, maka THR = 10% dari gaji
Jika Jenis kelamin laki-laki dan usia >=32, maka THR = 15% dari gaji
Selain di atas THR=2% dari gaji

Untuk membuat formulanya, bisa menggunakan fungsi IF(AND)

1. Buat tabel seperti di bawah ini

2. Masukkan formula
a. Sel E4
=IF(AND(B4="L",C4>=40),D4*10%,IF(AND(B4="W",C4>=35),D4*15%,D4*2%))
b. Sel F4
=D4+E4

Hasilnya akan terlihat seperti di bawah ini : 

Fungsi IF (OR) Pada Excel 2003

Fungsi IF (OR) di  microsoft excel 2003  digunakan jika kita menginginkan salah satu dari beberapa syarat terpenuhi agar logika bernilai benar. Dalam contoh ini kita mencoba menggunakan fungsi IF (OR) excel 2003 untuk menghitung discount biaya tiket kereta api.

Contoh:
Dalam rangka hari ibu nasional, maka tiket kereta api akan memberikan diskon kepada penumpang
a. Jika jenis kelamin wanita atau anak-anak umur <=10 tahun, diskon tiket 40%
b. Selain di atas diskon 10%.

Untuk membuat formulanya di excel 2003, lakukan prosedur berikut:

1. Buat tabel seperti di bawah ini :

2. Ketik formula
a. Di sel F4
=IF(OR(C4="W",D4<=10),E4*0.4,E4*0.1)
b. Di sel G4
=E4-F4
Copy formula hingga baris 9

Hasilnya seperti di bawah ini :

Fungsi IF Nested (IF Bersarang) di excel 2003

Berikut ini adalah contoh penggunaan fungsi IF nested excel 2003 yang bisa juga disebut IF bersarang atau IF bercabang.

Dalam contoh kita akan mencoba merumuskan pernyataan berikut:

Jika PERSONAL maka nilainya 3500
Jika PAKET 1 maka nilainya 9000
Jika PAKET 2 maka nilainya 13000

Untuk mudahnya buat tabel seperti di bawah ini:

Di kolom C akan diisi rumus atau formula yang menggunakan fungsi IF
Di sel C3 ketik formula berikut:
=IF(B3="Personal",3500,IF(B3="Paket 1",9000,13000))
Copy formula hingga baris ke sepuluh.
Hasilnya seperti di bawah ini :

Fungsi Floor Pengganti Fungsi IF Untuk Menghitung Nilai Interval Konstan

Dalam microsoft excel 2003,jika menggunakan fungsi IF kita bisa menghtung sebuah penyataan logika namun jika menggunakan if nested (IF bercabang) hanya dibatasi sampai 8 percabangan. Salah satu alternatif untuk menggantikan fungsi IF adalah fungsi Floor excel ,proses pengulangannya tidak terbatas.

Sebagai contoh:
Jika angka 1 sampai 50 maka bobotnya 1
Jika angka 51 sampai 100 maka bobotnya 2
Jika angka 101 sampai 150 maka bobotnya 3
dan seterusnya

Dari kasus di atas terlihat interval angka yang akan dijadikan pernyataan adalah 50, dengan anggapan interval tersebut bersifat konstan.
Berdasarkan informasi dari help excel 2003
Sintax :
FLOOR(number,significance)
Number    is the numeric value you want to round.
Significance    is the multiple to which you want to round.


Agar lebih mudahnya bisa lihat contoh penggunaan fungsi floor di bawah ini,

1. Buat tabel seperti berikut:
Kolom A berisi angka acak , kemudian kolom B (Bobot) akan diisi dengan formula yang berisi fungsi FLOOR

 

2. Di sel B3 ketik formula
=FLOOR((A3+49)/50,1)

Hasilnya akan tampak seperti di bawah ini :

Untuk kasus lain yang lebih kompleks, jika intervalnya tidak konstan bisa menggabungkan fungsi IF dan FLOOR .

Cara menggunakan fungsi IF VLOOKUP di excel 2003

Salahsatu fungsi yang tersedia di excel adalah fungsi logika IF, fungsi IF dapat digabungkan dengan berbagai fungsi-fungsi excel lainnya. Dalam contoh ini akan dibahas tentang cara menggabungkan fungsi IF dan VLOOKUP di excel 2003. 

 

Postingan ini sebagai jawaban buat teman yang pernah menanyakan tentang cara menggabungkan fungsi IF VLOOKUP, mudah-mudahan bisa sedikit memberikan gambaran tentanng penggunaan fungsi if di excel.

Dalam kasus ini saya menganggap di tabel referensi ada 2 jenis varibel yaitu variable yang relatif tetap dan variable yang berubah bergantung pada tahun yang berlaku.

Agar lebih mudahnya bisa buat 2 buah tabel seperti di bawah ini :

1. Buat tabel kerja dan tabel referensi berikut:
Kosongkan sel D4 dan sel E4 , karena akan diisi dengan formula yang berisi fungsi IF dan VLOOKUP

 

 

2. Selanjutnya di sel D4 ketik formula :
=VLOOKUP(C4,$G$4:$J$7,2)
Formula pada D4 hanyalah formula yang menggunakan fungsi VLOOKUP saja, hanya digunakan sebagai bahan pembanding terhadap formula IF+VLOOKUP.  Formula pada sel D4 tidak berkaitan dengan formula pada sel E4
Catatan:
Tanda $ pada range $G$4:$J$7 menandakan alamat / range absolut, untuk mengatifkannya bisa gunakan tombol F4 . Jika kesulitan ketik saja formula $-nya secara manual.

Di sel E4 ketik formula
=IF(B4=2010,(VLOOKUP(C4,$G$4:$J$7,3,0)),(VLOOKUP(C4,$G$4:$J$7,4,0)))

Hasilnya akan terlihat seperti di bawah ini: