VLOOKUP dengan 2 Kriteria , Emang bisa?

Halo semua.... 

Masih berurusan dengan manggil-manggilin data nih...#asiik

Kali ini kebutuhannya adalah manggil data dengan 2 kriteria. 

Kayak gimana tuh maksudnya?

Biar lebih jelas, saya coba ilustrasikan...

Jadi saya mempunyai raw data berupa output produksi sepatu per tanggal dan per karyawan.

Dan diminta untuk menjabarkan data tabel yang menunjukkan karyawan tersebut memproduksi apa di setiap tanggal.

Berikut raw data (sebelah kiri) dan tabel output yang diminta (sebelah kanan)

Kalau dari ilustrasi ini (dengan karyawan yang tidak begitu banyak), ya gampangannya tinggal sort, select, copy, dan paste saja tho.....

Bayangin kalau karyawannya banyak dan diminta untuk setiap tanggal di setiap bulan....Liyer euy...

Singkat cerita...saya siatin deh pake VLOOKUP "2 Kriteria"

Lha kok? Emang bisa rumus VLOOKUP pake lookup value dari 2 cell gitu ?

Seperti yang diketahui, sampai saat ini VLOOKUP tidak sama halnya dengan SUMIF, COUNTIF, AVERAGEIF (yang notabenenya bisa menggunakan 2 kriteria atau lebih)..

Trus bagaimana caranya ? 

Yuk dicoba cara saya nyiasatin rumus VLOOKUP supaya bisa menggunakna 2 kriteria...

1. Bikin "trigger" baru dengan menggabungkan 2 kriteria ini..

Prinsip dasar ketika bermain rumus VLOOKUP, ada 2 yaitu :

1. Trigger yang diinginkan

2. Trigger yang unik

Dalam ilustrasi ini, 2 kriteria yang dimaksud adalah NIK Karyawan dan Tanggal Produksi. langkahnya sebagai berikut :

1a. Insert New Coloumn, klik column D (dibagian atas), lalu klik kanan, lalu pilih insert

Perlu diingat, insert kolom barunya harus di sebelah kiri data yang mau dipanggil.

Dalam hal ini, karena data yang mau dipanggil ada di kolom E, maka insert new kolomnya bisa di kolom A,B,C, atau D.

1b. Tulis judulnya/headernya "Concat NIK+Tanggal Pembuatan", lalu buat rumus penggabungan datanya (bisa pake concatenate atau "&")

Dalam ilustrasi ini, rumusnya adalah

= A2&C4

1c. Copy dan Paste (drag) rumus sampai bawah. sehingga hasilnya sebagai berikut

2. Tulis rumus Vlookup nya di table penyajian, sebagai berikut :

=VLOOKUP($J4&L$3,$D:$E,2,0)

Lookupvalue = $J4&L$3    ; dapat disesuaikan simbol "$" nya supaya ga "lari" rumusnya, 

Tablearray = $D:$E     

Col_index_number = 2    : index number dapat disesuaikan sesuai dengan 

[range_lookup] = 0    ; bisa disesuaikan mau pake true atau false juga...

Perlu diingat, untuk lookupvalue, urutan concatnya jg harus sama dengan triggernya ya...

Contoh kalau triggernya, di concat NIK+Tanggal Pembuatannya, maka lookupvaluenya juga NIK + tanggal pembuatan, bukan Tanggal Pembuatan+NIK.

3. Copy dan Paster rumus ke kanan dan ke bawah sesuai kebutuhan.

4. Selesai deh.....

Gimana? Lebih cepet kan, kurang lebih 5 menit selesai untuk berapun raw datanya.

Saya baru coba nyiasatin vlookup untuk 2 kriteria saja...

Rekan2 boleh mencoba sendiri nyiasatin VLOOKUP untuk lebih dari 2 kriteria. 

Nanti info ya hasilnya dengan cara tulis di kolom komentar.

Jika ada yang mau bertanya atau butuh topik lainnya, silakan tulis di kolom komentar juga ya...

Semoga Bermanfaat. Terima kasih

Excel4Work

"Memanggil" Data (Lookup) dengan kriteria text contains tertentu ? XLOOKUP solusinya.....

 

Halo semua.....

Catatan kali berdasarkan pengalaman saya baru2 ini. Jadi tho, saya sedang mengerjakan data dan membutuhkan aktivitas di excel untuk memanggil data (lookup) dengan kritera text contain tertentu.

Vlookup ? Untuk kebutuhan ini, Vlookup tidak sanggup karena Vlookup tidak bisa dikombinasikan dengan formula text contain.

