小白讲Excel:初阶“条件格式”这么玩才对,你玩对了吗?

来源:小哥聊经验    2019/10/11 0:41:16
责任编辑:张小俊
字体:

我们在测试之前回顾一下规范别人录入时提到的数据验证的玩法和弊端,其中的某些场景并不适合用,比如触发频率比较高且准确度不是很高情况,如验证一些号码真假,因为数据验证会阻断录入的状态:弹窗,使用体验不好,而采用了【条件格式】普遍反馈挺好,但也有人说,在录入的过程中会遇到录入完,提示错误需返回修改,这样也会影响录入的连续性和情绪,我给他的建议录入完成后,统一修改录入失误的情况,具体操作步骤如下:点【数据】菜单下的点击【筛选】按钮,开启筛选功能,点击“筛选”按钮选“按颜色筛选”,选错误字体颜色就能将所有录入有误的筛选出来,再统一的修改;

修改数据的操作步骤优化方法

这样就能保障输入连续性,至于情绪只要你自己调节了;是不是不论你如何努力总有人会找出问题反馈给你,而我们只要把我们能做的做好就行了,别的都与我们无关。好了闲话少叙,进入今天的正题吧,我们对【条件格式】的使用分为三个阶段,你可以看看你在哪个段位中?

初阶功法

关键词:“格式”

掌握程度:熟知Excel中【条件格式】使用技巧,对其功能有全面的了解,简单使用规则管理!

那么问题来了,怎么样才能全面的了解【条件格式】呢?

虽然【条件格式】的下拉菜单挺多,其实主要分2部分:样式规则,规则管理工具,样式规则细分为:格式化规则,图形化规则;

条件格式的菜单分类示意图

样式规则:菜单的大部分都是预置好的选项,直接根据实际情况选用,而且操作步骤基本相同,首先选择使用的单元格引用范围,然后选择你要用的条件格式命令而且是实时预览,是不是很爽啊。我们先来熟悉一下样式规则的两个命令:

1)突出显示单元格规则(H):包含数值的逻辑关系比如:大于,小于,等于,介于等,比如需求单独标识出在10万-20万之间的销售额具体操作步骤:

选择销售额所在的列或销售额的引用范围,点【开始】菜单下的【条件格式】中的【突出显示单元格规则(H)】中的【介于(B)】,分别在两个输入框填写10万,20万,不用注意顺序,然后选择预置的样式或自定义字体颜色和其它格式,然后确定。

突出显示单元格规则的介于界面示意图

除了逻辑关系,还包括有点意思的“文本包含(T)”:它支持模糊查询,支持通配符*(代表0个到多个字符),?(代表1个字符),~(转义字符:~*/~?用于查询单元格中出现*/?);我在实际的操作中只显示查询的结果,它比查询功能好用,只要选择突出的颜色,结果一目了然,如果已开启筛选,可筛选字体颜色汇总查询结果;如果你是想直接选择结果,还是用查询功能比较简便。

“发生日期(A)”的使用场景还是比较多,比如生日提醒,通常的思路:生成今年的生日日期再与today()生成日期做差,再加判断是否是本月综合得出本月过上日的人有谁,而用条件格式就可以大大简化这个过程,只需生成今年日期+条件格式就能解决,需要创建辅助生成今年的生日日期,创建的今年的生日方法有拼接法,日期函数,建议使用date函数,比如出生日期在b列,c列为辅助列:c1的公式为=date(year(today()),month(b2),day(b2)),鼠标移至c2右下角,变为时,双击鼠标左键,完成公式填充,然后选中C列,点击【条件格式】-【突出显示单元格规则(H)】->【发生日期(A)】选本月,选一种颜色样式,点确定,最终效果如下:

发生日期的操作界面示意图以及效果

其实“发生日期”的使用场景比如分批次到期处理的合同,最近七天的订单情况,本周,上周的销售额,不用复杂的公式,只需调整【发生日期】弹窗的选项+按颜色筛选就能完成数据的汇总,是真的很方便。

