陽 昭

(永平縣林業(yè)局，云南 永平 672600)

國內(nèi)普遍使用的Microsoft Office辦公軟件中的Excel電子表格組件具有強(qiáng)大的數(shù)據(jù)處理與分析功能，用途廣泛。但要想精通Excel應(yīng)用，數(shù)組公式和數(shù)組運(yùn)算是必須跨越的門檻，因?yàn)槭褂盟梢越鉀Q更復(fù)雜的計算問題。對于林業(yè)工程項(xiàng)目中經(jīng)常要進(jìn)行的多字段多條件求和來說，Excel內(nèi)置函數(shù)顯得解題乏術(shù)；用常規(guī)的篩選、數(shù)據(jù)透視表方法處理速度慢、繁瑣，準(zhǔn)確性難以保證。使用Excel數(shù)組公式的數(shù)組運(yùn)算，可以輕而易舉地解決此類問題，同時可以讓Excel中現(xiàn)有的函數(shù)功能變得更強(qiáng)大。筆者先介紹數(shù)組公式和數(shù)組運(yùn)算的有關(guān)知識，然后舉出一個實(shí)例進(jìn)行分析。

1 數(shù)組公式簡介

1.1 數(shù)組概念

在Excel函數(shù)與公式應(yīng)用中，數(shù)組是指一行、一列或多行多列的一組數(shù)據(jù)元素的集合。數(shù)據(jù)元素可以是數(shù)值、文本、日期、邏輯值和錯誤值。數(shù)組的維度是指數(shù)組的行列方向，一行多列的數(shù)組為橫向數(shù)組，一列多行的數(shù)組為縱向數(shù)組。多行多列的數(shù)組則同時擁有縱向和橫向2個維度。數(shù)組的維數(shù)是指數(shù)組中不同維度的個數(shù)。只有一行或一列在單一方向上延伸的數(shù)組，稱為一維數(shù)組；多行多列同時擁有2個維度的數(shù)組稱為二維數(shù)組。

1.2 數(shù)組類型

