[练习题]字符串中取数相加（已总结）

liyh67 · 发表于 2007-8-26 18:57

谢谢gvntw的指点

Luckyguy2008 · 发表于 2007-8-26 19:49

学习，谢谢gvntw老师

hhzjxss · 发表于 2007-8-26 19:55

gvntw大师真厉害！

zxc304 · 发表于 2007-8-27 08:11

谢谢了

aichong · 发表于 2007-10-6 13:38

来一个宏表函数的：定义名称：arr=EVALUATE("{"""&SUBSTITUTE(SUBSTITUTE(aichong!$A2,"块","元"),"取",""";""")&"""}")y=LEFT(arr,FIND("元",arr)-1)<span style="FONT-SIZE: 10.5pt; FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'; mso-bidi-font-size: 12.0pt; mso-font-kerning: 1.0pt; mso-bidi-font-family: 'Times New Roman'; mso-ansi-language: EN-US; mso-fareast-language: ZH-CN; mso-bidi-language: AR-SA;">B2=SUM(IF(ISNUMBER(--y),(--y)))，数组公式。特点：金额大小无限制，可以有小数点，正负数均可。所以通用性强。公式比较简单。

[此贴子已经被作者于2007-10-6 23:04:02编辑过]

gouweicao78 · 发表于 2007-9-29 14:34

<span style="FONT-SIZE: 10.5pt; FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'; mso-bidi-font-size: 12.0pt; mso-font-kerning: 1.0pt; mso-bidi-font-family: 'Times New Roman'; mso-ansi-language: EN-US; mso-fareast-language: ZH-CN; mso-bidi-language: AR-SA;">定义名称：X=SUBSTITUTE(SUBSTITUTE("取"&MID(SUBSTITUTE(Sheet1!A2&"X","块","元"),ROW(INDIRECT("1:"&LEN(Sheet1!A2)-2)),COLUMN(Sheet1!$C:$E))&"元","取取",),"元元",)<span style="FONT-SIZE: 10.5pt; FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'; mso-bidi-font-size: 12.0pt; mso-font-kerning: 1.0pt; mso-bidi-font-family: 'Times New Roman'; mso-ansi-language: EN-US; mso-fareast-language: ZH-CN; mso-bidi-language: AR-SA;"> <span style="FONT-SIZE: 10.5pt; FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'; mso-bidi-font-size: 12.0pt; mso-font-kerning: 1.0pt; mso-bidi-font-family: 'Times New Roman'; mso-ansi-language: EN-US; mso-fareast-language: ZH-CN; mso-bidi-language: AR-SA;">B2=SUM(IF(ISNUMBER(--x),(--x)))，数组公式。<span style="FONT-SIZE: 10.5pt; FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'; mso-bidi-font-size: 12.0pt; mso-font-kerning: 1.0pt; mso-bidi-font-family: 'Times New Roman'; mso-ansi-language: EN-US; mso-fareast-language: ZH-CN; mso-bidi-language: AR-SA;"> <span style="FONT-SIZE: 10.5pt; FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'; mso-bidi-font-size: 12.0pt; mso-font-kerning: 1.0pt; mso-bidi-font-family: 'Times New Roman'; mso-ansi-language: EN-US; mso-fareast-language: ZH-CN; mso-bidi-language: AR-SA;">特点——1、如果A列的字数较少，则运算量会少些。2、可以计算包含小数的金额。<span style="FONT-SIZE: 10.5pt; FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'; mso-bidi-font-size: 12.0pt; mso-font-kerning: 1.0pt; mso-bidi-font-family: 'Times New Roman'; mso-ansi-language: EN-US; mso-fareast-language: ZH-CN; mso-bidi-language: AR-SA;"> <span style="FONT-SIZE: 10.5pt; FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'; mso-bidi-font-size: 12.0pt; mso-font-kerning: 1.0pt; mso-bidi-font-family: 'Times New Roman'; mso-ansi-language: EN-US; mso-fareast-language: ZH-CN; mso-bidi-language: AR-SA;">其中：<span style="FONT-SIZE: 10.5pt; FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'; mso-bidi-font-size: 12.0pt; mso-font-kerning: 1.0pt; mso-bidi-font-family: 'Times New Roman'; mso-ansi-language: EN-US; mso-fareast-language: ZH-CN; mso-bidi-language: AR-SA;">思路要点：将MID截取的3个以上字符前后都加上“取”“元”字，这样，只有实际截取到的字符为“取n元”——这是目标值，（n为数值，可带小数）的时候，合并才能变成“取取n元元”，然后再将“取取”、“元元”去掉，由此得到数值n。<span style="FONT-SIZE: 10.5pt; FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'; mso-bidi-font-size: 12.0pt; mso-font-kerning: 1.0pt; mso-bidi-font-family: 'Times New Roman'; mso-ansi-language: EN-US; mso-fareast-language: ZH-CN; mso-bidi-language: AR-SA;"> <span style="FONT-SIZE: 10.5pt; FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'; mso-bidi-font-size: 12.0pt; mso-font-kerning: 1.0pt; mso-bidi-font-family: 'Times New Roman'; mso-ansi-language: EN-US; mso-fareast-language: ZH-CN; mso-bidi-language: AR-SA;">MID的第2参数以Row产生垂直数组序列起点，第3参数以Column($C:$E)产生3以上的水平数组序列作为字符串长度，由此，MID得到的是Row*Column的文本矩阵（以下简称MID矩阵）。<span style="FONT-SIZE: 10.5pt; FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'; mso-bidi-font-size: 12.0pt; mso-font-kerning: 1.0pt; mso-bidi-font-family: 'Times New Roman'; mso-ansi-language: EN-US; mso-fareast-language: ZH-CN; mso-bidi-language: AR-SA;"> <span style="FONT-SIZE: 10.5pt; FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'; mso-bidi-font-size: 12.0pt; mso-font-kerning: 1.0pt; mso-bidi-font-family: 'Times New Roman'; mso-ansi-language: EN-US; mso-fareast-language: ZH-CN; mso-bidi-language: AR-SA;">Column($C:$E)取3~5个字，即“取0元”~“取000元”，这个变量可根据数值位数的大小改变，能满足小数金额。比如改为Column($C:$Q)，则满足16位数字（含小数点）的计算。但近量根据最长金额字符数来确定，以减少运算量。<span style="FONT-SIZE: 10.5pt; FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'; mso-bidi-font-size: 12.0pt; mso-font-kerning: 1.0pt; mso-bidi-font-family: 'Times New Roman'; mso-ansi-language: EN-US; mso-fareast-language: ZH-CN; mso-bidi-language: AR-SA;"> <span style="FONT-SIZE: 10.5pt; FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'; mso-bidi-font-size: 12.0pt; mso-font-kerning: 1.0pt; mso-bidi-font-family: 'Times New Roman'; mso-ansi-language: EN-US; mso-fareast-language: ZH-CN; mso-bidi-language: AR-SA;">因“取n元”最少要3个字符，所以MID的第2参数为LEN(A2)-2（即：到“取”字开始）<span style="FONT-SIZE: 10.5pt; FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'; mso-bidi-font-size: 12.0pt; mso-font-kerning: 1.0pt; mso-bidi-font-family: 'Times New Roman'; mso-ansi-language: EN-US; mso-fareast-language: ZH-CN; mso-bidi-language: AR-SA;"> <span style="FONT-SIZE: 10.5pt; FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'; mso-bidi-font-size: 12.0pt; mso-font-kerning: 1.0pt; mso-bidi-font-family: 'Times New Roman'; mso-ansi-language: EN-US; mso-fareast-language: ZH-CN; mso-bidi-language: AR-SA;">误差问题：<span style="FONT-SIZE: 10.5pt; FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'; mso-bidi-font-size: 12.0pt; mso-font-kerning: 1.0pt; mso-bidi-font-family: 'Times New Roman'; mso-ansi-language: EN-US; mso-fareast-language: ZH-CN; mso-bidi-language: AR-SA;">1、  金额为1位数，假设A2以“取9元”结尾，则MID矩阵最后1行得到的都是“取9元”，与目标值同。<span style="FONT-SIZE: 10.5pt; FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'; mso-bidi-font-size: 12.0pt; mso-font-kerning: 1.0pt; mso-bidi-font-family: 'Times New Roman'; mso-ansi-language: EN-US; mso-fareast-language: ZH-CN; mso-bidi-language: AR-SA;">2、  金额为2位数，如“取99元”，则MID矩阵的倒数第2行为{"取99","取99元",……后面都重复<span style="FONT-SIZE: 10.5pt; FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'; mso-bidi-font-size: 12.0pt; mso-font-kerning: 1.0pt; mso-bidi-font-family: 'Times New Roman'; mso-ansi-language: EN-US; mso-fareast-language: ZH-CN; mso-bidi-language: AR-SA;">3、  金额为3位数，如“取999元”，则MID矩阵的倒数第3行为{"取99","取999","取999元",……后面重复，依此类推<span style="FONT-SIZE: 10.5pt; FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'; mso-bidi-font-size: 12.0pt; mso-font-kerning: 1.0pt; mso-bidi-font-family: 'Times New Roman'; mso-ansi-language: EN-US; mso-fareast-language: ZH-CN; mso-bidi-language: AR-SA;">重复的部分均为我们要得到的目标值“取n元”。<span style="FONT-SIZE: 10.5pt; FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'; mso-bidi-font-size: 12.0pt; mso-font-kerning: 1.0pt; mso-bidi-font-family: 'Times New Roman'; mso-ansi-language: EN-US; mso-fareast-language: ZH-CN; mso-bidi-language: AR-SA;"> <span style="FONT-SIZE: 10.5pt; FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'; mso-bidi-font-size: 12.0pt; mso-font-kerning: 1.0pt; mso-bidi-font-family: 'Times New Roman'; mso-ansi-language: EN-US; mso-fareast-language: ZH-CN; mso-bidi-language: AR-SA;">误差修正：<span style="FONT-SIZE: 10.5pt; FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'; mso-bidi-font-size: 12.0pt; mso-font-kerning: 1.0pt; mso-bidi-font-family: 'Times New Roman'; mso-ansi-language: EN-US; mso-fareast-language: ZH-CN; mso-bidi-language: AR-SA;">A2&”X”将A2的字符后面强制合并1个非数字文本X（或者一个空格" "等），则上述误差问题：<span style="FONT-SIZE: 10.5pt; FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'; mso-bidi-font-size: 12.0pt; mso-font-kerning: 1.0pt; mso-bidi-font-family: 'Times New Roman'; mso-ansi-language: EN-US; mso-fareast-language: ZH-CN; mso-bidi-language: AR-SA;">1、  金额为1位数，MID矩阵最后1行得到{"取9元","取9元X",……后面重复"取9元X"<span style="FONT-SIZE: 10.5pt; FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'; mso-bidi-font-size: 12.0pt; mso-font-kerning: 1.0pt; mso-bidi-font-family: 'Times New Roman'; mso-ansi-language: EN-US; mso-fareast-language: ZH-CN; mso-bidi-language: AR-SA;">2、  金额为2位数，MID矩阵倒数第2行得到{"取99","取99元","取99元X",……后面重复"取99元X"<span style="FONT-SIZE: 10.5pt; FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'; mso-bidi-font-size: 12.0pt; mso-font-kerning: 1.0pt; mso-bidi-font-family: 'Times New Roman'; mso-ansi-language: EN-US; mso-fareast-language: ZH-CN; mso-bidi-language: AR-SA;">依此类推，解决了重复计算问题。

[此贴子已经被作者于2007-9-29 20:39:33编辑过]

willin2000 · 发表于 2007-9-29 15:47

<div class="msgheader">QUOTE:</div><div class="msgborder">以下是引用gvntw在2007-8-24 14:12:59的发言： <div class="msgheader">QUOTE:</div><div class="msgborder">以下是引用zxc304在2007-8-24 7:56:57的发言： =SUM(IF(ISERROR(FIND("取"&ROW(1:1000)&{"元","块"},A2)),0,ROW(1:1000)))来个最简单的</div>呵呵，很高兴看到这个答案。之所以说字符串规律：“取000元或块，数字不大于1000”就是为了用"取"&连续序列号&{"元","块"}来判断取数的。公式还可以简（行号必须绝对）：=SUM(IF(ISERR(FIND("取"&ROW($1:$1000)&{"元","块"},A2)),,ROW($1:$1000)))=SUM(ISNUMBER(FIND("取"&ROW($1:$1000)&{"元","块"},A2))*ROW($1:$1000))</div>下面情况公式结果错误:任盈盈取45块，任我行取45块，宁女侠取5元林震南取10元方证取112块 <table cellspacing="0" cellpadding="0" width="502" border="0" xstr="" style="WIDTH: 377pt; BORDER-COLLAPSE: collapse;"><colgroup><col width="502" style="WIDTH: 377pt; mso-width-source: userset; mso-width-alt: 16064;"></col></colgroup><tbody><tr height="19" style="HEIGHT: 14.25pt;"><td class="xl23" width="502" height="19" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 377pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent;">gnvtw的解析与评价</td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td class="xl22" height="19" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent;">公式构思巧妙，巧用"取"&ROW($1:$1000)&{"元","块"}来判断字符串中是否含有“取000元或块”，再对符合条件的行号数相加</td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td class="xl22" height="19" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent;">但没考虑重复值问题，结果错误。</td></tr></tbody></table>

[此贴子已经被作者于2007-9-29 15:51:59编辑过]

aichong · 发表于 2007-10-7 00:14

再来一个：定义名称：arr1=TRIM(MID(SUBSTITUTE(SUBSTITUTE(aichong_F37!$A2,"块","元"),"取",REPT(" ",999)),999*(ROW(INDIRECT("1:"&LEN(aichong_F37!$A2)))-1)+1,999))z=LEFT(arr1,FIND("元",arr1)-1)<span style="FONT-SIZE: 10.5pt; FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'; mso-bidi-font-size: 12.0pt; mso-font-kerning: 1.0pt; mso-bidi-font-family: 'Times New Roman'; mso-ansi-language: EN-US; mso-fareast-language: ZH-CN; mso-bidi-language: AR-SA;">B2=SUM(IF(ISNUMBER(--z),(--z)))，数组公式。特点：金额大小无限制，可以有小数点，正负数均可。通用性强。

gvntw · 发表于 2007-10-7 13:30

谢谢gouweicao78兄和aichong兄的参与以及给出的精彩答案和解题思路！

gouweicao78 · 发表于 2007-10-8 11:37

<div class="msgheader">QUOTE:</div><div class="msgborder">以下是引用aichong在2007-10-6 13:38:42的发言： 来一个宏表函数的：定义名称：arr=EVALUATE("{"""&SUBSTITUTE(SUBSTITUTE(aichong!$A2,"块","元"),"取",""";""")&"""}")y=LEFT(arr,FIND("元",arr)-1)<span style="FONT-SIZE: 10.5pt; FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'; mso-bidi-font-size: 12.0pt; mso-font-kerning: 1.0pt; mso-bidi-font-family: 'Times New Roman'; mso-ansi-language: EN-US; mso-fareast-language: ZH-CN; mso-bidi-language: AR-SA;">B2=SUM(IF(ISNUMBER(--y),(--y)))，数组公式。特点：金额大小无限制，可以有小数点，正负数均可。所以通用性强。公式比较简单。 </div>直接一个定义名称：y=--EVALUATE("{"""&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(aichong!$A2,"块","元"),"元",""";"""),"取",""";""")&"""}")B2<span style="FONT-SIZE: 10.5pt; FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'; mso-bidi-font-size: 12.0pt; mso-font-kerning: 1.0pt; mso-bidi-font-family: 'Times New Roman'; mso-ansi-language: EN-US; mso-fareast-language: ZH-CN; mso-bidi-language: AR-SA;">=SUM(IF(ISNUMBER(y),y))

		自动登录	找回密码
密码			注册