“重复值(D)”也是常用的功能之一,具体的用法极其简单,选择要筛选重复值的引用范围,点击【开始】菜单下【条件格式】,弹出菜单选【突出显示单元格规则】中的【重复值(D)】,弹出重复值弹窗,根据你的实际需求选“重复”或“唯一”,再选择一个目标格式,点确定;不论你是标记重复还是唯一值都能满足你!

重复值的操作界面示意图

2)最前/最后规则(T):它的使用场景比较多,归结为三类:

排名:前10项(T)和后10项(B);虽然命令有准确的数字10,但它的最小值1,最大值1000的整数,这个区间你可以随意填写。比例:前10%(P)和最后10%(O):同上,不过它的最小值1,最大值为100的整数,根据你的需求填写。平均值:高于平均值(A)和低于平均值(V):根据选定的引用范围,自动计算平均值,我们只可以选择突显的样式。

最前/最后规则操作示意图

说完了格式化规则,接着开始“图形化规则”的类型和用法:

图形化规则:更突显了“条件格式”中的“格式”两个字,主要是用于数值类型的样式的展示,它参数设置选项更多的是修改外形的颜色,形状,先来整体感受一下同一组数据不同的效果图:

图形化规则的效果示意图

数据条:阶梯状条形图,条形图的宽度与单元格数值正正比,最大值为X,数据所在列的宽度为L,其它值Y对应的宽度为:(Y/X)*L,所以它适合数据纵向对比,效果明显;色阶:颜色渐变与数值的变化率成正比,变化率越小,颜色渐变过度柔和,它适合横向纵向的都可以,通常用于整体把控数据复杂变化,比如空气质量,产品合格率月变化,季度变化等。图标集:分类图示,用于表现数值所处的某种状态和阶段,多用于跟踪项目进度;虽然看着挺简单的东西,还有有些使用技巧的,我们逐一剖析一下它们隐藏的技巧;

数据条对比一组数据之“旋风图”!

玩好旋风图有3点:

颜色尽量有明显差异,视觉冲击力大两组的数据的对齐方式,一左一右,防止旋风图与数值重叠; 统一刻度,且数据条的宽度应留数值显示区域,一般为85%左右;现公司有两个销售部门A,B,去年一年的销售额做成“旋风图”效果,具体步骤如下:

销售A部门的数据左对齐,销售B部门的数据右对齐,调整A,B的宽度一致(选择B列鼠标不放,拖至C列就可同时选择B,C列,调整任一列宽,两列同时变),A选择“红色数据条”,B选择“浅蓝色数据条”; 观察数据,A最小值为20,最大值为75,B最小值为35,最大值为86,综合信息将A,B数据条统一最小值为15,最大值为110;选择【条件格式】下的管理规则,默认为“当前选择”,点击下拉菜单,选择当前工作表,弹出当前工作表的列表,选择任意规则,点击【编辑规则(E)】弹出“编辑样式规则”窗口,修改最大、小值,确定,重复步骤修改另一个规则,修改完成后可先点“应用”按钮,就可以预览效果,满意后确定退出编辑。

旋风图操作步骤和效果示意图

其实单一数据也可以使用这种效果的条形效果,比如一个列数据中即有正数也有负数的情况下,选择任意【数据条(D)】显示自带负数为红色的条形图,什么不要红色?好办,这就告诉你怎么自定义负数的样式和坐标轴的位置和颜色,具体操作如下:

单一数据也玩旋风图的操作步骤

说完数据条,在看看“色阶”有什么潜规则吧!

“色阶”用它“姿色”完美呈现等级和热度

色阶虽然没有额外的参数上自定义设定,它更加擅长的它的丰富的颜色表现,除了系统默认的预定义的,我们也可以根据自己的情况进行自定义颜色以及过渡区间,常见用于空气污染,水污染和安全等级,高度变化等只要与等级或状态变化的有有关的都可以用它呈现。

色阶的样式具体有2类:双色刻度,三色刻度,不论双色或3色它们的默认的所占的区域都是平均分布,要想说明过渡区间,需要了解修改规则数值的几种类型:

