Excel數(shù)組公式應(yīng)用徹底醒悟

Excel中函數(shù)、公式大家都非常熟了，但“數(shù)組公式”你是否聽(tīng)過(guò)、用過(guò)？什么是數(shù)組公式呢？根據(jù)微軟官方給出的解釋?zhuān)?span lang=EN-US>數(shù)組公式：數(shù)組公式對(duì)一組或多組值執(zhí)行多重計(jì)算，并返回一個(gè)或多個(gè)結(jié)果。

        一、什么是數(shù)組公式？

        直接看微軟的解釋?zhuān)苍S并不是很容易理解，根據(jù)個(gè)人的理解，讓其更直白一點(diǎn)，可以這樣簡(jiǎn)單理解，引用了數(shù)組（可以是一個(gè)或多個(gè)數(shù)值，或是一組或多組數(shù)值），并在編輯欄可以看到以“{}”括起來(lái)的公式就是數(shù)組公式。而數(shù)組公式的作用就是對(duì)一組（單個(gè)數(shù)據(jù)可以看成是一組）、多組數(shù)據(jù)進(jìn)行處理，然后得到想要的結(jié)果。

        二、如何輸入數(shù)組公式

        既然數(shù)組公式是以“{}”括起來(lái)的，那是不是在編輯欄在公式的兩端分別輸入“{}”就可以了呢？答案是否定的，在Excel中要輸入數(shù)組公式，必須以特定的方法來(lái)輸入，算是告訴Excel，我們這里輸入的是數(shù)組公式。

        在某個(gè)單元格輸入數(shù)組公式的方法如下：

        1.在編輯欄輸入完整的公式，并使編輯欄仍處在編輯狀態(tài)；

        2.按下Ctrl+Shift+Enter快捷鍵

        經(jīng)過(guò)以上兩步操作以后，編輯欄會(huì)自動(dòng)脫離編輯狀態(tài)，并且選中單元格后，在編輯欄可以看到公式的兩端有“{}”符號(hào)標(biāo)記，而雙擊進(jìn)入公式的編輯狀態(tài)時(shí)，你會(huì)發(fā)現(xiàn)“{}”符號(hào)是不存在的。

        

 

