Terima kasih Mr Kid, nanti saya coba fahami dulu
2013/5/11 Mr. Kid <[email protected]> > ** > > > hehehe.... > tumben baru nongol lagi... > > 1. Mengapa harus ada *C1 ? =SUMIF($B$9:$B$22,A1,$C$9:$C$22)*C1 > >> supaya hasil akhir menampakkan nilai sum berdasar kriteria di A dan > terfilter melalui autofilter pada tabel data. > > 2. Apa fungsi formula di C1 > =1*(SUMPRODUCT(SUBTOTAL(3,OFFSET($B$8,ROW($B$9:$B$22)-ROW($B$8),0))*($B$9:$B$22=A1))>0) > >> C1 adalah cek keberadaan nilai kriteria di kolom A pada hasil > autofilter. > > > =1*(SUMPRODUCT(SUBTOTAL(3,OFFSET($B$8,ROW($B$9:$B$22)-ROW($B$8),0))*($B$9:$B$22=A1))>0) > bahasa manusianya : > "cel,cel, coba cek setiap record di kolom B > [OFFSET($B$8,ROW($B$9:$B$22)-ROW($B$8),0)], jika terfilter oleh autofilter > hitung jumlah cacah text di record tersebut [SUBTOTAL(3, .... )] kemudian > periksa juga, apakah setiap record (tidak peduli terfilter atau tidak) itu > memiliki nilai yang sama dengan nilai di A1 [($B$9:$B$22=A1)]. Jika > keduanya terpenuhi (tetap tampil dalam autofilter dan [*] nilainya sama > dengan A1), maka jumlahkan semuanya [SumProduct(....)] dan periksa apakah > jumlah ini [SumProduct] lebih dari 0. Kemudian konversikan agar menjadi > numerik dengan mengalikan 1 [1*]" > > Bagian 1* bisa dihilangkan. Itu hanya contoh jika hasilnya akan digunakan > secara langsung dalam bentuk tipe data numerik. > Pada kasus ini, dengan adanya proses perkalian dengan C1 dalam formula > SumIF, maka formula di C1 ini tidak perlu dikonversi menjadi bertipe > numerik. > Jadi, hasil SumProduct(...)>0 adalah TRUE setara 1 atau FALSE setara 0 > > Kira-kira penjelasan per bagiannya begini : > 1. bagian ROW($B$9:$B$22)-ROW($B$8) > >> adalah nomor indeks record, dimulai dari angka 1 sampai angka ke-N > sesuai jumlah record (tanpa header) > >> Row nya B9 - Row nya B8 = 1, Row nya B10 - Row nya B8 = 2, dst... > > 2. bagian OFFSET($B$8,ROW($B$9:$B$22)-ROW($B$8),0) > >> adalah pemilih sebuah cell di kolom B berpatokan dari B8 (absolute = > tetap) lompat ke bawah (baris berikutnya) sebanyak masing-masing hasil > bagian nomor 1 tadi, yaitu 1,2,3,dst. > >> karena cell patokan dalam Offset hanyalah sebuah cell, maka hasil > offset juga sebuah cell karena tidak ada definisi tinggi dan lebar area > hasil offset (alias menggunakan defaultnya). > >> hasil offset ini adalah suatu range dan bukan nilai range. Oleh sebab > itu bisa dirujuk oleh fungsi yang menuntut inputan berupa range seperti > SubTotal, CountIF, dsb > > 3. bagian SUBTOTAL(3, hasil Offset ) > >> adalah penghitung agregat berdasar ketampakan range. Jadi, kalau tidak > tampak akibat terfilter oleh autofilter, maka tidak diproses oleh subtotal. > Ini salah satu karakteristik dari fungsi SubTotal. > >> angka 3 adalah sebuah enumerasi yang mewakili fungsi agregat CountA. > Jadi akan menghitung cacah keberadaan data pada range yang diproses. > >> range yang diproses adalah hasil Offset (nomor 2) yang berupa sebuah > range. Karena nomor 2 berproses sekian banyak kali sebanyak jumlah record, > maka SubTotal ini juga akan berproses sebanyak jumlah record membentuk > suatu array data. > >> hasil subtotal berupa array nilai 0 (tidak ada data atau tidak tampak) > atau 1 (ada datanya dan tampak) sebanyak jumlah record, seperti > {1,0,0,1,0,1,dst} > > 4. bagian ($B$9:$B$22=A1) > >> adalah perbandingan setiap record data di B9:B22 dengan nilai di A1 > >> perbandingannya adalah sama dengan. Jika sama maka bernilai TRUE > (setara 1) dan jika tidak sama bernilai FALSE (setara 0) > >> hasil bagian ini adalah array data bernilai TRUE atau FALSE sebanyak > jumlah record juga seperti {TRUE,FALSE,dst...} > > 5. bagian * > >> adalah hubungan antara kedua kriteria, yaitu hasil Subtotal (nomor 3) > yang berisi array {1,0,dsb...} dengan hasil nomor 4 yang berupa array > {TRUE,FALSE,dsb...} > >> keduanya memiliki jumlah elemen array yang sama, yaitu sebanyak jumlah > record di B9:B22 > >> kedua kriteria ini harus dipenuhi seluruhnya, karena diminta penanya > adalah 'yang tampak di hasil autofilter' dan 'sesuai nilai di A1' > >> maka logika penghubung kedua kriteria adalah logika AND yang setara > perkalian yang memiliki notasi * > > 6. bagian SubTotal()*($B$9:$B$22=A1) > >> adalah proses cek kedua kriteria (nomor 3 dan nomor 4) itu terpenuhi > seluruhnya (*) atau tidak. > >> karena bentuknya perkalian, maka tipe numerik * tipe Boolean = tipe > numerik. > >> jadi array {1,0,dsb..} * {true,false,dsb...} akan mengashilkan array 1 > atau 0 seperti {1,0,1,1,0,dsb...} > >> elemen array hasil adalah sebanyak jumlah record juga. > > 7. bagian SumProduct() > >> menjumlahkan seluruh hasil nomor 6. > >> karakteristik SumProduct yang bekerja dengan inputan array maka formula > tidak perlu CTRL SHIFT ENTER ketika seluruh array yang diproses SumProduct > semuanya bisa memproses array secara langsung. Hal ini akan berbeda > ceritanya ketika ada fungsi IF dalam SumProduct, karena fungsi IF tidak > bisa bekerja sebagai array kalau tidak di enter dengan CTRL SHIFT ENTER > >> hasil bagian ini adalah sebuah nilai dan bukan lagi suatu array. > >> nilai hasilnya adalah 0 atau lebih dari 0. > >> jika hasilnya lebih dari 0, maka minimal ada 1 record yang 'tampak > dalam autofilter dan memiliki nilai sama dengan A1' > > 8. bagian SumProduct() > 0 > >> adalah bagian cek, apakah hasil SumProduct dapat diartikan ada record > yang 'tampak dalam autofilter dan memiliki nilai sama dengan A1' > >> hasil bagian ini adalah sebuah nilai dengan tipe Bolean (TRUE atau > FALSE). > >> TRUE berarti SumProduct menunjukkan ada record yang 'tampak dalam > autofilter dan memiliki nilai sama dengan A1' > > 9. bagian 1* ( SumProduct() > 0 ) > >> adalah proses konversi tipe data dari tipe boolean hasil nomor 8 > menjadi bertipe numerik > >> Boolean TRUE setara 1 dan Boolean FALSE setara 0 > >> nilai 1 artinya ada record yang 'tampak dalam autofilter dan memiliki > nilai sama dengan A1' > >> bagian ini adalah contoh jika hasil akan digunakan langsung. > >> pada kasus ini sebenarnya tidak dibutuhkan adanya bagian konversi si > nomor 9 ini, karena dalam formula SumIF di B1 ada proses perkalian dengan > hasil C1 yang cukup sampai proses nomor 8 saja (coba saja hilangkan bagian > 1* dari formula C1) > > Kira-kira demikianlah alurnya. > > hadeeh.. capek juga nulisnya... senam jari kok ya pas malem minggu ya... > harapannya, ada banyak manfaat dalam dongeng malem minggu di atas. > > tetap semangat belajar guys.... gak peduli malem minggu atau malem jumat > atau malem apapun.... > biar Indonesia yang kebanyakan hari libur ini bisa lebih maju > setidaknya para pengguna Excel-nya... > > :) > > Wassalam, > Kid. > > > > > > > > > > > > > > > > > 2013/5/11 Mansor <[email protected]> > >> ** >> >> >> Mr Kid, >> >> 1) Mengapa harus ada *C1 ? >> =SUMIF($B$9:$B$22,A1,$C$9:$C$22)*C1 >> >> 2) Apa fungsi formula di C1 >> >> >> =1*(SUMPRODUCT(SUBTOTAL(3,OFFSET($B$8,ROW($B$9:$B$22)-ROW($B$8),0))*($B$9:$B$22=A1))>0) >> >> >> 2013/5/11 Mr. Kid <[email protected]> >> >> ** >>> >>> >>> hehehe... >>> sambil menunggu yang suka VBA membuatkan script autofilter (moga-moga >>> ada), mungkin seperti file terlampir bisa menjadi salah satu alternatif >>> (tanpa VBA). >>> >>> Wassalam, >>> Kid. >>> >>> >>> >>> 2013/5/11 daniel_aho <[email protected]> >>> >>>> ** >>>> >>>> >>>> Terima kasih sekali atas tanggapan dari Pak Mr Kid & Pak lapendosol >>>> opik, >>>> >>>> sangat membantu, tapi untuk filter otomatis sesuai dengan checkBox yang >>>> dipilih, bisa ndak ya?hehhehehe seperti file terlampir, >>>> >>>> terima kasih. >>>> >>>> >>>> daniel >>>> >>>> >>>> >>>> On 5/10/2013 7:10 PM, Mr. Kid wrote: >>>> >>>> >>>> Mau coba yang agak ribet waktu buatnya ? >>>> >>>> File terlampir memanfaatkan properti linkedcells. >>>> >>>> Wassalam, >>>> Kid. >>>> >>>> >>>> >>>> 2013/5/10 daniel_aho <[email protected]> >>>> >>>>> >>>>> >>>>> Dear Para Master2 Excel, >>>>> >>>>> mohon dibantu pencerahannya, bagaimana rumus untuk menjalankan >>>>> checkbox >>>>> sehingga berfungsi sebagai filter data dan fungsi Sum sesuai dengan >>>>> checkbox yang dipilih, >>>>> berikut terlampir filenya untuk lebih jelasnya. >>>>> >>>>> mohon maaf jika selalu merepotkan, >>>>> >>>>> terima kasih sekali >>>>> >>>>> daniel. >>>>> >>>>> >>>> >>>> >>> >> > >
