8、神奇炫酷的下拉菜单

一、基本用法

1、手工录入

如果数量比较少，可以先手工录入所涉及的所有部门，然后在下一个单元格，按Alt+ ↓ ，选择输入需要的部门即可。这里要注意：想要实现这样的功能前提是已经输入部分内容。

或者可以选择数据有效性——设置——允许“序列”——来源里录入销售部，财务部，设计部——确定

2、引用

当数据比较多时，就无法一个个录入，此时可以选择引用。数据验证——设置——允许“序列”——来源里选择参数表里的信息，确定——即可选择

3、名称框

无论数少还是相当多，可以用名称框的方法。首先把参数表里的数据在名称框定义名称——选中需设置区域——数据验证——设置——允许“序列”——来源里直接输入定义的名称或按F3选择——确定

除以上三个方法外，其他的验证条件，比如整数大于小于几，日期、时间、文本长度都可以设置。

二、进阶用法

1、提示信息

在数据表中有时 批注并不好用，显示的话会遮住其他信息，这是可以使用数据验证提示信息。数据验证——输入信息——标题，信息——确定

此提示信息还可以移动。

2、出错警告

数据验证——出错警告——填写信息——确定。当录入错误时，就会有以下提示。

3、圈释无效信息

当设置了数据有效性后，选择圈释无效信息，可以将出错的信息标识出来。数据验证——圈释无效信息——完成。

三、动态下拉菜单

利用超级表来实现。选中数据——插入——表格——确定或者直接Ctrl+T创建。之后将此表定义名称框为销售员。在需建立下拉菜单的单元格：数据验证——设置——序列——来源：=销售员——确定就可以了。当销售员表出现变化时，右边的单元格也会联动。

四、快速录入当前时间

当需要记录录入时间时，可以利用公式now（）在旁边单元格显示当前时间，然后将数据设置有效性，进行选取记录。

五、借贷只能一方输入

=COUNTA($A2:$B2)=1 

COUNTA函数：返回参数列表中非空值的单元格个数。利用函数 COUNTA。可以计算单元格区域或数组中包含数据的单元格个数。

六、二级下拉菜单

选中省份区域，定义名称：省份。数据验证——设置——来源“省份”——确定，省份列即设置好了。

然后选中省份和城市区域——定位常量（可以只将省份和城市区域选中）——公式——根据所选内容创建——根据情况选择首行或最左列——确定——选择城市区域——数据验证——序列—— 来源=INDIRECT(A2)

动态二级下拉菜单，需要以下两个公式

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)))

七、联想式下拉菜单

操作步骤：

第1步  源数据排序。目的是把相似的内容排列在一起。如上图C列所示。

第2步  选取A列要设置的区域，右键菜单 - 定义名称。在如图所示的新那建名称窗口中:

名称：城市

引用位置：

 =OFFSET('7.联想式下拉菜单'!$C$1,MATCH('7.联想式下拉菜单'!E6&"*",'7.联想式下拉菜单'!G:G,0)-1,,COUNTIF('7.联想式下拉菜单'!G:G,'7.联想式下拉菜单'!E6&"*"),1)

公式说明：match（）查找输入的字符串在C列的开始位置，&"*"实现模糊查找。

Countif（）统计以输入字符串开头的车型有多少个

offset()获取开始行数和总行数，用offset生成动态引用区域。

第3步数据选项卡  -  数据有效性 - 允许“序列”，来源 =城市

第4步  在数据有效性窗口中，去掉“输入无效数据时显示出错警告”的勾选。