hehehe... sorry baru sempet. formula A : unique berdasar 2 kriteria (kriteria 1 [kolom A] dan kriteria 2 [kolom B]), untuk nilai kriteria 1 yang tertentu =SUMPRODUCT( ( A2:A28=A38 ) / COUNTIFS ( A2:A28,A2:A28,B2:B28,B2:B28 ) )
formula B : unique berdasar 2 kriteria (kriteria 1 [kolom A] dan kriteria 2 [kolom B]), pada semua nilai kriteria 1 =sumproduct( 1/countifs ( a1:a10,a1:a10,b1:b10,b1:b10 )) Formula A tanpa kolom bantu untuk seluruh versi Excel di link : https://groups.yahoo.com/neo/groups/belajar-excel/conversations/messages/33238 adalah : =SUM(1*(FREQUENCY(*IF(A2:A28=A38,*MATCH(B2:B28,B2:B28,0)*)* ,ROW(A2:A28)-1)>0)) yang di-biru adalah pemilih data agar bagian Match hanya menghitung unik dari kriteria 2 [kolom B] karena kriteria 1 [kolom A] sudah pasti hanya berisi 1 item yang sama. Formula B tidak membutuhkan bagian filter kriteria 1 [kolom A] untuk satu item tertentu. Berarti bagian yang di-biru harus dibuang. Rujukan ke range disesuaikan dengan data untuk kasus formula B. =Sum( 1*(Frequency(MATCH(b1:b10,b1:b10,0),*ROW(a1:a10)-1*) >0) ) Sampai disini, masih ada yang mengganjal, yaitu : 1. bagian yang dilatar kuning. Bagian Match ini hanya berdasar kriteria 2 [kolom B] sedangkan data unik inginnya berdasar kriteria 1 [kolom A] juga. Maka perlu disusun data komposit dari kedua kriteria tersebut dengan cara menggabungkannya disertai sebuah delimiter. Misal pakai pipe delimiter. MATCH(*a1:a10&"|"&*b1:b10,*a1:a10&"|"&*b1:b10,0) >> akibat nomor 1 ini, maka formula berubah menjadi : =Sum( 1*(Frequency(MATCH(*a1:a10&"|"&*b1:b10,*a1:a10&"|"&* b1:b10,0),*ROW(a1:a10)-1*) >0) ) 2. bagian yang *diwarna merah* Hasil Match suati range terhadap range dirinya pastilah selalu ada (tidak mungkin #N/A) dan minimal selalu 1. Maksimalnya adalah item terakhir si range data. Artinya, array_bin untuk fungsi frequency cukup dimulai dari nilai 1 sampai jumlah cell dalam range data yang dipakai oleh Match. Kalau begitu, bagian *ROW(a1:a10)* yang sudah menghasilkan larik angka mulai dari 1 sampai 10 (jumlah cell terakhir dari range data) sudah bisa memenuhi kebutuhan array_bin untuk fungsi Frequency. Jadi, bagian *-1* bisa dibuang. >> akibat nomor 2 ini, maka formula hasil pengubahan di nomor 1 akan berubah lagi menjadi : =Sum( 1*(Frequency(MATCH(*a1:a10&"|"&*b1:b10,*a1:a10&"|"&* b1:b10,0),*ROW(a1:a10)*) >0) ) Dari usaha penghilang ganjalan di atas, sudah didapat formula yang harus di-Enter sebagai array formula (pencet CTRL SHIFT ENTER) berbunyi : =Sum( 1*(Frequency(MATCH(*a1:a10&"|"&*b1:b10,*a1:a10&"|"&*b1:b10,0) ,*ROW(a1:a10)*) >0) ) Karena males pencet CTRL SHIFT ENTER, maka dipakailah fungsi Sum yang mampu bekerja dengan array data, yaitu si SumProduct. Jadilah formula bukan array formula : =Sum*Product*( 1*(Frequency(MATCH(*a1:a10&"|"&*b1:b10,*a1:a10&"|"&* b1:b10,0),*ROW(a1:a10)*) >0) ) Berhubung fungsi SumProduct relatif lambat dalam bekerja, maka perlu dianalisa seberapa parah dampak pengubahan formula dalam menggunakan si SumProduct. Setelah dicoba berulang kellez, disimpulkan bahwa : *** dampak penggunaan fungsi SumProduct tidak menurunkan kinerja kalkulasi Excel *** ( horeee.... ) Maka disimpulkan bahwa formula yang bisa digunakan adalah : =Sum*Product*( 1*(Frequency(MATCH(*a1:a10&"|"&*b1:b10,*a1:a10&"|"&* b1:b10,0),*ROW(a1:a10)*) >0) ) gitu kellez yai... Wassalam, Kid. 2014-09-05 19:45 GMT+10:00 odong nando [email protected] [belajar-excel] <[email protected]>: > > > itu ceritanya gini mas bagus, jadi wkt saya akan menghitung nilai unik > dari 2 kriteria, saya coba dengan formula > =sumproduct( 1/countifs ( a1:a10,a1:a10,b1:b10,b1:b10 )), tp karena saya > mengerjakannya di excel 2003 maka saya tidak dapat menggunakan fungsi > countifs. setelah itu sy coba ganti fungsi countif itu dengan fungsi > sumproduct dan sum, tapi malah salah hasilny ngaco. setelah itu, bos kid > menyarankan saya untuk melihat arsip milis ke alamat > > > https://groups.yahoo.com/neo/groups/belajar-excel/conversations/messages/33238 > > setelah sy buka, ternyata itu pertanyaan saya juga dengan kasus yang > berbeda. sy berfikir bahwa penjelasan yg ada di kasus saya yang lain adalah > sama dengan kasus saya yang ini. ya memang ada kemiripan. > > ternyata saya hanya faham yang menggunakan colom bantu dengan memanfaatkan > fungsi countif, setelah itu saya bertanya kembali, intinya saya menanyakan > formula yang dapat mengganti formula > " =sumproduct( 1/countifs ( a1:a10,a1:a10,b1:b10,b1:b10 )) ", tapi tanpa > kolom bantu, setelah itu bos kid menyarankan untuk menggunakan array > formula, saya dah utak atik array formula yang saya tau, tapi masih tidak > berhasil juga menghitung nilai unik multi kriteria di excel 2003 tanpa > colom bantu. > > thx, > > odong > > > Pada Jumat, 5 September 2014 15:41, "'Bagus' [email protected] > [belajar-excel]" <[email protected]> menulis: > > > > > Mas Odong; > > untuk yang ini: =sumproduct( 1/countifs ( a1:a10,a1:a10,b1:b10,b1:b10 )) > apa bisa dilampirkan contoh datanya..? > > > Wassalam > ~ Bagus ~ > > > ----- Original Message ----- > *From:* odong nando [email protected] [belajar-excel] > <[email protected]+[belajar-excel]> > *To:* [email protected] > *Sent:* Friday, September 05, 2014 3:22 PM > *Subject:* Bls: [belajar-excel] Hitung unique multi kriteria di excel 2003 > > > bos kid, > sepertinya formula = SUMPRODUCT( ( A2:A28=A38 ) / COUNTIFS ( > A2:A28,A2:A28,B2:B28,B2:B28 ) ) > tidak sama dengan =sumproduct( 1/countifs ( a1:a10,a1:a10,b1:b10,b1:b10 > )), > > klo memang maksud bos kid itu pakai rumus yang ada " FREQUENCY "nya, saya > dah coba utak-atik tapi mentok bos kid... > untuk cari nilai unik yang multi kriteria yang jalan di 2003. > > thx, > > odong > > > Pada Jumat, 5 September 2014 14:03, "'Mr. Kid' [email protected] > [belajar-excel]" <[email protected]> menulis: > > > > Hai Nando, > > Coba di-enter sebagai array formula (sudah ada keterangan sebagai array > formula). > Sudah kenal array formula ? Kalau belum, coba jalan-jalan kesini > <http://excel-mr-kid.blogspot.com.au/2011/03/array-formula-kenalan-yuuuk.html> > . > > Wassalam, > Kid. > > > > 2014-09-05 13:21 GMT+10:00 odong nando [email protected] > [belajar-excel] <[email protected]>: > > > dear bos kid, > > berdasarkan arahan bos kid, saya sudah lihat file ny, dan yang saya > mengerti untuk kasus ini hanya yang memakai kolom bantu, menjadi : > =sumproduct( 1/countif ( c1:c10,c1:c10 ) ) > dengan asumsi kolom batu ada di colum C. > tapi saya tidak berhasil memakai cara yang tanpa kolom bantu, > sudi kiranya bos kid untuk menunjukkan formula hitung unik multi kriteria > yang dapat jalan di excel 2003. > :) > > thx, > > odong > > > Pada Kamis, 4 September 2014 14:23, "'Mr. Kid' [email protected] > [belajar-excel]" <[email protected]> menulis: > > > > Coba ke arsip : > > https://groups.yahoo.com/neo/groups/belajar-excel/conversations/messages/33238 > > Wassalam, > Kid. > > > > 2014-09-03 20:00 GMT+10:00 odong nando [email protected] > [belajar-excel] <[email protected]>: > > > Dear all... > Apa formula subtitusi untuk : > =sumproduct( 1/countifs ( a1:a10,a1:a10,b1:b10,b1:b10 )) > Yg dpt jalan di excel 2003, alny dah coba countifs ny di ganti sum or > sumproduct, tp hasilnya ngacoo... :'( > br . > odong > > > > > > > > > > >
