2017年6月2日 星期五

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

最近有些網友不約而同的問到在 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)。

沒有留言:

張貼留言

檢視其他文章

好康東東