ＥＸＣＥＬ數組公式在條件統計中的應用

摘要：闡述了EXCEL數組公式的概念、類型與編輯的方法，以實例的形式說明了數組公式在多字段條件下進行各類統計的獨特長處。

關鍵字：EXCEL；數組公式；條件統計

中圖分類號：TP39文獻標識碼：A 文章編號：1009-3044(2008)30-0747-02

EXCEL array Formula in Terms of Statistics

YE Jian-liang

(Shaoxing County Vocational Education Center，Shaoxing 312030，China)

Abstract: EXCEL expounded the concept of an array formula， type and edit approach to examples in the form of an array formula that the conditions in many fields under the unique strengths of various types of statistics.

Key words: EXCEL; Array formula; Statistics conditions

作為微軟OFFICE組件之一的EXCEL，不僅具有制表方便靈活、輸出數據格式豐富美觀的特點，而且因為具有強大的的數據檢索、排序、篩選與統計功等功能，被廣泛應用于企事業單位的信息處理之中。但在數據統計方面，EXCEL系統提供的系統函數相對較少，如無條件相關的SUM，AVERAGE與條件相關的COUNTIF，SUMIF等，面對多字段條件的數據統計，系統函數顯得束手無策。筆者經過較為深入、系統的研究，認為使用EXCEL的數組公式，可較好地解決此類問題。本文以實例方式探討了數組公式的有關知識及其在多字段條件下進行數據統計的公式編寫。

1 數組公式簡介與編輯

1.1 概念

所謂EXCEL數組公式，即公式中使用了數組，對一組或多組值執行多重計算，并返回一個或多個結果，其形式表現為在地址欄中公式或數據面被“{ }”括住，但公式首尾的“{ }”由系統自動生成而不是手工添加上去的（如圖1）。

1.2 類型

數組公式按其取得結果單元格的多少，可以分為占據多個單元格的數組公式（如圖C4：C8）與只占一個單元格的數據公式（如圖D4），在條件統計中，基本上為后者。數組有常量與區域兩類，常量數組中數據要放在“{}”中，而且內部各列的數值要用逗號“，”隔開（如圖D10：“=SUM({1，2，3}*{4，5，6})”，當二維數組時各行的數值要用分號“;”隔開）。區域數組用工作表中的單元格區域表示，如“A4:A8”、“B4:B8”。

1.3 創建與編輯公式

1.3.1 創建

A 選擇要保存結果的單元格區域，如C4：C8（或D4）

B 輸入公式 =A4：A8*B4：B8 (或=sum(A4：A8*B4))

C 同時按Ctrl+Shift+Enter三鍵，則單元格中自動產生數組公式“{=A4：A8*B4：B8}”。

1.3.2 編輯

編輯數組公式，應先選定數組公式的范圍，然后在地址欄中加以修改（或按F2），編輯完畢后再按Ctrl+Shift+Enter三鍵。如果是刪除公式，則在選定范圍后直接按DELETE鍵。

2 數組公式中的條件表達與相關統計函數

2.1 條件表達

數據統計中條件的類型一般不外乎“與、或、非”三種。在數組公式應用中， “非”關系一般用“<>”表示，字段之間的“與”關系，用IF語句嵌套實現或用條件間相乘（“*”）來表示，而不用AND函數來表達，字段之間的“或”關系，用條件間相加（“+”）來表示，而不用OR函數來表達。

2.2 相關統計函數

根據統計要求的實際情況，一般下列函數在數組公式中應用較多，其名稱與作用如表1。

3 數組公式應用實例

圖為我校07級計算機應用專業1班學生入學情況，由于人員變動，已對其狀態作了調整。總體情況為，入學總人數48人，已轉出1人，流生5人，現在冊42人，其中男生24人，女生18人。現就數組公式在條件統計中的應用舉例如下：

