2.3 为一个序列设置数据验证_Excel2016数据处理与分析实战秘籍-QQ阅读中文短篇网

上QQ阅读APP看书，第一时间看更新

2.3 为一个序列设置数据验证

在Excel中，非序列和序列是一对相对的概念。非序列是指数据的规律性不强，即便排列在一行或一列中，也不作为数据验证所规定的范围。序列则指一行或一列数据呈现出一定的排列规律，也就是说数据间有一定的逻辑关系，并作为数据验证所规定的范围，形成一个下拉列表，供用户选择性输入，达到规范数据的目的。

为非序列数据设置的数据验证，有些能够准确规定数据的长度、类型等，有些只是大概规定数据的范围，起到了一定的约束数据的作用，不能杜绝以假乱真的数据，出了错只能靠仔细、认真的检查来纠正。而以一个有限长度的序列设置数据验证，则允许输入的数据只能是这个序列中的任意一个数据，不仅可以方便用户录入，而且可以规范数据。当然，设置“序列”类的数据验证后，数据录入肯定规范、统一，但未必快速、高效。特别是序列长度较长时，花费在查找序列条目的时间可能会耗时较长。这种情况下，设置了数据验证，反而会降低输入速度。一般情况下，“序列”类型的数据验证带有强制性，如果缺少强制性，往往就失去了设置“序列”类型数据验证的意义。

为便于介绍，在文件“第2章数据验证”中插入一个工作表，更名为“序列”。在此工作表，建立数据表和辅助表，如图2-31所示。

图2-31 “序列”工作表中数据表和辅助表

在“数据验证”对话框的“设置”选项卡，如果在“允许”下拉列表中选择“序列”（默认勾选“提供下拉箭头”复选框），就可以在所选单元格形成有下拉列表的序列，方便用户并规范录入。设置“序列”时，在“来源”引用框中有不同的输入方式，包括直接输入和引用区域，下面分别进行介绍。

2.3.1 直接输入一个序列设置数据验证

在“序列”工作表中，B2:B6区域要求只能输入性别“男”或“女”，设置数据验证时要求直接使用这个序列，应该如何操作呢？

事先选择B2:B6区域，打开“数据验证”对话框，数据验证可以这样设置和使用：

1 选择“设置”选项卡。

2 在“允许”下拉列表中选择“序列”选项。

3 在“来源”文本输入框中直接输入文本“男，女”。词组之间用半角逗号隔开。

4 勾选“提供下拉箭头”复选框。

5 单击“确定”按钮，完成序列设置。

6 设置好序列类型的数据验证后，鼠标单击这些单元格，就会在其旁边出现下拉箭头，单击下拉箭头，出现下拉列表，用户可以从中选择需要的条目。

操作过程及效果如图2-32所示。

图2-32 直接输入序列及效果

显然，这种方法直截了当，适用于序列短且无变化的序列来源。

注意

下拉列表的宽度由具有数据验证的单元格的宽度来确定。用户可能需要调整该单元格的宽度，以防止将宽于下拉列表宽度的有效输入部分截断。

2.3.2 引用区域设置有序列的数据验证

设置“序列”类型的数据验证，如果序列较长或有数据表布局上的诸多考虑，可以引用本工作表的数据区域或其他工作表的数据区域作为数据来源。

在“序列”工作表中，有关“学历”的条目列表在E2:E4区域，分别为“大学”“中学”“小学”，要求C2:C6区域只能输入这些条目，设置数据验证时要求直接使用这个区域，应该如何操作呢？

事先选择C2:C6区域，打开“数据验证”对话框，数据验证可以这样设置和使用：

1 选择“设置”选项卡。

2 在“允许”下拉列表中选择“序列”选项。

3 在“来源”文本输入框中，可用鼠标直接拖选条目区域，也可以直接输入：

4 勾选“提供下拉箭头”复选框。

5 单击“确定”按钮，完成序列设置。

6 设置好序列类型的数据验证后，鼠标单击C2:C6区域的任意单元格，就会在其旁边出现下拉箭头，单击下拉箭头，出现下拉列表，用户可以从中选择需要的条目。

操作过程及效果如图2-33所示。

图2-33 引用区域序列及效果

显然，这种方法间接迀回，适用于序列长且无变化的序列来源。

注意

关于数据验证的序列，需要注意：

● 用于创建数据验证的序列可以存放在其他工作表中。如果下拉列表的条目列表在另一张工作表中，比如在“Sheet2”工作表的E2:E5区域，引用区域时必须带上工作表名称，写成“Sheet2! $E$2:$E$5”，注意表名之后的感叹号“！”。如果下拉列表的条目列表在另一个工作表上，而用户想要防止别人看到或进行更改，请考虑隐藏和保护该工作表。

● Excel默认只能使用一列多行（垂直方向）或一行多列（水平方向）的数据源作为数据验证的序列来源。如果序列来源区域为多行多列，无论是直接引用还是使用名称，单击“确定”时，Excel都会拒绝输入并弹出警告，如图2-34所示。

图2-34 数据源为多行多列时的警告

● 注意适当控制序列的长度。一个序列如果数十行、上百行，甚至几百行，拖动滚动条选择所需要的条目不是一件轻松的事情。显然，设置过长的序列会以牺牲时间为代价换取数据的规范性，是得不偿失的。

● 一般情况下，作为数据验证的序列条目至少要达到2个，才称得上为序列。特殊情况下，一个条目也可以成为最短的序列。如果心存疑虑，不妨看一看下面的例子，就豁然开朗了。

在“序列”工作表，K2:K6区域的数据为进校时间，要求精确到分钟，如何才能又快又规范地填写时间呢？可以通过为动态时间设置数据验证来实现。

事先将K2:K6区域的单元格格式设置为“2012/3/14 13:30”，在M1单元格输入如下函数公式：

打开“数据验证”对话框后，数据验证可以这样设置和使用：

1 选择“设置”选项卡。

2 在“允许”下拉列表中选择“序列”选项。

3 在“来源”文本输入框中，可用鼠标直接拖选条目区域，也可以直接输入：

4 勾选“提供下拉箭头”复选框。

5 单击“确定”按钮，完成序列设置。

6 设置好序列类型的数据验证后，鼠标单击K2:K6区域的任意单元格，就会在其旁边出现下拉箭头，单击下拉箭头，出现下拉列表，用户中可以从中选择需要的条目。

操作过程及效果如图2-35所示。

图2-35 引用单元格为动态时间设置数据验证

注意

如果时间超过了1分钟，请按键盘上的“F9”键刷新后再从下拉列表中选择当前时间使用。如果想看到“秒”，可以修改单元格的时间格式。

2.3.3 使用名称设置有序列的数据验证

可以将“序列”类型的数据验证所引用的序列来源区域定义为名称。名称包括静态名称和动态名称两类。下面分别进行介绍。

2.3.3.1 使用静态名称设置数据验证

所谓静态名称，是指一个名称所引用的序列来源是固定、静止不变的，一般情况下不增减条目。期望在数据验证中使用名称，首先要定义一个名称。如果要定义一个叫作“学历1”的静态名称，应该如何操作呢？操作步骤如下：

1 选择“序列”工作表E2:E4区域。

2 单击“公式”选项卡。

3 在“定义的名称”组中选择“定义名称”按钮。

4 在打开的“新建名称”对话框中，在“名称”框中输入“学历1”。作为“名称”的名字，“名称”要容易理解和记忆，以方便日后的修改。“范围”框里保持“工作簿”选项不变。“引用位置”框中已自动填写“=序列!$E$2:$E$4”，这里保持不变。注意对区域的引用为绝对引用。

5 单击“确定”按钮，完成名称“学历1”的定义。

操作过程如图2-36所示。

图2-36 定义名称

此处定义的名称“学历1”是一个静态名称，因为它引用的区域，或者说序列长度是固定不变的，除非重新修改“名称”所引用的区域。

使用静态名称作为序列来源，与引用区域数据作为序列来源，设置数据验证时的方法和步骤是完全一样的：选择“序列”工作表的C2:C6区域，打开“数据验证”对话框后，选择“设置”选项卡，在“允许”下拉列表中选择“序列”，在“来源”框中将公式修改为“=学历1”，保持勾选“提供下拉箭头”复选框状态，单击“确定”按钮，完成数据验证的设置。之后，用鼠标单击C2:C6区域的任意单元格，就会在其旁边出现下拉箭头，单击下拉箭头，出现下拉列表，用户可以从中选择需要的条目，如图2-37所示。

