最近有些網友不約而同的問到在 Excel 中使用下拉式清單的問題。如下圖,在一個進貨商的資料清單A1~A7中,每一個包含的資料清單數量並不相同。

Excel-產生動態的下拉式清單內容(資料驗證,INDIRECT,OFFSET,COUNTA)

 

【方法一:清單內容不變動】

如果清單項目的內容不會變動,則可以使用名稱定義+INDIRECT函數來處理。

1. 選取儲存格A2:H8。

Excel-產生動態的下拉式清單內容(資料驗證,INDIRECT,OFFSET,COUNTA)

2. 在[特殊目標]對話框中選取「常數」,結果只會選取有資料的儲存格。

Excel-產生動態的下拉式清單內容(資料驗證,INDIRECT,OFFSET,COUNTA)

3. 在儲存格選取狀態下,按 Ctrl+Shift+F3 鍵,勾選「最左欄」項目。

如此可以進貨商的項目名稱定義為名稱。

Excel-產生動態的下拉式清單內容(資料驗證,INDIRECT,OFFSET,COUNTA)

名稱定義結果如下:(因為名稱A1和儲存格的位址相同,所以會自動加上「_」。)

Excel-產生動態的下拉式清單內容(資料驗證,INDIRECT,OFFSET,COUNTA)

4. 選取儲存格B12,進入[資料驗證]對話框。在[資料驗證準則]區中設定:

儲存格內允許:清單;來源:=INDIRECT(A12&"_")。

Excel-產生動態的下拉式清單內容(資料驗證,INDIRECT,OFFSET,COUNTA)

 

【方法二:清單內容會變動】

如果清單項目的內容會變動,則可以在資料驗證中使用 OFFSET 處理。

選取儲存格A1:A8,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:進貨商。

Excel-產生動態的下拉式清單內容(資料驗證,INDIRECT,OFFSET,COUNTA)

接著,選取儲存格B12,進入[資料驗證]對話框。在[資料驗證準則]區中設定:

儲存格內允許:清單;來源:=OFFSET($A$1,MATCH(A12,進貨商,0),1,1,
COUNTA(OFFSET($A$1,MATCH(A12,進貨商,0),1,1,7)))

其中:

(1) MATCH(A12,進貨商,0)

找出儲存格A12內容在進貨商中的位置。(傳回一個數字)

(2) OFFSET($A$1,MATCH(A12,進貨商,0),1,1,7)

根據第(1)式的傳回值代入 OFFSET 函數,找出儲存格A12所對應的資料區間(本例為:儲存格B6:H6)

(3) COUNTA(OFFSET($A$1,MATCH(A12,進貨商,0),1,1,7))

計算在儲存格A12所對應的資料區間有幾個有文字的儲存格。(本例傳回5)

(4) OFFSET($A$1,第(1)式,1,1,第(3)式)

找出儲存格A12所對應的資料區間(本例為:儲存格B6:H6)中有內容的儲存格(本例為:儲存格B6:F6)。

arrow
arrow
    文章標籤
    Excel INDIRECT OFFSET COUNTA
    全站熱搜
    創作者介紹
    創作者 vincent 的頭像
    vincent

    學不完.教不停.用不盡

    vincent 發表在 痞客邦 留言(2) 人氣()