制作员工工资管理表.ppt

《制作员工工资管理表.ppt》由会员分享，可在线阅读，更多相关《制作员工工资管理表.ppt（63页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、第第4章章 制作员工工资管理表制作员工工资管理表4.1 获取外部数据获取外部数据 Excel 2003可以获取的外部数据库文可以获取的外部数据库文件类型有很多种，如：件类型有很多种，如：Access、FoxPro、dBase、SQL Server、Lotus、Oracle、HTML文件、文件、Web档案、档案、XML文件和文本文件和文本数据等。对于这些文件，数据等。对于这些文件，Excel都能访问，都能访问，并能将这些文件转化为并能将这些文件转化为Excel中的表格形式。中的表格形式。步骤步骤步骤步骤1 1 启动启动启动启动Excel 2003Excel 2003后，单击后，单击后，单击后，单

2、击“常用常用常用常用”工具栏工具栏工具栏工具栏上的的上的的上的的上的的“打开打开打开打开”按钮按钮按钮按钮“”“”，或选择，或选择，或选择，或选择“文件文件文件文件”|“”|“打开打开打开打开”命命命命令。令。令。令。步骤步骤步骤步骤2 2 弹出弹出弹出弹出“打开打开打开打开”对话框，在对话框，在对话框，在对话框，在“查找范围查找范围查找范围查找范围”下拉下拉下拉下拉列表中选择列表中选择列表中选择列表中选择“员工基本情况表员工基本情况表员工基本情况表员工基本情况表.xls”.xls”文件位置，文件位置，文件位置，文件位置，如图所示。选中要导入的文件后，单击如图所示。选中要导入的文件后，单击如图

3、所示。选中要导入的文件后，单击如图所示。选中要导入的文件后，单击“打开打开打开打开”按钮，即可获取所要的数据记录。按钮，即可获取所要的数据记录。按钮，即可获取所要的数据记录。按钮，即可获取所要的数据记录。4.2 快速准确录入数据快速准确录入数据步骤步骤步骤步骤1 1 在在在在ExcelExcel中打开中打开中打开中打开“员工基本情况表员工基本情况表员工基本情况表员工基本情况表.txt”.txt”，在在在在“职务职务职务职务”列右边插入列右边插入列右边插入列右边插入“性别性别性别性别”列。如图所示。列。如图所示。列。如图所示。列。如图所示。步骤步骤步骤步骤2 2 鼠标右击鼠标右击鼠标右击鼠标右击

4、E3E3单元格，从弹出的快捷菜选单元格，从弹出的快捷菜选单元格，从弹出的快捷菜选单元格，从弹出的快捷菜选择择择择“设置单元格格式设置单元格格式设置单元格格式设置单元格格式”命令，弹出命令，弹出命令，弹出命令，弹出“设置单元格格设置单元格格设置单元格格设置单元格格式式式式”对话框，选择对话框，选择对话框，选择对话框，选择“数字数字数字数字”选项卡，在选项卡，在选项卡，在选项卡，在“分类分类分类分类”列表列表列表列表框中单击框中单击框中单击框中单击“自定义自定义自定义自定义”选项，然后在右侧的选项，然后在右侧的选项，然后在右侧的选项，然后在右侧的“类型类型类型类型”文文文文本框中输入本框中输入本框

5、中输入本框中输入“=1“=1“男男男男”；=2“=2“女女女女”（英文状态下（英文状态下（英文状态下（英文状态下输入符号），如图所示。输入符号），如图所示。输入符号），如图所示。输入符号），如图所示。步骤步骤步骤步骤3 3 单击单击单击单击“确定确定确定确定”按钮，返回工作表中，复制按钮，返回工作表中，复制按钮，返回工作表中，复制按钮，返回工作表中，复制E3E3单元格，再选中单元格，再选中单元格，再选中单元格，再选中“E4“E4：E22”E22”单元格区域，右单元格区域，右单元格区域，右单元格区域，右键单击该区域，在弹出的快捷菜单中选择键单击该区域，在弹出的快捷菜单中选择键单击该区域，在弹出的

6、快捷菜单中选择键单击该区域，在弹出的快捷菜单中选择“选选选选择性粘贴择性粘贴择性粘贴择性粘贴”命令，在出现的命令，在出现的命令，在出现的命令，在出现的“选择性粘贴选择性粘贴选择性粘贴选择性粘贴”对话对话对话对话框中框中框中框中“粘贴粘贴粘贴粘贴”选项下单击选项下单击选项下单击选项下单击“格式格式格式格式”单选按钮，如图单选按钮，如图单选按钮，如图单选按钮，如图所示。再单击所示。再单击所示。再单击所示。再单击“确定确定确定确定”按钮即可完成在按钮即可完成在按钮即可完成在按钮即可完成在“E4“E4：E22”E22”单元格区域复制公式。单元格区域复制公式。单元格区域复制公式。单元格区域复制公式。步骤

