数据有效性（也叫数据验证）是我们使用频率最多的一个功能，比如下拉菜单的制作、限制性输入等，那你真的会用数据有效性吗？本期通过6个经典案例与大家分享一下数据有效性的用法。

1、下拉菜单的制作

下拉菜单是数据有效性最经典的用法，这里与大家分享一下相关的制作方法。

一级下拉菜单的制作：

选择区域，点击数据——数据验证——设置——验证条件——序列，之后选择来源区域即可。

注意：来源若是手动输入，要注意的是文本之间的分隔符是英文状态下的逗号。

二级下拉菜单的制作：

先选中区域，点击公式——定义名称——根据所选内容创建——勾选首行，之后建立一级菜单和二级菜单，做二级菜单时，来源写公式=INDIRECT(单元格地址)。

2、在指定的区域输入数字

选中区域，打开验证对话框，在允许条件中选择整数，之后根据需要输入相应的区间。

3、只允许输入数字

这时选择的条件是自定义，之后输入公式=ISNUMBER(B1)=TRUE。

4、逐行输入内容

一样是选择自定义，在公式中输入=COUNTA(A$1:A13)=ROW(A1)

5、输入唯一值

同理，选择自定义输入公式=COUNTIF(A:A,A1)=1

6、输入当前时间

选中区域，之后按Ctrl+1打开设置单元格格式，然后选择自定义，输入数字格式H:MM:SS，点击确定。

这里需要在其他任意单元格输入公式=NOW()

完成以上操作之后，直接选中区域，打开数据验证，选择序列，来源设置=刚刚输入的公式的单元格。

这几个用法，你会了么？若有什么问题欢迎在下方留言。

经常使用Excel的同学肯定知道，规范数据源的重要性，如果是多人协作完成，数据的规范性显得更为重要，但是，如何去设置呢？这就要用到Excel自带的数据有效性设置技巧呢。

一、下拉列表。

方法：

1、选定目标单元格。

2、【数据】-【数据验证】。

3、选择【允许】中的【序列】，在【来源】中输入下拉列表内容，内容中间用英文逗号（,）隔开，或单击【来源】右侧的箭头，选择下拉列表内容要显示的内容。

4、【确定】。

二、限定日期范围。

目的：限定销售日期为9月1日至当天。

方法：

1、选定目标单元格。

2、【数据】-【数据验证】。

3、选择【允许】中的【日期】，然后输入【开始日期】和【结束日期】，如果结束日期为当天，可以用函数=TODAY()来实现。

4、输入【出错警告】信息，并【确定】。

三、限定字符长度。

目的：规范身份证号和电话号码。

方法：

1、选定目标单元格。

2、【数据】-【数据验证】。

3、选择【允许】中的【数据长度】，【数据】中的【等于】，并在【长度】中输入实际的长度值。

4、输入【出错警告】信息或直接【确定】。

四、内容不允许重复。

目的：身份证号不能重复。

方法：

1、选定目标单元格。

2、【数据】-【数据验证】。

3、选择【允许】中的【自定义】，在【公式】中输入：=COUNTIF($C$3:C6,C6)=1。

4、输入【出错警告】信息或【确定】。

结束语：

从上述的示例中我们可以看出，【数据验证】（数据有效性）的功能非常的强大，对我们规范数据源有这非常重要的一，同时提高了我们的工作效率。

使用或学习过程中，如果有任何疑问，欢迎留言讨论哦！

Excel数据有效性是指对单元格输入的内容、数量、上限等进行限制，允许符合条件的内容输入，不允许不符合条件的内容输入，这样能够有效的检测输入内容的有效性、正确性，避免错误数据或内容的输入。

数据有效性很多人都会用，但要完全精通却不好说，下面就整理了Excel数据有效性的9个技巧供大家参考，如果是大神就呵呵一笑，如果正好对你有用，那就默默点个赞呗。

一、快速“录入”数据有效性

数据有效性中最常用的条件就是“序列”，在有众多序列项时，逐个输入太麻烦，一般数据来源直接引用单元格位置（【数据】-【数据验证】），但是这种方式容易因为指定单元格内容的变化而改变（有时候不小心删了就找不到了），在Excel中还占了单元格位置。

如下图，在单元格中输入公式：“=A2:A8”，再按F9，获得={"大鱼君";"百家君";"今日头条";"掌上教育";"小菜哥";"UC新闻";"综艺时评"}，在将（";"）替换为（,）即可复制到数据验证来源处，不需要一个一个输入了。

二、限定日期范围

经常会在Excel中输入日期，比如入职日期、离职日期、出生日期等等，稍有不慎就录错了，限定日期范围能够在一定程度上避免日期输入的错误。设置日期范围如下所示。

三、限定文本长度

为了保证录入数据的规整星，需要限定文本录入的长度，这个时候就可以利用数据有效性进行限定。

