Cách nào để lựa chọn Range vùng dữ liệu tối ưu?

  • Thread starter heroic
  • Ngày gửi
H

heroic

Thành viên sơ cấp
11/11/04
56
0
0
vn
#1
Mong chỉ giáo giúp amator về VBA!
1.Em đang gặp phải vấn đề là khi range một vùng mặt hàng rồi đặt tên thường phải để dư ra VD: A1:B50 như vậy thì khi hiện lisbox hay listview thì mới có đủ mặt hàng nếu mặt hàng ít hơn là 10, hay lớn hơn 50 thì không tối ưu cho file. Có cách nào giải quyết bằng code VBA với việc này? khi thực hiện nó chỉ range vùng có dữ liệu thôi Mong các cao thủ chỉ giáo!
2. Khi rút trích dữ liệu từ một worksheet dùng auto filter thì lọc bỏ được các dòng trống, Vậy có cách nào xoá luôn được cái dòng trống đó bằng VBA không?
 
H

handung107

Thành viên thân thiết
28/8/04
576
13
0
VN
www.giaiphapexcel.com
#2
Cả 2 vấn đề của bạn đều không cần dùng VBA.
1/ Đặt tên cho vùng dữ liệu động (Dynamic Range). Trong hộp Refer to, thay vì bạn gõ cố định tên vùng , bạn hãy dùng công thức tham chiếu OFFSET

Giả sử vùng dữ liệu của tôi gồm : Cột A (Họ và Tên), Cột B (Địa chỉ), Cột C (Mã số thuế) trên Sheet tên DMKH và tôi bắt đầu nhập liệu từ hàng thứ 2 trở đi
Đặt tên :
HOTEN : =OFFSET('DMKH'!$A$2,0,0,COUNTA($A:$A),1)
DIACHI : =OFFSET('DMKH'!$B$2,0,0,COUNTA($A:$A),1)
MST : =OFFSET('DMKH'!$C$2,0,0,COUNTA($A:$A),1)
Và tên CSDL là :
DL==OFFSET('DMKH'!$A$2,0,0,COUNTA($A:$A),3) (CSDL có 3 cột)

Nếu tôi muốn vùng DL này được mở rộng cả về phía bên phải của cột C thì :

DL : =OFFSET('DMKH'!$A$2,0,0,COUNTA('DMKH'!$A:$A),COUNT A('DMKH'!$1:$1)).

2/ Rút dữ liệu lọai bỏ hàng trống, điều này bàn luận rất nhiều trên diễn đàn, bạn có thể tham khảo ngay tại Topic Excel tổng quát hay Excel và KT. Bạn có thể dùng Add-in Asap utilities cũng có chức năng lọai bỏ dòng trống trong vùng đã chọn. Download add-in này tại : www.asap-utilities.com
 
L

levanduyet

Welcome
16/10/04
535
11
18
HCM
my.opera.com
#3
Hay hay

handung107 nói:
Cả 2 vấn đề của bạn đều không cần dùng VBA.
1/ Đặt tên cho vùng dữ liệu động (Dynamic Range). Trong hộp Refer to, thay vì bạn gõ cố định tên vùng , bạn hãy dùng công thức tham chiếu OFFSET

Giả sử vùng dữ liệu của tôi gồm : Cột A (Họ và Tên), Cột B (Địa chỉ), Cột C (Mã số thuế) trên Sheet tên DMKH và tôi bắt đầu nhập liệu từ hàng thứ 2 trở đi
Đặt tên :
HOTEN : =OFFSET('DMKH'!$A$2,0,0,COUNTA($A:$A),1)
DIACHI : =OFFSET('DMKH'!$B$2,0,0,COUNTA($A:$A),1)
MST : =OFFSET('DMKH'!$C$2,0,0,COUNTA($A:$A),1)
Và tên CSDL là :
DL==OFFSET('DMKH'!$A$2,0,0,COUNTA($A:$A),3) (CSDL có 3 cột)

Nếu tôi muốn vùng DL này được mở rộng cả về phía bên phải của cột C thì :

DL : =OFFSET('DMKH'!$A$2,0,0,COUNTA('DMKH'!$A:$A),COUNT A('DMKH'!$1:$1)).

