2017年湖北省技能高考新增考点系列之：数据有效性检查教程.doc

excel 中数据的有效性的应用 ?防止数据输入错误 ??????? 典型应用如下： ??????? （1） 防止日期错误： ??????? 只准输入日期或某个日期之后的特定日期：点击EXCEL菜单“数据- HYPERLINK /search/blog.php?type=blogkeyword=%E6%9C%89%E6%95%88%E6%80%A7 \t _blank 有效性-”， 在“设置-允许”对话框中选择“日期”、并在相应位置输入起始日期。 ????????? ?????? （2） 只准输入整数： ??????? 在“设置-允许”对话框中选择“整数” ??????? （3） 防止输入重复值： ??????? 首先要选定整行/列或单元格区域（比如选中B列），然后再点击EXCEL菜单“数据- HYPERLINK /search/blog.php?type=blogkeyword=%E6%9C%89%E6%95%88 \t _blank 有效性-”， 在“设置”对话框中选择“自定义”、在“公式”中输入“=COUNTIF(B:B,B1)2” ???????? ??????? 检验一下：在B3单元格中输入“A”，看看会出现什么结果？ ??????? （4） 只能输入大于上一行的数值（或日期）： ??????? ??????? 注意引用区域的最后一行行标为相对引用。A4的有效性公式为：=MAX($B$3:$B4) ??????? 4、 条件输入 ??????? 只准输入符合一定条件的数据： ??????? （1） 只能输入大于左侧的数字 ???????? ??????? （2） 按条件输入_根据左侧条件来决定右侧单元格如何输入： ??????? 下图中，如果单据类型选择了“入库单”，则只能在“入库数量”所在列即C列输入数据，而不能在“出库数量”所在列即D列输入数据；反之亦然。 ??????? ??????? C列公式为：=IF(B5=入库单,ISNUMBER(C5),FALSE) ??????? D列公式为：=IF(B5=出库单,ISNUMBER(D5),FALSE) [应用一]下拉菜单输入的实现 例1：直接自定义序列 有时候我们在各列各行中都输入同样的几个值，比如说，输入学生的等级时我们只输入四个值：优秀，良好，合格，不合格。我们希望Excel2000单元格能够象下拉框一样，让输入者在下拉菜单中选择就可以实现输入。 操作步骤：先选择要实现效果的行或列；再点击数据\有效性，打开数据有效性对话框；选择设置选项卡，在允许下拉菜单中选择序列；在数据来源中输入优秀，良好，合格，不合格（注意要用英文输入状态下的逗号分隔！）；选上忽略空值和提供下拉菜单两个复选框。点击输入信息选项卡，选上选定单元格显示输入信息，在输入信息中输入请在这里选择。 例2：利用表内数据作为序列源。 有时候序列值较多，直接在表内打印区域外把序列定义好，然后引用。 操作步骤：先在同一工作表内的打印区域外要定义序列填好(假设在在Z1：Z8），如“单亲家庭，残疾家庭，残疾学生，特困，低收人，突发事件，孤儿，军烈属”等，然后选择要实现效果的列（资助原因）；再点击数据\有效性，打开数据有效性对话框；选择设置选项卡，在允许下拉菜单中选择序列；“来源”栏点击右侧的展开按钮（有一个红箭头），用鼠标拖动滚动条，选中序列区域Z1：Z8（如果记得，可以直接输入=$Z$1:$Z$8；选上忽略空值和提供下拉菜单两个复选框。点击输入信息选项卡，选上选定单元格显示输入信息，在输入信息中输入请在这里选择。 例3：横跨两个工作表来制作下拉菜单 用INDIRECT函数实现跨工作表 在例2中，选择来源一步把输入=$Z$1:$Z$8换成=INDIRECT(表二!$Z$1:$Z$8),就可实现横跨两个工作表来制作下拉菜单。 [应用二]自动实现输入法中英文转换 有时，我们在不同行或不同列之间要分别输入中文和英文。我们希望Excel能自动实现输入法在中英文间转换。 操作步骤：假设我们在A列输入学生的中文名，B列输入学生的英文名。先选定B列，点击进入数据\有效性，打开数据有效性对话框；选择输入法对话框，在模式下拉菜单中选择关闭（英文模式）；然后再确定，看看怎么样。 [应用三]数据唯一性检验 员工的身份证号码应该是唯一的，为了防止重复输入，我们用“数据有效性”来提示大家。 操作步骤：选中需要建立输入身份证号码的单元格区域（如B2至B14列），执行“数据→有效性”命令，打开“数据有效性”对话框，在“设置”标签下，按“允许”右侧的下拉按钮，在随后弹出的快捷菜单中，选择“自定义”选项，然后在下面“公式”方框中输入公式：=COUNTIF(B:B,B2)=1，确定返回。以后在上述单元格中输入了重复的身份证号码时，