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

1、Excel數(shù)組公式從入門到精通之入門篇Excel中函數(shù)、公式大家都非常熟了，但數(shù)組公式”你是否聽過、用過？什么是數(shù)組公式呢？根據(jù)微軟官方給出的解釋，數(shù)組公式:數(shù)組公式對一組或多組值執(zhí)行多重計算，并返回一個或多個結(jié)果。一、什么是數(shù)組公式？直接看微軟的解釋，也許并不是很容易理解，根據(jù)個人的理解，讓其更直白一點， 可以這樣簡單理解，引用了數(shù)組（可以是一個或多個數(shù)值，或是一組或多組數(shù)值），并在編輯欄可以看到以“ ”括起來的公式就是數(shù)組公式。而數(shù)組公式的作用就是對一組（單個數(shù)據(jù)可以看成是一組）、多組數(shù)據(jù)進行處理，然后得到想要的結(jié)果。二、如何輸入數(shù)組公式既然數(shù)組公式是以“括起來的，那是不是在編輯欄在公式的

2、兩端分別輸入“ ”就可以了呢？答案是否定的，在 Excel中要輸入數(shù)組公式，必須以特定的方法來輸入，算 是告訴Excel ,我們這里輸入的是數(shù)組公式。在某個單元格輸入數(shù)組公式的方法如下：.在編輯欄輸入完整的公式，并使編輯欄仍處在編輯狀態(tài)；.按下 Ctrl+Shift+Enter 快捷鍵經(jīng)過以上兩步操作以后，編輯欄會自動脫離編輯狀態(tài)，并且選中單元格后，在編輯 欄可以看到公式的兩端有“ ”符號標記，而雙擊進入公式的編輯狀態(tài)時，你會發(fā)現(xiàn)“ ”符號是不存在的。三、數(shù)組公式有什么用？這里做了一個類似微軟官網(wǎng)上的例子，這里詳細說明一下用法及好處。以上面圖片中的內(nèi)容為例，假設(shè)我一共買了三支股票，其股份及買入

3、價格分別如圖 中所示，現(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 的

4、和。而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歹U）,這時你會看到 “#VALUE!的錯誤數(shù)值結(jié)果，因為在其他列時，Excel無法判斷該取數(shù)組中的哪一個數(shù)值。如果將第7行

5、中的算式外面加上 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元的股票有幾支(這一

6、功能可以使用Countif函數(shù)來實現(xiàn)，這里為了讓數(shù)組公式更便于理解，所以使用SUM、IF相結(jié)合的數(shù)組公式來實現(xiàn)。在 B10 單元格輸入公式“ =SUM(IF(B3:D35,1):按鈕按下 Ctrl+Shift+Enter組合鍵，使公式變成數(shù)組公式“二SUM(IF(B3:D35,1):從上圖中可以看到，計算結(jié)果為2,計算正確。如果對此懷疑，可以擴大數(shù)據(jù)區(qū)域，從而更容易理解。下面說一下公式的整個運算過程。F(B3:D35,1) ，計算B3:D3 區(qū)域內(nèi)數(shù)值小于 5的個數(shù)，因為使用 IF判斷， 數(shù)值小于5時，取值1 ,所以該公式計算的結(jié)果是1 , false , 1 ,然后SUM進行數(shù)組求和也就是1

7、+false+1 ,所以結(jié)果為 2。SUM在求值時會自動忽略False ,我們也可以把False直接當作0來處理?；蛘邔⒐礁某蒊F(B3:D35,1,0)，這樣計算的結(jié)果就是1,0, 1 了。如果要統(tǒng)計股價低于5的股價和要怎么辦呢？將上述公司修改成“ =SUM(IF(B3:D35,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ù)組公式從入門

8、到精通之入門篇，不覺已十多天過去了，今天補上精通篇”。當然說 精通”可能有點過了，但是希望大家通過這兩篇博文能夠真正認識數(shù)組公式”，并且在工作中使用數(shù)組公式幫我們解決實際問題。Excel數(shù)組公式從入門到精通之精通篇一、課程回憶什么是數(shù)組公式呢？顧名思義就是公式中包含數(shù)組的了，詳細含義請參看前文。但這里重點提醒的一點就是，如果要使用數(shù)組公式，在編輯欄輸入完公式以后一定要按下“Ctrl+Shift+Enter ”組合鍵，使編輯欄的公式處在“ 之中。二、數(shù)組公式繼續(xù)深入印象中是好幾年前了，當時看過的一篇掃盲貼中，作者舉的例子真是太實用了。具體細節(jié)記不太清楚了，大致意思就是使用函數(shù)計算1到100的和。

9、這里同樣以此為例。.求1至ij 100 的和在往下看之前，大家想一下，如果讓你來處理該如何來處理呢？只用一個函數(shù)解決 1到1 00的和，當然也可以是1000、10000甚至更多。討論具體的數(shù)值沒有太大意義，此處只是希望通過此例讓大家更進一步的了解數(shù)組公式的用法。解答：=SUM(ROW(1:100)問題分析：求1到100的和，答案是 5050 (小學(xué)生都知道A-A ),但Excel必須是你 告訴了它正確的方法，它才能知道。計算從 1到100的和，實際上就是計算1+2+3+4+98+99+100,好了，答案出來了，在編輯欄中輸入=sum(1+2+3+4+ +98+99+100)相信聰明的一定對此答

10、案不滿意，雖然能得到正確的結(jié)果，但很明顯是錯誤”的方法。要得到1到100的正確數(shù)列，最簡單的方法就是使用Row()或是Column。 函數(shù)，由于個人習(xí)慣，我比較習(xí)慣于 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:

11、100) 然后按Ctrl+Shift+Enter組合鍵，你會驚喜的發(fā)現(xiàn)，我們要的結(jié)果出現(xiàn)了。.在 Excel2003 中享受 “ SUMIFS ”SUMIF函數(shù)應(yīng)該很多人都用過，非常好用。但如果遇到多條件判斷的怎么辦呢？從Office 2007開始，弓|入了 SUMIFS函數(shù)，可以解決這種多條件求和問題。但如果仍然使用Office 2003怎么辦呢？其實使用 SUM、IF再結(jié)合數(shù)組公式即可實現(xiàn) SUMIFS的效果。如下圖所示，某教師有一張任教的幾個班級的學(xué)生成績表。8.E .班一二一三一三二一二.三 一JE1234LH6TE91 01 11 2名三四五六七八朝汗忘虎昭姓張李王就錢超王馬張越展績

12、鹿優(yōu)良優(yōu)良良優(yōu)優(yōu)良優(yōu)良世13任務(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ù)的和。中間的 “

13、IF(A2:A12= 一班)+ (A2:A12= 二班),1,0)的運算過程是這卞W勺，判斷 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é)果就是1、0、0、0、1、0、1、0、0、0”，中間的加號就是將這兩個數(shù)組相加，也就是說最終的 數(shù)組為“1、

14、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)計員

15、工張三在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都不要了，

16、實際上這里的符號就是起到了一個類似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