《信息技术基础》课件第3章 Excel数据处理与分析.pptx

1、 第3章 Excel数据处理与分析第第3章章 Excel数据处理与分析数据处理与分析目目录录员工信息表的制作工资表的计算管理与分析工资表工资图表设计3.13.23.33.43.1员工信息表效果图3.1 员工信息表的制作员工信息表的制作 3.1.1数据的编辑窗口元素名称功能全选按钮单击可以选中当前工作表中的所有单元格名称栏显示当前活动单元格的地址或名称，如图4-1中的E6就是名称栏编辑栏用来显示或编辑当前单元格中的数据或公式等行标用数字表示。行标和列标一起用来表示单元格的地址列标用字母表示拆分条分水平拆分条和垂直拆分条，拖动拆分条可以将窗口拆分成四个部分，要取消拆分，则在拆分线上双击鼠标。活动单

2、元格当前正在编辑的单元格，如上图E6为活动单元格工作表标签工作簿底端的标签，用于显示工作表的名称，在工作簿窗口中单击某个工作表标签，则该工作表成为当前工作表，可以对它进行编辑。任务实施 步骤1新建Excel工作簿，命名为员工信息表.xlsx。步骤2：在sheet1工作表中，选择单元格A1，输入标题“汇通科技有限公司员工信息表”，按Enter键。步骤3：在单元格A2中，输入“员工编号”，按向右方向键或Tab键，使B2单元格成为当前单元格，输入“姓名”。步骤4：用相同的方式依次输入其他列标题。任务实施（续）步骤5：单击单元格A3，输入“001”按回车后，鼠标指针指向A3单元格的“填充柄”（位于单元

3、格右下角的小黑块），此时鼠标指针变为黑十字，按住鼠标向下拖动填充柄，拖动至目标单元格时释放鼠标任务实施（续）步骤6：用相同的方式输入“姓名”、“职务”、“学历”列中的其他数据。步骤7：输入“性别”列，选中C3单元格，输入“男”，使用填充柄，将所有员工的性别都输入“男”，在按住【ctrl】键的同时选中性别为“女”的单元格，输入一个“女”，同时按【ctrl+enter】组合键，那么选中的所有单元格中都输入“女”。步骤8：输入工作日期，选择单元格G3，输入“2002/6/5”或“2002-6-5”，用相同的方式输入其他日期数据。3.1 员工信息表的制作员工信息表的制作3.1.2 数据的数据的修饰修饰

4、 设置数字格式 设置对齐方式 设置字体格式 设置边框 调整行高和列宽 自动套用单元格样式 设置条件格式任务实施 步骤1选中A1：G1区域。步骤2：单击“开始”选项卡的“对齐方式”组中的“合并后居中”按钮，然后单击“开始”选项卡的“样式”组中的“单元格样式”按钮，在打开的下拉列表框中选择“标题”样式。步骤3：选中A2:G2区域，单击“开始”选项卡的“字体”组中的“字体”按钮，在打开的下拉列表框中选择“楷体”选项；单击“字号”下拉列表按钮选择“10”选项，对齐方式为居中对齐，填充颜色为绿色。步骤4：选中A2：G34单元格区域，单击“开始”选项卡的“样式”组中的“套用表格”按钮，在打开的列表框中选择

5、“表样式浅色16”样式。任务实施(续)步骤5：单击“数据”选项卡的“排序和筛选”组中的“筛选”按钮，关闭单元格筛选。步骤6：单击“开始”选项卡的“对齐方式”组中的“居中”按钮，设置水平和垂直居中对齐。步骤7：选中F3：F34单元格区域。步骤8：单击“开始”选项卡“样式”组中的“条件格式”按钮，弹出“条件格式”下拉列表，选择“突出显示单元格规则”子菜单中的”文本包含”命令，弹出“文本中包含”对话框，如下图所示，选择设置为下拉列表中的自定义格式，设置字体为“红色”，填充效果为“黄色”。步骤9：选中A2：G34单元格区域，右键单击选择“设置单元格样式”，然后点击“边框”选项卡，选择左边的样式为实现，

6、预置“外边框”，然后选择样式的虚线样式，预置“内框线”，效果如下图所示。步骤10：双击工作表“sheet1”的标签，标签出现反白（黑底白字）时，输入新的工作表名称“员工信息表”。3.1.3 任务拓展：制作考勤任务拓展：制作考勤表表【拓展目的】掌握工作表的基本操作方法，掌握数据的输入格式。掌握工作表中文本的修饰。3.2工资表的计算 3.2.1任务描述本任务主要是通过Excel 自带的计算功能，计算出每个员工的相应数据如工龄、奖金、应发工资、实发工资等各项数据一月份奖金表效果图员工信息表效果图一月份应发工资效果图一月份实发工资总表效果图3.2工资表的计算3.2.2使用公式运算符使用公式函数单元格的

