Array Formula-数组公式1

							Eraser2000自说自话之:数组公式
  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
           *** 数组公式的外面会自动加上大括号{}予以区分
                                                        10 11
                   55 {=SUM(($I$23:$I$25>10)*($J$23:$J$25))}
                                                        20 22
                                                        30 33
           ***   有的时候,看上去是一般应用的公式也应该是属于数组公式,只是它所引用的是数组常量
                 对于参数为常量数组的公式,则在参数外有大括号{},公式外则没有,输入时也不必按CTRL+SHIFT+ENTER
                    55 =SUM(({10;20;30}>10)*{11;22;33})
                 可以看到,这两个公式的结果是一样的。

 接下来让我们看看数组公式是如何进行工作的。                                    Eraser说:
                                                          数组公式可以用几个一般公式和一些过渡数据
                                                          来代替,从而达到同样的目的。
                                                          不用数组公式,地球同样转,所以千万不要迷信数
 I'm from www.officefans.net.
格只返回第一个值,如:




入时也不必按CTRL+SHIFT+ENTER




用几个一般公式和一些过渡数据

       所以千万不要迷信数
                    Part I End
Eraser2000自说自话之:数组公式
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                        Eraser说:
                       1 =SUM(TRUE,FALSE)                   TRUE和FALSE
                       0 =SUM(C41,C42)                      广泛的运用的
                       1 =SUM(C41*1,C42*1)                  所以我在这里也说了许多
               IF()的判断可以被简化,也就是利用了这一点,在Part IV里,还有更详细的例子。
相信你现在已经对数组公式有了个大概的了解了,下面让我们来看一些数组公式的应用。

I'm from www.officefans.net.
是直接作为参数。
/FALSE先参与一次四则运算,转换成数值。




    说:
    FALSE的问题,在非数组公式中也是着很
 广泛的运用的,了解这些是非常有帮助的。
 所以我在这里也说了许多,希望你们不要说我罗嗦
Part II End
Eraser2000自说自话之:数组公式
Part III Row()函数在数组公式中的运用

      谈到数组公式,我们不得不说一下ROW()这个函数,它在数组公式中起到了很大的作用,许多公式中都需要用到它来作为

      我们先来做一个题目：
      求正整数列1,2,3,4……100这100个数字之和(首先假设你不知道等差数列求和公式,呵呵)

       5886 {=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
                                                         Eraser说:
                                                         我偏不用ROW()函数来返回数列
      I'm from www.officefans.net.
许多公式中都需要用到它来作为参数。




行分别运算,从而就能返回一组数据。




用的是100行还是100列
),这就要看具体情况了。


用了ROW()的话,那么只需要输入第一个公式,其他的拖拽就行了,如下:




    函数来返回数列,我偏要自己
                    Part III End