有常量數(shù)組、區(qū)域數(shù)組、內(nèi)存數(shù)組、命名數(shù)組。直接在公式中寫入數(shù)組元素，并用大括號“{}”在首尾進(jìn)行標(biāo)識的數(shù)組為常量數(shù)組。一維縱向常量數(shù)組的各元素用半角分號“;”區(qū)隔，如[1;2;3;4;5;6]。一維橫向常量數(shù)組的各元素用半角逗號“,”區(qū)隔，如{“張三”,“李四”,“王五”}，二維常量數(shù)組的各列元素用半角逗號“,”區(qū)隔，各行元素用半角分號“;”區(qū)隔，如{1,2,3;#N/A,TRUE;“森林”,“2008-8-8”,“股市”;#VALUE,FALSE,12}。在公式或函數(shù)參數(shù)中引用工作表的某個單元格區(qū)域，且其中函數(shù)參數(shù)不是單元格引用或區(qū)域類型，也不是向量時，則Excel會自動將該區(qū)域引用轉(zhuǎn)換成由區(qū)域中各單元格的值構(gòu)成的同維度同尺寸的數(shù)組，稱為區(qū)域數(shù)組。某一公式通過計算，在內(nèi)存中臨時返回多個結(jié)果值構(gòu)成的數(shù)組為內(nèi)存數(shù)組。用名稱命名一個常量數(shù)組、區(qū)域數(shù)組或內(nèi)存數(shù)組為命名數(shù)組。

1.3 數(shù)組公式概念

公式中使用了數(shù)組，以按下組合鍵后公式首尾加上“{}”來完成編輯的公式。數(shù)組公式的實(shí)質(zhì)是單元格公式的一種書寫形式，用來通知Excel計算引擎對其執(zhí)行多重計算。

1.4 數(shù)組公式類型

按其取得結(jié)果單元格的多少，可以分為占據(jù)多個單元格的多單元格數(shù)組公式與只占一個單元格的數(shù)組公式。

1.5 數(shù)組公式輸入和編輯

輸入時選定公式結(jié)果需要顯示的單元格或單元格區(qū)域后輸入公式，輸入完畢按下組合鍵完成公式輸入。單個單元格數(shù)組公式選定公式所在單元格進(jìn)行編輯，最后按下組合鍵結(jié)束編輯；多單元格數(shù)組公式只需選定公式區(qū)域中任一單元格對其進(jìn)行編輯，最后按下組合鍵結(jié)束編輯。

2 數(shù)組運(yùn)算規(guī)則

掌握數(shù)組運(yùn)算規(guī)則才能有目的地構(gòu)建數(shù)組、改變數(shù)組尺寸，以避免編寫出違背規(guī)則的錯誤公式。由于數(shù)組的構(gòu)成元素包含數(shù)值、文本、邏輯值、錯誤值，因此數(shù)組繼承著各類數(shù)據(jù)的運(yùn)算特性(錯誤值除外)，即數(shù)值型和邏輯型數(shù)組可以進(jìn)行加法和乘法等常規(guī)的算術(shù)運(yùn)算；文本型數(shù)值可以進(jìn)行連接符運(yùn)算。在數(shù)組運(yùn)算中，數(shù)組可以擴(kuò)展以適應(yīng)與之運(yùn)算的數(shù)組，其中單元素數(shù)組可以橫向、縱向擴(kuò)展；一維橫向數(shù)組可以縱向擴(kuò)展；一維縱向數(shù)組可以橫向擴(kuò)展；二維數(shù)組不能擴(kuò)展。下面用2個數(shù)組相乘示例說明數(shù)組運(yùn)算規(guī)則。

2.1 單值或單元素數(shù)組間的運(yùn)算

單值或單元素數(shù)組可以與另一個數(shù)組自由運(yùn)算，返回與另一個數(shù)組相同尺寸的結(jié)果(圖1)。

圖1 單值或單元素數(shù)組與數(shù)組之間運(yùn)算規(guī)則Fig.1 Operational algorithm between array and monodrome or single-element array

2.2 同方向一維數(shù)組間的運(yùn)算

2個同方向一維數(shù)組的運(yùn)算進(jìn)行相同位置元素一一對應(yīng)運(yùn)算，因此要求2個數(shù)組具有相同尺寸，否則返回與較多元素數(shù)組相同的結(jié)果，但多出較少元素數(shù)組部分為#N/A錯誤(圖2)。

2.3 不同方向一維數(shù)組間的運(yùn)算

2個不同方向的一維數(shù)組即M行垂直數(shù)組與N列水平數(shù)組的運(yùn)算，數(shù)組中的每一個元素分別與另一個數(shù)組中的每一個元素進(jìn)行運(yùn)算返回M*N二維數(shù)組(圖3)。

圖2 同方向一維數(shù)組間的運(yùn)算規(guī)則Fig2 Operationalalgorithmamongone-dimensionalarraysinthesamedirection

圖3 不同方向一維數(shù)組間的運(yùn)算規(guī)則Fig3 Operationalgorithmamongone-dimensionalarraysindifferentdirections

2.4 一維數(shù)組與二維數(shù)組間的運(yùn)算

當(dāng)一維數(shù)組與二維數(shù)組具有同向相同尺寸的特征時，即M行數(shù)組或者N列數(shù)組與M行N列數(shù)組，進(jìn)行該方向的一一對應(yīng)運(yùn)算并返回M*N二維數(shù)組，否則在一維數(shù)組方向上差異部分整行或整列返回#N/A錯誤(圖4)。

圖4 一維數(shù)組與二維數(shù)組間的運(yùn)算規(guī)則Fig4 Operationalgorithmbetweenone-dimensionalarrayandtwo-dimensionalarray

2.5 二維數(shù)組間的運(yùn)算

2個二維數(shù)組運(yùn)算按尺寸較小的數(shù)組所有元素一一對應(yīng)運(yùn)算，并返回2個數(shù)組較大尺寸方向元素個數(shù)相運(yùn)算的數(shù)組。如果2個數(shù)組的尺寸完全相同，則全部返回正確的運(yùn)算結(jié)果，否則僅2個數(shù)組較小尺寸方向元素個數(shù)相運(yùn)算區(qū)域可以返回正確的運(yùn)算結(jié)果，超出部分均返回#N/A錯誤(圖5)。

圖5 二維數(shù)組間的運(yùn)算規(guī)則Fig.5 Operation algorithm among two-dimensional arrays

3 邏輯值與數(shù)值轉(zhuǎn)換規(guī)則

在Excel函數(shù)運(yùn)算中，邏輯值只有TRUE和FALSE 2個，TRUE代表邏輯值“真”，F(xiàn)ALSE代表邏輯值“假”。對2個數(shù)值數(shù)據(jù)或文本數(shù)據(jù)進(jìn)行比較運(yùn)算時，其運(yùn)算結(jié)果是一個邏輯值。邏輯值與數(shù)值的關(guān)系為：在四則運(yùn)算中，邏輯值轉(zhuǎn)換為數(shù)值，即TRUE=1，F(xiàn)ALSE=0。在邏輯判斷中，數(shù)值轉(zhuǎn)換為邏輯值，即0=FALSE，所有非0數(shù)值=TRUE。以上規(guī)則被廣泛應(yīng)用在數(shù)組運(yùn)算中。

4 理解“多重計算”

多重計算是指對公式中有對應(yīng)關(guān)系的數(shù)組元素同步執(zhí)行相關(guān)計算。Excel幫助文件對數(shù)組公式的說明為“對一組值或多組值執(zhí)行多重計算，并返回一個或多個結(jié)果，數(shù)組公式置于大括號{}中”。只有理解了多重計算才能更好地理解數(shù)組公式，現(xiàn)舉例說明。在A1:A5中分別輸入-1、0、2、142、-33這5個數(shù)字，求所有正數(shù)之和。在單元格中輸入數(shù)組公式{=SUM((A1:A5>0)*A1:A5)}即可求得結(jié)果為144。按照公式的運(yùn)算順序，該公式的計算過程解析如下：1)執(zhí)行最里層括號內(nèi)(A1:A5>0)的運(yùn)算，即A1>0、A2>0、A3>0、A4>0、A5>0的5個比較運(yùn)算，得到的邏輯值數(shù)組為{FALSE; FALSE;TRUE; TRUE; FALSE}。2)根據(jù)四則運(yùn)算中邏輯值轉(zhuǎn)換為數(shù)值的規(guī)則和數(shù)組運(yùn)算規(guī)則分別乘以A1:A5單元格區(qū)域的值，得到數(shù)組{0;0;2;142;0}。3)對{0;0;2;142;0}求和得到144。本例所舉的此類運(yùn)算過程就是 “多重計算”。運(yùn)算原理圖解如圖6。

