Excel技巧:数组公式的高级应用（示例解析）

要成为一名 Excel 高级用户，您需要知道如何使用数组公式，它能执行非数组公式所不能执行的计算。

本招介绍一些高级的数组公式。

示例1：对包含错误值的区域求和。

当试图对包含错误值（例如 #N/A）的区域求和时，Excel 中的 SUM 函数不再适用。本示例演示如何对包含错误的命名为“数据区”的区域中的值求和。

{=SUM(IF(ISERROR(数据区),””,数据区))}

该公式创建一个新数组，包含除错误值以外的原始值。从内层函数开始向外运算，ISERROR 函数在单元格区域 (数据区) 中搜索错误。IF 函数在指定的条件计算结果为 TRUE 时返回指定值，在计算结果为 FALSE 时返回另一个值。在此处，它为所有错误值返回空字符串(“”)，因为它们的计算结果为TRUE，并且返回该区域 (数据区) 中的其他值（因为这些值计算结果为 FALSE，表示它们不包含错误值）。接着 SUM 函数计算筛选出的数组的总和。

示例2：计算区域中错误值个数。

本示例与上面的公式相似，但它返回名为“错值区”的区域中的错误值个数，而不是将错误值筛选掉：

{=SUM(IF(ISERROR(错值区),1,0))}

该公式创建一个数组，它为包含错误的单元格包含值 1，为不包含错误的单元格包含值0。可以简化该公式，并达到相同的结果，方法是移除 IF 函数的第三个参数，如下所示：

{=SUM(IF(ISERROR(错值区),1))}

如果未指定该参数，IF 函数在单元格不包含错误值时返回 FALSE。可以进一步简化该公式：

{=SUM(IF(ISERROR(错值区)*1))}

此公式版本可以执行计算是因为 TRUE*1=1 并且 FALSE*1=0。

示例3：条件求和。

有时候需要有条件的求和。下面的数组公式仅对名为“销售量”的区域中的正值求和：

{=SUM(IF(销售量>0,销售量))}

IF 函数创建正值和 false 值数组。

还可以对满足多个条件的值求和。例如，下面的数组公式计算大于 0 并且小于等于 3 的值：

{=SUM((销售量>0)*(销售量<=3)*(销售量))}

还可以创建使用 OR 条件的数组公式。例如，可以对小于 3 和大于 4 的正值求和：

{=SUM(IF((销售量<3)*(销售量>0)+(销售量>4),销售量))}

IF 函数查找所有小于 3 和大于 4 的正值，然后将这些值传递给 SUM 函数。

示例4：计算零以外的平均值。

本示例是需要对区域中的值求平均值时，如何从该区域中移除零。下面的公式使用名为“销售量”的数据区域：

{=AVERAGE(IF(销售量<>0,销售量))}

IF 函数创建不等于 0 的值数组，然后将这些值传递给 AVERAGE 函数。

示例5：计算两个单元格区域中的不同值个数。

此数组公式对名为“销售量”和“上期销售量”的两个单元格区域中的值进行比较并返回它们之间不同值的个数。如果这两个区域中的内容完全相同，此公式将返回 0。要使用此公式，单元格区域必须大小相同并且包含相同的维数：

{=SUM(IF(销售量=上期销售量,0,1))}

IF 函数使用值 0 和值 1 填充数组（0 表示单元格不匹配，1 表示单元格匹配）。然后 SUM 函数返回该数组中的值的和。

示例6：查找区域中最大值的位置。

下面公式返回“销售量”区域最大值的实际单元格地址：

{=ADDRESS(MIN(IF(销售量=MAX(销售量),ROW(销售量),””)), MIN(IF(销售量=MAX(销售量),COLUMN(销售量),””)))}

本公式中，IF(销售量=MAX(销售量),ROW(销售量),””)将返回最大值的行号，其它数据的行号返回为“”，运用MIN函数取出这个最大值的行号（因为其它行号都为空，只有最大值的行号返回了）。同理，MIN(IF(销售量=MAX(销售量),COLUMN(销售量),””))返回最大值的列号。

欢迎投稿 职场/创业方向. 邮箱wangfzcom(AT)163.com：王夫子社区 » Excel技巧:数组公式的高级应用（示例解析）

标签：Excel教程