Excel波士顿矩阵图实操：四象限、对数轴与动态更新

做产品线复盘或者年度经营分析波士顿矩阵图几乎是绕不过去的一张图。它拿市场增长率和相对市场份额两个维度把一堆业务、产品线甚至SKU切成明星、金牛、问题、瘦狗四类谁该继续砸资源、谁该收割现金、谁该果断收缩一眼就能看出大概。而绝大多数人落地这张图的方式就是打开Excel拉两列数据画一张散点图。听着简单但真正动手就会发现坑不少横轴到底用线性还是对数、象限分割线怎么画、气泡大小怎么映射销售额、数据一更新图会不会跟着变。这篇就把我这些年做波士顿矩阵图的完整思路、参数口径和Excel实操步骤摊开讲清楚从数据准备到成品出图再到常见故障排查尽量让你照着做就能出图。内容偏实操适合三类人看一是做产品、运营、战略分析需要定期给业务画矩阵图的朋友二是刚入行、对Excel图表还不太熟但手里正好有业务数据要盘的人三是画过但总觉得看着像那么回事实际用起来不顺手的老手。全文基于Excel原生功能不依赖任何插件Mac版和Windows版都能参考。1. 波士顿矩阵图在Excel里到底怎么做才不算白画很多人对波士顿矩阵图的理解停留在四个格子加几个点出图之后往PPT里一贴就完事。但真正被业务方challenge过几次之后你就会明白这张图的杀伤力全在口径上。口径对了它是一张决策图口径糊了它就是一张好看的装饰画。所以第1章先把为什么要这么做讲透后面动手才有依据。1.1 四象限的判定口径两个轴分别代表什么波士顿矩阵的两个维度横向是相对市场份额纵向是市场增长率。注意横轴不是绝对市场份额而是本企业份额 ÷ 最大竞争对手份额。这个换算非常关键因为绝对份额在不同行业不可比——快消行业20%可能只是个小玩家而在某些工业品领域20%已经是龙头。用相对份额跨业务线才有可比性。相对份额的判定线通常取1.0。大于1.0说明你是这个细分市场的老大小于1.0说明你还被对手压着。纵轴的市场增长率分界线没有绝对标准常见的做法有三种取**10%**这个经验值、取行业整体增长率、取所有业务增长率的加权平均。我个人更倾向用加权平均因为它能反映你自己业务组合的实际分布而不是拍脑袋的10%。如果公司有明确的战略目标增长率那直接用目标值当分界线也合理。把两条线一交叉四个象限就出来了。右上角是明星业务高增长、高份额现金牛是左下低增长、高份额问题是左上高增长、低份额瘦狗是右下低增长、低份额。这里有个特别容易搞反的地方横轴左高右低因为相对份额越大越靠左。你在Excel里画图时如果没做处理默认是左小右大出来的象限就全反了这是新手最常翻车的地方后面实操会专门讲怎么改。1.2 为什么大量波士顿矩阵图其实经不起推敲我见过太多版本问题集中在三处。第一处是横轴直接用绝对市场份额图能画出来但一旦产品分属不同行业业务方一眼就能挑出毛病。第二处是没做对数处理。相对份额的分布往往是长尾的有的业务份额是3.0有的是0.15如果横轴用线性刻度0.15和0.3这两档会被挤在一起完全看不出差异而3.0那边却占了半张图。经典BCG矩阵横轴是对数刻度从10到0.1中间点是1.0这样左右对称视觉上才公平。第三处是象限分割线缺失或者画歪只有点没有线读者根本不知道你四象限是怎么切的。还有一个隐性问题是数据不更新。季度一变份额和增长率都变了但图还是手工改的稍微偷个懒图表就过期。解决方法是把数据源做成表格或者走数据透视让图表挂在动态区域上。这件事在Excel里完全能实现不需要写代码第3章会给出具体做法。说到底一张合格的波士顿矩阵图要满足四个条件口径统一、横轴对数、分割线清晰、数据可刷新。做到这四条这张图才真正能进汇报材料。2. 动手之前把数据口径和字段捋清楚Excel本身只是画图的工具真正决定成败的是你喂给它的数据长什么样。第2章就把表格结构定下来后面画图会顺很多。我建议单独建一个工作表专门放原始数据和计算字段图表放在另一个工作表互不干扰也方便后续复用。2.1 三个必须算出来的指标无论你手里原始数据是什么形态最终都要落到三列数值上相对市场份额X轴、市场增长率Y轴、规模气泡大小。再配一列业务名称用来打标签。先把这三列的算法讲清楚。相对市场份额 本企业在该细分市场的份额 ÷ 该市场最大竞争对手的份额。如果你的行业数据里拿不到最大对手份额可以用主要竞争对手份额的最高值替代逻辑是一样的。市场增长率一般取近一年的同比公式是本期规模 − 上期规模÷ 上期规模或者直接用行业口径的数据。规模这一维可以选择销售额、收入、用户数、利润随你的分析目的走但要保持前后一致不能这个业务用销售额、那个业务用利润。举个具体的例子假设一家公司有5条产品线数据如下产品线年销售额万元本企业份额最大对手份额相对市场份额市场增长率A线820025%40%0.6318%B线1560042%28%1.5012%C线43008%35%0.233%D线1180030%30%1.006%E线21005%22%0.23-4%相对市场份额那一列是算出来的比如A线就是 25÷40 0.625四舍五入到0.63。D线是30÷30 1.00正好压线。增长率那一列如果按10%的分界B线12%和A线18%就落在高增长区其余三条在低增长区如果按加权平均算结果可能不同。这里提醒一句分界线定在哪直接决定谁被划进哪个象限所以定线的时候一定要和业务方对齐别自己拍。2.2 数据源怎么整理才方便后续更新把上面那张表整理好之后有三个小动作能让后面省很多事。第一选中整个数据区域按Ctrl T转成Excel表格Table。转成表格之后你在下面追加一行新产品图表引用的区域会自动扩展这是动态化最省力的做法比手写OFFSET公式稳得多。第二给每个字段起清爽的英文或者拼音表头比如 X_Share、Y_Growth、Size、Name避免中文表头在公式引用里出现奇怪问题。第三计算列用公式写不要手填数字。相对市场份额那一格写成B2/C2这种直接引用增长率写成(本期-上期)/上期这样原始数据一改指标自动跟着变。如果你的原始数据分散在多个工作表或者来自系统导出可以用数据透视表先做一轮汇总再在透视表基础上算指标。透视表的好处是刷新一下就把新数据带进来了配合切片器还能做多维度切换这个在进阶部分细说。另外假若你经常要从别的系统导数据Power QueryExcel 2016以后在数据选项卡里是更省心的选择导入一次、设好清洗步骤以后一键刷新连复制粘贴的功夫都省了。提示转成表格之后图表的选择数据里引用项会自动带表格名比如 表1[X_Share]这种结构化引用的可读性比 A2:A6 好很多别人接手也看得懂。数据这一层理顺了真正的画图只占整个工作量的三成但决定了后面七成的体验。3. Excel实操从零到一张可动态更新的波士顿矩阵这一章是全文的核心我按实际动手顺序一步步来先插入气泡图改横轴为对数并把方向调对接上坐标轴交叉点用误差线补出两条象限分割线最后处理标签、气泡大小和配色。每一步我都会说明为什么这么做这样你遇到变体数据时能自己判断。3.1 横轴取对数这一步决定图专不专业数据准备好后选中 X_Share、Y_Growth、Size 三列按住Ctrl多选不相邻的列插入 → 图表 → 散点图里的气泡图。为什么用气泡图而不是散点图因为散点图只能表达两个维度气泡图能用气泡大小再塞进一个规模维度信息量直接翻倍这也是标准BCG矩阵的常见呈现方式。图出来后双击横坐标轴打开设置坐标轴格式。在坐标轴选项里找到对数刻度勾选基数保持10。这时候你会发现横轴刻度变成了0.1、1、10这样的等距分布。这正是经典BCG的画法——相对份额从0.1到10中间1.0是分界左右各占一半视觉上公平。接下来是最关键的一步把横轴方向反过来。选中横坐标轴 → 坐标轴选项 → 勾选**逆序刻度值**。这样10会跑到左边、0.1跑到右边相对份额越大越靠左符合波士顿矩阵左上为问题、右上为明星的阅读习惯。不做这一步整个矩阵的象限含义就全反了。逆序之后纵坐标轴会自动跳到图的右侧不用慌下一步就把它拉回中间。3.2 用误差线画出两条象限分割线四象限光有点没有线是没灵魂的。分割线的画法有好几种我推荐误差线法因为它最稳、更新数据时不用重画。思路是在分界点上放两个隐形的辅助点一个负责竖直分割线一个负责水平分割线然后用误差线把它们拉长成贯通的线。具体操作在数据表里补两行辅助数据。第一个辅助点X 1Y 10%或者你定的增长率分界值Size 给个很小的值比如1第二个辅助点X 1Y 10%和第一个点坐标相同但用途不同。实际更常见的做法是设两个点竖线点坐标 (1, 纵轴中点)横线点坐标 (横轴中点, 分界增长率)。但横轴是对数轴中点不好取所以我常用另一种更简洁的方案——两个点都放在 (1, 分界增长率) 这个交叉点上一个用垂直误差线延伸成竖线一个用水平误差线延伸成横线。右键图表 → 选择数据 → 添加系列把这个辅助点的 X、Y 加进去气泡大小选那一列。选中该辅助点 → 图表右上角 → 误差线 → 更多选项。要画竖线就用垂直误差线方向选正负末端选无线端误差量选固定值数值设成一个比纵轴跨度还大的数比如纵轴范围是 -10% 到 30%跨度是40%那固定值设0.4就足以让线贯穿整个图区。要画横线就用水平误差线固定值在对数轴上的设置要小心见下面的注意项。把辅助点的标记设为无、线条设为无只留误差线误差线的颜色改成灰色或者浅色粗细1磅左右一条干净的分割线就成了。注意横轴是对数轴时水平误差线的固定值到底按数据差值还是倍数走Excel 的表现不太直观很多时候会拉出不均匀的线。如果你对精度要求高建议竖线用垂直误差线的固定值法纵轴是线性轴没问题横线改用两个点连成的系列来画准备两个点 (0.1, 分界增长率) 和 (10, 分界增长率)用带直线的散点图系列接上这样横线绝对平直。两种方法各有取舍误差线省事双点连线精确。如果觉得误差线太绕还有一个偷懒方案直接画形状线条从图的左边缘拉到右边缘。缺点是数据范围一变线就对不齐了只适合一次性出图、不改数据的场景。3.3 坐标轴交叉点与刻度微调让分割线真正对上刻度线和点画好了位置对不对还得靠坐标轴帮忙。双击纵坐标轴在坐标轴选项里找到**纵坐标轴交叉把默认的自动改成坐标轴值填1。这样纵轴就移到了 X1 的位置也就是相对份额的分界线上。同理双击横坐标轴找到横轴与纵轴的交叉设置填分界增长率**比如0.1横轴就会上移到增长率分界的位置。做完这两步图上的十字交叉线和你画的误差线就对上了四象限一目了然。刻度方面纵轴的最小值和最大值根据数据分布手动设一下比如 -10% 到 30%主要单位设10%或者0.05让刻度看起来不密不疏。横轴的对数刻度基数保持10主要单位设10就行会显示 0.1、1、10 三档干净利落。实操心得如果某个业务的点正好压在交叉线上数据刚好等于分界值视觉上会很尴尬。这种情况要么微调分界线比如把10%改成9.5%要么给这个点单独换个颜色并在标签里注明别让它混在四象限中间。3.4 数据标签、气泡缩放与配色点、线、轴都到位了剩下的是让它好看且好读。数据标签是重头戏默认气泡图不会显示业务名称需要右键数据点 → 添加数据标签 → 选中标签 → 设置数据标签格式 → 勾选**单元格中的值**然后框选业务名称那一列。为了不打乱图表布局勾选时把Y值取消只留名称。标签位置选居中或者靠上避免和气泡重叠。气泡大小这块有个坑Excel 默认按数据值线性映射气泡直径导致大业务的泡泡可能糊满整个图。解决办法是双击数据系列 → 气泡大小缩放把比例调到70%左右或者干脆用面积映射Excel 里气泡是按直径算的视觉面积会放大所以大值容易夸张。如果规模差异特别大比如一个业务销售额是另一个的20倍可以考虑对规模取平方根或者用面积字段间接控制让布局更均衡。配色建议不超过四色按象限分色最直观明星用亮色、金牛用稳重的深色、问题用警示色、瘦狗用灰色。做法是给每个点单独设置填充——选中单个气泡点两下先选中系列再选中单个点右键设置填充颜色。点比较多时可以按象限拆成四个系列各自配色这样改色更省事。图例、网格线能关就关矩阵图不需要网格线那是干扰。整套做完你的波士顿矩阵图应该长这样中间是两条交叉的分割线纵横轴分别标注市场增长率和相对市场份额四个角落分别对应四个象限每个气泡是一个业务大小代表规模名字打在气泡上。到这里一张能进汇报稿的图就成型了。4. 常见问题与排查技巧实录图画到一半卡住是常事。这一章把我在实操里踩过的坑整理成速查表按图表类和非图表类分开前者是画图本身的坑后者是Excel使用环境导致的问题——后者听起来无关但在真实办公场景里非常常见。4.1 图表类问题速查表现象大概率原因处理办法象限含义全反了横轴没设逆序横坐标轴选项勾选逆序刻度值横轴刻度挤成一团没开对数刻度横坐标轴勾选对数刻度基数10分割线拉不长或拉过头误差线固定值设小了/大了按纵轴跨度的1.2倍设固定值或改用双点连线数据标签不显示业务名没勾单元格中的值设置数据标签格式里勾选并框选名称列气泡大小差异过大直接线性映射调气泡缩放比例或对规模取平方根纵轴跑到图右边逆序后的正常现象设纵坐标轴交叉为1把它拉回中间新增业务不出现在图上数据区域没扩展把数据转成表格CtrlT自动扩展这张表基本覆盖了80%的卡点。遇到问题时先对号入座别急着推倒重来。图表类问题几乎都能在选择数据和坐标轴格式这两个入口里解决把它们点开逐个检查比盲目重画快得多。4.2 复制粘贴失灵这类非图表问题画图过程中另一个高频事故是Excel无法粘贴数据。场景很典型你刚从系统或者网页复制了一列份额数据切回Excel按CtrlV没反应或者提示无法粘贴此内容。这个时候人会很烦但其实原因多半不在数据本身。我梳理下来主要有这么几种。第一剪贴板被别的程序占用。像远程桌面、输入法面板、某些截图工具会长期占用剪贴板通道Excel想拿也拿不到。解决办法是把这些程序退到后台或者打开Excel的剪贴板面板开始选项卡 → 剪贴板右下角小箭头看一眼里面是什么状态清空再粘。第二Excel还停在单元格编辑态。就是你光标还在某个格子里闪这时候整个窗口其实被这个单元格占着外面的内容粘不进来。按两下 Esc 退出编辑态或者点一下别的单元格再粘通常就好了。第三工作表或工作簿被保护受保护的区域不允许写入粘贴自然失败去审阅里撤销保护即可。如果是Mac版Excel情况会有点不一样因为macOS的剪贴板机制和图形层的差异Mac上粘贴大块数据或者跨应用粘贴偶尔会丢格式甚至卡死。我自己的习惯是Mac上批量导数据一律走数据 → 从文本/CSV或者用 Power Query不跟剪贴板较劲。另外Mac版Excel里有些图表选项位置和Windows版不同比如误差线菜单层级更深找的时候多点点右键菜单里的设置格式入口。还有一类复制粘贴异常是格式惹的祸。比如从网页复制来的表格带了一大堆隐藏样式粘进来之后单元格变花或者公式莫名出错。稳妥的做法是用粘贴 → 选择性粘贴 → 数值只带走数字样式全丢。这招在处理别人发来的花表格时特别管用。提示遇到复制粘贴反复失败最省时间的排查顺序是退出编辑态 → 清空剪贴板 → 去掉保护 → 重启Excel按这个顺序走基本都能定位。4.3 数据核对与批量处理的小技巧画矩阵图之前数据核对是绕不过去的一环。分享两个我常用的方法。一是两列查重比如核对产品线名称和另一张表是否一致用COUNTIF(区域, 本单元格)返回0的就是对不上的比肉眼找快得多。二是多条件筛选用数据 → 筛选 → 高级筛选或者干脆上SUMIFS、COUNTIFS这类函数做交叉校验比如SUMIFS(销售额, 产品线, A线)看看和明细是否相等。批量处理方面如果手头有一堆相似表格要合并再算指标别一个个手工粘。用 Power Query 把所有文件放进同一个文件夹数据 → 获取数据 → 从文件夹一次性导进来合并设好清洗步骤后以后丢新文件进去点刷新就完事。这个方法对每月要出一次矩阵图的岗位来说能省下大把时间。5. 进阶玩法让它真正用在汇报和决策里一张静态图解决的是看一眼的需求但业务分析往往要回答更复杂的问题去年到现在结构变了没有、某个事业部单独看是什么样、这张图能不能一键出PPT。第5章就讲几个进阶方向按投入从低到高排。5.1 多年度对比与切片器联动最省力的进阶是多年度对比。做法是把每个业务每一年的增长率、份额、规模都放进一张宽表加一列年份然后用数据透视表 切片器。切片器负责切年份透视表负责输出当期的三个指标图表挂在透视表上。这样拖动切片器矩阵图就跟着变去年是问题业务、今年爬进明星区的变化过程一目了然。更炫一点的做法是散点动画——把每段时间的数据做成逐帧用VBA控制刷新做出一段点阵移动的动画。这个在汇报里很抓眼球但制作成本不低适合重要场合。日常分析用切片器完全够了。5.2 和数据透视表、Power Query、VBA串起来数据透视表是把矩阵图做动态化的地基。它天然支持多维度、多指标还能做值显示方式比如把份额显示成占比减少手工计算。Power Query负责数据接入和清洗一次配置、长期复用。这两个配合起来你每月出图的工作量可以从两小时压到十分钟。如果需要更自动的流程VBA能帮忙。比如一键把当前图表导出成图片、一键刷新所有透视表并重排标签位置、一键把图插入指定的PPT模板页。下面给一段简单的例子作用是刷新当前工作簿所有数据连接和透视表然后把活动图表导出为PNGSub RefreshAndExport() Dim ws As Worksheet Dim pc As PivotCache 刷新所有数据连接 For Each pc In ThisWorkbook.PivotCaches pc.Refresh Next pc 导出活动图表 Dim cht As ChartObject For Each cht In ActiveSheet.ChartObjects cht.Chart.Export Filename:ThisWorkbook.Path \BCG_Matrix.png, FilterName:PNG Next cht MsgBox 刷新并导出完成 End Sub这段代码不难理解先遍历工作簿里所有透视缓存并刷新再把当前工作表里的图表对象导出成PNG。用之前记得把文件另存为.xlsm格式否则宏存不下来。实际用的时候路径和文件名按需改如果只想导某一张图可以给图表起个名字再精确导出。注意宏安全设置默认会拦截未知来源的宏自己写的代码在本地跑一般没问题但如果要发给别人最好说明一下或者改成加载项分发。5.3 打印导出与出图细节最后一步是把图交付出去。如果直接打印先在页面布局里设好打印区域最好把图表单独放到一个工作表用缩放到一页避免被拦腰截断。图表的字号在屏幕上看着合适打印出来往往偏小建议标题和坐标轴字体不小于10磅数据标签不小于9磅。导出图片的话右键图表 → 另存为图片支持PNG、JPEG等格式选PNG清晰度更高。如果图表里有半透明填充导出时选JPEG容易出现色块PNG更稳。放进PPT时我更建议用选择性粘贴 → 图片增强型图元文件这样矢量边缘清晰缩放不糊缺点是颜色偶尔会有细微偏移介意的话就直接粘贴为图片。还有个小细节颜色对比度。汇报常常投在投影仪上浅灰配浅蓝在屏幕上能看清投出来就糊成一片。出图前把对比度调高一点分割线用深灰而不是浅灰气泡颜色饱和度别太低。这些不是技术问题但直接影响图能不能用。做波士顿矩阵图这件事我从最早手工画形状、到后来死磕误差线、再到现在用表格加透视表一条龙中间翻过的车基本都在上面这几章里了。如果只让我留一句话给新手那就是先把数据口径和分界线的依据讲清楚再谈图好不好看。图只是结果的呈现口径才是这张图能不能被业务方认可的根本。至于Excel那些操作细节多画几遍自然就熟了卡住的时候翻翻第4章那张速查表八九不离十。