[Cần giúp đỡ] Chạy công thức cho khoảng 5000 dòng x 50 cột sau đó chuyển về giá trị cho nhẹ file (1 người xem)

[Hoàng Nhật Phương]

Làm như anh bảo:
- DictCount không biết bao nhiêu nên phải redim RArr bằng số dòng với SArr
- Trong vòng lặp chia ra 2 trường hợp exist và not exist, viết không khéo thì code dài
- Phải dự phòng trường hợp sheet chi tiết chưa được sắp xếp (câu lệnh phải tính thêm)
Tôi thì lười nên chọn cách dễ.


Nếu ngay từ đầu tôi viết gộp 2 biến thành 1, 2 For thành 1, 2 mảng thành 1 thì mấy tiểu thư nhà cô đọc 1 lần có hiểu không?
Thôi nói luôn:
Các câu lệnh gán cho 6 cột đầu và cột 9 là dư, những lần sau ghi đè lên lần trước, còn những cột còn lại thì lần sau cộng dồn vào lần trước

Ahii, con cũng không đọc yêu cầu và không để ý dữ liệu chú ạ,nhưng đúng là nhìn vào Code thì cũng thấy dư dư như chú chỉ dẫn ạ.

 

[ptm0412]

Ahii, con cũng không đọc yêu cầu và không để ý dữ liệu chú ạ,nhưng đúng là nhìn vào Code thì cũng thấy dư dư như chú chỉ dẫn ạ.

Giờ hiểu rồi thì đọc lại code của chú Hiểu @HieuCD đi:
- Đã bỏ bớt 1 for và 1 mảng
- Có If không cần else (là cách khéo đó, không khéo là code dài ngoằng không cần thiết)
- Không còn vụ ghi đè 6 cột đầu và cột 9 nữa

Mỗi tội khai báo RArr dư nhiều quá. Nếu dữ liệu rất nhiều mà Dict rất ít thì vừa hao phí vừa chậm

Sau đó, thử dùng mảng map như chú @VetMini bảo.

 

[Hoàng Nhật Phương]

Giờ hiểu rồi thì đọc lại code của chú Hiểu (@hieuxd ) đi:
- Đã bỏ bớt 1 for và 1 mảng
- Có If không cần else (là cách khéo đó, không khéo là code dài ngoằng không cần thiết)
- Không còn vụ ghi đè 6 cột đầu và cột 9 nữa

Sau đó, thử dùng mảng map như chú @VetMini bảo.

Con sẽ làm thử rồi thông tin lại lên đây sau nhé chú Mỹ,
Hiện đang trong giờ làm con cũng vào mang trộm thi thoảng đọc không để ý chú lại mắng.
Tối nay con phải làm một đống bài tập & học thuộc từ mới tiến Nhật nữa T_T

 

[ptm0412]

Tạo bảng dữ liệu như sau để map SArr và RArr


Code sẽ là:

Sub Tonghop2()
Dim Dict1, SArr(), RArr(), mapArr()
Dim LRw As Long, ik As Long, LsxNo As String, k As Long, sRow&
Set Dict1 = CreateObject("Scripting.Dictionary")
LRw = Sheet1.[A100000].End(xlUp).Row
SArr = Sheet1.Range("A4:AI" & LRw).Value
mapArr = Sheet4.[A1:B25].Value
Application.ScreenUpdating = False
t = Timer
sRow = UBound(SArr, 1)
ReDim RArr(1 To sRow, 1 To 35)
For i = 1 To sRow
    LsxNo = CStr(SArr(i, 1))
    If Not Dict1.exists(LsxNo) Then
        k = k + 1
        Dict1.Add LsxNo, k
        For j = 1 To 7
            RArr(k, mapArr(j, 1)) = SArr(i, mapArr(j, 2))
        Next
    End If
    ik = Dict1.Item(LsxNo)
    For j = 8 To 25
        RArr(ik, mapArr(j, 1)) = Val(RArr(ik, mapArr(j, 1))) + Val(SArr(i, mapArr(j, 2)))
    Next
