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

Kepada para pembaca yang budiman, dari penulis blog ingin mengucapkan:

SELAMAT IDUL FITRI 1435 H
MOHON DIMAAFKAN LAHIR BATIN

Dan mudah2xan saya dapat lebih aktif lagi menulis artikel2x  tentang rumus excel setelah kita semua berlebaran.

Salam
Wass.Wr.Wb
Rizky

Kasus Tabel Dengan Lay Out Yang Sulit

Kebetulan saya ada beberapa kasus menarik yang menyangkut Lookup Data tetapi dengan bentuk tabel yang agak sulit dengan rumus yang reguler. Maksud saya rumus reguler adalah rumus yang lazim digunakan atau yang sudah digunakan secara luas.

Seperti ini misalnya:

Kasus 1:

Nah mungkin pernah liat yang beginian? Terus misal kriterianya adalah Buah dan kita ingin mengambil hasilnya dari Kolom B, itu berarti "Apel", "Jeruk" dan "Durian"

Hasil yang diinginkan adalah seperti yang diatas, kriteria yang dipakai adalah Kopi dan Apel, nah coba memakai rumus VLOOKUP atau INDEX + MATCH pasti gagal deh... huehehe....

Rumus 1 (Kriteria "Kopi") :
=IFERROR(INDEX($B$2:$B$10;SMALL(IF(LOOKUP(ROW($A$2:$A$10);ROW($A$2:$A$10)/($A$2:$A$10<>"");$A$2:$A$10)=$A$13;ROW($B$2:$B$10)-ROW($B$2)+1);ROWS(A$1:A1)));"")

Rumus 2 (Kriteria "Apel") :
=IFERROR(INDEX(LOOKUP(ROW($A$2:$A$10);ROW($A$2:$A$10)/($A$2:$A$10<>"");$A$2:$A$10);SMALL(IF($B$2:$B$10=$D$13;ROW($B$2:$B$10)-ROW($B$2)+1);ROWS(A$1:A1)));"")

Kasus 2:

Kemudian hasil yang diinginkan adalah mengambil data QTY atau jumlah barang yang terjual oleh kriteria "Nama Sales", "Jenis Barang" dan "Merek Barang"

Rumusnya:
=INDEX(B22:E25;MATCH(1;(INDEX(B22:D25;0;MATCH($A28;B21:D21;0))=B29)*(A22:A25=A29);0);MATCH($A28;B21:D21;0)+1)

Kasus 3:

Contoh diatas ada juga Column Header yang Cell-nya digabung atau Merged, sebetulnya untuk penggunaan rumus pada Cell yang di-merged tidak disarankan biasanya akan gagal waktu di-ENTER, atau kalau jika digunakan sebagai kriteria agak ribet rumusnya, sebisa mungkin tabel data bersih yaitu tidak ada Cell yang di-MERGED dan tidak ada karakter spasi kosong pada tabel anda.

Sesuai Tabel Kasus 3 diatas, hasil yang diinginkan seperti ini

Nah kriterianya adalah "Area" kemudian hasil yang diinginkan adalah semua baris data yang ada dibawahnya...

Rumus Untuk Tanggal :
=IFERROR(INDEX($B$38:$H$38;SMALL(IF(LOOKUP(COLUMN($B$37:$H$37);COLUMN($B$37:$H$37)/($B$37:$H$37<>"");$B$37:$H$37)=$A$44;COLUMN($B$37:$H$37)-COLUMN($B$37)+1);COLUMNS($A20:A20)));"")

Rumus Untuk Qty:
=IFERROR(INDEX($B$39:$H$41;SMALL(IF((INDEX($B$39:$H$41;0;MATCH(1;($B$38:$H$38=B$44)*(LOOKUP(COLUMN($B$37:$H$37);COLUMN($B$37:$H$37)/($B$37:$H$37<>"");$B$37:$H$37)=$A$44);0))<>"");MATCH(ROW($A$39:$A$41);ROW($A$39:$A$41));"");ROWS($A$20:$A20));MATCH(1;($B$38:$H$38=B$44)*(LOOKUP(COLUMN($B$37:$H$37);COLUMN($B$37:$H$37)/($B$37:$H$37<>"");$B$37:$H$37)=$A$44);0));"-")

Semuanya adalah Rumus Array, tekan tombol CTRL-SHIFT-ENTER bersamaan

Salam

File Latihan

