在Excel中,我们经常用比例累加图来表示预算、各部分支出比例等数据。默认的比例叠加图是根据比例的大小确定柱子的长度或面积,然后简单的叠加在一起。事实上,在当今流行的视觉设计中,
我们也可以完全通过颜色和颜色的深浅来实现叠加图。这样不仅美观,而且可视化效果更突出。
例如,下表显示了某公司各部门报销的招待费。为了更直观的看到各个部门的招待费累计情况,现在可以使用条件格式,用不同的颜色填充单元格,实现累计图表。
原始资料
先将招待费总额相加,然后在C2输入公式“=(B2/B$7)*100%”,再下拉公式,就可以查出各单位招待费各自的比例数据。
比例数据
选择E1:N10区域,点击“开始格式行高”,设置为28,继续设置列高为“4”。即选择一个10*10的单元格作为颜色累积的填充区域。
选择填充区域
然后在E1输入“0.91”,点击“开始填充序列等差数列”,输入“0.01”作为步长值,输入1作为结束值,点击“确定”完成行序列的输入。在E2中输入“0.81”,如上完成顺序输入。
然后,选择已填充的两行序列进行向下填充,完成所有数据输入。这里每个单元格对应一个百分比,初始E10=0.01(也就是1%),然后招待费的百分比就可以用单元格依次表示,步进为1%,直到100%。
选择填充区域
单击开始条件格式突出显示单元格规则其他规则。在打开的窗口中,对包含以下内容的单元格按选择格式。在编辑规则的描述下选择“单元格值”和“小于或等于”。
然后在后面输入公式“=SUM($C$3:$C$6”)。继续点击“格式”,设置填充格式为红色。这里,$符号用于绝对引用单元格,这意味着如果单元格的值小于或等于C3:C6之和,则应用条件格式。
条件格式
继续设置剩下的三个规则。其他规则同上,公式依次为“=SUM($C$4:$C$6)”、“=SUM($ C $ 5:C $ 6”和“=$C$6”。然后依次点击“格式”,为不同的规则设置不同的颜色。
应用范围全部设置为$E$1:$N$10,所有规则可以在管理规则中依次设置。
行政法规
单击“确定”应用规则,以便根据上述规则填充每个单元格。为了让数据更美观,选中E1: N10区域,右键单元格,选择“单元格格式数字自定义”。
输入“;”在“类型”框中。”(三个半角小数格式),表示隐藏单元格的数字格式。
格式化数字
现在我们回到单元格,可以看到原来的数字已经完全隐藏,不同颜色的单元格其实对应的是各个部门的百分比。当我们改变实际数字时,不同百分比的单元格会同步变化。
这样,通过不同颜色的单元格数量,我们很容易看到不同部门的招待费比例。为了便于检查,我们还可以在单元格旁边添加各个部门的文字说明。这个颜色累积图是不是更直观?
单元格样式堆积图
我们可以看到,灵活运用条件格式,可以非常方便地用单元格来表示数字的比例,还可以举一反三,做出更多的图形。比如你可以直接用数据栏来表示招待费的比例。点击开始条件格式数据栏其他规则同上。
在打开的窗口选择“基于各自值设置所有单元格的格式”,将类型的最小值和最大值全部设置为“数字”,值分别为0和1,条形图的外观选择红色和实形边框。
数据条设置
点击确定后,可以看到在每个百分比数值后面都会添加一个和百分比数值对应的红色数据条,通过数据条的长短就可以非常直观看到数值的不同。
使用数据条直接表示占比