Excel波士顿矩阵图一直是商业分析里的高频需求几乎每份战略汇报PPT里都会出现一张。但真正能用手头Excel做出来的人说实话不多——大多数教程要么只讲原理不讲操作要么上来就让你装各种插件最离谱的是直接让你画个四宫格填颜色那是示意图不是数据分析图。我这次把实际做图的完整流程拆开讲透从数据口径怎么定、坐标轴怎么交叉、误差线怎么设到最后标签怎么摆、怎么复用模板全部走一遍。你手头只要有Excel 2016以上版本跟着做完就能得到一张完全可以放进周报、复盘会、甚至对外PPT里的波士顿矩阵图。先说清楚适合谁看做业务分析、产品规划、经营管理、咨询顾问的朋友以及所有需要在Excel里动手做“四象限分析”的人。这篇文章不涉及任何插件加载全部用Excel原生功能实现你学到的是原理而不仅仅是点按钮的步骤所以就算换到WPS或Mac版Excel思路完全通用。1. 波士顿矩阵图是什么为什么非用Excel做不可1.1 不是画个四宫格就叫波士顿矩阵很多人一听到波士顿矩阵第一反应是“在PPT里画个2x2的表格把业务填进去”。这种做法的确快但问题很大格子里的位置全凭感觉两个业务明明差距很大放在同一个格子边缘和角落看起来没区别数据一更新整张图就得重新手画。真正的波士顿矩阵图本质是一张以两个连续指标为坐标轴的散点图业务在图上是一个个有实际坐标的点落在哪个象限是数据说了算不是手动拖进去的。这套分析框架是波士顿咨询公司的布鲁斯·亨德森在1970年代提出的核心就两个维度横轴叫相对市场份额纵轴叫市场增长率。这两个维度组合出四个象限高增长高份额明星、低增长高份额金牛、高增长低份额问号、低增长低份额瘦狗。每个象限对应不同的资源策略这也是它一直被沿用至今的原因——不是因为它画得好看而是因为它把“钱往哪里投”这个决策压缩成了一张二维图。用生活化的类比来理解你可以把公司的业务组合想象成一个花园。明星业务是正在快速生长的果树要浇水施肥金牛业务是已经成熟的果树只管摘果子问号业务是刚发芽但不知道能不能成活的种子要挑着养瘦狗业务是病恹恹的枯枝该修剪就修剪。波士顿矩阵图就是把这个花园的状态拍成一张快照让你一眼看出每棵树的状况。1.2 为什么用Excel而不是专业可视化工具很多专业BI工具都能做散点图但Excel在商业分析场景里依然是首选原因很朴素第一数据从业务系统导出来就是Excel格式不需要额外打通数据管道第二做出来的图表和PPT无缝衔接复制粘贴就能用第三Excel里改一个数字图自动变不用重新导入导出第四你的同事、老板、客户电脑上大概率都有Excel不需要考虑对方有没有装Tableau或者Power BI。但还有一个更实际的原因波士顿矩阵图的制作过程本身就是一个数据梳理过程。你得先想清楚“相对市场份额怎么算”“市场增长率取哪段时间”“分割线定在什么位置”这些口径问题在Excel里做的时候会被强制面对而用BI工具拖拽字段很容易绕过思考做出一张好看但没有业务依据的图。1.3 做图前你必须明白的四个象限判断规则先看一张速查表后面做图时你会反复用到它象限市场增长率相对市场份额策略倾向明星高高加大投入保持领先金牛低高维持份额收割利润问号高低选择性投入观察潜力瘦狗低低收缩或退出注意这里的“高”和“低”不是相对于所有业务的平均值而是相对于你设定好的分割线。分割线怎么定是后面第二步的关键这里先按住不表。2. 做图前先花10分钟把数据口径理清楚2.1 理解两个核心指标的计算口径波士顿矩阵的横轴“相对市场份额”标准定义是“本公司某项业务的市场份额 ÷ 最大竞争对手的市场份额”。为什么不用绝对市场份额而用相对值因为绝对市场份额无法反映你在市场里的竞争地位一个10%的份额在小众市场可能是老大在巨头林立的大市场里可能连前三都进不了。相对市场份额以1为分界大于1说明你是市场领导者小于1说明你是挑战者或跟随者。这个“1”就是天然的横轴分割线。纵轴“市场增长率”相对简单通常是近两年或近三年的业务增长率。分割线一般取行业平均增长率也可以用公司整体增长率作为参照。这个值没有唯一标准答案核心是让分割线有业务依据而不是拍脑袋。做图的时候这条分割线就是Y轴的交叉点位置。2.2 一个可以直接套用的最小数据表我用一个简单的例子带你走流程。假设你是一家消费品公司的分析人员手上有6条业务线业务线市场份额最大对手份额相对市场份额近两年增长率业务规模销售额万元即饮咖啡25%15%1.6732%800功能饮料18%22%0.828%450瓶装水35%20%1.754%1200茶饮料9%16%0.5624%300植物蛋白饮料12%10%1.20-3%200碳酸饮料5%30%0.17-8%150数据表里“相对市场份额”不是原始数据是用“市场份额 ÷ 最大对手份额”算出来的Excel里直接写公式就行。如果你手头只有销售收入和市场总规模可以改成“业务收入 ÷ 最大对手收入”如果你在分析产品线而不是业务线维度上同理但数据来源得换成产品维度。口径只要统一矩阵图的逻辑不会变。行业平均增长率我取10%作为纵轴分割线。那么在这个例子里即饮咖啡和瓶装水是明星/金牛茶饮料是问号其余是瘦狗。看上去很合理但别急着下结论等图做出来再说。2.3 确认坐标轴分割线的两个关键值到这一步你需要明确两个数字横轴分割线相对市场份额 1这里天然固定不需要额外计算纵轴分割线市场增长率 10%你自己定的行业平均增长率这两个值在后面设置坐标轴交叉点的时候要用到。还有一个隐藏参数横轴显示范围。默认情况下Excel的坐标轴范围是自动的但为了让四个象限在视觉上比例协调我通常把横轴范围设在0到3之间纵轴范围设在-15%到40%之间。这样分割线大致落在图的中部看起来最舒服。3. 核心制作步骤散点图、误差线和坐标轴交叉3.1 插入散点图并设置数据系列打开Excel把上面的数据表复制成Sheet“数据”。选中“相对市场份额”和“近两年增长率”两列数据只要数据区域不要表头点击菜单栏“插入”→“散点图”→“仅带标记的散点图”。这时你会得到一个什么都没有的空白散点图先别慌这是正常现象——空白的原因是Excel可能把第一列当成X轴序列了需要手动修正。在图表空白处右键“选择数据”→“添加数据系列”。系列名称写“业务组合”X轴系列值选“相对市场份额”那一列不含表头Y轴系列值选“近两年增长率”那一列。确认后图表里会出现6个点。每一个点就是一条业务线但目前它们长得一模一样后面我会教你如何把业务名称标上去。为什么必须用散点图而不用折线图和柱状图因为散点图的本质是用两个连续变量定位一个点正好对应波士顿矩阵的坐标逻辑。折线图强调趋势柱状图强调大小都不适合表达“二维定位”。3.2 用误差线画出四象限分割线这一步是整个做图过程的关键也是最容易把人绕晕的地方。选中刚才的散点图数据点Excel会显示出“图表工具”选项卡。点击“添加图表元素”→“误差线”→“其他误差线选项”此时Excel会给你的数据点添加水平和垂直两组误差线。默认情况下误差线是“正偏差”——只会往一个方向延伸这不对我们要的是从每个点向四个方向延伸的十字线。在Excel右侧的“设置误差线格式”面板里水平误差线和垂直误差线要分别设置水平误差线X轴方向选“正负偏差”末端样式选“无端帽”误差量选“固定值”填入一个覆盖整个坐标轴宽度的数。垂直误差线Y轴方向选“正负偏差”末端样式选“无端帽”误差量选“固定值”同样填入一个覆盖整个坐标轴高度的数。问题是这个“固定值”填多少误差线的特点是它以数据点为中心向两边各延伸你填写的值。如果横轴设计范围是0到3中心分割线在1那么想让误差线覆盖整个坐标轴固定值需要填max(1-0, 3-1) 2。实际上你可以填个更大的数比如10误差线会超出图表边界但Excel会自动裁剪到绘图区边缘效果等同于画了一条贯穿整个图表的横线。这个方法我叫它“暴力覆盖法”简单粗暴但极其好用。填完后你会看到每条业务线的点上都长出了十字线它们叠加在一起会变成一团乱麻。但别急下一节通过坐标轴交叉设置乱麻会自动收敛成一个标准的四象限十字。3.3 坐标轴交叉设置把分割线拨到正确位置误差线画出来了但分割线还没有归位。这时需要调整坐标轴的交叉点双击水平轴即X轴在右侧“设置坐标轴格式”面板中找到“纵坐标轴交叉”分组点选“轴值”填入你要的增长率分割值。在这个例子里是0.1因为Excel里10%要写成0.1。这时候Y轴的分割线就位移到了10%的位置。同样的双击垂直轴即Y轴在“横坐标轴交叉”中填入横轴分割值1。这时候X轴的分割线就位移到了相对市场份额为1的位置。到这里一个四象限十字已经完美落在图中央。原理上误差线画的是“数据点向外延伸的物理线段”坐标轴交叉设置调整的是“坐标轴原点的显示位置”两者配合才能让分割线显示在正确位置。而业务数据点本身并不受分割线影响它依然按照真实坐标显示该在哪就在哪。3.4 调整坐标轴边界和刻度让图更规范上面设置完之后横轴可能显示负数区间。这是正常现象因为横坐标轴交叉到1之后左侧自然会出现0到1的负向区域但相对市场份额小于0没有实际意义。右键水平轴在“设置坐标轴格式”里把“边界”的最小值改为0最大值改为3如果想让图标紧凑也可以把最大值改成2.5。同样垂直轴的最小值改成-0.15最大值改成0.4这样增长率显示的上下留白比较均匀。此时横轴刻度是0、1、2、3纵轴刻度是-15%、0%、10%、20%、30%、40%。两个分割线分别落在1和10%的刻度上视觉上四象限就出来了。如果你觉得默认刻度太密可以在“刻度线间隔”中调整比如纵轴改成0.1间隔即每隔10%一个刻度。3.5 标注业务名称消除“点是谁”的困惑现在图上有6个点但你分不清哪个点对应哪条业务线。选中数据点点击“图表工具”→“添加图表元素”→“数据标签”→“右侧”。默认显示的标签是Y轴数值比如32%、8%这不是我们想要的名字。右键任意数据标签选择“设置数据标签格式”勾选“单元格中的值”然后在弹窗中选择“业务线名称”那一列。取消勾选“Y值”和“X值”只保留“单元格中的值”。确认后每个点旁边会出现对应的业务名称。这个功能Excel 2013以上都支持WPS最近几个版本也有。标签加完后可能会出现重叠——尤其当两个点坐标接近时。手动把标签拖开即可。这个动作虽然看起来费时间但对最终报告效果的影响非常大我见过太多图因为标签叠得乱七八糟直接被老板打回重做。3.6 按业务规模调整气泡大小进阶如果你不满足于简单的散点图想把“业务规模”这个第三维信息也放进去可以把图表类型换成气泡图。做法是右键图表→“更改图表类型”→“气泡图”。然后在“选择数据”中将“气泡大小”系列值指向“业务规模”那一列。气泡图的面积默认按数值平方铺开所以800万销售额的气泡面积会是200万的16倍视觉对比会非常夸张。建议对数值做开平方处理新增一列“气泡半径”公式是SQRT(业务规模)再把这个列作为气泡大小数据源。这样气泡面积和业务规模近似成正比视觉上不骗人。4. 让图表学会“说话”颜色、标签和版面4.1 四象限填充色与前背景配色建议一份合格的波士顿矩阵图不是只靠黑白的点线就完事了。四个象限最好有不同的底色或边框色让读者一眼就能按区域对照策略。推荐一套低饱和度的配色方案打印和投屏都友好明星象限右上浅绿色填充代表投入和成长金牛象限左下浅蓝色填充代表现金和稳定问号象限左上浅橙色填充代表不确定和观察瘦狗象限右下浅灰色填充代表收缩和退出在Excel里添加象限背景色的方法常见的有两种。第一种插入矩形形状手动放置在绘图区的对应区域把填充透明度调到80%左右。优点是操作简单缺点是不会随着坐标轴范围的变化自动缩放。第二种设置网格线颜色的同时用单元格条件格式做一个“隐藏数据区”来模拟底色技术上更高级但操作复杂。对一份静态报告来说第一种完全够用。我的个人习惯是用色块填充点名象限但让色块半透明确保数据点和坐标轴刻度始终清晰可见。透明度用80%-85%之间具体值看投影环境。4.2 标签碰撞处理业务多的时候怎么办业务线少比如六条以内手动拖开标签就好。一旦业务线超过10条标签必然会打架。这时候有几个策略策略一只标“需要关注”的业务。明星和问号是战略重点金牛保持标签瘦狗如果数量多可以合并成一个“其他”。策略二用引导线拉出标签。右键数据点→“添加数据标签”→“更多选项”→勾选“显示引导线”但Excel的引导线只在你手动拖动标签时才会出现所以还是要手动拖一次。策略三图表下方加文字说明。不放点标签而是在图例或备注里写“即饮咖啡、茶饮料分别位于右上、左上”干净且专业。我在实际项目里最常用的是策略一。原因很简单老板看图的目的是做决策不是考眼力把次要业务隐藏掉反而更能突出结论。4.3 从Excel图表到PPT的完整迁移方案图表做完后复制粘贴进PPT是最后一步但这里也有讲究。推荐方式在Excel里选中图表按CtrlC复制去PPT里选择“粘贴”→“保留源格式并嵌入工作簿”。这样做的最大优势是以后PPT里的图和Excel源文件联动双击PPT里的图表可以直接改数据、更新图形。缺点是如果你后续修改了Excel源文件PPT里不会自动同步需要重新复制一次。如果PPT要发给外部客户防止对方看到你的源数据建议用“粘贴为图片”选“增强型图元文件”。这个格式放大缩小都清晰而且不会暴露数据表。还有一种情况是导出高分辨率图片右键图表→“另存为图片”选PNG格式分辨率选“屏幕”或“打印”。但如果只是PPT内嵌显示高质量屏幕分辨率完全够用。5. 实操中一定会遇到的5个经典问题5.1 散点图只有1个点或全部消失出现这种问题九成是数据区域选错了或者数据本身被Excel识别成了文本格式。排查方式选择数据源看X轴系列的“系列值”引用范围。如果引用范围里混入了文本表头直接报错如果数据列被设为文本格式即使数字显示正常Excel也画不出来。处理办法选中那一列用“分列”功能强制转换为数值格式快捷键AltAE再做图。5.2 误差线的方向不对只往右边或者只往上边延伸误差线默认就是“正偏差”只往外侧延伸。你要的全部覆盖式十字线必须把方向改成“正负偏差”。另外如果在散点图上有多个系列误差线可能会被同时加到多个系列上或者加错系列。最稳妥的操作方式是先点击某一个数据点确认系列名称后再点击“添加误差线”在设置面板里通过顶部下拉框确认你现在设置的是哪个系列。5.3 坐标轴交叉之后横轴出现负值区域前面说了这是正常的。如果不想让小于0的相对市场份额出现在坐标轴上手动修改横轴边界最小值。有些人会想“能不能把负值区域隐藏且不压缩图的比例”其实没必要担心边界设为0后图会自动重排。同理如果纵轴出现-20%这种没意义的负数把边界最小值调到“自动”以外填一个合理的下限。5.4 气泡图打印出来颜色失真Excel默认的气泡颜色是比较亮的单色打印或投屏时容易刺眼。建议用单色系渐变色或统一加边框。具体操作右键气泡→“设置数据系列格式”→“填充”选择“纯色填充”或“图案填充”同时把边框色选成和背景接近的颜色视觉会干净很多。5.5 表格数据更新后坐标轴分割线位置不会自动变化如果你把增长率分割值、相对市场份额1这些参数直接写在坐标轴设置里那么数据更新后这些分割值不会自动跟随。这一点必须提前想清楚。我在做模板时会把“分割值”单独放在工作表顶部的两个单元格里每次刷新数据后报表人员只需要手动改这两个单元格里的值再双击坐标轴同步一下交叉值。步骤上做不到一步自动联动但10秒钟能搞定说实话能接受。6. 进阶技巧动态切换和自动化标注6.1 用Excel表格和切片器实现动态切换如果你的公司有多条业务线、多年份数据可以做一张自动切换年份的波士顿矩阵。做法把所有年份数据堆叠在同一个Sheet加一列“年份”选中数据区域后按CtrlT转换为Excel表格。插入图表后在“插入”选项卡里找到“切片器”选择“年份”字段。这样切片器上点哪一年图表和表格会同时联动刷新。这个功能的实用价值很大年度战略会时你不需要做三年三张图一张图配合切片器就能快速演示。动态图给老板留下的印象远好于放三张静态PPT。6.2 用辅助列自动判断业务落位写在表格里图表有图表的表达表格有表格的表达。我习惯在每个业务线后面加一列“象限判断”公式长这样IF(AND(E21,F20.1),明星,IF(AND(E21,F20.1),金牛,IF(AND(E21,F20.1),问号,瘦狗)))其中E列是相对市场份额F列是增长率0.1是增长率分割线。填好公式后配上条件格式让“明星”两个字显示绿色、“金牛”显示蓝色、“问号”显示橙色、“瘦狗”显示灰色。这样即使不看图表直接在数据表里扫一眼“象限判断”列每条业务线的落位一目了然。报告里图表和数据表配合着看说服力直接翻倍。6.3 模板化把做好的图沉淀成可复用文件你辛辛苦苦做完这张图如果不沉淀成模板下一次分析要重做太亏了。我的做法是把数据表、辅助列、象限判断公式、散点图全部放在同一个Sheet里另存为xlsx模板文件。新一期数据来了之后直接把原数据替换到“数据”区域图表会自动更新点坐标。唯一需要手动处理的是坐标轴交叉值以及在“增长率分割线”单元格里改数字。误差线已经设好的固定值会保持原样不需要重设。如果你经常要做同类分析甚至可以考虑把这个模板分享给同事统一全公司的分析口径。这件事看上去很小但对团队分析效率的提升是实打实的。最后再说一个我自己的习惯所有供汇报用的波士顿矩阵图做完后我都会把坐标轴的“主要网格线”去掉只保留分隔四象限的十字线。网格线看似无关紧要但实际在投屏时会分散注意力让图显得很乱。去掉网格线、保留十字读者第一眼就聚焦在象限划分上图表的说服力反而更强。波士顿矩阵图不复杂它难的是有没有真正理解每个设置背后的逻辑。你照着步骤做一遍之后不妨再想想如果横轴换成“渗透率”、纵轴换成“用户满意度”这套做法是不是同样适用——想通了你才算真正掌握了这种四象限分析图的制作能力。
Excel波士顿矩阵图一直是商业分析里的高频需求几乎每份战略汇报PPT里都会出现一张。但真正能用手头Excel做出来的人说实话不多——大多数教程要么只讲原理不讲操作要么上来就让你装各种插件最离谱的是直接让你画个四宫格填颜色那是示意图不是数据分析图。我这次把实际做图的完整流程拆开讲透从数据口径怎么定、坐标轴怎么交叉、误差线怎么设到最后标签怎么摆、怎么复用模板全部走一遍。你手头只要有Excel 2016以上版本跟着做完就能得到一张完全可以放进周报、复盘会、甚至对外PPT里的波士顿矩阵图。先说清楚适合谁看做业务分析、产品规划、经营管理、咨询顾问的朋友以及所有需要在Excel里动手做“四象限分析”的人。这篇文章不涉及任何插件加载全部用Excel原生功能实现你学到的是原理而不仅仅是点按钮的步骤所以就算换到WPS或Mac版Excel思路完全通用。1. 波士顿矩阵图是什么为什么非用Excel做不可1.1 不是画个四宫格就叫波士顿矩阵很多人一听到波士顿矩阵第一反应是“在PPT里画个2x2的表格把业务填进去”。这种做法的确快但问题很大格子里的位置全凭感觉两个业务明明差距很大放在同一个格子边缘和角落看起来没区别数据一更新整张图就得重新手画。真正的波士顿矩阵图本质是一张以两个连续指标为坐标轴的散点图业务在图上是一个个有实际坐标的点落在哪个象限是数据说了算不是手动拖进去的。这套分析框架是波士顿咨询公司的布鲁斯·亨德森在1970年代提出的核心就两个维度横轴叫相对市场份额纵轴叫市场增长率。这两个维度组合出四个象限高增长高份额明星、低增长高份额金牛、高增长低份额问号、低增长低份额瘦狗。每个象限对应不同的资源策略这也是它一直被沿用至今的原因——不是因为它画得好看而是因为它把“钱往哪里投”这个决策压缩成了一张二维图。用生活化的类比来理解你可以把公司的业务组合想象成一个花园。明星业务是正在快速生长的果树要浇水施肥金牛业务是已经成熟的果树只管摘果子问号业务是刚发芽但不知道能不能成活的种子要挑着养瘦狗业务是病恹恹的枯枝该修剪就修剪。波士顿矩阵图就是把这个花园的状态拍成一张快照让你一眼看出每棵树的状况。1.2 为什么用Excel而不是专业可视化工具很多专业BI工具都能做散点图但Excel在商业分析场景里依然是首选原因很朴素第一数据从业务系统导出来就是Excel格式不需要额外打通数据管道第二做出来的图表和PPT无缝衔接复制粘贴就能用第三Excel里改一个数字图自动变不用重新导入导出第四你的同事、老板、客户电脑上大概率都有Excel不需要考虑对方有没有装Tableau或者Power BI。但还有一个更实际的原因波士顿矩阵图的制作过程本身就是一个数据梳理过程。你得先想清楚“相对市场份额怎么算”“市场增长率取哪段时间”“分割线定在什么位置”这些口径问题在Excel里做的时候会被强制面对而用BI工具拖拽字段很容易绕过思考做出一张好看但没有业务依据的图。1.3 做图前你必须明白的四个象限判断规则先看一张速查表后面做图时你会反复用到它象限市场增长率相对市场份额策略倾向明星高高加大投入保持领先金牛低高维持份额收割利润问号高低选择性投入观察潜力瘦狗低低收缩或退出注意这里的“高”和“低”不是相对于所有业务的平均值而是相对于你设定好的分割线。分割线怎么定是后面第二步的关键这里先按住不表。2. 做图前先花10分钟把数据口径理清楚2.1 理解两个核心指标的计算口径波士顿矩阵的横轴“相对市场份额”标准定义是“本公司某项业务的市场份额 ÷ 最大竞争对手的市场份额”。为什么不用绝对市场份额而用相对值因为绝对市场份额无法反映你在市场里的竞争地位一个10%的份额在小众市场可能是老大在巨头林立的大市场里可能连前三都进不了。相对市场份额以1为分界大于1说明你是市场领导者小于1说明你是挑战者或跟随者。这个“1”就是天然的横轴分割线。纵轴“市场增长率”相对简单通常是近两年或近三年的业务增长率。分割线一般取行业平均增长率也可以用公司整体增长率作为参照。这个值没有唯一标准答案核心是让分割线有业务依据而不是拍脑袋。做图的时候这条分割线就是Y轴的交叉点位置。2.2 一个可以直接套用的最小数据表我用一个简单的例子带你走流程。假设你是一家消费品公司的分析人员手上有6条业务线业务线市场份额最大对手份额相对市场份额近两年增长率业务规模销售额万元即饮咖啡25%15%1.6732%800功能饮料18%22%0.828%450瓶装水35%20%1.754%1200茶饮料9%16%0.5624%300植物蛋白饮料12%10%1.20-3%200碳酸饮料5%30%0.17-8%150数据表里“相对市场份额”不是原始数据是用“市场份额 ÷ 最大对手份额”算出来的Excel里直接写公式就行。如果你手头只有销售收入和市场总规模可以改成“业务收入 ÷ 最大对手收入”如果你在分析产品线而不是业务线维度上同理但数据来源得换成产品维度。口径只要统一矩阵图的逻辑不会变。行业平均增长率我取10%作为纵轴分割线。那么在这个例子里即饮咖啡和瓶装水是明星/金牛茶饮料是问号其余是瘦狗。看上去很合理但别急着下结论等图做出来再说。2.3 确认坐标轴分割线的两个关键值到这一步你需要明确两个数字横轴分割线相对市场份额 1这里天然固定不需要额外计算纵轴分割线市场增长率 10%你自己定的行业平均增长率这两个值在后面设置坐标轴交叉点的时候要用到。还有一个隐藏参数横轴显示范围。默认情况下Excel的坐标轴范围是自动的但为了让四个象限在视觉上比例协调我通常把横轴范围设在0到3之间纵轴范围设在-15%到40%之间。这样分割线大致落在图的中部看起来最舒服。3. 核心制作步骤散点图、误差线和坐标轴交叉3.1 插入散点图并设置数据系列打开Excel把上面的数据表复制成Sheet“数据”。选中“相对市场份额”和“近两年增长率”两列数据只要数据区域不要表头点击菜单栏“插入”→“散点图”→“仅带标记的散点图”。这时你会得到一个什么都没有的空白散点图先别慌这是正常现象——空白的原因是Excel可能把第一列当成X轴序列了需要手动修正。在图表空白处右键“选择数据”→“添加数据系列”。系列名称写“业务组合”X轴系列值选“相对市场份额”那一列不含表头Y轴系列值选“近两年增长率”那一列。确认后图表里会出现6个点。每一个点就是一条业务线但目前它们长得一模一样后面我会教你如何把业务名称标上去。为什么必须用散点图而不用折线图和柱状图因为散点图的本质是用两个连续变量定位一个点正好对应波士顿矩阵的坐标逻辑。折线图强调趋势柱状图强调大小都不适合表达“二维定位”。3.2 用误差线画出四象限分割线这一步是整个做图过程的关键也是最容易把人绕晕的地方。选中刚才的散点图数据点Excel会显示出“图表工具”选项卡。点击“添加图表元素”→“误差线”→“其他误差线选项”此时Excel会给你的数据点添加水平和垂直两组误差线。默认情况下误差线是“正偏差”——只会往一个方向延伸这不对我们要的是从每个点向四个方向延伸的十字线。在Excel右侧的“设置误差线格式”面板里水平误差线和垂直误差线要分别设置水平误差线X轴方向选“正负偏差”末端样式选“无端帽”误差量选“固定值”填入一个覆盖整个坐标轴宽度的数。垂直误差线Y轴方向选“正负偏差”末端样式选“无端帽”误差量选“固定值”同样填入一个覆盖整个坐标轴高度的数。问题是这个“固定值”填多少误差线的特点是它以数据点为中心向两边各延伸你填写的值。如果横轴设计范围是0到3中心分割线在1那么想让误差线覆盖整个坐标轴固定值需要填max(1-0, 3-1) 2。实际上你可以填个更大的数比如10误差线会超出图表边界但Excel会自动裁剪到绘图区边缘效果等同于画了一条贯穿整个图表的横线。这个方法我叫它“暴力覆盖法”简单粗暴但极其好用。填完后你会看到每条业务线的点上都长出了十字线它们叠加在一起会变成一团乱麻。但别急下一节通过坐标轴交叉设置乱麻会自动收敛成一个标准的四象限十字。3.3 坐标轴交叉设置把分割线拨到正确位置误差线画出来了但分割线还没有归位。这时需要调整坐标轴的交叉点双击水平轴即X轴在右侧“设置坐标轴格式”面板中找到“纵坐标轴交叉”分组点选“轴值”填入你要的增长率分割值。在这个例子里是0.1因为Excel里10%要写成0.1。这时候Y轴的分割线就位移到了10%的位置。同样的双击垂直轴即Y轴在“横坐标轴交叉”中填入横轴分割值1。这时候X轴的分割线就位移到了相对市场份额为1的位置。到这里一个四象限十字已经完美落在图中央。原理上误差线画的是“数据点向外延伸的物理线段”坐标轴交叉设置调整的是“坐标轴原点的显示位置”两者配合才能让分割线显示在正确位置。而业务数据点本身并不受分割线影响它依然按照真实坐标显示该在哪就在哪。3.4 调整坐标轴边界和刻度让图更规范上面设置完之后横轴可能显示负数区间。这是正常现象因为横坐标轴交叉到1之后左侧自然会出现0到1的负向区域但相对市场份额小于0没有实际意义。右键水平轴在“设置坐标轴格式”里把“边界”的最小值改为0最大值改为3如果想让图标紧凑也可以把最大值改成2.5。同样垂直轴的最小值改成-0.15最大值改成0.4这样增长率显示的上下留白比较均匀。此时横轴刻度是0、1、2、3纵轴刻度是-15%、0%、10%、20%、30%、40%。两个分割线分别落在1和10%的刻度上视觉上四象限就出来了。如果你觉得默认刻度太密可以在“刻度线间隔”中调整比如纵轴改成0.1间隔即每隔10%一个刻度。3.5 标注业务名称消除“点是谁”的困惑现在图上有6个点但你分不清哪个点对应哪条业务线。选中数据点点击“图表工具”→“添加图表元素”→“数据标签”→“右侧”。默认显示的标签是Y轴数值比如32%、8%这不是我们想要的名字。右键任意数据标签选择“设置数据标签格式”勾选“单元格中的值”然后在弹窗中选择“业务线名称”那一列。取消勾选“Y值”和“X值”只保留“单元格中的值”。确认后每个点旁边会出现对应的业务名称。这个功能Excel 2013以上都支持WPS最近几个版本也有。标签加完后可能会出现重叠——尤其当两个点坐标接近时。手动把标签拖开即可。这个动作虽然看起来费时间但对最终报告效果的影响非常大我见过太多图因为标签叠得乱七八糟直接被老板打回重做。3.6 按业务规模调整气泡大小进阶如果你不满足于简单的散点图想把“业务规模”这个第三维信息也放进去可以把图表类型换成气泡图。做法是右键图表→“更改图表类型”→“气泡图”。然后在“选择数据”中将“气泡大小”系列值指向“业务规模”那一列。气泡图的面积默认按数值平方铺开所以800万销售额的气泡面积会是200万的16倍视觉对比会非常夸张。建议对数值做开平方处理新增一列“气泡半径”公式是SQRT(业务规模)再把这个列作为气泡大小数据源。这样气泡面积和业务规模近似成正比视觉上不骗人。4. 让图表学会“说话”颜色、标签和版面4.1 四象限填充色与前背景配色建议一份合格的波士顿矩阵图不是只靠黑白的点线就完事了。四个象限最好有不同的底色或边框色让读者一眼就能按区域对照策略。推荐一套低饱和度的配色方案打印和投屏都友好明星象限右上浅绿色填充代表投入和成长金牛象限左下浅蓝色填充代表现金和稳定问号象限左上浅橙色填充代表不确定和观察瘦狗象限右下浅灰色填充代表收缩和退出在Excel里添加象限背景色的方法常见的有两种。第一种插入矩形形状手动放置在绘图区的对应区域把填充透明度调到80%左右。优点是操作简单缺点是不会随着坐标轴范围的变化自动缩放。第二种设置网格线颜色的同时用单元格条件格式做一个“隐藏数据区”来模拟底色技术上更高级但操作复杂。对一份静态报告来说第一种完全够用。我的个人习惯是用色块填充点名象限但让色块半透明确保数据点和坐标轴刻度始终清晰可见。透明度用80%-85%之间具体值看投影环境。4.2 标签碰撞处理业务多的时候怎么办业务线少比如六条以内手动拖开标签就好。一旦业务线超过10条标签必然会打架。这时候有几个策略策略一只标“需要关注”的业务。明星和问号是战略重点金牛保持标签瘦狗如果数量多可以合并成一个“其他”。策略二用引导线拉出标签。右键数据点→“添加数据标签”→“更多选项”→勾选“显示引导线”但Excel的引导线只在你手动拖动标签时才会出现所以还是要手动拖一次。策略三图表下方加文字说明。不放点标签而是在图例或备注里写“即饮咖啡、茶饮料分别位于右上、左上”干净且专业。我在实际项目里最常用的是策略一。原因很简单老板看图的目的是做决策不是考眼力把次要业务隐藏掉反而更能突出结论。4.3 从Excel图表到PPT的完整迁移方案图表做完后复制粘贴进PPT是最后一步但这里也有讲究。推荐方式在Excel里选中图表按CtrlC复制去PPT里选择“粘贴”→“保留源格式并嵌入工作簿”。这样做的最大优势是以后PPT里的图和Excel源文件联动双击PPT里的图表可以直接改数据、更新图形。缺点是如果你后续修改了Excel源文件PPT里不会自动同步需要重新复制一次。如果PPT要发给外部客户防止对方看到你的源数据建议用“粘贴为图片”选“增强型图元文件”。这个格式放大缩小都清晰而且不会暴露数据表。还有一种情况是导出高分辨率图片右键图表→“另存为图片”选PNG格式分辨率选“屏幕”或“打印”。但如果只是PPT内嵌显示高质量屏幕分辨率完全够用。5. 实操中一定会遇到的5个经典问题5.1 散点图只有1个点或全部消失出现这种问题九成是数据区域选错了或者数据本身被Excel识别成了文本格式。排查方式选择数据源看X轴系列的“系列值”引用范围。如果引用范围里混入了文本表头直接报错如果数据列被设为文本格式即使数字显示正常Excel也画不出来。处理办法选中那一列用“分列”功能强制转换为数值格式快捷键AltAE再做图。5.2 误差线的方向不对只往右边或者只往上边延伸误差线默认就是“正偏差”只往外侧延伸。你要的全部覆盖式十字线必须把方向改成“正负偏差”。另外如果在散点图上有多个系列误差线可能会被同时加到多个系列上或者加错系列。最稳妥的操作方式是先点击某一个数据点确认系列名称后再点击“添加误差线”在设置面板里通过顶部下拉框确认你现在设置的是哪个系列。5.3 坐标轴交叉之后横轴出现负值区域前面说了这是正常的。如果不想让小于0的相对市场份额出现在坐标轴上手动修改横轴边界最小值。有些人会想“能不能把负值区域隐藏且不压缩图的比例”其实没必要担心边界设为0后图会自动重排。同理如果纵轴出现-20%这种没意义的负数把边界最小值调到“自动”以外填一个合理的下限。5.4 气泡图打印出来颜色失真Excel默认的气泡颜色是比较亮的单色打印或投屏时容易刺眼。建议用单色系渐变色或统一加边框。具体操作右键气泡→“设置数据系列格式”→“填充”选择“纯色填充”或“图案填充”同时把边框色选成和背景接近的颜色视觉会干净很多。5.5 表格数据更新后坐标轴分割线位置不会自动变化如果你把增长率分割值、相对市场份额1这些参数直接写在坐标轴设置里那么数据更新后这些分割值不会自动跟随。这一点必须提前想清楚。我在做模板时会把“分割值”单独放在工作表顶部的两个单元格里每次刷新数据后报表人员只需要手动改这两个单元格里的值再双击坐标轴同步一下交叉值。步骤上做不到一步自动联动但10秒钟能搞定说实话能接受。6. 进阶技巧动态切换和自动化标注6.1 用Excel表格和切片器实现动态切换如果你的公司有多条业务线、多年份数据可以做一张自动切换年份的波士顿矩阵。做法把所有年份数据堆叠在同一个Sheet加一列“年份”选中数据区域后按CtrlT转换为Excel表格。插入图表后在“插入”选项卡里找到“切片器”选择“年份”字段。这样切片器上点哪一年图表和表格会同时联动刷新。这个功能的实用价值很大年度战略会时你不需要做三年三张图一张图配合切片器就能快速演示。动态图给老板留下的印象远好于放三张静态PPT。6.2 用辅助列自动判断业务落位写在表格里图表有图表的表达表格有表格的表达。我习惯在每个业务线后面加一列“象限判断”公式长这样IF(AND(E21,F20.1),明星,IF(AND(E21,F20.1),金牛,IF(AND(E21,F20.1),问号,瘦狗)))其中E列是相对市场份额F列是增长率0.1是增长率分割线。填好公式后配上条件格式让“明星”两个字显示绿色、“金牛”显示蓝色、“问号”显示橙色、“瘦狗”显示灰色。这样即使不看图表直接在数据表里扫一眼“象限判断”列每条业务线的落位一目了然。报告里图表和数据表配合着看说服力直接翻倍。6.3 模板化把做好的图沉淀成可复用文件你辛辛苦苦做完这张图如果不沉淀成模板下一次分析要重做太亏了。我的做法是把数据表、辅助列、象限判断公式、散点图全部放在同一个Sheet里另存为xlsx模板文件。新一期数据来了之后直接把原数据替换到“数据”区域图表会自动更新点坐标。唯一需要手动处理的是坐标轴交叉值以及在“增长率分割线”单元格里改数字。误差线已经设好的固定值会保持原样不需要重设。如果你经常要做同类分析甚至可以考虑把这个模板分享给同事统一全公司的分析口径。这件事看上去很小但对团队分析效率的提升是实打实的。最后再说一个我自己的习惯所有供汇报用的波士顿矩阵图做完后我都会把坐标轴的“主要网格线”去掉只保留分隔四象限的十字线。网格线看似无关紧要但实际在投屏时会分散注意力让图显得很乱。去掉网格线、保留十字读者第一眼就聚焦在象限划分上图表的说服力反而更强。波士顿矩阵图不复杂它难的是有没有真正理解每个设置背后的逻辑。你照着步骤做一遍之后不妨再想想如果横轴换成“渗透率”、纵轴换成“用户满意度”这套做法是不是同样适用——想通了你才算真正掌握了这种四象限分析图的制作能力。
