下载文档原格式
数据的有效性----设置输入条件在不少情况下，设置好输入条件后，能增加数据的有效性，避免非法数据的录入。
如年龄为负数等。
那末有没有办法来避免这种情况呢？有，就是设置数据的有效性。
设置好数据的有效性后，可以避免非法数据的录入。
下面我们还是以实例来说明如何设置数据的有效性。
1. 设置学生成绩介于 0----100 分之间。
图 6-4-1假设试卷的满分 100 分，因此学生的成绩应当介于 0----100 分之间的。
我们可以通过以下几步使我们录入成绩时保证是 0----100 分之间，其它的数据输入不能输入。
第 1 步：选择成绩录入区域。
此时我们选中 B3：C9 。
第 2 步：点击菜单数据―>有效性，弹出数据有效性对话框。
界面如图 6-4-2 所示。
图 6-4-2在图 6-4-2 中的默认的是允许任何值输入的，见图中红色区域所示。
第 3 步：点击允许下拉按钮，从弹出的选项中选择小数。
(如果分数值是整，此处可以选择整数)图 6-4-3选择后界面如图 6-4-4 所示。
在图 6-4-4 中，数据中选择介于，最小值： 0 ，最大值：100 。
图 6-4-4第 4 步：点击确定按钮，完成数据有效性设置。
下面我们来看一下，输入数据时有何变化。
见图 6-4-5 所示。
图 6-4-5从图中可以看出，我们输入 80 是可以的，因为 80 介于 0----100 之间。
而输入 566 是不可以的，因为它不在 0----100 之间。
此时，点击重试按钮，重新输入，点击取销按钮，输入的数据将被清除。
第 5 步：设置输入信息在数据有效性对话框中，点击输入信息页，打开输入信息界面。
如图 6-4-6 所示。
图 6-4-6设置好输入信息后，我们再来看输入数据时，界面有何变化。
如图 6-4-7 所示。
图 6-4-7从图中可见，输入时多了人性化的提示，而这个提示信息就是我们刚刚设置的内容。
第 6 步：设置出错警告在数据有效性对话框中，点击出错警告页，在出错警告页我们可以设置警告信息。
1、下拉列表选择数据（直接输入）我们可以使用“数据→有效性”来进行处理。
具体操作方法为：先选中要输入性别的单元格，比如C4:C50，选择菜单“数据→有效性”命令，打开“数据有效性”对话框，单击“设置”选项卡的“允许”下拉箭头，在弹出的菜单中选择“序列”项，然后在“来源”框中输入“男,女”，注意其中的逗号是在英文状态下输入的。
单击“确定”即可。
2、下面就通过一个编排教师的课表为例教大家如何实现，该Excel表格能在填表时选择教师姓名，并能在另一列表中选择他所负责的课程名称。
一创建数据源表在sheet2表中输入教师姓名以及所负责的课程，把教师姓名横放在第2行。
选中B2:F2，即教师姓名。
然后在名称框为它输入一个名字“name”（图1），输入完成后一定要按回车，转到sheet1工作表。
二数据关联为了在sheet1表引用name名称，在教师姓名列下拉框选（B3:B9）单元格，点击菜单栏中的“数据→有效性”，在弹出的“数据有效性”对话框中选择“设置”选项卡，在“允许”选择框中选择“序列”，在来源输入框中输入“=name”（图2），点击“确定”后，在下拉列表中就可选择各个教师了。
提示：现在就可体会出名称框的妙用，因为来源的拾取按钮是不能跨表去拾取其他表的数据的。
第二步就是实现能够自动选择教师所负责的课程，由于教师姓名是变动的，要求负责的课程名称也要随之变动。
负责课程这一列中的有效性数据来自于教师姓名这一列，怎么解决这个问题？同样，我们可用名称框来解决。
回到sheet2表，用不着给表中的每个教师的课程单独取名，很麻烦也很耽误时间。
把整个区域选中（B2:F6），用每一列的第一行数据取名，点击“插入→名称→指定”，在指定名称对话框中只选中“首行”（图3），点击“确定”后就可在sheet1表中使用了。
转到sheet1表，把负责课程列下的区域选中（C3:C9），点击“数据→有效性→序列”。
接着就要注意来源输入框中的内容了，因为不能等于单元格，在这里希望引用教师姓名所对应的名称里的数据来做下拉列表，这里要用到函数indirect，它表示从某一单元格中取数据，然后把此数据转换成一个区域。
EXCEL中数据有效性的设置与应用在日常办公中，Excel作为一种强大的数据处理工具，深受用户的喜爱。
特别是在数据有效性管理方面，它提供了多种功能，帮助用户确保输入数据的准确性和一致性。
通过合理配置数据有效性，用户不仅可以避免因错误输入导致的数据混乱，还能提高工作效率。
接下来，将深入探讨Excel中数据有效性的设置及其应用。
数据有效性的基本概念数据有效性是指对单元格中输入内容的限制与控制。
通过设置有效性规则，用户可以在输入数据时设定条件，确保输入值在指定范围内的合法性。
例如，可以限制某一单元格只能输入数字、特定文本，甚至下拉选择等，提升数据的规范性。
设置数据有效性的步骤在Excel中，设置数据有效性并不复杂，按照几个步骤即可完成。
选择单元格：选中需要设置有效性的单元格或区域。
打开数据有效性对话框：在菜单栏找到“数据”选项，点击“数据有效性”按钮，打开相关设置窗口。
设置有效性条件：在弹出的对话框中，用户可以选择不同的有效性类型。
例如：数字：限制输入为特定数字范围内的数值。
日期：设置日期范围，确保用户输入在有效日期内。
列表：通过下拉菜单提供选择项，避免用户输入错误文本。
输入提示与错误警告：除了基本限制，用户还可以设置输入提示，提示用户可输入的内容。
设置错误警告，当输入不符合要求时，给予用户明确反馈。
确认设置：完成设置后，点击“确定”按钮，数据有效性设置即生效。
应用场景分析数据有效性的实际应用场景广泛，尤其是在以下几个方面尤为突出。
财务报表制作在财务报表中，数量、金额及比例等数据极为重要。
此时，借助数据有效性，可以设定金额只能输入正数，防止因错误输入导致的不准确。
可以通过列表限制账户名称，提高报告的规范性和可靠性。
项目管理在项目管理中，通常需要对不同阶段的进度、预算和风险等级进行监控。
通过数据有效性设置，可以强制每一项进度更新只能选择预设的进度状态，确保信息上传的准确性，便于后续分析与决策。
人事管理对于员工信息的录入，通过设置有效性，能够确保如入职日期、薪资等关键字段的数据规范，避免因手误产生错误。
Excel数据有效性怎么设置（excel的公式和数据的使用技巧）在实际的工作中，我们经常遇到数据不规范的情况，如果数据源不够规范，处理起来费时费力……此篇文章我们来学习一些含金量极高的数据有效性设置技巧。
一、数据唯一性。
目的：禁止输入重复工号并进行提示。
方法：1、选择工号所在的列（暨A列）。
2、【数据】-【数据验证】，选择【允许】中的自定义，在公式中输入=COUNTIF(A:A,A3)=1。
3、单击【出错警告】标签，输入【标题】和【错误信息】并【确定】。
解读：工号同身份证号一样，是唯一的，不能重复的用，也就是只有一个。
所以用公式=COUNTIF(A:A,A3)=1来限制它的个数，如果它的个数大于1，就报错。
要求重新输入。
二、数据准确性。
目的：限制文本的长度，当文本长度不够或超长时都进行限制输入。
方法：1、选择身份证号所在的列（暨E列）。
2、【数据】-【数据验证】，选择【允许】中的文本长度，【数据】中的【等于】，【长度】中输入18（身份证号的长度为18）。
3、单击【出错警告】标签，输入【标题】和【错误信息】并【确定】。
解读：身份证号的长度为18，所在【长度】中输入18，此数据根据实际情况自行定义。
例如手机号的长度为11。
三、逐行录入，严禁跳行。
目的：逐行录入数据，不允许跳行。
方法：1、选定第一列（一般情况都是从第一列录入下一行数据）。
2、【数据】-【数据验证】，选择【允许】中的【自定义】，在【公式】中输入=COUNTA(A$3:A3)=ROW(A1)。
3、单击【出错警告】标签，输入【标题】和【错误信息】并【确定】。
解读：公式：=COUNTA(A$3:A3)计算的是从A3开始到当前非空单元格的个数；=ROW(A1)提取的是从A1单元格开始的行号，从1开始逐步增加。
公式=COUNTA(A$3:A3)=ROW(A1)计算的是当前非空单元格的数目如果不等于逐步增加的行号，就限制输入。
四、下拉列表显示当前时间。
数据的有效性检验讲义摘要本讲义旨在介绍数据的有效性检验的概念、方法和步骤。
数据的有效性检验是数据分析的重要组成部分，通过对数据的有效性进行检验，可以确保数据的准确性和可靠性，从而为后续的数据分析提供可信的基础。
在本讲义中，将介绍数据的有效性检验的几种常见方法，包括数据清洗、异常值检测和缺失值处理等，以及应用这些方法的步骤和技巧。
1. 数据的有效性检验介绍1.1 数据的有效性概念数据的有效性是指数据是否符合预期的要求，包括数据的完整性、准确性、一致性和可靠性。
数据的有效性检验是通过各种方法和技术来评估和验证数据的有效性，以确保数据的可信度和准确性。
1.2 数据的有效性检验的重要性数据的有效性检验对于数据分析的准确性和可靠性至关重要。
如果数据存在错误、缺失或异常值等问题，将会导致数据分析的结果出现偏差或错误，从而影响决策的准确性。
因此，进行数据的有效性检验是确保数据分析结果有效的关键步骤。
2. 数据的有效性检验方法2.1 数据清洗数据清洗是指对数据集中的错误、不一致和不完整数据进行处理的过程。
数据清洗的目的是保证数据的准确性和一致性，以便后续的数据分析能够得到正确和可靠的结果。
数据清洗的常见方法包括去重、填充缺失值和纠正错误等。
2.1.1 去重数据集中可能存在重复的数据记录，去重可以帮助我们排除重复的数据，保证数据的唯一性。
常见的去重方法包括基于列的去重和基于行的去重。
2.1.2 填充缺失值在数据集中，可能存在缺失值的情况，即某些列的数值为空。
填充缺失值的方法主要有删除包含缺失值的行、使用均值或中位数填充缺失值等。
2.1.3 纠正错误数据集中可能存在错误或异常值，例如错误的数据类型、超出合理范围的数值等。
纠正错误的方法包括修改数据类型、排除异常值等。
2.2 异常值检测异常值是指与其他数据明显不符的数值，可能是数据记录错误或异常情况的产生。
异常值检测的目的是识别和排除异常值，以确保数据分析的准确性。
2.2.1 统计方法统计方法是最常用的异常值检测方法之一，基于统计学理论和方法来判断数据是否异常。
EXCEL中数据有效性的设置
数据制作过程中需要对某些单元格设置下拉列表，便于数据的选择和限制其他无效数据的输入，需要对数据的有效性进行设置。
实例图如下：
制作过程解析：
1.下拉列表中需要显示的内容少于5-10列，可直接设置数据的有效性；
首先，点击工具栏中的”数据”→“数据有效性”，如下图：
点击进入界面进行设置：
“允许（A）”下面的空格中选择→“序列”
“来源（S）”下面的空格中填写需要设置的内容，例如“高级工程师,中级工程师,初级工程师,工程师助理,实习生”类似的内容。
如下图所示：
备注：“高级工程师,中级工程师,初级工程师,工程师助理,实习生”中间需要用全角的字符逗号“,”将这些内容隔开。
2.下拉列表中需要显示的内容大于5-10列，可通过公式设置数据的有效性；
第一步，在制作的EXCEL表格中选择不需要操作的一列，输入需要制作的下拉列内容，例如需要设置入职年份的下拉列表，如下图所示：
第二步，点击工具栏中的”数据”→“数据有效性”，进入设置界面后在“允许（A）”下面的空格中选择→“序列”；“来源（S）”下面的空格中输入公式：=$$:$$(注意，所有字符均为全角字符)。
根据第一步中输入的数据完善公式。
例如，上图中完善的公式如下：=$I$3:$I$14，如下图所示：
点击确定设置完成，完成的下拉列表如下所示：
第三步，将第一步输入数据的那一列隐藏。
备注：如果设置的下拉列表过长，则可以先输入全角字符的“=”，按住Ctrl 键，点击需要输入数据的首行和末尾，完成公式：=$I$3:$I$14的输入。
一、数据有效性简介：1、选定目标单元格或是单元格区域，点击数据--有效性，打开数据有效性对话框，会发现有四个部分：设置，输入信息，出错警告，输入法模式。
下面分别讲解：或是④定义的名的 空 间狂奔发现有四个部分：相关信息后，没有标题，不能没有输入信息。
如下：置，只要选中此单元格，就会出现小提示框。
件的内容时，出错警告就会提示你！话框提示，并且是无法输入内容的！话框提示，但是会问你是否继续，如果是，还是可以输入内容的！话框提示，说“输入值非法”，只要点击确定还是可以输入进去的！如下：，系统会自动打开中文输入法，任务栏的语言栏将会显示中文图标,“关闭”则会相应的显示英文图标。
有效性的其他选项卡的设置依旧照常保留，如果想把所有选项卡的设置都清除，则可以单击“全部清除”按钮。
己试试！这个不难！可以输入此范围内的整数的！！头时”，用户选择此单元格时，在单元格右方就会出现下拉箭头，单击下拉箭头，可以从中选择某一项填入单元格是数据中已经存在的数据。
引用或是③公式或是④定义的名称男女按“Alt+↓”组合键来选择数据内容。
箭头不出现，可以在“工具--选项--视图”单击对象中的“全部显示”，然后点击确定即可（前提是你确保已经勾选了“提供下拉箭头”性复制应用到其他单元格，或是删除已经设置好的数据有效性。
③按F5键）的结果有两种：效性”单选按钮--单击“全部”--单击确定。
单击“数据有效性”单选按钮--单击“相同”--单击确定（首先要选中你想寻找的数据有效性的其中一个单元格）完全一致才叫相同，否则就不是相同！！，可以使用拖动单元格向下或是向右填充的方式来复制数据有效性；如果不是在一行或是一列，可以使用选择性粘格或是区域上采用：选择性粘贴--单击有效性验证--确定即可。
部清除”按钮--确定即可。
数据有效性的单元格，然后单击数据--有效性--会弹出一个对话框，单击“否”按钮关闭对话框--单击“数据有效性”的对话框的“确定单击“确定”即可清除选定区域的数据有效性。
数据有效性应用详解（干货！强烈建议收藏！）Excel基础学习园地公众号“Excel基础学习园地”是一个免费发布Excel基础知识、函数应用、操作技巧、学习方法等资讯的公众号，请点击上方“Excel基础学习园地”添加关注，方便我们每天向您推送精彩资讯。
可以利用数据有效性制作表格模板，强制性要求其他人按规矩填写表格，以下图表格为例：在表格内输入数据时，我们可以利用数据验证来规范数据的类型，甚至限制输入数值的大小范围。
我们先来利用有效性对基本工资这一列进行设置：规定只能填写整数，并且不低于3500；选中这一列，然后点击数据有效性；在【允许】下拉选项里选中整数（这里还有很多其他的项目，有兴趣的朋友可以抽空自己测试）选中整数以后，下面会出现【数据】这个下拉选项，如果【允许】选择的是其他项目，下面的选项菜单也会发生相应变化。
最小值我们填入3500，点确定就好了。
接下来我们对身份证号码这一列进行设置，要求是长度必须等于18位，防止输入错误：同样的，选择这一列，设置有效性：文本长度等于18；当输入的号码不是18位的时候，同样会提示错误。
对于日期的输入，是不规范的情况最多的一类数据，我们同样可以使用数据有效性进行限制：只能输入2010年1月1日到2018年1月31日之间的日期，并且只能是标准的日期格式：如图进行设置。
特别说明一点，如果在开始日期或者结束日期输入格式不对的日期时，是会报错的：2018.1.31这种是最常见的错误格式。
日期超过规定范围会提示。
日期格式不对也会提示。
接下来对性别进行设置，只能输入男或者女：注意：来源里的项目之间用英文的逗号分隔。
这样设置以后，就可以使用下拉菜单进行填表了。
再来对姓名进行设置，要求是不能出现重名，如果有重名的话，需要加数字进行区分。
使用自定义，然后输入公式=countif($A$2:$A$19,A2)=1，这个公式是统计数据出现次数的。
因为我们限制不能重复，也就是countif 的结果只能为1。
技巧1 在单元格创建下拉列表有许多新手在EXCEL中第一次见到下图所示的下拉列表时，都以为是程序做的，当他们知道图中下拉列表只是一个普通的利用数据有效性完成的EXCEL技巧时，他们会觉得很惊奇。
那么，现在我们一起学习一下，怎么利用数据有效性来做个下拉列表吧：第一步在一个连续的单元格区域输入列表中的项目,如图中E7:E11有个商品名称的表第二步选中A2单元格，单击“菜单”——“数据”——“有效性”,在“数据有效性”对话框的"设置"选项卡中,在“允许”下拉列表中选择“序列”项.第三步在"来源"框中输入“=$E$8:$E$11”（或输入“=”号后，用鼠标选中E8:E11）第四步勾选"忽略空值"与"提供下拉箭头"复选框,如图所示第五步单击"确定"按钮,关闭"数据有效性"对话框. 这样,就能实现第一张图所示的效果了。
如果列表的内容较少,或者不方便在工作表中输入列表项目,也可以省略上述的第一步,然后将第三步的操作改为:直接在"来源"框中输入列表内容,项目之间以半角的逗号分隔.如图所示在一般情况下,数据的有效性中的序列来源,只能引用当前工作表中的单元格区域。
如果希望能够引用其他工作表中的单元格区域,则必须先为单元格区域定义名称,然后在"来源"框中输入名称.例如,将另一张工作表中的A2:A10区域，名称定义为“SPMC”,然后在“数据有效性”的“来源”框中输入“=SPMC”。
技巧二：另类的批注当我们需要对表格中的项目进行特别说明时,常常会使用EXCEL的批注功能。
给单元格做批注的方法，这里不多浪费时间。
而给大家介绍一下另类批注：使用批注多了，我们会发现EXCEL的批注也有不足之处:一、批注框的大小尺寸会受到单元格行高变化的影响;二、批注框的默认情况下，是只显示标识符。
