背景
不知道大家在工作中是否碰到过这样的情况：进行表格填写时，为了规范填表人的填写内容，方便统计和数据处理，会在表格相应位置设置下拉菜单，提供可选项。接下来主要介绍两类情况，一种是单项无关联的下拉菜单，列出所有可选项即可；另一种是关联下拉菜单，即后一列可选项会根据前一列所填内容发生变化。
操作步骤-无关联下拉菜单
首先选择你要设置下拉菜单的单元格，在Excel上方的菜单栏中，进入“数据”菜单栏，找到“数据验证”，鼠标左键单击，即可出来如下对话框
在对话框“设置”页面，“允许（A）”选择“序列”，对话框将出现如下变化：
接下来在“来源（S）”中填写你需要设置下拉菜单中所包含的所有可选项即可，填写有两种方式：
1）方式一：直接手写输入，但必须注意，所有选项之间的分隔逗号必须是英文输入法键入的，如果是在中文输入法键入的，系统无法识别分隔，会出现如下情况：
如果按照在英文输入法正确键入分隔逗号的话，下拉菜单才可设置成功，呈现效果如下：
2）方式二：首先在excel列举所有可选项，点击“来源（S）”下方框右侧的箭头进行框选即可。本例中所有可选项的列项（C2-C5）与下拉菜单（A2）设置在同一张sheet中，实际工作中，为了美观和避免误导填表人，一般可将列项单独放在一张新的sheet中，最后将列有所有列项的sheet进行隐藏。
通过以上两种方式设置好下拉菜单后，便可限制填表人在所有下拉菜单中进行选择，而不是随意填写了，如果填写内容并非下拉菜单中的列项，则系统会提示错误，且无法填写不匹配内容：
操作步骤-关联下拉菜单
接下来介绍关联下拉菜单的操作步骤，这种情况主要用到无关联下拉菜单设置方式中的方式二。
因此，我们首先把所有可选项的列项在新的sheet中（sheet2）列示：
关联下拉菜单想要呈现的效果是：如果前一个单元格选择苹果，那后一个单元格只能选择3、5、7、9四个数字中的一个，其余同理，即后一个单元格能选择的内容与前一个单元格所填内容相关。
接下来，首先在A列设置第一个单元格的下拉菜单，与无关联下拉菜单设置方式二步骤一致，不再详述，如下所示：
然后在B列设置与A列相关的第二个下拉菜单，还是先打开“数据验证”，选择“序列”，在“来源”下方框中输入如下函数：
=OFFSET(Sheet2!$A$1,1,MATCH($A8,Sheet2!$A$1:$D$1,0)-1,4)
关于OFFSET和MATCH函数的介绍，在文章末尾添加了两个链接，百度中均有非常详细的解说，在此不再详述。这里说明几个参数的选择，方便大家根据实际情况修改。
OFFSET参数1：所有可选项内容左上角第一个元素所在的单元格。如前所述，本例将所有可选项内容单独在sheet2中列明，且从A1开始列示，故参数1选择为“Sheet2!$A$1”；
OFFSET参数2：根据关联下拉菜单的可选项开始行数确定，本例在关联下拉菜单中所需要的可选项就是首行名称下面对应的该列数字，即从第2行开始，相对于参数1而言，就是往下移动1行，故参数2设置为“1”；
OFFSET参数3：往右移动的单元数，这个与前一个下拉菜单所填内容相关，假如前一个单元格填写为“菠萝”，则关联下拉菜单所需要的可选项就是第3列，菠萝下方的所有数据，那往右移动列数就是3，这个数字的确认用到了MATCH函数。
MATCH参数1：前一个下拉菜单所在的单元格；
MATCH参数2：搜索的范围，本例需要搜索前一个下拉菜单所填内容在所有可选项名称中的位置，即搜索范围是sheet2第一行（名称所在行），注意MATCH的搜索范围只能是单行或单列；
MATCH参数3：0表示精准匹配。
也就是说，假如前一个下拉菜单所填内容是“菠萝”，MATCH函数反馈的值就是3，那相对于OFFSET参数1而言，就是向右移动2列，因此OFFSET参数3在MATCH函数的反馈值上减1；
OFFSET参数4：所需要返回的行数，这里所需数据为4行，因此需要返回4行，参数4设置为“4”；
OFFSET参数5：所需要返回的列数，这里需要数据所占列数为1，因此参数5为1，（为1时可以省略，所以本例函数中未写）。
最后，可以把sheet2进行隐藏，鼠标在sheet2上右击选择隐藏即可。
至此便完成了关联下拉菜单的设置，如果大家有什么更好的办法希望可以互相交流。
微信扫一扫 