D8.神奇的下拉-数据验证

作者: 7850cf9dc417 | 来源:发表于2018-09-08 16:14 被阅读0次

周末必须要打一下鸡血~喊一下咱们2队的口号：
2队~E战到底
E点E滴，融为E体
万众E心，E战到底

一、基本用法
1、手动输入
（1）针对前提是已经输入部分内容，使用快捷键Alt+↓即可快速生成
（2）选定目标区域，选择“数据”，点击“数据验证”，验证条件选择“序列”，来源直接手动输入相关内容，注意此时的分隔符需要用英文状态下的逗号

image.png

2、引用
引用不同工作表的数据

引用.gif
3、名称框
直接将被引用的目标区域定义名称框，即可直接使用
4、其他

其他.png

二、进阶用法
1、提示信息（可批量生成提示信息）
2、出错警告（根据自身需要进行设定后输入错误预警提示）
3、圈释无效信息（快速校验错误）

提示.png

三、动态下拉列表
当原设定下拉菜单中需要增减数据时，正常情况是需要重新设置下拉菜单，此时，我们可以借助生成超级表的方式来设定动态的联动下拉列表
步骤：
（1）选定原引用数据，快捷键Ctrl+T生成超级表，并对生成的超级表目标数据定义名称框
（2）到“数据验证”下，选择“序列”，来源选择“已设定的名称框”即可

动态下拉.gif

四、快速录入当前时间
步骤：
（1）在空白单元格输入函数“=now（）”，其意义是记录当下的时间
（2）此时，在“时间列”里直接选择“数据验证”，验证条件“序列”，来源直接选取刚刚设定的函数单元格，点击“确定”
（3）随后设置单元格格式，Ctrl+E调出单元格格式设置，选择“自定义”，选择需要显示的时间格式即可

时间.gif

五、会计使用借贷只能输入一方（COUNTA函数）
借助“=counta”函数，意味为返回参数列表中非控制的单元格个数。目的是函数可以计算单元格区域内或者数组中包含数据的单元格
（1）选择需要设置的借贷方的区域，选择“数据有效性”
（2）选择“设置”—“自定义”—输入函数“=counta（目标区域）=1”，需要注意的是目标区域需要进行锁定

借贷.png

借贷.gif

六、建立二级下拉菜单（INDIRECT函数）
函数的意思为：在列和列之间创建二级下拉菜单，其联动于一级菜单下所属的二级明细

indirect函数理解.png

例子：设置省份后面的二级下属城市
步骤
（1）首先，采用定义名称框的方式先设置好一级省份名称
（2）由于二级城市列表有空白单元格，首先需要选择“定位”—“常量”，选中目标单元格
（3）在“公式”中选择“根据所选内容创建”，默认选择“首行”，其目的是为了设置每个省份下属的城市可以有效区分
（4）选择“数据有效性”—“序列”—来源需要手动输入函数“=Indirect（一级区域单元格）”

二级.gif

注意：如果需要增减省份以及二级城市，并带有联动效果，可借助OFFSET函数

已上述为例，
省份A2:A22中设置数据验证中的序列，来源输入：
=OFFSET( $F$ 1,0,0,1,COUNTA( $F$ 1: $XFD$ 1))
二级城市B2:B22中设置数据验证中的序列，来源输入：
=OFFSET( $D$ 1,1,(MATCH(A2, $F$ 1: $XFD$ 1,0)-1),COUNTA(OFFSET( $D$ 1,1,(MATCH(A2, $F$ 1: $XFD$ 1,0)-1),999,1)))