1.1.5数据有效性的两种经典用法
(完整版)EXCEL--数据有效性
数据的有效性----设置输入条件在不少情况下，设置好输入条件后，能增加数据的有效性，避免非法数据的录入。
如年龄为负数等。
那末有没有办法来避免这种情况呢？有，就是设置数据的有效性。
设置好数据的有效性后，可以避免非法数据的录入。
下面我们还是以实例来说明如何设置数据的有效性。
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 步：设置出错警告在数据有效性对话框中，点击出错警告页，在出错警告页我们可以设置警告信息。
关于数据有效性用法
(其实全在EXCEL的帮助里，我给你复制到这里了，并做了部分修改，希望你能满意)
一、选定要限制其数据有效性范围的单元格。
在“数据”菜单上，单击“有效性”命令，再单击“设置”选项卡。
指定所需的数据有效性类型：
有序列的数值
在“允许”下拉列表框中，单击“序列”。
4、计算基于其他单元格内容的有效性数据
在“允许”框中，单击所需的数据类型。
在“数据”框中，单击所需的限制类型。
在“数据”框或其下面的框中，单击用于指定数据有效性范围的单元格。例如，如果只有在结果不超出预算时，才许可输入帐户，则可单击“允许”框中的“小数”，再单击“数据”框中的“小于或等于”，然后在“最大值”框中单击包含预算的单元格。
单击“来源”框，然后执行下列操作之一：
若要就在框中定义序列，请键入序列值，并用逗号分隔。
若要使用已命名的单元格区域，请键入等号 (=)，随后键入区域的名称。
若要使用单元格引用，请在工作表上选择单元格，然后按 Enter。
注释 单元格引用必须指向相同工作表上的单元格。如果必须引用不同的工作表 或工作簿，则请使用指定的名称，并确保工作簿已经打开。
指定单元格是否可为空白单元格。
如果希望空白单元格（空值）有效，请选中“忽略空值”复选框。
如果要避免输入空值，请清除“忽略空值”复选框。
注释 如果数值的有效性是基于已命名的单元格区域，并且在该区域中有空白单元格，则设置“忽略空值”复选框将使有效单元格中输入的值都有效。同样，为有效性公式所引用的任何单元格也如此：如果引用的单元格是空值，则设置“忽略空值”复选框将使有效单元格中输入的值都有效。
请在“样式”框中选择下列选项之一：
数据有效性
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数据有效性怎么设置（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中数据有效性操作技巧
EXCEL中数据有效性的设置
数据制作过程中需要对某些单元格设置下拉列表，便于数据的选择和限制其他无效数据的输入，需要对数据的有效性进行设置。
实例图如下：
制作过程解析：
1.下拉列表中需要显示的内容少于5-10列，可直接设置数据的有效性；
首先，点击工具栏中的”数据”→“数据有效性”，如下图：
点击进入界面进行设置：
“允许（A）”下面的空格中选择→“序列”
“来源（S）”下面的空格中填写需要设置的内容，例如“高级工程师,中级工程师,初级工程师,工程师助理,实习生”类似的内容。
如下图所示：
备注：“高级工程师,中级工程师,初级工程师,工程师助理,实习生”中间需要用全角的字符逗号“,”将这些内容隔开。
2.下拉列表中需要显示的内容大于5-10列，可通过公式设置数据的有效性；
第一步，在制作的EXCEL表格中选择不需要操作的一列，输入需要制作的下拉列内容，例如需要设置入职年份的下拉列表，如下图所示：
第二步，点击工具栏中的”数据”→“数据有效性”，进入设置界面后在“允许（A）”下面的空格中选择→“序列”；“来源（S）”下面的空格中输入公式：=$$:$$(注意，所有字符均为全角字符)。
根据第一步中输入的数据完善公式。
例如，上图中完善的公式如下：=$I$3:$I$14，如下图所示：
点击确定设置完成，完成的下拉列表如下所示：
第三步，将第一步输入数据的那一列隐藏。
备注：如果设置的下拉列表过长，则可以先输入全角字符的“=”，按住Ctrl 键，点击需要输入数据的首行和末尾，完成公式：=$I$3:$I$14的输入。
数据有效性总结
一、数据有效性简介：1、选定目标单元格或是单元格区域，点击数据--有效性，打开数据有效性对话框，会发现有四个部分：设置，输入信息，出错警告，输入法模式。
下面分别讲解：或是④定义的名的 空 间狂奔发现有四个部分：相关信息后，没有标题，不能没有输入信息。
如下：置，只要选中此单元格，就会出现小提示框。
件的内容时，出错警告就会提示你！话框提示，并且是无法输入内容的！话框提示，但是会问你是否继续，如果是，还是可以输入内容的！话框提示，说“输入值非法”，只要点击确定还是可以输入进去的！如下：，系统会自动打开中文输入法，任务栏的语言栏将会显示中文图标,“关闭”则会相应的显示英文图标。
有效性的其他选项卡的设置依旧照常保留，如果想把所有选项卡的设置都清除，则可以单击“全部清除”按钮。
己试试！这个不难！可以输入此范围内的整数的！！头时”，用户选择此单元格时，在单元格右方就会出现下拉箭头，单击下拉箭头，可以从中选择某一项填入单元格是数据中已经存在的数据。
引用或是③公式或是④定义的名称男女按“Alt+↓”组合键来选择数据内容。
箭头不出现，可以在“工具--选项--视图”单击对象中的“全部显示”，然后点击确定即可（前提是你确保已经勾选了“提供下拉箭头”性复制应用到其他单元格，或是删除已经设置好的数据有效性。
③按F5键）的结果有两种：效性”单选按钮--单击“全部”--单击确定。
单击“数据有效性”单选按钮--单击“相同”--单击确定（首先要选中你想寻找的数据有效性的其中一个单元格）完全一致才叫相同，否则就不是相同！！，可以使用拖动单元格向下或是向右填充的方式来复制数据有效性；如果不是在一行或是一列，可以使用选择性粘格或是区域上采用：选择性粘贴--单击有效性验证--确定即可。
部清除”按钮--确定即可。
数据有效性的单元格，然后单击数据--有效性--会弹出一个对话框，单击“否”按钮关闭对话框--单击“数据有效性”的对话框的“确定单击“确定”即可清除选定区域的数据有效性。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
姓名
王天宇 刘露露 陈祥通 徐志晨 张哲宇 王炫皓 王海德 谢丽秋 王崇江
10 人力资源部 关天胜
方法一：辅助
序号
1 2 3 4 5 6 7 8 9
部门
财务部 财务部 技术部 行政部 行政部 行政部 行政部 技术部 行政部
姓名
王天宇 刘露露 陈祥通 徐志晨 张哲宇 王炫皓 王海德 谢丽秋 王崇江
10 人力资源部 关天胜
序号 部门 姓名
1
销售部 王天宇
2
财务部 刘露露
3
技术部 陈祥通
4
行政不 徐志晨
5 人力资源部 张哲宇
6
销售部 王炫皓
7
பைடு நூலகம்
财务部 王海德
8
技术部 谢丽秋
9
行政部 王崇江
10 人力资源部 关天胜
序号
1 2 3 4 5 6 7 8 9
部门
销售部 行政部 技术部 行政部 行政部 财务部 技术部 行政部 行政部
方法二：手工录入序