跳过‘非法值’求和，难吗？7套方案、随便你选

赵建军 Excel表格学院

今天在学员群给大家分享了一个实际工作中经常遇到的难题，就是数据表中有错误值，这种情况下的快速的计算求和。

                                             

虽然题目中的数据条目设计的很少，但是我们实际工作中确实经常成千上万条数据的，我们如何去解决这样的问题呢？？

有的同学说：我们可以直接用定位非法值，通过批量填充将非法值替换为0再求和。这不失为一种好的办法，但是有时候错误值在表格中也是有意义的，我们不希望将其替换为0。

今天我来介绍7个解决方案，教你在遍布错误值的表格中也能轻松求和。

1、 大招1：

=SUMIF(J4:J13"<9e307")

很多人看到这样的是函数可能有点儿懵，‘9e307’是个什么鬼？

敲黑板了，知识点：9e307是一个极大值，可以理解为一个大的没边儿的数据，‘<9e307’就相当于将所有数值都满足条件并纳入求和。

2、 大招2：

=AGGREGATE(9,6,J4:J13)

这个公式基本上小伙伴们真的很少见到过，用过的应该就更少了，今天就在这里简单为大家介绍一下：

这函数是一个大神级函数，多参数组合的函数，有一夫当关万夫莫开之勇。

返回列表或数据库中的合计。AGGREGATE 函数可将不同的聚合函数应用于列表或数据库，并提供忽略隐藏行和错误值的选项。

具体公式：AGGREGATE(function_num, options, ref1, [ref2], …)

A、第一个参数function_num是选择什么计算函数类型，可选项有19个大类分别用

1—19表示，这里面我们就重点介绍几个：

1：平均值average

2：计数count；

3：字符计数COUNTA；

4：最小值min；

5：最大值max；

6：乘法product；

9：求和sum；

其余的可以去探索哟；

B、第二个参数option，必须是一个数值，要明确剔除什么样的数值，对应关系如下：

0 或省略忽略嵌套 SUBTOTAL 和 AGGREGATE 函数

1忽略隐藏行、嵌套 SUBTOTAL 和 AGGREGATE 函数

2忽略错误值、嵌套 SUBTOTAL 和 AGGREGATE 函数

3忽略隐藏行、错误值、嵌套 SUBTOTAL 和 AGGREGATE 函数

4忽略空值

5忽略隐藏行

6忽略错误值

7忽略隐藏行和错误值

本题目种用到的参数是9和6，9为求和，6为忽略错误值。

这个大神级函数，希望你能够get到哟！

上面所介绍的两种方法是用基本函数就可以完成的方法，下面几种方法我们引入数组函数的概念来解决了；

3、大招3：

=SUM(IFERROR(J4:J13,0))

这个函数比较容易理解，是运用了iferror函数，对单元格中的数据进行判断，如果是错误值则返回0，然后再求和，但是这个函数如果单独回车的话无法得出正确值，必须在输入公式之后，用CTRL+shift+enter，才能有效果，公式显示为：

{=SUM(IFERROR(J3:J14,0))}

4、大招4：

=SUMIF(J4:J13,">0")

这个函数用的也很巧妙的，使用的是判断大于0，但该函数有写局限性，当表格中的数据小于0的时候可能会有问题，不过已经算很好的答案了，因为大不了再写一个小于0即可哈^-^

5、大招5：

=SUM(IF(ISNA(C4:C9),0,C4:C9))

此方法只适用于非法值为N/A的类型，同样使用数组进行判断。方法3中的IFERROR可以包含所有类型的非法值。

6、大招6：来自@💅 👠 丽丽🚴🏻 🏃：

=SUM(IF(ISERROR(J4:J13),0,J4:J13))

同样是数组公式，用到了if函数和iserror函数，这样也起到了一个完美的判断效果；

7、大招7：来自@💅 👠 丽丽🚴🏻 🏃：

=SUM(IF(ISNUMBER(J4:J13),J4:J13,0))

这个组合函数的技巧很棒呢，直接判断是否是数值，如果是就返回数值本身，否则返回0，再进行求和。

这个问题本身就是开放性的问题，在列一一列举主要是帮助大家更好地掌握函数应用，能够灵活使用。

简单汇总一下：

1、aggregate，大神级函数，

2、sumif，条件求和函数，外搭一个sumifs函数也是ok的哟

3、isna，判断非法值函数，

4、isnumber，判断是否是数值函数

5、iserror，判断是否是错误

6、iferror，含义同上

7、 ……

最后，做个互动——以上7种方法，你最喜欢哪一种呢？欢迎留言。

 作者   赵建军

Excel表格学院联合创始人，团队核心成员。