7、步骤步骤步骤4 4 在在在在“性别性别性别性别”列的单元格中输入列的单元格中输入列的单元格中输入列的单元格中输入“1”“1”或者或者或者或者“2”“2”，按下按下按下按下“Enter”“Enter”回车键即可输入回车键即可输入回车键即可输入回车键即可输入“男男男男”或或或或“女女女女”，如图所，如图所，如图所，如图所示。示。示。示。步骤步骤步骤步骤5 5 将将将将“性别性别性别性别”列数据输入完成后，再将列数据输入完成后，再将列数据输入完成后，再将列数据输入完成后，再将“底底底底薪薪薪薪”列、列、列、列、“工龄工资工龄工资工龄工资工龄工资”列、列、列、列、“岗位工资岗位工资岗位工资岗位工资”列

8、和列和列和列和“基本基本基本基本工资工资工资工资”列的数据统一设置为列的数据统一设置为列的数据统一设置为列的数据统一设置为“货币型货币型货币型货币型”，并保留，并保留，并保留，并保留2 2位小数，并以位小数，并以位小数，并以位小数，并以“员工基本情况表员工基本情况表员工基本情况表员工基本情况表.xls”.xls”保存在指保存在指保存在指保存在指定位置。效果图如图所示。定位置。效果图如图所示。定位置。效果图如图所示。定位置。效果图如图所示。4.3 使用公式和函数使用公式和函数 公式是单元格中一系列值、单元格的引公式是单元格中一系列值、单元格的引用、名称或运算符的组合，可生成新的值。用、名称或运算

9、符的组合，可生成新的值。可利用公式对工作表的数据进行加、减、可利用公式对工作表的数据进行加、减、乘、除等运算。在公式中，不但可以引用乘、除等运算。在公式中，不但可以引用同一个工作表中的不同单元格，也可以引同一个工作表中的不同单元格，也可以引用同一工作簿中的不同工作表中的单元格，用同一工作簿中的不同工作表中的单元格，还能引用其他工作簿任意工作表中的单元还能引用其他工作簿任意工作表中的单元格。格。4.3.1使用公式求基本工资使用公式求基本工资 Excel中的公式由等号、单元格和运算中的公式由等号、单元格和运算符号三部分组成，以符号三部分组成，以“=”开头，公式中也可开头，公式中也可以出现函数。公式

10、实质上也是单元格数据，以出现函数。公式实质上也是单元格数据，Excel会将输入单元格中的以等号开头的数会将输入单元格中的以等号开头的数据自动认为是公式。接下来介绍以简单公据自动认为是公式。接下来介绍以简单公式求出式求出“基本工资记录表基本工资记录表”中的基本工资项，中的基本工资项，具体操作步骤如下。具体操作步骤如下。步骤步骤1打开打开“员工基本情况表员工基本情况表.xls”，在，在“基本工资基本工资”列中的单元格列中的单元格K3中输入公式中输入公式“=H3+I3+J3”，如图所示。，如图所示。步骤步骤2按编辑栏中的按编辑栏中的“”输入按钮输入公输入按钮输入公式，或直接按式，或直接按“Enter

11、”回车键确认公式输回车键确认公式输入，此时单元格入，此时单元格K3中的数据如图所示。中的数据如图所示。步骤步骤3拖动拖动K3单元格右下角的填充柄，单元格右下角的填充柄，用自动填充功能计算出所有员工的基本工用自动填充功能计算出所有员工的基本工资，如图所示。资，如图所示。4.3.2引用函数求年龄引用函数求年龄 函数是函数是Excel 2003内置的一种复杂公内置的一种复杂公式，其功能是完成某些特定计算。所有的式，其功能是完成某些特定计算。所有的函数都包括一个函数名和一系列参数，其函数都包括一个函数名和一系列参数，其格式为格式为“函数名（参数值函数名（参数值1，参数值，参数值2）”。函数名表示将执行

12、的操作，参数是函数中函数名表示将执行的操作，参数是函数中用来执行操作或计算的数值。合理地使用用来执行操作或计算的数值。合理地使用函数，可以大大节省用户的输入时间，简函数，可以大大节省用户的输入时间，简化公式的输入。化公式的输入。步骤步骤1 打开打开“员工基本情况表员工基本情况表.xls”，单击，单击“年年龄龄”列中的单元格列中的单元格G3，在其中输入公式，在其中输入公式“=YEAR（TODAY（）（）-YRAR（F3）”公公式，如图所示。按式，如图所示。按“Enter”回车键确认公式输回车键确认公式输入，则单元格入，则单元格G3显示显示“1900年年2月月”。步骤步骤步骤步骤2 2 右键单击右

