[Excel] 動態參照範圍~~

[leonchou]

大家好~~ 動動腦 Part... 算了, 反正動動腦又來啦~

這次的問題是: 如何以一個變動的值來控制下拉清單的內容(參照範圍)??
舉例：下拉清單１儲存格連結是Ａ７、參照到Ａ１：Ａ５，是五個組長的名字。
　　　Ｂ∼Ｆ五欄分別是這五個組長各自的組員名單, 人數不定, 均從第一列開始。
需求：現欲新增一個下拉清單２，當下拉清單１選擇某個組長時，下拉清單２
　　　的內容就會自動列出該組長的組員以供選取。

該怎麼做呢? 依我想，至少就有兩三個答案了喔~~
大家別只是談如何安裝、取得、設定某某軟體呀...
多點學術討論吧??

[ysb]

Chou 大師
請問一下


下拉清單２ 是否可以抓取由 vlookup 函數所得的結果
我使用的方法只能夠顯示第一個組員、其他4個無法顯示在 下拉清單２ 中

[leonchou]

謝謝你的回應~ 這麼稱呼不敢當.. ^^|||

咦~ 這我倒好奇了.. VLOOKUP 至少要有對照表(清單)才能用阿?
不過 VLOOKUP 只能取一個值, 確實是不行的~

提示一下: OFFSET 和 INDIRECT 函數..

有興趣的話, 查一下 Excel 的函數說明吧~~
Come on everybody !!

[aztec]

小弟的作法，實在不太敢拿出來，每次解題都有拼湊的味道


　　Ａ　　Ｂ　　Ｃ　　Ｄ　　Ｅ　　Ｆ
----------------------------------
1　陳一　陳一　林二　張三　李四　王五
2　林二　
3　張三　
4　李四　
5　王五　

假設資料如上,各組人員人數不等

第一步，因為怕周大師以後不願意親臨指導，所以
一定要先用周大師最最最慣用的技倆之一

各位猜到了嗎？哈，就是插入->名稱->建立，把陳
一到王五等五欄設NAME，記得要選標題列, 欄要拉
多長？看你自己高興，要拉到底也沒關係，只要這
些資料的下方沒資料就好。

第二步

設一個名稱KKK=OFFSET(Sheet1!$A$1,1,Sheet1!$A$7,COUNTA(CHOOSE(Sheet1!$A$7,陳一,林二,張三,李四,王五)))

要特別說明的是COUNTA(CHOOSE(Sheet1!$A$7,陳一,林二,張三,李四,王五))
最初這一段是要用counta(indirect("A"&A7)), 但判斷失效，只好偷吃步改成
上面那種方式，要請周大師指點一下囉。

第三步，把第二個下拉清單的 「範圍」設成 KKK 就搞定了。

[leonchou]

呵呵~ aztec兄都解到這地步了.. 我還是公布了吧~~

這題的重點是: 新增一個參照到公式的名稱, 以及公式的內容.

名稱不止可以參照到儲存格位置, 也能參照到公式 -- 這點知道的人並不多.
有關名稱和設定下拉方塊, aztec兄已說明了~
以下是公式本身的寫法 --

[公式一]　=INDIRECT(INDEX($A$1:$A$5,$A$7))

[公式二]　=OFFSET($A$2,,$A$7,ROWS(INDIRECT(INDEX($A$1:$A$5,$A$7))))

上述兩個公式任選一個都可以. 但第二個公式有點多此一舉,
而且組員清單的排列還必需和依照組長順序才行.
因為已先建立各組組員的範圍名稱了. 用 INDEX 取出選擇的
組長名字, 再用 INDIRECT 轉換為名稱參照位置. 由於 A1:A5
已有組長的清單, 故直接使用 INDEX 即可, 不需使用 CHOOSE.

所以第一個公式比較理想. 但題目本來就不止一種解法,
列舉出來大家參考, 純學術討論~~