数据有效性概述与示例.docx

数据有效性概述与示例什么是数据有效性验证？Microsoft Excel 数据有效性验证使您可以定义要在单元格中输入的数据类型例如，您仅可 以输入从 A 到 F 的字母 您可以设置数据有效性验证，以避免用户输入无效的数据，或 者允许输入无效数据，但在用户结束输入后进行检查您还可以提供信息，以定义您期望在 单元格中输入的内容，以及帮助用户改正错误的指令如果输入的数据不符合您的要求，Excel将显示一条消息，其中包含您提供的指令当您所设计的表单或工作表要被其他人用来输入数据（例如，预算表单或支出报表）时，数 据有效性验证尤为有用本文介绍了如何设置数据有效性验证，包括可以进行验证的数据类型和可以显示的消息还 提供了一个工作簿，您可以下载该工作簿，以获取您可以在自己的工作表上进行修改和使用 的有效性验证的示例可以验证的数据类型Excel 使您可以为单元格指定以下类型的有效数据：数值 指定单元格中的条目必须是整数或小数您可以设置最小值或最大值，将某个数值 或范围排除在外，或者使用公式计算数值是否有效日期和时间 设置最小值或最大值，将某些日期或时间排除在外，或者使用公式计算日期 或时间是否有效长度 限制单元格中可以输入的字符个数，或者要求至少输入的字符个数。

值列表 为单元格创建一个选项列表（例如小、中、大），只允许在单元格中输入这些值 用户单击单元格时，将显示一个下拉箭头，从而使用户可以轻松地在列表中进行选择可以显示的消息类型 对于所验证的每个单元格，都可以显示两类不同的消息：一类是用户输入数据之前显示的消 息，另一类是用户尝试输入不符合要求的数据时显示的消息如果用户已打开 Office 助手， 则助手将显示这些消息输入消息 一旦用户单击已经过验证的单元格，便会显示此类消息您可以通过输入消息 来提供有关要在单元格中输入的数据类型的指令错误消息 仅当用户输入无效数据并按下 Enter 时，才会显示此类消息您可以从以下三 类错误消息中进行选择： 信息消息 此类消息不阻止输入无效数据除所提供的文本外，它还包含一个消息图标、 一个“确定”按钮（用于在单元格中输入无效数据）和一个“取消”按钮（用于恢复单元格 中的前一个值）警告消息 此类消息不阻止输入无效数据它包含您提供的文本、警告图标和三个按钮： “是”用于在单元格中输入无效数据；“否”用于返回单元格进一步进行编辑；“取消”用于 恢复单元格的前一个值停止消息 此类消息不允许输入无效数据它包含您提供的文本、停止图标和两个按钮： “重试”用于返回单元格进一步进行编辑）；“取消”用于恢复单元格的前一个值。

请注意， 不能将此类消息作为一种安全措施：虽然用户无法通过键入和按 Enter 输入无效数据，但 是他们可以通过复制和粘贴或者在单元格中填写数据的方式来通过验证如果未指定任何信息，则 Excel 会标记用户输入数据是否有效，以便您以后进行检查，但 用户输入的数据无效时，它不会通知用户有效性验证的示例 在操作中进行检验是了解数据有效性验证功能的最佳方法如果您下载本文附带的示例工作 簿，您将获得所有有效性验证类型的示例和所有消息类型的示例工作簿包括一些指令，用 于查看每个被验证的单元格所使用的设置，并将有效性验证设置复制到您自己的工作簿（您 可以在此工作簿中根据需要修改这些设置）设置数据有效性验证 确定要在工作簿中使用的有效性验证后，您可以使用“数据”菜单中的“有效性”命令对其 进行设置您将发现，从本文下载的示例工作簿中的指令也可以通过 Excel 2002 的帮助获 得以下是该过程的一般概述：设置工作表 通过在工作表中输入数据和公式开始如果您要使用有效选项列表，请输入 列表并为其命名定义单元格的设置 从要验证的第一个单元格开始，使用“数据有效性”对话框指定所需 的有效性验证类型、输入信息（如果需要）和错误信息（如果需要）。

设置其他单元格的有效性验证 将有效性验证设置从第一个单元格复制到其他单元格，然 后对设置进行修改，这样做通常可以节省时间测试有效性验证规则 尝试在单元格中输入有效和无效数据，以确保设置正常工作并且消 息如期显示使用“有效性”命令对这些设置进行更改在一个单元格中更改有效性验证后， 您可以将这些更改自动应用至其他具有相同设置的所有单元格中设置有效选项列表 如果您使用了有效选项列表并且不希望用户查看和更改列表，您可以 将列表置于另一个工作表中，设置有效性验证，隐藏包含该列表的工作表，然后使用密码保 护工作簿工作簿密码将有助于保护包含列表的工作表在需要时应用保护 如果要保护工作表或工作簿，请在设置完有效性验证后进行保护工 作表之前，请确保“解除锁定”任何已经过验证的单元格，否则，用户将无法在单元格中输 入内容在需要时共享工作簿 如果您要共享工作簿，请在完成有效性验证和保护的设置后执行 共享工作簿后，除非您停止共享，否则将不能更改有效性验证的设置，但是在共享工作簿时， Excel 将继续验证指定的单元格检查无效数据的结果 用户在工作表中输入数据后，您可以按照本文后面的介绍检查无效 数据在验证的单元格中输入数据以下是用户输入数据的过程。

您可以使用输入消息和错误消息，提供一些用户需要了解的指 令，使用户了解您如何设置工作表以确保数据正确要查看此过程，请下载本文附带的示例 工作簿，并查看“消息”工作表中的示例查看输入消息 用户单击经过验证的单元格或使用箭头键移至单元格时，您输入的消息将 以“助手”气球或单独的消息框方式显示如果您为单元格提供了下拉列表，则单元格右侧 将显示下拉箭头键入数据 当用户键入数据或单击下拉箭头从列表中选择值时，输入的消息将停留在屏幕 上（下拉列表可能会覆盖部分消息）输入有效数据如果用户输入有效数据并按下Enter，则数据将被输入到单元格并且不会 出现特殊情况输入无效数据 如果用户输入的数据不符合条件，并且您为无效数据指定了错误消息，则 错误消息将显示在“助手”气球或单独的消息窗口中用户可以阅读消息，并决定如何处理对于信息消息，用户可以单击“确定”以输入无效数据，或者单击“取消”重新开始 对于警告消息，用户可以单击“是”以输入无效数据，单击“否”继续编辑单元格，或者单 击“取消”重新开始对于停止消息，用户将无法输入无效数据，但可以单击“重试”编辑单元格，或者单击“取 消”重新开始如果未提供任何消息，则对于用户来说，在经过验证的单元格中输入数据与一般的 Excel 数 据输入相同。

然而， Excel 会标记包含无效条目的单元格，以便于您查找这些内容检查工作表中的无效内容 收回用户可能在其中输入了无效数据的工作表后，您可以使 Excel 将不符合条件的所有数 据画上红色圆圈，以便于查找工作表中的错误要实现此目的，请使用“公式审核”工具栏 上的“圈释无效数据”按钮和“清除无效数据标识圈”按钮因为单元格中的值不符合标准，所以用圆圈标记更正单元格中的数据后，圆圈将消失要查看此功能，请下载本文附带的示例工作簿，并查 看“无效数据”工作表公式是单个或多个函数的结合运用AND “与”运算，返回逻辑值，仅当有参数的结果均为逻辑“真（TRUE）”时返回逻辑“真（TRUE）”，反之返回逻辑“假（FALSE）”条件判断AVERAGE 求出所有参数的算术平均值 数据计算COLUMN 显示所引用单元格的列标号值 显示位置CONCATENATE 将多个字符文本或单元格中的数据连接在一起，显示在一个单元格中 字 符合并COUNTIF 统计某个单元格区域中符合指定条件的单元格数目 条件统计DATE 给出指定数值的日期 显示日期DATEDIF 计算返回两个日期参数的差值 计算天数DAY 计算参数中指定日期或引用单元格中的日期天数。