7、引用使用函数任务实施 步骤步骤1：用最大值函数：用最大值函数MAX，在，在“一月份奖金一月份奖金表表”中，计算公司员工所得的中，计算公司员工所得的“最高奖金额最高奖金额”选择单元格J2。单击“开始”选项卡“编辑”组中的“自动求和”按钮右侧的下三角符号，从弹出的下拉菜单中选择“最大值”命令。此时单元格中出现了求最大值函数“MAX（）”，并用鼠标在工作表选择参数范围F3:F34，单击Enter键或单击编辑栏的“输入”按钮确认。在单元格J2中显示出计算结果。步骤步骤2：用最小值函数：用最小值函数MIN、求和函数、求和函数SUM、求平均值、求平均值函数函数AVERAGE，在，在“一月份奖金表一月份奖金

8、表”中，计算出公司员中，计算出公司员工的工的“最低奖金额最低奖金额”、“总奖金额总奖金额”、“平均奖金额平均奖金额”选择单元格J3。单击“开始”选项卡“编辑”组中的“自动求和”按钮右侧的下三角符号，从弹出的下拉菜单中选择“最小值”命令，直接用鼠标在工作表中重新选择参数范围“F3:F34”，单击Enter键，在J3单元格中显示计算结果。选择单元格J4，重复步骤，注意将“最小值”命令改为“求和”，就可以计算出“总奖金额”。选择单元格J5，重复步骤，注意将“最小值”命令改为“平均值”，就可以计算出“平均奖金额”。步骤步骤3：用排序函数：用排序函数RANK，计算出公司员工，计算出公司员工所得奖金的所得

9、奖金的“名次名次”选中G3单元格。单击编辑栏左侧的“插入函数”按钮，弹出“插入函数”对话框，在“搜索函数”框中输入“rank”，单击“转到”按钮，Excel 2010自动搜索相关函数，如图3-45所示。选择“RANK”函数，单击“确定”按钮，打开“函数参数”对话框。定位到第一个参数“Number”框，选择F3单元格；定位到第二个参数”Ref”框，选择区域“F3：F34”，如图3-46所示。单击“确定”按钮，在G3单元格中，计算出第1个员工的“名次”。拖动G3单元格中的填充柄至单元格G34，计算出所有员工所获奖金的名次。步骤步骤4:使用日期函数使用日期函数TODAY、YEAR计算出每个员工的计算

