學不完．教不停．用不盡

網友問到：如下圖這樣的資料格式，如何處理動態下拉式清單的問題。

下圖中，在儲存格E13的下拉式清單中選取一個班級之後，在儲存格G13會自動變換為該班的人員姓名。這是一個動態的下拉式清單設計。

下圖中各班的內容(姓名)會不斷的變換，數量不會固定。不希望下拉式清單中顯示空白的內容。

【公式設計與解析】

1. 選取儲存格B1:K1，定義名稱：班級。

2. 計算儲存格E13所指班級對應的人數

公式：=COUNTA(OFFSET($A$1,1,MATCH($D$13,班級,0),7))

(1) MATCH($D$13,班級,0)

找出儲存格D13內容在「班級」儲存格陣列中的位置，會傳回一個數字。

(2) OFFSET($A$1,1,MATCH($D$13,班級,0),7)

依第(1)式的傳回值，套入 OFFSET 函數中可得儲存格D13所設定班級的儲存格範圍。

此公式中的參數「7」，只是因為圖中各班人數的最大值是「7」，所以實際的儲存格範圍裡會有些儲存格是空白。

(3) COUNTA(OFFSET($A$1,1,MATCH($D$13,班級,0),7))

利用 COUNTA 函數依第(2)式傳回的儲存格範圍，計算該班的人數。

3. 以下公式可以取得指定班級的儲存格範圍。

公式：=OFFSET($A$1,1,MATCH($D$13,班級,0),COUNTA(OFFSET($A$1,1,
MATCH($D$13,班級,0),7)),1)

(1) COUNTA(OFFSET($A$1,1,MATCH($D$13,班級,0),7))

計算儲存格E13所指班級對應的人數

(2) OFFSET($A$1,1,MATCH($D$13,班級,0),第(1)式,1)

根據第(1)式傳回的人數，再以 OFFSET 函數取得各班的儲存格範圍。

4. 將第3式做為資料驗證準則的資料來源。(將公式複製到資料驗證的來源中)

而儲存格E13也是要設定資料驗證，則準為：清單，來源：班級。(先前已定義的名稱)

如此，便可以動態的改變班級即可得各班所屬的人員姓名。