把台风搬到Excel里，会有多牛X？

职场 2024-12-24 20:31 湖北

点击蓝字【秋叶 Excel】👆

发送【6】

免费领 1000+篇 Excel 精选教程！

本文作者：小花

本文编辑：卫星酱

近期，小花在浏览澎湃新闻时，又发现了一张精美的图表，

它是一张堆积条形图的复杂变形。

注：图片来自澎湃新闻，侵删

认真观察这张图，你会发现它有几个特点：

❶ 形状均以台风符号填充，十分美观；
❷ 同一条形中有三层堆积，分别代表不同的台风等级，视觉区分度很高；
❸ 能够同时对不同等级次数和累计次数进行对比，信息量大；
❹ 每个省份不只用一个条形表示，而是根据数据，以 50 为单位、用数量不等的多个条形来表示，可以方便直观地展示数量上的区别，非常完美地解决了不同系列之间数值差异较大的问题。

作图经验丰富的小伙伴肯定一眼就看出了这张图的实战价值，

那么，如何绘制这样一张图呢？

今天小花就逐一拆解，快跟着我学起来吧！

PS.文末可下载练习文件。

准备工作获取素材

小花在以前的图表教程中分享过两种符号获取方式——插入图标和图形组合。

但它们在本案例中，均不太适用。

我们需要使用第三种方法来获取符号，那就是截图去背景法。它可用于捕获非常规符号。

❶ 任意选一种截图工具，将需要的符号截图，注意仅保留符号和纯色背景部分。

❷ 然后将图片粘贴在 Excel 表内，点击【图片工具】-【格式】-【删除背景】-【保留更改】，即可获得指定符号。

❸ 最后再裁剪掉多余空白部分，仅保留符号大小即可。

绘制图表分步拆解

▋01 构建绘图辅助数据

显然，该符号条形图的本质是堆积条形图，每个条形代表一个类别，所以必须将源数据拆解成绘图所需的数据源区域，具体要要求为：

按不同等级罗列数值；
每一行累加不超过 50；
超过 50 部分另起一行；
不同省份间设一 0 值行用于区分。

函数公式可以帮助我们快速构建绘图数据区。

❶ 省份按指定次数依次重复

Excel 365 使用者可以用 Textsplit+Concat+Rept 函数来完成，其他版本的小伙伴可以按下面方式，构建辅助列，再用 Lookup 函数来完成。

F 列辅助列——累计次数公式：

=CEILING(B2,50)+50+F2

公式说明：

首先在 F2 中输入 1，F3 公式输入上述公式。

其中 Ceiling 函数以 50 为基数向上取最近倍数值，这就能够将数据按每 50 一组分行，+50 则实现了每个省份数据之间插入一个 0 值行。

G 列绘图省份类别列——按指定次数重复公式：

=LOOKUP(ROW(A1)*50,F:F,A:A)

公式说明：

Lookup 查询每个 50 整数倍值，根据其在 F 列的位置，返回对应的 A 列值。

由于 Lookup 总是匹配小于且最接近值，所以 2-7 行对应的值 50-300 均与 F2 对应，因此均返回 A1 值，即广东，从而实现了指定次数重复。

❷ 各类别对应系列数值分行及计算

需要将各类别总次数按不同强度台风次数依次填列到不同系列中，并确保每个系列值累加值不超过 50，超过则填列到下一行，且每一强度系列值之和等于对应强度次数。

H-J 列绘图数据列——按 50 次一组分行类似：

=MIN(VLOOKUP($G2,$A:$E,COLUMN(C$1),0)-SUMIF($G$1:$G1,$G2,H$1:H1),50-SUM($G2:G2))

公式说明：

VLOOKUP($G2,$A:$E,COLUMN(C$1),0)查询该省份对应强度台风的次数，

减去 SUMIF($G$1:$G1,$G2,H$1:H1)返回绘制数据区域已填列的次数，则为本单元格剩余可填列该强度的次数值，

50-SUM($G2:G2)表示更高强度列在本行已经填列的数值与 50 的差额，二者孰小即为本单元格可填列的值。

❸ 标签辅助类构建

K 列为各类别次数标签系列，此列用于在最后非堆积条形中增加一个用于添加累计次数的的极小值条形。

=IF(SUM(H3:J3)>0,0,0.01)

L 列为省份标签，仅取首个类别值，此列用于虚拟垂直轴标签。

=IF(G2=G1,"",G2)

M 列为次数标签值，它是各省份累计次数标签，此列最终作为 K 列的标签添加至堆积条形图中。

=IF(SUM(H3:J3)>0,"",VLOOKUP(G2,A:B,2,0))

▋02 绘制堆积图

❶ 插入堆积条形图

选择 G1:K32 单元格区域，插入堆积条形图，调整垂直轴次序为逆序类别、拉伸图表高度，调整水平轴最大值为 50、系列间隙宽度为 0%。

❷ 填充台风符号

【Ctrl+C】复制超强台风等级符号，选择对应台风条形后，【Ctrl+V】粘贴填充，并进一步修改填充方式为<层叠并缩放>，保持默认缩放比例为 1，即可实现按次数填充对应数量台风符号的效果。

同理，完成对其他台风等级符号的填充，台风符号堆积条形图就跃然纸上了。

❸ 设置虚拟标签

选择标签系列，设置无填充，并新增标签，位置选择轴内测，将标签值修改引用 M2:M32 的值。

同时通过【设计】选项卡-【选择数据】，将水平轴标签引用范围更改为 L2:L32。

至此，累计台风次数和垂直轴标签均复刻完成。

❹ 调整其他元素

最后稍加调整图标标题、图例、边框等元素，一张以符号填充、间距不等、定值分行的堆积条形图就绘制完成了。

以上，就是小花分享的定值分行+符号填充堆积条形图的绘制方法，简单总结如下：

❶ 利用公式构建分行数据及模拟标签数据；

❷ 插入条形图并使用符号填充；

❸ 添加标签并修改为引用指定单元格值。

本文分享的图形对展示绝对值差异较大的数据具有极强的应用价值。

它会是很多小伙伴们期待已久的良方，牛刀小试，你定将爱不释手！

如果你想学习更多 Excel 图表知识，掌握更多图表美化要点~

那就加入《秋叶 Excel 3 天集训营》吧，这门课由拉登Dony 老师教学，还配有助教老师答疑！

只需 3 天，你就能获取：

✅ Excel 高效办公技巧

✅ 视频+直播+图文笔记+课后练习

✅ 35 个常用函数说明手册

✅ 超多 Excel 学习资源……

原价 99 元

今天免费领取！

还等什么⚠️

快扫描下图中的二维码抢课吧！

👇

只需下班花 30 分钟，就能学到更多 Excel 干货！

💬

点击下方公众号卡片

发送【台风】

免费领本文配套练习文件！