Excel数组公式在林业多条件求和中的应用

阳 昭

(永平县林业局，云南 永平 672600)

国内普遍使用的Microsoft Office办公软件中的Excel电子表格组件具有强大的数据处理与分析功能，用途广泛。但要想精通Excel应用，数组公式和数组运算是必须跨越的门槛，因为使用它可以解决更复杂的计算问题。对于林业工程项目中经常要进行的多字段多条件求和来说，Excel内置函数显得解题乏术；用常规的筛选、数据透视表方法处理速度慢、繁琐，准确性难以保证。使用Excel数组公式的数组运算，可以轻而易举地解决此类问题，同时可以让Excel中现有的函数功能变得更强大。笔者先介绍数组公式和数组运算的有关知识，然后举出一个实例进行分析。

1 数组公式简介

1.1 数组概念

在Excel函数与公式应用中，数组是指一行、一列或多行多列的一组数据元素的集合。数据元素可以是数值、文本、日期、逻辑值和错误值。数组的维度是指数组的行列方向，一行多列的数组为横向数组，一列多行的数组为纵向数组。多行多列的数组则同时拥有纵向和横向2个维度。数组的维数是指数组中不同维度的个数。只有一行或一列在单一方向上延伸的数组，称为一维数组；多行多列同时拥有2个维度的数组称为二维数组。

1.2 数组类型

