2 Way Lookup With Multiple Results

Selamat Siang Semuanya........

Glad to be back, mohon maaf agak lama tidak posting karena saya sakit flu, maklum lagi musim pancaroba, penyakit berjangkit dimana-mana.

Yang terpenting hari ini ada postingan baru yak... kali ini membahas 2 Way Lookup lagi ya tetapi berbeda dengan yang pertama , tulisan terdahulu hasilnya hanya satu saja (single result), nah kenapa tidak dengan hasil yang beberapa atau multi results. Langsung saja tabel kasus dibawah ini :

Seperti kita paham 2 way lookup mempunyai bentuk tabel seperti diatas terdapat Column Header (Mata Pelajaran) dan Row Header (Nama). Nah Pertanyaannya, mungkin ndak ya jika kita ingin mengambil data berdasar Nilainya? Misal:

Dari kriteria "Nilai", hasil yang diinginkan adalah Nama2x yang mendapatkan nilai tersebut, sekalian dengan Mata Pelajaran yang diambil.
Rumus Nama:
=IFERROR(INDEX($A$2:$A$6;SMALL(IF(($B$2:$E$6=$A$9)*($B$2:$E$6<>"");ROW($B$2:$E$6)-ROW($B$2)+1);ROWS(A$1:A1)));"-")
Rumus Mata Pelajaran:
=IFERROR(INDEX($B$1:$E$1;SMALL(IF(INDEX($B$2:$E$6;MATCH($B9;$A$2:$A$6;0);0)=$A$9;COLUMN($B$2:$E$6)-COLUMN($B$2)+1);COUNTIF($B$9:B9;B9)));"-")

Pertanyaan 2:

Kriterianya adalah Nama, dan ingin mengambil data Nilai dan Mata Pelajarannya.
Rumus Nilai:
=IFERROR(INDEX($B$2:$E$6;SMALL(IF(($A$2:$A$6=$A$18)*($B$2:$E$6<>"");MATCH(ROW($B$2:$E$6);ROW($B$2:$E$6));"");ROW(A1));1*(SMALL(IF(($A$2:$A$6=$A$18)*($B$2:$E$6<>"");MATCH(ROW($B$2:$E$6);ROW($B$2:$E$6))+1*MATCH(COLUMN($B$2:$E$6);COLUMN($B$2:$E$6));"");ROW(A1))-SMALL(IF(($A$2:$A$6=$A$18)*($B$2:$E$6<>"");MATCH(ROW($B$2:$E$6);ROW($B$2:$E$6));"");ROW(A1))));"-")
Rumus Mata Pelajaran:
=IFERROR(INDEX($B$1:$E$1;SMALL(IF(INDEX($B$2:$E$6;MATCH(A$18;$A$2:$A$6;0);0)=$B18;COLUMN($B$1:$E$1)-COLUMN($B$1)+1);COUNTIFS($A$18:$A$18;$A$18;$B$18:B18;B18)));"-")

Pertanyaan 3:

Kriterianya adalah Mata Pelajaran, hasil yang diambil adalah data Nilai dan Nama Siswa.
Rumus Nilai:
=IFERROR(INDEX($B$2:$E$6;SMALL(IF((INDEX($B$2:$E$6;0;MATCH($A$25;$B$1:$E$1;0))<>"");MATCH(ROW($A$2:$A$6);ROW($A$2:$A$6));"");ROW(A1));MATCH($A$25;$B$1:$E$1;0));"-")
Rumus Nama Siswa:
=IFERROR(INDEX($A$1:$A$6;SMALL(IF($B$1:$E$1=$A$25;IF(ISNUMBER(MATCH($B$2:$E$6;$B$25:$B$29;0));IF(ISNA(MATCH($A$2:$A$6;$B2:E$6;0));ROW($A$2:$A$6)-ROW($A$1)+1)));ROWS($A$1:A1)));"-")

Semuanya array formulas ya....dan copy kebawah sampai hasilnya blank.

Download File Latihan

Penomoran Otomatis Pada Data Yang Sama (Duplikat)

Saya pernah memberikan memberikan materi autonumbering (memberi nomor otomatis pada data), tetapi kali ini untuk data yang muncul lebih dari satu kali alias data yang sama atau duplikasi.

Kasus Pertama, kita ingin memberikan nomor pada data yang pertama muncul dan data yang muncul berikutnya diisi blank atau kosong.
Rumus (diletakkan di cell A2 dan copas ke bawah):

Rumusnya

=IF(COUNTIF($B$2:B2;B2)=1;MAX($A$1:A1)+1;"")

Kasus Kedua untuk penomoran data berulang diberikan urutan pada grup data yang sama dan akan restart jika menemukan data baru, perhatikan contoh tabel diatas 

rumusnya

=IF(B2="";"";COUNTIF($B$2:B2;B2))

Kasus Ketiga, memberikan nomor yang sama pada data yang sama 

rumusnya:

=IFERROR(IF(COUNTIF(B$2:B2;B2)=1;MAX(A$1:A1)+1;VLOOKUP(B2;CHOOSE({1\2};$B$1:B1;$A$1:A1);2;0));"")