图2-37 使用名称设置数据验证序列

通过定义名称设置数据验证，可以引用其他工作簿的序列。比如，学历序列在另一个工作簿“数据.xlsx”中的工作表“Sheet1”的A2:A5区域。首先需要在源工作簿将这个区域定义为“学历”名称，序列来源为“=Sheet1!$A$2:$A$5”。设置数据验证时，需要用“[]”将源工作簿名称括上，手动输入公式“=INDIRECT（[数据.xlsx]"Sheet1!学历"）”。

【函数公式解析】

在本例静态名称公式中，INDIRECT返回由文本字符串指定的引用，此函数立即对引用进行计算，并显示其内容。如果需要更改公式中对单元格的引用，而不更改公式本身，请使用函数INDIRECT。具体语法为：

ref_text必需。对单元格的引用，此单元格包含A1样式的引用、R1C1样式的引用、定义为引用的名称或对作为文本字符串的单元格的引用。如果ref_text不是合法的单元格引用，则INDIRECT返回错误值。如果ref_text是对另一个工作簿的引用（外部引用），则被引用的工作簿必须已打开。如果源工作簿没有打开，则INDIRECT返回错误值#REF!。如果设置的数据验证是通过定义名称而引用其他工作簿的序列，另一个工作簿必须处于打开状态，本工作簿的数据验证才能使用。

a1可选。一个逻辑值，用于指定包含在单元格ref_text中的引用的类型。如果a1为TRUE或省略，ref_text被解释为A1样式的引用。如果a1为FALSE，则将ref_text解释为R1C1样式的引用。

2.3.3.2 使用动态名称设置数据验证

所谓动态名称，是指一个名称所使用的序列的条目可以根据需要随时增加或减少。

数据验证使用静态名称的最大弊病在于序列条目增减时，需要在“编辑名称”对话框的“引用位置”框中增大或缩小引用区域，否则，当序列条目增减时，数据验证的下拉条目要么不够，要么出现空白。如果数据验证使用动态名称，就能彻底、完美地解决序列条目增减的问题。

在“序列”工作表，事先运用前面介绍的定义名称的方法定义名称“学历2”，公式为：

将上例在“序列”工作表C2:C6区域设置“数据验证”所使用的“序列”公式修改为：

下面检验在数据验证中使用动态名称的效果，具体步骤如下：

（1）单击C2:C6区域的任意单元格，在下拉列表中可以选择需要的条目。

（2）如果将F4单元格的“高中及以下”修改为“高中”，在F5单元格增加“初中及以下”条目，单击C2单元格旁的下拉箭头，条目就动态变化了。

条目增加前后的对比效果如图2-38所示。

图2-38 修改条目后的效果（使用动态名称）

【函数公式解析】

在本例动态名称公式中，COUNTA函数计算非空单元格的个数，经常与OFFSET函数配对使用，二者交相辉映、相得益彰。COUNTA函数的语法在1.4.2节已介绍过，在此不再赘述。

式中，OFFSET函数是一个易失性函数，会随着工作表的刷新而动态刷新。OFFSET函数也是一个使用频率很高的函数，返回对单元格或单元格区域中指定行数和列数的区域的引用。具体语法为：

如果将这个语法翻译过来，就是：

reference（引用）必需。要以其为偏移量的底数的引用。引用必须是对单元格或相邻的单元格区域的引用；否则OFFSET返回错误值#VALUE!。

rows必需。需要左上角单元格引用的向上或向下行数。若使用5作为rows参数，可指定引用中的左上角单元格为引用下方的第5行。rows可为正数（这意味着在起始引用的下方）或负数（这意味着在起始引用的上方）。

cols必需。需要结果的左上角单元格引用的从左到右的列数。若使用5作为cols参数，可指定引用中的左上角单元格为引用右方的第5列。cols可为正数（这意味着在起始引用的右侧）或负数（这意味着在起始引用的左侧）。

height可选。需要返回的引用的行高。height必须为正数。

width可选。需要返回的引用的列宽。width必须为正数。

本例整个公式的意思是，以“序列”工作表的F2单元格为基点，向下偏移的行数为0行，向右偏移的列数为0列，非空单元格个数（学历的分类个数）作为返回引用的行高。通过增删D2:D4区域的文本可以随时修改这个数，从而实现动态引用。

