Misal ada data seperti gambar dibawah. Tarif pengiriman barang menggunakan jasa expedisi JNE.
Berapakah ongkos kirim masing-masing alamat berikut, jika pengiriman barang menggunakan JNE Reguler.
Fungsi excel manakah yang akan digunakan untuk mencari ongkos kirim. Apakah fungsi VLOOKUP bisa digunakan atau harus menggunakan fungsi lain dengan berbagai trik tambahan yang harus dilakukan?
Solusi dengan fungsi VLOOKUP dan kolom bantuan
Solusi ini adalah solusi paling mudah tetapi menghasilkan tampilan paling jelek :). Kenapa paling jelek?, karena adanya penambahan kolom bantuan.
Fungsi VLOOKUP hanya bisa mencari 1 data lookup_value. Jika ada lebih dari 1 data lookup_value maka digunakan trik dengan menambah kolom bantuan. Kolom bantuan berfungsi untuk menghasilkan data baru hasil gabungan dari beberapa data.
Dari contoh diatas kolom bantuan digunakan untuk menggabung data propinsi, kabupaten dan kecamatan menjadi satu dikolom baru. Kolom bantuan tersebut akan digunakan sebagai data lookup_value baru.
Kolom bantuan yang harus ditambahkan ada dua. Kolom bantuan pertama ditambahkan ditabel tarif ongkos kirim dan kolom bantuan kedua ditambahkan didata transaksi.
Kolom bantuan ditabel tarif harus berada disebelah kiri informasi tarif. Gambar pertama menunjukkan informasi tarif berada dikolom D. Sisipkan 1 kolom baru untuk menampung kolom bantuan. Hasilnya seperti gambar dibawah.
Gambar diatas menunjukkan kolom bantuan diletakkan dikolom paling kiri.
Buat formula untuk menggabungkan data propinsi, kabupaten dan kecamatan.
Formula yang digunakan adalah sebagai berikut:
=B2&C2&D2
Kopi formula tersebut untuk semua data yang ada dibawahnya.
Hasilnya seperti gambar dibawah.
Kolom bantuan didata transaksi bisa diletakkan diposisi manapun, bisa disebalah kanan atau kiri data ongkos kirim yang akan dicari.
Buat formula untuk menggabungkan data propinsi, kabupaten dan kecamatan. Hasilnya seperti gambar dibawah.
Kolom bantuan diletakkan dikanan kolom ongkos kirim yang akan dicari. Formula yang digunakan mirip dengan kolom bantuan sebelumnya, menggunakan tanda “&” (ampersand) untuk menggabung data propinsi, kabupaten dan kecamatan.
Setelah data kolom bantuan siap saatnya merangkai fungsi VLOOKUP.
Letakkan kursor dicell F2. Ketik formula untuk fungsi VLOOKUP.
Parameter lookup_value diisi dengan cell yang berisi gabungan data propinsi, kabupaten dan kecamatan. Isi parameter ini dengan cell G2.
Parameter table_array diisi dengan range A2:G134, buat menjadi absolute reference. Range ini berisi informasi tarif ongkos kirim untuk beberapa daerah, dengan kolom paling kiri adalah kolom bantuan gabungan data propinsi, kabupaten dan kecamatan.
Parameter col_index_num diisi dengan angka 5, yaitu posisi kolom tarif JNE regular dirange A2:G134.
Parameter range_lookup diisi dengan FALSE, karena akan mencari data yang sama.
Formula yang dihasilkan seperti berikut:
=VLOOKUP(G2,’Tarif JNE’!$A$2:$G$139,5,FALSE)
Kopi formula dicell F2, kemudian paste dirange F3:F9. Hasilnya seperti gambar dibawah.
Tarif ongkos kirim masing-masing alamat bisa diketahui.
Solusi dengan fungsi INDEX, fungsi MATCH dan array formula
Solusi ini tidak membutuhkan kolom bantuan tetapi formula yang dibuat harus menggunakan array formula. Selain itu fungsi yang digunakan bukan fungsi VLOOKUP tetapi gabungan fungsi INDEX dan fungsi MATCH.
Letakkan kursor dicell F2. Ketik formula untuk fungsi INDEX.
Parameter array fungsi INDEX diisi dengan range D2:D139, range ini berisi informasi tarif ongkos kirim JNE Reguler (worksheet “Tarif JNE”). Buat menjadi absolute reference.
Parameter row_num fungsi INDEX diisi dengan fungsi MATCH.
Parameter lookup_value fungsi MATCH diisi dengan gabungan data propinsi, kabupaten dan kecamatan didata transaksi (worksheet “Ongkos Kirim”).
Formula yang dihasilkan menjadi seperti berikut:
=INDEX(‘Tarif JNE’!$D$2:$D$139
,MATCH(‘Ongkos Kirim’!E2&’Ongkos Kirim’!D2&’Ongkos Kirim’!C2
Parameter lookup_array fungsi MATCH diisi dengan gabungan data propinsi, kabupaten dan kecamatan yang ada diworksheet “Tarif JNE”. Buat semuanya menjadi absolute reference.
Formula yang dihasilkan menjadi seperti berikut:
=INDEX(‘Tarif JNE’!$D$2:$D$139
,MATCH(‘Ongkos Kirim’!E2&’Ongkos Kirim’!D2&’Ongkos Kirim’!C2
,’Tarif JNE’!$A$2:$A$139&’Tarif JNE’!$B$2:$B$139&’Tarif JNE’!$C$2:$C$139
Parameter match_type diisi dengan angka 0, karena mencari data yang sama
Parameter column_num fungsi INDEX diisi dengan angka 1
Formula akhir yang dihasilkan menjadi seperti berikut:
=INDEX(‘Tarif JNE’!$D$2:$D$139
,MATCH(‘Ongkos Kirim’!E2&’Ongkos Kirim’!D2&’Ongkos Kirim’!C2
,’Tarif JNE’!$A$2:$A$139&’Tarif JNE’!$B$2:$B$139&’Tarif JNE’!$C$2:$C$139,0),1)
Jika sudah tekan tombol CTRL+SHIFT+ENTER bersamaan, karena formula menggunakan array formula
Kopi formula dicell F2, kemudian paste dirange F3:F9. Hasilnya seperti gambar dibawah.
Tarif ongkos kirim masing-masing alamat bisa diketahui.
Solusi dengan fungsi SUMIFS
Solusi ini tidak membutuhkan kolom bantuan maupun array formula, tetapi membutuhkan Excel 2007 atau yang lebih baru. Selain itu solusi ini hanya bisa digunakan jika data yang dicari berupa angka.
Tarif ongkos kirim berupa angka, jasi solusi ini bisa digunakan untuk mencari ongkos kirim masing-masing alamat.
Letakkan kursor dicell F2. Ketik formula untuk fungsi SUMIFS.
Parameter sum_range diisi dengan range D2:D139 yang ada diworksheet “Tarif JNE”, range yang berisi informasi tarif untuk JNE Regular. Buat menjadi absolute reference.
Formula yang dihasilkan menjadi seperti berikut:
=SUMIFS(‘Tarif JNE’!$D$2:$D$139,
Parameter criteria_range1 diisi dengan range A2:A139 yang ada diworksheet “Tarif JNE”, range yang berisi informasi data propinsi.
Parameter criteria1 diisi dengan cell E2 yang ada diworksheet “Ongkos Kirim”, cell yang berisi informasi data propinsi.
Formula yang dihasilkan menjadi seperti berikut:
=SUMIFS(‘Tarif JNE’!$D$2:$D$139
,’Tarif JNE’!$A$2:$A$139,’Ongkos Kirim’!E2,
Parameter criteria_range2 diisi dengan range B2:B139 yang ada diworksheet “Tarif JNE”, range yang berisi informasi data kabupaten.
Parameter criteria2 diisi dengan cell D2 yang ada diworksheet “Ongkos Kirim”, cell yang berisi informasi data kabupaten.
Formula yang dihasilkan menjadi seperti berikut:
=SUMIFS(‘Tarif JNE’!$D$2:$D$139
,’Tarif JNE’!$A$2:$A$139,’Ongkos Kirim’!E2
,’Tarif JNE’!$B$2:$B$139,’Ongkos Kirim’!D2,
Parameter criteria_range3 diisi dengan range C2:C139 yang ada diworksheet “Tarif JNE”, range yang berisi informasi data kecamatan.
Parameter criteria3 diisi dengan cell C2 yang ada diworksheet “Ongkos Kirim”, cell yang berisi informasi data kecamatan.
Formula lengkap yang dihasilkan menjadi seperti berikut:
=SUMIFS(‘Tarif JNE’!$D$2:$D$139
,’Tarif JNE’!$A$2:$A$139,’Ongkos Kirim’!E2
,’Tarif JNE’!$B$2:$B$139,’Ongkos Kirim’!D2
,’Tarif JNE’!$C$2:$C$139,’Ongkos Kirim’!C2)
Kopi formula dicell F2, kemudian paste dirange F3:F9. Hasilnya seperti gambar dibawah.
Tarif ongkos kirim masing-masing alamat bisa diketahui.
Mana solusi terbaik?
Jika data yang dicari berupa angka maka solusi dengan fungsi SUMIFS adalah yang terbaik.
Solusi ini tidak memerlukan kolom bantuan maupun array formula. Kelemahannya, solusi ini harus menggunakan excel 2007 atau yang lebih baru.
Jika data yang dicari berupa text maka solusi dengan fungsi INDEX, fungsi MATCH dan array formula adalah yang terbaik.
Solusi ini tidak memerlukan kolom bantuan, tetapi membutuhkan array formula, agak sedikit komplek cara merangkainya, tetapi solusi ini mempunyai kelebihan bisa digunakan untuk mencari data berupa angka ataupun text.
Jika kolom bantuan bukan suatu masalah maka solusi dengan fungsi VLOOKUP dan kolom bantuan adalah yang terbaik. Solusi ini bisa digunakan untuk mencari data text ataupun angka. Kelemahan solusi ini hanya soal tampilan saja.
Selamat memilih :).
Download File Latihan
File latihan bisa didownload disini
Array formula dibahas dengan detail dibab 12 buku RUMUS EXCEL. LIHAT DAFTAR ISI
Kami juga mempunyai 12 video tutorial tentang array formula dikursus online rumus excel. Informasi detail tentang kursus online rumus excel bisa dilihat disini.