2/ Rút dữ liệu lọai bỏ hàng trống, điều này bàn luận rất nhiều trên diễn đàn, bạn có thể tham khảo ngay tại Topic Excel tổng quát hay Excel và KT. Bạn có thể dùng Add-in Asap utilities cũng có chức năng lọai bỏ dòng trống trong vùng đã chọn. Download add-in này tại : www.asap-utilities.com
Chị Dung,
Việc dùng Dynamic Range em đã gặp "vấn nạn" của nó rồi. Đó là tốc độ. Em đang viết chương trình tạo báo cáo cho một người bạn. Em đã dùng Dynamic Range + Sumproduct...Kết quả là quá chậm! "Khách hàng" không chấp nhận.
Theo kinh nghiệm của em, nên đặt tên cho khối dữ liệu của mình (mà không có record trống). Sau đó mỗi khi muốn thêm vào ta chỉ việc chèn hàng vào trong khối dữ liệu này. Điều này sẽ tránh cho việc Re-Cal...dẫn đến chậm chương trình và cuối cùng ... em phải chuyển hướng khác.
Vài lời chia sẻ.
Thân,
LVD
 
L

Longlv

Thành viên sơ cấp
21/10/05
44
1
8
41
Hanoi
#4
Dynamic Range với ADO

Còn em lại gặp một vấn đề khác với Dynamic Range, nếu dùng Dynamic Range với ADO thì chương trình không chạy được, chỉ khi dùng Range cố định thì lại chạy bình thường. Không biết mọi người có gặp trường hợp như em không?

Kết quả của ADO chỉ đúng khi Workbook được save.
 
V

VanHao

Thành viên thân thiết
18/12/05
70
2
6
Go Vap
#5
heroic nói:
Mong chỉ giáo giúp amator về VBA!
1.Em đang gặp phải vấn đề là khi range một vùng mặt hàng rồi đặt tên thường phải để dư ra VD: A1:B50 như vậy thì khi hiện lisbox hay listview thì mới có đủ mặt hàng nếu mặt hàng ít hơn là 10, hay lớn hơn 50 thì không tối ưu cho file. Có cách nào giải quyết bằng code VBA với việc này? khi thực hiện nó chỉ range vùng có dữ liệu thôi Mong các cao thủ chỉ giáo!
2. Khi rút trích dữ liệu từ một worksheet dùng auto filter thì lọc bỏ được các dòng trống, Vậy có cách nào xoá luôn được cái dòng trống đó bằng VBA không?
Bạn tạo một Macro đặt tên cho các vùng. Mỗi lần trích lọc dữ liệu chỉ cần Refresh lại là xong ngay. Nếu làm theo cách này thì chỉ những vùng dữ liệu nào phát sinh thì mới được đặt tên. Bạn dùng Macro Recorder ghi lại thao tác đặt tên cho vùng rồi chỉnh sửa chút đỉnh, sau đó tạo nút gán cho cái Macro vửa tạo trên một sheet nào đó. Chúc bạn thành công.
 
V

VanHao

Thành viên thân thiết
18/12/05
70
2
6
Go Vap
#6
heroic nói:
Mong chỉ giáo giúp amator về VBA!
2. Khi rút trích dữ liệu từ một worksheet dùng auto filter thì lọc bỏ được các dòng trống, Vậy có cách nào xoá luôn được cái dòng trống đó bằng VBA không?
Đây là đoạn VBA dùng để xoá dòng trống, bạn dùng thử xem
Cells(5, 3).Select ' chỉ là ví dụ , vùng dữ liệu của bạn có thể khác
Set C = ActiveCell
Do While Not C.Offset(0, 0).Value = "."
If C.Offset(0, 0).Value = 0 And C.Offset(0, 1).Value = 0 And C.Offset(0, 2).Value = 0 And C.Offset(0, 3).Value = 0 Then
Range(C.Offset(0, 0), C.Offset(0, 0)).Select
Selection.EntireRow.Delete
Set C = ActiveCell
Else
Set C = C.Offset(1, 0)
End If
Loop
 
L

levanduyet

Welcome
16/10/04
535
11
18
HCM
my.opera.com
#7
Không hiểu!

