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

數(shù)組公式從入門到精通數(shù)組公式從入門到精通數(shù)組公式從入門到精通數(shù)組公式從入門到精通入門篇本主題包含三部分：入門篇、提高篇、應用篇（分中級和高級)數(shù)組與數(shù)組公式在Excel中，凡是以半角符號“=”開始的單元格內(nèi)容都被Excel認為是公式，其只能返回一個結(jié)果。而數(shù)組公式可以返回一個或者是多個結(jié)果，而返回的結(jié)果又可以是一維或二維的，換句話說，Excel中的數(shù)組公式返回的是一個一維或二維的數(shù)組集合。在Excel中需要按下“Ctrl+Shift+Enter”組合鍵結(jié)束數(shù)組公式的輸入.為什么要用數(shù)組公式?如果你的需要滿足以下條件之一，那么采用數(shù)組公式技術(shù)可能會是你很好的選擇方案.你的運算結(jié)果會返回一個集合嗎？你是否希望用戶不會有意或無意的破壞某一相關(guān)公式集合的完整性?你的運算中是否存在著一些只有通過復雜的中間運算過程才會等到結(jié)果的運算？看到這些另人費解的問題,你可能會摸不著頭緒。不要緊，看了以下內(nèi)容你也許就會明白了。什么情況下會返回一個集合?看一個簡單的例子,選中C1:E3，輸入“=｛”Name"，”Sex"，"Age";”John”,”Male",21;”Mary"，"Female”，20｝”，按“Ctrl+Enter”組合鍵。圖1-1（ArrayFormula_A01.bmp）結(jié)果在C1:E3中看到的結(jié)果全是“Name”,而實際真正返回的結(jié)果應該是一個包含三行三列的二維數(shù)組，如何辦？答案就是用數(shù)組公式。選中C1:E3，輸入“={"Name"，”Sex",”Age”；"John"，"Male"，21；”Mary","Female”，20}”，按“Ctrl+Shift+Enter”組合鍵。圖1—2(ArrayFormula_A02。bmp)可能你又會問，這有何用？為何不在單元格中直接輸入內(nèi)容,反而要這么麻煩？這僅僅是一個例子,說明的是如何通過數(shù)組公式返回一個結(jié)果集。給你個問題，如果存在這樣一個工作表:包含字段｛”ID","Name","Sex”,"Age"}，如何將“Sex”為“Female”的記錄抽取出來（為了打印報表，抽取的記錄需要連續(xù)存放）?這個問題將在“應用篇”里進行解答。什么情況下會用到相關(guān)公式完整性？什么是相關(guān)公式完整性？這僅僅是我給出的一個定義，請再回到“圖1—2”，請選擇C1：E3中任意一單元格，然后做隨意的修改（哪怕和原先的公式一樣），按“Enter”鍵結(jié)束輸入。結(jié)果如何?修改未成功！提示“不能更改數(shù)組的某一部分”。圖1—3(ArrayFormula_A03.bmp）為什么會是這樣呢？因為你正企圖破壞相關(guān)公式的完整性。由于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)象,從而導致與其它相關(guān)單元格脫離關(guān)系，這樣數(shù)組公式就失去作用,所以系統(tǒng)不又允許你更改數(shù)組公式的部分內(nèi)容。這樣的好處是可以維護數(shù)據(jù)的完整性，做到與數(shù)據(jù)源總是有一致的對應關(guān)系。請繼續(xù)關(guān)注“數(shù)組公式從入門到精通”之“提高篇”，讓我們繼續(xù)深入數(shù)組公式！

數(shù)組公式從入門到精通提高篇相信你在“入門篇”中已經(jīng)學會了如何建立數(shù)組公式，同時也大致了解在什么情況下適合使用數(shù)組公式解決問題.需要說明的是,在“入門篇”中提到的使用數(shù)組公式的三種情況并不是絕對的,要視具體情況而定。在接下來的討論中,你將會了解數(shù)組公式的一些工作原理。在進行正式討論之前，先跟著我做一些準備工作。Excel的主要功能就是數(shù)據(jù)的分析和處理，我們現(xiàn)在只關(guān)心的是數(shù)據(jù)處理中的數(shù)據(jù)抽取。所謂數(shù)據(jù)抽取就是對源數(shù)據(jù)按照一定的條件篩選后所得到的結(jié)果。如何定制條件篩選呢?方法很多，這里介紹“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”結(jié)束公式輸入.圖2-1（ArrayFormula_B01.bmp)之所以創(chuàng)建以上公式，是因為我想對滿足“ProductID"為D9，“City”為D10的記錄進行匯總，很明顯，從上面的返回結(jié)果表明D11中的結(jié)果是正確的，而D10中的結(jié)果是錯誤的。為什么會是這樣呢？在接下來的演示中通過講述AND（）和OR（)函數(shù)的工作原理來解釋為什么D10中的公式返回了錯誤的結(jié)果，以及演示為什么D11中的公式可以神奇般的得到結(jié)果。選中在上面工作表的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é)果.為了節(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）”返回的結(jié)果只有一個，而不是七個！同理,AND（）函數(shù)類似。不信,你可以更改數(shù)據(jù)表中的一些數(shù)據(jù)來進行驗證.現(xiàn)在你該知道D10返回錯誤值的原因了吧？那為什么D11能夠返回正確的結(jié)果?這正是我們要解決AND()和OR()函數(shù)在數(shù)組公式中存在問題的出發(fā)點.先看看下面這個說法：“＊"相當于AND，“+”相當于OR。這是一些論壇中常見的回答,我到如今為止也這樣解答了不少朋友的疑問。結(jié)論正確么？難道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”中的結(jié)果說明：將TRUE和FALSE轉(zhuǎn)換為整型后的值分別為1和0.引用大小制約此主題并非重要，不過為了使你更加深入數(shù)組公式，還是在這里介紹一下。這里的引用大小制約指的是數(shù)組公式中各相關(guān)引用之間的大小制約或引用大小對結(jié)果集大小的制約。主關(guān)鍵區(qū)域決定數(shù)組函數(shù)返回值的大小(關(guān)鍵區(qū)域是指決定數(shù)組公式返回結(jié)果集大小的區(qū)域)看“圖2-7”中的公式“=IF(C2:C3=”Mary",ROW(D2：D3））”,這里的主關(guān)鍵區(qū)域為C2:C3，那么該公式的結(jié)果集大小為2（即有兩個元素)。有互依賴關(guān)系的引用之間大小要一致互依賴就是共同決定某個結(jié)果.看“圖2—9”的D12“=SUM(IF（(C2:C7=D9）*(D2：D7=D10)，E2:E7))”，其中C2:C7與D2：D7就是互依賴的引用，它們共同決定IF（）函數(shù)第一個參數(shù)的值，所以它們的大小必