1. 为什么我要用Excel手搓波士顿矩阵图波士顿矩阵BCG Matrix这个东西第一次接触是在做产品线复盘的时候。当时老板丢过来一句把咱们这几条业务线按增长和份额过一遍看看哪些该保、哪些该砍我脑子里第一反应是去找专业BI工具结果发现公司账号没开权限申请流程要走两周。后来用Excel硬生生搭了一版做完发现自己动手画反而更清楚每一格的政策逻辑因为你知道每个坐标轴上的数字是怎么来的。波士顿矩阵的核心逻辑其实很简单用市场增长率当纵轴用相对市场占有率当横轴把业务或产品切成四个象限——明星、现金牛、问题、瘦狗。听起来像市场营销教科书里的老古董但它最实用的地方在于它逼着你把感觉这门生意不错变成这门生意在增长率和占有率这两个可量化维度上到底站在哪。Excel做这件事的优势在于数据就在表里公式一拉图形一改随时能跟着业务变化更新不像PPT里贴的死图改一个数就得重画一遍。这篇文章适合三类人看。第一类是产品经理、市场运营、战略岗需要给老板或团队做业务组合分析但手头没有专业分析工具第二类是在校学生做课程案例、商业计划书时要用到矩阵图又不想被复杂软件卡住第三类是我这种喜欢用Excel把各种管理模型落地的人享受从零搭出一个能复用模板的过程。全文我会从数据准备讲到图形调优中间穿插公式写法、参数设定、样式处理的完整操作也会把我踩过的坑和修正思路一并说清楚。你跟着做一遍最后手里会有一个能改成任何行业的波士顿矩阵模板下次换个数据源就能直接用。2. 波士顿矩阵的底层逻辑与Excel实现路线2.1 四个象限到底在说什么波士顿矩阵的本质是一个二维决策网格。纵轴是市场增长率衡量的是这块业务所处赛道整体在不在往上走横轴是相对市场占有率衡量的是你在赛道里相对最大竞争对手的位置。这里的相对两个字很关键它不是你的绝对份额而是你的份额除以最大竞争对手的份额。用绝对份额会失真因为一个整体规模很小的市场里拿到30%听起来高但可能竞争对手只有你一家在做而一个红海市场里拿20%可能已经是绝对领先。四个象限对应的策略逻辑我习惯用一句话记住明星高增长、高占有赛道在涨你也在领跑需要继续投钱巩固位置短期可能不赚钱。现金牛低增长、高占有赛道成熟了但你位置稳赚的钱要拿出来养明星和问题业务。问题高增长、低占有赛道在涨但你位置尴尬要么加大投入搏一把要么果断退出。瘦狗低增长、低占有赛道没劲你也不占优该考虑收缩或者剥离。注意矩阵的价值不在贴标签而在资源分配。做完图之后如果没有配套的投入/退出动作这张图就只是装饰。2.2 用Excel还是用专业工具我试过用Power BI、Tableau画散点图做矩阵也试过用Python的matplotlib最后在日常汇报场景里还是回到Excel。原因很实际数据源通常是别人发来的Excel改一个假设值要重新导数据、重新跑脚本的链路太长而Excel里改一个单元格整张图跟着动沟通成本最低。用Excel实现波士顿矩阵核心思路是把散点图当矩阵图用。具体做法是准备一张两列数据的表一列是相对市场占有率一列是市场增长率插入散点图把X轴设为占有率Y轴设为增长率在图表中手动添加两条参考线或者通过辅助数据系列画出象限分割线调整坐标轴范围让分割线落在高与低的临界值上给每个点加上业务名称标签再用颜色区分象限。这条路线不依赖任何加载项Excel加载项那套东西在不同版本兼容性经常出问题尤其是Mac版Excel和Windows版的菜单差异很大是最稳的方案。下面几节我把每一步拆开讲。3. 数据准备把原始数字整理成能画图的表3.1 确定要分析的对象和指标口径动手之前先想清楚一件事你分析的点是什么。是产品线、区域市场、业务单元还是客户细分我见过有人把不同层级的东西混在一张图里比如一个点代表华东区另一个点代表某款产品这种图做出来分析意义很弱因为横纵轴的口径对不上。确定对象之后定义两个指标的计算口径市场增长率 本期市场规模 - 上期市场规模/ 上期市场规模通常用行业增长率而不是自家增长率。如果拿不到行业数据退而求其次用自家增长率但要在图上注明口径。相对市场占有率 自家市场份额 / 最大竞争对手市场份额。如果你的份额是20%最大竞争对手是40%相对占有率就是0.5。下面是我常用的一张数据准备表结构你可以直接照着搭业务单元自家销售额(万)市场份额最大竞对份额相对占有率本期行业规模(亿)上期行业规模(亿)市场增长率A产品线480024%40%0.602017.414.9%B产品线320016%16%1.001210.910.1%C产品线210010.5%28%0.37585.837.9%D产品线15007.5%32%0.23465.75.3%这张表里相对占有率和市场增长率是最终要喂给图表的两个字段其他列是支撑计算的过程数据。我习惯把过程数据和绘图数据放在同一个工作表的不同区域中间留空行避免误操作。3.2 用公式把指标算出来手动一个个除很容易错尤其是业务线多的时候。直接在Excel里写公式改数据自动重算。相对占有率列假设在第E列第2行D2/MAX($D$2:$D$5)这个写法是拿每行的市场份额除以所有业务中最大的市场份额。要注意如果你的最大竞争对手份额是单独一列代表外部对手就不该用自家列的最大值而应该用对手列的最大值公式改成E2/MAX($E$2:$E$10)市场增长率列假设上期在第G列本期在第H列(H2-G2)/G2写成百分比格式后记得把单元格格式设成百分比、保留一位小数不然图轴上的标签会显示成一长串小数。提示相对占有率大于1意味着你是市场第一。有些人会把横轴做成反向轴左高右低模仿经典BCG图的布局但具体哪种我不强求取决于你汇报对象的阅读习惯。3.3 处理异常值和缺失数据实际操作中最烦的不是算指标而是数据不干净。我遇到过的坑归纳起来有三类第一类是数据粘贴不进去。尤其是从网页或别的系统里复制表格粘到Excel变成一坨。Mac版Excel和Windows版在这方面表现不一样有时候复制了没反应有时候格式全乱。稳妥做法是用选择性粘贴-文本或者先粘到记事本再复制过来也可以用Power Query导入数据-获取数据-来自文件后面的数据刷新都靠它省事。第二类是分母为零。上期行业规模如果是0或空除法会报错图上出现#DIV/0!这类错误标记。用IFERROR包一层IFERROR((H2-G2)/G2, )空值处理成空字符串画图时可以用筛选把空候选剔除。第三类是量纲不一致。有的业务用万元有的用亿元直接算份额就会出错。统一量纲这件事看起来简单但最容易翻车我一般会在表头写清楚单位并在旁边加一个校验列用条件格式把异常值标红。4. 散点图到矩阵图的完整实操4.1 插入散点图并绑定数据数据准备好之后选中相对占有率和市场增长率两列包括表头走菜单插入 - 图表 - 散点图 - 只带数据标记的散点图。选这个而不是带线的是因为我们要的是矩阵分布不是趋势线。插进来之后大概率会有一个问题X轴和Y轴的数据可能绑反了或者只绑了Y没绑X。右键图表 - 选择数据在弹出的对话框里把X轴系列值指向相对占有率那一列Y轴系列值指向增长率那一列。这里有个细节X轴值必须选具体单元格区域不能直接写A产品线这种文字。绑完之后你会看到点散落在坐标平面上但还看不出象限。下一步就是画分割线。4.2 用辅助系列画出象限分割线画分割线有个笨办法是手动插直线图形但那样不随图表缩放走动一下图线就歪了。正确做法是用额外的数据系列画线。先确定两个临界值。相对占有率的临界值一般取1.0也可以取整个行业的平均份额市场增长率的临界值一般取行业平均增长率或GDP增速比如5%。以市场增长率临界值5%为例画一条水平分割线。你需要构造一段数据X辅助Y辅助05%25%其中X的0和2要覆盖你X轴的实际范围。如果你的相对占有率最大到1.5X就写0和1.5即可。选中这两行两列的数据复制然后右键图表 - 选择数据 - 添加 - 系列名称写增长率分界线X轴系列值选0和2那一列Y轴系列值选5%、5%那一列。同理画垂直分割线用一堆点表示X1的那条竖线X辅助Y辅助10%140%Y的范围要覆盖你Y轴的最大最小。这两条线一开始会显示成点右键这个系列 - 更改系列图表类型 - 选带直线和数据标记的散点图或者右键该系列 - 设置数据系列格式 - 线条选实线、标记选无就变成一条干净的分割线了。注意分割线的两个端点数据一定要超出你的坐标轴范围否则线画不满整个图。4.3 调整坐标轴范围让象限分布均匀默认的坐标轴范围是Excel自动算的经常让点挤在一个角落。右键X轴 - 设置坐标轴格式把最小值设为0最大值按实际情况定比如1.5或者2.0。Y轴最小值如果是正增长可以设0如果有负增长情况要设成负数比如-10%。坐标轴交叉点的设置也很关键。如果想让分割线正好在图上呈现十字分割可以让坐标轴交叉在边界值上但这跟前面画的辅助线容易叠在一起。我的习惯是辅助线画一条坐标轴范围手动定保持简洁。横轴的刻度单位建议设成0.5纵轴设成5%或10%这样读者一眼能对应上数值。4.4 给每个点加业务名称标签这一步Excel的原生支持很弱散点图默认不带数据标签带标签也只显示数值。要显示业务名称有两条路路线一用XY Chart Labeler加载项。这是个老牌的第三方工具装上之后可以批量把某个区域的文字指定为数据点标签。但加载项在不同Excel版本和操作系统上安装方式差异很大企业环境里没管理员权限可能装不上所以我在Mac上加装的时候折腾了很久。路线二手动逐个加标签再改内容。点中某个数据点右键 - 添加数据标签然后把标签内容改成一个引用单元格。具体操作是选中标签 - 单击进入编辑状态 - 在编辑栏里输入然后点你要引用的业务名称单元格 - 回车。这样标签就绑定了单元格文字。业务点少的时候10个以内这个方法最快。路线三用VBA批量加。如果你经常做这张图写一段简短的宏能省不少事。核心逻辑是遍历每个数据点把对应名称写进标签。代码大概是这样Sub AddLabels() Dim srs As Series Dim p As Point Dim i As Integer Set srs ActiveChart.SeriesCollection(1) For i 1 To srs.Points.Count srs.Points(i).HasDataLabel True srs.Points(i).DataLabel.Text Cells(i 1, 1).Value Next i End SubCells(i1,1)这里假设业务名称在A列从第2行开始。运行前先选中图表不然ActiveChart会报错。这种批处理思路在Excel里通用遇到需要重复改格式的场合都能套。4.5 用颜色区分象限给不同象限的点上不同颜色视觉上比全黑的点清晰得多。手工改是选中单个点 - 设置数据点格式 - 填充颜色。点多了就烦了。更省事的做法是按象限拆成四个数据系列每个系列一种颜色。具体是在数据准备表里加四列用公式把不属于该象限的值变成空IF(AND($E21,$H25%),$H2,)这段公式的意思是如果相对占有率≥1且增长率≥5%就显示增长率值否则为空。四个象限各写一个类似的公式然后把这四列分别作为四个系列加进图表。空值的点不会显示天然实现了按象限分色。这个方法的额外好处是你在图例里能直接看到明星现金牛等字样比手工加图例省事。5. 参数临界值到底怎么定5.1 增长率临界值的常见取值临界值定在哪里直接决定了每个点落在哪个象限。增长率这条线我见过几种做法取GDP增速或者行业平均增速。这是最主流的做法业务增速高于大盘算高增长。比如当前行业平均是5%临界值就取5%。取企业自身历史平均增速。适合企业内部对标比的是跟过去的自己比。取中位数。当业务数量多、分布不均时用中位数能把点均匀分到两边避免大部分点落在一侧。这三种没有绝对优劣。我做快速诊断时用行业平均做内部资源复盘时用自身中位数汇报时会把口径明确写在图下方。5.2 相对占有率临界值为什么默认取1相对占有率取1含义是你和最大竞争对手打平。高于1说明你是第一低于1说明你在追赶。这个值直观、好解释所以是行业共识。但在某些市场里第一名和第二名差距很大取1会让绝大多数点都落在左下。这时候可以用更灵活的临界值比如取全部业务相对占有率的中位数或者取某个跬步区间。关键在于临界值一旦定下全公司这张图都要用同一套口径不能这个季度用1、下个季度用中位数否则趋势对比就失效了。5.3 参数敏感性分析我做过一个小实验把同一个业务组合的增长率临界值从5%调到8%原本三个明星有两个掉进了现金牛因为它们增长率在6%左右。这说明临界值的选择会显著影响结论。所以做完图之后别急着下判断。我习惯在旁边做一列敏感性对照把临界值上下浮动一个区间看看哪些点会翻象限。翻来翻去稳如泰山的点战略判断可以更笃定在边界上反复横跳的点说明它本来就处于过渡带投入决策要更谨慎。业务增长率临界值5%时象限临界值8%时象限是否稳定A产品线14.9%明星明星稳定B产品线10.1%明星明星稳定C产品线37.9%问题问题稳定D产品线5.3%明星(临界)现金牛不稳定这张对照表比矩阵图本身更能看出门道。D产品线卡在临界值附近说明它的增长动力并不牢靠值得单独复盘。6. 图表美化与呈现技巧6.1 让象限区域有底色纯白背景的矩阵图看起来干巴巴的。给四个象限铺上浅浅的底色读者能一眼分清区域。做法不是用图形盖而是插入四个矩形形状设置无边框、填充浅色明星用浅黄、现金牛用浅绿、问题用浅蓝、瘦狗用浅灰然后把它们对齐到四个象限的位置最后把形状置于图表底层右键 - 置于底层。这里的关键是形状要随图表位置固定最好把图表和形状组合起来按住Ctrl多选后组合移动图表时形状跟着走。不然拖个图底色就跟你玩失踪。6.2 网格线和坐标轴的取舍Excel默认的网格线有点抢戏。我的处理是右键网格线 - 删除只保留分割十字线图面立刻干净。坐标轴的数字可以设成浅灰色不要用纯黑避免视觉喧宾夺主。坐标轴标题一定要加提醒读者X轴是相对市场占有率、Y轴是市场增长率。很多时候图很好看但忘了写轴名看的人得猜。6.3 气泡大小作为第三维度如果你的业务有第三个值得展示的指标比如营收规模、客户数可以用气泡图代替散点图。插入 - 图表 - 气泡图第三个维度绑定营收列气泡越大代表营收越多。用气泡图有个小坑气泡面积不容易精确估读容易让人误判。所以在汇报时我一般会在旁注写明气泡大小代表营收规模仅为示意具体数值见图旁表格。6.4 导出和复用的注意事项图做完之后通常要导出到PPT或文档里。右键图表 - 复制粘到PPT时选保留源格式并嵌入工作簿这样数据还能双击修改。如果只是想给静态图选粘贴为图片。我个人更推荐嵌入工作簿版本因为老板经常现场要求把B产品的增长调成8%再给我看看这时候嵌入式版本直接双击改数据现场就能响应。6.5 Mac版Excel的差异点如果你用Mac版Excel有几处体验和Windows不同得提前有个心理准备图表元素菜单藏在图表设计选项卡里不如Windows直观数据标签引用的编辑方式在Mac上需要双击标签进入编辑状态再点公式栏操作路径更长部分加载项不可用所以前面提到的XY Chart Labeler方案在Mac上不保险优先手工或VBA复制粘贴数据偶尔出现可以复制但无法粘贴的情况多见于从其他应用复制的富文本用粘贴为纯文本或先落在文本编辑器再复制可以绕过。这些差异不是功能缺失而是习惯差异。我两个平台都用Max下做矩阵图完全可行只是要多点两下。7. 常见问题排查与避坑清单7.1 点不显示或错位图表里点不见了常见原因有这么几个数据区域里有空值或文本被当成0画到了X0的位置坐标轴范围设得太窄点跑到视野外或者某个系列被设成了无线条无标记。排查顺序是先点开选择数据确认每个系列的X/Y引用范围再检查这几个单元格是不是数字格式。7.2 分割线画不满或被截断前面提过辅助线的端点要超出坐标轴范围。如果线画出来只到一半先看坐标轴的最大值是不是比辅助线的X大。另外辅助系列有时候会被Excel自动识别成不同图表类型记得统一改成散点图。7.3 数据标签重叠业务点密集时标签会叠在一起。手动拖开是最直接的办法但费时间。可以用文本框引线的方式把标签挪到旁边再用线条指过去。点少的时候这样处理视觉效果比自动排布更专业。7.4 更新数据后图不刷新改了下表数据但图没动往往是因为系列引用的是值而不是单元格区域。选择数据里检查每个系列的X和Y引用确保是Sheet1!$E$2:$E$5这种区域形式而不是一串花括号包围的数字。用整列引用或者定义名称会更省心。7.5 一个高频问题速查表现象最可能的原因快速解法点全部堆在原点X/Y引用错位或数据为文本检查系列引用转数字格式图表里多出神秘系列辅助线系列未改类型更改为无标记散点图标签显示为数值未绑定单元格文字编辑栏用引用业务名称象限底色错位形状未与图表组合多选后组合固定复制到PPT后图变模糊粘贴为图片改选嵌入工作簿Mac下粘贴没反应富文本兼容问题先粘到纯文本编辑器增长率算出错误值分母为0或空用IFERROR包裹这张表是我实际做图过程中攒下来的隔一段时间就会遇到其中一两条。7.6 关于抄作业的一点经验我做了这么多年矩阵图发现真正难的不是画图而是把图画对。很多人的图很漂亮但一旦问这个临界值为什么取8%就答不上来。我的建议是图做出来之后自己先反问三句话——临界值凭什么这么定、每个点的位置数据对不对、四象限的结论是否和实际业务直觉一致。三句话都能答上这张图才敢拿去汇报。另外一个小技巧把数据准备表、参数说明、图表放在同一个工作表用批注或单元格写清楚口径。这样你三个月后回头看或者同事接手你的图都能快速理解。我吃过这个亏有次换了个季度更新老图死活想不起来当初临界值取的是行业均值还是GDP增速只能重新问一遍业务口的人很没面子。7.7 让图跟你一起演进的思路波士顿矩阵做完不是终点。我通常会在同一个文件里再留一个季度追踪区域把每个季度各业务的位置记录下来用折线连接同一业务的不同时期点位就能看到它是在象限间移动还是原地不动。这个轨迹比单张静止的图标更有说服力能讲出这个业务正从明星滑向现金牛这种故事资源决策就有了动态依据。这种追踪表用Excel的散点图再叠加一条按业务分组的折线系列就能实现公式略复杂但原理和画分割线一样——都是靠辅助系列。愿意折腾的可以自己演进不愿动手的把基础版练熟也已经能覆盖大多数汇报场景了。