Excel數(shù)組公式在林業(yè)多條件求和中的應用

陽 昭

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

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

1 數(shù)組公式簡介

1.1 數(shù)組概念

在Excel函數(shù)與公式應用中，數(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ù)組元素，并用大括號“{}”在首尾進行標識的數(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ù)組公式的實質(zhì)是單元格公式的一種書寫形式，用來通知Excel計算引擎對其執(zhí)行多重計算。

1.4 數(shù)組公式類型

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

4 理解“多重計算”

多重計算是指對公式中有對應關系的數(shù)組元素同步執(zhí)行相關計算。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。按照公式的運算順序，該公式的計算過程解析如下：1)執(zhí)行最里層括號內(nèi)(A1:A5>0)的運算，即A1>0、A2>0、A3>0、A4>0、A5>0的5個比較運算，得到的邏輯值數(shù)組為{FALSE; FALSE;TRUE; TRUE; FALSE}。2)根據(jù)四則運算中邏輯值轉(zhuǎn)換為數(shù)值的規(guī)則和數(shù)組運算規(guī)則分別乘以A1:A5單元格區(qū)域的值，得到數(shù)組{0;0;2;142;0}。3)對{0;0;2;142;0}求和得到144。本例所舉的此類運算過程就是 “多重計算”。運算原理圖解如圖6。

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

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

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

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

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

用省級公益林數(shù)據(jù)作實例，為了簡潔易懂，對原數(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ù)進行檢查整理和預處理

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

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

圖9 定義名稱Fig9 Definingnames

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

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

6.4 公式解析與說明

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

7 結(jié)語

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

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

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

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

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

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