最近推送的五篇文章: 说明: 本文为 2016年7月写的旧文,最近有好几个读者朋友问到类似问题,现搬到公众号。 前言 前天《“偷懒”的技术》读友群里有读者提出下面的问题: 下表中用Excel的什么功能能表示数据的同比增减情况?如果环比有所增加,用向上箭头标注;若同比有所下降,用向下箭头标注。 效果如下图所示: 这是财务人员很常见的一个需求,很有代表性,就抽时间将原来简短的回答扩充成本篇文章。希望对大家有帮助。 这个需求有二个方法可以实现:公式和条件格式(图标集)。图标集是2007版才增加的功能,所以Excel 2003版的用户只能使用第一个方法。 问题看以简单实际上还是有点小小的困难,难点在于: 公式法难点:如何智能化的判断数据是增还是减,自动给箭头添加不同的颜色,以示区分。 图标集难点:如何批量给多个单元格加上相应的图标? 下面分别介绍: 在D2单元格中输入公式
然后将D列的列宽设置刚好一个字符的宽度,这样上下三角箭头就紧贴2016年的数据。效果如下图: 这个需求有二个方法可以实现:公式和条件格式(图标集)。图标集是2007版才增加的功能,所以Excel 2003版的用户只能使用第一个方法。 下面分别介绍 一、 使用公式在D2中输入公式
然后将D列的列宽设置刚好一个字符的宽度,这样上下三角箭头就紧贴2016年的数据。效果如下图: 尽管已经实现了所需的功能,但是不太完美,上下三角箭头颜色一样的,不好区分,不能一眼就看出哪个是增加,哪个是减少。所以应该用颜色区分: 假设同比增长用红色,同比下降用绿色。如果数据是固定不变,我们可以通过设置单元各的字体颜色来分别将其设为红色或绿色。数字可能会发生变化,为了达到字符随在数字大小相应变化,可以用自定义格式。 操作步骤: 1、 将D2单元格公式改为=IF(C2>B2,1,IF(C2=B2,0,-1)),然后下拉填充至D8。此公式的计算结果是,当2016年金额显示大于2015年时,返回1,小于2015时返回-1,二者相等时为空。 2、 选定D2:D8单元格区域,右键,设置单元格格式 [红色]▲;[绿色]▼; 如下图: 自定义格式解释:
详细的解释参见文后的自定义格式的相关知识点。 在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必选书籍! 本公众号不同于其他号,一篇文章不会重复推送,要阅读历史文章,请在本公众号主页发送关键词“目录”,也可发送其他关键词阅读相应文章或下载相应资料。
|
|