Longlv nói:
Còn em lại gặp một vấn đề khác với Dynamic Range, nếu dùng Dynamic Range với ADO thì chương trình không chạy được, chỉ khi dùng Range cố định thì lại chạy bình thường. Không biết mọi người có gặp trường hợp như em không?
Tôi không nghĩ như vậy. Trường hợp cụ thể của bạn là như thế nào?
Thân,
LVD
 
H

heroic

Thành viên sơ cấp
11/11/04
56
0
0
vn
#8
Name define dữ liệu kết quả là con số 0?

Qua tham khảo các ý kiến thì mình cũng chưa thấy hiệu quả, không biết Bác Duyệt có cách nào tối ưu? Qua 1 ngày thứ bảy nghiên cứu ghi macro mình cũng đã tìm ra cách range vùng động thay đổi của dữ liệu là mình dùng thêm một biến đếm total dùng hàm countif() và khi dữ liệu tràn xuống dòng bao nhiêu thì countif đã đếm cho mình biết số dòng cần range tới để đặt tên. Nhưng không hiểu sao kiểm tra khi dùng tên define này để cho lên listbox thì không hiện ra cái gì cả mà chỉ là con số 0 nếu ta gán trên một cell bằng cái tên đó!

Vậy vấn đề ở đây là vì sao lại thế? AmatorVBA lại là Amtor!
 
V

VanHao

Thành viên thân thiết
18/12/05
70
2
6
Go Vap
#9
VanHao nói:
Bạn tạo một Macro đặt tên cho các vùng. Mỗi lần trích lọc dữ liệu chỉ cần Refresh lại là xong ngay. Nếu làm theo cách này thì chỉ những vùng dữ liệu nào phát sinh thì mới được đặt tên. Bạn dùng Macro Recorder ghi lại thao tác đặt tên cho vùng rồi chỉnh sửa chút đỉnh, sau đó tạo nút gán cho cái Macro vửa tạo trên một sheet nào đó. Chúc bạn thành công.
Bạn làm thử một Macro đặt tên thử xem.
Ví dụ đặt tên cho cột Mã hàng, cột này là cột A , mã bắt đầu từ dòng 2, sheet là DMHH
Từ một sheet khác Mở Record New Macro đặt tên là Datten và bắt đầu thao tác
Chọn sheet DMHH
Chọn ô A2
Nhấn tổ hợp phím Ctrl + Shift + phím mủi tên đi xuống ( Down Arrow )
Chọn insert /Name /Define
Trong hộp Name in workbook nhập vào mahang, sau đó Enter
Click vào nút Stop Recording để ngừng Macro
Mở Visual Basic Editor vào module 1 sẽ thấy đoạn code

Sheets("DMHH").Select
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Names.Add Name:="mahang", RefersToR1C1:="=DMHH!R2C1:R3C1"

Thay đoạn "=DMHH!R2C1:R3C1" bằng Selection và nó như vầy
ActiveWorkbook.Names.Add Name:="mahang", RefersToR1C1:=Selection
Trở ra ngoài bảng tính tạo một nút gán Macro Datten và có thể sử dụng
Bạn áp dụng cách này cho tất cả các tên khác vào chung MAcro này là có thể an tâm sử dụng.
Tuy nhiên không để dòng trống chen vào giữa vùng dữ liệu, vì sao thì chắc bạn cũng hiểu phải không. Chúc thành công
 
HongViet

HongViet

Thành viên thân thiết
10/11/05
286
10
18
Đà nẵng
#10
Nhờ VanHao góp í cho cách sau:

Tôi đặt tên cho dãy AA với tên là MaHang; Nhưng trong hộp Refers tôi nhập hàm:
=OFFSET($A$2; 0; 0 ; SoRecord(A2:A99); 1)
Ở đây SoRecord(A2:A99) là hàm tự tạo để đếm số reccords có trong cột AA
Nhờ bạn nhận xét xem nó sẽ làm nhanh hay chậm như thế nào để còn rút kinh nghiệm?!
Xin cảm ơn trước
Function SoRecord(rRange As Range) As Integer
Dim ii As Integer: Dim SRange As Range
Application.ScreenUpdating = 0
Sheets("S2").Select
For Each SRange In rRange
If SRange.Value <> "" Then
ii = 1 + ii
Else
Exit For
End If
Next SRange
Application.ScreenUpdating = -1
SoRecord = ii
End Function

