热膨胀公式阵列公式从入门到精

第一章、陣列公式從入門到精通

入門篇

本主題包含三部分：入門篇、提高篇、應用篇（分中級和高級）

對於剛接觸Excel陣列公式的人來說，總是會感覺到它的一份神秘。又Excel
的Online Help中只有很少關於它的主題，所以這種神秘感就更強了。不要緊，
只要跟著我的思路走，你很快就 會看清陣列公式的真面目！

陣列概念
對於陣列概念，大家都會很熟悉，其就 是一個具有維度的集合。比如：一維
陣列、二維陣列、多維陣列。陣列的表示一般爲“{}”所包括（一 維和二維陣列）。
Excel中也不例外，如果你想直接表示一個陣列，也必須用“{}”括起來。

陣列與陣列公式
在Excel中，凡是以半形符號“=”開始的單格內容都被Ex cel認爲是公式，
其只能返回一個結果。而陣列公式可以返回一個或者是多個結果，而返回的結果又可以是一維或二維的，換句話說，Excel中的陣列公式返回的是一個一維或二
維的陣列集合。
在Excel中需要按下 “Ctrl+Shift+Enter”組合鍵結束陣列公式的輸入。

爲什麽要用陣列公式？
如果你的需要滿足以下條件之一，那麽採用陣列公式技術可能會是你很好的
選擇方案。
你的運算結果會返回一個集合嗎？
你是否希望用戶不會有意或無意的破壞某一相關公式集合的完整性？
你的運算中是否存在著一些只有通過複雜的中間運算過程才會等到結
果的運算？
看到這些另人費解的問題，你可能會摸不著頭緒。不要緊，看了以下內容你
也許就會明白了。

