Excel在工资核算中的应用

1、Excel在工资核算中应用培训师：汪伟Page 1一、工资核算n 建立建立职工基本信息表职工基本信息表薪酬标准信息表薪酬标准信息表职工考勤信息表职工考勤信息表职工工资信息表职工工资信息表工资查询与汇总工资查询与汇总Page 2建立职工基本信息表职工基本信息表打开打开ExcelExcel，并创建新工作簿文件，并创建新工作簿文件，命名为命名为“工资处理工资处理”；操作步骤选择工作表选择工作表 sheet1sheet1名，将其改名名，将其改名为为“职工基本信息表职工基本信息表“，并在，并在A1A1单元格输入单元格输入“职工基本信息表职工基本信息表”；在第二行的在第二行的 A2A2、B2B2、C2C2

2、等单元格等单元格分别输入分别输入“职工编号职工编号” 、“姓姓名名”、“性别性别”、“部门部门”、“职工类别职工类别”、“职务职务”、“职职称称”、“工龄工龄” ；Page 3建立职工基本信息表职工基本信息表将将“职工基本信息表职工基本信息表”合并居中合并居中操作步骤为为“性别性别”、“部门部门”、“职工职工类别类别”、“职务职务”、“职称职称”等等分别建立数据有效性，并在该列分别建立数据有效性，并在该列复制；复制；输入职工基本信息数据。输入职工基本信息数据。职工基本信息表见下图职工基本信息表见下图Page 4职工基本信息表职工基本信息表n 此数据一般由人力资源管理部门提供给财务部门。此数据一

3、般由人力资源管理部门提供给财务部门。Page 5建立薪酬标准信息表建立薪酬标准信息表操作步骤选择工作表选择工作表 sheet2sheet2名，将其改名名，将其改名为为“薪酬标准信息表薪酬标准信息表“，并在，并在A1A1单元格输入单元格输入“薪酬标准薪酬标准信息表信息表”；设立设立 扣款计算标准扣款计算标准设立设立 计税计算标准计税计算标准设立设立 岗位类别标准岗位类别标准设立设立 职称类别标准职称类别标准/ /职务类别等职务类别等Page 6薪酬标准信息表薪酬标准信息表Page 7建立职工考勤信息表建立职工考勤信息表选择工作表选择工作表 sheet3sheet3名名, ,命名为命名为“职工考勤

4、信息表职工考勤信息表”操作步骤在在A1A1单元格输入单元格输入“职工考勤信息职工考勤信息表表”；在第二行的在第二行的 A2A2、B2B2、C2C2等单元格等单元格分别输入分别输入“职工编号职工编号” 、“姓姓名名”、 “部门部门”; ;然后是当月的然后是当月的日期与星期；最后是考勤统计。日期与星期；最后是考勤统计。Page 8职工考勤信息表职工考勤信息表n考勤信息考勤信息一般又企一般又企业内各部业内各部门记录门记录,报报人力资源人力资源部门核实部门核实汇总后汇总后,在在交给财务交给财务部门作为部门作为核算职工核算职工薪酬的依薪酬的依据据.Page 9建立固定工资信息表建立固定工资信息表插入一张

5、工作表插入一张工作表, ,命名为命名为“固定工资信固定工资信息表息表”操作步骤在在A1A1单元格输入单元格输入“固定工资信息表固定工资信息表”；在第二行的在第二行的 A2A2、B2B2、C2C2等单元格分别等单元格分别输入输入“职工编号职工编号” 、“姓名姓名”、 “部部门门”; ;然后是职务、职称、基本工资、然后是职务、职称、基本工资、岗位津贴、职务津贴、职称津贴、工龄岗位津贴、职务津贴、职称津贴、工龄工资等。工资等。设置固定项目的数据引用和计算公式设置固定项目的数据引用和计算公式。Page 10Page 11建立变动工资信息表建立变动工资信息表插入一张工作表插入一张工作表, ,命名为命名为

