今天讨论的问题是每日一练的一道题目,原题为:
原本这个问题是一道sumif函数的练习题,知识点是利用通配符来进行条件求和,公式为:=SUMIF(A2:A11,"*电脑",C2:C11)
关于sumif函数的用法参考文章:零基础学函数:SUMIF函数基本用法解析
可是这个问题竟然被函数班的学员们又整出来6种思路,老师也忍不住来了3个比较有难度的公式,加上sumif,一共凑出来10种解法,下面就看看都是哪些公式。(下面的这些公式都是数组公式)
这个公式是第一种思路,从字面做了一些文章,使用SUBSTITUTE将“电脑”替换为空,再与原数据比较,从而得到一组逻辑值,用这个作为求和的条件。
SUBSTITUTE函数也是一个比较有用的文本类函数,作用就是替换指定的内容。经常在一些复杂的问题中大放异彩。
如果前面一个思路叫替换思路的话,这个思路就变成了查找思路。公式使用了FIND函数查找单元格内是否有“电脑”如果找不到的话,利用IFERROR函数得到0,还是利用数组原理进行求和。
既然大家都在文字内容上做文章,分别用了查找和替换两种思路,就有人另辟蹊径,根据最右边的一个字是否为“脑”作为判断条件。这个公式相比前面两个更加易于理解,真的是用了心思。
经过前面三个方法,要继续在文字内容上做文章已经不太可能了,于是就开始在公式思路上做文章,讲方法3里的if函数舍弃,直接用逻辑值计算,就有了这个解法。
新的思路出现,不能在内容做文章,那就在内容长短做文章,有人发现电脑的名字都是四个字(不管是不是巧合,在这个数据源中思路没问题),分别使用len函数和mod函数来实现。
咦,好像mod函数有点多余啊,于是马上有了下一个公式。
思路同前一个,但是更加直接暴力,对名称长度为4的进行求和。这叫偷鸡知道吗同学,不过偷的漂亮!
老师们终于看不下去了,一个原本是sumif的问题,又要被这些爱折腾的学生们玩残了,好吧,让你们看看老师是怎么玩的:
这个公式里依稀有查找思路的影子,不过后面的^-1是个什么玩意,求解释啊!
勉勉强强看明白这个公式的就没几个人了,老师继续发招:
公式用到了三个函数:SUM、COUNTIF、OFFSET,单独的函数都明白,放在一起就歇菜了,老师给讲讲啊……
哇咔咔,MM函数,老师,我们要疯!
一个sumif的问题彻底玩残了……
结语:感谢学员“无悔”整理以上公式,感谢小鸟老师和蘑菇老师写出来的这几个让人惊叹的公式。
对于这个问题来说,最标准的肯定是SUMIF的解法,但是站在学习的角度,后面这些公式无论从思路还是函数的运用方面都是非常精彩的,也只有这样才能提高自己使用函数的水平,有人评论说这些都是“奇淫技巧”,我就呵呵了……
对于喜欢玩函数的我们来说,乐在其中,玩着学是一种最高效的学习方法,大家一起玩,在这种氛围的熏陶下,函数将不在是堵在你使用Excel前面的一道坎,而是让你从此爱上Excel的一个理由。
每日一练还在继续,老菜鸟的班天天都有好戏上演。想成为Excel的玩家就来吧
新书即将出版,现在可以预定,扫描下方二维码,备注信息:预定
联系客服