Nên chọn lọc DL = AF hay vòng lặp!

[ThuNghi]

Tôi đang phân vân nên chọn lọc DL = AF hay vòng lặp! (VBA)
Các bạn chỉ hộ ưu, khuyết điểm lớn nhât của 02 cách này nhé!
Xin cám ơn!

 

[OverAC]

Theo em thì lọc bằng Advance Filter tốt hơn vì nó nhanh hơn. Tuy nhiên nó khó điều khiển với các điều kiện lọc phức tạp.
Dể điều khiển được điều kiện lọc, làm được nhiều việc kèm theo nhưng chậm.

Dù sao thì em vẫn thích cách lọc bằng Advance Filter hơn.

 

[Mr Okebab]

Tôi đang phân vân nên chọn lọc DL = AF hay vòng lặp! (VBA)
Các bạn chỉ hộ ưu, khuyết điểm lớn nhât của 02 cách này nhé!
Xin cám ơn!

Cái nào cũng có cái hay và dở cả.
AF :

• Hay : Lọc nhanh, mạnh, dễ làm, dễ điều khiển, có thể lọc từ đơn giản đến phức tạp
• Dở : Phải tạo bảng phụ (vì số cột của Data và bảng lọc ra là bằng nhau, trong khi đó ta chỉ cần 3,4 . . cột thôi. Các điều kiện lọc phức tạp hơi khó thiết lập. Chỉ lấy dữ liệu từ 1 bảng nên làm việc với nhiều bảng thì chịu. Chính vì vậy các dữ liệu nếu ta lấy từ nhiều bảng thì sẽ rất phức tạp. Việc lọc này đòi hỏi phải đặt name (cũng là một dạng công thức), vì data luôn thay đổi. Việc thay đổi thứ tự cột trong data rất nguy hiểm vì việc chỉnh sửa rất khó khăn (từ bảng phụ -->Báo cáo)
• Thích hợp : Khi mà bảng dữ liệu đã chứa tất cả dữ liệu mà ta cần có để ra báo cáo. TH này không nên dùng cho CSDL thiết kế theo kiểu Main, Sub

Vòng lặp

• Hay : Mạnh,nhanh, lọc được từ nhiều nguồn khác nhau, điều kiện lọc có thể rất phức tạp, liên quan đến nhiều bảng (VD SheetMain và SheetSub). ta không cần tạo bảng phụ, có thể lọc ra dữ liệu và đẩy trực tiếp vào báo cáo. Ta không cần đặt name cũng có thể lọc được. Việc chỉnh sửa các cột trong data không gây ảnh hưởng nhiều vì chỉ cần thay đổi code chút xíu là được (chỉ số của Offset)
• Dở : Lập các điều kiện khó hơn, khi thay đổi các điều kiện sẽ mất nhiều công sức hơn so với AF
• Thích hợp : Có thể phù hợp theo kiểu Only table (chỉ có 1 bảng data duy nhất), hoặc cả theo kiểu Main Sub (đặc biệt hữu dụng)

Vì vậy, tùy theo từng người thôi.
Ngày xưa thì mình dùng AF, bây giờ thì lại khoái dùng vòng lặp !!???

Thân!

 

[ThuNghi]

Tôi cũng dùng vòng lặp, nhưng hay lùng bùng các tham số dòng cột. Và bực nhất là dl nguồn thay đổi, số TT cột, dòng change.
Xin thọ giáo nữa:
1/Thay vì range(...).count hay WS.counta(), có nên cho nó giá trị count vào 1 cells() nào đó. Áp dụng trong tìm dòng cuối.
2/Khi tìm trong danh mục theo 1 tiêu thức nào đó, giống như đưa dl vào listbox (file của Hiếu), nên làm công thức index match, dùng vba copy công thức xuống, vẫn giữ formular, liệu có nhanh hơn là VBA hòan tòan (vòng lặp).
3/ Các cao thủ giúp cho tôi cú pháp và ví dụ vòng lặp for each...Phần này chưa hiểu lắm.
Xin cám ơn nhiều.

 

[Mr Okebab]

Tôi cũng dùng vòng lặp, nhưng hay lùng bùng các tham số dòng cột. Và bực nhất là dl nguồn thay đổi, số TT cột, dòng change.
Xin thọ giáo nữa:
1/Thay vì range(...).count hay WS.counta(), có nên cho nó giá trị count vào 1 cells() nào đó. Áp dụng trong tìm dòng cuối.
2/Khi tìm trong danh mục theo 1 tiêu thức nào đó, giống như đưa dl vào listbox (file của Hiếu), nên làm công thức index match, dùng vba copy công thức xuống, vẫn giữ formular, liệu có nhanh hơn là VBA hòan tòan (vòng lặp).
3/ Các cao thủ giúp cho tôi cú pháp và ví dụ vòng lặp for each...Phần này chưa hiểu lắm.
Xin cám ơn nhiều.

Theo mình nghĩ :

1. Không nên cho giá trị dòng cuối cùng vào 1 ô nào đó vì đó cũng là công thức mà, hơn nữa lại thêm 1 ô.

- Nếu để đặt name thì nên đặt giá trị này là 1 name luôn. VD : DC = counta(Data!A1:A65000)
Sau đó muốn lấy giá trị này để đặt name (dùng Offset) thì cứ gọi nó ra. Sau này có chỉnh sửa gì thì chỉ chỉnh sửa DC là được

- Nếu để tính toán trong VBA (dùng vòng lặp chẳng hạn), ta nên dùng cách DC = Range("A65000").End(xlUp).Row như vậy rất nhanh và không phụ thuộc vào ô nào cả (để nếu bị xóa . . thì vẫn không ảnh hưởng)
Nó chỉ tính toán 1 lần khi bắt đầu chạy Sub, còn nếu là ô thì cứ khi nào sheet đó tính toán là nó lại tính toán lần nữa. Như vậy tốc độ sẽ giảm đi.
​

2. Dùng công thức sau đó copy xuống các hàng dưới hay dùng VBA ??
Bản thân mình cũng đã suy nghĩ nhiều về nó.
Nếu chỉ có 1 ô thì đúng là dùng công thức nhanh hơn nhiều. Vì công thức cũng là một đoạn mã máy đã được tối ưu hoàn toàn. Nó cũng là một vòng lặp thôi.
VD : Countif : Nó phải duyệt qua tất cả các ô (vòng lặp đấy), sau đó ô nào thỏa mãn điều kiện thì nó sẽ tính là 1.
Hàm sum, counta, count . . . cũng thế.

Hãy thử tưởng tượng nhé :
Data của ta có 10.000 dòng. Bảng tổng hợp của ta có 4 cột, 200 hàng.

- Công thức : Như vậy nếu dùng công thức ta sẽ có 800 ô tương đương 800 công thức. Và để tính toán nó thì mỗi ô cũng đều là một vòng lặp, như vậy ta phải dùng 800 vòng lặp. Làm cách này thì dễ vì lập công thức không có gì là khó cả. Khi cần thay đổi thì rất nhanh

- Vòng lặp : Ta chỉ cần duyệt 1 lần thôi. Nhưng khi thay đổi điều kiện thì lại rất mất thời gian.
​

Vậy đấy, khi các ô chỉ có vài chục thì dùng công thức cũng không sao, tuy nhiên số lượng ô tính toán lên đến hàng trăm, hàng ngàn thì dùng vòng lặp sẽ tốt hơn (và khổ hơn)

3. Bác thử xem qua File này xem nhé. Em đang làm dở (chuyển từ AF sang vòng lặp, sử dụng Main, Sub)

Thân!

 

[ThuNghi]

File baocao của Mr Hiếu hay ghê, nhưng các báo cáo sao các footer lại phải năm ở row 2000 và dùng hide row trống. Làm như vậy file sẽ nặng và nếu dùng asap nó sẽ bỏ hết các dòng trống.
Bạn cho luôn "cuốn thượng","trung", chỉ có "cuốn hạ" nên bị "tẩu hỏa nhập ma", giúp thì giúp cho trót. Không phải ham đâu. Cám ơn nhiều nhé. Mail cho mình đi.

 

[Mr Okebab]

File baocao của Mr Hiếu hay ghê, nhưng các báo cáo sao các footer lại phải năm ở row 2000 và dùng hide row trống. Làm như vậy file sẽ nặng và nếu dùng asap nó sẽ bỏ hết các dòng trống.
Bạn cho luôn "cuốn thượng","trung", chỉ có "cuốn hạ" nên bị "tẩu hỏa nhập ma", giúp thì giúp cho trót. Không phải ham đâu. Cám ơn nhiều nhé. Mail cho mình đi.

Việc có các dòng trống và hide nó đi không làm ảnh hưởng đến tốc độ tính toán và dung lượng của File (dung lượng file tăng thêm chút xíu vì có mấy đường kẻ bao quanh)

Việc xóa dòng trống thì đơn giản, tuy nhiên khi thay đổi thì lại phải Insert, mà insert lâu hơn là hide, vì dữ liệu input trực tiếp vào báo cáo mà. Còn tại sao là 2000 là bởi vì mình ước lượng 1 báo cáo có tối đa khoảng 2000 dòng. Không thích thì thay đổi thôi.

Việc "thượng - trung - hạ" liên quan đến tổ chức CSDL, cái này mới đau đầu, chứ làm mấy cái báo cáo thì . . . dẽ ẹc. Vì chưa ra đâu vào đâu nên chưa dám đưa lên (sợ bị cười). Trong các phần thì phần làm và tổ chức DATA mất nhiều công sức nhất mà.

Thân!

 

[Tuấn Giang]

Cảm ơn bác,

Sub CapNhatDM()
With S200
.Visible = xlSheetVisible
.Select

End With
Range("A2:AL1000").ClearContents
Range("A1:AL1").Copy
With Range("A2:AL1000")
.PasteSpecial xlPasteFormulas
.Calculate
.Copy
.PasteSpecial xlPasteValues
End With
S200.Visible = xlSheetVeryHidden
End Sub

Khi em vào Quản trị -> Cập nhật dữ liệu thì nó báo lỗi ở dòng màu vàng đó.

 

[Mr Okebab]

Cảm ơn bác,

Khi em vào Quản trị -> Cập nhật dữ liệu thì nó báo lỗi ở dòng màu vàng đó.

Vì S200 không tồn tại, mình xóa đi rồi.

Đây chỉ là VD về vòng lặp chứ không phải là một chương trình bạn ạ.

Thân!