最低值:系统自动选择,我们无法修改,颜色的按平均分布;数值:支持小数,正负都可,且可以不是条件格式引用范围的数值;百分比/百分点值:在数学的概念上的差异,数字的比较小的时候,两者并无太大区别;公式:指用数学函数或表达式来控制颜色的变化(初级段位不用过多了解)但大部分的使用场景颜色的分布并不平均,我们需要根据实际情况来修改,比如一个班级的学生各科成绩,我们选【色阶(S)】的红黄绿,想以红色为60以下的成绩,绿色为60分以上,90以下的成绩,90以上为黄色,60-90位绿色到黄色的渐变,但最终的效果不是我们想要的,就需要调整最大中小值和颜色了,具体设置如下:

学生成绩效色阶效果图

这样一眼就能看出班里的大概综合成绩怎么样?接下来我们了解图标集的具体用法。

图标集跟踪项目的状态效果佳

我们用图标集显示项目的状态在合适不过了,默认的图标集会根据图标个数来平均拆分100%来区分图标状态,想明白如何计算,分析一下3个图标的计算原理公式:

3个图标对应的数据范围为:<最小值+33%*(最大值-最小值);>= 最小值+33%*(最大值-最小值)且<最小值+67%*(最大值-最小值);>= 最小值+67%*(最大值-最小值);注:33%是由100%/3取整,67%=100%/3*2取整得到的;

若你选择单元格范围的最小值为0%,最大值为100%,则<33%,为第一图标,>=33%且<67%为第二图标,>=67%为第三个图标;若遇到不可预测的情况,可以直接带入公式就能明白状态和值的对应关系,4个图标和5个图标对应的原理公式与3个图标公式类似,只是划分的百分比不同和状态数不同,如果你觉得麻烦,可以参考默认图标集默认规则:

图标分类默认比例示意图

了解以上的内容有助于根据实际的情况选择图标集的类型,不过图标集也给我们预留的许多设置修改的选项,以满足不同场景对数据划分和样式上的特殊需要,比如隐藏单元格数据只显示图标,调整图标的大小,以及某个阶段隐藏图标显示等都可以在下图中找到。

汇总常用的图标技巧示意图

到此【条件格式】的初级段位的操作技巧就这么多了,由于篇幅的原因,预知中高阶玩法,且听下回分解!按照往常的惯例,依然准备的本文的彩蛋:如果你想把别的表格的条件格式拿来自己用,怎么操作呢?很简单,复制有条件格式的单元格内容到自己表格中,然后点【条件格式】弹出菜单中的【管理规则】,如果在界面没有找到条件格式规则,将【显示其格式规则(S)】的“当前选择”改为“当前工作表”后,点击规则的使用范围,修改为你需要范围,点击确定就能移花接木啦!今天文章就到这了,喜欢我就关注我吧,我是爱讲彩蛋的小胖子-我爱极客的廖晨!

请注意:本文为编辑制作专题提供的资讯,页面显示的时间仅为生成静态页面时间而非具体内容事件发生的时间,由此给您带来的不便敬请谅解!

相关攻略及问答:

excel如何使用条件格式,让一列与另一列比较?

问:例如A B两列数据,需要比较B和A的大小,如果B比A小,则B中方框变红 如何...

答:需要新建一个条件格式规则,就可以实现在excel使用条件格式,让一列与另一列比较。演示软件版本为excel2007,具体操作请参照以下步骤。 1、首先在电脑上打开目标excel表格,然后选中表格中B列的数据,点击次级选项卡中的“格式”选项。 2、然后在...

excel如何用条件格式,对学生的每门课中小于60分的...

问:excel的操作题,这道不会做……excel使用条件格式,对学生的每门课中小于6...

答:在高级条件格式中进行自定义设置就可以了。 具体的操作步骤如下所示: 1、打开EXCEL,选中你要设置条件格式的区域。(下图仅为演示数据,成绩区域为B2:C4)。 2、点击上方的条件格式,在下拉列表中选择【突出显示单元格规则】,然后在展开列表中...

EXCEL条件格式中,如何进行多条件条件格式对需求填...

答:假设表一数据在A2:C8,选中这个区域,条件格式(开始--样式--条件格式),新建,用公式: =OR(A2=$E$2:$E$3) 设置格式:填充黄色。

