Excel条件格式可视化大法:进阶版
共 476字,需浏览 1分钟
· 2022-04-25
这篇文章给搭建讲讲Excel条件格式有什么进阶的使用方法以及有哪些有趣的应用吧。
02 进阶
01 聚光灯效果
用条件格式实现如下筛选相应条件得到的类似聚光灯效果。
数据验证
首先对地区和月份做数据验证形成序列筛选。
![](https://filescdn.proginn.com/34c5b0e19e5fd675d04a53bb3eac7301/8514b4730af697e205642a469433aa13.webp)
条件格式使用公式
选中A1:G13区域,点击条件格式——新建规则——使用公式确定要设置格式的单元格。输入=J$2,将单元格设置为填充黄色的格式。
![](https://filescdn.proginn.com/d83f76528cb14af1f775d951e72365dc/cd2d9b75f804002bf734ea59233dcbbc.webp)
同样地,设置月份选择的单元格格式公式,=AJ$3,这里需要注意相对引用的写法。
![](https://filescdn.proginn.com/473cddcb75e901716e7551ed6912b071/8dce74171169ef9aa3c814f21688f639.webp)
这样就实现了通过筛选相应条件得到类似聚光灯的效果。
02 百分比动图
先选中一定列,调整成大概正方形的样子。
![](https://filescdn.proginn.com/d4feace6689782bb2c75d3659907ff26/c230903b03f09877d57f1605fa303d2f.webp)
输入一个1~100的矩阵
![](https://filescdn.proginn.com/cf8185ac99c3d1beee8078f0d98deadb/6f5e1b5c891147ab856c4987b0403db0.webp)
对这块区域填充成灰色,线条颜色设置成白色,设置全边框模式。
![](https://filescdn.proginn.com/6709c16a75d575c82651e1ee05d80e73/d189120d6e92164eb069259b1a3e5bfb.webp)
用=RANDBETWEEN(0,100)函数生成一个随机数
![](https://filescdn.proginn.com/9f5cdde473a218a8b9f2ad0c003f80f8/342fe4a7cc0bbda3fdcb7119c2a51d58.webp)
设置条件格式,用公式设置,如果B3单元格小于等于B1,=B3<=1,则设置单元格格式填充为黄色。
![](https://filescdn.proginn.com/a3b34f89c6ce3790020eabe6aef7a83b/32203fde3753b7d3f9ba32b950e901e6.webp)
![](https://filescdn.proginn.com/9b9aec881b9ae13d7c6212e24f0b0e23/dba3de1d773debb661a99b0e7e5e9585.webp)
把区域中的数字隐藏掉,选中该区域,右键设置单元格格式,在自定义的类型中输入;;;@ ,即可隐藏数字。
![](https://filescdn.proginn.com/54d3f5c3bf57b3b99606c47782b1c8f8/885cc20106163f352cac0203e7e235d6.webp)
![](https://filescdn.proginn.com/8b76781384dc1333943780359a64721d/0e2103071df7e19afb975e894b221bbb.webp)
03 条件格式旋风图
数据如图,对品类A和品类B用条件格式实现旋风图的效果。
![](https://filescdn.proginn.com/c7a3ed8669f0f4a418216557698ba756/a25ce26373f8b73488cfae8243c53150.webp)
选中品类B,点击条件格式——数据条,选择一个数据条样式。
![](https://filescdn.proginn.com/b4119862804ea712fe1e106c8b0fb820/38ce62af52a854743229d070501e5c94.webp)
选中品类A,点击条件格式——数据条,选择其他规则,在弹出的对话框中,条形图方向选择从右到左。
![](https://filescdn.proginn.com/b400f2283996c0c4ea83f4dd6fc6228f/f0bb6420acd66b00989c2a919e9632f2.webp)
即可实现旋风图的效果
![](https://filescdn.proginn.com/fe1d5680d5732f09492185452e0c38eb/756d845827e62c6aca5a6da215de3087.webp)
但是呢我们发现,单元格中的数字和数据条的颜色会有影响,我们更希望的展示形式是数字在一边,数据条在另一边。
![](https://filescdn.proginn.com/aa707f9415f7b3b76fd074df44b0283c/bd6069a1b4fa467b605c51ca0e140d18.webp)
选中品类B,点击数据条——其他规则,在弹出的对话框中,最大值部分,选择类型为数字,值为700,让单元格数据条右边空白部分最大为700,这个值具体是多少,可以根据数据进行调整。
![](https://filescdn.proginn.com/6f5c69af0cea9d01a6af5dab2e4f8645/f7ecaa40d259fc58eb2bda17ba4ba2e4.webp)
同样地,选中品类A,将最大值改为数字,700.
![](https://filescdn.proginn.com/53999ad3ece9e72192e9723897b76c9a/14e9357d464c0b93987c11d4817b03b4.webp)
04 控制是否可视化
条件格式和表单控件相结合,实现控件按钮控制是否显示条件格式的功能。
在开发工具中选择插入——复选框,如果没有开发工具的,可以从文件——选项——自定义工具区中,勾选上开发工具,则开发工具这个功能便会出现在菜单栏了。
![](https://filescdn.proginn.com/065645d5f747610bb4cad380e0f7bdaf/4aba896335ac8dbe41d833de410cad4a.webp)
设置单元格链接到一个空白的单元格上。
![](https://filescdn.proginn.com/3f10081cfc237b7064e656102a0ee4b1/a8105d8786b615624a90fa482d48adf5.webp)
选中品类A和品类B的数值区域,点击条件格式——新建规则——使用公式确定设置格式的单元格,输入公式 =2=FALSE ,不用设置格式,意思是如果控件未被选中(即为FALSE),则没有条件格式的样式。
![](https://filescdn.proginn.com/5577a30e2fdde7ac1ef507a8a92b5277/d7d43acbb3329f944c62b69664fdaef3.webp)
再点击条件格式——管理规则,将刚刚写好的规则右边 如果为真则停止 的勾勾选上。
![](https://filescdn.proginn.com/ee2458303ccf57951ba4bfece9271c1e/b0d4d3eef761d790d02cbba39aee61c9.webp)
即可实现通过表单控件控制条件格式的效果了。
![](https://filescdn.proginn.com/68b1792f957d261cee61da39705f86db/bafa368a1560d7eedaf7b75a3b390a46.webp)
05 对比表格
有时我们需要对比两个表格的数据是否一致,这时可以使用条件格式,将不一致的部分标记出来。
选中左边表格区域,点击条件格式——新建规则,输入公式 =A2<>E2,设置格式为填充黄色。即如果A2单元格不等于E2单元格,则将该单元格填充黄色标记出来。
![](https://filescdn.proginn.com/fd70a0f25e359cdab16102b4697b5dbf/eeec0370a9743ec23b2453c7a8e2b7f6.webp)
可以看到不一致的部分就标记出来了。
![](https://filescdn.proginn.com/89cd398a7abda66ad5b8c29cbc639572/cf6f4dc4503516361ef91d64f6c6b59e.webp)
06 甘特图
通过条件格式的新建规则制作甘特图。
![](https://filescdn.proginn.com/0644ffab1d37eeec9dd6ff9e6c240e6e/06bd21bd5ba30faee0491e3ca84aba7e.webp)
选中D2:H5区域,点击条件格式——新建规则——使用公式确定要设置格式的单元格,输入公式=AND(DC2,DB2),设置单元格格式为填充蓝色。
![](https://filescdn.proginn.com/0b3cec7d8e0e65faa78db0424fd51de3/edd218e69a3b7e9dad78de5c521c1802.webp)
即可得到甘特图一般的效果。
![](https://filescdn.proginn.com/0644ffab1d37eeec9dd6ff9e6c240e6e/06bd21bd5ba30faee0491e3ca84aba7e.webp)
对比Excel系列图书累积销量达15w册,让你轻松掌握数据分析技能,可以在全网搜索书名进行了解选购: