Excel數組公式應用徹底醒悟
Excel數組公式應用徹底醒悟
Excel中函數、公式大家都非常熟了，但「數組公式」你是否聽過、用過？什麼是數組公式呢？根據微軟官方給出的解釋，數組公式：數組公式對一組或多組值執行多重計算，並返回一個或多個結果。
一、什麼是數組公式？
直接看微軟的解釋，也許並不是很容易理解，根據個人的理解，讓其更直白一點，可以這樣簡單理解，引用了數組（可以是一個或多個數值，或是一組或多組數值），並在編輯欄可以看到以「{}」括起來的公式就是數組公式。而數組公式的作用就是對一組（單個數據可以看成是一組）、多組數據進行處理，然後得到想要的結果。
二、如何輸入數組公式
既然數組公式是以「{}」括起來的，那是不是在編輯欄在公式的兩端分別輸入「{}」就可以了呢？答案是否定的，在Excel中要輸入數組公式，必須以特定的方法來輸入，算是告訴Excel，我們這裡輸入的是數組公式。
在某個單元格輸入數組公式的方法如下：
1.在編輯欄輸入完整的公式，並使編輯欄仍處在編輯狀態；
2.按下Ctrl+Shift+Enter快捷鍵
經過以上兩步操作以後，編輯欄會自動脫離編輯狀態，並且選中單元格後，在編輯欄可以看到公式的兩端有「{}」符號標記，而雙擊進入公式的編輯狀態時，你會發現「{}」符號是不存在的。
三、數組公式有什麼用？
這裡做了一個類似微軟官網上的例子，這裡詳細說明一下用法及好處。
以上面圖片中的內容為例，假設我一共買了三支股票，其股份及買入價格分別如圖中所示，現在我要計算我的總股本。
上面的計算過程可以說一點問題沒有，也絕對正確。但是試想一下，如果類似的數據有很多，如果不是計算我自己買的幾支股票而是其他類似情況的數據處理的時候，採用上面的方法時，其工作量可想而知了。
我們再回到第一幅圖中的公式「{=SUM(B2:D2*B3*D3}」，此公式便是一個典型的數組公式的應用，此公式的作用就是計算B2*B3、C2*C3以及D2*D3的和。而B2:D2*B3*D3便是一個數組，其中包含三個元素，各元素的值就分別是各項的乘積。
從上面的圖中可以看到，第4行和第7行的計算結果是一樣的，這就是數組的效果，數組會根據當前單元格所在位置自動取數組中對應序列的數值，如果將「=B2:D2*B3:D
如果將第7行中的算式外面加上SUM，你會發現結果仍然一樣，因為默認情況下，數組算式只取對應序列的值，再加上SUM也只是對應的值，如果使用數組公式，便是告訴Excel計算數組中所有數值的和，也就是單元格B5中的結果。
三、數組公式應用進階
數組公式最典型的應用應該是使用SUM替代SUMIF，雖然SUMIF很好用，但在Office 2007之前，也就是SUMIFS函數出現之前，如果想利用SUMIF進行一次多重條件判斷的求和計算是很難實現的。
為了更便於理解，這裡再用上面的例子進行一個比較簡單的運算，上面只是列出了三支股票，如果我同時購買了多支股票，現在想知道這些股票當中，股價小於5元的股票有幾支（這一功能可以使用Countif函數來實現，這裡為了讓數組公式更便於理解，所以使用SUM、IF相結合的數組公式來實現。
在B10單元格輸入公式「=SUM(IF(B3:D3<5,1))」，按鈕按下Ctrl+Shift+Enter組合鍵，使公式變成數組公式「{=SUM(IF(B3:D3<5,1))}」。
下面說一下公式的整個運算過程。
1.IF(B3:D3<5,1)，計算B3:D3區域內數值小於5的個數，因為使用IF判斷，數值小於5時，取值1，所以該公式計算的結果是1，false，1，然後SUM進行數組求和也就是1+false+1，所以結果為2。SUM在求值時會自動忽略False，我們也可以把False直接當作0來處理。或者將公式改成IF(B3:D3<5,1,0)，這樣計算的結果就是1，0，1了。
不知道看完了上面這些，大家對數組公式是否有所了解了，更進階的應用，且聽下回分解。
Excel數組公式從入門到精通之精通篇
一、課程回憶
什麼是數組公式呢？顧名思義就是公式中包含數組的了，詳細含義請參看前文。但這裡重點提醒的一點就是，如果要使用數組公式，在編輯欄輸入完公式以後一定要按下「Ctrl+Shift+Enter」組合鍵，使編輯欄的公式處在「{}」之中。
二、數組公式繼續深入
印象中是好幾年前了，當時看過的一篇掃盲貼中，作者舉的例子真是太實用了。具體細節記不太清楚了，大致意思就是使用函數計算1到100的和。這裡同樣以此為例。
1.求1到100的和
在往下看之前，大家想一下，如果讓你來處理該如何來處理呢？只用一個函數解決1到100的和，當然也可以是1000、10000甚至更多。討論具體的數值沒有太大意義，此處只是希望通過此例讓大家更進一步的了解數組公式的用法。
解答：{=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)」。相信聰明的一定對此答案不滿意，雖然能得到正確的結果，但很明顯是「錯誤」的方法。
要得到1到100的正確數列，最簡單的方法就是使用Row()或是Column()函數，由於個人習慣，我比較習慣於Row()，所以這裡以Row()函數為例。
熟悉Row()函數：在A1單元格中輸入「＝Row()」，使用填充柄填充至A5，看到什麼結果？是不是每一個單元格中值就是其對應的行數。
驚喜：Row()表示單前行，如果使用Row(1:100)就表示一個數組，其中包含的便是第一到第一百行的行號，即1、2、3、……、98、99、100這些數值，現在我們就把這個數組應用到公式中。
在工作表的任意一個單元格中輸入「=sum(Row(1:100))」，然後按Ctrl+Shift+Enter組合鍵，你會驚喜的發現，我們要的結果出現了。
任務：統計出「一班」、「二班」共計多少人？
此題要如何解決？SUMIF用兩次？或是COUNT用兩次？這裡還是演示數組公式的用法，所以先用SUM和IF組合的形式。
這裡再次解釋一下這個公式「{=SUM(IF((A2:A12="一班")+(A2:A12="二班"),1,0))}」，外側SUM沒什麼好用的了，就是求（）內各數的和。中間的「IF((A2:A12="一班")+(A2:A12="二班"),1,0))」的運算過程是這樣的，判斷A2:A12區域內單元格的值是否是「一班」，如果是則結果為1，則此公式計算的結果依次是「1、0、0、1、0、0、1、0、0、1、
從上面的圖中標註可以看出，所以的公式我全部使用了數組（A2:A12這就是一個數組），並且上圖上的沒有使用數組公式的公式中的數組全部可以使用單個單元格替代，之所以全部列出，還是希望大家更好的理解一下數組。
在Excel中，數組如果不放在數組公式中使用，通常數組在特定單元格中只代表與其特定單元格所對應的一個值（數組中的一個元素），放在數組公式中使用時，通常整個數組元素都會參與運算。
三、數組公式精通
這裡是一個實際工作中的例子，只是我稍微變化了一下，還是SUM應用的例子。
需求：如下圖所示，現在要統計員工張三在1號加工所有機器的「實績」，也就是說在右側的數據中先過濾日期為1，然後再過濾人員為張三的數據，最後統計實績的結果。如果使用一個公式完成這一需求，你能想到嗎？當然SUMIFS是除外的，因為SUMIFS是Office 2007以後的產物。
這次用的公式可以看出，比之前用的公式還要簡單，連IF都不要了，實際上這裡的「＝」符號就是起到了一個類似IF的效果。
這裡再說明一下公式的執行過程，公式中E2:E21表示數組區域，這個相信已經不需要再說明了，放到數組公式中就是依次取數組中的各個數值，也就是依次取日期中的值。E2:E21=A2，實際上就是拿日期中的每一個值依次與A2中的日期進行比對，如果相等則結果為True，即1，如果不相等則為False即為0。到了這裡也許你有一點明白了，如果第一不相等，則後面的無需再繼續下去了，因為公式里用的全部是「*」乘積符號，任何數乘0等於0。如果此項符合再繼續判斷G2:G21區域，也就是用姓名依次比對，如果和B2中的姓名相同，則為Ture，即1，如果為False，即0，繼續下一個回合。如果此項也為Ture，很明顯前面兩項的結果為1*1＝1，再乘以H2:H21數組中對應的數字，即符合條件的「實績」，以第一個符合條件的第一條記錄為例，在數組公式運行的第一個回合為SUM(1*1*234)，結果當然為234了，然後再依次完成整個數組的運算，我們最終的目的就達到了。
數組公式非常有用，效率也高，但真正的理解、熟練掌握也不是一件很容易的事。但大家記住數組中的數據是一一對應的，放到數組公式中使用時，數組中的數據會按順序依次參與相應的運算。
希望大家能夠慢慢的理解、貫通。
推薦閱讀：
※六大財務公式——投資項目財務可行性分析
※如何製作圖表非常精美的 Excel 文檔？
※這才是Excel函數中的神器，名副其實的萬金油公式！
※有哪些可以初等表述的數學結論只能由高深的方法證明?
※數學上有哪些醜陋的公式？