|
大家好，Excel中我们经常会用到下拉菜单，这样可以给数据录入者提高录入效率，下拉菜单最常用的方法就是利用设置数据有效性，看看下图：
设置下拉菜单 用这种方式确实很好，但是细心的同学会发现一个问题，当公司组织架构调整后，增加或删除一个部门，这时要将新的部门名称添加到部门表格中，添加后我们再看看下拉菜单，其实是没有更新的，这样就不太智能了！有同学说，重新设置下数据有效性的数据来源就可以啦。当然，这也是可以的，当数据来源中的数据每天都有增加或更新时，这时你就会感到力不从心了。
下拉菜单不自动更新 对于这样的问题，我们来想想是否有解决方案呢？答案是肯定的，不然小编就不会分享这篇技巧啦！ 我们首先要解决的问题是，有没有一种方法，可以动态获取部门信息呢，其实熟悉函数公式的童鞋知道，利用查找函数OFFSET函数就可以实现。 只要调整其对应的参数，OFFSET函数可以动态返回单元格区域，从而得到我们想要的数据区域。 我们先来看看OFFSET函数的具体用法： OFFSET函数 函数功能：返回对单元格或单元格区域中指定行数和列数的区域的引用。 返回的引用可以是单个单元格或单元格区域。 可以指定要返回的行数和列数。 使用格式：=OFFSET(reference,rows,cols,height,width) 通俗解释：=OFFSET(参考单元格，偏移的行数,偏移的列数,返回数据区域的高度,返回数据区域的宽度) 我们来一一列出各个参数： 第1个参数：我们以A1单元格为参考单元格 第2个参数：部门信息在A1单元格的下一行即A1单元格向下偏移1行，这个参数固定为1 第3个参数：部门信息所在的区域不在A1单元格的右侧或右下侧，同样这个参数固定为0 第4个参数：部门信息所在区域的高度（行数），由于会随时增加或减少，这个参数是动态变化的，稍后我们来讲怎么得到这个参数的值。 第5个参数：部门信息所在区域的宽度（列数），部门信息不断向下更新，这里只有1列的情况，所以这个参数固定为1. 即以A1单元格为参考单元格，向下偏移1行=1，向右不偏移=0，部门的行数=动态变化，部门信息的列数=1，来返回整个部门的信息。因为部门行数会发生变化，除了部门行数未知，其他的参数基本都已确定好了，我们列出公式如下： =OFFSET($A$1,1,0,部门信息区域的行数,1) 未知的部门行数怎么确定呢？其实只需要一个简单的计数函数即可搞定， COUNTA函数 函数功能：返回非空单元格的个数 使用格式：=COUNTA(value1,value2,...) 通俗解释：=COUNTA(数据区域) COUNTA($A:$A)这样动态返回部门信息的个数（函数）公式，但是我们要去除表头，部门的个数即：COUNTA($A:$A)-1，嵌入到OFFSET中，来看看公式是否正确： 我们在C1单元格中输入如下公式：=OFFSET($A$1,1,0,COUNTA($A:$A)-1,1)，选择公式后按F9即可显示当前所有的部门：={'生产部';'研发部';'销售部';'系统部';'财务部'}，部门信息正确。 然后我们在A7单元格输入“生技部”后，此时部门信息有更新，我们再在C3单元格中输入=OFFSET($A$1,1,0,COUNTA($A:$A)-1,1)，同样的我们选择公式后按F9即可显示更新后所有的部门：={'生产部';'研发部';'销售部';'系统部';'财务部';'生技部'}——结果显示增加了刚刚添加的“生技部”，达到了我们的要求，看看下图操作↓↓↓：
OFFSET函数动态返回数据区域 通过OFFSET函数，部门信息无论怎么变化，我们都能得到更新后的部门信息，这时只需要在数据有效性中的来源重新修改成公式即可。 操作步骤如下：
这时我们来增加或删除、更改部门信息时，看看下拉菜单是否会跟着变化啦。赶紧试试吧！ 注意：因我们是在“人员名单”表格中引用“部门”表格中的信息，需要在公式中加入表格名称。
在数据有效性中设置公式 当然除了用直接用公式作为数据来源外，我们还可以先定义一个名称，然后在数据有效性设置中引用名称也可以达到同样的效果，并且这样看起来更简洁，我们来看看如何操作↓↓↓：
数据有消息引用名称 要点总结：
今天的分享就到这里啦，熟看百遍，不如操作一遍，赶紧打开电脑试试吧！ Excel伦特吧，只为提高效率！ |
|
|
来自： Chosefree > 《一 二 三级下拉菜单》
GIF
GIF
GIF
GIF
GIF