6、“变动工资信息变动工资信息表表”操作步骤在在A1A1单元格输入单元格输入“变动工资信息表变动工资信息表”；在第二行的在第二行的 A2A2、B2B2、C2C2等单元格分别等单元格分别输入输入“职工编号职工编号” 、“姓名姓名”、 “部部门门”; ;然后是然后是奖金、加班费、病假、事奖金、加班费、病假、事假、旷工、罚款等假、旷工、罚款等。设置变动项目的数据引用和计算公式、设置变动项目的数据引用和计算公式、如如加班费、病假、事假、旷工，输入某加班费、病假、事假、旷工，输入某些数据等。些数据等。Page 12Page 13n 固定工资项目的数据，一般是由人力资源管理部门来提供的。其变动，固定工资项目的

7、数据，一般是由人力资源管理部门来提供的。其变动，也是由人力资源管理部门根据公司的有关规定来调整。也是由人力资源管理部门根据公司的有关规定来调整。n 相比之下，变动工资项目则是由各部门提供考勤、考核记录，人力资相比之下，变动工资项目则是由各部门提供考勤、考核记录，人力资源管理部门核实后，再由财务部门根据有关规定进行计算。如奖金是源管理部门核实后，再由财务部门根据有关规定进行计算。如奖金是根据各部门的记录和标准计算、加班费根据加班记录和加班费标准计根据各部门的记录和标准计算、加班费根据加班记录和加班费标准计算、扣款是根据考勤记录和扣款标准计算等。算、扣款是根据考勤记录和扣款标准计算等。Page 1

8、4建立职工工资明细表建立职工工资明细表插入一张工作表插入一张工作表, ,命名为命名为“职工工资明职工工资明细表细表”操作步骤在在A1A1单元格输入单元格输入“职工工资明细表职工工资明细表”；在第二行的在第二行的 A2A2、B2B2、C2C2等单元格分别等单元格分别输入输入“职工编号职工编号” 、“姓名姓名”、 “部门部门”; ;然后是基本工资、岗位工资、然后是基本工资、岗位工资、职务工资、职称工资、工龄工资、奖职务工资、职称工资、工龄工资、奖金、加班费、病事假金、加班费、病事假/ /旷工、罚款、应旷工、罚款、应发工资、个人所得税、实发工资。发工资、个人所得税、实发工资。设置固定项目的数据引用和

9、计算公式设置固定项目的数据引用和计算公式。Page 15Page 16Page 17自动生成工资条n 在工资明细表的基础上，通过宏编程，实现自动生成工资条，进行打在工资明细表的基础上，通过宏编程，实现自动生成工资条，进行打印。印。n 也可以增加一个宏，对生成的宏进行删除，将其还原为工资明细表。也可以增加一个宏，对生成的宏进行删除，将其还原为工资明细表。n 具体操作见具体操作见宏与宏与VBA编程编程的介绍的介绍Page 18n 23、清除单元格n ActiveCell.Clear 删除所有文字、批注、格式删除所有文字、批注、格式n 24、返回选定区域的行数n MsgBox Selection.R

10、ows.Countn 25、返回选定区域的列数n MsgBox Selection.Columns.Countn 26、返回选定区域的地址n Selection.Addressn 27、忽略所有的错误n ON ERROR RESUME NEXT Page 19n 28、遇错跳转n on error goto err_handle中间的其他代码中间的其他代码err_handle: 标签标签跳转后的代码跳转后的代码n 29、删除一个文件n kill c:1.txtn 30、定制自己的状态栏n Application.StatusBar = 现在时刻现在时刻: & Time Page 20二

11、、固定资产核算固定资产概述固定资产概述 n 1固定资产，是指同时具有下列特征的有形资产：固定资产，是指同时具有下列特征的有形资产：为生产商品、提供劳务、出租或经营管理而持有的（持有目为生产商品、提供劳务、出租或经营管理而持有的（持有目的）；的）；使用寿命超过一个会计年度。使用寿命超过一个会计年度。 n固定资产同时满足下列条件的，才能予以确认：固定资产同时满足下列条件的，才能予以确认：n与该固定资产有关的经济利益很可能流入企业；与该固定资产有关的经济利益很可能流入企业；n该固定资产的成本能够可靠地计量。该固定资产的成本能够可靠地计量。 Page 21n 2固定资产固定资产计量n 初始计量 ：n外

