PEMBAHASAN FUNGSI STRING, VLOOKUP, HLOOKUP PADA MS. EXCEL

Penjelasan singkat

Fungsi String:
1. LEFT (kunci_cell;jumlah_karakter)
2. RIGHT (kunci_cell;jumlah_karakter)
3. MID (kunci_cell;mulai_posisi_mengambil;jumlah_karakter)

Vlookup & Hlookup:
1. Vlookup (kunci_cell;range_table_vlookup;indeks_kolom)
2. Hlookup (kunci_cell;range_table_hlookup;indeks_baris)


Soal dan Pembahasan
1.

Rumus pada kolom

Jenis Kendaraan:

=IF(C3="Sd";"Sedan";IF(C3="Tr";"Truk";IF(C3="Mb";"Mini Bus")))

Tarif Sewa/ hari:

=IF(E3="Truk";"300000";IF(E3="Mini Bus";"400000";IF(E3="Sedan";"200000")))

Biaya Sewa:

=(D3*F3)

Bonus:

=IF(D3>7;G3*7,5%;"0")

Yang Dibayar:

=(G3-H3)

2.

Rumus pada kolom

Penerbit:

=IF(LEFT(B4;2)="AL";"Airlangga";IF(LEFT(B4;2)="AO";"Andi Offset";IF(LEFT(B4;2)="BA";"Binarupa Aksara";IF(LEFT(B4;2)="BP";"Balai Pustaka";IF(LEFT(B4;2)="EL";"Elexmedia";IF(LEFT(B4;2)="GA";"Gunung Agung";IF(LEFT(B4;2)="GM";"Griya Media";IF(LEFT(B4;2)="GR";"Gramedia";IF(LEFT(B4;2)="MI";"Mizan";IF(LEFT(B4;2)="SE";"Salemba Empat"))))))))))

Tahun:

=MID(B4;5;4)

Jenis Buku:

=IF(MID(B4;3;2)="01";"POLITIK";IF(MID(B4;3;2)="02";"EKONOMI";IF(MID(B4;3;2)="03";"SOSIAL";IF(MID(B4;3;2)="04";"BUDAYA";IF(MID(B4;3;2)="05";"HUKUM";IF(MID(B4;3;2)="06";"AGAMA";IF(MID(B4;3;2)="07";"MUSIK";IF(MID(B4;3;2)="08";"OLAHRAGA";IF(MID(B4;3;2)="09";"SEJARAH";IF(MID(B4;3;2)="10";"TEKNIK";IF(MID(B4;3;2)="11";"BAHASA")))))))))))

Harga Asal:

=IF(RIGHT(B4;1)="A";"67800";IF(RIGHT(B4;1)="B";"60200";IF(RIGHT(B4;1)="C";"58600";IF(RIGHT(B4;1)="D";"53000";IF(RIGHT(B4;1)="E";"48400";IF(RIGHT(B4;1)="F";"43800";IF(RIGHT(B4;1)="G";"39200";IF(RIGHT(B4;1)="H";"34600";IF(RIGHT(B4;1)="I";"30000";IF(RIGHT(B4;1)="J";"25400"))))))))))

Diskon:

=IF(C4="Airlangga";10%*F4;IF(C4="Gunung Agung";15%*F4;IF(C4="Balai Pustaka";20%*F4;"0")))

Harga Baru:

=(F4-G4)

3.

Rumus pada kolom

Jenis Kamar:

=VLOOKUP(C2;$A$15:$B$17;2;FALSE)

Tarif/ malam:

=HLOOKUP(C2;$F$14:$H15;2;FALSE)

Biaya Inap:

=(E2*F2)

Discount:

=IF(E2>5;5%*G2;"0")

Jumlah Bayar:

=(G2-H2)

Total Bayar:

=SUM(I2:I6)

Rata-Rata:

=AVERAGE(I2:I6)

Bayar Tertinggi:

=MAX(I2:I6)

Bayar Terendah:

=MIN(I2:I6)

4.

Rumus pada kolom

Nama Bus:

=IF(LEFT(C4;2)="GO";"Goodwil";IF(LEFT(C4;2)="KU";"Kurnia Jaya";IF(LEFT(C4;2)="SH";"Sahabat";IF(LEFT(C4;2)="SJ";"Sami Jaya";IF(LEFT(C4;2)="SN";"Setia Negara")))))

Jurusan:

=IF(D4="Goodwil";"Jakarta-Cirebon";IF(D4="Kurnia Jaya";"Jakarta-Denpasar";IF(D4="Sahabat";"Jakarta-Kuningan";IF(D4="Sami Jaya";"Jakarta-Surabaya";IF(D4="Setia Negara";"Jakarta-Semarang")))))

Kelas:

=IF(MID(C4;4;2)="BS";"Bisnis";IF(MID(C4;4;2)="EK";"Ekonomi";IF(MID(C4;4;2)="EX";"Eksekutif";IF(MID(C4;4;2)="SE";"Super Eksekutif"))))

