动态变量可维护的Excel薪资核算管理系统

□财会月刊·全国优秀经济期刊

动态变量可维护的Excel薪资核算管理系统

陈福军

（山东理工大学商学院山东淄博255049）

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

【关键词】薪资Excel

动态维护VLOOKUP函数SUMPRODUCT函数

1.基础数据表设计。基础数据表主要由基础档案、工资标准、个税政策、人事档案、员工考勤等工作表构成，这些工作表中存储的是薪资计算的基础数据。各工作表的具体内容应根据单位工资计算的具体要求进行设置，以工资标准和人事档案为例，其结构设计如图2、图3所示。为便于公式定义时引用，可将相关数据区域定义为区域名称，如将“工资标准”工作表的C：D区域定义名称为“薪级工资标准”。

2.薪资数据库设计。薪资数据库是薪资核算系统中薪资计算的主要工作表，它存储着工资计算的各种信息，是工资费用汇总统计和薪资凭证生成的依据。工资项目的设置既要满足员工工资发放的要求，还要满足薪资核算管理的要求，具体如图4所示（笔者对工资项目进行了简化）。

在薪资数据库设计过程中，可充分利用VLOOKUP函数实现数据的动态查找引用。VLOOKUP函数的基本语法格式为：

薪资核算是企业会计核算的重要内容，涉及工资费用分配、个人所得税代扣代缴、个人应交“三险一金”及企业应交“五险一金”的计算等。这些业务具有很强的规律性，因此可以充分利用Excel函数，通过建立薪资核算系统实现自动处理。

从薪资系统可维护性角度出发，应将那些对薪资计算公式具有影响性的变动因素（如专业技术职务等级、个人所得税政策等），尽可能地设置到基础数据表中，在薪资公式定义时通过VLOOKUP函数从基础数据表中动态查找所需结果，进而实现薪资数据的正确计算。当相关因素发生变动时，对基础数据表进行修改调整即可，而无需修改计算公式。

一、薪资核算系统基本框架设计

薪资核算系统主要由基础数据表、薪资数据库、工资费

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)。参数Lookup_value为需要在表格数组第一列中查找的数值；参数table_array为需要查找数据的数据区域，数据区域第一

列为lookup_value搜索的值，必须以升序排序；参数col_index_num为table_ar ray中待返回的匹配值的列序号；参数range_lookup为逻辑值，指定希望VLOOKUP查找的是精确

图1薪资核算数据传递关系

图2工资标准表

□·70·2013.12上

全国中文核心期刊·财会月刊□

图4

的匹配值还是近似的匹配值，如果为TRUE或省略，则返回精确匹配值或近似匹配值，若找不到精确匹配值，则返回小于look up_value的最大数值。如果range_lookup为FALSE，则返回精确匹配值，若找不到精确匹配值，则返回错误值“#N/A”。

以“基本工资”项目为例，在职员工基本工资取决于员工的工资薪级，员工工资薪级源于“人事档案表”工作表，而薪级工资标准则源于“工资标准”工作表，则“基本工资”项目计算公式可定义为“=IF(A2="","",IF(VLOOKUP(A2,人事档案,13,FALSE)="离职",0,VLOOKUP(VLOOKUP(A2,人事档案,12,

薪资数据库表

图5

工资费用分配表

UCT((薪资数据库!$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((薪资数据

FALSE),薪级工资标准,2,FALSE)))”。

3.工资费用分配表设计。工资费用分配表是对工资费用的汇总统计，也为编制薪资凭证提供数据。实务中，工资费用分配是按月份、按员工类别进行分类汇总，形成不同的费用种类，记入相关会计科目。

在工资费用管理过程中，需要根据员工类别进行分配的内容主要包括应付工资、企业交纳的“五险一金”等。在Excel中，制作工资费用分配表，可按图5所示设计其结构。

工资费用表设计的关键在于各项工资数据的汇总计算公式的定义，其数据源于“薪资数据库”工作表，需要按月份和员工类别分类汇总统计。数据的分类汇总虽然可以利用Excel所提供的分类汇总功能实现，但其应用的前提是需要按分类汇总字段进行排序，这样容易破坏原数据表的结构。在薪资系统设计过程中，可以利用SUMPRODUCT函数实现工资数据的多字段分类汇总。

SUMPRODUCT函数用于在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和，其语法格式为：SUM PRODUCT(array1 array2 …)。参数Array1,array2,…为需要进行相乘并求和的数组元素，对于逻辑值TRUE取值为1，逻辑值FALSE取值为0。

以“工资费用汇总”项目为例，应按月度和员工类别进行汇总统计，相同月份、相同员工类别的职工工资数据汇总在一起，因而其计算公式可定义为：“=IF($E$2="",0,SUMPROD

2013.12上·71·□

□财会月刊·全国优秀经济期刊

基于Excel的供应商欠款单设计

韩福才

（商丘工学院管理学院河南商丘476000）

【摘要】对于应付账款业务，通常会比较麻烦。通过利用Excel完善应付账款的手续，设置对供应商的欠款单，能够很好地解决此类问题。欠款单的设置，属于企业内部控制的一部分，在企业的实际经营活动中能够起到协调和监督的作用。

【关键词】应付账款入账手续欠款单一、应付账款入账手续存在的问题

企业因购买材料、商品和接受劳务等经营活动发生的业务，可以根据存货的采购合同、过磅单、验收单、质检单、入库单、付款单、采购发票等审核无误且手续齐全后进行账务处理。在实际会计工作中，如果涉及应付账款业务，会计人员在进行账务处理的过程中可能会感到很棘手，例如，已经预 …… 此处隐藏：2838字，全部文档内容请下载后查看。喜欢就下载吧 ……