用Excel为长沙翔腾纸盒有限公司设计工资管理系统.docx

湖南生物机电职业技术学院 毕业设计成果报告书 用 Excel为长沙翔腾纸盒有限公司设计工资管理系统 第一部分设计任务与调研 1、 毕业设计的主要任务 长沙翔腾纸盒有限公司（以下简称翔腾公司 ） 是一个以加工半成品纸盒为 成品的纸盒公司，主要向各省各公司销售纸质包装物及某些纸质用品，公司生 产规模较小。对于生产加工型公司，公司的员工工资核算是件尤为重要的部分， 本毕业设计主要是针对长沙翔腾纸盒有限公司的工资管理利用 excel强大的数 据功能设计的一套工资管理系统。 2、 设计目标及思路、方法 2.1设计目标及思路 通过自己的实际工作，发现公司在工资管理这块存在许多的不足，平时工资的 核算，主要的利用 Excel做些简单的表格统计工资；又随着公司员工的增多，工 作量大。工资核算原本就是是一项琐碎、复杂而又十分细致的工作，工资计算、 发放工资的工作量也越来越大，一般不允许出错，如果实行手工操作，每月发 放工资须手工填制大量的表格，这会消耗工作人员大量的时间和精力，同时也 无法做到实时监控，难以保证数据的准确性和及时性。虽目前市场上流行的工 资管理系统软件不少。但是，对于我们这种规模较小的公司来说，不需要太大 型的数据库系统， Excel成我们公司工资管理的首选，因此需要制定一套行之有 效、经济又适合本公司应用的工资核算管理系统。根据这一要求，从公司的实 际角度出发，我利用最新版本 WPS2016 Excel软件为本公司制定出来此套工资 管理系统，在实际工资管理中，利用 Excel函数引用功能，直接引用原始数据， 可以省略输入数据，防止二次输入发生错误。利用 Excel函数自动计算功能，可以 减少人工计算个人所得税、各种保险、奖金及工资总额的工作量，自动计算生成 工资表。Excel打印功能可以打印出任何想要的工资表、工资条。通过密码设置可 以防止工资管理数据的泄漏，给公司造成意外的损失。 2. 2设计方法 (1) 主要是利用学校学习的知识，利用书本、网络上的资料； (2) 通过查询资料，参照优秀作品，了解该设计需如何去操作，及要做哪些 准备，设计出大体的功能模块； (3) 通过进一步的了解，向学习好的朋友和经验丰富的同事进行学习交流， 结合公司的实际情况，对每个模块进行细化，对某些公式的设置； (4) 通过指导老师的指导，对设计好的作品进行修改，通过修改发现存在的 问题并解决，从而达到完善的效果 3、 收集与本课题相关的资料 3.1个人所得税计算表 工资薪金个人所得税税率表 （ 免征额 3500元 ) 级数 全月应纳税所得额（含税级距） 全月应纳税所得额（不含税级距 ) 税率 (%) 速算扣 除数 1 不超过 1,500元 不超过 1455元的 3 0 2 超过 1,500元至 4,500元的部分 超过 1455元至 4155元的部分 10 105 3 超过 4,500元至 9,000元的部分 超过 4155元至 7755元的部分 20 555 4 超过 9,000元至 35,000元的部分 超过 7755元至 27255元的部分 25 1,005 5 超过 35,000元至 55,000元的部分 超过 27255元至 41255元的部分 30 2,755 6 超过 55,000元至 80,000元的部分 超过 41255元至 57505元的部分 35 5,505 7 超过 80,000元的部分 超过 57505元的部分 45 13,505 3. 2工资条公式设置 网上进行收集资料，确定工资条公式的设置。（如图 1) 图 1 4、调研的过程、目的、总结 4.1调研的过程 (1) 深入翔腾公司工实习，了解翔腾公司企业文化以及公司财务制度； (2) 工资核算岗位实际工作，了解公司工资核算制度及工资核算系统，从发 现工资核算不足的地方； (3) 进一步调研，确认工资核算的确存在不足； (4) 想出解决方案，进行设计工资管理系统。 4.2调研的目的 (1) 为了准确、快捷的进行职工的工资核算，使工资核算的管理更加有条有 序； (2) 满足企业财务管理的需求； (3) 可以迅速制作工资表和查到所需信息； (4) 提高工作效率，节省人力物力。 4. 3调研总结 经过对翔腾公司深入地的调研，翔腾公司在工资核算管理这块确实存在许多 不足的地方，工资核算系统不够完善，也就通过 EXCEL做些简单的表格统计员工 的工资，又加之业务量的增加，公司的员工增加，工资核算越来越混乱。因而更 有必要为之设计一套经济实惠、适合公司的工资管理系统。 第二部分设计过程说明 1、过程叙述 本系统通过新版本 WPS2016 Excel软件设计的，设计的总体任务是要实现公 司职工工资信息管理的系统化，规范化和自动化。系统功能分析是在设计总体任 务的基础上完成的。 工资管理系统主要分为十二个模块，用户管理、职工基本情况、有关计算比 率、基本工资、福利、社会保险、考勤及奖金、工资结算单、工资汇总、工资条、 工资费用分配、工资查询。 1.1建立工资管理系统工作簿、封面及首页的设计 1.1.1建立工资管理系统工作薄 在计算机的磁盘上建立一个名称为 “ 深圳翔腾纸盒有限公司工作管理系统 ” 的 EXCEL工作簿文件，并在此工作簿中建立 “ 封面 ” 、 “ 首页 ” 、 “ 职工基本情况 表 ” 、 “ 有关计算比率表 ” 、 “ 工资调整表 ” 、 “ 福利表 ” 、 “ 社会保险表 ” 、 “ 考勤及奖金表 ” 、“ 工资结算单 ” 、 “ 工资汇总 表 ” 、 “ 工资条 ” 、 “ 工资费 用分配表 ” 、 “ 工资查询表 ”等工作表，这些工作表事先由手工插入并将其重命 名为相应名称。 1.1.2封面的设计 “ 封面 ” 工作表是进入工资管理系统的首页，当打开工作簿和关闭工作簿时 系统都将激活 “ 封面 ” 工作 3.表， “ 封面 ” 工作表的界面如图 2所示，其设计步 骤如下 : 图 2 (1) 执行【视图】不勾选 “ 显示网络线 ” ，即工作表背景为白色的； (2) 在当前的工作表中插入艺术字 “ 欢迎使用深圳翔腾纸盒有限公司工资管 理系统 ” （ 分两行输入 “ 欢迎使用 ” 和 “ 深圳翔腾纸盒有限公司工资管理系统 ” ） (3) ; (3)将 “ 深圳翔腾纸盒有限公司工资管理系统 ” 设置超链接 “ 首页 ” ， 点击“ 深圳翔腾纸盒有限公司工资管理系统 ” 直接进入 “ 首页 ” 工作表。 1.1. 3工资首页的设计 根据公司的实际情况，以及考虑到工作需要，就设计工资首页，对工资管理 系统所包括的东西一目了然，操作也简便。 (1) 执行【视图】不勾选 “ 显示网络线 ” ，即工作表背景为白色的； (2) 网上搜索相关图片，并将其插入工作表中，同时相应的图标下插入相应 的文字，为了使用方便，同时选中图标及文字，右击 一 超链接，超链接到本文档 相应的工作表。如图 3。 图 3 1. 2职工基本情况表 职工基本情况表是工资管理的基础信息，数据信息相对比较稳定的部分。主 要记录员工的编号 、姓名、部门、职务、职称、入职时间、性别、出生日期、身 份证号码、户口所在地、联系电话、银行帐号等信息。 Excel进行工作核算可以通 过 Excel函数直接引用职工基本信息表数据，使工作简单准确。同时如果有人事 变动仅需要变更变动人员的数据信息就可以，避免重复工作，防止出现错漏。在 之前增加好的 “ 职工基本情况表 ” 第一行， A-L列输入文件标题 “ 翔腾公司职工基 本情况一览表 ” 。设置字体宋体，字号 20,合并及居中。第二行输入相关信息， 字体宋体，字号 9。 1. 2. 1序号、工号、姓名 序号便于统计员工人数，序号可以利用 Excel函数自动生成，人员变动增减 数据时，拖拉公式自动生成新的序号。工号一般是由人事部根据编码规则编制工 号，大多是根据入职顺序排列，我们公司是按部门排列，工号是固定不变的。未 来防止工号出现重复数据，可以通过 “ 数据有效性 ” 来防止重复输入。 (1) 选择 B列，执行【数据】一【有效性】命令； (2) 打开【有效性】对话框，单击【设置】选项卡，在 “ 窗口选项中 ” 中允 许处选择 “ 自定义 ” ，公式输入 =C0UNTIF(B:B,B1).姓名栏也可以同上设置。 1.2.2部门、职务 部门、职务在公司一般相对固定，为了防止输入错误，利用数据有效性进行 录入。 (1) 执行【数据】 4【有效性】命令，打开【有效性】对话框，单击【设置】 选项卡，在 “ 窗口选项中 ” 中允许选择 “ 序列 ” ，来源输入 “ 办公室，财务部， 销售部，后勤部，制造部 ” 注意逗号是英文状态下的逗号（如图 4); (2) 利用 “ 填充柄 ” ，自动生成相应数据。 图 4 1.2.3身份证号、性别、出生曰期 身份证号码根据人事部数据直接填列，性别、出生日期根据 Excel函数自动 生成。 (1) 选中性别下 H3单元格点击【公式】工具栏下的【插入函数】； (2) 进入【插入函数】对话框，点击 “ 常用公式 ” 选中 “ 提取身份证性别 ” ， 在“ 参数输入 ” 提取身份证单元格 J3,单击【确定】，即公式 =IF(0R(LEN(J3)=15, LEN(J3)=18), IF(M0D(MID(J3, 15, 3)*1， 2)， "男， )， #N/A)。 (如图 5) 图 5 在出生日期下 13单元格下一同样的方法录入信息，及公式为 =DATE(MID(J3, 7, VLOOKUP(LEN(J3), 15, 2;18, 4, 2, 0), MID(J3, VL00KUP(LEN(J3) ,15, 9; 18, 11, 2, 0), 2), MID (J3, VL00KUP (LEN (J3), 15, 11; 18, 13, 2, 0), 2), M 用 “ 填充柄 ” ，自动生成相应数据。 1.2.4户口住址、联系电话及银行帐号 户口住址、联系电话及银行帐号这些数据根据资料手工录入，输入完成的基 础数据的职工基本情况表（如图 6) 1.3工资计算各种比率表 在 “ 有关计算比率 ” 工作表中，根据税法及公司规定，进行录入各比率，该 表格没有任何函数公式，其作用就是辅助后面工资的计算，（如图 7 ) 图 7 1.4基本工资一览表 在之前增加好的 “ 工资一览表 ” 工作表中第一行， A-J列选定并合并居中，输 入 “ 公司职工基本工资一览表 ” ，字体宋体，字号 20。第二行输入序号、工号、 姓名、部门、职务、基本工资、岗位工资、 工龄工资、奖金、合计，字体宋体， 字号 9。 1.4. 1序号、工号、姓名、部门、职务 序号利用 Excel函数自动生成，不要输入数据，工号、姓名、部门、职务不 用手工输入，从基本情况表中直接引用，在 B3单元格中输入公式 “= 职工基本情 况 !B3” ，利用“ 填充柄 ” ，自动生成相应数据， C3、 D3、 E3同上。 1. 4. 2基本工资、岗位工资、工龄工资、奖金、合计 我公司规定基本工资：技术人员 3000元，经理 2500元，厂长 2500元，会计 2300元，主任、出纳、部门经理、销售人员、工人 2000元，职员 1600元； 基本工资 F3单元格输入 =IF(E2:E46= 技术人员， 3000， IF(E3:E46= 经理 ' 2500, IF(E3:E46= 厂长， 2500, IF(E3:E46= 职员， 1600, IF(E3:E46= 会计 ' 2300,2000) 岗位工资：经理 2000元；，主任 1800元，会计 1900元，部门经理 2500元， 销售人员1900元，出纳、职员和工人 1000元，厂长 2000元，技术人员 2500元 ; 岗位工资 G3单元格输入 =IF(E12:E56= 技术人员， 2500, IF(E13:E56= 经理 ' 2000, IF(E13:E56= 厂长， 2000, IF(E13:E56= 主任， 1800, IF(E13:E56= 会计 ， 1900，： 任 13:£56=部门经理， 2000，正伍 13556=销售人员 ，1900,1000) 工龄工资，是根据工龄来计算的，按每年 300元。 在 H3 单 元 格 中 输 入 =(YEAR( TODAY )_YEAR( 职 工 基 本 情 况 ! G3)_ (MONTH (TODAY )-MONTH (职工基本情况 ! G3)， M0NTH(T0DAY()&月，） 基本工资 =IF(C3=， ，工资一览表 !F3) 岗位工资、工龄工资同上 住房补贴 =IF(C3=， ，福利表 !E3) 伙食补贴、交通补贴、医疗补助同上 应发合计 =IF(C3=， ， SUM(F3:M3); (3) 公司规定租房公积金是根据应发工资的 15%计提的，单元格 N3中输入公 式 =IF(C3=， ， N3*有关计算比率表 ! $C$9)。 (4) 个人所得税，首先进行一个辅助计算出应纳税所得额，应纳税所得额是 应付工资扣除起征点后的余额，所以单元格 W3输入公式 =IF(C3=， ， MAX(N3-有 关计算比率表 !$B$15,0)， 下拉填充数据；得出应纳税所得额，应纳个人所税额， 根据前面的有关计算比率表，按九级税率进行计算，单元格 S3输入公式 =IF(W3， 工资总额汇总表 ! B6, ） 工资总额 = IF(AND(A4 )， INDEX( 工 资 总 额 汇 总 表 ! $C$5: $C$100, MATCH(A4,工资总额汇； 职工福利费 = IF(AND(A4 )， B4*有 关 计 算 比 率 表 ! $G$ 16, IF (A4= 总计 ， SUM (#REF!) /"'); 工会经费 =IF(AND (A4 )， B4*有关计算比率表 ! $G$17, IF (A4= 总计 ,SUM(#REF!)， )）； 教育经费 =IF(AND (A4 )， B4*有关计算比率表 ! $G$18, IF (A4= 总计 ,SUM(#REF!)， )）； 养老保险 =IF (AND (A4 )， SUMIF (工资一览表 ! $D$3: $D$ 100, A4, 工资一览表！ $ J$3:$J$ 1 0 0) * 有 关 计算 比 率 表 ！ $ B$6， IF( A4= 总计 /,， SUM(#REF!)， ,） ； 医疗保险 =IF(AND(A4"总计， A4"总计 "， A4"总计 ' A4"'SUM(C4:I4). "）： （加图 14) 图 14 1. 12职工工资査询表 设置工资查询表，主要是因为之前公司想查询某个人的某个月工资的具体情 况，还的在 excel表中查询好久，才能过查找出来，又不能一目了然，这个查询 表能够方便、快速地查询公司每个员工的工资薪金，了解每个员工的工资情况。 (1)进入事先创建好的 “ 查询表 ” 工作表，在单元格中录入 “ 职工工资查询 表 ” ， “ 应付工资 ”“ 应扣薪金 ”“ 应发合计 ” 等核算项目，并对相应单元格进 行公式设置。 G3单元格，设置数据有效性，具体操作同上，数据来源。（如图 15) 图 15 E5单元格输入 =IF (G3= , INDEX (工资结算单 ! B2: B46, MATCH (查询表 ! G3， 工资结算单 ! D2:D46,0) G5单元格输入 =IF (ISERROR (INDEX (考勤及奖金表 ! AM5: AM48, MATCH (查询 表 ! G3,考勤及奖金表 ! C5: C48, 0) /' 病假 &INDEX (考勤及奖金 表 ! AM5: AM48, MATCH (查询表 ! G3,考勤及奖金表 ! C5: C48, 0) &"(天 ） "） E6 单元格输入 =IF(G3="， , G3) G6单元格输入 =IF (ISERROR (INDEX (考勤及奖金表 ! AN5: AN48, MATCH (查询 表 ! G3,考勤及奖金表 ! C5: C48, 0)， ""，事假 "&INDEX (考勤及奖金 表 !AN5:AN48,MATCH(查询表 !G3,考 勤 及 奖 金 表 ! （天 ） " ） E7单元格输入 =IF(G3= , , INDEX (职工基本情况 ! E2:E46, MATCH (査询表 ! G3, 职工基本情况 ! C2: C46, 0) G7单元格输入 =IF(ISERROR (INDEX(考勤及奖金表 ! A 5: A048, MATCH (査询 表 ! G3,考勤及奖金表 ! C5: C48, 0), ' 事假 &INDEX (考勤及奖金 表 !A05:A048,MATCH(査询表 !G3,考勤及奖金表 !C5:C48,0)&"(天 ） "） 应发工资有关项目公式设置，数据主要是从工资结算表提取： E11 单元格输入 =IF(G3= , ， INDEX(工资结算单 !F3:F46, MATCH(查询表 !G3, 工资结算单 ! D3:D46,0) E12 单元格输入 =IF(G3= , ， INDEX(工资结算单 !G2:G100, MATCH(查询表 !G3, 工资结算单 !D2:D100, 0)，其他的同上； 应扣薪资有关项目公式的设置： G11 单元格输入 =IF(G3= , ， INDEX(工资结算单 ! 02:0100, MATCH(查询表 !G3, 工资结算单 ! D2:D100,0) G12 单元格输入 =IF(G3=， ， INDEX(工资结算单 !P2:P100, MATCH(查询表 !G3, 工资结算单 !D2:D100,0);其他的同上。设置好的职工工资查询表，（如图 16)。 图 16 2、成果应用叙述 可行性的分析是用最小的代价在尽可能短实际内，确定问题是否能过解决， 也就是说，它的目的不是解决问题，而是确定问题是否值得解决，它是今后工的 的决定性环节。 经济可行性主要是对待本设计成本估算和成本、效益分析，是否有开发的 价值。本设计的开发成本较低，基本上不需要什么成本，主要是话费些时间。本 设计为工资管理工作效率带来了很大的帮助，主要表现如下方面：第一，本设计 的运行可以代替人工进行许多的繁杂的劳动；第二，本设计的运行可以大大的提 高管理工作的工作效率；第三，本设计的运行可以节省许多资源；因而在人力、 物力、财力等资源方面得到了很大节约，因此，本设计的开发在经济上是可行的。 企业工资管理系统是一个典型的信息管理系统，其主要通过软件工程方面的 选择课题、需求分析、总体设计、选用工具、系统测试等几个步骤实现。在设计 本设计的过程中，首先通过自身的实际操作，在了解本公司在工资管理系统中存 在的不足，对公司的财务及财务制度有了 一定的了解熟悉的情况下设计的。 一套工资核算模型创建完成，下月进行工资核算时，可以先复制一下该文件， 然后只要将 “ 基础信息 ” 表中月份、本月出勤天数、事假天数、病假天数等数据 输入，工资表、工资汇总表、工资条都将自动生成，大大提高了工作效率。 另外在使用过程中应注意以下几点： （ 1)在输入数字时，小数点有时输成中文 句号，可以选取 “ 基础信息 ” 表中的 D、 E、 F、 G、 H五列，单击菜单 数据 -有效 性 -输入法模式 中选取 “ 关闭 ” 。 （ 2)为防止单元格中创建的公式被误删或更改， 可以先选中有公式的单元格，在菜单 格式 -单元格 -保护 中的 锁定 前打勾， 然后单击菜单 工具 -保护 -保护工作表 ，输入密码。 （ 3)为限制不同用户查看 和修改文件，在文件创建完成后，可以输入打开权限密码和修改权限密码，具体单 击【文件】 -【另存为】 -【工具】-【常规选项】，在【打开权限密码】和【修改 权限密码】中输入不同的密码。 第三部分设计成果说明 3. 1设计成果 设计作品：用 Excel为长沙翔腾纸盒有限公司设计工资管理系统 3. 2作品的特点 本设计选用企业最日常，最重要的工资核算。利用专业知识及课外知识，用 Excel为长沙翔腾纸盒有限公司设置工资管理系统，该系统解决了当下翔腾公司工 资管理系统的相关不足的问题；该系统设计思路清晰，使用操作简洁，对于任何 一个会使用 Microsoft office办公软件的人来说，不是一件难事，使用范围广。 3. 3佐证材料 利用公司员工的实际工资，输入设计的工资管理系统中，与之前技术的工资 进行核对是否正确，（如图 17) 第四部分结束语 通过设计本工资管理系统，我不仅掌握了有关 WPS2016 Excel表格处理软件 中，有关数据处理的技巧，以及各个公式的应用，还对专业知识有了更深一步的 认识。 WPS2016 Excel作为一种常用的软件，非常简单实用，任何人只要稍微懂 Excel的都会使用。 同时，这次的毕业设计使我受到了一次专业知识、专业技能分析和解决问题 的全面系统的锻炼。我们在学好专业知识的同时多去学习与专业有关的知识，尽 可能的将自己所学到的东西应用到实际工作当中，才能起到学以致用的效果。我 会继续努力学习专业知识，不断地丰富自己，日后成为一名合格的会计工作人员。 第五部分致谢 回首过去，以前师生们的欢声笑语还放佛昨天，但转眼间，三年在学习时光 却已即将结束。在这三年的时间里，我学到了很多知识，并且在学习中也交到了 许多朋友，老师们的谆谆教诲也使我们在学习中能更加把我好学习的方法，克服 了一切困难，顺利地渡过了这三年的学习生活。在此，我还要真诚地感谢在毕业 设计中给我们提供帮助的指导老师们，感谢他们认真指导，是我们顺利地完成了 毕业设计。在这即将结束学业的时候，我祝愿各位老师健康快乐、万事如意；同 时也祝愿同学们能学有所用、美梦成真！ 最后，我要向百忙之中抽时间对本设计进行审阅，评议和参与本人设计答辩 的各位老师表示感谢。 第六部分参考文献 【 1】 Excel在工资管理中的应用，何明瑞，四川师范大学基础教学学院， 2011 年 10 月； 【 2】 Excel与财务管理，曾瑞玲，厦门大学出版社， 2007年 7月； 【 3】 Excel在财务管理工资的应用，李江霞，北京邮电大学出版社， 2014 年12月， 1页 -100页； 【 4】 Excel在会计与财务管理中的应用，谷秀凤，清华大学出版社， 2014 年 8月。