标签: 数组公式

补充一个数组公式的实际应用

关于数组公式解释见这里:http://kaikai.info/excel-array-formulas-explaination/

补充一个实际应用里出现的公式

作用是在 A组数据的各项文字中寻找到第一条包含 B 组词汇的数据,如图:

matchindex

原理是这样的,将 find()函数数组化,令其在 A4:A27 中逐个寻找 C4 中的『愿望』两字。当找到匹配时,Find() 函数会返回一个具体数值,即『愿望』两字在被查找的字串中在第几个字符出现;当找不到匹配时,则Find() 函数会返回 #Value!。在数组化后,返回的数组大致为 {#Value!, #Value!, #Value!, #Value!, 2, #Value!, …, #Value!}。

再通过 if() 和 iserror() 判断这个数组中每个数的有效性,转为 {0, 0, 0, 0, 1, 0, …, 0}。

再通过 Match() 函数将数值 1 的位置找出来,即数组第 5 项。最后再通过 Index() 函数把原歌词中的第五句摘出来,并返回到屏幕上。

通过这个组合数组函数,可以查找某个词在多个单元格中的某一个出现过。

将函数改造成:

则通过复制粘贴数组公式,可以知道 C4 这个词分别在哪几句中出现过,而不限于仅仅找出第一句。

Excel 数组公式的概念解释

Excel 公式,本质就是输入原始数据,处理后再输出结果数据,放在公式的单元格里。

有些公式,输入是一个数据,输出也是一个数据,例如取整 int()、10 底对数 log()。若 A1=5.5,=Int(A1) 显示为 5。

有些公式,输入是一组数据,输出一个数据,例如 Sum。这一组数据整个是一个参数。若 A1:A5={1,2,3,4,5},输入公式 =Sum(A1:A5),显示为 15。A1:A5 数组是 Sum() 的一个参数。

有些公式,输入是两个参数,输出一个数据,例如 Match(A2,A1:A5)。两个参数,A1 是一个待查数据,参数二是被搜索的数组。

而数组公式,则是输入一组数据,输出一组数据

以 Match() 为例,Match 公式的形式为 =Match(lookup_value, lookup_array, [match_type]),其中 第三参数 match_type (查询模式)在本文讨论中忽略。则本文讨论的简化为 =Match(lookup_value 待查询数值, lookup_array 被搜索数组)

可以看到,一个 Match 公式一次只能在 lookup_array 里查找一个数值。而把 Match 公式改写为数组公式,并用 Ctrl+Shift+Enter 确认以后,实际公式则变成了 {=Match( lookup_value_array, lookup_array, [match_type] )}。

在公式里,本来应该是单一数值的地方,被替换成了一个数组,待查询数值 变成了 待查询数组。则 Excel 会自动响应 Ctrl+Shift+Enter 命令,把该公式拆分成多次分别执行,每次取待查询数组里的一项,单独给出一个结果,然后循环到该数组里的每个元素都被查询一遍。

例如,选择 C1:C5 单元格并在公式栏中输入 =Match( B1:B5, A1:A10, 0 ) ,按 Ctrl+Shift+Enter 回车。Excel 会自动内部展开五次 =Match() 查询,每次查询在第一个参数位分别填入 B1 – B5。即在 A1:A10 中分别查找 B1 – B5 的值,查 5 遍,并把 5 个结果分别放在对应的 C1:C5 单元格里。

array_match

所以:

1. 因为往往有多个输出结果,使用数组公式需要先选择好输出位置,再在公式栏写公式,写完用 Ctrl+Shift+Enter 确认。注意,这多个单元格包含的是『一个公式』。

2. 数组公式需要你在写公式时,把『一个数据』的参数改写为『一组数据』。(例子中 Match() 函数本来的 lookup_value 即『需要查找的值』改成了『需要查找的数组』。)Excel 会自动循环这个改写数组里的每一个数据,然后把公式计算结果填到对应的单元格里。

3. 数组公式修改起来较为费劲,经常会出现『不能更改数组的某一部分』,正确的方法是先按 Ctrl+/ 全选该数组公式的整体占用位置,然后再在公式栏进行修改。

4. 某些公式,例如 Sum()、Len() 使用数组公式和直接使用该公式往往没有区别。所以如果你见到某个教程在以 Sum 举例讲数组函数时就不用往下看了。百度搜出来有不少是这样的。

5. 一般来说,常用的数组计算 Excel 都已经提供了特定的函数,比如 Logest()、Frequency 等。如果返回的值有两个以上的,也通常都拆成了多个公式,比如线性回归的 Slope()、Linest()、Steyx() 等。当需要多个计算结果时,也无需使用数组公式,使用 Excel 的公式复制粘贴就可以完成绝大部分工作。上文的例子即是如此,选择 C1:C5 然后输入 Match() 数组公式,和先在 C1 输入普通的 Match 公式 =Match( B1, $A$1:$A:$10,0 ),然后把公式复制到 C2:C5 上,效果是一样的,后续处理起来还方便一些。

 

那么数组公式有什么用呢?

大部分情况其实没什么用,确实没什么用,所以很多人用了好久也没用过数组公式。哲学点地说,等到你需要用数组公式时,数组公式就有用了。

数组公式的最大特点是『输出的是一个数组』,所以它需要用多个单元格才能放下,同时,它可以作为数组参数供其它函数使用。所以数组函数最大的使用场景是通过复杂嵌套函数,实现更大程度的 Excel 自动化。

例如,去除重复单元格,可以使用 Alt+A+M 的『删除重复项』实现,但这样意味着每次数据更新,都需要重新进行人工操作,当处理步骤较多时,往往意味着后续步骤也需要重新操作。而使用数组公式,则可以一劳永逸地解决这个问题。

因为『删除重复项』本质上就是一个『输入一个数组,输出一个数组』的操作。在这个例子里,Match() 函数的第一个参数和两个 Row() 参数进行了相同的对应循环,并把每个计算结果填入相应的单元格里。

而另一个例子

则相当于把 A1 单元格中的每个字符都单拆出来。辅以其它公式嵌套,可以比较方便地计算诸如『若干个单元格一共包含多少个特定字符』之类的问题。

 

一句话总结:当你在使用 Excel 时,需要处理『处理若干个数据,过程中包含若干数据,结果也是若干个数据』时,在宏程序之外,还可以考虑使用数组公式。