四、限定输入字符

有时候输入数据必须要包括某一个字符，可以采用自定义的方式，下面给我几个案例，如：限定开头必须要有北京则数据验证自定义为“=COUNTIF(C2,"北京*")”，星号为通配符。C2是选中区域的第一个单元格，同理如果要增加限定条件可以将两个条件用AND函数结合一起，如最后一个案例。

五、重复提示并禁止输入

经常在输入姓名、身份证、编号时是不能输入重复项的，所以需要限定输入内容重复时无法输入。在自定义中输入“=COUNTIF($C$2:$C$8,C2)=1”即可。如果限定为2，把=1改成=2即可。

六、输入前的提示语

在输入很多次都提示限定输入时会很烦躁，又不知道哪里出现问题。认真一点的制表人会添加输入信息提示，但选择这个单元格录入时，会提示这个单元格只能输入哪些内容，具体设置方法如下：

七、输入错误提示

停止、警告还是继续，并给出一定提示！

八、删除数据验证

一个表格汇总经常有很多数据验证，要清除某一个在不同的单元格很麻烦，这时可以Ctrl+G，定位拥有数据验证的单元格，再点开数据验证，左下角全部清除即可。

九、只能输入数字

为了方面表格数据导入数据库，在输入是对数据类型也需要限制，可以限制某些单元格只能输入数值型，自定义为“=ISNUMBER(C2)”即可，输入“=istext(C2)”则只能输入文本。

以上9个技巧对于常用Excel的老手来说都很简单的啦，但对于新手来说还有点作用。

如果喜欢就点个赞吧！

前言:在EXCEL中数据有效性的设置正常是支持一列的数据源,如果数据源是多列,它就没办法了;本期教你招骗术,忽悠忽悠它

动画演示过程:

解析:

此关键点在于"定义名称",先将A列的数据源定义名称为"地区",然后设置数据有效性,设置完成之后反过来修改已定义的名称"地区"所对应的数据源,然后数据有效性就这么这么.............

如何好好利用Excel数据有效性做动态数据归类？】

谢谢邀请。我是汤帅，一个PPT自由设计师，承接PPT定制。

利用Excel进行数据登记是众文员需要做的工作，但是若能掌握一部分技巧，数据输入会减轻很多工作量，而且可以减少错误的发生。数据有效性有时候能带来便利，而且其他人填写数据的时候也有参考作用。

数据有效性基础操作是：选中单元格→数据→数据有效性→序列，来源，框选需要的区域。

这时候有些小伙伴会提问，那如果我需要作出动态的结果呢，有时候内容太多，但是能通过一定的条件进行区分，可以降低数据有效性的内容呢？这个如何进行操作？这个难度也不是很大，只是数据维护需要及时。

动态数据有效性操作步骤：

步骤一：对数据进行命名，选中需命名单元格→公式，名称管理器，新建。

步骤二：设置条件单元格的数据有效性，选中单元格→数据→数据有效性→序列，来源，框选需要的区域。

步骤三：利用公式INDIRECT进行编制数据有效性，选中单元格→数据→数据有效性→序列，来源，输入公式。

今日互动：你觉得还有其他与客户沟通的思路吗？P.S：我是汤帅，承接PPT定制，有需要可以留言联系我。P.P.S：觉得回答得还行，就点赞吧！

我是汤帅，知名PPT定制设计师，职场技能优秀回答者。学点新鲜有趣的职场技能，关注我准没错。

点击上边蓝色字体关注我们哦~~

在日常工作中，我们避免不了的需要去收集反馈一些资料，例如意向客户跟进的情况，如果我们只是发了一张表让每个人反馈，那么你收集上来的结果将会是五花八门，给自己后期整理会带来特别大的麻烦，这个时候，如果你会用数据有效性，让他们只能从你给的选项中去选择，那后期我们统计分析结果就会节省很大一部分时间。

下面我们以一个录入人员信息所在省所在市为例子简单的讲解下，大家可以根据自己的实际应用去修改即可

一、仅有一个层级：

步骤：数据→数据有效性→序列→

1、如果所需要填写的较少的话，则我们可以直接把内容填写上去即可，如下图：

此处需注意：两个数值之间用英文状态下的逗号填写即可

2、如果所选择的数值较多，则我们可以把数据源填写到其他的空白处，然后直接选择数据源即可，具体操作如下：

二、有两个层级且相关联（即第二个选项是由第一个决定的）

第一步：创建名称管理器

选中所有的内容→公式→根据所选内容创建→首行（因为此例子省份在首行，二级菜单所有的市是根据省来决定的，所以选择首行，具体可以根据不同情况进行选择）

第二步：创建一级菜单名称

选中所有的省份然后在左上角修改名称即可，此处名字可随意填写，但是一定要记着

第三步：设置一级菜单

