[Excel] 動態參照範圍~~



贊助商連結


leonchou
2001-07-14, 01:17 AM
大家好~~ 動動腦 Part... 算了, 反正動動腦又來啦~ :D

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

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

贊助商連結


ysb
2001-07-20, 12:48 AM
Chou 大師
請問一下


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

leonchou
2001-07-20, 02:06 AM
謝謝你的回應~ 這麼稱呼不敢當.. ^^|||

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

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

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

aztec
2001-07-20, 03:21 PM
小弟的作法,實在不太敢拿出來,每次解題都有拼湊的味道


  A  B  C  D  E  F
----------------------------------
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
2001-07-21, 04:50 PM
呵呵~ 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.

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