1樓:辦公技巧雜談
這是個典型的資料對比問題,而不是簡單的檔案合併因為有對應關係。 vlookup 效率很低我有軟體可以對比效率極高點幾下滑鼠就完成了支援幾十萬級別的資料秒速出結果
2樓:
SubConsolidate
()Setd1=
CreateObject
("Scripting.Dictionary"
)''存放類別
Setd2
=CreateObject
("Scripting.Dictionary"
)''存放人名
Setdd
=CreateObject
("Scripting.Dictionary"
)For
Each
shIn
Worksheets
Ifsh
.Name
<>"彙總"
Then
myTitle=sh
.Cells(1
,"B")d1
(myTitle)=
""arr=sh
.[a1]
.CurrentRegion
Fori=2
ToUBound
(arr)d2
(arr(i
,1))=
""k=arr(i
,1)&
arr(1,
2)dd(
k)=arr(i
,2)Next
iEnd
IfNext
Sheets
("彙總"
).Range
("A1")=
"姓名"
Sheets
("彙總"
).Range
("A2"
).Resize(d2
.Count,1
)=.Transpose(d2
.keys
)''寫人名列
Sheets
("彙總"
).Range
("B1"
).Resize(1
,d1.Count)=
d1.keys
''寫標題行
arr=
Sheets
("彙總").[
a1].CurrentRegion
'' 寫入資料
Fori=2
ToUBound
(arr,1
)Forj=
2ToUBound
(arr,2
)k=arr(i
,1)&
arr(1,
j)arr(i,
j)=dd
(k)Next
jNext
iSheets
("彙總").[
a1].Resize
(UBound
(arr
),UBound
(arr,2
))=arrEndSub
vba如何快速省時的實現vlookup的效果,vba中如何實現vlookup省時匹配?
深木 Sub 搞事情 t Timer Dim i,j,k,m Dim arr Dim arr2 Dim Rng As Object,S2 As Object Dim R2,R3 As Long 清除原訂單狀態 Set S2 Sheet2 R2 S2.UsedRange.Rows.Count For ...
如何通過VBA來實現在excel中將每個sheet中存在括號的列標紅的功能呢?
老鷹 這是熱力圖的,可以除錯後使用 Sub tianse For i 182 To 188 Picture name Sheets Sheet1 Range O i Value Picture value Sheets Sheet1 Range P i If Picture value 0 Then...
求VBA大神指教,如何將圖1的EXCEl自動化生成圖2?
Subtrans Dim dataArea AsString Dimdata result iRow,i jdataArea A1 B3 data Range dataArea Valuei 0For iRow LBound data To UBound data Forj 0Todata iRow...