13、键单击右键单击右键单击“G3”“G3”单元格，从弹出的快捷菜单元格，从弹出的快捷菜单元格，从弹出的快捷菜单元格，从弹出的快捷菜选择选择选择选择“设置单元格格式设置单元格格式设置单元格格式设置单元格格式”命令，弹出命令，弹出命令，弹出命令，弹出“设置单元格设置单元格设置单元格设置单元格格式格式格式格式”对话框，选择对话框，选择对话框，选择对话框，选择“数字数字数字数字”选项卡，在选项卡，在选项卡，在选项卡，在“分类分类分类分类”列列列列表框中单击表框中单击表框中单击表框中单击“数字数字数字数字”选项或选项或选项或选项或“常规常规常规常规”选项，再单击选项，再单击选项，再单击选项，再单击“确定确定

14、确定确定”按钮。返回工作表中，拖动按钮。返回工作表中，拖动按钮。返回工作表中，拖动按钮。返回工作表中，拖动G3G3单元格右下单元格右下单元格右下单元格右下角的填充柄，用自动填充功能计算出所有员工的角的填充柄，用自动填充功能计算出所有员工的角的填充柄，用自动填充功能计算出所有员工的角的填充柄，用自动填充功能计算出所有员工的年龄，如图所示。年龄，如图所示。年龄，如图所示。年龄，如图所示。4.4 创建员工考勤表创建员工考勤表每个单位都有其考勤制度，如：每个单位都有其考勤制度，如：（1 1）迟到、早退与旷工制度。员工迟到或早退在）迟到、早退与旷工制度。员工迟到或早退在1 1小小时以内，扣当月工资的时以

15、内，扣当月工资的1%1%。（2 2）旷工。员工迟到、早退）旷工。员工迟到、早退1 1小时以上，记旷工一次，小时以上，记旷工一次，扣当月工资扣当月工资10%10%。（3 3）请假制度。请假的时间按小时计算，若一个月累）请假制度。请假的时间按小时计算，若一个月累计请假时间不超过计请假时间不超过8 8小时，则不扣工资。超过小时，则不扣工资。超过8 8小时以小时以外的，外的，4 4小时以内按小时以内按0.50.5天计算，天计算，4 4小时以外的按小时以外的按1 1天计天计算。算。（4 4）事假：员工因私人原因请假，事假期间不支付薪）事假：员工因私人原因请假，事假期间不支付薪水。水。（5 5）病假：员工

16、因病请假，支付）病假：员工因病请假，支付80%80%的薪水。的薪水。（6 6）对出全勤的员工，全勤奖为当月工资的）对出全勤的员工，全勤奖为当月工资的10%10%。国家法定假均支付全额薪水，因工作需要加班，加班国家法定假均支付全额薪水，因工作需要加班，加班费费100/100/天。天。4.4.1创建考勤表创建考勤表 步骤步骤1 在在Excel中打开中打开“员工基本情况表员工基本情况表.xls”，将其中的，将其中的“Sheet2”工作表重命名工作表重命名为为“12月份考勤表月份考勤表”，然后输入表头字段，然后输入表头字段“姓名姓名”、“部门部门”和和“日期日期”。步骤步骤2 打开打开“员工基本工资表

17、员工基本工资表”工作表，工作表，选中选中B3：C22单元格区域，将其内容复单元格区域，将其内容复制到制到“12月份考勤表月份考勤表”中。中。步骤步骤步骤步骤3 3 在在在在D1D1单元格中输入单元格中输入单元格中输入单元格中输入“12“12月月月月1 1日日日日”，拖动，拖动，拖动，拖动D1D1单元格右下角的填充柄填充日期，单击单元格右下角的填充柄填充日期，单击单元格右下角的填充柄填充日期，单击单元格右下角的填充柄填充日期，单击“自动自动自动自动填充选项填充选项填充选项填充选项”按钮，从展开的下拉列表中单击按钮，从展开的下拉列表中单击按钮，从展开的下拉列表中单击按钮，从展开的下拉列表中单击“以

18、以以以工作日填充工作日填充工作日填充工作日填充”选项，如图所示。此时系统会自选项，如图所示。此时系统会自选项，如图所示。此时系统会自选项，如图所示。此时系统会自动将周六和周日去掉，不计入考勤日期。动将周六和周日去掉，不计入考勤日期。动将周六和周日去掉，不计入考勤日期。动将周六和周日去掉，不计入考勤日期。步骤步骤步骤步骤4 4 鼠标右击选定的鼠标右击选定的鼠标右击选定的鼠标右击选定的D1D1：Y1Y1单元格区域，从单元格区域，从单元格区域，从单元格区域，从弹出的快捷菜选择弹出的快捷菜选择弹出的快捷菜选择弹出的快捷菜选择“设置单元格格式设置单元格格式设置单元格格式设置单元格格式”命令，弹命令，弹命

