Excel-雙層下拉式選單

2014-09-12 6:26 am
各位奇摩知識家強者們好,

下列為本excel的下載網址,
http://www.FunP.Net/757782

下面為客戶的報價資料:

客戶名代碼報價日單價產品規格
AA0012014/1/310紅襯衫S
AA0012014/3/2810黃襯衫S
AA0012014/4/2910白襯衫S
AA0012014/6/2110黑襯衫S
BA0022013/12/2011白襯衫M
BA0022014/2/2811黑襯衫M
CA0032014/2/1010.5紅襯衫M
CA0032014/5/1010.5黃襯衫M
CA0032014/8/1010.5白襯衫M
DA0042013/6/810.5白襯衫M
DA0042014/6/810.5黑襯衫M
EA0052014/1/225洋裝S
EA0052014/2/25襯裙S
EA0052014/3/210紅襯衫S
EA0052014/4/210黃襯衫S
EA0052014/5/210白襯衫S
FA0062014/2/2412紅襯衫L
FA0062014/4/112黃襯衫L
FA0062014/5/2912白襯衫L
FA0062014/7/312黑襯衫L
FA0062014/9/230洋裝L
GA0072014/2/910白襯衫S
GA0072014/5/1010黑襯衫S
HA0082014/6/627洋裝S

我想根據這些報價資料,在訂單上設計出下拉式選單
第一層先拉出客戶,第二層是拉出該客戶的相關產品
想請教各位前輩們,我可以怎麼設定呢?

訂單日期客戶產品數量
2014/9/1A紅襯衫10
2014/9/2B白襯衫8
2014/9/3C紅襯衫1
2014/9/4A紅襯衫20
2014/9/5E洋裝5
2014/9/6H洋裝2

先在此感謝回答的前輩們
更新1:

先感謝Lopez及cjw兩位大大提供範本,我來研究一下。 To 茶千千: 剛好不需要單價耶,要弄上去也是可以啦,我想說研究好這方法後再來調整^^"

更新2:

Lopez大大您好, 依照您的範本我終於成功設定出我要的模式了,非常感謝您。 但我還是有個問題想請教您。 =OFFSET($K$1,0,0,1,$AE$1)為什麼要涵蓋$AE$1呢? 以及=OFFSET($J$1,1,MATCH(B2,$K$1:$R$1,0),HLOOKUP(B2,$K$1:$R$22,22,TRUE),1)需涵蓋至$R$22被統計的數字呢? 我試過不蓋過去就會顯示錯誤。我不太了解其原理。不知道您可否稍微講解一下嗎?

更新3:

另外,我試著客人數量超過60筆的時候 例如,我選A客人。下拉式選單只能跑出第一筆產品名稱,例如紅襯衫。接著下面的黃襯衫、白襯衫、黑襯衫沒有跑出顯示。不知道有大大可以推測一下可能的原因嗎? 先謝謝你們了^^

更新4:

謝謝Lopez大大熱心教學。 http://www.funp.net/577789 此連結為我設定的問題。 經過比對,只有BF欄的"協成行(偉谷)"這中文字無法被excel對應到 改成"協成行"三個字也是一樣,但其他字就沒問題,如"偉谷"或是其他數字。 無法對應到"協成行(偉谷)"這串字的結果: 下拉式產品選項無任何產品跑出來。 對應到"協成行(偉谷)"後: 下拉式產品也只能跑出A產品選項。B-H選項跑不出來。 時好時壞,不知道是不是excel對於"協成行"三個字有缺陷?

更新5:

這個也許已經超出下拉式選單的範圍。 Lopez大大提供的解答已經能讓我開始運作了,非常感謝。

回答 (3)

2014-09-12 1:51 pm
✔ 最佳答案
Step1. 訂單,產品清單,合併為一個工作表.
(因為清單來源必須同一個工作表)
Step2. 資料, 驗證, 儲存格內允許"清單",
"來源"就必須自己設計了,
基本上主要運用檢視與參照類別的函數.

完成的檔案請參考:
http://www.FunP.Net/519196

2014-09-24 11:57:16 補充:
Q: OFFSET($K$1,0,0,1,$AE$1)為什麼要涵蓋$AE$1呢?
A: OFFSET(起點,下移列數,右移欄數,範圍高度,範圍寬度)
起點:K1儲存格,
$K$1,0,0 表示不偏移, 還是在 K1,
範圍高度是1,
$AE$1是統計客戶總數,A~H,所以範圍寬度是8,
所以這個OFFSET函數結果是K1開始,高度1,寬度8,
也就是K1:R1這個範圍,當作B欄的選單

2014-09-24 12:11:34 補充:
關於OFFSET($J$1,1,MATCH(B2,$K$1:$R$1,0),HLOOKUP(B2,$K$1:$R$22,22,TRUE),1)
其中 MATCH(B2,$K$1:$R$1,0) 是比對出客戶的順序,
例如在B2儲存格選"A"客戶,因為"A"是$K$1:$R$1這個範圍的第1個,
所以MATCH函數計算結果是1,

2014-09-24 12:15:38 補充:
HLOOKUP(B2,$K$1:$R$22,22,TRUE),1) 是查詢該客戶商品總數,
例如在B2儲存格選"A"客戶,在$K$1:$R$22這個範圍找,
是在K欄,所以輸出第22列,也就是K22 ,其值為4
所以HLOOKUP函數計算結果是4

2014-09-24 12:20:51 補充:
綜合上述的結論,
OFFSET($J$1,1,MATCH(B2,$K$1:$R$1,0),HLOOKUP(B2,$K$1:$R$22,22,TRUE),1)
若B2儲存格選"A"客戶,計算結果是:
OFFSET($J$1,1,1,4,1)
起點J1,下移1列,右移1欄,所以是K2,
範圍的高度4,寬度1,
所以是K2~K5,恰是希望選擇的範圍

2014-09-24 12:22:33 補充:
Q: 試過不蓋過去就會顯示錯誤...
你是指蓋過哪裡??

2014-09-24 12:27:39 補充:
關於"客人數量超過60筆的時候..."
我設計的範例只有K~AD欄,也就是最多只能處理20個客戶,
若是超過20個,有些公式是需要修正的

2014-09-24 12:29:47 補充:
若是你了解以上解說,也許可先自行修改,
若是還是不行,你再告訴我客戶最多大概到多少,
我再幫你修改
2014-09-12 5:49 pm
<<參考檔>>下載地址
http://www.FunP.Net/146729
2014-09-12 11:44 am
訂單不用單價嗎@@?
那怎麼看得出來來源是哪張報價單
還是不需要


收錄日期: 2021-04-27 21:21:20
原文連結 [永久失效]:
https://hk.answers.yahoo.com/question/index?qid=20140911000010KK07026

檢視 Wayback Machine 備份