Lalu manual dong ? ya kali.... Kalau datanya sedikit sih ga terasa....Tapi kalau datanya sampai 1000, mau pulang jam berapa ini

Coba deh pakai XLOOKUP .....

Bagaimana caranya ? Yuk disimak.

1. Ilustrasi Kebutuhan

Jadi contoh ada perusahaan penghasil nasi goreng membutuhkan data daerah pemasok bahan baku pembuatan nasi goreng, sehingga dapat dihitung biaya pengiriman bahan baku.

2. Dasar XLOOKUP

=XLOOKUP (lookup_value, lookup_array, [if_not_found], [match mode], [search model])

Nah lho, pakemnya beda dengan VLOOKUP dan HLOOKUP.

3. Buat rumusnya.

lookup_value =    "*"&G3&"*"   , G3 bisa diganti dengan trigger cell untuk pemanggilan data

lookup_value = $C$3:$C$6 , range disesuaikan dengan lookup value range.

lookup_array = $B$3:$B$6 , range disesuaikan dengan range yang hendak kita panggil datanya (dalam hal ini daerah pemasok)

If_Not_Found = "Dimana?", bisa diganti kata2nya tergantung kebutuhan

Match_Mode = 2 , wildcard character match

Untuk contoh permasalahan di atas, maka rumus lengkapnya seperti ini :

=XLOOKUP("*"&G3&"*",$C$3:$C$6,$B$3:$B$6,"Dimana?",2)

4. Drag Down / Copas sesuai kebutuhan 

5. Simpan dan Selesai

Cukup 5 langkah dan hanya membutuhkan waktu kurang lebih 2 menit untuk nulis rumusnya dan copy paste rumus.

Jika ada hendak ditanyakan, silakan tulis di kolom komentar

Semoga bermanfaat. Terima kasih

Excel4work

Menggabungkan 2 Sheet (atau Lebih) menjadi 1 Sheet, dengan Power Query di Excel

 Selamat Pagi / Siang / Malem Para Budiman

Bertemu kembali (lewat tulisan) dengan Excel4Work....

Kali ini kita akan membahas soal Power Query. Sebuah fitur canggih keluaran terbaru dari Microsoft Excel yang mempermudah pekerjaan pengolahan data. Power query sudah built up untuk Excel 2016 ke atas.

Untuk rekan-rekan yang mempunyai Excel 2013, bisa download fitur di link berikut ;

https://www.microsoft.com/en-us/download/details.aspx?id=39379

Power Query ini canggih guys. Dia bisa menggabungkan data, melakukan cleansing data dengan cepat, hingga mengolah data tanpa formula.

Nah untuk kali ini kita akan membahas bagaimana menggabungkan data dari 2 worksheet dalam 1 file menjadi 1 worksheet

1. Pastikan headernya urutannya sama.

   Contonya seperti ini ya....

Note : Supaya header tidak berulang karena menggabungkan data. Pakai header dari 1 sheet, untuk sheet lain headernya dihapus.

2. Pilih data, lalu pilih Get Data

Note : Terkadang di versi lain, penempatan Get Data tidak seperti contoh. Jadi cari sendiri yo di Ribbon "Data"

3. Pilih "Form File", Lalu pilih "From Workbook"

4. Pilih File yang hendak digabungkan, Lalu klik "Import"

5. Pilih File yang hendak digabungkan (dengan mencetang), lalu klik "Transform Data"

Begini hasilnya jika sudah Transform Data.

6. Klik kanan di header yang ada tulisan "table" nya, Pilih "Expand" dan uncentang "Use Original column name as profile", lalu klik "OK"

7. Sekarang saatnya membuat header. Caranya klik kanan di pojok kiri atas, lalu pilih "Use First Row as Headers"

8. Hapus kolom yang tidak digunakan, dengan cara select kolom yang tidak terpakai, lalu klik kanan, lalu pilih "Remove Column"

9. Jika sudah selesai, klik "Close & Load"

Note : jika mau meng-"Undo", silakan cari "Applied Steps", lalu klik tanda silang yang hendak di-"Undo". Tidak bisa Redo lagi ya.

10. Selesai. Begini penampakannya. Jangan lupa disimpan ya.

Gampang, hanya 10 langkah saja. Tidak perlu menggukan add-on kutools lagi. 

Dan sangat mempersingkat waktu kerjanya.

Jika ada pertanyaan atau memerlukan topik lain yang belum tersedia, silakan tulis di kolom komentar. Terima kasih.

Semoga bermanfaat.

Excel4work