10、出每个员工的“工龄工龄”计算“工龄”的公式为：工龄=当前年份-参加工作年份。其中在计算“当前年份”时，先使用TODAY函数获取系统日期，再用YEAR函数获取当年的年份，即当前年份=YEAR(TODAY()；参加工作年份=YEAR（工作日期）。切换到“员工信息”表。单击单元格H3。单击“公式”选项卡“函数库”组中的“日期和时间”按钮右侧的下三角符号，从弹出的下拉菜单中选择“TODAY”命令，此时H3单元格中显示当前系统的日期，编辑栏中显示“=TODAY()”。选中“TODAY()”，按Ctrl+X键，将选定内容剪切到剪贴板上。在“日期和时间”按钮的下拉列表中选择“YEAR”命令，打开“函数参数”

11、对话框。将插入点放置到参数处，按Ctrl+V键，将剪贴板中的内容粘贴到该处，如图3-48所示。单击“确定”按钮。将插入点放置到编辑栏中“=YEAR(TODAY()”的后面，输入“-YEAR(G3)”，按Enter键，完成计算“工龄”公式的输入。设置H3单元格的数据类型为“常规”，再双击H3单元格右下角的填充柄，计算出其他员工的“工龄”。3.3管理与分析工资表3.3管理与分析工资表3.3.1排序简单排序多关键字排序3.3.2筛选1.自定筛选自动筛选是指按照某一条件进行的数据筛选。2.高级筛选自动筛选可以实现同字段之间的“或”、“与”运算，也可以实现不同字段之间的“与”运算，但是不能实现多字段之间

12、的“或”运算。当筛选出满足字段间条件为“或”关系的记录时，必须使用高级筛选才能实现。3.3.3分类汇总分类汇总在做分类汇总之前，需先按照某个关键字排序，然后按照该字段进行分类汇总。嵌套分类汇总所谓嵌套分类汇总，是指用两个或两个以上关键字段作为分类字段的多次汇总方式。3.3.4数据透视表数据透视表是一种交互式的数据透视表，可以对工作表照片那个的数据进行汇总和分析，主要用于深入分析数据。任务实施步骤1：将“一月份应该工资表”创建6个副本，分别重命名为“简单排序”、“多关键字排序”、“自动筛选”、“高级筛选”、“分类汇总”和“嵌套分类汇总”。步骤2：切换到“简单排序”工作表中，将鼠标置于“应发工资”

13、列的任一单元格。步骤3：单击【开始】选项卡【编辑】组中的【排序和筛选】按钮，从弹出的列表中，选择【降序】命令，设置所有数据按应发工资降序排序。步骤4：切换到“多关键字排序”工作表中，将光标定位于数据区域，再次单击【开始】选项卡【编辑】组中的【排序和和筛选】按钮，在弹出的下拉菜单中，选择【自定义排序】命令，打开【排序】对话框，参数设置如图3-66所示。步骤5：切换到“自动筛选”表中，定位数字区域，单击【数据】选项卡【排序和筛选】组中的【筛选】按钮，单击“姓名”列标题旁的下拉箭头，在弹出的下拉框中，选择【文本筛选】子列表中的【开头是】命令，弹出【自定义自动筛选方式】对话框，参数设置如图3-67所示

14、。步骤6：在“工龄”列标题的下拉列表，在打开的下拉列表中选择“数字筛选”子列表中的【小于或等于】命令，参数设置如图3-68所示。步骤7：切换到“高级筛选”工作表中，筛选出“开发部”奖金大于等于500或者“客服部”的“应发工资”大于等于10000，具体的条件设置如图3-69所示，筛选对话框如图3-70所示。步骤8：切换到“分类汇总”工作表中，使用分类汇总求出各部门“工龄工资”、“奖金”的最大值。将光标定位于数字区域，单击单击【数据】选项卡【分级显示】组中的【分类汇总】按钮，打开【分类汇总】对话框，参数设置如图3-71所示。步骤9：切换到“嵌套分类汇总”工作表，汇总各部门男女职工的工龄工资和奖金的

15、最小值。先按“部门”和“性别”排序，“部门”的分类和上图一样，再次打开【分类汇总】按钮，打开【分类汇总】对话框，参数设置如图5-72所示。3.4工资图表设计3.4.1创建图表 图表图表可以更好地使所处理的数据以更加直观、生动、清晰地显示不同数据间的差异，更好地显示出发展趋势和分布状况，便于用户更好地理解各种数据之间的相互关系，使用户一目了然地看清数据的大小、差异和变化趋势。同时当工作表区域的数据发生变化时，图表中对应的数据也自动更新，可以同步显示数据。创建图表Excel2010中的图表有两种放置方式，一种是嵌入图，即将数据和图表显示在一张工作表中；另外一种是将图表单独放置在一张工作表中。3.4

16、.2修改图表调整图表的大小和位置要调整图表的大小，先选中图表，然后将鼠标光标移到图表的浅蓝色边框的控制点上，当鼠标光标变成可调整光标时，拖动鼠标左键即可调整图表的大小。更改图表类型用户需要更改已经创建好的图表的类型，可以单击“设计”选项卡的“类型”组中的“更改图表类型”按钮，弹出“更改图表类型”对话框 编辑图表数据源图表创建好后，可以根据需要随时更新图表所使用的数据源。选中图表，单击“设计”选项卡的“数据”组中的“选择数据”按钮，弹出“选择数据源”对话框，单击“图表数据区域”右侧的折叠按钮，返回到工作表中重新选择数据源区域，选择完毕，确认无误，单击“确定”按钮，即可完成数据源的更新。交换图表的

17、行与列创建图表后，横坐标和纵坐标可以进行调整。单击“设计”选项卡的“切换行/列”按钮即可。设置坐标轴与标题用户可以设置图表中的坐标轴的显示方式，从而使水平和垂直坐标的内容更加丰富，步骤也是单击“布局”选项卡“标签”组中的“坐标轴标题”按钮，在打开的下拉列表框中选择“主要横坐标轴标题”或“主要纵坐标轴标题”选项，再从其子菜单中选择设置项。任务实施步骤1：将图3-79的图表，右键单击，选择“移动图表”，选择“新工作表”按钮，并将新的工作表重命名为“各部门应发工资和奖金”。步骤2：选中图表。单击“设计”选项卡的“数据”组中的“切换行/列”按钮，调整数据显示。步骤3：单击【布局】选项卡【标签】组中的【

18、坐标轴标题】按钮，在弹出的菜单中选择【主要横坐标轴标题】子菜单中的【坐标轴下方标题】命令，在生成的文本框中输入“部门”；选择【主要纵坐标轴标题】子菜单中的【旋转过的标题】命令，在生成的文本框中输入“工资额”。步骤4：单击【布局】选项卡【标签】组中的【图表标题】按钮，在弹出的菜单中选择【图表上方】命令，在生成的文本框中输入“各部门奖金和应发工资对比”。如图3-80所示。3.4.3美化图表创建图表后，可以使用Excel2010提供的布局和样式来快速设置图表外观，选中图表，在“设计”选项卡的“图表布局”组中选择图表的布局类型，在“图表样式”组中选择图表的颜色搭配方案。3.4.4页面设置设置纸张设置页边距页眉和页脚设置设置打印工作表任务实施 步骤1：选择“文件”-“打印”菜单命令，打开“打印”面板，右侧为预览窗口，如图5-86所示。任务实施 步骤2：在“份数”数值框中输入要打印的份数。步骤3：如果打印当前工作表所有项，单击“设置”下方的“打印活动工作表”，如果仅打印部分页，则在“页数”和“至”微调框中输入起始页和终止页码。步骤4:单击“打印”按钮，开始打印。谢谢！信息技术是沟通现实世界的桥梁。