5个示例让你掌握公式在excel数据有效性自定义中的用法

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Excel数据输入技巧数据验证功能的高级用法Excel数据输入技巧：数据验证功能的高级用法在日常工作中，我们经常需要使用Excel进行数据的输入和整理。

Excel提供了丰富的数据处理功能，其中之一就是数据验证功能。

通过使用数据验证功能，我们可以确保输入的数据符合预期的格式和范围，减少数据错误和不一致性。

本文将介绍Excel数据验证功能的高级用法，帮助你更高效地进行数据输入。

一、设置数据验证条件数据验证功能可以用于限制输入的单元格内容，以确保数据的准确性和一致性。

我们可以按照以下步骤来设置数据验证条件。

1. 选择需要设置数据验证的单元格或单元格范围。

2. 在Excel的菜单栏中选择“数据”选项卡，然后点击“数据验证”按钮。

3. 在“设置”选项卡中，选择验证条件，如数字、文本长度、日期、时间等。

4. 根据选择的验证条件，输入相应的数值或公式。

例如，如果选择了“整数”，可以输入“大于0”或“小于100”的范围。

5. 在“输入提示”选项卡中，可以输入对该单元格输入内容的提示信息。

6. 在“错误提示”选项卡中，可以输入输入错误时的提示信息。

通过以上设置，我们可以实现对数据输入的限制，提高数据的准确性和一致性。

二、使用自定义公式进行数据验证除了常规的数据验证条件，我们还可以使用自定义公式作为数据验证的条件。

通过自定义公式，我们可以根据特定的规则进行数据验证。

以下是一些常见的自定义公式验证的示例。

1. 检查日期的输入是否为工作日假设某个单元格需要输入日期，我们可以使用自定义公式来验证输入的日期是否为工作日。

在数据验证的公式中，输入以下公式：=工作日(A1)这样，当我们输入的日期不是工作日时，就会弹出错误提示信息。

2. 检查输入是否为指定列表中的内容有时候，我们希望输入的内容必须是特定列表中的一个。

可以利用数据验证的功能来实现这一点。

先在Excel的某个区域创建一个列表，然后在需要验证的单元格中，选择“设置”为“列表”，并输入列表的区域范围。

Excel电子表格计算公式使用方法25条公式技巧总结对于Excel表格计算公式的方法实在太多，今天就整理了一个公式大全需要对有需要的朋友有些帮助。

1、两列数据查找相同值对应的位置=MATCH(B1,A：A，0)2、已知公式得结果定义名称=EVALUATE(Sheet1！C1)已知结果得公式定义名称=GET。

CELL（6，Sheet1！C1）3、强制换行用Alt+Enter4、超过15位数字输入这个问题问的人太多了，也收起来吧。

一、单元格设置为文本;二、在输入数字前先输入'5、如果隐藏了B列，如果让它显示出来？选中A到C列,点击右键，取消隐藏选中A到C列,双击选中任一列宽线或改变任一列宽将鼠标移到到AC列之间，等鼠标变为双竖线时拖动之。

6、EXCEL中行列互换复制，选择性粘贴，选中转置，确定即可7、Excel是怎么加密的（1)、保存时可以的另存为>〉右上角的”工具”>〉常规>>设置（2）、工具〉〉选项〉>安全性8、关于COUNTIFCOUNTIF函数只能有一个条件，如大于90，为=COUNTIF(A1:A10，”>=90”)介于80与90之间需用减，为 =COUNTIF（A1：A10，”>80"）-COUNTIF（A1:A10，"〉90"）9、根据身份证号提取出生日期（1）、=IF(LEN（A1）=18,DATE（MID(A1，7,4),MID(A1,11，2)，MID（A1，13,2)),IF (LEN(A1）=15，DATE(MID（A1，7，2)，MID（A1，9，2），MID（A1,11，2)）,"错误身份证号”))（2)、=TEXT（MID（A2，7,6+(LEN（A2）=18)＊2),”＃—00—00")＊110、想在SHEET2中完全引用SHEET1输入的数据工作组，按住Shift或Ctrl键，同时选定Sheet1、Sheet211、一列中不输入重复数字[数据］--[有效性]--[自定义]-—[公式］输入=COUNTIF（A：A,A1)=1如果要查找重复输入的数字条件格式》公式》=COUNTIF（A：A,A5)〉1》格式选红色12、直接打开一个电子表格文件的时候打不开“文件夹选项”-“文件类型”中找到。