Rumus Kalkulasi Tanggal Untuk Berbagai Keperluan

Saya akan "all out" kali ini, memberikan berbagai rumus untuk berbagai keperluan yang berkaitan kalkulasi menggunakan tanggal, langsung saja satu persatu dibawah ini:

KASUS A:Menghitung Jarak Antara 2 Tanggal
1. Dengan DATEDIF:
=IF(DATEDIF(A4;B4;"y")=0;"";DATEDIF(A4;B4;"y")&" tahun ")&IF(DATEDIF(A4;B4;"ym")=0;"";DATEDIF(A4;B4;"ym")&" bulan ")&IF(DATEDIF(A4;B4;"md")=0;"";DATEDIF(A4;B4;"md")&" hari")
2. Tanpa DATEDIF:
=TRIM(IF(INT((B4-A4)/365,25)=0;"";INT((B4-A4)/365,25)&" tahun")&" "&IF(INT(MOD((B4-A4)/365,25;1)*12)=0;"";INT(MOD((B4-A4)/365,25;1)*12)&" bulan")&" "&IF(INT(MOD((B4-A4)/30,44;1)*30,44)=0;"";INT(MOD((B4-A4)/30,44;1)*30,44)&" hari"))

Kasus B: Menghitung Umur
1. Dengan DATEDIF:
=IF(DATEDIF(A9;TODAY();"y")=0;"";DATEDIF(A9;TODAY();"y")&" tahun ")&IF(DATEDIF(A9;TODAY();"ym")=0;"";DATEDIF(A9;TODAY();"ym")&" bulan ")&IF(DATEDIF(A9;TODAY();"md")=0;"";DATEDIF(A9;TODAY();"md")&" hari")
2. Tanpa DATEDIF:
=TRIM(IF(INT((TODAY()-A9)/365,25)=0;"";INT((TODAY()-A9)/365,25)&" tahun")&" "&IF(INT(MOD((TODAY()-A9)/365,25;1)*12)=0;"";INT(MOD((TODAY()-A9)/365,25;1)*12)&" bulan")&" "&IF(INT(MOD((TODAY()-A9)/30,44;1)*30,44)=0;"";INT(MOD((TODAY()-A9)/30,44;1)*30,44)&" hari"))
atau
=IF(YEAR(DATE(YEAR(TODAY())-YEAR(A9);MONTH(TODAY())-MONTH(A9)+1;DAY(TODAY())-DAY(A9)+1))-1900=0;"";YEAR(DATE(YEAR(TODAY())-YEAR(A9);MONTH(TODAY())-MONTH(A9)+1;DAY(TODAY())-DAY(A9)+1))-1900&" Tahun ")&IF(MONTH(DATE(YEAR(TODAY())-YEAR(A9);MONTH(TODAY())-MONTH(A9)+1;DAY(TODAY())-DAY(A9)+1))-1=0;"";MONTH(DATE(YEAR(TODAY())-YEAR(A9);MONTH(TODAY())-MONTH(A9)+1;DAY(TODAY())-DAY(A9)+1))-1&" Bulan ")&IF(DAY(DATE(YEAR(TODAY())-YEAR(A9);MONTH(TODAY())-MONTH(A9)+1;DAY(TODAY())-DAY(A9)+1))-1=0;"";DAY(DATE(YEAR(TODAY())-YEAR(A9);MONTH(TODAY())-MONTH(A9)+1;DAY(TODAY())-DAY(A9)+1))-1&" Hari")

Kasus C: Rumus Untuk Mengetahui Hari Ulang Tahun pada Tahun Ini
Hari Ulang Tahun pada tahun ini adalah……
=TEXT(DATE(YEAR(TODAY());MONTH(A21);DAY(A21));"dddd")&", "&TEXT(DATE(YEAR(TODAY());MONTH(A21);DAY(A21));"dd mmm yyyy")

