Mengambil Data Unik (Extract Unique Values)

Selamat siang everybody, it's nice to see you again..........kali ini tulisan saya bertemakan mengambil data unik, sepert biasa saja lihat kasus kit hari ini........

1. Mengambil data Unik Tanpa Kriteria

Kasus diatas jika table tidak ada yang kosong atau blank:
Rumus 1 (Array Formula):
=IFERROR(INDEX($A$3:$A$11; MATCH(0;COUNTIF($C$2:C2;$A$3:$A$11);0));"")
Rumus 2 (Non Array Formula):
=LOOKUP(REPT("Z";255);CHOOSE({1;2};"";INDEX($A$3:$A$11;MATCH(TRUE;INDEX(ISNA(MATCH($A$3:$A$11;$E$2:$E2;0));0);0))))
Rumus 3 (Non Array Formula):
=IFERROR(INDEX($A$3:$A$11;MATCH(0;INDEX(COUNTIF($F$2:F2;$A$3:$A$11);0;0);0));"")

Jika tabel terdapat data yang kosong/blank, maka rumus diatas akan gagal kalkulasinya:
Rumus 1 (Array Formula):
=IFERROR(INDEX($A$16:$A$24; MATCH(0;COUNTIF($C$15:C15;$A$16:$A$24&"");0));"")
Rumus 2 (Array Formula):
=IFERROR(INDEX($A$16:$A$24; MATCH(0;IF(ISBLANK($A$16:$A$24);1;COUNTIF($E$15:E15; $A$16:$A$24)); 0));"")
Rumus 3 (Array Formula):
=IFERROR(INDEX($A$16:$A$24;SMALL(IF(FREQUENCY(IF($A$16:$A$24<>"";MATCH("~"&$A$16:$A$24&"";$A$16:$A$24&"";0));ROW($A$16:$A$24)-ROW($A$16)+1);ROW($A$16:$A$24)-ROW($A$16)+1);ROWS($A$1:A1)));"")
Rumus 4 (Array Formula):
=IFERROR(INDEX($A$16:$A$24;AGGREGATE(15;6;IF(FREQUENCY(IFERROR(MATCH(IF($A$16:$A$24<>"";$A$16:$A$24);$A$16:$A$24;0);"");ROW($A$16:$A$24)-ROW($A$16)+1);ROW($A$16:$A$24)-ROW($A$16)+1);ROWS($A$1:A1)));"")

2. Mengambil data Unik Dengan Kriteria

 Nah Kasus ke-2 ini mengambil data unik dengan kriteria, misalnya contoh di atas kita ingin mengambil produk PT. ABC, dan di tabel terdapat data duplikat atau yang sama, dan itu tidak diinginkan, hanya data unik saja yang diambil.

Rumusnya:

=IFERROR(INDEX($B$2:$B$11;AGGREGATE(15;6;IF(FREQUENCY(IFERROR(MATCH(IF($A$2:$A$11=$A$14;$B$2:$B$11);$B$2:$B$11;0);"");ROW($B$2:$B$11)-ROW($B$2)+1);ROW($B$2:$B$11)-ROW($B$2)+1);ROWS($A$1:A1)));"")

FILE LATIHAN

Tentang Fungsi RAND dan RANDBETWEEN

Selamat Pagi Semuanya

Maaf sebelumnya jika saya semakin jarang posting atau menulis di blog ini, sekali lagi karena waktu dan kemampuan saya yang terbatas, mohon doanya untuk saya selalu produktif menulis. Tema kali ini adalah mengenai fungsi RAND dan RANDBETWEEN,fungsi ini berguna untuk mengacak angka dan juga bisa mengacak teks. Keperluannya biasanya untuk bidang statistik dan matematika, atau penelitian yang berhubungan dengan pemilihan sampel secara random. Tetapi disini saya mebahasnya dari sisi membuat rumusnya saja ya.

1. Fungsi RAND akan mengembalikan angka desimal secara acak, penulisannya :

Sedangkan Fungsi RANDBETWEEN :

Fungsi RANDBETWEEN ini akan mengembalikan bilangan bulat, pada contoh diatas rumus akan mengembalikan angka 1 sampai 10. Dan bagaimana cara mengacaknya? Cukup tekan tombol F9 saja. Kelemahan fungsi ini angkanya akan selalu berubah tidak bisa di-save, kecuali and copy kemudian di-paste ke cell yang lain kemudian paste as value. Anda juga bisa menerapkan kedua fungsi ini ke dalam beberapa cell. Seperti contoh ini :

Kelemahan yang lain dari RAND dan RANDBETWEEN adalah kemungkinan akan muncul angka yang sama, dengan kasus diatas.

PENERAPAN YANG LAIN:
2. Acak Angka Antara 10 dan 50
    =10+RAND()*40 atau =RANDBETWEEN(10;50)

3. Acak Angka Antara 2 dan 4 (1 desimal)
    =RANDBETWEEN(20;40)/10 atau =(20+INT(RAND()*41))/10

