塞纳自动梯(佛山)有限公司
CNIM TRANSPORT EQUIPMENT
IT 培训讲义
IT tutorial handout
Excel conditional formatting/Excel条件格式公式应用
题目
TITLE | IT 培训讲义 IT tutorial handout |
摘要 SUMMARY | 对excel 函数Conditional Formatting 进行培训。 The prent document describes the Excel conditional formatting |
版本 Rev雪茫茫. | 页码 变形计刘珈辰Pages | 修改日期 Date | 更改内容 Content of changes | 作者 Written by | 批准 Approved |
A | 11 | 2014-07-7 | | Herman | |
| | | | | |
| | | | 灵鸾妮妮 | |
女孩学什么舞蹈好 | | | | | |
| | | | | 小金鱼吃什么食物 |
| | | | | |
| | | | | |
|
| | | | | | |
Excel conditional formatting/Excel条件格式公式应用
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 ur to identify information in an easy way. If we u 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.
1.Conditional formatting using cell value/单元格数值的条件格式应用。
在使用条件格式时,首先选择要应用条件格式的单元格或单元格区域,然后单击菜单“格式─—条件格式”,出现如图所示的“条件格式”对话框。在Excel 2003及以前的版本中,条件格式最多只能设置三个条件。
When using conditional formatting头发简笔画, first lect the cell or area that you wants to u 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.
选择“单元格数值”时的条件格式对话框。此时,若在第一个组合框中选择“单元格数值”,则右侧的组合框中将提供“介于”、“未介于”、“等于”、“不等于”、“大于”、“小于”、“大于或等于
”、“小于或等于”等选项,并且在其右侧的输入框中可以输入相应的数值,也可以选择工作表中的单元格。然后,单击“格式”按钮,设置当条件为真时所应用的格式。
其中,选择“介于”时,包括设置的最大值和最小值,而选择“未介于”时,不包括设置的最大值和最小值。
When lect” Cell Calue is” in the condition list, in the cond lection 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 choo. We can input our conditional value in the right hand side cell.
Be careful, when lect ”between”, the maximum and minimum value is included. And when we choo “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位)的号码所在单元格的填充色设置为绿色,输入完成后,通过查看单元格的填充色是否变为绿色,就可以知道输入的正确性了。
For example we want to u 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.