Excel数组公式顶级绝密ExcelArrayformulatopsecret
本⽂⽬标：
1、 理解excel软件背后数据处理的基本原理；
2、 搞懂excel数组公式原理，并会灵活运⽤，放⼤⾃⼰数据处理基本技能
excel vlookup函数怎么用⽹络上很多Excel教程，为了便于读者理解，将数组公式和⼤括号即“{}”进⾏对等，或者ctrl+alt+enter进⾏对等，这样对于很多刚刚接触excel这⼀强⼤的数据处理⼯具的⼈来说，能够快速上⼿。但从数组公式计算原理，哪些公式⽀持数组公式，如何更好的利⽤数组公式，⽀持⾃⼰的⼯作来说，增加了理解难度。为了便于后⽂概念的理解，这⾥插⼊⼀个重要的概念，excel基本数据类型。
Excel表格之中数据主要包括：
1、 数值类型 例如“123.456”就是⼀种数值类型，需要注意的excel之中时间类型的数据也是数值类型，这就是为什么，将时间类型的数据进⾏值粘贴后，结果变成了数值，还要进⾏时间格式设置的原因；
2、 字符型 该种类型很好理解，中⽂汉字、英⽂字符，各种符号都是字符型，就连平时看不见的空格也是字符型，咱中国⼈都知道汉字是象形⽂字，它是⼀种符号，汉字“⼀”是⼀种符号，同样它也有数值概念，阿拉伯数值也可以作为⼀种符号，总之是世间可以看见万物都可以作为符号。
3、布尔型excel之中只有两种TRUE和FALSE对应汉字“真”和“假”很好理解
4、空 也很好理解，就是excel单元格内什么也没有，这⾥需要注意的是和空字符的区别，同时需要注意的是excel之中空在有些场合对应的是数值类型的0。
5、错误类型，这种数据类型⽐较少见，例如当excel公式⾥⾯出现例如1/0情况时，会有#DIV/0!以“#”符号开头的结果，这样类型的数据就是错误类型，需要注意的是和字符串类型的区别。
上述的各种数据类型，通过⾁眼观察通常情况下很容易混淆，可通过type函数进⾏判断。例如字符型返回结果是2，布尔型是4。
返回结果
数据类型
公式返回结果
数据类型数据
数据公式
在这⾥有很多朋友，会举起⼿来，抢答我还能够通过单元格格式进⾏判断。这⾥先讲个案例吧。李⽟刚是现阶段很有名的花旦，⼤家都知道他本尊是纯爷们，但⼀上舞台，他能够⽐⼥⼈更妩媚，数据类型好⽐李⽟刚本尊，单元格格式好⽐上台艺术表演，只是表象，为了某种需要的设定，12345数值类型，能够通过单元格设置显⽰为1.23万，但它还是数值类型，参与数值计算，数值43101能够设定单元格格式，让它显⽰为2018-1-1。单元格格式设定是很⼤的⼀块内容，后续将会安排⽂章进⾏讲解，这⾥不做展开了。这⾥需要给他家灌输⼀个观点，excel是⼀种软件，excel的数据类型是给电脑看的，excel各种数据的处理都是基于这些基础的数据类型上进⾏处理的；单元格格式设定是给⼈看，便于⼈的理解。该种观念将会存在于后续整个系列⽂章，希望这个系列⽂章后，⼤家能够培养这样的观念。
我们再回到数组公式这条轨道上来，如果借⽤在计算机软件领域数组概念，数组表⽰的是⼀组相同类型数据的区块。Excel之中“数组公式”因为它是普通⼤众的应⽤的公式，它和程序员接触的数组概念有相似的地⽅，也有不同的地⽅。对于学习excel⼯具的⼈来说，不⽤去纠结其中的差别。微软提出数组
公式的概念，⽬的在于丰富excel公式的处理能⼒和应⽤场景。举个例⼦，excel之中的LEN()函数，它功能⽤于计算“字符”的长度，如果是空、数值和真假类型的数据，它先将这些类型的数据转化为字符类型，再计算其长度。它的参数会有两种情况，例如如果是⼀个单元格，那它就计算长度，并将结果返回回来，如果是⼀⽚区域单元格呢？第⼆种情况就涉及到本⽂的主题“数组公式”。
本⽂这⾥要提出⼀个观点，就是excel之中“数组”⽆处不在，只是它静静的，默默的在那⾥替您做了很多的事情，{}和ctrl+alt+enter好⽐
公司那些能说会道的，做了⼀部分的⼯作，将整个功劳揽到⾃⼰的头上的那个⼈。
excel之中，⼀列单元格(例如A1:A5)，它是⼀组相同类型的内容，他是数组（暂且这么认为，实际上有个转化过程，后⽂说明怎么转化的），假如=sum(A1:A5)函数对这列内容进⾏求和，它会返回最终的这些数据的内容的和，⽽且只有⼀个结果。你按不按ctrl+alt+enter组合键，对他没有任何影响；=sum({1,2,3,4,5})这样的公式，它也会返回⼀个结果，⽽且你按不按ctrl+alt+enter对他的结果没有任何影响。这⾥可能就有疑问了，这⾥需要告诉您的是SUM函数是⽀持数组公式的，那{}和ctrl+alt+enter这⾥发挥了怎样的作⽤了？
针对上述疑问，这⾥要告诉⼤家的是，“数组”在上述各种情况都发挥的作⽤，按了组合键情况，为了区别没按组合键，把它称为“数组公式”。上述案例⽐较特殊，都能返回相同且正确的结果，那⼤家就好奇
了，那组合键在数组公式之中扮演了什么样的⾓⾊呢！可以告诉⼤家的是组合键在“数组公式”这个特殊公式类别之中扮演者“导演”⾓⾊，它的戏路将⼀步步给您解开。
⼤学线性代数数组概念⼤家都了解，如果两个同形数组相减就是对应位置的元素相减，其结果也是⼀个同形的数组。=A1:A5-B1:B5，如果将结果放置C1:C5单元格，并且使⽤组合键的话，其情形和线性代数的数组概念完全吻合。如果不按组合键，您会发现其结果和当前活动单元格的位置有很⼤关系，它只返回当前活动单元格同⾏的数据计算结果，如果当前单元格的⾏和公式应⽤区域的⾏不对应就直接返回错误值了，这也是⽹络教程告诉您如果不按组合键，它就返回错误值的原因所在，因为这些⽂章的案例，都是不在同⾏引⽤的，所以返回错误值，如果放在同⾏的话，它还是有结果的，只是这个结果，不是您想要的结果。针对这些规律给出以下结论：
1、 Excel 如果⼀个区域是⼀⾏或者⼀列的话，不按组合键，它将取公式所在单元格对应⾏或者列的内容进⾏计算，如果区域是⼆维区域，不按组合键，它将取⽤公式所在单元格对应⾏和列的数据进⾏计算；
2、Excel 如果公式参数是常量数组例如{1,2,3,4,5}，如果不按组合键，所有的数据都参加计算，但公式计算结果只返回了结果数组的第⼀个元素；
3、 如果是引⽤是单元格区域和常量数组组合使⽤，如果不按组合键，引⽤区域提取的数据和常量数
组相乘，这点类似线性代数数组乘以⼀个系数，但公式计算结果只返回了结果数组的第⼀个元素；
相当于给excel⼀个通知，告诉excel软件将引⽤的单元格区域转化为常量数组，并参加计算，如果⽆组合
4、 Ctrl+alt+enter组合键，相当于给excel⼀个通知
键，Excel只会按照情况“1”的规则取⼀个数据，下图是⼀个例⼦，⼤家可以练习试试。
以下给出⼀个excel公式的数据取⽤转化规则，虽然这不⼀定符合excel软件背后的程序规则，但能够⽅便⼤家理解。对于Excel软件来说，如果公式是⼀个引⽤区域，如果⽆组合键通知，则软件将公式所在单元格对应⾏或者列的⼀个数据参加计算，如果有组合键通知，则会取出整个引⽤区域内的数据作为
⼀个常量数组。所以有组合键的情况下，不管公式的参数是引⽤，还是常量数组，最终都变成了常量数组的计算问题了，对于⽆组合键的情况，也可以理解为⼀种特殊形式的常量数组了，这就是前⽂之中说的excel公式之中“数组”概念⽆处不在的原因了。所以
ctrl+alt+enter组合键相当于，在excel公式之中单元格引⽤区域数据取⽤规则中扮演了“导演”⾓⾊。
到这⾥您可能⼜有疑问了，那为什么公式=SUM(A1:A5)，结果都是按照数组公式的结果返回的呢。这⾥需要跟⼤家说明的是，excel公式可以包含数据（例如A1:A5），运算符（例如+ - * 、）和预定义函数。Excel预定义函数本⾝对其参数的取⽤也有它⾃⼰的规则，⽽且该规则要优先于ctrl+alt+enter组合键。SUM函数将其内部的单元格区域作为⼀个数组来看待的。Excel之中引⽤频率很⾼的VLOOKUP函数就是个很好的例⼦，在⼀般场合使⽤中，第⼀个参数是⼀个单元格，其实它也可是⼀个数组，组合键对第⼀个参数取⽤规则有影响，但第⼆个参数本⾝就是⼀个区域，函数直接将其作为⼀个常量数组看待，所以其不受组合键的影响，⼤家可以些数据练习下。不过Vlookup函数使⽤数组公式⽐较变态，⽽且也没有任何好处，所以⽹络上很少有使⽤Vlookup函数数组公式的案例。
那函数=SUM(A1:A5*B1:B5)，为什么组合键对其有影响呢，这是因为组合键影响的是区域A1:A5和B1:B5之间的运算符，这点类似于数学之中运算优先级的问题。组合键影响A1:A5和B1:B5运算，其结果作为⼀个常量数组，提供给SUM函数运算。
那么⼤家可能⼜有疑问了，哪些函数⽀持数组公式呢，并且影响的是哪个参数呢？
在⼀般应⽤情况下，参数是⼀⽚的引⽤区域，那这个参数就⽀持数组直接取⽤规则例如sum函数Vlookup函数第⼆个参数，如果只是单个单元格应⽤较多，那就受组合键的影响，例如len函数vlookup函数第⼀个参数。
说了这么多，今后就别再问那些公式是数组公式了，因为就没有“数组公式”这⼀特殊的东西，数组在excel公式运算之中⽆处不在，常⽤的组合键只是影响了数组的取⽤规则，和最终数据的呈现上。⼀些函数的参数可以是数组，计算结果也可以是数组，这个计算结果可以作为另外⼀个函数的参数进⾏计算，ctrl+alt+enter组合键只是扮演了“导演”⾓⾊，它不是“主⾓”，它只是告知excel如何将单元格区域转化为数组的⽅式，和最终结果的展现上。
以上说了那么多，希望对excel数组公式有困惑的朋友有所帮助，当然本⼈⽂字功底和对excel的认识⽔平均有限，⾥⾯不免有不恰当，甚⾄让⼈困惑的地⽅，如果有何疑问欢迎提出，如果有错误的之处，也请不吝指正。
⽂章链接：
Excel 数组公式综合案例
Tushare 财经数据获取平台
Excel动态数组
Excel 数组公式真的会更耗时吗

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。