Cara Menggunakan Rumus Kombinasi Excel dengan Contoh Kasus Soal

Rumus dalam Microsoft excel pada tulisan ini, menggunakan beberapa rumus kombinasi yang cukup lengkap dengan penggabungan beberapa fungsi diantaranya: Vlookup, Hlookup,Left, Mid dan If.

Fungsi dan rumus-rumus kombinasi yang dibuat ini merupakan contoh Soal Excel yang sudah saya share dan bisa anda download pada tulisan Tutorial Microsoft Excel, yaitu “Daftar Pembelian Tiket Pesawat Save Air”.

Pada contoh soal tersebut, merupakan sebuah studi kasus yang sudah diberikan jawabannya, tetapi tidak diberikan proses pengerjaannya. Untuk itu atas permintaan pengunjung blog ini, saya mencoba menuliskan proses penggunaan rumus dan fungsi excel pada Daftar Pembelian Tiket Pesawat Save Air.

Oke, langsung saja, Contoh Soal yang harus anda diselesaikan tampak seperti gambar di bawah ini:

soal rumus kombinasi pada excel

Soal di atas, terdiri dari 4 tabel, dimana ada 1 tabel utama yang harus anda selesaikan pada kolom-kolom yang kosong. Lalu ada 3 tabel referensi/tabel bantu yaitu: tabel menu makanan, tabel tujuan dan tabel kelas.

Bagi anda yang ingin langsung praktek, silahkan ketikkan soal di atas….

Sekedar saran…

Alangkah lebih baik, bila anda menyesuaikan letak kolom dan baris sesuai dengan soal gambar yang ada di atas…

Tujuannya Apa?

Tentunya untuk memudahkan anda dalam proses belajar Microsoft excel…

Versi Microsoft office yang digunakan?

Silahkan menggunakan versi ms. Office 2007, 2010, 2013 atau yang paling terbaru tidak masalah, semua bisa anda gunakan..

Oke, untuk mengerjakan soal excel dari gambar di atas, ada keterangan petunjuk pengerjaannya. Petunjuk keterangan soal biasanya juga diberikan bila anda mengerjakan soal excel di sekolah, kampus ataupun lembaga-lembaga pendidikan lainnya.

Berikut ini keterangan soalnya:

Keterangan:

  1. Buat tabel di atas dengan menggunakan Microsoft Excel
    Data Masukan : Tgl Keberangkatan, Kode Tiket, Nama Tiket, Tgl Pembelian
    Keterangan Kode Tiket, Contoh: BBBY-P1-035
    BBB -> Menentukan Tujuan
    Y-> Menentukan Kelas
    P1-> Menentukan Menu
  2. Isikan rumus untuk kolom-kolom :
    a. Tujuan
    – Diambil dari Tabel Tujuan sesuai dengan Kode Tiket
    b. Harga
    – Diambil dari Tabel Tujuan sesuai dengan Kode Tiket
    c. Kelas
    – Diambil dari Tabel Kelas sesuai dengan Kode Tiket
    d. Menu
    – Diambil dari Tabel Menu Makanan sesuai dengan Kode Tiket
    e. Diskon
    – Jika tiket dibeli lebih dari 60 hari sebelumnya, maka mendapat diskon. Jumlah Diskon diambil dari Tabel Kelas sesuai Kode Tiket dikali Harga
    f. Harga Jual
    – Diambil dari Harga dikurangi Diskon ditambah Harga Menu. Harga Menu diambil dari Tabel Menu Makanan sesuai Kode Tiket
    g. Total Pembelian
    – Jumlah keseluruhan Harga Jual
    h. Jumlah Pembeli Kelas VIP
    – Jumlah Pembelian Tiket Kelas VIP

Sesuai dengan petunjuk soal yang diberikan, berikut langkah pengerjaannya:

1. Tujuan

Untuk mengisi kolom tujuan, gunakanlah rumus kombinasi fungsi vlookup dan fungsi left. Mengapa menggunakan fungsi vlookup? Karena kita akan mengambil data dalam bentuk vertikal dari tabel referensi (tabel bantu) yaitu tabel tujuan. Sedangkan fungsi Left untuk mengambil tiga karakter dari sebelah kiri kolom kode tiket (tabel utama).

Jawaban untuk kolom tujuan akan terlihat seperti gambar berikut ini:

mengisi kolom tujuan

Keterangan dari gambar jawaban di atas:

Angka 1 merupakan jawaban dari kolom tujuan yang diisikan di CELL F5 yaitu:

