在我们的日常工作中使用电子表格EXCEL收集数据、上报数据和填写数据是最常见的工作了，我们收集整理的数据往往会非常的庞大，如果每一项都要自己一点点的检查有效性实在是非常累人的一件事情，而且即使我们非常的仔细认真，也不敢保证我们的数据一定是合理有效的。

EXCEL就提供了一项非常好用的功能，即“数据有效性设置”和“圈释无效数据”，这个功能是不管OFFICE的EXCEL，还是WPS的电子表格都有的功能，非常的实用。下面小编就带大家一起看看它的妙用吧！

首先我们先找到数据有效性的功能按钮在什么地方，既然称之为数据有效性，很显然的，它就在“数据”选项卡里面，在OFFICE的EXCEL中就叫“数据有效性”，在WPS表格中就叫“有效性”，相信小伙伴们都很容易就找到了。点击它右下角的小三角，可以拉出一个菜单，分别含有“数据有效性”“圈释无效数据”和“清除无效数据标识圈”。

“数据有效性”的功能就是对选定单元格设置数据有效性规则，之后如果再这些单元格内输入的数据不符合有效性规则，就会报错；

“圈释无效数据”是需要配合上面的“数据有效性”来使用的，有时候我们的单元格中已经先有了数据，但是这些数据未必都合理，我们需要挑出数据不合理的数据，就可以先对这些数据的单元格设置数据有效性规则，然后再利用“圈释无效数据”进行表示，快速找到错误数据，提高工作效率。

“清除无效数据标识圈”的功能当然就是将上面标识出来的圈清除掉了。

我们看一下数据有效性含有哪些有效性规则，如下图：

有效性条件允许的类型有：任何值、整数、小数、序列、日期、时间、文本长度、自定义这8类。任何值没什么好说，选择了它就等于没有设置有效性了。这里的类型可以根据你对当前单元格要输入的内容来确定，比如你要输入的是金额，那么选择小数最好，为什么不选择整数，因为我们金额还有几毛几分。特别说明：小数并不一定非得要输入小数点后面的值，选择了小数这个选项，你输入“100”这样的整数也是可以的。如果你要输入的内容是数量，而且对应的单位名次是“只”“个”“台”这些，那么数据有效性肯定就是选择整数了，应该没有1.1台这种情况吧！单元格内容是日期就用日期，是时间就用时间。

然后还需要选择数据范围，主要是介于多少到多少之间、未介于多少到多少之间、等于和不等于、大于小于这些，相信聪明的小伙伴应该都能理解。

设置好数据有效性之后，如果之后在这些单元格里面输入不符合规则的数据，就会表格就会提示报错“输入值错误，其他用户已经限定了可以输入单元格的数值”，当然了，出错信息我们可以在“出错警告”里面自己定义，还可以选择出错之后是紧急只是警告还是停止输入，默认情况下是“停止”，即输入错误就不允许数据录入表格，如果选择的是“警告”，那么只是弹出警告提示框，问你是否继续，点击继续的话，数据仍然进入表格。

“圈释无效数据”这个功能主要是针对别人做好的表格上报给我们，我们需要检查数据的有效性，这种场景下使用最好。比如公司里面要求各部门、子公司上报自己的固定资产，包括资产原价值等属性，有的子公司上报上来的数据竟然含有一台电脑0.12元这种极其不合理的情况存在，我们通过先设置“数据有效性”规则，再点击“圈释无效数据”，一下子就将所有的不合理数据都挑选出来了。

小伙伴们，你学会了吗？如果你喜欢本文，一定要给小编我点赞、收藏、分享哦！如果有什么不明白的地方，或者有什么不同的意见，可以给小编我留言！

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数据有效性做动态数据归类？】

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

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

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

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

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

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

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

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

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

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

一、鼠标左键单击你要选择的列的字母，选择整列。如图所示：

二、选择工具栏的数据工具下的数据有效性，如下图所示：

三、选择数据有效性

四、根据你的需要自己设置相关的有效性，如下图所示：

需要提醒的是：如果在这一列中，如果有单元格设置了工作表保护的话，是不能设置数据有效性的。这种情况需要先取消工作表保护然后在设置有效性。

（本文内容由百度知道网友zxh4567贡献）

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

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

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

一、仅有一个层级：

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

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

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

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

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

第一步：创建名称管理器

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

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

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

第三步：设置一级菜单

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

第四步：设置二级菜单

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

下边的直接下拉即可

xsjdsjmm

