【作　　者】
陈福军

【作者单位】
（山东理工大学商学院 山东淄博 255049）

【摘　　要】

      【摘要】在Excel中充分利用IF函数、VLOOKUP函数和SUMPRODUCT函数，构建基于动态变量可维护性的薪资核算系统，不仅可以高效地实现薪资的核算管理，而且有助于提高数据的安全性。
【关键词】薪资   Excel   动态维护   VLOOKUP函数   SUMPRODUCT函数

薪资核算是企业会计核算的重要内容，涉及工资费用分配、个人所得税代扣代缴、个人应交“三险一金”及企业应交“五险一金”的计算等。这些业务具有很强的规律性，因此可以充分利用Excel函数，通过建立薪资核算系统实现自动处理。
从薪资系统可维护性角度出发，应将那些对薪资计算公式具有影响性的变动因素（如专业技术职务等级、个人所得税政策等），尽可能地设置到基础数据表中，在薪资公式定义时通过VLOOKUP函数从基础数据表中动态查找所需结果，进而实现薪资数据的正确计算。当相关因素发生变动时，对基础数据表进行修改调整即可，而无需修改计算公式。
一、薪资核算系统基本框架设计
薪资核算系统主要由基础数据表、薪资数据库、工资费用分配表、薪资凭证等组成，其数据传递关系如图1所示。