4. Acak Angka Kelipatan 10
    =ROUND(RANDBETWEEN(100;10000)/100;0)*10 atau =RANDBETWEEN(1;100)*10

5. Acak teks
    =CHOOSE(RANDBETWEEN(1;3);"Ayam Goreng";"Soto Kudus";"Rawon Setan")
    atau
    =INDEX({"Ayam Goreng";"Soto Kudus";"Rawon Setan"};RANDBETWEEN(1;3))
    Mohon diperhatikan ya angka 3 pada RANDBETWEEN harus disesuaikan dengan jumlah teksnya

6. Acak Teks Dengan Tabel

rumusnya =INDEX(A23:A27;RANDBETWEEN(1;5))

7. Acak angka Antara 1 Sampai 5 Tanpa Ada Duplikasi

Rumusnya (rumus array) :
=LARGE(ROW($1:$6)*NOT(COUNTIF($A$30:A30;ROW($1:$6)));RANDBETWEEN(1;7-ROW(A1)))

Mohon diperhatikan, pada ROW($1:$6), angka 6 harus disesuaikan dengan angka yang ingin berapa kali ditampilkan, kemudian pada RANDBETWEEN(1:7), angka 7 dari angka 6+1, jadi untuk contoh yang lain jika anda ingin menampilkan angka 10 x nerarti 10+1 = 11, jadi RANDBETWEEN(1;11-ROW($1:$10).

8. Acak Teks Tanpa Ada Duplikasi

Rumus Dengan Tabel (rumus array) :
=INDEX($A$40:$A$44;LARGE(MATCH(ROW($A$40:$A$44);ROW($A$40:$A$44))*NOT(COUNTIF($A$45:A45;$A$40:$A$44));RANDBETWEEN(1;ROWS($A$40:$A$44)-ROW(A1)+1)))
Rumus Tanpa Tabel:
=INDEX({"Fitzgerald";"Robert";"Caroline";"Patrick";"Mark"};LARGE(MATCH(ROW($A$7:$A$11);ROW($A$7:$A$11))*NOT(COUNTIF($C$45:C45;{"Fitzgerald";"Robert";"Caroline";"Patrick";"Mark"}));RANDBETWEEN(1;ROWS($A$7:$A$11)-ROW(A1)+1)))

9. Membuat ID/PIN dan Password
Nah ini yang menarik, ya membuat password, ya tentu bisa, tetapi sebelum itu coba perhatikan rumus dasar dibawah ini:
a.Menghasilkan Huruf Besar Secara Acak
   Semua Huruf
   =CHAR(RANDBETWEEN(65;90))  atau
   =CHAR(INT(RAND()* 25 + 65))  atau
   =CHAR(65+INT(RAND()*26))
   Hanya Huruf Hidup/Vokal
   =CHAR(CHOOSE(RANDBETWEEN(1;5);65;69;73;79;85))

b.Menghasilkan Huruf Kecil Secara Acak
   Semua Huruf
   =CHAR(RANDBETWEEN(97;122))  atau
   =LOWER(CHAR(INT(RAND()*25+65)))  atau
   =LOWER(CHAR(65+INT(RAND()*26)))
   Hanya Huruf Hidup/Vokal
   =LOWER(CHAR(CHOOSE(RANDBETWEEN(1;5);65;69;73;79;85)))

c.Menghasilkan Karakter Simbol Secara Acak
   =CHAR(RANDBETWEEN(33;47))

d.Menghasilkan Semua Huruf dan Simbol Secara Acak
=CHAR(TRUNC(RAND()*90+33))

e.Membuat ID/PIN atau Password Secara Acak

Rumus 1:
=RANDBETWEEN(0;5)&CHAR(45)&RANDBETWEEN(10;100)&CHAR(45)&RANDBETWEEN(101;1000)
Rumus 2:
=CHAR(RANDBETWEEN(65;90))&CHAR(RANDBETWEEN(97;122))&CHAR(RANDBETWEEN(97;122))&CHAR(RANDBETWEEN(97;122))&CHAR(RANDBETWEEN(65;90))&CHAR(RANDBETWEEN(65;90))&RANDBETWEEN(1;10000)
Rumus 3:
=RANDBETWEEN(1;2000)&CHAR(RANDBETWEEN(97;122))&CHAR(RANDBETWEEN(97;122))&CHAR(RANDBETWEEN(97;122))&CHAR(RANDBETWEEN(97;122))&CHAR(RANDBETWEEN(97;122))&CHAR(RANDBETWEEN(97;122))&CHAR(RANDBETWEEN(97;122))&CHAR(RANDBETWEEN(97;122))
Rumus 4:
=RANDBETWEEN(0;9)&CHAR(RANDBETWEEN(65;90))&CHAR(RANDBETWEEN(97;122))&CHAR(RANDBETWEEN(33;47))&RANDBETWEEN(0;9)&CHAR(RANDBETWEEN(65;90))&CHAR(RANDBETWEEN(97;122))&CHAR(RANDBETWEEN(33;47))

 Sekian dari saya, Wass.Wr.Wb.

File LATIHAN

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