(Chị HanDung107 thông cảm nha vì tại Excel & VBA mà!)
 
Sửa lần cuối:
H

heroic

Thành viên sơ cấp
11/11/04
56
0
0
vn
#11
Đã là đáp án cuối cùng tối ưu cho đề tài này chưa?

=INDIRECT("DMHH!$A$2:"&ADDRESS(1+DMHH!$F$1,3))

Qua tham khảo ví dụ SimpleInventory của Adam trần thì mình thấy theo cách này thì khi chúng ta dùng vào combox, listbox rất nhanh và chính xác, nhưng chỉ còn một điểm là khi thay đổi thêm một mặt hàng thì phải đóng lại rồi lưu mở lại thì mới đảm bảo mặt hàng đã được cập nhật. Vấn đề là phải refress lại ngay khi nhập thêm mã hàng mới bằng VBA nữa thôi.
Mọi người tham khảo rồi cho ý kiến, Mong Adam tran hoàn thiện sớm ví dụ trên giao diện đẹp chia sẻ cho mọi người cùng học tập.
 
L

Longlv

Thành viên sơ cấp
21/10/05
44
1
8
41
Hanoi
#12
Nếu dùng các Dynamic Range bằng hàm OFFSET hay INDIRECT kết hợp với việc sử dụng Sumproduct với dữ liệu khoảng 6000 bản ghi trở lên thường thì rất chậm. Không biết có ai gặp phải trường hợp này không?
 
H

heroic

Thành viên sơ cấp
11/11/04
56
0
0
vn
#13
Mình đã thử 10.000 dòng

Mình đã thử 10.000 dòng trong ví dụ SimpleInventory mà có thấy chậm đâu>? Bạn thử coi áp dụng xem, nếu với cách đó chưa tối ưu thì các Pro sẽ chỉ giáo cho!
 
Đào Việt Cường

Đào Việt Cường

Moderator
22/11/05
400
3
18
Khánh Hòa
#14
HongViet nói:
Tôi đặt tên cho dãy AA với tên là MaHang; Nhưng trong hộp Refers tôi nhập hàm:
=OFFSET($A$2; 0; 0 ; SoRecord(A2:A99); 1)
Ở đây SoRecord(A2:A99) là hàm tự tạo để đếm số reccords có trong cột AA
Nhờ bạn nhận xét xem nó sẽ làm nhanh hay chậm như thế nào để còn rút kinh nghiệm?!
Xin cảm ơn trước
Function SoRecord(rRange As Range) As Integer
Dim ii As Integer: Dim SRange As Range
Application.ScreenUpdating = 0
Sheets("S2").Select
For Each SRange In rRange
If SRange.Value <> "" Then
ii = 1 + ii
Else
Exit For
End If
Next SRange
Application.ScreenUpdating = -1
SoRecord = ii
End Function
Dear HongViet,
--------------
Em xin góp ý có được không ạ:
Trước hết em có nhận xét là hàm SoRecord của bác chậm khủng khiếp luôn. Từ trước nay em cực kỳ hạn chế việc sử dụng vòng lặp trên Excel. Đặc biệt chúng ta sử dụng vòng lặp để Edit hay Uppdate dữ liệu thì hàng loạt các biến cố xảy ra. Mặc dù SoRecord chỉ là read only nhưng sẽ dẫn chúng ta rơi vào tình trạng lạm dụng VBA.
Mặt khác các hàm tự tạo là hàm chưa được biên dịch sang ngôn ngữ máy, dù hàm được viết đúng cú pháp đến đâu cũng không nhanh bằng hàm do MS Office cung cấp sẵn. Ở đây, em hiểu SoRecord chính là hàm COUNTBLANK(<Range>). Hàm này cũng cho biết số ô trống (blank) trong <Range>. Việc xây dựng một hàm để thay thế hàm COUNTBLANK là không cần thiết nữa.
Em cực lực phản đối việc lạm dụng VBA để thực hiện các công việc không cần thiết đến mức như thế. Hãy ứng dụng VBA vào các công việc phức tạp để nó thực sự phát huy thế mạnh của nó.
 
L

