塞纳自动梯(佛山)有限公司
CNIM TRANSPORT EQUIPMENT
IT 培训讲义 IT tutorial handout
Excel conditional formatting/Excel条件格式公式应用
1
题目 TITLE 摘要 SUMMARY 版本 页码 Rev. Pages A 11 IT 培训讲义 IT tutorial handout 对excel 函数Conditional Formatting 进行培训。 The present document describes the Excel conditional formatting 修改日期 Date 2014-07-7 更改内容 Content of changes 作者 Written by Herman 批准 Approved
2
Excel conditional formatting/Excel条件格式公式应用
１．Conditional formatting using cell value/单元格数值的条件格式应用。 ............ 4
2.conditional formatting using formula/公式的条件格式条件格式应用。 ................ 5
2.1 conditional formatting for input check/判别输入是否正确 ................................... 5 2.1.1 create formula for conditional formatting/创建“条件格式”的公式 .................... 5
2.1.2 effect or result/实现的具体效果 .......................................................................... 8
2.2 find out sales digit top 3/找出销售额的前三名 ...................................................... 9
2.3 find out special date match the condition /让符合特殊条件的日期突出显示 .... 10
2.4 show grey shadow in odd or even row /让工作表间隔固定行显示阴影 ............ 10
3
Excel conditional formatting/Excel条件格式公式应用
我们知道，Excel“条件格式”功能可以根据单元格内容有选择地自动应用格式，它为Excel增色不少的同时，还为我们带来很多方便。如果让“条件格式”和公式结合使用，则可以发挥更大的威力，下面提供几个在“条件格式”中使用公式的应用实例，希望能给读者朋友带来一些启发。
When using excel conditional formatting, excel can change cell format according to the content of the cell. It can help user to identify information in an easy way. If we use conditional formatting with formula, we can have more powerful function in excel. The following is some examples in conditional formation, hope can help you to have some idea in using this function.
１．Conditional formatting using cell value/单元格数值的条件格式应用。
在使用条件格式时，首先选择要应用条件格式的单元格或单元格区域，然后单击菜单“格式─—条件格式”，出现如图所示的“条件格式”对话框。在Excel 2003及以前的版本中，条件格式最多只能设置三个条件。
When using conditional formatting, first select the cell or area that you wants to use conditional formatting, then click on menu “Format->conditional formatting”. A pop up window for condition will appear. In Excel2003 and ealier version, maximum you can define 3 condition.
选择“单元格数值”时的条件格式对话框。此时，若在第一个组合框中选择“单元格数值”，则右侧的组合框中将提供“介于”、“未介于”、“等于”、“不等于”、“大于”、“小于”、“大于或等
4
于”、“小于或等于”等选项，并且在其右侧的输入框中可以输入相应的数值，也可以选择工作表中的单元格。然后，单击“格式”按钮，设置当条件为真时所应用的格式。
其中，选择“介于”时，包括设置的最大值和最小值，而选择“未介于”时，不包括设置的最大值和最小值。
When select” Cell Calue is” in the condition list, in the second selection list
provides ”between”, “not between”, “equal to”, “not equal to”, ”greater than”, ”less than”, ”greater than or equal to”, “less than or equal to” too choose. We can input our conditional value in the right hand side cell.
Be careful, when select ”between”, the maximum and minimum value is included. And when we choose “not between”, the maximum and minimum value is not included.
2.conditional formatting using formula/公式的条件格式条件格式应用。
2.1 conditional formatting for input check/判别输入是否正确
如果我们需要在excel中输入大量的数据同时想excel针对这些数据进行一些提醒。例如在输入如身份证等有固定位数的号码，出现位数不正确的情形时，我们希望Excel能够给出提示。
If we need to input a large quantities of data into excel and want some notice about the input. For example if we need to input personal ID card information. All ID card has a fix length of number, when there is an error in the length, we wants excel to give notice.
2.1.1 create formula for conditional formatting/创建“条件格式”的公式
假设我们通过“条件格式”，把符合位数(15位或18位)的号码所在单元格的填充色设置为绿色，输入完成后，通过查看单元格的填充色是否变为绿色，就可以知道输入的正确性了。
5
For example we want to use conditional formatting to identify the ID number length, fill cell with green if the ID length is 15 or 18. By this way we just need to check the cell color, if the cell in green that means our input length is correct.
由于身份证号码数据是属于“文本”类型的，先选中需要存放身份证号码的A2:A52单元格区域，将它们的数字格式设置为“文本”。然后在A2:A52单元格区域处于被选中的状态下，选择菜单“格式→条件格式”命令，打开“条件格式”对话框，单击“条件1”下方的下拉箭头，在弹出的下拉列表中选择“公式”(图 1)。
Because ID number value is “text” format, first we select cell area A2:A52 and set the cell value format to text. And in the selection of this area status, we select
menu ”format->conditional formatting”. In the popup window, choose” Condition 1”drop down list, in the selection we choose “Formula is”. See picture 1.
接着在其右边的文本框中输入公式“=OR(LEN(A2)=15,LEN(A2)=18)”，然后单击“格式”按钮，在打开的“单元格格式”对话框中选择“图案”选项卡，选择绿色作为符合条件的单元格的填充色(图 2)。
Then in the right panel box input formula” =OR(LEN(A2)=15,LEN(A2)=18)”, click format button, in the new pop up window “Format Cells” select “Patterns”, choose green as the color to fill in cells which value is correct. See picture 2.
6
设置好后单击“确定”按钮，返回“条件格式”对话框，检查无误再次单击“确定”就完成了条件格式的设置(图 3)。
After setting click “ok” button to go back to “Conditional Formatting” window, and click “ok” button to finished conditional format setting. See picture 3
小提示：上面的操作，先选中了一个单元格范围A2:A52然后为这个单元格范围设置条件格式的公式。在这种情况下，公式中应使用选择范围中左上单元格的引用，此例中为A2。公式输入完成后，可以查看一下这个范围中的其它单元格的条件格式公式，如A8单元格，为“=OR(LEN(A8)=15,LEN(A8)=18)”，这是由于上面的引用为相对应用，它会根据单元格的实际偏移量自动改变，从而得到适合其它单元格的公式。
Notice: in the above action, we choose cell area A2:A5, then set conditional format for this cell area. In this situation, when we set formula we need to use the top cell in the formula,
7
in the above example we use A2. after setting the formula, we can check other cells see their formula, for example cell A8 formula is “=OR(LEN(A8)=15,LEN(A8)=18)”, the formula will change to correct formula according to the cell number change.
2.1.2 effect or result/实现的具体效果
现在来测试一下上面设置可以实现的效果，在A2：A52区域的单元格中输入一些身份证号码，当位数是18位或15位时，所在单元格的填充色自动变为“绿色”，而位数不对的身份证号码，所在单元格的填充色不发生任何改变(图 4)，从是否变色我们就可以判断输入的正确性了。
Now we can test the result of the above example. In cell area A2:52 input some ID number, when ID number length is 18 or 15 character, the cell will automatically change to green color, when ID number length is not correct, the cell color remain no change. See picture 4, by the color of the cell we can judge the value format is correct or not.
全部输入并确认正确后，如果需要删除单元格条件格式，则先选中A2:A52单元格区域，然后打开“条件格式”对话框，单击如图3中的“删除”按钮，在打开的“删除条件格式”对话框中勾选“条件1”复选框，单击确定即可(图 5)。
After all input is check and confirm that is correct, if we need to delete the cell conditional formatting, just select cell area A2:A52, open “Conditional Formatting” Windows again, click delete button. See picture 3 above. In the open “Delete Conditional Formatting”
8
window, select “Condition 1”, then click ok button. See picture 5. Now conditional formatting has been deleted.
2.2 find out sales digit top 3/找出销售额的前三名
如图6中的B2:B12单元格中存放着销售额数据，要找出其中的前三名，让它们以蓝色字体显示。
See picture 6, in cell area B2:B12 store sales digit, we wants to find out the top 3 sales digit, and show them in blue.
先选中B2:B12单元格，打开如图1所示的对话框，输入公式“=B2>LARGE($B$2:$B$12,4)”然后将符合条件的字体格式设置为蓝色即可。
First select cell area B2:B12, open Conditional Formatting window show as picture 1, in the formula box, input formula “=B2>LARGE($B$2:$B$12,4)” then set in “Fonts” color to blue.
9
2.3 find out special date match the condition /让符合特殊条件的日期突出显示
有时 ，我们可能希望符合特殊条件的日期所在的单元格突出显示，比如星期六或星期天。这时我们可以先选中日期所在的单元格，如图6中的A2:A12，然后打开如图1所示的单元格，输入公式“=OR(WEEKDAY(A2,2)=6,WEEKDAY(A2,2)=7)”，然后设置符合条件的单元格填充色为阴影即可。
Sometimes we want to highlight some date which match certain condition, for example Saturday or Sunday, here we can choose the cell area which store date information, for example A2:A12 in picture 6, then open “Conditional Formatting”. See picture 1. in the formula box input formula “=OR(WEEKDAY(A2,2)=6,WEEKDAY(A2,2)=7)”, then set grey shadow in “Patterns” to fill the cell match the formula.
2.4 show grey shadow in odd or even row /让工作表间隔固定行显示阴影
当单元格数据行较多，我们为了让显示效果更加醒目，可以让工作表间隔固定行显示阴影，效果如下图所示(图 7)。
If we have an excel contain a lot of Rows, and we want the rows more clear. We can set grey shadow to odd or even number rows. Result like picture 7 below
10
上面的效果是使用了公式“=MOD(ROW(),2)=0”，如果要间隔两行显示阴影则用公式“=MOD(ROW(),3)=0”，其余依次类推。
To have the above effect, we can use formula “=MOD(ROW(),2)=0”, and if we want to separate in every 2 rows, we can use “=MOD(ROW(),3)=0”.
小提示：函数MOD(number,divisor)返回两数相除的余数，其中Number为被除数，Divisor为除数。函数ROW(reference)返回引用的行号。其中Reference()为需要得到其行号的单元格或单元格区域，如果省略 reference，则假定是对函数 ROW 所在单元格的引用。 Notice: Formula MOD(number,divisor) return value of 2 digit of division, in the formula, number is dividend, divisor is divider. Formula ROW(reference) return row number. In this formula Reference() is the cell or cell area that you need to get the row number, if reference is empty, the default value of Formula Row refer to the whole worksheet.
11
因篇幅问题不能全部显示，请点此查看更多更全内容