注意

在单元格中编写函数公式，比起在“新建名称”对话框中的“引用位置”框中编写函数公式，更为方便、快速。用户最好先在单元格中编写好函数公式后，再复制到“新建名称”对话框中的“引用位置”框中。编写公式时，引用单元格或区域最好使用鼠标拖动选择的方式，Excel会自动为在其他工作表引用的单元格或区域添加工作表名称。要善于选择公式中的单元格或区域按F4键以转换绝对引用、混合引用。定义名称后，Excel会自动为在本表引用的单元格或区域添加本工作表名称。

2.3.3.3 使用动态表格设置数据验证

在Excel中插入一个“表格”，会将一个普通数据表转换成一个规范的可扩展的数据清单。Excel表格是一种特殊的名称，因而可以用于数据验证中。比起普通的动态公式，难度更低，无须使用复杂的函数公式。使用动态表格设置数据验证，首先要创建表格，然后再引用这个表格。

1．创建表格

可以将数据验证所使用的序列区域创建为一个表格。如果要将“序列”工作表G2:G5区域创建为表格，应该如何操作呢？具体操作如下：

1 选择“序列”工作表G1:G5区域中的任意一个单元格。

2 选择功能区“插入”选项卡。

3 在“表格”组中，单击“表格”按钮。

4 在弹出的“创建表”对话框中，单击“确定”按钮，就会插入表格，表格会自动进入筛选状态。“创建表”对话框会自动引用“表数据的来源”和勾选“表包含标题”复选框，用户也可以修改。

5 自动弹出“表格工具·设计”选项卡。

6 在“属性”组中，将“表名称”修改为“学历3”。

操作过程如图2-39所示。

图2-39 创建表格的过程及效果

注意

也可通过“套用表格样式”来创建表格。操作过程为：选择数据区域中的任意一个单元格→在“开始”选项卡中的“编辑”组中，单击“套用表格样式”按钮→在弹出的下拉菜单中，选择一种赏心悦目的样式→在弹出的“套用表格样式”对话框中，会自动引用“表数据的来源”和勾选“表包含标题”复选框→单击“确定”按钮，就会插入表格。除此之外，还可以使用“Ctrl+T”或“Ctrl+L”组合键调出“创建表”对话框以创建表格。

2．将表格用于数据验证

在“序列”工作表中，仍对C2:C6区域设置“数据验证”。将上例所使用的“序列”公式修改为：

下面检验在数据验证中使用表格的效果。

（1）单击C2:C6区域的任意单元格，在下拉列表中可以选择需要的条目。

（2）如果将G5单元格的“高中及以下”修改为“高中”，在G6单元格增加“初中及以下”条目，表格自动扩展。单击C2单元格旁的下拉箭头，条目就动态变化了。

条目增加前后的对比效果如图2-40所示。

图2-40 修改条目前后的效果（使用动态表格）

再次打开“数据验证”对话框，发现“序列”的“来源”框中的引用区域已由“$G$2:$G$5”自动扩展为“$G$2:$G$6”，如图2-41所示。

图2-41 引用区域已自动扩展

注意

在表格区域右下角用鼠标拖动控制点，可以加长或缩短表格。

2.3.3.4 使用多行多列的序列设置数据验证

从前面的介绍可知，一般情况下，数据验证所使用的序列源只能是一列或一行。但通过更改名称的来源区域，可以突破Excel名称数据源默认只能使用一列或一行的限制。

数据源在“序列”工作表的A10:C13区域，拟在“序列”工作表的E10:E13区域设置数据验证，如何为这多行多列的序列设置数据验证呢？

事先按照前面介绍的方法将“序列”工作表的A10:A13区域定义为名称“名单”。

选择“序列”工作表的E10:E13区域，打开“数据验证”对话框后，选择“设置”选项卡，在“允许”下拉列表中选择“序列”选项，在“来源”框中修改为“=名单”，单击“确定”按钮，完成数据验证设置。之后，用鼠标单击E10:E13区域的任意单元格，就会在其旁边出现下拉箭头，单击下拉箭头，出现下拉列表，用户可以从中选择需要的条目。

