用数据讲故事：13条Excel进阶技巧总结

本文将分享笔者在学习过程中总结的一些不常见但很好用的Excel函数、图表及展现方式，希望能对你的数据处理及可视化有用。

引言

我是一名数据科学同学，在工作过程中时常会遇到分析报告的场景，从底层的数据收集整理到最后的图表展现，曾遇到很多问题，大致分为三个方面：

1. 碰到数据手忙脚乱，数据处理效率低下
2. 知道自己表达什么，但不知用什么图表表达
3. 分析报告“花花绿绿”，文字太多，重点不清晰，不知所云

分析报告的本质是通过数据来讲故事，山姆·诺尔斯在书中提到怎么用数据来讲故事：

1. 逻辑清晰：数据是反映逻辑、折射关系、投射趋势，展现过程中前后的逻辑顺序很重要
2. 重图表轻文字：出色的数据故事通常都会伴随着精心制作的数据可视化，能用图表尽量不用文字，能用一张图尽量不用两张
3. 少即是多，简洁清晰：减少听众的认知负荷，把关注点转移到我们的重点上

笔者作为Excel的忠实爱好者，有时觉得它简单有时又觉得很难，但不得不承认的是Excel是一款强大的数据处理、可视化利器。以下是笔者在学习过程中总结的一些不常见但很好用的函数、图表及展现方式，想和大家分享，希望能对你的数据处理及可视化有用。

“不常见”却“很好用”的函数

▐ 一键成图的神奇函数——rept

例：=REPT("★",5) 结果：★★★★★

例：=REPT("|",V28*5)

 

▐ 多条件求和——SUMPRODUCT

计算ABCDEF商品的总GMV时我们最先想到的时计算单个商品的GMV，再通过SUM函数累加计算。用SUMPRODUCT可以实现一键累加计算，节省繁琐的步骤。



▐ 统计频数——FREQUENCY

我们经常会遇到，将表格中上万条数据分为几组，并统计频数的情况。第一想法是使用条件函数先为数字分组，再根据分组统计频数，虽然也能实现但终究步骤繁琐，效率偏低。

例：统计销量在<10,10-20,20-30,30-50,50-100,100-200的商品数

选中频数区域（单元格数与分组数应相同），输入公式=FREQUENCY(统计区域,区间点)，最后按Ctrl+Shift+Enter



▐ 文本提取函数

1. left（text，num_chars）
2. right（text，num_chars）
3. mid（text，start_num，num_chars）

▐ 三角符号展正负

‍‍ 选中要修改的单元格数据区域——调出单元格格式选项卡——自定义——在自定义输入框输入[颜色10]▲* 0%;[颜色3]▼* -0%;-，会将大于0的数据左侧加上“▲”，小于0的数据左侧加上“▼”。

“优美”的图表

▐ 条形组图

如果我们想看XX行业下的品类在A场、B场及C场的成交金额占比，堆积条形图能展示每个类目在三个场域的占比，但数据过于拥挤，很难直接得出结论。如果我们使用条形组图，数据的展示会更加简单明了，以下是条形组图的制作过程。 SETP1：在原有数据的基础上增加三个辅助列，辅助列的值=数据最大值+某一固定数值-数据列值，即保证每组的“数据列+辅助列值”之和相等，此处设置为15%。

SETP2：选中数据，插入堆积条形图

SETP3：修改数据条间距，并将所有的辅助列数据序列的数据条填充无色，添加数据标签及图例标签

▐ 参考线

为了更直观的突出数据与“目标/平均值”之间的差距，需要在图表增加一条参考线，以下是参考线的制作过程。

SETP1：在原有数据的基础上增加一个辅助列（参考线）

SETP2：在原有数据的基础上增加一个辅助列（参考线） SETP3：更改图标类型-选择组合图-将参考线序列设置为折线图

SETP4：选中散点图序列-设置数据序列格式--数据标记选项设置为“无”-线条选项设置颜色款及划线类型

▐ 蝴蝶图

蝴蝶图多用于同一对象两种指标的对比，比如我们想看XX行业下的品类在A场及B场的成交金额占比，以下是蝴蝶图的制作过程。

STEP1：选中数据，插入“簇状柱形图”

STEP2：将蓝色系列绘制在次坐标轴

STEP3：将次坐标轴设置为逆序刻度值

STEP4：分别修改主坐标轴、次水平坐标轴的最小值最大值范围（结合目标数据的取值范围，此处为-0.15~0.15）

STEP5：将中间的坐标轴调整到图表左侧



▐ 双饼图

有时我们需要对数据序列做对比，序列少且数量差异不大时使用饼图较为合适。但当序列多且差异较大时，占比过低的数据在饼图中无法辨识，如果能将小占比的数据拉出来单独做一个饼图就更好了，如图下：

STEP1：插入饼图-复合饼图

STEP2：默认情况下会将数据中最小的两个值分割成小饼图。本例中我们想把最小的三个值抽象出来，所以需要调整第二个饼图。右键数据系列格式-系列分割依据设置为“位置”-第二绘图中的值设置为3。

“明晰”的展现形式

▐ 去量纲化

本杰明说过，图并没有说谎，而是说谎者在画图。很多时候我们需要将不同量纲的数据放在同一个图表上进行趋势比较，可是鉴于很多数据的绝对额（量纲）差别很大，在一个图表坐标中很可能会掩盖很多实际增长趋势的信息。



看到图表时我们第一想法是GDP的增长快于其他三个指标的增长速度，但将数据全部都以1997年为基期做标准化处理：$G$2=B2/B$2*100%后，再使用标准化的数据制作折线图，会发现和之前的趋势有很大的差异。

 

▐ 强调要点

数据分析时我们首先要明确分析的目的，在制作图表时更需要强调要点，尽量少的使用标签，突出重点数据，弱化其他数据。如分析每英里成本高于平均水平的情况，将重点数据即平均值、高与平均值的数据用参考线及冲突性的颜色重点标识。

 

▐ 向双Y轴图说NO！

1. 替代方案一：剔除Y轴，添加数据标签
2. 替代方案二：竖直分割，叠加柱形图+折线图





▐ 视觉有序

1. 将“描述的重点”用特殊颜色突出
2. 将“同一类别”的信息突出



结语

数据可视化的目的不是“炫技”，而是更清晰有效的向听故事的人传达信息。以上也只是Excel隐藏能力的冰山一角，先占个坑，后续遇到很好用的技巧我也会继续更新，也欢迎数据爱好者一起交流学习。