Hai Asseggaf, Salah satu cara menghasilkan data terurut *disuatu tempat lain* adalah dengan *array formula* sorting. Salah satu array formula sorting untuk mengurutkan data di C2:G2 adalah : (hasil diletakkan di baris 20 mulai kolom C juga [C2:G20]) (misal untuk hasil di G20 -> hasil terkahir untuk memproses C2:G2) * =INDEX($C2:$G2,1,MOD(SMALL(COUNTIF($C2:$G2,"<"&$C2:$G2)*10+COLUMN($C2:$G2)-COLUMN($B2),COLUMN()-COLUMN($B20)),10)) * (copy formula ke cell hasil lainnya)
>> Bagi yang masih asing dengan array formula <<
- Tulis formula dan jangan tekan ENTER
- tapi tekan 3 tombol berikut bersamaan
CTRL SHIFT ENTER
>> ----------------------------------------------------- <<
*-- Data di C2:G2 --*
*z* *f* *a* *t* *e*
*-- Mengurutkan Data --*
Mengurutkan data bisa dilakukan dengan mengetahui jumlah cacah data yang
kurang dari dirinya.
Contoh : berapa jumlah cacah data di C2:G2 yang kurang dari
- nilai 'z' ? >> jawabnya : 4, yaitu f a t e
- nilai 'f' ? >> jawabnya : 2, yaitu a e
- nilai 'a' ? >> jawabnya : 0
- nilai 't' ? >> jawabnya : 3, yaitu f a e
- nilai 'e' ? >> jawabnya : 1, yaitu a
Maka jika disusun, jumlah cacah data yang kurang dari setiap nilai di C2:G2
:
*data : z f a t e
n : 4 2 0 3 1*
Bagaimana cara mendapatkan nilai-nilai n tersebut ?
Fungsi CountIF dapat digunakan untuk hal ini. Perhatikan bagian CountIF
pada array formula di atas :
*COUNTIF($C2:$G2,"<"&$C2:$G2)*
Bahas manusianya :
"Hitung jumlah cacah di C2:G2 yang kurang dari ("<") dirinya sendiri (*
&$C2:$G2*)"
karena kriteria dalam fungsi CountIF adalah *berupa banyak nilai data
kriteria yang diproses*, maka *hasilnya akan ada banyak nilai *juga. Ini
sering *disebut* *array*.
Hasil CountIF bisa digunakan untuk mengurutkan (perhatikan yang
di-*merah*[larik data dan larik n]
)
*- Jika ingin diurutkan Ascending, siapakah data c2:g2 yang harusnya
menjadi hasil pertama dalam pengurutan ? *
>> karakter 'a'
*- kenapa ? *
>> karena nilai n (jumlah data yang kurang dari karakter 'a') adalah 0
alias karakter 'a' adalah yang terkecil.
*- kok tahu, kalau 0 itu punyanya si 'a' ?*
>> karena nilai *0* di *larik n* memiliki nilai *'a'* di *larik data*.
Jadi untuk hasil sort urutan ke-2 dan seterusnya juga bisa didapat.
larik n bisa diurutkan Ascending dengan Small hingga menjadi :
n : 0 1 2 3 4
tapi, antara larik data dengan larik n jadi gak klop lagi. Yang 0 sekarang
ada di bawahnya 'z' dan yang 4 ada di bawahnya 'e'
*jadi, pengurutan larik n harus disertai larik data.*
Berhubung si Small itu bisanya hanya mengurutkan data numerik, maka untuk
nilai larik data yang tidak numerik ndak bisa diurutkan dengan small,
kecuali jika diganti dulu dengan angka. *Salah satu caranya adalah dengan
memanfaatkan nomor indeks kolom data.*
Kalau data di C2:G2 diganti dengan indeks kolom data, maka nilai di C2 bisa
diberi angka indeks kolom data 1 dan D2 bernilai 2 dan seterusnya.
Larik data dan larik n akan berteman dengan larik C sebagai berikut :
*C : 1 2 3 4 5
data : z f a t e
n : 4 2 0 3 1*
Sekarang data sudah bisa diwakili dengan nilai numerik. Sekarang, harus
disusun format gabungan larik C dan N yang mewakili hasil pengurutan.
Jika akan diurutkan ascending, harusnya nanti jadinya :
*C : 3 5 2 4 1
data : a e f t z
n : 0 1 2 3 4 *
Oh ternya dasar pengurutannya tetap berdasar larik N, yang diikuti dengan
larik C. Jadi kalau larik C nilainya 2, pasti nilainya data adalah 'f'
Untuk kebutuhan ini, maka dibuatlah format gabungannya, seperti N diikuti C
[NC] dan untuk kasus ini, masing-masing cukup 1 digit.
Contoh :
NC = 03 -> jumlah cacah datanya 0, dan indeks kolom datanya 3, maka data
yang bisa didapat untuk C bernilai 3 di C2:G2 adalah karakter 'a'
NC = 34 -> jumlah cacah datanya 3, dan indeks kolom datanya 4, maka data
yang bisa didapat untuk C bernilai 4 di C2:G2 adalah karakter 't'
Formulasi untuk menyusun NC ini adalah :
*nilai N* kemudian *dibuatkan ruang 1 digit dibelakangnya* yang kemudian *diisi
dengan nilai C*.
*N *** 10* *+ C*
*Data seperti gabungan NC ini sering disebut Composite Key.*
> Nilai N sudah diketahui didapat dari CountIF yang berbunyi :
* COUNTIF($C2:$G2,"<"&$C2:$G2)
*> Nilai C didapat dari indeks kolom Excel lokasi si data dikurangi indeks
kolom Excel dari 1 kolom sebelum data pertama (sebelum C2) yang berarti
indeks kolom Excel di kolom B2. Formulasinya :
* Column() - Column( $b2 )*
> jadi formulasi untuk menyusun NC sesuai kaidahnya : *N *** 10* *+ C*
*COUNTIF($C2:$G2,"<"&$C2:$G2) * 10 + **Column() - Column( $b2 )*
Larik NC akan berupa :
*C : 1 2 3 4 5
data : z f a t e
n : 4 2 0 3 1*
*NC : 41 22 03 34 15*
*Nilai *03 *akan tampak sebagai nilai *3* saja.
Sekarang, fungsi Small bisa digunakan untuk mengurutkan data secara
Ascending, sekaligus mengambil hasil pengurutan yang terkecil *ke-X*.
Nilai *X* sangat tergantung posisi cell hasil. Untuk hasil di G20 (sudah
disepakati di atas sana tentang lokasi hasilnya),
maka nilai X-nya adalah 5 karena C20 indeks kolom datanya adalah 1.
Jadi, nilai X juga bisa menggunakan formulasi penyusun larik C, tetapi
untuk baris Excel *20 *(baris Excel lokasi hasil)
*Column() - Column( $b20 )*
Lengkap sudah proses pengurutan dan pengambilan nilai berformat NC
menggunakan fungsi Small, yaitu : (potongan array formula yang berbunyi)
*SMALL( COUNTIF($C2:$G2,"<"&$C2:$G2) *10 + COLUMN($C2:$G2)-COLUMN($B2)
,COLUMN()-COLUMN($B20))*
*-- Mengekstrak data Composite Key --*
Kita sudah mendapatkan data composite key bernama NC yang merupakan
gabungan nilai jumlah cacah data disandingkan dengan 1 digit indeks kolom
data.
Formulasi penyusunnya adalah *N *** 10* *+ C* (perhatikan ** 10*)
Kunci struktur NC adalah dibagian ** 10*
Jika ingin mengambil nilai N, maka cukup dengan mengambil bilangan bulat
hasil pembagian NC dengan 10.
*Untuk kasus ini, yang dibutuhkan justru nilai C, karena nilai C akan
membawa kita ke nilai data di C2:G2.*
Banyak cara untuk mengambil nilai C dari composite key NC di atas. Salah
satunya adalah memanfaatkan fungsi Mod.
Fungsi Mod dapat mengambil nilai sisa bagi suatu bilangan data yang dibagi
dengan suatu bilangan pembagi.
Contoh :
- Nilai 5 yang dibagi dengan 2 akan menyisakan nilai 1 -> 2 x 2 = 4, untuk
mencapai 5 masih butuh 1 satuan lagi. :: di Excel >> =Mod( 5 , 2 ) ::
hasilnya 1
- Nilai 6 yang dibagi dengan 3 akan menyisakan nilai 0 -> 3 x 2 = 6, untuk
mencapai 6 masih butuh 0 satuan lagi. :: di Excel >> =Mod( 6 , 3 ) ::
hasilnya 0
Pada kasus ini,
- bilangan yang *dibagi *adalah hasil formula Small :
* SMALL( COUNTIF($C2:$G2,"<"&$C2:$G2) *10 +
COLUMN($C2:$G2)-COLUMN($B2) ,COLUMN()-COLUMN($B20))*
- bilangan *pembagi*nya adalah *10* yang menjadi pembentuk ruang digit C
pada formulasi NC : *N *** 10* *+ C*
Jadi formulasi untuk mengekstrak nilai C dari composite key NC adalah :
*Mod( bilangan yang dibagi , bilangan pembagi )*
yaitu :
*Mod( SMALL( COUNTIF($C2:$G2,"<"&$C2:$G2) *10 +
COLUMN($C2:$G2)-COLUMN($B2) ,COLUMN()-COLUMN($B20)) , 10 )*
*-- Mengambil data berdasar indeks kolom datanya --*
Sampai sejauh ini, telah didapatkan nilai indeks kolom data yang menjadi
akan menjadi hasil akhir. Indeks kolom data ini berupa posisi data pada
kolom tertentu relatif terhadap titik awal data. Untuk keperluan
pengambilan data dengan diketahui indeks kolom datanya, bisa memanfaatkan
fungsi Index.
*Index( referensi datanya , indeks baris data yang akan diambil , [indeks
kolom data yang akan diambil] )*
Pada kasus ini,
*referensi datanya *adalah data C2:G2
*indeks baris data yang akan diambil *adalah *1* (karena data hanya berisi
1 baris)
*[indeks kolom data yang akan diambil] *adalah nilai C hasil ekstrak data
composite key yang berbunyi :
*Mod( SMALL( COUNTIF($C2:$G2,"<"&$C2:$G2) *10 +
COLUMN($C2:$G2)-COLUMN($B2) ,COLUMN()-COLUMN($B20)) , 10 )*
Maka susunan formula pengambil datanya :
*Index( **C2:G2 , 1 , **Mod( SMALL( COUNTIF($C2:$G2,"<"&$C2:$G2) *10 +
COLUMN($C2:$G2)-COLUMN($B2) , COLUMN()-COLUMN($B20) ) , 10 ) )*
yang berupa array formula, karena bagian CountIF memproses banyak data
sekaligus dan menghasilkan banyak data juga yang berupa larik data (array).
-----------------------------------------------
File terlampir adalah contohnya.
Wassalamualaikum Wr. Wb.
Kid.
2012/9/16 Mohammed Asseggaf <[email protected]>
> **
>
>
> Halo semua,….
>
> lagi bingung nih mau nge sort row ,.. tapi ada beberapa row,…****
>
> ** **
>
> Bagaimana yah caranya****
>
>
>
>
re-sort per row_array_formula_sorting.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