什麽情況下會返回一個集合？
看一個簡單的例子，選中C1:E3，輸入“={
”，按“Ctrl+Enter”組合鍵。

圖1-1 (ArrayFormula_)
結果在C1:E3中看到的結果全是 “Name”，而實際真正返回的結果應該是
一個包含三行三列的二維陣列，如何辦？答案就是用陣列公 式。選中C1:E3，輸
入“={”，按
“Ctrl+Shift+Enter”組合鍵。

圖1-2 (ArrayFormula_)
可能你又會問，這有何用？爲何不在單格中直接輸入內容，反而要這麽麻
煩？
這僅僅 是一個例子，說明的是如何通過陣列公式返回一個結果集。給你個問
題，如果存在這樣一個工作表：包含 欄位{，如何將
“Sex”爲“Female”的記錄抽取出來 (爲了列印報表，抽取的記錄需要連續存
放) ？這個問題將在“應用篇”裏進行解答。

什麽情況下會用到相關公式完整性？
什麽是相關公式完整性？這僅僅是我給出的一個定義，請 再回到“圖
1-2”，請選擇C1:E3中任意一單格，然後做隨意的修改(哪怕和原先的公式一
樣)，按“Enter”鍵結束輸入。結果如何？修改未成功！提示“不能更改陣列的
某一部分”。

圖1-3 (ArrayFormula_)
爲什麽會是這樣呢？因爲你正企圖破 壞相關公式的完整性。由於C1:E3中公
式的資料源均爲“{
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_)


圖1-5 (ArrayFormula_)

怎麽樣？是否瞭解了陣列公式？是否學會了如何使用陣列公式？是否感覺
到了它的一點點威力？

請繼續關注“陣列公式從入門到精通”之“提高篇”，讓我們繼續深入陣列
公式！
第二章、陣列公式從入門到精通

一、提高篇

本主題包含三部分：入門篇、提高篇、應用篇（分中級和高級）


相信你在“入門 篇”中已經學會了如何建立陣列公式，同時也大致瞭解在什
麽情況下適合使用陣列公式解決問題。需要說 明的是，在“入門篇”中提到的使
用陣列公式的三種情況並不是絕對的，要視具體情況而定。
在接下來的討論中，你將會瞭解陣列公式的一些工作原理。
在進行正式討論之前，先跟著我做一些準備工作。
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_)

之所以創建以上公式，是因爲我想對滿足“Product ID”爲D9，“City”爲
D10的記錄進行匯總，很明顯，從上面的返回結果表明D11中的結果 是正確的，
而D10中的結果是錯誤的。爲什麽會是這樣呢？
在接下來的演示中通過講述AN D()和OR()函數的工作原理來解釋爲什麽
D10中的公式返回了錯誤的結果，以及演示爲什麽D1 1中的公式可以神奇般的
得到結果。
選中在上面工作表的G2:G7，輸入“=OR(C2: C7=D9,D2:D7=D10)”，按
“Ctrl+Shift+Enter”；選中H2:H7, 輸入“=AND(C2:C7=D9,D2:D7=D10)”，按
“Ctrl+Shift+Ente r”。

圖2-2 (ArrayFormula_)


圖2-3 (ArrayFormula_)

怎麽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,D
5=D10,D6=D1 0,D7=D10”，這回知道原因了吧？
“=OR(C2:C7=D9,D2:D7=D10)”返回 的結果只有一個，而不是七個！同理，
AND()函數類似。不信，你可以更改資料表中的一些資料來進 行驗證。
現在你該知道D10返回錯誤值的原因了吧？那爲什麽D11能夠返回正確的
結果？ 這正是我們要解決AND()和OR()函數在陣列公式中存在問題的出發點。
先看看下面這個說法：“ *”相當於AND，“+”相當於OR。這是一些論壇中
常見的回答，我到如今爲止也這樣解答了不少朋 友的疑問。結論正確麽？難道
Excel中的“*”和“+”有兩層含義？――嚴格的說，這是不正確的 ！因此，我
已經誤導了很多朋友，如果你曾經在某論壇中得到過我這樣的解答，我在這裏說
聲抱 歉！爲什麽“*”和“+”可以類比AND和OR呢？就像“圖2-1”中D12
的公式“=SUM(I F((C2:C7=D9)*(D2:D7=D10),E2:E7))”。
要瞭解其原理，就要揭開 FALSE和TRUE的面紗。在一新工作表的C2中輸
入“=TRUE+0”，按下右鍵；在D2中輸 入“=FALSE+0”，按下右鍵。

圖2-4 (ArrayFormula_)

“圖2-4”中的結果說明：將TRUE和FALSE轉換爲整型後的值分別爲1和
0。 建立如下圖中的工作表，選中D2:E3，輸入“=D$$1*$$C2”，按
“Ctrl+Enter ”；同樣選中D6:E7，輸入“=D$$5+$$C6”，按“Ctrl+Enter”。

圖2-5 (ArrayFormula_)

從上圖中很容易看出，對於“乘”操作 ，只有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_)


C5中的公式爲“=IF( C2:C3=”（爲陣列公式），你知道
它的值爲什麽是FALSE而不是三麽？
聰明的你可 能已經想到這種類型的陣列公式返回的是一個結果集，這個結果
集的大小與操作物件的大小是一致的，在 這裏操作物件爲C2:C3和D2:D3，因此
返回值爲兩個元素。
就是這樣，由於C2=” John”，不滿足條件，因此應該返回IF()函數的第三個
參數值，但這裏無第三個參數，所以系統 返回FALSE；由於C3=”Mary”，滿足
條件，因此返回第二個參數值，即ROW(D2:D3 )，而C3對應的是D3，所以返
回值應該爲3。爲了驗證結果，請選擇C5:C6，輸入
“= IF(C2:C3=”，按“Ctrl+Shift+Enter”。結果如何？

圖2-7 (ArrayFormula_)


聰明的Excel
先看看這個，知道 “=MIN(FALSE,3)”的返回值麽？結果返回0，從上面論
述的知識不難理解，因爲FALS E轉換爲整型的值爲0。我們已經知道“圖2-7”
中“=IF(C2:C3=”的結果集爲“{FAL SE,3}”，那麽，請選
擇“圖2-7”中的D5，輸入“=MIN(IF(C2:C3=”，按“Ctrl+Shift+Enter”，看結果。

圖2-8 (ArrayFormula_)

結果竟然是3，而不是0！這就是Excel聰明之處！爲 什麽說聰明呢？因爲在
絕大部分情況下我們想要的結果是滿足條件的部分，而捨棄非滿足條件的部分。< br>這對篩選資料非常有幫助！如果你堅持要將非滿足條件的部分包含進來，最簡單
的方法可以將公式 變形爲“=MIN(IF(C2:C3=”，簡簡單
單的一個逗號“,”，結果卻截然不同。對於如何對 篩選有幫助，將在“應用篇”
中給予實例解答。

類比IF()
再來看看 “圖2-1”中D12的公式
“=SUM(IF((C2:C7=D9)*(D2:D7=D10),E 2:E7))”，讓我們換種形式。在E12中輸
入“”，按“Ctrl+Shift+Enter”。

圖2-9 (ArrayFormula_)

結果也是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()效果都相同，要視具體情況而定，這就需要你靈活掌握了。

引用大小制約
此主題並非重要，不過爲了使你更加深入陣列公式，還是在這裏介紹一下。
這裏的引用大小制 約指的是陣列公式中各相關引用之間的大小制約或引用
大小對結果集大小的制約。
主關鍵區域 決定陣列函數返回值的大小（關鍵區域是指決定陣列公式
返回結果集大小的區域）

看 “圖2-7”中的公式“=IF(C2:C3=”，這裏的
主關鍵區域爲C2:C3，那麽該公式的結果 集大小爲2（即有兩個元素）。
2)
有互依賴關係的引用之間大小要一致
互依賴就是共同決定某個結果。
看“圖2-9”的D12
“=SUM(IF((C2 :C7=D9)*(D2:D7=D10),E2:E7))”，其中C2:C7與D2:D7
就是互依 賴的引用，它們共同決定IF()函數第一個參數的值，所以它們的
1)
大小必須一致，否則返回錯誤值。
對於沒有互依賴關係的引用大小有無限制？這就需要視具體 情況而
定了，再看“圖2-7”中的公式“=IF(C2:C3=”，其
中的兩個引用之間就沒 有大小的限制；而公式
“=MIN(IF(C2:C3=”就不一樣了，其中第一個引用
的大小 必須大於第二個引用的大小。

到現在爲止，你可以說已經掌握了Excel中的陣列公式的 工作原理以及一些
中級技巧。學到現在你已經足以可以應付一些工作中的需要了，當然前提是你真
正已經掌握了這些技術。

不知道你現在的感覺如何？可能你還有一頭霧水的感覺，這是正 常的，Excel
中的陣列公式確實不是很好理解。不要緊！只要多做練習，將實踐與理論結合，
很快你就會體會到它的強大威力！

爲了使你的工作效率更高，我又寫了“應用篇”，內容 主要是平時能夠遇到
問題的一些解決方案（包括自己心得和網友一些問題的解答）。