CA88新登入 18

Excel文本表格里如何实现条形图和黄绿红灯,巧用Excel盈亏图分析差异一目了然

问题:Excel文本表格里如何实现条形图和黄绿红灯?

条件格式

在日常的工作中我们经常要对统计数据进行盈亏测算,但是简单的数据核算并不能很好表示盈亏。在Excel中用好图表工具可以更直观表示盈亏状态。

回答:

相对于“数据有效性”来说,条件格式在表格中的应用要广泛和有用得多,当然其重要性也是不言而喻的。利用条件格式,可以为表格进行智能着色,也可以为表格添加强调、预警、分类等效果,提升表格的智能化和自动化程度。

就地取材,直接插入盈亏图

CA88新登入 1

01→

如果要直观的表达盈亏,Excel
2016已经自带盈亏图表,这样直接插入即可直观显示盈亏状态了。比如有下列一组数据,盈亏的数据已通过公式计算出来了,但是简单的数字显然无法直观表示盈亏。

上图是通过Excel中条件格式实现的,下面说说它的实现过程!

条件格式,就是为了满足一定条件的一个或一组单元格设置特定的格式,如字体、字号、字体颜色、填充色等。

CA88新登入 2

在我们输入数据后,选中累计率下方设置样式的表格。

CA88新登入,Excel2010中,条件格式在【开始】选项卡→【样式】区域,如下图:

图1 示例数据

1.开始–条件设置

CA88新登入 3

切换到“插入”,点击“盈亏”,数据引用范围选择“D2:D10”,选择一个存放迷你图的位置,点击”确定“,这样在指定一个单元格即会自动插入一个盈亏迷你表,通过表就可以直观看出每月的盈亏了。

CA88新登入 4

小树良品

CA88新登入 5

2.当鼠标移动到渐变填充区域或是新填充区域时,我们会看到单元格区域的变化,根据你的需要进行设置!效果如下图:

点击“条件格式”会出现下拉菜单,这里解释几个重要的选项:

图2 盈亏迷你表

CA88新登入 6

①突出显示单元格规则:对满足一定条件的数值单元格设定条件格式;

如果觉得迷你表图表太小,可以选中F2:F10区域,将其单元格合并。这样迷你图表可以显示的更大,而且可以对首尾图标颜色、大小等做出更多的个性化设置。

3.如果想设置其他颜色,我们还可以通过“条件格式–新建规则”,进行规则设置。如果已有规则可以通过“管理规则”进行修改!具体方法如下:

②项目选取规则:针对一组数据,对满足既定条件(如前N个、后N个、前N%、后N%,高于和低于平均值)的单元格设定条件格式;

CA88新登入 7

CA88新登入 8

③数据条:根据单元格数值大小为单元格配置条形图(In-cell Bar);

图3 调整图表大小

新建规则和管理规则界面一样,我们可以在“选择规则类型”进行选择想要的格式,
CA88新登入 9

④新建规则:所有的条件类型及格式都可以在此进行设定;

一一对应,打造独立盈亏图表

在编辑规则说明里下的格式样式一栏,我们选择“数据条”样式。

⑤清除规则:清除已定义的条件格式;

插入盈亏表的方法虽然简单,但是从图表效果可以看到,它只是简单的将每月的阴亏使用不同的颜色表示,而且是将所有月份组合在一起。如果需要精确区分每月盈亏程度,那么可以借助条件格式进行打造独立盈亏示意图。

CA88新登入 10

⑥管理规则:新建、编辑、删除条件格式;

选中E2,输入公式“=D2“并下拉,增加一列辅助列E,数据和D列相同。接着选中E列的数据,点击“条件格式→新建规则”,在打开的窗口其中的规则和类型选择“基于各自值设置所有单元格的格式”,格式和样式选择“数据条”,勾选“仅显示数据条”,条形图外观选择实心填充,同时填充的颜色选择“黄色”。

4.说说亮灯是怎么设置的:将累计完成率数据复制一份到亮灯行

CA88新登入 11

CA88新登入 12

CA88新登入 13

小树良品

图4 新建格式规则

选中区域–条件格式–图标集–形状

02→

点击“负值和坐标轴”,在打开的窗口填充颜色为红色,坐标轴设置中选择“单元格中的点值”,这样数据条会根据单元格不同数值进行标注。

CA88新登入 14

学习几种常见条件格式的应用:为表格添加“隔行底纹”、根据文字自动着色、根据数字自动着色

CA88新登入 15

效果如下图,下面我们需要把数值去掉,只保留形状

(1)为表格添加“隔行底纹”:出于美观的需要,我们常常为表格填充颜色,形成“隔行底纹”的效果。对于简单的表格,行数不多时,通常我们都是手工设置单元格颜色的,如果表格有成百上千甚至更多行,此时,除非你有愚公移山的毅力,否则你一定会打消添加“隔行底纹”的念头。而
条件格式可以帮助我们快速实现“隔行底纹”效果,并且无论表格有多少行。

图5 负值和坐标轴设置

CA88新登入 16

选取一组单元格区域为例,为偶数行(行号)条件灰色底纹。步骤如下:

依次点击“确定”退出,可以看到E列的盈亏数值按照黄红两种颜色分布排列,通过数据条颜色和大小就可以非常直观看到每月的盈亏状态了。

条件格式–管理规则–编辑规则–勾选“仅显示图标”即可,在值一栏进行设置取值范围!

①选中部分单元格区域,依次点击【开始】→【样式】→【条件格式】→【新建规则】;

CA88新登入 17

CA88新登入 18

②选择规则类型为:使用公式确定要设置格式的单元格;

图6 数据条表示盈亏