數組公式從入門到精通

入門篇本主題包含三部分：入門篇、提高篇、應用篇（分中級和高級）對於剛接觸Excel數組公式的人來說，總是會感覺到它的一份神秘。又Excel的Online Help中只有很少關於它的主題，所以這種神秘感就更強了。不要緊，只要跟著我的思路走，你很快就會看清數組公式的真面目！數組概念對於數組概念，大家都會很熟悉，其就是一個具有維度的集合。比如：一維數組、二維數組、多維數組。數組的表示一般為「{}」所包括（一維和二維數組）。Excel中也不例外，如果你想直接表示一個數組，也必須用「{}」括起來。數組與數組公式在Excel中，凡是以半形符號「=」開始的單元格內容都被Excel認為是公式，其只能返回一個結果。而數組公式可以返回一個或者是多個結果，而返回的結果又可以是一維或二維的，換句話說，Excel中的數組公式返回的是一個一維或二維的數組集合。在Excel中需要按下 「Ctrl+Shift+Enter」組合鍵結束數組公式的輸入。為什麼要用數組公式？如果你的需要滿足以下條件之一，那麼採用數組公式技術可能會是你很好的選擇方案。·     你的運算結果會返回一個集合嗎？·     你是否希望用戶不會有意或無意的破壞某一相關公式集合的完整性？·    你的運算中是否存在著一些只有通過複雜的中間運算過程才會等到結果的運算？看到這些另人費解的問題，你可能會摸不著頭緒。不要緊，看了以下內容你也許就會明白了。什麼情況下會返回一個集合？看一個簡單的例子，選中C1:E3，輸入「={"Name", "Sex", "Age"; "John", "Male", 21; "Mary", "Female", 20}」，按「Ctrl+Enter」組合鍵。

圖1-1 (ArrayFormula_A01.bmp)結果在C1:E3中看到的結果全是「Name」，而實際真正返回的結果應該是一個包含三行三列的二維數組，如何辦？答案就是用數組公式。選中C1:E3，輸入「={"Name", "Sex", "Age"; "John", "Male", 21; "Mary", "Female", 20}」，按「Ctrl+Shift+Enter」組合鍵。

圖1-2 (ArrayFormula_A02.bmp)可能你又會問，這有何用？為何不在單元格中直接輸入內容，反而要這麼麻煩？這僅僅是一個例子，說明的是如何通過數組公式返回一個結果集。給你個問題，如果存在這樣一個工作表：包含欄位{"ID", "Name", "Sex", "Age"}，如何將「Sex」為「Female」的記錄抽取出來 (為了列印報表，抽取的記錄需要連續存放) ？這個問題將在「應用篇」里進行解答。什麼情況下會用到相關公式完整性？什麼是相關公式完整性？這僅僅是我給出的一個定義，請再回到「圖1-2」，請選擇C1:E3中任意一單元格，然後做隨意的修改(哪怕和原先的公式一樣)，按「Enter」鍵結束輸入。結果如何？修改未成功！提示「不能更改數組的某一部分」。

圖1-3 (ArrayFormula_A03.bmp)為什麼會是這樣呢？因為你正企圖破壞相關公式的完整性。由於C1:E3中公式的數據源均為「{"Name", "Sex", "Age"; "John", "Male", 21; "Mary", "Female", 20}」，而C1:E3共用的一個公式(這與每個單元格都有相同的公式是有區別的，因為這僅僅是C1:E3擁有9個相同的公式，而不是一個!)，因此，當你要單獨更改其中一個單元格時，系統會認為你正在更改部分單元格的數據源，如此會導致數據源不一致的現象，從而導致與其它相關單元格脫離關係，這樣數組公式就失去作用，所以系統不又允許你更改數組公式的部分內容。這樣的好處是可以維護數據的完整性，做到與數據源總是有一致的對應關係。你的公式複雜嗎？如果有如下數據，在D6單元格中求出對所購物品需要付多少費用。你會如何做？在D6中輸入「=(C2*D2+C3*D3+C4*D4)」？結果正確，如果中間某個單元格地址輸入錯誤你的結果會正確嗎？如果記錄不只3條，而是成千上萬條，你是否會感覺到力不從心(如果不考慮單元格內字元數的限制)？如果用「圖1-5」中的方法，你的感覺又會如何？（在D6中輸入「=SUM(C2:C4*D2:D4)」，按「Ctrl+Shift+Enter」鍵結束輸入。其中涉及到的技巧會在「提高篇」中討論。）

圖1-4 (ArrayFormula_A04.bmp)

圖1-5 (ArrayFormula_A05.bmp)怎麼樣？是否了解了數組公式？是否學會了如何使用數組公式？是否感覺到了它的一點點威力？請繼續關注「數組公式從入門到精通」之「提高篇」，讓我們繼續深入數組公式！數組公式從入門到精通提高篇本主題包含三部分：入門篇、提高篇、應用篇（分中級和高級）相信你在「入門篇」中已經學會了如何建立數組公式，同時也大致了解在什麼情況下適合使用數組公式解決問題。需要說明的是，在「入門篇」中提到的使用數組公式的三種情況並不是絕對的，要視具體情況而定。在接下來的討論中，你將會了解數組公式的一些工作原理。在進行正式討論之前，先跟著我做一些準備工作。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的效果。技術說明：1)    Excel中的IF()工作表函數對條件真假的判斷是這樣，當條件的值為0時，認為是假；否則，全部認為是真。條件的數據類型一定是數值。比如「=IF(-3,1,0)」返回1。因此「+」的操作做到了模擬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()效果都相同，要視具體情況而定，這就需要你靈活掌握了。引用大小制約此主題並非重要，不過為了使你更加深入數組公式，還是在這裡介紹一下。這裡的引用大小制約指的是數組公式中各相關引用之間的大小制約或引用大小對結果集大小的制約。1)主關鍵區域決定數組函數返回值的大小（關鍵區域是指決定數組公式返回結果集大小的區域）看「圖2-7」中的公式「=IF(C2:C3="Mary",ROW(D2:D3))」，這裡的主關鍵區域為C2:C3，那麼該公式的結果集大小為2（即有兩個元素）。2)有互依賴關係的引用之間大小要一致互依賴就是共同決定某個結果。看「圖2-9」的D12「=SUM(IF((C2:C7=D9)*(D2:D7=D10),E2:E7))」，其中C2:C7與D2:D7就是互依賴的引用，它們共同決定IF()函數第一個參數的值，所以它們的大小必須一致，否則返回錯誤值。對於沒有互依賴關係的引用大小有無限制？這就需要視具體情況而定了，再看「圖2-7」中的公式「=IF(C2:C3="Mary",ROW(D2:D3))」，其中的兩個引用之間就沒有大小的限制；而公式「=MIN(IF(C2:C3="Mary",ROW(D2:D3)))」就不一樣了，其中第一個引用的大小必須大於第二個引用的大小。到現在為止，你可以說已經掌握了Excel中的數組公式的工作原理以及一些中級技巧。學到現在你已經足以可以應付一些工作中的需要了，當然前提是你真正已經掌握了這些技術。不知道你現在的感覺如何？可能你還有一頭霧水的感覺，這是正常的，Excel中的數組公式確實不是很好理解。不要緊！只要多做練習，將實踐與理論結合，很快你就會體會到它的強大威力！為了使你的工作效率更高，我又寫了「應用篇」，內容主要是平時能夠遇到問題的一些解決方案（包括自己心得和網友一些問題的解答）。因此請您繼續關注「數組公式從入門到精通」之「應用篇」，讓我們展示數組公式的威力！
推薦閱讀：