Excel之条件格式和数据有效性.ppt

Excel之条件格式和数据有效性,倘塘林业站罗荣飞,e-mail:876955482,条件格式,什么是条件格式？就是根据指定的条件来确定单元格的显示格式。条件格式的设定，这里以EXCEL2003为例：选定要设定条件格式的单元格，单击菜单“格式”“条件格式”设定条件设定格式确定。条件格式最多只能设定三条。条件格式有两大设置方式：1、单元格数值；2、公式。,以“单元格数值”为条件的设定,以“单元格数值”为条件的设定有：介于、未介于、等于、不等于、大于、小于、大于或等于、小于或等于。其中“介于、未介于、等于、不等于”可以是数值或文本，而“大于、小于、大于或等于、小于或等于”只适合数值。“介于”可以理解为“包含”，其它的就比较好理解了。,以“公式”为条件的设定,以“公式”为条件的设定，只需公式输出的值为真或1即可按设定的格式显示数据，而公式千变万化，这也是条件格式设置中最强大的部分。,设定格式区域,使用条件格式时，设定格式区域的选择很重要。比如，您要B1开始向下选到Bx,再做条件格式设定。为什么要这样，因为条件格式中的判断条件是对参照点来说的,这个参照点就是您选择区域的起点。举例来说,如果条件公式是=OR(LEN(B1)=15,LEN(B1)=18)这个公式，是对B1这个格设定的。其他被选中的单元格使用这个条件的时候，会对公式进行单元格相对引用原则进行变动.单独去看B8的条件格式就会是=OR(LEN(B8)=15,LEN(B8)=18)这就是为什么您输入一个公式=OR(LEN(B1)=15,LEN(B1)=18)其他满足条件也会变色的原因。条件格式中的条件是参照选点单元格区域的起点来说的.整列选择的话，该列第一个单元格默认是起点。,整行着色特例1,例：C列中包含“甲”的单元格所在行整体着色选中所需的行，选菜单:格式条件格式.左框选公式右框输入:=FIND(甲,INDIRECT(C&ROW()旁边格式按钮,选字体/底纹等选绿色确定,整行着色特例2,例：C列中等于1的单元格所在行整体着色1、全选工作表；2、设置条件格式：公式为:=$C1=13、分别设定字体颜色。,1、将20且30的值以红色显示；,一、“单元格数值”的条件格式示例,2、将20和30的值以红色显示；,3、将值为“不合格”或“待检”的单元格以红色显示；,4、将值为“合格”的单元格以红色显示；,二、“公式”的条件格式示例,1、使用IF函数将20的值以红色；,2、使用or函数将值为20或“不合格”的以红色显示；,使用公式来设定条件格式的例子这里就举简单的这两个，更复杂的以后再分享吧。,Thankyou!,COMPANYLOGO,EXCEL数据有效性用法,一、选定要限制其数据有效性范围的单元格。在“数据”菜单上，单击“有效性”命令，再单击“设置”选项卡。指定所需的数据有效性类型：有序列的数值在“允许”下拉列表框中，单击“序列”。单击“来源”框，然后执行下列操作之一：若要就在框中定义序列，请键入序列值，并用逗号分隔。若要使用已命名的单元格区域，请键入等号(=)，随后键入区域的名称。若要使用单元格引用，请在工作表上选择单元格，然后按Enter。注释单元格引用必须指向相同工作表上的单元格。如果必须引用不同的工作表或工作簿，则请使用指定的名称，并确保工作簿已经打开。,确认选中“提供下拉箭头”复选框。1、数字有范围限制请在“允许”框中，单击“整数”或“小数”。在“数据”框中，单击所需的限制类型。例如，若要设值上限或下限，请单击“介于”。输入允许的最大值、最小值或特定值。2、日期或时间有范围限制请在“允许”框中，单击“日期”或“时间”。在“数据”框中，单击所需的限制类型。例如，若要使日期在某天之后，请单击“大于”。输入允许的开始、结束或特定日期或时间。,3、文本为指定长度在“允许”框中，单击“文本长度”。在“数据”框中，单击所需的限制类型。例如，若要使文本长度不超过指定长度，请单击“小于或等于”。输入最大、最小或指定的文本长度。4、计算基于其他单元格内容的有效性数据在“允许”框中，单击所需的数据类型。在“数据”框中，单击所需的限制类型。在“数据”框或其下面的框中，单击用于指定数据有效性范围的单元格。例如，如果只有在结果不超出预算时，才许可输入帐户，则可单击“允许”框中的“小数”，再单击“数据”框中的“小于或等于”，然后在“最大值”框中单击包含预算的单元格。,5、使用公式计算有效性数据在“允许”框中，单击“自定义”。在“公式”编辑框中，输入计算结果为逻辑值（数据有效时为TRUE，数据无效时为FALSE）的公式。例如，如果只有在没有对任意帐户(cellD6)做预算时，野餐帐户单元格中的数值才有效，并且预算总值(D20)也小于已分配的40000美元，则可输入自定义公式=AND(D6=0,D2040000)。指定单元格是否可为空白单元格。如果希望空白单元格（空值）有效，请选中“忽略空值”复选框。如果要避免输入空值，请清除“忽略空值”复选框。注释如果数值的有效性是基于已命名的单元格区域，并且在该区域中有空白单元格，则设置“忽略空值”复选框将使有效单元格中输入的值都有效。同样，为有效性公式所引用的任何单元格也如此：如果引用的单元格是空值，则设置“忽略空值”复选框将使有效单元格中输入的值都有效。若要在单击该单元格后显示一个可选择的输入信息，请单击“输入信息”选项卡，选中“选定单元格时显示输入信息”复选框，然后输入该信息的标题和正文。,二、在输入无效数据时，指定MicrosoftExcel的响应方式：,操作方法单击“出错警告”选项卡，选中“输入无效数据时显示出错警告”复选框。请在“样式”框中选择下列选项之一：若要显示可输入无效数据的信息，请单击“信息”。若要显示警告信息，但仍可输入无效数据，请单击“警告”。若要阻止输入无效数据，请单击“停止”。填写该信息的标题或正文（最多225个字符）。注释如果不输入标题或正文，则标题就被默认为“MicrosoftExcel”，信息默认为“输入的值无效”。用户已经对输入到该单元格的数值做了限制。注释将数据有效性应用于单元格并不影响单元格的格式。,