Rumus untuk menghitung sisa hari menjelang ulang tahun atau sudah lewat
=IF(TODAY()>DATE(YEAR(TODAY());MONTH(A21);DAY(A21));"Wah sudah lewat tuh...";IF(TODAY()=DATE(YEAR(TODAY());MONTH(A21);DAY(A21));"Selamat Ulang Tahun !!";"Kurang "&TRIM(IF(INT((DATE(YEAR(TODAY());MONTH(A21);DAY(A21))-TODAY())/365,25)=0;"";INT((DATE(YEAR(TODAY());MONTH(A21);DAY(A21))-TODAY())/365,25)&" tahun")&" "&IF(INT(MOD((DATE(YEAR(TODAY());MONTH(A21);DAY(A21))-TODAY())/365,25;1)*12)=0;"";INT(MOD((DATE(YEAR(TODAY());MONTH(A21);DAY(A21))-TODAY())/365,25;1)*12)&" bulan")&" "&IF(INT(MOD((DATE(YEAR(TODAY());MONTH(A21);DAY(A21))-TODAY())/30,44;1)*30,44)=0;"";INT(MOD((DATE(YEAR(TODAY());MONTH(A21);DAY(A21))-TODAY())/30,44;1)*30,44)&" hari"))&" lagi"))

Kasus D: Membuat Reminder dari Agenda Kegiatan/Peristiwa atau Masa Kadaluarsa SIM/KTP/STNK
Di Cell C31 dan dicopy kebawah:
=IF(B31="";"";IF(B31=TODAY();"Hari ini Acaranya/Jatuh Tempo";IF(B31>TODAY();IF(DATEDIF(TODAY();B31;"y")=0;"";DATEDIF(TODAY();B31;"y")&" tahun ")&IF(DATEDIF(TODAY();B31;"ym")=0;"";DATEDIF(TODAY();B31;"ym")&" bulan ")&IF(DATEDIF(TODAY();B31;"md")=0;"";DATEDIF(TODAY();B31;"md")&" hari")&" lagi";"Sudah lewat "&IF(DATEDIF(B31;TODAY();"y")=0;"";DATEDIF(B31;TODAY();"y")&" tahun ")&IF(DATEDIF(B31;TODAY();"ym")=0;"";DATEDIF(B31;TODAY();"ym")&" bulan ")&IF(DATEDIF(B31;TODAY();"md")=0;"";DATEDIF(B31;TODAY();"md")&" hari"))))

Kasus E: Menghitung Jarak Antara 2 Tanggal Dalam Minggu dan Hari:
=IF(TRUNC((B44-A44)/7)=0;"";TRUNC((B44-A44)/7)&" Minggu")&" "&IF(MOD((B44-A44);7)=0;"";MOD((B44-A44);7)&" Hari")
atau
=IF(INT((B44-A44)/7)=0;"";INT((B44-A44)/7)&" Minggu ")&IF(MOD(((B44-A44)/7);1)*7=0;"";MOD(((B44-A44)/7);1)*7&" Hari")
atau
=IF(INT(DATEDIF(A44;B44;"D")/7)=0;"";INT(DATEDIF(A44;B44;"D")/7)&" Minggu ")&IF(MOD(DATEDIF(A44;B44;"D");7)=0;"";MOD(DATEDIF(A44;B44;"D");7)&" Hari")

Kasus F: Rumus untuk mengetahui minggu ke berapa dari tanggal (dalam 1 bulan)
="Minggu ke "&WEEKNUM(A55)-WEEKNUM(A55-DAY(A55)+1)+1
 atau
 ="Minggu ke "&INT((DAY(A55)+6)/7)

Kasus G: Rumus untuk mengetahui jumlah hari dari suatu bulan
=DAY(DATE(YEAR(A63);MONTH(A63)+1;0))&" Hari"
atau
=DAY(EOMONTH(A21;0))&" Hari"

Atau kasus seperti ini, anda punya list nama-nama bulan:

 









Rumus untuk menghasilkan jumlah hari dari suatu bulan dengan kriteria nama bulan (teks):
=DAY(DATE(YEAR(TODAY());MONTH($A69&1)+1;0))&" Hari"

Kasus H: Konversi Angka Hari ke Tahun, ulan dan Hari
=IF(DATEDIF(366;366+A78;"y")=0;"";DATEDIF(366;366+A78;"y")&" tahun ")&IF(DATEDIF(366;366+A78;"ym")=0;"";DATEDIF(366;366+A78;"ym")&" bulan ")&IF(DATEDIF(366;366+A78;"md")=0;"";DATEDIF(366;366+A78;"md")&" hari ")

Kasus I: Bulan ini tinggal berapa hari lagi yaaa….?
="tinggal "&EOMONTH(TODAY();0)-TODAY()&" hari lagi"
atau
="tinggal "&DATE(YEAR(TODAY());MONTH(TODAY())+1;0) - TODAY()&" hari lagi"