有常量数组、区域数组、内存数组、命名数组。直接在公式中写入数组元素，并用大括号“{}”在首尾进行标识的数组为常量数组。一维纵向常量数组的各元素用半角分号“;”区隔，如[1;2;3;4;5;6]。一维横向常量数组的各元素用半角逗号“,”区隔，如{“张三”,“李四”,“王五”}，二维常量数组的各列元素用半角逗号“,”区隔，各行元素用半角分号“;”区隔，如{1,2,3;#N/A,TRUE;“森林”,“2008-8-8”,“股市”;#VALUE,FALSE,12}。在公式或函数参数中引用工作表的某个单元格区域，且其中函数参数不是单元格引用或区域类型，也不是向量时，则Excel会自动将该区域引用转换成由区域中各单元格的值构成的同维度同尺寸的数组，称为区域数组。某一公式通过计算，在内存中临时返回多个结果值构成的数组为内存数组。用名称命名一个常量数组、区域数组或内存数组为命名数组。

1.3 数组公式概念

公式中使用了数组，以按下组合键后公式首尾加上“{}”来完成编辑的公式。数组公式的实质是单元格公式的一种书写形式，用来通知Excel计算引擎对其执行多重计算。

1.4 数组公式类型

按其取得结果单元格的多少，可以分为占据多个单元格的多单元格数组公式与只占一个单元格的数组公式。

1.5 数组公式输入和编辑

输入时选定公式结果需要显示的单元格或单元格区域后输入公式，输入完毕按下组合键完成公式输入。单个单元格数组公式选定公式所在单元格进行编辑，最后按下组合键结束编辑；多单元格数组公式只需选定公式区域中任一单元格对其进行编辑，最后按下组合键结束编辑。

2 数组运算规则

掌握数组运算规则才能有目的地构建数组、改变数组尺寸，以避免编写出违背规则的错误公式。由于数组的构成元素包含数值、文本、逻辑值、错误值，因此数组继承着各类数据的运算特性(错误值除外)，即数值型和逻辑型数组可以进行加法和乘法等常规的算术运算；文本型数值可以进行连接符运算。在数组运算中，数组可以扩展以适应与之运算的数组，其中单元素数组可以横向、纵向扩展；一维横向数组可以纵向扩展；一维纵向数组可以横向扩展；二维数组不能扩展。下面用2个数组相乘示例说明数组运算规则。

2.1 单值或单元素数组间的运算

单值或单元素数组可以与另一个数组自由运算，返回与另一个数组相同尺寸的结果(图1)。

图1 单值或单元素数组与数组之间运算规则Fig.1 Operational algorithm between array and monodrome or single-element array

2.2 同方向一维数组间的运算

2个同方向一维数组的运算进行相同位置元素一一对应运算，因此要求2个数组具有相同尺寸，否则返回与较多元素数组相同的结果，但多出较少元素数组部分为#N/A错误(图2)。

2.3 不同方向一维数组间的运算

2个不同方向的一维数组即M行垂直数组与N列水平数组的运算，数组中的每一个元素分别与另一个数组中的每一个元素进行运算返回M*N二维数组(图3)。

图2 同方向一维数组间的运算规则Fig2 Operationalalgorithmamongone-dimensionalarraysinthesamedirection

图3 不同方向一维数组间的运算规则Fig3 Operationalgorithmamongone-dimensionalarraysindifferentdirections

2.4 一维数组与二维数组间的运算

当一维数组与二维数组具有同向相同尺寸的特征时，即M行数组或者N列数组与M行N列数组，进行该方向的一一对应运算并返回M*N二维数组，否则在一维数组方向上差异部分整行或整列返回#N/A错误(图4)。

图4 一维数组与二维数组间的运算规则Fig4 Operationalgorithmbetweenone-dimensionalarrayandtwo-dimensionalarray

2.5 二维数组间的运算

2个二维数组运算按尺寸较小的数组所有元素一一对应运算，并返回2个数组较大尺寸方向元素个数相运算的数组。如果2个数组的尺寸完全相同，则全部返回正确的运算结果，否则仅2个数组较小尺寸方向元素个数相运算区域可以返回正确的运算结果，超出部分均返回#N/A错误(图5)。

图5 二维数组间的运算规则Fig.5 Operation algorithm among two-dimensional arrays

3 逻辑值与数值转换规则

在Excel函数运算中，逻辑值只有TRUE和FALSE 2个，TRUE代表逻辑值“真”，FALSE代表逻辑值“假”。对2个数值数据或文本数据进行比较运算时，其运算结果是一个逻辑值。逻辑值与数值的关系为：在四则运算中，逻辑值转换为数值，即TRUE=1，FALSE=0。在逻辑判断中，数值转换为逻辑值，即0=FALSE，所有非0数值=TRUE。以上规则被广泛应用在数组运算中。

4 理解“多重计算”

多重计算是指对公式中有对应关系的数组元素同步执行相关计算。Excel帮助文件对数组公式的说明为“对一组值或多组值执行多重计算，并返回一个或多个结果，数组公式置于大括号{}中”。只有理解了多重计算才能更好地理解数组公式，现举例说明。在A1:A5中分别输入-1、0、2、142、-33这5个数字，求所有正数之和。在单元格中输入数组公式{=SUM((A1:A5>0)*A1:A5)}即可求得结果为144。按照公式的运算顺序，该公式的计算过程解析如下：1)执行最里层括号内(A1:A5>0)的运算，即A1>0、A2>0、A3>0、A4>0、A5>0的5个比较运算，得到的逻辑值数组为{FALSE; FALSE;TRUE; TRUE; FALSE}。2)根据四则运算中逻辑值转换为数值的规则和数组运算规则分别乘以A1:A5单元格区域的值，得到数组{0;0;2;142;0}。3)对{0;0;2;142;0}求和得到144。本例所举的此类运算过程就是 “多重计算”。运算原理图解如图6。

5 数组公式中逻辑函数不能代替多重*、+计算

在Excel中，条件关系类型一般为“与、或、非”，对应的逻辑运算函数为“AND、OR、NOT”。“与”关系可以使用逻辑表达式相乘(*)和AND函数完成；“或”关系可以使用逻辑表达式相加(+)和OR函数完成。但在需要进行多重计算的数组公式中，“与、或”条件关系的计算是返回逻辑值数组的运算，而AND函数、OR函数返回的是单值FRUE或FALSE，无法返回数组结果，单值不能形成数组公式各参数间的一一对应关系。因此，进行多重计算的数组公式中的*，+运算不能用AND函数、OR函数代替。

图6 多重计算原理Fig.6 Multi-computing principle

6 利用数组公式进行多条件求和实例和运算过程解析

6.1 数据说明

用省级公益林数据作实例，为了简洁易懂，对原数据作了删减。在Microsoft Excel 2010中，用“省级公益林小班因子一览表”(图7)统计出“省级公益林林地面积统计表”(图8)。

图7 省级公益林小班因子一览Fig.7 List of subcompartment factor of provincialpublic beneficial forest

6.2 对数据进行检查整理和预处理