三、數(shù)組公式有什么用？

        這里做了一個(gè)類(lèi)似微軟官網(wǎng)上的例子，這里詳細(xì)說(shuō)明一下用法及好處。

        以上面圖片中的內(nèi)容為例，假設(shè)我一共買(mǎi)了三支股票，其股份及買(mǎi)入價(jià)格分別如圖中所示，現(xiàn)在我要計(jì)算我的總股本。

        正常情況下我應(yīng)該如何做？在B4輸入“=B2*B3”，然后填充至D4單元格，這樣B4、C4、D4就分別是每一支股票的股本了，然后在B5單元格再輸入“=SUM(B4:D4)”，這樣總股本就出來(lái)了。

        上面的計(jì)算過(guò)程可以說(shuō)一點(diǎn)問(wèn)題沒(méi)有，也絕對(duì)正確。但是試想一下，如果類(lèi)似的數(shù)據(jù)有很多，如果不是計(jì)算我自己買(mǎi)的幾支股票而是其他類(lèi)似情況的數(shù)據(jù)處理的時(shí)候，采用上面的方法時(shí)，其工作量可想而知了。

        我們?cè)倩氐降谝环鶊D中的公式“{=SUM(B2:D2*B3*D3}”，此公式便是一個(gè)典型的數(shù)組公式的應(yīng)用，此公式的作用就是計(jì)算B2*B3、C2*C3以及D2*D3的和。而B2:D2*B3*D3便是一個(gè)數(shù)組，其中包含三個(gè)元素，各元素的值就分別是各項(xiàng)的乘積。

        為了更好的驗(yàn)證數(shù)組說(shuō)法，分別在B4、C4、D4單元格中分別輸入=B2*B3、=C2*C3、=D2*D3，B7、C7、D7單元格中全部輸入=B2:D2*B3:D3，結(jié)果如下圖所示。

        從上面的圖中可以看到，第4行和第7行的計(jì)算結(jié)果是一樣的，這就是數(shù)組的效果，數(shù)組會(huì)根據(jù)當(dāng)前單元格所在位置自動(dòng)取數(shù)組中對(duì)應(yīng)序列的數(shù)值，如果將“=B2:D2*B3:D3”算式輸入到其他的列中（非B、C、D列），這時(shí)你會(huì)看到“#VALUE!”的錯(cuò)誤數(shù)值結(jié)果，因?yàn)樵谄渌袝r(shí)，Excel無(wú)法判斷該取數(shù)組中的哪一個(gè)數(shù)值。

        如果將第7行中的算式外面加上SUM，你會(huì)發(fā)現(xiàn)結(jié)果仍然一樣，因?yàn)槟J(rèn)情況下，數(shù)組算式只取對(duì)應(yīng)序列的值，再加上SUM也只是對(duì)應(yīng)的值，如果使用數(shù)組公式，便是告訴Excel計(jì)算數(shù)組中所有數(shù)值的和，也就是單元格B5中的結(jié)果。

        三、數(shù)組公式應(yīng)用進(jìn)階

        數(shù)組公式最典型的應(yīng)用應(yīng)該是使用SUM替代SUMIF，雖然SUMIF很好用，但在Office 2007之前，也就是SUMIFS函數(shù)出現(xiàn)之前，如果想利用SUMIF進(jìn)行一次多重條件判斷的求和計(jì)算是很難實(shí)現(xiàn)的。

        為了更便于理解，這里再用上面的例子進(jìn)行一個(gè)比較簡(jiǎn)單的運(yùn)算，上面只是列出了三支股票，如果我同時(shí)購(gòu)買(mǎi)了多支股票，現(xiàn)在想知道這些股票當(dāng)中，股價(jià)小于5元的股票有幾支（這一功能可以使用Countif函數(shù)來(lái)實(shí)現(xiàn)，這里為了讓數(shù)組公式更便于理解，所以使用SUM、IF相結(jié)合的數(shù)組公式來(lái)實(shí)現(xiàn)。

        在B10單元格輸入公式“=SUM(IF(B3:D3<5,1))”，按鈕按下Ctrl+Shift+Enter組合鍵，使公式變成數(shù)組公式“{=SUM(IF(B3:D3<5,1))}”。

        從上圖中可以看到，計(jì)算結(jié)果為2，計(jì)算正確。如果對(duì)此懷疑，可以擴(kuò)大數(shù)據(jù)區(qū)域，從而更容易理解。

        下面說(shuō)一下公式的整個(gè)運(yùn)算過(guò)程。

        1.IF(B3:D3<5,1)，計(jì)算B3:D3區(qū)域內(nèi)數(shù)值小于5的個(gè)數(shù)，因?yàn)槭褂?span lang=EN-US>IF判斷，數(shù)值小于5時(shí)，取值1，所以該公式計(jì)算的結(jié)果是1，false，1，然后SUM進(jìn)行數(shù)組求和也就是1+false+1，所以結(jié)果為2。SUM在求值時(shí)會(huì)自動(dòng)忽略False，我們也可以把False直接當(dāng)作0來(lái)處理。或者將公式改成IF(B3:D3<5,1,0)，這樣計(jì)算的結(jié)果就是1，0，1了。

        如果要統(tǒng)計(jì)股價(jià)低于5的股價(jià)和要怎么辦呢？將上述公司修改成“{=SUM(IF(B3:D3<5,B3:D5))}”即可，因?yàn)閿?shù)組公式是一一對(duì)應(yīng)的，也就是說(shuō)IF條件滿足時(shí)就取對(duì)應(yīng)的數(shù)值，所以此公式的計(jì)算結(jié)果為4.04，false，4.43，最后的結(jié)果是4.04+false+4.43=8.47。

         不知道看完了上面這些，大家對(duì)數(shù)組公式是否有所了解了，更進(jìn)階的應(yīng)用，且聽(tīng)下回分解。