12、购固定资产外购固定资产 n自行建造固定资产自行建造固定资产 n融资租入固定资产融资租入固定资产 n 后续计量n固定资产折旧固定资产折旧 n折旧方法包括年限平均法、工作量法、双倍余额递减法和年数总和法折旧方法包括年限平均法、工作量法、双倍余额递减法和年数总和法 n固定资产后续支出固定资产后续支出 Page 22n3固定资产处置n固定资产出售、报废或毁损固定资产出售、报废或毁损 n其他方式减少的固定资产其他方式减少的固定资产 n4固定资产分类分类企业的固定资产主要划分为三大类：企业的固定资产主要划分为三大类：n一是房屋建筑物（包括地下的各种水、电、气管道设施、厂区道路、绿一是房屋建筑物（包括地下的

13、各种水、电、气管道设施、厂区道路、绿化工程等）；化工程等）；n二是机器设备；二是机器设备；n三是运输起重设备，包括各种汽车、叉车、挖掘机、装载机、起重设备，三是运输起重设备，包括各种汽车、叉车、挖掘机、装载机、起重设备，电器设备、电子设备以及大型办公家私用品等电器设备、电子设备以及大型办公家私用品等Page 23n 固定资产核算图固定资产核算图图一Page 24图二Page 25固定资产业务流程图固定资产业务流程图 图一图一Page 26图一续图一续Page 27建立固定资产清单（卡片）建立固定资产清单（卡片）n 固定资产清单固定资产清单n固定资产卡片是指按照固定资产的项目开设，用以进行固定资

14、产明细核固定资产卡片是指按照固定资产的项目开设，用以进行固定资产明细核算的账簿。是固定资产管理中的基础数据，当企业取得固定资产时，就算的账簿。是固定资产管理中的基础数据，当企业取得固定资产时，就需要对本企业的固定资产卡片进行录入操作。需要对本企业的固定资产卡片进行录入操作。n 启动启动Microsoft Excel，创建一个新的工作簿文件，将其另存为：，创建一个新的工作簿文件，将其另存为：固固定资产核算定资产核算.xls.xls。n 1建立建立固定资产固定资产“项目设置表项目设置表”， 设置固定资产清单（卡片）中需要的有关数据。主要有：设备类别、设置固定资产清单（卡片）中需要的有关数据。主要有

15、：设备类别、部门名称、增加方式、部门名称、增加方式、使用状况等使用状况等.Page 28n部门名称: 厂部、财务处、结算中心、人事处、计划处,销售处、金工车间、结构车间、机装车间n增加方式: 直接购入、在建工程转入、投资者投入、捐赠、盘盈、部门调拨、其他n减少方式: 对外投资、出售、报废、无偿调出n使用状况: 在用、停用、季节性停用n设备类别:Page 29 房屋、生产设备、工具器具、运输工具、办公设备n 2建立建立固定资产清单固定资产清单n设置固定资产清单中的数据项目：设置固定资产清单中的数据项目： 卡片编号、编号、名称、规格型号、类别、增加方式、减少方式、使用状卡片编号、编号、名称、规格型

16、号、类别、增加方式、减少方式、使用状态、部门、当前日期、启用日期、已计提折旧月份、已计提折旧年份、态、部门、当前日期、启用日期、已计提折旧月份、已计提折旧年份、预计使用年份、净残值率、预计工作量、月工作量、原值、累计折旧、预计使用年份、净残值率、预计工作量、月工作量、原值、累计折旧、净值等。净值等。Page 30n设置数据有效性设置数据有效性 定义项目信息的名称；定义项目信息的名称； 设置项目信息的有效性（部门名称、增加方式、减少方式、使用状况、设设置项目信息的有效性（部门名称、增加方式、减少方式、使用状况、设备类别）备类别）n录入数据录入数据 方式：记录单、直接录入、可视化窗口方式：记录单、

