数据有效性概述与示例
什么是数据有效性验证？
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 部分函数列表.
函数的步骤：①选中存放结果的单元格
②单击“=”（编辑公式）
③函数（单击“三角形”形状按钮。或者直接输入函数名
④选范围
⑤CTR
L+回车键
①求和函数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函数应用的不了解正是阻挡普通用户完全掌握Excel的拦路虎，然而目前这一部份内容的教学文章却又很少见，所以特别组织了这一个《Excel函数应用》系列，希望能够对Excel进阶者有所帮助。《Excel函数应用》系列，将每周更新，逐步系统的介绍Excel各类函数及其应用，敬请关注！
----------------------------------
术语说明
什么是参数？参数可以是数字、文本、形如 TRUE 或 FALSE 的逻辑值、数组、形如 #N/A 的错误值或单元格引用。给定的参数必须能产生有效的值。参数也可以是常量、公式或其它函数。
参数不仅仅是常量、公式或函数，还可以是数组、单元格引用等：
1.数组--用于建立可产生多个结果或可对存放在行和列中的一组参数进行运算的单个公式。在 Microsoft Excel有两类数组：区域数组和常量数组。区域数组是一个矩形的单元格区域，该区域中的单元格共用一个公式；常量数组将一组给定的常量用作某个公式中的参数。
2.单元格引用--用于表示单元格在工作表所处位置的坐标值。例如，显示在第 B 列和第 3 行交叉处的单
元格，其引用形式为"B3"。
3.常量--常量是直接键入到单元格或公式中的数字或文本值，或由名称所代表的数字或文本值。例如，日期 10/9/96、数字 210 和文本"Quarterly Earnings"都是常量。公式或由公式得

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。