第五章Excel的使用.ppt

第五章 Excel的使用,Excel的基本应用-制作工资表,运行环境：OfficeXP,Excel简介,Excel是一个非常优秀的电子表格应用软件，它拥有一些经过优化的电子表格功能，使用它可以：设计能够适合各种应用的电子表格以表格的形式存储大量的数据处理和管理数据排列和分析数据执行数学计算等进行页面设置以便打印,本课目标,熟练掌握工作表的建立掌握公式和函数的使用学会对数据进行处理掌握工作表的设置与打印了解数据透视表的建立,本课总任务制作职工工资表,在本章中，我们将利用Excel完成“南方公司”职工工资的情况分析处理，根据该任务所涉及到的Excel操作的内容，该任务大体归纳为如四个子任务：任务一:建立职工基本情况表任务二:职工基本情况分析任务三:职工基本情况表的格式设置与打印任务四:职工情况表进一步分析。,点击工资表看范例,这就是我们最终的任务,我们会把“工资表”这个任务划分很多小任务，下面我们一起来看看具体的任务。,任务1：建立职工基本情况表,“巧妇难为无米之饮”，Excel的功能再强大，工作表中的原始数据还得手工输入，本任务是建立图所示的“南方公司职工基本情况表”,1.建立职工基本情况表 1).Excel的基本概念 2).建立工作表 a.输入基本数据 b.输入标题 c.输入序号 d.输入性别 e.输入部门、学历 f.输入应发工资、扣款合计、实发工资 g.重命名工作表 h.复制数据 i.保存2.职工基本情况分析3.职工基本情况表的格式设置与打印4.职工情况表进一步分析,1.建立职工基本情况表 1).Excel的基本概念 2).建立工作表 a.输入基本数据 b.输入标题 c.输入序号 d.输入性别 e.输入部门、学历 f.输入应发工资、扣款合计、实发工资 g.重命名工作表 h.复制数据 i.保存2.职工基本情况分析3.职工基本情况表的格式设置与打印4.职工情况表进一步分析,任务1-1：Excel的基本概念,Excel的启动：执行命令“开始”“程序Microsoft Excel”,屏幕显示Excel应用程序窗口.,点击这里打开Excel,名称框,活动单元格,工作表标签,公式编辑栏,行标题,列标题,本任务效果如下：,任务1-2：建立工作表,点击这里看本任务效,1.建立职工基本情况表 1).Excel的基本概念 2).建立工作表 a.输入基本数据 b.输入标题 c.输入序号 d.输入性别 e.输入部门、学历 f.输入应发工资、扣款合计、实发工资 g.重命名工作表 h.复制数据 i.保存2.职工基本情况分析3.职工基本情况表的格式设置与打印4.职工情况表进一步分析,任务1-2-1：输入基本数据,1、Excel启动后，自动建立三个空白的“工作表”，名称依次为“Sheet1”、“Sheet2”、“Sheet3”。2、在第2行中依次填入例标题：“序号、姓名、性别、”。3、对照图依次输入每人的“姓名”、“基本工资”、“补贴”、“养老”、“保险”数据。,1.建立职工基本情况表 1).Excel的基本概念 2).建立工作表 a.输入基本数据 b.输入标题 c.输入序号 d.输入性别 e.输入部门、学历 f.输入应发工资、扣款合计、实发工资 g.重命名工作表 h.复制数据 i.保存2.职工基本情况分析3.职工基本情况表的格式设置与打印4.职工情况表进一步分析,点击这里看本任务效,任务1-2-2：输入标题,第一步：单击“序号”上方的单元格(A列1行，简记为A1)，并向右拖动至“实发工资”上方的单元格(L列1行，简记为L1)，以选定标题占据的单元格区域。第二步：单击“格式”工具栏的“(合并及居中)”按钮。第三步：在融合出来的单元格中，输入标题“南方公司职工基本情况表”。,1.建立职工基本情况表 1).Excel的基本概念 2).建立工作表 a.输入基本数据 b.输入标题 c.输入序号 d.输入性别 e.输入部门、学历 f.输入应发工资、扣款合计、实发工资 g.重命名工作表 h.复制数据 i.保存2.职工基本情况分析3.职工基本情况表的格式设置与打印4.职工情况表进一步分析,点击这里看本任务效,任务1-2-3：输入序号,“序号”从“1”到“10”，很有规律，可以采用如下快捷方式输入：1、在单元格A3中输入数字1；2、执行命令“编辑填充序列”3、参照图操作：,1.建立职工基本情况表 1).Excel的基本概念 2).建立工作表 a.输入基本数据 b.输入标题 c.输入序号 d.输入性别 e.输入部门、学历 f.输入应发工资、扣款合计、实发工资 g.重命名工作表 h.复制数据 i.保存2.职工基本情况分析3.职工基本情况表的格式设置与打印4.职工情况表进一步分析,点击这里看本任务效,任务1-2-4：输入性别,性别不是“男”就是“女”，重复出现，快捷输入方法如下：1、选中单元格C32、按住Ctrl不放，依次单击C4、C6、C9、C10，如图。3、松开Ctrl键，输入“男”4、按住Ctrl不放，再按Enter健。5、类似输入所有的“女”字。,1.建立职工基本情况表 1).Excel的基本概念 2).建立工作表 a.输入基本数据 b.输入标题 c.输入序号 d.输入性别 e.输入部门、学历 f.输入应发工资、扣款合计、实发工资 g.重命名工作表 h.复制数据 i.保存2.职工基本情况分析3.职工基本情况表的格式设置与打印4.职工情况表进一步分析,点击这里看本任务效,任务1-2-5：输入部门、学历,多个人在同一部门，国家认可的学历只有那么几种，所以“部门”与“学历”名称重复出现。有如下快捷输入方法：当在D7中输入“市”后，系统自动显示出“市场部”，按Enter(回车)“场部”二字自动输入，这是因为“市场部”三个字已在D6中输入过。,1.建立职工基本情况表 1).Excel的基本概念 2).建立工作表 a.输入基本数据 b.输入标题 c.输入序号 d.输入性别 e.输入部门、学历 f.输入应发工资、扣款合计、实发工资 g.重命名工作表 h.复制数据 i.保存2.职工基本情况分析3.职工基本情况表的格式设置与打印4.职工情况表进一步分析,点击这里看本任务效,任务1-2-6：输入应发工资、扣款合计、实发工资,应发工资等于基本工资与补贴的和扣款合计等于养老与医保的和实发工资等于应发工资与扣款合计的差这些数据不必直接输入，即使你愿意直接输入，也有可能输错，具体如何输入，等完成了任务二，你自然就明白了！,1.建立职工基本情况表 1).Excel的基本概念 2).建立工作表 a.输入基本数据 b.输入标题 c.输入序号 d.输入性别 e.输入部门、学历 f.输入应发工资、扣款合计、实发工资 g.重命名工作表 h.复制数据 i.保存2.职工基本情况分析3.职工基本情况表的格式设置与打印4.职工情况表进一步分析,任务1-2-7：重命名工作表,工作表的缺省名称为“Sheet1”，很不直观，现改名为“绝密资料”：1、双击“Sheet1”使之变成选种状态2、输入“绝密资料”，单击任意单元格，退出工作表名的编辑状态,1.建立职工基本情况表 1).Excel的基本概念 2).建立工作表 a.输入基本数据 b.输入标题 c.输入序号 d.输入性别 e.输入部门、学历 f.输入应发工资、扣款合计、实发工资 g.重命名工作表 h.复制数据 i.保存2.职工基本情况分析3.职工基本情况表的格式设置与打印4.职工情况表进一步分析,点击这里看本任务效,任务1-2-8：复制数据,将“绝密资料”工作表复制到其它工作表中，如到Sheet2工作表中。第一步：单击“绝密资料”工作表的“全选”按钮，以选中整个工作表，单击“(复制)”按钮，将整个工作表复制到剪贴板上。第二步：单击工作表标签“Sheet2”单击Sheet2的A1单元格单击“(粘贴)”按钮。,1.建立职工基本情况表 1).Excel的基本概念 2).建立工作表 a.输入基本数据 b.输入标题 c.输入序号 d.输入性别 e.输入部门、学历 f.输入应发工资、扣款合计、实发工资 g.重命名工作表 h.复制数据 i.保存2.职工基本情况分析3.职工基本情况表的格式设置与打印4.职工情况表进一步分析,点击这里看本任务效,任务1-2-9：保存,Excel启动时自动建立三张工作表Sheet1、Sheet2、Sheet3，存盘时这些表保存在同一个文件中，这个文件称为“工作簿”，保存方法有：执行命令“文件保存”。单击工具栏上的“(保存)”按钮。若当前工作簿从未存盘，自动转为“另存为”命令，需要用户选择存放文件的位置、输入文件名。,1.建立职工基本情况表 1).Excel的基本概念 2).建立工作表 a.输入基本数据 b.输入标题 c.输入序号 d.输入性别 e.输入部门、学历 f.输入应发工资、扣款合计、实发工资 g.重命名工作表 h.复制数据 i.保存2.职工基本情况分析3.职工基本情况表的格式设置与打印4.职工情况表进一步分析,任务1小结,本任务是学习Excel的启动、工作表的建立、单元格的合并、规律数据的输入、重复数据的输入、工作表的复制、工作表的重命名、工作簿的保存，课后加强练习,巩固强化，认真思考，做到举一反三。,任务2：职工基本情况分析,当“职工基本情况表”建好后，要对表格中的数据进行完善与分析。公式和函数是Excel中的精华，Excel提供了各种统计计算功能，可以用系统的运算符和函数构造计算公式，系统将按照计算公式自动进行计算。还可以利用数据排序、数据筛选、分类汇总和合并计算功能对数据进行统计和分析，甚至可以将单元格中的数据以各种统计图表的形式显示，使繁杂的数据更加生动、易懂，可以直观、清晰地显示不同数据间的差异。,1.建立职工基本情况表2.职工基本情况分析 1).计算应发工资、扣款合计、实发工资 2).计算基本工资等款项的最大值 3).对基本工资排序 4).根据基本工资查询 5).计算每种学历的人均工资 6).计算每种学历的工资总额 7).图形方式显示每种学历的应发工资总额3.职工基本情况表的格式设置与打印4.职工情况表进一步分析,下面我们利用公式：应发工资=基本工资+补贴、扣款合计=养老+医保、实发工资=应发工资扣款合计，将计算结果填入到相应的单元格中。第一步：选定单元格H3，并在编辑栏输入公式“=F3+G3”并回车第二步：将鼠标指标悬停在H3的右下角，当指标变成时，向下拖动鼠标到H12再松手 第三步：在K3中输入“=I3+J3”，在L3中输入“=H3-K3”，重复第2步操作。,任务2-1：计算应发工资、扣款合计、实发工资,点击这里看本任务效果,1.建立职工基本情况表2.职工基本情况分析 1).计算应发工资、扣款合计、实发工资 2).计算基本工资等款项的最大值 3).对基本工资排序 4).根据基本工资查询 5).计算每种学历的人均工资 6).计算每种学历的工资总额 7).图形方式显示每种学历的应发工资总额3.职工基本情况表的格式设置与打印4.职工情况表进一步分析,Excel提供了大量的函数，对数据的处理提供了方便，最大值可利用函数MAX来计算：第一步：单击F13单元格。第二步：单击编辑栏左边“插入函数”快捷键，选择“常用函数”中的“MAX”。第三步：单击Number1右边的按钮，将数据区域选为，表示在此区域内运算。第四步：单击“确定”按钮，即将选定区域内F3：F12的最大值填入到F13单元格。第五步：拖动F13的填充柄，将F13中的公式复制到G13到L13，以求出其它款项的最大值。,任务2-2：计算基本工资等款项的最大值,点击这里看本任务效果,1.建立职工基本情况表2.职工基本情况分析 1).计算应发工资、扣款合计、实发工资 2).计算基本工资等款项的最大值 3).对基本工资排序 4).根据基本工资查询 5).计算每种学历的人均工资 6).计算每种学历的工资总额 7).图形方式显示每种学历的应发工资总额3.职工基本情况表的格式设置与打印4.职工情况表进一步分析,基本工资”是确定名次的依据，在Excel中称为“关键字”现在完成基本工资的降序排列：1、选中单元格F32、执行命令“数据”“排序”3、按图进行选择，其中“有标题行”是指第二行是标题而不是数据，不参与排队。,任务2-3：对基本工资排序,点击这里看本任务效果,1.建立职工基本情况表2.职工基本情况分析 1).计算应发工资、扣款合计、实发工资 2).计算基本工资等款项的最大值 3).对基本工资排序 4).根据基本工资查询 5).计算每种学历的人均工资 6).计算每种学历的工资总额 7).图形方式显示每种学历的应发工资总额3.职工基本情况表的格式设置与打印4.职工情况表进一步分析,若要查找部分数据，可用数据筛选功能。如要查询“基本工资”中小于2500且大于1700的所有人员。其操作步骤如下：1、单击数据区任意单元格，如F3。2、执行命令“数据”“筛选”“自动筛选”。3、单击“基本工资”右边的“自定义()”，弹出对话窗口。4、按图进行操作。,任务2-4：根据基本工资查询,点击这里看本任务效果,1.建立职工基本情况表2.职工基本情况分析 1).计算应发工资、扣款合计、实发工资 2).计算基本工资等款项的最大值 3).对基本工资排序 4).根据基本工资查询 5).计算每种学历的人均工资 6).计算每种学历的工资总额 7).图形方式显示每种学历的应发工资总额3.职工基本情况表的格式设置与打印4.职工情况表进一步分析,任务2-5：计算各种学历的人均工资,算算每种学历的人均工资看看高学历是否高收入？这种运算在Excel中称为“分类汇总”，操作步骤：1、按“学历”进行排序。2、执行命令“数据”“分类汇总”。3、按图进行操作，在“选定汇总项”处选定“基本工资”、“应发工资”、“实发工资”。4、在“汇总方式”处选择“求和、计数、最大值、最小值”等。,点击这里看本任务效果,1.建立职工基本情况表2.职工基本情况分析 1).计算应发工资、扣款合计、实发工资 2).计算基本工资等款项的最大值 3).对基本工资排序 4).根据基本工资查询 5).计算每种学历的人均工资 6).计算每种学历的工资总额 7).图形方式显示每种学历的应发工资总额3.职工基本情况表的格式设置与打印4.职工情况表进一步分析,任务2-6：计算不同学历的工资总额,利用“分类汇总”方法，计算每种学历的人均工资，但是数据显示的方式不尽人意，尤其要提取这些数据进行再分析时，感到很不方便，如希望得到图所示的汇总数据，这时可使用“合并计算”，方法如下：1、对“学历”排序，选定E14。2、执行命令“数据”“合并计算”3、按图操作，单击选定E2:L12。选定“学历”到“实发工资”的所有数据、标题。4、区域E2:L12中的首行是标题，首列是分类标准，故勾上“首行”、“首列”。,点击这里看本任务效果,1.建立职工基本情况表2.职工基本情况分析 1).计算应发工资、扣款合计、实发工资 2).计算基本工资等款项的最大值 3).对基本工资排序 4).根据基本工资查询 5).计算每种学历的人均工资 6).计算每种学历的工资总额 7).图形方式显示每种学历的应发工资总额3.职工基本情况表的格式设置与打印4.职工情况表进一步分析,任务2-7：图形方式显示每种学历的应发工资总额,在前面两个任务中，计算每种学历的人均工资、工资总额，现在形象直观将每类人员的应发工资总额以“饼图”(百分比)方式显示，如图所示，非常直观。,点击这里看本任务效果,1、选定E15:E19，及H15:H19，即图5-13中的“学历”、“应发工资”值。2、单击按钮选择“标准类型”选择“饼图”选择“三维饼图”单击“下一步”直到“步骤3”。3、因图中，已标示了每块的名称，故“步骤3”中应选择“图例”，去掉“显示图例”前面的“”，表示不必再给出图例。4、按照图所示，选择“数据标志”，选中“类别名称”与“百分比”。5、单击“完成”，大功告成！,1.建立职工基本情况表2.职工基本情况分析 1).计算应发工资、扣款合计、实发工资 2).计算基本工资等款项的最大值 3).对基本工资排序 4).根据基本工资查询 5).计算每种学历的人均工资 6).计算每种学历的工资总额 7).图形方式显示每种学历的应发工资总额3.职工基本情况表的格式设置与打印4.职工情况表进一步分析,任务2小结,本任务是对“职工基本情况”分析，通过此任务，我们学习了：公式和函数的使用、数据的分类汇总、数据的排序、数据的筛选、数据的合并计算、数据的图形显示，课后加强练习，对未讲到的内容，希望阅读相关知识，举一反三，熟练掌握。,任务3：职工基本情况表的格式设置与打印,现在对“南方公司职工基本情况表”工作表进行设置，最后得到如图所示的结果，我们应该怎么做呢？,1.建立职工基本情况表2.职工基本情况分析3.职工基本情况表的格式设置与打印 1).设置文本（数据）格式 a.设置标题、表头格式（字体，底纹，颜色）b.表格对齐与货格格式设置 c.设置单元格底纹 d.设置表格边框 e.设置工作表的列宽和行高 2).打印工作表4.职工情况表进一步分析,任务3-1：设置文本（数据）格式,文本的格式设置与前一章在Word中设置的方法类似,1.建立职工基本情况表2.职工基本情况分析3.职工基本情况表的格式设置与打印 1).设置文本（数据）格式 a.设置标题、表头格式（字体，底纹，颜色）b.表格对齐与货格格式设置 c.设置单元格底纹 d.设置表格边框 e.设置工作表的列宽和行高 2).打印工作表4.职工情况表进一步分析,任务3-1-1：设置标题、表头格式（字体，底纹，颜色）,第一步：将标题“南方公司职工基本情况表”的字体设置为：宋体，字号：20第二步：选定A2:L2，设置为：隶书、加粗。第三步：把字体颜色设为“红色”。第四步：背景设为“鲜绿”。,1.建立职工基本情况表2.职工基本情况分析3.职工基本情况表的格式设置与打印 1).设置文本（数据）格式 a.设置标题、表头格式（字体，底纹，颜色）b.表格对齐与货格格式设置 c.设置单元格底纹 d.设置表格边框 e.设置工作表的列宽和行高 2).打印工作表4.职工情况表进一步分析,点击这里看效果,任务3-1-2：表格对齐与货币格式设置,将“基本工资”到“实发工资”列数据右对齐；表头和其余各列居中把所有的工资数据以货币的格式显示,1.建立职工基本情况表2.职工基本情况分析3.职工基本情况表的格式设置与打印 1).设置文本（数据）格式 a.设置标题、表头格式（字体，底纹，颜色）b.表格对齐与货格格式设置 c.设置单元格底纹 d.设置表格边框 e.设置工作表的列宽和行高 2).打印工作表4.职工情况表进一步分析,点击这里看效果,任务3-1-3：设置单元格底纹,第一步：选定“所在部门”列的数据单元格区域D3：D12。第二步：执行命令“格式”“条件格式”。第三步：按照图设置，单击“格式”按钮选“图案”选择某种灰色,1.建立职工基本情况表2.职工基本情况分析3.职工基本情况表的格式设置与打印 1).设置文本（数据）格式 a.设置标题、表头格式（字体，底纹，颜色）b.表格对齐与货格格式设置 c.设置单元格底纹 d.设置表格边框 e.设置工作表的列宽和行高 2).打印工作表4.职工情况表进一步分析,点击这里看效果,任务3-1-4：设置表格边框,1、选择标题以外的所有单元格区域，单击工具栏中“边框线”下拉箭头，单击第三行第二个边框线型,2、单击“边框线”下拉箭头，单击第三行第四个边框线型,3、选定F3：L12，单击工具栏中“边框线”下拉箭头，单击第二行第一个边框线型,1.建立职工基本情况表2.职工基本情况分析3.职工基本情况表的格式设置与打印 1).设置文本（数据）格式 a.设置标题、表头格式（字体，底纹，颜色）b.表格对齐与货格格式设置 c.设置单元格底纹 d.设置表格边框 e.设置工作表的列宽和行高 2).打印工作表4.职工情况表进一步分析,点击这里看效果,任务3-1-5：设置工作表的列宽和行高,1、选定“学历”列，单击鼠标右键，单击“列宽”，在“列宽”对话框中输入6,2、选定表头行，单击鼠标右键，单击“行高”，在“行高”对话框中输入28,1.建立职工基本情况表2.职工基本情况分析3.职工基本情况表的格式设置与打印 1).设置文本（数据）格式 a.设置标题、表头格式（字体，底纹，颜色）b.表格对齐与货格格式设置 c.设置单元格底纹 d.设置表格边框 e.设置工作表的列宽和行高 2).打印工作表4.职工情况表进一步分析,点击这里看效果,任务3-2：打印工作表,将表格打印出来了，并常常要求，当一页打不完时，希望每页的顶部都有页标，方法如下：第一步：执行命令“文件”“页面设置”“工作表”，按图 操作，其“顶端标题行”值为$1:$2，它表示第一行、第二行是每页的标题。第二步：单击预览按钮可以查看打印效果，单击打印按钮可以打印输出。,1.建立职工基本情况表2.职工基本情况分析3.职工基本情况表的格式设置与打印 1).设置文本（数据）格式 a.设置标题、表头格式（字体，底纹，颜色）b.表格对齐与货格格式设置 c.设置单元格底纹 d.设置表格边框 e.设置工作表的列宽和行高 2).打印工作表4.职工情况表进一步分析,任务3小结,本任务是完成“职工基本情况表的格式设计与打印”，通过该任务，学习工作表的行、列、单元格格式、表格边框线、工作表的列宽和行高的设置，工作表的页面设置与打印输出。,1.建立职工基本情况表2.职工基本情况分析3.职工基本情况表的格式设置与打印4.职工情况表进一步分析,任务4：职工情况表进一步分析,如果要根据“所在部门”、“学历”、“性别”三个关键字来分类汇总“基本工资”，同一部门中同一学历的男性人员的基本工资、同一部门同一学历的女性人员的基本工资，“分类汇总”方法无能为力，这时可采用“数据透视表”。本任务，是根据南方公司职工基本情况表，以“所在部门”为第一关键字(“分页”字段)，以“性别”为第二关键字(“行”字段)，以“学历”为第三关键字(“列”字段)，计算“基本工资”的合计数，最终的效果如图。,1、选定有数据的单元格。2、执行命令“数据”“数据透视表和数据透视图”3、按图操作,4、图中直接单击“下一步”,5、在图中单击“布局”。,6、按照图5-34所示，将关键字段拖放到相应位置。,7、在上图中双击“求和项：基本工资”进入下图，从图中可知，还能选择其它汇总方式。,8、在“数据透视选项”对话框中，对各种选项进行设置，一般情况下，直接采用其默认值。9、单击“确定”，之后单击“完成”。,10、完成“透视表”的操作后，进入下图，利用这些工具来调整前面几步所做的操作，请大家试试下图的各个工具，看看能否按4个、5个关键字段进行分类汇总。,点击这里看效果,任务4小结,本任务是完成“职工情况表进一步分析”，通过此任务，学习了数据透视表的全部操作过程，了解了“透视表”与“分类汇总”、“合并计算”的相同与不同之点，透视表的功能非常强大，在其每步操作中，都有很多可选的操作，大家不断尝试，挖掘出透视表的潜能。,点击这里看最终效果,本章总结,通过本章学习，您已经学会了：工作表的建立、数据的输入等基本操作对工作表进行修饰，使工作表的外观给人以美的享受对工作表中的数据按用户需要进行升序或降序排列对工作表中的数据按用户需要进行筛选，保留用户感兴趣的内容对工作表中的数据按用户需要进行分类汇总，统计出有用的信息对工作表中的数据按用户需要进行合并计算，根据条件统计出有关的信息创建图表，对工作表中的数据按用户需要用图表的形式进行描述对工作表中的数据按用户需要进行打印设置，打印出所需格式的数据表,