Excel中条件格式怎么同时达到两个要求?

问:一关于EXCEL2003的“条件格式”的问题!希望各位达人帮忙解决!问题如下:...

答:那么你先选中A4:B50,点格式--条件格式-- 左边寻公式”--右边填上 =AND($A4>80,$B4>80,$A4"",$B4"") --在下的格式中选一种颜色

在条件格式中不能引用其他工作簿或者其他工作表?

问:Excel2007,现有sheet1表 A列数值1到10,sheet2表A列1,3,8三个数值。怎样...

答:其他工作簿不行 其他工作表的话可以这样 将Sheet2!A1:A3选中,Ctrl+F3,出现名称管理器,点新建,出现新建名称对话框 在名称中填入 wodemingzi 在引用位置中(其实已填好了)填入 =Sheet2!$A$1:$A$3 即定,关闭名称管理器 选中Sheet1的B列,条件...

EXCEL条件格式问题,包含特定文本的条件

问:一个表格有5列,行数无限制,我的目的是:在第5列中,只要有单元格里包...

答:以2016版本EXCEL为例,操作如下: 一,首先在原始数据中G列区别相对较大,以G列为例,G列中的“及格”两字占有率较高,以G列中“及格”为填充目标,选中G列,如图所示: 二,在开始选项里选择“条件格式”,条件格式里选择“色阶”-“其他规则”,如图所示...

EXCEL2007条件格式 图标集 一列与另一列数据做对比...

问:做成像7月8月这样的,就是弄不对。直接在想要对比的数据上对比,而不是...

答:条件格式本来就可以对一个区域使用 不需要一个一个格子使用 你把需要的一列都选上 然后自定义公式 系统会自动按照你框选的顺序 以第一个(一般都是左上角第一个)单元格的位置为基础 帮你自动填充其他格子 例如你框上B列整列 然后自定义写 =B1>A...

excel条件格式设置好后,怎么不能直接下拉单元格改...

问:第一排设置好之后 下拉第一个OOT结果不能改变后面的数值,比如序号为9的...

答:条件格式中的相关公式和Excel表格中的公式一样,需要注意绝对引用与相对引用。你的条件格式中是绝对引用,所以往下自制格式时会出错,把条件格式中行号的$去掉试试。

EXCEL设置条件格式:英语、计算机、法律科目的分数...

问:英语D列,计算机F列,法律H列,条件格式公式怎么写……

答:选中姓名所在列 设置条件格式 公式为 =AND(D1>85,F1>85,H1>85) 格式设置单元格字体加粗倾斜带单下划线并加红色底纹 如果是任一科在85以上就设置格式,那么AND改成OR

excel多条件格式怎么使空白单元格不变色?

问:I列已经设置条件1=I1>F1为红色,条件2=I1<F1为绿色,设置后空白单元格多...

答:将条件2改为 =AND(ISNUMBER(I1),I1

www.haoxyx.com true http://getqq.haoxyx.com/g/3307/33077228.html report 9491 我们在测试之前回顾一下规范别人录入时提到的数据验证的玩法和弊端,其中的某些场景并不适合用,比如触发频率比较高且准确度不是很高情况,如验证一些号码真假,因为数据验证会阻断录入的状态:弹窗,使用体验不好,而采用了【条件格式】普遍反馈挺好,但也有人说,在录入的过程中会遇到录入完,提示错误需返回修改,这样也会影响录入的连续性和情绪,我给他的建议录入完成后,统一修改录入失误的情况,具体操作步骤如下:点【数据】菜单下的点击【筛选】按钮,开启筛选功能,点击“筛选”按钮选“按颜色筛选”,选错误字体颜色就能将所有录入有误的
最近关注
首页推荐
热门图片
最新添加资讯
24小时热门资讯
精彩资讯
精彩推荐
热点推荐
真视界
精彩图片
社区精粹
关于本站 | 广告服务 | 手机版 | 商务合作 | 免责申明 | 招聘信息 | 联系我们
Copyright © 2004-2017 haoxyx.com All Rights Reserved. 好心游戏网 版权所有
京ICP备10044368号-1 京公网安备11010802011102号