精通Excel数组公式026：你弄清楚大型数组公式是怎么工作的吗？
excelperfect
在本系列中，大部分内容都是在阐述特定数组公式如何工作的逻辑，但是假设你有一个大型的数组公式，却不知道它是如何工作的，你该怎么办？你已经学到了许多技术，弄清楚为什么一个公式正在做它该做的事。
弄清楚特定数组公式工作逻辑的技巧：
1.将公式分解成尽可能小的部分，将每部分放置在单独的单元格中，这可以让你看到每部分是如何工作的。这给你提供了不同的视角，不同于单个单元格中查看整个公式。这一点在公式元素随公式的复制而变化时，尤其正确。如果你将这样的公式元素放置在单个单元格中并复制，可以清楚地看到这部分公式正在做什么。
2.当公式在单个单元格中时，运行“公式求值”命令（按Alt,M,V键，或者选择功能区“公式”选项卡“公式审核”组中的“公式求值”）。“公式求值”功能对于看到公式计算时Excel所遍历的步骤是非常好的。但有两个缺点：（1）有时评估的公式元素相对于公式求值对话框来说太大了；（2）有时这个对话框没有显示所有步骤或者与在公式处理于编辑模式时使用F9键显示的结果不同。
3.使用评估公式元素技巧，当公式在单元格中处于编辑模式时，按F9键评估公式的每个单独部分。这是一个非常宝贵的技巧，用来学习公式是如何做的。在使用F9键评估公式元素后，记得使用Ctrl+Z撤销评估。注意，如果公式元素评估后的字符数超过8192个，会给出错误消息，因为单元格能够显示的最大字符数是8192个。F9键与公式求值相比的优点在于，使用F9键有时显示公式元素评估的结果，而公式求值则不会显示。
4.查阅Microsoft函数帮助，这些文章介绍了函数的许多隐藏的功能。
5.观察屏幕提示，哪个参数以粗体突出显示，以帮助你了解特定的公式元素在大公式中的位置。要突出显示特定的公式元素，可以单击屏幕提示中相应的参数名称，这将突出显示位于该函数参数中的完整的公式元素。
6.使用“评估公式元素技巧”（按F9键）和阅读屏幕提示中函数参数名称相结合来“查看”每个公式元素向给定的函数参数传递的内容。
7.输入完整的公式后，将该单元格进入编辑模式，可以按F9键来评估公式的每个部分。当完成查看每个公式元素评估的结果后，按Esc键返回到单元格中的公式。注意，如果使用F9键之后按Enter键，那么评估计算的值将被硬编码到公式中。
8.如果短时间内还没有弄清楚，不要放弃。很多公式高手对于一些公式也会花费很多时间才弄明白。当你弄清楚并掌握后，这一切的工作都是值得的。
查找包含空单元格的行中的第1个数据项
下图1展示了一个数组公式，获取一行中的第1个非空单元格中的数值。你可以使用上面讲解的技巧来看看该公式的运行原理（这里略过，原文讲解得非常详细，对于初学者来说是一份很好的参考资料）。
图1
查找与行中第1个非空单元格相关的列标题
如下图2所示，从列标题中获取与行中第1个非空单元格对应的日期。
图2
查找列，在该列中匹配条件并提取数据
如下图3所示，首先查找一列（“第3天”），然后在该列中匹配条件（Job 4），获取对应的员工名，并垂直显示。注意，在右下侧列出的公式适用于Excel2010及以后的版本。
图3
查找列中最长的单词
下图4展示了一个公式，用来查找一列中具有最大字符数的数据项。
图4
计算满足1个条件的百分位
下图5展示了如何计算与第90百分位相应的CPA分数。被计算的分数将数据集划分为90%的值低于标记值，10%高于标记值。单元格D4和D5中计算所有CPA数据的百分位标记。单元格D11和D17计算满足条件（即学校名称）的百分位标记。
图5
按条件排序
有时候，需要按条件对数据排序。如下图6所示，计算每个系的学生成绩排名。
图6
计算连续两天运行时间之和的最大值
如下图7所示，计算7天内连续两天运行时间之和的最大值。
图7
根据可变长度的系列折扣计算等效净成本
如下图8所示，对于单元格D3来说，公式必须执行计算：0.8*0.95*0.9*0.8，而复制到单元格D4中，则执行计算：0.9*0.9。
图8
计算连续出现的最大次数
如下图9所示，使用了FREQUENCY函数，令人惊叹！公式中，OR条件统计是否在两列中的某一列，AND条件确定不在两列的任一列中。
图9
最后的提示
数组公式不能够计算整列
在Excel中，虽然可以创建非常大的数组公式，但不能创建使用整列的数组，因为重新计算使用整列单元格的数组公式是非常耗时的，Excel不允许创建这类数组。
使用定义的名称来避免Ctrl+Shift+Enter
如下图10所示，将公式中需要按Ctrl+Shift+Enter键的部分定义为名称，然后在后面的公式中使用这个名称，此时的公式不再需要按Ctrl+Shift+Enter键。
图10
注：本文为电子书《精通Excel数组公式（学习笔记版）》中的一部分内容节选。你可以到知识星球App的完美Excel社群下载这本电子书的完整中文版。
欢迎到知识星球：完美Excel社群，进行技术交流和提问，获取更多电子资料。