前两天在函数培训群出了一道条件求和的练习题,内容如下: A:C列是数据明细,需要在F列统计E列各单位的工资总值。 这题像我一样单纯,可以直接SUMIF函数走起▼ =SUMIF(A:A,E2,C:C) SUM是求和的意思,IF是如果的意思,SUMIF的意思就是如果A列的值等于E2,就对C列的数据累加求和。 我曾经以为这题也就这样了,平静的心拒绝再有浪潮……然而好死不死的,我出题的时候加了一句话,提供3种不同函数解法的可得评级A。 是命运的安排也好,是存心的捉弄也好,这一切已不再重要,总之各位新老学员爆发了,提供了各种稀奇古怪的解法。我罗列下其中十种解法,大家一起感受感受。 1, 其它正常解法 有的朋友使用了SUMIFS函数,它是SUMIF的弟弟,语法基本相似,只是参数顺序不同:第1参数是求和范围,第2参数是条件范围,第3参数是条件值。
还有的朋友用了SUM函数数组解法:
$A$2:$A$17=E2,判断A2:A17的值是否等于E2,得到一个由逻辑值TRUE和FALSE构成的内存数组,然后再乘以C2:C17。TRUE*数值=1*数值,返回数值自身。FALSE*数值=0*数值,返回0。最后再用SUM函数求和。 或者使用SUMPRODUCT函数:
或:
两个SUMPRODUCT稍有差异,后者使用了两个参数(注意逗号的存在),它会自动忽略C2:C17区域中的非数值数据。详情可以参考SUMPRODUCT函数教程: 先来看一个不正常里相对正常的,使用了365版本的FILTER函数:
FILTER筛选出A列等于E2值的C列数值,再用SUM求和。 …… 接着是一个冷门的数据库函数DSUM+错位引用原理:
配合图片食用味道更佳▼ 最后是一个区域或动态数组公式MMULT函数:
…… 上面三个已经很不正常了,对吧? ——但还有更不正常的,堪称蛇精病级别的。 3, 蛇精病啊 AVERAGEIF统计E2单位的工资平均值,再乘以E2单位出现的次数,也就等于E2单位的总工资——就问你这数学鬼才逻辑秀不秀吧。
如果上面这种算没事找事型,那下面这个就是没事找抽型,SUM+OFFSET函数组合。
IF($A$2:$A$17=E2,ROW($1:$16),0)判断A列的值是否等于E2,如果相等则返回序列号。OFFSET函数以C1单元格为基点,向下偏移取值,最后再用SUM函数求和…… 以为这就算完了? 那你严重低估了当代函数女青年的脑域宽度,加法了解一下? 解法10:F2=C2+C3+C4F3=C7+C8+C11F4=C14+C15+C16 …… |
|
来自: asaser > 《No2:函数公式》