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