数据验证自定义公式数据验证自定义公式数据验证功能是Excel中非常实用的功能之一。

使用数据验证功能可以确保输入的数据是正确的，并提供有用的反馈信息，以便用户知道如何使用工作表。

Excel的数据验证功能允许你使用公式自定义验证规则。

在本文中，我们将探讨如何使用数据验证自定义公式来验证数据。

使用数据验证自定义公式数据验证功能可用于确保输入值的范围，类型和格式正确。

通过使用数据验证，您可以更好地控制工作表中的数据，为您的数据表提供更有效的数据质量控制。

要使用数据验证功能，选择要验证的单元格或单元格范围，然后打开“数据验证”对话框。

在“设置”选项卡上，您可以选择验证规则类型。

在“公式”框中，您可以输入验证规则的自定义公式。

接下来是使用自定义公式进行数据验证的常见用例。

1.确保数值在某个范围内一个常见的验证要求是确保值在某个范围内。

例如，如果你正在创建一个销售报告，你可能需要确认每个销售额数值位于特定的范围内。

下面是一些示例公式：=AND(A4>=0,A4<=1000000)，确保销售额在0和1000000之间=AND(A4>=0,A4<=1000000,A4>=B4)，确保销售额在B4和1000000之间注：AND函数可以将多个条件组合在一起，并要求每个条件都为TRUE。

2.确保只接受规定值如果您只想接受特定数值，而不是范围，那么可以使用OR函数。

下面是几个示例公式：=OR(A1={"红色","黄色","蓝色","绿色"})，只接受红色，黄色，蓝色和绿色这四种颜色=OR(A1>="01/01/2021",A1<="12/31/2021")，只接受2021年内的日期注：花括号 {} 中列出的值是数组常量。

数组常量是一个含有数值的数组，可以用于数值列表。

Excel中进行高级功能符合数据有效性公式的步骤
假设只允许在区域B2:B10中输入奇数。

没有“Excel数据有效性”规则能限制只输入奇数，所以需要公式来实现。

执行这些步骤如下：
选择区域B2：B10，并确保单元格B2是活动单元格。

选择“数据”——“数据工具”——“数据有效性”。

显示“数据有效性”对话框。

单击“设置”选项卡并从“允许”下拉列表中选择“自定义”。

在“公式”框中输入如下公式，如图所示： =ISODD(B2)。

该公示使用了Excel 的ISODD函数，如果其数字参数是一个奇数，则该函数返回Ture。

注意公式引用的是活动单元格B2。

单击“出错警告”选项卡并选择“样式”为“停止”，键入“此处需填奇数”作为“错误信息”。

单击“确定”关闭“数据有效性”对话框。

看过Excel中进行高级功能符合数据有效性公式的还看了：
1.如何设置和使用Excel中的数据有效性
2.Excel表格中数据有效性全面功能的使用方法
3.Excel2016数据有效性怎么设置
4.怎么实现excel双重数据有效性
5.Excel中进行数据有效性设置的两种方法
6.excel单元格如何设置数据有效性
7.excel表格数据有效性如何使用
8.怎么在excel2013中设置两列数据有效性。

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中一个非常实用的功能，它可以根据我们设定的条件，对数据进行自动的格式化。

除了常见的基本条件格式（如大于、小于、等于）外，我们还可以利用条件格式公式实现更加复杂的条件判断。

例如，我们可以利用条件格式公式来实现对数据的动态标记。

假设我们有一列销售额数据，我们希望将销售额超过平均值的数据标记为红色，低于平均值的数据标记为绿色。

我们可以使用以下条件格式公式来实现：1. 选择需要格式化的数据范围；2. 点击“条件格式”-“新建规则”-“使用公式确定要设置的单元格格式”；3. 在“公式”框中输入公式：“=A1>AVERAGE($A$1:$A$10)”（假设销售额数据在A列，共有10行）；4. 点击“格式”按钮，选择红色作为格式化的颜色；5. 点击“确定”按钮，完成设置。

