在Excel中运用数据验证功能，能够对数据输入进行规范化管理，从而显著提升数据录入的整体效率。今天，我们将深入探讨函数公式在数据验证中的巧妙应用，呈现一个实用案例：通过在单元格内键入一个字母，即可动态生成以该字母为首字符的单词库。此类设置能够有效缩小数据验证的范围，避免用户在冗长的列表中耗费时间寻找所需数据。
1
成果预览
参照下图所示，当在A2单元格中键入字母“a”时，下拉列表将自动展示所有以“a”开头的英文单词。同理，输入“b”后，下拉列表将呈现以“b”为起始的单词列表。值得注意的是，若单元格内未输入任何字符，下拉列表将默认显示数据源中的全部单词。
2
实施流程
1、请参考下图，在“数据源”工作表的A2:A12范围内，我们预设了一系列用于制作下拉列表的数据。其中，具有相同首字母的单词必须按照特定顺序排列。
2、接下来，请选择“制作下拉菜单”工作表中需要设置动态搜索下拉菜单的单元格，然后点击【数据】菜单项下的【数据验证】选项，从而打开数据验证设置对话框。
（1）在【允许】选项区域，选择“序列”作为验证类型；
（2）在【来源】输入栏中，输入以下公式：=OFFSET(数据源!$A$1,MATCH($A2&”*”,数据源!$A$2:$A$12,0),0,COUNTIF(数据源!$A$2:$A$12,$A2&”*”))
（3）切换至【出错警告】选项卡，取消勾选“输入无效数据时显示出错警告”这一选项。若不取消此勾选，当用户在单元格内输入首字母后，点击单元格右下角的三角符号时，Excel将会弹出错误提示。
点击确认按钮，即可完成整个设置过程。
公式详解：
（1）OFFSET函数能够返回一个指定的单元格引用。例如，OFFSET($A$1,4,0,1)意味着以A1单元格为基准点，向下移动4行，定位至A5单元格；向左移动0列，依然保持在A5单元格；返回1行1列的单元格区域，结果仍然是A5单元格。因此，该公式最终返回的是A5单元格中的内容“back”。
（2）MATCH函数作为OFFSET函数的第二个参数，用于确定从“数据源”工作表的A1单元格开始，需要向下移动多少行。假设在“制作下拉菜单”工作表的A2单元格中输入字母“b”，则MATCH($A2&”*”,数据源!$A$2:$A$12,0)将返回“4”，表示在A2:A12范围内，首字母为“b”的单词首次出现的位置。
（3）COUNTIF函数作为OFFSET函数的第四个参数，用于计算返回的单元格区域包含多少行。如果“制作下拉菜单”工作表的A2单元格中输入字母“b”，则COUNTIF(数据源!$A$2:$A$12,$A2&”*”)将返回“2”，即A2:A12范围内首字母为“b”的单词数量为2。
（4）综合上述分析，当在“制作下拉菜单”工作表的A2单元格中输入字母“b”时，OFFSET函数结合MATCH函数和COUNTIF函数共同作用的结果是A5:A6单元格区域。将该函数组合返回的单元格区域作为数据验证对话框中“序列”的数据来源，当用户输入字母后，下拉列表将自动展示以该字母为首字符的英文单词列表。