单身多年的我有一个潜在的女朋友……不是,我有一个性别为女的普通朋友,前段时间给了我一段数据,如下: A列是人名,B列是数据明细,这明细看起来长的很奇怪,长成了一堆文本算式,而她需要在C列统计B列文本算式的结果。
看到这样的数据,我当场反手就……扔过去一个小视频。如视频演示,这事三步就可搞定。
首先,在C2单元格输入以下公式,并向下复制填充:
然后,将公式复制粘贴为数值。
最后,查找=替换为=。 ……问题解决后,为了防患于未然,我小心翼翼地劝说她这样的数据是不规范的,你得把不同的数据拆分到不同列……她不置可否,对我翻了个白眼,走了。
我心一紧,知道这事肯定还没完。 果然,过了没几天,她又来找我了,又丢给一个数据表,如下:
她给了我两颗糖,然后小声埋怨我上次给的方法失效了,没办法统计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函数合并为一个字符串,比如: 最后再使用EVALUATE宏表函数完成文本算式的计算。
就这么回事。
……
我看了看眼前的两张电影票,心里想应该怎么婉转拒绝,关系从两颗糖直接发展到两张电影票是不是速度太快了?咱们要不缓缓,先请吃个饭? "我有4张电影票,我和我老公用2张,还剩下2张……"
剩下的话我没有听清楚。 我 裂 了 。 ……
|