今天在学员群给大家分享了一个实际工作中经常遇到的难题,就是数据表中有错误值,这种情况下的快速的计算求和。 虽然题目中的数据条目设计的很少,但是我们实际工作中确实经常成千上万条数据的,我们如何去解决这样的问题呢??有的同学说:我们可以直接用定位非法值,通过批量填充将非法值替换为0再求和。这不失为一种好的办法,但是有时候错误值在表格中也是有意义的,我们不希望将其替换为0。今天我来介绍7个解决方案,教你在遍布错误值的表格中也能轻松求和。很多人看到这样的是函数可能有点儿懵,‘9e307’是个什么鬼?敲黑板了,知识点:9e307是一个极大值,可以理解为一个大的没边儿的数据,‘<9e307’就相当于将所有数值都满足条件并纳入求和。这个公式基本上小伙伴们真的很少见到过,用过的应该就更少了,今天就在这里简单为大家介绍一下:这函数是一个大神级函数,多参数组合的函数,有一夫当关万夫莫开之勇。返回列表或数据库中的合计。AGGREGATE 函数可将不同的聚合函数应用于列表或数据库,并提供忽略隐藏行和错误值的选项。具体公式:AGGREGATE(function_num, options, ref1, [ref2], …)A、第一个参数function_num是选择什么计算函数类型,可选项有19个大类分别用B、第二个参数option,必须是一个数值,要明确剔除什么样的数值,对应关系如下:0 或省略忽略嵌套 SUBTOTAL 和 AGGREGATE 函数1忽略隐藏行、嵌套 SUBTOTAL 和 AGGREGATE 函数2忽略错误值、嵌套 SUBTOTAL 和 AGGREGATE 函数3忽略隐藏行、错误值、嵌套 SUBTOTAL 和 AGGREGATE 函数本题目种用到的参数是9和6,9为求和,6为忽略错误值。上面所介绍的两种方法是用基本函数就可以完成的方法,下面几种方法我们引入数组函数的概念来解决了;这个函数比较容易理解,是运用了iferror函数,对单元格中的数据进行判断,如果是错误值则返回0,然后再求和,但是这个函数如果单独回车的话无法得出正确值,必须在输入公式之后,用CTRL+shift+enter,才能有效果,公式显示为:{=SUM(IFERROR(J3:J14,0))}这个函数用的也很巧妙的,使用的是判断大于0,但该函数有写局限性,当表格中的数据小于0的时候可能会有问题,不过已经算很好的答案了,因为大不了再写一个小于0即可哈^-^=SUM(IF(ISNA(C4:C9),0,C4:C9)) 此方法只适用于非法值为N/A的类型,同样使用数组进行判断。方法3中的IFERROR可以包含所有类型的非法值。=SUM(IF(ISERROR(J4:J13),0,J4:J13))同样是数组公式,用到了if函数和iserror函数,这样也起到了一个完美的判断效果;=SUM(IF(ISNUMBER(J4:J13),J4:J13,0))这个组合函数的技巧很棒呢,直接判断是否是数值,如果是就返回数值本身,否则返回0,再进行求和。这个问题本身就是开放性的问题,在列一一列举主要是帮助大家更好地掌握函数应用,能够灵活使用。2、sumif,条件求和函数,外搭一个sumifs函数也是ok的哟最后,做个互动——以上7种方法,你最喜欢哪一种呢?欢迎留言。 作者 赵建军 Excel表格学院联合创始人,团队核心成员。
|