Office-2010公司管理项目化教程(第三章)

1、Office 2010企业管理项目化教程公司员工工资管理Word小张是某汽车股份有限公司的会计，负责管理公司员工的信息和工资统计，由于公司工资制度改革，需要小张把原来的员工工资系统进行更新，从而满足新的工资制度。小张把该项目分解成三个任务来完成，第一个任务是更新公司“员工基本信息表”、“员工出勤表”、“员工业绩表”、“基本工资及社会保险表”四张数据表；第二个任务是通过Microsoft Query组件从多数据表中完成“工资总表”；第三个任务是通过多种方法完成工资条的制作。项目描述学习要点理解并掌握工作表中的各种操作（添加或删除字段，移动、复制和格式化工作表等）。掌握根据实际要求编写公式和嵌套函

2、数的方法。能利用Microsoft Query组件获取数据。掌握数据排序的灵活应用。项目3Contents Page目录页 任务3：批量制作工资条 任务1：更新公司员工数据表 任务2：制作工资总表任务1更新公司员工数据表 情景描述情景描述某公司采用Excel 2010来管理统计员工信息。 由于公司开设了三个分店，必须进行员工的重新分配，即增加“地区”字段； 为了与员工共享信息方便，要增加“电子邮箱”字段； “工龄”字段为了保证计算更加准确，也进行了公式的更新。 会计小张借助原来的“员工基本信息表”进行数据的更新。 由于公司考勤制度改革加入了迟到制度，取消病假制度，会计小张借助原来的“员工出勤表

3、”进行更新数据。 由于公司业绩考核改革，每月根据考核成绩给出考核等级，从而决定业绩奖金。会计小张借助原来的“员工业绩表”进行数据的更新，再通过更新的相关数据表来满足公司的改革要求。作品展示员工基本信息表效果图：作品展示员工出勤表效果图：作品展示员工考核表效果图：作品展示基本工资及社会保险效果图：任务要点3.掌握根据实际情况输入公式的方法。4.日期函数DATEDIF的使用方法。5. VLOOPUP函数的使用及嵌套方法。6. IF函数的使用及嵌套方法。2.通过移动和复制工作表获取外部数据。任务实施步骤1启动Excel 2010，将新建的工作簿保存为“公司员工工资管理”。更新员工基本信息表01任务实

4、施步骤2 打开本书配套素材“素材与实例”“项目3”“公司员工信息管理”原系统。更新员工基本信息表01任务实施步骤3将“公司员工信息管理”原系统中的“员工基本信息表”复制到“公司员工工资管理”工作簿中，如图所示。更新员工基本信息表01 移动和复制工作表时，原工作簿和目标工作簿都要处于打开状态。任务实施步骤4同理，将“公司员工信息管理”原系统中的“资料表”复制到“公司员工工资管理”工作簿中，并移至“员工基本信息表”之前。更新员工基本信息表01任务实施步骤5 将“公司员工工资管理”工作簿中的“Sheet1”、“Sheet2”、“Sheet3”三个工作表删除。步骤6将“公司员工工资管理”工作簿中“员工

5、基本信息表”的第一行标题行删除。更新员工基本信息表01任务实施步骤7选中“公司员工工资管理”工作簿中的“员工基本信息表”，在“工作日期”列前插入“电子邮箱”列，由于会沿用前一列数据格式，从而默认是数据有效性的格式，为此，需清除数据的有效性，操作步骤如下：更新员工基本信息表01（1）选中要清除格式的数据范围I2:I21单元格区域，如右图所示。任务实施更新员工基本信息表01（2）单击“数据”选项卡“数据工具”组中“数据有效性”按钮，打开“数据有效性”对话框，单击“全部清除”按钮，如右图所示，单击“确定”完成操作。任务实施步骤8按效果图右图所示输入内容。将“电子邮箱”列调整到最合适的列宽，并用“格式

6、刷”刷新格式。更新员工基本信息表01任务实施小技巧按列名选中整列，在右键菜单中选择“插入”项，可在此列前快速插入一列。 要快速调整最合适列宽和行高，可将鼠标指针移到两列名或行名中间的分割线上，当鼠标指针变成双向箭头形状时，双击鼠标左键即可。 利用“格式刷”可以复制格式：先选中原格式区域，再单击“格式刷”按钮，此时鼠标变成“刷子”形状，再刷目标区域即可。任务实施步骤9选中“公司员工工资管理”中的“员工基本信息表”，在“工龄（年）”列后插入“地区”列，并设置数据有效性，参数及效果如右图所示。将“地区”列调整到最合适的列宽，并用“格式刷”刷新格式。更新员工基本信息表01任务实施步骤10选中“公司员工

