《Excel-2016数据处理与分析》教学课件07用图表分析数据

模块7用图表分析数据通过本模块内容的学习，熟练使用图表展示分析数据，并掌握动态图表的制作方法。学习目标本模块中知识点虽然较多，但读者不用全部掌握，可将重心用在一些必要的知识点上，包括插入图表、设置图表样式、更改图表类型、添加数据标签和趋势线、设置坐标轴刻度大小、打印图表等。动态图表属于中高级操作，读者可以在掌握静态图表和相关函数后学习。学习重点7.1

创建图表根据数据创建图表，使得图表与数据相结合，更直接明白表达数据。结合图表也能更清晰地分析数据。7.1.1

图表的类型Excel2016中提供了多种图表类型供用户使用，不同类型的图表对数据分析的侧重不同，用户可根据数据分析的具体需求选用合适的图表，下面简要地介绍常用图表类型的特点。（1）柱形图。柱形图用于展示在一定范围内数据的变化及数据元素之间的比较。可用于跨若干类别比较数据元素之间的大小，当类别的顺序并不重要时，可以选用柱形图，如图7-1所示。（2）折线图。折线图显示的是一段时间内的连续数据，侧重于展现数据在某一时间段内的变化趋势，如图7-2所示。7.1.1图表的类型7.1.1图表的类型（3）饼图。饼图显示一个数据系列中各项的大小与各项总和的比例，强调个体与总体的占比关系，如图7-3所示。（4）条形图。条形图显示持续时间内各个项目的比较情况。在条形图中，通常沿垂直坐标轴组织类别，沿水平坐标轴组织值，如图7-4所示。7.1.1图表的类型（5）面积图。面积图可用于绘制随时间发生的变化量，用于引起人们对总值趋势的关注。通过显示所绘制的值的总和，面积图还可以显示部分与整体的关系，如图7-5所示。（6）XY散点图。散点图通常用于显示和比较数值，如科学数据、统计数据和工程数据，如图7-6所示。7.1.1图表的类型（7）股价图。股价图可以显示股价的波动。必须按正确的顺序组织数据才能创建股价图，如图7-7所示。（8）雷达图。雷达图适用于从多个维度分析一个或多个对象，如图7-8所示。7.1.1图表的类型（9）树状图。树状图以一种简便方式提供工作表中的数据，以比较不同级别的分类的分层视图，如图7-9所示。（10）旭日图。旭日图适合显示层次结构数据，并可以在分层结构中存在空（空白）单元格时绘制。不带任何分层数据（一级类别）旭日图类似于圆环图，如图7-10所示。7.1.1图表的类型（11）箱形图。箱形图适用于显示一组数据的分散情况，显示数据的分布到四分位值，四分位值可以反映出中间50%数据的离散程度，数值越小说明中间数据越集中；数值越大说明中间数据越分散，如图7-11所示。（12）瀑布图。瀑布图显示运行财务数据的总添加或减去的值。列是彩色编码，因此可以快速辨别正从负数，如图7-12所示。7.1.1图表的类型7.1.1图表的类型（13）组合图。组合图将两种或更多图表类型组合在一起，以便让数据更容易被理解，特别是数据变化范围较大时。由于采用了次坐标轴，这种图表更容易看懂，如图7-13所示。7.1.2

用三种方式创建图表知晓图表类型后，就可以根据实际需求自动或手动创建图表。其中，自动插入是指让Excel自动根据用户选择的数据自动匹配图表类型，有两种常用方法：推荐功能和分析工具库功能。手动插入是完全靠我们自己选择图表类型进行创建的。【实例7-1】创建销量图表老李将最近几天的饮料销量样本进行了收集整理，现在他打算将这几种饮料的销量情况用图表进行展示对比，为后面饮料的采购投入提供支持。1.使用推荐功能插入图表进行数据分析时，若不确定或不知晓哪种类型图表合适，则使用“推荐的图表”功能，具体操作如下。步骤1打开素材文件中的“素材\模块7\7.1\饮料销量.xlsx”文件，选择A1：B7单元格区域，单击“插入”选项卡“图表”组中的“推荐的图表”按钮，打开“插入图表”对话框，如图7-14所示。步骤2选择“簇状条形图”选项，单击“确定”按钮，如图7-15所示。7.1.2用三种方式创建图表7.1.2用三种方式创建图表步骤3Excel自动插入选择的图表，直观展示对比各类饮料的销量，如图7-16所示。7.1.2用三种方式创建图表2.使用图表分析工具库插入图表从2013版本开始，Excel中添加了分析工具库功能，协助用户快速提高处理分析数据的效率，同时更加便于用户在移动设备中创建图表。其使用方法为：选择数据区域，单击浮现的“快速分析”按钮，在出现的面板中单击“图表”选项卡，选择需要的图表选项，快速创建合适的图表，如图7-17所示。7.1.2用三种方式创建图表3.手动插入图表若清楚自己想要的图表类型，或明确了展示分析数据的图表类型，手动插入需要的图表更加合适，具体操作方法如下。步骤1选择A1：B7单元格区域，单击“插入”选项卡“图表”组中的“推荐的图表”按钮，如图7-18所示。步骤2打开“插入图表”对话框，单击“所有图表”选项卡，选择“条形图”选项，选择“簇状条形图”选项，单击“确定”按钮，如图7-19所示。7.1.2用三种方式创建图表7.1.2用三种方式创建图表步骤3通过以上操作在表格中插入条形图，如图7-20所示。实现步骤视频7.1.2用三种方式创建图表支招：除了通过“插入图表”对话框手动插入图表外，还可以通过功能选项插入，方法为：选择数据区域后单击“插入”选项卡“图表”组中的图表类型下拉按钮，在弹出的下拉列表中选择相应的图表选项，如图7-21所示。7.2

编辑图表图表是Excel重要的数据分析工具，它具有很好的视觉效果，使用图表能够将工作表中枯燥的数据显示得更清楚，更易于理解，从而使分析的数据更具有说服力。图表还具有帮助分析数据、查看数据的差异、分析走势等功能。7.2.1

编辑销售图表标题在创建图表时，系统会自动识别数据源，为图表设置标题，如果没有识别到合适的标题，Excel会在图表标题的位置显示“图表标题”字样。无论是哪种情况，绝大部分都需要我们手动编辑图表，也就是重命名图表标题。方法为：将鼠标指针移动到图表标题上，单击进入其编辑状态，删除原有的标题后输入新图表标题，如“季度销售”，单击表格中任一其他位置退出图表标题编辑状态，完成操作，如图7-22所示。7.2.1编辑销售图表标题7.2.2

更改销售图表类型Excel中图表类型多样，当我们选择的图表不能合适地体现出想要展示的数据时，可更改图表类型。【实例7-2】更改销售图表类型由于之前的销售图表类型不能很好地展示销售数据，老王打算将原有的折线类型图表更改为柱形图，他的操作步骤如下。步骤1打开素材文件中的“素材\模块7\7.2\季度销售1.xlsx”文件，单击图表，单击“图表工具-设计”选项卡“类型”组中的“更改图表类型”按钮，打开“更改图表类型”对话框，如图7-23所示。7.2.2更改销售图表类型7.2.2更改销售图表类型步骤2选择“簇状柱形图”选项，单击“确定”按钮，如图7-24所示。步骤3通过以上操作，自动将图表更改为簇状柱形图，如图7-25所示。实现步骤视频7.2.3

设置销售图表布局在创建图表时，并不是所有的图表元素都会在图表中显示出来，Excel采用的是默认的系统布局，用户如果需更改图表布局，可通过Excel中提供的“快速布局”功能为图表重新布局排列显示图表元素。【实例7-3】设置销售图表布局公司文员小周制作了一份季度销售报表准备递交给老板，可他总觉得数据不够直观，不能直观展示出各类数据，熟悉Excel操作的他决定使用“快速调整布局”功能快速将表格数据展现在图表中，他的操作步骤如下。步骤1打开素材文件中的“素材\模块7\7.2\季度销售.xlsx”文件，选择图表，单击“图表工具-设计”选项卡“图表布局”组中的“快速布局”下拉按钮，在弹出的下拉列表中选择“布局2”选项，如图7-26所示。7.2.3设置销售图表布局7.2.3设置销售图表布局步骤2图表中的元素布局立刻发生了变化，数据系列自动添加了表格数据并将纵向坐标轴隐藏，如图7-27所示。实现步骤视频7.2.4

更改图例位置默认情况下，图例会显示在图表上方标题之下或出现在图表右侧空白区域，若觉得位置不合适，可对其进行更改。【实例7-4】更改销售图表图例位置在销售图表中图例默认放置在右侧，小林为了能让图表系列显示更加宽松，他特意将图例的位置移到图表下方，他的操作步骤如下。步骤1打开素材文件中的“素材\模块7\7.2\月份销售.xlsx”文件，在图例上右击，在弹出的快捷菜单中选择“设置图例格式”命令，打开“设置图例格式”窗格，如图7-28所示。步骤2切换到“图例选项”选项卡，在“图例选项”展开选项中选中图例放置位置单选按钮，如选中“靠下”单选按钮，如图7-29所示。7.2.4更改图例位置7.2.4更改图例位置步骤3通过以上操作，图表“销售走势分析”图例位置已经更改，效果如图7-30所示。实现步骤视频7.2.5

设置销售图表坐标轴刻度大小设置图表坐标轴刻度大小，包括很多方面，如刻度大小、单位大小、单位显示方式等。其中最主要的是刻度大小和单位大小，也就是设置坐标轴的最大值和最大单位值。【实例7-5】设置销售图表坐标轴刻度大小小汤需要设置内部薪酬分析图表的坐标轴刻度，他在“设置坐标轴格式”窗格中对“边界”“单位”的刻度值进行了一番调整，使得图表以最合适的方式绘制展示数据，他的操作方式如下。步骤1打开素材文件中的“素材\模块7\7.2\内部薪酬分析图表.xlsx”文件，在垂直（值）轴上右击，在弹出的快捷菜单中选择“设置坐标轴格式”命令，打开“设置坐标轴格式”窗格，如图7-31所示。步骤2设置“最大值”为“45000”，设置“大”为“3000”，如图7-32所示。7.2.5设置销售图表坐标轴刻度大小7.2.5设置销售图表坐标轴刻度大小步骤3通过以上操作，坐标轴刻度大小发生了变化，效果如图7-33所示。实现步骤视频7.2.6

添加销售数据标签数据标签是图表元素之一，用于展示数据系列的数值，让用户直接“读数”，与数据系列相互呼应，让数据更加直观地展示。但在手动创建的图表中不会自动添加数据标签（使用推荐的功能和分析数据库工具创建的图表有时会自带数据标签），需要我们手动添加。【实例7-6】添加销售数据标签公司助理小刘根据公司每月的销售额制作了一份月销售图，可是不能直接展示各月份的销量值，于是他手动添加了数据标签。1.通过快捷菜单添加数据标签打开素材文件中的“素材\模块7\7.2\月份销售.xlsx”文件，选择数据系列并右击，在弹出的快捷菜单中选择“添加数据标签”命令，如图7-34所示。7.2.6添加销售数据标签实现步骤视频7.2.6添加销售数据标签支招：要添加带有形状标注的数据标签，可在快捷菜单中选择“添加数据标签”→“添加数据标注”命令，如图7-35所示。7.2.6添加销售数据标签2.通过下拉选项添加数据标签步骤1打开素材文件中的“素材\模块7\7.2\月份销售.xlsx”文件，选择数据系列，单击“图表工具-设计”选项卡中的“添加图表元素”下拉按钮，在弹出的下拉列表中选择“数据标签”→“数据标签外”选项，如图7-36所示。步骤2在数据系列的顶端自动添加对应的数据标签，如图7-37所示。支招：要删除数据标签，只需将其选中，然后按Delete键。如果要删除数据标签中的其中一个，可在要删除的数据标签上连续单击两次（特别注意不是双击）将其选中，然后按Delete键。7.2.6添加销售数据标签7.2.7

添加走势趋势线趋势线是反映数据波动的曲线，通过趋势线的方向可以明确地看出数据的发展趋势，适用于问题的预测研究。在Excel中，趋势线共有6种类型，分别如下。·线性趋势线：适用于为简单线性数据集创建最佳拟合直线。若数据点构成的图案外观类似直线，则表明数据是线性的。线性趋势线通常表示事物是以恒定速率增加或减少的。·对数趋势线：最佳拟合曲线，当数据变化率快速增加或降低，然后达到稳定的情况时，这种趋势线非常有用。对数趋势线可以使用负值和正值。·多项式趋势线：适用于数据波动的情况。·乘幂趋势线：此趋势线显示一条曲线，适用于以特定速度增加的度量值进行比较的数据集。若数据中含有零值或负值，则不能创建乘幂趋势线。7.2.7添加走势趋势线·指数趋势线：此趋势线显示一条曲线，适用于以不断增加的速率上升或下降时的数据值。若数据中含有零值或负值，则不能创建指数趋势线。·移动平均趋势线：平滑处理数据的波动以更清楚地显示图案或趋势。【实例7-7】添加运费成本走势趋势线经过一段时间后，公司领导需要了解运费成本的走势情况，以决定产品价格是否需要调整。助理小张明白了领导的意图后，在图表中添加了一条线性趋势线。1.一般添加趋势线步骤1打开素材文件中的“素材\模块7\7.2\邮递运费.xlsx”文件，选中图表，单击“图表工具-设计”选项卡中的“添加图表元素”下拉按钮，在弹出的下拉列表中选择“趋势线”→“线性”选项，如图7-38所示。步骤2Excel在“运费分析”图表中自动添加一条线性趋势线，展示邮费成本整体呈现下降趋势，如图7-39所示。7.2.7添加走势趋势线7.2.7添加走势趋势线2.通过快捷菜单添加趋势线步骤1打开素材文件中的“素材\模块7\7.2\邮递运费.xlsx”文件，在数据系列上右击，在弹出的快捷菜单中选择“设置趋势线格式”命令，打开“设置趋势线格式”窗格，如图7-40所示。步骤2选中“线性”单选按钮，添加线性趋势线，如图7-41所示。7.2.7添加走势趋势线步骤3图表中添加了一条展示5月16日—5月31日的邮费成本趋势线，可知邮费成本整体呈下降趋势，如图7-42所示。实现步骤视频7.2.7添加走势趋势线支招：若要让添加的趋势线更加贴合数据系列走势，可在打开的“设置趋势线格式”窗格中选中“多项式”单选按钮，在其后对应的数字框中输入参数，如图7-43所示。7.3

美化图表创建的图表都是默认的样式，而这些样式往往比较普通。要让图表美观，需要对图表进行外观样式和配色美化。7.3.1

应用图表样式Excel中自带了10多种图表样式，完全可以直接应用这些样式快速美化图表。【实例7-8】为运费分析图表应用样式财务部小谭制作了一份运费分析图表，可主管总觉得图表看起来不太美观，让小谭进行修改。小谭直接应用图表样式，立即得到了主管的肯定，他的操作步骤如下。步骤1打开素材文件中的“素材\模块7\7.3\邮递运费.xlsx”文件，选择“运费分析”图表，如图7-44所示。7.3.1应用图表样式步骤2单击“图表工具-设计”选项卡，在“图表样式”组中选择“样式4”，如图7-45所示。7.3.1应用图表样式步骤3运费分析图表应用新的图表样式，如图7-46所示。实现步骤视频7.3.2

更改图表颜色图表颜色样式是用颜色使得图表丰富，重点突出。在单击图表后，单击“格式”选项卡的“形状填充”下拉按钮，在弹出的下拉列表中可以对图表颜色样式进行更改。更改图表颜色分为两种：更改整个图表颜色和更改单个数据系列，两种操作都非常简单。【实例7-9】为运费分析更改图表颜色样式公司领导要求将5月25日的运费数据系列做明显标识，让其突出显示，以引起公司员工的注意。小刘用时不到1分钟就完成了，他的操作方法如下。步骤1打开素材文件中的“素材\模块7\7.3\邮递运费.xlsx”文件，在图表中连续单击“5月25日”对应的运费数据系列将其单独选中，如图7-47所示。7.3.2更改图表颜色7.3.2更改图表颜色步骤2单击“图表工具-格式”选项卡“形状样式”组中的“形状填充”下拉按钮，在弹出的拾色器中选择“橙色”色块选项，如图7-48所示。实现步骤视频7.3.2更改图表颜色支招：要更改图表整个数据系列的颜色，可直接单击“图表工具-设计”选项卡中的“更改颜色”下拉按钮，在弹出的拾色器中选择相应的颜色选项，如图7-49所示。7.3.3

移动图表在处理数据时浮于工作表上方的图表可能会妨碍数据分析的操作，此时可以将图表移到其他位置，以方便的工作。对于移动图表位置有两种移动方法，一是移动到当前表格中，另一种是将图表移动到新建的工作表中。对于前者，只需选择图表，按住鼠标左键并拖动即可，如图7-50所示。7.3.3移动图表7.3.3移动图表对于后者，需要借助对话框，具体方法为：选择需要移动的图表，单击“图表工具-设计”选项卡，单击“移动图表”按钮，打开“移动图表”对话框后，选中“新工作表”单选按钮，单击“确定”按钮，图表以全窗口的方式显示，如图7-51所示。支招：若是要将图表移到已有的表格中并以正常的大小或状态显示，可直接通过剪切、粘贴的方式快速实现。7.4

创建动态图表默认情况下，一张静态图表只能绘制展示一系列数据。对于单独系列较多的数据，若用静态图表逐一绘制展示，将会产生N张图表，表格完全变成图表堆，完全不符合商务图表的标准，这时用动态图表则可以将N张图表变成一张图表。下面介绍两种常见的动态图表创建方法。7.4.1

利用VLOOKUP函数创建动态图表VLOOKUP函数能根据查找值精准查找匹配数据，与数据验证功能联合应用，能轻松制作动态数据区域，接着插入图表就能创建出动态图表。支招：在表格中制作动态图表最关键的是让图表数据源有变化。根据这一点可以借用函数与数据验证结合（本例中就是VLOOKUP函数与数据验证）；函数与控件结合，如组合框控件、单选按钮控件、复选框控件与INDEX函数结合（与VLOOKUP函数和数据验证的结合原理基本相同，有兴趣的读者可以自行研究）；图表与筛选功能结合（利用筛选让数据隐藏从而改变图表中数据的绘制显示）等。【实例7-10】利用VLOOKUP函数创建动态销售图表在季度销售表中要将每一产品1~4月的销量用图表直观展示，静态图表需要4张，既不利于数据分析，又不能体现专业水准。于是，小李决定创建一张动态图表，操作步骤如下。7.4.1

利用VLOOKUP函数创建动态图表步骤1打开素材文件中的“素材\模块7\7.4\季度销售表.xlxs”文件，选中A10单元格，单击“数据”选项卡，单击“数据工具”组中的“数据验证”按钮，打开“数据验证”对话框，如图7-52所示。步骤2选择“允许”选项为“序列”，设置“来源”参数为“=$A$3：$A$6”，单击“确定”按钮，如图7-53所示。7.4.1利用VLOOKUP函数创建动态图表步骤3选择B10：D10单元格区域，在编辑栏中输入函数“=VLOOKUP（$A$10，$A$2：$E$6，2，0）”，按Ctrl+Enter组合键确认，如图7-54所示。步骤4选中C10单元格，在编辑栏中将VLOOKUP的col_index_num更改为“3”，按Ctrl+Enter组合键确认，如图7-55所示。7.4.1利用VLOOKUP函数创建动态图表步骤5选中D10单元格，在编辑栏中将VLOOKUP的col_index_num更改为“4”，按Ctrl+Enter组合键确认，如图7-56所示。步骤6选中A10单元格，单击右侧激活的下拉按钮，在弹出的下拉选项中选择产品选项，如图7-57所示。7.4.1利用VLOOKUP函数创建动态图表步骤7选择A9：D10单元格区域，单击“插入”选项卡中的“插入柱形图或条形图”下拉按钮，选择“簇状柱形图”选项，如图7-58所示。步骤8将图表移到合适位置，如图7-59所示。实现步骤视频7.4.2

利用控件创建动态图表利用控件创建动态图表的原理与用VLOOKUP函数制作动态图表的原理基本相同。最大的不同点是控件替换数据验证、INDEX函数替换VLOOKUP函数，操作方法大致相同。【实例7-11】利用控件创建部门考勤动态图表为了方便查看公司内部员工的出勤状况，人事部小江利用控件创建一张动态图表，将所有员工的出勤数据尽收其中，领导或同事只需使用控件就能立即绘制显示对应员工的出勤图表，他的具体操作步骤如下。步骤1打开素材文件中的“素材\模块7\7.4\考勤动态报表1.xlxs”文件，在“开发工具”选项卡下单击“控件”组中的“插入”下拉按钮，在弹出的下拉列表中选择“表单控件”选项组中的“组合框”选项，如图7-60所示。步骤2绘制组合框并在其上右击，在弹出的快捷菜单中选择“设置控件格式”命令，打开“设置控件格式”对话框，如图7-61所示。7.4.2利用控件创建动态图表7.4.2利用控件创建动态图表步骤3设置“数据源区域”参数为“$A$2：$A$13”，设置“单元格链接”参数为“$A$14”，单击“确定”按钮，如图7-62所示。步骤4选择B15：E15单元格区域，在编辑框中输入函数“=INDEX（A2：A13，$A$14，）”，按Ctrl+Enter组合键确认，如图7-63所示。7.4.2利用控件创建动态图表7.4.2利用控件创建动态图表步骤5单击组合框控件下拉按钮，在弹出的下拉列表中选择“赵菲菲”选项，如图7-64所示。步骤6选择B15：E15单元格区域，单击“插入柱形图或条形图”下拉按钮，在弹出的下拉列表中选择“簇状柱形图”选项，如图7-65所示。7.4.2利用控件创建动态图表步骤7将图表移到合适位置，如图7-66所示。步骤8重复步骤2操作切换控件的选项，图表切换绘制显示其他员工出勤数据，如图7-67所示。提示：在绘制组合框控件时要注意两点：一是必须是表单组合框控件，不能是ActiveX控件；二是在绘制组合框控件时建议不用单击绘制，因为单击绘制的组合框控件按钮大小随机产生、不易控制，最好用鼠标绘制。7.4.2利用控件创建动态图表实现步骤视频7.5

打印图表在工作中，需要将制作好的电子图表打印出来，有时需要打印一张图表，有时需要打印多张图表。7.5.1

打印单个图表默认状态下进行打印操作，表格和图表都会被打印，但要单独打印图表，需要采用一个选择技巧：选择要打印的单个图表，然后按Ctrl+P组合键切换到“打印”界面中，单击“打印”按钮即可，如图7-68所示。7.5.2黑白打印图表默认情况下，表格打印或图表打印都会以彩色的方式打印。要黑白打印图表，除了在打印前将图表选中外，还需要进行黑白打印设置。方法为：选中图表，单击“页面布局”选项卡“页面设置”组中的对话框启动器按钮，打开“页面设置”对话框，单击“图表”选项卡，选中“按黑白方式”复选框，单击“确定”按钮，如图7-69所示。7.5.2黑白打印图表7.6

综合实例前面详细讲解了图表从创建到美化的每一个操作步骤，下面以销售分析表为例，将上面所提到的知识点综合在一起，详细讲解在一张表格中如何完整地创建一张合格的图表，具体操作步骤如下。7.6综合实例步骤1打开素材文件中的“素材\模块7\综合实例\销量分析4.xlsx”文件。选择A2：B20单元格区域，单击“插入”选项卡，单击“插入柱形图或条形图”下拉按钮，在弹出的下拉列表中选择“簇状柱形图”选项，如图7-70所示。步骤2将鼠标指针移动到图表区，按住左键不放拖动图表，移动到空白区域后，释放左键，如图7-71所示。步骤3切换到“图表工具-设计”选项卡，单击“添加图表元素”下拉按钮，在弹出的下拉列表中选择“坐标轴”→“更多轴选项”选项，打开“设置坐标轴格式”窗格，如图7-72所示。步骤4选中“文本坐标轴”单选按钮，展开“数字”选项，如图7-73所示。步骤5在“类型”下拉列表框中选择“3/14”选项，关闭“设置坐标轴格式”任务窗格，如图7-74所示。7.6综合实例7.6综合实例7.6综合实例7.6综合实例步骤6选中图表后，将鼠标指针移动到图表右