Eraser2000自说自话之:数组公式
Part IV 利用数组公式进行条件求和

    条件求和的公式,我们一般都会使用SUMIF,但是SUMIF只能进行单个条件求和,而数组公式能帮助你进行多条件求和。

    产品编号 生产日期        产品单价 产品数量     1. 求产品BB的8月份产量
    AA     6/15/2000     1   100        625 {=SUM(IF(($B$7:$B$20="BB")*(MONTH($C$7:$C$20)=
    AA     6/20/2000     1   125            我们是这样来考虑的:
    BB     6/30/2000     2   150            用IF()来判断,如果$B$7:$B$20="BB"而且MONTH($C$7:$
    BB     7/10/2000     2   175            那么我们就取$E$7:$E$20中对应的值,否则就让它等于0。
    CC     7/15/2000     3   200   ***      公式中的"*"相当于AND,即同时满足条件
    CC     7/20/2000     3   225            该公式可以作如下简化
    AA     7/30/2000     1   250                 625 {=SUM(($B$7:$B$20="BB")*(MONTH($C$7:
    AA     8/10/2000     1   275
    BB     8/15/2000     2   300   2. 求产品BB和CC的总产值
    BB     8/20/2000     2   325       5350 {=SUM(IF(($B$7:$B$20="BB")+($B$7:$B$20="CC"),($
    CC     8/30/2000     3   350   ***      公式中的"+"相当于OR,即满足条件之一即可
    CC    10/10/2000     3   375            该公式可以作如下简化
    DD    10/15/2000     4   400                5350 {=SUM((($B$7:$B$20="BB")+($B$7:$B$20=
    DD    10/30/2000     4   425
                                   3. 求8月份之前AA的产量和8月份之后不包括AA的产品产量之和
                                       2650 {=SUM(IF((MONTH($C$7:$C$20)>=8)<>($B$7:$B$20
                                   ***      公式中的"<>"相当于不同时满足,即要么满足条件1,要么
                                            以下公式和上面的公式等价
                                                2650 {=SUM(IF((MONTH($C$7:$C$20)>=8)<>($
                                            "<>"还可以用"-"来代替
                                                2650 {=SUM(IF(($B$7:$B$20="AA")-(MONTH($C
                                            该公式可以作如下简化
    Eraser说:                                    2650 {=SUM((($B$7:$B$20="AA")<>(MONTH($C
    对于公式的简化问题,我个人认为如果不是很熟          ***      简化时,注意只能用"<>"而不能用"-"
    练的情况下,最好还是保留IF()语句。                     (有兴趣的朋友可以试试,如果一定要用"-"来进行简化,该怎
    因为有IF在的话你对公式将很容易理解,否则时         看完了这些后,Eraser2000再给你布置个家庭作业:
    间一长,不熟练的人可能自己都想不起来当时的          求产品AA和BB在8月份的总产值
                                   答案在N34单元格,不过先自己做,别先看哦 :)

    I'm from www.officefans.net.
 公式能帮助你进行多条件求和。


 $20="BB")*(MONTH($C$7:$C$20)=8),($E$7:$E$20),0))}

 $7:$B$20="BB"而且MONTH($C$7:$C$20)=8的话,
 E$20中对应的值,否则就让它等于0。
AND,即同时满足条件

 $B$7:$B$20="BB")*(MONTH($C$7:$C$20)=8)*$E$7:$E$20)}


 $20="BB")+($B$7:$B$20="CC"),($E$7:$E$20))*($D$7:$D$20))}
 OR,即满足条件之一即可

 ($B$7:$B$20="BB")+($B$7:$B$20="CC"))*$E$7:$E$20*$D$7:$D$20)}

份之后不包括AA的产品产量之和
 $C$7:$C$20)>=8)<>($B$7:$B$20="AA"),$E$7:$E$20))}
于不同时满足,即要么满足条件1,要么满足条件2,同时满足的不算

 F((MONTH($C$7:$C$20)>=8)<>($B$7:$B$20="AA"),$E$7:$E$20))}

 F(($B$7:$B$20="AA")-(MONTH($C$7:$C$20)>=8),$E$7:$E$20))}

 ($B$7:$B$20="AA")<>(MONTH($C$7:$C$20)>=8))*$E$7:$E$20)}
 <>"而不能用"-"
试试,如果一定要用"-"来进行简化,该怎么做呢？)
你布置个家庭作业:

                                              1525

                                                     Part IV End
Eraser2000自说自话之:数组公式
Part V 利用数组公式进行条件记数
       看完了如何使用数组公式求和后,我还要顺便说一下数数的问题。
       一般用COUNTIF可以进行条件计数,可是两个或以上条件时,COUNTIF就不行了,在这里我们可以用数组公式来解决。
       记数的问题,大多数情况下是用SUM来解决,是因为数数其实也是求和的一种,只不过是若干个1相加而已,但是SUM中参数的

     产品编号 生产日期        产品单价 产品数量          1.求产品AA有几次是产量大于200的
     AA     6/15/2000     1   100           2 {=SUM(IF(($B$8:$B$21="AA")*($E$8:$E$21>200),1,0))}
     AA     6/20/2000     1   125             我们是这样来考虑的:
     BB     6/30/2000     2   150             用IF()来判断,如果$B$8:$B$21="AA"而且$E$8:$E$21>200的话
     BB     7/10/2000     2   175             那么我们就赋予它值为1,不满足条件则为0。
     CC     7/15/2000     3   200             把所有的1相加就代表有几个记录是符合条件的了。
     CC     7/20/2000     3   225        *** 该公式可以作如下简化
     AA     7/30/2000     1   250           2 {=SUM(($B$8:$B$21="AA")*($E$8:$E$21>200))}
     AA     8/10/2000     1   275
     BB     8/15/2000     2   300        2.求记录中共有几种产品(即产品编号中不重复的编号总数)
     BB     8/20/2000     2   325           4 {=SUM(1/COUNTIF($B$8:$B$21,$B$8:$B$21))}
     CC     8/30/2000     3   350             我们是这样来考虑的:
     CC    10/10/2000     3   375             求共有几种产品,最好是把产品出现的次数加起来,但是产品都是
     DD    10/15/2000     4   400             出来的结果就肯定不对,最好是凡是一种产品的加起来就等于1,那
     DD    10/30/2000     4   425             有了这种想法,便有了1/COUNTIF($B$8:$B$21,$B$8:$B$21),去查
                                              出现N次,就让它等于1/N, 如"AA"共出现了4次,那么让每个"AA"都
                                              把4个1/4相加就得出了1,也就是说只要有不同的编号存在,每个编
                                              (这一条可能记住公式的人不少,真正理解的不多,我也口齿不大清

Eraser说:                                 3.求记录中共记录了几个月的数据
本小节的第3道题目说明了什么呢?                              是不是有人会用第2题里的公式去套用呢?
我想说的是数组公式并非是一成不变的,有许多"可以"                     =SUM(1/COUNTIF(MONTH(C8:C21),MONTH(C8:C21)))
和许多"不可以",这是要靠积累的。                             试过的人肯定知道,这个公式是无法输入的,所以我们必须换个思
当碰到"不可以"时,就要想想看是不是能从另外一个角                   4 {=SUM(IF(MATCH(MONTH($C$8:$C$21),MONTH($C$8:$C$21
度来看待问题,关键还是在解题的思路上面。                     *** 详细的就不多说了,给个提示吧,自己多琢磨哦
至于到底那个公式为什么不能输入,我所能告诉你的是                      利用MATCH()函数的match_type参数为0时,它只返回第一个符合
因为COUNTIF(range,criteria)函数的range参数不支持        那么第一个符合的就能得到一个参数1,排在后面的就得不到了哦
常量型数组,而且这也只是我个人通过实验得出的想法
                                              看完了这些后,Eraser2000再给你布置个家庭作业:
                                              还是第2个问题,但是用第3题的思路解,可以吗？

     I'm from www.officefans.net.
 可以用数组公式来解决。
 个1相加而已,但是SUM中参数的变化可就真谓千变万化了。


*($E$8:$E$21>200),1,0))}

 "AA"而且$E$8:$E$21>200的话,

录是符合条件的了。

 E$8:$E$21>200))}

中不重复的编号总数)
1,$B$8:$B$21))}

出现的次数加起来,但是产品都是重复出现的,如果每个产品都按1来计算的话
凡是一种产品的加起来就等于1,那么就最好了
 F($B$8:$B$21,$B$8:$B$21),去查找每个产品编号共出现了几次
"共出现了4次,那么让每个"AA"都对应一个值"1/4",
 说只要有不同的编号存在,每个编号都会被分成N份,并加上N次
真正理解的不多,我也口齿不大清楚,听的懂听不懂的请多原谅)




C21),MONTH(C8:C21)))
无法输入的,所以我们必须换个思路来考虑这个问题
 8:$C$21),MONTH($C$8:$C$21),0)=ROW($C$8:$C$21)-7,1))}
 自己多琢磨哦