在我们日常数据输入过程中，数据的多样化，数据的多元化，让数据的输入人员丝毫不得懈怠，比如身份证号的输入，18位数字，怎么在输入过程中确保数据位数正确呢？比如产品只有1级，2级，3级，怎么要求输入的内容只能限定这三类？

EXCEL数据有效性，它会替我们把好这一关。

操作方法：

一、选中我们需要限制的行或者列，或者指定区域单元格

二、单击“数据”下的“数据有效性”

三、选择“允许”下的下拉箭头会出现多个可供的限制内容

四：简单介绍一下每一项的应用

1、整数

2、小数

3、序列

4、日期、时间等

5、文本长度

6、自定义（自定义公式）

五、输入信息提示、出错警告的应用

输入信息：当鼠标单击到此单元格时，会提示此单元格需注意的特殊信息

出错警告：当输入信息错误时，提示出错警告，并停止输入

实战1：对产品种类限制只允许输入A、B、C、D类，可使用上述的“序列”

实战2：对身份证号输入列进行数据有效限定，位数为18位、跟随提醒、错误提示警告。

宝宝，出错喽~~~~(>_<)~~~~

总结：数据有效性，让我们的数据更加真实可靠，提高数据输入的正确率。

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定制设计师，职场技能优秀回答者。学点新鲜有趣的职场技能，关注我准没错。

>

【Excel如何模糊性使用高级数据有效性搜索数据？】

数据有效性有时候能带给大家便利，有时候能带给大家烦恼，在数据少的时候，数据有效性非常便利；但是数据累计到一定量的时候，可选项就非常多，有时候并不是很方便。

看着别人在做数据的时候利用下拉菜单好像很高级，下面我带大家从不同层次进行学习这个下拉菜单的制作。

一、制作数据有效性方法一：数据→数据有效性→序列，来源，框选需要的区域。

图1

基本的数据有效性设置已经回顾了，有一些朋友会疑问，数据有效性如何能做到模糊性筛选数据，例如使用筛选功能那样，只需要输入一两个关键词，就可以匹配到相应的数据，得出简化的可视数据，带来不一样的视觉感受。现在我帮大家打破这一常规，来带领大家慢慢建立自己的高级数据有效性王国。

大家可以建一个数据源的工作表和过渡搜索数据的工作表，第三个是使用数据有效性的工作表。如图所示，每一个表格的作用会在实际使用中提及。

图2

二、对数据源工作表建立一个名称管理

第一步是对数据源工作表的公司名称建立一个名称进行管理—公司，基本操作步骤：选中所需单元格→公式→根据所选内容创建，名称为首行→确定。

图3

三、建立数组公式

第二步是在过渡工作表中建立一个数组公式，该数组公式作用是根据输入单元格内容进行搜索符合条件的内容。温馨提示：数组公式需要Ctrl + Shift + Enter三键一起输入，被提示循环运用的时候，点确定即可。

该辅助数组公式为：

=INDEX(数据源!A:A,SMALL(IF(ISNUMBER(FIND(CELL("contents"),公司)),ROW(公司),1000),ROW(A1)))&""

图4

第三步是继第二步建立第二个名称—公司名，该名称使用一个公式提取第二步搜索的数据，该公式为：

=OFFSET(提取!$A$2,,,COUNTIF(公司,"*"&CELL("contents")&"*"))

图5

四、建立第二个数据有效性

第四步是建立第二个数据有效性，该数据有效性设置与基本设置一致。设置步骤：数据→数据有效性→序列，公司名（已经在名称管理器下建立名称）。

图6

突出显示重复数据

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表格中设置数据有效性（单元格设置下拉选项）可以提高我们数据输入的准确性，可以提高我们的工作效率。到底我们要怎么样设置Excel表格中的数据有效性（表格设置下拉选项）呢？今天通过一个小技巧跟大家分享Excel表格中如何设置数据有效性（表单元格设置下拉选项），希望对朋友们在工作中有所帮助！

1、选中部门下面的空单元格或整列——点击“数据”选项，选择“数据验证”下拉菜单下方的“数据验证”

2、在“数据验证”——“验证条件”——“允许”下框——选择“序列”

3、在“数据验证”——“验证条件”——“来源”下框输入部门名称，每个数据之间用英文状态下的逗号隔开并确定

4、设置完成后看下部门下面表格中下拉菜单的效果，我们再也不需要重复去输入部门名称了，只需要通过用鼠标点击操作即可选择！这样我们就可以提高工作效率！

如果我们的数据来源比较多的话，建议朋友们选择数据源区域。

今天的Excel表格中如何设置数据有效性（单元格设置下拉选项）小技巧分享，希望对大家工作中有所帮助！欢迎加入我们群一起交流学习！