Excel分类散点图制作全攻略：从数据整理到美化实战

上周帮朋友整理一份连锁门店经营分析表，数据本身不复杂，但他想在一张图里同时看清楚“门店面积—月销售额”的整体关系，还要给不同商圈类型、不同品牌等级的门店做分类，最好一眼就能看出哪类门店表现更好。当时我第一反应不是打开Python，而是老老实实打开Excel，用分类散点图把这个问题解决掉了。

很多人一说散点图就想到高端可视化工具，其实Excel原生功能完全做得出能上台面的分类散点图。所谓分类散点图，就是在普通散点图的基础上，把不同类别的数据点用不同的颜色、形状或大小区分开，让相关性、聚类趋势、异常点一目了然。这篇文章我会完整拆解从数据整理、系列设置、坐标轴调整到细节美化、问题排查的全流程，途中会穿插一些我踩过的坑和觉得好用的技巧，适合经常做数据分析表格、想把图表做清楚的运营、市场、财务、学生和科研人员。

1. 做图之前先想清楚：什么样的分类散点图才配叫“漂亮”

1.1 散点图到底解决什么问题

散点图的本质是看两个连续变量之间的关系。典型场景包括：广告投入和销售额是否正相关、门店面积和客流量的关系、考试成绩和复习时长的分布、产品价格和销量的走势等等。当只有两个变量时，普通散点图就够了，但现实中的数据往往还带第三个维度，比如“城市等级”“客户类型”“产品线”，这时候就需要给每一个点贴上阵营标签，让不同分类的点在同一个坐标空间里自然分开。这个需求，正是分类散点图存在的意义。

从我自己的使用经验看，分类散点图最有价值的三个场景是：

• 观察不同群体的分布区间是否明显分离，比如A品牌门店集中在高面积高销售额区域，B品牌集中在低面积高销售额区域，商业结论立刻出来。
• 发现异常点，比如某个点明明面积很小销售额却异常高，那大概率是数据录入错误或者存在特殊情况。
• 用于汇报演示，分类颜色一上，听众不用听你解释也知道哪片区域对应哪类数据。

1.2 “漂亮”不等于花哨，先想清楚图表要传达什么

我见过不少把散点图做得五颜六色、加一堆阴影立体效果的案例，第一眼很热闹，第二眼不知道重点在哪。漂亮的分类散点图应该满足四个标准：分类一眼能分清、数据精度不丢失、图例不产生歧义、打印或投屏后依旧清晰。颜色、形状、大小这些视觉元素都是为信息传达服务的，不是装饰品。

所以在打开Excel之前，我强烈建议先花5分钟在纸上画个草图，哪怕只是手画三个圈。问自己三个问题：横轴是什么、纵轴是什么、分类用什么视觉手段区分。很多人在Excel里调了半天，最后发现方向错了，问题就出在没想清楚这一步。分类散点图的核心不在于“像不像艺术海报”，而在于“别人能不能在三秒内看懂你发现了什么”。

1.3 为什么我不用Python和BI工具，而是选择Excel

这里不是说Python不好，我自己也常用matplotlib和seaborn画图。但分类散点图这个场景下，Excel有几个非常现实的优势：第一，绝大多数公司电脑都装了Excel，不需要额外环境；第二，领导或同事拿到文件后可以自己改数据、调格式，不用回来找你出图；第三，Excel散点图的交互体验足够直接，加个数据标签、改个配色都是点几下的事。如果图只是给自己做探索性分析看，那工具随意；如果图要做成交付物给别人看，Excel的原生图表往往是最快最稳的路径。

2. 数据整理：散点图的命脉，这一步最容易被跳过

2.1 推荐的数据表结构：长表格式

很多人做Excel图表失败，问题不在图表操作，而在数据表结构不对。分类散点图最推荐的数据表是“长表”结构，一行一条记录，每一条记录都包含X值、Y值和分类字段。比如我要做“门店面积—月销售额”的散点图，表格应该是这样的：

这种结构的最大好处是方便后续筛选、透视、公式计算，也方便在Excel里通过辅助列把一个分类拆分成多个系列。如果你拿到的原始数据是透视表那种“宽表”结构，建议先通过“数据—从表格/区域”清洗成标准一维表，再做后续操作。

2.2 数据清洗决定图表上下限

散点图对数据质量极其敏感，一个脏数据就能让整个坐标轴的缩放被带偏。我在做这类图之前会固定检查几件事。