Excel數(shù)組公式從入門(mén)到精通之精通篇

        一、課程回憶

        什么是數(shù)組公式呢？顧名思義就是公式中包含數(shù)組的了，詳細(xì)含義請(qǐng)參看前文。但這里重點(diǎn)提醒的一點(diǎn)就是，如果要使用數(shù)組公式，在編輯欄輸入完公式以后一定要按下“Ctrl+Shift+Enter”組合鍵，使編輯欄的公式處在“{}”之中。

        二、數(shù)組公式繼續(xù)深入

        印象中是好幾年前了，當(dāng)時(shí)看過(guò)的一篇掃盲貼中，作者舉的例子真是太實(shí)用了。具體細(xì)節(jié)記不太清楚了，大致意思就是使用函數(shù)計(jì)算1到100的和。這里同樣以此為例。

        1.求1到100的和

        在往下看之前，大家想一下，如果讓你來(lái)處理該如何來(lái)處理呢？只用一個(gè)函數(shù)解決1到100的和，當(dāng)然也可以是1000、10000甚至更多。討論具體的數(shù)值沒(méi)有太大意義，此處只是希望通過(guò)此例讓大家更進(jìn)一步的了解數(shù)組公式的用法。

        解答：{=SUM(ROW(1:100))}

        問(wèn)題分析：求1到100的和，答案是5050（小學(xué)生都知道^-^），但Excel必須是你告訴了它正確的方法，它才能知道。計(jì)算從1到100的和，實(shí)際上就是計(jì)算1+2+3+4+……+98+99+100，好了，答案出來(lái)了，在編輯欄中輸入“＝sum(1+2+3+4+……+98+99+100)”。相信聰明的一定對(duì)此答案不滿意，雖然能得到正確的結(jié)果，但很明顯是“錯(cuò)誤”的方法。

        要得到1到100的正確數(shù)列，最簡(jiǎn)單的方法就是使用Row()或是Column()函數(shù)，由于個(gè)人習(xí)慣，我比較習(xí)慣于Row()，所以這里以Row()函數(shù)為例。

       熟悉Row()函數(shù)：在A1單元格中輸入“＝Row()”，使用填充柄填充至A5，看到什么結(jié)果？是不是每一個(gè)單元格中值就是其對(duì)應(yīng)的行數(shù)。

        驚喜：Row()表示單前行，如果使用Row(1:100)就表示一個(gè)數(shù)組，其中包含的便是第一到第一百行的行號(hào)，即1、2、3、……、98、99、100這些數(shù)值，現(xiàn)在我們就把這個(gè)數(shù)組應(yīng)用到公式中。

        在工作表的任意一個(gè)單元格中輸入“=sum(Row(1:100))”，然后按Ctrl+Shift+Enter組合鍵，你會(huì)驚喜的發(fā)現(xiàn)，我們要的結(jié)果出現(xiàn)了。

        2.在Excel2003中享受“SUMIFS”

