前阵子给CDA学员做产品组合分析辅导有位做消费电子的运营负责人问我老板丢过来一堆SKU数据要求一周内看清“哪些产品该加预算、哪些该收缩、哪些该淘汰”用什么工具最省事我的第一反应就是Excel波士顿矩阵图。打开Excel把产品数据整理成三个维度半小时内拉出一张气泡图四象限分割线一铺汇报当场就过了。今天就把这套从数据处理到图表成型的完整流程拆给你别的地方你只能搜到零散的“插入气泡图”教程我尽量把计算口径、坐标轴坑和汇报话术一起讲透。1. 动手前先搞懂横轴、纵轴和气泡的业务逻辑很多人在Excel里画波士顿矩阵第一步就歪了不是不会插入气泡图而是把“相对市场份额”“市场增长率”“销售额”这三列塞进去之前根本不知道它们为什么能构成矩阵。先花十分钟把业务逻辑理清后面所有操作都会顺很多。1.1 波士顿矩阵的“两条轴”到底在比什么波士顿矩阵BCG Matrix是战略分析里判断产品组合的工具横轴不是“市场份额绝对数”而是“相对市场份额”也就是本产品市场份额除以市场上最大竞争对手的市场份额。1.0是分界线大于1说明你是市场老大小于1说明你还在追别人。纵轴是市场增长率通常用销售同比增速或行业增速分界线一般取公司整体平均增速也可以用10%这类行业经验值。气泡大小代表该产品产生的销售额或毛利额目的是让同等位置的产品之间还能比出体量差异。所以这张图本质上是在回答两件事这个产品在竞争格局里有没有话语权这个市场还在不在快速长大前者看横轴后者看纵轴。你如果直接把Excel里的“市场份额”塞进横轴不做相对化处理两个不同市场规模的产品可能被画在同一个位置上结论就废了。1.2 一张合格的绘图数据表长什么样我一般会在正式表旁边单独放一张“绘图区”工作表结构如下每列都有明确用途产品线本公司市场份额最大竞争对手份额相对市场份额市场增长率销售额万元智能手环25%20%1.2518%1200笔记本40%35%1.146%8000VR眼镜8%25%0.3220%450智能音箱10%30%0.334%900智能手表30%28%1.0725%2600路由器15%18%0.837%1500注意两个细节。第一相对市场份额这一列要用公式计算比如B2/C2不要手输数值不然数据更新时图表不会跟着动。第二市场增长率这一列建议保留Excel真百分比格式也就是单元格里存的是0.18显示成18%。这样后面设置坐标轴时会省很多事。1.3 相对市场份额和增长率基准线怎么定基准线是波士顿矩阵的灵魂却也是最容易被随手一拍的地方。我的经验是相对份额的基准线固定取1不要取均值因为“是否大于最大对手”是竞争格局的分界跟样本分布无关。增长率的基准线则建议先用AVERAGE(增长率区域)算一下整体均值再和10%这种行业惯例对比看哪个更贴近业务判断。举例来说如果公司整体增速才5%硬套10%的行业线会把所有产品都划到低增长象限肥猪都能画成瘦狗。这时候取均值作为分界线更合理。另外基准线定了之后要在表格下面用两行单元格把它写出来后面画辅助分割线要直接引用这个数字不要画完图再手动敲。2. 数据整理把散乱业务表加工成矩阵能用的三列结构做图前的数据整理往往是整个过程中最耗时的一步。业务部门给你的数据大概率是订单明细、产品档案、月度报表拼起来的距离“产品线-相对份额-增长率-销售额”四列结构还差几步。这几步做扎实图只是顺手的事。2.1 先做减法用SUMIFS把明细汇总到产品线如果你拿到的是一张订单明细表里面每一行是一笔订单产品线上有几十个SKU那第一步一定是汇总。Excel里最顺手的是SUMIFS比透视表更适合做一次性计算。比如你要算每个产品线今年的销售额可以写SUMIFS(明细!$E:$E, 明细!$A:$A, $A2, 明细!$F:$F, 2025-01-01)前一个参数是求和区域金额列后面的参数对分别是产品线条件、日期条件。如果还要算出去年销售额把日期条件换成去年的区间增长率就是今年销售额/去年销售额-1。这一步做完你手里就只剩产品线级别的干净数据了。2.2 用公式拆出相对市场份额、增长率和象限有了产品线的销售额和市场份额数据相对市场份额就是除法增长率就是减法都没什么可说的。关键是我建议你顺手把“象限”这一列也做出来后面拆系列、配色会非常省事。用IF和AND组合就可以实现IF(AND(D21, E2$I$2), 明星, IF(AND(D21, E2$I$2), 金牛, IF(AND(D21, E2$I$2), 问题, 瘦狗)))这里$I$2是刚刚确定的增长率基准线单元格。Excel的绝对引用务必加好否则下拉填充时基准线会跟着飘。这样每个产品一行结论图表做完之后你还能单独筛出“问题”类产品做专项分析。2.3 绘图前的数据体检快速定位空值和错误值数据量一大总有人把某个产品的增长率漏填或者市场份额输了个文本数字结果画出来的图要么少一个气泡要么气泡全部堆在一起。我的习惯是画图之前先用CtrlG打开定位窗口选择“定位条件→空值”把空白单元格一次性标出来处理再选择“公式→错误”把#DIV/0!、#N/A这类错误值直接干掉。如果你要检查的是某个列里有没有文本型数字可以再加一个辅助列输入ISNUMBER(E2)结果是FALSE的行就是要处理的脏数据。这套动作看起来基础但真的能拦住八成“为什么我的图不对”的问题。2.4 从外部表格拿数据时先做好文本清洗现在很多人拿到的数据一开始是Markdown表格、CSV甚至网页表格直接粘贴进Excel时经常会乱成“一列塞进一个单元格”。我的建议是Markdown表格先复制到纯文本编辑器里把竖线分隔变成Tab分隔再粘贴到Excel或者直接把内容贴进Excel后用“数据→分列”功能按竖线或空格拆开。如果你要处理的是CSV或数据库导出的文件直接用Excel的数据导入向导会比较稳编码选择UTF-8避免中文乱码。至于用Python的pandas库批量读写Excel、用JavaScript的xlsx库在前端生成表格文件这些自动化路子都可以做但最终给到Excel的数据结构必须是上面那张表的样子图表逻辑完全一致。3. 绘制主体用气泡图把矩阵画出来并搞定坐标轴数据表格就绪接下来就是最能体现“熟手和新人差别”的绘图环节。新手往往从头到尾只用会自动弹出的“推荐图表”最后做出来一张自己都看不懂的图老手会先想清楚用哪种图表类型再手动绑定数据区域最后再花五分钟调坐标轴。3.1 为什么我用气泡图而不是普通散点图波士顿矩阵里销售额这个维度最自然的呈现方式就是气泡大小。普通散点图只能表达两个维度你用大小去映射销售额还需要再加工气泡图天生支持三列数据X轴、Y轴、大小。插好后气泡面积和销售额是正相关的读图的人扫一眼就能看出哪个产品是“大块头”。这里有一个很多人没注意的细节气泡图默认用面积代表数值不是用直径。也就是说销售额翻一倍气泡面积翻一倍视觉上不会出现“一个小产品比别人大四倍那么夸张”的失真。如果你想控制整体气泡不要太夸张可以在设置里调整缩放比例这个后面讲。3.2 插入气泡图并绑定X、Y、尺寸三列数据选中绘图区表格里的三列数据也就是相对市场份额、市场增长率、销售额直接点“插入→图表→气泡图”选最基础的那个“三维气泡图”或“气泡图”Excel会自动把第一列当X、第二列当Y、第三列当大小。问题在于自动识别偶尔会选错区域。我一般习惯插入后右键“选择数据”把系列的X轴系列值、Y轴系列值、气泡大小系列值三栏重新指一遍别偷懒。比如X轴值选绘图区!$D$2:$D$7Y轴值选绘图区!$E$2:$E$7气泡大小选绘图区!$F$2:$F$7。这样指认完后续加辅助线、加标签时不容易出现“明明数据没错但图就是不对”的怪事。3.3 坐标轴设置逆序横轴、纵轴基准、对数刻度这一步是整个波士顿矩阵图能不能“成立”的关键。第一横轴必须逆序。波士顿矩阵的惯例是横轴从大到小也就是高份额在左边、低份额在右边。Excel默认递增所以双击横轴在“坐标轴选项”里勾选“逆序刻度值”。如果你忘了整张图的象限位置就会左右翻转汇报时容易闹笑话。第二纵轴最小值不要设为负数一般从0开始。增长率基准线10%应该在图的中间高度附近所以纵轴最大值建议设成25%或30%给上面的气泡留点呼吸空间。第三如果产品相对份额的跨度很大比如最大10、最小0.1横轴建议勾选“对数刻度”否则份额小的产品全挤在最左边。用了对数刻度后基准线1依然成立但你要注意辅助线的数据坐标还是1不用跟着变。3.4 画四象限分割线辅助系列法最省心四象限分割线主要有三种画法直接插入形状、误差线、辅助系列。我的建议很明确在辅助系列和形状之间二选一形状最快但不太智能辅助系列最正规且能随数据自动更新。辅助系列的思路是额外添加两个散点系列每个系列只有两个数据点Excel会把这两个点连成一条直线。比如横线系列的数据点可以写在两个单元格里X值0和2Y值都填增长率基准线10%。竖线系列X值都填1Y值填0和0.3。右键图表“选择数据→添加系列”把横线系列和竖线系列加进去再在这个系列上右键“更改系列图表类型”改成“散点图”。然后给这两个系列设置虚线、隐藏数据点标记四象限的骨架就出来了。如果你第一次操作发现辅助系列的线没有横贯整张图多半是X轴最大最小值没有设成2和0或者辅助系列不小心被分配到了次坐标轴。检查方法很简单点击辅助线的数据点看坐标轴格式里的最大最小值是否和主图一致。如果两条线不在同一个坐标系里立刻右键“设置坐标轴格式”把次坐标轴删掉。4. 让图“会说话”象限配色、产品标签与视觉细节骨架画好了接下来要解决“阅读体验”的问题。一张只有灰色气泡的波士顿矩阵领导还得根据坐标自己猜哪个产品在哪个象限如果每个象限一种颜色每个气泡上直接标产品名那基本不需要额外解释大家马上就能看懂。4.1 按象限拆成四个系列颜色一下就干净了很多人会在全部数据基础上手动改单个气泡颜色这样也能做但效率很低而且以后数据更新还得重新改。我更推荐按象限拆成四个系列。做法很简单在绘图区旁边做四个小区域分别放明星、金牛、问题、瘦狗产品的相对市场份额、增长率和销售额然后右键图表“选择数据”把这四组数据分别添加为四个气泡图系列。添加完成后右键某一个系列设置“填充”把四个象限分别设为不同颜色。我这里常用的是一套比较稳的配色明星用深绿金牛用蓝色问题用橙色瘦狗用灰色。这四个颜色同时用在图例里别人一眼就知道“绿点是要加钱的灰点是要淘汰的”信息传达效率非常高。4.2 数据标签显示产品名称而不是一串数字气泡默认不显示任何文字直接看数据得靠猜。我的做法是右键气泡系列添加数据标签然后右键“设置数据标签格式”勾选“单元格中的值”选择产品名称区域同时取消“Y值”勾选保留“标签位置”为“居中”或“靠上”。这样每个气泡上直接是产品名而不是一串百分比和数字。有一点要提醒你如果你的Excel版本比较老比如2013之前可能没有“单元格中的值”这个选项。替代方案是手动点两次标签然后改文字产品少的时候也够用。产品多的话可以用VBA批量把标签指定成单元格区域这段代码网上很多逻辑也不复杂但新手不建议一上来就碰宏。4.3 气泡大小、标签重叠和图表尺寸的微调策略气泡图默认会自动把最大值撑满整个绘图区有时候销售额差距过大小气泡会变得像针尖一样。双击任意气泡在“系列选项”里找到“将气泡大小缩放为”手动把缩放系数调大小产品的气泡就能勉强看清。我个人习惯是让最大气泡直径不超过绘图区宽度的四分之一这个比例在投影仪上最舒服。标签重叠是几乎一定会遇到的事尤其是明星象限和问题象限的相邻产品。我的笨办法是先把所有标签放到“居中”再挨个点住标签手动拖开。谁要说有自动避让功能那是画大饼Excel没有做这件事。产品特别多的时候也可以考虑只标注明星和问题象限的产品瘦狗本身就是要被淘汰的标不标优先级低。5. 高频故障排查错位、不更新、复制粘贴失灵怎么办凡是做过图表的人都知道最浪费时间的是“图做完了但看不出错在哪”。我把自己和学员踩过的坑集中梳理一下你遇到这些情况时可以直接按清单来查。5.1 所有气泡都挤在左下角先查X轴和Y轴的数值列这个问题八成是数据选错了类型。最常见的是选区域时把产品名称当成了X轴或Y轴的数值列Excel把文本转换成数字时全部按0处理于是所有气泡都堆在左下角。验证方法很简单点击图表里的数据系列看公式栏里的SERIES函数检查前三组引用是不是你想要的数值列。需要改正时右键“选择数据→编辑系列”重新指定X轴和Y轴范围。数据量小的话也可以直接在SERIES公式里手动改引用范围但要小心别把公式改坏。5.2 新增一行产品但图表纹丝不动把数据源变成表格很多人用普通单元格区域画完图下个月新增了一个产品右键“选择数据”去扩展范围图表还是老样子。解决这个问题的绝招是在数据源区域上任选一格按快捷键CtrlT把它变成Excel“表格”再重新添加图表系列。变成表格后Excel会自动扩大图表的数据范围新增行会直接出现在图上。如果你不想改变原表结构也可以用定义名称配合OFFSET函数做动态区域。这个技巧稍微硬核一点但效果一样新数据进来图自动更新不需要每次手动改区域。在给CDA学员讲课的时候我通常建议先用CtrlT简单直接不容易出错。5.3 Excel不能复制粘贴、图表工具消失时的排查思路做波士顿矩阵图时最让人抓狂的是图好不容易做完了想复制到PowerPoint或Word里结果CtrlV没反应。这种情况大多数不是操作问题而是Excel的加载项或剪贴板进程出问题了。你可以按顺序排查先按WinV打开剪贴板历史看看内容有没有被系统吃掉然后关掉Excel重开或者用Windows的剪贴板服务把被占用的缓存清掉。如果复制粘贴问题反复出现同时图表工具选项卡时有时无那基本可以怀疑是加载项冲突。点“文件→选项→加载项→管理COM加载项”把非Microsoft的加载项逐个禁用试试。想快速验证的话可以按WinR输入excel /safe进入安全模式如果复制正常了那就锁定是某个加载项在捣乱。年初我有位学员的Excel加载项被禁用后功能区整个少了一截图表工具也找不到走的正是这个恢复流程。5.4 坐标轴双轨制辅助线跑偏的元凶四象限分割线加了之后如果横线不在增长率10%的位置上或者竖线不在相对份额1的位置上多半涉及坐标轴“双轨制”问题。Excel允许同一个图表里存在主、次两套坐标轴当你把辅助散点系列添加进去时软件有时会默认把它丢到次坐标轴。主次坐标轴的刻度完全可能不同所以两条线就会偏离你的预期。检查方法右键辅助线的某个数据点选择“设置数据系列格式”在“系列绘制在”里确认是“主坐标轴”。同时再双击横轴或纵轴看看有没有一个隐藏的“次坐标轴”被勾选。把辅助线系列改到主坐标轴后分割线就会乖乖回到应该在的位置。6. 从图到汇报四分法的解读口径与一套可复用的报告模板最后一步不是Excel操作却是最重要的这张图画完之后怎么解释给业务方听。很多数据分析师图表做得很漂亮但汇报的时候只会念数据价值立刻打了对折。波士顿矩阵的优势在于四个象限对应着四套明确的策略你只需要把产品和策略一一对号入座结论就有了。6.1 四个象限的标准打法与话术明星产品相对份额高、市场增长快这是未来利润的引擎。策略是继续投入资源扩大优势话术可以说“这个产品所在赛道还在增长我们有领先身位预算应该优先倾斜”。金牛产品份额高但增长慢市场已经成熟。策略是少投入、多收现金流维持住渠道和价格体系就好。这类产品不追求高增长但它是整个公司的利润底盘。问题产品增长快但份额低要不要投入需要看公司能力。策略是选择试错资源有限的情况下集中力量投一两个有希望翻盘的其他的果断放弃。瘦狗产品份额低、增长也慢。策略是收缩、整合、退出不要恋战。放在报告里可以直接说“建议该产品线不再追加预算逐步清库存或合并到其他业务”。6.2 一页纸分析报告的排布顺序我出分析报告时页面排布一般遵循“结论先行图表”的顺序。最上面先写三行带颜色的关键结论比如“建议重点投入智能手环、智能手表建议收缩音箱、路由器”。中间放这张波士顿矩阵图辅助线一定要有名字和图例标注清楚横轴是相对市场份额、纵轴是市场增长率、气泡大小是销售额。图表下面放一张“决策汇总表”每一行一个产品列包含产品名、相对市场份额、市场增长率、销售额、所属象限、建议动作。这样领导不需要反复回看图才能行动。整套表格做好之后我一般存成模板文件下季度数据更新时只需要替换数据源图表和排布全部自动刷新。6.3 扩展思路动态筛选、Python/Pandas与前端工具联动到了这里你已经能做出一张能汇报的Excel波士顿矩阵图了。如果还想让它更高级一点可以考虑加数据条或切片器让图表能按事业部筛选也可以用Python的pandas库自动读Excel、计算相对份额和增长率、再写回Excel表格后面该做的图和手动操作完全一致。我自己在实际项目里有一种更顺手的做法先用SQL或pandas把明细数据汇总到产品线然后用xlsxwriter或openpyxl把绘图区数据直接生成到Excel文件里最后在Excel里完成图表微调和汇报排版。对于只有几百行的产品数据纯Excel就够了一旦数据源在数据库里要每周出报告自动化的价值就会非常明显。最后再分享一个我在实际辅导中反复强调的细节波士顿矩阵图的核心价值不在于气泡画得多精美而在于你把“相对市场份额”“增长率基准线”这些口径讲清楚。我习惯把基准线和个人判断写在绘图区旁边的注释单元格里别人拿到文件后不用追着问自然看得懂。第一次做的人往往会在坐标轴逆序和辅助线归属上卡住不用急按照我上面第3部分的方法一步步拆开查基本十分钟内能解决。