Cara Memakai Rumus Kombinasi Excel Dengan Rujukan Kasus Soal

Rumus Microsoft excel pada goresan pena “cara memakai rumus kombinasi excel dengan pola kasus soal” yang sedang anda baca ini, memakai beberapa rumus kombinasi yang cukup lengkap dengan penggabungan beberapa fungsi diantaranya: Vlookup, Hlookup,Left, Mid dan If.

Fungsi dan rumus-rumus kombinasi yang dibentuk ini merupakan pola Soal Excel yang sudah aku share dan sanggup anda d0wnl0ad pada goresan pena Tutorial Microsoft Excel, yaitu “Daftar Pembelian Tiket Pesawat Save Air”.

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

Oke, eksklusif saja, Contoh Soal yang harus anda diselesaikan tampak menyerupai gambar di bawah ini:

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 sajian makanan, tabel tujuan dan tabel kelas.

Bagi anda yang ingin eksklusif 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 berguru Microsoft excel…

Versi Microsoft office yang digunakan?

Silahkan memakai versi ms. Office 2007, 2010, 2013 atau yang paling terbaru tidak masalah, semua sanggup 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 memakai 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 menerima 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 memakai fungsi vlookup? Karena kita akan mengambil data dalam bentuk vertikal dari tabel tumpuan (tabel bantu) yaitu tabel tujuan. Sedangkan fungsi Left untuk mengambil tiga abjad dari sebelah kiri kolom kode tiket (tabel utama).

Jawaban untuk kolom tujuan akan terlihat menyerupai gambar berikut ini:

Keterangan dari gambar balasan di atas:

Angka 1 merupakan balasan 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, alasannya yakni kita hanya memerlukan tiga abjad untuk memilih tujuan dari sebelah kiri maka penulisannya nilai kuncinya yakni Left (C5,3).
• Angka 3 dipakai 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 hingga D26. Jangan lupa untuk menambahkan tanda dollar ($) untuk mengabsolutkan, menjadi menyerupai ini $B$23:$D$26.  Tujuan dukungan dollar ini sudah sering aku tulis dari banyak sekali artikel yang mengenai excel di blog ini, yaitu supaya pada ketika anda mengisi balasan di cell F5 kemudian mendrag /copy ke bawah untuk mengisi balasan pada kolom dibawahnya, hasilnya tidak menjadi #N/A.
• Nomor kolom isikan dengan 2, alasannya yakni letak kolom tujuan pada tabel tujuan, berada pada kolom kedua.

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

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

2. Harga

Untuk mengisi harga, sesuai dengan kode 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 sanggup anda lihat menyerupai dibawah ini:

Sama bukan?

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

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

Jadi untuk menjawab harga, rumus kombinasinya adalah:

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

Hasil lengkap dari balasan di atas, akan tampak menyerupai ini:

3. Kelas

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

Untuk mengisi kelas, anda sanggup memakai rumus kombinasi dari fungsi Vlookup dan mid. Fungsi mid dipakai untuk mengambil sejumlah abjad dari posisi tertentu sebanyak yang diinginkan. Bentuk penulisannya:

=Mid(text, start_num, num_chars)

Atau Sama juga menyerupai ini:

=MID(data_yang_ diambil, posisi_awal, jumlah_pengambilan)

Belum Paham perihal fungsi mid, silahkan baca goresan pena aku sebelumnya: Mengenal Fungsi Microsoft Excel (Statistik, Formula, If, Logika, Tabel).

Oke, jadi balasan 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 balasan yang didapatkan dari Angka 2 dan Angka 3.

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

Mengapa mesti memakai mid?

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

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

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

Hasil lengkap dari balasan kolom kelas, menyerupai gambar di bawah ini:

4. Menu

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

Pada gambar di atas, untuk mengisi kolom sajian di Cell I5  (kotak dengan angka1), rumus kombinasi yang dipakai yakni 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, yakni :

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

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

Hasil lengkap balasan dari kolom menu, menyerupai dibawah ini:

5. Diskon

Petunjuk soal untuk mengisi kolom diskon yakni bila tiket dibeli lebih dari 60 hari sebelumnya, maka menerima diskon. Jumlah Diskon diambil dari Tabel Kelas sesuai Kode Tiket dikali Harga.
Proses pengerjaan untuk Diskon, sanggup anda lihat menyerupai di bawah ini:

Untuk mengisi kolom Diskon Pada Cell J5, anda sanggup memakai rumus kombinasi Fungsi IF dan VLOOKUP, Selain itu rumus pengurangan,dan perkalian juga dipakai untuk menjawab diskon, menyerupai gambar di atas.

Hasil Pengerjaan rumus menjadi menyerupai 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 hingga kolom diskon ke isi semuanya. Hasilnya akan tampak menyerupai dibawah ini:

6. Harga Jual

Untuk mengisi kolom harga jual, anda sanggup mengambil harga dikurangi diskon ditambah harga Menu. Harga sajian diambil dari tabel sajian kuliner sesuai kode tiket.

Proses pengerjaan untuk harga jual, menyerupai di bawah ini:

Kolom harga jual pada gambar di atas, diisi memakai 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 hingga kolom harga jual ke isi semuanya. Hasil harga jual selengkapnya:

7. Total Pembelian

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

=SUM(K5:K14)

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

8. Jumlah Pembeli Kelas VIP

Untuk mengisi jumlah pembeli kelas VIP anda sanggup memakai fungsi COUNTIF. COUNTIF yakni salah satu fungsi statistik yang dipakai 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”)

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

Hasil lengkap dari penggerjaan awal hingga akhir, tampak menyerupai gambar di bawah ini:

Semoga bermanfaat..

Sumber aciknadzirah.blogspot.com

Share this post

Berlangganan update artikel terbaru via email: