大家好,，Excel中我們經(jīng)常會(huì)用到下拉菜單，這樣可以給數(shù)據(jù)錄入者提高錄入效率,，下拉菜單最常用的方法就是利用設(shè)置數(shù)據(jù)有效性,，看看下圖：

GIF

設(shè)置下拉菜單

用這種方式確實(shí)很好，但是細(xì)心的同學(xué)會(huì)發(fā)現(xiàn)一個(gè)問題,，當(dāng)公司組織架構(gòu)調(diào)整后,，增加或刪除一個(gè)部門，這時(shí)要將新的部門名稱添加到部門表格中,，添加后我們?cè)倏纯聪吕藛?，其?shí)是沒有更新的，這樣就不太智能了,！有同學(xué)說,，重新設(shè)置下數(shù)據(jù)有效性的數(shù)據(jù)來源就可以啦。當(dāng)然,，這也是可以的,，當(dāng)數(shù)據(jù)來源中的數(shù)據(jù)每天都有增加或更新時(shí)，這時(shí)你就會(huì)感到力不從心了,。

GIF

下拉菜單不自動(dòng)更新

對(duì)于這樣的問題,，我們來想想是否有解決方案呢？答案是肯定的,，不然小編就不會(huì)分享這篇技巧啦,！

我們首先要解決的問題是，有沒有一種方法,，可以動(dòng)態(tài)獲取部門信息呢,，其實(shí)熟悉函數(shù)公式的童鞋知道，利用查找函數(shù)OFFSET函數(shù)就可以實(shí)現(xiàn),。

只要調(diào)整其對(duì)應(yīng)的參數(shù),，OFFSET函數(shù)可以動(dòng)態(tài)返回單元格區(qū)域,，從而得到我們想要的數(shù)據(jù)區(qū)域。

我們先來看看OFFSET函數(shù)的具體用法：

OFFSET函數(shù)

函數(shù)功能：返回對(duì)單元格或單元格區(qū)域中指定行數(shù)和列數(shù)的區(qū)域的引用,。 返回的引用可以是單個(gè)單元格或單元格區(qū)域,。 可以指定要返回的行數(shù)和列數(shù)。

使用格式：=OFFSET(reference,rows,cols,height,width)

通俗解釋：=OFFSET(參考單元格,，偏移的行數(shù),偏移的列數(shù),返回?cái)?shù)據(jù)區(qū)域的高度,返回?cái)?shù)據(jù)區(qū)域的寬度)

我們來一一列出各個(gè)參數(shù)：

第1個(gè)參數(shù)：我們以A1單元格為參考單元格

第2個(gè)參數(shù)：部門信息在A1單元格的下一行即A1單元格向下偏移1行,，這個(gè)參數(shù)固定為1

第3個(gè)參數(shù)：部門信息所在的區(qū)域不在A1單元格的右側(cè)或右下側(cè)，同樣這個(gè)參數(shù)固定為0

第4個(gè)參數(shù)：部門信息所在區(qū)域的高度（行數(shù)）,，由于會(huì)隨時(shí)增加或減少,，這個(gè)參數(shù)是動(dòng)態(tài)變化的，稍后我們來講怎么得到這個(gè)參數(shù)的值,。

第5個(gè)參數(shù)：部門信息所在區(qū)域的寬度（列數(shù)）,，部門信息不斷向下更新，這里只有1列的情況,，所以這個(gè)參數(shù)固定為1.

即以A1單元格為參考單元格,，向下偏移1行=1，向右不偏移=0,，部門的行數(shù)=動(dòng)態(tài)變化,，部門信息的列數(shù)=1，來返回整個(gè)部門的信息,。因?yàn)椴块T行數(shù)會(huì)發(fā)生變化,，除了部門行數(shù)未知，其他的參數(shù)基本都已確定好了,，我們列出公式如下：

=OFFSET($A$1,1,0,部門信息區(qū)域的行數(shù),1)

