Latihan kali ini kita akan mencoba lagi fungsi VLookUp. Pertama buatlah tabel seperti ini:
Buat tabel dengan ketentuan kode menentukan Jurusan, Biaya, dan Jumlah SKS. Jika sudah, sorot kolom isi, karena akan dimasukkan fungsi VLookUp, lalu beri nama JURUSAN.
Buat tabel dengan ketentuan kode status dan Nama status. Jika sudah, sorot kolom isi, karena akan dimasukkan fungsi VLookUp, lalu beri nama STATUS.
Pada tabel utama, sel E4 (JURUSAN) masukkan fungsi VLookUp berikut:
=VLOOKUP(C4,JURUSAN,2)
Pada tabel utama, sel F4 (STATUS) masukkan fungsi VLookUp berikut:
=HLOOKUP(D4,STATUS,2)
Pada tabel utama, sel G4 (BIAYA) masukkan fungsi VLookUp berikut:
=VLOOKUP(C4,JURUSAN,3)
Pada tabel utama, sel H4 (JUMLAH SKS) masukkan fungsi VLookUp berikut:
=VLOOKUP(C4,JURUSAN,4)
Jika sudah, ikuti untuk baris selanjutnya. Gunakan cara cepat dengan memilih kolom dan menyeretnya ke bawah. Hasil akhir akan nampak seperti ini:
Sabtu, 21 November 2009
Excel : Kredit Sepeda Motor
Untuk latihan kali ini kita akan mencoba fungsi VLookUp. Pertama buatlah tabel seperti ini:
Ketentuan kode produk digit pertama adalah kode merek:
Sorot kolom isi, karena akan dimasukkan fungsi VLookUp, lalu beri nama MEREK.
Ketentuan kode produk digit kedua dan ketiga adalah kode jenis sepeda motor. Setiap jenis mempunyai harga, DP, dan angsuran yang berbeda:
Sorot kolom isi, karena akan dimasukkan fungsi VLookUp, lalu beri nama TIPE.
Pada tabel utama, sel D6 (MEREK) masukkan fungsi VLookUp berikut:
=VLOOKUP(LEFT(C6,1),MEREK,2)
Pada tabel utama, sel E6 (TIPE) masukkan fungsi VLookUp berikut:
=VLOOKUP(MID(C6,2,2),TIPE,2)
Pada tabel utama, sel F6 (TIPE) masukkan fungsi VLookUp berikut:
=VLOOKUP(MID(C6,2,2),TIPE,3)
Pada tabel utama, sel G6 (TIPE) masukkan fungsi VLookUp berikut:
=VLOOKUP(MID(C6,2,2),TIPE,4)
Pada tabel utama, sel H6 (TIPE) masukkan fungsi VLookUp berikut:
=VLOOKUP(MID(C6,2,2),TIPE,5)
Jika sudah, ikuti untuk baris selanjutnya. Gunakan cara cepat dengan memilih kolom dan menyeretnya ke bawah. Hasil akhir akan nampak seperti ini:
Ketentuan kode produk digit pertama adalah kode merek:
Sorot kolom isi, karena akan dimasukkan fungsi VLookUp, lalu beri nama MEREK.
Ketentuan kode produk digit kedua dan ketiga adalah kode jenis sepeda motor. Setiap jenis mempunyai harga, DP, dan angsuran yang berbeda:
Sorot kolom isi, karena akan dimasukkan fungsi VLookUp, lalu beri nama TIPE.
Pada tabel utama, sel D6 (MEREK) masukkan fungsi VLookUp berikut:
=VLOOKUP(LEFT(C6,1),MEREK,2)
Pada tabel utama, sel E6 (TIPE) masukkan fungsi VLookUp berikut:
=VLOOKUP(MID(C6,2,2),TIPE,2)
Pada tabel utama, sel F6 (TIPE) masukkan fungsi VLookUp berikut:
=VLOOKUP(MID(C6,2,2),TIPE,3)
Pada tabel utama, sel G6 (TIPE) masukkan fungsi VLookUp berikut:
=VLOOKUP(MID(C6,2,2),TIPE,4)
Pada tabel utama, sel H6 (TIPE) masukkan fungsi VLookUp berikut:
=VLOOKUP(MID(C6,2,2),TIPE,5)
Jika sudah, ikuti untuk baris selanjutnya. Gunakan cara cepat dengan memilih kolom dan menyeretnya ke bawah. Hasil akhir akan nampak seperti ini:
Sabtu, 14 November 2009
Excel : Daftar Nilai Siswa
Latihan kali ini kita akan mencoba lagi fungsi VLookUp. Pertama buatlah tabel seperti ini:
Buat tabel dengan ketentuan kode menentukan KELAS. Jika sudah, sorot kolom isi, karena akan dimasukkan fungsi VLookUp, lalu beri nama KELAS.
Buat tabel dengan ketentuan kode RATA2 yang akan menentukan PREDIKAT dan KETERANGAN. Jika sudah, sorot kolom isi, karena akan dimasukkan fungsi VLookUp, lalu beri nama PREDIKAT.
Pada tabel utama, sel E5 (KELAS) masukkan fungsi VLookUp berikut:
=VLOOKUP(D5,KELAS,2)
Pada tabel utama, sel I5 (RATA2) masukkan fungsi rata-rata nilai berikut:
=AVERAGE(F5:H5)
Pada tabel utama, sel J5 (PREDIKAT) masukkan fungsi VLookUp berikut:
=VLOOKUP(I5,PREDIKAT,2)
Pada tabel utama, sel K5 (KETERANGAN) masukkan fungsi VLookUp berikut:
=VLOOKUP(I5,PREDIKAT,3)
Jika sudah, ikuti untuk baris selanjutnya. Gunakan cara cepat dengan memilih kolom dan menyeretnya ke bawah. Hasil akhir akan nampak seperti ini:
Buat tabel dengan ketentuan kode menentukan KELAS. Jika sudah, sorot kolom isi, karena akan dimasukkan fungsi VLookUp, lalu beri nama KELAS.
Buat tabel dengan ketentuan kode RATA2 yang akan menentukan PREDIKAT dan KETERANGAN. Jika sudah, sorot kolom isi, karena akan dimasukkan fungsi VLookUp, lalu beri nama PREDIKAT.
Pada tabel utama, sel E5 (KELAS) masukkan fungsi VLookUp berikut:
=VLOOKUP(D5,KELAS,2)
Pada tabel utama, sel I5 (RATA2) masukkan fungsi rata-rata nilai berikut:
=AVERAGE(F5:H5)
Pada tabel utama, sel J5 (PREDIKAT) masukkan fungsi VLookUp berikut:
=VLOOKUP(I5,PREDIKAT,2)
Pada tabel utama, sel K5 (KETERANGAN) masukkan fungsi VLookUp berikut:
=VLOOKUP(I5,PREDIKAT,3)
Jika sudah, ikuti untuk baris selanjutnya. Gunakan cara cepat dengan memilih kolom dan menyeretnya ke bawah. Hasil akhir akan nampak seperti ini:
Excel : Latihan laporan Penjualan Mebel
Jika dimplementasikan pada laporan penjualan. Dari sebuah kode, bisa dipecah menjadi merk, jenis barang, harga disko. Seperti kode LAL350-A, digit 1 = merk, digit 2-3 = jenis barang, digit4-5-6 = harga, digit 7 = besarnya diskon.
Rumus untuk merk:
=IF(LEFT(C7,1)="L","Ligna",IF(LEFT(C7,1)="G","Garuda","Olimpic"))
Rumus untuk jenis barang:
IF(MID(C7,2,2)="AL","ALMARI",IF(MID(C7,2,2)="MB","MEJABELAJAR",IF(MID(C7,2,2)="MK","MEJA KERJA","RAK BUKU")))
Rumus untuk harga:
=IF(MID(C7,4,3)="250","250000",IF(MID(C7,4,3)="350","350000",IF(MID(C7,4,3)="475","475000","760000")))
Rumus untuk besarnya diskon:
=IF(RIGHT(C7,1)="A",35%*H7,IF(RIGHT(C7,1)="B",30%*H7,IF(RIGHT(C7,1)="C",25%*H7,15%*H7)))
Ketentuan untuk MERK (KODE DIGIT 1)
Jika L Ligna
Jika G Garuda
selain itu O Olimpic
Ketentuan untuk Jenis Barang (KODE DIGIT 2 dan 3)
Jika AL ALMARI
Jika MB MEJA BELAJAR
Jika MK MEJA KERJA
selian itu RB RAK BUKU
Ketentuan Harga
KODE Harga
250 250000
350 350000
475 475000
selain itu 760 760000
Ketentuan Jumlah Harga = harga x jumlah barang
ketentuan diskon
Jika A diskon 35% * Jumlah harga
Jika B diskon 30% * Jumlah Harga
Jika C diskon 25% * Jumlah Harga
selain itu D diskon 15% * Jumlah Harga
Harga Dibayar = Jumlah Harga - DISKON
Rumus untuk merk:
=IF(LEFT(C7,1)="L","Ligna",IF(LEFT(C7,1)="G","Garuda","Olimpic"))
Rumus untuk jenis barang:
IF(MID(C7,2,2)="AL","ALMARI",IF(MID(C7,2,2)="MB","MEJABELAJAR",IF(MID(C7,2,2)="MK","MEJA KERJA","RAK BUKU")))
Rumus untuk harga:
=IF(MID(C7,4,3)="250","250000",IF(MID(C7,4,3)="350","350000",IF(MID(C7,4,3)="475","475000","760000")))
Rumus untuk besarnya diskon:
=IF(RIGHT(C7,1)="A",35%*H7,IF(RIGHT(C7,1)="B",30%*H7,IF(RIGHT(C7,1)="C",25%*H7,15%*H7)))
Ketentuan untuk MERK (KODE DIGIT 1)
Jika L Ligna
Jika G Garuda
selain itu O Olimpic
Ketentuan untuk Jenis Barang (KODE DIGIT 2 dan 3)
Jika AL ALMARI
Jika MB MEJA BELAJAR
Jika MK MEJA KERJA
selian itu RB RAK BUKU
Ketentuan Harga
KODE Harga
250 250000
350 350000
475 475000
selain itu 760 760000
Ketentuan Jumlah Harga = harga x jumlah barang
ketentuan diskon
Jika A diskon 35% * Jumlah harga
Jika B diskon 30% * Jumlah Harga
Jika C diskon 25% * Jumlah Harga
selain itu D diskon 15% * Jumlah Harga
Harga Dibayar = Jumlah Harga - DISKON
Excel : Fungsi LEFT, MID, RIGHT, & IF
Jika fungsi sebelum diganbungkan dengn fungsi IF, maka bisa menjabarkan sebuah kode, misalnya saja kode NIM 95120067, 2 digit pertama adalah tahun angkatan, 2 digit selanjutnya adalah jurusan. Dengan begitu kita bisa secara otomatis membuat tabel dengan cepat. seperti berikut.
Rumus di kolom jurusan
=IF(MID(B3,3,2)="12","MANAJEMEN",IF(MID(B3,3,2)="11","AKUNTANSI","INFORMATIKA"))
Rumus di kolom angkatan
=IF(LEFT(B3,2)="95","1995",IF(LEFT(B3,2)="96","1996","1997"))
Atau
Rumus di kolom nama motor
=IF(LEFT(B10,1)="H","HONDA",IF(LEFT(B10,1)="Y","YAMAHA","SUZUKI"))
Rumus di kolom jenis
=IF(MID(B10,2,2)="SP","SUPRA",IF(MID(B10,2,2)="JP","JUPITER","SMASH"))
Rumus di kolom tahun
=IF(RIGHT(B10,4)="2005","2005",IF(RIGHT(B10,4)="2006","2006","2008"))
Atau
Rumus di kolom nama pesawat
=IF(LEFT(B18,1)="M","MERPATI","GARUDA")
Rumus di kolom jam berangkat
=IF(MID(B18,2,2)="12","JAM 12","JAM 07")
Rumus di kolom jurusan
=IF(RIGHT(B18,3)="001","JAKARTA","SURABAYA")
Rumus di kolom jurusan
=IF(MID(B3,3,2)="12","MANAJEMEN",IF(MID(B3,3,2)="11","AKUNTANSI","INFORMATIKA"))
Rumus di kolom angkatan
=IF(LEFT(B3,2)="95","1995",IF(LEFT(B3,2)="96","1996","1997"))
Atau
Rumus di kolom nama motor
=IF(LEFT(B10,1)="H","HONDA",IF(LEFT(B10,1)="Y","YAMAHA","SUZUKI"))
Rumus di kolom jenis
=IF(MID(B10,2,2)="SP","SUPRA",IF(MID(B10,2,2)="JP","JUPITER","SMASH"))
Rumus di kolom tahun
=IF(RIGHT(B10,4)="2005","2005",IF(RIGHT(B10,4)="2006","2006","2008"))
Atau
Rumus di kolom nama pesawat
=IF(LEFT(B18,1)="M","MERPATI","GARUDA")
Rumus di kolom jam berangkat
=IF(MID(B18,2,2)="12","JAM 12","JAM 07")
Rumus di kolom jurusan
=IF(RIGHT(B18,3)="001","JAKARTA","SURABAYA")
Excel : Fungsi LEFT, MID, RIGHT
Kita akan mengenal fungsi LEFT, MID dan RIGHT yang akan digabungkan dengan beberapa fungsi seperti IF
Fungsi 'LEFT', 'MID', dan 'RIGHT'
=RIGHT(kolom, jumlah karakter dari kanan)
=MID(kolom, karakterkesekian, jumlahkarakter)
=LEFT(kolom, jumlah karakter dari kiri)
Rumus pada kolom C:
=RIGHT(B4,5)
=MID(B5,6,4)
=LEFT(B6,5)
=MID(B7,5,4)
=MID(B8,2,2)
=MID(B9,11,6)
=MID(B10,6,4)
Fungsi 'LEFT', 'MID', dan 'RIGHT'
=RIGHT(kolom, jumlah karakter dari kanan)
=MID(kolom, karakterkesekian, jumlahkarakter)
=LEFT(kolom, jumlah karakter dari kiri)
Rumus pada kolom C:
=RIGHT(B4,5)
=MID(B5,6,4)
=LEFT(B6,5)
=MID(B7,5,4)
=MID(B8,2,2)
=MID(B9,11,6)
=MID(B10,6,4)
Sabtu, 07 November 2009
Excel : fungsi IF
Membuat tabel IF
Pertama kita akan buat tabel dengan satu IF (satu syarat), mata kuliah Manajemen memiliki kode 12, sedangkan yang lainnya kita akan anggap Akuntansi. Buat tabel seperti ini lebih dulu.
Klik kolom Nama Jurusan, lalu klik Insert Function dan pilih IF.
Pada Logical_test masukkan B4=12, pada Value_if_true masukkan MANAJEMEN, pada Value_if_false masukkan AKUNTANSI. Jika sudah klik OK.
Drag ke kolom bawahnya.
Pertama kita akan buat tabel dengan satu IF (satu syarat), mata kuliah Manajemen memiliki kode 12, sedangkan yang lainnya kita akan anggap Akuntansi. Buat tabel seperti ini lebih dulu.
Klik kolom Nama Jurusan, lalu klik Insert Function dan pilih IF.
Pada Logical_test masukkan B4=12, pada Value_if_true masukkan MANAJEMEN, pada Value_if_false masukkan AKUNTANSI. Jika sudah klik OK.
Drag ke kolom bawahnya.
Langganan:
Postingan (Atom)