Salam
Rizky

Memisahkan Data Alamat

Salah satu yang saya sukai di rumus excel adalah manipulasi data teks, nah kali ini yang di utak atik adalah data alamat.

Coba perhatikan data alamat diatas, ya disana data dipisahkan oleh koma dan ada jarak spasi disana, itu yang menjadi kunci untuk rumusnya nanti.

Hasil yang di-inginkan

Yang dipisahkan adalah alamat, kemudian kota, propinsi dan terakhir adalah kode pos. Mohon diingat jika ingin memisahkan seperti ini, anda harus punya data yang polanya konsisten, karena rumus excel tidak akan mengenal "Sutrabaya" sebagai kota atau "Jawa Tengah" sebagai propinsi, yang penting polanya sama sehingga logika pemisahannya bisa diterapkan pada rumus.

Pertama-tama yang harus dilakukan adalah mengambil data "Kota", "Propinsi" dan "Kode Pos", karena ini sebagai kunci.

Rumusnya adalah:
=TRIM(RIGHT(SUBSTITUTE(A2;",";REPT(" ";90));270))    , dari sini yang lain jadi agak mudah.

Rumus Kota:
=TRIM(LEFT(TRIM(RIGHT(SUBSTITUTE(A2;",";REPT(" ";90));270));SEARCH(" ";TRIM(RIGHT(SUBSTITUTE(A2;",";REPT(" ";90));270));1)-1))

Rumus Alamat:
=MID(TRIM(LEFT(A2;SEARCH(D2;A2;1)-1));1;LEN(TRIM(LEFT(A2;SEARCH(D2;A2;1)-1)))-1)

Rumus Propinsi:
=TRIM(MID(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A2;", ";REPT(" ";90));270));" ";REPT(" ";99));100;200))

Rumus Kode Pos:
=TRIM(RIGHT(SUBSTITUTE(A2;" ";REPT(" ";99));99))

Nah mungkin ada dari pembaca yang punya rumus lain? silahkan di-share disini

Salam
Rizky

Download File Latihan

SHARE MOMENT

Assalamualaikum

Hi to you all......

