下载掌阅APP，畅读海量书库
立即打开
如果想要了解销售额最大的前N个客户或最小的后N个客户，业绩最好的前N大业务员，某个地区下销售额最大的前N大产品等，就要制作查看前N名和后N名的排名图表。
如果排序的项目很多，将所有项目都显示在图表上，使用柱形图或条形图就显得很拥挤，此时，可以使用数值调节按钮或者滚动条来控制图表上显示的数据个数，灵活查看前N名和后N名的排名情况。
图2-45是一个供应商发货汇总表。现在制作可以查看发货量最大或最小的前N名供应商的排名图表，其效果如图2-46和图2-47所示。本案例素材是“案例2-3.xlsx”。
图2-45 供应商发货汇总表
图2-46 发货量最大的前10个供应商
图2-47 发货量最小的后10个供应商
这个动态分析图表，同样需要设计辅助区域，并使用动态名称绘制，如图2-48所示。
图2-48 设计辅助区域
两个选项按钮的链接单元格是P2，一个滚动条的链接单元格是P3，这样，W列的排序公式为：
=IF($P$2=1,LARGE($S$3:$S$24,U3),SMALL($S$3:$S$24,U3))
排序后供应商名称匹配公式为（单元格V2）：
=INDEX($R$3:$R$24,MATCH(W3,$S$3:$S$24,0))
S列直接引用原始数据，并进行可能存在的重复数据处理，单元格S2公式为：
=C3+RAND()/1000000
单元格O5显示动态标题文字，公式为：
=IF(P2=1,"发货量最大的前 ","发货量最小的后 ")&P3&" 个供应商"
滚动条用来显示供应商个数，因此需要使用OFFSET函数引用动态区域，定义“供应商”和“发货量”两个动态名称，引用区域分别如下。
名称“供应商”：
=OFFSET(Sheet1!$V$3,,,Sheet1!$P$3,1)
名称“发货量”：
=OFFSET(Sheet1!$W$3,,,Sheet1!$P$3,1)
还可以对这个排名图表进行继续完善，例如，选择显示前N个供应商时，用饼图展示前N个供应商的发货量占全部发货量的比例，此时，需要再设计辅助区域，如图2-49所示，其中，单元格Z2计算前/后个供应商发货量合计计算公式为：
=SUM(发货量)
单元格Z3是其他供应商发货量的合计，计算公式为：
=SUM(W3:W24)-Z2
图2-49 辅助区域，计算当前图表显示的供应商发货量合计
利用辅助区域绘制饼图，进行格式化，然后放置到条形图上的适当位置，如图2-50所示。
图2-50 同时显示前/后N个供应商发货量排名和合计占比
在Excel上制作排名的动态图表很麻烦，不过，做好图表后，使用起来就非常方便了。
这样查看前N个或者后N个的动态排名分析图表，在Tableau中制作非常简单。创建一个筛选器，并联合使用排序按钮，即可完成动态分析。
建立数据连接，制作基本的条形图，先按降序排序，如图2-51所示。
图2-51 创建的基本条形图
插入一个计算字段“筛选N个”，公式为“INDEX()”，如图2-52所示。
然后将这个计算字段拖至筛选器，弹出“筛选器”对话框，如图2-53所示，选择“至多”选项，并将值设置为最大值。
图2-52 计算字段“筛选N个”
图2-53 “筛选器”对话框
再将筛选器显示在图表右侧，如图2-54所示。
图2-54 在图表右侧显示筛选器
可以通过图表右侧的筛选器滑块控制图表上显示数据的个数，如图2-55所示。
图2-55 查看发货量最大的前10个供应商
单击工具栏上的“升序”按钮，再调整筛选器滑块，得到发货量最小的后N个供应商，如图2-56所示。
图2-56 查看发货量最少的后5个供应商
如果源数据是每天各商品的销售流水，现在要分析指定时间段内各个商品累计销售排名，可以联合使用SUMIFS函数和控件制作动态分析模板；也可以使用数据透视图来动态分析，或者使用Tableau制作动态筛选器来控制图表。
图2-57是示例数据，记录每天的每个产品的销售额。本案例素材是“案例2-4.xlsx”。
图2-57 示例数据
一个最简单的方法，是使用Excel数据透视图和日程表来建立各商品销售额的排名模型，操作简便，使用灵活。
首先创建数据透视表和数据透视图，并对各产品销售额降序排序，如图2-58所示。
图2-58 各产品销售额及排序
插入一个日程表，以日显示，将日程表放到透视表和透视图的顶部，如图2-59所示。
图2-59 使用日程表指定显示的日期区间
在日程表上拖动滑块，指定要显示的日期区间，图表可以显示为指定日期区间内各产品销售额的排名。
在Excel中，利用函数和控件制作任意指定日期区间内的排名，有些烦琐，因为既要汇总计算，又要用函数进行排名，不过，这样的制作过程，也是一个逻辑思维的训练过程，对数据分析很有帮助。
动态图表的效果如图2-60所示。下面是制作方法和主要步骤。
图2-60 指定时间段内的产品销售排名
首先设计一个全年的日期列表，然后插入两个组合框，分别用于选择开始日期和截止日期，数据源区域是日期列表区域，单元格链接分别是单元格P4和P5，如图2-61所示。
图2-61 设置控件
根据组合框返回值再取出对应的日期，保存在单元格Q4和Q5中，公式分别如下。
单元格G4：
=INDEX(M3:M367,P4)
单元格G5：
=INDEX(M3:M367,P5)
设计汇总表格，计算每个产品在指定时间段内的销售额合计，如图2-62所示，单元格T4的计算公式如下：
=SUMIFS(销售记录!C:C,销售记录!A:A,">="&$Q$4,销售记录!A:A, "<="&$Q$5, 销售记录!B:B,S4)
图2-62 汇总各产品的销售额
设计排序辅助表，对汇总表进行降序排序处理，如图2-63所示，公式如下。
单元格W4，匹配产品名称公式为：
=INDEX($S$4:$S$11,MATCH(X4,$T$4:$T$11,0))
单元格X4，降序排序公式为：
=LARGE($T$4:$T$11,V4)
图2-63 对销售额进行降序排序
最后，用排序数据区域绘制柱形图，并进行布局和格式化，得到用户需要的动态图表。这样可以查看任意指定时间段的各产品销售额排名，如图2-64所示。
图2-64 任意指定时间段的排名图表
在Tableau中，需要创建参数和计算字段，然后创建仪表板，可以非常方便地指定开始日期和截止日期，观察指定时间段内的各商品销售排名，效果如图2-65所示。
图2-65 Tableau指定时间段的自动化排名分析仪表板
下面是仪表板的主要制作方法和步骤。
建立数据连接，并绘制基本柱形图，如图2-66所示。
图2-66 基本的排名柱形图
创建“开始日期”和“截止日期”两个参数。方法是单击字段“日期”下拉按钮，在下拉列表中执行“创建”→“参数”命令，如图2-67所示。
图2-67 “创建”→“参数”命令
打开“创建参数”对话框，分别创建“开始日期”和“截止日期”两个参数，如图2-68和图2-69所示，其中“开始日期”参数的当前值设置为“2022-1-1”，允许的值选中“全部”单选按钮；“截止日期”参数的当前值设置为“2022-12-31”，允许的值选中“全部”单选按钮。
图2-68 参数“开始日期”
图2-69 参数“截止日期”
然后再创建一个计算字段“日期筛选”，如图2-70所示，公式为：
[日期]>=[开始日期] AND [日期]<=[截止日期]
将创建的字段“日期筛选”拖至筛选器，在弹出的“筛选器”对话框中勾选“真”复选框，如图2-71所示。
图2-70 计算字段“日期筛选”
图2-71 勾选“真”复选框
这样，就得到了能够对日期进行指定开始日期和截止日期的筛选分析，如图2-72所示。
图2-72 创建参数、计算字段，建立筛选
插入一个仪表板，将这个工作表拖至仪表板，然后单击仪表板右侧的下拉按钮，在下拉列表中执行“参数”命令，然后分别选择“开始日期”和“截止日期”选项，如图2-73所示。
图2-73 选择“开始日期”和“截止日期”参数
这时在仪表板右上角会出现两个参数卡，如图2-74所示。
单击某个参数卡左侧的下拉按钮，在下拉列表中选择“浮动”选项，如图2-75所示。
图2-74 两个参数卡
图2-75 设置参数卡为“浮动”
可以将这两个参数拖放到仪表板的适当位置，然后布局，得到可以查看任意指定时间区间的产品销售排名图表，如图2-76所示。
图2-76 拖放并布局日期参数卡
前面介绍的是单纯排名与对比分析图表。这种对比分析图表反映的信息比较单一，只能看出其高低大小，无法同时去观察其他信息，例如，每个产品的销售额占比如何？每个产品在这个时间段的销售分布如何？等等，因此，可以将柱形图、条形图、面积图、折线图、饼图、圆环图等组合起来，生成信息更丰富的图表。
当需要制作多个类型图表组合在一起的分析报告时，使用Tableau就比Excel方便得多。下面介绍如何在Tableau中制作一个能够反映任意时间段内各产品的排名、各产品的每天销售数据趋势、各产品销售的占比结构，以及该时间段内的销售总额的图表。
前面已经制作了指定时间段的筛选器，这个筛选器需要对所有的工作表和仪表板有效，因此需要对日期筛选器设置“使用此数据源的所有项”，如图2-77所示。
图2-77 将日期筛选器用于所有项
插入一个新工作表，绘制折线图和圆点图（销售额绘制两个图表，一个是折线图，一个是圆图，并设置为双轴），再进行适当美化，得到如图2-78所示的图表。
图2-78 指定时间段内日销售变化
再插入一个工作表，绘制饼图，观察指定时间段内各产品销售额的占比，设置格式及显示标签等，如图2-79所示。
图2-79 指定时间段内各产品销售额占比
最后，将新制作的两个工作表拖至仪表板，做好布局，得到能够同时观察指定时间段内，各产品销售额排名、占比，以及每天的销售情况，如图2-80所示。
图2-80 指定时间段各产品销售分析
选择不同的开始日期和截止日期，得到该时间段内各产品的排名分析、结构分析和趋势分析，如图2-81所示。
图2-81 任意指定时间段内的各产品排名分析、结构分析和趋势分析
不过，由于此时日期跨度延长，圆点较多，标签显得很凌乱，这里取消了显示圆点的标签。
本质上同比分析和环比分析都是对比分析内容，因为要把今年的数据与去年同期相比，把这个月数据和上个月相比，归根到底，就是一个比较分析。
如果老板问，今年销售收入与去年同期相比，增长情况如何？也许马上就会想到用Excel绘制一个柱形图，如图2-82所示。本案例素材是“案例2-5.xlsx”。
这个图表并不完美，缺少了一些重要信息，例如，增长率是多少？具体增长了多少？如果再添加一个上升或下降的箭头，是不是更有对比效果？
首先，两根柱形并不是同一类数据，一个是去年的，一个是今年的，因此需要用两种不同的颜色来表示，这样对比才明显。
其次，根据实际情况是同比增加还是同比下降，添加一个上升箭头或下降箭头，醒目标识同比增长或同比下降，这样的对比分析效果就更加强烈。
最后，在图表上插入一个文本框，显示同比增长率情况说明。
这样就得到一个内容丰富的同比分析图表，如图2-83所示。
图2-82 两年销售额同比分析
图2-83 修饰美化后的同比分析图表
还要注意一个问题是，在绘制单一两根柱形时，有时数值轴并不是从0开始的，而是被自动设置了，这样会造成错误的理解，如图2-84所示。从图表上看，今年似乎比去年下降很多，实际上并非如此，出现这种情况的原因是，数值轴刻度的最小值是自动设置的，设置为了2080，这样显得两个柱形相差很大。
此时，需要将数值轴的最小刻度设置为0，才能得到正确的图表，如图2-85所示。
图2-84 数值轴最小刻度被自动设置，两个柱形看起来相差很大
当然，有时需要强化其之间的差异，这时，可以将数值轴的最小刻度设置为一个合适的值。
图2-85 设置数值轴的最小刻度为0
如果要分析的各项目之间各自独立，没有关联性，例如各产品销售的同比分析，此时，需要合理设置去年柱形和今年柱形的颜色和重叠比例，以便使图形看起来更加美观，信息更加清晰。
图2-86是各产品两年销售的同比分析报表，那么，应该如何绘制可视化同比分析图表来清晰展示出每个产品的同比增长情况？本案例素材是“案例2-5.xlsx”。
这个表格反映了两个重要信息：一个信息是总销售的增长情况，另一个信息是每个产品的销售增长情况。
图2-86 各产品两年销售同比分析报表
总销售增长情况可以采用2.6.1节制作单一的同比分析的方法制作图表；每个产品的同比增长情况，则需要使用两个图表来表达：一个是柱形图，一个是折线图（不显示线条，仅显示标记点）。
将3个图表进行组合，在左上角添加一个形状，填写文字说明，最后的可视化分析报告如图2-87所示。
图2-87 产品销售同比分析报告
各产品销售同比分析图表是普通柱形图，重点是设置系列的间隙宽度和重叠比例，以及去年柱形和今年柱形的填充颜色。这个设置比较烦琐，需要一点一点来仔细设置。
各产品增长率就是一个简单的折线图，不显示线条，设置数据点标记格式，然后再设置同比增加和同比下降百分比数字的自定义格式，自动根据正负数显示不同颜色，再显示每个数据点的垂直线。
有些情况下要做的同比分析的数据系列属于两种不同类型的数据，但其之间有逻辑上的关联，例如销售额和毛利，毛利是销售额的一部分，两者比例就是毛利率，毛利占销售额比例越大，毛利率则越大。对于这样的问题，可以绘制嵌套柱形图来比较二者的大小，外部柱形是销售额，内部柱形是毛利。
图2-88是同时考虑销售额和毛利的同比分析图表，毛利嵌在销售额中，在坐标轴标签上同时显示年份名称和毛利率。
在Excel中，这个图表绘制并不复杂，主要是设置次坐标轴和辅助坐标轴。下面是主要制作步骤。
首先绘制普通柱形图，如图2-89所示。
图2-88 销售额和毛利同比增长分析
图2-89 绘制的普通柱形图
将系列“毛利”绘制在次坐标轴上，如图2-90所示。
图2-90 将系列“毛利”绘制在次坐标轴
删除右侧的次数值轴，然后分别选择销售额和毛利，分别设置其间隙宽度，将销售额和毛利分别显示出来，如图2-91所示。
图2-91 分别设置销售额和毛利的间隙宽度
在单元格设计辅助坐标轴标签，使用公式将年度名称和毛利率组合成新字符串，如图2-92所示，单元格D7公式为：
=D2&CHAR(10)&"毛利率 "& TEXT(D4/D3,"0.0%")
图2-92 设计辅助坐标轴标签
然后将图表的分类轴引用区域修改为这个辅助坐标轴区域，得到如图2-93所示的图表。这样，坐标轴标签中就同时显示年份名称和毛利率。
图2-93 修改调整分类坐标轴引用区域
最后对图表进行适当的格式化和美化，完成最终图表的制作。
如果要将每个产品销售放在一起做同比分析，不仅要观察每个产品的同比增长情况，还需要看所有产品合计数的增长情况，此时，可以绘制堆积柱形图。
对图2-94所示的数据，如何对比分析各产品各年的销售，并观察所有产品销售合计的各年变化趋势？本案例素材是“案例2-5.xlsx”。
图2-94 2018—2021年各产品销售统计
图2-94所示的表格可以绘制如图2-95所示的堆积柱形图，并显示系列线，将图例显示在右侧，调整各项目的先后顺序使其与工作表顺序相同，这样的图表是比较清晰的。
图2-95 分析各产品历年销售
一般堆积柱形图中，调整各项目的先后次序是一个必需的操作，因为需要保持图表上各项目的上下顺序与工作表上各项目的上下顺序相同。
这种顺序调整是在“选择数据源”对话框中进行。选择某个项目，单击“上移”或“下移”按钮即可，如图2-96所示。
图2-96 调整各项目的先后次序
某些具有特殊意义的数据使用普通柱形图或者条形图，都无法把数据的含义准确无误地表达出来。例如，资金的流入和流出对比，今年的财务指标与去年的财务指标对比，此时，可以绘制两个方向的条形图。
图2-97是两年的主要财务指标，现在要求绘制如图2-98所示的分左右条形的图表（有人称之为旋风图）。
图2-97 两年财务指标
图2-98 两年财务指标对比图
图2-98是堆积条形图，需要先设计G～J列的辅助区域，如图2-99所示。H列中去年数据引用原始数据中去年的负数，J列今年的数据引用原始数据中今年的正数，中间I列输入一个固定的百分比数字，用于显示财务指标名称。
公式设计完成后，再对H列设置自定义数字格式，将负数显示为正数，自定义数字格式代码为：
0.00%;0.00%;0.00%
图2-99 设计辅助区域
用辅助区域G～J列绘制基本堆积条形图，如图2-100所示。
图2-100 绘制的基本堆积条形图
然后设置图表格式，包括设置坐标轴逆序类别，将中间的条形设置为无填充、无线条，添加数据标签为“类别名称”，两侧的条形添加数据标签为“值”，调整间隙宽度，设置条形颜色，调整数字标签位置，等等。
图2-101是公司各部门两年的入职人数和离职人数统计报表。现在要制作一个分析各部门两年流动性的分析报告。
本案例素材是“案例2-6.xlsx”。
图2-101 各部门两年入职人数和离职人数统计报表
首先绘制总公司两年入职和离职人数的对比分析图表，如图2-102所示。这个图表的特点如下。
（1）横向上，分别比较两年的入职人数和两年的离职人数，例如，入职人数去年是81人，今年是109人；离职人数去年是107人，今年是139人。
（2）纵向上，分别比较每年的入职人数和离职人数。例如，去年入职人数是83人，离职人数是107人；今年入职人数是109人，离职人数是139人。
图2-102 公司两年入职人数和离职人数对比分析图表
这个图表的绘制数据区域是图2-103所示的辅助区域，图表是堆积条形图，设置起来比较烦琐，与2.6.5节介绍的两年财务指标对比分析图一样，这里不再赘述。
图2-103 设计辅助区域
当分析每个部门的两年流动性时，至少需要从两个角度来分析。
（1）去年和今年的入职人数对比，发生了什么变化。
（2）去年和今年的离职人数对比，发生了什么变化。
因此，可以绘制如图2-104所示的左右布局的对比分析图，其制作步骤如下。
图2-104 各部门去年和今年入职人数和离职人数对比分析图
首先设计辅助区域，如图2-105所示。可以使用VLOOKUP函数快速从原始数据区域查找数据，没必要一个一个单元格进行链接。
例如，单元格R3公式为：
=-VLOOKUP(O3,$B$4:$F$10,2,0)
单元格R3公式为：
=-VLOOKUP(O3,$B$4:$F$10,4,0)
其他单元格公式以此类推。
图2-105 设计辅助区域
以辅助区域绘制堆积条形图，如图2-106所示。
图2-106 绘制基本的堆积条形图
将系列“入职人数”“年份”和“离职人数”绘制在次坐标轴上，如图2-107所示。然后将主坐标轴的分类轴区域设置为O列的部门区域，将次坐标轴的分类轴区域设置为P列的年份区域。
图2-107 将系列“入职人数”“年份”和“离职人数”绘制在次坐标轴
添加次要纵坐标轴，将主分类轴和次分类轴均设置“逆序类别”，并设置系列的重叠比例为100%，间隙宽度为50%，得到如图2-108所示的图表。
图2-108 设置坐标轴格式
删除图表左侧的次要纵坐标轴、图表顶部水平轴，并设置不显示底部水平轴的标签，得到如图2-109所示的图表。
图2-109 继续设置坐标轴格式
选择系列“年份”，设置为无填充、无轮廓，添加数据标签（标签显示类别名称），得到如图2-110所示的图表。
图2-110 显示年份名称
最后将图例移到图表顶部，并删除图例中的不需要项，修改图表标题，设置条形颜色，手动插入水平线条，将各部门数据上下隔开，就得到了用户需要的图表。