EXCEL表格设置下拉列表，如何让选项自动更新，一个公式搞定

说起数据验证，相信大家一定不陌生，肯定会说设置下拉列表，通过选择选项的方式来代替重复录入的工作，提高工作效率嘛。

而下拉选项里的选项，通常会根据选项的多少使用2种方式来提供，选项少的直接在数据验证窗口的来源输入框里输入选项，而选项多的则在表格里找个空白地，先列出来，然后在数据验证的窗口选择来源区域。

不废话了，直接上步骤图，来看看常规的做法。

多选项的先在表格里列出所有的选项。

选择表格里要设置下拉选项的单元格区域，点击菜单栏上“数据-数据验证-数据验证”。

打开数据验证窗口，点击验证条件，允许选项框里，选择“序列”，选项少，就来来源里直接输入选项，每个选项之间用英文逗号分隔。

而现在面临的是多个选项，我们也提前在表格里准备好了选项数据列。点击右侧的箭头，移动鼠标去选中选项数据列，来源里显示出单元格的地址，注意默认添加了绝对引用，也就是这个选项区域是固定不变的。

点击确定，单元格右侧出现下拉箭头符号，点击弹出列表，和我们K列准备的选项是一样一样地。

但问题来了，如果我们选项发生了变化，有新增加的吧，左侧的下拉选项里不会自动增加，有删除的吧，左侧的下拉选项下方又显示出空白，如图所示，删除了K列的部分产品编号后，左侧的下拉列表下方显示出大片空白行，不美观啊！咋办！

在网上搜索过大量的教材，包括我自己以前也是这么干的，打开数据验证窗口，修改来源啊，重新选择一次选项区域。此方法确实可行，但对于懒惰的我还是嫌弃麻烦啊，偶尔更新选项后，还容易忘记再去改验证窗口的来源，那能否自动刷新下拉列表呢？

嘿嘿，自从学习了offset函数后，发现我的梦想实现了。

话不多说，改数据验证窗口的来源，咱这次直接设置一个公式：=OFFSET($K$2,0,0,COUNTA($K:$K)-1)，看下效果先。

点击确定后，选择单元格，咦？选项下方的空白行没有了。

K列新增加两个选项，G列的下拉列表，自动增加了这2个选项，不用再去数据验证窗口，重新选择来源区域，我的梦想实现了。

这个公式啥意思呢，把这个公式复制过来，找个空白单元格，粘贴上公式，回车后显示出一个数组结果。

点击菜单栏下方的“FX”图标，打开函数参数面板，这也是我喜欢的一个窗口，对于搞不太懂的函数，就喜欢在这里翻看每个参数的意思。

OFFSET函数，以指定的引用为参照系，通过给定偏移量返回新的引用。

这里就是以K2也就是选项区域的第一个单元格为基点，向下偏移0行，向右偏移0行，第四个参数就是返回的行数，这里使用COUNTA函数，计算出K列非空白单元格的个数，为啥减1，因为K1是标题，不算在选项里。

一句话来解释这个函数公式就是从K2单元格开始，获取K2列非空白单元格的个数-1行，那不就是K列的选项有几个就获取几个，所以不管新增加还是删除，都会返回最新的选项值。

咋样，就加这样一个公式，是不是又能减轻你的工作量，多多的偷一会儿懒，关键是简化了工作，就是提高了工作效率啊！