Next
Sheet3.[A5].Resize(5000, 35).ClearContents
Sheet3.[A5].Resize(k, 35).Value = RArr
Application.ScreenUpdating = True
MsgBox Timer - t & " seconds", , "ptm0412"
End Sub

Test thử thì tốc độ 3 code bài 5 (@ptm0412), bài 20 (@hieuxd) và bài này tốc độ tương đương

 

[VetMini]

Trước khi viết trong vòng lặp tôi đã ghi ra giấy như thế này và chả thấy quy luật gì, thậm chí 31, 32, 33 còn lặp lại của 24, 25, 26. Nếu nhiều cột (100 cột) thì tôi dùng dữ liệu trong hình này tạo 1 mảng 2 cột và lặp for ngon lành. Cũng có ngoại lệ và phải dùng If hoặc case bên trong nữa. (nhất là cột 18 bị lỗi cộng text, phải dùng val)
...

Trong bài #9 tôi có nói rằng mục đích chưa hẳn là để cho gọn code.
Dùng quen Select Case rồi thì sẽ thấy nó cũng là một cách để kiểm soát quy luật. Nhìn vào cái block code đó và lướt qua các hàng Cases, người ta sẽ nắm được đại khái những dòng nào sẽ theo quy luật nào, dễ debug nếu thấy kết quả không vừa ý. Hoặc dễ sửa đồi nếu quy luật đổi.
Tuy nhiên, phải đồng ý với bạn là bài này nó chẳng theo quy luật gì cả. Tôi chỉ ví dụ cho người hỏi hiểu cách thức thôi.

Vụ nhiều cột thì bài #9 tôi cũng đề cập đến việc mapping. Đến đây thì lập trình hướng đối tượng tỏ rõ ưu điểm: bạn có thể tạo class sao cho nó có thể biến chuyển theo nhu cầu khác nhau.

 

[ptm0412]

Dùng quen Select Case rồi thì sẽ thấy nó cũng là một cách để kiểm soát quy luật. ...
Vụ nhiều cột thì bài #9 tôi cũng đề cập đến việc mapping. Đến đây thì lập trình hướng đối tượng tỏ rõ ưu điểm: bạn có thể tạo class sao cho nó có thể biến chuyển theo nhu cầu khác nhau.

Tôi có biết ưu điểm của select case và có dùng trong 1 số trường hợp.
Bài 24 trên tôi cũng map nhưng map theo kiểu đơn giản nhất mà tôi có thể nghĩ ra. Chưa phải dùng class

 

[yeungannam003]

Sử dụng Dictionary và mảng
Ghi chú: công thức từ cột N trở về sau của bạn sai, tại sao lại là OK
N4==IF(A4<>"",SUMIF('spN3'!$A$4:$OK$585,A4,'spN3'!$P$4:$P$585),"")
Ngoài ra 3 cột AE, AF, AG công thức giống 3 cột X, Y, Z. Bạn tự xem và sửa code nếu cần

Xem file đính kèm, tôi đang ghi kết quả vào dòng 5, chừa dòng 4 của bạn lại, muốn về dòng 4 thì sửa code.

Sử dụng Dictionary và mảng
Ghi chú: công thức từ cột N trở về sau của bạn sai, tại sao lại là OK
N4==IF(A4<>"",SUMIF('spN3'!$A$4:$OK$585,A4,'spN3'!$P$4:$P$585),"")
Ngoài ra 3 cột AE, AF, AG công thức giống 3 cột X, Y, Z. Bạn tự xem và sửa code nếu cần

Xem file đính kèm, tôi đang ghi kết quả vào dòng 5, chừa dòng 4 của bạn lại, muốn về dòng 4 thì sửa code.

Thật tuyệt vời! Mặc dù em chưa hiểu hết được code của bác. Em sẽ nghiên cứu dần. Cảm ơn bác nhiều nha! Chúc bác nhiều sức khỏe

 

[yeungannam003]

