Excel公式技巧84：对混合数据中的数值求和

excelperfect

有时候，有些需求真的是让人难以想像！如下图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($A$1,{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的特点，合理布局，往往会给数据分析带来便利，而不必像上面那样，费尽心力编写冗长且难以理解的数组公式了。

欢迎在下面留言，完善本文内容，让更多的人学到更完美的知识。

欢迎到知识星球：完美Excel社群，进行技术交流和提问，获取更多电子资料。