Fungsi LOOKUP-Referensi Referensi Terdapat beberapa fungsi lookup-referensi lookup referensi pada Excel. Tetapi disini hanya akan dibahas 2 fungsi lookup-referensi referensi yang sering digunakan, yaitu HLOOKUP dan VLOOKUP. Secara garis besar, penggunaan rumus kedua fungsi tersebut tidaklah jauh berbeda. Yang membedakan hanyalah terletak pada susunan tabel bantu (tabel referensi) antata keduanya. Apakah tersusun secara horizontal/ datar atau vertikal/ tegak.
Fungsi HLOOKUP
HLOOKUP
:digunakan untuk menampilkan data dari sebuah tabel yang disusun dalam format MENDATAR/ Horizontal
B
C
D
NO TES A-010
NAMA Diana
NILAI UJIAN 95
HURUF KETERANGAN A Memuaskan =HLOOKUP(D4;C8:G10;2;0) ATAU =HLOKKUP(D4;TNILAI;2;0) F4=?(silahkan dicoba diisi dengan format rumus seperti di atas)
60 D Mengulang
73 C Cukup
Tabel Bantu 0 Nilai E Huruf Gagal Ket
E
F
85 B Baik
1 2 3
95 A Memuaskan
tabel disusun mendatar/horisontal dengan urutan data menaik FORMAT penulisan: =HLOOKUP(lookup_value;table_array;row_index_num;[range_lookup]) lookup_value;table_array;row_index_num;[range_lookup]) lookup_value: nilai kunci pembacaan tabel table_array: range data yang dususun HORIZONTAL sebagai tabel bantu yang akan dibaca row_index_num: nomor urut baris untuk pembacaan tabel dari baris paling ATAS mulai 1 sd. n
Fungsi VLOOKUP
VLOOKUP
:digunakan untuk menampilkan data dari sebuah tabel yang disusun dalam format TEGAK/ Vertikal
A
B
C
NO TES A-010
NAMA Diana
NILAI UJIAN 95
Tabel Bantu Nilai
Huruf
0
E
Gagal
60
D
Mengulang
73
C
Cukup
85
B
Baik
95
A
Memuaskan
1
2
3
D
E
HURUF KETERANGAN A Memuaskan =VLOOKUP(D4;H4:J8 H4:J8;2;0) ATAU =VLOOKUP(D4;TNILAI TNILAI;2;0) Untuk kolom E bagaimana rumusnya? Silahkan di coba seperti rumus di atas hanya dengan mengganti kolom indeksnya
Keterangan
FORMAT rumus: =VLOOKUP(lookup_value;table_array;col_index_num;[range_lookup]) lookup_value;table_array;col_index_num;[range_lookup])
1
lookup_value: nilai kunci pembacaan tabel table_array: range data yang dususun VERTIKAL sebagai tabel bantu yang akan dibaca col_index_num: nomor urut KOLOM untuk pembacaan tabel dari kolom paling KIRI mulai 1 sd. n
Catatan: TNILAI merupakan table_array yang sudah diberi nama. Pemberian nama boleh bebas tetapi dengan aturan tidak boleh menggunakan spasi jika nama tabel terdiri dari dua kata atau lebih. Tetapi usahakan pemberian nama tabel disesuaikan dengan isi datanya misalnya dengan nama TNILAI (karena berisi data nilai), TJADWAL (karena berisi data jadwal), TPSWAT(karena berisi data pesawat) dan lain-lain. Cara pemberian nama tabel: 1. OFFICE 2003 - Blok atau seleksi range datanya pada tabel bantunya(bukan termasuk nama fieldnya), perhatikan apakah tabel vertikal atau horizontal - Pilih menu Insert Define Name - Muncul kotak dialog New Name, kemudian pada kolom isian Name, berikan nama tabel - Klik Add OK - Jika tabel bantunya lebih dari satu, dengan cara yang sama lakukan seperti langkah di atas 2. OFFICE 2007 - Blok atau seleksi range datanya pada tabel bantunya(bukan termasuk nama fieldnya), perhatikan apakah tabel vertikal atau horizontal - Pilih menu Formula Define Name - Muncul kotak dialog New Name, kemudian pada kolom isian Name, berikan nama tabel - Klik OK - Jika tabel bantunya lebih dari satu, dengan cara yang sama lakukan seperti langkah di atas LATIHAN SOAL 1 TABEL_BANTU A
B
JENIS USAHA
KETERANGAN
1
NG
NIAGA
2
PT
PROPERTI
3
IT
INTERNET
4
AG
AGROBISNIS
2
DATA PENGUSAHA DAN JENIS USAHA DESA SUKA MAJU TASIKMALAYA
NO
NAMA
KETERANGAN
JENIS USAHA
1
PARMAN
NG
2
SULAIMAN
IT
3
WAHYU
PT
4
DEWI
AG
5
INDRA
NG
6
DESY
AG
7
INTAN
IT
8
HENDRA
NG
RUMUS A
RUMUS B
Petunjuk pengerjaan: 1.
Kolom KETERANGAN RUMUS A diisi dengan fungsi VLOOKUP dengan range data berdasarkan alamat sel data pada tabel bantu
2.
Kolom KETERANGAN RUMUS B diisi dengan fungsi VLOOKUP dengan range data berdasarkan definisi nama pada tabel bantu
Jawaban: RUMUS A
=
RUMUS B
=
LATIHAN SOAL 2 TABEL 1 NAMA BUKU DAN HARGA BUKU KODE
NAMA BUKU
HARGA BUKU
BK1
BERDAMAI DENGAN STRES
20000
BK2
HIDUP SEHAT BAGI WANITA
23000
BK3
REFORMASI PERBANKAN
17000
BK4
KEPADA BANGSA
31000
TABEL 2 KELOMPOK BUKU KODE BUKU
ED
PI
SS
TK
KELOMPOK BUKU
EKONOMI
PSIKOLOGI
SERI SEHAT
TOKOH
TABEL 3 KOTA PENJUALAN DAN DISTRIBUTOR KODE
KOTA PENJUALAN
DISTRIBUTOR
BM
BANDUNG
METRO
JJ
JAKARTA
JASATAMA
SG
SEMARANG
GRAMEDIA
YA
JOGJAKARTA
GUNUNG AGUNG
LAPORAN PENJUALAN BUKU
NO URUT 1
KODE BUKU
BK3-ED-BM
NAMA BUKU
KELOMPOK BUKU
DISTRIBUTOR
KOTA PENJUALAN
JUMLAH BUKU
PEMBAYAR AN
KETERANGAN
500
3
2
BK2-SS-BM
375
3
BK3-ED-YA
670
4
BK4-TK-SG
360
5
BK3-ED-YA
500
6
BK4-TK-SG
1000
7
BK1-PI-JJ
2100
8
BK2-SS-JJ
1200
9
BK1-PI-JJ
780
10
BK4-TK-SG
800 JUMLAH PEMBAYARAN PEMBAYARAN TERTINGGI PEMBAYARAN TERENDAH RATA-RATA PEMBAYARAN
Petunjuk Pengerjaan: 1. Definiskan nama ketiga tabel bantu di atas 2. Kode Buku pada Tabel Isian ditulis dengan format: XXX-XX-XX misalnya: BK3-ED-BM dimana BK3 menunjukkan kode nama buku, ED menunjukkan kode kelompok buku dan BM menunjukkan kode kota penjualan dan distributor 3. Nama Buku diisi dari Tabel 1 berdasarkan kode nama buku dari kode buku tabel isian 4. Kelompok Buku diisi dari Tabel 2 berdasarkan kode kelompok buku dari kode buku pada tabel isian 5. Distributor diisi dari Tabel 3 berdasarkan kode kota penjualan dan distributor dari kode buku tabel isian 6. Kota Penjualan diisi dari Tabel 3 berdasarkan kode kota penjualan dan distributor dari kode buku tabel isian 7. Pembayaran diisi Harga Buku dikalikan Jumlah Buku. Harga Buku diambil dari Tabel 1 berdasarkan kode nama buku dari kode buku tabel isian 8. Keterangan diisi dengan syarat: Jika kota penjualannya Jogjakarta, maka diberikan keterangan “Diskon 5%”, jika tidak isikan keterangan “-“ Jawaban: Kolom
Rumus
Nama Buku
=
Kelompok Buku
=
Distributor
=
Kota Penjualan
=
Pembayaran
=
Keterangan
=
LATIHAN SOAL 3 TABEL 1 NOMOR PENERBANGAN
BERANGKAT DATANG
TUJUAN
BONUS
GA219 GA430
HARGA TIKET
09.00-14.20
MEDAN
LUNCH
300.000
07.10-08.00
YOGYAKARTA
SNACK
150.000
GA660
06.00-08.45
DENPASAR
BREAKFAST
225.000
GA872
23.00-08.00
TOKYO
DIN/BRF
GA874
08.45-13.30
HONGKONG
BREAKFAST
GA878
14.30-21.45
PERT
DINNERS
1.050.000
GA898
19.45-22.00
DENPASAR
DINNERS
225.000
GA958
09.45-13.30
MANILA
LUNCH
650.000
1.175.000 700.000
4
GA980
17.25-20.00
SINGAPORE
DINNERS
500.000
GA972
19.00-22.15
BANGKOK
DINNERS
775.000
TABEL2 KODE JENIS PESAWAT
JENIS PESAWAT
747
BOEING 747
AB3
AIR BUS 3
D10
DC 10
DC9
DC 9
NO
KODE PENERBANGA N
BERANGKAT DATANG
JENIS PESAWATTUJUAN
HARGA TIKET
PAJAK
PSC AGEN
JUMLAH PENUMPANG
1
GA219DC9
85
2
GA660AB3
132
3
GA898747
121
4
GA874D10
99
5
GA958747
67
6
GA660AB3
156
7
GA430747
204
8
GA878AB3
98
9
GA874D10
56
10
GA872747
77
11
GA980AB3
43
12
GA958D10
52
13
GA874D10
81
14
GA958747
65
15
GA872DC9
102
TOTAL
BONUS
Petunjuk pengerjaan 1. Definiskan nama kedua tabel bantu tersebut 2. Kode Penerbangan terdiri dari Nomor Penerbangan (XXXXX) dan Kode Jenis Pesawat (YYY) XXXXXYYY 3. Berangkat Datang diisi dari Tabel 1 berdasarkan nomor penerbangan pada Kode Penerbangan tabel isian 4. Jenis Pesawat – Tujuan diisi dengan dua rumus. Rumus pertama (Jenis Pesawat) diisi dari Tabel 2 berdasarkan kode jenis pesawat pada Kode Penerbangan tabel isian dan Rumus kedua (Tujuan) diisi dari Tabel 1 berdasarkan nomor penerbangan pada Kode Penerbangan tabel isian Catatan: Antara rumus dipisahkan dengan perintah &”/”& 5. Harga Tiket diisi dari Tabel 1 berdasarkan nomor penerbangan pada Kode Penerbangan tabel isian 6. Pajak merupakn 10% dari Harga Tiket 7. PSC Agen diisi dengan syarat: JIKA HARGA TIKET KURANG DARI 100RB, PSC AGEN ADALAH 0,5% DARI HARGA TIKET; JIKA HARGA TIKET 100RB-300RB, PSC AGEN ADALAH 10% DARI HARGA TIKET; JIKA HARGA TIKET ANTARA 300RB-600RB, PSC AGEN ADALAH 15% DARI HARGA TIKET; JIKA HARGA TIKET LEBIH DARI 600RB, PSC AGEN ADALAH 20% DARI HARGA TIKET 8. Total diisi dari Tabel 1 berdasarkan nomor penerbangan pada Kode Penerbangan tabel isian 9. Bonus diisi dari Tabel 1 berdasarkan nomor penerbangan pada Kode Penerbangan tabel isian Jawaban: Kolom
Rumus
Berangkat Datang
=
Jenis Pesawat – Tujuan
=
Harga Tiket
=
5
Pajak
=
PSC Agen
=
Total
=
Bonus
=
LATIHAN SOAL 4 TABEL DAFTAR HARGA BARANG UKURAN
KODE BARANG S
M
JN KM TS
75.000 50.000 35.000
L 98.000 65.000 55.000
XL 102.000 70.000 99.000
125.000 95.000 80.000
TABEL DAFTAR NAMA SALES & DEPT. STORE SHIFT KERJA
KODE
DEPT STORE
01
SEMANGGI PLAZA
DIEGO TEDY
TEGUH WIRANGGA
02
PLAZA SENAYAN
PUTRI OLISA
MAHENDRA
03
PONDOK INDAH
NANDO PUTRA
UWI HESTINING
PAGI (PG)
KODE
SIANG (SG)
STOCK TANGGAL KIRIM
BARANG
DEPT. STORE
AW AL
AKH IR
1 2 3
KM100S KM025M TS095L
01-SR-PG 02-DG-PG 01-AA-SG
10 025 095
6 2 9
14-Mei-10 19-Apr-10 23-Jul-10
4 5 6 7 8 9
JN045M KM125XL TS036M KM123L JN020S TS040M
01-SR-PG 03-RM-PG 02-DB-SG 01-SR-PG 03-RM-PG 02-DB-SG
045 125 036
8 35 10 21 5 19
19-Okt-10 04-Feb-10 24-Apr-10 22-Jul-10 24-Mar-10 10-Mar-10
10
JN128XL
03-HY-SG
22
09-Agust-10
NAMA SALESDEPT. STORE PL SEN DIEGO
TANG GAL SETOR ###
NAM A PROD UK K
0
T
10
JE KE T-
###
HARGA SATUAN
000
NAMA PANGGI LAN SR DG AA
JUMLA H SETOR AN #
SR RM DB
TOTAL JUMLAH SETORAN JUMLAH SETOR TERBESAR JUMLAH SETOR TERKECIL RATA-RATA JUMLAH SETORAN Petunjuk pengerjaan 1. Definiskan nama kedua tabel bantu tersebut 2. Stock awal mengambil data angka yang terdapat pada Kode Barang 3. Nama Sales-Dept. Store diisi dari Tabel Daftar Nama Sales & Dept. Store berdasarkan kode Dept Store dan kode Shift Kerja 4. Tanggal Setor diisi satu minggu setelah tanggal kirim 5. Nama Produk diisi berdasarkan Kode Barang, Jika dua digit dari kiri = KM, maka diisi Kemeja, jika JN diisi Jeans dan jika TS diisi T-Shirt 6. Harga Satuan diisi dari Tabel Daftar Harga Barang berdasarkan Kode Barang dan Ukuran pada Tabel Daftar Harga Barang 7. Nama Panggilan mengambil data SR, DG, RM dan seterusnya dari Kode Dept. Store 8. Jumlah Setoran diisi berdasarkan Stock Awal dikurangi Stock Akhir kemudian dikalikan Harga Satuan
Daftar Pustaka Arifin Johar. Mengungkap Kedahsyatan 205 Fungsi Terapan Plus Fungsi Buatan Microsoft Excel. 2009. Jakarta. PT. Elex Media Komputindo Kumpulan Latihan Soal Excel
6
