写在前面
提到EXCEL数据可视化,首先会想到的是图表功能,实际上EXCEL有个能根据数据大小而变化格式的工具,叫条件格式。工作中常用这个工具对数据做提醒和预警功能。
命令位置
包含三部分命令组
最常用的突出显示包含数字范围、文本、日期和重复四类条件
以下通过职场中常见的六个案例,来做说明
案例1-标注不合格数据
要求:规定指标数据范围,低于最小值为绿色,高于最大值为红色。
本案例使用了窗体中的数值调节钮,来做指标范围变化的演示。
选取数据范围后,设置数据条件,如果是固定数值可以直接输入,可以用系统格式方案,也可以自定义格式
如果是单元格中的数据,选择对应的单元格
案例2-目标达成可视化
要求:根据销售额显示目标完成情况
要用到数据条功能,默认方式是选择范围最大值的数据条填满单元格
本例中需要通过【管理规则】--【编辑规则】,重新设置规则
最大值设置为销售目标所在的单元格即可
这里还可以设置最小值,负值和坐标轴,也可以仅显示数据条,不显示数据。
案例3-销售数据涨红跌绿
要求:每种产品,如果五月销售额高于四月数值,标记红色,低于四月数值,标记绿色
选择数据范围 D3:D16,条件范围选择第一个单元格D3对应值C3即可,要注意的是C3不能带固定符号$
自动就会实现每行数据D列和C列比较
案例4-合同到期预警
要求:计算合同到期天数
>60天,标记绿灯
30--60天,标记黄灯
<>
需要做好两个前提:
1.根据合同签订日期和期限(月)计算到期日期,要用到函数EDATE
2.计算到期日期离今天的天数,用TODAY(),实现动态变化,如果今天打开还有10天到期,明天打开这个文件自动变成9天。
用到图标集中的红绿灯
同样对图标规则进行设置,修改类型为数字
从2010版本开始,支持图标的自由组合
案例5-订单状态
要求:发货日期为空,订单状态为“未发货”,否则为“已发货”
先用IF函数来做判断
A2单元格公式 =IF(D2<>'','已发货','未发货')
可以用突出显示中的【文本包含】,包含“未发货”就变颜色
当发货日期输入内容时,状态变为已发货,取消颜色提醒。
案例6-根据选取内容自动变颜色
要求:根据选择的销售人员,对应的数据变颜色,方便查看,并自动计算销售额汇总
这也是一个综合应用,主要用到了删除重复值,数据有效性和SUMIF条件求和函数
选择范围B2:E23,需要新建公式规则,公式框中输入 =$B2=$G$7,需要注意$符号的使用
以上,通过6个实用案例,介绍EXCEL条件格式的用法,你也可以用得上.
联系客服