再在功能区选择“公式”选项卡，单击“定义的名称”组中的“名称管理器”，打开“名称管理器”对话框（也可以按“Ctrl+F3”组合键调出“名称管理器”），选中名称“名单”，在“引用位置”框内，将引用区域“=序列!$A$10:$A$13”修改成“=序列!$A$10:$C$13”，如图2-42所示。

图2-42 更改名称引用区域为多行多列区域

敲击Enter键确认，再单击“关闭”按钮，数据验证所用名称就包含多行多列的序列了。

数据验证所用名称为单列及为多行多列的效果对比如图2-43所示。

图2-43 数据验证所用名称为单列及为多行多列的效果对比

2.3.4 设置可以增缩序列的数据验证

2.3.4.1 设置随输入累加条目的数据验证

有时需要序列随着输入数据的增加而累加不重复条目，以方便以后同名数据的输入。

在文件“第2章数据验证”中新建工作表“累加条目”，并设计好数据表，如图2-44所示。

图2-44 “累加条目”示例表

表中，B列已输入2项器材，将对B列设置数据验证；H列为辅助列，是不重复条目，也是对B列设置数据验证所要使用的序列来源；当B列条目增加时，H列的不重复条目随之累加；反过来，B列的数据验证下拉列表随之加长。如何为B列设置随输入累加条目的数据验证呢？

1．定义名称

定义名称“row”，公式为：

定义名称“data”，公式为：

定义名称“x”，公式为：

定义名称“累加”，公式为：

2．输入公式

在H3单元格输入如下公式：

鼠标放置于H3单元格，将光标移至其右下角，当出现黑色十字时，拖动鼠标左键向下移动，将H3单元格的公式向下按需填充。

3．设置数据验证

选定B列欲设置数据验证的区域如B3:B2000区域，在“数据验证”对话框选择“设置”选项卡，在“验证条件”的“允许”下拉列表中选择“序列”选项，在“来源”框输入公式“=累加”。在“出错警告”选项卡，在“样式”中选择“信息”选项，允许用户自行输入。

这样，在B3:B2000区域，用户可以输入不在序列中的器材名称，序列可以自动累加、变长，也可以通过下拉菜单选择已有的器材名称代替直接输入。

4．输入数据试用

具体步骤如下：

（1）单击B5单元格，旁边会出现下拉箭头。单击下拉箭头，会出现下拉序列。

（2）如果在B5单元格输入“乒乓球”，单击B6单元格旁边的下拉箭头，就会出现累加了条目且重新按音序排了序的下拉序列。

（3）如果在B6单元格下拉列表中选择“足球”，单击B7单元格旁边的下拉箭头，就会发现下拉序列并未再累加，是一个没有重复值的序列。

（4）如果在B8单元格输入“AAA”，单击B9单元格旁边的下拉箭头，就会发现下拉序列再次累加，按音序排序，且排除了空单元格，如图2-45所示。

图2-45 随输入累加条目的效果

【函数公式解析】

在本例第一个名称“row”的公式中，ROW函数获取行号，IF函数获取B列不为“空”的单元格的行号，行号呈升序排列，最后的数字是最大行号。“9E+307”是9×10³⁰⁷，是Excel计算的一个最大数据。

LOOKUP函数是一个查找效率极高的函数，从后面往前面查找，查找小于并最接近于“9E+307”的数，这样，就查找到B列不为“空”的最末单元格的行数为“4”。

在本例第二个名称“data”的公式中，OFFSET函数用名称“row”获取的B列不为“空”的最大行数作为偏移的高度，以获取B列的数据，是一个数组，为“{"足球";"排球"}”。这是一个动态的序列，不再是名称“row”所引用的第3～2000行这个长区域，因而可大大缩短序列。同时，序列中还允许源区域有空格。

在本例第三个名称“x”的公式中，COUNTIF统计序列data“小于”（按音译排序）本身的“器材”个数。在Excel中，排序时，数字小于字母，字母小于文本，文本小于逻辑值，错误值不参与排序。“*10^8”是对统计到的个数配权，“ROW（data）-2”再用取得的行数进一步配权。序列若要按文本长度排序，可再加入“LEN（data）*10^10”配权。MATCH函数查找B列数据的位置（行号），查找结果与B列的行号作逻辑判断，其结果又作为内层IF函数的条件部分。内层IF函数没有第3参数，在条件不成立时，值为FALSE。外层IF函数屏蔽空行，得到配权后的序列“{100000001;2}”。

