excel数组公式从入门到精通.docx
《excel数组公式从入门到精通.docx》由会员分享，可在线阅读，更多相关《excel数组公式从入门到精通.docx（16页珍藏版）》请在冰点文库上搜索。
excel数组公式从入门到精通
数组公式从入门到精通
入门篇
本主题包含三局部：
入门篇、提高篇、应用篇〔分中级和高级〕
对于刚接触Excel数组公式的人来说，总是会感觉到它的一份神秘。
又Excel的OnlineHelp中只有很少关于它的主题，所以这种神秘感就更强了。
不要紧，只要跟着我的思路走，你很快就会看清数组公式的真面目！
数组概念
对于数组概念，大家都会很熟悉，其就是一个具有维度的集合。
比方：
一维数组、二维数组、多维数组。
数组的表示一般为“{}〞所包括〔一维和二维数组〕。
Excel中也不例外，如果你想直接表示一个数组，也必须用“{}〞括起来。
数组与数组公式
在Excel中，但凡以半角符号“=〞开场的单元格容都被Excel认为是公式，其只能返回一个结果。
而数组公式可以返回一个或者是多个结果，而返回的结果又可以是一维或二维的，换句话说，Excel中的数组公式返回的是一个一维或二维的数组集合。
在Excel中需要按下“Ctrl+Shift+Enter〞组合键完毕数组公式的输入。
为什么要用数组公式？
如果你的需要满足以下条件之一，那么采用数组公式技术可能会是你很好的选择方案。
你的运算结果会返回一个集合吗？
你是否希望用户不会有意或无意的破坏某一相关公式集合的完整性？
你的运算中是否存在着一些只有通过复杂的中间运算过程才会等到结果的运算？
看到这些另人费解的问题，你可能会摸不着头绪。
不要紧，看了以下容你也许就会明白了。
什么情况下会返回一个集合？
看一个简单的例子，选中C1:
E3，输入“={"Name","Sex","Age";"John","Male",21;"Mary","Female",20}〞，按“Ctrl+Enter〞组合键。
图1-1（ArrayFormula_A01.bmp）
结果在C1:
E3中看到的结果全是“Name〞，而实际真正返回的结果应该是一个包含三行三列的二维数组，如何办？
答案就是用数组公式。
选中C1:
E3，输入“={"Name","Sex","Age";"John","Male",21;"Mary","Female",20}〞，按“Ctrl+Shift+Enter〞组合键。
图1-2（ArrayFormula_A02.bmp）
可能你又会问，这有何用？
为何不在单元格中直接输入容，反而要这么麻烦？
这仅仅是一个例子，说明的是如何通过数组公式返回一个结果集。
给你个问题，如果存在这样一个工作表：
包含字段{"ID","Name","Sex","Age"}，如何将“Sex〞为“Female〞的记录抽取出来（为了打印报表，抽取的记录需要连续存放）？
这个问题将在“应用篇〞里进展解答。
什么情况下会用到相关公式完整性？
什么是相关公式完整性？
这仅仅是我给出的一个定义，请再回到“图1-2〞，请选择C1:
E3中任意一单元格，然后做随意的修改（哪怕和原先的公式一样），按“Enter〞键完毕输入。
结果如何？
修改未成功！
提示“不能更改数组的某一局部〞。
图1-3（ArrayFormula_A03.bmp）
为什么会是这样呢？
因为你正企图破坏相关公式的完整性。
由于C1:
E3中公式的数据源均为“{"Name","Sex","Age";"John","Male",21;"Mary","Female",20}〞，而C1:
E3共用的一个公式（这与每个单元格都有一样的公式是有区别的，因为这仅仅是C1:
E3拥有9个一样的公式，而不是一个!
），因此，当你要单独更改其中一个单元格时，系统会认为你正在更改局部单元格的数据源，如此会导致数据源不一致的现象，从而导致与其它相关单元格脱离关系，这样数组公式就失去作用，所以系统不又允许你更改数组公式的局部容。
这样的好处是可以维护数据的完整性，做到与数据源总是有一致的对应关系。
你的公式复杂吗？
如果有如下数据，在D6单元格中求出对所购物品需要付多少费用。
你会如何做？
在D6中输入“=（C2*D2+C3*D3+C4*D4）〞？
结果正确，如果中间某个单元格地址输入错误你的结果会正确吗？
如果记录不只3条，而是成千上万条，你是否会感觉到力不从心（如果不考虑单元格字符数的限制）？
如果用“图1-5〞中的方法，你的感觉又会如何？
〔在D6中输入“=SUM（C2:
C4*D2:
D4）〞，按“Ctrl+Shift+Enter〞键完毕输入。
其中涉与到的技巧会在“提高篇〞中讨论。
〕
图1-4（ArrayFormula_A04.bmp）
图1-5（ArrayFormula_A05.bmp）
怎么样？
是否了解了数组公式？
是否学会了如何使用数组公式？
是否感觉到了它的一点点威力？
请继续关注“数组公式从入门到精通〞之“提高篇〞，让我们继续深入数组公式！
数组公式从入门到精通
提高篇
本主题包含三局部：
入门篇、提高篇、应用篇〔分中级和高级〕
相信你在“入门篇〞中已经学会了如何建立数组公式，同时也大致了解在什么情况下适合使用数组公式解决问题。
需要说明的是，在“入门篇〞中提到的使用数组公式的三种情况并不是绝对的，要视具体情况而定。
在接下来的讨论中，你将会了解数组公式的一些工作原理。
在进展正式讨论之前，先跟着我做一些准备工作。
Excel的主要功能就是数据的分析和处理，我们现在只关心的是数据处理中的数据抽取。
所谓数据抽取就是对源数据按照一定的条件筛选后所得到的结果。
如何定制条件筛选呢？
方法很多，这里介绍“IF（）〞函数和模拟AND、OR的原理和用法。
模拟AND、OR
让我们先来看看为什么要模拟AND、OR，而不用Excel的工作表函数AND（）、OR（）？
建立如下列图的工作表，分别在D11、D12中输入“=SUM（IF（AND（C2:
C7=D9,D2:
D7=D10）,E2:
E7））〞、“=SUM（IF（（C2:
C7=D9）*（D2:
D7=D10）,E2:
E7））〞，并分别按“Ctrl+Shift+Enter〞完毕公式输入。
图2-1（ArrayFormula_B01.bmp）
之所以创立以上公式，是因为我想对满足“ProductID〞为D9，“City〞为D10的记录进展汇总，很明显，从上面的返回结果说明D11中的结果是正确的，而D10中的结果是错误的。
为什么会是这样呢？
在接下来的演示过讲述AND（）和OR（）函数的工作原理来解释为什么D10中的公式返回了错误的结果，以与演示为什么D11中的公式可以神奇般的得到结果。
选中在上面工作表的G2:
G7，输入“=OR（C2:
C7=D9,D2:
D7=D10）〞，按“Ctrl+Shift+Enter〞；选中H2:
H7,输入“=AND（C2:
C7=D9,D2:
D7=D10）〞，按“Ctrl+Shift+Enter〞。
图2-2（ArrayFormula_B02.bmp）
图2-3（ArrayFormula_B03.bmp）
怎么G2:
G7都是TRUE；而H2:
H7都是FALSE？
实际我们想要的是“图2-3〞中的结果。
为了节省篇幅，我直接把答案告诉你，G2:
G7中的公式相当于“=OR（C2=D9,C3=D9,C4=D9,C5=D9,C6=D9,C7=D9,D2=D10,D3=D10,D4=D10,D5=D10,D6=D10,D7=D10〞，这回知道原因了吧？
“=OR（C2:
C7=D9,D2:
D7=D10）〞返回的结果只有一个，而不是七个！
同理，AND（）函数类似。
不信，你可以更改数据表中的一些数据来进展验证。
现在你该知道D10返回错误值的原因了吧？
那为什么D11能够返回正确的结果？
这正是我们要解决AND（）和OR（）函数在数组公式中存在问题的出发点。
先看看下面这个说法：
“*〞相当于AND，“+〞相当于OR。
这是一些论坛中常见的答复，我到如今为止也这样解答了不少朋友的疑问。
结论正确么？
难道Excel中的“*〞和“+〞有两层含义？
――严格的说，这是不正确的！
因此，我已经误导了很多朋友，如果你曾经在某论坛中得到过我这样的解答，我在这里说声抱歉！
为什么“*〞和“+〞可以模拟AND和OR呢？
就像“图2-1〞中D12的公式“=SUM（IF（（C2:
C7=D9）*（D2:
D7=D10）,E2:
E7））〞。
要了解其原理，就要揭开FALSE和TRUE的面纱。
在一新工作表的C2中输入“=TRUE+0〞，按回车键；在D2中输入“=FALSE+0〞，按回车键。
图2-4（ArrayFormula_B04.bmp）
“图2-4〞中的结果说明：
将TRUE和FALSE转换为整型后的值分别为1和0。
建立如下列图中的工作表，选中D2:
E3，输入“=D$1*$C2〞，按“Ctrl+Enter〞；同样选中D6:
E7，输入“=D$5+$C6〞，按“Ctrl+Enter〞。
图2-5（ArrayFormula_B05.bmp）
从上图中很容易看出，对于“乘〞操作，只有TRUE*TRUE才会返回1〔TRUE〕，因此“*〞模拟了AND的效果；对于“加〞操作，只有FALSE+FALSE才会返回0〔FALSE〕，因此“+〞模拟了OR的效果。
技术说明：
1）Excel中的IF（）工作表函数对条件真假的判断是这样，当条件的值为0时，认为是假；否那么，全部认为是真。
条件的数据类型一定是数值。
比方“=IF（-3,1,0）〞返回1。
因此“+〞的操作做到了模拟OR的效果。
理解IF（）
IF（）还用理解？
ExcelOnlineHelp中不是已经表达的很清楚了吗？
也许你会这样问。
我并非是想文字充数，请看下列图：
图2-6（ArrayFormula_B06.bmp）
C5中的公式为“=IF（C2:
C3="Mary",ROW（D2:
D3））〞〔为数组公式〕，你知道它的值为什么是FALSE而不是三么？
聪明的你可能已经想到这种类型的数组公式返回的是一个结果集，这个结果集的大小与操作对象的大小是一致的，在这里操作对象为C2:
C3和D2:
D3，因此返回值为两个元素。
就是这样，由于C2=〞John〞，不满足条件，因此应该返回IF（）函数的第三个参数值，但这里无第三个参数，所以系统返回FALSE；由于C3=〞Mary〞，满足条件，因此返回第二个参数值，即ROW（D2:
D3），而C3对应的是D3，所以返回值应该为3。
为了验证结果，请选择C5:
C6，输入“=IF（C2:
C3="Mary",ROW（D2:
D3））〞，按“Ctrl+Shift+Enter〞。
结果如何？
图2-7（ArrayFormula_B07.bmp）
聪明的Excel
先看看这个，知道“=MIN（FALSE,3）〞的返回值么？
结果返回0，从上面论述的知识不难理解，因为FALSE转换为整型的值为0。
我们已经知道“图2-7〞中“=IF（C2:
C3="Mary",ROW（D2:
D3））〞的结果集为“{FALSE,3}〞，那么，请选择“图2-7〞中的D5，输入“=MIN（IF（C2:
C3="Mary",ROW（D2:
D3）））〞，按“Ctrl+Shift+Enter〞，看结果。
图2-8（ArrayFormula_B08.bmp）
结果竟然是3，而不是0！
这就是Excel聪明之处！
为什么说聪明呢？
因为在绝大局部情况下我们想要的结果是满足条件的局部，而舍弃非满足条件的局部。
这对筛选数据非常有帮助！
如果你坚持要将非满足条件的局部包含进来，最简单的方法可以将公式变形为“=MIN（IF（C2:
C3="Mary",ROW（D2:
D3）,））〞，简简单单的一个逗号“,〞，结果却截然不同。
对于如何对筛选有帮助，将在“应用篇〞中给予实例解答。
模拟IF（）
再来看看“图2-1〞中D12的公式“=SUM（IF（（C2:
C7=D9）*（D2:
D7=D10）,E2:
E7））〞，让我们换种形式。
在E12中输入“〞，按“Ctrl+Shift+Enter〞。
图2-9（ArrayFormula_B09.bmp）
结果也是30！
所以“*〞可以模拟IF（）！
由于我们已经揭开了TRUE和FALSE的面纱，因此不难理解，对于“*〞操作，只有TRUE*TRUE才会返回1，所以结果相当于“=SUM（0*12.34,0*13.34,1*30,0*29,0*103.05,0*113.05）〞，当然结果为30了。
注意：
并非所有情况下“*〞与IF（）效果都一样，要视具体情况而定，这就需要你灵活掌握了。
引用大小制约
此主题并非重要，不过为了使你更加深入数组公式，还是在这里介绍一下。
这里的引用大小制约指的是数组公式中各相关引用之间的大小制约或引用大小对结果集大小的制约。
1）主关键区域决定数组函数返回值的大小〔关键区域是指决定数组公式返回结果集大小的区域〕1
看“图2-7〞中的公式“=IF（C2:
C3="Mary",ROW（D2:
D3））〞，这里的主关键区域为C2:
C3，那么该公式的结果集大小为2〔即有两个元素〕。
2）有互依赖关系的引用之间大小要一致
互依赖就是共同决定某个结果。
看“图2-9〞的D12“=SUM（IF（（C2:
C7=D9）*（D2:
D7=D10）,E2:
E7））〞，其中C2:
C7与D2:
D7就是互依赖的引用，它们共同决定IF（）函数第一个参数的值，所以它们的大小必须一致，否那么返回错误值。
对于没有互依赖关系的引用大小有无限制？
这就需要视具体情况而定了，再看“图2-7〞中的公式“=IF（C2:
C3="Mary",ROW（D2:
D3））〞，其中的两个引用之间就没有大小的限制；而公式“=MIN（IF（C2:
C3="Mary",ROW（D2:
D3）））〞就不一样了，其中第一个引用的大小必须大于第二个引用的大小。
到现在为止，你可以说已经掌握了Excel中的数组公式的工作原理以与一些中级技巧。
学到现在你已经足以可以应付一些工作中的需要了，当然前提是你真正已经掌握了这些技术。
不知道你现在的感觉如何？
可能你还有一头雾水的感觉，这是正常的，Excel中的数组公式确实不是很好理解。
不要紧！
只要多做练习，将实践与理论结合，很快你就会体会到它的强大威力！
为了使你的工作效率更高，我又写了“应用篇〞，容主要是平时能够遇到问题的一些解决方案〔包括自己心得和网友一些问题的解答〕。
因此请您继续关注“数组公式从入门到精通〞之“应用篇〞，让我们展示数组公式的威力！
Excel数组公式从入门到精通之精通篇
上次写了篇《Excel数组公式从入门到精通之入门篇》，不觉已十多天过去了，今天补上“精通篇〞。
当然说“精通〞可能有点过了，但是希望大家通过这两篇博文能够真正认识“数组公式〞，并且在工作中使用数组公式帮我们解决实际问题。
Excel数组公式从入门到精通之精通篇
一、课程回忆
什么是数组公式呢？
顾名思义就是公式中包含数组的了，详细含义请参看前文。
但这里重点提醒的一点就是，如果要使用数组公式，在编辑栏输入完公式以后一定要按下“Ctrl+Shift+Enter〞组合键，使编辑栏的公式处在“{}〞之中。
二、数组公式继续深入
印象中是好几年前了，当时看过的一篇扫盲贴中，作者举的例子真是太实用了。
具体细节记不太清楚了，大致意思就是使用函数计算1到100的和。
这里同样以此为例。
1.求1到100的和
在往下看之前，大家想一下，如果让你来处理该如何来处理呢？
只用一个函数解决1到100的和，当然也可以是1000、10000甚至更多。
讨论具体的数值没有太大意义，此处只是希望通过此例让大家更进一步的了解数组公式的用法。
解答：
{=SUM（ROW（1:
100））}
问题分析：
求1到100的和，答案是5050〔小学生都知道^-^〕，但Excel必须是你告诉了它正确的方法，它才能知道。
计算从1到100的和，实际上就是计算1+2+3+4+……+98+99+100，好了，答案出来了，在编辑栏中输入“＝sum（1+2+3+4+……+98+99+100）〞。
相信聪明的一定对此答案不满意，虽然能得到正确的结果，但很明显是“错误〞的方法。
要得到1到100的正确数列，最简单的方法就是使用Row（）或是Column（）函数，由于个人习惯，我比拟习惯于Row（），所以这里以Row（）函数为例。
熟悉Row（）函数：
在A1单元格中输入“＝Row（）〞，使用填充柄填充至A5，看到什么结果？
是不是每一个单元格中值就是其对应的行数。
惊喜：
Row（）表示单前行，如果使用Row（1:
100）就表示一个数组，其中包含的便是第一到第一百行的行号，即1、2、3、……、98、99、100这些数值，现在我们就把这个数组应用到公式中。
在工作表的任意一个单元格中输入“=sum（Row（1:
100））〞，然后按Ctrl+Shift+Enter组合键，你会惊喜的发现，我们要的结果出现了。
2.在Excel2003中享受“SUMIFS〞
SUMIF函数应该很多人都用过，非常好用。
但如果遇到多条件判断的怎么办呢？
从Office2007开场，引入了SUMIFS函数，可以解决这种多条件求和问题。
但如果仍然使用Office2003怎么办呢？
其实使用SUM、IF再结合数组公式即可实现SUMIFS的效果。
如下列图所示，某教师有一任教的几个班级的学生成绩表。
任务：
统计出“一班〞、“二班〞共计多少人？
此题要如何解决？
SUMIF用两次？
或是COUNT用两次？
这里还是演示数组公式的用法，所以先用SUM和IF组合的形式。
在任一单元格中输入“=SUM（IF（（A2:
A12="一班"）+（A2:
A12="二班"）,1,0））〞公式按下回车键，是不是发现结果是“#VALUE！
〞，再次进入编辑栏然后按下“Ctrl+Shift+Enter〞快捷键，是不是发现正确的结果出来了？
这里再次解释一下这个公式“{=SUM（IF（（A2:
A12="一班"）+（A2:
A12="二班"）,1,0））}〞，外侧SUM没什么好用的了，就是求〔〕各数的和。
中间的“IF（（A2:
A12="一班"）+（A2:
A12="二班"）,1,0））〞的运算过程是这样的，判断A2:
A12区域单元格的值是否是“一班〞，如果是那么结果为1，那么此公式计算的结果依次是“1、0、0、1、0、0、1、0、0、1、0〞，因为第一个条件为真，第二个条件肯定就不为真了，因为一个单元格不可能同时等于“一班〞和“二班〞，所以第一个数组就是“1、0、0、1、0、0、1、0、0、1、0〞。
这时再判断A2:
A12区域单元格的值是否是“二班〞，如果是结果那么为1，否那么为0，所以这个数组条件计算的结果就是“0、1、0、0、0、1、0、1、0、0、0〞，中间的加号就是将这两个数组相加，也就是说最终的数组为“1、1、0、1、0、1、1、1、0、1、0〞，然后使用SUM求和，结果就为7了。
从上面的图中标注可以看出，所以的公式我全部使用了数组〔A2:
A12这就是一个数组〕，并且上图上的没有使用数组公式的公式中的数组全部可以使用单个单元格替代，之所以全部列出，还是希望大家更好的理解一下数组。
在Excel中，数组如果不放在数组公式中使用，通常数组在特定单元格中只代表与其特定单元格所对应的一个值〔数组中的一个元素〕，放在数组公式中使用时，通常整个数组元素都会参与运算。
三、数组公式精通
这里是一个实际工作中的例子，只是我稍微变化了一下，还是SUM应用的例子。
需求：
如下列图所示，现在要统计员工三在1号加工所有机器的“实绩〞，也就是说在右侧的数据中先过滤日期为1，然后再过滤人员为三的数据，最后统计实绩的结果。
如果使用一个公式完成这一需求，你能想到吗？
当然SUMIFS是除外的，因为SUMIFS是Office2007以后的产物。
答案：
在上图所示的C2单元格中输入“=SUM（（E2:
E21=A2）*（G2:
G21=B2）*（H2:
H21））〞公式，然后按下“Ctrl+Shift+Enter〞组合键，你会发现想要的结果已经出现了。
数组公式就是这么简单，解决问题也是这么简单。
这次用的公式可以看出，比之前用的公式还要简单，连IF都不要了，实际上这里的“＝〞符号就是起到了一个类似IF的效果。
这里再说明一下公式的执行过程，公式中E2:
E21表示数组区域，这个相信已经不需要再说明了，放到数组公式中就是依次取数组中的各个数值，也就是依次取日期中的值。
E2:
E21=A2，实际上就是拿日期中的每一个值依次与A2中的日期进展比对，如果相等那么结果为True，即1，如果不相等那么为False即为0。
到了这里也许你有一点明白了，如果第一不相等，那么后面的无需再继续下去了，因为公式里用的全部是“*〞乘积符号，任何数乘0等于0。
如果此项符合再继续判断G2:
G21区域，也就是用依次比对，如果和B2中的一样，那么为Ture，即1，如果为False，即0，继续下一个回合。
如果此项也为Ture，很明显前面两项的结果为1*1＝1，再乘以H2:
H21数组中对应的数字，即符合条件的“实绩〞，以第一个符合条件的第一条记录为例，在数组公式运行的第一个回合为SUM（1*1*234），结果当然为234了，然后再依次完成整个数组的运算，我们最终的目的就到达了。
数组公式非常有用，效率也高，但真正的理解、熟练掌握也不是一件很容易的事。
但大家记住数组中的数据是一一对应的，放到数组公式中使用时，数组中的数据会按顺序依次参与相应的运算。
希望大家能够慢慢的理解、贯穿。
升级会员 