公式：{=SUM(IF((D7:D54)=\"在冊\"，1，0))}

公式分析：IF函數分別檢測單元格D7至D54，如果其值為“在冊”，則返回值1，否則為0，SUM函數對返回的值進行求和。

例2：求班級全部入學學生社會科目在50至60之間的總成績

公式：{=SUM(IF(((J7:J54)>=50)*((J7:J54)<60)，(J7:J54)))}

公式分析：IF函數分別檢測單元格J7至J54，如果其值為大于等于50且小于60，則返回該單元格值，否則為0（此處IF函數格式中的值2省略了），然后通過SUM函數對返回的值進行求和。

注：在上二例中因只涉及單字段條件統計，也可用系統函數實現，分別為

例1公式：=COUNTIF(D7:D54，\"在冊\")

例2公式：=SUMIF(J7:J54，\">=50\")-SUMIF(J7:J54，\">=60\")

例3：求班級中所有在冊的男生人數

公式： {=SUM(IF(((D7:D54)=\"在冊\")*((E7:E54)=\"男\")，1，0))}

或if嵌套公式： =SUM(IF((D7:D54)=\"在冊\"，IF((E7:E54)=\"男\"，1，0)，0))

公式分析：IF函數分別同時檢測D7至D54與E7至E54，如同行上D列值為“在冊”，E列上值為“男”，則返回1，否則為0，SUM函數對所有返回的值進行求和。

例4：求班級在冊學生語文在100分以上（含）的的平均分

公式：{=AVERAGE(IF(((D7:D54)=\"在冊\")*((K7:K54)>=100)，(K7:K54)))}

公式分析：IF函數分別同時檢測D7至D54與K7至K54，如同行上D列值為“在冊”且K列上值大于等于100，則返回K列的值，AVERAGE函數對所有返回的值求平均。

例5：求班級在冊學生語文在100分以上（含）或數學在100以上（含）的人數

公式：{=SUM(IF(((D7:D54)=\"在冊\")*(((K7:K54)>=100)+((L7:L54)>=100))，1))}

公式分析：IF函數分別同時檢測D7至D54與K7至K54、L7至L54，如同行上D列值為“在冊”并且K列上值大于等于100或L列上值大于等于100，則返回值1，SUM函數對所有返回的值求和。

例6：求入學總分在前5位的平均總分

公式：=AVERAGE(LARGE((O7:O54)，{1，2，3，4，5}))

公式分析：LARGE函數共執行5次，使用5個不同的參數，獲取O7至O54中前5個最大的數，AVERAGE函數對此5個數求平均值。

例7：求在冊男生總分最高分

公式：{=MAX(IF(((D7:D54)=\"在冊\")*((E7:E54)=\"男\")，(O7:O54)))}

公式分析：IF函數分別同時檢測D7至D54與E7至E54，如同行上D列值為“在冊”，E列上值為“男”，則返回O列的

值， MAX函數對所有返回的值求最大值。

4 結束語

在EXCEL中利用SUM、SUMIF，COUNTIF及有關的運算符只能實現對單字段數據的條件統計，利用數組公式不僅完全可以替代解決，而且可較好地處理工作表內多字段、多條件的各種數據統計問題。另外，在具體使用數組公式時，須注意以下幾點：

1)數組公式的“{}”是按Ctrl+Shift+Enter三鍵自動生成。

2)對于多條件的數組公式，可先對單記錄進行條件表達，然后再將單元格用單元格區域替代，單元格區域應用（）括住。如例2公式，我們先寫出“=SUM(IF( (J7>=50)*(J7<60)， J7 )) ”，然后將所有J7用(J7:J54)代替。在實際操作中，輸入公式前，可先在紙上書寫正確。

3)編輯時應注意“（）”的配對，尤其在多條件表達時，缺少其中一個將系統一般無法自動糾錯，人工糾錯也會

有一定困難。

參考文獻：

[1] 李繼兵.EXCEL公式與函數應用范例[M].北京：中國青年出版社，2005.

[2] [美]，Johnwalkenbach著.EXCEL2002公式與函數應用寶典[M]. 北京：電子工業出版社. 2002.

[3] 郭大春，李德民.巧用數組公式統計各班優秀[N].中國電腦教育報，2005.

注：本文中所涉及到的圖表、注解、公式等內容請以PDF格式閱讀原文