第4章制作员工工资管理表

第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(”9:00”)))。按下“Enter”回车键，统计出迟到的结果，如图所示。步骤3统计员工“早退”结果。单击AB2单元格，在其中输入公式：=SUMPRODUCT((C3：Z3TIMEVALUE(”16:30”))*(C3:Z3TIMEVALUE(”17:30”)))。按下“Enter”回车键，统计出早退的结果，如图所示。步骤4统计员工“旷工”结果。单击AC2单元格，在其中输入公式：=SUMPRODUCT((C2:Z3TIMEVALUE(”9:00”))*(C2:Z3TIMEVALUE(”16：30”)))。按下“Enter”回车键，统计出早退的结果，如图所示。步骤5统计员工“病假”结果。单击AD2单元格，在其中输入公式：=COUNTIF（C2：Z3，”病假”），按下“Enter”回车键，即可统计出员工请病假的结果，如图所示。步骤6统计员工“事假”结果。单击AE2单元格，在其中输入公式：=COUNTIF（C2：Z3，“事假”），按下“Enter”回车键，即可统计出员工请事假的结果，如图所示。步骤7选中AA2：AE2单元格区域，然后将鼠标移至AE2单元格右下角处，当指针变为实心十字形时，按住鼠标左键拖至AE41时，释放鼠标左键，即可完成其他员工的考勤统计工作，如图所示。4.5创建出勤统计表4.5.1创建考勤表步骤1在“员工基本情况表”中在“员工基本情况表”中插入一新工作表，将其标签名改为“12月份考勤统计表”，然后在工作表中输入标题“2008年12月份考勤统计表”，在第2行输入表头字段：姓名、部门、迟到次数、早退次数、旷工、病假天数、事假天数、本月工资、应扣工资和全勤奖。步骤2使用引用公式引用“员工基本工资表”工作表“姓名”数据。单击“12月份考勤统计表”A2单元格，在其中输入公式“=员工基本工资表!B3”；或只输入“=”，再单击“员工基本工资表”中对应的单元格如B3，按“Enter”回车键确认输入，如图所示。步骤3利用自动填充功能填充该列中的其他单元格。在“12月份考勤统计表”中用同样方式引用“员工基本工资表”中的“部门”和“基本工资”列数据；VLOOKUP（）函数的作用是在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列的数值。格式：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)4.5.2不同工作表利用函数统计数据步骤1单击C2单元格，在其中输入公式：=VLOOKUP(A2,'12月考勤表’!$A$1:$AE$41,27,0)，按Enter键即可得到该员工迟到的次数，并利用自动填充功能填充该列的其他单元格，如图所示。步骤2单击D2单元格，在其中输入公式：=VLOOKUP(A2,‘12月考勤表’!$A$1:$AE$41,28,0)，按Enter键即可得到该员工早退的次数，并利用自动填充功能填充该列的其他单元格。步骤3单击E2单元格，在其中输入公式：=VLOOKUP(A2,'12月考勤表'!$A$1:$AE$41,29,0)，按Enter键即可得到该员工旷工的次数，并利用自动填充功能填充该列的其他单元格。步骤4单击F2单元格，在其中输入公式：=VLOOKUP(A2,'12月考勤表'!$A$1:$AE$41,30,0)，按Enter键即可得到该员工病假天数，并利用自动填充功能填充该列的其他单元格。步骤5单击E2单元格，在其中输入公式：=VLOOKUP(A2,'12月考勤表'!$A$1:$AE$41,31,0)，按Enter键即可得到该员工事假天数，并利用自动填充功能填充该列的其他单元格。效果如图。步骤6计算应扣工资。制度中规定：迟到和早退扣工资的1%/次；旷工扣工资的10%/次；病假扣当天工资的20%；事假扣当天工资。单击A2单元格，在其中输入公式：=IF(C20,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单元格，在其中输入引用公式：=[员工福利表.xls]Sheet1