請教2重下拉式選單篩選的問題

諾姆

請教一下各位大大2重下拉式選單篩選的問題
Sheet1為資料來源如圖

下載 (20.72 KB)

2012-7-12 14:17

Sheet2為表單如圖

下載 (7.31 KB)

2012-7-12 14:16

希望得到結果是
1.表單(Sheet2)內種類欄位可下拉出現膠帶，捆包袋，夾鏈袋的選項，資料不重複
2.表單(Sheet2)內種類欄位選好後，料號欄位下拉出現相對應的值,如膠帶就出現A1，A2，捆包袋就出現B1到B6，夾鏈袋就出現C1到C3
小弟是有去爬一下文了，不過有一些文章的附件小弟沒權限下載，可能有大大問過了，如有重複麻煩指點一下謝謝。

boyao

要用"定義"+OFFSET函數....
或者...去下面ㄉ網頁看看
http://www.dotblogs.com.tw/lastsecret/archive/2010/07/13/16540.aspx

Hsieh

回復 1# 諾姆
首先須將清單整理成下圖

下載 (18.52 KB)

2012-7-12 23:18

先建立以下2個定義名稱
種類=OFFSET(Sheet1!$A$2,,,COUNTA(Sheet1!$A:$A)-1,)
料號=OFFSET(Sheet1!$B$2,,,COUNTA(Sheet1!$B:$B)-1,)

E2陣列公式=IF(ROW(A1)>SUM(1/COUNTIF(種類,種類)),0,INDIRECT("A"&MIN(IF(ISERROR(MATCH(種類,$E$1:E1,0)),ROW(種類),""))))
向下複製
F2陣列公式=IF(COLUMN(A$1)>COUNTIF(種類,$E2),0,INDIRECT("B"&SMALL(IF(種類=$E2,ROW(種類),""),COLUMN(A$1))))
向右向下複製
再建立下列3個定義名稱
List1=OFFSET(Sheet1!$E$2,,,LOOKUP("龘",Sheet1!$E$2:$E$200,ROW(Sheet1!$E$1:$E$199)),)
選取Sheet2!B2狀態下定義以下2個名稱
List=OFFSET(Sheet1!$E$2,MATCH(Sheet2!B$1,List1,0)-1,1,,200)
List2=OFFSET(Sheet1!$E$2,MATCH(Sheet2!B$1,List1,0)-1,1,,LOOKUP("龘",List,COLUMN(List)-5))
如此
Sheet2!B1驗證清單公式=List1
Sheet2!B2驗證清單公式=List2

諾姆

回復 3# Hsieh


    感謝大大指教，稍微試了一下因該可以用，我在試試套用到我要用的表格上，如有疑問希望大大能在指教一下。

roy1221

請問我嚐試使用後當
種類>80種；料號>700個    →運算數量很多時
在運算時會用很久的時間，有辨法加快運算速度嗎？

諾姆

回復 3# Hsieh
Sheet1為資料來源料號如果只是輸入數字的話，Sheet2的夏拉就會變得無法使用請問大大有辦法解決嗎?

Hsieh

回復 6# 諾姆

List2參照到公式
=OFFSET(Sheet1!$E$2,MATCH(Sheet2!B$1,List1,0)-1,1,,LOOKUP(2,1/(List<>0),COLUMN(List)-5))

諾姆

感謝大大喔目前策是正常了，因該沒有啥問題了，感謝大大。

Andy2483

謝謝論壇,謝謝各位前輩
後學以1#範例練習陣列與字典,學習情境結果方案如下,請各位前輩指教
執行前:

下載 (35.44 KB)

2024-2-23 08:56

執行結果:

下載 (42.82 KB)

2024-2-23 08:57

