数据有效性的九种典型用法 Excel之家ExcelHome

数据有效性的九种典型用法
来源：Excel之家ExcelHome 2021年01月26日

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

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函数就引用多少行。

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

今天的练习文件在此：

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

提取码:poYq

图文制作：祝洪忠

职场

人效分析，高阶层HR必须要会的技能!

2021年已过近半，很多企业HR都在制作人效分析报告，通过分析人才的投入和产出，数字化呈现人才的价值，从而找到提升人效的方法，这也是从人力管理提升到人力经营的标志性工作。如何做好人效分析？今天小...

HR云公社 2021年05月29日
越没出息的人，越喜欢在这3件事上表现“出众”，要反思

俗话说：“烂泥扶不上墙”，这句话形容的是一个人生性颓废、消极，即使有人拉他一把，也不能成大事。每个人的成功，有30%是靠外界的力量，例如机遇、人脉、运气等；有70%的是靠内在的潜力，例如你的意志力、毅力...

白领英才猎头 2021年05月29日
稻盛和夫：迷茫时的3个重要决定，改变了我的人生轨迹

命运是掌握在自己手里的，越早找到方向，就会越早的走出迷茫与困惑，也更容易在今后的道路上早日取得成就，成就属于自己的人生价值。本文精选了稻盛先生在迷茫时做出的重要决定，希望您品读完后，能从中受益。01...

Seelong 2021年05月29日
亚马逊首席科学家：工作5年反思!

“ 从象牙塔步入职场，很多人都会感到迷茫或是困惑：这条漫漫长路该怎么走？又如何走好？图片来自 Pexels最近，亚马逊资深首席科学家、AI 大牛李沐，根据自己五年的工作过往，在知乎上分享了《工作五年反思》一...

51CTO技术栈 2021年05月29日
一半孩子分流去打工、读职高：比起高考，中考可能是更残酷的“一考定终生”

作者：王耳朵，来源：王耳朵先生（ID：huangezishiba）感谢授权短篇集推送这篇文章，我想先跟大家分享3个孩子不同的人生故事。第一个故事，是知乎一位匿名用户的自述。2002年，她15岁，刚初中毕业，决意辍学。一...

短篇集 2021年05月29日
2021了，汉堡王和麦当劳还在相爱相杀

作者：二汪视觉：Mitter近日，创意代理商Buzzman为比利时汉堡王推出了系列广告「Come and get it back」。<< 滑动查看下一张图片 >>由于疫情状况的好转，线下门店即将迎来重新开业。于是，汉堡王极具诚意地挑...

广告头条 2021年05月29日
借某易HR言论事件，说说HR和求职者之间的“类医患关系”

近日某易HR的一番言论，又让HR被送上了风口浪尖。还有不知道这件事儿的小伙伴，51酱先来带大家了解前因后果。01HR言论引争议企业开除员工并道歉回顾此次事件，27日，一张某易HR的朋友圈截图在网络热传。截图中，...

51社保网 2021年05月29日
周末读书会：是真忙，还是瞎忙?

大家应该看过一部纪录片，叫《我在故宫修文物》，讲的是一群平凡而普通的人在故宫修文物的故事。影片中没有跌宕起伏的故事情节，只有每个文物工作者在专心投入工作的日常生活。其中有一个让我印象最深刻的是关...

灰鸽叔叔 2021年05月29日
对左晖的评价为什么会两极分化|观察家

企业家应该重视社会情绪变化背后的深层次原因，从追求极致效率到关注每一个人的发展，是中国企业家集体面临的一场思想深处的转型考验。文｜《中国企业家》记者万建民头图插画｜肖丽链家和贝壳创始人左晖英年早...

中国企业家杂志 2021年05月29日
他们躺平的背后，竟是原生家庭的困局

在“内卷”成为社交热词的同时，“躺平”悄无声息的拉起了对立大旗。躺平一词最早源自百度贴吧内一则题为《躺平即是正义》的贴文。躺平比佛系更进一步，意思是年轻人不想做赚钱的机器，主动降低欲望，尽量减少工...

出飒 2021年05月29日
没有淡季的市场，只有淡季的思想!

问题：怎么才能每天都收到这种文章呢？答案：只需要点击上面的《助成学堂》关注即可！1、顾客是最好的老师，同行是最好的榜样，取众人之长，才能长于众人。2、依赖感大于实力。销售的97%都在建立信赖感，3%...

助成学堂 2021年05月29日
销售5条铁律，条条击中你的内心!

很多成功的销售都会告诉我们，只要努力付出就一定会有回报。如果你的业绩不够好，那一定是方法不对，一定是不够努力，数量决定质量等等，但其实回头一想，销售的5条铁律，也许可以帮你很大的忙！01.思维相比于销...

总裁俱乐部 2021年05月29日
震惊!雷军告诫年轻人不要提意见，背后原因却令人暖心

编辑：人力君作者：西西来源：人力资源管理（ID：renliziyuanguanli）前有董明珠称年轻人找工作不应该把钱放在第一位，后有雷军说年轻人入职半年内不要提意见。这样的话一出，很多网友都不乐意：“不都是“仗着...

人力资源管理 2021年05月29日
创业合伙人十个问题

创业合伙人十个问题1、创业团队是否有大家信服明确的老大？企业的股权架构设计，核心是老大的股权设计。老大不清晰，企业股权没法分配。创业企业，要么一开始就有清晰明确的老大，要么磨合出一个老大。很多公司...

总裁微刊 2021年05月29日
[项目纪实]某大型烟草企业绩效管理体系搭建项目纪实

客户行业烟草行业问题类型职能部门绩效考核【客户评价】职能部门的考核一直是困扰我们的一大难题，各个职能部门考核成绩无差异，有明显的“大锅饭”现象，对此，我们自身也做了很多努力，尝试了几次改革，但...

华恒智信 2021年05月29日

1 2 3 4 5 >>

职场

公众号 • Excel之家ExcelHome

热门文章

近期更新