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.
>>>>
>>>>
>>>
>>>
>>
>
>
