Untuk bahan renungan, menggunakan cara yang butuh kolom bantu saja ya. Kolom bantu ada di kolom J mulai J4. Data hasil summary ada di kolom K mulai K4. Cell K3 adalah header data hasil summary. Ini adalah array formula di kolom bantu (cell J4). Karena array formula, maka enter dengan Ctrl Shift Enter.
=*MIN(* *IF(* * COUNTIF($K$3:$K3,$C$4:$E$7) ** , "" ** , ( * * ROW($C$4:$E$7)-ROW($B$3)* * )* **1000* + *COLUMN($C$4:$E$7)-COLUMN($B$3)* * **)** * * )* Bahasa manusianya : "Cari nilai terendah [MIN] dari nilai-nilai hasil suatu kalkulasi berdasar kondisi [IF], yang kondisinya adalah jumlah cacah dengan 1 kriteria [COUNTIF], dari nilai-nilai data [*$C$4:$E$7*] yang terdapat di hasil summary (mulai dari header) [*$K$3:$K3*] yang bergerak dinamis running (perhatikan penggunaan absolute reference -- karakter $ -- pada sisi baris). Ketika kondisi itu (yang hasil CountIF) *bukan nol* (bernilai TRUE), maka hasil kalkulasi kondisi [IF] adalah NULLSTRING [""] . Sedangkan ketika hasilnya adalah *nol *(bernilai FALSE), maka hasil kondisi [IF] adalah nilai-nilai kombinasi indeks posisi data dengan susunan nilai_index_baris diikuti 3 digit nilai_indeks_kolom data [* ( * *ROW($C$4:$E$7)-ROW($B$3)* * )* **1000* + *COLUMN($C$4:$E$7)-COLUMN($B$3) *] " Jadi, hasilnya berupa nilai komposit dengan struktur RRRRCCC [R = indeks baris; C = indeks kolom] seperti nilai 1001 yang berarti data dengan indeks baris 1 dan indeks kolom 001 Fungsi perkalian 1000 [**1000*] adalah untuk mendapatkan indeks kolom yang selalu terdiri atas 3 digit, seperti 001 di atas. Nilai komposit ini adalah nilai posisi data yang belum ada di hasil summary (berdasar fungsi COUNTIF). Karena parameter kriteria pada COUNTIF adalah berupa array data, maka formula harus menjadi array formula, yang artinya, harus di-Enter dengan 3 tombol yaitu Ctrl Shift Enter. Setelah kolom helper didapatkan, maka *pengambilan *data berdasar nilai komposit bisa dilakukan menggunakan fungsi Index. Fungsi Index memiliki syntax : =Index( array_data_yang_akan_*diambil* , nilai_indeks_baris_si_data_yang_akan_*diambil * , nilai_indeks_kolom_si_data_yang_akan_ *diambil* ) ** penggunaan kata diambil atau pengambilan di atas, untuk kondisi membutuhkan nilai dari si range yang dirujuk. Artinya : fungsi Index adalah fungsi lookup and reference yang hasilnya adalah sebuah object (benda) berupa range atau item array. Tentu di benda atau item array itu ada nilai propertinya, yang akan diambil itu tadi. Oleh sebab itu, pada hasil summary diberi formula : =IF( N(J4) , * INDEX( $C$4:$E$7 , INT(J4/1000) , RIGHT(J4,3) )* , "" ) Bagian yang di-bold adalah pokok formula pengambilan data sebagai hasil summary Fungsi N digunakan untuk mengambil nilai numerik suatu ekspresi atau nilai data. Hasil array formula di kolom helper akan menghasilkan 0 bila data unique telah tersusun semua di hasil summary. Pada fungsi kondisi [IF], ketika kondisi bukan nol, maka akan bernilai TRUE. Kondisi bernilai nol, maka kondisi bernilai FALSE. Ketika fungsi N menghasilkan 0 (karena nilai kolom helper bernilai 0 atau berupa teks), maka kondisinya [IF] bernilai FALSE dan akan menghasilkan NULLSTRING. Jika tidak, maka fungsi Index akan dikalkulasi. Hasil di kolom helper selalu tetap, yaitu RRRRCCC. Oleh sebab itu untuk nilai_indeks_baris_si_data_yang_akan_*diambil* bisa didapat dengan *INT(J4/1000)* karena fungsi INT yang membulatkan kebawah, sedangkan pembagian 1000 [ *J4/1000* ] adalah untuk menghilangkan sisi nilai indeks kolom yang tersimpan dalam nilai hasil kolom helper. Untuk nilai_indeks_kolom_si_data_yang_akan_*diambil* bisa didapat dengan * RIGHT(J4,3)* karena susunan RRRRCCC selalu tetap pada sisi indeks kolom yang selalu 3 digit (akibat perkalian 1000 -- masih ingatkan) Demikian cerita singkat sebagai dongeng sebelum tidur. *Setelah bosan dengan cerita dengan kolom bantu *;) Nah, bagaimanakah dengan cara menyusun array formula tanpa kolom bantu di kolom L ? Perhatikan array formula helper : =*MIN(* *IF(* * COUNTIF($K$3:$K3,$C$4:$E$7) ** , "" ** , ( * * ROW($C$4:$E$7)-ROW($B$3)* * )* **1000* + *COLUMN($C$4:$E$7)-COLUMN($B$3)* * **)** * * )* dan formula pengambil nilai data : =IF( N(J4) , * INDEX( $C$4:$E$7 , INT(J4/1000) , RIGHT(J4,3) )* , "" ) Karena hasil summary nanti ada dikolom L (mulai L4), maka semua rujukan ke kolom K, yaitu pada bagian CountIF di array formula helper, harus diubah menjadi merujuk ke kolom L, yang menjadi : =*MIN(* *IF(* * COUNTIF($L$3:$L3,$C$4:$E$7) ** , "" ** , ( * * ROW($C$4:$E$7)-ROW($B$3)* * )* **1000* + *COLUMN($C$4:$E$7)-COLUMN($B$3)* * **)** * * )* Kemudian, pada formula pengambil data yang merujuk ke J4 (alias hasil kolom helper) harus diganti (di-substitusi) dengan susunan formula yang baru diperbaiki tadi (dari K jadi L). Maka formula : =IF( N(J4) , * INDEX( $C$4:$E$7 , INT(J4/1000) , RIGHT(J4,3) )* , "" ) akan berubah menjadi : =IF( N(* MIN(** IF( COUNTIF($L$3:$L3,$C$4:$E$7) , "" , ( ROW($C$4:$E$7)-ROW($B$3) ) *1000 + COLUMN($C$4:$E$7)-COLUMN($B$3) ) ) * ) , * INDEX( $C$4:$E$7 , INT(** MIN(** IF( COUNTIF($L$3:$L3,$C$4:$E$7) , "" , ( ROW($C$4:$E$7)-ROW($B$3) ) *1000 + COLUMN($C$4:$E$7)-COLUMN($B$3) ) ) **/1000) , RIGHT( **MIN( IF( COUNTIF($L$3:$L3,$C$4:$E$7) , "" , ( ROW($C$4:$E$7)-ROW($B$3) ) *1000 + COLUMN($C$4:$E$7)-COLUMN($B$3) ) )** ,3) )* , "" ) Kira-kira demikianlah. Regards. Kid. 2011/10/17 hasna <[email protected]> > ** > > > Dear Master, > > Terima kasih Mr. Kid, ternyata sumproduct bisa dipakai juga sebagai > pengganti countif. untuk kasus 1 walaupun sedikit masih bingung, tapi akan > saya pelajari lebih lanjut. > Sekali lagi terima kasih atas pencerahannya. > > hasna > ------------------------- > [email protected] > > > >
