Langsung ke konten utama

Contoh Soal Ms.Excel (Penggunaan Fungsi String, Vlookup, Dan Hlookup)

Pada materi kali ini, kita akan membahas tentang soal kasus dalam microsoft excel dengan menggunakan fungsi string, vlookup, dan hlookup. Langsung saja kita ke soal pertama

Soal Pertama

Dalam menjawab soal ini, kita harus mencari terlebih dahulu jenis kendaraan yang dipakai. Oleh karena itu tabel 'Jenis Kendaraan' kita masukan fungsi
=IF(C3="SD";"Sedan";IF(C3="TR";"Truk";"Mini Bus"))

Untuk tabel 'Tarif Sewa Per Hari' kita masukan fungsi
=IF(E3="Truk";300000;IF(E3="Mini Bus";400000;200000))

Lalu berikutnya table 'biaya sewa' kita masukan fungsi
=(D3*F3)

Lalu table 'bonus' menggunakan fungsi
=IF(D3>7;G3*7,5%;"0")

Table terakhir yaitu 'yang dibayar' menggunakan fungsi
=(G3-H3)

Maka hasil yang didapat akan seperti ini



Soal Kedua

KODE
PENERBIT
AL
Air Langga
AO
Andi Offset
BA
Binarupa Aksara
BP
Balai Pustaka
EL
Elexmedia
GA
Gunung Agung
GM
Griya Media
GR
Gramedia
MI
Mizan
SE
Salemba Empat

KODE
HARGA
A
67.800
B
60.200
C
58.600
D
53.000
E
48.400
F
43.800
G
39.200
H
34.600
I
30.000
J
25.400

KODE
JENIS BUKU
1
POLITIK
2
EKONOMI
3
SOSIAL
4
BUDAYA
5
HUKUM
6
AGAMA
7
MUSIK
8
OLAHRAGA
9
SEJARAH
10
TEKNIK
11
BAHASA

Dalam soal kali ini, kita akan menjawab kolom penerbit terlebih dahulu dengan menggunakan rumus
=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"))))))))))

Lalu untuk kolom tahun menggunakan rumus
=MID(B4;5;4)

Untuk kolom ketiga yakni kolom jenis buku menggunakan rumus
=IF(MID(B5;3;2)="01";"POLITIK";IF(MID(B5;3;2)="02";"EKONOMI";IF(MID(B5;3;2)="03";"SOSIAL";IF(MID(B5;3;2)="04";"BUDAYA";IF(MID(B5;3;2)="05";"HUKUM";IF(MID(B5;3;2)="06";"AGAMA";IF(MID(B5;3;2)="07";"MUSIK";IF(MID(B5;3;2)="08";"OLAHRAGA";IF(MID(B5;3;2)="09";"SEJARAH";IF(MID(B5;3;2)="10";"TEKNIK";IF(MID(B5;3;2)="11";"BAHASA")))))))))))

Selanjutnya kolom harga asal menggunakan rumus
=IF(RIGHT(B4;1)="A";"67.800";IF(RIGHT(B4;1)="B";"60.200";IF(RIGHT(B4;1)="C";"58.600";IF(RIGHT(B4;1)="D";"53.000";IF(RIGHT(B4;1)="E";"48.400";IF(RIGHT(B4;1)="F";"43.800";IF(RIGHT(B4;1)="G";"39.200";IF(RIGHT(B4;1)="H";"34.600";IF(RIGHT(B4;1)="J";"25.400")))))))))

Untuk kolom berikutnya menggunakan rumus
=IF(C4="AIRLANGGA";F4*10%;IF(C4="GUNUNG AGUNG";F4*15%;IF(C4="BALAI PUSTAKA";F4*20%;"0")))

Dan yang terakhir kolom harga baru menggunakan rumus
=(F4-G4)

Maka hasil akhirnya akan seperti ini

Soal Ketiga

Dalam kasus ini, kita akan masuk kedalam penggunaan fungsi VLOOKUP dan HLOOKUP. Untuk table pertama kita akan menggunakan rumus sebagai berikut
=VLOOKUP(C2;$A$14:$B$16;2;FALSE)

