Studi Kasus [Excel] Fungsi VLOOKUP dan HLOOKUP - Kasus 2

Ketentuan Soal Kasus 2:
1.    Blok area C18:D19 dan berikan nama melalui Menu Formulas – Name Manager dengan nama “Kelas”.
2.    Blok area B23:E26 dan berikan nama melalui Menu Formulas – Name Manager dengan nama “Nama”.
3.    Blok area G19:I22 dan berikan nama melalui Menu Formulas – Name Manager dengan nama “Bonus”.
4.    Nama Kereta diambil dari Tabel Nama dan Tujuan Kereta

=VLOOKUP(MID(B5,3,2),nama,2,FALSE)

5.    Kelas diambil dari Tabel Kelas

=HLOOKUP(LEFT(B5,1),kelas,2)

6.    Tujuan diambil dari Tabel Nama dan Tujuan Kereta

=VLOOKUP(MID(B5,3,2),nama,3,FALSE)

7.    Waktu diisi denga ketentuan :
•    Jika Kode Tiket X maka Waktu Pagi
•    Jika Kode Tiket Y maka Waktu Siang
•    Jika Kode Tiket V maka Waktu Sore
•    Jika Kode Tiket Z maka Waktu Malam

=IF(RIGHT(B5,1)="X","Pagi",IF(RIGHT(B5,1)="Y","Siang",IF(RIGHT(B5,1)="V","Sore","Malam")))

8.    Jumlah Tiket diambil dari kolom Kode Tiket

=VALUE(MID(B5,6,2))

9.    Harga Tiket diisi dengan ketentuan :
•    Jika Jumlah Tiket >=5 maka Harga Tiket dapat potongan 2,5% dari Harga Tiket yang ada dalam tabel
•    Selain ketentuan tersebut Harga Tiket sesuai dengan yang ada di tabel

=IF(H5>=5,VLOOKUP(MID(B5,3,2),nama,4,FALSE)*97%,VLOOKUP(MID(B5,3,2),nama,4,FALSE))

10.    Discount Khusus diisi dengan ketentuan :
•    Jika Kelas Super Executive dan Waktu Pagi maka Disc 7% * Harga Tiket
•    Jika Kelas Super Executive dan Waktu Siang maka Disc 5% * Harga Tiket
•    Jika Kelas Super Executive dan Waktu Sore maka Disc 2% * Harga Tiket
•    Selain ketentuan tersebut tidak dapat Disc

=IF(AND(E5="Super Executive",G5="Pagi"),7%*I5,IF(AND(E5="Super Executive",
G5="Siang"),5%*I5,IF(AND(E5="Super Executive",
G5="Sore"),2%*I5,0)))

11.    Total Bayar diisi dengan ketentuan Harga Tiket dikurangi Disc Khusus

=I5-J5

12.    Bonus diambil dari tabel Bonus

=IF(E5="super Executive",VLOOKUP(D5,bonus,2,FALSE),VLOOKUP(D5,bonus,3,FALSE))

excel

Related Posts