如下圖所示，某教師有一張任教的幾個(gè)班級(jí)的學(xué)生成績(jī)表。

        任務(wù)：統(tǒng)計(jì)出“一班”、“二班”共計(jì)多少人？

        此題要如何解決？SUMIF用兩次？或是COUNT用兩次？這里還是演示數(shù)組公式的用法，所以先用SUM和IF組合的形式。

        在任一單元格中輸入“=SUM(IF((A2:A12="一班")+(A2:A12="二班"),1,0))”公式按下回車(chē)鍵，是不是發(fā)現(xiàn)結(jié)果是“#VALUE！”，再次進(jìn)入編輯欄然后按下“Ctrl+Shift+Enter”快捷鍵，是不是發(fā)現(xiàn)正確的結(jié)果出來(lái)了？

 

        這里再次解釋一下這個(gè)公式“{=SUM(IF((A2:A12="一班")+(A2:A12="二班"),1,0))}”，外側(cè)SUM沒(méi)什么好用的了，就是求（）內(nèi)各數(shù)的和。中間的“IF((A2:A12="一班")+(A2:A12="二班"),1,0))”的運(yùn)算過(guò)程是這樣的，判斷A2:A12區(qū)域內(nèi)單元格的值是否是“一班”，如果是則結(jié)果為1，則此公式計(jì)算的結(jié)果依次是“1、0、0、1、0、0、1、0、0、1、0”，因?yàn)榈谝粋€(gè)條件為真，第二個(gè)條件肯定就不為真了，因?yàn)橐粋€(gè)單元格不可能同時(shí)等于“一班”和“二班”，所以第一個(gè)數(shù)組就是“1、0、0、1、0、0、1、0、0、1、0”。這時(shí)再判斷A2:A12區(qū)域內(nèi)單元格的值是否是“二班”，如果是結(jié)果則為1，否則為0，所以這個(gè)數(shù)組條件計(jì)算的結(jié)果就是“0、1、0、0、0、1、0、1、0、0、0”，中間的加號(hào)就是將這兩個(gè)數(shù)組相加，也就是說(shuō)最終的數(shù)組為“1、1、0、1、0、1、1、1、0、1、0”，然后使用SUM求和，結(jié)果就為7了。

        從上面的圖中標(biāo)注可以看出，所以的公式我全部使用了數(shù)組（A2:A12這就是一個(gè)數(shù)組），并且上圖上的沒(méi)有使用數(shù)組公式的公式中的數(shù)組全部可以使用單個(gè)單元格替代，之所以全部列出，還是希望大家更好的理解一下數(shù)組。

        在Excel中，數(shù)組如果不放在數(shù)組公式中使用，通常數(shù)組在特定單元格中只代表與其特定單元格所對(duì)應(yīng)的一個(gè)值（數(shù)組中的一個(gè)元素），放在數(shù)組公式中使用時(shí)，通常整個(gè)數(shù)組元素都會(huì)參與運(yùn)算。

        三、數(shù)組公式精通

        這里是一個(gè)實(shí)際工作中的例子，只是我稍微變化了一下，還是SUM應(yīng)用的例子。

        需求：如下圖所示，現(xiàn)在要統(tǒng)計(jì)員工張三在1號(hào)加工所有機(jī)器的“實(shí)績(jī)”，也就是說(shuō)在右側(cè)的數(shù)據(jù)中先過(guò)濾日期為1，然后再過(guò)濾人員為張三的數(shù)據(jù)，最后統(tǒng)計(jì)實(shí)績(jī)的結(jié)果。如果使用一個(gè)公式完成這一需求，你能想到嗎？當(dāng)然SUMIFS是除外的，因?yàn)?span lang=EN-US>SUMIFS是Office 2007以后的產(chǎn)物。

        答案：在上圖所示的C2單元格中輸入“=SUM((E2:E21=A2)*(G2:G21=B2)*(H2:H21))”公式，然后按下“Ctrl+Shift+Enter”組合鍵，你會(huì)發(fā)現(xiàn)想要的結(jié)果已經(jīng)出現(xiàn)了。數(shù)組公式就是這么簡(jiǎn)單，解決問(wèn)題也是這么簡(jiǎn)單。

        這次用的公式可以看出，比之前用的公式還要簡(jiǎn)單，連IF都不要了，實(shí)際上這里的“＝”符號(hào)就是起到了一個(gè)類(lèi)似IF的效果。

        這里再說(shuō)明一下公式的執(zhí)行過(guò)程，公式中E2:E21表示數(shù)組區(qū)域，這個(gè)相信已經(jīng)不需要再說(shuō)明了，放到數(shù)組公式中就是依次取數(shù)組中的各個(gè)數(shù)值，也就是依次取日期中的值。E2:E21=A2，實(shí)際上就是拿日期中的每一個(gè)值依次與A2中的日期進(jìn)行比對(duì)，如果相等則結(jié)果為True，即1，如果不相等則為False即為0。到了這里也許你有一點(diǎn)明白了，如果第一不相等，則后面的無(wú)需再繼續(xù)下去了，因?yàn)楣嚼镉玫娜渴?#8220;*”乘積符號(hào)，任何數(shù)乘0等于0。如果此項(xiàng)符合再繼續(xù)判斷G2:G21區(qū)域，也就是用姓名依次比對(duì)，如果和B2中的姓名相同，則為Ture，即1，如果為False，即0，繼續(xù)下一個(gè)回合。如果此項(xiàng)也為Ture，很明顯前面兩項(xiàng)的結(jié)果為1*1＝1，再乘以H2:H21數(shù)組中對(duì)應(yīng)的數(shù)字，即符合條件的“實(shí)績(jī)”，以第一個(gè)符合條件的第一條記錄為例，在數(shù)組公式運(yùn)行的第一個(gè)回合為SUM(1*1*234)，結(jié)果當(dāng)然為234了，然后再依次完成整個(gè)數(shù)組的運(yùn)算，我們最終的目的就達(dá)到了。

        數(shù)組公式非常有用，效率也高，但真正的理解、熟練掌握也不是一件很容易的事。但大家記住數(shù)組中的數(shù)據(jù)是一一對(duì)應(yīng)的，放到數(shù)組公式中使用時(shí)，數(shù)組中的數(shù)據(jù)會(huì)按順序依次參與相應(yīng)的運(yùn)算。

        希望大家能夠慢慢的理解、貫通。