分享

数据可视化:如何用箭头标注指标的同比增减情况?

 偷懒的技术 2021-01-30

最近推送的五篇文章:

说明:

本文为 2016年7月写的旧文,最近有好几个读者朋友问到类似问题,现搬到公众号。

前言

前天《“偷懒”的技术》读友群里有读者提出下面的问题:

下表中用Excel的什么功能能表示数据的同比增减情况?如果环比有所增加,用向上箭头标注;若同比有所下降,用向下箭头标注。

效果如下图所示:

这是财务人员很常见的一个需求,很有代表性,就抽时间将原来简短的回答扩充成本篇文章。希望对大家有帮助。

这个需求有二个方法可以实现:公式和条件格式(图标集)。图标集是2007版才增加的功能,所以Excel 2003版的用户只能使用第一个方法。

问题看以简单实际上还是有点小小的困难,难点在于:

公式法难点:如何智能化的判断数据是增还是减,自动给箭头添加不同的颜色,以示区分。

图标集难点:如何批量给多个单元格加上相应的图标?

下面分别介绍:

一、使用公式

在D2单元格中输入公式

=IF(C2>B2,"▲",IF(C2=B2,"","▼"))

然后将D列的列宽设置刚好一个字符的宽度,这样上下三角箭头就紧贴2016年的数据。效果如下图:

这个需求有二个方法可以实现:公式和条件格式(图标集)。图标集是2007版才增加的功能,所以Excel 2003版的用户只能使用第一个方法。

下面分别介绍

一、 使用公式

在D2中输入公式

=IF(C2>B2,"▲",IF(C2=B2,"","▼"))

然后将D列的列宽设置刚好一个字符的宽度,这样上下三角箭头就紧贴2016年的数据。效果如下图:

尽管已经实现了所需的功能,但是不太完美,上下三角箭头颜色一样的,不好区分,不能一眼就看出哪个是增加,哪个是减少。所以应该用颜色区分:

假设同比增长用红色,同比下降用绿色。如果数据是固定不变,我们可以通过设置单元各的字体颜色来分别将其设为红色或绿色。数字可能会发生变化,为了达到字符随在数字大小相应变化,可以用自定义格式。

操作步骤:

1、 将D2单元格公式改为=IF(C2>B2,1,IF(C2=B2,0,-1)),然后下拉填充至D8。此公式的计算结果是,当2016年金额显示大于2015年时,返回1,小于2015时返回-1,二者相等时为空。

2、 选定D2:D8单元格区域,右键,设置单元格格式

[红色]▲;[绿色]▼;

如下图:

自定义格式解释:

上图的自定义格式用分号隔成三段:

第一段用于正数的格式,第二段用于负数,第三段用于0。

[红色]表示将字体设置为红色。

翻译一下就是:

如果单元格的值是正数,就显示为红色上三角形(公式结果为1的显示红色上三角);

如果是负数就显示为绿色下三角形(公式结果为-1的显示绿色下三角);

如果是零,显示为空。

详细的解释参见文后的自定义格式的相关知识点。

二、使用条件格式

在Excel 2007的版本中条件格式增加了图标集的功能,我们可以使用这个功能进行标注。但是这个功能有一点小小的缺陷:

不能使用相对引用,也就是不能将多单元格批量设置为符合要求的图标。

下面我们来看如何设置以及如何规避这个缺陷。

选中C2:C8单元格区域→点击Excel的“开始”选项卡下的“条件格式”图标在弹出的下拉菜单选择“新建规则”在弹出的新建规则窗口中,规则类型选择第一个“基于各自值设置所有单元格的格式”;格式样式设置为“图标集”,图标样式选择上下箭头(也可选择上下三角符号)。然后按下图进行设置:

设置后出现的结果如下:

显然,C3和C4单元格图标是错误的。

为什么出现这种结果呢?

因为条件格式设置的值是$B$2,为绝对引用,C2:C8单元格都是与B2单元格进行比较,而不是与同一行的单元格比较,所以会出现上面的错误。

