EXCEL中条件格式公式(精)_excel条件格式公式

EXCEL中条件格式公式(精)由刀豆文库小编整理，希望给你工作、学习、生活带来方便，猜你可能喜欢“excel条件格式公式”。

我们知道, Excel“ 条件格式 ” 功能可以根据单元格内容有选择地自动应用格式,它为

Excel 增色不少的同时,还为我们带来很多方便。如果让 “ 条件格式 ” 和公式结合使用,则 可以发挥更大的威力,下面提供几个在 “ 条件格式 ” 中使用公式的应用实例,希望能给读者 朋友带来一些启发。

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

1、创建 “ 条件格 式 ” 的公式 假设我们通过 “ 条件格式 ” ,把符合位数(15位或 18位 的号码所在单元格 的填充色设置为绿色,输入完成后,通过查看单元格的填充色是否变为绿色,就可以知道 输入的正确性了。由于身份证号码数据是属于 “ 文本 ” 类型的,先选中需要存放身份证 号码的 A2:A52单元格区域,将它们的数字格式设置为 “ 文本 ”。然后在 A2:A52单元格 区域处于被选中的状态下,选择菜单 “ 格式→条件格式 ” 命令,打开 “ 条件格式 ” 对话框,单 击 “ 条件 1” 下方的下拉箭头,在弹出的下拉列表中选择 “ 公式 ”(图 1。[转

载 ]“”TITLE=“”Excel鈥溙跫格式鈥澒绞道 [转载 ]“”/接着在其右边的文本框中输入公式 “=OR(LEN(A2=15,LEN(A2=18” ,然后单击 “ 格式 ” 按钮,在打开的 “ 单元格格式 ” 对 话框中选择 “ 图案 ” 选项卡,选择绿色作为符合条件的单元格的填充色(图 2。[转

载 ]“”TITLE=“”Excel鈥溙跫格式鈥澒绞道 [转载 ]“”/设置好后单击 “ 确定 ” 按钮,返回 “ 条 件格式 ” 对话框,检查无误再次单击 “ 确定 ” 就完成了条件格式的设置(图 3。[转

载 ]“”TITLE=“”Excel鈥溙跫格式鈥澒绞道 [转载 ]“”/小提示:上面的操作,先选中了一 个单元格范围 A2:A52,然后为这个单元格范围设置条件格式的公式。在这种情况下,公 式中应使用选择范围中左上单元格的引用,此例中为 A2。公式输入完成后,可以查看一 下这个范围中的其它单元格的条件格式公式,如 A8单元格,为

“=OR(LEN(A8=15,LEN(A8=18” ,这是由于上面的引用为相对应用,它会根据单元 格的实际偏移量自动改变,从而得到适合其它单元格的公式。

2、实现的具体效果 现在来测试一下上面设置可以实现的效果,在 A2:A52区域的单元格中输入一些身份证 号码,当位数是 18位或 15位时,所在单元格的填充色自动变为 “ 绿色 ” ,而位数不对的 身份证号码,所在单元格的填充色不发生任何改变(图 4 ,从是否变色我们就可以判断输 入的正确性了。[转载 ]“”TITLE=“”Excel鈥溙跫格式鈥澒绞道 [转载 ]“”/全部输入并确认 正确后,如果需要删除单元格条件格式,则先选中 A2:A52单元格区域,然后打开 “ 条件 格式 ” 对话框,单击如图 3中的 “ 删除 ” 按钮,在打开的 “ 删除条件格式 ” 对话框中勾选 “ 条件 1” 复选框,单击确定即可(图 5。[转载 ]“”TITLE=“”Excel鈥溙跫格式鈥澒绞道 [转 载 ]“”/

二、找出销售额的前三名 如图 6中的 B2:B12单元格中存放着销售额数据,要 找出其中的前三名,让它们以蓝色字体显示。先选中 B2:B12单元格,打开如图 1所示的对话框,输入公式

“=B2>LARGE($B$2:$B$12,4” 然后将符合条件的字体格式 设置为蓝色即可。实现的具体效果如下图(图 6。[转载 ]“”TITLE=“”Excel鈥溙跫格式鈥 澒绞道 [转载 ]“”/说明:虽然可以对 “ 销售额 ” 数据列排序找出前三名,但是,可能我们希 望以日期为顺序排列,这时 “ 条件格式 ” 就可以做到 “ 两全其美 ” 了。

三、让符合特殊条 件的日期突出显示 有时,我们可能希望符合特殊条件的日期所在的单元格突出显示, 比如星期六或星期天。这时我们可以先选中日期所在的单元格,如图 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。[转 载 ]“”TITLE=“”Excel鈥溙跫格式鈥澒绞道 [转载 ]“”/上面的效果是使用了公式

“=MOD(ROW(,2=0” ,如果要间隔两行显示阴影则用公式 “=MOD(ROW(,3=0” , 其余依次类推。小提示:函数 MOD(number,divisor 返回两数相除的余数,其中 Number 为被除数, Divisor 为除数。函数 ROW(reference返回引用的行号。其中 Reference?? 为需要得到其行号的单元格或单元格区域,如果省略 reference ,则假定是 对函数 ROW 所在单元格的引用。/"