大家好，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(部門!$A$1,1,0,COUNTA(部門!$A:$A)-1,1)選擇「確定」，完成！這時我們來增加或刪除、更改部門信息時，看看下拉菜單是否會跟著變化啦。趕緊試試吧！
注意：因我們是在「人員名單」表格中引用「部門」表格中的信息，需要在公式中加入表格名稱。
當然除了用直接用公式作為數據來源外，我們還可以先定義一個名稱，然後在數據有效性設置中引用名稱也可以達到同樣的效果，並且這樣看起來更簡潔，我們來看看如何操作↓↓↓：
選擇「公式」菜單選擇「定義的名稱」中的定義名稱在新建名稱窗口上名稱填入「部門」範圍選擇「工作簿」引用位置填入公式：=OFFSET(部門!$A$1,1,0,COUNTA(部門!$A:$A)-1,1)定義名稱完成，然後我們再設置數據有效性。
要點總結：
利用OFFSET函數動態返回部門區域部門的個數（行數）可以通過計數函數COUNTA確定數據有效性中的來源可以引用多種形式的數據，如公式或定義名稱今天的分享就到這裡啦，熟看百遍，不如操作一遍，趕緊打開電腦試試吧！
如果您在工作中有什麼更好的方法，歡迎分享留言給我們，同時有任何問題，我們都會竭盡全力幫到您~