Excel小技巧: 製作可參照其他工作表的下拉式選單

最近重頭學習Excel的一些技巧, 有些非常實用, 做一下筆記.
有些地方我會放一些專門用語, 由於手頭上沒有中文Office, 只能用笨拙的翻譯.
為了避免用語分歧, 我會在下面適當的插入英文註解.


以前在製作下拉式選單的時候, 只會笨笨的選擇同一張工作表裡面的列表值
這有一些缺點, 包括了

1. 列表內的選項數量變更時, 必須自己去重新設定下拉式選單的範圍, 否則下拉式選單會跑出許多空白.
2. 選項列表以及值列表必須在同一張工作表中. 這讓整個版面很難控制.

以下介紹兩個小技巧, 他們分別解決上面兩個問題.

第一個, 適當使用定名範圍(Named Range)
在選單列 插入>名稱>定義 對話框中, 我們可以自行定義一些關鍵字來供自己使用.
在這裡, 適當的使用OFFSET與COUNTA, 我們可以設定一個可動式的定名範圍

舉例來說, 在工作表Sheet1中的列A的A1放了標題, A2~A10放了列表內容.
我們可以在定義範圍(Defined Range)的定義欄中放入下面的定義式來定義一個可動範圍

=OFFSET(Sheet1!$A$1,0,1,COUNTA(Sheet1!$A:$A)-1,1)

這樣的話, 就算是我們在A11上加入了一個新選擇項, 它也會自動含入定義範圍中.

第二個, 在下拉式選單(儲存格輸入規則, Input Role)設定時,
選擇「清單」, 並在範圍的部分直接指定上面的定名範圍(Named Range),
這時候, 定名範圍的內容不一定要是在該工作表中.
也就是說, 你可以在Sheet2中呼叫Sheet1中的定名範圍

換句話說, 輸入用的工作表與選項定義的工作表可以分割開來.
終極的選項, 他們甚至可以不在同一個工作簿中!!

ps. 以後再來補說明圖...