漏斗图在流程分析中应用广泛,它通过漏斗各环节的数据比较,能够让观看者直观准确地发现问题所在,进而进行针对性分析,并采取行之有效的措施。
为了更好地讲解绘图步骤,我们虚拟了一家网店销售数据(左表),并绘制了漏斗图(右图):
今天就让我们一起学习绘制这种图表吧!
Step 1
插入辅助列
在「数量」所在列左右各添加一个辅助列,分别命名为「辅助列 1」 和「辅助列 2」。
小贴士 ❶
事实上,辅助列 2 并不是必要的,在实际操作中,仅构建辅助列 1 即可。在这里添加辅助列 2 仅作讲解原理之用。
Step 2
确定辅助列的值
❶ 在 B2 单元格输入公式:
=(MAX($C$2:$C$7)-$C2)/2
注意绝对引用符不能漏了!
❷ 然后将公式复制到 B3:B7 和 D2:D7 中,确定各辅助单元格的数值。
公式解释
❶ MAX 是最大值函数,用于返回 C2:C7 中的最大值。
❷ (最大值 - 当前行数量值)/2 是当前数量与最大数量的差的一半。由于辅助列 1 和辅助列 2 是一一对应的,列表区域任意一行 BCD 三列的数值之和始终等于 MAX($C$2:$C$7)。
Step 3
插入堆积条形图
❶ 选择 A1:D7,点击【插入】-【图表】选项组中的
按钮,弹出「插入图表」对话框。❷ 点击【所有图表】下的【条形图】,选择「堆积条形图」。
❸ 点击【确定】,插入图表。
Step 4
逆序类别
此时的漏斗图是倒着的,要如何扭转乾坤呢?
只需先单击选择纵坐标轴,再双击纵坐标轴,在「设置坐标轴格式」面板中的【坐标轴选项】下的「坐标轴选项」中勾选中「逆序类别」即可。(这里叙述有点绕,不过是很好找到的~)
Step 5
隐藏辅助列条形
看完上一步,估计很多资深表亲都恍然大悟了——
原来构筑对称相等且使每一行 BCD 之和都相等的两个辅助列,就是为了每一条堆积条形图等长且数量系列条居中对齐,从而形成「漏斗」。
小贴士 ❷
辅助列 1、2 的存在让图表左右对称。其实,图中用于堆积条形图基底的辅助列 1 就足以使数据系列条形成漏斗,只是我这里用了辅助列 2,会帮助大家理解起来更直观更容易~
这时新的困扰来了,漏斗形成了,但是辅助条的存在显然是个累赘,如何将他们隐藏起来又不影响其「占位」作用呢?
方法说来也简单,那就是将辅助列调整为无线条无填充,它们就彻底隐形了。
❶ 点击堆积条形图中辅助列 1 对应的蓝色条形,点击鼠标右键,选择【设置数据系列格式】,接着便会出现「设置数据系列格式」面板。
❷ 点击【填充与线条】也就是
按钮,在「填充」中勾选「无填充」,在「边框」中勾选「无线条」。❸ 运用同样的方法隐藏辅助列 2 对应的条(实操中不设置辅助列 2 则跳过该步骤)。
Step 6
美化图表
❶ 点击选中网格线,按【Delete】键删除网格线,并按同样的方法删除图例。
❷ 点击图表标题,修改成「网店销售漏斗图」。
❸ 点击选中水平坐标轴,双击以弹出「设置坐标轴格式」面板,在【填充与线条】里的「线条」下勾选「无线条」,在【坐标轴选项】里的「标签」的「标签位置」中选择「无」,并设置坐标轴边界最大值为 2000。
然后用同样的方法,将垂直坐标轴也调整为无线条。
❹ 选中数量系列条形图,在「设置数据系列格式」面板中的【系列选项】里调整「间隙宽度」为 50%,在【填充与线条】中调整填充为「纯色填充」,填充蓝色。
小贴士 ❸
间距及颜色根据具体应用场景和个人审美灵活调整啦~
❺ 再选中数量系列条形图,点击【图表元素】按钮,选择【数据标签】,点击右侧三角形按钮,在下拉菜单中选择【更多选项】,在出现的「设置数据系列格式」面板中,去掉「显示引导线」前面的勾选,然后在【开始】选项卡中设置系列文本为加粗、白色。
❻ 对各图表元素做一些微调,一张美美的漏斗图就新鲜出炉了!
不过最近小 E 听说在微软爸爸最新发布的 Excel2019 中,只需选中已输入好的数值,就能一键生成漏斗图了。
当然如果还没机会用到 2019 版本,我们按照文章的步骤练习,也一样轻松搞定!
作者:小花(秋叶 Excel 课程答疑老师)
联系客服