对很多Excel的初学者来说,“数组”是个令人望而生畏的概念。但是,只要稍微花一点时间,就会发现数组并不难理解。尤其是,在新版本的Excel中,已经支持动态数组了,就更加有必要了解数组的来龙去脉和它们的使用了。 当然,在Excel中,我们说到数组,一般是指在公式中引用的数组或者返回的数组,而不是VBA中的数组。 简单地说,数组就是一组数据的集合。其中的每个数据一般被称为数组元素,简称元素。 例如: 1,2,3,5,8,11,19 就可以看做一个“数组”。 1,3,5,7,9,11,13 2,4,6,8,10,12,14 也可以看作是一个“数组”。 而下面的数据: 1 3 5 7 9 当然也可以看做一个“数组”。 从中可以看出,数组其实天生就跟Excel的表格比较相配。上面的三种数组实际上跟下图中的表格区域中的值没有区别: 所以,我们在理解数组时,在直观上就可以“当成”Excel中的表格区域来想象。(当然,具体到公式中,两者还是有一定区别的)。 实际上,如果值存放在单元格区域中,那么我们就是用: A1:A5 来引用这些数值。 如果单元格中没有存放这些数值,就可以使用数组: 1 3 5 7 9 来引用这些数值。 当然,数组中的元素不一定是数字,也可以指文本,日期,逻辑值等数据类型。 数组有一个很重要的概念:维数。 在Excel的公式中,我们接触到的数组都是一维的或者二维的。 一维数组 所谓一维数组,就是只有一行的数组或者只有一列的数组: 只有一行的数组: 1,2,3,5,8,11,19 只有一列的数组: 1 3 5 7 9 二维数组 二维数组就是有多行多列的数组。下面就是一个2行7列的二维数组: 1,3,5,7,9,11,13 2,4,6,8,10,12,14 上面例子中的数组不能直接放在Excel公式中,必须加上一个符号,这样Excel的计算引擎一眼就能看出后面的内容是数组,就可以进行数组对应的处理了。 这个符号就是: {} 所有的数组元素都必须在一对大括号中 例如: {1,3,5,7,9,11,13} 在某些公式中可以直接使用数组: =SUM({1,3,5,7,9,11,13}) 数组中如果有多列,列之间要用英文的逗号:,隔开。 如果数组中有多行,行之间需要使用英文的分号:;隔开。 =SUM({1;3;5;7;9;11;13}) 结果跟上面是一样的: 如果多行多列,就需要同时使用逗号和分号来区分不同的行和不同的列: {1,2,3;4,5,6;7,8,9} 在不支持动态数组的Excel中使用数组,分为两种情况:
先来看第一种情况:
严格地说,这种情况不属于大家经常听说的“数组公式”, 比如,我们前面的例子: =SUM({1,3,5,7,9,11,13}) 尽管使用了数组,对使用者来说就好像是使用区域一样,不需要额外的操作。(不用CTRL+SHIFT+ENTER)。 这种情况比较简单。 2. 参数是数组,结果是数组 这才是真正的数组公式。 例如: {=IF({0,1},"A","B")} 这就是一个数组公式,输入的时候,要输入公式本身(不要输入大括号): =IF({0,1},"A","B") 然后按CTRL+SHIFT+ENTER来完成公式的输入。输入之后的公式在编辑栏中就自动加上了大括号: 因为输入的是一个数组(1行两列),返回的一个跟输入数组相同维数的数组(1行两列)。所以,一般应该在Excel中选择好放置结果的区域:两个相邻的单元格, 然后输入公式: 然后按CTRL+SHIFT+ENTER,完成公式输入,得到完整的公式结果: 需要再次提醒的是,结果数组跟参数数组一定是相同的维数,上面的例子中,参数数组是 {0,1}, 是一行两列的数组,所以结果也是一行两列的数组,你只能选择同一行上相邻的两个单元格来接收数组。 如果公式改一下: =IF({0;1},"A","B") 参数变成了: {0;1}, 是一个两行一列的数组,返回结果也是两行一列的数组,你只能选择同一列上相邻的两个单元格接收数组: 有的时候数组公式的结果并不一定是个数组。 例如,在这篇文章(原来,VLOOKUP也可以反向查找)中,我们提到了一个VLOOKUP公式: 用这个公式进行反向查找。这个公式的结果只返回一个值,但是中间过程中的IF公式却需要返回多个值,因此,仍然是数组公式,需要CTRL+SHIFT+ENTER来完成公式输入。 还有一个经常遇到的例子就是条件求和(在没有SUMIF函数之前经常用): 这里,我们需要求所有>0的数据的合计,可以使用公式: {=SUM(IF(A2:A9>0,A2:A9,0))} 这里可以理解为IF函数针对A2:A9做了一个循环,得到了一个中间数组,然后对中间数组进行求和: 当ROW和COLUMN函数中的参数是一个多行或多列的区域时,返回的实际是一个数组。 ROW函数实际返回的是多行1列的数组: 这个结果实际上: {1;2;3;4;5;6;7;8;9} COLUMN函数返回的是1行多列的数组: 这个结果实际上: {1,2,3,4,5,6,7,8,9} 利用这两个函数,我们可以简化很多数组公式的写法。 比如,公式: {=CHOOSE({1,2,3,4,5,6,7,8,9},"a","b","c","d","e","f","g","h","i")} 可以简化为: {=CHOOSE(COLUMN(A:I),"a","b","c","d","e","f","g","h","i")} 提醒一下,这么使用的时候一定要注意需要的是单行数组还是单列数组。 |
|