5 數(shù)組公式中邏輯函數(shù)不能代替多重*、+計算

在Excel中，條件關(guān)系類型一般為“與、或、非”，對應(yīng)的邏輯運(yùn)算函數(shù)為“AND、OR、NOT”?！芭c”關(guān)系可以使用邏輯表達(dá)式相乘(*)和AND函數(shù)完成；“或”關(guān)系可以使用邏輯表達(dá)式相加(+)和OR函數(shù)完成。但在需要進(jìn)行多重計算的數(shù)組公式中，“與、或”條件關(guān)系的計算是返回邏輯值數(shù)組的運(yùn)算，而AND函數(shù)、OR函數(shù)返回的是單值FRUE或FALSE，無法返回數(shù)組結(jié)果，單值不能形成數(shù)組公式各參數(shù)間的一一對應(yīng)關(guān)系。因此，進(jìn)行多重計算的數(shù)組公式中的*，+運(yùn)算不能用AND函數(shù)、OR函數(shù)代替。

圖6 多重計算原理Fig.6 Multi-computing principle

6 利用數(shù)組公式進(jìn)行多條件求和實(shí)例和運(yùn)算過程解析

6.1 數(shù)據(jù)說明

用省級公益林?jǐn)?shù)據(jù)作實(shí)例，為了簡潔易懂，對原數(shù)據(jù)作了刪減。在Microsoft Excel 2010中，用“省級公益林小班因子一覽表”(圖7)統(tǒng)計出“省級公益林林地面積統(tǒng)計表”(圖8)。

圖7 省級公益林小班因子一覽Fig.7 List of subcompartment factor of provincialpublic beneficial forest

6.2 對數(shù)據(jù)進(jìn)行檢查整理和預(yù)處理

圖7的表格中，列通常稱為字段，行稱為記錄。表格的第一行是字段名。統(tǒng)計前要對表格數(shù)據(jù)作檢查，更正錯漏，要求每一字段下的數(shù)據(jù)類型要一致。為了便于公式編輯、直觀簡潔易理解，在公式中定義了名稱，對單元格區(qū)域進(jìn)行命名，使引用的區(qū)域數(shù)組變成命名數(shù)組。對圖7的表格單元格區(qū)域定義名稱的操作方法步驟是：1)左鍵拖動鼠標(biāo)選擇數(shù)據(jù)區(qū)域A3:H10。2)單擊選項(xiàng)卡【公式】→命令組【定義的名稱】→【根據(jù)所選內(nèi)容創(chuàng)建】，彈出【以選定區(qū)域創(chuàng)建名稱】對話框。3)在對話框勾選【首行】復(fù)選框，【確定】完成名稱定義(圖9)。通過定義名稱，如D4:D10單元格區(qū)域的名稱就被定義為列首的字段名“地類”，其它列依此類推。

