|
公众号回复2016 下载office2016 这是一道公式练习题，要求很简单，就是求一列数字的百位数数字之和： 大家一共搞出来8种公式解法，求和无一例外都是用SUM数组方式或者是SUMPRODUCT函数实现，区别在于对百位数的提取思路，真的有点八仙过海各显神通的感觉。 以下重点分析提取百位数的思路，求和就不做解释了。 PS：使用SUM函数的公式都需要按Ctrl shift和回车完成公式输入 =SUM(--IF(LEN(A3:A10)>=3,MID(A3:A10,LEN(A3:A10)-2,1))) 这个公式使用了IF函数提取数据的百位数，效果如B列所示。 没有什么弯弯绕，直来直去的思路，首先利用LEN函数判断数据长度是否够3位，够的话就用MID函数提取百位数。 当数字长度不确定的时候，百位数就是从左侧开始起数字长度减2的位置，例如一个5位数百位在左起第3个数字，7位数百位在左起第5个数字…… MID(A3:A10,LEN(A3:A10)-2,1)这部分就是提取百位数的。 这个公式中需要说明一点的就是IF函数省略了第三个参数（包括逗号），这种情况下如果需要返回第三参数时，会得到逻辑值FALSE，公式是为了省略两个字才这样写的，完整的话可以加上第三参数： IF(LEN(A3:A10)>=3,MID(A3:A10,LEN(A3:A10)-2,1),0) 至于IF左边的两个减号，那是为了把提取出来的数字变成可以求和的数值，MID、LEFT以及RIGHT等函数提取出来的数字都是文本格式，无法直接求和。 --同样可以变成1*、0+等，只要进行了计算就能变成数值，同时还可以把逻辑值FALSE变成0。 在今天分享的八个公式中，这个最长，但胜在好理解。 初学者使用公式解决问题时，长一点不怕，能用自己掌握的函数达到目的就很不错了。 =SUM(IFERROR(--MID(A3:A10,LEN(A3:A10)-2,1),0)) 这是公式1的优化版，将IF换成了IFERROR（03版没有这个函数，不过还在用03版的人估计也没几个了吧）。 直接用MID提取百位数，当数字不足三位时，会得到错误值： IFERROR函数的作用就是将错误值转为0，可见多学会一个函数，公式就能短一点点…… =SUM(LEFT(RIGHT(("0"&A3:A10),3))*1) 从这个公式开始就有点要玩花活的意思了，提取百位数分两步进行，先用RIGHT函数提取最右边的三位数，对不足三位的数字左边补一个0，"0"&就是这个作用。 第二步再用LEFT提取最左边的一位就OK了，LEFT省略第二参数是表示提取1位。 LEFT与RIGHT的配合固然巧妙，"0"&用的就更值得点赞了。 这个公式里用的就是*1，看到了吧…… =SUM(INT(RIGHT(A3:A10,3)/100)) 如果说前面的几个公式玩的是文本构造的话，从这个公式开始就变成玩数字逻辑了。 RIGHT(A3:A10,3)同样是提取右边三位，这个没变化，但是把"0"&换成/100，性质就完全不同了，上图B列的模拟结果相信众位看官都能看明白，但自己就是想不到的有木有…… 再用INT提取整数部分就搞定了，连加减乘除都不用，数学逻辑厉害真的有好处啊！ 公式4的长度已经缩减到29个字符了，为了再减少一个字符，大伙煞费苦心…… =SUM(MOD(INT(A3:A10/100),10)) 只有彻底放弃提取数字的思路，将数学逻辑玩到底。 直接用INT(A3:A10/100)看看效果，好像个位数就是需要的结果，此时你有两个选择，再用RIGHT提取然后转数值，但这样公式就又长了，怎么办？ 用MOD求数字除10的余数，就是个位数。 看不懂这句话的可以去拜访一下你的小学数学老师~~~ =SUM(--RIGHT(INT(A3:A10%))) 这个公式和公式4很像对不对，只不过调换了INT和RIGHT的顺序，再把/100换成了%，怎么公式就变短了呢？ PS：%就是将数字缩小100倍的作用，等同于/100） =SUM(--MID(A3:A10%%,4,1)) 场面有点火爆，还有点失控，变成两个%然后用MID提取，这都是什么思路啊…… 感觉自己的智商瞬间降低了！ 第八个公式留给你来写吧，不管长短，结果对了就行，留言等着大家点赞吧！ 最后只想说一句：要学公式函数还得这样玩才行！  强烈推荐菜鸟系统学习Excel函数的宝典，也就是下面这本，非常适合新手学习。 出版社的主编说“第一次看到这本书的时候感觉非常好，这么多年能把函数用这种风格的语言讲出来的，几乎没有”。 不夸张地说，看了肯定能受益，毕竟书里的内容，都是我自己踩过的坑。 |
|
|