Yang harus dingat adalah setelah kita memblok kolom yang menjadi kode kamar (C2) kita blok tabel kode kamar dan jenis kamar yang berada dibawah tabel soal. tabel inilah yang menjadi juru kunci dalam soal ini. Jika tabelnya berbentuk vertikal, maka gunakanlah rumus VLOOKUP. Namun jika tabelnya berbentuk horizontal, maka gunakanlah rumus HLOOKUP.
Setelah memblok tabel yang menjadi kunci soal, tekan f4. Maka rumus akan berubah menjadi kode yang tidak beraturan. 
Berikutnya, lihat table yang menjadi juru kunci. Apakah jawaban yang ingin kita cari berada di kolom 1 atau kolom 2? jika itu kolom 2, maka pada fungsi tadi kita tulis angka 2. Berikutnya kita ketik "FALSE" maka hasil dari jawaban pun akan terisi sesuai dengan tabel kunci tersebut

Untuk kolom kedua yakni tarif/malam, kita menggunakan fungsi
=HLOOKUP(C2;$E$13:$H$14;2;FALSE)

Untuk kolom ketiga kita menggunakan fungsi
=E2*F2

Berikutnya untuk kolom diskon kita menggunakan formula
=IF(E2>5;G2*5%;0)

Terakhir, untuk kolom jumlah bayar kita menggunakan rumus
=G2-H2

Maka hasil akhirnya akan menjadi seperti ini

Soal Keempat
Langsung saja untuk kolom pertama yakni "Nama Bus" kita menggunakan rumus
=IF(LEFT(C4;2)="KU";"Kurnia Jaya";IF(LEFT(C4;2)="GO";"Goodwil";IF(LEFT(C4;2)="SH";"Sahabat";IF(LEFT(C4;2)="SJ";"Sami Jaya";IF(LEFT(C4;2)="SN";"Setia Negara")))))

Untuk kolom kedua yakni "Jurusan" kita menggunakan rumus
=IF(LEFT(C4;2)="GO";"Jakarta-Cirebon";IF(LEFT(C4;2)="KU";"Jakarta-Denpasar";IF(LEFT(C4;2)="SH";"Jakarta-Kuningan";IF(LEFT(C4;2)="SJ";"Jakarta-Surabaya";IF(LEFT(C4;2)="SN";"Jakarta-Semarang")))))

Untuk kolom "Kelas" kita menggunakan rumus
=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"))))

Selanjutnya kolom tarif dasar kita menggunakan rumus
=IF(LEFT(C4;2)="GO";"50.000";IF(LEFT(C4;2)="KU";"250.000";IF(LEFT(C4;2)="SH";"70.000";IF(LEFT(C4;2)="SJ";"200.000";IF(LEFT(C4;2)="SN";"130.000")))))

Untuk kolom tarif tambahan kita menggunakan formula
=IF(MID(C4;4;2)="BS";G4*25%;IF(MID(C4;4;2)="EX";G4*50%;IF(MID(C4;4;2)="SE";G4*75%;IF(MID(C4;4;2)="EK";G4*0%))))

Dan yang terakhir kolom "Harga Tiket" kita menggunakan rumus
=SUM(G4+H4)

Setelah menggunakan rumus-rumus diatas maka hasil yang didapat akan menjadi seperti ini

Soal Kelima

Untuk kolom "Limit Sewa" kita menggunakan rumus
=IF(C3=4;3;IF(C3=3;2;1))

Untuk kolom "Masa Sewa" kita menggunakan rumus
=LEFT(F3;2)-LEFT(E3;2)

Selanjutnya untuk kolom "Status Denda" kita gunakan rumus
=IF(G3>D3;"DENDA";"TIDAK DENDA")

Berikutnya kolom "Sewa" kita menggunakan rumus
=VLOOKUP(LEFT(B3;2);$A$15:$D$18;3)*C3

Kolom "Biaya Denda" kita menggunakan formula
=VLOOKUP(LEFT(B3;2);$A$15:$D$18;4)*C3*(G3-D3)

Terakhir untuk kolom "Total" kita menggunakan rumus
=SUM(I3+J3)

Maka hasil akhir yang muncul akan seperti dibawah ini


Soal Keenam

