Excel数据有效性使用方法

合集下载

Excel中如何设置数据有效性

Excel中如何设置数据有效性数据有效性是Excel中一项重要的功能，它可以限制用户在特定单元格中输入的数据类型、数值范围和列表项，提高数据的准确性和统一性。

本文将介绍如何在Excel中设置数据有效性。

一、基本操作在Excel中，设置数据有效性的操作步骤如下：1. 选择需要设置数据有效性的单元格或单元格范围。

2. 点击Excel菜单栏中的“数据”选项卡。

3. 在“数据工具”组中找到“数据有效性”按钮，并点击打开“数据有效性”对话框。

二、验证规则在“数据有效性”对话框中，有多种验证规则可供选择，下面分别介绍几种常用的验证规则。

1. 数值范围验证数值范围验证可以限制输入的数值必须在指定的范围内。

在“设置”选项卡中选择“整数”或“小数”，并设置最小值和最大值，即可实现该验证规则。

2. 列表验证列表验证可以限制输入的内容必须是预先指定的列表项之一。

在“设置”选项卡中选择“列表”，并将列表的选项输入到“源”框中，使用英文逗号分隔每个列表项。

3. 文本长度验证文本长度验证可以限制输入的文本长度必须在一定范围内。

在“设置”选项卡中选择“文本长度”，并设置最小长度和最大长度，即可实现该验证规则。

4. 日期验证日期验证可以限制输入的日期必须符合指定的日期格式和范围。

在“设置”选项卡中选择“日期”，并设置日期格式和起止日期，即可实现该验证规则。

5. 自定义公式验证自定义公式验证可以根据自定义的公式判断输入是否有效。

在“设置”选项卡中选择“自定义”，并在“公式”框中输入自定义公式，公式返回值为TRUE时表示数据有效。

三、输入提示在“输入提示”选项卡中，可以针对设置的数据有效性规则提供文字提示。

当用户选择单元格时，Excel会显示此处设置的提示信息，帮助用户正确输入。

四、错误提示在“错误提示”选项卡中，可以设置当用户输入不符合数据有效性规则时的错误提示信息。

可以自定义错误标题和错误信息，以及错误提示框的显示样式。

五、示例演练为了更好地理解如何设置数据有效性，下面以一个案例为例进行演练。

Excel设置数据有效性实现单元格下拉菜单的3种方法

Excel设置数据有效性实现单元格下拉菜单的3种方法一、直接输入：1.选择要设置的单元格，譬如A1单元格；2.选择菜单栏的“数据”→“有效性”→出现“数据有效性”弹出窗口；3.在“设置”选项中→“有效性条件”→“允许”中选择“序列”→右边的“忽略空值”和“提供下拉菜单”全部打勾→在“来源”下面输入数据，譬如“1,2,3,4,5,6,7,8,9”(不包括双引号，分割符号“,”必须为半角模式)→按“确定”就OK了，再次选择该A1单元格，就出现了下拉菜单。

二、引用同一工作表内的数据：如果同一工作表的某列就是下拉菜单想要的数据，譬如引用工作表Sheet1的B2：B5，B2：B5分别有以下数据：1、2、3、4，操作如下：1.选择要设置的单元格，譬如A1单元格；2.选择菜单栏的“数据”→“有效性”→出现“数据有效性”弹出窗口；3.在“设置”选项中→“有效性条件”→“允许”中选择“序列”→右边的“忽略空值”和“提供下拉菜单”全部打勾→在“来源”下面输入数据“=$B$2:$B$5”，也可以按右边带红色箭头的直接选择B2：B5区域→按“确定”就OK了，再次选择该A1单元格，就出现了下拉菜单。

三、引用不同工作表内的数据（必须用到定义名称）：如果不同工作表的某列就是下拉菜单想要的数据，譬如工作表Sheet1的A1单元格要引用工作表Sheet2的B2：B5区域，工作表Sheet2的B2：B5分别有以下数据：1、2、3、4，操作如下：1.定义名称：菜单栏→“插入”→“名称”→“定义”→弹出“定义名称”窗口，在“在当前工作薄中的名称”下面输入“DW”（可以自己随便明明）→“引用位置”下面输入“=Sheet2!$B$2:$B$5”，也可以按右边带红色箭头的直接选择B2：B5区域→按“添加”后再按“确定”完成第一步。