Option Explicit
Sub TEST()
Dim Brr, Z, i&, R&, N&, T1$, T2$
'↑宣告變數:&是長整數,$是字串變數,沒有指定的是通用型變數
Set Z = CreateObject("Scripting.Dictionary")
'↑令Z這通用型變數是 字典
Brr = Range([B1], [A65536].End(xlUp))
'↑令Brr這通用型變數是二維陣列,以[B1]到 A欄最後有內容儲存格,這兩儲存格涵蓋方正範圍儲存格值帶入陣列中
For i = 1 To UBound(Brr)
'↑設順迴圈!令i變數從1 到Brr陣列縱向最大索引列號
   T1 = Trim(Brr(i, 1)): R = Z(T1): T2 = Trim(Brr(i, 2))
   '↑令T1這字串變數是 i迴圈列1欄Brr陣列值去除頭尾空白字元後的新字串
   '令R這長整數變數是以 T1變數查Z字典回傳item值,令T2這字串變數是 i迴圈列2欄Brr陣列值去除頭尾空白字元後的新字串
   If R = 0 Then N = N + 1: R = N: Brr(R, 1) = T1: Brr(R, 2) = T2: Z(T1) = R: GoTo i01
   '↑如果R變數是 0!就令N變數累加1,令R變數同N變數,令R列1欄Brr陣列值是 T1變數,令R列2欄Brr陣列值是 T2變數
   '令T1變數為key的item值換以 R變數放回Z字典中,令程序跳到標示 i01位置繼續執行
   If InStr("/" & Brr(R, 2) & "/", "/" & T2 & "/") = 0 Then Brr(R, 2) = Brr(R, 2) & "/" & T2
   '↑如果R列2欄Brr陣列值在前後連接"/"符號所組成的新字串裡 沒有包含T2變數在前後連接"/"符號所組成的新字串!,
   '就令T2變數以"/"連接在R列2欄Brr陣列值裡
i01: Next
[E1].Resize(N, 2) = Brr
'↑令[E1]儲存格向下擴展N變數列,向右擴展2欄的儲存格範圍,以Brr陣列值帶入
End Sub

Andy2483

謝謝論壇,謝謝各位前輩
後學以1#範例練習陣列與字典,學習另一情境需求方案如下,請各位前輩指教
執行前:

下載 (64.21 KB)

2024-2-23 14:09

執行結果:

下載 (81.25 KB)

2024-2-23 14:10

Option Explicit
Sub TEST_2()
Dim Brr, Z, i&, R&, N&, T1$, T2$, C%, U%, M%
'↑宣告變數:&是長整數,%是短整數,$是字串變數,沒有指定的是通用型變數
Set Z = CreateObject("Scripting.Dictionary")
'↑令Z這通用型變數是 字典
Brr = Range([B1], [A65536].End(xlUp))
'↑令Brr這通用型變數是二維陣列,以[B1]到 A欄最後有內容儲存格,這兩儲存格涵蓋方正範圍儲存格值帶入陣列中
ReDim Crr(UBound(Brr), UBound(Brr))
For i = 2 To UBound(Brr)
'↑設順迴圈!令i變數從1 到Brr陣列縱向最大索引列號
   T1 = Trim(Brr(i, 1)): R = Z(T1): T2 = Trim(Brr(i, 2)): C = Z(T1 & T2): U = Z(T1 & "/c")
   '↑令T1這字串變數是 i迴圈列1欄Brr陣列值去除頭尾空白字元後的新字串
   '令R這長整數變數是以 T1變數查Z字典回傳item值(如果R是0,這程序已經建立key=T1,item=0 在Z字典中)
   '令T2這字串變數是 i迴圈列2欄Brr陣列值去除頭尾空白字元後的新字串
   '令C這短整數變數是以 T1變數連接T2變數組成的新字串查Z字典回傳item值(如果C是0,這程序已經建立key=T1 & T2,item=0 在Z字典中)
   '令U這短整數變數是以 T1變數連接"/c"字串組成的新字串查Z字典回傳item值(如果U是0,這程序已經建立key=T1 & "/c",item=0 在Z字典中)
   If R = 0 Then N = N + 1: R = N: Crr(R, 0) = T1: Z(T1) = N
   '↑如果R變數是 0!就令N變數累加1,令R變數同N變數,令R變數列0欄Crr陣列值是 T1變數,令T1變數為key的item值換以 N變數放回Z字典中
   If C = 0 Then U = U + 1: Crr(R, U) = T2: Z(T1 & "/c") = U: Z(T1 & T2) = 1
   '↑如果C變數是 0!就令U變數累加1,令R變數列U變數欄Crr陣列值是 T2變數,
   '令T1變數連接"/c"字串組成的新字串為key的item值換以 U變數放回Z字典中
   '令T1變數連接T2變數組成的新字串為key的item值換以 數字1放回Z字典中
   If M < U Then M = U: Crr(0, M) = Brr(1, 2) & "_" & M
   '如果M這短整數小於M變數!就令M變數同U變數,令0列M變數欄Crr陣列值是 第1列2欄Brr陣列值,連接下底線,再連接M變數所組成的新字串
i01: Next
Crr(0, 0) = Brr(1, 1) & "\" & Brr(1, 2): [I1].Resize(N + 1, M + 1) = Crr
'↑令[I1]儲存格向下擴展N變數列,向右擴展2欄的儲存格範圍,以Brr陣列值帶入
End Sub