Page 1 of 7
PTI-5
HDSN 2014
Save latihan ini dengan nama file NIM_5, dan salin data ini di sheet-1 dengan nama LOOKUP DAFTAR HARGA JUAL BARANG No Kode Barang
Nama Barang
Harga Jual satuan
1
A-1
Apple
10,000,000
2
A-2
Samsung
7,000,000
3
A-3
Acer
4,500,000
4
A-4
HP
4,600,000
5
A-5
Sony
6,700,000
6
A-6
Dell
6,800,000
7
A-7
Lenovo
4,500,000
Transaksi penjualan selama bulan Januari 20xx (xx tahun sekarang)
No
Tgl Trans.
Kode Barang
Nama Barang
Qty
Harga Jual satuan
Total
1
12 Jan
A-1
?
15
?
?
2
13 Jan
A-2
?
34
?
?
3
14 Jan
A-1
?
32
?
?
4
15 Jan
A-2
?
12
?
?
5
16 Jan
A-4
?
45
?
?
6
17 Jan
A-5
?
67
?
?
7
18 Jan
A-6
?
43
?
?
8
19Jan
A-7
?
54
?
?
Total Penjualan Apple ……….. Rata-rata penjualan Aplle……….. Jumlah transaksi penjualan selama bulan ini…….. Jumlah transaksi (Volume transaksi) untuk Apple………. Total Penjualan Apple dan Total Penjualan Samsung ……….. Volume penjualan tertinggi untuk Aplle berapa kali…… Volume Qty penjualan terendah berjumlah …. units Diminta: 1. Mencari nama barang =lookup(sel A-1;sel nama barang apple s.d. lenovo) 2. Mencari harga jual satuan =lookup(Sel A-1;sel harga satuan aplle s.d. lenovo) 3. Mencari total =sel qty apple*sel harga satuan apple Catatan: supaya formula bisa dicopy ke sel berikutnya range nama barang dan harga jual satuan diabsolutkan.
Page 2 of 7
PTI-5
HDSN 2014
Kerjakan latihan ini pada sheet-2 dengan nama vlookup DAFTAR HARGA COST PRICE DAN SALES PRICE UNTUK MOTOR BARU TAHUN INI (Tabel-1) No
Code Description
Cost Price
Sales Price
Profit margin
1 T-1
Touring 250 cc
35,000,000
45,000,000
?
2 T-2
Touring 600 cc
54,000,000
64,000,000
?
3 S-1
Sport 250 cc
38,000,000
48,000,000
?
4 S-2
Sport 600 cc
90,000,000
120,000,000
?
5 TR-1
Trial 250 cc
55,000,000
65,000,000
?
6 TR-2
Trial 350 cc
65,000,000
75,000,000
?
7 Au-1
Automatic 125 cc
12,000,000
19,000,000
?
8 Au-2
Automatic 150 cc
18,000,000
22,000,000
?
Laporan penjualan Motor selama periode Januari - Maret tahun sekarang (Tabel-2) No
Code
Description
Volume terjual
Profit margin
1 T-1
?
23
?
2 T-2
?
45
?
3 S-1
?
43
?
4 S-2
?
12
?
5 TR-1
?
22
?
6 TR-2
?
67
?
7 Au-1
?
45
?
8 Au-2
?
23
?
Diminta: 1. Mencari Description T-1 =vlookup(sel code T-1,Range absolute code T-1 di daftar harga s.d sel profit margin au-2;2;false) 2. Mencari Profit Margin T-1 pada tabel-2 =vlookup(Range absolute code T-1 di daftar harga s.d sel profit margin au-2;5;false)*cel volume terjual T-1
Page 3 of 7
PTI-5
HDSN 2014
Salin data berikut ini di sheet3 dengan nama vlookupleft Daftar harga barang Laptop (TABEL 1) No
Kode Barang
Nama Barang
Harga
1 A-1
Asus
6000000
2 H-1
HP
6500000
3 A-2
Acer
6000000
4 T-1
Toshiba
7000000
5 S-1
Sony
8000000
6 L-1
Lenovo
8700000
7 D-1
Dell
7000000
8 A-3
Apple
9 L-2
LG
4500000
10 N-1
Net
6700000
11 L-3
Indonet
2300000
11700000
TABEL 2 Laporan Penjualan Laptop bekas sebagai berikut: No
Kode transaksi 1 A-1-14062014-23
Nama Barang Volume Harga ?
?
?
Tanggal Jual Jumlah ?
?
2 A-2-14062014-13 3 A-3-14062014-20 4 L-1-12072014-23 5 L-1-12072014-23 6 A-2-14062014-13 7 L-1-12072014-23 8 A-1-14062014-33
Jumlah Volume
………
Keterangan kode barang: A-1-14062014-23 Artinya: A-1, kode nama barang 14062014, tanggal penjualan 23, volume / qty barang yang terjual. Nama Barang: =Vlookup(Left(sel kode;2);range data absolute;kolom ke berapa di table array;FALSE) Volume =RIGHT() Tanggal =Date(Year;Month;Days); Volume dicari dengan Value(Right()) Tanggal jual =date(year;month;days) year, Month dan Days dicari dengan Value(mid().
Page 4 of 7
PTI-5
HDSN 2014
Proses data berikut ini di sheet4 dengan sheet vlookupright Tabel-1 Daftar mahasiswa yang mengambil mata kuliah Komputer Akuntansi di Polban Nomor Induk Mahasiswa
Nama Mahasiswa
Fakultas
Prodi
2014-001-Ak
Burhan
?
?
2014-091-An
Andri
3014-021-TK
Dudi
2014-002-Ak
Badu
2014-092-An
Dudu
3014-022-TK
Basri
3014-022-TE
Gina
Tabel 2 Daftar arti kode Kode
Prodi
Kode
Fakultas
An
Aministrasi Niaga
20 Ekonomi Bisnis
Ak
Akuntansi
30 Teknik
Tk
Teknik Komputer
Te
Teknik Elektro
Arti Nomor induk mahasiswa: xxxx-xxx-xx xxxx-xxx-xx abcd-fgh-jk ab = kode untuk Fakultas. 20 Fakultas Ekonomi Bisnis; 30 Fakultas Teknik cd= angkatan akademis masuk tahun 2014/2015 fgh = Nomor daftar urut masuk jk= kode program studi Fakultas: =vlookup(value(left(sel nim;2);table array fakultas;2) Prodi: =vlookup(right(sel nim;2);table array prodi;2), jika tidak keluar, lakukan sortir kode prodi
Page 5 of 7
PTI-5
HDSN 2014
Kerjakan data berikut ini pada sheet5 dengan nama hlookup Tabel 1 Daftar kode barang yang diperjual-belikan Kode Barang Nama Barang Harga Jual
BB
TP
Bubur
PP
Telor Pepsodent Puyuh
2,500 12,000
AG
BR
Anggur manis
25,000
TL
Beras Rojo Lele
50,000
25,000
Tabel 2 Penjualan Mingguan Kebutuhan Sehari-hari Kode
Nama Barang
AG
?
Volume 23
PP
44
TP
54
BB
56
TL
55
BR
43
AG
98
Harga Jual 50.000
Total
Cost Price
Laba
1150000
?
%Laba ?
Harga Jual adalah 10% dari harga Pokok (harga jual tsb adalah laba 10% dari Cost) Penjualan volume tebanyak? Penjualan Total terendah? Rata-rata penjualan volume? Jumlah penjulan volume untuk AG? Rata-rata Volume penjualan AG? Berapakan jumlah transaksi minggu ini? Nama barang: =Hlookup(sel kode;table array kode nama barang;baris keberapa) Harga Jual: =hlookup(sel kode; table array harga jual;baris ke 3) Cost Price: Harga jual =110%=total(1150.000) Price Cost = total jual/110 Laba=total sales-cost %Laba = Laba/cost Price*100%
Telor ayam 16,000
Page 6 of 7
PTI-5
HDSN 2014
Salin data ini di Sheet6 Tabel 1 Daftar Harga Jual Anggur Australia/Newzeland Kode/KW
KW-1
KW-2
KW-3
AA
25,000
20,000
10,000
AB
28,000
22,000
11,000
AC
30,000
23,000
12,000
AD
32,000
24,000
13,000
Tabel 2 transaksi selama minggu ke 1 Bulan November 2014 Kode Transaksi
Harga Jual Vlookup
Harga Jual Hlookup
Volume
Total Penjualan
AA-KW-1-300
?
?
?
?
AB-KW-3-600
?
?
?
?
AC-KW-1-500
?
?
?
?
AD-KW-2-234
?
?
?
?
Ket: AA-KW-1-300 XX- = kode barang (AA) XX-X- = Kwalitas barang (KW-1) XX = Volume yang terjual (300) Kerangka formula mencari harga jual dengan Vlookup: 1. Muhammad Risyan: 1.1. =Vlookup(left(kt;2);table array;if(mid(kt;4;4)=“KW-1”;”2”;if(mid(kt;4;4)=“KW-2”;”3”;”4”)));False 1.2. =vlookup(left(kt;2);table array di tabel-1;if(mid(kt;4;4)=“kw-1”;2;if(mid(kt;4;4)=“kw-2”;3;4))) 1.3. =If(mid(kt;4;4)=“KW-1”;vlookup(kt;tbl array kw-1*;2;If(mid(kt;4;4)=“KW-2”;vlookup(kt;tbl array kw-2*;3;vlookup(kt;tbl array kw-3*;4)) Kerangka formula mencari Harga Jual dengan Hlookup 1. Abdullah: =Hlookup(mid(kt;4;4);TABLE ARRAY;if(left(kt;2)=“AA”;2;if(left(kt;2)=“AB”;3;(left(kt;2)=“AC”; 4;5)));false) 2. =if(kode=1;hlookup(kt;table array u kw-1;2;if(kode=2;hlookup(kt;table array kw-2;3;hlookup(kt;table array s.d. kw-3;4)) Volume: =velue(right(KT;3)) Total Penjualan =harga jual x Volume
Page 7 of 7
PTI-5
HDSN 2014
Kerjakan di sheet7 data berikut ini DAFTAR GAJI POKOK GOL/JABATAN
A
K
S
1
100
500
900
2
200
600
1000
3
300
700
2000
4
400
800
3000
KET: A = ADMINISTRASI
K = KETUA BAGIAN S = SEKRETARIS
DAFTAR GAJI POKOK KARYAWAN PT RANDALL NO
NIK
NAMA
GAPOK -H
GAPOK-V
1 2A-1987
AMIN
?
?
2 3A-2007
DUDI
?
?
3 4S-2010
ANDI
?
?
4 4K-2009
KURDI
?
?
=hlookup(mid(NIK;2;1);sel nik; Tabel Array Daftar gaji Pokok;If(value(left(NIK;1))=1;2;if(… dst.. =vlookup(value(left(nik;1)); tabel array gapok;if(mid(nik;2;1)=“a”;1;if(mid(nik;2;1)=“k”;3, dst…