未知的部門行數(shù)怎么確定呢,？其實(shí)只需要一個(gè)簡(jiǎn)單的計(jì)數(shù)函數(shù)即可搞定，

COUNTA函數(shù)

函數(shù)功能：返回非空單元格的個(gè)數(shù)

使用格式：=COUNTA(value1,value2,...)

通俗解釋：=COUNTA(數(shù)據(jù)區(qū)域)

COUNTA($A:$A)這樣動(dòng)態(tài)返回部門信息的個(gè)數(shù)（函數(shù)）公式,，但是我們要去除表頭,，部門的個(gè)數(shù)即：COUNTA($A:$A)-1，嵌入到OFFSET中,，來看看公式是否正確：

我們?cè)贑1單元格中輸入如下公式：=OFFSET($A$1,1,0,COUNTA($A:$A)-1,1),，選擇公式后按F9即可顯示當(dāng)前所有的部門：={'生產(chǎn)部';'研發(fā)部';'銷售部';'系統(tǒng)部';'財(cái)務(wù)部'}，部門信息正確,。

然后我們?cè)贏7單元格輸入“生技部”后,，此時(shí)部門信息有更新，我們?cè)僭贑3單元格中輸入=OFFSET($A$1,1,0,COUNTA($A:$A)-1,1),，同樣的我們選擇公式后按F9即可顯示更新后所有的部門：={'生產(chǎn)部';'研發(fā)部';'銷售部';'系統(tǒng)部';'財(cái)務(wù)部';'生技部'}——結(jié)果顯示增加了剛剛添加的“生技部”,，達(dá)到了我們的要求，看看下圖操作↓↓↓：

GIF

OFFSET函數(shù)動(dòng)態(tài)返回?cái)?shù)據(jù)區(qū)域

通過OFFSET函數(shù),，部門信息無論怎么變化,，我們都能得到更新后的部門信息,，這時(shí)只需要在數(shù)據(jù)有效性中的來源重新修改成公式即可。

操作步驟如下：

• 選擇“數(shù)據(jù)”菜單

• 點(diǎn)擊“數(shù)據(jù)工具”中的“數(shù)據(jù)驗(yàn)證”

• 更改設(shè)置標(biāo)簽中的來源：=OFFSET(部門!$A$1,1,0,COUNTA(部門!$A:$A)-1,1)

• 選擇“確定”,，完成,！

這時(shí)我們來增加或刪除、更改部門信息時(shí),，看看下拉菜單是否會(huì)跟著變化啦,。趕緊試試吧！

注意：因我們是在“人員名單”表格中引用“部門”表格中的信息,，需要在公式中加入表格名稱,。

GIF

在數(shù)據(jù)有效性中設(shè)置公式

當(dāng)然除了用直接用公式作為數(shù)據(jù)來源外，我們還可以先定義一個(gè)名稱,，然后在數(shù)據(jù)有效性設(shè)置中引用名稱也可以達(dá)到同樣的效果,，并且這樣看起來更簡(jiǎn)潔，我們來看看如何操作↓↓↓：

• 選擇“公式”菜單

• 選擇“定義的名稱”中的定義名稱

• 在新建名稱窗口上名稱填入“部門”

• 范圍選擇“工作簿”
  

• 引用位置填入公式：=OFFSET(部門!$A$1,1,0,COUNTA(部門!$A:$A)-1,1)

• 定義名稱完成,，然后我們?cè)僭O(shè)置數(shù)據(jù)有效性,。

GIF

數(shù)據(jù)有消息引用名稱

要點(diǎn)總結(jié)：

• 利用OFFSET函數(shù)動(dòng)態(tài)返回部門區(qū)域

• 部門的個(gè)數(shù)（行數(shù)）可以通過計(jì)數(shù)函數(shù)COUNTA確定

• 數(shù)據(jù)有效性中的來源可以引用多種形式的數(shù)據(jù)，如公式或定義名稱

今天的分享就到這里啦,，熟看百遍，不如操作一遍,，趕緊打開電腦試試吧,！

Excel倫特吧，只為提高效率,！