Kasus J:
Yang satu ini mungkin untuk just for fun karena bisa untuk men-cek, hari dan tanggal gajian kita kurang berapa hari sih? Dan rumus ini saya buat otomatis akan men-skip hari "Sabtu" dan "Minggu". Seperti contoh diatas, pada cell A90 tertulis angka 1, rumus akan mengkonversi tanggal 1 tersebut adalah tanggal 1 Maret 2015 dan itu jatuh pada hari minggu, dan itu weekend, nah ndak mungkin toh terima gaji? Nah pasti mundur ke Senin. Ke-2 rumus di cell A92 dan A94(warna kuning) tidak saya tulis disini karena terlalu panjang, tetapi silahkan dilihat di file latihan.

Rumus untuk mencari hari dan tanggal terakhir Bulan Ini:
=TEXT(EOMONTH(TODAY();0);"dddd")&", "&TEXT(EOMONTH(TODAY();0);"dd mmmm yyy")
atau
=TEXT(DATE(YEAR(TODAY());MONTH(TODAY())+1;0);"dddd")&", "&TEXT(DATE(YEAR(TODAY());MONTH(TODAY())+1;0);"dd mmmm yyyy")

Rumus untuk mencari hari dan tanggal pertama Bulan ini:
=TEXT(EOMONTH(TODAY();0)+1;"dddd")&", "&TEXT(EOMONTH(TODAY();0)+1;"dd mmmm yyyy")
atau
=TEXT(DATE(YEAR(TODAY());MONTH(TODAY())+1;1);"dddd")&", "&TEXT(DATE(YEAR(TODAY());MONTH(TODAY())+1;1);"dd mmmm yyyy")

Rumus untuk cek tahun kabisat atau bukan:
=IF(DAY(DATE(A108;2;29))=29;"Tahun Kabisat";"Normal")
atau
=IF(MONTH(DATE(A108;2;29))=2;"Tahun Kabisat";"Normal")
atau
=IF(ISNUMBER(--(A108&"-02-29"));"Tahun Kabisat";"Normal")
atau
=IF(MOD(A108;4)=0;"Tahun Kabisat";"Normal")

Mudah2xan bermanfaat Wassalamualaikum Warahmatullahi Wabarakatuh

DOWNLOAD FILE LATIHAN

Mengambil Data Dengan Multiple Results

Saya pernah menulis posting tentang mengambil data dengan beberapa hasil atau multiple results..... saya ingin refresh materi itu kali ini. Saya ada beberapa contoh yang mungkin berguna untuk diterapkan dan dikembangkan sesuai kebutuhan anda semua para pembaca budiman.

Kasus 1: Multi Result 1 Kriteria

Dengan tabel kasus diatas kita ingin mengambil data "Item" sesuai dengan "Kode" 101, disana terlihat kode 101 punya beberapa item. Hasil yang kita inginkan nanti seperti ini...

Jadi hasil multi results bisa dibuat secara vertical ataupun horizontal.
Rumusnya:

Untuk Hasil Vertical:
=IFERROR(INDEX($B$2:$B$6;SMALL(IF(($A$2:$A$6=$B$9)*($B$2:$B$6<>"");ROW($B$2:$B$6)-ROW($B$2)+1);ROWS($B$1:B1)));"")
Kemudian copas ke bawah

Untuk Hasil Horizontal:
=IFERROR(INDEX($B$2:$B$6;SMALL(IF(($A$2:$A$6=$B$15)*($B$2:$B$6<>"");ROW($B$2:$B$6)-ROW($B$2)+1);COLUMNS($B$1:B1)));"")
Kemudian copas kesamping kanan

Kasus 2: Multi Results 2 Kriteria

Hampir sama, hanya ada penambahan kriteria yaitu Kolom "Group", hasil yang diinginkan...

Pada gambar diatas dengan 2 kriteria yaitu Kolom "Group" dan Kolom "Kode" dan hasil yang diambil adalah Kolom "Item", baik secara vertical maupun horizontal.
Yang kedua adalah 2 kriteria pada Kolom "Kode", ditampilkan secara vertical.

Rumus untuk yang vertical (di Cell C9 dan copas ke bawah):
=IFERROR(INDEX($C$2:$C$6;SMALL(IF(($B$2:$B$6=$B$9)*($A$2:$A$6=$B$10)*($C$2:$C$6<>"");ROW($C$2:$C$6)-ROW($C$2)+1);ROWS($C$1:C1)));"")

