Excel教程——創建動態下拉菜單

Excel中單元格的下拉菜單，基本都是通過設置單元格的數據有效性，將其序列設置為某一區域的數據源來實現的。

通過設置單元格的數據有效性，為其創建下拉菜單，比較簡單，操作過程如下：

示例1：數據源設置為單元格區域（非整列）

示例2：數據源設置為整列

通過上面兩種方式都可以創建下拉菜單，但各有弊端：

第一種情況，A2單元格的下拉菜單數據源是固定的，下拉選項不能自動的增加或刪減。

第二種情況，B2單元格的下拉菜單雖然會自動增加或刪減，但下拉菜單選項會產生一些空白行，另外標題行也被加入到了下拉選項，顯然不符合我們的要求。

今天我們來學習OFFSET函數，通過使用該函數，可以為單元格的下拉菜單設置活動數據源，從而創建動態下拉菜單。

1.函數語法

OFFSET(reference, rows, cols, [height], [width])

OFFSET 函數語法具有下列參數：

• 引用 必需。 要以其為偏移量的底數的引用。 引用必須是對單元格或相鄰的單元格區域的引用；否則OFFSET 返回 錯誤值 #VALUE!。

• Rows 必需。 需要左上角單元格引用的向上或向下行數。 使用 5 作為 rows 參數，可指定引用中的左上角單元格為引用下方的 5 行。 Rows 可為正數（這意味著在起始引用的下方）或負數（這意味著在起始引用的上方）。

• Cols 必需。 需要結果的左上角單元格引用的從左到右的列數。 使用 5 作為 cols 參數，可指定引用中的左上角單元格為引用右方的 5 列。 Cols 可為正數（這意味著在起始引用的右側）或負數（這意味著在起始引用的左側）。

• 高度 可選。 需要返回的引用的行高。 Height 必須為正數。

• 寬度 可選。 需要返回的引用的列寬。 Width 必須為正數。

2.函數舉例

3.建立動態菜單

首先使用名稱管理區創建一名稱“品種”，并設置其公式為：=OFFSET(品種!$A$1,1,,COUNTA(品種!$A:$A)-1)。

函數COUNTA(品種!$A:$A)，用于返回品種工作表中A列非空的單元格個數。因為第一行為標題行，所以需要減去1，方能表示該列的品種數量。

然后將需要設置菜單的單元格設置其數據有效性，選擇序列，并輸入公式：=品種