原来你与Excel高手之间就差一个“数据验证”

来自:Excel技巧精选(微信号:ExcelSkill),作者:技巧妹 

数据验证在实际工作中经常使用到,比如下拉菜单的制作,防止重复值的录入等,本期技巧妹与大家分享数据验证的2个高级用法。


一、搜索式下拉菜单的制作方式


第一步:对数据进行排序


选中需要制作下拉菜单的区域,点击数据——排序——升序。




第二步:制作下拉菜单


选中区域,点击数据——数据验证——序列,之后在来源中输入公式

=OFFSET($A$1,MATCH(C2&"*",$A$2:$A$25,0),0,COUNTIF($A$2:$A$25,C2&"*"),1)

,然后再选中【出错警告】,取消“输入无效数据时显示出错警告”复选框即可。



公式说明:


这里的数据源是最重要的,所以这里简单解释一下这个公式的含义:


OFFSET函数语法:

=OFFSET (基点,偏移的行数,偏移的列数,新引用的行数,新引用的列数)


MATCH函数语法:

=MATCH(查找对象,指定查找的范围或者数组,查找方式)


COUNTIF函数语法:

=COUNTIF(条件区域,条件)


下图是公式的详解:



二、带图片的下拉菜单怎么设置?


第一步:在工作表中插好图片


比如将“刘涛、靳东、胡歌、孙俪”输入到第一列,然后插入相对应的图片,如下图所示:



第二步:新建一个工作表


在新的工作表中,我们简单设置下表格,具体如下图所示:



首先,鼠标放在A2单元格,点击【数据】下的【数据验证】,然后在相应的对话框中,选择【序列】,来源则设置为前面已经弄好的名字。



第三步:给图片进行照相


在这里我们利用照相机功能进行粘贴图片,若是工具栏中没有照相机的,可以去Excel选项中【自定义功能区】的【不在功能区命令】自行添加,这里就不再细说了。


那如何给图片拍照呢?


首先选中图片所在的单元格,然后点击【照相机】,之后在相应的工作表贴上即可。



步:定义名称框


点击任意单元,选择【公式】下的【定义名称】,在这里帮主是创建“图片”然后在引用地址中输入公式“=INDIRECT("Sheet13!$B$"&MATCH(Sheet14!$A$2,Sheet13!$A:$A,0))”



第五步:对图片进行引用


点击图片,将其公式设置为“=图片”即可。



以上就是今天要与大家分享的有关数据验证的高级用法,若是想要了解它的基础用法,可以查看以下两篇:

1、Excel下拉菜单怎么制作?如何设置二级联动下拉菜单?

2、《赶紧来掌握这6种数据验证技巧

推荐↓↓↓
Excel技巧精选