Excel 函数、VBA 及数据透视表在劳动工资分类统计中的应用

2021-04-15唐惠立

统计科学与实践 2021年8期

□ 唐惠立

使用sumproduct 函数进行多条件分类汇总

由于劳资工资报表涉及全部专业，为了更好地掌握各个行业的指标状况，往往需要对数据进行分行业、分地区汇总。然而，常用的汇总函数如count，sum，sumifs 等却无法满足分类汇总的需求，因此，我们需要借助于数组函数sumproduct完成此项任务。

（一）sumproduct 函数使用方法简介

sumproduct 函数返回相应范围（range）或数组（array）内各个元素的累加之和。该函数运行的原理为：首先对传入的参数数组中对应的元素进行指定的运算（默认为乘法），计算完毕后将形成一个与传入参数相同维数的结果数组，最后对该结果数组中的各项求和返回最终计算结果。

sumproduct 函数在对应的元素之间进行的默认操作是乘法，但也可以执行加减除运算。其默认格式为：

=SUMPRODUCT (array1，[array2]，[array3]，...)

值得注意的是，传入的参数需要都具有相同的维数。在执行其他运算时，需明确指定运算符号，将分隔数组参数的逗号替换为所需的算术运算符 (*、/、+、-)，如指定为加法时写作：

=SUMPRODUCT (数组1+数组2+数组3)

代表的意思是将数组1、数组2、数组3 对应的元素相加后，再对结果进行累加。其计算过程可用图1表示。

图1

此外，sumproduct 函数还可以较简单地添加分组条件，只需在数组参数后面加上必要的逻辑判断语句即可，逻辑运算返回的结果是以FALSE、TRUE 组成的、与传入的参数数组同维数的结果数组，此数组在参与四则运算时，Excel 会自动将FALSE、TRUE 转换为0、1，如=SU MPRODUCT(({7,2,3,9}＞0)*{1,1,1,1}) 最终返回的结果为4。利用该特性，我们可以非常方便地进行分行业汇总。

第一步，准备行业门类的起止代码和门类名称。按图2 方式在Excel表格中添加需要的门类信息。

图2

第二步，对基础表数据进行分类汇总。

假设基础表202-1 表S 列为行业代码所在列，K 列为我们需要汇总的工资总额数据列，则利用sumproduct函数，公式可编写如下：

=SUMPRODUCT ((‘202-1 表数据’! $S $2 :$S $65535 ＞=’汇总表’!$A 8)*(‘202-1 表数据’! $S $2 :$S $65535 ＜=’汇总表’!$B 8)*(‘202-1表数据’! $K $2 :$K $65535))

公式解析：

(‘202-1 表数据’! $S $2 :$S$65535 ＞=’汇总表’! $A 8) *(‘202-1 表数据’! $S $2 :$S $65535＜=’汇总表’!$B8) 基础表202-1 表S 列（行业代码列）大于该门类所在的起始值（$A8 单元格），小于该门类所在的终止值（$B8 单元格）。此部分公式意在筛选出符合本行业门类的企业。

(‘202-1 表数据’! $K $2 :$K$65535) K 列为202-1 基础表工资总额所在列，经过行业门类筛选的企业数据将与本列相乘，不符合本行业的企业数据全部自动计算为0，故最终返回结果为本行业门类的工资总额总和。

数据行标$K $2 :$K $65535 行标 65535 是一个习惯写法，只要该值大于202-1 表导出数据里的最大行数即可。

根据此方法，以此类推，还可以对202-1 表里的其他指标，如从业人数、平均工资等指标进行分类汇总。

二、利用日期函数计算报表所在季度

由于劳动工资报表的报表期横跨两个季度，在进行数据对比分析时，无法简单地通过系统记录时间对数据进行分季度操作。如果手动添加报表所在的季度文字列，不仅操作繁琐容易造成数据遗漏，且添加的文字信息不通用、难处理，二次利用十分繁琐。

例如在分析跨年度数据时，如果采用手动添加所在季度的基础数据，往往无法有效与Excel 内置的时间日期函数配合，造成不能满足灵活分析的窘境。因此，我们可以灵活利用Excel 内置的日期函数，对劳动工资报表的“初次上报时间”进行转换处理，从而可以在分析中较容易地对基础数据进行分年度分季度的汇总。

根据劳动工资报表制度，季报的上报时间一般为季末月的27 日至次月的8 日左右截止，可见，上报时间的月份虽集中在2 个月份，但却横跨2 个季度，我们可以简单地将“初次上报时间”月份减1，就可确保计算后的日期落在正确的季度内。因此，我们可以使用eomonth 函数完成此项工作，其语法如下：

EOMONTH(start_date,months)

图3

图4

该函数以start_date 指定的起始日期为基准，加减months 指定的月份数后，返回该日期所在月份的最后一天的日期值。

于是，我们可以新建一个“期别”列，用于存储计算后的期别日期，如图3 所示。

接下来我们可以借助其他函数计算该日期所在的季度，一种方法的公式如下：

=INT((MONTH(期别)+2)/3)

上述计算公式得出的结果即为所在季度数，如图4。

另外也可以使用TEXT 函数生成以中文显示的“第几季度”的格式：

=TEXT(ROUNDUP(MONTH(期别)/3,),”[dbnum1]第0季度”)

综合采用此种方法的优点是，得出的期别数据依旧保留了日期的格式，方便日后使用日期函数对其进行进一步分析利用，比如提取年份、使用数据透视表分析等操作。

利用VBA 自定义函数计算行业门类

在第一节的内容中，笔者介绍了使用固定行业分类和sumproduct函数的方法对数据进行分类汇总的操作，然而在其他的一些环境下，我们可能需要对单个企业进行行业门类的划分，固定行业分类的方法显然不能满足这项要求。因此，为了简单且快速地完成这项需求，我们可以借助VBA，编写自定义函数，使用自定义函数对数据的行业代码进行处理，从而计算出单个企业所在行业门类名称。

在Excel 中依次点击开发工具-Visual Basic，打开VBA 编辑器，点击插入-模块，新建一个名为Hydm2Name 的方法，该方法接收一个4 位国民经济行业分类代码的参数，以及一个可省略的用于控制返回格式的数字型参数，该函数的计算结果将返回相应的门类名称。

完整的代码如下：