Đăng ký học Excel và VBA cùng GPE tháng 11 - TPHCM

Đăng ký học Excel và phân tích số liệu cùng GPE tháng 12 - TPHCM

Mua sách "VBA trong Excel - Cải thiện và tăng tốc" tái bản

Dùng Macro lọc dữ liệu bảng trong Excel

Thảo luận trong 'Excel và Kế Toán' bắt đầu bởi handung107, 20 Tháng sáu 2006.

  1. handung107

    handung107 Administrator Staff Member



    Giả sử bạn có một bảng chứa danh sách lý lịch nhân viên (hoặc bất cứ danh sách gì như ở dưới):

    Sheet1:

    [​IMG]

    Và bạn muốn chỉ lấy lý lịch những nhân viên có mã số 0252, 0253, .... Nếu bạn chọn từng người trong danh sách (có thể là vài trăm đến vài nghìn nhân viên) thì sẽ rất mất thời gian.
    Để thực hiện nhanh, ta sẽ tạo macro lọc những nhân viên cần lấy như sau:
    - Tạo vòng lặp từ đầu tới cuối của danh sách mã số nhân viên, lấy từng mã số NV, sau đó tạo tiếp vòng lặp để duyệt từ đầu tới cuối danh sách lý lịch, khi tìm thấy thì cho ngừng vòng lặp và tạo tiếp một vòng lặp khác duyệt qua các cột của mẫu tin tìm thấy để chép (copy) sang Sheet2.
    - Thực hiện:
    + Tạo một sheet để chứa kết quả lọc, (Sheet2)
    + Tạo một sheet chứa mã NV:

    Sheet3:

    [​IMG]

    Trong Excel, vào menu Tools\Macro\Visual Basic Editor:
    Trong cửa sổ bên trái, nhấn đúp vào VBAProject để bung các nhánh con trong đó ra. Bạn sẽ thấy có ba sheet mà mình tạo ở trên, nhấn đúp vào một sheet bất kỳ để mở cửa sổ soạn thảo code của sheet đó, sau đó thực hiện soạn mã như ở dưới:

    Sub CopyRecord()
    Dim i, j, k, n, n1 As Integer
    Dim temp
    'n số mẫu tin của sheet 3
    'n1 số mẫu tin của sheet 1
    n = 100
    n1 = 1500
    'vòng lặp duyệt từ mẫu tin thứ 2 đến mẫu tin cuối cùng của sheet chứa mã nhân viên(trong ví dụ là 100)
    For i = 2 To n
    'lưu giái trị của hàng thứ i, cột thứ 2 của sheet3 vào biến temp (trong ví dụ là mã NV).
    'như vậy biến temp sẽ lần lượt chứa mã số nhân viên của từng người có trong danh sách
    temp = Sheet3.Cells(i, 2)
    'vòng lặp duyệt từ mẫu tin thứ 2 đến mẫu tin cuối cùng của sheet chưa danh sách nhân viên
    For j = 2 To n1
    'kiểm tra nếu mã nhân viên trong Sheet1 (danh sách nhân viên) trùng với biến temp thì tạo tiếp vòng lặp duyệt qua các cột của mẫu tin tìm thấy.
    If temp = Sheet1.Cells(j, 2) Then
    k = 2
    Do While Not IsEmpty(Sheet1.Cells(j, k))
    'Cập nhật các ô vào sheet2
    Sheet2.Cells(i, k) = Sheet1.Cells(j, k)
    k = k + 1
    Loop
    Exit For
    End If
    Next
    Next
    End Sub


    Sau khi đã viết xong code, ta nhấn F5 để chạy macro này, chờ vài giây để macro lọc dữ liệu, sau khi maco thực hiện xong nhiệm vụ, bạn hãy mở Sheet2 ra coi. Cha cha! nó đã copy đầy đủ theo danh sách mã số của bạn.
    Nếu bạn muốn marco tự động chạy thì thực hiện như sau:
    Macro chạy mỗi khi bạn thay đổi dữ liệu trong sheet lý lịch: Chép đoạn mã trên (không chép hàng đầu tiên và cuôi cùng: Sub CopyRecord và End Sub). Nhấn đúp vào Sheet1 để mở cửa sổ code, trong listbox bên trái phía trên của cửa số code, chọn Worksheet, bên phải chọn Change, bạn sẽ thấy như sau:
    Private Sub Worksheet_Change(ByVal Target As Range)
    End Sub
    Dán đoạn mã đã copy vào trong thủ tục này. Bây giờ thì mỗi lần bạn thay đổi dữ liệu trong Sheet1 thì macro sẽ tự động cập nhật lại dữ liệu. Nếu dữ liệu quá nhiều và bạn phải làm việc thường xuyên trên Sheet1 thì không nên dùng cách này vì nó sẽ làm chậm quá trình xử lý của bạn (nhớ xóa đoạn mã cũ sau khi bạn đã copy nó sang sheet khác). Bạn có thể áp dụng cách này để có thể làm được nhiều việc khác nữa.
     
  2. Đào Việt Cường

    Đào Việt Cường Cu Tí sành điệu

    Làm việc với MSE, cần khai thác các tính năng sẵn có!

    Dear handung107,
    -----------------
    Theo em thì phương pháp này không được khuyến khích sử dụng trong MS Excel. Nói cách khác phương pháp này chỉ thích hợp trong lập trình Visual Basic mà không liên quan đến Excel.
    Một cách thông thường mà em vẫn thường làm để in tất cả các sổ cái là tận dụng các phương thức và hành động của các đối tượng trong workbook. Em thấy việc nêu thêm một phương pháp nữa trong việc trích lọc dữ liệu trong MS Excel là cần thiết vì theo em cách mà chị giới thiệu sẽ dẫn người lập trình không khai thác tối đa các đặc trưng cơ bản của MSE - đối tượng mà chúng ta đang làm việc.
    Vẫn bài toán trên, em minh hoạ phương pháp của em thông qua việc sử dụng phương thức Filter hoặc tính năng cao cấp Advandced Filter. Việc lựa chọn 1 trong 2 phương thức này tuỳ thuộc vào số điều kiện trích lọc. Trong bài toán này có hơn một điều kiện lọc nên em chọn phương pháp lọc Advandced Filter:
    Bước 1: Xây dựng vùng điều kiện lọc (Criteria range)
    Điều kiện lọc phải đảm bảo độ chính xác các chỉ tiêu cần lọc. Nghĩa là các cột điều kiện phải thuộc bảng dữ liệu. Trong bài tập này, em có thể sử dụng bảng điều kiện của chị (chỉ cần một cột là Mã NV).
    Bước 2: Trích lọc bằng thủ tục lọc ra Bảng tạm (Temporary sheet)
    Bảng tạm là bảng kết quả của Advanfilter do đó bảng này chứa ít nhất cột điều kiện lọc (Mã NV). Tuỳ thuộc vào thông tin kết xuất mà ta có thể lấy ra bao nhiêu cột trên bảng nguồn. Đôi khi chúng ta có thể đưa luôn kết quả lọc này vào báo cáo.
    Thủ tục lọc được minh hoạ như sau:
    So sánh thủ tục này với một vòng duyệt khoảng vài 1.000 dòng chúng ta sẽ thấy sự khác biệt đáng kể.

    Bước 3: Căn chỉnh báo cáo (Report sheet)
    Đây là công việc đương nhiên phải làm nhưng hầu hết chúng ta coi nhẹ nó. Để in một sổ cái cần thiết phải hiệu chỉnh sao cho dữ liệu luôn sẵn sàng để in. Phương pháp đơn giản nhất là sử dụng MSE Template (*.xlt) để tạo ra các mẫu có sẵn và sử dụng lại.
    Hi vọng đây cũng là một phương pháp giúp cho công việc kế toán của chúng ta tiết kiệm được thời gian hơn - dù chỉ là vài tích tắc!
     
    Lần chỉnh sửa cuối: 20 Tháng sáu 2006
  3. SA_DQ

    SA_DQ Thành viên danh dự

    =DFINDns( Sh1 As Object, Sh2 As Object, Str1 As String, Str2 As String)

    Chắc tác gia bên echip viết không hết ~ gì đã làm! Họ chỉ hé mở 1 phần việc. . . .
    Tôi vừa hoàn thành hàm =DFINDns( Sh1 As Object, Sh2 As Object, Str1 As String, Str2 As String) lọc bảng nhân sự từ 2 sheets:
    Đề bài gồm Sheet1 như trên; Sheet2 chứa các trường dữ liệu như sau:
    QHe( [MaNV], [QH], [HoTen]. . .); trường [QH] gồm B: bố; M: mẹ; Ch: chồng; V: vợ; A: anh, em; C: chị, em; C1, C2, C3. . .: các con. Bàng này quan hệ 1 - nhiều với CSDL chính.
    Yêu cầu nêu ra là nếu nhập Str1 ='Ch' & Str2 = 'V' thì hàm sẽ trả về là bảng dữ liệu liệt kê ~ trường: [MaNV], [Ten], [QH], [HoTen] của ~ người đã có vợ hoặc chồng trong CSDL Sh1
    Đúng như Việt Cường nêu; nên dùng lọc Advance ban đầu để tiết kiệm nguồn lực! Tiện đây nhờ VCường & các bạn góp ý cho Sub này với:
    Mã:
    [B]Sub LocNhSu()[/B] [COLOR="Blue"]' Keyboard Shortcut: Ctrl+Shift +N[/COLOR]
     Dim SChu As String [COLOR="blue"] ' Loc Du Lieu & Copy Sang Sheet 'S0"[/COLOR]
         Application.ScreenUpdating = 0
         Sheets("S0").Select:                    Columns("AA:AG").Select
         Selection.ClearContents
         Application.Goto Reference:="GiaDinh"
        Range("GiaDinh").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("ChGDinh"), Unique:=False
         Range("GiaDinh").Select:                 Selection.Copy
         Sheets("S0").Select:                    Range("AA1").Select
         ActiveSheet.Paste
         Sheets("S1").Select:                    Application.CutCopyMode = False
    On Error Resume Next
        ActiveSheet.ShowAllData
    [B]End Sub[/B]
    Tháng sau mới được phép bà nhà gởi hàm =DFINDns(Sh1 As Object, Sh2 as Object, StrC As String) lên, nếu ai đó có nhu cầu!
     
    Lần chỉnh sửa cuối: 8 Tháng mười hai 2006
  4. Đào Việt Cường

    Đào Việt Cường Cu Tí sành điệu

    Dear SA_DQ,
    ------------
    Trong chủ đề này em muốn nhấn mạnh đến cách thức để xây dựng một thủ tục VBA trên MSE.
    Chúng ta phải thừa nhận rằng việc xây dựng một thủ tục trước hết để giải quyết yêu cầu bài toán và điều quan trọng nữa là cần quan tâm đến tính hiệu quả và tối ưu của giải pháp. Hãy so sánh 2 ví dụ sau đây để thấy rằng lập trình trên Excel không giống như lập trình "không phải với Excel":

    và thủ tục:

    Còn về thủ tục LocNhSu(), em thấy thủ tục này còn mang nặng "màu sắc Macro": Sử dụng nhiều hành động. Chúng ta tối ưu mã lệnh bằng cách hạn chế các hành động Select, Copy-Paste. Ví dụ
    Thay vì:
    Range("GiaDinh").Select
    Selection.Copy
    thì có thể viết thành:
    Range("GiaDinh").Copy
    Thay vì:
    Range("GiaDinh").Copy
    Sheets("S0").Select
    Range("AA1").Select
    ActiveSheet.Paste
    thì có thể viết thành:
    Range("GiaDinh").Copy Sheets("S0").Range("AA1")
    Em có một vài ý kiến nhỏ như vậy, rất mong lĩnh hội thêm các kinh nghiệm từ anh chị!
     
  5. workman

    workman Thành viên danh dự

    Cường thân mến

    Tôi không phải là dân lập trình chuyên nghiệp, nên thường có cách làm hơi khác. Bạn xem giúp xem hộ.

    Thông thường khi ta làm lệnh copy - paste, thì viết lệnh như Cường là ổn lắm rồi. Nhưng thực tế thông thường cái mà ta cần đâu phải là copy-paste, mà là lệnh lấy dữ liệu ở sheet này chuyển sang sheet kia.

    Lúc này ta làm lệnh

    Sheets("S0").Range("AA1") = Range("GiaDinh")

    Lúc này, excel không phải dùng lệnh paste copy cổ điển và tốn bộ nhớ, mà chỉ đơn giản là một lệnh gán giá trị thuần túy.

    Tôi nhận thấy lệnh copy - paste ngoài việc chuyển giá trị còn tham chiếu rất nhiều dữ liệu khác như format, border.., do đó khi làm một vòng lặp nhiều ô, xử lý trên nhiều sheets, thì thời gian chờ đợi sẽ là khá lâu.

    Theo kinh nghiệm của tôi thì việc gán giá trị thay vi dùng lệnh copy - paste sẽ làm cho chương trình chạy nhanh RẤT đáng kể.
     
  6. cuonghp

    cuonghp Thành viên mới

    Bạn có thể up file mẫu để mọi người tham khảo được không? Không phải ai dùng excel cũng biết lập và gọi thủ tục đó cả. Cảm ơn.
     
  7. binhlt

    binhlt Thành viên mới

    Giup minh voi ban oi

    Mình nhờ bạn giup vấn đề này nhé:
    Mình có 1 bảng tính như File đính kèm
    Yêu cầu đưa ra là:
    - Vấn đề thứ nhất: Tạo 1 Form trong đó khi nhập tên LN bao nhiêu đấy (Nhập mã LN ở cột Mã cán bộ) và một nút TIM KIEM thì nó tự động tìm đến LN đó và đưa ra tất cả các thông tin của LN cần tìm và đưa sang một Sheet khác.
    - Vấn đề thứ 2: Lấy dữ liệu 7 ngày gần đây hoặc những ngày kế tiếp của cột (NGAY DEN HAN) và đưa ra một cột mới gọi là cột (Ngày lưu ý),
     
    Lần chỉnh sửa cuối: 17 Tháng tám 2007
  8. ThuNghi

    ThuNghi Hãy cho rồi sẽ nhận!

    LN42001040: LN
    LN42001041: LN
    HOANGMH : ??? lấy mã gì
    Bạn muốn dùng công thức hay VBA.
    Bạn làm 1 report mẫu có những yêu cầu của bạn
    Nếu dùng công thức thì sử dụng index, match và Vlookup hay dùng advance filter.
     
  9. Nguyễn Duy Tuân

    Nguyễn Duy Tuân Nghị Hách

    Từ CSDL của bạn, hứng lên tôi tạo một loạt sổ luôn.
     

    Các file đính kèm:

  10. binhlt

    binhlt Thành viên mới

    binhlt gưi lại File

    Mình gửi lại File mẫu cho các bạn
    Nhờ các bạn giúp
     
    Lần chỉnh sửa cuối: 17 Tháng tám 2007
  11. Nguyễn Duy Tuân

    Nguyễn Duy Tuân Nghị Hách

    Bạn xem kỹ file tôi đã gửi đi, tất cả đã làm rồi, chỉ cần vào các sheet "Lọc..." thay đổi trong ô là báo cáo tự cập nhật.

    Bảng dữ liệu DATA của bạn kể lên đến 40.000 dòng vẫn chạy nhanh thôi.
     
  12. chungocoai

    chungocoai Thành viên mới

    các anh chị cho em nhờ chút với.
    hiện em có 1 thư mục với 30 workbook.(files excel ấy ạ)
    trong mỗi sheets lại có các trường như: HOTEN, LUONG, NGAYCONG, HESO
    nhưng nó sắp xếp lộn xộn, không theo thứ tự nào cả, vì dữ liệu theo hàng ngày nên em nhặt khổ quá.
    bây giờ tạo một báo cáo cho từng người theo tháng mà nhặt mãi không xong.
    anh chị chỉ cho em cách nào để có thể làm 1 files excel để mỗi lần click chuột là em có thể liên kết các file đó lại và đưa ra một báo cáo chi tiết cho từng người.
    em xin chân thành cảm ơn!
     
  13. sealand

    sealand Thành viên gạo cội

    Bạn đưa File mấu cần tổng hợp và 1 vài file dữ liệu (nhớ xóa bớt cho nhẹ) lên thì chắc là có cách tổng hợp . Nghe bạn nói khó hình dung quá.
     
  14. yeudoi

    yeudoi Moderator Staff Member

    Mình nghĩ 30 file mà sắp sếp các trường trong đó không theo thứ tự thì rất khó tạo báo cáo đấy bạn ah.có lẽ chúng ta nên tạo một chủ đề về các cách để lập dữ liệu và đưa vào báo cáo, bởi việc lọc dữ liệu chắc là ai sử dụng excel cũng rất cần. Nhưng mà để có một phương pháp tối ưu để lọc thì không phải ai cũng biết.Vì vậy mong mọi người cùng thảo luận tiếp.
     
  15. nguyenvietviet

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

    Dùng Macro lọc dữ liệu

    Các bạn dùng phương pháp rất hay nhưng phải up ví dụ lên chứ.
    Tôi có một file dùng macro lọc nhưng nó xử lý dữ liệu rất chậm.
    Tôi dùng macro ở sheet "bk" lọc theo 2 điều kiện : Mã tàu và mã tổ của sheet "NKNX".
    File của tôi xử lý rất chậm dữ liêu nhập vào ở sheet " nknx" và lọc ở sheet " BK".
    Mong các bạn xem qua và chỉ giáo giúp tôi. Có cách nào lọc nhanh hơn không?
     
    Lần chỉnh sửa cuối: 22 Tháng tám 2009
  16. ndu96081631

    ndu96081631 Ăn cùng GPE, Ở cùng GPE, Sống cùng GPE Staff Member Super Moderator

    Bạn nói sao chứ tôi mở file, lọc thử thấy chạy vèo vèo
    Có khi nào "chậm" là do bạn mở thêm file khác không ---> vì khi mở 1 file có vấn đề về tính toán thì sẽ ảnh hưởng tốc độ đến tất cả file khac ---> Bạn kiểm tra lại thử
    (cái này sao nhìn giống "sản phẩm" của mình thế nhỉ)
     
  17. nguyenvietviet

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

    Đúng. Nó là sản phẩm của bạn.
    Nhưng nó chạy rất chậm khi nhập nhiều dữ liệu ở sheet " nknx".
    Hay bạn có thể sửa lại: Khong cho nó chạy tự động macro ở sheet "bk" mà thêm nút "Thực hiện" thì nó mới chạy.
    Bạn xem lại nhé!
     
  18. ndu96081631

    ndu96081631 Ăn cùng GPE, Ở cùng GPE, Sống cùng GPE Staff Member Super Moderator

    Vậy có lẻ bạn phải kiểm tra lại máy tính của bạn!
    Tôi đang thử nghiệm với dử liệu giả lập 20.000 dòng vẫn chạy vèo vèo
    Advanced Filter thì không thể chậm được bạn à.. Trừ phi bạn có mở thêm các file khác hoặc máy tính bạn có vấn đề mà thôi!
     
  19. sealand

    sealand Thành viên gạo cội

    Thực sự ban nên xem lại máy tính của bạn. Mình test trênmáy xách tay mà không kịp hiện đồng hồ cát
     
  20. nguyenvietviet

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

    Máy của minh cấu hình thuộc loại cũng khủng đấy! Chậy văn phòng không thành vấn đề.
    A cho mình hỏi chút "ndu":
    Mình muốn chỉ lọc theo mã hàng được không?
    Ví dụ: Cũng là D11 tên hàng là Dây hàn CO2 và Dây hàn trần thì lúc nó lọc tổng thì ra Dây hàn CO2 là 1000 kg và Dây hàn trần là 1000 kg.
    Giờ mình muốn chỉ lọc một D11 thôi nghĩa là chỉ Dây hàn CO2: 1000 kg hoặc Dây hàn trần 1000 kg.
    Mong bạn sớm có câu trả lời.
     

Chia sẻ trang này