jalan ceritak rumus
=SUMPRODUCT((LEN(B5)-LEN(SUBSTITUTE(B5,ROW($1:$9),"")))*ROW($1:$9))
atau dlm bentuk lain:
=SUMPRODUCT((LEN(B5)-LEN(SUBSTITUTE(B5,{1,2,3,4,5,6,7,8,9},"")))*{1,2,3,4,5,6,7,8,9}
kedua rumus itu sebenarnya hampir sama dan sebangun karena
array konstanta {1,2,3,4,5,6,7,8,9} sangat mirip dengan array hasil dari
array formula ROW($1:$9)
kalau yang pertama berupa array mendatar, yang terakir ini menghasilkan
array membujur {1;2;3;4;5;6;7;8;9}
Dlm contoh, nilai di cell B5 adalah text: *"1287 dan 7821 bisa
dihitung"* panjang
(LEN) text ini = 27
Substitute seharusnya tidak perlu dijelaskan, karena dia hanya mengganti
karakter tertentu_1 dengan karakter tertentu_2. Jika karakter tertentu_2 =
"", artinya karakter tertentu_1
didalam data yg dirujuk akan di hilangkan.
misal Substitute("M1231",1,"") = melucuti semua karakter "1", sehingga
hasilnya "M23"
Jadi kalau [karakter "1" ] - nya ada 2 maka LEN pada hasil berkurang 2
Kalau misalnya dlm data yg dirujuk terdapat 13 karakter "1" maka LEN dari
hasilnya tentu
akan berkurang 13 jika dibanding dengan LEN sebelum dilucutinya [karakter 1]
Misalnya RUMUS tsb kita sederhanakan menjadi
=SUMPRODUCT((LEN(B5) - LEN(SUBSTITUTE(B5,{*1}*,"")) * {1})
hasil sementaranya
=SUMPRODUCT((27 - 25) * 1)
=SUMPRODUCT(2 * 1}
anda dapat mengatakannya sebagai angka SATU nya ada 2
= hasilnya = 2
Hasilnya hanya 2 karena angka yg kita lucuti hanya* angka 1*
*
*
*Sekarang anda bayangkan kalau rumusnya*
=SUMPRODUCT((LEN(B5) - LEN(SUBSTITUTE(B5,*{**1,2}*,"")) * {1,2})
artinya anda hanya berkehendak menjumlah angka 1 dan angka 2 yang ada
*Dengan rumus seperti ini*
=SUMPRODUCT((LEN(B5)-LEN(SUBSTITUTE(B5,{1,2,3,4,5,6,7,8,9},""))
)*{1,2,3,4,5,6,7,8,9}
kita akan *melucuti semua ANGKA* yg ada di B5
hasil sementara =
=SUMPRODUCT({*2,2*,0,0,0,0,*2,2*,0}*{1,2,3,4,5,6,7,8,9})
Anda dapat melihat ARTI array pertama yg terbentuk =
angka 1 dilucuti dua kali artinya angka 1 ada: 2
angka 2 dilucuti dua kali artinya angka 2 ada: 2
angka 7 dilucuti dua kali artinya angka 7 ada: 2
angka 8 dilucuti dua kali artinya angka 8 ada: 2
angka lain {3,4,5,6,9) = 0 / tidak ada yang dilucuti
bahwa semua angka yang ada selalu ada DUA, itu hanya kebetulan, sesuai data
saja.
lihat sekarang kita punya dua array {*2,2*,0,0,0,0,*2,2*,0}
*{1,2,3,4,5,6,7,8,9}
Jika tiap elemen pada* kedua Array* tsb diperkalikan dengan elemen yg
posisinya sama,
lalu tiap hasil perkalian diJUMLAH (itulah kerja si SUMRPDUCT) anda dapat
menghitung sendiri
hasil akhirnya *yaitu 36*
Kesimpulannya, rumus ini meneghitung: tiap JENIS ANGA (yaitu 1 s/d 9)
dilucuti berapa kali
BERAPA-KALI nya itulah yg dicatat dalam array pertama, nantinya untuk
mengalikan semua
angka (yaitu 1 s/d 9) yg ada di array ke 2
Mengapa angka 0 (eNol) tidak dilibatkan ? *{0,1,2,3,4,5,6,7,8,9}*
Karena eNol itu dikalikan brapa saja hasilnya tetap eNol, mengapa pulak
harus dipikirin ??
Emang Gue Pikirin ??
2010/8/24 HerrSoe <herr...@gmail.com>
>
>
> Terima kasih kapada *pak zain, pak anton* dan *mbak siti* yg telah
> memberikan rumus-rumus solusinya yg mentakjubkan
> Mohon maaf karena saat memposted pertanyaan "lampiran workbook contoh
> data" ketinggalan / llupa meng-attach-nya.
>
> 2 (dua) rumus dari pak Zain memang untuk dua kondisi data (numerik semua
> -vs- alfanumerik)
>
> Rumus pak anton sudah kami coba, dan berhasil baik jika data rujukannya
> = data bilangan, atau data yg *semua anggota karakternya = angka*
>
> Ada satu formula yg saya masih 'super-bingung' menangkap *sistem kerjanya*,
> yaitu
> formula dari mbak siti
>
> =SUMPRODUCT((LEN(B5)-LEN(SUBSTITUTE(B5;ROW($1:$9);"")))* ROW($1:$9))
> kok bisa ya??
> Mudah-mudahan mbeliau (dan atau temans lain) ada waktu lega untuk
> mendongengkan "jalan ceritaknya" biar aku bisa nyombong *"mendadak
> jenius"* ke temans meja sebelah. he he..
>
> terima kasih..
> HerrSoe
>
>