那我们将$B$2改为B2,怎么样呢,会出现这样的错误提示:

假如再将其改成$B$$2:$B$8单元格区域,又会弹出下面的错误提示:

那如何才能让条件格式进行相对引用呢?

我们可以这样做:

选择要设置条件格式的单元格时,仅选择C2单元格,然后还是按照上面的步骤进行设置,将值“=$B$2”改为

=OFFSET($B$2,ROW()-2,0)

然后点击“确定”退出。

公式中的ROW表示取当前行的行号,OFFSET函数为单元格引用函数,上面的公式表示以B2为基准,向下偏移到对应行的单元格。

通过OFFSET函数和ROW函数,解决了图标集不能使用相对引用的问题。

我们在设置好B2单元格的条件格式后,再选择B2单元格,然后双击格式刷,逐个点击C3、C4……C8单元格,将B2单元格的条件格式应用到C3、C4……C8单元格。

通过这个方法可以解决不能将多个单元格批量设置条件格式(图标集)的问题

设置好后,效果如下图:

基本上实现了我们的需求,美中不足的是此方法图标的颜色不能自定义设置,比如无法将向上的箭头改成红色,向下改成绿色。

附“自定义格式中关于条件设置”的相关知识:

  • 完整的条件格式共分为四段:

[条件1][颜色1]数字格式1;[条件2][颜色2]数字格式2;[颜色3]数字格式3;[颜色4]文本格式4
  • 四段不一定要分全,如果只写一段,则为:

[条件][颜色]数字格式

用于表示所有满足条件的全部数字,

  • 如果是二段:

[条件][颜色]数字格式;[颜色3]数字格式3

第一段表示用于满足条件的数字,第二段表示不满足条件的格式

  • 如果是三段:

[条件1][颜色1]数字格式1;[条件2][颜色2]数字格式2;[颜色3]数字格式3

第一段用于满足条件1的数字,第二段用于满足条件2的数字,第三段表示不满足条件1、2的格式

条件1和条件2都可以被省略。当未指定条件时,条件1默认为">0",条件2默认为“<0"。即四个区段中,第一区段用于正数,第二区段用于负数,第三区段用于0,第四区段用于文本。即为下面的格式

关于自定义格式中各种符号的相关知识及应用,其内容较多,这里就不一一介绍了,请参阅Excel的帮助文件或阅读《“偷懒”的技术:打造财务Excel达人》的第三章。

Excel畅销书推荐:

《“偷懒”的技术:打造财务Excel达人》2017年当当网畅销榜Excel类第一名,办公类第二名,好评率99.8%,学Excel必选书籍!

本公众号不同于其他号,一篇文章不会重复推送,要阅读历史文章,请在本公众号主页发送关键词“目录”,也可发送其他关键词阅读相应文章或下载相应资料

关键词列表


关键词

回复关键词推送的内容或文件

目录

本公众号已发表的文章,按类别编写的目录导航

礼包

《“偷懒”的技术:打造财务Excel达人》示例文件和赠送礼包

答疑

《“偷懒”的技术:打造财务Excel达人》常见问题答疑。

练习

根据《偷懒的技术》读者群提问改编的练习题,来源于工作,实用!

整理类

介绍如何应用常见的数据整理技巧,将不规范的数据整理为规范的数据

筛选类

来源于实战的自动筛选、高级筛选文章

仪表盘

回复本关键词下载《豪华仪表盘》的示例文件

图表模板

下载《财务分析经典图表模板》,财务分析时简单套用就可以了

财务图表1

下载《财务分析经典图表及制作方法(第1季)》示例文件

财务图表2

下载《财务分析经典图表及制作方法(第2季)》示例文件

中秋

用Excel制作的海上明月图

七夕

一些有趣好玩的热点文章,主要为自定义格式、条件格式方面的
管理会计《管理会计应用指引及案例汇编》PDF
工资模板

下载最新的工资及个人所得税模板(2018年个人所得税法)

    转藏 分享 献花(0

    0条评论

    发表

    请遵守用户 评论公约

    类似文章 更多