Sử dụng Dictionary và mảng
Ghi chú: công thức từ cột N trở về sau của bạn sai, tại sao lại là OK
N4==IF(A4<>"",SUMIF('spN3'!$A$4:$OK$585,A4,'spN3'!$P$4:$P$585),"")
Ngoài ra 3 cột AE, AF, AG công thức giống 3 cột X, Y, Z. Bạn tự xem và sửa code nếu cần

Xem file đính kèm, tôi đang ghi kết quả vào dòng 5, chừa dòng 4 của bạn lại, muốn về dòng 4 thì sửa code.

Có vấn đề này mong chú chỉ thêm. Con muốn ở sheet2
cột 1-6: dòng 4 cũng được cập nhật khi chạy code luôn, hiện tại đang cập nhật từ hàng 5
cột 7-33: dòng 4 được giữ nguyên công thức nhưng từ dòng 5 số liệu được cập nhật theo công thức dòng 4 (có thể thay đổi khi cần), hiện tại dù con có thay đổi công thức dòng 4 thì các số liệu từ dòng 5 vẫn cập nhật theo công thức trước đó
Vì con chưa nắm được code của chú nên chú có thể chỉ con cần chỉnh chỗ nào để xử lý 2 vấn đề này không ạ
Cảm ơn chú nhiều!

 

[SA_DQ]

@yeungannam003, post: 1007535, member: 1277786
CSDL như file của bạn thiết kế như vậy sẽ có rất nhiều ô trống trong vùng từ cột 36 đến cột 401
Để khử các hàng hà sa số những ô trống này người ta thường xây dựng CSDL quan hệ (1 -> n)
Bạn có thể tham khảo đâu đó & tiến hành xây dựng lại CSDL & chuyển hẵn sang VBA; Chứ hiện giờ nội gần 400 công thức để tính thứ trong tuần cho các ngày của năm cũng đủ mệt máy rồi

Thân ái!

 

[ptm0412]

cột 7-33: dòng 4 được giữ nguyên công thức nhưng từ dòng 5 số liệu được cập nhật theo công thức dòng 4 (có thể thay đổi khi cần), hiện tại dù con có thay đổi công thức dòng 4 thì các số liệu từ dòng 5 vẫn cập nhật theo công thức trước đó

Code đang viết dựa trên công thức chứa hàm SumIf, nếu công thức khác không phải SumIf là phải bỏ code.
Nếu cũng là SumIf nhưng thay đổi cột tính sum, thì sửa trong bảng mapArr ở bài 24, cột B. Giải thích:
Sheet "thsl" đang không dùng nên tôi tạo bảng 2 cột 25 dòng: Cột A chứa số thứ tự cột kết quả và cột B chứa thứ tự cột dữ liệu nguồn cần tính tổng theo SumIf. Trừ 6 cột đầu và cột 9 không tính tổng.
Thí dụ :
Cột G kết quả (thứ tự 7) có công thức G4 =IF(A4<>"",SUMIF('spN3'!$A$4:$A$585,A4,'spN3'!$F$4:$F$585),""), tức là tính tổng từ cột F dữ liệu nguồn (thứ tự 6) thì trong bảng tra A8 = 7 và B8=6


Nếu G4 vẫn là SumIf và thay đổi cột tính tổng là cột khác thì sửa thứ tự cột nguồn ở cột B bảng tra (ô B8).
Nếu chuyển G sang cột khác thì sửa ô A8 thành thứ tự cột tương ứng
Nếu chèn/ xoá cột thì phải sửa nguyên bảng. Chèn/ xoá cột sheet dữ liệu thì sửa toàn bộ cột B, chèn/ xoá cột sheet tổng hợp thì sửa toàn bộ cột A, đồng thời phải nới rộng/ thu nhỏ số cột của mảng RArr (hiện tại là 35)
Nếu G4 không phải SumIf mà thay đổi từa lưa thì phải bỏ code này và quay về code gốc của bạn.
Do đó công thức dòng 4 không thể nào xài với code này. Một là cập nhật đè lên, hai là ẩn dòng 4 đi.