Excel中漏斗图的制作方法讨论

       之前本人在Microsoft Excel中尝试了有关业务流程的漏斗图制作。经资料查阅得知，Excel是在其2016版本开始，在可视化工具中直接引入“漏斗图”这个概念的。这就引起了本人的一些疑问：在2016版本之前，Excel是如何实现漏斗图的功能的？新引入的漏斗图功能是否有其局限性？本篇文章将结合某个特定案例对Excel中漏斗图的两种制作方法进行讨论，比较各自的优劣势，以供读者作参考。

       先介绍一下数据。选用的数据极其精简，包含某个特定业务下从对客户的潜在圈定开始，到最终成交所涉及的共计六个阶段的各阶段用户数。六个阶段具体分为：潜在阶段、接触阶段、意向阶段、明确阶段、谈判阶段以及成交阶段。数据刚开始的两列为“阶段”、“客户数”，我们为了探索不同阶段间的变化率，进一步引入了“转化率”这一指标，作为数据的第三列，该列的具体计算方式为：“=B3/B2”，并用填充柄向下拖拽。这里用相对引用，而非绝对引用，是因为都是在单列上，且都是该行B列的值与B列各自对应上一行的比值。将“转化率”所得列的值转化为百分比形式。最终得到的数据集如下图一所示：

       

   图一 不同阶段用户数

       接下来就是去做这样的一个不同阶段间的漏斗变化图。由于目前本人使用的Excel已经在2016版本之后，因此当选中上述数据后，点击“插入”，之后再是选择图表，能发现已有“漏斗图”（如图二）这一选项。

图二 漏斗图可视化选项

       然而选择该图表可能并不能完美地展现我们想要达到的效果。从最终的结果（图三）中我们可以看到，单就“客户数”这一列针对不同阶段的数值，漏斗图可以较好地展现出我们想要的效果，从图中也能通过漏斗左右两侧的斜率等粗略地看出每一阶段用户的流失情况。但除了每阶段的“用户数“是定量的，其余的所有特征，例如我们在表中算出的转化率，对于我们而言在图中最多都只能是定性的。在业务中，我们可能不单单需要了解各阶段的结果，更要从漏斗图中直观看出各阶段的变化情况，因此这样的漏斗图能实现初步效果，但可能并不能完美匹配我们的需求。

图三 使用“漏斗图“进行可视化效果

       那么我们要找的答案是怎么样的呢？本人查阅了网上的各种资料，在各大教程中发现，无论是EXCEL的何种版本，能兼容且更为经典的一种做法是将上述数据通过EXCEL中的堆积条形图实现。而另作者感觉比较惊讶的一个现象是，即使现在能直接如上方法选用EXCEL中的漏斗图，但基本上仍然使用堆积条形图绘制漏斗图。其原理是通过堆积的条形图的模样来“模拟“漏斗图，在堆积条形图的基础上将左侧多余的条形图部分透明化。以下是具体的操作步骤：

       首先，我们需要在原数据中在“阶段“与”用户数“之间添加一列”占位“，它的作用就是充当上文提及的”左侧多余的条形图“部分，到时候再在图中将多余的这块部分透明化。那大家可以想到的一点是，只要是漏斗图，其图肯定是一个轴对称图形（如图三右下角最终的可视化效果），那我们该如何确定各行占位的取值呢？

        对，此时应该使用公式：（$C$2-$C2）/2，并将此公式用填充柄将值从B2拖拽到最后一行。其中，$C$2代表漏斗各阶段的最大值，采用绝对引用，相当于总是将各行与漏斗最上面的一条边（C2)进行比较，充当“不动”的参照标准。由于要形成轴对称的效果，那势必其他阶段对应的图中，两侧应该平分掉与第一行的差异，这就是为什么最后还要除以2。具体的原理可由图四表示出：

图四 漏斗图原理展示

       就像图四展示的那样，堆积条形图中的各行在图中是从列名的那条线开始往右侧发展的。以图中“接触“阶段为例，”占位“的值相当于该图中左侧红色实矩形的那一部分，至于该行右侧的那个红色空心矩形，我们不用考虑，因为”接触“阶段的堆积条形图发展不到那里。对于红色实矩形，即”占位“，就应该表示成各行与最上面”潜在“的条形相差的值的一半，因为按照轴对称的逻辑，图中两个矩形应该大小完全相等，下面行同理。

       在这之后选中A到C列（注意这里可以不用选D列），点击EXCEL“插入”图表中“条形图”--“堆积条形图”，输出相应图标如图五：

图五 最初的堆积条形图

       这样的效果显然仍需要改善。我们首先点击图中任意一条“占位”条，会发现所有蓝色条都被选中，在“填充”中选择白色，让这些条“消失”。

图六 选中颜色条，将其改为白色，这样就看不见了

       接着，由于目前出来是塔形，根本原因在于目前是按“占位”的降序排的，将“塔形”改成“漏斗形”的方法是选中图左侧的阶段文本框，右击选择“设置坐标轴格式”，在出来的右侧框中选中“逆序类别”，如图七：

图七 勾选“逆序类别”

       在这之后可以取消网格线并调整线条粗细等，让图形更好看。例如取消网格线如图八：

图八 取消勾选网格线

       到这里基本上就和直接用2016版本后的“漏斗图”差不多了，但目前这个方法能在这个基础上在图上反映出数据中的“转化率”。方法如下：

       鼠标右击线条，出现“添加数据标签”，点击，如图九：

图九 设置数据标签

       目前出来的数据标签仍然是“客户数”，我们想把它换成“转化率”，则选中这些标签，右键，选择最后的“设置数据标签格式”。从弹出的右侧对话框中，勾选“单元格中的值”，如图十：

图十 选择“单元格中的值”

       出来的对话框中选择案例中的D2到D7（D2可事先标注“100%”）。得到图十一的结果：

图十一 转化率的展示

       最后，选中我们的图，点击上面的“图表设计“，在”添加图表元素“中选择“线条”—“系列线”，可将漏斗两侧用线条连起来：

图十二 系列线让漏斗图更完美

       最终效果如下：

图十三 用堆积条形图最终形成的漏斗图效果

       以上是在EXCEL中实现漏斗图的两种方法，分别是通过“漏斗图”可视化及“堆积条形图”模拟，在一定条件下效果是相近的。

【两种方法的适用场景总结】

       漏斗图——当原数据仅仅只有我们关心的维度（如案例中“客户数”），而不用考察其他的（如案例中“转化率”），即只考虑自身不用考虑彼此之间的变化时，可使用。方法出现较新，优点是快速有效可视化，但亲测无法展现如“转化率”等综合因素可视化，对可视化的限制较大；选择这种方法适用于如数据只含有“阶段”、“客户数”，想要展现“客户数”的销售漏斗分析；

       堆积条形图——经典的漏斗图绘制方法，不单单考虑条形图各行自身，而需要一并分析各行之间变化情况时推荐，具体方法如上述提及，缺点则是较为繁琐，需要生成“占位”作为条形的一部分，之后再把它用白色处理“消失”；选择这种方法则适用于如案例中数据含有“阶段”、“客户数”、“转化率”等，能多角度展现漏斗中的一些差异，不推荐用于只含有“阶段”、“客户数”的情形。

       综上，作者认为可能也是目前在EXCEL绘制漏斗图仍主要运用堆积条形图模拟，而不直接使用漏斗图绘制的重要原因。