Excel可以这么玩,有“错误”也能正确求和

软件先锋 2016-07-06 09:41网络整理点击: 标签:

工作表的某些单元格有时会出现类似于“#N/A”的错误信息(如图1)。若需要忽略这些单元格,对相应的行或列进行求和时,直接使用SUM函数是无法完成包含错误信息所在行或列的求和操作。但实际的数据量较大,手工求和也容易出错。此时,该怎么办呢?

 

1612A-HLCWQH-1

标准方法1:忽略错误求和法

选择G2单元格,在编辑栏输入“=SUM(IFERROR(A2:F2,0))”,IFERROR表示当表达式是一个错误时,返回value_if_error,否则返回表达式自身的值,相当于忽略了错误信息所在的单元格,按下“Ctrl+Shift+Enter”组合键转换为数组公式。向下拖拽或双击填充柄,很快就可以看到图2所示的求和结果。

1612A-HLCWQH-2

如果是“#DIV/0!”或“#VALUE!”的错误信息,上述公式也可以获得正确的求和结果。

标准方法2:设定范围求和法

也可以使用“=SUMIF(B2:B12,"<9E307")”,此处的SUMIF是对满足条件的单元格求和,表示对B2:B12的单元格区域进行求和,这里的9E307表示科学计数法下的9*10^307,在Excel中是能够接受的最大正整数,所以判断条件“<9E307”可以当作是取所有数值,排除所有非数值。公式执行之后向右拖拽填充柄,可以看到图3看示的求和结果。

1612A-HLCWQH-3

进阶方法:使用AGGREGATE一次搞定

如果使用Excel 2010或更高版本,可使用AGGREGATE一次搞定,操作更为简单。

这里需要对各行数据忽略错误进行求和:选择G2单元格,在编辑栏输入公式“=AGGREGATE(9,6,B2:F2)”,第一参数为一个介于1-19之间的数字,分别代表指定的函数,此处设置为“9”(此处函数参数选择是根据函数对应的执行命令,与表格中展示的内容无关),对应SUM函数,表示公式用于求和;第二参数为一个介于0-7之间的数字,分别代表忽略的项目,此处设置为“6”,表示忽略错误值。公式执行之后向下拖拽或双击填充柄,可以看到图4所示的求和结果。

1612A-HLCWQH-4

小提示:

AGGREGATE用来返回一个数据列表或数据库的合计,不仅可以实现诸如SUM、AVERAGE、COUNT、LARGE等的功能,而且还可以忽略隐藏行、错误值、空值等。如果区域中包含错误值,SUM等函数将返回错误,而使用AGGREGATE函数则没有这一问题。AGGREGATE还可以忽略隐藏行、嵌套、空值,但不能忽略隐藏列。