MATCH函数在范围单元格中搜索特定的项，然后返回该项在此区域中的相对位置，是一个应用非常广泛的函数。具体语法为：

lookup_value必需。要在lookup_array中匹配的值。例如，如果要在电话簿中查找某人的电话号码，则应该将姓名作为查找值，但实际上需要的是电话号码。lookup_value参数可以为值（数字、文本或逻辑值）或对数字、文本或逻辑值的单元格引用。

lookup_array必需。要搜索的单元格区域。

match_type可选。数字－1、0或1。match_type参数指定Excel如何将lookup_value与lookup_array中的值匹配。此参数的默认值为1。若第3参数为1或省略，MATCH查找小于或等于lookup_value的最大值，lookup_array参数中的值必须以升序排序，例如：…-2,-1,0,1,2,…,A-Z,FALSE,TRUE。若第3参数为0，MATCH查找完全等于lookup_value的第一个值，lookup_array参数中的值可按任何顺序排列。若第3参数为－1，MATCH查找大于或等于lookup_value的最小值，lookup_array参数中的值必须按降序排列，例如：TRUE,FALSE,Z-A,…2,1,0,-1,-2,…

MATCH返回匹配值在lookup_array中的位置，而非其值本身。例如，MATCH（"b",{"a","b","c"},0）返回2，即b在数组{"a","b","c"}中的相对位置。匹配文本值时，MATCH函数不区分大小写字母。如果MATCH函数查找匹配项不成功，它会返回错误值#N/A。如果match_type为0且lookup_value为文本字符串，可在lookup_value参数中使用通配符——问号（?）和星号（*）。问号匹配任意单个字符；星号匹配任意一串字符。如果要查找实际的问号或星号，请在字符前键入波形符（～）。

在本例第四个名称“累加”的公式中，OFFSET函数用COUNT函数统计的不重复个数作为偏移的高度，获取到设置数据验证需要用到的序列“{"排球";"足球"}”。显然，累加的序列已按音序排了序。

在本例第五个公式，即H3单元格的公式中，由于名称“x”的结果为“{100000001;2}”，SMALL函数又是对名称x形成的配了权的序列由小到大排序，其第一最小值为“{2}”。RIGHT函数取出这个序列各数据右边的5位数字，作为INDEX函数定位的行数，这里为“{"2"}”。INDEX函数返回名称data形成的序列的引用，即具体的器材名称“"排球"”。外层IF函数是屏蔽重复值所在行或空行。最后结果为“{"排球"}”。

本例的器材名称是按升序排列的，如果想优先按文本长度排序，在配权时可加进文本长度的配权公式“LEN（data）*10^10”。

注意

● 数据验证应该不支持三维引用，否则可不用H列作辅助列，也不用定义名称“累加”，而直接用公式“=OFFSET（$B$2,--RIGHT（SMALL(x,ROW(INDIRECT("1:" &COUNT(x)))),5),)”。

● 实际上，Excel已自带有随输入增加条目的功能，只是多了操作步骤。比如，A21:A22区域已有数据“刘备”“张飞”，鼠标右击A23单元格，在弹出的快捷菜单中选择“从下拉列表中选择”选项。则弹出下拉列表，下拉列表包括“刘备”“张飞”。如果在A23单元格输入“关羽”，右击A24单元格，在弹出的快捷菜单中选择“从下拉列表中选择”选项，“关羽”就添加到下拉列表了，并且，下拉列表是按音序排序的，如图2-46所示。

图2-46 Excel自带的随输入增加条目的功能

2.3.4.2 设置随选择缩减序列的数据验证

有时需要序列随着下拉选择输入而缩减序列，以免输入重复值。

在文件“第2章数据验证”中新建一个工作表，更名为“缩减条目”，并设计好数据表，如图2-47所示。

图2-47 “缩减条目”示例表

表中，已在B列初步输入1名监考教师，将对B列设置数据验证；D～F列为辅助列，D列为名单，E列是D列名单中不包含B列已有名单而余下名单的行号，F列则是这个缩减了的名单，也是设置B列数据验证所要使用的序列来源；当B列增加数据时，F列的条目则随之缩减，B列的数据验证下拉列表也随之缩减。如何为B列设置随选择缩减序列的数据验证呢？