Untuk kolom pertama "Nama Kelas" kita menggunakan rumus
=IF(LEFT(B4;2)="CI";"COMPUTER INTRODUCTION";IF(LEFT(B4;2)="GD";"Graphic Design";IF(LEFT(B4;2)="MO";"Microsoft Office";IF(LEFT(B4;2)="SP";"Senior Programming";IF(LEFT(B4;2)="YP";"Yunior Programming")))))

Kolom kedua yakni "Status" kita menggunakan formula sbb
=RIGHT(B4;1)

Untuk kolom ketiga kita menggunakan rumus
=IF(RIGHT(B4;1)="1";"Pelajar";IF(RIGHT(B4;1)="2";"Wiraswasta";IF(RIGHT(B4;1)="3";"Swasta";IF(RIGHT(B4;1)="4";"PNS"))))

Selanjutnya kolom keempat kita menggunakan rumus
=IF(LEFT(B4;2)="CI";"Senin-Selasa";IF(LEFT(B4;2)="GD";"Senin-Rabu";IF(LEFT(B4;2)="MO";"Senin-Kamis";IF(LEFT(B4;2)="SP";"Senin-Jumat";IF(LEFT(B4;2)="YP";"Senin-Sabtu")))))

Untuk mencari kolom biaya kita menggunakan rumus
=IF(LEFT(B4;2)="CI";"100000";IF(LEFT(B4;2)="GD";"450000";IF(LEFT(B4;2)="MO";"300000";IF(LEFT(B4;2)="SP";"600000";IF(LEFT(B4;2)="YP";"300000")))))

Kolom berikutnya yakni "Diskon" kita menggunakan rumus
=IF(RIGHT(B4;1)="1";H4*15%;IF(LEFT(B4;2)="SP";H4*15%;0))

Dan kolom terakhir yaitu "Jumlah Biaya" kita menggunakan rumus
=H4-I4

Maka hasil akhir yang didapat akan seperti berikut ini

Soal Ketujuh

Langsung saja untuk kolom "Merek Sepeda Motor" kita menggunakan rumus
=VLOOKUP(E5;$A$18:$C$26;2;FALSE)

Untuk kolom "Harga Cash" kita menggunakan rumus
=VLOOKUP(E5;$A$18:$C$26;3;FALSE)

Untuk kolom "Angsuran Pokok" kita menggunakan rumus
=G5/D5

Berikutnya kolom "Angsuran Bunga" kita menggunakan formula
=G5*30%

Untuk kolom "Angsuran Total" kita menggunakan formula
=H5+I5

Terakhir yakni kolom "Harga Kredit" kita menggunakan rumus
=J5*D5

And the result is being like this

Soal Kedelapan

Pertama adalah kita menjawab kolom "Jabatan Struktural", kita menggunakan formula
=IF(C2="I";"STAF";IF(C2="II";"KASUBAG";IF(C2="III";"KABAG";"DIREKSI")))

Yang kedua adalah kolom "Gaji Pokok", kita menggunakan rumus
=VLOOKUP(C2;$A$19:$D$23;2)

Yang ketiga adalah kolom "Tunjangan Istri", kita menggunakan rumus
=IF(D2="NIKAH";VLOOKUP(C2;$A$19:$D$23;4);0)

Berikutnya kolom "Tunjangan Anak" kita menggunakan formula
=IF(AND(D2="NIKAH";E2<=3);VLOOKUP(C2;$A$20:$D$23;3)*E2;IF(AND(D2="NIKAH";E2>3);VLOOKUP(C2;$A$20:$D$23;3)*4;0))

Untuk kolom "Tunjangan Struktural" kita menggunakan rumus
=HLOOKUP(F2;$F$14:$J$15;2;FALSE)

Kolom berikutnya yakni kolom "Tunjangan Transport" kita menggunakan rumus
=HLOOKUP(F2;$F$18:$J$19;2;FALSE)

Untuk kolom "Total Gaji" kita menggunakan rumus
=SUM(G2:J2)

Untuk kolom "Total Gol I" kita menggunakan fungsi COUNTIF dengan rumus
=COUNTIF(C2:C11;"I")

Untuk kolom "Total Gol II" kita menggunakan rumus
=COUNTIF(C2:C11;"II")

