A列是人名,B列是数据明细,这明细看起来长的很奇怪,长成了一堆文本算式,而她需要在C列统计B列文本算式的结果。
如视频演示,这事三步就可搞定。
首先,在C2单元格输入以下公式,并向下复制填充:
="="&B2
然后,将公式复制粘贴为数值。
最后,查找=替换为=。
……问题解决后,为了防患于未然,我小心翼翼地劝说她这样的数据是不规范的,你得把不同的数据拆分到不同列……她不置可否,对我翻了个白眼,走了。
我心一紧,知道这事肯定还没完。
果然,过了没几天,她又来找我了,又丢给一个数据表,如下:
她给了我两颗糖,然后小声埋怨我上次给的方法失效了,没办法统计B列的金额,公式返回一堆错误值,害她被领导说……
我心想你这数据都变成啥鬼样子了,原来的公式能不失效吗?我挽了挽袖子——是时候秀一点真正的技术了。
首先,选中C2单元格,在【公式】选项卡下依次单击【名称管理器】→【新建】,打开新建名称对话框。
名称栏输入星光……这个大家可以根据个人爱好随便修改……将以下公式粘贴到编辑栏:
公式看不全可以左右拖动...▼
=EVALUATE(SUBSTITUTE(SUBSTITUTE(B2,"(","+n("""),")",""")"))
单击【确定】命令,关闭对话框。
此时在C2单元格输入以下公式,即可计算正确结果。
=星光
解释下公式的意思。
SUBSTITUTE(SUBSTITUTE(B2,"(","+n("""),")",""")")
使用两个SUBSTITUTE函数,先把B2单元格的左括号(替换为+n(",再把右括号)替换为")。
比如B2单元格,替换后的结果如下:
2305+n("基本工资")-77+n("过冬费")+175+n("奖金")-31+n("考勤")
这么替换的目的,是将括号内的数据纳入n函数中,并用一对双引号包起来变成文本值,n函数会将文本值返回0,数值返回自身。
最后再套一个宏表函数EVALUATE,完成该文本算式的计算。
这个问题用查找替换的基操也能搞定,但使用宏表函数的好处是,如果数据有新增或减少,直接将公式复制就可以了,不用反复手工折腾——坏处是,得将文件保存为xlsm格式,不然就会丢失宏表函数。
……
我说的口干舌燥,她用很奇怪的眼神看着我,那眼神里有希望、有惊奇、有茫然,有欢喜,有……我!我不由脸红心热了——当然,事后证明,我想多了,毕竟谁看我眼睛里都必然有我的倒影
~~o(>_<)o ~~
……
第2天,正当我犹豫不决要不要买两张电影票的时候,她又主动来找我了。
又丢给我一张表。
又是对B列的文本算式求值。
如下▼
同时她低下头笑嘻嘻悄声问我,最近有时间吗?她那里多出两张电影票。
但看到这张表的霎那间,我忽然悟了——这姑娘恐怕脑袋不大好,不然的话,为啥总是把数据折腾成这个鬼样子折磨自己呢?
我摸了摸自个聪明绝顶的脑袋,心里叹息,不是良配啊这。
我决定展示一下自己绝顶的技术。
我淡淡说道,现在这个问题,看似复杂,但其实只需要将名称管理器的公式改成下面这样就可以了。
=EVALUATE(CONCAT(IF(ISNUMBER(FIND(MID(B2,ROW($1:$99),1),"0123456789.+-*/")),MID(B2,ROW($1:$99),1),"")))
MID(B2,ROW($1:$99),1),将B2单元格的值拆分为一个个单独的字符,再使用FIND函数判断每个字符在"0123456789.+-*/"是否存在,如果存在,则返回字符自身,否则返回假空。
然后使用CONCAT函数合并为一个字符串,比如:
2016+164+193-40
最后再使用EVALUATE宏表函数完成文本算式的计算。
就这么回事。
……
我看了看眼前的两张电影票,心里想应该怎么婉转拒绝,关系从两颗糖直接发展到两张电影票是不是速度太快了?咱们要不缓缓,先请吃个饭?
"我有4张电影票,我和我老公用2张,还剩下2张……"
剩下的话我没有听清楚。
我
裂
了
。
……
联系客服