7、工资管理”工作簿中的“员工基本信息表”，删除“工龄（年）”列数据。若当前日期本人已经过生日才算一年，否则不能算，此时可采用DATEDIF函数来实现。选中K2单元格，输入如右图所示的公式，再通过复制公式更新其余数据。更新员工基本信息表01任务实施小技巧DATEDIF函数的功能：返回两个日期参数的差值。 语法： DATEDIF(date1,date2,y)； DATEDIF(date1,date2,m)； DATEDIF(date1,date2, d)其中：date1表示前面日期，date2表示后面一个日期，y(m、d)要求返回两个日期相差的年（月、天）数。注意：这是Excel中的一个隐藏函数，在

8、函数向导中找不到，可以直接输入使用，对于计算年龄、工龄等非常有效。任务实施步骤1 将“公司员工信息管理”原系统中的“员工出勤表”复制到“公司员工工资管理”工作簿中。步骤2 选中“公司员工工资管理”中的“员工出勤表”，将第一行的标题行删除。更新员工出勤表02任务实施步骤3 选中“公司员工工资管理”中的“员工出勤表”，在“事假”前插入两列，分别为“本月累计迟到（分钟）”、“本月累计事假（天）”，并调整合适的列宽。删除“病假”、“事假”两列。更新员工出勤表02任务实施步骤4 选中“公司员工工资管理”中的“员工出勤表”，选中A2:G21单元格区域，删除其内容保留格式，如图所示。更新员工出勤表02任务实

9、施步骤5 选中“公司员工工资管理”中的“员工出勤表”，其中“员工编号”列数据直接引用“员工基本信息表”工作表中的“员工编号”列数据，如下图所示。更新员工出勤表02任务实施步骤6 选中“公司员工工资管理”中的“员工出勤表”，通过“员工编号”列引用“员工基本信息表”中的“姓名”、“部门”列数据，完成该表中两列数据的填充。“姓名”、“部门”列公式如下图所示。更新员工出勤表02任务实施小技巧VLOOKUP函数的功能：用于在表格或数值数组的首列查找指定的数值，并由此返回表格或数值当前行中指定列处的数值。语法： VLOOKUP(Lookup_valueTable_arrayCol_index _numRa

10、nge_lookup)Lookup_value：表示要查找的值，它可以为数值、引用或文字串。 Table_array：用于指示要查找的区域，查找值必须位于这个区域的最左列。此区域必须是绝对引用。Col_index_num：为相对列号。查找区域的最左列为1，其右边一列为2，依次类推。Range_lookup：唯一逻辑值，指明函数VLOOKUP查找时是精确查找（FALSE），还是近似匹配（TRUE）。任务实施步骤7 将“公司员工信息管理”原系统中的“资料”表复制到“公司员工工资管理”工作簿中。更新员工出勤表02任务实施步骤8选中“公司员工工资管理”中的“员工出勤表”，通过“资料”表中的“职务工资”

11、表格信息完成该表的“职务工资”列数据的填充。（1）定义区域名称。将“员工基本信息表”中A2:L21单元格区域定义为“员工基本信息”，将“资料”中A3:B12单元格区域定义为“职务工资”。更新员工出勤表02任务实施（2）“员工出勤表”中的“职务工资”列引用“员工基本信息表”中的“职务”列数据，如下图所示。更新员工出勤表02任务实施（3）以“员工出勤表”中的“职务工资”列查找“资料”中的“职务工资”，如下图所示。更新员工出勤表02“职务工资”列计算采用VLOOKUP函数的嵌套。小提示任务实施步骤8选中“公司员工工资管理”中的“员工出勤表”，按效果图输入“本月累计迟到（分钟）”、“本月累计事假（天）

