返回目录：excel表格制作
EXCEL进阶课堂 · 函数说 持续更新！我们将为各位小伙伴提供更加专业、更加精炼、更加实用的EXCEL操作技能，帮助大家轻松解决工作任务，提高工作效率，不再做不停加班的表哥，表姐。欢迎各位小伙伴转发、点赞、讨论，更欢迎私信获取练习素材，刻意练习才能学有收获。
这是函数说的第22篇教程。
下拉列表，是EXCEL中进行数据录入的有利助手，一是可以提高输入效率，二是可以保证数据的规范性，是各位小伙伴值得去学习和掌握的技能。
进阶君通过观察，发现身边的朋友们在制作下拉列表时，通常做出的是静态的下拉列表，也就是说下拉列表一旦做好，里面的选项就是固定的，不会因为数据源的变化而自动变化。如何才能做出能自动调整的动态下拉列表呢？
制作下拉列表，有两个要素：一是数据源，也就是下拉选项的来源；二是目标单元格，也就是在哪些单元格上应用下拉列表。
1 静态下拉列表的制作方法
静态的下拉表，通常有两种制作方法，核心技能点是应用数据有效性设定。有如下案例：
（一）直接应用数据源区域的方法
这种方法比较简单，按如下步骤完成：
（1）选择目标单元格：要应用下拉列表的单元格为E3:E12，用鼠标拖拉的方式，将其选中。
（2）打开数据有效性设置窗口：选择EXCEL 数据 菜单，点选 数据有效性 命令，弹出数据有效性设置窗口。
数据有效性设置窗口
（3）设置有效性序列：在设置选项卡中，将 有效性条件允许 选择为 序列，将 来源 通过单选的方式设置为 H3:H6，确定即可。
以上设置的操作过程及效果如下动图所示：
（二）数据源名称命名法
这种方法，就是先将数据源区域，取定一个名称，用名称来代表区域。这种方法在二级菜单联动当中应用较多，本篇教程中起到基础学习的作用。
（1）数据源区域取名称：选择数据源区域 H3:H6，然后在名称框当中输入 选项，回车确认即可。
（2）选择目标单元格：要应用下拉列表的单元格为E3:E12，用鼠标拖拉的方式，将其选中。
（3）打开数据有效性设置窗口：选择EXCEL 数据 菜单，点选 数据有效性 命令，弹出数据有效性设置窗口。
（4）设置有效性序列：在设置选项卡中，将 有效性条件允许 选择为 序列，将 来源 通过单选的方式设置为公式：=INDIRECT（"选项"），确定即可。
公式：=INDIRECT（"选项"）的作用就是去引用名称为 选项 的数据区域
以上设置的操作过程及效果如下动图所示：
用这两种方法制作的下拉列表有一个致命的问题，那就是下拉选项不能动态调整。
这个案例中，如果班级要增加1个5班，那么就能重新去做下拉列表的所有步骤，无法自动的动态调整。有没有解决办法呢？当然有，可以运用动态下拉列表的制作方法来完成。
2 动态下拉列表的制作方法
通过前文的分析，我们不难看出，解决动态下拉列表的关键点在于数据源区域要能动态调整，不能是一个固定的区域。如何才能实现呢？
（一）数据源动态变化实现分析
案例中，看到的数据源区域是H3:H6，其中H3起点，H6终点。生活常识告诉我们，如果我们还要增加班级，通常是在下面增加，也就意味着，如果要使数据源成为动态变化的，那么我们的终点就不能固定。
但是终点单元格不固定，我们又无法来表示一个数据区域。所以我们还必须确定终点单元格。
如果我们找班级数据源列的第一个空白单元格，是不是可以说空白单元格上面单元格就是终点单元格呢？也就是说数据源列中的单元格个数应该是该列当中非空白单元格的个数。如何才能统计出一列当中非空白单元格的个数呢？COUNTA这个函数可以完成。
如果这个函数不了解的，请点击下面链接，学习该函数：
「函数说20」文本单元格计数？空单元格计数？两个函数搞定所有
案例中，公式=COUNTA（H:H）就可以统计出该列中非空白单元格的个数。但是小伙伴们需要注意的是，H2中的”班级“并不是选项之一，所以在这个案例当中，如果要统计选项的个数，应该是：=COUNTA（H:H）-1。
现在知道了选项的个数，并不代表知道了数据源选区。但是我们可以说从H3单元格开始，往下走=COUNTA（H:H）-1 个单元格，这个区域就是数据源选区。以某个单元格为基准，偏移N行N列，取N行N列的单元格，用OFFSET函数可以完成。
如果这个函数不了解的，请点击下面链接，学习该函数：
「函数说11」精确瞄准单元格？高手们都把offset函数当作狙击步枪
对于本案例当中，以H3单元格，偏移0行0列，取=COUNTA（H:H）-1行1列，就是选项数据源区域。
公式为：=OFFSET（$H$3,0,0,COUNTA（H:H）-1,1）
（二）数据源动态变化实现步骤
（1）选择目标单元格：要应用下拉列表的单元格为E3:E12，用鼠标拖拉的方式，将其选中。
（2）打开数据有效性设置窗口：选择EXCEL 数据 菜单，点选 数据有效性 命令，弹出数据有效性设置窗口。
（3）设置有效性序列：在设置选项卡中，将 有效性条件允许 选择为 序列，将 来源 通过单选的方式设置为公式：=OFFSET（$H$3,0,0,COUNTA（H:H）-1,1），确定即可。
这样设定好后，后续无论如何增加、修改、删除班级的信息，下拉列表都会自动的动态调整。
以上设置的操作过程及效果如下动图所示：
为方便小伙伴们学习，进阶君将原始素材共享出来，获取素材的方法：
第一步：关注 Excel进阶课堂。
第二步：私信 Excel进阶课堂，因为设定的是自动回复，所以内容一定要准确
私信内容：练一练
第三步：根据得到信息打开网盘，找到 第22讲动态下拉列表制作 工作簿 自行下载