一是文本型数字问题。很多系统导出的Excel，数字列左上角有绿色小三角，这种文本型数字在散点图里会被当成文本处理，导致点不显示或者坐标轴混乱。解决方法是选中列，点击黄色警示标“转换为数字”，或者用公式乘1强制转换。二是缺失值。散点图的某一项为空，整行就会缺一个坐标。如果希望缺失值保留位置但不显示，可以用公式把空值转为#N/A，比如=IF(OR(ISBLANK(B2),ISBLANK(C2)),NA(),B2)，这样Excel图表会忽略#N/A单元格，同时保留整体行结构。三是重复值。完全一样的坐标点会出现重叠，视觉上只有一个点，这时候可以通过D列添加微小的随机扰动来“抖开”，具体方法后面排查部分会讲。

2.3 用辅助列拆分系列，为分类散点图打地基

普通散点图只需要一列X和一列Y，但分类散点图要按类别分开设置系列。Excel最传统的做法是：每一个分类单独占两列，分别为该分类的X值和Y值。比如高端系列的X列写=IF($D2="高端",$B2,NA())，Y列写=IF($D2="高端",$C2,NA())，往下拉，不属于该分类的行就会变成#N/A。这样做有两大好处：第一，添加系列时只需要一次性框选该分类的X和Y两列，不需要手动逐个点选单元格；第二，后续如果数据增加，只要公式下拉到位，图表会自动带上新数据。

当然，如果分类数量少，比如只有两到三个，也可以不拆分列，直接在“选择数据”里挨个指定每个系列的X值和Y值范围。不过我自己还是推荐辅助列方案，因为它在数据量大的时候更稳定，也不会因为筛选隐藏行导致系列错乱。

3. 从数据到散点图：一步步做出来

3.1 图表类型千万别选错

这是新手翻车率最高的地方。插入图表时，很多人看到散点图图标里有“带平滑线和数据标记的散点图”，就直接点了，结果画出来一条折线，数据点被按顺序连起来，完全不符合散点图逻辑。正确操作是：先选中任意一个有数据的单元格，点击菜单“插入—图表—散点图”，选择第一个“仅带数据标记的散点图”，英文界面叫Scatter with only markers。没有线条连接，只有点，这才是散点图的标准形态。

3.2 选择数据：把X轴和Y轴的绑定关系设置正确

插入空白图表后，右键选择“选择数据”，左侧是系列列表，右侧是水平轴标签。在散点图中，水平轴标签通常不参与设置，重点是系列的X值和Y值。点击“添加”系列，给系列起一个名字，然后在X轴系列值框里选择X列区域，Y轴系列值框里选择Y列区域。这里有个常见的反直觉点：散点图里“X轴系列值”和“Y轴系列值”的关系是绑定的，如果你只给Y轴选了一列连续数据，X轴也选了一列连续数据，两个区域长度必须一致，否则Excel会提示错误或者自动截断。

3.3 为每个分类单独添加系列并设置颜色

这是分类散点图和普通散点图最大的区别所在。如果数据表里已经用辅助列拆好了各分类的X和Y，那操作就很简单：右键图表—选择数据—添加系列，分别把“A品牌X值”“A品牌Y值”两个辅助列指定到对应输入框，依次把高端、中端、亲民三个系列添加进去。添加完成后，右键任意一个系列，选择“设置数据系列格式”，在“填充与线条”中把标记颜色改成对应品牌的专属颜色。每个系列的标记大小、边框颜色也可以在这里单独设置。

有一个细节值得注意：不同分类尽量用区分度高的颜色，而且每个系列的标记边框可以设置成同色系更深一点的描边，这样点与点重叠时依然能分辨个体轮廓。我常用的做法是高端用深蓝色、中端用橙红色、亲民用灰色，这三种颜色在打印和投影环境下表现都比较稳定。

3.4 坐标轴刻度的调整：别让默认值毁了你的图

很多分类散点图最终效果不佳，问题都出在坐标轴的默认刻度上。Excel默认的最小值可能是负数、最大值过大或者刻度间距不合理，这会导致数据点被压缩到某个角落。正确做法是：双击横轴，在“坐标轴选项”里手动设置“最小值”“最大值”“主要单位”。比如门店面积从50到300平方米，横轴最小值就设40，最大值设320，主要单位设50；销售额从5到80万元，纵轴最小值设0，最大值设80，主要单位设10。这样设置出来的图表不仅有留白，视觉比例也更舒适，还能避免Excel自动从0到100导致的数据点都挤在左下角。

如果两组数据量级差距特别大，比如一个变量是几百、另一个是几万，可以考虑把对应轴改成“对数刻度”。右键坐标轴，勾选“对数刻度”，基数为10，能避免小数值点被大数值点压制到看不见。

3.5 用VBA快速批量生成多个系列

