入門篇
本主題包含三部分：入門篇、提高篇、應用篇（分中級和高級）
對于剛接觸Excel數(shù)組公式的人來說，總是會感覺到它的一份神秘。又Excel的Online Help中只有很少關于它的主題，所以這種神秘感就更強了。不要緊，只要跟著我的思路走，你很快就會看清數(shù)組公式的真面目！
數(shù)組概念
對于數(shù)組概念，大家都會很熟悉，其就是一個具有維度的集合。比如：一維數(shù)組、二維數(shù)組、多維數(shù)組。數(shù)組的表示一般為“{}”所包括（一維和二維數(shù)組）。Excel中也不例外，如果你想直接表示一個數(shù)組，也必須用“{}”括起來。
數(shù)組與數(shù)組公式
在Excel中，凡是以半角符號“=”開始的單元格內容都被Excel認為是公式，其只能返回一個結果。而數(shù)組公式可以返回一個或者是多個結果，而返回的結果又可以是一維或二維的，換句話說，Excel中的數(shù)組公式返回的是一個一維或二維的數(shù)組集合。
在Excel中需要按下 “Ctrl+Shift+Enter”組合鍵結束數(shù)組公式的輸入。
為什么要用數(shù)組公式？
如果你的需要滿足以下條件之一，那么采用數(shù)組公式技術可能會是你很好的選擇方案。
· 你的運算結果會返回一個集合嗎？
· 你是否希望用戶不會有意或無意的破壞某一相關公式集合的完整性？
· 你的運算中是否存在著一些只有通過復雜的中間運算過程才會等到結果的運算？
看到這些另人費解的問題，你可能會摸不著頭緒。不要緊，看了以下內容你也許就會明白了。
什么情況下會返回一個集合？
看一個簡單的例子，選中C1:E3，輸入“={"Name", "Sex", "Age"; "John", "Male", 21; "Mary", "Female", 20}”，按“Ctrl+Enter”組合鍵。
圖1-1 (ArrayFormula_A01.bmp)
結果在C1:E3中看到的結果全是“Name”，而實際真正返回的結果應該是一個包含三行三列的二維數(shù)組，如何辦？答案就是用數(shù)組公式。選中C1:E3，輸入“={"Name", "Sex", "Age"; "John", "Male", 21; "Mary", "Female", 20}”，按“Ctrl+Shift+Enter”組合鍵。
圖1-2 (ArrayFormula_A02.bmp)
可能你又會問，這有何用？為何不在單元格中直接輸入內容，反而要這么麻煩？
這僅僅是一個例子，說明的是如何通過數(shù)組公式返回一個結果集。給你個問題，如果存在這樣一個工作表：包含字段{"ID", "Name", "Sex", "Age"}，如何將“Sex”為“Female”的記錄抽取出來 (為了打印報表，抽取的記錄需要連續(xù)存放) ？這個問題將在“應用篇”里進行解答。
什么情況下會用到相關公式完整性？
什么是相關公式完整性？這僅僅是我給出的一個定義，請再回到“圖1-2”，請選擇C1:E3中任意一單元格，然后做隨意的修改(哪怕和原先的公式一樣)，按“Enter”鍵結束輸入。結果如何？修改未成功！提示“不能更改數(shù)組的某一部分”。
圖1-3 (ArrayFormula_A03.bmp)
為什么會是這樣呢？因為你正企圖破壞相關公式的完整性。由于C1:E3中公式的數(shù)據(jù)源均為“{"Name", "Sex", "Age"; "John", "Male", 21; "Mary", "Female", 20}”，而C1:E3共用的一個公式(這與每個單元格都有相同的公式是有區(qū)別的，因為這僅僅是C1:E3擁有9個相同的公式，而不是一個!)，因此，當你要單獨更改其中一個單元格時，系統(tǒng)會認為你正在更改部分單元格的數(shù)據(jù)源，如此會導致數(shù)據(jù)源不一致的現(xiàn)象，從而導致與其它相關單元格脫離關系，這樣數(shù)組公式就失去作用，所以系統(tǒng)不又允許你更改數(shù)組公式的部分內容。這樣的好處是可以維護數(shù)據(jù)的完整性，做到與數(shù)據(jù)源總是有一致的對應關系。
你的公式復雜嗎？
如果有如下數(shù)據(jù)，在D6單元格中求出對所購物品需要付多少費用。你會如何做？在D6中輸入“=(C2*D2+C3*D3+C4*D4)”？結果正確，如果中間某個單元格地址輸入錯誤你的結果會正確嗎？如果記錄不只3條，而是成千上萬條，你是否會感覺到力不從心(如果不考慮單元格內字符數(shù)的限制)？如果用“圖1-5”中的方法，你的感覺又會如何？（在D6中輸入“=SUM(C2:C4*D2:D4)”，按“Ctrl+Shift+Enter”鍵結束輸入。其中涉及到的技巧會在“提高篇”中討論。）
圖1-4 (ArrayFormula_A04.bmp)
圖1-5 (ArrayFormula_A05.bmp)
怎么樣？是否了解了數(shù)組公式？是否學會了如何使用數(shù)組公式？是否感覺到了它的一點點威力？
請繼續(xù)關注“數(shù)組公式從入門到精通”之“提高篇”，讓我們繼續(xù)深入數(shù)組公式！
數(shù)組公式從入門到精通
提高篇
本主題包含三部分：入門篇、提高篇、應用篇（分中級和高級）
相信你在“入門篇”中已經(jīng)學會了如何建立數(shù)組公式，同時也大致了解在什么情況下適合使用數(shù)組公式解決問題。需要說明的是，在“入門篇”中提到的使用數(shù)組公式的三種情況并不是絕對的，要視具體情況而定。
在接下來的討論中，你將會了解數(shù)組公式的一些工作原理。
在進行正式討論之前，先跟著我做一些準備工作。
Excel的主要功能就是數(shù)據(jù)的分析和處理，我們現(xiàn)在只關心的是數(shù)據(jù)處理中的數(shù)據(jù)抽取。所謂數(shù)據(jù)抽取就是對源數(shù)據(jù)按照一定的條件篩選后所得到的結果。如何定制條件篩選呢？方法很多，這里介紹“IF()”函數(shù)和模擬AND、OR的原理和用法。
模擬AND、OR
讓我們先來看看為什么要模擬AND、OR，而不用Excel的工作表函數(shù)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)
之所以創(chuàng)建以上公式，是因為我想對滿足“Product ID”為D9，“City”為D10的記錄進行匯總，很明顯，從上面的返回結果表明D11中的結果是正確的，而D10中的結果是錯誤的。為什么會是這樣呢？
在接下來的演示中通過講述AND()和OR()函數(shù)的工作原理來解釋為什么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”中的結果。
為了節(jié)省篇幅，我直接把答案告訴你，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()函數(shù)類似。不信，你可以更改數(shù)據(jù)表中的一些數(shù)據(jù)來進行驗證。
現(xiàn)在你該知道D10返回錯誤值的原因了吧？那為什么D11能夠返回正確的結果？這正是我們要解決AND()和OR()函數(shù)在數(shù)組公式中存在問題的出發(fā)點。先看看下面這個說法：“*”相當于AND，“+”相當于OR。這是一些論壇中常見的回答，我到如今為止也這樣解答了不少朋友的疑問。結論正確么？難道Excel中的“*”和“+”有兩層含義？――嚴格的說，這是不正確的！因此，我已經(jīng)誤導了很多朋友，如果你曾經(jīng)在某論壇中得到過我這樣的解答，我在這里說聲抱歉！為什么“*”和“+”可以模擬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()工作表函數(shù)對條件真假的判斷是這樣，當條件的值為0時，認為是假；否則，全部認為是真。條件的數(shù)據(jù)類型一定是數(shù)值。比如“=IF(-3,1,0)”返回1。因此“+”的操作做到了模擬OR的效果。
理解IF()
IF()還用理解？Excel Online Help中不是已經(jīng)表達的很清楚了嗎？也許你會這樣問。
我并非是想文字充數(shù)，請看下圖：
圖2-6 (ArrayFormula_B06.bmp)
C5中的公式為“=IF(C2:C3="Mary",ROW(D2:D3))”（為數(shù)組公式），你知道它的值為什么是FALSE而不是三么？
聰明的你可能已經(jīng)想到這種類型的數(shù)組公式返回的是一個結果集，這個結果集的大小與操作對象的大小是一致的，在這里操作對象為C2:C3和D2:D3，因此返回值為兩個元素。
就是這樣，由于C2=”John”，不滿足條件，因此應該返回IF()函數(shù)的第三個參數(shù)值，但這里無第三個參數(shù)，所以系統(tǒng)返回FALSE；由于C3=”Mary”，滿足條件，因此返回第二個參數(shù)值，即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。我們已經(jīng)知道“圖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聰明之處！為什么說聰明呢？因為在絕大部分情況下我們想要的結果是滿足條件的部分，而舍棄非滿足條件的部分。這對篩選數(shù)據(jù)非常有幫助！如果你堅持要將非滿足條件的部分包含進來，最簡單的方法可以將公式變形為“=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()！由于我們已經(jīng)揭開了TRUE和FALSE的面紗，因此不難理解，對于“*”操作，只有TRUE*TRUE才會返回1，所以結果相當于“=SUM(0*12.34, 0*13.34, 1*30, 0*29, 0*103.05, 0*113.05)”，當然結果為30了。
注意：并非所有情況下“*”與IF()效果都相同，要視具體情況而定，這就需要你靈活掌握了。
引用大小制約
此主題并非重要，不過為了使你更加深入數(shù)組公式，還是在這里介紹一下。
這里的引用大小制約指的是數(shù)組公式中各相關引用之間的大小制約或引用大小對結果集大小的制約。
1)主關鍵區(qū)域決定數(shù)組函數(shù)返回值的大?。P鍵區(qū)域是指決定數(shù)組公式返回結果集大小的區(qū)域）
看“圖2-7”中的公式“=IF(C2:C3="Mary",ROW(D2:D3))”，這里的主關鍵區(qū)域為C2:C3，那么該公式的結果集大小為2（即有兩個元素）。
2)有互依賴關系的引用之間大小要一致
互依賴就是共同決定某個結果。
看“圖2-9”的D12“=SUM(IF((C2:C7=D9)*(D2:D7=D10),E2:E7))”，其中C2:C7與D2:D7就是互依賴的引用，它們共同決定IF()函數(shù)第一個參數(shù)的值，所以它們的大小必須一致，否則返回錯誤值。
對于沒有互依賴關系的引用大小有無限制？這就需要視具體情況而定了，再看“圖2-7”中的公式“=IF(C2:C3="Mary",ROW(D2:D3))”，其中的兩個引用之間就沒有大小的限制；而公式“=MIN(IF(C2:C3="Mary",ROW(D2:D3)))”就不一樣了，其中第一個引用的大小必須大于第二個引用的大小。
到現(xiàn)在為止，你可以說已經(jīng)掌握了Excel中的數(shù)組公式的工作原理以及一些中級技巧。學到現(xiàn)在你已經(jīng)足以可以應付一些工作中的需要了，當然前提是你真正已經(jīng)掌握了這些技術。
不知道你現(xiàn)在的感覺如何？可能你還有一頭霧水的感覺，這是正常的，Excel中的數(shù)組公式確實不是很好理解。不要緊！只要多做練習，將實踐與理論結合，很快你就會體會到它的強大威力！
為了使你的工作效率更高，我又寫了“應用篇”，內容主要是平時能夠遇到問題的一些解決方案（包括自己心得和網(wǎng)友一些問題的解答）。
因此請您繼續(xù)關注“數(shù)組公式從入門到精通”之“應用篇”，讓我們展示數(shù)組公式的威力！
