2022-01-10 07:45

数据有效性的九种典型用法

小伙伴们好啊，今天咱们说说数据验证（数据有效性）的几种典型用法：

1、限制年龄范围

因为员工年龄不会小于18岁，也不会大于60岁，因此输入员年龄的区间应该是18~60之间的整数。通过设置数据验证，可以限制输入数据的区间范围。

2、限制输入重复数据

在数据验证中，如果公式结果等于TRUE或是不等于0的任意数值，Excel允许录入，否则Excel将拒绝录入。

选中A2:A10，设置数据验证，自定义公式为：

=COUNTIF(A:A,A2)=1

其中的A2，是所选区域的活动单元格。

3、圈释无效数据

对于已经输入的内容，也可以先设置好数据验证规则，然后使用圈释无效数据功能，方便地查找出不符合要求的数据。

4、各项预算不能超过总预算

如下图所示，是某人的育儿计划表，从幼儿园到结婚计划预算180万元，要求各分项预算之和不能超过总预算。

选中B2:B7单元格区域，数据→数据验证→自定义，输入以下公式。

=SUM($B$2:$B$7)=$D$2

设置完成后，B列各分项之和超过D2单元格的预算，就会弹出错误提示。

5、根据其他列内容限制输入

如下图所示，是某公司员工信息调查表，D列的配偶姓名填写时，要求C列的婚否一项中必须为“是”，否则禁止录入。

选中D2:D6单元格区域，数据→数据验证→自定义，输入以下公式。

=C2="是"

6、限制录入周末日期

如下图所示，是某人的工作计划表，B列的拟定日期填写时，要求不能录入周末日期。

选中B2:B6单元格区域，数据→数据验证→自定义，输入以下公式。

=WEEKDAY(B2,2)6

WEEKDAY(B2,2) ，根据B2单元格的日期，返回对应的星期。第二参数使用2，用数字1~7来表示周一到周日。WEEKDAY(B2,2)6，就是限定录入日期小于周六了。

7、制作下拉菜单

8、动态扩展的下拉菜单

如下图所示，要根据A列的对照表，在D列生成下拉菜单，要求能随着A列数据的增减，下拉菜单中的内容也会自动调整。

选中要输入内容的D2:D10单元格区域，数据→数据验证→序列，输入以下公式。

=OFFSET($A$2,0,0,COUNTA($A:$A)-1)

公式表示以A2作为基点，向下偏移0行，向右偏移0列，新引用的行数为COUNTA函数统计到的A列非空单元格个数，结果-1，是因为A1是表头，计数要去掉。

这样就是A列有多少个非空单元格，下拉菜单中就显示多少行。

9、动态二级下拉菜单

如下图所示，A、B列是客户城市和县区的对照表，在D列已经生成一级下拉菜单，要求在E列生成二级下拉菜单，要求能随着D列所选不同的一级菜单，E列下拉菜单中的内容也会自动调整。

选中要输入内容的E2:E6单元格区域，数据→数据验证→序列，输入以下公式。

=OFFSET($B$1,MATCH($D2,$A$2:$A$16,0),0,COUNTIF($A:$A,$D2))

公式表示以B1为基点，以MATCH函数得到的城市首次出现的位置作为向下偏移的行数。

向右偏移的列数为0。

新引用的行数为COUNTIF($A:$A,$D2)的计算结果。

COUNTIF($A:$A,$D2)的作用是，根据D列以及菜单中的城市名在A列统计有多少个与之相同的城市个数。有多少个城市名，OFFSET函数就引用多少行。

好了，今天咱们的内容就是这些吧，祝大家一天好心情！

今天的练习文件在此：

http://caiyun.feixin.10086.cn/dl/1B5CvatoKe8xw

提取码:poYq

图文制作：祝洪忠

上一篇：陈春花：「六问」企业核心价值观，蓄能未来发展
下一篇：【强交付招聘官】实现高效率招聘与面试（十三）

导读：水下的“甜脆果”，是菜也是果，儿时当零食吃，因采挖难而少人种天气逐渐冷起来了，而北方地区此时基本都已经入冬了，好些地方都已经开始供暖了，农民又要开始“猫冬..

安徽：繁昌疫情传播力“超乎想象”_繁昌疫情最新数据消息

澎湃新闻记者蒋子文3月24日以来，安徽省芜湖市繁昌区4天内新增新冠肺炎本地确诊病例5例、无症状感染者21例。近日，芜湖市已宣布打响“繁昌保卫战”。3月28日，芜湖市..

