1.4.3
使用数组公式解决更加复杂的问题
所谓数组公式，就是对数组进行计算的公式。前面我们介绍的公式基本上都是执行一个简单计算，并且返回一个计算结果。当需要对两组或两组以上的数据进行计算并返回一个或多个计算结果时，就需要使用数组公式了。
数组公式有以下的特征：
数组公式可以存在于多个单元格，也可以是仅输入到一个单元格。即使输入到一个单元格的数组公式，也必须同时按【Ctrl+Shift+Enter】组合键。
下面我们结合实际案例，来说明数组公式的使用方法。
n 案例1-17
图1-72的A列至C列数据为从数据库导出的数据，其中把C列的批次是数量和单位的混合字符串。现在要求把批次分成数量和单位两部分，分别保存在D列和E列，以便于以后进行统计分析。
图1-72 批次是由数量和单位组成，无法进行统计分析
显然，这样的问题不是一个简单的公式所能解决的，需要使用数组公式。
要将批次中的数量和单位分开，需要了解批次数据中从左边开始哪些字符是数字，到哪个字符就由数字变为了文本（汉字或字母），这样才能利用文本函数LEFT把数量取出来，利用文本函数MID把批次取出来。下面我们以第3行单元格C3的批次数据为例，说明解决问题思路和公式的创建方法及步骤。
要想判断批次数据中从左边开始哪些字符是数字，到哪个字符就由数字变为了文本，需要知道批次数据有多少个字符，然后利用MID函数把每个字符取出来。因此，使用LEN函数得到批次数据的长度，再利用INDIRECT函数和ROW函数得到一个从1开始到批次数据长度结束的自然数序列：选择单元格区域G2:G15（或者G列从第2行开始到某行为止，这个行数要大于批次数据字符长度），输入数组公式：
=ROW(INDIRECT("1:"&LEN(C3)))
图1-73 生成一个从1开始到批次数据长度结束的自然数序列
有了这个自然数序列，就可以利用MID函数把批次数据的各个字符分别取出来了：选择单元格区域H2:H15，输入数组公式“=MID(C3,G2:G15,1)”，如图1-74所示。
图1-74 批次数据的各个字符分别取出
由于利用MID函数从字符串中取出的不论是数字还是文本，都是被处理成文本的，因此需要把取出来的批次数据中代表数量的数字转换成真正的数字，选择单元格区域I2:I15，输入数组公式“=1*H2:H
图1-75 把取出的批次数据各个字符转换成纯数字
显然，从批次数据左侧开始取出的各个数据，只要开始出现了错误值，就表明从该字符开始就是单位了，因此需要判断从那个位置开始出现了错误值：选择单元格区域J2:J15，输入数组公式“=ISERROR(I2:I15)”，如图1-76所示。
图1-76 获取逻辑值的数组
这样，知道了批次数据各个字符串是否数字（FALSE表示是数字，TRUE表示是文本，第一个出现TRUE的位置就是单位开始出现的位置），因此可以利用MATCH函数把这个位置定出来：选择单元格K2，输入公式“=MATCH(TRUE,J2:J15,0)”，得到这个位置的数字，如图1-77所示。
图1-77 得到批次数据中哪个字符开始就是单位
知道了批次数据中哪个字符开始就是单位，利用LEFT函数就很容易把数量数字取出，利用MID函数把单位取出：选择单元格L2，输入公式“=1*LEFT(C3,K2-1)”，得到批次数据中的数量数字，如图1-78所示。
图1-78 得到批次数据中哪个字符开始就是单位
最后，再按照上面相反的顺序，将中间的计算过程逐一消除，把中间计算过程的计算公式综合成一个公式，步骤如下：
步骤1：单元格L3的公式引用了单元格K2的结果，而单元格K2的计算公式为“=MATCH(TRUE,J2:J15,0)”，这样，把单元格K2的计算公式去掉等号，仅仅复制等号后面的函数本身，并把它替换掉单元格L3计算公式中的单元格引用K2，得到新的计算公式：
=1*LEFT(C3,MATCH(TRUE,J2:J15,0)-1)
步骤2：这个公式中引用了单元格区域J2:J15的结果，而单元格区域J2:J15的计算公式为“=ISERROR(I2:I15)”，这样，把上述公式中的单元格引用J2:J15替换为ISERROR(I2:I15)，得到新的计算公式（需要注意从此开始是数组公式了，因此要按【Ctrl+Shift+Enter】组合键）：
=1*LEFT(C3,MATCH(TRUE,ISERROR(I2:I15),0)-1)
步骤3：这个公式中引用了单元格区域I2:I15的结果，而单元格区域I2:I15的计算公式为“=1*H2:H
=1*LEFT(C3,MATCH(TRUE,ISERROR(1*H2:H15),0)-1)
步骤4：这个公式中引用了单元格区域H2:H15的结果，而单元格区域H2:H15的计算公式为“=MID(C3,G2:G15,1)”，这样，把上述公式中的单元格引用H2:H15替换为MID(C3,G2:G15,1)，得到新的计算公式：
=1*LEFT(C3,MATCH(TRUE,ISERROR(1*MID(C3,G2:G15,1)),0)-1)
步骤5：这个公式中引用了单元格区域G2:G15的结果，而单元格区域H2:H15的计算公式为“=ROW(INDIRECT("1:"&LEN(C3)))”，这样，把上述公式中的单元格引用G2:G15替换为ROW(INDIRECT("1:"&LEN(C3)))，得到新的计算公式：
=1*LEFT(C3,MATCH(TRUE,ISERROR(1*MID(C3,ROW(INDIRECT("1:"&LEN(C3))),1)),0)-1)
步骤6：这个公式就是最终需要的公式，将这个公式本身原封不动地复制到单元格D3（要注意将公式复制到单元格后，按【Ctrl+Shift+Enter】组合键），就得到了批次数据中的数量。
步骤7：在单元格E3输入公式“=MID(C3,LEN(D3)+1,999)”，得到了批次数据中的单位。
最后的结果如图1-79所示。
图1-79 利用数组公式分离数量和单位
说明：这个案例也为我们介绍了一种创建复杂计算公式的方法，即公式分解与综合法。
n 案例1-18
有时候报表中会有一些错误值，这时如果使用SUM函数进行加总的话，就会出现错误，而得不到正确的结果，如图1-80所示。为了能够得到正确的计算结果，也就是忽略错误值，仅仅计算单元格区域的数值，就需要使用下面的数组公式了（参见图1-81）：
=SUM(IF(ISERROR(B2:B6),"",B2:B6))
或者
=SUM(IFERROR(B2:B6,""))
