帖子: 145
主題: 42
精華: 0
積分: 193
點名: 0
作業系統: Windows7
軟體版本: Office2010
閱讀權限: 20
性別: 男
註冊時間: 2010-11-25
最後登錄: 2024-10-10

論壇贊助勳章

1^# 跳轉到 » 倒序看帖

字體大小: tT

發表於 2021-6-13 02:10 | 只看該作者

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

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

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

途程問題20210613.rar (11.2 KB)

樓主熱帖

Adam

准提部林

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 2834
主題: 10
精華: 0
積分: 2890
點名: 0
作業系統: 〔略〕
軟體版本: 〔略〕
閱讀權限: 100
性別: 男
來自: 〔略〕
註冊時間: 2013-5-13
最後登錄: 2024-11-22

2^#

發表於 2021-6-13 09:57 | 只看該作者

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

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

TOP

准提部林

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 2834
主題: 10
精華: 0
積分: 2890
點名: 0
作業系統: 〔略〕
軟體版本: 〔略〕
閱讀權限: 100
性別: 男
來自: 〔略〕
註冊時間: 2013-5-13
最後登錄: 2024-11-22

3^#

發表於 2021-6-13 10:23 | 只看該作者

兩種方式:

Xl0000558.rar (14.44 KB)

TOP

adam2010

中學生 Rank: 2

帖子: 145
主題: 42
精華: 0
積分: 193
點名: 0
作業系統: Windows7
軟體版本: Office2010
閱讀權限: 20
性別: 男
註冊時間: 2010-11-25
最後登錄: 2024-10-10

論壇贊助勳章

4^#

發表於 2021-6-13 14:46 | 只看該作者

回復 2# 准提部林

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

Adam

TOP

hcm19522

大學生 Rank: 4

帖子: 1387
主題: 4
精華: 0
積分: 1397
點名: 0
作業系統: WIN
軟體版本: 2007
閱讀權限: 50
性別: 男
註冊時間: 2015-9-11
最後登錄: 2024-11-22

熱烈參與勳章

5^#

發表於 2021-6-13 16:06 | 只看該作者

本帖最後由 hcm19522 於 2021-6-13 16:36 編輯

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

google"EXCEL迷" blog 或google網址:https://hcm19522.blogspot.com/

TOP

Andy2483

　暱稱: 厚臉皮的學生

大學生 Rank: 4

帖子: 1446
主題: 40
精華: 0
積分: 1470
點名: 0
作業系統: Windows 7
軟體版本: Excel 2010 & 2016
閱讀權限: 50
性別: 男
來自: 台灣
註冊時間: 2020-7-15
最後登錄: 2024-10-21

6^#

發表於 2023-5-25 09:51 | 只看該作者

回復 3# 准提部林

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

執行前:

執行結果:

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

用行動裝置瀏覽論壇學習很方便,謝謝論壇經營團隊
請大家一起上論壇來交流

TOP

Andy2483

　暱稱: 厚臉皮的學生

大學生 Rank: 4

帖子: 1446
主題: 40
精華: 0
積分: 1470
點名: 0
作業系統: Windows 7
軟體版本: Excel 2010 & 2016
閱讀權限: 50
性別: 男
來自: 台灣
註冊時間: 2020-7-15
最後登錄: 2024-10-21

7^#

發表於 2023-5-25 11:00 | 只看該作者

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

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

用行動裝置瀏覽論壇學習很方便,謝謝論壇經營團隊
請大家一起上論壇來交流

TOP