oce dah... met mengembangkan ilmu yang sudah didapat. jangan lupa untuk semakin senang berbagi ilmu ya.
wassalam, Kid. On Sep 6, 2014 7:48 PM, "odong nando [email protected] [belajar-excel]" < [email protected]> wrote: > > > Wah, indah nya rasanya dpt ilmu baru... > Bos kid jgn bosen ya sy repotin minta tolong buatin rumus... :) > Bukannya saya menolak untuk menggunakan excel fersi terbaru, tp di kntor > sy cuma da excel 2003 itu jg cuma da di kompi sy n 2 kompi tmn sy. Yg lain > pake kingsoft wps yg gratisan...jd mau ga mau biar formula di semua kompi > dpt trpkai ya saya pake fersi 2003...so mungkin sy bisa ngerasain excel > fersi terbaru cuma di kantor yg baru jg kalee ya, ya doain ja ya bos kid n > rekan2 lain, biar sy dpt kantor yang baru yg udah pake excel > terbaru..hehe...n di kntor sy yg skrg sampai2 terbilang pun saya buat dari > formula, mo gmn lg wps gretongan ga kompitble ma makro, walaupun banyak > makro yg gratisan bwt trbilang, cuma bisa dipake bwt excel ja yg pake wps > ga bisa... > klo bos kid masih inget yg wkt da yg nanya terbilang n bos kid nunjukin > contoh2 formula trbilang n 1 arsip dgn macro di arsip milis, trus sy ga > bisa liat rasany dongkol banget, eh tp pak HK share contoh2 formula > terbilang dr jmn ny mbah siti. Rasany baru agak plong, ga penasaran lg. > Rasanya untuk formula terbilang 2 cell dengan 1 cell rujukan 1 cell hasil > terbilang bisa sampe 15 digit bilangan cacah, or 13 digit bilangan cacah > dengan,2 koma, masih jd andelan kantor sy... hehe.... > Www.Jadi-curhat.com > HªhªhªhªHªhª =)) º°˚˚°º > > Trimakasih banyak ya bos kid... > > br . > > odong > > ------------------------------ > * From: * 'Mr. Kid' [email protected] [belajar-excel] < > [email protected]>; > * To: * BeExcel <[email protected]>; > * Subject: * Re: [belajar-excel] Hitung unique multi kriteria di excel > 2003 > * Sent: * Fri, Sep 5, 2014 10:47:51 AM > > > > 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] >> *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 >> >> >> >> >> >> >> >> >> >> > >
