Selama ini untuk mengisi dengan fungsi logika digunakan fungsi if, fungsi if tersebut cukup berguna jika pilihan yang tersedia relatif sedikit, namun fungsi if akan merepotkan jika pilihan yang ada sangat banyak, akibatnya formula anda mengguna if akan sangat panjang,
nah kelemahan tersebut bisa diantisipasi dengan fungsi referensi cell yaitu vlookup. Untuk memahami fungsi vlookup ada baiknya anda perhatikan lembar kerja dibawah ini :
Untuk mengerjakan lembar kerja diatas buatlah tiga buah tabel referensi seperti tampak pada gambar berikut :
Tabel Harga
Tabel Merek
Tabel Tipe Mobil
Berikan nama untuk ketiga tabel tersebut dengan insert name define, tabel pertama diberi nama merk, harga dan tipe.
Tugas anda harus mengisi kolom merek, tipe, tahun keluar, harga dasar, target harga jual, under/upper estimate, dan grafik.
Saatnya bekerja .
Mengisi Merek Mobil
Gunakan formula berikut :
=VLOOKUP(LEFT(C6;1);merek;2;FALSE)
Formula diatas mengambil satu karakter sebelah dari kolom kode mobil dengan fungsi LEFT yang merupakan bagian dari fungsi text, dan membandingkannya dengan tabel merek. Pada tabel merek kolom merek berada pada urutan ke-2, karena kode nya tidak di sortir / diurut maka pada penutup vlookup up diberikan kondisi FALSE.
Mengisi Tipe
Gunakan Formula berikut untuk mengisi tipe mobil :
=VLOOKUP(MID(C6;3;3);tipe;2;FALSE)
Formula diatas membandingkan 3 karakter ditengah-tengah dengan fungsi MID, dan membandingkannya dengan tabel tipe, dimana kolom tipe berada pada kolom ke=2, dan diberikan nilai FALSE karena tabel tipe kode tidak di sortir / diurut.
Mengisi Tahun Keluar
Untuk mengisi tahun keluar gunakan formula :
=IF(MID(C6;7;2)=”07″;”2007″;IF(MID(C6;7;2)=”08″;”2008″;IF(MID(C6;7;2)=”09″;”2009″;”2010″)))
Formula diatas, menggabungkan fungsi IF dan fungsi MID dimana fungsi MID hanya untuk mengambil dua karakter dari kode mobil.
Mengisi Harga Dasar
Untuk mengisi harga pokok kendaraan gunakan formula :
=VLOOKUP(CONCATENATE(LEFT(C6;1);MID(C6;3;3));harga;2;FALSE)
Fungsi diatas untuk mencocokan kode kode harga dengan tabel harga, dimana kode harga menggabungkan karakter ke-1 dari kode mobil dan karakter ke 3 dan 3 karekter selanjutnya dengan MID menggunakan fungsi text CONCATENATE, dan membandingkan dengan tabel harga yang dan mengambil nilai kolom harga di kolom kedua, karena kode harga tidak disortir maka gunakan nilai FALSE.
Mengisi Target Harga Jual
Gunakan Formula berikut :
=G6+(G6*VLOOKUP(LEFT(C6;1);merek;3;FALSE))
Target harga jual merupakan laba yang diinginkan dikali dengan harga dasar/pokok, besarnya laba yang diinginkan ada pada tabel merek, untuk itu diambil setiap laba masing-masing merek kemudian dikali dengan harga dasar dan hasilnya ditambahkan dengan harga dasar.
Misal untuk honda maka formulanya : 250.000.000+(250.000.000*10%), dari rumusan diatas maka jadilah formulanya seperti tampak pada formula menghitung target harga jual.
Mengisi Under / Upper Estimate
Gunakan Formula berikut :
=((I6-H6)/I6)*100
Under / Upper Estimate merupakan selisih kurang atau selisih lebih antara harga jual yang sesungguhnya dengan target harga jual, jika nilainya minus maka dibawah target dan jika plus sebaliknya
Mengisi Grafik
Gunakan Formula Berikut :
=IF(J12<0;REPT("n";-ROUND(J12*10;0));REPT("n";ROUND(J12*10;0)))Formula diatas mengecek jika nilainya minus (<0) maka membuat huruf "n" diulang-ulang dengan fungsi REPT sebanyak kolom J (dibulatkan dengan ROUND) dikali 10, namun sebelum fungsi REPT diberi tanda - (minus), dan jika positif (>0) maka sama dengan penjelasan diatas namun fungsi REPT nya tanpa tanda minus di depannya.
MERUBAH MENJADI GRAFIK
Untuk merubah warna karakter di kolom K dengan warna merah jika kolom J nilainya negatif dan warnanya kuningan jika nilainya Positif, maka langkah-langkahnya :
1. Ubah jenis huruf menjadi wingding dan warna menjadi kuning.
2. Kita ubah yang nilainya negatif menjadi berwarna merah, tempatkan pada Cell K6 klik Conditional Formating pada ribbon Home, kemudian klik manage Rule
3. Selanjutnya akan tampil gambar dibawah, untuk membuat rule baru klik New Rule..
4. Setelah keluar pilihan rule anda pilih rule Use Formula to Determine wich Cell to Format (lihat Gambar), slenjutnya pada kotak formula isi =J6<0 (artinya jika kolom J6 bernilai minus), maka klik tombol Format
5. Pada kotak dialog Format Cell ubah warnanya menjadi merah, klik OK dan tutup semua jendela.
6. Copy Formula di K6 ke baris berikutnya, maka hasilnya akan tampak :
Untuk lengkapntya silahkan Download Filenya 
0 comments: