Excel自学三部曲_Part3：Excel工作场景实战（三）

---

三、基础图表、透视图表制作与分析

1. 条形图

（1）给逾期金额加上条形图

  领导表示能不能给“逾期金额”也加上条形图，并且他想看一下各个城市的逾期发展趋势，先来做逾期金额的条形图。

• 首先选中数据，点击“条件格式”，选择“数据条-渐变填充中的橙色数据条”，这样条形图就做好了
  
  
  • 条件格式
    • 可以在单元格中插入条形、颜色、图标等条件格式
    • 并且根据数值设置不同的运算规则和显示格式
    • 选择有条件格式的区域可以编辑或删除规则
  • 条件格式的显示比例
    • 同一条件格式下的单元格会按相同的比例展示图形
    • 量级不同的数据，要分开设置条件格式
      
      

（2）各个城市的逾期发展趋势（迷你图）

  搞定了逾期金额的条形图，再来做下8月上中下旬的逾期发展趋势

• 选中“广州”的逾期金额，点击“插入-迷你图-折线”，接着点击还空着的“8月上旬逾期环比单元格”，作为放置迷你图的位置，接着下拉填充柄就批量完成了每个城市“上中下旬的逾期金额折线图”
  
  
• 可以适当调整下迷你图，点击折线图，进入迷你图编辑，先勾选高点-用来突出显示需要注意的风险点；接着点击迷你图的颜色，设置为条形图相同的金色；然后点击“标记颜色”，从红色改为橙色，这样统一配色后看起来会更舒服
  
  • 迷你图
    • 选择单行或单列数据即可插入迷你图
    • 迷你图支持折线、柱形、盈亏三种图形
    • 标记点、颜色、坐标轴都可进行编辑
    • 需要右键迷你图在选项中才可以清除
      
      

2. 柱状图

  在完成了逾期金额的条形图之后，领导又提出能不能给他做一些更丰富的对比分析，搞点多维柱状图、饼图、散点图等等，帮助他进行更加全面的业务决策。
这里需要注意的是每个迷你图只能放一行或一列数据，提示里也写的很清楚，如果我们强行选择两行或两列数据，就会提示你位置引用或数据区域无效，此时，如果我们想同时展现每个城市上中下旬的逾期金额的柱状图就只能插入图表了

（1）同时展示每个城市上中下旬的逾期金额

• 选中“城市”和“8月上中下旬逾期数据”，插入“簇状柱形图”
  
  
• 点击图形所在的绘图区，右键设置“绘图区域格式”，点击想要修改的元素，可以在右侧修改元素和边框，还可以修改柱形之间的间隙（注：这样修改的灵活性很高，但是效率确很低，如果想要制作更加美观的图表，还是使用更加专业的BI 工具比较好）
  
  
  
  • 插入图表
    • 先选择数据区域，然后选择图表类型
    • 图表类型和数据区域可以重新选择
    • 右键图表，可以设置各种绘图区格式
    • 图表右侧的加号可以增加并设置标签、坐标轴、辅助线等元素
    • 图表右侧的笔刷可以快速修改图表样式和颜色
    • 图表右侧的筛选可以筛选图表中的数据
      
      

a. 格式设置：第一个笔刷-图表元素

• 点击图表右侧第一个加号图标，可以勾选各类元素，当然每个元素右侧都可以快速进行一些常用的设置，例如，增加或取消网格线，点击更多选项还能具体设置细节
  
  
• 例如，想要调整图例的位置，除了直接拖拽，还可以在右侧调整图例的位置，并且选择是否与图表重叠；这里选中右上，并且取消勾选“不与图表重叠”，这样一个悬浮在图表上浮的图例就做好了，更符合标准的图例展现形式
  
  

b. 格式设置：第二个笔刷-图表样式/颜色

• 除了通过右键在格式里修改，点击图表右侧第二个笔刷图标，也可以快速修改图表的样式和各种配色，这里将颜色选为第4行
  
  

c. 格式设置：第二个笔刷-筛选

• 除了“增加元素”、“调整格式”，右侧的第三个图表，点击后即可对数据进行筛选，取消勾选后应用，就可以剔除一些不想看的数据，再次勾选应用就能添加回来
  
• 做好图表后，可以取个标题，双击图表标题，命名为“各城市8月逾期金额”，添加的坐标轴标题也能用相同的方式修改；而图例和轴标题则需要修改原有的列名和数据
  
  
  
  至此，第一个多维柱状图就做好了，接下来做一下“各城市8月份逾期总额的占比”，占比一般用饼图或环形图。

3. 饼图/环形图

（1）各城市8月份逾期总额的占比

• 选中数据，点击插入，在所有图表里面选择“饼图”，再选“环形图”，可以看到“上中下旬”分为了3个环，而我们需要的是8月所有逾期金额的总和在一个环里，这个时候要怎么处理呢？

• 因为我们原本选择的数据就是3列，因此Excel会对这3列单独作图，如果要让这3个环合并，首先要让这3列数据合并，因为图表只是数据的一种表达形式，当你想表达不一样的数据，要做的肯定是先修改数据，再修改图表，这里具体怎么做呢？
  其实只需要增加一个辅助列，等于上中下旬的逾期金额总和，命个名，让图表重新选择这个区域的数据，如下图：
  

• 然后勾选“数据标签”，不过现在的标签都在环形内部，而Excel的环形图数据标签是不支持直接设置到外部的，需要一个个手动拖拽
  

• 这里将图表类型换成“饼图”，标签就能设置了
  

• 如果非要想看“环形图”，可以手动插入一个圆形，然后去除边框再调成白色，如果觉得不够圆可以右键设置格式，直接输入高度和宽度，再调整一下白色圆形图的位置，环形图就做好了
  
  

• 不过，还有一个问题，就是此时的数据标签显示的是数字，直接显示城市才更符合阅读逻辑，毕竟就是要看哪个城市占比高，同时还可以省下图例；这里选择“数据标签-更多选项”，将标签中的“类别名称”勾选上，取消勾选“值”，位置标签选择“数据标签外”，取消勾选“图例”，再调一下空白圆的大小
  
  
  

• 最后，饼图和环形图一定要加上排序，现在没有排序，其实很难一眼看出占比前5的城市到底是哪几个，这种混乱的图表布局极大地降低了信息传递的有效性，是非常不专业的表现；而Excel想要让图表排序，首先要对数据进行排序，移动图表到左侧，选择数据区域，如下图：
  

• 按照“逾期总和”降序排序，这样整个图表就清晰多了，最后改一下配色，这样环形图就做好了
  

  • 辅助列
    • 想修改图表要先修改数据
    • 可以通过增加辅助列专门用于作图
  • 环形图标签设置
    • 环形图不支持在外部显示标签
    • 需要换成饼图后调整标签位置
    • 然后插入白色圆形做出环形图
  • 图表排序
    • 要对图表排序，先对数据进行排序
    • 饼图和环形图排序后才更加清晰

• 至此，环形图就做好了，当然，做完了环形图之后，不知道大家觉不觉得，饼图和环形图后续占比较小的城市区分起来不够清晰，这还只有十几个城市，如果对象增加到几十个，饼图的扇区就会越来越小，根本看不清楚，像这种多对象占比分析，我们一般会用树状图进行展现，不过，由于Excel里对树状图的调整不够智能，一般会用Tableau呈现。这里已经完成了占比和绝对值的分析，作为领导，只知道这两个结果肯定不够，我们得知道数据的发展趋势才能做出更加准确的判断，如果要看发展趋势，那基于日期的折线图肯定是最直观的。
  
  

4. 折线图

（1）这个月整体的成交和逾期趋势（数据透视表）

• 不过，当前数据只聚合到“城市”，并没有到每天，如果要算每天的成交额或逾期金额，即灵活的聚合或重构表格，只需要用Excel的数据透视功能即可

• 任意选中一个需要进行数据透视区域的单元格，然后点击插入，直接点击数据透视表上方的图标，点击确定，就可以为自动选中的表格区域进行数据透视了
  
  

• 此时透视表右侧有很多字段供我们选择，包括隐藏字段，如果不想他们出现，可以修改一下引用区域，点击数据透视表分析功能栏里的“更改数据源”，然后将区域的边界从N列改为H列就好了，当然也可以直接删除现有区域，然后手动进行选择
  
  

• 同时这里还有个“数据刷新”功能，当引用区域的数据产生变化时别忘了点击刷新，这样才能同步区域内的数据变化
  

• 现在我们已经有了表格里本身有的字段，如果想增加字段，除了直接在原数据士进行修改，然后刷新同步；还可以先勾选一个字段，然后在计算模块，点击“字段、项目和集”，进行自定义添加，例如添加一个字段“逾期率”，公式=逾期金额/成交额，点击添加，就可以看到逾期率已经新增到了字段列表中
  
  
  

• 再添加一个估算利率，公式=应收利息/成交额
  
  

• 简单来说，只需要将字段拖拽到下方的四个区域，就可以根据拖拽的字段重新构建出一张新表，例如：将“城市”拖拽到行，“成交额”拖到值，就能让数据根据“城市”对“成交额”进行聚合运算，还能继续添加“应收利息”
  
  
  

• 除了拖拽聚合数据，还可以对数据进行筛选，只需要把字段拖到筛选区域即可，例如：将“战区”拖到筛选，左上角就出现了筛选器，展开后就可以对战区进行选择，可多选；如果字段已经存在于其他区域，例如“城市”已经在行，再拖“城市”到筛选，那么城市就会被直接移动到筛选区域，从透视表中消失
  
  

• 此时，可以右键字段列表的“城市”字段，然后添加为切片器，这样就能将区域中已有的字段设置为筛选器了，最后重新将“城市”拖为行，点击切片器中的“城市”，就能在保留字段的同时实现筛选了，切片器同样支持多选，只要点击上方的多选图标即可；并且点击切片器之后，在上方的“切片器”功能栏也能够修改样式，进行各种设置。这样我们就掌握了两种在透视表中筛选数据的操作。
  
  
  

• 讲完了筛选，我们将“战区”拖到列，可以发现透视表自带各种层级展开和汇总，但有的时候我们并不需要这些汇总，这个时候就可以点击“设计”，点击“分类汇总”，进行展示上的选择，点击“不显示分类汇总”就会取消汇总
  
  

• 接着点击“总计”一样可以设置，我们这里直接删除，然后在报表布局里选择以“表格形式”显示，并“重复所有项目标签”，
  
  
  

• 接着在透视表样式里点击展开，选择最左上角的样式，删除前两行空行，就可以得到一个跟平常一样清晰直观的表格了；如果再次启用“总计”，就能得到一张非常清晰的数据报表
  
  
  

  • 插入数据透视表
    • 选择数据区域可以对此区域的字段进行透视
    • 透视的数据区域可以重新选择
    • 点击刷新才可以同步区域的数据变化
  • 在透视表中增加字段
    • 选择字段项目和集
    • 可以命名新字段并写入公式
  • 唤出透视表字段列表
    • 重新点击透视表区域
    • 或右键透视表选择显示字段列表
  • 透视表的拖拽区域
    • 行列可以分别对数值进行区分
    • 数值可以选择求和、平均、计数等聚合方式
    • 字段上下顺序会对应字段的展开层级顺序
    • 点击或取消勾选都可以删除字段
  • 透视表的筛选
    • 区域中还没有的字段可以直接拖到筛选区域
    • 已有的字段可以在字段列表添加为切片器
    • 切片器支持多选，还可以修改样式
  • 透视表的布局
    • 点击设计可以对透视表的展开、汇总、表格格式进行编辑
    • 删除汇总、重复标签、选择最左上角的样式可以得到于净清晰的表格
  • 透视表的可视化
    • 透视表可以直接插入条件格式和迷你图
    • 也可以直接插入图表
      
      

（2）这个月整体的成交和逾期趋势（折线图）

• 此时如果要看“这个月整体的成交和逾期趋势”，那么数据准备上就要取消“战区”和“城市”的勾选，然后将“逾期金额”拖到“值”，这样就完成了数据准备
• 那接下来要怎么作图呢？
  透视表和正常表格一样，可以插入“条件格式”、“迷你图”和“图表”，不同的是“条件格式”和“迷你图”都是根据你选中的区域作图，而插入“图表”不用选择区域，直接点击透视表再点插入图表，就会根据整个透视表的数据出图，然后可以用筛选器修改数据的同时，控制图表的展示
  
  
  
  