2.选择菜单栏的“数据”→“有效性”→出现“数据有效性”弹出窗口；3.在“设置”选项中→“有效性条件”→“允许”中选择“序列”→右边的“忽略空值”和“提供下拉菜单”全部打勾→在“来源”下面输入“=DW”，“DW”就是刚刚定义好的名称，按“确定”就OK 了，再次选择该A1单元格，就出现了下拉菜单。

(完整版)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数据有效性(数据验证)应用详解

Excel数据有效性（数据验证）应用详解我们可以利用数据有效性制作表格模板，强制性要求其他人按规矩填写表格。

看课件：1、利用数据验证为单元格的数据输入设置条件限制在表格内输入数据时，我们可以利用数据验证来规范数据的类型，甚至限制输入数值的大小范围。

我们先来利用有效性对基本工资这一列进行设置：规定只能填写整数，并且不低于3500选中这一列，然后点击数据有效性在【允许】下拉选项里选中整数（这里还有很多其他的项目，有兴趣的朋友可以抽空自己琢磨琢磨）选中整数以后，下面会出现【数据】这个下拉选项，如果【允许】选择的是其他项目，下面的选项菜单也会发生相应变化。

最小值我们填入3500，点确定就好了这时候如果输入的数据不符合我们的规定，就会弹出提示框。

接下来我们对身份证号码这一列进行设置，要求是长度必须等于18位，防止输入错误：同样的，选择这一列，设置有效性：文本长度等于18.当输入的号码不是18位的时候，同样会提示错误。

对于日期的输入，是不规范的情况最多的一类数据，我们同样可以使用数据有效性进行限制：只能输入2010年1月1日到2018年1月31日之间的日期，并且只能是标准的日期格式：如图进行设置。

特别说明一点，如果在开始日期或者结束日期输入格式不对的日期时，是会报错的：2018.1.31这种是最常见的错误格式。

日期超过范围会提示日期格式不对也会提示接下来对性别进行设置，只能输入男或者女：注意，来源里的项目之间用英文的逗号分隔。

这样设置以后，就可以使用下拉菜单进行填表了。

再来对姓名进行设置，要求是不能出现重名，如果有重名的话，需要加数字进行区分。

使用自定义，然后输入公式=countif($A$2:$A$19,A2)=1，这个公式对于我们来说应该没什么问题的。

因为我们限制不能重复，也就是countif的结果只能为1。

出现重复的时候会提示错误。

现在这个表里只剩个录入时间了，要求是填入录入信息的年月日时分秒，如果靠手工输入会很麻烦，看看怎样通过有效性来快速输入时间。

Excel数据有效性验证技巧如何限制数据输入范围

Excel数据有效性验证技巧如何限制数据输入范围在Excel中，数据有效性验证是一项非常有用的功能，可以帮助用户限制数据输入的范围，保证数据的准确性和一致性。

本文将介绍一些Excel中的数据有效性验证技巧，帮助读者了解如何使用该功能来限制数据输入范围。

一、基本概念和设置方法数据有效性验证是一种可以限制单元格中输入数据的类型和范围的功能。

在Excel中，我们可以通过以下步骤来设置数据有效性验证：1. 选择需要设置数据有效性验证的单元格或单元格区域；2. 点击“数据”选项卡中的“数据工具”组，然后点击“数据有效性”按钮；3. 在“设置”选项卡中，选择合适的验证规则，可以是整数、小数、日期、时间、文本等；4. 根据需要设置验证条件，例如限制输入的数值大于或小于某个特定值，或在一定范围内；5. 在“输入信息”和“错误警告”选项卡中，可以设置输入时的提示信息和错误警告提示。

二、限制数值类型和范围对于数值类型的数据，我们可以使用数据有效性验证来限制其输入范围。

例如，我们可以设置某个单元格只能输入整数、小数或特定范围的数值。

