【干货】用Excel做波士顿矩阵图,5分钟搞定

点击蓝字 关注我们

在工作中，很多决策并非因为信息不对称，而是缺乏科学、系统的业务分析工具。

波士顿矩阵是一种看起来简单，但却能解决企业最重大问题：企业产品和业务如何进行资源投入配置决策的战略工具。今天给大家介绍如何用Excel实现波士顿矩阵分析。

01

02

今天我们用Excel做两个波士顿矩阵象限图，一个是单系列的，所有气泡的颜色一样；一个是多系列的，按照象限设置不同的颜色填充。

1.单系列波士顿矩阵图

步骤一：单系列波士顿矩阵图辅助数据

单系列波士顿矩阵图的好处是用较少的数据就可以实现，不足的地方是没有办法自动按象限区域设置气泡颜色，需要手动一个个选择气泡进行颜色填充。

步骤二：创建气泡图

选中B2:D11单元格数据区域，【插入】→【二维气泡图】，创建一个单系列的气泡图。如果发现X轴、Y轴或者气泡大小对应的数据值与期望得不符，可以通过“选择数据”命令，更改数据系列。

步骤三：将坐标轴位于图表象限中间

选择纵坐标轴，切换至“坐标轴选项”选项卡，设置坐标轴最小值为0、最大值为400.0；与横坐标轴交叉值为200.0，这里的200参考了订单量的平均值设置；标签位置值为“低”。切换至【填充与线条】选项卡，设置线条宽度为“1.5磅”、开始箭头类型“燕尾箭头”、结束箭头类型“燕尾箭头”。

重复操作，选取横坐标轴，切换至“坐标轴选项”选项卡，设置坐标轴最小值为0、最大值为10000.0；与横坐标轴交叉值为5000.0，这里的5000参考浏览量平均值进行设置；标签位置值为“低”。

切换至【填充与线条】选项卡，设置线条宽度为“1.5磅”、开始箭头类型“燕尾箭头”、结束箭头类型“燕尾箭头”。

步骤四：给气泡图添加标签

选择气泡图系列，添加数据标签，标签包括“单元格中的值”，标签区域范围“A2:A11”；标签位置居中。

步骤五：图表美化

因为数据的关系，咱们创建的气泡之间有重叠，这显然是不行的，可以通过修改气泡大小解决。选择图表系列，设置气泡缩放比例为60%。

2.多系列波士顿矩阵图

按照Excel的规则，一个系列一个色系，想要让不同象限气泡出现不同颜色，就是把每个象限对应不同的图表系列。

步骤一：准备数据

明星产品的规则是浏览量大于等于均值5000，订单量大于等于均值200，对应G2单元格公式为

“=IF(AND($B2>=$B$14,$C2>=$C$14),B2,NA())”。 

金牛产品的规则是浏览量小于均值5000，订单量大于等于均值200，K2单元格公式“

=IF(AND($B2<$B$14,$C2>=$C$14),B2,NA())”。

问题产品的规则是浏览量大于等于均值5000，订单量小于均值200，O2单元格公式“

=IF(AND($B2>=$B$14,$C2<$C$14),B2,NA())”。

衰退产品的规则是浏览量小于均值5000，订单量小于均值200，S2单元格公式“

=IF(AND($B2<$B$14,$C2<$C$14),B2,NA())”。

步骤二：创建多系列气泡图

因为辅助数据格式的问题，这里创建气泡图的步骤稍微有些麻烦。先选择G2:I11单元格区域，【插入】→【气泡图】，创建一个单系列的气泡图，然后通过工具栏【选择数据】功能，添加剩余的3个象限系列和1个均值标准系列。

步骤三：修改坐标轴极值

选择纵坐标轴，设置最小值为0、最大值为400，单位为50。选择横坐标轴，设置最小值为0，最大值为10000，单位为1000。

设置的目的只有一个，就是让纵坐标轴出现标签200、横坐标轴出现标签5000。

步骤四：制作虚拟象限坐标轴

选择系列“判断标准”，设置填充“无填充”，添加误差线。选择Y误差线，切换至【误差线选项】选项卡，设置末端样式“无线端”、误差量类型“百分比”，值为“100%”；切换至【填充与线条】选项卡，设置线条宽度“1.5磅”，结尾箭头类型“燕尾箭头”。

选择X误差线，执行同样的设置操作。

步骤五：调整气泡大小

因为数据的关系，有气泡重叠的现象，需要缩小气泡的面积。选择任一气泡，设置气泡大小缩放比例为60。

步骤六：美化图表

1）为气泡添加标签；

2）为象限添加说明。

职场上，掌握数据分析技能，可以极大地提升工作效率。CDA数据分析师在各行业的数据岗中认可度非常高，一般都要求考过CDA数据分析师二级，CDA二级中包含了模型搭建的详细内容，对于数据岗的工作来说特别有帮助。

扫码“CDA认证”小程序，获取更多行业模型、知识资料。