excel如何查找重复数据的简单介绍

很多朋友对于excel如何查找重复数据和不太懂，今天就由小编来为大家分享，希望可以帮助到大家，下面一起来看看吧！在excel表中怎么查找重复数据表格数据比较长时，需要查找一些重复数据，使用countif函数一下子就可以解决。以下是我为您带来的关于excel表中查找重复数据，希望对您有所帮助。exce..

数字中国建设多项数据亮眼

excel横排数据变竖排_excel中横排数据变竖排

很多朋友对于excel横排数据变竖排和excel中横排数据变竖排不太懂，今天就由小编来为大家分享，希望可以帮助到大家，下面一起来看看吧！excel怎么把横排名字转成竖排Excel表格文字横排设置成竖排技巧方法一、利用文字方向来实现竖排首先，光标单击选中需要竖排的单元格，然后单击菜单栏的“格式”下拉列..

网友建议惩治统计数据造假

俄军公布惨重伤亡数据_俄军伤亡真实数据

资料图算起来，从2月24日开战，这场二战后欧洲最大的地面战争，已经进入第二个月。两军交战，损失到底怎样？当然，这肯定是一个罗生门。看俄军的简报，乌军肯定早已被彻底..

俄军公布惨重伤亡数据_俄军不公布伤亡

【环球时报综合报道】在美国总统拜登在欧洲访问之际，俄乌战场上的炮火未停。《基辅独立报》引述乌克兰军参谋部消息称，有情报表明俄罗斯希望在卫国战争胜利日5月9日前结束..

俄军公布惨重伤亡数据_美国公布俄军伤亡

俄国防部继续举行特别军事行动简报会，俄军两上将在行动简报会上发言，披露战果与损失情况3月26日晚间，俄联邦国防部进行了最新一轮关于俄武装部队在乌克兰特别军事行动的..

汽车的钣金是什么意思_汽车钣金数据复原是什么意思

随着我国汽车保有量高速增长，堵车多，停车难，交通事故也是频发。只要有轻微的事故，一般都得经过钣金喷漆后期修理后才能恢复本来面貌。因此汽车钣金喷漆技术也越来越吃香..

“开年之季”数据背后的中国经济

《苹果手机卸载的软件怎么彻底清除数据》

演示机型：Iphone 13系统版本：iOS15.4苹果已卸载应用怎么删除数据共有4步，以下是Iphone 13中卸载应用后删除数据的具体操作步骤：操作/步骤1设置中点击通用首先..

《苹果如何清理系统数据》

演示机型：Iphone 13系统版本：iOS15.2苹果的系统数据怎么清理共有3步，以下是Iphone 13中清理系统数据的具体步骤：操作/步骤1通用中进入储存空间在设置的通用界..

元旦游客数据暴涨，旅游业回暖了吗？媒体：一季度仍处蛰伏期

2023年1月1日，南京，游人在夫子庙景区赏灯拍照。视觉中国资料图作为“防疫新十条”后的首个小长假，2023年元旦假期不少地方的旅游业正快速回暖。2023年1月1日..

蔚来李斌就数据泄露一事致歉，称将追查到底

12月20日晚间，蔚来创始人、董事长李斌在蔚来官方社区就用户数据泄露一事发文致歉。李斌表示：“保护好用户信息安全是我们的责任，我们没有做好，向大家深表歉意，会对此次..

微信数据损坏什么意思_微信数据有损坏什么意思

大家好，小编来为大家解答微信数据损坏什么意思这个问题，微信数据有损坏什么意思很多人还不知道，现在让我们一起来看看吧！为什么微信会突然数据损坏?由于手机剩余空间不足可能会导致微信数据损坏。按照以下方法操作解决：1、清理手机内存，卸载手机上不常用的软件确保手机空间充足。2、重新登录微信..

“数据二十条”总体考虑是什么？怎样理解其内涵？国家发改委解答

“数据二十条”全文：中共中央国务院关于构建数据基础制度更好发挥数据要素作用的意见新华社北京12月19日电数据基础制度建设事关国家发展和安全大局。《中共中央国务院关于构建数据基础制度更好发挥数据要素作用的意见》（“数据二十条”）19日对外发布。意见出台的总体考虑是什么？..

本文由 Excel之家ExcelHome 来源发布

数据有效性的九种典型用法

评论问答