具体操作如下：1. 选择需要设置数据有效性验证的单元格；2. 点击“数据”选项卡中的“数据工具”组，然后点击“数据有效性”按钮；3. 在“设置”选项卡中，选择“整数”或“小数”验证规则；4. 在“数据”选项卡中，根据需要设置允许的数值范围，如最小值、最大值、步长等；5. 在“输入信息”和“错误警告”选项卡中，可以设置输入时的提示信息和错误警告提示。

三、限制日期和时间格式对于日期和时间类型的数据，我们也可以使用数据有效性验证来限制其输入格式和范围。

例如，我们可以设置某个单元格只能输入日期或时间，并且限制其输入范围。

具体操作如下：1. 选择需要设置数据有效性验证的单元格；2. 点击“数据”选项卡中的“数据工具”组，然后点击“数据有效性”按钮；3. 在“设置”选项卡中，选择“日期”或“时间”验证规则；4. 在“数据”选项卡中，根据需要设置允许的日期或时间范围，如最早日期、最晚日期等；5. 在“输入信息”和“错误警告”选项卡中，可以设置输入时的提示信息和错误警告提示。

Excel数据有效性功能使用教程

Excel数据有效性功能使用教程我们在excel中进行操作，时间久了难免会录入出现错误，由于数据庞大很难肉眼去一个个检查，那么这是我们需要用到数据有效性功能，那我们该如何使用Excel数据有效性规那么呢?1、选中要输入数据的单元格。

2、点击数据——数据有效性。

3、在有效性条件中，允许选择整数，数据选择介于，最小值和最大值，需要事先在表格空白处输入好，翻开数据有效性后直接选取数据最小值和最大值。

4、选择出错警告，在错误信息中输入”请输入0到100的数据“。

5、在你要输入的数据区域，如果你输入的数据不在0到100以内，会弹出报错信息。

Ctrl+A全选Ctrl+C复制Ctrl+X剪切Ctrl+V粘贴Ctrl+D自动填充Ctrl+B加粗Ctrl+U下划线Ctrl+I斜体Ctrl+K超链接Ctrl+F查找Ctrl+H替换Ctrl+L创立表Ctrl+N新建工作簿Ctrl+O翻开文件Ctrl+P打印Ctrl+S保存Ctrl+W关闭Ctrl+Y撤销后重做Ctrl+Z撤销1.怎样快速翻开设置单元格格式我们经常在输入数值等的时候需要设置单元格格式，很多人都已经习惯了点击鼠标右键选项，但是我们可以使用快捷键CTRL加1快速翻开，如果自己的手不在鼠标上非常方便。

当然，我们也可以用鼠标点击如下列图所示位置快速翻开设置单元格格式选项。

2.excel怎样设置小数位数如果一行带小数的数字我们要快速转化为整数的话可以使用快捷键CTRL加SHIFT加1，设置起来速度非常快。

当然，我们经常不会只使用整数，小数位的缩进或者延伸我们可以选中要设置的单元格后使用点击下面两个选项进行设置。

3.关闭excel表的快捷方式我们经常需要翻开一个表格完成工作后需要关闭，可以使用快捷方式CTRL加W，使用习惯后比拟方便，希望能够对大家有帮助。

EXCEL中数据有效性的设置与应用

EXCEL中数据有效性的设置与应用在日常办公中，Excel作为一种强大的数据处理工具，深受用户的喜爱。

特别是在数据有效性管理方面，它提供了多种功能，帮助用户确保输入数据的准确性和一致性。

通过合理配置数据有效性，用户不仅可以避免因错误输入导致的数据混乱，还能提高工作效率。

接下来，将深入探讨Excel中数据有效性的设置及其应用。

数据有效性的基本概念数据有效性是指对单元格中输入内容的限制与控制。

通过设置有效性规则，用户可以在输入数据时设定条件，确保输入值在指定范围内的合法性。

例如，可以限制某一单元格只能输入数字、特定文本，甚至下拉选择等，提升数据的规范性。

设置数据有效性的步骤在Excel中，设置数据有效性并不复杂，按照几个步骤即可完成。

