Re: [belajar-excel] Total Angka dalam 1 Cell
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
RE: [belajar-excel] Total Angka dalam 1 Cell
Pakai rumus array (ctrl+shit+enter): =SUM(--MID(TEXT(A1,REPT(0,99)),ROW($1:$99),1)) Asumsi jika di A1 adalah berupa angka. kalo yang umum (bisa text atau angka) =SUM(IF(ISNUMBER(--MID(A6REPT(_,99-LEN(A6)),ROW(1:99),1)),--MID(A6REPT( _,99-LEN(A6)),ROW(1:99),1),0)) _ From: belajar-excel@yahoogroups.com [mailto:belajar-ex...@yahoogroups.com] On Behalf Of HerrSoe Sent: Monday, August 23, 2010 2:22 PM To: belajar-excel Subject: [belajar-excel] Total Angka dalam 1 Cell mohon bantuan bagaimana rumus-nya (rumus biasa :tanpa bantuan makro) untuk menjumlah semua karakter angka yg ada dlm satu cell misal NOMOR 1234567890 hasil yg diinginkan : 45 (hasil dari 1+2+3+4+5+6+7+8+9+0) Tanggal 31 Des 2009 jam 16:30 hasil yg diinginkan : 25 2500,34 hasil = 14 dst Sebelumnya diucapkan terima kasih. Terima kasih juga kpd Bp Anton atas kiriman solusinya (pemanfaatan Advanced Filter) dlm subject [belajar-excel] VLOOKUP Data Ke: Sekian bbrapa hari y.l. HerrSoe
Re: [belajar-excel] Total Angka dalam 1 Cell
From: STDEV(i) setiyowati.d...@gmail.com
To: belajar-excel@yahoogroups.com
Sent: Mon, August 23, 2010 2:54:36 PM
Subject: Re: [belajar-excel] Total Angka dalam 1 Cell
Herr,
coba juga rumus ini
=SUMPRODUCT((LEN(B5)-LEN(SUBSTITUTE(B5,ROW($1:$9),)))*ROW($1:$9))
boleh juga ditulis dlm versi 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})
keringanannya :
sumproduct ini tanpa harus dienter sbg Array Formula (Strl_Shift+Enter)
-siti-
2010/8/23 zainul ulum [@ yahoo] zainul_u...@yahoo.com
Pakai rumus array (ctrl+shit+enter):
=SUM(--MID(TEXT(A1,REPT(0,99)),ROW($1:$99),1))
Asumsi jika di A1 adalah berupa angka.
kalo yang umum (bisa text atau angka)
=SUM(IF(ISNUMBER(--MID(A6REPT(_,99-LEN(A6)),ROW(1:99),1)),--MID(A6REPT(_,99-LEN(A6)),ROW(1:99),1),0))
From:belajar-ex...@yahoogroups.com[mailto:belajar-ex...@yahoogroups.com] On
Behalf Of HerrSoe
Sent:Monday, August 23, 2010 2:22 PM
To:belajar-excel
Subject:[belajar-excel] Total Angka dalam 1 Cell
mohon bantuan
bagaimana rumus-nya (rumus biasa :tanpa bantuan makro) untuk
menjumlah semua karakter angka yg ada dlm satu cell
misal
NOMOR 1234567890
hasil yg diinginkan : 45 (hasil dari 1+2+3+4+5+6+7+8+9+0)
Tanggal 31Des2009jam 16:30
hasil yg diinginkan : 25
2500,34
hasil = 14
dst
Sebelumnya diucapkan terima kasih.
Terima kasih juga kpd Bp Anton atas kiriman solusinya (pemanfaatan Advanced
Filter)
dlm subject [belajar-excel] VLOOKUP Data Ke: Sekian bbrapa hari y.l.
HerrSoe
Re: [belajar-excel] Total Angka dalam 1 Cell
sepertinya ada missing text dikit pada rumus pak anton *sebelum di SUM,* bentuknya seperti ini =(--MID(A1;ROW(INDIRECT(1:LEN(A1)));1)) jika rumus ini di tulis sekaligus di 27 cells sekolom sbg ArrayFormula maka hasilnya (di pc-ku) seperti ini 1 2 8 7 #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! 7 8 2 1 #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! makanya ketika di-SUM menghasilkan [#VALUE] karena di dalam range (array) tsb terdapat error values. barangkali harus ada *test IsErr *dulu 2010/8/23 anton suryadi summon...@yahoo.com satu pilihan lagi: =SUM(--MID(A1,ROW(INDIRECT(1:LEN(A1))),1)) (array formula) semoga bermanfaat
Re: [belajar-excel] Total Angka dalam 1 Cell
saya kmrin baca pertanyaannya cm seklebat sj,, ^_^ muun maap
saya interpretasikan pertanyaannya, data semua numerik,
ini ada versi lain masih senada dengan formula bu ctv,
yang mungkin lebih mudah untuk dibaca
=SUM((LEN(A1)-LEN(SUBSTITUTE(A1,{1,2,3,4,5,6,7,8,9},)))*{1,2,3,4,5,6,7,8,9})
semoga bermanfaat
From: HerrSoe herr...@gmail.com
To: belajar-excel@yahoogroups.com
Sent: Tue, August 24, 2010 9:31:07 AM
Subject: Re: [belajar-excel] Total Angka dalam 1 Cell
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
2010/8/24 STDEV(i) setiyowati.d...@gmail.com
sepertinya ada missing text dikit pada rumus pak anton
sebelum di SUM, bentuknya seperti ini
=(--MID(A1;ROW(INDIRECT(1:LEN(A1)));1))
jika rumus ini di tulis sekaligus di 27 cells sekolom sbg ArrayFormula
maka hasilnya (di pc-ku) seperti ini
(--cut--)
makanya ketika di-SUM menghasilkan [#VALUE]
karena di dalam range (array) tsb terdapat error values.
barangkali harus ada test IsErr dulu
2010/8/23 anton suryadi summon...@yahoo.com
satu pilihan lagi:
=SUM(--MID(A1,ROW(INDIRECT(1:LEN(A1))),1))
(array formula)
semoga bermanfaat
