excel数组公式从入门到精通
数组公式从入门到精通
入门篇
本主题包含三部分：入门篇、提高篇、应用篇（分中级和高级）
对于刚接触Excel数组公式的人来说，总是会感觉到它的一份神秘。又Excel的Online Help中只有很少关于它的主题，所以这种神秘感就更强了。不要紧，只要跟着我的思路走，你很快就会看清数组公式的真面目！
数组概念
对于数组概念，大家都会很熟悉，其就是一个具有维度的集合。比如：一维数组、二维数组、多维数组。数组的表示一般为“{}”所包括（一维和二维数组）。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)
之所以创建以上公式，是因为我想对满足“Product ID”为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()还用理解？Excel Online Help中不是已经表达的很清楚了吗？也许你会这样问。
我并非是想文字充数，请看下图：
图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)主关键区域决定数组函数返回值的大小（关键区域是指决定数组公式返回结果集大小的区域）
看“图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函数应该很多人都用过，非常好用。但如果遇到多条件判断的怎么办呢？从Office 2007开始，引入了SUMIFS函数，可以解决这种多条件求和问题。但如果仍然使用Office 2003怎么办呢？其实使用SUM、IF再结合数组公式即可实现SUMIFS的效果。
如下图所示，某教师有一张任教的几个班级的学生成绩表。
任务：统计出“一班”、“二班”共计多少人？
此题要如何解决？SUMIF用两次？或是COUNT用两次？这里还是演示数组公式的用法，所以先用SUM和IF组合的形式。
在任一单元格中输入“=SUM(IF((A2:A12="一班")+(A2:A12="二班"),1,0))”公式按下回车键，是不是发现结果是“#V ALUE！”，再次进入编辑栏然后按下“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是Office 2007以后的产物。
答案：在上图所示的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了，然后再依次完成整个数组的运算，我们最终的目的就达到了。
数组公式非常有用，效率也高，但真正的理解、熟练掌握也不是一件很容易的事。但大家记住数组中的数据是一一对应的，放到数组公式中使用时，数组中的数据会按顺序依次参与相应的运算。
希望大家能够慢慢的理解、贯通。
Excel数组公式实现条件统计
Excel 公式函数学习宝典 Excel数组公式实现条件统计 [文章信息] 作者: 陈秀峰原创 时间: 2004-11-30 出处: 天极网 责任编辑: Shiny [文章导读] 分男、女组统计学生每次考试成绩的最高（低）分、平均分、总分等，这种统计可以用数组公式来实现…… 前两天遇到一位教师朋友，他向我请教一个问题：学校为了研究男生和女生在学习上是否存在差别，在初二年级随机选定了100名学生（男生、女生各50名）作为研究对象，分男、女组统计他们每次考试成绩的最高（低）分、平均分、总分等，成绩表是用Excel制作的（如图1）。其实这种统计可以用数组公式来实现。 1、启动Excel2003（其他版本操作相似），打开成绩表。 2、在表格的下部，依照图2的样式，制作保存统计结果的表格。
Excel 公式函数学习宝典 3、选中D104单元格，输入公式：=MAX(IF($C$3:$C$102=$C104,D$3:D$102))，输入完成后，按下“Ctrl+Shift+Enter”组合键对公式进行确认。 注意：我们这里输入的是一个数组公式，数组公式输入完成后，不能直接按“Enter”键进行确认，必须按“Ctrl+Shift+Enter”组合键进行确认。数组公式被确认后，会在公式两端出现数组公式的标志符号——一对大括号{}（如图3）。 4、再次选中D104单元格，将鼠标移至该单元格右下角成细十字线状（填充柄）时，按住左键向下拖拉至D111单元格中。
Excel 公式函数学习宝典 5、然后将D106与D107、D108与D109、D110与D111单元格中第一个函数名称（MAX）分别修改为“MIN、AVERAGE、SUM”，每个公式修改完成后，均需要按“Ctrl+Shift+Enter”组合键进行确认。 注意：统计最高分也可以用下述数组公式：=MAX(($C$3:$C$102=$C104)*(D$3:D$102))（D104单元格）； 统计总分也可以用下述数组公式：=SUM(($C$3:$C$102=$C110)*(D$3:D$102))（D110单元格），或者用非数组公式：=SUMIF($C$3:$C$102,$C111,D$3:D$102)（D111单元格）。 6、同时选中D104至D111单元格，用“填充柄”将上述公式拖拉复制至E104至K111单元 格区域中，所有统计数据即刻呈现在我们面前（参见图2）。
Excel数组公式
Excel数组公式 ●什么是数组公式？ 引用了数组（可以是一个或多个数值，或是一组或多组数值），并在编辑栏可以看到以“{}”括起来的公式就是数组公式。而数组公式的作用就是对一组（单个数据可以看成是一组）、多组数据进行处理，然后得到想要的结果。Excel中数组公式非常有用，尤其在不能使用工作表函数直接得到结果时，数组公式显得特别重要，它可建立产生多值或对一组值而不是单个值进行操作的公式。 ●输入数组公式： 首先必须选择用来存放结果的单元格区域（可以是一个单元格），在编辑栏输入公式，然后按Ctrl＋Shift＋Enter组合键锁定数组公式，Excel将在公式两边自动加上花括号“{}”。注意：不要自己键入花括号，否则，Excel认为输入的是一个正文标签。而双击进入公式的编辑状态时，你会发现“{}”符号是不存在的。 ●选取数组公式： 所占有的全部区域先选中区域中任意一个单元格，然后按下Ctrl＋/键即可。 ●编辑或删除数组公式： 编辑数组公式时，须选取数组区域并且激活编辑栏，公式两边的花括号将消失，然后编辑公式，最后按Ctrl＋Shift＋Enter键。选取数组公式所占有的全部区域后，按Delete键即可删除数组公式。 ●数组常量的使用： 数组公式中还可使用数组常量，但必须自己键入花括号“{}”将数组常量括起来，并且用“，”和“；”分离元素。其中“，”分离不同列的值，“；”分离不同行的值。 使用不在工作表中的数组： 有时，公式仅占用一个单元格时也需要以数组的方式输入。其具体原则是：一个公式使用了数组，并且这个数组不在工作表上，就应该使用数组的方式输入这个公式。 下面介绍两个使用数组公式的例子。 1.如需分别计算各商品的销售额，可利用数组公式来实现。 单元格F2中的公式为：{=SUM(IF(A2:A11=″商品1″，B2:B11＊C2:C11,0))}。这个数组公式创建了一个条件求和，若在A2:A11中出现值“商品1”，则数组公式将B2:B11和C2:C11中与其相对应的值相乘并累加，若是其他值则加零。同时，虽然数组B2:B11和C2:C11均在工作表中，但其相乘的数组B2:B11＊ C2:C11不在工作表中，因此必须使用数组公式。 2.假设要将A1:A50区域中的所有数值舍入到2位小数位，然后对舍入的数值求和。很自然地就会想到使用公式：=ROUND(A1,2)＋ROUND(A2,2)＋…＋ROUND(A50,2)。 有没有更简捷的算法呢？有。因为数组ROUND(A1:A50,2)并不在工作表中，因此要使用数组的方式输入公式，即：{=SUM(ROUND(A1:A50,2))}。
最新常见的勾股数组公式
常见的公法股数公式20161003整理 ＜一＞、22n m a -=,mn b 2=,22n m c +=,)1(≥n m 证明：略 1）这是我见到的勾股数组公式中最全面的一组，但我不知道它是不是包含了所有的 勾股数组；（估计是包含了） 2）这组勾股数组经过一定的变换便可得到许多变式的勾股数组的公式； 3）此组中有不少是三个数有公约数的； 4)三个数中要么两奇数一偶数，要么三个都是偶数；（至少有一个偶数）
＜二＞、当第一组中的n=1时，有12-=m a ,m b 2=,12+=m c ,)1( m ，这说明它与第一组是特殊与一般的关系。 1）这组勾股数的b 是连续偶数； 2）b-a=2,即第三个数比第一个数大2； 3）此组中有不少是三个数有公约数的；
4）这组只是第一组中的n=1部分；它不包含第一组中的n=2、3、4、5……; 5) 如果我们对这一组再进行一些变形代换，还可以得到不同的勾股数组； ＜三＞、当第一组中的n=m-1, 有1 2)1(22-=--=m m m a , m m m m b 22)1(22-=-=, 122)1(222+-=-+=m m m m c ,)1( m ，这说明它与第一组是是特殊与一般的 关系。
1）此组中的b 是4的倍数，且为4的1、3、6、……、2 ) 1(+k k 倍(k 是正整数)； 2）此组中有b-c=1,即c 比b 大1； 3）此组中的a 是不小于3的连续奇数； ＜四＞、当第一组 中的m=n+1时, 有1 2)1(22+=-+=m n n a , n n n n b 22)1(22+=+=, 122)1(222++=++=n n n n c ,)1(≥n ，这说明它与第一组是是特殊与一般的关 系。
Excel数组公式应用详解
Excel数组公式应用详解 一、什么是数组公式？ 直接看微软的解释，也许并不是很容易理解，根据个人的理解，让其更直白一点，可以这样简单理解，引用了数组（可以是一个或多个数值，或是一组或多组数值），并在编辑栏可以看到以“{}”括起来的公式就是数组公式。而数组公式的作用就是对一组（单个数据可以看成是一组）、多组数据进行处理，然后得到想要的结果。 二、如何输入数组公式 既然数组公式是以“{}”括起来的，那是不是在编辑栏在公式的两端分别输入“{}”就可以了呢？答案是否定的，在Excel中要输入数组公式，必须以特定的方法来输入，算是告诉Excel，我们这里输入的是数组公式。 在某个单元格输入数组公式的方法如下： 1.在编辑栏输入完整的公式，并使编辑栏仍处在编辑状态； 2.按下Ctrl+Shift+Enter快捷键 经过以上两步操作以后，编辑栏会自动脱离编辑状态，并且选中单元格后，在编辑栏可以看到公式的两端有“{}”符号标记，而双击进入公式的编辑状态时，你会发现“{}”符号是不存在的。 三、数组公式有什么用？ 这里做了一个类似微软官网上的例子，这里详细说明一下用法及好处。 以上面图片中的内容为例，假设我一共买了三支股票，其股份及买入价格分别如图中所示，现在我要计算我的总股本。
正常情况下我应该如何做？在B4输入“=B2*B3”，然后填充至D4单元格，这样B4、C4、D4就分别是每一支股票的股本了，然后在B5单元格再输入“=SUM(B4:D4)”，这样总股本就出来了。 上面的计算过程可以说一点问题没有，也绝对正确。但是试想一下，如果类似的数据有很多，如果不是计算我自己买的几支股票而是其他类似情况的数据处理的时候，采用上面的方法时，其工作量可想而知了。 我们再回到第一幅图中的公式“{=SUM(B2:D2*B3*D3}”，此公式便是一个典型的数组公式的应用，此公式的作用就是计算B2*B3、C2*C3以及D2*D3的和。而 B2:D2*B3*D3便是一个数组，其中包含三个元素，各元素的值就分别是各项的乘积。 为了更好的验证数组说法，分别在B4、C4、D4单元格中分别输入=B2*B3、=C2*C3、=D2*D3，B7、C7、D7单元格中全部输入=B2:D2*B3:D3，结果如下图所示。
Excel中数组函数的运用(excel函数)
Excel中数组公式非常有用，尤其在不能使用工作表函数直接得到结果时，数组公式显得特别重要，它可建立产生多值或对一组值而不是单个值进行操作的公式。 输入数组公式首先必须选择用来存放结果的单元格区域（可以是一个单元格），在编辑栏输入公式，然后按Ctrl＋Shift＋Enter组合键锁定数组公式，Excel将在公式两边自动加上花括号“{}”。注意：不要自己键入花括号，否则，Excel认为输入的是一个正文标签。 编辑或删除数组公式编辑数组公式时，须选取数组区域并且激活编辑栏，公式两边的花括号将消失，然后编辑公式，最后按Ctrl＋Shift＋Enter键。选取数组公式所占有的区域后，按Delete键即可删除数组公式。 下面介绍几个使用数组公式的例子。 1、有如图所示的工作表，需分别计算出两个班的男女生人数。 单元格B22中的公式为：=SUM((A2:A20="一1班")*(D2:D20="男"))，再按Ctrl＋Shift＋Enter键。这个数组公式创建了一个条件求和，若在A2:A20中出现值“一1班”，则返回一个逻辑值“true”，值为“1”，若D2:D20中出现值“男”，也返回一个逻辑值“true”，值为“1”，则数组公式将与其相对
应的值相乘并累加，若是1*1=1,则加1，若是其他就返回1*0=0或是0*1=0,则累加零。(虽然数组A2:A20和D2:D20均在工作表中，但其相乘的数组A2:A20和D2:D20不在工作表中，因此必须使用数组公式)。 求女生一1班的女生人数也是一样，把公式改为：=SUM((A2:A20="一1班")*(D2:D20="女"))，当然，要是求一2班或是其它班级的男女生数也是一样的道理，请大家自己领会。 2、在统计考试成绩的时候，有可能要统计出90—100分、80—89分、70—79分等各分数段的人数，并计算出占班级人数的百分比，这时也要利用数组公式更方便。同样以下面的工作表作为例子：要求出分数在70—80分之间的人数。 在B24中输入一个数组公式：=SUM((E2:E20>=70)*(E2:E20<=80)) ，再按Ctrl＋Shift＋Enter键。这个数组公式也创建一个条件求和，若是E2:E20当中的成绩>=70并且E2:E20当中的成绩<=80，返回1*1=1，sum就累加1，反之1*0=0或是0*1=0，就累加0。如图：
数组运算法则
认识一维数组和二维数组。理清概念很重要，不要混淆数组、数组公式。 第一，一维数组和二维数组的定义 单行或单列的数组，我们称为一维数组。 多行多列（含2行2列）的数组是二维数组。 第二，数组和数组公式的区别 数组，就是元素的集合，按行、列进行排列。 数组公式：就是包含有数组运算的公式。ctrl+shift+enter，三键结束，这个过程就是告诉excel请与数组运算的方式来处理本公式，反馈一个信息，就是在公式的外面添加一对花括号。 第三，一维数组和二维数组的运算规律 1、单值x与数组arry运算 执行x与arry中每一个元素分别运算并返回结果，也就是与arry本身行列、尺寸一样的结果。 比如：2*{1,2;3,4;5,6}，执行2*1、2*2、2*3……2*6运算，并返回3行2列的二维数组结果{2,4;6,8;10,12}，如下图所示： 数组中行和列分别用逗号、分号来间隔。逗号表示行，行之间的关系比较紧密，用逗号分割；列之间，关系相对比较疏远一点，用分号分割。 又比如："A"&{"B","C"}返回{"AB","AC"}。"A"={"B","A","C"}返回{FALSE,TRUE,FALSE} 2、同向一维数组运算 执行arry1与arry2对应位置的元素分别运算并返回结果。要求arry1与arry2尺寸必须相同，否则多余部分返回#N/A错误。 比如： {1;2;3}*{4;5;6}返回{4;10;18}； {1,2,3,4}*{4,5,6}返回{4,10,18,#N/A}，如下图所示： 3、异向一维数组运算 arry1的每一元素与arry2的每一元素分别运算并返回结果，得到两个数组的行数*列数个元素，也就是M行数组与N列数组运算结果为M*N的矩阵数组。 比如：{1;2;3}*{4,5,6,7,8}，执行1*4、1*5、……1*8、2*4、2*5……3*8，返回{4,5,6,7,8;8,10,12,14,16;12,15,18,21,24}
彻底教会你使用Excel数组公式
彻底教会你使用Excel数组公式 Excel中函数、公式大家都非常熟了，但“数组公式”你是否听过、用过？什么是数组公式呢？根据微软官方给出的解释，数组公式：数组公式对一组或多组值执行多重计算，并返回一个或多个结果。 一、什么是数组公式？ 直接看微软的解释，也许并不是很容易理解，根据个人的理解，让其更直白一点，可以这样简单理解，引用了数组（可以是一个或多个数值，或是一组或多组数值），并在编辑栏可以看到以“{}”括起来的公式就是数组公式。而数组公式的作用就是对一组（单个数据可以看成是一组）、多组数据进行处理，然后得到想要的结果。 二、如何输入数组公式 既然数组公式是以“{}”括起来的，那是不是在编辑栏在公式的两端分别输入“{}”就可以了呢？答案是否定的，在Excel中要输入数组公式，必须以特定的方法来输入，算是告诉Excel，我们这里输入的是数组公式。 在某个单元格输入数组公式的方法如下： 1.在编辑栏输入完整的公式，并使编辑栏仍处在编辑状态； 2.按下Ctrl+Shift+Enter快捷键 经过以上两步操作以后，编辑栏会自动脱离编辑状态，并且选中单元格后，在编辑栏可以看到公式的两端有“{}”符号标记，而双击进入公式的编辑状态时，你会发现“{}”符号是不存在的。 三、数组公式有什么用？ 这里做了一个类似微软官网上的例子，这里详细说明一下用法及好处。
以上面图片中的内容为例，假设我一共买了三支股票，其股份及买入价格分别如图中所示，现在我要计算我的总股本。 正常情况下我应该如何做？在B4输入“=B2*B3”，然后填充至D4单元格，这样B4、C4、D4就分别是每一支股票的股本了，然后在B5单元格再输入“=SUM(B4:D4)”，这样总股本就出来了。 上面的计算过程可以说一点问题没有，也绝对正确。但是试想一下，如果类似的数据有很多，如果不是计算我自己买的几支股票而是其他类似情况的数据处理的时候，采用上面的方法时，其工作量可想而知了。 我们再回到第一幅图中的公式“{=SUM(B2:D2*B3*D3}”，此公式便是一个典型的数组公式的应用，此公式的作用就是计算B2*B3、C2*C3以及D2*D3的和。而 B2:D2*B3*D3便是一个数组，其中包含三个元素，各元素的值就分别是各项的乘积。 为了更好的验证数组说法，分别在B4、C4、D4单元格中分别输入=B2*B3、=C2*C3、=D2*D3，B7、C7、D7单元格中全部输入=B2:D2*B3:D3，结果如下图所示。
EXCEL中数组函数
EXCEL中数组函数 Excel中数组公式非常有用，尤其在不能使用工作表函数直接得到结果时，数组公式显得特别重要，它可建立产生多值或对一组值而不是单个值进行操作的公式。 输入数组公式首先必须选择用来存放结果的单元格区域（可以是一个单元格），在编辑栏输入公式，然后按Ctrl＋Shift＋Enter组合键锁定数组公式，Excel将在公式两边自动加上花括号“{}”。注意：不要自己键入花括号，否则，Excel认为输入的是一个正文标签。 编辑或删除数组公式编辑数组公式时，须选取数组区域并且激活编辑栏，公式两边的花括号将消失，然后编辑公式，最后按Ctrl＋Shift＋Enter键。选取数组公式所占有的区域后，按Delete键即可删除数组公式。下面介绍几个使用数组公式的例子。 1、有如图所示的工作表，需分别计算出两个班的男女生人数。 单元格B22中的公式为：=SUM((A2:A20="一1班")*(D2:D20="男"))，再按Ctrl＋Shift＋Enter键。这个数组公式创建了一个条件求和，若在A2:A20中出现值“一1班”，则返回一个逻辑值“true”，值为“1”，若D2:D20中出现值“男”，也返回一个逻辑值“true”，值为“1”，则数组公式将与其相对应的值相乘并累加，若是1*1=1,则加1，若是其他就返回1*0=0或是0*1=0,则累加零。(虽然数组A2:A20和D2:D20均在工作表中，但其相乘的数组A2:A20和D2:D20不在工作表中，因此必须使用数组公式)。 求女生一1班的女生人数也是一样，把公式改为：=SUM((A2:A20="一1班")*(D2:D20="女"))，当然，要是求一2班或是其它班级的男女生数也是一样的道理，请大家自己领会。
Excel数组公式
数组公式(最基础) 如果你是用Excel的人，相信你对“公式”这个概念并不陌生。 可是，什么是数组公式？ 如果你是初学Excel的人，如果你对数组公式还不知道是什么回事，如果你对数组公式感兴趣，那么你可以试着读一读此贴，也许对你认识什么是数组公式，并去用好它会有一点点帮助。 当然，这些只是一些最基础的知识，只是在你学习数组公式前帮助你练的一个基本功。希望对你有用，也希望大家在读贴的过程中能把自己的学习心得以及遇到的问题放出来，供大家一起学习。 第一部分：了解数组公式 在开始讲数组公式之前，我们先来认识几个必要的概念。 1、数组 什么是数组？仁者见仁，智者见智。我个人的感觉是：数组是具有某种联系的多个元素的组合。某班级里有50个学生，这里，如果班级是数组，50个学生就是数组里的50个元素。当然，班级里的元素是可变的，可以是20个，可以是30个，也可以是60个。放到Excel里，班级就相当于工作表，而学生就相当于工作表里的单元格数值。所以，Excel里的数组，我还把它理解是为多个单元格数值的组合。 2、公式 如果你在使用Excel，如果你说你还没听过“公式”这个名词，我只能说：“你太OUT了！” 什么是公式？我的理解是：在Excel里，凡是以半角符号“=”开始的、具有计算功能的单元格内容就是所谓的Excel公式。如：=SUM(B2:D2)，=B2+C2+D2这些都是公式。 3、数组公式 数组公式是相对于普通公式而言的。普通公式（如上面的=SUM(B2:D2)，=B2+C2+D2等），只占用一个单元格，只返回一个结果。而数组公式可以占用一个单元格，也可以占用多个单元格。它对一组数或多组数进行多重计算，并返回一个或多个结果。 集合在教室外面的学生，老师把他们叫进教室。老师说：“第一组第一桌的同学进教室。”于是第一组第一桌的同学走进教室。老师接着叫：“第一组第二桌的同学进教室。”然后是第二桌的同学进教室。老师再叫：“第一组第三桌的同学进教室。”然后第三桌的同学走进教室。接着是第四桌，第五桌……，就这样一个学生一个学生的叫，这就是普通公式的做法，学生回到座位，就像数值回到工作表的单元格里，一个座位叫一次，就像一个单元格输入一个公式。 如果老师说：“第一组的全部进教室。”学生听到命令后，第一桌的同学走进去，然后是第二桌，第三桌……，老师不用再下第二个命令，这是数组公式的处理方法。 4、数组公式的标志 在Excel中数组公式的显示是用大括号对“{}”来括住以区分普通Excel公式。如图： （1）数组公式： （2）普通公式：输入数组公式：用Ctrl+Shift+Enter结束公式的输入。 特别提醒：这是最关键的，这相当于用户告诉Excel：“我不是一般人，爷我是数组公式，你得对我特别关照。”于是，Excel 明白了，不能用常规的逻辑来对待这位大爷。当你按下三键后，Excel会自动给公式加上“{}”以和普通公式区别开来，不用用户输入“{}”，但如是是想在公式里直接表示一个数组，就需要输入“{}”来把数组的元素括起来。如： =IF({1,0},D2:D8,C2:C8)这个公式里的数组{1,0}的括号就是用户自己输入的。 5、数组的维数 “维数”是数组里的又一个重要概念。数组有一维数组，二维数组，三维数组，四维数组……，在公式里，我们更多接触到的只是一维数组和二维数组。一维数组我们可以简单地看成是一行的单元格数据集合，比如A1:F1。一维数组的各个元素间用英文的逗号“,”隔开(如果是单独的一列时，用英文分号“;”隔开）。
一起来认识数组公式(最基础)祥解
一起来认识数组公式（最基础） ＝＝＝＝＝＝＝写在前面＝＝＝＝＝＝＝ 如果你是用Excel的人，相信你对“公式”这个概念并不陌生。 可是，什么是数组公式？ 如果你是初学Excel的人，如果你对数组公式还不知道是什么回事，如果你对数组公式感兴趣，那么你可以试着读一读此贴，也许对你认识什么是数组公式，并去用好它会有一点点帮助。 当然，这些只是一些最基础的知识，只是在你学习数组公式前帮助你练的一个基本功。希望对你有用，也希望大家在读贴的过程中能把自己的学习心得以及遇到的问题放出来，供大家一起学习。 第一部分：了解数组公式 在开始讲数组公式之前，我们先来认识几个必要的概念。 1、数组 什么是数组？仁者见仁，智者见智。 我个人的感觉是：数组是具有某种联系的多个元素的组合。某班级里有50个学生，这里，如果班级是数组，50个学生就是数组里的50个元素。当然，班级里的元素是可变的，可以是20个，可以是30个，也可以是60个。放到Excel里，班级就相当于工作表，而学生就相当于工作表里的单元格数值。所以，Excel 里的数组，我还把它理解是为多个单元格数值的组合。 2、公式 如果你在使用Excel，如果你说你还没听过“公式”这个名词，我只能说：“你太OUT了！” 什么是公式？我的理解是：在Excel里，凡是以半角符号“=”开始的、具有计算功能的单元格内容就是所谓的Excel公式。如：=SUM(B2:D2)，=B2+C2+D2这些都是公式。 3、数组公式 数组公式是相对于普通公式而言的。普通公式（如上面的=SUM(B2:D2)，=B2+C2+D2等），只占用一个单元格，只返回一个结果。 而数组公式可以占用一个单元格，也可以占用多个单元格。它对一组数或多组数进行多重计算，并返回一个或多个结果。 集合在教室外面的学生，老师把他们叫进教室。老师说：“第一组第一桌的同学进教室。”于是第一组第一桌的同学走进教室。老师接着叫：“第一组第二桌的同学进教室。”然后是第二桌的同学进教室。老师再叫：“第一组第三桌的同学进教室。”然后第三桌的同学走进教室。接着是第四桌，第五桌……，就这样一个学生一个学生的叫，这就是普通公式的做法，学生回到座位，就像数值回到工作表的单元格里，一个座位叫一次，就像一个单元格输入一个公式。 如果老师说：“第一组的全部进教室。”学生听到命令后，第一桌的同学走进去，然后是第二桌，第三桌……，老师不用再下第二个命令，这是数组公式的处理方法。 4、数组公式的标志 在Excel中数组公式的显示是用大括号对“{}”来括住以区分普通Excel公式。 如图： （1）数组公式： （2）普通公式： 输入数组公式：用Ctrl+Shift+Enter结束公式的输入。 特别提醒：这是最关键的，这相当于用户告诉Excel：“我不是一般人，爷我是数组公式，你得对我
Excel数组公式的神奇妙用
数组公式从入门到精通 入门篇 本主题包含三部分：入门篇、提高篇、应用篇（分中级和高级） 对于刚接触Excel数组公式的人来说，总是会感觉到它的一份神秘。又Excel的Online Help中只有很少关于它的主题，所以这种神秘感就更强了。不要紧，只要跟着我的思路走，你很快就会看清数组公式的真面目！ 数组概念 对于数组概念，大家都会很熟悉，其就是一个具有维度的集合。比如：一维数组、二维数组、多维数组。数组的表示一般为“{}”所包括（一维和二维数组）。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”组合键。
Excel数组公式及运用
第一部分：了解数组公式 在开始讲数组公式之前，我们先来认识几个必要的概念。 1、数组 什么是数组？仁者见仁，智者见智。 我个人的感觉是：数组是具有某种联系的多个元素的组合。某班级里有50个学生，这里，如果班级是数组，50个学生就是数组里的50个元素。当然，班级里的元素是可变的，可以是20个，可以是30个，也可以是60个。放到Excel里，班级就相当于工作表，而学生就相当于工作表里的单元格数值。所以，Excel里的数组，我还把它理解是为多个单元格数值的组合。 2、公式 如果你在使用Excel，如果你说你还没听过“公式”这个名词，我只能说：“你太OUT了！”什么是公式？我的理解是：在Excel里，凡是以半角符号“=”开始的、具有计算功能的单元格内容就是所谓的Excel公式。如：=SUM(B2:D2)，=B2+C2+D2这些都是公式。 3、数组公式 数组公式是相对于普通公式而言的。普通公式（如上面的=SUM(B2:D2)，=B2+C2+D2等），只占用一个单元格，只返回一个结果。 而数组公式可以占用一个单元格，也可以占用多个单元格。它对一组数或多组数进行多重计算，并返回一个或多个结果。 集合在教室外面的学生，老师把他们叫进教室。老师说：“第一组第一桌的同学进教室。”于是第一组第一桌的同学走进教室。老师接着叫：“第一组第二桌的同学进教室。”然后是第二桌的同学进教室。老师再叫：“第一组第三桌的同学进教室。”然后第三桌的同学走进教室。接着是第四桌，第五桌……，就这样一个学生一个学生的叫，这就是普通公式的做法，学生回到座位，就像数值回到工作表的单元格里，一个座位叫一次，就像一个单元格输入一个公式。 如果老师说：“第一组的全部进教室。”学生听到命令后，第一桌的同学走进去，然后是第二桌，第三桌……，老师不用再下第二个命令，这是数组公式的处理方法。 4、数组公式的标志 在Excel中数组公式的显示是用大括号对“{}”来括住以区分普通Excel公式。 如图： （1）数组公式： （2）普通公式：
Excel的SUM函数9种公式设置范例(完整资料).doc
【最新整理，下载后即可编辑】 Excel的SUM函数9种公式设置范例 1、数组求和：{=SUM((G12:G21>100)*G12:G21)} [公式说明]：本公式为数组公式，可以对G12:G21区域中大于100的数据进行求和，而排除小于等于100的数据。输入公式时必须按【Ctrl+Shift+Enter】组合键结束，否则无法得到正确结果。 [使用注意]：1、公式中"G12:G21>100"部分表示求和条件，后跟实际求和区域"G12:G21"。如果有多个条件，可一并罗列出来。例如求大于100且小于115的数据之和，公式如下:=SUM(G12:G21>100)*(G12:G21<115)*G12:G21)。2、此数组公式只适用于单个区域求和，如果有多个区域，只能用多个SUM 求和，然后相加。例如对G12:G21和H12:H21区域中大于100的数汇总，公式如下:=SUM(SUM((G12:G21>100)*G12:G21),SUM((H12:H21>100)*H1 2:H21))。3、对于SUM函数的数组公式，可以用SUMPRODUCT 函数来代替，从而将数组公式转换成普通公式。例如本案例的公式用SUMPRODUCT函数后，普通公式如下:=SUMPRODUCT((G12:G21>100)*G12:G21)。 2、数据类型转换求和：=SUM(VALUE(H5),H6:H10,J5:J10,L5:L10)；=SUM(--(H5),H6:H10,J5:J10,L5:L10)；=SUM((H5)*1,H6:H10,J5:J10,L5:L10)；=SUM((H5)/1,H6:H10,J5:J10,L5:L10) [公式说明]：SUM函数用于对单元格区域的数据或者逻辑值、表达式进行求和，它有1-255个参数。鉴于本题的特殊性，公式也可以改为"=SUM(区域1)"，函数会忽略区域中的文本。 [使用注意]：1、SUM函数有1-255个参数。参数可以是区域，也
数组公式的使用基础(常用)
教你使用Excel数组公式 Excel中数组公式非常有用，尤其在不能使用工作表函数直接得到结果时，数组公式显得特别重要，它可建立产生多值或对一组值而不是单个值进行操作的公式。 输入数组公式首先必须选择用来存放结果的单元格区域（可以是一个单元格），在编辑栏输入公式，然后按Ctrl＋Shift＋Enter组合键锁定数组公式，Excel将在公式两边自动加上花括号“{}”。注意：不要自己键入花括号，否则，Excel认为输入的是一个正文标签。 选取数组公式所占有的全部区域先选中区域中任意一个单元格，然后按下Ctrl＋/ 键即可。 编辑或删除数组公式编辑数组公式时，须选取数组区域并且激活编辑栏，公式两边的花括号将消失，然后编辑公式，最后按Ctrl＋Shift＋Enter键。选取数组公式所占有的全部区域后，按Delete键即可删除数组公式。 数组常量的使用数组公式中还可使用数组常量，但必须自己键入花括号“{}”将数组常量括起来，并且用“，”和“；”分离元素。其中“，”分离不同列的值，“；”分离不同行的值。 使用不在工作表中的数组有时，公式仅占用一个单元格时也需要以数组的方式输入。其具体原则是：一个公式使用了数组，并且这个数组不在工作表上，就应该使用数组的方式输入这个公式。 下面介绍两个使用数组公式的例子。 1.如需分别计算各商品的销售额，可利用数组公式来实现。 单元格F2中的公式为：{=SUM(IF(A2:A11=″商品1″，B2:B11＊C2:C11,0))}。这个数组公式创建了一个条件求和，若在A2:A11中出现值“商品1”，则数组公式将B2:B11和C2:C11中与其相对应的值相乘并累加，若是其他值则加零。同时，虽然数组B2:B11和C2:C11均在工作表中，但其相乘的数组B2:B11＊C2:C11不在工作表中，因此必须使用数组公式。 2.假设要将A1:A50区域中的所有数值舍入到2位小数位，然后对舍入的数值求和。很自然地就会想到使用公式：=ROUND(A1,2)＋ROUND(A2,2)＋…＋ROUND(A50,2)。 有没有更简捷的算法呢？有。因为数组ROUND(A1:A50,2)并不在工作表中，因此要使用数组的方式输入公式，即：{=SUM(ROUND(A1:A50,2))}。
Row函数在Excel数组公式中的应用
Row函数在Excel数组公式中的应用 绝大多数excel数组公式中，都可以看到row函数的身影，那么row函数到底起到什么作用呢？我们先从它的基本用法说起。 Row函数，返回一个引用的行号。row() 返回公式所在行的行数，如：在第5行输入=row() 公式返回值 5 row(单元格地址) 返回单元格所在的行数，如：Row(b6) 返回B6单元格所在的行6row(开始行数:终止行数) 返回引用行的行数，结果是一组数。如：Row(1:3) 返回第1:3行所在的行数{1;2;3}，由于在单元格中只能显示一个值，所以显示的结果是1生成这样的序号有用吗？当然有！【例1】求1+2+3+4+5 ...+100=？公式{=sum(row(1:100))} 公式说明：用row函数生成 1~100的序号，然后用sum求和. 由于是数组运算，需要在公式表达式最后按ctrl+shift+enter键确认输入。 【例2】截取字符串中最前面的数字，假设A1单元格中值为353.54优质公式： =LOOKUP(9^9,--LEFT(A1,ROW(1:99)))公式说明： row(1:99) 生成{1 ;2;3;4;5;6...99} 的序号，共99个数字 --Left(A1,ROW(1:99)，分别从左边截取1个,2个,3个 (99) 个字符, 99是该字符可能的最大长度，如果字符最长20个，也可以用row(1:20)。由于截取后是文本，所以需要用--把文
本型数字转换成数值,把纯文本转换成错误值。 {3;35;353;#V ALUE!;#V ALUE!;#V.......;#V ALUE!} Loookup （9^9,) 用一个足够大的数在截取后的数字中进行查找，返回最后一个数字。由于lookup可以支持数组运算，所以公式不需要按三键输入。总结：row() 函数的主要作用是生成一组序号，在后利用这组序号对另一个或一组值进行多次处理。
Excel常用函数公式大全(实用)
Excel常用函数公式大全 1、查找重复内容公式：=IF(COUNTIF(A:A,A2)>1,"重复","")。 2、用出生年月来计算年龄公式：=TRUNC((DAYS360(H6,"2009/8/30",FALSE))/360,0)。 3、从输入的18位身份证号的出生年月计算公式： =CONCATENATE(MID(E2,7,4),"/",MID(E2,11,2),"/",MID(E2,13,2))。 4、从输入的身份证号码内让系统自动提取性别，可以输入以下公式： =IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,"男","女"),IF(MOD(MID(C2,17,1),2)=1,"男","女"))公式内的“C2”代表的是输入身份证号码的单元格。 1、求和：=SUM(K2:K56) ——对K2到K56这一区域进行求和； 2、平均数：=AVERAGE(K2:K56) ——对K2 K56这一区域求平均数； 3、排名：=RANK(K2，K$2:K$56) ——对55名学生的成绩进行排名； 4、等级：=IF(K2>=85,"优",IF(K2>=74,"良",IF(K2>=60,"及格","不及格"))) 5、学期总评：=K2*0.3+M2*0.3+N2*0.4 ——假设K列、M列和N列分别存放着学生的“平时总评”、“期中”、“期末”三项成绩； 6、最高分：=MAX(K2:K56) ——求K2到K56区域（55名学生）的最高分； 7、最低分：=MIN(K2:K56) ——求K2到K56区域（55名学生）的最低分； 8、分数段人数统计： （1）=COUNTIF(K2:K56,"100") ——求K2到K56区域100分的人数；假设把结果存放于K57单元格； （2）=COUNTIF(K2:K56,">=95")－K57 ——求K2到K56区域95～99.5分的人数；假设把结果存放于K58单元格； （3）=COUNTIF(K2:K56,">=90")－SUM(K57:K58) ——求K2到K56区域90～94.5分的人数；假设把结果存放于K59单元格； （4）=COUNTIF(K2:K56,">=85")－SUM(K57:K59) ——求K2到K56区域85～89.5分的人数；假设把结果存放于K60单元格；
Excel数组公式应用彻底醒悟
Excel数组公式应用彻底醒悟 Excel数组公式应用彻底醒悟 Excel中函数、公式大家都非常熟了，但“数组公式”你是否听过、用过？什么是数组公式呢？根据微软官方给出的解释，数组公式：数组公式对一组或多组值执行多重计算，并返回一个或多个结果。 一、什么是数组公式？ 直接看微软的解释，也许并不是很容易理解，根据个人的理解，让其更直白一点，可以这样简单理解，引用了数组（可以是一个或多个数值，或是一组或多组数值），并在编辑栏可以看到以“{}”括起来的公式就是数组公式。而数组公式的作用就是对一组（单个数据可以看成是一组）、多组数据进行处理，然后得到想要的结果。 二、如何输入数组公式 既然数组公式是以“{}”括起来的，那是不是在编辑栏在公式的两端分别输入“{}”就可以了呢？答案是否定的，在Excel中要输入数组公式，必须以特定的方法来输入，算是告诉Excel，我们这里输入的是数组公式。 在某个单元格输入数组公式的方法如下： 1.在编辑栏输入完整的公式，并使编辑栏仍处在编
辑状态； 2.按下Ctrl+Shift+Enter快捷键 经过以上两步操作以后，编辑栏会自动脱离编辑状态，并且选中单元格后，在编辑栏可以看到公式的两端有 “{}”符号标记，而双击进入公式的编辑状态时，你会发现“{}”符号是不存在的。 三、数组公式有什么用？ 这里做了一个类似微软官网上的例子，这里详细说明一下用法及好处。 以上面图片中的内容为例，假设我一共买了三支股票，其股份及买入价格分别如图中所示，现在我要计算我的总股本。 正常情况下我应该如何做？在B4输入 “=B2*B3”，然后填充至D4单元格，这样B4、C4、D4就分别是每一支股票的股本了，然后在B5单元格再输入“=SUM(B4:D4)”，这样总股本就出来了。上面的计算过程可以说一点问题没有，也绝对正确。但是试想一下，如果类似的数据有很多，如果不是计算我自己买的几支股票而是其他类似情况的数据处理的时候，采用上面的
常见的勾股数组公式
常见的公法股数公式20161003整理 证明：略 1）这是我见到的勾股数组公式中最全面的一组，但我不知道它是不是包含了所有的勾股数组；（估计是包含了） 2）这组勾股数组经过一定的变换便可得到许多变式的勾股数组的公式； 3）此组中有不少是三个数有公约数的； 4)三个数中要么两奇数一偶数，要么三个都是偶数；（至少有一个偶数）
＜二＞、当第一组中的n=1 明它与第一组是特殊与一般的关系。 1）这组勾股数的b是连续偶数； 2）b-a=2,即第三个数比第一个数大2； 3）此组中有不少是三个数有公约数的； 4）这组只是第一组中的n=1部分；它不包含第一组中的n=2、3、4、5……; 5) 如果我们对这一组再进行一些变形代换，还可以得到不同的勾股数组；
＜三＞、当第一组中的n=m-1, 有 , , 关系。 1）此组中的b是4的倍数，且为4的1、3、6(k是正整数)；2）此组中有b-c=1,即c比b大1；
3）此组中的a是不小于3的连续奇数； ＜四＞、当第一组中的m=n+1时, 有 , , 系。 1) 从此组中数据可以看出，它与第3组是一样的，但我没有找到相互的代换方法；2）此组中的a不小于3连续奇数；
3）c-b=1,即c比b大1； 4）此组中的b是4的倍数，且为4的1、3、6 (k是正整数)； ＜五＞、当第 一组中的m=,n=1时， 有 这说明它与第一组是是特殊与一般的关系 1) 此组中的b是不小于4的连续偶数；
3) 让此式中的k＝2n，便得到a=n2-1,b=2n,c=n2+1, 这正是第二组； 以上五组是我在教学和辅导中见到的公式，下面我再试写几组： ＜六＞、当第五组中的k=4n时，有a=4n,b=4n2-1,c=4n2+1,(n>0),这说明它与第五组是是特殊与一般的关系 1）a是4的k倍； 2）这是一组一偶二奇的勾股数组；
Excel数组指南(微软官方)
数组公式指南和示例 了解数组公式 本节介绍数组公式，并说明如何输入、编辑和解决疑难问题。 为什么要使用数组公式？ 如果您有在Excel中使用公式的体验，您知道您可以执行一些非常复杂的操作。例如，您可以在任何给定年份数计算贷款的总成本。但是，如果您确实要在Excel 中的主公式，您需要知道如何使用数组公式。您可以使用数组公式以执行复杂的任务，例如︰ ?计算包含在某个单元格区域中的字符数。 ?仅对满足特定条件的数字求和，例如某一区域中的最小值或介于上限和下限之间的数字。 ?对一系列值中的每第n个值求和。 注意:您可能会看到称为"CSE公式，"的数组公式，因为您按CTRL+SHIFT+ENTER输入您的工作簿。 数组和数组公式简介 如果您已完成更小的编程，您可能已跨术语数组运行。我们出于数组是项的集合。在Excel中，这些项目可以驻留在单个行（称为一维水平数组）、（一维垂直数组的形式），一列或多个行和列（二维数组）。不能在Excel中创建三维数组或数组公式。 数组公式是可以在一个或多个数组中的项目执行多个计算公式。数组公式可以返回多个结果或单个结果。例如，可以将在单元格区域的数组公式和数组公式用于计算列或行的分类汇总。可以也将在单个单元格的数组公式，然后计算单个金额。数组公式驻留在多个单元格称为多单元格的公式，并驻留在单个单元格数组公式调用单个单元格的公式。
下节中的示例将演示如何创建多单元格和单个单元格数组公式。 试一试！ 本练习演示如何使用多单元格数组公式和单个单元格数组公式来计算一组销售数据。第一组操作是使用多单元格公式计算一组小计。第二组操作是使用单个单元格公式计算总计。 创建多单元格数组公式 1.打开一个新的空白工作簿。 2.复制示例工作表数据，然后将其粘贴到新工作簿从单元格A1开始。 销售人员汽车类型销售数量单价总销售额 刘鹏四门轿车5 2200 双门轿车4 1800 尹歌四门轿车6 2300 双门轿车8 1700 林彩瑜四门轿车3 2000 双门轿车1 1600 潘杰四门轿车9 2150 双门轿车5 1950 施德福四门轿车6 2250 双门轿车8 2000 1.使用粘贴选项按钮出现附近以匹配目标格式。 2.将数组（单元格区域C2到D11）中的值相乘，选择单元格E2E11，通过， 然后在编辑栏中输入下面的公式︰