有时候,有些需求真的是让人难以想像!如下图1所示,在列A中存在文本、数值和空单元格。现在,想要求头3个出现的数字之和,也就是说,求单元格A5中的10000、A14中的2000、A20中的1000这3个数字之和。
图1
我们一眼就可以看出这3个数字是该列中首先出现的前3个数字,但Excel不知道。如何使用公式来求得这3个数字之和呢?可以使用下面的数组公式实现。
在单元格D2中输入下面的数组公式:
=SUM(SUM(OFFSET(A1,SMALL(IF(ISNUMBER(A2:A100),ROW(A2:A100)),{1,2,3})-1,)))
结果如下图2所示。
图2
上述公式可以转换为:
=SUM(SUM(OFFSET(A1,{5,14,20}-1,)))
其中,
SUM(OFFSET(A1,SMALL(IF(ISNUMBER(A2:A100),ROW(A2:A100)),{1})-1,))
返回10000。
SUM(OFFSET(A1,SMALL(IF(ISNUMBER(A2:A100),ROW(A2:A100)),{2})-1,))
返回2000。
SUM(OFFSET(A1,SMALL(IF(ISNUMBER(A2:A100),ROW(A2:A100)),{3})-1,))
返回1000。
传递到最外层的SUM函数:
SUM(10000, 2000, 1000)
得到13000。
有点难以理解!
其实,尽可能让数据符合Excel的特点,合理布局,往往会给数据分析带来便利,而不必像上面那样,费尽心力编写冗长且难以理解的数组公式了。