Pengulangan Data (Repeated Cell Contents)

Happy weekend.....
Salam semuanya, di hari minggu yng cerah ini saya akan memberikan materi berupa mengulang data atau values sesuai kriteria, supaya lebih jelas silahkan diikuti tulisan ini.

Kasus A

Kemudian hasil yang diinginkan untuk pengulangan data seperti ini :

Rumus untuk seluruh data "Nama Buah" diulang 3 kali (sesuai kriteria pada cell C2:
=IF(ROWS(D$2:D2)>$C$2*ROWS($A$3:$A$6);"";INDEX($A$3:$A$6;MOD(ROWS(D$2:D2)-1;ROWS($A$3:$A$6))+1))

Rumus untuk masing2x nama buah diulang 3 kali:
=IFERROR(INDEX($A$3:$A$6;ROUNDUP(ROWS(A$1:A1)/$C$2;0));"")

Kasus B

Kasus B berbeda tetapi mirip, hanya kriteria ada di kolom B dan bervariasi angkanya, dan hasil yang diinginkan

Rumusnya
=LOOKUP(ROW(A1);SUMIF(OFFSET(B$1;;;ROW($1:$99););"<>")+1;A$2:A$99)


KASUS C

Kasus ketiga ini tabelnya mempunyai area baris yang kosong, well tidak bisa pake rumus yg diatas karena blank rows ini harus dikondisikan tidak berpengaruh kepada hasilnya nanti, hasil yang ingin ditampilkan seperti ini

so rumus seperti ini
1. Rumus untuk seluruh data "Nama" diulang 3 kali (sesuai kriteria pada cell B2 (rumus array): =IF(ROWS(A$1:A1)>$B$2*COUNTIF($A$2:$A$11;"="&"?*");"";INDEX($A$2:$A$11;SMALL(IF($A$2:$A$11<>"";ROW($A$2:$A$11)-ROW($A$2)+1);MOD(ROWS(A$1:A1)-1;COUNTIF($A$2:$A$11;"="&"?*"))+1)))
2. Rumus untuk masing2x nama diulang 3 kali (rumus array):
=IFERROR(INDEX($A$2:$A$11;SMALL(IF($A$2:$A$11<>"";ROW($A$2:$A$11)-ROW($A$2)+1);ROUNDUP(ROWS(A$1:A1)/$B$2;0)));"")

Happy day to all of you, here you go to file link download

Sort by Sales (Mengurutkan Berdasarkan Penjualan)

Hi readers tercinta.................. lama tak sapa anda, maaf ya, posting saya hari ini masih mengupas tentang penjualan/sales, hanya kali ini saya coba untuk mengurutkan data penjualan dari yang terbesar ke yang terkecil, kebetulan saya ada 2 kasus yang mudah2x-an menarik para pembaca.

KASUS A

Rumus untuk Nama yang berurutan berdasar total sales terbesar (letakkan di cell A12 sesuai gambar) :
=IFERROR(INDEX($A$2:$A$8;MATCH(1;INDEX(($B$2:$B$8=LARGE($B$2:$B$8;ROWS($A$11:A11)))*(COUNTIF($A$11:A11;$A$2:$A$8)=0););0));"")

Rumus untuk Total Sales  (B12) :
=IFERROR(VLOOKUP($A12;$A$2:$B$8;2;FALSE);"")

2 rumus diatas bukan rumus array ya, rumus reguler aja.............

KASUS B

Nah yang ini agak berbeda, kita coba untuk sort berdasarkan rekap penjualan contoh diatas misalnya selama 1 hari
Rumusnya:
=IFERROR(INDEX(CHOOSE({2\1};SUMIF($A$2:$A$8;$A$2:$A$8;$B$2:$B$8);$A$2:$A$8);MATCH(LARGE(IF(FREQUENCY(MATCH(SUMIF($A$2:$A$8;$A$2:$A$8;$B$2:$B$8)&$A$2:$A$8;SUMIF($A$2:$A$8;$A$2:$A$8;$B$2:$B$8)&$A$2:$A$8;);ROW($A$2:$A$8)-MIN(ROW($A$2:$A$8))+1);SUMIF($A$2:$A$8;$A$2:$A$8;$B$2:$B$8)+ROW($A$2:$A$8)/10000);ROWS(A$1:A1));SUMIF($A$2:$A$8;$A$2:$A$8;$B$2:$B$8)+ROW($A$2:$A$8)/10000;);COLUMNS($A1:A1));"")

rumus ini diletakkan pada cell A12 dan jangan lupa ini rumus array ya, jadi setelah diletakkan pada cell A12 kemudian tekan tombol F2 supaya terlihat isi rumusnya kemudian tekan tombol CTRL+SHIFT+ENTER secara bersamaan, kalau berhasil akan muncul tanda kurung kurawal seperti ini {"rumus"}, setelah itu bisa dicopy kebawah dan kesamping.

BONUS KASUS

Nah anda punya data penjualan seperti diatas dan disuruh bos anda membuat laporan penjualan dengan format seperti ini:

Mengapa saya 4 warnai? karena rumusnya beda2x.............
1.Rumus "Salesman+Produk" (rumus array)
=IFERROR(INDEX($B$3:$B$12&"-"&$C$3:$C$12;SMALL(IF(FREQUENCY(IF($B$3:$B$12&"-"&$C$3:$C$12<>"";MATCH("~"&$B$3:$B$12&"-"&$C$3:$C$12;$B$3:$B$12&"-"&$C$3:$C$12&"";0));ROW($B$3:$B$12)-ROW($B$3)+1);ROW($B$3:$B$12)-ROW($B$3)+1);ROWS(A$16:A16)));"")

2. Rumus  "Berapa kali jualan" (rumus reguler)
=IF(SUMPRODUCT(--($A16=$B$3:$B$12&"-"&$C$3:$C$12))=0;"";SUMPRODUCT(--($A16=$B$3:$B$12&"-"&$C$3:$C$12)))

3. Rumus "Rincian Sales" (rumus array)
=IFERROR(INDEX($D$3:$D$12;SMALL(IF($B$3:$B$12&"-"&$C$3:$C$12<>"";IF($B$3:$B$12&"-"&$C$3:$C$12=$A16;ROW($A$3:$A$12)-ROW($A$3)+1));COLUMNS($D16:D16)));"")

4. Rumus "Total" (rumus reguler)
=IF(SUM(D16:F16)=0;"";SUM(D16:F16))

Best regards
File Latihan

Menghitung Data Unik (Count Unique Values)

Judul Inggrisnya "count unique values", ya kali ini saya akan berikan contoh rumus untuk menghitung data yang unik, data yang dihitung banyak mengandung duplikasi. Seperti ini contohnya

Cobalah diperhatikan nama2x yang ada di dalam tabel terdapat banyak pengulangan atau duplikasi, padahal kita ingin menghitung tanpa menghitung duplikatnya. Jadi kalau dihitung data yang benar2x unik adalah 3, yaitu "Sam", "Joko", dan "Halim".
Rumus:
=SUMPRODUCT((A2:A7<>"")/COUNTIF(A2:A7;A2:A7&""))
atau
=SUM(1/COUNTIF(A2:A7;A2:A7)) (array formula)

atau

=SUMPRODUCT(1/COUNTIF(A2:A7;A2:A7))

Kemudian bagaimana menghitung dengan kriteria?

Perhatikan gambar, pada kolom nama dan properti terdapat data dobel/duplikasi, nah kasusnya kita ingin menghitung properti berdasar nama, pada contoh yang dihitung adalah "Yuna" dan hasilnya adalah 2, yaitu "Rumah" dan "Motor".
Rumus:
=SUM(IF(FREQUENCY(IF(A17:A24<>"";IF(A17:A24=A27;MATCH("~"&B17:B24;B17:B24&"";0)));ROW(B17:B24)-ROW(B17)+1);1))
atau
=SUM((A17:A24=A27)*(A17:A24<>"")/COUNTIFS(A17:A24;A17:A24;B17:B24;B17:B24))

Dua rumus ini semuanya array formula (tekan tombol CTRL+SHIFT+ENTER secara bersamaan

atau rumus reguler

=SUM(IF(FREQUENCY(IF((A3:A10=D2)*(B3:B10<>"")*(A3:A10<>""); MATCH(B3:B10;B3:B10;0));ROW(B3:B10)-ROW(A3:A10)+1);1))

Sekian dari saya, salam semuanya.......................

Menampilkan Nama Sheet

Mungkin ada kalanya kita ingin mengetahui nama tab sheet yang terdapat di dalam worksheet tanpa harus repot menggeser ke kiri atau ke kanan nama sheetnya ( biasanya kalau sudah buanyak sheet-nya).

Apa sih nama sheet itu?

Nah ini dia, saya pakai contoh file saya sendiri xixi..... ada "2015", "2016" dan "2017", nah ini lah yang pengen kita tarik menggunakan rumus.

1. Menampilkan letak (path) dari sheet dan nama sheet:

Rumusnya : =CELL("filename";A1)

2. Menampilan letak (path) dari file excel ini:

Rumusnya: =LEFT(CELL("filename";A1);FIND("[";CELL("filename";A1);1)-1)

3. Menampilkan nama file excel ini:

Rumusnya:
=MID(CELL("filename");SEARCH("[";CELL("filename"))+1; SEARCH("]";CELL("filename"))-SEARCH("[";CELL("filename"))-1)
atau
=REPLACE(LEFT(CELL("filename";A1);FIND("]";CELL("filename";A1))-1);1;FIND("[";CELL("filename";A1));"")

4. Menampilkan nama sheet yang aktif::

Rumusnya:
=TRIM(RIGHT(SUBSTITUTE(CELL("filename";A1);"]";REPT(" ";99));99))
atau
=MID(Sheets;FIND("]";Sheets)+1;255)

*Note: Rumus akan bekerja jika workbook/file excel tersebut sudah di save terlebih dahulu, jika belum rumus akan menampilkan #NAME? alias tidak dikenali.

Trik Lainnya, Menampilan List dari semua sheet yang ada:

Langkah sebelum rumus:
1. Klik "Define Name" pada tab formula
2. Ketiklah nama misalnya "Sheets"
3. Copy-lah kode ini ke dalamnya =TRANSPOSE(GET.WORKBOOK(1))&T(NOW())
4. Enter "Ok"
5. Kemudian simpanlah file anda sebagai "Excel Macro Enabled Worksheet"

Barulah ketikkan rumus ini:
=IFERROR(INDEX(MID(Sheets;FIND("]";Sheets)+1;255);ROW(A1);1);"")
kemudian copas kebawah sampai hasilnya blank.

FILE LATIHAN

Menghitung Kelompok Umur 2

Ass.Wr.Wb

Senang rasanya setiap kali bisa memberikan sesuatu kepada para pembaca sekalian, nah apakah anda pernah membaca posting pertama saya? Disini Menghitung Kelompok Umur , sebetulnya saua mau update postingan tersebut, tetapi saya pikir ulang lebih saya buat yang baru dengan penyempurnaan atau tambahan. Serta untuk mengakomodasi beberapa pertanyaan yang ingin contoh menghitung dengan beberapa kriteria.

Tidak jauh berbeda dengan tabel kasus yang pertama

Menghitung Umur dengan kriteria (Age Grouping)
1. Kurang dari 30 Tahun
2. 30 - 40 Tahun
3. 41 - 50 Tahun
4. Lebih dari 50 Tahun

Kemudian ada tambahan kriteria, yaitu gender grouping, dihitung berdasarkan jenis kelamin Laki-laki dan Perempuan. Nah pembahasannya satu2x dan saya buatkan rumus untuk 3 kasus.  

KASUS 1 (Age Grouping saja):
a. Kurang dari 30 Tahun =COUNT(IF(C3:C12<30;C3:C12))
b. 30 - 40 Tahun =COUNT(IF(C3:C12>=30;IF(C3:C12<=40;C3:C12)))
c. 41 - 50 Tahun =COUNT(IF(C3:C12>=41;IF(C3:C12<=50;C3:C12)))
d. Lebih dari 50 Tahun =COUNT(IF(C3:C12>50;C3:C12))
  
 KASUS 2 (Age dan Gender Grouping):
a. Kurang dari 30 Tahun
=COUNT(IF(C3:C12<30;IF(B3:B12="Laki-laki";C3:C12)))
=COUNT(IF(C3:C12<30;IF(B3:B12="Perempuan";C3:C12)))
b. 30 - 40 Tahun
=COUNT(IF(C3:C12>=30;IF(C3:C12<=40;IF(B3:B12="Laki-laki";C3:C12))))
=COUNT(IF(C3:C12>=30;IF(C3:C12<=40;IF(B3:B12="Perempuan";C3:C12))))
c. 41 - 50 Tahun
=COUNT(IF(C3:C12>=41;IF(C3:C12<=50;IF(B3:B12="Laki-laki";C3:C12))))
=COUNT(IF(C3:C12>=41;IF(C3:C12<=50;IF(B3:B12="Perempuan";C3:C12))))
d. Lebih dari 50 Tahun
=COUNT(IF(C3:C12>50;IF(B3:B12="Laki-laki";C3:C12)))
=COUNT(IF(C3:C12>50;IF(B3:B12="Perempuan";C3:C12)))

Nah rumus yang saya tampilkan disini adalah rumus array (harus tekan tombol CTRL+SHIFT+ENTER secara bersamaan), tetapi jangan kuatir, pada file latihan saya, ada banyak contoh rumus menggunakan SUM+IF, SUMPRODUCT dan COUNTIFS.

KASUS 3:

Nah yang ini kita ingin mengelompokkan umur berdasarkan kriteria yang kita buat, hasil atau rumus yang ingin dibuat adalah pada kolom warna kuning (Kolom D).
Rumus:
=IF(OR(C3="";ISTEXT(C3));"";LOOKUP(C3;{0;30;41;51};{"<30 Tahun";"30-40 Tahun";"41-50 Tahun";">50 Tahun"}))

FILE LATIHAN

KONVERSI HIJRIYAH KE MASEHI DAN SEBALIKNYA

Halo and Greetings guys........................

Saya berusaha membuat konverter tanggal hijriyah ke masehi dengan mencari informasi di internet mengenai cara perhitungannya, tetapi selalu gagal, karena banyak penjelasan yang kurang lengkap dan memadai. Kemudian saya menemukan disini:

repo.unnes.ac.id dan dibuat oleh Andi Pangerang, beliau memberikan step by step kalkulasinya, salut kepada bapak Andi Pangerang. Kemudian saya mencoba meringkasnya tanpa menggunakan banyak tabel lookup, tetapi berdampak rumusnya panjang.. yah ndak papalah. Saya juga membuatkan konversi dari Masehi ke Hijriyah dengan memanfaatkan fasilitas dari MS Excel.

Inilah image dari konverter tersebut:
1. KONVERSI HIJRIYAH KE MASEHI

2. KONVERSI MASEHI KE HIJRIYAH

Silahkan anda DOWNLOAD FILE

Deal with Dates Before 1900 (Menghitung Umur Sebelum Tahun 1900)

Ass.Wr.Wb.

Salam semuanya, tulisan saya kali ini masih berkaitan dengan tanggal, mudah2xan tidak membuat bosan :) , belum lama ini saya mencoba untuk mencari cara untuk membuat rumus menghitung umur di postingan saya di "menghitung umur tanpa fungsi datedif", bisa bekerja untuk kalkulasi tahun sebelum 1900. Entah kenapa Microsoft tidak mendukungnya di MS Excel, pasti error jika menggunakan rumus. Saya coba cek tanggal yang seperti ini misal 8/5/1456 , saya test menggunakan =ISNUMBER

dan hasilnya:

Ya hasilnya FALSE, berarti MS Excel tidak mengenali tanggal tersebut karena tahunnya sebelum 1900, dengan kata lain anda mengetikkan tanggal "1456" maka tanggal tersebut terformat sebagai text bukan angka. Saya coba eksperimen selalu gagal akhirnya ketemu, yaitu dengan menambahkan angka tahun dengan jumlah angka tertentu supaya ter-konversi menjadi angka. Saya coba angka2x kecil +200, +300, +400, tetapi belum benar hasilnya. Akhirnya ketemu angka 7000, saya coba tambahkan angka diatas itu sudah tidak bisa alias error. Nah kita buka rumusnya dengan 2 kasus. Saya memakai contoh menghitung umur dari tanggal lahir (start date) dan meninggal (end date), anda bisa menggunakan istilah lain, mungkin end date bisa diganti dengan =TODAY untuk menghitung umur sampai hari ini

Kasus 1

Mungkin tidak semua orang butuh ya, kenapa harus menghitung umur pada abad 1? Ya untuk membuktikan rumus berhasil. Dan rumus ini juga bisa digunakan untuk tahun setelah 1900.
Rumus:
=DATEDIF(DATE(B2+7000;C2;D2);DATE(B3+7000;C3;D3);"y")&" Tahun"&" "&IF(DATEDIF(DATE(B2+7000;C2;D2);DATE(B3+7000;C3;D3);"MD")>=0;DATEDIF(DATE(B2+7000;C2;D2);DATE(B3+7000;C3;D3);"YM");DATEDIF(DATE(B2+7000;C2;D2);DATE(B3+7000;C3;D3);"YM")-1)&" Bulan"&" "&IF(DATEDIF(DATE(B2+7000;C2;D2);DATE(B3+7000;C3;D3);"MD")>=0;DATEDIF(DATE(B2+7000;C2;D2);DATE(B3+7000;C3;D3);"MD");DATEDIF(DATE(B2+7000;C2;D2);DATE(B3+7000;C3;D3);"MD")+31)&" Hari"

Kasus 2

Untuk kasus ke-2 ini rumusnya sangat puanjaang...... sehingga tidak saya tulis disini, tetapi saya sediakan di file exercise, rumusnya mudah2xan akurat, bisa digunakan untuk semua tahun ya, baik sebelum maupun setelah tahun 1900.

FILE LATIHAN

Merubah Desimal ke Tahun dan Bulan

Selamat pagi readers...bahagia bisa posting lagi, kali ini tentang desimal untuk di konversi ke "Tahun" dan "Bulan". Misal seperti ini di cell A1=1, maka Excel menerjemahkannya sebagai 1 Tahun, kemudian 1,5 diterjemahkan menjadi 1 Tahun 6 Bulan.

Langkahnya:
A. TAHUN
Anda bisa menggunakan fungsi INT atau TRUNC untuk mengambil tahun, ke-2 fungsi ini mengambil angka di depan koma pada desimal. Kasusnya dan langkah penyelesaiannya silahkan diikuti dibawah ini:

Asumsi di cell A1 ada angka 1,5.
a.=INT(A1) atau =TRUNC(A1;0)
b. Tambahkan dengan teks "Tahun", menjadi =INT(A1)&" Tahun" atau =TRUNC(A1;0)&" Tahun ", ada spasi ya diantara kata "Tahun".
c. Untuk menghindari muncul "0 Tahun" jika kriteria di A1 dibawah 1, misal 0,5, maka "0 Tahun" ditampilkan sebagai blank atau teks kosong, secara rumus: =IF(INT(A1);INT(A1)&" Tahun";"") atau =IF(TRUNC(A1;0);TRUNC(A1;0)&" Tahun ";"")

B. BULAN
 Nah untuk bulan, angka yang diambil dari desimal adalah angka yang dibelakang koma,
a. Rumusnya =INT((A1-INT(A1))*12) atau =TRUNC((A1-INT(A1))*12), hasilnya "6", artinya 6 Bulan, silahkan ditambahkan saja dengan teks "Bulan" dibelakangnya.
b. Ditambahkan kondisi menampilkan teks kosong atau blank jika krierianya bukan desimal, misal angka 1,2 dst. Rumusnya menjadi =IF((A1-INT(A1))*12;INT((A1-INT(A1))*12)&" bulan ";"") atau =IF((A1-INT(A1))*12;TRUNC((A1-INT(A1))*12)&" bulan";"")

Nah semuanya ketemu baik "Tahun" dan "Bulan", finalisasi rumus menjadi:
1. =IF(INT(A1);INT(A1)&" tahun ";"")&IF((A1-INT(A1))*12;INT((A1-INT(A1))*12)&" bulan ";"")
2. =IF(TRUNC(A1;0);TRUNC(A1;0)&" tahun ";"")&IF((A1-INT(A1))*12;TRUNC((A1-INT(A1))*12)&" bulan";"")

Sekian dari saya
Salam semuanya
Rizky

Mengetahui Zodiak, Weton Dan Shio Dari Tanggal

Ass.Wr.Wb.

Salam semuanya dan senang menjumpai anda semuanya kali ini, tulisan saya sekarang ini sebetulnya perbaikan postingan saya yang berjudul "Kumpulan Trik Rumus untuk Tanggal", jadi saya hapus dan digantingan dengan perbaikan serta tambahan di tulisan ini.

Postingan membahas tentang mengetahui zodiak/bintang, weton dan shio dari tanggal lahir anda, hanya sebatas untuk pengetahuan saja, tidak usah mempercayai ramalannya ya...
Tidak lupa juga rumus untuk konversi tanggal Masehi ke tanggal Hijriyah.

1. ZODIAK

Hasil yang diinginkan

Rumus yang menggunakan tabel data zodiak ada 2:
1. =IF(E2="";"-";INDEX($A$4:$A$15;MATCH(1;IF((DATE(2000;MONTH(E2);DAY(E2))>=  DATE(2000;MONTH($B$4:$B$15);DAY($B$4:$B$15)));1;0);1)))
2. =IF(E2="";"-";INDEX(A$3:A$15;MATCH(1;IF(--TEXT(E2;"dmmm")>=--TEXT(B$3:B$15; "dmmm");IF(--TEXT(E2;"dmmm")<=--TEXT(C$3:C$13;"dmmm");1));1)))

Semuanya formula array, jadi jangan lupa tekan tombol CTRL+SHIFT+ENTER bersamaan.

Rumus tanpa menggunakan tabel ada 2 juga:
1. =IF(E7="";"-";LOOKUP(--TEXT(E7;"mdd");{101;121;220;321;421;521;622;723;824;923;1023;1123;1222};{"Capricorn";"Aquarius";"Pisces";"Aries";"Taurus";"Gemini";"Cancer";"Leo";"Virgo";"Libra" ;"Scorpio";"Sagittarius";"Capricorn"}))
2. =IF(E7="";"-";INDEX({"Capricorn";"Aquarius";"Pisces";"Aries";"Taurus";"Gemini";"Cancer";"Leo" ;"Virgo";"Libra";"Scorpio";"Sagittarius";"Capricorn"};MATCH(MONTH(E7)*100+DAY(E7);{0;121 ;220;321;421;522;622;723;823;924;1024;1123;1222})))
Wah panjangnya.....

2. WETON

Weton adalah perhitungan jawa untuk pernikahan atau nasib berdasar hari kelahirannya dan merupakan peninggalan budaya jawa.

Rumus menggunakan tabel ada 2:
1. =VLOOKUP(MOD(E2;7);A3:B9;2) &" " &VLOOKUP(MOD(E2;5);A3:C9;3)
 2. =INDEX(B3:B9;MATCH(MOD(E2;7);A3:A9;0))&" "&INDEX(C3:C7;MATCH(MOD(E2;5); A3:A7;0))

Rumus tanpa tabel lookup ada 2:
1. =TEXT(E7;"dddd")&" "&CHOOSE(MOD(E7;5)+1;"Kliwon";"Legi";"Pahing";"Pon";"Wage")
2. =TEXT(WEEKDAY(E7);"dddd")&" "&CHOOSE({1\2\3\4\5};INDEX({"Kliwon";"Legi";"Pahing" ;"Pon";"Wage"};MOD(E7;5)+1))

3. SHIO

Rumus menggunakan tabel lookup:
1. Shio =IF(D10="";"-";VLOOKUP(MOD((YEAR(D10)-4);12);A3:B14;2))
2. Elemen =IF(D10="";"-";VLOOKUP(INT(MOD((YEAR(D10)-4);10)/2);D3:E7;2))

Rumus tanpa tabel lookup:
1. Shio
=IF(D13="";"";INDEX({"Tikus";"Kerbau";"Macan";"Kelinci";"Naga";"Ular";"Kuda";"Kambing"; "Monyet";"Ayam";"Anjing";"Babi"};MATCH(MOD((YEAR(D13)-4);12);({0;1;2;3;4;5;6;7;8;9;10;11}) ;0)))
2. Elemen  
=IF(D13="";"";LOOKUP(INT(MOD((YEAR(D13)-4);10)/2);{0;1;2;3;4;5;6;7;8;9;10;11};{"Kayu";"Api" ;"Tanah";"Logam";"Air"}))

4. KONVERSI TANGGAL MASEHI KE HIJRIYAH

Sebetulnya pada Excel sudah dibekali kode untuk konversi tanggal masehi atau gregorian date ke hijriyah yaitu "b2" yang digabungkan dengan Fungsi TEXT. Tetapi tidak ada salahnya saya memberikan contohkan dengan menggunakan tabel.

Rumus dengan tabel lookup:
=TEXT(C2-1;"b2d")&" "&INDEX(A2:A13;TEXT(C2-1;"b2m"))&" "&TEXT(C2-1;"b2yyyy")

Rumus tanpa tabel, ada 2:
1. =TEXT(C5-1;"b2d ")&INDEX({"Muharram";"Shafar";"Rabi'ul Awwal";"Rabi’ul Akhir";"Jumadil Awwal";"Jumadil Akhir";"Rajab";"Sya'ban";"Ramadhan";"Syawwal";"Dzulqa'dah";"Dzulhijjah"} ;TEXT(C5-1;"b2m"))&" "&TEXT(C5-1;"b2yyyy")
2. =TEXT(C5-1;"b2mmm d"&" ,yyy")

Sudah capek? beluuum dong...... pengennya nambahin lagi, ada tambahan trik rumus untuk tanggal, tetapi tidak saya tulis disini tetapi di FILE LATIHAN, silahkan di download dan di pelajari. Mudah2xan ada manfaatnya.

Wass.Wr.Wb.

Download File Latihan

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