统计工作中几个EXCEL常用函数的使用体会

统计工作中几个EXCEL常用函数的使用体会
摘要：在基层统计的日常工作中，经常遇到加权求和的计算，如在计算企业能源消费量或工业产值时，还经常需要在会计报表或其他统计表中查找满足条件的数据，为提高基层统计工作效率，简化统计数据的计算流程，本文简单阐述如何使用SUMPRODUCT、HLOOKUP和VLOOKUP函数，提高工作效率的使用心得。

关键词：excel 函数与公式能源消费统计加权求和查询
Excel是微软办公套装软件的一个重要的组成部分，它可以进行各种数据的处理、统计分析和辅助决策操作，广泛地应用于管理、统计财经、金融等众多领域。

在日常统计工作中，它是基层统计几乎每天都会用到的办公软件，Excel有大量的公式函数可以应用选择，能实现许多方便的功能，给使用者方便。

通过这些年的统计工作经历,我认为以下这几个较为常用的函数的使用，对提高统计工作效率很有帮助。

使用SUMPRODUCT函数进行加权求和
在日常统计工作中，有许多总量指标需要加权求和，如：能源消费总量、销售额等，使用SUMPRODUCT函数可以很快实现。

函数简介
SUMPRODUCT(array1,array2,array3,...)函数定义为“返回相应的数组或区域的乘积的和”。

参数：Array1,array2,array3,...为2到30个数组，其相应元素需要进行相乘并求和。

使用实例
指定区域求和
公式：=SUMPRODUCT(A2:B4,C2:D4)
结果：两个数组的所有元素对应相乘，然后把乘积相加，
即3*2+4*7+8*6+6*7+1*5+9*3=156
满足条件的区域求和
＝SUMPRODUCT((条件1)*(条件2)*(条件3)*…(条件n)*某区域)
作用：汇总同时满足条件1、条件2到条件n的记录指定区域的汇总金额。

例如：如果需要“对下表中“男性”“中级”职称的工资求和”则有：
公式：=SUMPRODUCT((B2:B8=“男”)*(C2:C8=“中级”)*D2:D8)
结果：统计性别为男性且职称为中级职称的职工的工资总和，结果为：90元。

数组求和——计算企业能源消费量
=SUMPRODUCT(C2:C6,D2:D6)
结果：
=9927*0.7143+562453*0.0341+2049*1.229+12*1.4714+95*1.4571
=28944.806吨标煤
注意事项
数组参数必须具有相同的维数，否则，函数SUMPRODUCT将返回错误值
#V ALUE!。

SUMPRODUCT函数将非数值型的数组元素作为0处理。

在SUMPRODUCT中，2003及以下版本不支持整列（行）引用，必须指明范围，不可在SUMPRODUCT函数使用A:A、B:B，Excel2007及以上版本可以整列（列）引用，但并不建议如此使用，公式计算速度慢。

SUMPRODUCT函数不支持“*”和“？”通配符
SUMPRODUCT函数多条件求和时使用“，”和“*”的区别：当拟求和的区域中无文本时两者无区别，当有文本时，使用“*”时会出错，返回错误值#V ALUE!，而使用“，”时SUMPRODUCT函数会将非数值型的数组元素作为0处理，故不会报错。

使用HLOOKUP和VLOOKUP进行数据搜索引用
在日常统计工作中，经常需要从财务报表或别的统计表中提取数据，用手工录入，当需要引用的数据量很大时，有时会出现录入错误的情况，而HLOOKUP 和VLOOKUP这两个函数都是具有查找功能的函数，使用后可以大大提高工作效率。

这两个函数的区别是“按行”或“按列”查找，HLOOKUP中的H代表“行”。

现以HLOOKUP为例进行说明。

函数简介
HLOOKUP(lookupvalue，tablearray，rowindexnum，rangelookup)，其定义是“在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值。

”
Lookupvalue为需要在数据表第一行中进行查找的数值。

可以为数值、引用或文本字符串。

Tablearray为需要在其中查找数据的数据表。

可以使用对区域或区域名称的引用。

Tablearray的第一行的数值可以为文本、数字或逻辑值。

