市场复盘会前一天产品总监丢过来一句帮我把这几条产品线的家底用一张图讲清楚谁该保、谁该砍、谁该投然后你就对着Excel里那堆销售额和增长率数据发呆了。这种时候波士顿矩阵图BCG Matrix几乎是绕不开的工具——横轴比的是相对市场份额纵轴看的是市场增长率四个象限一摆产品的战略位置立刻一目了然。我这些年用Excel画过不下几十张波士顿矩阵图从最开始用折线图硬凑到后来摸清了散点图、辅助系列、动态区域的组合拳中间踩的坑能写满一整页纸。这篇就把整套流程拆开讲数据怎么整理、散点图怎么绑、象限分割线怎么画、标签怎么贴、模板怎么做到数据一改图就自动更新以及那些只会在实操里冒出来的意外情况怎么收场。不管你是刚接手数据分析的新人还是做了几年报表想升级模板的老手下面这些步骤都可以直接照着复现。1. 波士顿矩阵的业务逻辑与Excel里的图形选择在动手画之前得先搞清楚这张图到底在回答什么问题。很多人一上来就打开插入图表结果画出来的东西连自己都说不清每个点为什么在那个位置。波士顿矩阵图不是装饰画它是一张决策地图选错图形类型或者取错数据口径后面全白费。1.1 四个象限分别代表什么谁该被砍谁该加码波士顿矩阵的核心是把业务单元按两个维度切成四块。横轴是相对市场份额纵轴是市场增长率。相对市场份额的计算方式是本产品的市场份额除以该细分市场里最大竞争对手的市场份额。这个比值大于1说明自己是老大小于1说明还得看别人脸色。之所以用相对而不是绝对份额是因为绝对数字会骗人——在一个高度分散的市场里占20%可能已经是头部在一个双寡头市场里占20%就是跟班相对值才能反映真实的话语权。纵轴的市场增长率通常取行业整体增速反映这个赛道是在扩张还是在萎缩。把两个维度交叉就得到四个象限高增长高份额的明星通常是重点投入的对象低增长高份额的现金牛赚钱能力强但增长空间有限适合稳住并抽取现金流高增长低份额的问题也叫问号业务需要判断是加码投入还是及时止损低增长低份额的瘦狗多数情况下考虑收缩或退出。这四个名字不是随便起的它们对应的是完全不同的资源分配策略所以图形的准确性直接决定了结论的可信度。我见过太多人把横轴纵轴标反或者把份额算成绝对份额结果明星被画到瘦狗区管理层一看就质疑数据。画图前先在纸上把这两个维度的定义写清楚比什么都重要。1.2 为什么Excel里应该用XY散点图而不是气泡图或雷达图Excel里能表达二维关系的图有好几种但画波士顿矩阵XY散点图是首选。原因很简单散点图的每一个点都由一对坐标值X值和Y值精确定位横轴纵轴都是数值轴能真实反映相对市场份额和增长率的连续变化。你给一个产品填上X和Y它就落在该落的位置不会因为排序或者分类顺序而漂移。有人会想到气泡图。气泡图确实多了一个维度——用气泡面积表示第三个变量比如销售额或者利润。这看起来很美好一张图能塞进三个信息。但气泡图有个坑人眼对面积的感知是非线性的面积大一倍主观感受可能大好几倍。如果真要展示销售额大小我建议把气泡大小的参照基准设清楚或者在旁边配一张柱形图做补充别让气泡喧宾夺主。另外气泡图的横纵轴跟散点图一样是数值轴这一点是相通的。雷达图和折线图就别考虑了。雷达图适合展示多个维度的综合评分把二维定位问题硬塞进去只会让人看不懂折线图默认把X当分类轴相对市场份额这种连续数值会被平均分布点的位置全错。我早期就吃过这个亏用折线图画了一版横轴上0.5和2.0被排成等距图整个失真后来全部推倒重来。1.3 相对市场份额怎么算才不会被challenge数据口径是波士顿矩阵最容易被挑刺的地方。相对市场份额的分子是本产品的市场份额分母是最大竞争对手的市场份额这两个数字必须来自同一统计口径、同一时间窗口。如果分子用的是自家出货量占比分母用的是对手的零售额占比那比值就没有意义了。市场增长率同理。用同比还是环比用最近一个季度还是滚动十二个月要在图表下方标清楚。我一般习惯在图表旁边加一行小字注释口径比如份额基于2024年全年出货量增长率基于2024 vs 2023同比。这样做的好处是当有人质疑某个产品为什么落在明星区时你能立刻指出数据来源而不是含糊其辞。还有一个细节当最大竞争对手份额为0或者数据缺失时除法会报错。这种情况要么标记为待补充要么用行业平均值做替代并在注释里说明。别直接留个错误值在表里图表会直接崩掉。2. 数据表怎么搭从原始数据到可画图的字段设计图是表的孩子表搭不好图必然歪。我习惯在正式画图之前先把所有要用的字段在一个工作表里列清楚画图时只引用这块区域绝不东拼西凑。下面是我常用的字段结构你可以直接抄。2.1 一张标准的数据底表长什么样我会把底表设计成这样的列产品线名称、本期销售额、本产品市场份额、最大竞争对手份额、相对市场份额公式列、市场增长率百分比、气泡大小可选用于气泡图。前四列是原始输入后面几列能算的就算能引用的就引用尽量不手工填。这里有个小原则输入列和计算列分开。原始输入用白底计算列用浅灰底或者加个标记这样数据更新时你一眼就知道哪些能改、哪些是自动算的。视图里可以用小绿三角之类的错误检查标记来快速定位异常单元格但注意别误触忽略错误那会让真正的错误被掩盖过去。2.2 用公式算相对市场份额顺便说说LET函数相对市场份额就是一个除法但写得好能省不少事。最朴素的写法是C2/D2其中C2是本产品市场份额D2是最大竞争对手份额。如果担心分母为0或者为空可以套一层判断IF(OR(D20, D2), , C2/D2)Excel新版里有了LET函数可以把中间变量命名公式读起来更顺LET(share, C2, rival, D2, IF(OR(rival0, rival), , share/rival))LET的好处不只是好看它在复杂公式里能避免重复计算同一个表达式尤其当你的份额计算还要引用其他中间列时写一遍就够。我现在的模板里凡是涉及三次以上重复引用的计算一律用LET包起来。2.3 分界线取值平均值、中位数还是行业基准象限分割线画在哪是波士顿矩阵的另一个关键决策。最常见的做法是用平均值作为分界——相对市场份额取所有产品的均值增长率取所有产品的均值。这样做的好处是四个象限里的点分布相对均衡视觉上好看。但平均值容易被极端值带偏。如果有一个产品的份额特别高均值会被拉上去导致大部分产品都落在低份额区。这时候中位数是个更稳健的选择它不受极端值影响能保证大致一半产品在一侧。我的经验是产品数量少但差异大时优先用中位数产品数量多且分布均匀时用平均值。还有一种做法是直接用行业基准或者公司战略设定的阈值。比如公司规定相对份额大于1.2才算真正的领先那分界线就画在1.2。这种分法的好处是贴合业务判断坏处是可能让某个象限空掉。选择哪种取决于这张图是给谁看的、要支持什么决策没有标准答案但一定要在图上标注清楚你用的是哪一种。2.4 数据校验别让脏数据毁掉整张图底表建好之后花两分钟做一遍校验很值。检查项包括份额列是不是都在0到100%之间增长率有没有异常的负几百有没有空值有没有文本混进数值列。Excel的数据透视表在做这种快速体检时特别好用——把产品线拖到行销售额拖到值一眼就能看出哪些产品的数据缺失或者异常。如果发现某个产品的份额加起来超过100%多半是口径重叠得回去核实。如果增长率出现1000%这种离谱数字很可能是手滑多打了一位。这些错误如果不提前修画到图上就是几个飞出去的离群点整张图的比例尺全被撑坏。3. 一步步画出矩阵散点图的插入与坐标轴调整底表就绪可以进入正题了。这一节我把从插入图表到象限分割线的完整过程走一遍每一步都说明为什么这么做。3.1 插入XY散点图并绑定X和Y系列选中相对市场份额和市场增长率两列数据不含表头或者含表头但Excel能识别点插入图表散点图选不带连线的散点图。插入后右键图表 选择数据确认X系列是相对市场份额Y系列是市场增长率。顺序别搞反反了整张图的逻辑就颠倒了。这里有个新手常犯的错选中数据时把产品名称列也选进去Excel会把名称列当成一个系列结果是图上多了莫名其妙的点。正确做法是只选两列数值名称后面再通过标签单独绑定。3.2 横轴逆序让高份额稳稳落在左边波士顿矩阵有一个跟普通坐标系相反的地方相对市场份额高的一侧应该在左边。也就是说越靠左代表份额越大。这是咨询行业的惯例明星区在左上角瘦狗区在右下角看惯了这张图的人一眼就能定位。要实现逆序右键横轴 设置坐标轴格式 在坐标轴选项里勾选逆序刻度值。勾选之后横轴会从大到小排列高份额自然跑到左边。注意勾选逆序后纵轴默认会跑到图表右侧如果想让纵轴回到左边需要在纵轴设置里把纵坐标轴交叉改成最大分类或手动调整交叉点。3.3 坐标轴最值的取整技巧图好不好看很大程度取决于坐标轴的上下限设得合不合理。默认的自动刻度经常把点挤在一角或者留一大片空白。我的做法是手动设置最值横轴最小值设为0最大值设为相对份额最大值向上取整到0.5的倍数。比如最大是2.3就设2.5。纵轴最小值通常设0或者略低于最小增长率最大值向上取整到整数百分比。设置完刻度后如果某个点压在轴线上可以把最值稍微放宽一点。刻度单位也要调横轴主刻度0.5一格纵轴主刻度10%一格读图的人不用费劲去数。3.4 用辅助系列画象限分割交叉线散点图本身没有现成的象限分割线得自己造。原理是在数据表里加两条辅助系列每条只有两个点用它们画直线。第一条画竖线X值等于份额分界线比如均值1.0Y值取纵轴的最小值和最大值两端的值。第二条画横线Y值等于增长率分界线X值取横轴的最小值和最大值。把这两组数据作为新系列加到图表里图表类型改成带直线的散点图然后把线条设成浅灰虚线。具体操作右键图表 选择数据添加系列X值选竖线的两个X单元格Y值选竖线的两个Y单元格。加完再重复一次加横线。两条线都要单独设置线条颜色和线型别用默认的实线会盖住数据点。这里有个细节坑辅助线的端点值必须跟坐标轴的最值完全对齐否则线会画长或画短。我会把辅助线的端点直接引用坐标轴最值所在的单元格而不是手打数字这样坐标轴一改线也跟着动。4. 让图表像成品标签、气泡与视觉分区图能画出来只是及格能让人一眼看懂、愿意拿去做汇报才算合格。这一节讲标签、气泡和配色这些细节决定了这张图是能用还是好用。4.1 数据标签绑定产品名称的三种方式散点图默认没有数据标签或者只显示坐标值。要让每个点显示产品名称有三种思路。第一种是手动逐个改点中单个数据点 右键 添加数据标签然后点标签编辑文字。产品少的时候能用产品一多就是折磨。第二种是**值来自单元格这是Excel 2013之后的新功能右键数据标签 设置数据标签格式 勾选单元格中的值**然后框选产品名称那一列。一步到位所有点的标签都变成产品名。这是我最推荐的方式尤其是产品线会变动的时候。第三种是用辅助列拼接把产品名和数值拼成一个字符串用标签显示。这种方式灵活但容易让标签太长干扰读图。一般只在需要同时显示名称和关键数字时用。4.2 气泡大小映射销售额的注意事项如果你决定用气泡图那销售额就映射到气泡大小。这里的关键是参照基准。Excel默认按数值比例缩放面积但人眼容易高估大面积。我的做法是把销售额开个平方根再映射让视觉大小更接近线性感受然后在图例里说明气泡面积代表销售额。另外气泡图的负值会报错如果某个产品销售额为负退货多得先处理。还有气泡重叠的问题产品多的时候气泡会挤成一团这时候可以考虑半透明填充或者干脆回到纯散点图把销售额放到标签里。4.3 象限底色与配色分区教科书上的波士顿矩阵通常四个象限颜色不同视觉上一目了然。Excel里给象限上色有个技巧用图表区背景图片或者矩形形状叠放。更省事的做法是画四个矩形分别填充不同的浅色设置成半透明然后把它们对齐到四个象限位置置于数据点之下。配色上我有几条经验明星区用暖色比如浅黄现金牛区用稳重的蓝问题区用橙色瘦狗区用灰色。颜色饱和度都压低别用大红大绿否则数据点会被淹没。所有点的颜色保持一致或者按产品线分组避免视觉噪音。4.4 标题、图例与说明文字的排版一张能拿去汇报的图标题不能只写图表1。标题里最好带口径比如产品线波士顿矩阵份额为2024年相对份额增长率为同比。图例如果只有两类数据点分割线可以精简或者去掉。说明文字放在图表下方用文本框写清楚分界线的取值依据。字体统一用同一种标题大一号加粗正文小一号。别在一张图里混用三种字体那是新手最容易犯的排版错误。5. 动态化从一张静态图到能自动更新的模板做到这一步图已经能用了。但每次数据更新就要重新选一次数据区域太累。真正好用的模板应该做到数据一改图自动变。这一节讲怎么把它做成动态的。5.1 定义名称加OFFSET实现动态区域Excel里让图表自动扩展数据范围核心工具是定义名称配OFFSET或者INDEX。原理是定义一个会随数据行数变化的区域让图表系列引用这个名称而不是固定的单元格范围。操作步骤公式名称管理器新建名称叫比如份额引用位置写OFFSET(Sheet1!$C$2,0,0,COUNTA(Sheet1!$C$2:$C$1000),1)这表示从C2开始向下取非空单元格个数行。增长率列同理定义。定义好之后在图表的选择数据里把系列值改成Sheet1!份额这种引用名称的写法。这样你往下加行图上的点会自动增加。OFFSET是易失性函数数据量特别大的时候会拖慢表格。新版Excel更推荐用INDEX配COUNTA或者干脆把数据转成表格CtrlT表格本身有自动扩展的特性图表引用表格列也能联动。我现在的模板基本都用表格化数据省心。5.2 结合数据透视表和切片器做多维度切换如果一张图要同时服务多个口径——比如按季度看、按区域看、按渠道看——那光靠普通公式就不够了。这时候数据透视表加切片器的组合就很香。先把底表转成数据透视表的数据源把产品线放行、份额和增长率放值再插入切片器控制季度或者区域。透视表算出的结果作为图表的数据源点切片器透视表变了图也跟着变。切片器可以多选还能设置成按钮样式点在图上就能切换维度汇报的时候非常加分。要注意的是透视表刷新是有延迟的如果底表更新了得手动刷新右键 刷新或者设置打开文件时自动刷新。这一点后面踩坑章节还会细说。5.3 用SUMIFS统一汇总口径底表如果是从多个来源汇总来的很可能出现同一个产品在多行的情况。画图前要先合并。SUMIFS在这里特别好用SUMIFS(销售额列, 产品线列, 当前产品名)它按产品名把分散在多行的销售额加总份额和增长率也能用类似方式归并。用SUMIFS的好处是只要底表追加了新数据汇总值自动更新不用手工重算。我习惯把汇总区单独放一张工作表图表只引用汇总区这样数据源和展示层彻底分开维护起来清爽。6. 那些年踩过的坑图不刷新、粘贴失灵与标签错位前面讲的是理想路径但实际操作里意外总是层出不穷。这一节我把遇到过的高频问题整理出来附上排查思路希望你能少走点弯路。6.1 数据更新后图表纹丝不动最常见的情况是底表改了数字图表却没变。原因通常有三个。第一图表引用的是固定的单元格范围而你新增的数据在范围之外得手动扩展范围或者改用动态名称。第二如果用了透视表透视表没有刷新图表自然还是旧数据。第三你可能改了计算的辅助列但图表引用的却是原始列。排查顺序先看图表的选择数据里范围对不对再看透视表是不是需要刷新最后确认公式有没有重算可以按F9强制重算。我一般会在模板里加一个刷新按钮用VBA或者简单的说明文字提醒使用者先刷新再截图。6.2 Excel无法复制粘贴导致图表复制失败的排查在整理模板或者往PPT搬图的时候Excel复制粘贴失灵是另一个让人抓狂的问题。明明按了CtrlC粘贴的时候却没反应或者出现可以复制但无法粘贴的情况。这类问题的原因有好几种逐个排查基本能解决。第一种是剪贴板被其他程序占用。有些远程桌面工具、输入法或者剪贴板管理器会抢占剪贴板导致Excel的复制内容丢失。这时候关掉可疑程序或者用Excel内置的剪贴板面板开始选项卡右下角的小箭头查看当前内容。第二种是加载项冲突。某些第三方加载项会干扰复制粘贴可以试试文件选项加载项把可疑的加载项临时禁用重启Excel再看。第三种是工作簿本身的问题比如有大量条件格式、数据验证或者公式重算阻塞。可以新建一个空白工作簿测试一下如果空白工作簿正常那就是原文件的问题考虑把数据复制到一个新工作簿里重建。第四种是Mac版与Windows版的差异。Mac版Excel的复制粘贴行为和快捷键跟Windows不完全一样有时候跨平台传文件会出问题。遇到这种情况先确认快捷键Mac上是CommandC/V再检查是不是文件格式兼容性导致的。提示如果只是想把图表搬到PPT其实不用复制图片可以直接用粘贴为链接这样Excel里图一变PPT里的图刷新一下就同步了。当然前提是文件路径别乱动。6.3 标签重叠和某个点选不中的处理产品多的时候数据标签会互相压在一起根本看不清。解决办法有几个把标签位置设成靠上靠右错开手动拖动个别标签选中单个标签再拖或者干脆只在关键产品上显示标签其余产品靠图例和表格对照。还有一个恼人的问题是某个数据点怎么都选不中。散点图里点密集的时候单击选中的可能是整体系列。这时候先单击选中系列再单击那个具体的点就能进入单点编辑状态。或者用键盘方向键在点之间切换比鼠标点更精准。6.4 分界线跟着数据乱动的意外如果你把分界线的位置设成了自动引用均值那当底表新增数据时均值会变分界线跟着挪象限的划分也变了。这在某些场景下是好事反映最新分布但在汇报场景下可能是灾难——昨天讲的图和今天讲的不一样会被追问。我的建议是如果这张图是对外汇报用的把分界线锁定成固定值并注明分界线基于X年基准。如果是内部动态监控用的那让它自动跟均值走也没问题。关键是想清楚图的使用场景别让自动变成失控。画完这张波士顿矩阵图我最大的体会是Excel画图这件事工具本身不难难的是数据口径的统一和细节的把控。同一份数据分界线取中位数还是均值、横轴要不要逆序、气泡面积怎么映射都会影响最终结论。我的经验是动手之前先想清楚这张图要回答什么问题然后让数据和图形都服务于这个问题的答案剩下的就是熟能生巧。模板做顺了之后从原始数据到成图十分钟就能搞定比我最早手工摆点、一个个贴标签的时候快了不知道多少倍。后面如果你想让模板更智能可以再往里面加个下拉框控制分界线阈值或者用条件格式把处于危险区的产品自动标红这些都是可以继续深挖的方向。