图7的表格中，列通常称为字段，行称为记录。表格的第一行是字段名。统计前要对表格数据作检查，更正错漏，要求每一字段下的数据类型要一致。为了便于公式编辑、直观简洁易理解，在公式中定义了名称，对单元格区域进行命名，使引用的区域数组变成命名数组。对图7的表格单元格区域定义名称的操作方法步骤是：1)左键拖动鼠标选择数据区域A3:H10。2)单击选项卡【公式】→命令组【定义的名称】→【根据所选内容创建】，弹出【以选定区域创建名称】对话框。3)在对话框勾选【首行】复选框，【确定】完成名称定义(图9)。通过定义名称，如D4:D10单元格区域的名称就被定义为列首的字段名“地类”，其它列依此类推。

图8 省级公益林林地面积统计Fig.8 Area statistics of provincial public beneficialforestland

图9 定义名称Fig9 Definingnames

6.3 在统计表中编写公式

在图8的表中进行统计求和。D6:E9区域单元格的数据要满足多个条件，需编写多条件求和公式；C6:C11区域、C5:E5区域为统计表的合计栏，用常用的SUM函数求和即可。对多条件求和的数组公式用D6单元格举例。D6中的数据需同时满足“权属”为“集体”、“地类”为“有林地”、“亚林种”为“水源涵养林”3个条件，条件关系类型为“与”。在D6中输入的数组公式是：{=SUM((林地使用权=“集体”)*(地类=$B6)*(亚林种=D$4)*补偿面积)}。公式中使用了单元格的混合引用，$B6引用了B6单元格的内容“有林地”，D$4引用了D4单元格的内容“水源涵养林”，目的是便于复制公式到其它单元格，减少公式输入和编辑量。D6:E9区域其它单元格的公式依D6单元格公式的编写原理方法输入即可。

6.4 公式解析与说明

按照公式的运算顺序，D6单元格公式的计算过程解析如下：1)先执行最里层括号内的(林地使用权=“集体”)、(地类=$B6)、(亚林种=D$4)三个条件数组的比较运算，(林地使用权=“集体”)运算即G4=“集体”, G5=“集体”, G6=“集体”, G7=“集体”, G8=“集体”, G9=“集体”, G10=“集体”的7个比较运算，得到的逻辑值数组为{TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE}，同理执行(地类=$B6)运算得到逻辑值数组{FALSE; TRUE;FALSE; FALSE; FALSE; FALSE; TRUE}，(亚林种=D$4) 运算得到逻辑值数组{TRUE; TRUE; FALSE; TRUE; FALSE; TRUE; TRUE}。2)根据四则运算中逻辑值转换为数值的规则和数组运算规则，{TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE}、{FALSE; TRUE;FALSE; FALSE; FALSE; FALSE; TRUE}、{TRUE; TRUE; FALSE; TRUE; FALSE; TRUE; TRUE} 3个数组与H4:H10区域相乘后再求和得出结果596。数组公式运算过程图解如下图10。注意公式是数组相乘后用SUM函数求和，各个条件数组、求和区域必须具有相同的尺寸，否则公式将返回#N/A错误。

7 结语

数组公式最大的优势是能进行一些其他方法无法进行的运算，但它是Excel中最难理解的特性之一，学习掌握困难，学习使用对象是掌握Excel基本操作，熟悉公式和函数使用的用户。如果与不熟悉数组公式的用户共享编写有数组公式的工作簿，而他需要进行一些修改又不清楚数组公式让人迷惑的特性时，使用它就会遇到麻烦。在此由于篇幅所限，笔者只能抛砖引玉，有兴趣者可参阅相关书籍和资料进一步学习。

图10 数组公式运算过程图解Fig10 Diagramofthealgorithmofthearrayformula

[1] Excel Home.Excel应用大全[M].北京：人民邮电出版社，2008.

[2] Excel Home之家.Excel实战技巧精粹[M].北京：人民邮电出版社，2007.

[3] Excel Home.Excel公式与函数实战技巧精粹[M].北京：人民邮电出版社，2008.

[4] (美)沃肯贝奇(Walkenbach,J.)著；杨艳，刘啟业，胡娟译.Excel 2007宝典[M].北京：人民邮电出版社，2008.