Tarif Dasar:

=IF(D4="Goodwil";"50000";IF(D4="Kurnia Jaya";"250000";IF(D4="Sahabat";"70000";IF(D4="Sami Jaya";"200000";IF(D4="Setia Negara";"130000")))))

Tarif Tambahan:

=IF(F4="Bisnis";25%*G4;IF(F4="Ekonomi";0%*G4;IF(F4="Eksekutif";50%*G4;IF(F4="Super Eksekutif";75%*G4))))

Harga Tiket:

=SUM(G4+H4)

5.

Rumus pada kolom

Limit Sewa:

=IF(C3>=4;3;IF(C3=3;2;1))

Masa Sewa:

=LEFT(F3;2)-LEFT(E3;2)

Status Denda:

=IF(G3>D3;"DENDA";"TIDAK DENDA")

Sewa:

=VLOOKUP(LEFT(B3;2);$B$15:$E$18;3)*C3

Biaya Denda:

=VLOOKUP(LEFT(B3;2);$B$15:$E$18;4)*C3*(G3-D3)

Total:

=SUM(I3+J3)

6.

Rumus pada kolom

Nama Kelas:

=VLOOKUP(LEFT(B4;2);$B$22:$E$26;2)

Status:

=RIGHT(B4;1)

Pekerjaan:

=IF(E4="1";"PELAJAR";IF(E4="2";"WIRASWASTA";IF(E4="3";"SWASTA";IF(E4="4";"PNS"))))

Hari Kursus:

=VLOOKUP(D4;$C$22:$E$26;3)

Biaya:

=VLOOKUP(D4;$C$22:$E$26;2)

Diskon:

=IF(OR(D4="Senior Programming";F4="PELAJAR");H4*15%;0)

Jumlah Biaya:

=(H4-I4)

7.

Rumus pada kolom

Merek Sepeda Motor:

=IF(E5="HBT";"Beat";IF(E5="HSF";"Supra Fit";IF(E5="SSG";"Shogun";IF(E5="SSM";"Smash";IF(E5="SST";"Satria";IF(E5="YJZ";"Jupiter Z";IF(E5="YMI";"Mio";IF(E5="YNO";"Nouvo"))))))))

Harga Cash:

=IF(E5="HBT";"11500000";IF(E5="HSF";"13700000";IF(E5="SSG";"10250000";IF(E5="SSM";"10500000";IF(E5="SST";"22200000";IF(E5="YJZ";"12750000";IF(E5="YMI";"12000000";IF(E5="YNO";"10250000"))))))))

Angsuran Pokok:

=(G5/D5)

Angsuran Bunga:

=(G5*30%)/12

Angsuran Total:

=SUM(H5+I5)

Harga Kredit:

=(J5*D5)

8.

Rumus pada kolom

Jabatan Struktural:

=IF(C2="I";"STAF";IF(C2="II";"KASUBAG";IF(C2="III";"KABAG";IF(C2="IV";"DIREKSI"))))

Gaji Pokok:

=VLOOKUP(C2;$B$21:$E$24;2)

Tunjangan Istri:

=IF(D2="Nikah";VLOOKUP(C2;$B$21:$E$24;4);"0")

Tunjangan Anak:

=IF(AND(D2="NIKAH";E2<=3);VLOOKUP(C2;$B$21:$E$24;3)*E2;IF(AND(D2="NIKAH";E2>3);VLOOKUP(C2;$B$21:$E$24;3)*4;0))

Tunjangan Struktural:

=HLOOKUP(F2;$H$14:$K$15;2)

Tunjangan Transport:

=HLOOKUP(F2;$H$19:$K$20;2)

Total Gaji:

=SUM(G2:J2)

Total Golongan I / II / III / IV:

=COUNTIF(C2:C11;"I")

=COUNTIF(C2:C11;"II")

=COUNTIF(C2:C11;"III")

=COUNTIF(C2:C11;"IV")

9.

Rumus pada kolom

Program Studi:

=IF(LEFT(A2;3)="MNJ";"MANAJEMEN";IF(LEFT(A2;3)="AKT";"AKUNTASI";IF(LEFT(A2;3)="ISP";"ILMU EKONOMI DAN STUDI PEMBANGUNAN";IF(LEFT(A2;3)="D-3";"D-3 KEUANGAN DAN PERBANKAN"))))

Jumlah Mahasiswa (text):

=TEXT(MID(A2;5;2);"00")

Jumlah Mahasiswa (number):

=VALUE(C2)

Kelas:

=RIGHT(A2;1)

Kelas Lengkap:

=CONCATENATE(B2;"-";E2)

Ruang Kelas:

=CONCATENATE("Ruang Praktikum";" ";E2)

Well, kira-kira begitulah pembahasan hari ini! Semoga bermanfaat dan Mohon maaf bila ada salah :)