1．输入公式

在E3单元格输入如下公式：

在F3单元格输入如下数组公式：

选择E3:F3区域，将公式填充到F300单元格。

在A3单元格输入如下公式：

将A3单元格的公式填充到A300单元格。

2．定义名称

定义名称“缩减”，公式为：

3．设置数据验证

选定B列欲设置数据验证的区域如B3:B300区域，在“数据验证”对话框选择“设置”选项卡，在“验证条件”的“允许”下拉列表中选择“序列”选项，在“来源”框输入公式“=缩减”。在“出错警告”选项卡，在“样式”中选择“信息”选项，允许用户自行输入。

这样，在B3:B300区域，用户可以通过下拉菜单选择输入，而教师名单序列会随着这个选择性输入不断缩减，选择输入会随之越来越方便。

4．输入数据试用

具体的操作步骤如下：

（1）单击B4单元格，旁边会出现下拉箭头。单击下拉箭头，会出现下拉序列。

（2）如果在B4单元格下拉列表中选择“孙七”，单击B5单元格旁边的下拉箭头，就会发现下拉序列已经不再包含“孙七”，序列得到缩减。

试用的效果如图2-48所示。

图2-48 随选择缩减序列的效果

【函数公式解析】

在本例第一个公式中，ROW函数获取行号。COUNTIF函数统计D3单元格教师在B列中出现的次数，结果为“0”，“0”与“1”进行比较，比较结果构成内层IF函数的判断条件。判断结果为“FALSE”，所以取得当前单元格的行号“3”。外层IF函数是屏蔽无数据的行。

在本例第二个公式中，花括号“{}”是数组公式的标志，无须输入，按“Ctrl+Shift+Enter”组合键自动生成。内层IF函数取得E3:E300区域不为空的单元格的行号。SMALL函数对E列的行号由小到大排序，其最小行号为“{3}”。INDEX函数根据SMALL函数排序的行号返回D列的教师名单，为“"张三"”。外层IF函数屏蔽无数据行。

在本例第三个公式中，通过公式形成一个动态的序号，此时为“{1}”。

在本例第四个公式中，COUNTIF函数统计F列不为空的单元格个数，个数减去列标题占用的1行作为OFFSET函数第4参数，即偏移的高度。OFFSET函数形成一个动态的不断缩减的序列，此时为“{"张三";"李四";"赵六";"孙七";"钱八"}”。

2.3.5 设置可模糊输入序列的数据验证

前面谈到，作为数据验证的序列来源不能过长，否则使用效果轻则差强人意，重则得不偿失。但在实际工作中，的确有超长的序列存在，而且对数据的规范性要求特别高，不允许有“同名异姓”的情况存在。那么，这是否意味着序列长和输入快是一对不可调和的矛盾？Excel对此是束手无策还是另有高招来化解这对矛盾？令人欣慰的是，在Excel中，借助函数公式、VBA和辅助列，还真能设置可模糊输入序列的数据验证。这种数据验证，可以输入一个数据中任意位置的汉字或该数据的汉字拼音首字母，输入时逐步缩小模糊查找范围，再形成序列供用户选择性输入。Excel这招“化骨绵掌”，与列筛选器中使用关键字筛选配合唯一值列表筛选的用法相同，再长的序列也会被缩小到几条之内，同时还保证了输入数据的精确无误。

2.3.5.1 表格布局

在文件“第2章数据验证”中新建一张工作表，工作表命名为“模糊序列”。为便于观察效果和论述，将原始数据表、辅助列和模糊序列布局在一张工作表里，表头如图2-49所示。

图2-49 “模糊序列”表头

表中，A、B列为源数据，将在I列设置可以模糊输入序列的数据验证。设置后，当在I列输入姓名拼音首字母或姓名的汉字时，将列出相关人名的下拉列表供选择输入，而且随着输入字母或汉字的增多，序列会变短，以方便选择。D、E、F列是为了实现目的要用到的辅助列，起一个过渡的作用。那么，如何为I列设置可以模糊输入序列的数据验证呢？下面，将详细地介绍这一过程。

2.3.5.2 自定义函数

