超全总结!Excel2013数据分析实例讲解

-.z.E*cel数据处理与分析E*cle2013是微软公司office2013系列办公软件的组件之一，它是一个功能强大的电子表格程序。E*cle不仅可以将整齐而美观的表格呈现给用户，还可以用来进展数据的分析和预测，完成许多复杂的数据运算，帮助使用者做出更加有根据的决策。同时它还可以将表格中的数据通过各种各样的图形、图表的形式表现出来，增强表格的表达力和感染力。本章过员工工资表、公司销售统计表这两个案例，讲解了日常工作中E*cle的常用功能，使学生能够掌握响应的E*cle使用方法和使用技巧，提高表格的制作水平，从而提高工作效率。本章容将通过大量的实例来学习E*cel数据处理的根本操作。2.1数据输入工作簿是指在E*cel2013环境中用来存储并处理工作数据的文件，它是由假设干个工作表组成的。在E*cel2013中，可以说一个文件就是一个工作簿，工作簿窗口下方有假设干个标签，单击其中一个标签就会切换到该工作表。翻开E*cel2013时，映入眼帘的工作界面就是工作表。它由众多的行和列中的单元格排列在一起构成。工作表能存储包含字符串、数字、公式、图表和声音等丰富的信息或数据，并能够对这些信息或数据进展各种处理，同时能将工作表打印出来。当工作簿建立之后，就可以在工作簿的每一个工作表中输入数据了。在E*cel工作表的单元格中可以输入的数据有文本、数字、日期、时间和公式等。一、输入文本单元格中的文本包括任何字母、数字和键盘符号的组合。每个单元格最多可包含32000个字符，如果单元格列宽容不下文本字符串，就要占用相邻的单元格。如果相邻单元格中已有数据，就会截断显示。二、输入数字在E*cel中，数字可用逗号、科学计数法或*种格式表示。输入数字时，只要选中需要输入数字的单元格，按键盘上的数字键即可。在e*cel2013中，输入的数字数据长度在12为以上时，会自动转变为科学记数格式。当数据以0开头时则自动舍弃前面的0。如果要让长度为12位以上的数字正常显示，可以通过下面两种方法来实现。1、实用“’〞符号辅助输入在单元格中，先输入“’〞〔英文输入状态下的单引号〕，接着再输入。输入完成后，按<Enter>键即可正常显示输入的。2、设置单元格格式为“文本〞格式后输入三、输入日期和时间日期和时间也是数字，但它们有特定的格式。在输入日期时用斜线或短线分隔日期的年、月、日。例如，可以输入“2010/01/26〞或“2010-03-26〞，如果要输入当前的日期，按组合键Ctrl+;(分号)即可。在输入时间时，如果按12小时制输入时间，需在时间数字后空一格，并键入字母a或p，分别表示上午或下午。例如，输入10:40p，按Enter键后四的结果是22:40:00，如果只输入时间数字，E*cel将按AM(上午)处理，如果要输入当前的时间，按组合键Ctrl+Shift+;(分号)即可。四、自动填充利用自动填充功能，可以快速地复制数据、复制公式，加快数据的输入速度。操作方法。选定单元格，拖动该单元格右下方的填充句柄，即将光标移至该单元右下角，至光标变成十字形状〔+〕。按住鼠标左键不放，向下〔向右〕拖动。五、数据输入技巧E*cel2013中有许多数据输入的技巧，如在前面介绍过的自动填充功能等。本节将再介绍其他一些数据输入的技巧。1.在同一数据列中自动填写重复录入项E*cel2013具有数据记忆式键入功能，在同一行或同一列中，如果前面的单元格中已有数据输入，即在*单元格输入一个与前面单元格一样的数据时，E*cel会自动显示出该单元格后面的数据。但E*cel只能自动完成包含文字的录入项，或包含文字与数字的录入项。2.在多个单元格中输入一样的数据如果在工作表中有多处重复出现一样的数据，则在数据输入时，可首先将这些单元格同时选中，同时选中的操作方法为在选中第一个单元格后按下Ctrl键，再依次单击其他单元格。然后通过编辑栏输入数据，同时按下Ctrl+Enter键。此时数据将同时显示在被选中的多个单元格中。例1．在E*cel中建立公司员工工资表以及输入数据。启动E*cel，新建一个工作簿。并保存为“员工工资表.*ls〞。在“sheet1〞工作表的A1单元格中输入标题“员工工资表〞，选取A1：O1单元格区域，合并该单元格区域，实质字体为“隶书〞、“深蓝色〞、“加粗〞、字号为“24〞、“水平居中〞。在A2：O2单元格区域中依次输入“员工号〞、“月份〞、“〞等各列标题，字体设为“宋体〞、“红色〞、“加粗〞，结果如下图。图2.1员工工资表标题行在A3至O12单元格中输入相应的数据。其中，在A3单元格输入数字“GD001〞，选定A3单元格，拖动该单元格右下方的填充句柄至A12单元格，完成“员工号〞列数据的输入。选中C3:C12，单击“格式〞——“单元格〞菜单命令，在弹出的【单元格格式】对话框中单击“数字〞选项卡，在“分类〞列表框中选择“文本〞，单击“确定〞按钮完成设置，在C3:C12单元格中可输入18位数字的号。输入“〞、“部门〞、“职务〞、“根本工资〞、“职务工资〞、“加班津贴〞、“奖金〞、“缺勤〞等列数据。2.2公式和函数的使用电子表格系统除了能进展一般的表格处理外，还应该具有数据计算能力。E*cel2013作为优秀的电子表格处理软件，允许使用公式对数值进展计算。关于单元格和区域引用引用的作用在于标识工作表上的单元格或单元格区域，并指明公式中所使用的数据的位置。通过引用，可以在公式中使用工作表不同局部的数据，或者在多个公式中使用同一个单元格的数值。还可以引用同一个工作簿中不同工作表上的单元格和其他工作簿中的数据。引用不同工作簿中的单元格称为。默认情况下，E*cel使用A1引用样式，此样式引用字母标识列〔从A到IV，共256列〕，引用数字标识行〔从1到65,536〕。这些字母和数字称为行号和列标。假设要引用*个单元格，请输入列标和行号。例如，B2引用列B和行2穿插处的单元格。一、引用其他工作表中的单元格下面的例如中，AVERAGE工作表函数将计算同一个工作簿中名为Marketing的工作表的B1:B10区域的平均值。到同一个工作簿中的另一工作表上请注意，工作表的名称和感慨号(!)应位于区域引用之前。二、绝对引用与相对引用的区别一、相对引用公式中的相对单元格引用〔例如A1〕是基于包含公式和单元格引用的单元格的相对位置。如果公式所在单元格的位置改变，引用也随之改变。如果多行或多列地复制公式，引用会自动调整。默认情况下，新公式使用相对引用。例如，如果将单元格B2中的相对引用复制到单元格B3，将自动从=A1调整到=A2。图2.2相对引用二、绝对引用单元格中的绝对单元格引用〔例如$A$1〕总是在指定位置引用单元格。如果公式所在单元格的位置改变，绝对引用保持不变。如果多行或多列地复制公式，绝对引用将不作调整。例如，如果将单元格B2中的绝对引用复制到单元格B3，则在两个单元格中一样，都是$A$1。图2.3绝对引用三、混合引用混合引用具有绝对列和相对行，或是绝对行和相对列。绝对引用列采用$A1、$B1等形式。绝对引用行采用A$1、B$1等形式。如果公式所在单元格的位置改变，则相对引用改变，而绝对引用不变。如果多行或多列地复制公式，相对引用自动调整，而绝对引用不作调整。例如，如果将一个混合引用从A2复制到B3，它将从=A$1调整到=B$1。图2.4混合引用公式公式是对数据进展分析与计算的等式，使用公式可以对工作表中的数值进展加法、减法、乘法、除法等计算。所有的公式必须以符号“=〞或“+〞开场。一个公式是由运算符和参与计算的元素(操作数)组成的。公式的输入操作类似于输入文字数据，但输入一个公式的时候应以一个等号(=)作为开场，然后才是公式的表达式。在单元格中输入公式的步骤如下。(1) 选择要输入公式的单元格。(2) 在编辑栏的输入框中输入一个等号(=)，或者在当前选择的单元格中输入一个等号(=)，然后输入公式表达式。(3) 单击【确认】按钮或按Enter键就可以得到计算结果。公式中常常包含对其他单元格的相对引用。例2.接着上面的操作，在员工工资表中输入“应发工资〞、“缺勤扣款〞、“实发工资〞等列的数据。操作如下：单击“员工工资表.*ls〞的sheet1工作表中的L3单元格，在单元格中输入公式“=H3+I3+J3+K3〞,按回车键得出应发工资。在N3单元格中输入公式“=H3/30*M3〞，在O3单元格中输入公式“=L3-N3〞,结果如图2.5所示。图2.5输入公式按住CTRL键，单击M3、O3单元格，选中M3和O3单元格，单击【格式】——【单元格】，在弹出的【单元格格式】对话框中单击“数字〞选项卡，在“分类〞列表框中选择“数值〞选项，在“小数位数〞中输入“2〞，单击“确定〞按钮完成设置，设置的数字格式将应用于M3:O3单元格区域中。图2.6【单元格格式】对话框中“数字〞选项卡设置选定L3单元格，拖动该单元格右下方的填充句柄至L12单元格，完成“应发工资〞列数据的输入。使用同样的方法，完成“缺勤扣款〞和“实发工资〞列数据的输入。函数函数可以理解为是一种e*cel已定义好的复杂公式，也可以认为是公式的简写形式。函数可以单独使用，也可以在公式中调用函数。一、函数的语法规定函数使用一些被称为参数的数据按规定的顺序或构造进展计算，单数可以是数字、常量、逻辑值或但与个引用等。函数执行后一般给出一个结果，这个结果成为返回值。函数的构造为：函数名〔参数1，参数2，参数3，……〕二、手工输入函数对于函数的输入，可以采用手工的方法，首先单击选定要输入函数的单元格，鼠标单击E*cel编辑栏，键入一个等号“=〞，此时单元格进入公示编辑状态，在等号后按照公式的组成顺序依次输入各个局部，公式输入完毕后，单击编辑栏中的“输入〞(即“√〞)按钮或按回车即可。例3.在员工工资表中输入“月份〞、“性别〞等列的数据。操作如下：在D3单元格输入“=now()〞，按回车完毕输入，D3单元格将自动显示系统当前时间。选中D3单元格，单击鼠标右键，在弹出的快捷菜单中选择“设置单元格格式〞选项，在弹出的【单元格格式】对话框中单击“数字〞选项卡，在“分类〞列表框中选择“日期〞选项，在“类型〞文本框中输入“2001年3月〞，单击“确定〞按钮完成设置，设置的日期类型将应用于单元格D3中，调整列宽至适宜宽度。可以根据号求出性别的值。在E3单元格中输入公式“=IF(MOD(MID(C3,17,1),2)=1,"男","女")〞，按“Enter〞键得到计算结果。其中MID(C3,17,1)作用是将C3单元格中的字符串从第17位起，取1位。MOD(MID(C3,17,1),2)=1，作用是判断号的第17位能否被2整除，即判断是奇数还是偶数，如果MOD(MID(C3,17,1),2)=1，则C3单元格中第17位为奇数，否则为偶数。IF(MOD(MID(C3,17,1),2)=1,"男","女")，作用是如果MOD(MID(C3,17,1),2)=1成立，返回“男〞；否则，返回“女〞。图2.7性别的计算对于“应发工资〞的输入，除了可以用之前的公式以外，还可以用函数实现输入。选定L3单元格，单击“常用〞工具栏中的“自动求和〞按钮，L3单元格中显示求和函数，拖动鼠标选定H3:K3单元格区域，如图2.8所示，按【Enter】键，系统将自动完成求和运算并显示求和结果。图2.8求和函数选定D3单元格，拖动该单元格右下方的填充句柄至D12单元格，完成“月份〞列数据的输入。使用同样的方法，完成“性别〞和“应发工资〞列数据的输入。具体数据如图2.9所示。图2.9员工工资表数据将鼠标指向工作表标签，单击右键，在弹出的快捷菜单中选择“重命名〞，将工作表标签更改为“工资表〞。三、粘帖函数对于不熟悉的函数或较复杂的函数，手工输入时容易出错，可以采用【插入函数】对话框粘贴函数的方法输入函数。【插入函数】对话框是E*cel输入公式的重要工具，下面以Count和countif函数的使用为例介绍粘贴函数的过程。Count和countif函数都是统计函数，利用函数count可以计算单元格区域中数字项的个数，利用函数countif可以计算给定区域满足特定条件的单元格的数目。例4．在工作表工资明细表中，统计员工总人数以及全勤人数。操作步骤：在工作表“工资表〞A14单元格中输入“总人数〞，单击选定要输入函数的单元格B14。单击“编辑栏左侧〞的“插入函数〞按钮。弹出如以下图所示的【插入函数】对话框。图2.10【插入函数】对话框输入信息选项卡如果对于所用的函数不确定，可以在“搜索函数〞对应的文本框中输入需求的简单描述，单击“转到〞按钮，则在“选择函数〞列表中会有一些列的函数。逐个单击函数，在【插入函数】对话框窗口的下部会出现关系所选函数的相关说明，可根据相关说明选择适用的函数。本例中，我们可以在“或选择类别〞列表中选择“统计〞，在“选择函数〞列表中选择〞count〞，单击“确定〞按钮，弹出如下“函数参数〞窗口，填写相应的参数，单击“确定〞按钮，得出总人数。图2.11【函数参数】对话框在A15单元格中输入“全勤人数〞，单击选定要输入函数的单元格B15。单击“编辑栏左侧〞的“插入函数〞按钮。弹出如以下图所示的“插入函数〞对话框。图2.12【插入函数】对话框在“或选择类别〞列表中选择“统计〞，在“选择函数〞列表中选择〞countif〞，单击“确定〞按钮，弹出如图2.13“函数参数〞窗口，填写相应的参数。图2.13【函数参数】对话框单击“确定〞按钮，得出全勤人数。结果如图2.14所示。图2.14count和countif函数计算结果上述方法的最大优点就是引用的区域很准确，特别是三维引用时不容易发生工作表或工作簿名称输入错误的问题。2.3工作表的操作工作簿中可以包含多个工作表。新建E*cel工作簿的时候，默认情况下含有3工作表，标签分别为“sheet1〞、“sheet2〞、“sheet3〞，这和工具菜单下的选项命令的设置有关，如图2.15所示。如果要再插入新的工作表，可以选择“插入〞菜单下的“工作表〞命令。图2.15【选项】对话框窗口复制和移开工作表复制和移开工作表的操作可以采用鼠标拖动或菜单设置两种方式。例5．复制工作表“工资表〞，具体操作如下：右击选定“工资表〞工作表的标签，在出现的快捷菜单中选择“移动或复制工作表〞命令，翻开【移动或复制工作表】对话框。在“以下选定工作表之前〞列表框中选择“Sheet2〞，选定“建立副本〞选项，如图2.16所示。如果没有选定“建立副本〞选项，则只完成工作表的移动操作，不会复制工作表。图2.16【移动或复制工作表】对话框单击“确定〞按钮完成设置，在“Sheet2〞工作表的前面复制了一个名为“工资表〔2〕〞的工作表。图2.17复制工作表的结果按住Ctrl键的同时用鼠标左键拖开工作表标签到目标位置也可以复制工作表。如果不按住Ctrl键直接用鼠标左键拖开工作表标签到目标位置则为移开工作表提示：如果要跨工作簿进展工作表的移动或复制，则必须在“移动或复制工作表〞对话框中的“工作簿〞列表框中选择目标工作簿。工作表的重命名当工作簿中的工作表为多个的时候，默认的工作表标签为“sheet1〞、“sheet2〞、“sheet3〞……为了便于快速区分工作表的容，通常我们会给工作表起一个有意义的标签。工作表的重命名常用的有以下三种方法：1、单击“格式〞菜单下的“工作表〞子菜单中的“重命名〞菜单命令，当前工作表的标签为反白显示，可以输入新的标签名，为当前工作表重命名。2、鼠标指向需要重命名的标签，例如“工资表〔2〕〞，单击鼠标右键，在弹出的快捷菜单中选择“重命名〞，工作表的标签为反白显示，可以输入新的标签名“工资分析表〞作为当前工作表的新标签，如图2.18所示。图2.18重命名后的工作表3、双击需要重命名的标签，工作表的标签为反白显示，可以输入新的标签名为当前工作表重命名。工作表的删除如果要删除多余的工作表，可执行以下操作：1．右键单击“Sheet3〞工作表的标签。2．在出现的快捷菜单中选择“删除〞命令，删除“Sheet3〞工作表。2.4常用的数据处理与分析像其他数据库软件创立的数据库一样，E*CEL数据也可以方便的对数据进展处理和分析。数据有效性数据有效性可以指定允许的数据类型，以及可以承受的值得围。利用E*cel2013对数据设置自动检测功能，减少误操作。例6.在“工资表〞中，要求的输入长度必须是11位，可以利用数据有效性的功能进展数据的有效性设置。操作如下：单击工作表“工资表〞标签，选中“工资表〞为当前工作表。选择设定区域C3：C12作为承受条件作用的单元格区域。单击“数据〞菜单中的“有效性〞命令，弹出【数据有效性】对话框，【数据有效性】对话框中的“设置〞选项卡指明了数据类型及允许值的围；“输入信息〞选项卡能够创立一个提示，告诉用户哪种数据是允许的。“出错警告〞选项卡是在输入非法数据时，显示错误的信息。选择“设置〞选项卡，在“允许〞项选择“文本长度〞，在“数据〞项选择“等于〞，在“长度〞项输入“18〞，如图2.19所示。图2.19【数据有效性】对话框单击“输入信息〞选项卡，选择“选定单元格时显示输入信息〞，在“标题〞项输入“友情提醒〞，在“输入信息〞文本框中输入“请输入18位的号〞，如图2.20所示。图2.20【数据有效性】的“输入信息〞设置单击“出错警告〞选项卡，设置警告提示信息，如图2.21所示。图2.21【数据有效性】窗口“出错警告〞设置单击“确定〞按钮，完成“〞列数据项的有效性设定，此时选定“〞列〔区域C3：C12〕中的任一单元格，都会出现提示信息，效果如图2.22所示。图2.22完成数据有效性设置后出现提示信息的效果如果输入长度不是18位，则会弹出如图2.23所示的出错警告，提示“您输入的号不是18位的。〞图2.23出错警告对话框例7．在“工资表〞中，利用数据有效性设定“奖金〞输入值围为0至5000之间操作如下：2．在“工资表〞中选择设定区域K3：K12作为承受条件作用的单元格区域。单击“数据〞菜单中的“有效性〞命令，弹出【数据有效性】对话框，在“允许〞项选择“整数〞，在“数据〞项选择“介于〞，在“最小值〞项输入“0〞，在“最大值〞项输入“5000〞，如图2.24所示。图2.24【数据有效性】对话框单击“输入信息〞选项卡，选择“选定单元格时显示输入信息〞，在“标题〞项输入“友情提醒〞，在“输入信息〞文本框中输入“输入值在0至5000之间〞。单击“出错警告〞选项卡，设置警告提示信息，在“样式〞项中选择“警告〞，在“标题〞项中输入“错误〞，在“错误信息〞项中输入“超出值围〞。单击“确定〞按钮，完成“奖金〞列数据项的有效性设定。数据条件格式在E*cel2013中提供了一个功能非常独特的数据处理功能，它就是“条件格式〞。通过数据条件格式的设置，可以将单元格中满足指定条件的数据进展特定标记。例8．在工资表中，设置“实发工资〞大于等于7000元的以“红色〞显示，“实发工资〞大于等于5000元且小于7000的以“蓝色〞显示，操作如下：在“工资表〞中选中设置数据条件格式的单元格区域，即O3:O12单击“格式〞菜单下的“条件格式〞命令，翻开如图2.25所示的【条件格式】对话框。图2.25【条件格式】对话框单击【条件格式】对话框中第二个方框右侧的下拉按钮，选中“大于或等于〞选项，在后面的方框中输入数值“7000〞。单击“格式〞按钮，翻开“单元格格式〞对话框，将“字体〞的“颜色〞设置为“红色〞。设置后的对话框如图2.26所示。图2.26设置了一个条件后的【条件格式】对话框按“添加〞按钮，并按照上面的操作设置好条件及格式设置，如图2.27所示。图2.27设置了两个条件的【条件格式】对话框.设置完成后，按下“确定〞按钮，这时“工资表〞中O3:O12数据是按条件设置的要求以不同颜色显示出来了。数据排序“排序〞功能是将数据按照特定的关键字进展排列，从而直观地反映数据间的区别。一、单关键字排序例9．在“工资分析表〞中，按“实发工资〞升序排序，即按“实发工资〞从小到大排序。操作如下：单击工作表标签“工资分析表〞，切换到“工资分析表〞。单击“实发工资〞列的任意单元格。单击工具栏中的“升序〞按钮后，“实发工资〞列中的数据自动按升序排列显示，如图2.28所示。图2.28按“实发工资〞升序排序后的结果二、多关键字排序例10．在“工资分析表〞中，“职务工资〞列中可能会有一样的数据。遇到这样的问题时，可以再设置次要关键字“应发工资〞，这样，会在职务工资一样的情况下再按照应发工资的大小来排序。操作如下：单击“工资分析表〞工作表数据区域的任意单元格。单击“数据〞菜单中的“排序〞命令，翻开【排序】对话框，在主要关键字中选择“职务工资〞按“升序〞排序；次要关键字中选择“应发工资〞按降序排序，“我的数据区域〞中，选择“有标题行〞，如图2.29所示。图2.29【排序】对话框单击“确定〞后，排序结果如图2.30所示。图2.30排序后的结果三、让数据按需排序如果你要将数据按*列进展排序，但是日常生活中对这种数据的大小排列可能是既不是按拼音顺序，也不是按笔画顺序，怎么办"可采用自定义序列来排序。例11．在“工资分析表〞中将员工按其职务进展升序排序。注意，职务的大小既不是按拼音顺序，也不是按笔画顺序，而是由小到大的顺序为“职员〞、“主管〞、“经理〞、“总监〞。操作如下：单击“工具〞菜单下的“选项〞命令，翻开“选项〞对话框，单击“自定义序列〞选项卡，在“自定义序列〞列表中选择“新序列〞，在“输入序列〞列表中按职务的从低到高输入各种职务，如图2.31所示。图2.31【选项】对话框单击“添加〞按钮将刚刚输入的序列添加到“自定义序列〞列表中，单击“确定〞按钮退出。单击“工资分析表〞工作表数据区域的任意单元格，单击“数据〞菜单下的“排序〞命令，翻开“排序〞对话框，在主要关键字中选择“职务〞按“升序〞排序。单击【排序】对话框中的“选项〞按钮，弹出“排序选项〞对话框，单击“自定义排列次序〞的下拉按钮，选中刚刚自定义的序列〔如图2.32所示〕，单击“确定〞按钮图2.32【排序选项】对话框按“确定〞按钮返回，所有数据就按要求进展了排序，结果如图2.33所示。图2.33自定义排序后的结果数据筛选数据管理时经常需要从众多的数据中挑选出一局部满足条件的记录进展处理。筛选是是E*cel提供的最为实用的数据处理功能之一，查找和处理区域中数据子集的快捷方法。筛选区域仅显示满足条件的行，该条件由用户针对*列指定。MicrosoftE*cel提供了两种筛选区域的命令：自动筛选和高级筛选。与排序不同，筛选并不重排区域。筛选只是暂时隐藏不必显示的行。一、自动筛选数据“自动筛选〞适用于简单条件。例12，利用“工资分析表〞复制一个工作表“工资分析表〔2〕〞，在“工资分析表〔2〕〞中筛选出销售部员工的工资数据。操作如下：按住Ctrl键的同时用鼠标左键拖动“工资分析表〞工作表标签，复制一个工作表“工资分析表〔2〕〞单击“工资分析表〔2〕〞中数据区域的任意单元格，单击【数据】菜单【筛选】下的【自动筛选】命令，翻开自动筛选器，如图2.34所示。图2.34翻开自动筛选器单击“部门〞字段名后的自动筛选箭头，在弹出的下拉列表中选择“销售〞。这时，“工资分析表〔2〕〞中就只显示了销售部员工的工资数据，其他部门的数据被隐藏起来。如图2.35所示。图2.35筛选结果例13．在“工资分析表〔2〕〞中筛选出实发工资在5000元以上〔含5000〕的员工数据。操作如下：单击“工资分析表〔2〕〞标签，单击“数据〞菜单下的“筛选〞子菜单中的“全部显示〞菜单命令，使该工作表中所有数据显示出来。单击“实发工资〞字段名后的自动筛选箭头，在弹出的下拉列表中选择“自定义〞，弹出【自定义自动筛选方式】对话框。在对话框中“实发工资〞下面的下拉列表框中选择“大于或等于〞，在其右侧的组合框中输入“5000〞，如图2.36所示。图2.36【自定义自动筛选方式】对话框单击“确定〞按钮，返回工作表中。这时，工作表中多余的表头被隐藏起来，如图2.37所示。图2.37自动筛选结果二、高级筛选高级筛选可以一次性对多个条件进展筛选。高级筛选与自动筛选不同，它要求在一个工作表区域单独指定筛选条件，称为条件区域。条件区域由条件标题行和条件表达式组成，如果条件表达式是在同一行的不同单元格中，E*cel适用AND运算符连接，表示将返回匹配所有单元格中条件的数据，如果表达式是在条件区域中的不同行中，E*cel适用OR运算符连接，表示匹配任何一个单元格中条件的数据都将返回。条件区域要与数据区域分开〔即和数据区域隔开至少一行或一列〕。高级筛选的功能有以下几个：1、指定与两列或两列以上有关的筛选条件及连接符【或】；2、对既定的*列指定3个或更多的筛选条件。例14．在“员工工资表..*ls〞中，假设给出这样的条件：筛选出科研、销售、市场三个部门根本工资大于或等于2000的记录。具体操作如下：按住Ctrl键的同时用鼠标左键拖动“工资分析表〞工作表标签，复制一个工作表“工资分析表〔3〕〞单击“工资分析表〔3〕〞标签切换到“工资分析表〔3〕〞工作表，选中数据区域的任意单元格。选取“工资分析表〔3〕〞工作表中的任意空白单元格并填写条件区域，如图2.38中下方的小方框所示。含义为：部门为“科研〞并且根本工资大于或等于2000，或者部门为“销售〞并且根本工资大于或等于2000，或者部门为“市场〞并且根本工资大于或等于2000。图2.38设置条件区域单击数据区域的任意一个单元格，单击【数据】菜单中的“筛选〞下的“高级筛选〞命令，弹出【高级筛选】对话框。在【高级筛选】对话框中，选定“将筛选结果复制到其它位置〞选项，分别选取列表区域、条件区域和复制到的位置，复制到其它位置可选取“A20〞单元格，设置如图2.39所示。注意，选取的列表区域和条件区域必须包含标题行。图2.39【高级筛选】对话框单击“确定〞，筛选后结果如图2.40所示。图2.40高级筛选结果分类汇总分类汇总是指将数据按指定的类进展汇总分析，在进展分类汇总前先要对所汇总数据进展排序。分类汇总可以将数据按照不同的类别进展统计。分类汇总不需要输入公式，也不需要使用函数，E*cel将自动处理并插入分类结果。在对数据分类汇总之前，我们要明确三个问题，第一个问题：分类的依据〔也称分类字段〕是什么？第二个问题：汇总的对象是什么？第三个问题：汇总的方式是什么？例15．在“员工工资表〞中，按“性别〞统计“职务工资〞、“奖金〞、“实发工资〞平均值，具体操作如下：按住Ctrl键的同时用鼠标左键拖动“工资分析表〞工作表标签，复制一个工作表“工资汇总表〞光标定位在“性别〞列的任一单元格，单击工具栏上的“升序〞或“降序〞按钮，按“性别〞进展排序。单击【数据】菜单中的“分类汇总〞命令，翻开【分类汇总】对话框。在“分类字段〞下拉列表中选择“性别〞，在“汇总方式〞下拉列表中选择“平均值〞，在“选定汇总项〞列表中选定“职务工资〞、“奖金〞、“实发工资〞选项，如图2.41所示。图2.41【分类汇总】对话框单击“确定〞按钮，得到汇总结果如图2.42所示。图2.42分类汇总结果提示：在上图窗口左侧显示了分类汇总的标志，其中是“隐藏明细数据符号〞；是分级显示标记。单击只显示总的汇总值，单击显示各类汇总值，单击显示所有的明细数据。是“显示明细数据符号〞。如果想要删除汇总的结果，可以单击数据区域中任一单元格，然后单击【数据】菜单中的“分类汇总〞命令，翻开【分类汇总】对话框，在【分类汇总】对话框中单击【全部删除】按钮，则只删除汇总结果，对原有的数据不删除。合并计算合并计算可以将单独工作表中的数据合并计算到一个主工作表中。这些工作表可以和主工作表在同一个工作簿中，也可以位于其他工作簿中。例16．公司的产品都销往全国各地，各个地区都会将销售数据汇报到总公司，如果公司要统计产品的总销售数据，可以使用“合并计算〞功能，实现各地区销售数量合并计算。假设各地区的销售数据分别位于华东销售情况表.*ls、华南销售情况表.*ls、华北销售情况表.*ls、华中销售情况表.*ls等不同的工作簿文件中具体数据如图2.43所示。图2.43各地区的销售数据操作步骤如下：依次翻开华东销售情况表.*ls、华南销售情况表.*ls、华北销售情况表.*ls、华中销售情况表.*ls等不同的工作簿文件。新建一个工作簿，保存为“总销售情况表.*ls〞。在“sheet1〞工作表的A1单元格中输入“总销售情况表〞，选择A1:F1单元格区域，设置字体为“楷体〞、“加粗〞，字号为“16〞，并单击格式工具栏中的“合并及居中〞按钮，将A1:F1单元格区域合并为一个单元格。选中A2单元格，单击“数据〞菜单下的“合并计算〞，翻开如图2.44所示的【合并计算】对话框。图2.44【合并计算】对话框在“函数〞中选择“求和〞。在“引用位置〞中单击拾取器按钮，单击windows任务栏中的“华南地区销售情况表〞按钮，在“华南地区销售情况表.*ls〞中选取华南销售情况数量的区域，在本例中为A2:F6区域，并单击拾取器按钮返回到“合并计算〞对话框中，单击“添加〞按钮，将所选的区域添加到“所有引用位置〞列表中。根据同样的操作，逐一将华北、华东、华中地区的销售数量添加到“所有引用位置〞列表中，如图2.45所示。图2.45所有引用完成后的【合并计算】对话框设置完成后，单击“确定〞按钮，即可合并计算出4各地区各季度产品的总销售数量，如图2.46所示。图2.46合并计算的结果2.5图表在数据分析中的应用图表可以通过图形的表达方式将复杂的数据表现出来，能够更加直观地比拟数据之间地变化，以及变化趋势关系。创立图表E*cel图表可以将数据图形化，帮助我们更直观地显示数据，使数据比照和变化趋势一目了然，提高信息整理价值，更准确直观地表达信息和观点。图表和工作表是互相的，当工作表中的数据发生改变时，图表中对应项的数据也自动改变。创立图表的操作方很多，我们将通过“图表向导〞创立的方法以为例子说明图表的建立最根本的图表。例17．下表为*公司各地区销售统计表，为了让业绩情况更直观的显示，请根据图中的数据转化为图表。图2.47“各地区销售情况表〞数据分析：利用“图表向导〞制作图表，首要的一步是选择生成图表的数据区域，接下来根据“图表向导〞的提示一步一步操作即可快速生成根本的图表。操作步骤：翻开“各地区销售统计表.*ls〞中的“销售情况表〞工作表，单击“常用〞工具栏上的“图表向导〞按钮。或者选择菜单“插入→图表〞命令，翻开如图2.48所示的【图表向导－4步骤之1－图表类型】对话框。图2.48【图表向导－4步骤之1－图表类型】对话框单击“标准类型〞选项卡，在“图表类型〞中选择“柱形图〞，在“子图表类型〞中选择“簇状柱形图〞，如上图所示。单击下方的“按下不放可查看例如〞按钮，可以看到将得到的图表外观的预览。单击“下一步〞按钮，翻开【图表向导－4步骤之2－数据源】对话框，单击“数据区域〞最右边的按钮，然后按住“Ctrl〞键并同时拖动鼠标，分别选择“季度〞、“CRT电视〞和“液晶电视〞列作为数据区域，系列产生在“列〞，如图2.49所示。图2.49【图表向导－4步骤之2－数据源】对话框单击“下一步〞按钮，翻开【图表向导－4步骤之3－图表选项】对话框，选择“标题〞选项卡，在“图表标题〞文本框中输入“销售图表〞，“分类〔*〕轴〞文本框中输入“季度〞，“分类〔Y〕轴〞文本框中输入“数量〞，如图2.50所示；图2.50【图表向导－4步骤之3－图表选项】对话框如果想在图表中看到图表系列所对应的具体数据，可以单击“数据标志〞选项卡，在“数据标签包括〞中选择“值〞，如图2.51所示。图2.51【图表向导－4步骤之3－图表选项】对话框“数据标志〞选项卡单击“下一步〞按钮，翻开【图表向导－4步骤之4－图表位置】对话框，如图2.52所示，选定“作为其中的对象插入〞。图2.52【图表向导－4步骤之4－图表位置】对话框单击“完成〞，结果如图2.53所示。图2.53创立图表后的结果图表的编辑与设置图表创立后，可以根据需要对图表进展编辑与设置。1、移动图表有时候，我们希望移动图表到恰当的位置，让工作表看起来更美观，下面的操作我们将学习如何移动图表。1.单击图表的边框，图表的四角和四边上将出现8个黑色的小正方形。2.接着一直按住鼠标不放，移动鼠标，这时鼠标指针会变成四向箭头和虚线，如图2.54所示，继续移动鼠标，同时图表的位置随着鼠标的移动而改变。图2.54移动图表3.用这样的方法把图表移动到恰当的位置即可。2、调整图表的大小同样地，我们也可能希望调整图表的大小，有这种需求时进展下面的操作即可。1.单击图表的边框，图表的四角和四边上将出现8个黑色的小正方形。2.将鼠标指针移动到*个正方形上，然后拖动它就可以改变图表的大小。3、添加图表数据例18．下面我们将“销售情况表〞中的“等离子〞电视列数据添加到图表中，其操作如下：先选定插入的图表，再单击【图表】菜单中的“添加数据〞命令，翻开【添加数据】对话框，如图2.55所示。图2.55【添加数据】对话框单击“选定区域〞右边的拾取器按钮，在工作表中选取C3:C6区域，〔不含标题！！〕再单击拾取器按钮返回如图2.56所示的【添加数据】对话框。图2.56选定了区域的【添加数据】对话框单击“确定〞按钮，在图表中增加了等离子电视的数据系列。结果如图2.57所示。图2.57添加图表数据后的结果4、删除图表数据在图表中可直接删除数据，工作表中的数据不发生任何变化。例如要删除“等离子电视〞柱，右击柱状图中的“等离子电视〞柱，在翻开的快捷菜单中单击“去除〞命令即可在图标中去除该系列。5、重新设置图表选项充分利用“图表向导〞以及“图表选项〞中提供的控制图表细节的选项，可以得到更符合我们需求的图表。例19．重新设置例17中生成的“销售图表〞，其操作如下：先选定插入的图表，再单击【图表】菜单中的“图表选项〞命令，翻开【图表选项】对话框。单击“标题〞选项卡，在“图表标题〞中将“销售情况表柱形图〞修改为“销售图表〞。单击“网络线〞选项卡，选定“分类〔*〕轴〞下“主要网格线〞选项。单击“图例〞选项卡，选定“位置〞下的“靠上〞选项；单击“数据表〞选项卡，选定“显示数据表〞选项，修改后的效果如图2.58所示。图2.58重新设置图表选项后的结果6、改变图表位置默认情况下，生成的图表是做为其中的对象插入到当前工作表的，如果想象改变图表的位置，把图表放在一个独立的工作表的话，我们可以做如下操作：选中要改变位置的图表，单击“图表〞菜单下的“位置〞菜单命令，翻开如图2.59所示的“图表位置〞对话框。在“图表位置〞中选择“作为新工作表插入〞，单击“确定〞按钮，即可完成图表位置的改变。图2.59【图表位置】对话框7、更换图表的类型当生成图表后，我们有可能希望查看数据在不同图表类型下的显示效果，即更换当前图表的类型，具体操作也是相当简单的。单击图表的边框，选中图表。然后选择菜单“图表〞下的“图表类型〞命令，翻开如图2.60所示的【图表类型】对话框。我们修改图表类型为“条形图〞，子类型默认，一直按住“按下不可放可查看例如〞按钮，即可预览该图表类型得到的效果图。如果觉得满意，单击“确定〞即可完成图表类型的修改。图2.60【图表类型】对话框接下来可以尝试一下更改为其它的图表类型后的显示效果。通过这些操作，大家可以明白：同样的数据在不同的图表类型下，显示的效果可以有很大的差异，而具体选择哪种图表类型，则由我们希望向观众表达的意思来决定。8、删除图表当我们想删除图表时，单击图表的边框选中它，单击“Delete〞键即可删除它。2.6数据透视表的应用数据透视表是一种对大量数据快速汇总和建立穿插列表的交互式表格，它具有能够全面、灵活地对数据进展分析、汇总等功能。只需要改变对应的字段位置，即可得到多种分析结果。创立数据透视表数据透视表的一个显著的特点是可以对数据进展动态的分析，可以通过改变字段的位置，得到想要的分析结果。因此字段的设置是建立数据透视表的关键，我们要学会分析进展统计的目的，然后合理设置字段即可。例20．在员工工资表.*ls的“工资表〞中，增加一列数据“学历〞〔如图2.61所示〕，使用数据透视表按来分析各部门员工的学历情况。图2.61在“工资表〞中增加了“学历〞数据操作方法如下：选定“工资表〞中数据区域任意单元格。单击“数据〞菜单中的“数据透视表和数据透视图〞命令，弹出【数据透视表和数据透视图向导——3步骤之1】对话框，如图2.62所示。图2.62【数据透视表和数据透视图向导——3步骤之1】对话框在“请指定待分析数据的数据源类型〞选项中选定“MicrosoftofficeE*cel数据列表或数据库〞，在“所需创立的报表类型〞选项中选中“数据透视表〞。单击“下一步〞按钮，弹出如图2.63所示的【数据透视表和数据透视图向导——3步骤之2】对话框。图2.63【数据透视表和数据透视图向导——3步骤之2】对话框单击“下一步〞按钮，弹出如图2.64所示的【数据透视表和数据透视图向导——3步骤之3】对话框，在“数据透视表显示位置〞选项中选中“新建工作表〞。图2.64所示的【数据透视表和数据透视图向导——3步骤之3】对话框单击“布局〞按钮，弹出【数据透视表和透视图向导——布局】对话框，用鼠标拖动对话框右侧的“部门〞字段按钮，将其放置到“行〞区域；“学历〞字段放置到“列〞区域；拖动“职务〞字段到“页〞区域；拖动“编号〞字段到“数据区域，汇总方式为“计数〞如图2.65所示〞。如果需要改变数据的汇总方式，可双击数据区域中的“计数项：编号〞，翻开【数据透视表字段】对话框，如图2.66所示，在“汇总方式〞列表框中选择适宜的汇总方式，单击“确定〞按钮，返回布局对话框，可改变数据的汇总方式。图2.65【数据透视表和透视图向导——布局】对话框图2.66【数据透视表字段】对话框单击“确定〞按钮，返回到【数据透视表和数据透视图先到——布局】对话框，单击“完成〞按钮，向导将自动创立新的数据透视表，并在其中显示统计值，如图2.67所示。图2.67数据透视表结果双击该工作表标签，将工作表重命名为“数据透视表〞设置数据透视条件数据透视表生成后，可通过设置数据透视条件来显示的数据。在例20创立的数据透视表中〔如图2.66〕，可做如下设置来显示不同的数据结果。1.例如要分析各部门主管的学历情况，可在如图2.66所示的数据透视表中单击“职务〞右边的B1单元格右侧的下拉按钮，在弹出的选项列表中选择“主管〞，单击“确定〞按钮，数据透视表的结果如图2.68所示。图2.68关于各部门主管的学历分析结果2.例如要显示“销售〞部门的员工的学历情况，可在如果2.66所示的数据透视表中单击“部门〞字段右侧的下拉按钮，在弹出的选项列表中将除了“销售〞以外的选项的复选项取消，如图2.69所示，单击“确定〞按钮完成设置。图2.69显示“销售〞部门的员工的学历情况习题二一、选择题如要关闭工作簿，但不想退出E*cel，可以单击________。A、“文件〞下拉菜单中的“关闭〞命令B、“文件〞下拉菜单中的“退出〞命令C、关闭E*cel窗口的按钮×D、“窗口〞下拉菜单中的“隐藏〞命令在建立工作表时，______可以删除工作表C列。单击列号C，选择“文件〞菜单下的“删除〞单击列号C，选择“编辑〞菜单下的“删除〞单击列号C，选择工具条上的“剪切〞按钮单击列号C，选择“编辑〞菜单下的“去除〞下的“全部〞在E*cel中，单元格A1的容为112,单元格B2的容为593,则在C2中应输入______

，使其显示A1+B2的和。A、=A1+B2

B、"A1+B2"

C、"=A1+B2"

D、=SUM(A1:B2)在E*cel工作簿中，同时选择多个不相邻的工作表，可以在按住______

键的同时依次单击各个工作表的标签。A、Tab

B、Alt

C、Ctrl

D、Esc在E*cel中，“Sheet2!C2〞中的Sheet2表示______

。工作表名B、工作簿名C、单元格名D、公式名在E*cel中，创立一个图表时第一步要_________。选择图表的形式选择图表的类型选择图表存放的位置选定创立图表的数据区在E*cel

工作表中，如要选取假设干个不连续的单元格，可以_______。按住Shift键，依次点击所选单元格按住Ctrl键，依次点击所选单元格按住Alt键，依次点击所选单元格D、按住Tab键，依次点击所选单元格在E*cel的单元格中要以字符方式输入时，应首先输入字符_______。