levanduyet

Welcome
16/10/04
535
11
18
HCM
my.opera.com
#15
Longlv nói:
Nếu dùng các Dynamic Range bằng hàm OFFSET hay INDIRECT kết hợp với việc sử dụng Sumproduct với dữ liệu khoảng 6000 bản ghi trở lên thường thì rất chậm. Không biết có ai gặp phải trường hợp này không?
Vấn đề chậm còn tùy thuộc vào máy của bạn, số tập tin bạn đang mở,...
Thân,

LVD
 
Đào Việt Cường

Đào Việt Cường

Moderator
22/11/05
400
3
18
Khánh Hòa
#16
Dear all,
--------
Về vấn đề chậm máy khi sử dụng các tham chiếu động, theo em điều này đương nhiên có thể xảy ra. Chỉ cần hiểu một cách đơn giản là "Excel phải calculate (tính toán) mọi thứ". Điều này có thể giải thích tại sao workbook có nhiều công thức sẽ "nặng nề" hơn workbook có ít công thức. Công thức càng nhiều thì quá trình tính toán càng lâu, việc sử dụng các tham chiếu tĩnh đương nhiên sẽ nhanh hơn so với việc sử dụng tham chiếu động. Tuy nhiên tối ưu hay không lại phải xét nhiều yếu tố cũng như các tiện ích mà nó đem lại. Theo em, trước khi định nghĩa một Dynamic Reference cần quan tâm đến vấn đề sau:
- Khả năng tự động mở rộng tham chiếu có thực sự cần thiết không
-Có thường xuyên làm việc với Dynamic Name hay không?
- So với tốc độ tính toán hiện thời, việc áp dụng các tham chiếu động có làm ảnh hưởng đáng kể hay không?
- (Bổ sung vào đây)
Trong chủ đề này, em thấy có hai vấn đề đặt ra rất hay là:
Heroic nói:
1.Em đang gặp phải vấn đề là khi range một vùng mặt hàng rồi đặt tên thường phải để dư ra VD: A1:B50 như vậy thì khi hiện lisbox hay listview thì mới có đủ mặt hàng nếu mặt hàng ít hơn là 10, hay lớn hơn 50 thì không tối ưu cho file
Longlv nói:
2. Nếu dùng Dynamic Range với ADO thì chương trình không chạy được
Vấn đề 1 (của Heroic) có thể mở rộng ra là: Làm sao ta có thể đặt được tham chiếu cho một "vùng không liên tục", nghĩa là dữ liệu rời rạc. Ví dụ, tạo danh sách mã mặt hàng theo nhóm mặt hàng (tất nhiên việc sắp xếp không theo nhóm mặt hàng là bắt buộc)
Vấn đề thứ 2 (của Longlv) đã có lần được chị HanDung107 đề cập: Các Public Reference và Private Reference. Theo em hiểu thì khi chúng ta sử dụng Dynamic Reference là chúng ta đang sử dụng Private Reference. Nghĩa là mọi kết nối từ bên ngoài bằng ADODB tới các Dynamic Name đều không thực hiện được. Em chưa tìm được lời giải thích nào cho câu hỏi:
"- Vì sao các Dynamic Name không xuất hiện trong Name Box?".
Cả hai vấn đề trên thật đáng bàn, em rất mong học hỏi từ anh chị và các bạn!
 
Sửa lần cuối:
T

Tuanktcdcn

Lão già ham vui
18/6/04
548
51
28
41
Hà Nội
www.bluesofts.net
#17
Làm dãy động chỉ tốt khi khối lượng tính toán dùng tới Name đó ít, hoặc sự biến động trong về số dòng trong Name đó cũng ít.

Các bạn mà copy công thức trên cho khoảng cho khoảng 200 dòng sẽ phải chờ một chút, một số giá trị trong khoảng đó mà thay đổi thì cũng lại phải chờ.

Tùy vào tình hình cụ thể, các bạn nên dùng phương án khác! Vì với khối dữ liệu lớn thì không ổn chút nào.
Tại sao mà hay có người nói dữ liệu trên file Excel lớn -> máy chạy chậm. Vì nó update công thức, mà công thức lại theo dữ liệu. Nên người ta hay khuyên dùng Excel mục đích cho bảng tính nhỏ. Mình nghĩ nếu khéo dùng thì vẫn ok.

