员工工资管理系统.ppt

第8章员工工资管理系统 在一个企业中 每一个月人事部门都需要统计员工的工资 由于员工的工资涉及到员工的出勤量 福利以及奖金等数据 还需要对个人所得税等进行计算 所以 这个时候如果手工计算员工工资的工作量是非常大的 本章将介绍利用Excel来制作一个员工工资管理系统 并能够自动生成工资条 1 创建员工出勤统计表员工出勤统计表是用来记录企业员工出勤情况的表格 可以参考上一章内容创建 2 创建员工福利表 员工福利表 是记录员工福利情况的基本表格 主要应包括 员工编号 员工姓名 所在部门 住房补贴 车费补贴 和 保险金 等 其中 员工编号 员工姓名 和 所在部门 的字段值应与 员工出勤统计表 中的数据一致 3 创建员工奖金表 员工奖金表 是记录员工奖金情况的基本表格 主要应包括 员工姓名 所在部门 和 奖金 等 同样 这里的 员工姓名 和 所在部门 的字段值也要与 员工出勤统计表 和 员工福利表 中的数据保持一致 4 创建员工工资表 员工工资表 的作用就是汇总上述3个表格中的数据 然后计算出每一位员工最后应该获得的实际工资 因此 员工工资表 应该包括 员工编号 员工姓名 所在部门 基本工资 奖金 住房补助 保险金 应发金额 扣税所得额 个人所得税 以及 实发工资 等字段 根据上述内容创建 员工工资表 的具体步骤如下 插入一个新工作表 并将其更名为 员工工资表 在 员工工资表 中输入相应的字段名称 创建完 员工工资表 后 就需要输入数据了 首先 用自动填充功能输入员工编号 1 输入出勤数据因为包括 员工姓名 所在部门 请假扣款 在内的数据都在 员工出勤统计表 中 所以 可以直接引用其中的数据 但如果对每一组数据都进行搜索数据设置的话将非常麻烦 为此 可以使用Excel中提供的 定义名称 功能来简化操作 定义名称的具体步骤如下 切换到 员工出勤统计表 中 选择单元格区域 A2 E22 然后单击 插入 名称 定义 菜单项 打开 定义名称 对话框 在 在当前工作簿中的名称 文本框中输入 出勤表 然后单击 添加 按钮即可完成数据名称的添加 单击 关闭 按钮返回工作表中 注 定义名称还有一种更简洁的方法 在工作表中选中单元格区域 A2 M22 在名称框中输入 出勤表 即可定义该名称 下面利用定义VLOOKUP函数输入出勤数据 具体的操作步骤如下 切换到 员工工资表 中 选中单元格B3 单击 插入 函数 菜单项打开 插入函数 对话框 然后在 或选择类别 下拉列表中选择 查找与引用 选项 在 选择函数 列表框中选择 VLOOKUP 选项 单击 确定 按钮打开 函数参数 对话框 然后在 Lookup value 文本框中输入 A3 在 Table array 文本框中输入 出勤表 在 Col index num 文本框中输入 2 在 Range lookup 文本框中输入 0 单击 确定 按钮返回工作表中 此时在单元格B3中使会显示计算结果 用同样的方法为单元格C3插入公式 VLOOKUP A3 出勤表 4 0 便会得到入 所在部门 的值 用同样的方法为单元格I3插入公式 VLOOKUP A3 出勤表 12 0 便会得到 请假扣款 的值 2 输入奖金数据按照前面的方法为 员工奖金表 中的单元格区域定义区域名称 奖金表 在 员工工资表 中选中单元格E3 在其中输入公式 VLOOKUP A3 奖金表 5 0 便会得到 奖金 的值 3 输入福利数据按照前面的方法为 员工奖金表 中的单元格区域定义名称 福利表 在 员工工资表 中选中单元格F3 在其中输入公式 VLOOKUP A3 福利表 4 0 然后按下 回车 键 即可在单元格F3中显示 住房补助 的计算结果 选中单元格G3 在其中输入公式 VLOOKUP A3 福利表 5 0 在单元格G3中便会得到 车费补助 列的值 选中单元格H3 在其中输入公式 VLOOKUP A3 福利表 6 0 在单元格H3中便会得到 保险金 的值 选择单元格区域 B3 I3 然后利用自动填充功能将公式复制到其他的单元格区域即可得到结果 4 计算应发金额员工工资应发金额的计算公式为 应发金额 基本工资 奖金 住房补助 车费补助 保险金 请假扣款根据上述公式 在单元格J3中输入公式 D3 E3 F3 G3 H3 I3 即可计算出 应发金额 的值 然后利用自动填充功能将该公式填充到该列的其他单元格中得到结果 5 计算扣税所得额在工资表中对个人所得税的计算相对要复杂一些 不是所有的人都按照相同的税率来纳税 也不是全部的收入都要纳税 扣税所得额 就是需要纳税的那一部分收入 不同的 扣税所得额 有不同的税率和速算扣除数 扣税所得额 为 应发工资 减去1600元个税起征点后超出的数额 如果这个值小于零 应发工资少于1600元 就不必交纳个人所得税 若这个值大于零 应发工资多于1600元 就要交纳个人所得税 根据上述内容计算 扣税所得额 的具体步骤如下 选中单元格K3 单击 插入 函数 菜单项 打开 插入函数 对话框 单击 确定 按钮打开 函数参数 对话框 然后在 Logical test 文本框中输入 J3 1600 在 Value if true 文本框中输入 0 在 Value if false 文本框中输入 J3 1600 如图8 10所示 单击 确定 按钮返回工作表中 此时在单元格K3中便会显示计算结果 利用自动填充功能将公式复制到该列的其他单元格中 6 计算个人所得税计算出了 扣税所得额 下面再来求 个人所得税 即需要交纳的税款 在单元格L3中输入如下公式 IF K3 500 K3 2000 K3 5000 k3 20000 k3 20 375 7 计算实发金额 实发金额 就是 应发金额 减去 个人所得税 只要在单元格M3中输入 J3 L3 然后再对该列的其他单元格进行公式的复制即可 DSUM函数的作用是返回数据列表或者数据库的列中满足指定条件的数字的和 DSUM函数的语法结构如下 DSUM database field criteria 各个参数的含义如下 database是构成列表或者数据库的单元格区域 数据库是包含一组相关数据的列表 其中包含相关信息的行为记录 而包含数据的列为字段 列表的第一行包含着每一列的标志项 field用于指定函数所使用的数据列 列表中的数据列必须在第一行具有标志项 field可以是文本 即两端带引号的标志项 此外field也可以是代表列表中数据列位置的数字 其中1表示第一列 2表示第二列 依此类推 criteria为一组包含给定条件的单元格区域 可以为参数criteria指定任意区域 只要它至少包含一个列标志和列标志下方用于设定条件的单元格 了解了函数的作用后 首先在工资表中的单元格区域 C25 D29 中输入如下图所示的数据 然后对该区域进行简单的格式化设置 利用函数DSUM计算工资额在各个部门中的汇总的具体步骤如下 选中单元格D26 单击 插入 函数 菜单项 打开 插入函数 对话框 然后在 或选择类别 下拉列表中选择 数据库 选项 在 选择函数 列表柜中选择 DSUM 选项 单击 确定 按钮打开 函数参数 对话框 然后在 Database 文本框中输入 C2 M22 在 Field 文本框中输入 M2 在 Criteria 文本框中输入 C25 C26 单击 确定 按钮返回工作表中 此时在单元格D26中便会显示计算结果 在单元格D27中输入如下公式 SUM DSUM C2 M22 M2 C25 C27 D26 然后按下 回车 键 在单元格D27中便会显示计算结果 注 在上面的公式中 DSUM函数的设定条件为单元格区域 C25 27 即计算了数据区域 C2 M22 中所有人事部和财务部员工的工资总和 因此要用SUM函数将人事部员工的工资总和减去后才能够得到财务部员工的工资总和 同样的道理 在单元格D28中输入如下公式 SUM DSUM C2 M22 M2 C25 C28 D26 D27 然后按下 回车 键 在单元格D28中便会显示 销售部 计算结果 在单元格D29中输入如下公式 SUM DSUM C2 M22 M2 C25 C29 D26 D27 D28 然后按下 回车 键 在单元格D29中便会显示 业务部 的计算结果 制作工资条1 INDEX函数该函数返回的是表或者区域中的值或值的引用 INDEX函数有两种形式 数组合引用 数组形式通常返回竖直或者数值数组 引用形式通常返回引用 当函数INDEX的第一个参数为数组常数时则使用数组形式 数组形式的INDEX函数的语法结构如下 INDEX array row num column num 2 ROW函数ROW函数的作用是返回引用的行号 该函数的语法如下 ROW reference 3 COLUMN函数COLUMN函数的作用是返回给定引用的列标 该函数的语法结构如下 COLUMN reference 制作工资条的具体步骤如下 插入一个新的工作表并将其重命名为 工资条 然后在 工资条 中的单元格Al中输入如下公式 IF MOD ROW 3 0 IF MOD ROW 3 1 员工工资表 A 2 INDEX 员工工资表 A M ROW 4 3 1 COLUMN 输入完后按下 回车 键 在单元格A1中就会显示计算结果 分别在单元格 B1 M1 单元格中输入以下公式 B1 IF MOD ROW 3 0 IF MOD ROW 3 1 员工工资表 B 2 INDEX 员工工资表 A M ROW 4 3 1 COLUMN C1 IF MOD ROW 3 0 IF MOD ROW 3 1 员工工资表 C 2 INDEX 员工工资表 A M ROW 4 3 1 COLUMN D1 IF MOD ROW 3 0 IF MOD ROW 3 1 员工工资表 D 2 INDEX 员工工资表 A M ROW 4 3 1 COLUMN E1 IF MOD ROW 3 0 IF MOD ROW 3 1 员工工资表 E 2 INDEX 员工工资表 A M ROW 4 3 1 COLUMN F1 IF MOD ROW 3 0 IF MOD ROW 3 1 员工工资表 F 2 INDEX 员工工资表 A M ROW 4 3 1 COLUMN G1 IF MOD ROW 3 0 IF MOD ROW 3 1 员工工资表 G 2 INDEX 员工工资表 A M ROW 4 3 1 COLUMN H1 IF MOD ROW 3 0 IF MOD ROW 3 1 员工工资表 H 2 INDEX 员工工资表 A M ROW 4 3 1 COLUMN I1 IF MOD ROW 3 0 IF MOD ROW 3 1 员工工资表 I 2 INDEX 员工工资表 A M ROW 4 3 1 COLUMN J1 IF MOD ROW 3 0 IF MOD ROW 3 1 员工工资表 J 2 INDEX 员工工资表 A M ROW 4 3 1 COLUMN K1 IF MOD ROW 3 0 IF MOD ROW 3 1 员工工资表 K 2 INDEX 员工工资表 A M ROW 4 3 1 COLUMN L1 IF MOD ROW 3 0 IF MOD ROW 3 1 员工工资表 L 2 INDEX 员工工资表 A M ROW 4 3 1 COLUMN M1 IF MOD ROW 3 0 IF MOD ROW 3 1 员工工资表 M 2 INDEX 员工工资表 A M ROW 4 3 1 COLUMN 实例拓展1 建立基本表格单击 插入 工作表 菜单项在工作簿中插入工作表并将其重命名为 员工工资详情表 然后根据 员工工资表 中的内容建立表格并进行格式化设置 2 创建下拉列表框在这里需要对每一位员工的编号进行输入 如果是较大的企业 编号数据的输入工作量将会非常大 为此可以使用窗体控件来创建下拉列表 选择员工的编号 创建下拉列表的具体步骤如下 单击 视图 工具栏 窗体 菜单项 打开 窗体 工具栏 单击 组合框 按钮 然后用鼠标在单元格C2的位置拖动出适当大小的组合框 用鼠标右键单击添加的组合框 在弹出的快捷菜单中选择 设置控件格式 菜单项 打开 设置控件格式 对话框 切换到 控制 选项卡中 然后在 数据源区域 文本框中输入 员工工资表 A 3 A 22 在 单元格链接 文本框中输入 A 2 在 下拉显示项数 文本框中输入 6 并选中 三维阴影 复选框 如图8 20所示 单 确定 按钮返回工作表中 单击其他的任何一个单元格 然后再单击添加的组合框 此时会弹出下拉列表框 在下拉列表中选择员工编号 1008 在单元格A2中则会显示出链接值 8 3 设置其他项目利用 窗体 工具栏建立下拉列表后 下面使用函数INDEX对其他的项目设置自动输入功能 具体步骤如下 选中单元格C3 单击 插入 函数 菜单项 打开 插入函数 对话框 然后在 或选择类别 下拉列表中选择 查找与引用 选项 在 选择函数 列表框中选择 INDEX 选项 单击 确定 按钮打开 选定参数 对话框 然后在 参数 列表框中选择 array row num column num 选项 单击 确定 按钮打开 函数参数 对话框 然后在 Array 文本框中输入 员工工资表 A3 M22 在 Row num 文本框中输入 A2 在 Column num 文本框中输入 2 单击 确定 接钮返回工作表中 此时在单元格C3中则会显示编号为1008员工的姓名 依次在单元格C4 C5 C6 C7 C8 C9 C10 E7 E8 E9 E10中输入如下公式 INDEX 员工工资表 A3 M22