• 这里我们可以看到，“逾期金额”因为很小，跟“成交金额”共用一个数据轴展示后，趋势就不明显了，可以选中“逾期金额”的折线，然后右键设置格式，点击“次坐标轴”，就可以将两个数值分别在左右两个数轴上展示了
  
  
• 还可以右键“更改图表类型”，将“逾期金额”的类型改为柱形图，这样就完成了组合图的制作。
  
  
  
• 如果想看各个“战区”的趋势，就是将战区添加到行，不过添加后数据是先日期再战区，放大一下图表，图上的分区也密密麻麻很奇怪；这里我们想看的应该是每个战区每天的趋势，所以字段的展开顺序要换一下，将“战区”拖到“日期”上方，此时数据和图表都按照先战区再日期的方式展开了；不过还是不太对劲，各个战区的折线是连到一起的，如果可以分颜色展示，各个战区才会更清晰，那怎么分呢？
  
  
• 不难发现，透视表的“行和列”分别对应图表的“轴和图例”，如果我们想让“战区”以颜色图例的方式区分折线，只需要将“战区”移动到“图例或列”的区域即可；同时Excel中还可以添加趋势线，勾选“趋势线”，然后选择要添加趋势线的折线，这里选最上方的东部战区，选好后点击“更多选项”还能调整趋势线的类型并显示趋势线的公式
  
  
  
  
  
• 当然这里同时看多个战区的“成交金额”和“逾期金额”还是太复杂了，还是分来看比较好，按住ctrl后拖拽复制当前的透视表，然后移出新表Sheet1(2)中的“成交金额”，旧表Sheet1中的“逾期金额”，接着重新添加趋势线，并显示公式
  
  
  
• 然后分别复制粘贴到之前的工作表中，并调整大小和颜色，最后添加标题，这样我们的业务发展趋势就做好了
  
  • 双轴和组合图
    • 多个数值可以设置为组合图
    • 组合图可以增加一个次轴展现数据
    • 组合途中每个数轴可以单独选择图表类型
  • 透视图
    • 透视表中插入的图与透视表中的字段有对应关系
    • 行对应标签轴
    • 列对应图列
    • 可以根据对应关系直接调整图表的布局和展现形式
  • 趋势线
    • 折线图和散点图都可以添加不同形式的趋势线
    • 可以显示趋势线的公式用于计算
  • 散点图
    • 透视表中不能直接插入散点图
    • 需要先透视聚合出散点图所需的数据
    • 然后复制数据后再基于粘贴的数据插入散点图
    • 散点图先选数值，标签要单独选中标签单元格添加
      
      

5. 散点图

（1）逾期金额或应收利息与成交额之间的关系（数据透视表）

• 散点图研究的是某个维度上两个数值之间的关系，例如：我们想研究各个小组的成交额与利息之间的关系，就要先算出各小组的成交额和利息，这里将“业务组”拖拽至“行”，“成交额、应收利息”拖拽至“列”
  
• 此时，如果我们直接点击插入图表-散点图，就会显示无法创建这种图表，需要复制数据，而其它图表类型就能正常选择
  
• 这里最简单的方法就是直接复制透视表的数据，然后粘贴，此时双击数值是可以修改的，而透视表中双击数值会向下下钻到这一格聚合数据所对应的原数据
  
• 接着选择粘贴出来的数据区域，然后点击插入，选择右侧的散点图，这样就能看到各个小组的应收利息和成交额，基本上都呈正相关分布；并且散点图一样可以添加趋势线，并显示公式，非常方便做一些回归分析
  
  
• 但是Excel中的散点图没有维度标签，比如这里就没有小组标签，如果想要添加，需要点击“数据标签”的更多选项，然后在标签选项里，勾选单元格中的值，选择我们要添加的标签才行
  
  
  
  其实很多像这样的操作，BI具里只需要一下就能完成，而Excel中需要弯弯绕绕很多步才能实现，效率和美观远不及BI工具；因此Excel只适合快速预览数据，并简单做一些图看趋势，掌握以上的基础图表就差不多了，更多专业的图表还是要放到BI工具里面去做。