12、”两列数据，并计算“应扣额”，如图所示。公司的考核制度如下（1）一个月中累计迟到不超过10分钟，不扣款；累计迟到不超过30分钟，扣款20元；累计迟到不超过60分钟，扣款30元；累计迟到超过60分钟，按职务工资的3%进行扣款。（2）病假、婚假不扣款。（3）事假一天扣100元。更新员工出勤表02任务实施小技巧多层条件的IF函数构成：IF(条件1，符合条件1的结果，IF(条件2，符合条件2的结果，IF(条件3，符合条件3的结果，IF(条件4)。其中条件1条件2条件3=15,1000,if(VLOOKUP(A2,员工基本信息,11,false)=10,800,if(VLOOKUP(A2,员工基本信息,

13、11,false)=5,500,200)单个函数可以用函数向导来完成，但多个函数嵌套使用必须学会手动输入公式。小技巧任务实施步骤9 选中“公司员工工资管理”中的“基本工资及社会保险”工作表，通过“资料”表中的“学历工资”表格信息完成该表的“学历工资”。其中：更新基本工资及社会保险04（1） “基本工资及社会保险”中的“学历工资”列，通过“员工编号”列引用“员工基本信息表”中的“学历”列数据，“员工基本信息表”数据区定义名称为“员工基本信息”。任务实施更新基本工资及社会保险04（2） 通过“基本工资及社会保险”中的“学历工资”列的结果引用“资料”中的“学历工资”数据（“资料”表中“学历工资”数据

14、区定义名称为“学历”），完成“学历工资”列数据。公式如下。=VLOOKUP(VLOOKUP(A2,员工基本信息,8,FALSE),学历,2,FALSE)“学历工资”列计算采用VLOOKUP函数的嵌套。小技巧任务实施步骤10 计算“基本工资及社会保险”工作表中的“基本工资”和“社会保险”。更新基本工资及社会保险04（1）计算出员工的“基本工资”计算方法为：基本工资=工龄工资+职务工资+学历工资任务实施更新基本工资及社会保险04（2） 计算出员工需缴纳的“社会保险”，社会保险由养老保险、医疗保险、失业保险、住房公积金四部分构成。这里是个人缴纳部分，计算方法如下：养老保险=基本工资8%医疗保险=基本

15、工资2%失业保险=基本工资1%住房公积金=基本工资7%社会保险=养老保险+医疗保险+失业保险+住房公积金任务2制作工资总表 情景描述情景描述某公司工资总表要能够同时满足公司财务处理的需求和员工的需求。财务处理侧重于工资的归属，员工关注的是自身的工资组成情况。 在本任务中，每个员工的工资包括员工的基本信息、基本工资和业绩奖金、社会保险、考核扣款等，不过这些工资项目暂时不在同一个工作表中体现，需要借助任务1中的四个表合并成为一张总的工资表。上述计算的结果属于员工的应发工资，还需要根据我国规定的个人所得税计算方法，通过公式最终计算出实发工资，最终完成工资总表。作品展示2.熟悉并使用Microsoft

16、 Query组件，能够通过设置条件来控制数据的输出显示。任务要点3.掌握根据实际要求灵活输入公式的方法。1. 掌握利用多表生成 工资总表相关数据的方法。任务实施步骤1 在“公司员工工资管理”工作簿的最后新建“工资总表”。步骤2 打开“工资总表”工作表，选中A1单元格，在“数据”选项卡的“获取外部数据”组中执行“自其他来源”中的“来自Microsoft Query”命令，打开如右图所示的“选择数据源”对话框。多表生成工资总表01任务实施步骤3 在“选择数据源”对话框选择数据库类型为“Excel Files*”，单击“确定”按钮，打开“选择工作簿”对话框，选择“公司员工工资管理”工作簿，如右图所示

17、。多表生成工资总表01任务实施步骤4 选中“公司员工工资管理”后单击“确定”按钮，打开“查询向导-选择列”对话框，单击“可用的表和列”中的“员工基本信息表”前的“+”号，展开该表所含的列，分别选中要显示的列“员工编号”“姓名”“部门”“地区”到查询结果列中，如右图所示。多表生成工资总表01任务实施步骤5 单击“下一步”按钮，完成要显示的列的选择，进入“查询向导-筛选数据”对话框， 单击“下一步”按钮，跳过筛选过程，打开“查询向导-排序顺序”对话框，在该步骤中不需要设置任何内容。 单击“下一步”按钮，打开“查询向导-完成”对话框，在“请确定下一步的动作”设置区选中“在Microsoft Quer

18、y中查看数据或编辑查询”， 单击“完成”按钮，完成查询向导的操作，进入Microsoft Query界面，如右图所示。多表生成工资总表01任务实施步骤6执行Microsoft Query窗口中“表”菜单下的“添加表”命名，显示“添加表”对话框，如右图所示，分别双击列表框中的“员工出勤表”“员工业绩表”“基本工资及社会保险”，单击“关闭”按钮关闭“添加表”对话框。多表生成工资总表01任务实施步骤7 执行Microsoft Query窗口中“表”菜单下的“连接”命令，显示“连接”对话框，如右图所示，在左侧选择“员工基本信息$.员工编号”，“运算符”为=（等于），在右侧表选择“员工出勤$.员工编号”

19、，“连接内容”选择1，单击“添加”按钮，添加一个连接。多表生成工资总表01任务实施 同理，通过“员工编号”字段建立“员工出勤表”和“员工业绩表”之间的连接，再通过“员工编号”字段建立“员工业绩表”和“基本工资及社会保险”之间的连接。从而四个表通过“员工编号”字段连接在一起，如下图。多表生成工资总表01多表建立连接，也可以在添加所需表之后，通过各表间用鼠标拖拽“员工编号”字段建立连接。小技巧任务实施步骤8在Microsoft Query窗口从添加的表中双击“工资总表”所需要的字段，将其添加到表中，效果如下图所示。多表生成工资总表01任务实施步骤9执行Microsoft Query窗口中“文件”菜

20、单下的“将数据返回Microsft Excel”命令，打开如右图所示的“导入数据”对话框，按图中所示选择，切换到Excel状态。多表生成工资总表01任务实施小技巧用Microsoft Query组件通过多表生成一个表，一定要注意多表之间必须有唯一字段建立多表之间的连接，这样生成的表数据才能关联在一起。如果关联表的数据进行了更改，则在生成表只需在数据区右击，在右键菜单中选择“刷新”项，即可一起更新数据。任务实施步骤1 在“工资总表”工作表中，导入数据时自动进入筛选状态，取消其自动筛选状态，并添加“应发工资”、“应纳税工资额”、“个人所得税”、“实发工资”四个字段。通过公式和函数计算完成工资总表0

21、2任务实施步骤2 按照“公司员工信息管理”原系统中的“工资总表”格式，利用“格式刷”刷“公司员工工资管理”中的“工资总表”。先清除“工资总表”列格式，如右图所示，再刷新格式。通过公式和函数计算完成工资总表02任务实施通过公式和函数计算完成工资总表02步骤3在用公式计算“工资总表”工作表中的“应发工资”。计算方法：应发工资=基本工资+业绩奖金-社会保险-应扣额步骤4 用IF函数计算“工资总表”中的“应纳税工资额”。计算方法：如果应发工资大于3500元需纳税，纳税金额为“应发工资3500”，否则为0。任务实施通过公式和函数计算完成工资总表02步骤5计算个人所得税在K3单元格输入如图3-27示的公式

22、，因为对应的值是一个范围，所以采用VLOOKUP函数的模糊查询。多表如果引用Microsoft Query组件生成的数据，对应单元格名称变为列名。小技巧从2011年9月1日起，个人所得税是月收入超过3500起征。个人所得税=应纳税工资额税率-速算扣除数，其中税率如“资料”工作表中的介绍，如下图所示。 函数VLOOKUP中range_lookup（第四个参数）为TRUE（或忽略）时，表示“模糊查找”（近似匹配值），函数VLOOKUP在区域table_array（第二个参数）的第一列中找不到对应的具体指looup_value（第一个参数），则返回小于或等于lookup_value的最大值。 小技巧

23、通过公式和函数计算完成工资总表02任务实施步骤6 计算实发工资： 实发工资=应发工资-个人所得税。通过公式和函数计算完成工资总表02任务3批量制作工资条 情景描述情景描述 为了使员工了解到自己的工资信息，核实工资数据，通常要将员工的工资信息反馈给员工个人。因此，通常情况下都会为员工打印工资条。工资条的生成方式可分为如下三种：第一种是使用Word的邮件合并生成主文档工资条模板和Excel提供工资总表数据源联合应用生成工资条，这种方式的劣势是过程过于复杂；第二种就是直接在Excel中使用公式来生成工资条，通过找到标题行，内容行之间的行号关系就可以通过复制公式的方法生成工资条，这种方式的劣势是公式过

24、于复杂；第三种就是利用排序功能，自动生成工资条，这种方法简单直观。本任务采用第三种方法制作工资条。作品展示本任务批量制作的工资条效果如右图所示。任务要点3.掌握准确定位单元格区域的方法。1.灵活掌握排序的使用方法。2.熟悉并熟练掌握“格式刷”的使用。任务实施步骤1 新建“工资条”工作表，复制“工资总表”中的数据到“工资条”工作表中，以A2单元格为开始单元格。制作数据表01因为工资条采用排序法生成，所以在粘贴数据时只要求粘贴数值。小技巧任务实施步骤2 打开“工资总表”工作表，将“员工编号”设置前置零效果。选中A列，插入两个新列。步骤3 B列生成新的“序号”字段，在B3单元格输入1，依次填充至B2

25、2单元格。制作数据表01任务实施步骤4 A列生成一个“辅助”字段，在A3单元格输入1，依次填充至A22单元格；在A23单元格输入1.2，依次填充至A41单元格，如图所示。制作数据表01 按【Ctrl】向下填充数据时，相当于步长值设置为1。任务实施步骤1 选中A2:N41单元格区域，按“辅助”列执行“升序”数据排序。步骤2 快速选择定位区域。选中A2:N41单元格区域，执行“开始”选项卡“编辑”组中“查找和选择”列表中的“定位条件”命令，如右图所示。快速生成小标题02任务实施步骤3 在打开的“定位条件”对话框选中“空值”项，如右图所示，单击“确定”按钮，选中区域中所有空行。快速生成小标题02任务实施步骤4 保持选中空行的状态下，输入“=”号，然后选中B2单元格。同时按两次【F4】键，最后按【Ctrl+Enter】组合键，如右图所示。快速生成小标题02任务实施小技巧【F4】键可以重复上一次用户进行的操作 比如插入一列，按【F4】键，可以重复用户的插入列动作。 在编辑公式时，引用其他单元格