摘要：闡述了EXCEL數(shù)組公式的概念、類型與編輯的方法，以實(shí)例的形式說明了數(shù)組公式在多字段條件下進(jìn)行各類統(tǒng)計(jì)的獨(dú)特長處。

關(guān)鍵字：EXCEL；數(shù)組公式；條件統(tǒng)計(jì)

中圖分類號(hào)：TP39文獻(xiàn)標(biāo)識(shí)碼：A 文章編號(hào)：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，不僅具有制表方便靈活、輸出數(shù)據(jù)格式豐富美觀的特點(diǎn)，而且因?yàn)榫哂袕?qiáng)大的的數(shù)據(jù)檢索、排序、篩選與統(tǒng)計(jì)功等功能，被廣泛應(yīng)用于企事業(yè)單位的信息處理之中。但在數(shù)據(jù)統(tǒng)計(jì)方面，EXCEL系統(tǒng)提供的系統(tǒng)函數(shù)相對較少，如無條件相關(guān)的SUM，AVERAGE與條件相關(guān)的COUNTIF，SUMIF等，面對多字段條件的數(shù)據(jù)統(tǒng)計(jì)，系統(tǒng)函數(shù)顯得束手無策。筆者經(jīng)過較為深入、系統(tǒng)的研究，認(rèn)為使用EXCEL的數(shù)組公式，可較好地解決此類問題。本文以實(shí)例方式探討了數(shù)組公式的有關(guān)知識(shí)及其在多字段條件下進(jìn)行數(shù)據(jù)統(tǒng)計(jì)的公式編寫。

1 數(shù)組公式簡介與編輯

1.1 概念

所謂EXCEL數(shù)組公式，即公式中使用了數(shù)組，對一組或多組值執(zhí)行多重計(jì)算，并返回一個(gè)或多個(gè)結(jié)果，其形式表現(xiàn)為在地址欄中公式或數(shù)據(jù)面被“{ }”括住，但公式首尾的“{ }”由系統(tǒng)自動(dòng)生成而不是手工添加上去的（如圖1）。

1.2 類型

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

1.3 創(chuàng)建與編輯公式

1.3.1 創(chuàng)建

A 選擇要保存結(jié)果的單元格區(qū)域，如C4：C8（或D4）

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

C 同時(shí)按Ctrl+Shift+Enter三鍵，則單元格中自動(dòng)產(chǎn)生數(shù)組公式“{=A4：A8*B4：B8}”。

1.3.2 編輯

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

2 數(shù)組公式中的條件表達(dá)與相關(guān)統(tǒng)計(jì)函數(shù)

2.1 條件表達(dá)

數(shù)據(jù)統(tǒng)計(jì)中條件的類型一般不外乎“與、或、非”三種。在數(shù)組公式應(yīng)用中， “非”關(guān)系一般用“<>”表示，字段之間的“與”關(guān)系，用IF語句嵌套實(shí)現(xiàn)或用條件間相乘（“*”）來表示，而不用AND函數(shù)來表達(dá)，字段之間的“或”關(guān)系，用條件間相加（“+”）來表示，而不用OR函數(shù)來表達(dá)。

2.2 相關(guān)統(tǒng)計(jì)函數(shù)

根據(jù)統(tǒng)計(jì)要求的實(shí)際情況，一般下列函數(shù)在數(shù)組公式中應(yīng)用較多，其名稱與作用如表1。

3 數(shù)組公式應(yīng)用實(shí)例

圖為我校07級(jí)計(jì)算機(jī)應(yīng)用專業(yè)1班學(xué)生入學(xué)情況，由于人員變動(dòng)，已對其狀態(tài)作了調(diào)整?？傮w情況為，入學(xué)總?cè)藬?shù)48人，已轉(zhuǎn)出1人，流生5人，現(xiàn)在冊42人，其中男生24人，女生18人?，F(xiàn)就數(shù)組公式在條件統(tǒng)計(jì)中的應(yīng)用舉例如下：

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

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

例2：求班級(jí)全部入學(xué)學(xué)生社會(huì)科目在50至60之間的總成績

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

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

注：在上二例中因只涉及單字段條件統(tǒng)計(jì)，也可用系統(tǒng)函數(shù)實(shí)現(xiàn)，分別為

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

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

例3：求班級(jí)中所有在冊的男生人數(shù)

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

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

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

例4：求班級(jí)在冊學(xué)生語文在100分以上（含）的的平均分

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

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

例5：求班級(jí)在冊學(xué)生語文在100分以上（含）或數(shù)學(xué)在100以上（含）的人數(shù)

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

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

例6：求入學(xué)總分在前5位的平均總分

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

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

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

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

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

值， MAX函數(shù)對所有返回的值求最大值。

4 結(jié)束語

在EXCEL中利用SUM、SUMIF，COUNTIF及有關(guān)的運(yùn)算符只能實(shí)現(xiàn)對單字段數(shù)據(jù)的條件統(tǒng)計(jì)，利用數(shù)組公式不僅完全可以替代解決，而且可較好地處理工作表內(nèi)多字段、多條件的各種數(shù)據(jù)統(tǒng)計(jì)問題。另外，在具體使用數(shù)組公式時(shí)，須注意以下幾點(diǎn)：

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

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

3)編輯時(shí)應(yīng)注意“（）”的配對，尤其在多條件表達(dá)時(shí)，缺少其中一個(gè)將系統(tǒng)一般無法自動(dòng)糾錯(cuò)，人工糾錯(cuò)也會(huì)

有一定困難。

參考文獻(xiàn)：

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

[2] [美]，Johnwalkenbach著.EXCEL2002公式與函數(shù)應(yīng)用寶典[M]. 北京：電子工業(yè)出版社. 2002.

[3] 郭大春，李德民.巧用數(shù)組公式統(tǒng)計(jì)各班優(yōu)秀[N].中國電腦教育報(bào)，2005.

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