有網友問到一個非常實用的問題：一般在 Excel 的工作表中輸入資料時，可以利用「資料驗證」中的清單來產生一個選單，方便使用者以選取選項的方式來輸入資料。但是，如果選項內容是會變動的，或是內容會出現一些空白選項(參考下圖)，將會造成一些困擾，該如何克服這些問題？
我們要的是一個會自動增加內容的選單，而且沒有多餘的空白項：
做法如下：
假設選取用來作為選項的內容為儲存格D2:D22。
先新增一個名稱：MENU，其內容參照為：=OFFSET(D2,,,COUNTA(D2:D22))
正確的寫法如下：(注意工作表名稱及絶對參照($))
內容參照為：=OFFSET(工作表1!$C$2,,,COUNTA(工作表1!$C$2:$C$22))
其中使用 COUNTA 函數來取得儲存格範圍中有多少個含有資料的儲存格數，再透過 OFFSET 函數取得一個動態儲存格範圍(有內容的儲存格範圍)。
接著，選取儲存格A2:A22，設定資料驗證：
儲存格內允許：「清單」；來源：=MENU。
.
如此，只要在選項的儲存格範圍中新增一筆資料時，選單中也會同步增加一個選項，而且不會出現一些空白項。
【延伸學習】
文章標籤
全站熱搜
請問欄位加總該如何用函數 假設B,E,F,G四格有打勾 列欄位會出現4 麻煩請大大解惑
假設以「V」當為打勾，公式：=COUNTIF(儲存格範圍,"V")
請問如何改變"下拉式清單"中"清單"的字體大小呢? 一直找不到方法，麻煩達人解答
很抱歉！我也沒有可行的方法。
您好, 非常感謝您的教學 我想詢問如果在選項中間若有空格(例如: 第一個選項AAA, 第二個留白, 第三個CCC) 會造成COUNTA公式出錯 不知道是否有解決的方法 謝謝您
您好, 很感謝您無私的分享, 收穫良多. 可以請教一個問題. 如果已經選取清單資料的欄位. 如果我變更了清單的內容. 請問要如何才能讓已選定的資料自動更改. 例如 A1的VALUE = EXP 123. 但之後我改了清單內容將EXP 123改成EXP456. 請問要何如才能讓A1的值自動變更成EXP 456
如果以本文中所述方式是以下拉式清單手動選取，則無法自動變更。
我想問一個問題，下拉式選單可否設計選過的選項，不再出現1
請問老師，如果C欄是用公式決定他的值，即使顯示是空白，但還是會出現在選單中，是否有其他公式可改善?
請問如果要設計兩層式的下拉選單，第一、二層選單都要能有擴充功能(忽略空白項)，第二層的該如何設計?
同 #7 TACO 相同問題！第二層擴充功能就沒辨法使用！
您好，謝謝您的教學，我嚐試成功了! 有另一個需求，想請教您 因為有做跨報表下拉， 若是使用 =INDIRECT 函數 下把的內容就沒辦法跑出來了 請問要如何調整呢 ?
請問動態清單的資料內容以表格建立後, 手動選取清單範圍儲存格,是否可取代此方式, 會出現其他問題嗎,謝謝