17、直接录入、可视化窗口n计算固定资产净值计算固定资产净值 固定资产净值固定资产净值=原值原值-累计折旧累计折旧 Page 31固定资产变动n 1固定资产增加固定资产增加n直接输入新增加的固定资产的有关数据直接输入新增加的固定资产的有关数据n 2固定资产调拨固定资产调拨n直接输入内部调拨的固定资产的有关数据直接输入内部调拨的固定资产的有关数据n 3固定资产减少固定资产减少n直接输入减少的固定资产的有关数据直接输入减少的固定资产的有关数据Page 32固定资产折旧的计提n 固定资产折旧固定资产折旧 固定资产折旧是指企业的固定资产在使用过程中，通过损耗而逐渐转固定资产折旧是指企业的固定资产在使用过程中

18、，通过损耗而逐渐转移到产品成本或商品流通费的那部分价值。移到产品成本或商品流通费的那部分价值。n为了保证企业将来有能力重置固定资产，把固定资产的成本分配到各个为了保证企业将来有能力重置固定资产，把固定资产的成本分配到各个受益期，实现期间收入与费用的正确配比（企业必须在固定资产的有效受益期，实现期间收入与费用的正确配比（企业必须在固定资产的有效使用年限内，计提一定数额的折旧费）。使用年限内，计提一定数额的折旧费）。Page 33n 1折旧函数介绍折旧函数介绍Excel中的折旧函数主要有：中的折旧函数主要有：n平均年限法：平均年限法： SLN（cost , salvage , life )n年数总

19、和法：年数总和法： SYD（cost,salvage,life,per)n双倍余额递减法：双倍余额递减法：DDB（cost,salvage,life,period,factor)nCost: 固定资产原值nSalvage: 固定资产预计净残值nLife: 折旧期限nPeriod: 需要计算折旧的期间Page 34nFactor: Factor: 余额递减速率，如缺省，默认值为余额递减速率，如缺省，默认值为2 2。 nPer: 表示期间，期单位与表示期间，期单位与life相同相同n 双倍余额递减法 双倍余额递减法也是一种加速折旧的方法，它是用直线法的双倍百分双倍余额递减法也是一种加速折旧的方法，

20、它是用直线法的双倍百分比，逐期乘以该期期初固定资产账面余额，作为该期的折旧额，直到比，逐期乘以该期期初固定资产账面余额，作为该期的折旧额，直到折旧额小于按直线计算的折旧额为止。折旧额小于按直线计算的折旧额为止。 年折旧额年折旧额 = 期初固定资产账面余额期初固定资产账面余额 * 双倍直线折旧率双倍直线折旧率Page 35 双倍直线折旧率双倍直线折旧率 = 2预计使用年限预计使用年限 * 100%n双倍余额递减法函数双倍余额递减法函数DDB( ) DDB(cost,salvage，life,period,factor)功能：功能： 返回固定资产在某期间返回固定资产在某期间( period )的折

21、旧数额。折旧数额是根据资产的原的折旧数额。折旧数额是根据资产的原始成本始成本( cost )、预计使用年限、预计使用年限( life )、预计净残值、预计净残值( salvage )及递减速及递减速率率( factor ),按倍率递减法计算而得出的。按倍率递减法计算而得出的。DDB函数所有参数均需为正函数所有参数均需为正 Page 36n DDB（资产原值，残值，使用年限（资产原值，残值，使用年限,折旧期折旧期,递减速率）递减速率）n 例例 假设某一公司购买自动化生产设备的成本为假设某一公司购买自动化生产设备的成本为1500万元万元,使用年限为使用年限为8年年,残值为残值为250万元万元,请计

22、算如下几年的折旧。请计算如下几年的折旧。n第一年第一年=DDB（1500，250，8*365，1）n第二年第二年=DDB（1500，250，8，2）n第第5个月个月=DDB（1500，250，8*12，5，3）Page 37n 年数总和法 年数总和法是一种加速折旧方法，它以固定资产的原始成本减去预计年数总和法是一种加速折旧方法，它以固定资产的原始成本减去预计残值后的余额乘以一个逐年递减的分数，作为该期的折旧额。这个分残值后的余额乘以一个逐年递减的分数，作为该期的折旧额。这个分数的分母是固定资产使用年限的各年年数之和，分子是固定资产尚可数的分母是固定资产使用年限的各年年数之和，分子是固定资产尚可

