Excel數組公式從入門到精通之精通篇

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組合鍵，你會驚喜的發現，我們要的結果出現了。

        2.在Excel2003中享受“SUMIFS”

        SUMIF函數應該很多人都用過，非常好用。但如果遇到多條件判斷的怎麼辦呢？從Office 2007開始，引入了SUMIFS函數，可以解決這種多條件求和問題。但如果仍然使用Office 2003怎麼辦呢？其實使用SUM、IF再結合數組公式即可實作SUMIFS的效果。

        如下圖所示，某教師有一張任教的幾個班級的學生成績表。

        任務：統計出“一班”、“二班”共計多少人？

        此題要如何解決？SUMIF用兩次？或是COUNT用兩次？這裡還是示範數組公式的用法，是以先用SUM和IF組合的形式。

        在任一單元格中輸入“=SUM(IF((A2:A12="一班")+(A2:A12="二班"),1,0))”公式按下Enter鍵，是不是發現結果是“#VALUE！”，再次進入編輯欄然後按下“Ctrl+Shift+Enter”快捷鍵，是不是發現正确的結果出來了？

        這裡再次解釋一下這個公式“{=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、0”，因為第一個條件為真，第二個條件肯定就不為真了，因為一個單元格不可能同時等于“一班”和“二班”，是以第一個數組就是“1、0、0、1、0、0、1、0、0、1、0”。這時再判斷A2:A12區域内單元格的值是否是“二班”，如果是結果則為1，否則為0，是以這個數組條件計算的結果就是“0、1、0、0、0、1、0、1、0、0、0”，中間的加号就是将這兩個數組相加，也就是說最終的數組為“1、1、0、1、0、1、1、1、0、1、0”，然後使用SUM求和，結果就為7了。

        從上面的圖中标注可以看出，是以的公式我全部使用了數組（A2:A12這就是一個數組），并且上圖上的沒有使用數組公式的公式中的數組全部可以使用單個單元格替代，之是以全部列出，還是希望大家更好的了解一下數組。

        在Excel中，數組如果不放在數組公式中使用，通常數組在特定單元格中隻代表與其特定單元格所對應的一個值（數組中的一個元素），放在數組公式中使用時，通常整個數組元素都會參與運算。

        三、數組公式精通

        這裡是一個實際工作中的例子，隻是我稍微變化了一下，還是SUM應用的例子。

        需求：如下圖所示，現在要統計員工張三在1号加工所有機器的“實績”，也就是說在右側的資料中先過濾日期為1，然後再過濾人員為張三的資料，最後統計實績的結果。如果使用一個公式完成這一需求，你能想到嗎？當然SUMIFS是除外的，因為SUMIFS是Office 2007以後的産物。

        答案：在上圖所示的C2單元格中輸入“=SUM((E2:E21=A2)*(G2:G21=B2)*(H2:H21))”公式，然後按下“Ctrl+Shift+Enter”組合鍵，你會發現想要的結果已經出現了。數組公式就是這麼簡單，解決問題也是這麼簡單。

        這次用的公式可以看出，比之前用的公式還要簡單，連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了，然後再依次完成整個數組的運算，我們最終的目的就達到了。

        數組公式非常有用，效率也高，但真正的了解、熟練掌握也不是一件很容易的事。但大家記住數組中的資料是一一對應的，放到數組公式中使用時，數組中的資料會按順序依次參與相應的運算。

        希望大家能夠慢慢的了解、貫通。

     本文轉自windyli 51CTO部落格，原文連結：http://blog.51cto.com/windyli/313744，如需轉載請自行聯系原作者