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

Thảo luận trong 'Ứng dụng Excel' bắt đầu bởi heroic, 10 Tháng ba 2006.

9,903 lượt xem

  1. heroic

    heroic Thành viên hoạt động

    Bài viết:
    56
    Đã được thích:
    0
    Nơi ở:
    vn
    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?
     
    #1
  2. handung107

    handung107 Thành viên thân thiết

    Bài viết:
    576
    Đã được thích:
    13
    Nơi ở:
    VN
    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
     
    #2
  3. levanduyet

    levanduyet Welcome

    Bài viết:
    535
    Đã được thích:
    11
    Giới tính:
    Nam
    Nơi ở:
    HCM
    Hay hay

    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
     
    #3
  4. Longlv

    Longlv Thành viên hoạt động

    Bài viết:
    44
    Đã được thích:
    1
    Nơi ở:
    Hanoi
    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.
     
    #4
  5. VanHao

    VanHao Thành viên thân thiết

    Bài viết:
    70
    Đã được thích:
    2
    Nơi ở:
    Go Vap
    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.
     
    #5
  6. VanHao

    VanHao Thành viên thân thiết

    Bài viết:
    70
    Đã được thích:
    2
    Nơi ở:
    Go Vap
    Đâ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
     
    #6
  7. levanduyet

    levanduyet Welcome

    Bài viết:
    535
    Đã được thích:
    11
    Giới tính:
    Nam
    Nơi ở:
    HCM
    Không hiểu!

    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
     
    #7
  8. heroic

    heroic Thành viên hoạt động

    Bài viết:
    56
    Đã được thích:
    0
    Nơi ở:
    vn
    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!
     
    #8
  9. VanHao

    VanHao Thành viên thân thiết

    Bài viết:
    70
    Đã được thích:
    2
    Nơi ở:
    Go Vap
    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
     
    #9
  10. HongViet

    HongViet Thành viên thân thiết

    Bài viết:
    286
    Đã được thích:
    10
    Nơi ở:
    Đà nẵng
    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à!)
     
    Last edited: 14 Tháng ba 2006
    #10
  11. heroic

    heroic Thành viên hoạt động

    Bài viết:
    56
    Đã được thích:
    0
    Nơi ở:
    vn
    Đã 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.
     
    #11
  12. Longlv

    Longlv Thành viên hoạt động

    Bài viết:
    44
    Đã được thích:
    1
    Nơi ở:
    Hanoi
    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?
     
    #12
  13. heroic

    heroic Thành viên hoạt động

    Bài viết:
    56
    Đã được thích:
    0
    Nơi ở:
    vn
    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!
     
    #13
  14. Đào Việt Cường

    Đào Việt Cường Moderator

    Bài viết:
    400
    Đã được thích:
    3
    Giới tính:
    Nam
    Nơi ở:
    Khánh Hòa
    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ó.
     
    #14
  15. levanduyet

    levanduyet Welcome

    Bài viết:
    535
    Đã được thích:
    11
    Giới tính:
    Nam
    Nơi ở:
    HCM
    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
     
    #15
  16. Đào Việt Cường

    Đào Việt Cường Moderator

    Bài viết:
    400
    Đã được thích:
    3
    Giới tính:
    Nam
    Nơi ở:
    Khánh Hòa
    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à:
    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!
     
    Last edited: 14 Tháng ba 2006
    #16
  17. Tuanktcdcn

    Tuanktcdcn Lão già ham vui

    Bài viết:
    548
    Đã được thích:
    50
    Nơi ở:
    Hà Nội
    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.
     
    #17
  18. VanHao

    VanHao Thành viên thân thiết

    Bài viết:
    70
    Đã được thích:
    2
    Nơi ở:
    Go Vap
    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.
     
    #18
  19. heroic

    heroic Thành viên hoạt động

    Bài viết:
    56
    Đã được thích:
    0
    Nơi ở:
    vn
    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.
     
    #19
  20. Tuanktcdcn

    Tuanktcdcn Lão già ham vui

    Bài viết:
    548
    Đã được thích:
    50
    Nơi ở:
    Hà Nội
    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
     
    #20

Chia sẻ trang này