Excel058-揭开数组的庐山真面目

布衣公子PPT · 公众号 · PPT · 2017-11-08 21:00

正文

很多小伙伴一听到数组就觉得很高深莫测，让人有种望而却步的感觉，其实数组并不是我们想象的这么难，只要明白了其中的原理，我们就能在工作中轻松驾驭数组公式，帮助我们解决很多棘手问题。

为了方便大家理解数组公式运算的逻辑和原理，今天先从一个简单的案例讲起。

如上图所示，我们要在I列求出每个员工一周内的值班天数，“√”表示当天值班，“×”表示当天休息。快速转动你的小脑瓜，看看有什么好的办法？

在我不会数组和一些高级函数的时候，只能用最传统的方法解决。

在I2单元格录入公式：=(B2="√")+(C2="√")+(D2="√")+(E2="√")+(F2="√")+(G2="√")+(H2="√")，向下填充即可。

这个公式无疑可以帮助我们解决这个问题，可是是不是有点太长太复杂了，当然如果你不介意，用这种方法也很好。有没有更简单的方法？数组公式要闪亮登场了。

在I2单元格录入公式：

=SUM(--(B2:H2=“√”))，按CTRL+SHIFT+ENTER三键结束，向下填充公式。

公式短了很多，看起来超级简单，但到底是什么意思呢？别着急，让我来一步一步分析给你听。

我们用F9查看公式的方法来查看每个步骤的结果。

首先用鼠标选中公式中的B2:H2部分，按F9查看公式，可以得出如上图所示的数组，数组元素放置在{}内。{}内其实是江雨薇星期一到星期日的所有值班记录。

数组其实是一组有顺序有方向的数据集合。那么怎么看顺序和方向呢。如上图所示数组，顺序从星期一到星期日依次记录该员工所有的值班记录，这跟我们选择单元格区域时的顺序是一致的。

那么方向呢？我们是从左到右选择单元格区域B2:H2的，所以这个数组是一个横向数组，我们可以从数组元素之间的分隔符来判断数组的方向。分号分隔的为纵向数组，逗号分隔的为横向数组，如果我们选择单元格区域A1:A10，就会生成纵向数组，其各元素之间以分号间隔。

接下来我们选中图①所示公式部分，按F9查看结果，生成图②所示的一组由逻辑值组成的数组。图①数组中的每一个元素会与等号后面的“ √ ”进行对比，如果相等，则显示为TRUE，反之则显示为FALSE。最后结果就生成了图②所示的一组逻辑值数组。

由于逻辑值无法直接用SUM求和，所以我们需要在前面加—符号将逻辑值转换成0或者是1。逻辑值转换为数值的方法很多，可以乘以1，可以除以1，也可以加0或减0，总之就是不要改变其原先的值就好。

最后我们用SUM函数将数组中的每一个元素加起来，就得到了最终结果。特别要注意的是，我们要按CTRL+SHIFT+ENTER三键结束公式录入才能得出正确结果，这是因为SUM本身不支持公式以数组方式进行内部运算。那么除了用SUM求和，还能用什么呢？

很多小伙伴肯定第一个想到的是SUMPRODUCT，对的，这个函数是支持数组运算的，所以用这个函数求和，不需要三键结束即可得到正确的结果。SUMPRODUCT的具体用法会在后面分享给大家的。

其实还有更简单的方法，就是用COUNTIF函数，这个函数的具体用法我们会在后面做详细的讲解哦！

往期教程：

Excel055-SUM函数其实没你想象的那么简单