=VLOOKUP (LEFT(C5,3),$B$23:$D26,2)

Penulisan rumus kombinasi diatas, tentunya Sesuai dengan penulisan fungsi vlookup

=VLOOKUP (nilai kunci, letak tabel, nomor kolom)

Hasil pengerjaan soal dari angka 1 didapatkan dari angka 2 dan 3, bila dijabarkan:

  • Angka 2 dijadikan sebagai nilai kunci (C5) pada fungsi vlookup, karena kita hanya memerlukan tiga karakter untuk menentukan tujuan dari sebelah kiri maka penulisannya nilai kuncinya adalah Left (C5,3).
  • Angka 3 digunakan sebagai letak tabel dan nomor kolom pada fungsi VLOOKUP, letak tabel yang diambil dari tabel bantu/referensi yaitu tabel tujuan. Untuk mengisinya, blok dari cell B23 sampai D26. Jangan lupa untuk menambahkan tanda dollar ($) untuk mengabsolutkan, menjadi seperti ini $B$23:$D$26.  Tujuan pemberian dollar ini sudah sering saya tulis dari berbagai artikel yang mengenai excel di blog ini, yaitu agar pada saat anda mengisi jawaban di cell F5 kemudian mendrag /copy ke bawah untuk mengisi jawaban pada kolom dibawahnya, hasilnya tidak menjadi #N/A.

Nomor kolom isikan dengan 2, karena letak kolom tujuan pada tabel tujuan, berada pada kolom kedua.

Jawaban Lengkapnya untuk kolom tujuan, terlihat seperti gambar di bawah ini:

hasil kolom tujuan 2

Masih bingung dengan fungsi vlookup? Silahkan baca terlebih dahulu penjelasan mengenai fungsi vlookup dan hlookup disertai gambar

2. Harga

Untuk mengisi harga, sesuai dengan instruksi soal yang diberikan dimana harga diambil dari Tabel Tujuan sesuai dengan Kode Tiket.  Instruksi yang diberikan untuk menjawab kolom harga, sama persis dengan kolom tujuan sebelumnya.

Proses pengerjaannya bisa anda lihat seperti dibawah ini:

engerjaan kolom harga

Sama bukan?

Yup, untuk mengisi harga masih menggunakan rumus kombinasi dengan menggunakan dua fungsi, yaitu fungsi vlookup dan left.

Tapi ada satu yang membedakan jawaban yang telah digambarkan di atas, yaitu pada nomor kolom. Nomor kolom menjadi 3 bukan 2, karena pada tabel tujuan (tabel bantu yang terletak di bawah, diberi lingkaran dengan angka 3), harga terletak pada kolom ketiga, sesuai dengan panah yang saya berikan pada gambar.

Jadi untuk menjawab harga, rumus kombinasinya adalah:

=VLOOKUP (LEFT(C5,3),$B$23:$D26,3)

Hasil lengkap dari jawaban di atas, akan tampak seperti ini:

hasil pengerjaan kolom harga

3. Kelas

Untuk mengisi kolom kelas, anda bisa mengambil dari tabel kelas sesuai dengan kode tiket. Untuk pengerjaannya bisa anda lihat seperti di bawah ini:
pengerjaan kolom kelas

 Untuk mengisi kelas, anda bisa menggunakan rumus kombinasi dari fungsi Vlookup dan mid. Fungsi mid digunakan untuk mengambil sejumlah karakter dari posisi tertentu sebanyak yang diinginkan. Bentuk penulisannya:

=Mid(text, start_num, num_chars)

Atau Sama juga seperti ini:

=MID(data_yang_ diambil, posisi_awal, jumlah_pengambilan)

Belum Paham tentang fungsi mid, silahkan baca tulisan saya sebelumnya: Mengenal Fungsi Microsoft Excel (Statistik, Formula, If, Logika, Tabel).

Oke, jadi jawaban untuk kolom harga:

=VLOOKUP(MID(C5, 4,1),$G$23:$I$25,2)

Penulisan rumus kombinasi untuk kelas, tentunya Sesuai dengan penulisan fungsi vlookup, dibawah ini:

=VLOOKUP (nilai kunci, letak tabel, nomor kolom)

Penjelasannya?

Angka 1 pada gambar di atas merupakan jawaban yang didapatkan dari Angka 2 dan Angka 3.

Angka 2 merupakan nilai kunci yang terletak pada Cell C5, yaitu menggunakan fungsi Mid (C5,4,1).

