Sử dụng VBA để Xử lý 20.000Data Thay INDEX(Match)

[Đô Huỳnh]

Chào Anh/Chị và các bạn,

Em đang gặp vấn đề khi xử lý 20.000 Data.

Cụ thể: Em cần tìm giá trị theo 2 điều kiện

- Cách cũ: Em đã dùng
1. AGGREGATE(14,6,(<Condition 1>)*(<Condition 2>)*(<Kết quả>),1)
=> Máy chạy không nổi
2. {VLOOKUP(<Criteria 1> & <Criteria 2> ,CHOOSE({1,2},<Criteria_range 1>&<Criteria_range 2>, <Kết quả>),2,0)}
=> 5p cho 1.000 Data
3. INDEX kết hợp MATCH
INDEX(<Kết quả> , MATCH( 1 , INDEX((<Condition 1>)*(<Condition 2>),0),0)
=> 3p cho 1.000 Data
INDEX(<Kết quả> , MATCH( 1 , (<Condition 1>)*(<Condition 2>),0))
=> 2p cho 1.000 Data
=> 43p cho 20.000 Data

Vấn đề: File khá nặng và mỗi lần mở / Hide / Filter thì nó chạy tính toán mất thời gian.

- Cách mới của cũ: Em dùng VBA để Paste Value cho nhẹ.
=> 1h12p Cho 20.000 Data

Vấn đề: Data em nối bằng Query để khi cập nhật thì nó có dữ liệu cho Sheet thống kê.

Đoạn code và file đính kèm như sau:

Dim i As Integer
Dim j As Integer
i = 2
j = 2

'Check ID
Do While Sheets("STATISTIC").Cells(i, j).Value <> ""

'Chen Cong thuc vao O C2
Sheets("STATISTIC").Cells(i, j + 1).FormulaArray = _
"=IFNA(INDEX(Revenue[Revenue],MATCH(1,(Revenue[ID]=RC2)*(Revenue[CK]=R1C),0)),""-"")"

'Copy C2
Cells(i, j + 1).Copy

'Paste
Range("D" & i & ":" & "W" & i).Select
'Range("W" & i).Activate
ActiveSheet.Paste

'Paste Value
Range("C" & i & ":" & "W" & i) = Range("C" & i & ":" & "W" & i).Value

i = i + 1
Loop

Application.CutCopyMode = False
Cells(1, 1).Select
MsgBox "Done"

End Sub

[File đính kèm là Excel 3 sheet: Statistic (Chứa Form) , Data (Tham chiếu), 20000Data]

Mong mọi người chỉ điểm, Thanks All !!!!!

 

[HieuCD]

Tôi kiểm tra qua thì thấy các ô sau có kết quả khác với kết quả gốc của chủ thớt: J2, H3, H4, H4.

Không rỏ chủ thớt lấy kết quả như thế nào, mình kiểm tra lại dữ liệu thấy các ô trên chủ thớt tính thiếu, ví dụ ô H3

 

[Đô Huỳnh]

Không rỏ chủ thớt lấy kết quả như thế nào, mình kiểm tra lại dữ liệu thấy các ô trên chủ thớt tính thiếu, ví dụ ô H3
View attachment 252175

View attachment 252176

Dạ, Em có kiểm tra lại.

Do nguồn của Sheet Data bị sai,
ID: YLEN1Y75LK7101975
C1980 là 60.000; ô C34535 sai, đúng là 160.000
Em sẽ báo bên nguồn sửa sau.

Cảm ơn @batman1 và @HieuCD nhiều ạ.

 

[batman1]

Không rỏ chủ thớt lấy kết quả như thế nào, mình kiểm tra lại dữ liệu thấy các ô trên chủ thớt tính thiếu, ví dụ ô H3
View attachment 252175

View attachment 252176

Nếu thiếu thì tôi cũng thấy dễ hiểu, vì thiếu CK = 80 000, có 2 Revenue cho 1 CK = 60000. Nhưng có kết quả 697 000 từ đâu thì hơi "sốc" vì không có ID = YLEN1Y75LK7101975 mà Revenue = 697 000 tôi không phân tích code).

 

[hocexcel_1991]

Tôi kiểm tra qua thì thấy:
- Của chủ thớt. Với ID = YLEN1Y75LK7101974 thì kết quả cho CK = 80 000 là 348 500 (J75 sheet STATISTIC). Trong sheet Data thì ứng với ID = YLEN1Y75LK7101974 và CK = 80 000 cũng là 348 500.

- Trong lời giải bài #14 thì với ID = YLEN1Y75LK7101974 thì kết quả cho CK = 80 000 lại là 697000 (J14 sheet KetQua)

Nếu tôi không lầm thì các kết quả sau cũng sai: H15, H16, H17, D293, F293, D345, H376, J444, F543, D690, D842, F842, D1381, H2920, F3209, D3670, D3692, F3758, D4177, F4225, D4245, D4277, C4471, và 130 ô khác cũng sai.

Có thể tôi nhầm lẫn với các ô tô xanh nhưng chắc chắn sai ở J14, H15, H16 và H17 (đã kiểm tra). Bạn thử kiểm tra lại xem cho chắc chắn.

Bài của em đang làm là tính tổng (cột "Revenue" của sheet Data) các ID và CK cho trước dựa theo sheet nguồn là Data. Em chưa kiểm tra lại so với sheet"STATISTIC" của chủ thớt (cũng có khả năng em hiểu sai với ý của chủ thớt)