项目4---员工工资查询系统.pptx

,#,目标导航,掌握,EXCEL,中基本表格的制作，学会使用各种公式和函数，完成查询、现实等功能，掌握,EXCEL 2010,中对数据的高级应用。,教学重点与难点,1,、公式和函数的应用,2,、数据的高级应用,任务一 完成,员工信息表制作,创建工作薄员工信息查询系统。,选定工作簿中的某个工作表，先录入表格行、列标题文字，在员工号、姓名、身份证号、联系地址、邮政编码列中分别录入各员工的相关信息。,使用,MOD,、,MID,、,IF,、,DATE,、,DATEIF,函数，从身份证号中截取出生年月、性别、年龄填充到相关区域。,通过设置工作表边框、背景、行高、列宽以及字体和对其方式实现工作表格式设置，最后进行页面设置（纸张选择、页眉页脚、顶端标题行、页边距、打印预览）,任务一 完成,员工信息表制作,解决方案：,1.1.1,、创建工作薄员工信息查询系统，具体如图所示,任务一 完成,员工信息表制作,实现步骤,2.1,、选定工作簿中的某个工作表，先录入表格行、列标题文字，在员工号、姓名、身份证号、联系地址、邮政编码列中分别录入各员工的相关信息，如下图所示。,任务一 完成,员工信息表制作,在录入员工号 时，可以采用连续的录入形式进行填充。先输入第一个员工的员工号，拖动填充手柄，完成其他员工号的填充，注意：该员工号必须是连续，有规律的。如下图所示,录入员工号,任务一 完成,员工信息表制作,录入性别，性别”列的数据只有“男”和“女”两种。录入性别的方法是，先用数据填充的方法将所有学生,的,性别,填充为“男”或者“女”,，,然后,用,ctrl+enter,键成批修改,录,错,信息。,如图,所示,录入性别,任务一 完成,员工信息表制作,录入出生日期，身份证号中包含了员工的出生日期信息，从第,7,位开始的,4,位数字为出生年号，从第,11,位开始的,2,位数字为出生号，从第,13,位开始的,2,位数字为出生日号。利用,Excel,提供的,MID,函数可以从身份中截取这些信息，再用,DATE,函数将所截取的年、月、日数据拼装成完整的出生日期，然后填充至对应的单元格中,。,具,体操作如下：,录入出生日期,任务一 完成,员工信息表制作,（,1,）,单击,D3,单元格，在,D3,单元格中先输入等号“,=,”,表示后面输入的是公式，再输入“,DATE,（,MID(E3,，,7,，,4),，,MID(E3,，,11,，,2),，,MID,（,E3,，,13,，,2,）,”,按回车键。也就是输入在第,1,到第,4,步编辑栏中所显示的公式。其中等号“,=,”是公式中不可缺少的一个运算符，如果忘了输入“,=,”，则,Excel,会将所输入的内容理解成一个字符串。,2,）,DATE,函数是常用的日期函数，其功能是将年、月、日数据拼装成一个日期型数据。函数的格式是,DATE,（,Year,，,Month,，,Day,），其中,Year,为年号，,Month,为月号，,Day,为日号,，如图所示,。,设置员工信息的格式，通过调整行高和列宽，以及边框和底纹来设置表格格式。,选择行。单击鼠标右键，系统会弹出快捷菜单，在快捷菜单单击,【,行高,】,命令，系统会出现“行高”对话框,在“行高”对话框中输入数字单击,【,确定,】,就调整好行号了。,使用相同的方法设置列,宽，如图所示,任务一 完成,员工信息表制作,设置边框。选择单元格，打开“单元格格式“对话框。单击鼠标右键，系统会弹出一个快捷菜单，在快捷菜单中选择,【,设置单元格格式,】,命令，在弹出的”单元格格式“对话框中，单击”边框“选项卡，设置边框。,4,、设置底纹,。,选择,单元格，打开“单元格格式“对话框。单击鼠标右键，系统会弹出一个快捷菜单，在快捷菜单中选择,【,设置单元格格式,】,命令，在弹出的”单元格格式“对话框中，单击”图案“选项卡，设置底纹,。如图所示,任务一 完成,员工信息表制作,根据,项目要求，结合给项目特征，可将给项目分解成下述三个任务步骤实：,（,1,）创建,1,个工作表，工作标签分别命名为“,1,月”，“,2,月”，“,3,月”，“员工工资查询系统”，其中前,3,个工作表分别是各月工资情况，每个月人数一致，且根据项目要求，这些工作表的结构完全一致，如何利用这一特征，同时对,3,个工作表进行相同的操作设置，一次完成，避免重复操作呢？在,Excel,中，系统可以同时选定多个工作表，当在其中某一表中同时进行某些操作时，这些操作可传递到组中其他表，即可实现一次完成表结构和相同的输入，不必重复多次进行。,（,2,）数据填充。利用利用,Excel,提供的求和，求最大值,max(),最小值,MINN,和平均值函数,AVERAGE,来统计工资表中的最高，最低和平均。,任务二 完成,员工工资查询,制作,解决方案,（,1,）完成员工工资表的制作,任务二 完成,员工工资查询,制作,实施步骤：,（,2,）,.,计算“实发工资”,根据,岗位工资中的实发工资计算公式“实发工资,=,应发工资,80%,，可以自定义公式来计算。计算“岗位工资”的实发工资的操作步骤如下：,单击,单元格,E6,，输入公式“,=+D4*0.8”,（公式中的*号表示数学中的乘法运算），或单击单元格,E6,后，再单击编辑栏，在编辑栏中输入相同内容，公式输入完成后，按回车。,（,3,）计算扣款项“小计”,任务二 完成,员工工资查询,制作,实施步骤：,(4),计算“税款”,在,Excel,中，系统提供了,if,函数实现税款的计算，他的语法格式为：,IF,（,logical_test,value_if_true,value_if_false,）,logical_test:,任何一个可以判断为,TURE,或,FALSE,的数值或表达式。,value_if_true:,当,logical_test,为,TRUE,的返回值,value_if_false,：当,logical_test,为,FALSE,的返回,值,任务二 完成,员工工资查询,制作,实施步骤：,公式的创建,公式,是利用单元格的引用地址对存放在其中的数值进行计算的等式。一般由三部分组成：等号、运算数、运算符。运算符可以是数值常量，也可以是单元格或单元格区域，甚至是,Excel,提供的函数。用户可以通过以下步骤创建公式：,选中输入公式的单元格。,输入“等号”。,在单元格或者编辑栏中输入公式具体内容。,按回车键或输入按，完成公式的,输入,任务二 完成,员工工资查询,制作,注意：,Excel,中单元格地址的引用,Excel,中单元格的地址有,3,种引用方式：相对引用，有时，用户在复制或自动填充公式时，期望单元格地址不随便复制或填充的目的单元格的变化面变化，这种方式称为“单元格的绝对引用”。,如果单元格引用地址的一部分为绝对引用地址，另一部分为相对引用地址，例如,D6,或,D,6,称为“单元格的混合引用”。如果“”符号在行号前，表示该行位置是“绝对不变”的，处于锁定状态，而列位置会随目的单元格的位置变化而变化。同理，如果“”符号在列号前，表示该列位置是“绝对不变”的，处于锁定状态，而行位置会随目的单元格位置的变化而变化,任务二 完成,员工工资查询,制作,注意：,(5,),完成工资查询界面功能,任务二 完成,员工工资查询,制作,实施步骤：,函数,：,VLOOKUP(LOOKUP_BALUE,TABLE_ARRAY,COL_INDEX_NUM,RAN GE-LOOKUP),LOOKUP_BALU,：表示指定的数值；,TABLE_ARRAY,：表格或数据清单；,COL_INDEX_NUM,：返回值在表中的列数值,首列为,1,；,RANGE-LOOKUP,：,0,表示模糊查询，非,0,，表示精确查询,任务二 完成,员工工资查询,制作,实施步骤：,IF(logical_test,value_if_true,value_id_false),Logical_test:,判断任何一个为,TURE,或,FLASE,的数值或表达式,value_if_true,：当表达式为,TURE,输出的值,value_id_false,：当表达式为,FALSE,输出的值,任务二 完成,员工工资查询,制作,实施步骤：,（,1,）我们在,E8,单元格中输入公式：,=IF($E$5=”,”,VLOOKUP($E$5,员工基本信息表,!$A$2:$I$179,1,0),，首先判断,E5,是否为空，也就是关键字是否为空，如果不为空怎到对应的学生基本信息表中查询出学生的学号。,（,2,）然后在,H8,中输入公式：,=IF($E$5=”,”,VLOOKUP($E$5,员工基本信息表,!$A$2:$I$179,2,0),。,（,3,）按照如上的方法完成部门、家庭住址、联系电话等数据的查询，最后结果如图所示。,任务二 完成,员工工资查询,制作,实施步骤：,1,、创建名为“工资管理分析”的工作表的操作步骤,2,、设置数据清单排序,3,、设置数据筛选,4,、设置数据汇总,5,、创建数据图表,6,、置数据透视表,任务,三,员工,工资管理分析表,实施步骤：,（,1,）双击员工工资查询系统,.XLS,。,（,2,）右键单击工作表名称，在弹出的快捷菜单中，执行,【,插入,】,命令，插入一张新的工作表。,（,5,）重复操作步骤（,3,）、（,4,），工作表“,Sheet1”,前插入了,2,个工作表“,Sheet4”,、“,Sheet5“,。,（,6,）选中工作表“,Sheet5“,标签右击，在弹出的快捷菜单中，执行,【,重命名,】,命令，或双击该标签，使标签名处于编辑状态，将标签名重命名为“员工工资管理分析”。,（,7,）参照操作步骤（,6,），依次将工作表标签“,Sheet4“,、“,Sheet1“,、“,Sheet2“,、“,Sheet3“,重命名为“排序”、“筛选”、“汇总”、“图表”。,任务,三,员工,工资管理分析表,1,、创建名为“工资管理分析”的工作表的操作步骤,对工作表“员工工资分析“第一季度总额”字段设置降序排列,操作步骤如下：,（）单击工作表“排序”的标签，打开“排序”工作表。,（）选中“总分”列中的任一数据单元格，单击菜单栏上的,【,数据,】,【,排序,】,命令，打开“排序”对话框。,（）在“排序”对话框中，在“主要关键字”下拉列表框中选择“第一季度总额”，“降序”方式,。,（）单击,【,确定,】,按钮，完成“第一季度工资”的降序排列,任务,三,员工,工资管理分析表,2,、设置数据清单排序,1,、使用自动筛选，筛选出“平均工资,3000“,的数据记录,操作步骤如下：,（,1,）单击工作表中“筛选“的标签，打开”筛选“工作表。,（,2,）在“筛选“工作表中，选中数据清单中任一单元格。,（,3,）单击菜单栏中的,【,数据,】|【,筛选,】|【,自动筛选,】,命令，此时在数据清单中每个列标题右侧都会出现“自动筛选“下拉式按钮，表示进入了自动筛选状态。,（,4,）单击“平均工资“字段旁的下拉式按钮，在弹出的下拉列表框内选择”（自定义）“，将打开“自定义帅选方式”对话框中，在在”平均工资“下拉列表框中选择”大于“，在后面的下拉列表框中输入”,3000“,。,（,6,）单击,【,确定,】,按钮，完成自动筛选。,任务,三,员工,工资管理分析表,3.1,、设置数据筛选,使用,高级筛选，筛选出平均工资大于,3000,分的员工记录，原数据清单仍显示。,操作步骤如下：,（,1,）单击“筛选”工作表的空白单元格,A54,，在相应的空白单元格区域,A54:I55,内录入筛选条件，筛选结果从单元格,A58,开始显示，原数据清单仍显示。,（,2,）单击菜单栏中的,数据,筛选,高级筛选,命令，打开“高级筛选”对话框。,（,3,）在“高级筛选”对话框中，选择“将筛选结果复制到其他位置”选取。,（,4,）在“高级筛选”对话框中，单击“条件区域”和“复制到”编辑栏右侧的,拾取,按钮，分别将,A54,：,I55,和,A58,真入到编辑栏内（注意“复制到”编辑栏设置为单元格,A58,，表示筛选后的结果会显示在以单元格,A58,为左上角开始的单元格区域内）。,（,5,）单击,确定,按钮，完成筛选操作，结果显示区域的左上角单元格为,A58,，即从第,58,行第,1,列开始显示结果。,任务,三,员工,工资管理分析表,3.2,、设置数据筛选,1,、将平均工资制作柱形图，创建图表。,操作步骤如下：,（,1,）选中工作表“图表”数据清单内的“平均工资”列的任一单元格，单击工具栏中的“降序排序”按钮，此时按平均工资降序排列，前五名员工依次显示在第,2,行至第,6,行内。,（,2,）选中数据区域内任一单元格，单击,插入,|,图表,命令，打开“图表向导,-4,步骤之,1-,图表类型”对话框。单击,下一步,按钮。,（,3,）打开“图表向导,-4,步骤之,2-,图表源数据”对话框，单击“数据区域”选项卡，单击“数据区域”编辑栏右侧的“拾取”按钮，将前五名员工的姓名和第一季度工资、平均工资选取（操作方法：按住,Ctrl,键，依次选择单元格区域,B2,：,B6,和,E2:I6,。选中图表，拖动至单元格区域,A7,：,H21,内,显示。,任务,三,员工,工资管理分析表,4,、设置数据汇总,（,4,）单击“系列”选项卡，在“系列”列表框中，选择“系列,1”,，在“名称”文本框内输入“第一季度工资”，仿此操作，依次逐一更名各系列，单击,下一步,按钮。,（,5,）打开“图表向导,-4,步骤之,3-,图表选项”对话框，单击“标题”选项卡，在图表标题文本框内输入“前五名员工工资图”，“分类（,X,）轴”文本框内输入“姓名”，“数值（,Y,）轴”文本框内输入“工资”。,单击“图例”选择卡，在“位置”框架中选择“靠上”单元选钮，。其他选项卡可根据需要进行设置。单击,下一步,按钮。,（,6,）打开“图表向导,-4,步骤之,4-,图表位置”对话框，选择“作为其中的对象插入”单行钮，如果选择“作为新工作表插入”，将会插入一个新图表。单击,完成,按钮，生成图表插入到“图表”工作表中。,任务,三,员工,工资管理分析表,4,、设置数据汇总,利用“数据透视表”对“员工工资分析”按员工号统计第一季度工资和平均工资。,操作步骤：,（,1,）选中工作表“员工工资分析”的数据清单中的任一单元格。,（,2,）单击,数据,|,数据透视表和数据透视图,命令，打开“数据透视表和数据透视图向导,-3,步骤之一”对话框，默认系统选项。,（,3,）单击,下一步,按钮，打开“数据透视表和数据透视图向导,-3,步骤之,2”,对话框，此步骤用于选取创建数据透视表的数据源区域，系统自动设定当前数据清单区域，如果要重新选定数据区域，可以通过“拾取”按钮，或直接在编辑栏内输入区域，如果选定区域来自外部文件可通过单击,按钮,来完成。,任务,三,员工,工资管理分析表,6,、设置数据透视表,（,4,）单击,下一步,按钮，打开“数据透视表和数据透视图向导,-3,步骤之,3”,对话框，用于设置数据透视表的显示位置，如图,3-77,示。在此选择默认值“新建工作表”，单击,完成,按钮，此时,Excel,在当前工作表前插入了一个新工作表“,Sheetl”,，并自动打开该工作表，显示数据透视表的页面布置，页面布局界面的右侧显示“数据透视表字段列表”对话框表显示,数据项：在数据透视表中要汇总的数据。,（,5,）在“数据透视表字段列表”中拖动“部门”字段至“请将页字段拖至此处”字样的位置，或先选中“部门”字段，在,添加到,按钮右侧下拉列表框中选择“页面区域”，单击,添加到,按钮操作即可。下面选用拖动施工操作。将“姓名”字段拖至“将行字段拖至此处”字样的位置，将“平均工资”字段拖至“请将数据项拖至此处”字样的位置，得到数据透视表结果。,任务,三,员工,工资管理分析表,6,、设置数据透视表,大连理工大学出版社,地址：大连市软件园路,80,号,E-mail :dutp,URL,：,,