如果分类数量非常多，比如有十几个产品线，手动添加系列会非常痛苦。这时候可以用一段简单的VBA循环，遍历分类名，自动往图表里加系列。我在处理二十个分类的散点图时写过类似的代码，核心逻辑就是：先清空图表系列，然后对每个分类名生成一个Series，把X值指向该分类辅助列的区域，Y值指向对应列。VBA里设置系列颜色的代码大概是：

Dim srs As Series Set srs = ActiveChart.SeriesCollection.NewSeries srs.Name = "分类名称" srs.XValues = Range("B2:B100") srs.Values = Range("C2:C100") srs.Format.Line.ForeColor.RGB = RGB(31, 78, 121)

不过我要提醒一句，VBA适合一劳永逸的自动化场景，如果只做一张图，手动添加系列的时间成本完全可接受，没必要为了自动化而自动化。关于VBA更复杂的动态分类方案，比如用控件切换显示不同分类，可以等基础熟练之后再研究。

4. 让散点图真正“漂亮”：细节美化实操

4.1 配色方案怎么选，推荐几组能直接抄的颜色

颜色是分类散点图最直接的视觉语言。Excel自带的彩色配色并不是不能用，只是默认配色饱和度高、分类一多就容易撞色。我更推荐从数据可视化社区常用的配色方案里选，下面这组是我在业务图表里反复用的“安全色”：

这组颜色在白色背景上对比度高，对色弱人群相对友好，而且打印成黑白稿时通过深浅也能区分一二。如果分类超过五个，与其增加颜色数量，不如考虑把形状也利用起来，比如第一类用圆形、第二类用方块、第三类用三角，形状加颜色双编码，图表的信息密度会明显提升。

4.2 网格线、背景和边框的处理手法

漂亮的图表通常不需要太多装饰元素。坐标轴网格线如果默认是深色的，建议改为浅灰色，线型用虚线；图表背景填充色保持白色或者极浅的灰色，不要用渐变；图表的边框线可以去掉或者只保留极细的浅色线。具体操作：点击图表区域，在“设置图表区格式”中将边框设为“无线条”；点击绘图区，将填充色改为浅灰或者保持无填充；网格线通过“图表设计—添加图表元素—网格线—更多选项”调整颜色和虚线样式。这样处理完的图表干净很多，信息的视觉层级也更分明。

4.3 数据标签和标注：只在关键点上标注

给散点图加数据标签是很多人喜欢的操作，但全部标签都显示出来的结果通常是灾难，一堆文字把点都盖住了。我的原则是“标签宁缺毋滥”，只对需要重点解释的点进行标注。具体做法有两种。第一种是选中某个单独的数据点，右键“添加数据标签”，这时只有选中点被标记；第二种是通过“数据标签格式—标签选项—单元格中的值”，指定一个包含备注文字的辅助列，给特殊点加上自定义评语，比如“异常门店”“开业首月”“新店爬坡期”。坐标轴标题和图表标题也要简洁，标题甚至可以是一个有信息量的短句，而不是冷冰冰的“门店数据散点图”。

4.4 图例、字体和尺寸：细节决定质感

图例位置默认在图表右侧，如果图表宽度有限，图例会挤压绘图区空间。我习惯把图例拖到图表左上角或顶部，稍微调整字体大小为10号左右，对齐方式保持水平。图表内所有字体尽量统一，中文正文用微软雅黑，数字用Arial或原有默认字体，字号最小不低于9号。图表尺寸方面，如果把图表放进PPT或Word，务必按比例缩放，不要横向拉伸导致点变椭圆。Excel图表默认的宽高比例是16比9左右，但如果数据点在横轴方向延展得多，也可以手动把图表调成更宽的矩形，让绘图区比例和坐标轴分布匹配起来。

4.5 Mac版Excel和Windows版的操作差异

如果你用的是Mac版Excel，路径会稍有不同。插入散点图在“插入—图表—散点图”，右键菜单变成双指轻点或Control+点击，“选择数据”在“图表设计”标签下。设置系列格式的面板，Mac版在右侧有专门的格式侧边栏，操作逻辑类似，但快捷键和Windows版本不一样。给Mac用户的建议是：不要直接照搬Windows版的教程截图，按功能名称去菜单里找，效率和准确性更高；另外Mac版Excel对VBA的支持相对较弱，涉及VBA自动化时要谨慎保存和测试。

4.6 导出和复用：让图表能进入文档和汇报

