[發問] 雙條件搜尋與總途程公式

adam2010

系統WIP資料只有途程編號，想要知道目前WIP已經生產到第幾道途程&總共有幾個途程
目前是用下圖的公式，但因為資料量愈來愈大(料號有上千筆*途程約35~65不等)導致有時開啟檔案會很緩慢
想請教各位高手有無其他較不吃資源的公式或者其他方法(或者是要使用巨集跑完變成文字?)
1.途程資料

下載 (48.9 KB)

2021-6-13 02:08

2.WIP(D欄是目前的公式)

下載 (69.89 KB)

2021-6-13 02:08

途程問題20210613.rar (11.2 KB)

下載次數: 7

2021-6-13 02:08

准提部林

料號有上千筆*途程約35~65不等
__資料大約3~6萬行

可用定義名稱, 但不見得快多少, offset本身就會干擾工作表操作~~
用vba吧!

准提部林

兩種方式:
Xl0000558.rar (14.44 KB)

下載次數: 7

2021-6-13 10:23

adam2010

回復 2# 准提部林

版主真是太強大了，不過巨集內容看不太懂我在是是看套到正式的檔案看看，謝謝

hcm19522

D2=LOOKUP(,0/((A2=途程資料!A:A)*(B2=途程資料!C:C)),COUNTIF(OFFSET(途程資料!A$2,,,ROW($1:$1998)),途程資料!A$2:A$1999))-1&"/"&COUNTIF(途程資料!A:A,A2)

https://blog.xuite.net/hcm19522/twblog/589831005

Andy2483

回復 3# 准提部林


    謝謝論壇,謝謝前輩
後學藉此帖學習前輩VBA方案,方案學習心得如下,請前輩再指導

執行前:

下載 (63.9 KB)

2023-5-25 09:48

執行結果:

下載 (68.46 KB)

2023-5-25 09:49

Sub TEST_A1()
Dim Arr, xD, T1$, T2$, i&
'↑宣告變數:(Arr,xD)是通用型變數,(T1,T2)是字串變數,i是長整數變數
Set xD = CreateObject("Scripting.Dictionary")
'↑令xD這通用型變數是 字典
Arr = Range(Sheet1.[c1], Sheet1.Cells(Rows.Count, 1).End(xlUp))
'↑令Arr這通用型變數是 二維陣列,以表1的A~C欄儲存格值帶入陣列中
For i = 2 To UBound(Arr)
'↑設順迴圈
    T1 = Arr(i, 1): T2 = Arr(i, 3)
    '↑令T1這字串變數是 i迴圈列第1欄Arr陣列值
    xD(T1) = xD(T1) + 1
    '↑令以T1變數當key,item累加1,納入xD字典裡
    xD(T1 & "/" & T2) = Arr(i, 2)
    '↑令T1變數連接"/",再連接T2變數 所組成的新字串當key,
    'item是 i迴圈列第2欄Arr陣列值
Next i
Arr = Range(Sheet2.[c1], Sheet2.Cells(Rows.Count, 1).End(xlUp))
'↑令Arr陣列倒掉舊陣列值,換裝表2的A~C欄儲存格值
For i = 2 To UBound(Arr)
'↑設順迴圈
    T1 = xD(Arr(i, 1) & "")
    '↑令T1變數是 (以i迴圈列Arr陣列值)查 xD字典回傳item值
    'Arr(i, 1) & "":陣列值加空字元,定義讓它是字串 查字典
    T2 = xD(Arr(i, 1) & "/" & Arr(i, 2))
    '↑令T2變數是 i迴圈列第1欄Arr陣列值連接"/",再連接i迴圈列第2欄Arr陣列值,
    '所組成的新字串
    Arr(i - 1, 1) = T2 & IIf(T2 = "", "", "/") & T1
    '↑令T2變數連接"/"再連接T1變數所組成的新字串,
    '寫到Arr陣列裡(覆蓋掉原陣列值)
Next i
With Sheet2.[e2].Resize(UBound(Arr) - 1)
'↑以下是關於[E2]儲存格擴展向下Arr陣列最大索引號-1格,
'因為有效結果值是從1索引號開始放,所以要-1
     .NumberFormatLocal = "@"
     '↑令這範圍儲存格格式是 文字
     .Value = Arr
     '↑令這範圍儲存格以Arr陣列值寫入儲存格裡,超過這範圍的陣列值忽略
End With
End Sub

Andy2483

謝謝論壇,謝謝各位前輩
後學藉此帖練習陣列與字典,學習方案如下,請各位前輩指教

Option Explicit
Sub TEST()
Dim Brr, Crr, Y, i&, TT$, T1$, T2$, T3$
'↑宣告變數
Set Y = CreateObject("Scripting.Dictionary")
'↑令Y變數是 字典
Brr = Range([途程資料!C1], [途程資料!A65536].End(3))
'↑令Brr變數是 二維陣列,以A~C欄儲存格值帶入陣列中
For i = UBound(Brr) To 2 Step -1
'↑設逆迴圈
   T1 = Brr(i, 1): T2 = Brr(i, 2): T3 = Brr(i, 3): TT = T1 & "|" & T3
   '↑令變數裝入陣列值或陣列值所組成的字串
   If Y(T1) = "" Then Y(T1) = T2
   '↑如果T1變數查字典 其item值是空的!就令其item值是 T2變數(最大途程序號)
   Y(TT) = T2 & "/" & Y(T1)
   '↑令TT這組合字串當key,item是 T2變數連接 "/" 再連接,
   '再連接以T1變數查Y字典得到的item值所組成的新字串
Next
Brr = Range([WIP!C2], [WIP!A65536].End(3))
'↑令Brr陣列倒掉舊陣列值,換裝(WIP)表的A~C欄儲存格值
ReDim Crr(1 To UBound(Brr), 1 To 1)
'↑宣告Crr變數是 二維空陣列,縱向範圍同Brr陣列,橫向1~1
For i = 1 To UBound(Brr)
'↑設順迴圈
   Crr(i, 1) = Y(Brr(i, 1) & "|" & Brr(i, 2))
   '↑令i迴圈列第1欄Crr陣列值是 第1欄與第2欄Brr迴圈陣列值,
   '以"|"間隔 所組成的新字串,查Y字典所得到的item值
Next
With [WIP!F2].Resize(UBound(Crr), 1)
'↑以下是關於[F2]儲存格擴展向下Crr陣列最大索引號列
     .NumberFormatLocal = "@"
     '↑令這範圍儲存格格式是 文字
     .Value = Crr
     '↑令這範圍儲存格以Crr陣列值寫入儲存格裡,超過這範圍的陣列值忽略
End With
Set Y = Nothing: Erase Brr, Crr
'↑令釋放變數
End Sub