23、使用年限。使用年限。n年数总和法函数年数总和法函数SYD( )功能：功能： 返回某项固定资产某期间的按年数总和法计算的折旧数额。所有参数都应返回某项固定资产某期间的按年数总和法计算的折旧数额。所有参数都应为正数，否则将返回错误值为正数，否则将返回错误值#NUM!Page 38n参数：参数：nCost 固定资产的原始成本固定资产的原始成本nSalvage 固定资产报废时预计净残值固定资产报废时预计净残值nLife 固定资产可使用年数的估计数固定资产可使用年数的估计数nPer 指定要计算第几期折旧数额指定要计算第几期折旧数额nLife 与与 per 参数应采用相同的单位，且参数应采用相同的单位，且

24、 per 应应小于或等于小于或等于 life。 SYD（资产原值，残值，使用年限，折旧期）（资产原值，残值，使用年限，折旧期）n例例 某自动化生产设备，使用期限为某自动化生产设备，使用期限为5年，原值年，原值为为100万元，残值万元，残值10万元。计算万元。计算1到到5年的折旧年的折旧额。额。Page 39n=SYD（1000000，100000，5，1）=300000n=SYD（1000000，100000，5，2）=240000n=SYD（1000000，100000，5，3）=180000n=SYD（1000000，100000，5，4）=120000n=SYD（1000000，1000

25、00，5，5）=60000Page 40n 直线折旧法 年折旧额年折旧额=(原始成本原始成本-预计净残值预计净残值)使用年限使用年限n直线折旧法函数直线折旧法函数SLN( )语法：语法：SLN( cost , salvage , life)n 功能：返回某项固定资产每期按直线折旧法计算功能：返回某项固定资产每期按直线折旧法计算的折旧数额。所有的参数值必须是正数。否则将的折旧数额。所有的参数值必须是正数。否则将返回错误值返回错误值#NUM!n参数：参数：nCost固定资产的原始成本。固定资产的原始成本。nLife固定资产可使用年数的估计数。固定资产可使用年数的估计数。nSalvage固定资产报废

26、时的预计净残值。固定资产报废时的预计净残值。Page 41=INT（DAYS360（K3，J3）/30） 设当前日期为：设当前日期为：2000年年1月月30日日DAYS360（K3，J3）计算从固定资产使用日期到）计算从固定资产使用日期到当前日期的天数。当前日期的天数。DAYS360（K3，J3）/30 为从固定资产使用日期为从固定资产使用日期到当前日期的月数。到当前日期的月数。n计算计算 已计提折旧年份已计提折旧年份 =INT（L3/12)+1 在该列复制公式在该列复制公式n输入当月工作量输入当月工作量 对于采用工作量法计提折旧的，要输入当月工作对于采用工作量法计提折旧的，要输入当月工作量量

27、 Page 42n 3折旧计提折旧计提 设采取的折旧方法有：设采取的折旧方法有：n平均年限法平均年限法 编号为编号为1n工作量法工作量法 编号为编号为2n双倍余额递减法双倍余额递减法 编号为编号为3n年数总和法年数总和法 编号为编号为4 在净值后一列设置折旧方法列、月折旧额在净值后一列设置折旧方法列、月折旧额1列和月折旧额列和月折旧额2列。列。n 选择月折旧额选择月折旧额1的下一格（即的下一格（即V2），设置公式：），设置公式：=if(U3=“1”,(1-O3)*R3/(N3*12),if(U3=“2”,(1-O3)*R3/P3*Q3,DDB(R3,R3*O3,N3,M3)/12,Page 43SYD(R3,R3*O3,N3,M3)/12) 在该列复制公式在该列复制公式n 对于折旧已计提完毕，但仍继续使用的固定资产，不应再计提折旧。对于折旧已计提完毕，但仍继续使用的固定资产，不应再计提折旧。这时，须修正这时，须修正V列的折旧公式。列的折旧公式。 将将W3单元的公式设为：单元的公式设为：=if(V3(V3-R3*O3),(R3-R3*O3),V3) 在该列复制公式在该列复制公式n 对于本月新增固定资产，本月不应计提折旧。这时，须对对于本月新增固定资产，本月不应计提折旧。