数组公式讲座（一）：数组公式入门

ANJEJEY

很棒!

slhenry

数组公式很奇妙，运用得好，可省很多事，但学习过程较难理解。

erhai

斑竹，如果把上例的各个数同乘以一个数该怎么做？

zhounie

这样的讲座非常有益，这是书本上看不到的。到此论坛，受益非浅！

lijianjian

真棒，我也教你生财之道------你可以开培训班了，对我你要免费：）

txzxk

我已理解了数组的含义。

lfy

Part I 简述: 数组公式: 是用于建立可以产生多个结果或对可以存放在行和列中的一组参数进行运算的单个公式。 数组公式的特点就是可以执行多重计算,它返回的是一组数据结果。 *** 由于一个单元格内只能储存一个数值,所以当结果是一组数据时,单元格只返回第一个值,如: 23 ={23,24,25,22} 如果你需要用到所有的运算结果时,要么用多个单元格去分别返回,如: 23 =INDEX({23,24,25,22},1) 24 =INDEX({23,24,25,22},2) 25 =INDEX({23,24,25,22},3) 22 =INDEX({23,24,25,22},4) 要么用某些函数来取其共性,如SUM, MAX/MIN,等 94 =SUM({23,24,25,22}) 25 =MAX({23,24,25,22}) 参数： 数组公式最大的特征就是所引用的参数是数组参数,包括区域数组和常量数组。 *** 区域数组,是一个矩形的单元格区域,如 $A$1:$D$5 常量数组,是一组给定的常量,如{1,2,3}或{1;2;3}或{1,2,3;1,2,3} 数组公式中的参数必须为"矩形",如{1,2,3;1,2}就无法引用了 输入： 同时按下CTRL+SHIFT+ENTER *** 数组公式的外面会自动加上大括号{}予以区分 55 {=SUM(($I$23:$I$25>10)*($J$23:$J$25))} 10 11 20 22 30 33 *** 有的时候,看上去是一般应用的公式也应该是属于数组公式,只是它所引用的是数组常量 对于参数为常量数组的公式,则在参数外有大括号{},公式外则没有,输入时也不必按CTRL+SHIFT+ENTER 55 =SUM(({10;20;30}>10)*{11;22;33}) 可以看到,这两个公式的结果是一样的。 接下来让我们看看数组公式是如何进行工作的。

lfy

Part II 数组公式的计算过程 产品编号 产品单价 产品数量 AA 1 100 BB 2 150 CC 3 200 AA 1 250 BB 2 300 问题:求产品AA的总价 350 {=SUM(IF(($B$4:$B$8="AA"),($C$4:$C$8)*($D$4:$D$8),0))} 它的运算过程是这样的: 1. IF函数先判断$B$4:$B$8里=AA的参数,返回的结果为 AA TRUE 1 100 BB FALSE 2 150 CC FALSE 3 200 AA TRUE 1 250 BB FALSE 2 300 用公式表述为:{=SUM(IF({TRUE;FALSE;FALSE;TRUE;FALSE},($C$4:$C$8)*($D$4:$D$8),0))} 2.然后对返回TRUE的行项执行下一个运算,($C$4:$C$8)*($D$4:$D$8) 对于返回FALSE的,则直接返回0 TRUE 1 100 100 =1*100 FALSE 2 150 0 =0 FALSE 3 200 0 =0 TRUE 1 250 250 =1*250 FALSE 2 300 0 =0 3.到了这一步,公式返回出的是一组符合要求的数字了,该组数字用数组常量表示为{100;0;0;250;0} 4.最后用SUM函数把这一组数组常量相加,得到最后结果 350 =SUM({100;0;0;250;0}) *** 该公式可简化为 350 {=SUM(($B$4:$B$8="AA")*$C$4:$C$8*$D$4:$D$8)} 在加减乘除的运算中TRUE=1,FALSE=0,所以逻辑值能直接参与运算。 但是SUM函数中如果引用的数据区域中含有逻辑值的话,是忽略逻辑值的,除非是直接作为参数。 所以当你需要在SUM中使数据区域中的TRUE=1,FALSE=0的话,必须要使TRUE/FALSE先参与一次四则运算,转换成数值。 看看下面的例子能帮助你理解逻辑值是如何出来的,并能更好地理解上面这段话。 TRUE =(1=1) FALSE =(1+1=1) 1 =TRUE+FALSE -1 =FALSE-TRUE 0 =TRUE*FALSE 1 =SUM(TRUE,FALSE) 0 =SUM(C41,C42) 1 =SUM(C41*1,C42*1) IF()的判断可以被简化,也就是利用了这一点,在Part IV里,还有更详细的例子。 相信你现在已经对数组公式有了个大概的了解了,下面让我们来看一些数组公式的应用。

lfy

Part III Row()函数在数组公式中的运用 谈到数组公式,我们不得不说一下ROW()这个函数,它在数组公式中起到了很大的作用,许多公式中都需要用到它来作为参数。 我们先来做一个题目： 求正整数列1,2,3,4……100这100个数字之和(首先假设你不知道等差数列求和公式,呵呵) 5050 {=SUM(ROW($A$1:$A$100))} *** 我们都知道,ROW()是用于返回单元格行号的函数,通常它只能引用一个参数。 但是在数组公式中,该函数就能引用多个单元格作为参数,对于整个引用区域进行分别运算,从而就能返回一组数据。 ROW(A1)=1 ROW(A2)=2 …… ROW(A100)=100 从而 ROW($A$1:$A$100)={1;2;3……100} 知道了这一点以后,我们就能在数组公式中利用这一个功能来得到一组连续的正整数。 *** 当然COLUMN()的作用和ROW()是相同的,上面的计算也能用以下公式: 5050 {=SUM(COLUMN($A$1:$CV$1))} 但是相对于ROW()的引用方式来说,A1:A100要比A1:CV1更直观地体现出所引用的是100行还是100列 所以ROW()一般来说使用得更普遍些,当然也不排除有时候需要用到COLUMN(),这就要看具体情况了。 *** 举个例子吧,我们在PartI里好象说了个把一组4个数据分别列出{23,24,25,22}, 当是我是用了=INDEX({23,24,25,22},1), 每行的参数都是手工改的,但是现在用了ROW()的话,那么只需要输入第一个公式,其他的拖拽就行了,如下: 23 =INDEX({23,24,25,22},ROW(A1)) 24 =INDEX({23,24,25,22},ROW(A2)) 25 =INDEX({23,24,25,22},ROW(A3)) 22 =INDEX({23,24,25,22},ROW(A4)) 如果是需要分列来显示,那么就用COLUMN()来的方便了 23 24 25 22

lfy

Part IV 利用数组公式进行条件求和