Postingan kali ini hanya berbagi pengalaman kenapa saya membuat blog ini. Awalnya biasa saja dengan program excel ini yah hanya untuk mengetik saja, tetapi terkadang muncul masalah tatkala saya disuruh bos membuat sebuah report summary, padahal datanya banyak. Wah bingung kan, ndak tau rumus waktu itu, terpaksa manual awalnya, sangat waste of time. Berawal dari situ saya mikir2x mungkin gak ya ada cara yang lebih gampang? Saya mulai beli2x buku2x excel, puyeng juga waktu pertama kali baca, step by step memulai dari rumus yang sederhana =IF(A1="A";"Lulus";IF(A1="B";"Agak Bagus" hehehe, berhasil dengan ini tambah semangat belajar lagi dan mendalami tentang excel, terutama rumus, tentang pivot tabel, graphic chart nol besar :p, apalagi yang namanya macro/VBA nyerah deh

Yang paling berkesan adalah sewaktu saya bergabung dengan forum excel online, kemampuan semakin terasah, bukan dengan bertanya masalah saya kepada yang orang lain , tetapi saya mencoba membantu memecahkan persoalan rumus/permasalahan yang dilontarkan di forum, kadang gagal ada juga yang sukses, dan di forum saya bisa melihat dan mempelajari cara para pakar excel memecahkan persoalan.

Forum yang saya ikuti:

1. www.excelforum.com
2. forum.chandoo.org
3. www.mrexcel.com
4. www.ozgrid.com

Silahkan jadi member (gratis), mau belajar atau posting masalah anda, silahkan saja. Jika ingin melihat postingan saya, search aja nickname saya "azumi" di semua forum tsb diatas.

Suatu keahlian tidak mutlak membutuhkan kepandaian tetapi lebih penting adalah ketekunan dan niat yang kuat dari anda. Dan jika sudah ahli atau mempunyai skills, tetaplah rendah hati dan berbagilah ke orang lain serta jangan merasa puas tetaplah merasa kurang ilmu yang kita miliki. Terakhir walaupun pengetahuan excel masih sangat terbatas,keinginan untuk menyebarkan ilmu pengetahuan sangatlah kuat, jadi guru sudah telat/kasep haha... paling mudah dan tidak mengganggu pekerjaan akhirnya saya pilih media blogger ini.

Salam
Rizky

Utak Atik Data Penjualan

Ass.Wr.Wb

Happy morning I hope to you all the best

Di pagi yang cerah ini mungkin asyik kalau memberikan sesuatu lagi kepada para pembaca sekalian, kali ini tentang data penjualan lagi, soalnya menarik untuk dibahas.

Tabel Kasus:

Dan pertanyaannya adalah:

Nah ada 6 pertanyaan yang dikelompokkan ke 3 kasus, nah satu persatu dikupas.
CASE 1:
Untuk siapa yang menghasilkan penjualan terbesar, rumusnya:
=INDEX($A$2:$A$10;MATCH(MAX($C$2:$C$10);$C$2:$C$10;0))
atau
=VLOOKUP(MAX(C2:C10);CHOOSE({1\2};$C$2:$C$10;$A$2:$A$10);2;0)
Untuk penjualan terkecil, anda tetap bisa menggunakan rumus diatas, hanya saja MAX diganti dengan MIN.

CASE 2:
Siapakah yang mempunyai Sales = 1316?
=VLOOKUP(1316;CHOOSE({1\2};C3:C10;A3:A10);2;FALSE)
Berapakah customer/nasabahnya?
=VLOOKUP(1316;CHOOSE({1\2\3};C3:C10;A3:A10;B3:B10);3;FALSE)

CASE 3:
Siapakah yang mempunyai profit ratio yang terbesar?
=VLOOKUP(MAX(E3:E10);CHOOSE({1\2};E3:E10;A3:A10);2;FALSE)
Berapakah profit rationya?
=VLOOKUP(VLOOKUP(MAX(E3:E10);CHOOSE({1\2};E3:E10;A3:A10);2;FALSE);A3:E10;5;FALSE)

Nah, semua rumus diatas bukan rumus array ya, rumus biasa saja, kelebihan rumus non array adalah menghemat resources dari komputer kita alias RAM/Memori, tetapi biasanya rumusnya jadi lebih panjang. Kalau array formula lebih efisien dan lebih pendek (untuk kasus tertentu), tetapi makan RAM-nya lebih banyak. Dua2xnya mempunyai kelebihan dan kekurangannya masing2x, tinggal kita saja yang memilih mana yang terbaik untuk kasus yang kita punyai.

Cheers --> File Latihan

Pengambilan Data Komisi Penjualan

Hi readers

Saya ingin membahas mengenai pengambilan data (komisi) berdasarkan sales (penjualan).
KASUS 1.

Nah misalnya kasus tabelnya seperti gambar diatas, jadi setiap sales mendapatkan kesempatan mendapatkan bonus/komisi berdasarkan sales-nya. Jika Sales>150 maka gaji + 25% komisi, dan bila Sales>250 maka gaji+50% komisi.
Nah bagaimana caranya jika kita punya kriteria Nama dan Gaji, seperti contoh diatas, bisa otomatis bisa menemukan komisi yang sesuai dengan hasil penjualannya?
Step by stepnya:

Pada gambar diatas yang diwarna kuning (ada 3 cell) itu tugas kita ya untuk bikin rumusnya,
Yang Pertama : =VLOOKUP(B9;IF(B2:B3=C9;A2:D3);IF(B10>250;4;3);FALSE) , ini adalah rumus array, coba perhatikan pada bagian nomor index yaitu IF(B10>250;4;3), nah disini kuncinya, jika penjualan >50 maka mengambil data di kolom ke-4, jika tidak terpenuhi maka mengambil data di kolom ke-3
                      
Yang Kedua : =VLOOKUP(B9;IF(B2:B3=C9;A2:D3);IF(B10>250;4;3);FALSE)*C9
Yang Ketiga : =B14+C9

KASUS 2:

Nah kali ini ada 2 Tabel, untuk tabel perhitungan komisinya terpisah dari tabel data seperti contoh diatas, step-step rumusnya seperti digambar ini, jadi initinya bagaimana menemukan pengahsilan dari karyawan yang ditentukan berdasarkan jabatannya dan komisi tergantung pada tercapai atau tidaknya target tersebut.

Saya menjelaskan lebih detail di FILE LATIHAN
Salam

Konversi Data (Menyatukan dan/atau Memisahkan Data)

Hi apa kabar anda semua, lama tak jumpa ya.. nah ada kasus menarik lagi bagi kawan semua

KASUS 1:

Nah berdasar data sederhana diatas, hasil yang diinginkan adalah sebagai berikut (urut baris)

Jadi dari data yang multi kolom menjadi 1 kolom, rumusnya berikut ini
=INDEX($A$1:$D$3;MOD(ROWS(A$1:A1)-1;ROWS($A$1:$D$3))+1;INT((ROWS(A$1:A1)-1)/ROWS($A$1:$D$3))+1)

Atau hasil yang diinginkan seperti ini (urut kolom)

=INDEX($A$1:$D$3;INT((ROWS(A$1:A1)-1)/COLUMNS($A$1:$D$3))+1;MOD(ROWS(A$1:A1)-1;COLUMNS($A$1:$D$3))+1)


KASUS 2:

Dan hasil yang diinginkan

Nah kasus ke-2 ini adalah kebalikan dari yang pertama, yaitu men-split data dari 1 kolom menjadi multi kolom, dan rumusnya

=IFERROR(IF(ROWS(A1:$A$1)<=$C$1;INDEX($A$1:$A$12;ROWS($A$1:A1)+(COLUMNS($A$1:A1)-1)*$C$1);"");"")

Nah pada gambar anda perhatikan di Cell C1, terdapat angka 2, bisa dirubah sesuai kebutuhan, 2 itu artinya 2 baris, anda bisa rubah menjadi 3, 4 dan seterusnya.

Rumus2x tersebut rumus reguler ya bukan rumus array

Sekian dari saya, happy learning guys....

File Latihan