 Word技巧达人推荐搜索Word课程 Word表格 段落 邮件合并 样式 图表 今天我们来看一个求和的问题，数据源和模拟结果如下： 这个小伙伴观察数据，发现消费券都是在最后的，故把数据源中的消费券提取出来了。他想对消费券的金额求和，模拟结果如C列所示。 这个小伙伴用的是WPS，故我们可以考虑直接使用EVALUATE。 EVALUATE：对以文字表示的一个公式或表达式求值，并返回结果。 语法：EVALUATE(值) 如果在office里敲=EVALUATE，不会出现，需要定义名称。 定义名称方法：公式——定义名称，在引用位置写上公式，名称框写入名字即可。 用EVALUATE对消费券的金额求和，那么肯定需要类似于300+30的算式。观察以后发现每个数字的前面都有消费券:，故我们用SUBSTITUTE(B2,'消费券:','')将消费券:替换掉，然后这个单元格里面就会有逗号分隔的数字，我们可以再用一次SUBSTITUTE把逗号替换为+，这样我们用EVALUATE就可以求和了。因为原本的B2有错误，我们可以用iferror排错。公式如下：
<左右滑动查看更多> 效果如下图： 当然，我们可以把B列的公式套进上面，这样就不需要辅助列了。
<左右滑动查看更多> 效果如下： 观察要求和的数据，我们发现都是按照,消费券:来分开的，故我们可以考虑用函数分列的套路。分列的套路在另一篇文章中会提到，这里简单提一下。 此函数的意思是在分隔符号加上单元格字符长度的单元格，然后再根据列号逐个取出字符，然后再去掉空格。 举个例子，此处C12有8个字符，故我们把空格重复8次。 从第1位开始取8位，这样就会取出67 （67后面跟6个空格），然后把空格去了就是第一个数67了。 从第9位开始取8位，这样就会取出 45 （45后面跟4个空格），然后把空格去了就是第二个数45了。 从第17位开始取8位，这样就会取出 45 （45后面跟4个空格），然后把空格去了就是第二个数45了。 介绍完分列的套路，我们可以用分列的核心（即MID(SUBSTITUTE($C2,'*',REPT(“ “,LEN($C2))),(COLUMN(A1)-1)*LEN($C2)+1,LEN($C2))）来解决这个问题。 为了方便，我们把A列中的,消费券:替换为99个空格。由于我们不知道需要分多少次，大概看一下发现没有超过10组的，故我们可以构建一个1到9的数组（即最多根据,消费券:分9次）和99相乘。分了之后因为MID取出来的是文本，故变为数值之后相加即可 故完整公式如下：
<左右滑动查看更多> 效果如下图： 在解法2的基础上，我们发现text函数可以屏蔽错误，故考虑下面的公式。
<左右滑动查看更多> （提示：[>];;0;!0可以屏蔽错误，在此不解释。Text取出的是个文本，故需要加--变为数值。CHAR(32)就是空格。） 0.31 效果如下图： 注：上述解法均适用于消费券都在后面的情况，否则会如下图所示计算错误： 这个数据源没有这种情况。这种情况解法1不成立，解法2和解法3我们需要把消费券:和,分开替换，即
<左右滑动查看更多>
<左右滑动查看更多> 如果你使用Excel 2016、2019和365版，恭喜你，你能在数据-新建查询里找到power query；如果你使用2010和2013版，那么你需要下载power query后加载到Excel里；如果你使用2003和2007版，那么请你升级Excel吧；如果你使用WPS任意版本，那么请换Excel来使用power query吧。 以下为oldman大佬的PQ解法分享~ let 源 = Excel.CurrentWorkbook(){[Name='表1']}[Content], Process = Table.AddColumn(源,'n',(x)=> [a = List.Select(Text.Split(x[收款方式],','),each Text.Contains(_,'消费券')), b = List.Transform(a,each Number.From(Text.Remove(_,Text.ToList('消费券:')))), c = List.Sum(b) ][c] ), RemovCol = Table.RemoveColumns(Process,{'收款方式'})in RemovCol<左右滑动查看更多> 代码使用方法： Step 01: 单击【数据】选项卡 - 选择【自表格/区域】命令；
好啦今儿分享到这里，祝大家好运！ 喜欢的同鞋请转发、点赞！ 扫描下方二维码，加入微信交流群~
|
|
|
来自： hercules028 > 《excel》
