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值和分类字段。比如我要做“门店面积—月销售额”的散点图表格应该是这样的门店编号面积㎡月销售额万元品牌等级S00112034.5高端S0028518.2中端S00320062.7高端S0049022.1中端S00515016.8亲民这种结构的最大好处是方便后续筛选、透视、公式计算也方便在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自带的彩色配色并不是不能用只是默认配色饱和度高、分类一多就容易撞色。我更推荐从数据可视化社区常用的配色方案里选下面这组是我在业务图表里反复用的“安全色”分类建议颜色RGB值第一组深蓝31, 78, 121第二组橙红230, 85, 68第三组灰色145, 148, 151第四组如需墨绿63, 117, 93第五组如需金色236, 176, 74这组颜色在白色背景上对比度高对色弱人群相对友好而且打印成黑白稿时通过深浅也能区分一二。如果分类超过五个与其增加颜色数量不如考虑把形状也利用起来比如第一类用圆形、第二类用方块、第三类用三角形状加颜色双编码图表的信息密度会明显提升。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号三是对严重重叠的数据进行“抖动”处理用辅助列在原值基础上加一个极小的随机值比如C2RAND()*0.5让重叠的点微错开更容易看出密度分布。抖动幅度要控制好不能影响数据判读一般不超过该变量实际刻度的1%。5.6 数据差距太大小数值全挤在坐标轴底部怎么办这种情况优先考虑改对数刻度。右键坐标轴—设置坐标轴格式—勾选“对数刻度”再把“显示单位”调整成万或千。比如数据范围从1到50000线性坐标轴下小于5000的点几乎全部贴在底部对数刻度则能把小值区域拉开让整体分布趋势更明显。缺点是读者需要理解对数尺度的含义所以如果要给业务部门看要在图表下方加一行注释说明横轴或纵轴为对数刻度。5.7 常见问题速查表问题现象主要原因解决办法点连成线选择了带平滑线的散点图更改为仅带数据标记的类型全图只有一个点X轴和Y轴选成同一列重新设置系列的X值和Y值范围分类颜色都一样格式修改应用到了整个图表选中目标系列后再改标记颜色添加系列后不显示辅助列公式返回空白或#N/A检查分类条件引用和原始数据多系列图例顺序乱添加系列的顺序和预期不一致在选择数据窗口中用上下箭头调整几百个点叠成一团标记太大且无透明度调小标记大小并设置透明度坐标轴比例严重失衡Excel自动刻度不合理手动设置最小、最大和主要单位5.8 复盘我第一次做分类散点图时踩过的坑最后聊点我自己的体会。第一次给领导汇报时我做了一张包含八个分类的散点图当时为了区分明显每个分类用了不同的颜色和标记形状结果整张图看起来像是打翻了调色盘领导第一反应是“这张图是不是有什么问题”而不是“这个报告有什么发现”。后来我把分类合并成高、中、低三档颜色尽量克制形状统一用圆形只对两个异常点加了标签反而汇报效果好了很多。选择合适的颜色和形状组合本质上是在替观众做视觉筛选把干扰信息减到最少。另一个小心得是每次完成一张图建议组里汇总成一个“Excel图表模板库”把做好的图以模板形式保存下来下次换数据源直接右键“选择数据”改区域即可节省的时间非常可观。这算是长期做Excel图表的人很值得养成的一个工作习惯。

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

相关推荐

IEEE802.11a物理层仿真：OFDM基带链路实现与避坑指南

香橙派RK3588交叉编译入门：从Hello到yolov5s部署的必经之路

香橙派RK3588交叉编译YOLOv5s部署：从hello到NPU推理

更多资讯

英语写作批改到底看什么？3个原理帮你弄明白

地下管廊几百台设备统一管理，PLC自控系统内部结构全拆解

多设备文件同步实战：从工具选型到Syncthing配置全解析

如何用Madeira在iPhone上启动Thumper：完整操作教程与142帧实录

CAT v3.1.0 Java链路监控部署与埋点实战

敢于拼搏，敢于面对

今日更新

我发现了一个新思路：用 Remotion + Claude Code 像写代码一样自动化生成短视频

Windows下 Codex 中 Chrome 和 Computer Use 插件不可用问题排查及解决参考方式：TaoToken 统一 Key 配置与验证

2026 大模型集体涨价：用 Python 做企业 Token 成本测算与选型避坑（附配置）

本周精选

如何彻底离线使用 Laya-CoreML：Hugging Face 下载、缓存机制与 local_files_only 详解

基于1Panel 部署MySQL MCP 服务：TaoToken 统一 Key 接入与 config.toml 配置实战

崇州园区营销网站建设避坑指南：5大注意事项保备案安全

本月热门

我发现了一个新思路：用 Remotion + Claude Code 像写代码一样自动化生成短视频

Windows下 Codex 中 Chrome 和 Computer Use 插件不可用问题排查及解决参考方式：TaoToken 统一 Key 配置与验证

2026 大模型集体涨价：用 Python 做企业 Token 成本测算与选型避坑（附配置）

读完文章，想聊聊您的网站？