Excel在工资核算中的应用

Excel在工资核算中应用培训师：汪伟一、工资核算建立职工基本信息表薪酬标准信息表职工考勤信息表职工工资信息表工资查询与汇总建立职工基本信息表打开Excel，并创建新工作簿文件，命名为“工资处理”；操作步骤选择工作表sheet1名，将其改名为“职工基本信息表“，并在A1单元格输入“职工基本信息表”；在第二行的A2、B2、C2等单元格分别输入“职工编号”、“姓名”、“性别”、“部门”、“职工类别”、“职务”、“职称”、“工龄”；建立职工基本信息表将“职工基本信息表”合并居中操作步骤为“性别”、“部门”、“职工类别”、“职务”、“职称”等分别建立数据有效性，并在该列复制；输入职工基本信息数据。职工基本信息表见下图职工基本信息表此数据一般由人力资源管理部门提供给财务部门。建立薪酬标准信息表操作步骤选择工作表sheet2名，将其改名为“薪酬标准信息表“，并在A1单元格输入“薪酬标准信息表”；设立扣款计算标准设立计税计算标准设立岗位类别标准设立职称类别标准/职务类别等薪酬标准信息表建立职工考勤信息表选择工作表sheet3名,命名为“职工考勤信息表”操作步骤在A1单元格输入“职工考勤信息表”；在第二行的A2、B2、C2等单元格分别输入“职工编号”、“姓名”、“部门”;然后是当月的日期与星期；最后是考勤统计。职工考勤信息表考勤信息一般又企业内各部门记录,报人力资源部门核实汇总后,在交给财务部门作为核算职工薪酬的依据.建立固定工资信息表插入一张工作表,命名为“固定工资信息表”操作步骤在A1单元格输入“固定工资信息表”；在第二行的A2、B2、C2等单元格分别输入“职工编号”、“姓名”、“部门”;然后是职务、职称、基本工资、岗位津贴、职务津贴、职称津贴、工龄工资等。设置固定项目的数据引用和计算公式。建立变动工资信息表插入一张工作表,命名为“变动工资信息表”操作步骤在A1单元格输入“变动工资信息表”；在第二行的A2、B2、C2等单元格分别输入“职工编号”、“姓名”、“部门”;然后是奖金、加班费、病假、事假、旷工、罚款等。设置变动项目的数据引用和计算公式、如加班费、病假、事假、旷工，输入某些数据等。固定工资项目的数据，一般是由人力资源管理部门来提供的。其变动，也是由人力资源管理部门根据公司的有关规定来调整。相比之下，变动工资项目则是由各部门提供考勤、考核记录，人力资源管理部门核实后，再由财务部门根据有关规定进行计算。如奖金是根据各部门的记录和标准计算、加班费根据加班记录和加班费标准计算、扣款是根据考勤记录和扣款标准计算等。建立职工工资明细表插入一张工作表,命名为“职工工资明细表”操作步骤在A1单元格输入“职工工资明细表”；在第二行的A2、B2、C2等单元格分别输入“职工编号”、“姓名”、“部门”;然后是基本工资、岗位工资、职务工资、职称工资、工龄工资、奖金、加班费、病事假/旷工、罚款、应发工资、个人所得税、实发工资。设置固定项目的数据引用和计算公式。自动生成工资条在工资明细表的基础上，通过宏编程，实现自动生成工资条，进行打印。也可以增加一个宏，对生成的宏进行删除，将其还原为工资明细表。具体操作见《宏与VBA编程》的介绍23、清除单元格ActiveCell.Clear'删除所有文字、批注、格式24、返回选定区域的行数MsgBoxSelection.Rows.Count25、返回选定区域的列数MsgBoxSelection.Columns.Count26、返回选定区域的地址Selection.Address27、忽略所有的错误ONERRORRESUMENEXT28、遇错跳转onerrorgotoerr_handle'中间的其他代码err_handle:'标签'跳转后的代码29、删除一个文件killc:\1.txt30、定制自己的状态栏Application.StatusBar=现在时刻:&Time二、固定资产核算㈠固定资产概述1．固定资产，是指同时具有下列特征的有形资产：⑴为生产商品、提供劳务、出租或经营管理而持有的（持有目的）；⑵使用寿命超过一个会计年度。固定资产同时满足下列条件的，才能予以确认：与该固定资产有关的经济利益很可能流入企业；该固定资产的成本能够可靠地计量。2．固定资产计量初始计量：外购固定资产自行建造固定资产融资租入固定资产后续计量固定资产折旧折旧方法包括年限平均法、工作量法、双倍余额递减法和年数总和法固定资产后续支出3．固定资产处置固定资产出售、报废或毁损其他方式减少的固定资产4．固定资产分类企业的固定资产主要划分为三大类：一是房屋建筑物（包括地下的各种水、电、气管道设施、厂区道路、绿化工程等）；二是机器设备；三是运输起重设备，包括各种汽车、叉车、挖掘机、装载机、起重设备，电器设备、电子设备以及大型办公家私用品等㈡固定资产核算图图一图二㈢固定资产业务流程图图一图一续㈣建立固定资产清单（卡片）固定资产清单固定资产卡片是指按照固定资产的项目开设，用以进行固定资产明细核算的账簿。是固定资产管理中的基础数据，当企业取得固定资产时，就需要对本企业的固定资产卡片进行录入操作。启动MicrosoftExcel，创建一个新的工作簿文件，将其另存为：固定资产核算.xls。1．建立固定资产“项目设置表”，设置固定资产清单（卡片）中需要的有关数据。主要有：设备类别、部门名称、增加方式、使用状况等.部门名称:厂部、财务处、结算中心、人事处、计划处,销售处、金工车间、结构车间、机装车间增加方式:直接购入、在建工程转入、投资者投入、捐赠、盘盈、部门调拨、其他减少方式:对外投资、出售、报废、无偿调出使用状况:在用、停用、季节性停用设备类别:房屋、生产设备、工具器具、运输工具、办公设备2．建立固定资产清单设置固定资产清单中的数据项目：卡片编号、编号、名称、规格型号、类别、增加方式、减少方式、使用状态、部门、当前日期、启用日期、已计提折旧月份、已计提折旧年份、预计使用年份、净残值率、预计工作量、月工作量、原值、累计折旧、净值等。设置数据有效性定义项目信息的名称；设置项目信息的有效性（部门名称、增加方式、减少方式、使用状况、设备类别）录入数据方式：记录单、直接录入、可视化窗口计算固定资产净值固定资产净值=原值-累计折旧㈤固定资产变动1．固定资产增加直接输入新增加的固定资产的有关数据2．固定资产调拨直接输入内部调拨的固定资产的有关数据3．固定资产减少直接输入减少的固定资产的有关数据㈥固定资产折旧的计提固定资产折旧固定资产折旧是指企业的固定资产在使用过程中，通过损耗而逐渐转移到产品成本或商品流通费的那部分价值。为了保证企业将来有能力重置固定资产，把固定资产的成本分配到各个受益期，实现期间收入与费用的正确配比（企业必须在固定资产的有效使用年限内，计提一定数额的折旧费）。1．折旧函数介绍Excel中的折旧函数主要有：平均年限法：SLN（cost,salvage,life)年数总和法：SYD（cost,salvage,life,per)双倍余额递减法：DDB（cost,salvage,life,period,factor)Cost:固定资产原值Salvage:固定资产预计净残值Life:折旧期限Period:需要计算折旧的期间Factor:余额递减速率，如缺省，默认值为2。Per:表示期间，期单位与life相同双倍余额递减法双倍余额递减法也是一种加速折旧的方法，它是用直线法的双倍百分比，逐期乘以该期期初固定资产账面余额，作为该期的折旧额，直到折旧额小于按直线计算的折旧额为止。年折旧额=期初固定资产账面余额*双倍直线折旧率双倍直线折旧率=2÷预计使用年限*100%双倍余额递减法函数DDB()DDB(cost,salvage，life,period,factor)功能：返回固定资产在某期间(period)的折旧数额。折旧数额是根据资产的原始成本(cost)、预计使用年限(life)、预计净残值(salvage)及递减速率(factor),按倍率递减法计算而得出的。DDB函数所有参数均需为正DDB（资产原值，残值，使用年限,折旧期,递减速率）例假设某一公司购买自动化生产设备的成本为1500万元,使用年限为8年,残值为250万元,请计算如下几年的折旧。第一年=DDB（1500，250，8*365，1）第二年=DDB（1500，250，8，2）第5个月=DDB（1500，250，8*12，5，3）年数总和法年数总和法是一种加速折旧方法，它以固定资产的原始成本减去预计残值后的余额乘以一个逐年递减的分数，作为该期的折旧额。这个分数的分母是固定资产使用年限的各年年数之和，分子是固定资产尚可使用年限。年数总和法函数SYD()功能：返回某项固定资产某期间的按年数总和法计算的折旧数额。所有参数都应为正数，否则将返回错误值#NUM!参数：Cost——固定资产的原始成本Salvage——固定资产报废时预计净残值Life——固定资产可使用年数的估计数Per——指定要计算第几期折旧数额Life与per参数应采用相同的单位，且per应小于或等于life。SYD（资产原值，残值，使用年限，折旧期）例某自动化生产设备，使用期限为5年，原值为100万元，残值10万元。计算1到5年的折旧额。=SYD（1000000，100000，5，1）=300000=SYD（1000000，100000，5，2）=240000=SYD（1000000，100000，5，3）=180000=SYD（1000000，100000，5，4）=120000=SYD（1000000，100000，5，5）=60000直线折旧法年折旧额=(原始成本-预计净残值)÷使用年限直线折旧法函数SLN()语法：SLN(cost,salvage,life)功能：返回某项固定资产每期按直线折旧法计算的折旧数额。所有的参数值必须是正数。否则将返回错误值#NUM!参数：Cost——固定资产的原始成本。Life——固定资产可使用年数的估计数。Salvage—固定资产报废时的预计净残值。=INT（DAYS360（K3，J3）/30）设当前日期为：2000年1月30日DAYS360（K3，J3）计算从固定资产使用日期到当前日期的天数。DAYS360（K3，J3）/30为从固定资产使用日期到当前日期的月数。计算已计提折旧年份=INT（L3/12)+1在该列复制公式输入当月工作量对于采用工作量法计提折旧的，要输入当月工作量3．折旧计提设采取的折旧方法有：平均年限法编号为1工作量法编号为2双倍余额递减法编号为3年数总和法编号为4在净值后一列设置折旧方法列、月折旧额1列和月折旧额2列。⑴选择月折旧额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,SYD(R3,R3*O3,N3,M3)/12)))在该列复制公式⑵对于折旧已计提完毕，但仍继续使用的固定资产，不应再计提折旧。这时，须修正V列的折旧公式。将W3单元的公式设为：=if(V3(V3-R3*O3),(R3-R3*O3),V3)在该列复制公式⑶对于本月新增固定资产，本月不应计提折旧。这时，须对W列的折旧公式在X列进行修正。（3）在“编辑栏”中输入下面的公式，然后按【Enter】键确认。=IF(MOD(ROW(),3)=0,,IF(MOD(ROW(),3)=1,员工工资明细表!A$2,INDEX(员工工资明细表!$A:$O,INT((ROW()-1)/3)+3,COLUMN())))（4）选中A1单元格，用鼠标拖动其填充句柄至O1单元格，将公式复制到B1:O