计算天数DCOUNT 返回数据库或列表的列中满足指定条件并且包含数字的单元格数目 条件统计FREQUENCY 以一列垂直数组返回某个区域中数据的频率分布 概率计算IF 根据对指定条件的逻辑判断的真假结果，返回相对应条件触发的计算结果 条件计算INDEX 返回列表或数组中的元素值，此元素由行序号和列序号的索引值进行确定 数据 定位INT 将数值向下取整为最接近的整数 数据计算ISERROR用于测试函数式返回的数值是否有错如果有错，该函数返回TRUE,反之返回FALSE 逻辑判断LEFT 从一个文本字符串的第一个字符开始，截取指定数目的字符 截取数据LEN 统计文本字符串中字符数目 字符统计MATCH 返回在指定方式下与指定数值匹配的数组中元素的相应位置 匹配位置MAX 求出一组数中的最大值 数据计算MID 从一个文本字符串的指定位置开始，截取指定数目的字符 字符截取MIN 求出一组数中的最小值 数据计算MOD 求出两数相除的余数 数据计算MONTH 求出指定日期或引用单元格中的日期的月份 日期计算NOW 给出当前系统日期和时间 显示日期时间OR仅当所有参数值均为逻辑“假（FALSE）”时返回结果逻辑“假（FALSE）”，否则都返 回逻辑“真（TRUE）”。

逻辑判断RANK 返回某一数值在一列数值中的相对于其他数值的排位 数据排序RIGHT 从一个文本字符串的最后一个字符开始，截取指定数目的字符 字符截取SUBTOTAL 返回列表或数据库中的分类汇总 分类汇总SUM 求出一组数值的和 数据计算SUMIF 计算符合指定条件的单元格区域内的数值和 条件数据计算TEXT 根据指定的数值格式将相应的数字转换为文本形式 数值文本转换TODAY 给出系统日期 显示日期VALUE 将一个代表数值的文本型字符串转换为数值型 文本数值转换VLOOKUP 在数据表的首列查找指定的数值，并由此返回数据表当前行中指定列处的数值 条件定位WEEKDAY 给出指定日期的对应的星期数 星期计算Excel 部分函数列表.函数的步骤：①选中存放结果的单元格② 单击“ =”（编辑公式）③ 找函数（单击“三角形”形状按钮或者直接输入函数名④ 选范围⑤ CTRL+回车键① 求和函数SUM（）② 平均函数AVERAGE（）③ 排位函数RANK （）例： Rank（A1： $A$1： $A$15）行号和列号前面加上“$ “符号A叫行号1或者15叫列号，表示单元格所在的位置数据 单元格在A列1号或者是A列15号④ 最大值函数 MAX （）⑤ 最小值函数 MIN （）⑥ 统计函数 COUNTIF（ ） 计算满足给定单元格的数目例：Countif （ A1： B5, ” >60”）统计分数大于 60 分的人数，注意，条件要加双引号，在英文状态下输入。

⑦ 单元格内容合并 CONCTENATE（） 将若干文字串合并到一个字符串中⑧ RIGHT（A1， 4） 提取字符串最右边的若干字符，长度为 4位⑨ LEFT （） 返回字符串最左边的若干字符⑩ MIDB（）自文字的指定位置开始向右提取指定长度的字符串11、重复函数 REPT（） 单元格重量出现的次数12、 NOW（）返回电脑内部的系统日期与时间13、MONTH（ ） 将序列数转换为对应的月份数编者语： Excel 是办公室自动化中非常重要的一款软件，很多巨型国际企业都是依靠 Excel 进行数据管理它不仅仅能够方便的处理表格和进行图形分析，其更强大的功能体现在对数 据的自动处理和计算，然而很多缺少理工科背景或是对Excel强大数据处理功能不了解的人 却难以进一步深入编者以为，对Excel函。