巧用Excel中的“条件格式”和“公式”
开篇：润墨网以专业的文秘视角，为您筛选了一篇巧用Excel中的“条件格式”和“公式”范文，如需获取更多写作素材，在线客服老师一对一协助。欢迎您的阅读与分享！
摘要:excel中“条件格式”的功能是根据单元格内容有选择地自动设置或应用格式,它为Excel增色不少的同时,还为我们带来很多方便。如果让“条件格式”和“公式”结合使用,则可以发挥更大的作用,更能使我们的工作达到事半功倍。该文通过几个实例来体验“条件格式”和“公式”的巧妙结合。
关键词:条件格式;公式;excel
中图分类号:TP311文献标识码:A文章编号:1009-3044(2010)07-1712-02
The Magical Using of Excel's "Conditional Formatting" and "Formula"
HOU Xue-lian1,2, ZHANG Xiao-rong1
(1.Shandong Electric Power School, Tai'an 271000, China; 2.Shandong University of Science and Technology, Qingdao 266510, China)
Abstract:In the Excel,the "conditional formatting" feature is based on the contents of the cell to selectively and automatically set or application format, it enriched by a lot of Excel, it is also brought us a lot of convenience. If "conditional formatting" and "formulas" used in combination, you can play a greater role, also enable us to work to achieve more with less. In this paper, it experiences the "conditional formatting" and "formula" the ingenious combinationwith several examples.
Key words: conditional formatting; formula; excel
在Excel 2003中,所谓条件格式是指当指定条件为真时,Excel自动应用于单元格的格式,例如,单元格底纹或字体颜色。如果想为某些符合条件的单元格应用某种特殊格式,使用条件格式功能可以比较容易实现。也就是说Excel中“条件格式”的功能是根据单元格内容有选择地自动设置或应用格式,它为Excel增色不少的同时,还为我们带来很多方便。而公式是在工作表中对数据进行分析的等式,用于对工作表进行加、减、乘、除等的运算,熟练使用execl中的公式可以大大提高统计速度,体现excel的强大的统计功能。如果让“条件格式”和公式结合使用,则可以发挥更大的作用,更能使我们的工作达到事半功倍。本文通过几个实例来体验“条件格式”和“公式”的巧妙结合。
实例一:判断输入的正确性
我们知道身份证、单位工作人员工号等往往是有着固定位数的号码,如果在输入过程中出现位数不正确的情形时,Excel能够给出提示会使我们的工作减少不必要的重复。我们可以使用“条件格式”和“公式”来实现这一要求。
1 设置“条件格式”和“公式”
假设“条件格式”要求为:把符合位数(16位或18位)的号码所在单元格的填充色设置为绿色,输入完成后,通过查看单元格的填充色是否变为绿色,就可以知道输入的正确性。
首先将存放身份证号码的A2:A15单元格区域数字格式设置为“文本”,然后在A2:A15单元格区域处于被选中的状态下,选择菜单“格式条件格式”命令,打开“条件格式”对话框,单击“条件1”下方的下拉箭头,在弹出的下拉列表中选择“公式”(图1)。
在其右边的文本框中输入公式“=OR(LEN(A2)=16,LEN(A2)=18)”,然后单击“格式”按钮,在打开的“单元格格式”对话框中选择“图案”选项卡,选择绿色作为符合条件的单元格的填充色(图2)。
设置好后单击“确定”按钮,返回“条件格式”对话框(图3),点击确定设置完毕。
2 实现效果
在A2:A15区域的单元格中输入一些身份证号码,当位数是18位或16位时,所在单元格的填充色自动变为“绿色”,而位数不对的号码所在单元格的填充色不发生任何改变(图4),从而可以判断输入的正确性。
全部输入并确认正确后需要删除单元格条件格式时,则先选中A2:A15单元格区域,然后打开“条件格式”对话框,单击如图三中的“删除”按钮,在打开的“删除条件格式”对话框中勾选“条件1”复选框,单击确定即可(图5)。
实例二:设置销售额的前三名的字体为蓝色、加粗
假设在工作表中(图6)B2:B12单元格中存放着销售额数据,要求找出其中的前三名且不改变其排序方式,将前三名以蓝色、加粗字体显示。
1) 设置“条件格式”和“公式”
先选中B2:B12单元格,选择菜单“格式条件格式”命令,打开“条件格式”对话框,输入公式“=B2>LARGE($B$2:$B$12,4)”,然后将符合条件的字体格式设置为蓝色,加粗,如图7。
点击确定设置完毕。
2) 实现效果:实现效果如图8。
实例三:突出显示符合特殊条件的日期
在excel中函数WEEKDAY(serial_number,return_type)的功能为返回某日期为星期几,Serial_number表示一个顺序的序列号,代表要查找的那一天的日期。当参数return_type为2时,函数返回数字 1(星期一)到数字 7(星期日)之间的整数。
假设要求当天为星期六或星期天的日期所在的单元格突出显示。
1) 设置“条件格式”和“公式”
以图六中数据为源数据,选中日期所在的单元格A2:A12,选择菜单“格式条件格式”命令,打开“条件格式”对话框,输入公式“=OR(WEEKDAY(A2,2)=6,WEEKDAY(A2,2)=7)”,然后设置符合条件的单元格图案为灰色-50%,如图9。
点击确定,设置完毕。
2) 实现效果:实现效果如图10。
实例四:隔行着色
当单元格数据行较多,我们为了让显示效果更加醒目,可以为工作表设置间隔一定的行数添加颜色。
在Excel中,函数MOD(number,divisor)返回两数相除的余数,其中Number为被除数,Divisor为除数。函数ROW(reference)返回引用的行号,其中Reference为需要得到其行号的单元格或单元格区域,如果省略 reference,则假定是对函数 ROW 所在单元格的引用。
1)设置“条件格式”和“公式”
假设要求工作表每隔一行添加灰色-25%的底纹。首先选择整个工作表,选择菜单“格式条件格式”命令,打开“条件格式”对话框,输入公式“=MOD(ROW(),2)=0”,设置格式(图11)
单击确定设置完毕。
2) 实现效果
实现效果如图12。
同样的设置方式,如果要间隔两行显示阴影则用公式“=MOD(ROW(),3)=0”,其余依次类推。
3 结束语
以上是“条件格式”与“公式”结合使用的四个实例。从中我们可以看出,巧妙的使用“条件格式”和“公式”一方面可以使我们的工作减少不必要的重复,一方面还能够使我们的工作效果更加明显。