19、令，弹命令，弹出出出出“设置单元格格式设置单元格格式设置单元格格式设置单元格格式”对话框，选择对话框，选择对话框，选择对话框，选择“数字数字数字数字”选项选项选项选项卡，在卡，在卡，在卡，在“分类分类分类分类”列表框中单击列表框中单击列表框中单击列表框中单击“自定义自定义自定义自定义”选项，然选项，然选项，然选项，然后在右侧的后在右侧的后在右侧的后在右侧的“类型类型类型类型”文本框中输入文本框中输入文本框中输入文本框中输入“d“d“日日日日”（英文（英文（英文（英文状态下输入符号），单击状态下输入符号），单击状态下输入符号），单击状态下输入符号），单击“确定确定确定确定”按钮，返回工按钮，返回

20、工按钮，返回工按钮，返回工作表中，设置日期格式后效果如图所示。作表中，设置日期格式后效果如图所示。作表中，设置日期格式后效果如图所示。作表中，设置日期格式后效果如图所示。4.4.2录入考勤记录录入考勤记录步骤步骤步骤步骤1 1 在在在在“12“12月考勤表月考勤表月考勤表月考勤表”中的中的中的中的“日期日期日期日期”列输入每名列输入每名列输入每名列输入每名员工的员工的员工的员工的“上班上班上班上班”和和和和“下班下班下班下班”标题，然后选中标题，然后选中标题，然后选中标题，然后选中D2D2：Z41Z41区域，鼠标右击区域，鼠标右击区域，鼠标右击区域，鼠标右击D2D2：Z41Z41区域，从弹出的

21、快捷菜区域，从弹出的快捷菜区域，从弹出的快捷菜区域，从弹出的快捷菜选择选择选择选择“设置单元格格式设置单元格格式设置单元格格式设置单元格格式”命令，打开命令，打开命令，打开命令，打开“设置单元格设置单元格设置单元格设置单元格格式格式格式格式”对话框，选择对话框，选择对话框，选择对话框，选择“数字数字数字数字”选项卡，在选项卡，在选项卡，在选项卡，在“分类分类分类分类”列列列列表框中单击表框中单击表框中单击表框中单击“时间时间时间时间”选项，然后在右侧的选项，然后在右侧的选项，然后在右侧的选项，然后在右侧的“类型类型类型类型”列列列列表框中单击表框中单击表框中单击表框中单击“13“13：00”0

22、0”选项，如图所示。选项，如图所示。选项，如图所示。选项，如图所示。步骤步骤2 单击单击“确定确定”按钮，返回工作表，在按钮，返回工作表，在D2：Z41单元格区域内输入每位员工的上下单元格区域内输入每位员工的上下班时间，如图所示。班时间，如图所示。4.4.3引用函数统计出勤情况引用函数统计出勤情况 单位考勤制度规定上下班时间分别为单位考勤制度规定上下班时间分别为8：00和和17：30；若大于；若大于8：00小于小于9：00上上班的，则视为迟到，或大于班的，则视为迟到，或大于16：30小于小于17：30，则视为早退。若大于，则视为早退。若大于9：00上班或小上班或小于于16：30下班则视为旷工。

23、下班则视为旷工。步骤步骤1 在日期行之后的单元格中依次输入在日期行之后的单元格中依次输入“迟到迟到”、“早退早退”、“旷工旷工”、“病假病假”和和“事假事假”等需要统计的列标题。并将这几列单元格上等需要统计的列标题。并将这几列单元格上下两两合并，对应于姓名行。如图所示。下两两合并，对应于姓名行。如图所示。步骤步骤2 统计员工统计员工“迟到迟到”结果。单击结果。单击AA2单元单元格，在其中输入公式：格，在其中输入公式：=SUMPRODUCT(C2：Z2TIMEVALUE(”8:00”)*(C2：Z2TIMEVALUE(”16:30”)*(C3:Z3TIMEVALUE(”9:00”)*(C2:Z3

24、0,H2*0.01*C2)+IF(D20,H2*0.01*D2)=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(E20,H2*0.1*E2)+IF(F20,H2/22*0.2*F2)+IF(G20,H2/22*G2)+IF(G20,H2/22*G2)，按，按，按，按EnterEnter键即可得到第键即可得到第键即可得到第键即可得到第一名员工应扣的工资，利用自动填充功能填充一名员工应扣的工资，利用自动填充功能填充一名员工应扣的工资，利用自动填充功能填充一名员工应扣的工资，利用自动填充

25、功能填充该列的其他单元格，并设置数字格式为数值，该列的其他单元格，并设置数字格式为数值，该列的其他单元格，并设置数字格式为数值，该列的其他单元格，并设置数字格式为数值，保留小数点保留小数点保留小数点保留小数点2 2位。位。位。位。步骤步骤步骤步骤7 7 根据全勤奖制度，应奖励全勤员工当月工资根据全勤奖制度，应奖励全勤员工当月工资根据全勤奖制度，应奖励全勤员工当月工资根据全勤奖制度，应奖励全勤员工当月工资的的的的10%10%作奖金。单击作奖金。单击作奖金。单击作奖金。单击J2J2单元格，在其中输入公式：单元格，在其中输入公式：单元格，在其中输入公式：单元格，在其中输入公式：=IF(I2=0,H2

26、*0.1,0)=IF(I2=0,H2*0.1,0)，使用自动填充功能完成该列数，使用自动填充功能完成该列数，使用自动填充功能完成该列数，使用自动填充功能完成该列数据的填充。数据并保留据的填充。数据并保留据的填充。数据并保留据的填充。数据并保留2 2位小数位。位小数位。位小数位。位小数位。4.6 创建并编辑员工工资管理表创建并编辑员工工资管理表 4.6.1创建员工工资管理表创建员工工资管理表步骤步骤步骤步骤1 1 在在在在“员工基本情况表员工基本情况表员工基本情况表员工基本情况表”中插入一新工作表，中插入一新工作表，中插入一新工作表，中插入一新工作表，将其标签名改为将其标签名改为将其标签名改为将

27、其标签名改为“12“12月份工资管理表月份工资管理表月份工资管理表月份工资管理表”，然后在工，然后在工，然后在工，然后在工作表中输入标题作表中输入标题作表中输入标题作表中输入标题“员工工资管理表员工工资管理表员工工资管理表员工工资管理表”，在第，在第，在第，在第2 2行输入行输入行输入行输入表头字段：月份、员工编号、姓名、部门、基本表头字段：月份、员工编号、姓名、部门、基本表头字段：月份、员工编号、姓名、部门、基本表头字段：月份、员工编号、姓名、部门、基本工资、住房补贴、交通补贴、全勤奖、考勤扣款、工资、住房补贴、交通补贴、全勤奖、考勤扣款、工资、住房补贴、交通补贴、全勤奖、考勤扣款、工资、住

28、房补贴、交通补贴、全勤奖、考勤扣款、社保金、应发工资、应扣所得税和实发工资。社保金、应发工资、应扣所得税和实发工资。社保金、应发工资、应扣所得税和实发工资。社保金、应发工资、应扣所得税和实发工资。步骤步骤步骤步骤2 2 引用数据。使用引用公式引用引用数据。使用引用公式引用引用数据。使用引用公式引用引用数据。使用引用公式引用“员工基员工基员工基员工基本工资表本工资表本工资表本工资表”中中中中“员工编号员工编号员工编号员工编号”、“姓名姓名姓名姓名”、“部门部门部门部门”、“基本工资基本工资基本工资基本工资”等列数据。等列数据。等列数据。等列数据。步骤步骤步骤步骤3 3 输入输入输入输入“月份月份

29、月份月份”列数据。先选中列数据。先选中列数据。先选中列数据。先选中A3A3：A22A22单单单单元格区域，设置单元格式格式如下图所示。然元格区域，设置单元格式格式如下图所示。然元格区域，设置单元格式格式如下图所示。然元格区域，设置单元格式格式如下图所示。然后在后在后在后在A3A3单元格中输入单元格中输入单元格中输入单元格中输入“2008-12-31”“2008-12-31”，再按住，再按住，再按住，再按住CtrlCtrl键把月份复制到相应的单元格。键把月份复制到相应的单元格。键把月份复制到相应的单元格。键把月份复制到相应的单元格。步骤步骤步骤步骤4 4 打开第打开第打开第打开第4 4章建立的章

30、建立的章建立的章建立的“员工福利表员工福利表员工福利表员工福利表.xls”.xls”，返，返，返，返回回回回“12“12月份工资管理表月份工资管理表月份工资管理表月份工资管理表”，单击，单击，单击，单击F3F3单元格，在其单元格，在其单元格，在其单元格，在其中输入引用公式：中输入引用公式：中输入引用公式：中输入引用公式：=员工福利表员工福利表员工福利表员工福利表.xlsSheet1!D3.xlsSheet1!D3，在，在，在，在“员工工资管理表员工工资管理表员工工资管理表员工工资管理表”中中中中“住住住住房补贴房补贴房补贴房补贴”列中引用列中引用列中引用列中引用“员工福利表员工福利表员工福利表

31、员工福利表.xls”Sheet1.xls”Sheet1中中中中相对应的数据。同理，在相对应的数据。同理，在相对应的数据。同理，在相对应的数据。同理，在“员工工资管理表员工工资管理表员工工资管理表员工工资管理表”中中中中“交通补贴交通补贴交通补贴交通补贴”和和和和“社保金社保金社保金社保金”列中引用列中引用列中引用列中引用“员工福利表员工福利表员工福利表员工福利表.xls”Sheet1.xls”Sheet1中相对应的数据，并填充完所选中相对应的数据，并填充完所选中相对应的数据，并填充完所选中相对应的数据，并填充完所选列数据。列数据。列数据。列数据。步骤步骤5 引用引用“12月份考勤统计表月份考勤

32、统计表”中中“应扣应扣工资工资”和和“全勤奖全勤奖”两列数据。在两列数据。在“12月份月份工资管理表工资管理表”中分别单击中分别单击H3和和I3单元格，单元格，在其中分别输入引用公式：在其中分别输入引用公式：=12月份考月份考勤统计表勤统计表!J2和和=12月份考勤统计表月份考勤统计表!I2，再选中，再选中H3：I3单元格区域，利用填充单元格区域，利用填充柄填充完其余数据。柄填充完其余数据。步骤步骤6 设置数据格式。在设置数据格式。在“员工工资管理员工工资管理表表”中，右键单击选中的中，右键单击选中的E3：M22单元格单元格区域，从弹出的快捷菜选择区域，从弹出的快捷菜选择“设置单元格设置单元格

33、格式格式”命令，弹出命令，弹出“设置单元格格式设置单元格格式”对话对话框，选择框，选择“数字数字”选项卡，在选项卡，在“分类分类”列表列表框中单击框中单击“货币货币”选项，选择样式并保留选项，选择样式并保留小数位小数位2位。位。4.6.2公式和函数运用公式和函数运用 一般工资表中都包含所得税一项，而所得税是根一般工资表中都包含所得税一项，而所得税是根一般工资表中都包含所得税一项，而所得税是根一般工资表中都包含所得税一项，而所得税是根据员工的应发工资进行计算。而据员工的应发工资进行计算。而据员工的应发工资进行计算。而据员工的应发工资进行计算。而“应发工资应发工资应发工资应发工资”是利是利是利是利

34、用用用用“员工工资管理表员工工资管理表员工工资管理表员工工资管理表”中的中的中的中的“基本工资基本工资基本工资基本工资”、“住房补贴住房补贴住房补贴住房补贴”、“交通补贴交通补贴交通补贴交通补贴”、“全勤奖全勤奖全勤奖全勤奖”等计算得出的。等计算得出的。等计算得出的。等计算得出的。“实发实发实发实发工资工资工资工资”是是是是“应发工资应发工资应发工资应发工资”减去减去减去减去“所得税所得税所得税所得税”、“考勤扣款考勤扣款考勤扣款考勤扣款”、“社保金社保金社保金社保金”得出来的。得出来的。得出来的。得出来的。在在在在“员工工资管理表员工工资管理表员工工资管理表员工工资管理表”中至少要建立中至少

35、要建立中至少要建立中至少要建立3 3个公式，一个个公式，一个个公式，一个个公式，一个是计算是计算是计算是计算“所得税所得税所得税所得税”，一个是计算，一个是计算，一个是计算，一个是计算“应发工资应发工资应发工资应发工资”，一个，一个，一个，一个是计算是计算是计算是计算“实发工资实发工资实发工资实发工资”。表5-1 所得税税率及扣除金额（工资、薪金所得适用）步骤步骤步骤步骤1 1 在在在在“应发工资应发工资应发工资应发工资”一列中一列中一列中一列中K3K3单元格中输入公单元格中输入公单元格中输入公单元格中输入公式：式：式：式：=E3+F3+G3+H3-I3-J3=E3+F3+G3+H3-I3-J

36、3，或用鼠标单击引用，或用鼠标单击引用，或用鼠标单击引用，或用鼠标单击引用相应的单元格，按下相应的单元格，按下相应的单元格，按下相应的单元格，按下EnterEnter键，将根据公式计算键，将根据公式计算键，将根据公式计算键，将根据公式计算出相应的值。拖动出相应的值。拖动出相应的值。拖动出相应的值。拖动K3K3单元格右下角的填充柄，单元格右下角的填充柄，单元格右下角的填充柄，单元格右下角的填充柄，用自动填充功能计算出所有员工的应发工资。用自动填充功能计算出所有员工的应发工资。用自动填充功能计算出所有员工的应发工资。用自动填充功能计算出所有员工的应发工资。步骤步骤步骤步骤2 2 计算计算计算计算“

37、所得税所得税所得税所得税”。在。在。在。在“应发工资应发工资应发工资应发工资”一列中一列中一列中一列中L3L3单元单元单元单元格中输入公式：格中输入公式：格中输入公式：格中输入公式：=IF(K32000,0,IF(K3-=IF(K32000,0,IF(K3-2000)500,(K3-2000)*0.05,IF(K3-2000)2000,(K3-2000)500,(K3-2000)*0.05,IF(K3-2000)2000,(K3-2000)*0.1-25,IF(K3-2000)5000,(K3-2000)*0.15-2000)*0.1-25,IF(K3-2000)5000,(K3-2000)*

38、0.15-125,(K3-2000)*0.2-375)125,(K3-2000)*0.2-375)，用填充柄填充，用填充柄填充，用填充柄填充，用填充柄填充“应扣所得应扣所得应扣所得应扣所得税税税税”列其余数据。列其余数据。列其余数据。列其余数据。步骤步骤步骤步骤3 3 计算实发工资。在单元格计算实发工资。在单元格计算实发工资。在单元格计算实发工资。在单元格L3L3中输入公式：中输入公式：中输入公式：中输入公式：=K3-L3=K3-L3。按回车键确认公式输入，然后利用。按回车键确认公式输入，然后利用。按回车键确认公式输入，然后利用。按回车键确认公式输入，然后利用自动句柄填充功能填充数据。自动句柄

39、填充功能填充数据。自动句柄填充功能填充数据。自动句柄填充功能填充数据。4.8 自动筛选和排序功能4.8.1应用自动筛选应用自动筛选步骤步骤步骤步骤1 1 选择命令。单击工作表中任意含有数据的单元选择命令。单击工作表中任意含有数据的单元选择命令。单击工作表中任意含有数据的单元选择命令。单击工作表中任意含有数据的单元格，单击菜单栏格，单击菜单栏格，单击菜单栏格，单击菜单栏“数据数据数据数据”|“”|“筛选筛选筛选筛选”|“”|“自动筛选自动筛选自动筛选自动筛选”命令，字命令，字命令，字命令，字段行的所有单元格右侧均会出现一个下拉按钮，如图段行的所有单元格右侧均会出现一个下拉按钮，如图段行的所有单元

40、格右侧均会出现一个下拉按钮，如图段行的所有单元格右侧均会出现一个下拉按钮，如图所示。所示。所示。所示。步骤步骤步骤步骤2 2 筛选数据。单击筛选数据。单击“部门部门”右侧的下拉按钮，右侧的下拉按钮，从展开的下拉列表中单击从展开的下拉列表中单击“财务处财务处”选项，单击选项，单击“确确定定”按钮，将筛选出满足条件的记录，如图所示。按钮，将筛选出满足条件的记录，如图所示。步骤步骤步骤步骤3 3 取消筛选。若要取消自动筛选功能，只需取消筛选。若要取消自动筛选功能，只需再选择再选择“数据数据”|“”|“筛选筛选”|“”|“自动筛选自动筛选”命令，取消勾选命令，取消勾选“自动筛选自动筛选”复选框即可。复

41、选框即可。小贴士：小贴士：若要筛选出基本工资大于若要筛选出基本工资大于30003000元，小于元，小于40004000元的元的记录，就要使用记录，就要使用“自定义自定义”选项了。具体操作是：单击选项了。具体操作是：单击“基本工资基本工资”右侧的下拉按钮，在出现的下拉列表中选择右侧的下拉按钮，在出现的下拉列表中选择“自定义自定义”选项，在打开的选项，在打开的“自定义自动筛选方式自定义自动筛选方式”对话框对话框中设置筛选方式，如图所示。并单击中设置筛选方式，如图所示。并单击“确定确定”按钮，即按钮，即可显示筛选出的记录。可显示筛选出的记录。4.8.2应用排序功能应用排序功能步骤步骤步骤步骤1 1

42、选择命令。选定工作表中含有数据的任意单元选择命令。选定工作表中含有数据的任意单元格，单击菜单栏中格，单击菜单栏中“数据数据”|“”|“排序排序”命令，在出现的命令，在出现的“排序排序”对话框中的对话框中的“主要关键字主要关键字”下拉列表框中选择下拉列表框中选择“实发工资实发工资”项，再单击其右侧项，再单击其右侧“降序降序”单选按钮。在单选按钮。在“次要关键字次要关键字”中中选择选择“员工编号员工编号”项，再单击右侧项，再单击右侧“升序升序”。如图所示。如图所示。步骤步骤步骤步骤2 2 设置对话框。单击设置对话框。单击“确定确定”按钮，此时系统弹出按钮，此时系统弹出如图左所示的对话框，提示用户将

43、按照文本或数字进如图左所示的对话框，提示用户将按照文本或数字进行排序，保持默认设置行排序，保持默认设置步骤步骤步骤步骤3 3 完成排序设置。再单击完成排序设置。再单击“确定确定”按钮返回工作表按钮返回工作表中，可看到排序后的效果，如图右所示。中，可看到排序后的效果，如图右所示。4.9 数据透视表的应用数据透视表的应用 数据透视表是一种对大量数据快速汇总和建立交叉列表的交互式表格，可以快速合并和比较大量数据，具有透视和筛选功能和极强的数据分析功能，它能转换行或列以查看数据的不同汇总结果，也可以显示不同页面以筛选数据，还可以根据需要显示区域中的明细数据。步骤步骤步骤步骤1 1选定单元格。单击选定单

44、元格。单击“员工工资管理表员工工资管理表”中任中任意含有数据单元格。意含有数据单元格。步骤步骤步骤步骤2 2选择命令。单击菜单栏上选择命令。单击菜单栏上“数据数据”|“”|“数据透数据透视表和数据透视图视表和数据透视图”命令，打开命令，打开“数据透视表和数据数据透视表和数据透视图向导透视图向导”对话框，如图所示。对话框，如图所示。步骤步骤步骤步骤3 3选定区域。在选定区域。在“请指定待分析数据的数据源类请指定待分析数据的数据源类型型”栏中选择栏中选择“Microsoft Excel“Microsoft Excel数据列表或数据库数据列表或数据库”选项，选项，然后在然后在“所需创建的报表类型所需

45、创建的报表类型”栏中选择栏中选择“数据透视表数据透视表”，单击下一步按钮，打开如图左所示的对话框。单击下一步按钮，打开如图左所示的对话框。步骤步骤步骤步骤4 4选择透视表显示位置。单击选择透视表显示位置。单击“下一步下一步”按钮，打按钮，打开如图右所示的对话框，选择开如图右所示的对话框，选择“新建工作表新建工作表”选项。选项。步骤步骤步骤步骤5 5透视表布局。单击透视表布局。单击“布局布局”选项，打开如图左所选项，打开如图左所示的对话框。在示的对话框。在“数据透视表布局数据透视表布局”对话框中，可以设对话框中，可以设计数据透视表的布局。将数据清单中的字段名拖至页、计数据透视表的布局。将数据清单

46、中的字段名拖至页、行、列或数据区域。例如要将行、列或数据区域。例如要将“住房补贴住房补贴”、“交通补贴交通补贴”、“基本工资基本工资”、“应发工资应发工资”和和“实发工资实发工资”按部门汇总的操按部门汇总的操作是：把作是：把“月份月份”拖到拖到“页页”，把，把“部门部门”拖至拖至“行行”，把，把“住房住房补贴补贴”、“交通补贴交通补贴”、“基本工资基本工资”、“应发工资应发工资”和和“实发实发工资工资”等字段名拖至等字段名拖至“数据数据”区域，如图右所示。区域，如图右所示。步骤步骤步骤步骤6 6完成透视表设置。单击完成透视表设置。单击“确定确定”按钮，生成的数按钮，生成的数据透视表如图所示。据

47、透视表如图所示。当员工基本工资表的数据发生变化时，生成的透当员工基本工资表的数据发生变化时，生成的透视表中的数据也随之变化。在数据透视表中只能对数视表中的数据也随之变化。在数据透视表中只能对数据进行查看而不能进行修改。据进行查看而不能进行修改。4.10 保护工资表保护工资表步骤步骤步骤步骤1 1 选择命令并设置密码。单击菜单栏上选择命令并设置密码。单击菜单栏上“工具工具”|“”|“保护保护”|“”|“保护工作表保护工作表”命令，打开命令，打开“保护工作表保护工作表”对话框。对话框。在在“取消工作表保护时使用的密码取消工作表保护时使用的密码”文本框中输入要设文本框中输入要设置的密码；在置的密码；

48、在“允许此工作表的所有用户进行允许此工作表的所有用户进行”列表框列表框中设置允许用户进行的操作，如勾选中设置允许用户进行的操作，如勾选“排序排序”、“使用自使用自动筛选动筛选”复选框等。如图所示。复选框等。如图所示。步骤步骤步骤步骤2 2 确认密码。单击确认密码。单击“确定确定”按钮，系统将弹出如图按钮，系统将弹出如图左所示的左所示的“确认密码确认密码”对话框，再次输入密码，单击对话框，再次输入密码，单击“确确定定”按钮。按钮。步骤步骤步骤步骤3 3 完成工作薄保护。当试图清除工资表中某个员完成工作薄保护。当试图清除工资表中某个员工的记录时，系统将弹出如图右所示的提示信息。提工的记录时，系统将

49、弹出如图右所示的提示信息。提示如果要清除该内容，必须要选取对工作表的保护。示如果要清除该内容，必须要选取对工作表的保护。步骤步骤步骤步骤4 4 取消工作薄保护。如果要撤消对工作表的保护，取消工作薄保护。如果要撤消对工作表的保护，可单击菜单栏上可单击菜单栏上“工具工具”|“”|“保护保护”|“”|“撤消工作表保护撤消工作表保护”命令。命令。在出现的在出现的“撤消工作表保护撤消工作表保护”对话框中输入正确的密码，对话框中输入正确的密码，再单击再单击“确定确定”按钮即可完成撤消工作表保护功能的操作。按钮即可完成撤消工作表保护功能的操作。小贴士：小贴士：小贴士：小贴士：在如图所示的选项中，在如图所示的选项中，“允许用允许用户编辑区域户编辑区域”可以让用户编辑指可以让用户编辑指定的区域，实现方法是为不同定的区域，实现方法是为不同的区域设置不同的操作密码；的区域设置不同的操作密码；“保护工作薄保护工作薄”可以对整个工作薄可以对整个工作薄起到保护作用，还可以根据需起到保护作用，还可以根据需要选择只保护工作薄的要选择只保护工作薄的“结构结构”或或者者“窗口窗口”；“保护并共享工作薄保护并共享工作薄”可以共享工作薄，提供以追踪可以共享工作薄，提供以追踪修订方式共享，从而避免丢失修订方式共享，从而避免丢失修订信息。修订信息。