Rumus untuk hasil horizontal (di Cell C15 dan copas ke kanan):
=IFERROR(INDEX($C$2:$C$6;SMALL(IF(($B$2:$B$6=$B$15)*(A2:A6=B16)*($C$2:$C$6<>"");ROW($C$2:$C$6)-ROW($C$2)+1);COLUMNS($C$1:C1)));"")

Rumus untuk 2 Kriteria "101" dan "202" (sama2x kriteria Kode):
=IFERROR(INDEX($B$2:$C$6;SMALL(IF(COUNTIF($B$19:$B$20;INDEX($B$2:$C$6;;1;1))>0;ROW($B$2:$C$6)-MIN(ROW($B$2:$C$6))+1);ROW(A1));2);"")

Kasus 3: Multi Results pada 2 Tabel

Nah bagaimana dengan 2 Tabel? mari kita tampilkan hasil yang diinginkan....

Nah menurut gambar diatas, kita diminta menampilkan data "Item" pada 2 tabel sesuai 2 kriteria

Rumus untuk hasil vertical:
=IFERROR(IFERROR(INDEX($C$2:$C$6;SMALL(IF(($A$2:$A$6=$A$9)*($B$2:$B$6=$B$9);ROW($C$2:$C$6)-MIN(ROW($C$2))+1);ROWS($A$1:A1)));INDEX($G$2:$G$6;SMALL(IF(($E$2:$E$6=$A$9)*($F$2:$F$6=$B$9);ROW($G$2:$G$6)-MIN(ROW($G$2))+1);ROWS($A$1:A1)-COUNTIFS($A$2:$A$6;$A$9;$B$2:$B$6;$B$9))));"")

Dan untuk hasil horizontal:
=IFERROR(IFERROR(INDEX($C$2:$C$6;SMALL(IF(($A$2:$A$6=$A$9)*($B$2:$B$6=$B$9);ROW($C$2:$C$6)-MIN(ROW($C$2))+1);COLUMNS($A$1:A1)));INDEX($G$2:$G$6;SMALL(IF(($E$2:$E$6=$A$9)*($F$2:$F$6=$B$9);ROW($G$2:$G$6)-MIN(ROW($G$2))+1);COLUMNS($A$1:A1)-COUNTIFS($A$2:$A$6;$A$9;$B$2:$B$6;$B$9))));"")

Kasus 4: Multi Results dengan Menghilangkan Duplikasi

Kali ini coba perhatikan, misal kriterianya adalah "101", tetapi kok ada item yang sama ya? Nah kita inginnya membuang yang dobel itu. Hasil yang diinginkan..

Jadi item "Kemoceng" ditampilkan hanya satu saja... ini dia rumusnya
=IFERROR(INDEX($B$2:$B$6;SMALL(IF(($A$9=$A$2:$A$6)*(COUNTIF($B$8:B8;$B$2:$B$6)=0);ROW($A$2:$A$6)-MIN(ROW($A$2:$A$6))+1;"");1));"")
kemudian copas ke bawah

Oh iya semua rumus diatas adalah rumus array atau rumus CSE (CTRL+SHIFT+ENTER)

Semoga bermanfaat.... cheers

File Latihan

Mengambil Data Penjualan Tertinggi Berdasar Group

Langsung aja ya kasusnya seperti ini......

Nah data ada beberapa sales person yang membawahi area atau daerah penjualan, kita ingin ambil penjualan tertinggi berdasar area/daerah penjualan, hasil yang diinginkan seperti ini:

Rumusnya array lagi ya, seperti ini:
Cell A13:
=IFERROR(INDEX($A$2:$A$10;MATCH(0;COUNTIF($A$2:$A$10;"<"&$A$2:$A$10)-SUM(COUNTIF($A$2:$A$10;"="&A$12:A12));0));"")

Cell B13:
=INDEX($B$2:$B$10;MATCH(LARGE(IF($A$2:$A$10=A13;$C$2:$C$10;"");COUNTIF($A$13:A13;A13));IF($A$2:$A$10=A13;$C$2:$C$10;"");0))

kemudian copas ke bawah.

Kasus yang lain, dari data duatas, kita ingin menjumlahkan data penjualan ranking pertama dan kedua, jadi penjualan Top 2.

Cell B19:
=SUMPRODUCT(LARGE(($A$2:$A$10=A19)*($C$2:$C$10);{1\2}))

copas ke bawah

Happy to learn guys.....
Cheers....