VBA chỉ nên giải quyết một công việc rồi thoát ngay. Nếu cho một hàm VBA trên Sheet mà dùng cho nhiều vùng dữ liệu động thì không nên chút nào. Các bạn cứ thử copy các công thức trên cho chỉ 200 ô thôi sẽ thấy tai hại như thế nào.
 
V

VanHao

Thành viên thân thiết
18/12/05
70
2
6
Go Vap
#18
HongViet nói:
Tôi đặt tên cho dãy AA với tên là MaHang; Nhưng trong hộp Refers tôi nhập hàm:
=OFFSET($A$2; 0; 0 ; SoRecord(A2:A99); 1)
Ở đây SoRecord(A2:A99) là hàm tự tạo để đếm số reccords có trong cột AA
Nhờ bạn nhận xét xem nó sẽ làm nhanh hay chậm như thế nào để còn rút kinh nghiệm?!
Xin cảm ơn trước
Function SoRecord(rRange As Range) As Integer
Dim ii As Integer: Dim SRange As Range
Application.ScreenUpdating = 0
Sheets("S2").Select
For Each SRange In rRange
If SRange.Value <> "" Then
ii = 1 + ii
Else
Exit For
End If
Next SRange
Application.ScreenUpdating = -1
SoRecord = ii
End Function

(Chị HanDung107 thông cảm nha vì tại Excel & VBA mà!)
Bạn Hồng Việt mến! Góp ý về hàm tự tạo của bạn thì bạn Đào Việt Cường có nói rồi. Còn việc xử lý nhanh chậm của excel thì bạn Tuancdktcn cũng có giải thích và tôi hoàn toàn đồng ý như vậy. Trong vấn đề này,tôi chỉ có thể chia xẻ kinh nghiệm như vầy. Tôi có bảng danh mục hàng hóa (full) và từ bảng này tôi tạo ra bảng tổng hợp nhập xuất tồn ( chỉ lấy mặt hàng có phát sinh ). Dựa vào bảng tổng hợp tôi đặt tên mahang ( cột mã hàng trên bảng tổng hợp NXT) và tôi dùng tên mahang cho combo box trên sổ chi tiết hàng hóa. Như vậy, mahang trên combo box của tôi luôn động vì không lấy trên DMHH và sự chọn lựa sẽ đầy đủ và loại trừ những mã hàng đã hết hoặc không phát sinh.
 
H

heroic

Thành viên sơ cấp
11/11/04
56
0
0
vn
#19
Nhanh hay chậm VBA giải quyết tốt hơn không?

Không biết ý của mình hiểu có đúng không vì khi để công thức quá nhiều trên bảng tính sẽ chậm và bị phình to kích thước. Nên mình muốn khi thực hiện một thao tác nào đó trên bảng tính thì dùng VBA để xử lý Range, definame, ..qua bên listbox.. thì kích thước sẽ đỡ hơn và xử lý nhanh hơn?
bởi nếu ta range vùng cố định, kích thước file sẽ lớn và dùng nhiều name define trong vùng range thừa thì xử lý sẽ không tối ưu.
 
T

Tuanktcdcn

Lão già ham vui
18/6/04
548
51
28
41
Hà Nội
www.bluesofts.net
#20
heroic nói:
Không biết ý của mình hiểu có đúng không vì khi để công thức quá nhiều trên bảng tính sẽ chậm và bị phình to kích thước. Nên mình muốn khi thực hiện một thao tác nào đó trên bảng tính thì dùng VBA để xử lý Range, definame, ..qua bên listbox.. thì kích thước sẽ đỡ hơn và xử lý nhanh hơn?
bởi nếu ta range vùng cố định, kích thước file sẽ lớn và dùng nhiều name define trong vùng range thừa thì xử lý sẽ không tối ưu.
Mình vẫn thường làm như thế này


Private Sub Workbook_Open()
DefineName
End Sub


Sub ListTable()

If Haschange Then
UpdateName
End if

...Post to Listbox/Combo....

End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
DeleteName
End Sub
 

Thành viên trực tuyến

  • daongocnam0603
  • xediengiatot

Xem nhiều