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

Excel中函數(shù)、公式大家都非常熟了,，但“數(shù)組公式”你是否聽過,、用過？什么是數(shù)組公式呢,？根據(jù)微軟官方給出的解釋,，數(shù)組公式：數(shù)組公式對一組或多組值執(zhí)行多重計算，并返回一個或多個結(jié)果,。

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

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

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

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

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

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

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

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

        

 

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

        這里做了一個類似微軟官網(wǎng)上的例子，這里詳細說明一下用法及好處,。

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

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

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

        我們再回到第一幅圖中的公式“{=SUM(B2:D2*B3*D3}”,，此公式便是一個典型的數(shù)組公式的應(yīng)用，此公式的作用就是計算B2*B3,、C2*C3以及D2*D3的和,。而B2:D2*B3*D3便是一個數(shù)組，其中包含三個元素,，各元素的值就分別是各項的乘積,。

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

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

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

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

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

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

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

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

        下面說一下公式的整個運算過程。

        1.IF(B3:D3<5,1),，計算B3:D3區(qū)域內(nèi)數(shù)值小于5的個數(shù)，因為使用IF判斷,，數(shù)值小于5時,，取值1，所以該公式計算的結(jié)果是1,，false,，1,，然后SUM進行數(shù)組求和也就是1+false+1，所以結(jié)果為2,。SUM在求值時會自動忽略False,，我們也可以把False直接當作0來處理?；蛘邔⒐礁某?span lang=EN-US>IF(B3:D3<5,1,0),，這樣計算的結(jié)果就是1，0,，1了,。

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

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

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

        一、課程回憶

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

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

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

        1.求1到100的和

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

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

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

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

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

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

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

        2.在Excel2003中享受“SUMIFS”

如下圖所示,，某教師有一張任教的幾個班級的學生成績表,。

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

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

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

 

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

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

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

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

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

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

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

        這次用的公式可以看出,，比之前用的公式還要簡單，連IF都不要了,，實際上這里的“＝”符號就是起到了一個類似IF的效果,。

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

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

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