Untuk kolom "Total Gol III" kita menggunakan rumus
=COUNTIF(C2:C11;"III")

Dan untuk kolom terakhir yakni kolom "Total Gol IV" kita menggunakan rumus
=COUNTIF(C2:C11;"IV")

Maka hasil akhir yang kita dapat adalah sebagai berikut

Soal Kesembilan

Untuk kolom pertama "Program Studi" kita menggunakan rumus
=IF(LEFT(A8;3)="MNJ";"Manajemen";IF(LEFT(A8;3)="AKT";"Akuntansi";IF(LEFT(A8;3)="ISP";"Ilmu Ekonomi Dan Studi Pembangunan";IF(LEFT(A8;3)="D-3";"D-3 Keuangan Dan Perbankan"))))

Untuk kolom kedua yaitu "Jumlah Mahasiswa (TEXT)" kita menggunakan rumus
=TEXT(MID(A8;5;2);"00")

Berikutnya untuk kolom "Jumlah Mahasiswa (Number)" kita menggunakan rumus
=VALUE(C8)

Untuk kolom "Kelas" kita menggunakan rumus
=RIGHT(A8;1)

Kolom "Kelas Lengkap" kita menggunakan rumus
=CONCATENATE(B8;"-";E8)

Terakhir untuk kolom "Ruang Kelas" kita menggunakan rumus
=CONCATENATE("Ruang Praktikum";" ";E8)

Maka hasil akhir yang kita dapat akan menjadi seperti ini


Itu dia beberapa soal yang menggunakan fungsi String, Vlookup, dan Hlookup. Semoga tulisan ini dapat membantu teman-teman semua yang merasa kebingungan dengan ketiga rumus tersebut ya :)

Komentar

Postingan populer dari blog ini

Kasus Pelanggaran Etika, Privasi, dan Keamanan Informasi

Dewasa ini, kasus pelanggaran etika, privasi, dan keamanan informasi kerap bermunculan karena ulah orang-orang yang tidak bertanggung jawab. Di era teknologi modern, informasi bisa sangat cepat menyebar sehingga kasus-kasus seperti ini pun semakin meluas.  Lalu kasus seperti apakah yang pernah melanggar etika, privasi dan keamanan informasi? Tulisan kali ini akan membahasnya. Let's check it out! 1.      Isu Etika Pembobolan Situs KPU Pada hari Sabtu, 17 April 2004, Dani Firmansyah (25 th), konsultan Teknologi Informasi (TI) PT Danareksa di Jakarta berhasil membobol situs milik Komisi Pemilihan Umum (KPU) di  http://tnp.kpu.go.id  dan mengubah nama-nama partai di dalamnya menjadi nama-nama unik seperti Partai Kolor Ijo, Partai Mbah Jambon, Partai Jambu, dan lain sebagainya. Dani menggunakan teknik SQL Injection (pada dasarnya teknik tersebut adalah dengan cara mengetikkan string atau perintah tertentu di address bar browser) unt...

Cara Membuat Table, Kolom Koran, Space Number, dan Daftar Isi di Ms. Word

Membuat table, kolom koran, space number, dan daftar isi mungkin terbilang mudah bagi kebanyakan orang. Namun bagi yang belum tahu, tidak usah bingung! Kalian bisa mengikuti cara-cara di bawah ini. Check it out! 1. Cara Membuat Table Pertama pilih menu insert yang berada pada menu bar, lalu pilih menu table Setelah itu kalian bisa langsung membuat table dengan memilih jumlah kolom dan baris yang sesuai keinginan kita karena sudah tersedia di menu tersebut. Namun jika ingin membuat kolom dan baris yang berbeda, kalian bisa mengklik insert table hingga akan muncul tampilan seperti ini Kalian bisa langsung mengisikan jumlah kolom dan baris sesuai keinginan kalian. Jika sudah sesuai, tinggal klik ok saja. Dan table mu sudah siap untuk digunakan! 2. Cara Membuat Kolom Koran Ketika kalian melihat koran mungkin kalian akan bertanya-tanya, bagaimana cara membuat kolom-kolom berikut dengan rapi? Caranya mudah sekali Pertama yang harus kalian lakukan adala...