圖8 省級公益林林地面積統(tǒng)計Fig.8 Area statistics of provincial public beneficialforestland

圖9 定義名稱Fig9 Definingnames

6.3 在統(tǒng)計表中編寫公式

在圖8的表中進(jìn)行統(tǒng)計求和。D6:E9區(qū)域單元格的數(shù)據(jù)要滿足多個條件，需編寫多條件求和公式；C6:C11區(qū)域、C5:E5區(qū)域?yàn)榻y(tǒng)計表的合計欄，用常用的SUM函數(shù)求和即可。對多條件求和的數(shù)組公式用D6單元格舉例。D6中的數(shù)據(jù)需同時滿足“權(quán)屬”為“集體”、“地類”為“有林地”、“亞林種”為“水源涵養(yǎng)林”3個條件，條件關(guān)系類型為“與”。在D6中輸入的數(shù)組公式是：{=SUM((林地使用權(quán)=“集體”)*(地類=$B6)*(亞林種=D$4)*補(bǔ)償面積)}。公式中使用了單元格的混合引用，$B6引用了B6單元格的內(nèi)容“有林地”，D$4引用了D4單元格的內(nèi)容“水源涵養(yǎng)林”，目的是便于復(fù)制公式到其它單元格，減少公式輸入和編輯量。D6:E9區(qū)域其它單元格的公式依D6單元格公式的編寫原理方法輸入即可。

6.4 公式解析與說明

按照公式的運(yùn)算順序，D6單元格公式的計算過程解析如下：1)先執(zhí)行最里層括號內(nèi)的(林地使用權(quán)=“集體”)、(地類=$B6)、(亞林種=D$4)三個條件數(shù)組的比較運(yùn)算，(林地使用權(quán)=“集體”)運(yùn)算即G4=“集體”, G5=“集體”, G6=“集體”, G7=“集體”, G8=“集體”, G9=“集體”, G10=“集體”的7個比較運(yùn)算，得到的邏輯值數(shù)組為{TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE}，同理執(zhí)行(地類=$B6)運(yùn)算得到邏輯值數(shù)組{FALSE; TRUE;FALSE; FALSE; FALSE; FALSE; TRUE}，(亞林種=D$4) 運(yùn)算得到邏輯值數(shù)組{TRUE; TRUE; FALSE; TRUE; FALSE; TRUE; TRUE}。2)根據(jù)四則運(yùn)算中邏輯值轉(zhuǎn)換為數(shù)值的規(guī)則和數(shù)組運(yùn)算規(guī)則，{TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE}、{FALSE; TRUE;FALSE; FALSE; FALSE; FALSE; TRUE}、{TRUE; TRUE; FALSE; TRUE; FALSE; TRUE; TRUE} 3個數(shù)組與H4:H10區(qū)域相乘后再求和得出結(jié)果596。數(shù)組公式運(yùn)算過程圖解如下圖10。注意公式是數(shù)組相乘后用SUM函數(shù)求和，各個條件數(shù)組、求和區(qū)域必須具有相同的尺寸，否則公式將返回#N/A錯誤。

7 結(jié)語

數(shù)組公式最大的優(yōu)勢是能進(jìn)行一些其他方法無法進(jìn)行的運(yùn)算，但它是Excel中最難理解的特性之一，學(xué)習(xí)掌握困難，學(xué)習(xí)使用對象是掌握Excel基本操作，熟悉公式和函數(shù)使用的用戶。如果與不熟悉數(shù)組公式的用戶共享編寫有數(shù)組公式的工作簿，而他需要進(jìn)行一些修改又不清楚數(shù)組公式讓人迷惑的特性時，使用它就會遇到麻煩。在此由于篇幅所限，筆者只能拋磚引玉，有興趣者可參閱相關(guān)書籍和資料進(jìn)一步學(xué)習(xí)。

圖10 數(shù)組公式運(yùn)算過程圖解Fig10 Diagramofthealgorithmofthearrayformula

[1] Excel Home.Excel應(yīng)用大全[M].北京：人民郵電出版社，2008.

[2] Excel Home之家.Excel實(shí)戰(zhàn)技巧精粹[M].北京：人民郵電出版社，2007.

[3] Excel Home.Excel公式與函數(shù)實(shí)戰(zhàn)技巧精粹[M].北京：人民郵電出版社，2008.

[4] (美)沃肯貝奇(Walkenbach,J.)著；楊艷，劉啟業(yè)，胡娟譯.Excel 2007寶典[M].北京：人民郵電出版社，2008.