一个单元格内有多组数据，提取固定字符前面的数字求和~~~~~

qc158

wshcw 发表于 2015-7-18 15:06
好吧，前面想加多少就加多少,这里就加1元,A2单元格就变成:
1元24号武汉教授来院订钟点房2间100元，配宿 ...

请问如果不只是A2单元格内有数据,A2:f2均有数据,公式应如何修改.
先将文本合并起来再套用以上公式，

wshcw

qc158 发表于 2015-7-18 16:11
请问如果不只是A2单元格内有数据,A2:f2均有数据,公式应如何修改.
先将文本合并起来再套用以上公式，

将A2:F2合并起来，再套用上述公式即可，如：

1. =SUMPRODUCT(--TEXT(MID(TEXT(MID(SUBSTITUTE("大"&PHONETIC(A2:F2),"元",REPT(" ",16)),ROW($1:$1500),17),),2,16),"[<>];;0;\0"))

复制代码

看附件 一个单元格内有多组数据，提取金额并求和.rar (4.01 KB, 下载次数: 48)

2015-7-18 17:40 上传

点击文件名下载附件

qc158

wshcw 发表于 2015-7-18 17:43
将A2:F2合并起来，再套用上述公式即可，如：

那如果是这种方式的统计求和呢，如何做，见附件。



计算单元格内数量.rar (42.59 KB, 下载次数: 13)

2015-7-19 09:20 上传

点击文件名下载附件

wshcw

qc158 发表于 2015-7-19 09:21
那如果是这种方式的统计求和呢，如何做，见附件。

1. =SUMPRODUCT(--TEXT(MID(TEXT(MID(SUBSTITUTE("大"&PHONETIC(G4:AC4),"PCS",REPT(" ",16)),ROW($1:$1500),17),),2,16),"[<>];;0;\0"))

复制代码

不正确部分，应该是你数据不规范，检查更正．
计算单元格内数量.rar (43.12 KB, 下载次数: 18)

2015-7-19 11:53 上传

点击文件名下载附件

欧阳右乙

我觉着，这是自找麻烦。设计个表格，把负责人、项目、金额等列好，分别填上，一目了然，还好计算。

wshcw

qc158 发表于 2015-7-19 09:21
那如果是这种方式的统计求和呢，如何做，见附件。

小数点以及小写的“pcs"在作怪,改进公式就没有事了.

1. =SUMPRODUCT(--TEXT(MID(TEXT(MID(SUBSTITUTE(SUBSTITUTE("大"&PHONETIC(G4:AC4),"PCS",REPT(" ",26)&"在"),".","在"),ROW($1:$1500),27),),2,26),"[<>];;0;\0"))

复制代码

计算单元格内数量.rar (41.75 KB, 下载次数: 49)

2015-7-19 22:11 上传

点击文件名下载附件

qc158

wshcw 发表于 2015-7-19 22:11
小数点以及小写的“pcs"在作怪,改进公式就没有事了.

大神，能解释一下这公式吗？看得不是很懂

wshcw

qc158 发表于 2015-7-19 23:39
大神，能解释一下这公式吗？看得不是很懂

=SUMPRODUCT(--TEXT(MID(TEXT(MID(SUBSTITUTE(SUBSTITUTE("大"&PHONETIC(G4:AC4),"PCS",REPT(" ",26)&"在"),".","在"),ROW($1:$1500),27),),2,26),"[<>];;0;\0"))
公式解析:
1、PHONETIC是将字符串先合并
2、SUBSTITUTE("大"&PHONETIC(G4:AC4),"PCS",REPT(" ",26)&"在"),是将"PCS",字符替换为26个空格（一般情况下，15个空格就够了）再并上一个非数字非字母也非符号的字符。
3、MID(SUBSTITUTE(SUBSTITUTE("大"&PHONETIC(G4:AC4),"PCS",REPT(" ",26)&"在"),".","在"),ROW($1:$1500),27)，这是提取27个字符（注意：这里比公式替换的空格多一个字符）
4、TEXT(MID(SUBSTITUTE(SUBSTITUTE("大"&PHONETIC(G4:AC4),"PCS",REPT(" ",26)&"在"),".","在"),ROW($1:$1500),27),)，注意这步的技巧，TEXT函数没有带参数，也就是最后一个反括号前只有一个“,”，其含义是隐藏数值，只显示文本，相当于：参数";;;@"的效果。
5、MID(TEXT(MID(SUBSTITUTE(SUBSTITUTE("大"&PHONETIC(G4:AC4),"PCS",REPT(" ",26)&"在"),".","在"),ROW($1:$1500),27),),2,26),将前面带一个字符的数字串进行过虑，去年最前一个字符，所以从第2个字符开始截取至26个，即MID(单元格,2,26),的格式处理。
6，--TEXT(MID(TEXT(MID(SUBSTITUTE(SUBSTITUTE("大"&PHONETIC(G4:AC4),"PCS",REPT(" ",26)&"在"),".","在"),ROW($1:$1500),27),),2,26),"[<>];;0;\0")，将上一步从第2个字符开始截取至26个字符处理后，用TEXT将文本数字格式转换为数值，文本转换为0
7、SUMPRODUCT(--TEXT(MID(TEXT(MID(SUBSTITUTE(SUBSTITUTE("大"&PHONETIC(G4:AC4),"PCS",REPT(" ",26)&"在"),".","在"),ROW($1:$1500),27),),2,26),"[<>];;0;\0"))，用SUMPRODUCT函数将数值汇总起来。
大概意思就是这样，水平有限，只能这么解释了，请谅解。

qc158

wshcw 发表于 2015-7-20 10:58
=SUMPRODUCT(--TEXT(MID(TEXT(MID(SUBSTITUTE(SUBSTITUTE("大"&PHONETIC(G4:AC4),"PCS",REPT(" ",26)&"在 ...

神啊,再次感谢,这个公式是好,但是在运算时比较慢,能否优化一下?
还有问题请教一下,"[<>];;0;\0"这个参数是什么意思?有这方面的知识可以分享一下吗.如"0;;;!0"等.
另外,ROW($1:$1500)能否减少到ROW($1:$100)?

~谢谢!

wshcw

qc158 发表于 2015-7-20 11:07
神啊,再次感谢,这个公式是好,但是在运算时比较慢,能否优化一下?
还有问题请教一下,"[];;0;\0"这个参数是 ...

本人能力,水平有限,优化不了,你可以另请高明.
参数"[<>];;0;\0",就是文本显示为零,其他正常显示
ROW($1:$1500)不能减少到ROW($1:$100),因为合并字符+拉伸PCS为27字符后,字符串长度超长了,已是将近1500个字符了.