Excel条件格式公式应用四例

飞飞万里晴空

飞飞万里晴空

2016-02-19 13:28

人生本是一个不断学习的过程,在这个过程中,图老师就是你们的好帮手,下面分享的Excel条件格式公式应用四例懂设计的网友们快点来了解吧!

我们知道,Excel条件格式功能可以根据单元格内容有选择地自动应用格式,它为Excel增色不少的同时,还为我们带来很多方便。如果让条件格式和公式结合使用,则可以发挥更大的威力,下面提供几个在条件格式中使用公式的应用实例,希望能给读者朋友带来一些启发。

一、 判别输入是否正确

在输入如身份证等有固定位数的号码,出现位数不正确的情形时,我们希望Excel能够给出提示。虽然可以使用数据有效性设置实现,但是当输入出错时,Excel总会弹出一个提示的对话框,有朋友可能觉得这样 唐突的提醒有点影响心情,那就让条件格式来温和的提醒吧。

1、创建条件格式的公式

假设我们通过条件格式,把符合位数(15位或18位)的号码所在单元格的填充色设置为绿色,输入完成后,通过查看单元格的填充色是否变为绿色,就可以知道输入的正确性了。

由于身份证号码数据是属于文本类型的,先选中需要存放身份证号码的A2:A52单元格区域,将它们的数字格式设置为文本。然后在A2:A52单元格区域处于被选中的状态下,选择菜单格式→条件格式命令,打开条件格式对话框,单击条件 1下方的下拉箭头,在弹出的下拉列表中选择公式(图 1)。

Excel条件格式公式应用四例

接着在其右边的文本框中输入公式=OR(LEN(A2)=15,LEN(A2)=18),然后单击格式按钮,在打开的单元格格式对话框中选择图案选项卡,选择绿色作为符合条件的单元格的填充色(图 2)。

Excel条件格式公式应用四例(多图)

设置好后单击确定按钮,返回条件格式对话框,检查无误再次单击确定就完成了条件格式的设置(图 3)。

Excel条件格式公式应用四例(多图)

(本文来源于图老师网站,更多请访问http://m.tulaoshi.com/diannaorumen/)

小提示:上面的操作,先选中了一个单元格范围A2:A52,然后为这个单元格范围设置条件格式的公式。在这种情况下,公式中应使用选择范围中左上单元格的引用,此例中为A2。公式输入完成后,可以查看一下这个范围中的其它单元格的条件格式公式,如A8单元格,为=OR(LEN(A8)=15,LEN(A8)=18),这是由于上面的引用为相对应用,它会根据单元格的实际偏移量自动改变,从而得到适合其它单元格的公式。

2、实现的具体效果

现在来测试一下上面设置可以实现的效果,在A2:A52区域的单元格中输入一些身份证号码,当位数是18位或15位时,所在单元格的填充色自动变为绿色,而位数不对的身份证号码,所在单元格的填充色不发生任何改变(图 4),从是否变色我们就可以判断输入的正确性了。

Excel条件格式公式应用四例(多图)(2)

全部输入并确认正确后,如果需要删除单元格条件格式,则先选中A2:A52单元格区域,然后打开条件格式对话框,单击如图3中的删除按钮,在打开的删除条件格式对话框中勾选条件1复选框,单击确定即可(图 5)。

Excel条件格式公式应用四例(多图)(2)

二、 找出销售额的前三名

如图6中的B2:B12单元格中存放着销售额数据,要找出其中的前三名,让它们以蓝色字体显示。

先选中B2:B12单元格,打开如图1所示的对话框,输入公式=B2LARGE($B$2:$B$12,4)然后将符合条件的字体格式设置为蓝色即可。实现的具体效果如下图(图 6)。

Excel条件格式公式应用四例(多图)(3)

说明:虽然可以对销售额数据列排序找出前三名,但是,可能我们希望以日期为顺序排列,这时条件格式就可以做到两全其美了。

三、 让符合特殊条件的日期突出显示

有时 ,我们可能希望符合特殊条件的日期所在的单元格突出显示,比如星期六或星期天。这时我们可以先选中日期所在的单元格,如图6中的A2:A12,然后打开如图1所示的单元格,输入公式=OR(WEEKDAY(A2,2)=6,WEEKDAY(A2,2)=7),然后设置符合条件的单元格填充色为阴影即可。

小提示:函数 WEEKDAY(serial_number,return_type)的功能为返回某日期为星期几,Serial_number??表示一个顺序的序列号,代表要查找的那一天的日期。当参数return_type为2时,函数返回数字 1(星期一)到数字 7(星期日)之间的整数。

四、 让工作表间隔固定行显示阴影

当单元格数据行较多,我们为了让显示效果更加醒目,可以让工作表间隔固定行显示阴影,效果如下图所示(图 7)。

Excel条件格式公式应用四例(多图)(3)

(本文来源于图老师网站,更多请访问http://m.tulaoshi.com/diannaorumen/)

上面的效果是使用了公式=MOD(ROW(),2)=0,如果要间隔两行显示阴影则用公式=MOD(ROW(),3)=0,其余依次类推。

小提示: 函数 MOD(number,divisor)返回两数相除的余数,其中Number为被除数,Divisor为除数。函数ROW(reference)返回引用的行号。其中Reference??为需要得到其行号的单元格或单元格区域,如果省略 reference,则假定是对函数 ROW 所在单元格的引用。

条件格式与公式结合还有非常多的应用,感兴趣的朋友不妨再研究研究哦。

展开更多 50%)
分享

猜你喜欢

Excel条件格式公式应用四例

电脑入门
Excel条件格式公式应用四例

Excel“条件格式”公式实例

办公软件
Excel“条件格式”公式实例

s8lol主宰符文怎么配

英雄联盟 网络游戏
s8lol主宰符文怎么配

Excel2024中条件格式的应用技巧

电脑入门
Excel2024中条件格式的应用技巧

Excel条件格式如何转化普通格式

excel
Excel条件格式如何转化普通格式

lol偷钱流符文搭配推荐

英雄联盟 网络游戏
lol偷钱流符文搭配推荐

Excel数组公式实现条件统计

办公软件
Excel数组公式实现条件统计

EXCEL条件格式转成普通格式方法

电脑入门
EXCEL条件格式转成普通格式方法

lolAD刺客新符文搭配推荐

英雄联盟
lolAD刺客新符文搭配推荐

立体视觉 让您的网页“靓”起来

立体视觉 让您的网页“靓”起来

内核模块的编译文件

内核模块的编译文件
下拉加载更多内容 ↓