图表做好后，最常用的去处是PPT汇报和Word报告。普通做法是直接复制图表粘贴到PPT里，粘贴时建议选择“使用目标主题并嵌入工作簿”或“图片”，这样别人打开文档时不需要依赖原始Excel也能看到完整图表。如果要把图表导出成图片，可以在Excel中右键图表区域，选择“另存为图片”，格式选PNG，得到的是矢量级分辨率的图片，放大不糊，完全够用。打印场景则需要在“页面设置”里勾选“水平居中”，并调整缩放比例，保证图表单独占一页时不会被分页切断。

5. 常见问题与排查技巧实录

5.1 为什么我的散点图变成一条斜线或一个点？

这个问题的出现频率在所有图表问题里排第一。最常见的原因是：X轴和Y轴选成了同一列，或者选择数据时把两列数据放在同一个“系列值”输入框里。还有一种情况是数据区域包含空行，Excel会把空行作为分隔，导致点与点之间形成视觉连接。排查方法：右键图表—“选择数据”—检查每个系列对应的X值和Y值区域是否分别是两个不同列，且一一对应。如果数据行很多，建议先创建一个筛选视图，确认没有隐藏空白行。

5.2 为什么点被连成了折线？

出现这种情况基本可以断定，插入图表时选择了带平滑线类型。解决办法有两种：更改图表类型时选择“仅带数据标记的散点图”，或者右键系列—设置数据系列格式—线条—无线条。另外还要注意，如果X轴的“坐标轴类型”显示为“文本坐标轴”，说明Excel没有把X列识别为数值列，此时点也会被按顺序连接起来。应当双击横轴，在“坐标轴选项—坐标轴类型”中切换为“日期/数值轴”，问题就能解除。

5.3 为什么添加了分类系列但图上不显示？

通常有三种可能：分类的X值和Y值区域选反了；目标分类的所有辅助列值都是#N/A或空白；分类区域包含了Excel无法识别的文本。最常见的原因是辅助列公式的判断条件引用错误，比如分类名称多了一个空格、大小写不一致等。检查数据时可以用COUNTIF统计一下该分类在原始数据里有多少行，如果原始数据为空，自然图上显示不出来。

5.4 为什么不同分类的颜色设置完又是一样的？

这个问题我在带新人时见过很多次。逐个添加系列后，如果只是选中图表里的所有点统一设置标记颜色，Excel会把这个格式应用到所有系列。正确做法是：在图表上单击一次，确认选中的是整个系列而非单个点，然后在格式面板里修改“标记—填充—颜色”。判断是否选中的办法是观察图表右侧的“当前所选内容”下拉框，里面显示的是“系列1”还是“系列2”，选对系列再改颜色，就不会串色了。

5.5 数据点太多完全看不清怎么办？

当数据量进入几百条乃至上千条，散点图会变成一团墨渍。我常用的处理办法有三个：一是降低标记的透明度，在“设置数据系列格式—标记—填充—透明度”里调整为30%到50%，重叠点会呈现深浅层次；二是把标记大小调小，比如从默认的7号改成4号或5号；三是对严重重叠的数据进行“抖动”处理，用辅助列在原值基础上加一个极小的随机值，比如=C2+RAND()*0.5，让重叠的点微错开，更容易看出密度分布。抖动幅度要控制好，不能影响数据判读，一般不超过该变量实际刻度的1%。

5.6 数据差距太大，小数值全挤在坐标轴底部怎么办？

这种情况优先考虑改对数刻度。右键坐标轴—设置坐标轴格式—勾选“对数刻度”，再把“显示单位”调整成万或千。比如数据范围从1到50000，线性坐标轴下小于5000的点几乎全部贴在底部，对数刻度则能把小值区域拉开，让整体分布趋势更明显。缺点是读者需要理解对数尺度的含义，所以如果要给业务部门看，要在图表下方加一行注释说明横轴或纵轴为对数刻度。

5.7 常见问题速查表

5.8 复盘：我第一次做分类散点图时踩过的坑

最后聊点我自己的体会。第一次给领导汇报时，我做了一张包含八个分类的散点图，当时为了区分明显，每个分类用了不同的颜色和标记形状，结果整张图看起来像是打翻了调色盘，领导第一反应是“这张图是不是有什么问题”，而不是“这个报告有什么发现”。后来我把分类合并成高、中、低三档，颜色尽量克制，形状统一用圆形，只对两个异常点加了标签，反而汇报效果好了很多。选择合适的颜色和形状组合，本质上是在替观众做视觉筛选，把干扰信息减到最少。另一个小心得是：每次完成一张图，建议组里汇总成一个“Excel图表模板库”，把做好的图以模板形式保存下来，下次换数据源直接右键“选择数据”改区域即可，节省的时间非常可观。这算是长期做Excel图表的人，很值得养成的一个工作习惯。