✔ 最佳答案
公式靈活性高,難在抓出準確的計算範圍,減少不必要的計算,
VBA若也是使用函數,且逐格或逐行寫入,在資料多下,也是不夠快,
尚且修改任一數據,皆必須重新執行一次;
所以,看需求及資料多寡再決定公式或VBA~~
2015-07-29 12:30:46 補充:
Sheet3
B1.輸入4 C1,輸入6 設格式為:0”字元”
B2:右拉下刷
=SUMPRODUCT(N(COUNTIF(OFFSET(Sheet1!$B$1,MATCH($A2,Sheet1!$A:$A,)-1,,COUNTIF(Sheet1!$A:$A,$A2),99),Sheet2!$A$1:$A$25)*(LEN(Sheet2!$A$1:$A$25)=B$1)))
用多一點的資料試試看~~
也是僅供參考,不答題!
2015-07-29 14:18:18 補充:
Sub TEST()
Dim R, xD, T$, U$, N&, V%, Arr, Brr
[Sheet3!2:60000].ClearContents
Set xD = CreateObject("Scripting.Dictionary")
For Each R In [Sheet2!A1:A25].Value: xD(R) = 1: Next
2015-07-29 14:18:40 補充:
Arr = Sheets("Sheet1").UsedRange
ReDim Brr(1 To UBound(Arr), 1 To 3)
For j = 1 To UBound(Arr)
T = Arr(j, 1)
If T <> "" And T <> U Then N = N + 1: U = T: Brr(N, 1) = U
2015-07-29 14:18:48 補充:
For k = 2 To UBound(Arr, 2)
T = Arr(j, k): V = Len(T)
If V = 4 Or V = 6 Then Brr(N, V / 2) = Brr(N, V / 2) + xD(T)
Next k
Next j
[Sheet3!A2:C2].Resize(N) = Brr
End Sub
2015-07-29 14:19:58 補充:
參考即可~~
Sheet1,一萬列應也幾秒可完成~~
2015-07-31 22:54:55 補充:
007~009程式碼,尚未知是否符合題需?
那些程式對還不熟VBA的,可能有點難度,要講解也不易,
DIC字典檔及ARRAY陣列,以前我也是摸一大陣子才稍會運用,
還有多天,不急著結案,等看看有否其他大大較平易的解法!
VBA重點在提問者能了解,並可依不同需求自己能修改,
這才是主要目的~~
2015-08-01 18:01:49 補充:
EXCEL VBA. 統計各項目明細符合條件的個數
<.准提部林.>
---------------------------------
■明細資料:z12263426372639343734393739263437z12010501100124051005241024010510z13010501300128053005393039010530z130105012801200130052805200530z131120202820272037112811271137z14012815383738 z14012810191519101519 z14012820313031203031
■比對條件:01280524010530
■輸出結果: 4個字元6個字元z1211z1322z1430
■程式碼:
Sub TEST()
Dim R, xD, T$, U$, N&, V%, Arr, Brr
[Sheet3!2:60000].ClearContents
Set xD = CreateObject("Scripting.Dictionary")
For Each R In [Sheet2!A1:A25].Value: xD(R) = 1: Next
Arr = Sheets("Sheet1").UsedRange
ReDim Brr(1 To UBound(Arr), 1 To 3)
For j = 1 To UBound(Arr)
T = Arr(j, 1)
If T <> "" And T <> U Then N = N + 1: U = T: Brr(N, 1) = U
For k = 2 To UBound(Arr, 2)
T = Arr(j, k): V = Len(T)
If V = 4 Or V = 6 Then Brr(N, V / 2) = Brr(N, V / 2) + xD(T)
Next k
Next j
[Sheet3!A2:C2].Resize(N) = Brr
End Sub
---------------------------------
<範例檔>下載:
檔案名稱:20150801a01(統計各項目符合個數)
下載連結:
http://www.funp.net/140107
---------------------------------
2015-08-01 18:08:27 補充:
趁空貼答,看來應該沒人再有興趣參與此題!
〔監看功能〕,學VBA至今,從未用過,也不會用!^ ^
所以寫程式都以〔段式〕寫法,可逐段測試!