通过以上步骤，我们就可以将销售额超过平均值的数据标记为红色。

同样的方法，我们可以将销售额低于平均值的数据标记为绿色。

除了标记数据外，条件格式公式还可以用于其他一些高级应用。

例如，我们可以利用条件格式公式实现对数据的排名。

假设我们有一列分数数据，我们希望将分数排名前三的数据标记为黄色，我们可以使用以下条件格式公式来实现：1. 选择需要格式化的数据范围；2. 点击“条件格式”-“新建规则”-“使用公式确定要设置的单元格格式”；3. 在“公式”框中输入公式：“=RANK(A1,$A$1:$A$10)<=3”（假设分数数据在A 列，共有10行）；4. 点击“格式”按钮，选择黄色作为格式化的颜色；5. 点击“确定”按钮，完成设置。

如何在Excel中设置数据有效性的复杂规则和自定义错误提示信息在Excel中设置数据有效性的复杂规则和自定义错误提示信息Excel是一款功能强大的电子表格软件，广泛应用于数据处理和分析。

在使用Excel进行数据录入时，我们常常需要对数据进行限制，以确保数据的准确性和完整性。

Excel中的“数据有效性”功能可以帮助我们实现这一目的。

本文将介绍如何在Excel中设置数据有效性的复杂规则和自定义错误提示信息，以提高数据录入的效率和准确性。

一、打开数据有效性对话框在Excel中，我们可以通过以下两种方式打开数据有效性对话框：1. 选择想要设置数据有效性的单元格或单元格范围。

2. 选择“数据”选项卡，在“数据工具”组中点击“数据有效性”。

二、设置数据有效性规则数据有效性规则是我们对数据进行限制和验证的规则。

在数据有效性对话框中的“设置”选项卡中，我们可以设置各种复杂的数据有效性规则。

1.整数范围规则如果我们希望输入的数据必须在某个整数范围内，可以设置整数范围规则。

在“允许”下拉菜单中选择“整数”，在“数据”选项卡中输入整数范围的最小值和最大值。

2.小数位数规则如果我们希望输入的数据必须满足特定的小数位数要求，可以设置小数位数规则。

在“允许”下拉菜单中选择“小数”，在“数据”选项卡中输入小数的位数。

3.文本长度规则如果我们希望输入的文本长度必须在某个范围内，可以设置文本长度规则。

在“允许”下拉菜单中选择“文本长度”，在“数据”选项卡中输入文本长度的最小值和最大值。

4.列表规则如果我们希望输入的数据必须是一个预先定义好的列表中的值，可以设置列表规则。

在“允许”下拉菜单中选择“序列”，在“数据”选项卡中输入需要限制的数据范围。

5.公式规则如果我们希望输入的数据必须满足特定的计算公式，可以设置公式规则。

在“允许”下拉菜单中选择“自定义”，在“数据”选项卡中输入符合要求的公式。

三、设置自定义错误提示当我们输入不符合数据有效性规则的数据时，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)计算的是当前非空单元格的数目如果不等于逐步增加的行号，就限制输入。

四、下拉列表显示当前时间。

Excel2020中使用符合数据有效性公式的操作方
法
假设只允许在区域B2:B10中输入奇数。

没有“Excel数据有效性”规则能限制只输入奇数，所以需要公式来实现。

执行这些步骤如下：
选择区域B2：B10，并确保单元格B2是活动单元格。

选择“数据”——“数据工具”——“数据有效性”。

显示“数据有效性”对话框。

单击“设置”选项卡并从“允许”下拉列表中选择“自定义”。

在“公式”框中输入如下公式，如图所示：=ISODD(B2)。

该公示使用了Excel的ISODD函数，如果其数字参数是一个奇数，则该函数返回Ture。

注意公式引用的是活动单元格B2。

单击“出错警告”选项卡并选择“样式”为“停止”，键入“此处需填奇数”作为“错误信息”。

单击“确定”关闭“数据有效性”对话框。