数据→数据有效性→序列→=省份（第二步设置的名称）

第四步：设置二级菜单

数据→数据有效性→序列→=INDIRECT($G2)（($G2为二级菜单对应的前一列的单元格）

下边的直接下拉即可

xsjdsjmm

Excel的数据有效性功能可以防止数据录入错误，节省录入时间。不需要输入,而是使用的时候，可以通过鼠标点击选择数据。那么就简单教大家怎么设置。

比如我们现在要设置供应商为数据有效性。有的供应商名字很长，每次输入就容易错。设置有效性可以防止错误。

供应商数据有效性

list表G列的数据

点击数据里面数据有效性

数据有效性设置

设置I列使用数据有效性的结果

工作注意健康

是不是要运动

问题来源

EXCEL输入数据时，经常会输入不规范或者无效的数据，对数据的统计工作带来很大的麻烦。

数据验证能够建立特定的规则，限制单元格可以输入的内容，从而规范数据输入，提高数据统计与分析效率。

数据验证，在EXCEL2010及以前的版本中称为“数据有效性”。

使用示例

一、规范性别输入

利用数据验证输入性别，不仅数据规范，而且快速。

方法如下动图：

特别注意：

1、序列来源中，“男”与“女”两个字之间一定是“英文状态”的逗号，即半角逗号；

2、只要对一个单元格设置了数据验证，鼠标拖动单元格右下角填充柄，进行填充，即可将“数据验证”填充到其他单元格，

二、限定输入内容

当然，输入的内容也可以用第一种输入性别的方法以序列的方式直接写入来源。

三、限定数字范围

1、静态限制输入数值范围：

2、动态限制输入数值范围：

这种动态限定数据的方法，设置了数据验证以后，可以通过修改最小值与最大值单元格的数值，动态调整数据允许输入的范围。

四、限定文本长度

限定文本长度还可以设置通过“文本长度——介于”，来设置文本位数的范围。

五、限制输入重复信息

【数据】——【数据验证】：验证条件选择自定义，输入公式：

“=COUNTIF(H:H,H1)=1”。

公式的含义是：H列中，H1单元格的内容只出现1次。如果H列中H1单元格内容出现次数超过1，则禁止输入。

如动图：

六、限定身份证号码

【数据】——【数据验证】：验证条件选择自定义，输入公式：

“=AND(LEN(H1)=18,COUNTIF(H:H,H1&"*")=1)”。

其中：

LEN(H1)=18，含义是H1单元格内容的长度为18位；

COUNTIF(H:H,H1&"*")=1，含义是在H列中H1单元格内容只出现1次，也就是不能重复出现；

(为什么查找身份证出现次数，还要在查找单元格后面添加“&"*"”，请参考Excel | 身份证号码统计易出现的错误)

AND(LEN(H1)=18,COUNTIF(H:H,H1&"*")=1)，含义是同时满足内容长度为18位、不能重复出现两个条件。

七、限制输入空格

【数据】——【数据验证】：验证条件选择自定义，输入公式：

“=ISERR(FIND(" ",ASC(H1)))”。

其中：

ASC(H1)，含义是H1单元格的全角空格转换为半角空格；

FIND(" ",ASC(H1))，含义是在H1单元格中查找空格，如果包含空格，则返回空格在H1单元格的位置，即一个数字；如果没有空格，则返回错误值#VALUE；

ISERR(FIND(" ",ASC(H1)))，含义是通过ISERR函数，将不包含空格时返回的错误值转换为逻辑值TRUE，表示允许输入；将包含空格时返回的数值转换为逻辑值FALSE，表示禁止输入。

如下动图：

突出显示重复数据

1、选中A2:A11单元格区域，新建格式规则

2、使用公式为：

=COUNTIF(A$2:A2,A2)>1

3、设置突出显示的格式

提示：

COUNTIF函数第一参数使用A$2:A2，表示从A2单元格开始至公式所在行的A列。在这个动态扩展的范围中，统计有多少个单元格与公式所在行的内容相同。

突出显示每种商品的最低价格

1、选中B4:F13单元格区域，新建格式规则

=B4=MIN($B4:$F4)

1、MIN($B4:$F4)部分计算出公式所在行的最小值。

2、然后判断B4（活动单元格），是否等于公式所在行的最小值。

3、在条件格式中，针对活动单元格的设置，将被作用到所选区域的每一个单元格。

突出显示已完成合同

1、选中A4:F13单元格区域，新建格式规则

=$F4="是"

本例需要特别注意引用方式，因为每一列都是依据F列的判断结果来显示条件格式，所以要使用列绝对引用。

突出显示周末日期

1、选中A3:F7单元格区域，新建格式规则

=WEEKDAY(A$3,2)>5

1、WEEKDAY函数返回某日期为星期几。第二参数使用2，表示以1~7表示星期一到星期日。

2、对第三行中的日期进行判断后，如果数值大于5，即表示该日期为周六或是周日。

合同到期提醒

1、选中A4:D7单元格区域，新建格式规则

=AND($D4>TODAY(),$D4-TODAY()<7)

使用两个条件对D4单元格中的日期进行判断，第一个条件是大于系统当前日期，第二个条件是和系统当前日期的间隔小于7。

员工生日一周内提醒

=DATEDIF($D4,NOW()+7,"yd")<=7

1、DATEDIF函数用于计算两个日期之间的间隔。

2、第三参数使用yd，表示计算两个日期忽略年的间隔天数。

注意：

由于DATEDIF函数的第三参数在使用“YD”时有特殊的计算规则，因此当结束日期是3月份时，计算结果可能会出现一天的误差。

数据有效性不仅能够对单元格的输入数据进行条件限制，还可以在单元格中创建下拉列表菜单方便用户选择输入。

普通青年这样用：

步骤简要说明：

选中区域，设置数据验证，允许条件选择序列，输入要在下拉菜单中显示的内容：

男,女

注意不同选项要使用半角的逗号隔开。

文艺青年这样用：

选中数据区域，设置数据验证，在【输入信息】选项卡下输入提示内容。

强迫症青年这样用：

选中数据区域，设置数据验证，自定义公式为：

=COUNTA(E$3:E3)=ROW(A1)

注意：公式中的E3是选中数据区域的首个单元格。

走你青年这样用：

=COUNTIF(E:E,E3)=1

注意：公式中的E3是实际选中数据区域的首个单元格。

牛B青年这样用：

任意单元格（如H3）输入公式=NOW()

选中数据区域，设置数据验证，序列来源为：

=H3

注意：要预先设置所选数据区域的数字格式为：H:MM:SS

图文制作：祝洪忠

Excel如何进行多层次联动数据有效性设置

谢谢邀请。我是汤帅，一个PPT自由设计师，承接PPT定制。

数据有效性对于大家而言并不陌生，而且很熟悉，但是运用自如的就是比较困难。在网上一搜索高级的数据有效性需要用到VBA进行设置，这无可厚非，毕竟口令能解决的不需要花费太多时间去专研不必要的方法，两者最终结果都一样。

下拉菜单有时候因为原始数据过多，它的作用就并不是很明显。选项过于繁复，理性输入人会舍弃下拉选取数据，而是选择直接输入数据，以节省选择时间，提高工作效率。所以此时数据有效性的弊端就凸显出来，如果能根据上一个选择而联动筛选出本次所想要的数据，这是非常理想的，也是本次想向大家介绍的一种方法。

回顾一下数据有效性的设置方法，步骤：数据→数据有效性→序列，来源，框选需要的区域。

移除点击此处添加图片说明文字

图1

然后直接进入本次主体，所谓联动，就是当我第一个选择了广东省，第二个可以根据第一个选择筛选出与第一个下支的数据，第三个可以根据第二个筛选出相应的数据，以此类推。本次利用的例子是关于服饰，以类型联动出颜色，再得到相应的金额。

移除点击此处添加图片说明文字

图2

一、设置一级数据有效性

第一步是一级的款型设置，本次数据有效性利用到的公式是=OFFSET($A$2,,,COUNTA(A:A)-1)。

这个公式利用OFFSET动态提取数据，利用定位作用，而且范围是A列非空行数个数。这里OFFSET函数对行和列没有设置值，意味着不向下和不向右取值。

移除点击此处添加图片说明文字

图3

二、设置二级联动数据有效性，增加颜色。

第二步是将款式的颜色作为数据源，不过本次的颜色可能有重复，有重复的，大家可以建立一个辅助列，将此数据进行删除重复项处理。本次数据有效性利用到的公式是=OFFSET($C$1,MATCH(G2,B:B,)-1,,COUNTIF(B:B,G2))。使用OFFSET动态提取C列颜色的数据，而且更加直观。唯一缺点是重复的数据需要增加辅助列进行筛选。

移除点击此处添加图片说明文字

图4

三、设置第三级联动数据有效性，增加价钱。

第三步是将款式的价钱作为数据源，不过本次的价钱可能有重复，有重复的数据，大家可以建立辅助列，将此数据进行删除重复项处理。

本次数据有效性利用的公式是=OFFSET($D$1,MATCH(H2,C:C,)-1,,COUNTIF(C:C,H2))。

移除点击此处添加图片说明文字

图5

Excel的技能是需要大家不断练习才能掌握，不经常练习大家很容易会淡忘。希望大家能互勉共同进步。

今日互动：你觉得还有其他的思路吗？

P.P.S：觉得回答得还行，就点赞吧！

我是汤帅，知名PPT定制设计师，职场技能优秀回答者。学点新鲜有趣的职场技能，关注我准没错。

>