返回目录:excel表格制作
只写最接地气的办公干货,加入我们,组队前行。
工作中我们会遇到这样的情况:综合部门的小伙伴会发放表格让其他部门填写,有的时候,交上来的表格会非常混乱,这个时候,如果对单元格进行一些特别的设置,就会在一定程度上避免这些问题。
今天我们就围绕这个话题,讲讲Excel中【数据验证】方面的问题(较早版本又名:数据有效性),分享一些办公“老鸟”的经验,相信你会有所收获。
下面,我们来详细讲解6种实用方法:
1、限制单元格输入重复值
步骤:选中需要进行设置的单元格(本案例 B5:B6),数据选项卡,数据验证,自定义,输入公式=countif(B:B,B5)=1,确定。
关键步骤截图:
这里先说一下countif函数,这个函数的作用是:用于统计满足某个条件的单元格的数量。
语法可以理解为=COUNTIF(要检查哪些区域? 要查找哪些内容?),具体到本案例就是countif(统计B列中,B5的值),翻译一下就是统计B5单元格在B列中出现的次数,所以countif(B:B,B5)=1就是限定B5单元格的值只能在B列出现1次,即唯一性,如果不满足唯一性就提示错误。
2、限制单元格输入非法值(黑名单)
上图演示的是,进入输入黑名单中的姓名:小张。(如果需要限定多个名字,在J列按顺序向下写)
操作方法同第一步,公式为=countif(J:J,B5)=0,注意J列是黑名单所在的列。
关键步骤截图如下:
按照前面所讲的内容,countif(J:J,B5)=0就是限定B5单元格的值只能在J列(黑名单)出现0次,即不能出现,也就是说禁止输入,如果不满足就提示错误。
限制黑名单输入的实际应用案例参考:
1分钟,从1万个人员中删除“黑名单”,这个技能只有0.01%的人会
重要概念提示:
细心的小伙伴们可能会有个疑问:为什么我们选中了2个(或多个)单元格,为啥只在公式中写入了B5,B6去哪里了?
写入的B5是我们选中区域的活动单元格,请注意我们选择区域的时候是从B5开始框选到B6的,默认情况下B5就是活动单元格,如果先选择的B6,B6就是活动单元格,公式中写入B6。活动单元格的概念在条件格式中也会经常遇到,一定要记住哦。
为了帮助大家更好地理解这一概念,我们做个活动单元格的演示:
上图中,我们用鼠标框选了B5:B15区域,注意看图中左上角鼠标指示的名称框的位置,这里显示的就是活动单元格的位置。随着我们一次一次按Tab键(键盘左侧),活动单元格也发生了变化。默认情况下,单个连续区域中,先选择的就是活动单元格。对于多个不连续区域的默认单元格,自己尝试一下吧。
3、限制文本长度的输入
上图演示的是,将学生的学籍号限定在4位或5位数,如果输入的位数不是限定范围,则提示错误。
步骤:选中需要进行设置的单元格(本案例 C5:C6),数据选项卡,数据验证,文本长度,介于,最小值输入4,最大值输入5,确定。如果不是4位或5位的文本长度就会提示错误,见上图演示。
关键步骤截图:
4、限制单元格输入不合规定的成绩
上图演示的是,我们将成绩限定在0-100范围内,允许输入小数和整数。
步骤:选中需要进行设置的单元格(本案例 D5:D6),数据选项卡,数据验证,小数,介于,最小值输入0,最大值输入100,确定。
关键步骤截图:
注意:如果成绩只允许输入整数,要将小数改为整数。最小值和最大值根据实际需要进行调整,比如0至150.
5、限制日期段的输入
上图演示的是,只能在【录入日期】中,输入今天及之前的日期。上图中输入了2019-5-28,超出了当前日期(2019-5-19),就会提示错误。
步骤:选中需要进行设置的单元格(本案例 F5:F6),数据选项卡,数据验证,日期,小于或等于,结束日期中输入=today(),确定。
关键步骤截图:
如果想限定2019-1-1至当天的日期,操作参考下图(today函数表示引用当前日期)。如果要限定2018年1月1日至2019年12月1日,则应在开始日期中输入2019-1-1,在结束日期中输入2019-12-1,以此类推。
6、下拉菜单制作
下拉菜单的制作是我们经常遇到的问题,应用范围极广,还可以配合函数公式、图表进行操作,达到动态图表演示的效果。
上图演示的是,在姓名列,只允许输入张森、黎明、王五这三个名字了。实际工作中根据需要,设定需要经常输入的信息。
步骤:选中需要进行设置的单元格(本案例 B5:B6),数据选项卡,数据验证,小数序列,在来源中输入张森,黎明,王五,确定。
关键步骤截图:
其他制作下拉菜单的方法和实际应用案例:
只需输入姓名就能调用员工所有信息——这Excel技能越早知道越好
利用下拉菜单制作的动态演示效果示例:
利用Excel做学生历次模拟考试成绩分析——这样填报志愿才不会亏
特别需要强调的是:
1、在Excel中,我们输入的标点符号全部应在英文半角状态下输入,否则无法达到我们演示的效果。
2、尽管数据验证的方法很强大,它也有自身的不足,如:从其他表格或区域中复制粘贴进来的数据,可破除数据验证的效果。(请自行验证,这里不再演示。)
今天你学会了吗?希望你能举一反三、灵活掌握。
你还有其他的方法吗?欢迎在留言区和我们一起讨论交流。
也欢迎写下你的疑问,我们再做进一步解答。
Excel办公精英,希望你每天都有收获。
↓↓↓↓↓往期精彩好文,点击即可查看
只需9秒——将Word中的参会人员名单,按1列导入到Excel里
仅用20秒——完成1000份录取通知书的制作,你还在加班吗?
1招破解Excel单元格合并后,批量填充序号,好用到没有朋友
行政人员必会的4种Excel图表创建方法,能用图说明问题的就别废话
欢迎关注同名微信公众号、今日头条号
【Excel办公精英】