Rowindexnum为tablearray中待返回的匹配值的行序号。

Rangelookup为一逻辑值，指明函数HLOOKUP查找时是精确匹配，还是近似匹配。

使用实例：在查找符合条件的数值
：公式：=HLOOKUP(“成本”,A1:C4,2,TRUE)
结果：在首行查找“成本”，并返回同列中第2行的值，即“150”。

：公式：=HLOOKUP(“费用”,A1:C4,3,FALSE)。

结果：在首行查找“费用”，并返回同列中第3行的值，即“95”
：=HLO OKUP(“利润”,A1:C4,4)
结果：在首行查找“利润”，并返回同列中第4行的值，即“60”。

注意事项
如果range_lookup为TRUE，则table_array的第一行的数值必须按升序排列：...-2、-1、0、1、2、…、A-Z、FALSE、TRUE；否则，函数HLOOKUP将不能给出正确的数值。

如果range_lookup为FALSE，则table_array不必进行排序。

如果为TRUE或省略，则返回近似匹配值。

也就是说，如果找不到精确匹配值，则返回小于lookupvalue的最大数值。

如果rangevalue为FALSE，函数HLOOKUP将查找精确匹配值，如果找不到，则返回错误值#N/A!
如果函数HLOOKUP小于tablearray第一行中的最小数值，函数HLOOKUP 返回错误值#N/A!。

使用MATCH函数进行数据定位
函数简介
MATCH(lookupvalue，lookuparray，matchtype)，其定义为“返回在指定方式下与指定数值匹配的数组中元素的相应位置。

”
Lookupvalue为需要在数据表中查找的数值，可以为数值（数字、文本或逻辑值）或对数字、文本或逻辑值的单元格引用。

Lookuparray可能包含所要查找的数值的连续单元格区域。

Lookuparray应为数组或数组引用。

使用实例：定位符合条件的对象
因为日常统计工作中的多数是需要准确定位，因此，在这里我们着重说一说“matchtype为0”情况下的定位，如果函数MATCH查找不成功，则返回错误值#N/A。

公式：=MATCH(“乙”,A2:A5,0)
结果：在数据区域A2:A5中查找“乙”单位的利润的位置。

结果为“2”，即乙单位的利润在数据区域的第二行。

2）公式=MATCH(“戊”,A2:A5,0)
结果：在数据区域A2:A5中查找“戊”单位的利润的位置。

结果为“#N/A”，即没有找到戊单位，所以返回错误值#N/A。

应用拓展：
组合使用MATCH和HLOOKUP函数，在过录表中自动抽取数据，减少人为查找的误差。

第一步：B2单元格的公式为：=MATCH(B1,合并利润表过录表!A3:A55,0)
结果为：在合并利润过录表的A3:A55区域中，查找“利润总额本年实际累计数”这一指标的位置，返回结果为“37”，即该指标在第37行。

第二步：B3单元格的公式为：=HLOOKUP(A3,合并利润表过录表!A3:AE55, B2,FALSE)
结果为：在合并利润过录表的A3:AE55区域中，返回“第一子公司”的“利润总额本年实际累计数”这一指标的数据，返回结果为“62.11”，即第一子公司本年的利润总额为62.11万元。

注意事项
查找文本值时，函数MATCH不区分大小写字母。

如果matchtype为0且lookupvalue为文本，lookupvalue可以包含通配符、星号(*)和问号(?)。

星号可以匹配任何字符序列；问号可以匹配单个字符。

结束语：
以上这几个函数公式是在日常统计工作中经常会用到，合理的使用会减少人为因素产生的误差，对日常统计工作帮助很大。

目前，对Excel软件已经与基层统计工作紧密相连，已经深入到日常工作中的方方面面，熟练使用Excel中的公式和函数，将会大大提高基层统计的工作效率。

参考文献：
1、《中文版Excel2003电子表格实用教程》，王斌，清华大学出版社(2009-04)
2、《Excel公式与函数逆引大全》，程慧杰科学出版社(2011-05)
3、《Excel2003实例与操作》，郭燕，航空工业出版社（2010-8-1）。