工资结算前交叉核对工资表的 3 个 Excel 公式
使用 3 个 Excel 公式核对考勤、人员名单与会计分录,标记重复或缺失的工资行,并在发放工资条前定位差异。
仅通过查看页面底部的合计来确定工资表,很容易遗漏差异,因为缺失的一行可能会被重复计算的一行所掩盖。以下三项交叉检查可在发放工资单之前,帮助核查从考勤数据、人员名单到会计分录的各项内容。每项检查都会返回一个差异数值或一个警告标签,因此薪资核算人员无需用肉眼逐个单元格查找。应在数据副本上执行这些检查,并保留原始文件不变,以便需要时追溯。
设置公式前准备数据
需要使用的三张表是工资表、考勤表和分录表。三者必须属于同一薪资周期、覆盖相同的人员范围,并采用统一的员工编号。不应按姓名核对,因为重名、多余空格或书写方式变化都可能产生错误结果。
- 将员工编号统一为相同的数据类型,去除首尾空格;数据区域内不要使用合并单元格。
- 明确区分出勤工资、津贴、奖金、各项扣减和实发工资;不要将这些指标混在一列中。
- 从计算区域中剔除合计行、重复标题行和草稿分录;在单独的引用单元格中明确注明薪资周期。
如果一名员工在同一周期内有多份合同、多个职位或多个工资支付来源,需要事先确定合适的核对键。此时,仅使用员工编号并不足够;应结合合同编号或薪资项目编号,以避免误报重复。
公式一:将工资总额与考勤表核对
工资总额不能直接与总出勤天数相减。首先要核对的是每一行用于计算工资的出勤天数。在工资表中添加“已核对出勤天数”列,并使用条件求和函数从考勤表中获取数据:
=SUMIFS(ChamCong[CongHuongLuong];ChamCong[MaNV];[@MaNV];ChamCong[Ky];KyLuong)
然后创建“出勤天数差异”列,用工资表中的出勤天数减去刚获取的出勤天数。只有结果为 0 的行,才继续检查出勤工资。对于按标准出勤天数计算工资的情况,可以添加以下公式:
=ROUND([@LuongTheoCong]-[@LuongCoBan]/[@CongChuan]*[@CongDaDoiChieu];2)
结果不为 0,通常与标准出勤天数错误、基本工资尚未更新、带薪休假天数被遗漏或公式取错周期有关。加班、夜班和按小时计算的项目应单独检查,不要并入按日统计的出勤天数。当每一行都核对一致后,再汇总出勤工资、津贴、奖金和扣减各列,与工资总额核对;这种方法有助于确定差异来自出勤天数,还是来自某个具体金额项目。
公式二:用计数函数查找重复行和缺失行
要查找工资表中的重复行,请添加一个检查列,并将员工编号和薪资周期作为条件:
=IF(COUNTIFS(BangLuong[MaNV];[@MaNV];BangLuong[Ky];KyLuong)>1;”重复检查”;””)
不要立即删除被标记的行。首先需要判断这是重复录入错误,还是合理情况,例如同一员工有两笔独立薪资。如果业务规则允许多行记录,应在计数函数的两个条件中都加入薪资项目编号。
要查找缺失行,应将公式设置在需要支付薪资的人员名单或本周期有出勤记录的人员名单上,而不是直接设置在工资表上:
=IF(COUNTIFS(BangLuong[MaNV];[@MaNV];BangLuong[Ky];KyLuong)=0;”工资表缺失”;”已有”)
还需要按相反方向再执行一项检查:逐一取工资表中的员工编号,在人员名单中计数。这个方向可以发现已离职、编号错误或不属于本周期范围但仍出现在工资表中的人员。在得出结论前,应剔除空白行,并检查允许没有工资的情况,例如整个周期均为无薪休假。
公式三:将工资表合计与分录合计核对
最后一项核对必须在两侧采用同一指标。如果分录确认的是应付工资,就应与工资表的应付工资总额核对;如果分录尚未扣除保险、税款和其他扣款,则不要采用实发工资。还应选择正确的工资确认分录,不要将付款分录或无关的调整分录一并计入。
=ROUND(SUM(BangLuong[LuongPhaiTra])-SUMIFS(ButToan[SoTien];ButToan[Ky];KyLuong;ButToan[MaNghiepVu];”GhiNhanLuong”;ButToan[TaiKhoan];”334″);2)
结果为 0 表示两侧合计在所选范围内一致。如果不为 0,应依次检查会计期间、入账状态、人员名单、冲销分录和舍入方式。当分录按部门拆分时,应先逐部门核对,再进行汇总;一笔金额即使记错部门,全公司的合计仍可能一致,但各部门的费用却会出错。
应立即执行的事项
- 添加三个检查列:出勤天数差异、重复行和缺失行。
- 设置一个差异单元格,用于计算工资表合计与同一指标下分录合计之间的差异。
- 只有在所有警告均已解释清楚并保存调整记录后,才能最终确认。
应将这三个公式固定保存为可重复使用的模板,并附上核对范围和指标的说明。VASCOM 建议同时保存检查结果和数据修改日志,以便下一周期快速复核,而无需从头重建流程。