1. 基础数据表设计。基础数据表主要由基础档案、工资标准、个税政策、人事档案、员工考勤等工作表构成，这些工作表中存储的是薪资计算的基础数据。各工作表的具体内容应根据单位工资计算的具体要求进行设置，以工资标准和人事档案为例，其结构设计如图2、图3所示。为便于公式定义时引用，可将相关数据区域定义为区域名称，如将“工资标准”工作表的C：D区域定义名称为“薪级工资标准”。
2. 薪资数据库设计。薪资数据库是薪资核算系统中薪资计算的主要工作表，它存储着工资计算的各种信息，是工资费用汇总统计和薪资凭证生成的依据。工资项目的设置既要满足员工工资发放的要求，还要满足薪资核算管理的要求，具体如图4所示（笔者对工资项目进行了简化）。
在薪资数据库设计过程中，可充分利用VLOOKUP函数实现数据的动态查找引用。VLOOKUP函数的基本语法格式为：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)。参数Lookup_value为需要在表格数组第一列中查找的数值；参数table_array为需要查找数据的数据区域，数据区域第一列为lookup_value搜索的值，必须以升序排序；参数col_index_num为table_array中待返回的匹配值的列序号；参数range_lookup为逻辑值，指定希望VLOOKUP查找的是精确的匹配值还是近似的匹配值，如果为TRUE或省略，则返回精确匹配值或近似匹配值，若找不到精确匹配值，则返回小于lookup_value的最大数值。如果range_lookup为FALSE，则返回精确匹配值，若找不到精确匹配值，则返回错误值“#N/A”。
以“基本工资”项目为例，在职员工基本工资取决于员工的工资薪级，员工工资薪级源于“人事档案表”工作表，而薪级工资标准则源于“工资标准”工作表，则“基本工资”项目计算公式可定义为“=IF(A2="","",IF(VLOOKUP(A2,人事档案,13,FALSE)="离职",0,VLOOKUP(VLOOKUP(A2,人事档案,12,FALSE),薪级工资标准,2,FALSE)))”。
3. 工资费用分配表设计。工资费用分配表是对工资费用的汇总统计，也为编制薪资凭证提供数据。实务中，工资费用分配是按月份、按员工类别进行分类汇总，形成不同的费用种类，记入相关会计科目。
在工资费用管理过程中，需要根据员工类别进行分配的内容主要包括应付工资、企业交纳的“五险一金”等。在Excel中，制作工资费用分配表，可按图5所示设计其结构。
工资费用表设计的关键在于各项工资数据的汇总计算公式的定义，其数据源于“薪资数据库”工作表，需要按月份和员工类别分类汇总统计。数据的分类汇总虽然可以利用Excel所提供的分类汇总功能实现，但其应用的前提是需要按分类汇总字段进行排序，这样容易破坏原数据表的结构。在薪资系统设计过程中，可以利用SUMPRODUCT函数实现工资数据的多字段分类汇总。
SUMPRODUCT函数用于在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和，其语法格式为：SUMPRODUCT(array1∗array2∗…)。参数Array1, array2, …为需要进行相乘并求和的数组元素，对于逻辑值TRUE取值为1，逻辑值FALSE取值为0。
以“工资费用汇总”项目为例，应按月度和员工类别进行汇总统计，相同月份、相同员工类别的职工工资数据汇总在一起，因而其计算公式可定义为：“=IF($E$2="",0,SUMPRODUCT((薪资数据库!$X$2:$X$154=MONTH($E$2))∗(薪资数据库!$D$2:$D$154=A4)∗(薪资数据库!$U$2:$U$154)))”。
4. 薪资凭证模板设计。薪资凭证是根据“薪资数据库”和“工资费用分配表”工作表生成的，其设计思路是：除凭证字号允许手工输入或调整外，其余信息（包括摘要信息）均应由公式判断生成。判断生成的方式应根据薪资凭证业务的选择，自动生成摘要信息、获取会计科目编码，根据会计科目编码获取会计科目名称，根据薪资凭证业务类型和员工类别汇总生成借、贷金额。由于薪资业务类型不同，凭证所包含的分录条数也不一致，少则两条，多则九条。凭证自动生成时，分录必须连续，不能出现空行。为实现上述要求，计算公式的设置必须充分依靠IF函数的判断功能。相关工资数据的汇总是以员工类别和工资月份为汇总依据，金额的生成除要依靠IF函数的判断功能外，还要依靠SUMPRODUCT函数实现按员工类别和工资月份对工资数据进行汇总。在Excel中，薪资凭证格式可按图6所示进行设计。
薪资凭证自动生成的第一判断要素为业务类型，根据业务类型判断分录会计科目，根据会计科目和业务类型汇总金额，因此，薪资凭证的生成除最主要的“薪资数据库”外，还需要提供会计科目表、薪资业务类型列表及直接人工费分配比例列表等辅助数据。对于“工资分摊业务类型”，可通过数据有效性功能进行设置，将数据有效性数据取值来源设置为“=$K$2:$K$13”。
在辅助数据设置的基础上，薪资凭证模板设置的关键在于定义薪资凭证的计算公式，特别是凭证借、贷方金额取数公式的定义是模板定义的重点，以图6所示F4单元借方金额计算公式为例，其计算公式可定义为：“=IF($J$2=$K$2,工资费用分配表!$E$4,IF($J$2=$K$3,SUMPRODUCT((薪资数据库!$AF$2:$AF$2556=MONTH($D$2))∗(薪资数据库!$V$2:$V$2556)),IF($J$2=$K$4,SUMPRODUCT((薪资数据库!$AF$2:$AF$2556=MONTH($D$2))∗(薪资数据库!$L$2:$L$2556)),IF($J$2=$K$5,SUMPRODUCT((薪资数据库!$AF$2:$AF$2556=MONTH($D$2))∗(薪资数据库!$N$2:$N$2556)),IF($J$2=$K$6,SUMPRODUCT((薪资数据库!$AF$2:$AF$2556=MONTH($D$2))∗(薪资数据库!$P$2:$P$2556)),IF($J$2=$K$7,SUMPRODUCT((薪资数据库!$AF$2:$AF$2556=MONTH($D$2))∗(薪资数据库!$R$2:$R$2556)),0))))))+IF($J$2=$K$8,工资费用分配表!$F$4,IF($J$2=$K$9,工资费用分配表!$G$4,IF($J$2=$K$10,工资费用分配表!$H$4,IF($J$2=$K$11,工资费用分配表!$I$4,IF($J$2=$K$12,工资费用分配表!$J$4,IF($J$2=$K$13,工资费用分配表!$K$4,0))))))”。
二、薪资核算数据处理
每月薪资核算的处理为：①打开基础数据表，录入变动数据或调整数据，如员工考勤；②打开“薪资数据库”工作表，录入工资月份值，计算生成相关月份每一员工的工资数据；③打开“工资费用分配表”工作表，录入工资费用汇总日期，生成相应月份工资费用分配表；④打开“薪资凭证”工作表，选择“工资分摊业务类型”，生成相应业务类型的薪资凭证。在薪资数据处理时，由于月度值参与薪资数据的分类汇总统计，因此必须保证月度值录入的正确性，特别是“薪资数据库”工作表中每一员工工资数据的月度值的输入正确尤为关键。
主要参考文献
      陈福军.适用于税政变动的Excel个人所得税计算模型.财会月刊，2013；11