制作员工工资管理表.ppt

第4章 制作员工工资管理表 4.1 获取外部数据 l Excel 2003可以获取的外部数据库文 件类型有很多种，如：Access、FoxPro、 dBase、SQL Server、Lotus、Oracle、 HTML文件、Web档案、XML文件和文本 数据等。对于这些文件，Excel都能访问， 并能将这些文件转化为Excel中的表格形式 。 l步骤1 启动Excel 2003后，单击“常用”工具栏 上的的“打开”按钮“”，或选择“文件”|“打开”命 令。 l步骤2 弹出“打开”对话框，在“查找范围”下拉 列表中选择“员工基本情况表.xls”文件位置， 如图所示。选中要导入的文件后，单击“打开” 按钮，即可获取所要的数据记录。 4.2 快速准确录入数据 l步骤1 在Excel中打开“员工基本情况表.txt”， 在“职务”列右边插入“性别”列。如图所示。 l步骤2 鼠标右击E3单元格，从弹出的快捷菜选 择“设置单元格格式”命令，弹出“设置单元格格 式”对话框，选择“数字”选项卡，在“分类”列表 框中单击“自定义”选项，然后在右侧的“类型” 文本框中输入“=1“男”；=2“女”（英文状态 下输入符号），如图所示。 l步骤3 单击“确定”按钮，返回工作表中，复制 E3单元格，再选中“E4：E22”单元格区域，右 键单击该区域，在弹出的快捷菜单中选择“选 择性粘贴”命令，在出现的“选择性粘贴”对话 框中“粘贴”选项下单击“格式”单选按钮，如图 所示。再单击“确定”按钮即可完成在“E4： E22”单元格区域复制公式。 l步骤4 在“性别”列的单元格中输入“1”或者“2”， 按下“Enter”回车键即可输入“男”或“女”，如图所 示。 l步骤5 将“性别”列数据输入完成后，再将“底 薪”列、“工龄工资”列、“岗位工资”列和“基本 工资”列的数据统一设置为“货币型”，并保留2 位小数，并以“员工基本情况表.xls”保存在指 定位置。效果图如图所示。 4.3 使用公式和函数 l 公式是单元格中一系列值、单元格的引 用、名称或运算符的组合，可生成新的值 。可利用公式对工作表的数据进行加、减 、乘、除等运算。在公式中，不但可以引 用同一个工作表中的不同单元格，也可以 引用同一工作簿中的不同工作表中的单元 格，还能引用其他工作簿任意工作表中的 单元格。 4.3.1 使用公式求基本工资 l Excel中的公式由等号、单元格和运算 符号三部分组成，以“=”开头，公式中也可 以出现函数。公式实质上也是单元格数据 ，Excel会将输入单元格中的以等号开头的 数据自动认为是公式。接下来介绍以简单 公式求出“基本工资记录表”中的基本工资 项，具体操作步骤如下。 l步骤1 打开“员工基本情况表.xls”，在“ 基本工资”列中的单元格K3中输入公式 “=H3+I3+J3”，如图所示。 l步骤2 按编辑栏中的“ ”输入按钮输入公 式，或直接按“Enter”回车键确认公式输 入，此时单元格K3中的数据如图所示。 l步骤3 拖动K3单元格右下角的填充柄， 用自动填充功能计算出所有员工的基本工 资，如图所示。 4.3.2 引用函数求年龄 l 函数是Excel 2003内置的一种复杂公 式，其功能是完成某些特定计算。所有的 函数都包括一个函数名和一系列参数，其 格式为“函数名（参数值1，参数值2）” 。函数名表示将执行的操作，参数是函数 中用来执行操作或计算的数值。合理地使 用函数，可以大大节省用户的输入时间， 简化公式的输入。 l步骤1 打开“员工基本情况表.xls”，单击“年 龄”列中的单元格G3，在其中输入公式 “=YEAR（TODAY（）-YRAR（F3）”公 式，如图所示。按“Enter”回车键确认公式输 入，则单元格G3显示“1900年2月”。 l步骤2 右键单击“G3”单元格，从弹出的快捷菜 选择“设置单元格格式”命令，弹出“设置单元格 格式”对话框，选择“数字”选项卡，在“分类”列 表框中单击“数字”选项或“常规”选项，再单击“ 确定”按钮。返回工作表中，拖动G3单元格右下 角的填充柄，用自动填充功能计算出所有员工的 年龄，如图所示。 4.4 创建员工考勤表 l每个单位都有其考勤制度，如： l（1）迟到、早退与旷工制度。员工迟到或早退在1小 时以内，扣当月工资的1%。 l（2）旷工。员工迟到、早退1小时以上，记旷工一次 ，扣当月工资10%。 l（3）请假制度。请假的时间按小时计算，若一个月累 计请假时间不超过8小时，则不扣工资。超过8小时以 外的，4小时以内按0.5天计算，4小时以外的按1天计 算。 l（4）事假：员工因私人原因请假，事假期间不支付薪 水。 l（5）病假：员工因病请假，支付80%的薪水。 l（6）对出全勤的员工，全勤奖为当月工资的10%。 l国家法定假均支付全额薪水，因工作需要加班，加班 费100/天。 4.4.1 创建考勤表 l步骤1 在Excel中打开“员工基本情况表 .xls”，将其中的“Sheet2”工作表重命名 为“12月份考勤表”，然后输入表头字段“ 姓名”、“部门”和“日期”。 l步骤2 打开“员工基本工资表”工作表， 选中B3：C22单元格区域，将其内容复 制到“12月份考勤表”中。 l步骤3 在D1单元格中输入“12月1日”，拖动D1 单元格右下角的填充柄填充日期，单击“自动 填充选项”按钮，从展开的下拉列表中单击“以 工作日填充”选项，如图所示。此时系统会自 动将周六和周日去掉，不计入考勤日期。 l步骤4 鼠标右击选定的D1：Y1单元格区域，从 弹出的快捷菜选择“设置单元格格式”命令，弹 出“设置单元格格式”对话框，选择“数字”选项 卡，在“分类”列表框中单击“自定义”选项，然 后在右侧的“类型”文本框中输入“d“日”（英文 状态下输入符号），单击“确定”按钮，返回工 作表中，设置日期格式后效果如图所示。 4.4.2 录入考勤记录 l步骤1 在“12月考勤表”中的“日期”列输入每名 员工的“上班”和“下班”标题，然后选中D2：Z41 区域，鼠标右击D2：Z41区域，从弹出的快捷菜 选择“设置单元格格式”命令，打开“设置单元格 格式”对话框，选择“数字”选项卡，在“分类”列 表框中单击“时间”选项，然后在右侧的“类型”列 表框中单击“13：00”选项，如图所示。 l步骤2 单击“确定”按钮，返回工作表，在 D2：Z41单元格区域内输入每位员工的上下 班时间，如图所示。 4.4.3 引用函数统计出勤情况 l 单位考勤制度规定上下班时间分别为8 ：00和17：30；若大于8：00小于9：00上 班的，则视为迟到，或大于16：30小于17 ：30，则视为早退。若大于9：00上班或小 于16：30下班则视为旷工。 l步骤1 在日期行之后的单元格中依次输入“ 迟到”、“早退”、“旷工”、“病假”和“事假” 等需要统计的列标题。并将这几列单元格上 下两两合并，对应于姓名行。如图所示。 l步骤2 统计员工“迟到”结果。单击AA2单元 格，在其中输入公式：=SUMPRODUCT(C2 ：Z2TIMEVALUE(”8:00”)*(C2： Z2TIMEVALUE(”16:30”)*(C3:Z3TIMEVALUE(”9 :00”)*(C2:Z30,H2*0.01*C2)+IF(D20,H2*0.01*D2) +IF(E20,H2*0.1*E2)+IF(F20,H2/22*0.2*F2) +IF(G20,H2/22*G2)，按Enter键即可得到第 一名员工应扣的工资，利用自动填充功能填充 该列的其他单元格，并设置数字格式为数值， 保留小数点2位。 l步骤7 根据全勤奖制度，应奖励全勤员工当月工资 的10%作奖金。单击J2单元格，在其中输入公式： =IF(I2=0,H2*0.1,0)，使用自动填充功能完成该列数 据的填充。数据并保留2位小数位。 4.6 创建并编辑员工工资管理表 l4.6.1 创建员工工资管理表 l步骤1 在“员工基本情况表”中插入一新工作表， 将其标签名改为“12月份工资管理表”，然后在工 作表中输入标题“员工工资管理表”，在第2行输入 表头字段：月份、员工编号、姓名、部门、基本 工资、住房补贴、交通补贴、全勤奖、考勤扣款 、社保金、应发工资、应扣所得税和实发工资。 l步骤2 引用数据。使用引用公式引用“员工基 本工资表”中“员工编号”、“姓名”、“部门”、“ 基本工资”等列数据。 l步骤3 输入“月份”列数据。先选中A3：A22单 元格区域，设置单元格式格式如下图所示。然 后在A3单元格中输入“2008-12-31”，再按住 Ctrl键把月份复制到相应的单元格。 l步骤4 打开第4章建立的“员工福利表.xls”，返 回“12月份工资管理表”，单击F3单元格，在其 中输入引用公式：=员工福利表 .xlsSheet1!D3，在“员工工资管理表”中“住 房补贴”列中引用“员工福利表.xls”Sheet1中 相对应的数据。同理，在“员工工资管理表”中 “交通补贴”和“社保金”列中引用“员工福利表 .xls”Sheet1中相对应的数据，并填充完所选 列数据。 l步骤5 引用“12月份考勤统计表”中“应扣 工资”和“全勤奖”两列数据。在“12月份 工资管理表”中分别单击H3和I3单元格， 在其中分别输入引用公式：=12月份考 勤统计表!J2和=12月份考勤统计表!I2 ，再选中H3：I3单元格区域，利用填充 柄填充完其余数据。 l步骤6 设置数据格式。在“员工工资管理 表”中，右键单击选中的E3：M22单元格 区域，从弹出的快捷菜选择“设置单元格 格式”命令，弹出“设置单元格格式”对话 框，选择“数字”选项卡，在“分类”列表 框中单击“货币”选项，选择样式并保留 小数位2位。 4.6.2 公式和函数运用 l一般工资表中都包含所得税一项，而所得税是根 据员工的应发工资进行计算。而“应发工资”是利 用“员工工资管理表”中的“基本工资”、“住房补贴 ”、“交通补贴”、“全勤奖”等计算得出的。“实发 工资”是“应发工资”减去“所得税”、“考勤扣款”、 “社保金”得出来的。 l在“员工工资管理表”中至少要建立3个公式，一个 是计算“所得税”，一个是计算“应发工资”，一个 是计算“实发工资”。 l表5-1 所得税税率及扣除金额（工资、 薪金所得适用） l步骤1 在“应发工资”一列中K3单元格中输入公 式：=E3+F3+G3+H3-I3-J3，或用鼠标单击引用 相应的单元格，按下Enter键，将根据公式计算 出相应的值。拖动K3单元格右下角的填充柄， 用自动填充功能计算出所有员工的应发工资。 l步骤2 计算“所得税”。在“应发工资”一列中L3单元 格中输入公式：=IF(K32000,0,IF(K3- 2000)500,(K3-2000)*0.05,IF(K3-2000)2000,(K3- 2000)*0.1-25,IF(K3-2000)5000,(K3-2000)*0.15- 125,(K3-2000)*0.2-375)，用填充柄填充“应扣所得 税”列其余数据。 l步骤3 计算实发工资。在单元格L3中输入公式 ：= K3-L3。按回车键确认公式输入，然后利 用自动句柄填充功能填充数据。 4.8 自动筛选和排序功能 l4.8.1 应用自动筛选 l步骤1 选择命令。单击工作表中任意含有数据的单元 格，单击菜单栏“数据”|“筛选”|“自动筛选”命令，字 段行的所有单元格右侧均会出现一个下拉按钮，如图 所示。 l步骤2 筛选数据。单击“部门”右侧的下拉按钮， 从展开的下拉列表中单击“财务处”选项，单击“确 定”按钮，将筛选出满足条件的记录，如图所示。 l步骤3 取消筛选。若要取消自动筛选功能，只需 再选择“数据”|“筛选”|“自动筛选”命令，取消勾选“ 自动筛选”复选框即可。 小贴士： l 若要筛选出基本工资大于3000元，小于4000元的 记录，就要使用“自定义”选项了。具体操作是：单击“ 基本工资”右侧的下拉按钮，在出现的下拉列表中选 择“自定义”选项，在打开的“自定义自动筛选方式”对话 框中设置筛选方式，如图所示。并单击“确定”按钮， 即可显示筛选出的记录。 4.8.2 应用排序功能 l步骤1 选择命令。选定工作表中含有数据的任意单元 格，单击菜单栏中“数据”|“排序”命令，在出现的“排序” 对话框中的“主要关键字”下拉列表框中选择“实发工资” 项，再单击其右侧“降序”单选按钮。在“次要关键字”中 选择“员工编号”项，再单击右侧“升序”。如图所示。 l步骤2 设置对话框。单击“确定”按钮，此时系统弹出 如图左所示的对话框，提示用户将按照文本或数字进 行排序，保持默认设置 l步骤3 完成排序设置。再单击“确定”按钮返回工作表 中，可看到排序后的效果，如图右所示。 4.9 数据透视表的应用 l 数据透视表是一种对大量数据快速汇总 和建立交叉列表的交互式表格，可以快速 合并和比较大量数据，具有透视和筛选功 能和极强的数据分析功能，它能转换行或 列以查看数据的不同汇总结果，也可以显 示不同页面以筛选数据，还可以根据需要 显示区域中的明细数据。 l步骤1 选定单元格。单击“员工工资管理表”中任 意含有数据单元格。 l步骤2 选择命令。单击菜单栏上“数据”|“数据透 视表和数据透视图”命令，打开“数据透视表和数 据透视图向导”对话框，如图所示。 l步骤3 选定区域。在“请指定待分析数据的数据源类 型”栏中选择“Microsoft Excel数据列表或数据库”选项 ，然后在“所需创建的报表类型”栏中选择“数据透视表” ，单击下一步按钮，打开如图左所示的对话框。 l步骤4 选择透视表显示位置。单击“下一步”按钮，打 开如图右所示的对话框，选择“新建工作表”选项。 l步骤5 透视表布局。单击“布局”选项，打开如图左所 示的对话框。在“数据透视表布局”对话框中，可以设 计数据透视表的布局。将数据清单中的字段名拖至页 、行、列或数据区域。例如要将“住房补贴”、“交通补 贴”、“基本工资”、“应发工资”和“实发工资”按部门汇总 的操作是：把“月份”拖到“页”，把“部门”拖至“行”，把“ 住房补贴”、“交通补贴”、“基本工资”、“应发工资”和“ 实发工资”等字段名拖至“数据”区域，如图右所示。 l步骤6