设置可以模糊输入序列的数据验证，需要获取姓名的汉字拼音首字母。姓名的汉字拼音首字母可以在专用软件中取得，也可以在Excel中使用数组公式或自定义函数取得。鉴于数组公式取得拼音首字母的准确度要低于自定义函数，这里就介绍使用自定义函数取得汉字拼音首字母。

自定义函数必须使用VBA，按“Alt+F11”组合键，或者在“开发工具”选项卡“代码”组中单击“Visual Basic”按钮，均可进入Visual Basic编辑状态。自定义函数的基本过程为：

1 在工程资源管理器窗口，在左侧框里选择任意工作表。

2 在右键快捷菜单里选择“插入”菜单的级联菜单“模块”选项（或单击“插入”选项卡，在扩展菜单中选择“模块”选项），插入一个新模块。

3 在左侧框里双击“模块1”选项。

4 在右侧窗口中输入下列代码：

操作过程如图2-50所示。

图2-50 在VBA中插入模块

关闭Visual Basic编辑窗口，返回Excel编辑状态。这样，就自定义了一个函数“getpy”备用。

注意

自定义函数“getpy”取得的汉字拼音首字母是小写字母，主要考虑了模糊查找时输入的方便。如果想取得大写字母，可将诸如“="a"”式中的小写字母改成大写字母即可。这个自定义函数用了穷举法，如果用户发现有的汉字未能正确取出首字母，可将此字添加在代码中，形成这类样式“If char="昱" Then getpychar="y"”。代码中的“65536”兼顾了Excel 2003版本。文件需要另存为“启用宏的工作簿”。

2.3.5.3 定义名称

名称可以用来简化公式，有助于理解公式，本例可以设置两个名称。

要在I列设置可以模糊输入序列的数据验证，需要Excel“认识”当前I列活动单元格中已经输入的字母或汉字，所以需要定义名称来识别当前内容。姑且定义名称“add”，公式为：

由于把I列设置数据验证将用到的模糊序列放在了F列，这个模糊序列将会随着I列输入的字母或汉字的增多而缩短，所以需要将F列的动态模糊序列定义为一个名称。姑且定义名称“xulie”，公式为：

【函数公式解析】

在本例第一个名称公式中，CELL函数取得活动单元格的内容。如果Excel未刷新，这个内容将保持；如果Excel刷新了，就成了新的活动单元格的内容。

在本例第二个名称公式中，COUNTIF函数统计非空文本的个数，作为INDEX函数的第2参数，即行数。INDEX函数返回F列某行的引用，作为模糊查找序列的终点，与F2单元格起点的值形成一个动态的序列。INDEX函数此处是返回值的引用。“$F$3:INDEX”用法是一个比较新颖的用法。在后面介绍三级序列实例时，将详细介绍INDEX函数。

2.3.5.4 输入公式

D、E、F列是辅助列，辅助列的数据可以通过函数公式自动获取。

在D3单元格输入如下公式：

在E3单元格输入如下公式：

在F3单元格输入如下公式：

将D3:F3区域的公式向下按需填充。

【函数公式解析】

在第一个公式，即D3单元格的公式中，getpy函数为自定义函数，取得A3单元格姓名拼音的首字母。

在第二个公式，即E3单元格的公式中，FIND函数查找活动单元格内容在A3、B3单元格文本中的位置，提供了按姓名、部门名称中的部分字模糊查找姓名的两种方式。ISNUMBER函数判断是否数字，构成OR函数的参数。LEFT函数用活动单元格内容的文本长度决定左侧取值长度，再与活动单元格内容进行比较，匹配结果作为OR函数的参数。这意味着用拼音首字母查找姓名，是从左到右依次进行的，不能从中间截取一部分来模糊查找，主要考虑了查找的准确度。OR函数任意一个条件满足即为TRUE，成为IF函数的判断条件，条件成立，则取出当前单元格的行号。

在第三个公式，即F3单元格的公式中，SMALL函数对E列行号进行排序，其结果作为INDEX函数的行号。INDEX根据这个行号返回A列的姓名。IF函数的条件部分是屏蔽行号为“空”的值。

SMALL函数的返回数据集中的第k个最小值。使用此函数以返回在数据集内特定相对位置上的值。具体语法为：