大家好,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函数,部门信息无论怎么变化,我们都能得到更新后的部门信息,这时只需要在数据有效性中的来源重新修改成公式即可。
操作步骤如下:
- 选择“数据”菜单
- 点击“数据工具”中的“数据验证”
- 更改设置标签中的来源:=OFFSET(部门!$A$1,1,0,COUNTA(部门!$A:$A)-1,1)
- 选择“确定”,完成!
这时我们来增加或删除、更改部门信息时,看看下拉菜单是否会跟着变化啦。赶紧试试吧!
注意:因我们是在“人员名单”表格中引用“部门”表格中的信息,需要在公式中加入表格名称。
在数据有效性中设置公式
当然除了用直接用公式作为数据来源外,我们还可以先定义一个名称,然后在数据有效性设置中引用名称也可以达到同样的效果,并且这样看起来更简洁,我们来看看如何操作↓↓↓:
- 选择“公式”菜单
- 选择“定义的名称”中的定义名称
- 在新建名称窗口上名称填入“部门”
- 范围选择“工作簿”
- 引用位置填入公式:=OFFSET(部门!$A$1,1,0,COUNTA(部门!$A:$A)-1,1)
- 定义名称完成,然后我们再设置数据有效性。
数据有消息引用名称
要点总结:
- 利用OFFSET函数动态返回部门区域
- 部门的个数(行数)可以通过计数函数COUNTA确定
- 数据有效性中的来源可以引用多种形式的数据,如公式或定义名称
今天的分享就到这里啦,熟看百遍,不如操作一遍,赶紧打开电脑试试吧!