办公自动化第4章+制作员工工资管理表.ppt

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