前往顾页
以后地位: 主页 > 精通Office > Excel教程 >

Excel数组公式从入门到精通—进步篇

时候:2017-04-29 21:39来源:知行网www.zhixing123.cn 编辑:麦田守望者

信赖你在“入门篇”中已学会了若何建立数组公式,同时也年夜致体味在甚么环境下适合利用数组公式处理问题。需求申明的是,在“入门篇”中提到的利用数组公式的三种环境其实不是绝对的,要视详细环境而定。
      在接上去的会商中,你将会体味数组公式的一些事情道理。
      在进行正式会商之前,先跟着我做一些筹办事情。
      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的结果。
      技术申明:
      了解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()结果都不异,要视详细环境而定,这就需求你矫捷把握了。
      Excel中的IF()事情表函数对前提真假的判定是如许,以后提的值为0时,以为是假;不然,全数以为是真。前提的数据范例必然是数值。比如“=IF(-3,1,0)”前往1。是以“+”的操纵做到了摹拟OR的结果。
------分开线----------------------------
标签(Tag):EXCEL excel数组公式 Excel2003
------分开线----------------------------
保举内容
猜你感兴趣