选择单元格：选中需要设置有效性的单元格或区域。

打开数据有效性对话框：在菜单栏找到“数据”选项，点击“数据有效性”按钮，打开相关设置窗口。

设置有效性条件：在弹出的对话框中，用户可以选择不同的有效性类型。

例如：数字：限制输入为特定数字范围内的数值。

日期：设置日期范围，确保用户输入在有效日期内。

列表：通过下拉菜单提供选择项，避免用户输入错误文本。

输入提示与错误警告：除了基本限制，用户还可以设置输入提示，提示用户可输入的内容。

设置错误警告，当输入不符合要求时，给予用户明确反馈。

确认设置：完成设置后，点击“确定”按钮，数据有效性设置即生效。

应用场景分析数据有效性的实际应用场景广泛，尤其是在以下几个方面尤为突出。

财务报表制作在财务报表中，数量、金额及比例等数据极为重要。

此时，借助数据有效性，可以设定金额只能输入正数，防止因错误输入导致的不准确。

可以通过列表限制账户名称，提高报告的规范性和可靠性。

项目管理在项目管理中，通常需要对不同阶段的进度、预算和风险等级进行监控。

通过数据有效性设置，可以强制每一项进度更新只能选择预设的进度状态，确保信息上传的准确性，便于后续分析与决策。

人事管理对于员工信息的录入，通过设置有效性，能够确保如入职日期、薪资等关键字段的数据规范，避免因手误产生错误。

谈EXCEL数据有效性的简易操作

谈EXCEL数据有效性的简易操作自己学习了几种简易的有效性操作，比网上的方法容易记住也便于操作，介绍给大家。

下列都是自己所学所写。

1.直接输入这种仅仅限于数据比较少的时候。

如下面的例子。

在B列输入每个人的性别。

我们用直接输入的方法。

选择单元格B2至B5后选择“数据-有效性”设置-有效性条件-同意（下拉选择）-序列在来源处输入男,女-确定注意：中间的“,”务必是在英文状态下才行。

这样就出现下拉菜单，能够直接选择了。

如有新增B6以后的单元格，只要下拉/双击则可。

2.同工作表的序列选择假如需要选择内容很多，一个一个KEY上去很烦恼，我们能够用参数表的功能来作下拉清单。

如下例子，我们要讲籍贯都填写完毕，在同一个工作表中制作一个参数表，表头仍为籍贯，用作区别。

选择C2至C7，数据-有效性-设置-有效性条件-同意-序列在来源处选择参数表中需要作为内容的单元格。

记住，不要选择籍贯，否则也会出现在下拉菜单中。

确定。

3.参数表在单独一个工作表中，用定义名称来操作网上很多介绍的方法都是用函数INDIRECT来制作，本人记不住，也觉得烦恼。

现在介绍一个很简单的方法。

这种方法在实际操作中应用非常广泛。

如下列例子：我们要在SHEET1里边做下拉清单，同时建立一个参数表参数表如下首先，要把参数表定义名称。

有两种方法。

第一种是选择A1至A5，插入—名称—指定指定—首行—确定。

这样，我们就将参数表的数据定义的名称是“籍贯”。

第二种方法是直接定义名称，首行能够没有“籍贯”所选的单元格自成一个名称。

选择所需单元格-插入—名称—定义定义名称中数据籍贯—添加—确定用以上两种方法都能够定义名称。

这是第一步。

其次，回到sheet1工作表，选择需要建立下拉清单的单元格数据—数据有效性—设置—有效性条件—同意—序列来源中直接输入“=籍贯”—确定。

效果如下图学会了吗？是不是很简单。

1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。

楼猪，你的要求很容易实现，使用条件格式就可以啦，点击C1，点击工具栏中的“格式”-“条件格式”，然后根据图片所示设置就可以啦。

条件一，从左至右为：
单元个数值等于open
点“格式”按钮，点“图案”标签，选择红色。

点“添加”按钮。

条件二，从左至右为：
单元个数值等于close
点“格式”按钮，点“图案”标签，选择绿色。

确定。

OK？
3|评论。