e参数为0时,它只返回第一个符合条件的值的位置
 参数1,排在后面的就得不到了哦

你布置个家庭作业:
思路解,可以吗？

                                                  Part V End
Eraser2000自说自话之:数组公式
Part VI 数组应用范例
        到了这一小节,我就不再罗嗦了,自己多看看一些例子吧,研究一下对提高有帮助哦。

    1. 把B列中不重复的名称在C列中一一列出          2. 把员工按工资收入高低从大到小排列
    客户名称        不重复的清单             姓名     工资
    韦小宝         韦小宝                A        123.01
    张无忌         张无忌                B        545.45
    郭靖          郭靖                 C        848.65
    黄蓉          黄蓉                 D         54.24
    韦小宝         令狐冲                E        215.05
    令狐冲         杨过                 F         25.31
    杨过          东方不败               G        848.65
    东方不败        胡斐                 H        578.03
    胡斐          END                I        848.65
    郭靖          END                J       2154.98
    东方不败                           K         25.55
    韦小宝                            L        251.05
    张无忌                            M        545.45
    杨过                             N       1230.12

    3………
    Eraser说:
    恩……不好意思,没收集那么多,您先凑和着看成不？
    有时间的话我再给大家续~~~
    祝大家都能运用上数组公式,让数组公式为您的工作出一份
    力！
    谢谢观赏！只要您没觉得白费时间,我也就满意了,呵呵。




    I'm from www.officefans.net.
工资收入高低从大到小排列
          排列结果
            J
            N
            I
            G
            C
            H
            M
            B
            L
            E
            A
            D
            K
            F