贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

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

文章標籤
創作者介紹

學不完.教不停.用不盡

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


留言列表 (1)

發表留言
  • CC
  • 老師,想問一下,如果像這種情況,不同的進貨商有不同/相同的項目,不同的價錢,可以通過下拉式清單來篩選哪家最便宜/貴、看看哪家提供了哪幾種項目、這項目有哪幾個進貨商提供...嗎? 謝謝
找更多相關文章與討論

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