Mengapa mesti menggunakan mid?

Karena kita ingin mengambil karakter Y yang ada pada Kode Tiket BBBY, yang terletak pada urutan keempat sebanyak 1 karakter.

Angka 3 merupakan letak tabel dan nomor kolom, saya rasa tidak perlu dijelaskan lagi ya? Karena proses penjelasannya sama dengan letak tabel dan nomor kolom pada jawaban sebelumnya.

Selanjutnya tinggal anda drag/copy ke bawah dari cell H5 untuk mengisi kolom kelas yang masih kosong.

Hasil lengkap dari jawaban kolom kelas, seperti gambar di bawah ini:

pengerjaan kolom kelas

4. Menu

Untuk mengisi kolom menu; diambil dari tabel menu makanan (tabel bantu) sesuai dengan kode tiket. Bila dilihat dari tabel menu makanan, posisi data yang akan diambil berbentuk horizontal, untuk itu kita bisa menggunakan fungsi Hlookup. Pengerjaannya bisa Anda lihat seperti di bawah ini:
pengerjaan kolom menu

Pada gambar di atas, untuk mengisi kolom menu di Cell I5  (kotak dengan angka1), rumus kombinasi yang digunakan adalah Fungsi Hlookup dan Mid.

Penulisan Fungsi HLOOKUP

=HLOOKUP(lookup_value,tabel_array,row_index_num)

atau

=HLOOKUP(nilai kunci, letak tabel, nomor baris)

Hasil yang didapatkan dari rumus HLOOKUP dan MID, adalah :

=HLOOKUP(MID(C5,6,2),$C$17:$E$19,2)

Pada fungsi HLOOKUP jawaban di atas, fungsi mid (C5,6,2) sebagai nilai kunci. C17:E19 merupakan letak tabel dan angka 2 merupakan nomor baris.

Hasil lengkap jawaban dari kolom menu, seperti dibawah ini:
hasil pengerjaan kolom menu

5. Diskon

Petunjuk soal untuk mengisi kolom diskon adalah jika tiket dibeli lebih dari 60 hari sebelumnya, maka mendapat diskon. Jumlah Diskon diambil dari Tabel Kelas sesuai Kode Tiket dikali Harga.
Proses pengerjaan untuk Diskon, bisa anda lihat seperti di bawah ini:

pengerjaan kolom diskon

Untuk mengisi kolom Diskon Pada Cell J5, anda bisa menggunakan rumus kombinasi Fungsi IF dan VLOOKUP, Selain itu rumus pengurangan,dan perkalian juga digunakan untuk menjawab diskon, seperti gambar di atas.

Hasil Pengerjaan rumus menjadi seperti ini:

=IF ($D$3-E5>60,G5*VLOOKUP(MID(C5,4,1),$G$23:$I$25,3),0)

Setelah cell J5 sudah anda isi, silahkan copy/drag ke bawah sampai kolom diskon ke isi semuanya. Hasilnya akan tampak seperti dibawah ini:

hasil pengerjaan kolom diskon

6. Harga Jual

Untuk mengisi kolom harga jual, anda bisa mengambil harga dikurangi diskon ditambah harga Menu. Harga menu diambil dari tabel menu makanan sesuai kode tiket.

Proses pengerjaan untuk harga jual, seperti di bawah ini:

pengerjaan harga jualKolom harga jual pada gambar di atas, diisi menggunakan rumus kombinasi pengurangan, penjumlahan, Hlookup dan juga Mid..

=G5-J5+(HLOOKUP(MID(C5,6,2),$B$17:$E$19,3))

Setelah cell K5 sudah anda isi, silahkan copy/drag ke bawah sampai kolom harga jual ke isi semuanya. Hasil harga jual selengkapnya:

hasil pengerjaan harga jual

7. Total Pembelian

Total pembelian, didapatkan dari jumlah keseluruhan harga jual, untuk itu gunakan fungsi sum, dengan cara meletakkan kursor di K15, lalu blok area dari K5 sampai K14, menjadi seperti ini:

=SUM(K5:K14)

Selain itu anda bisa mengunakan AutoSum, untuk menghitung penjumlahan/total secara langsung.

pengerjaan total pembelian

8. Jumlah Pembeli Kelas VIP

Untuk mengisi jumlah pembeli kelas VIP anda bisa menggunakan fungsi COUNTIF. COUNTIF adalah salah satu fungsi statistik yang digunakan untuk menghitung jumlah sel yang memenuhi kriteria; misalnya, untuk menghitung berapa kali kelas “VIP” muncul dalam daftar Kelas
penulisan rumusnya:

=COUNTIF(range, critea)

Hasil Pengerjaan:

=COUNTIF(H5:H14,”VIP”)

pengerjaan jumlah pembeli

Setelah mengerjakan total pembelian, akhirnya selesai sudah Cara menggunakan rumus Kombinasi Excel dengan Contoh Kasus Soal….

Hasil lengkap dari penggerjaan awal sampai akhir, tampak seperti gambar di bawah ini:
hasil lengkap seluruhnya

Semoga bermanfaat..

Bagikan Yuuk!

    16 thoughts on “Cara Menggunakan Rumus Kombinasi Excel dengan Contoh Kasus Soal

    1. pinel gwijangge April 29, 2016 at

      rumus ini aku ngk gerti tolong di perjelaskan satu perstu kch??

      • umar danny April 29, 2016 at

        @pinel: coba dibaca lagi tahap-demi tahap, atau baca panduannya dari awal, pada kategori panduan belajar ms.excel

    2. aulia putri August 5, 2016 at

      klo problem saya itu tabel bantunya dipisah jdi 2 tapi jawabannya dijadiin satu kolom gimana? misal didalem kolom tujuan itu harusnya jawabannya harusnya gini misal : (Yogyakarta jakarta – Paket A) nah rumusnya itu kan harusnya ditengahnya ditambah &”-“& trus cara nyari harganya itu gimana ya? pake rumus apa?

      • umar danny August 5, 2016 at

        @aulia: tinggal arahkan saja, lalu klik dimana tabel bantu/referensi itu berada..

    3. drmr August 18, 2016 at

      =IF($D$3-E5>60,G5*VLOOKUP(MID(C5,4,1),$G$23:$I$25,3),0) angka 0 diujung ini maksudnya apa ya mas? tolong dijelaskan, terimakasih..

    4. drmr August 18, 2016 at

      untuk pengisian diskon cell J5

      • umar danny August 20, 2016 at

        @drmr: itu menggunakan fungsi IF, angka 0 itu jika salah (ga ada diskonnya)..

        • Raditz November 22, 2016 at

          Maksudnya jika salah apa ya mas? soalnya masih blm ngerti anka 0 itu. pas saya coba ilangin yang ga dapet diskon jadi FALSE.. mohon pencerahannya mas

          • umar danny November 22, 2016 at

            @raditz: Coba pahami dulu tentang fungsi IF…dimana penulisan fungsi IF itu =IF(tes_logika;nilai_jika_benar;nilai_jika_salah)…
            Dalam hal ini, angka 0 digunakan untuk nilai jika salah ATAU tidak mendapatkan diskon… silahkan Anda baca dulu tulisan saya sebelumnya tentang fungsi IF di blog ini, saya sudah pernah membuatnya.

    5. sari October 17, 2016 at

      gmna rumus Kombinasi vlook dgn perkalian di cell lain ?

      • umar danny October 17, 2016 at

        @sari:proses pengerjaannya sama saja, tinggal mengarahkan ke cell yang menjadi referesinya, walau cell tersebut terdapat dalam sheet yang berbeda..

    6. irfan November 1, 2016 at

      kalau mencari Total penghasilan gmana ya? Dari gaji pokok, tunjangan, bonus, masa kerja, golongan.. Kok aku coba pakek formula harga hasilnya value

      • umar danny November 1, 2016 at

        @irfan: untuk total penghasilan gunakan fungsi SUM mas, silahkan baca tulisan saya mengenai SUM di blog ini ada kok caranya, ATAU pada tulisan ini SUM juga digunakan untuk menghitung total pembelian..

    7. Dika November 22, 2016 at

      Terimakasih Gan, Sangat Bermanfaat sekali contoh soalnya, bisa buat latihan Ujian Paket Program Niaga nanti malam ini. heuheu Keep Posting ya ! 🙂

    8. fadil December 7, 2016 at

      mas kok saya gagal di mid yg kelas yahhhh, ada kek peringatan gt muncul pas mau klik tabel kelasnya abis ketik rumus mid ??

    9. fadil December 7, 2016 at

      owhhhhhh pasntes ga bisaa soalnya agan pake , pas saya ubh jadi ; seperti rumus yg sebleumnya sy pakai jadi bisaa thanks gannnn

    Leave a Reply

    Name *
    Email *
    Website