Trang 1/3 1 2 3 cuốicuối
Hiển thị kết quả tìm kiếm từ 1 đến 10 trên tổng số: 29
  1. #1
    Tham gia ngày
    05 2006
    Nơi Cư Ngụ
    TP HCM
    Bài gởi
    1,470
    Cảm ơn
    1,222
    Được cảm ơn 16,380 lần trong 1,309 bài viết

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



    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:



    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:



    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. #2
    Tham gia ngày
    06 2006
    Nơi Cư Ngụ
    Nha Trang
    Bài gởi
    528
    Cảm ơn
    185
    Được cảm ơn 741 lần trong 289 bài viết

    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:
    Sub Range_AdvancedFilter (rgFilterRange as Range, rgCriteriaRange as Range, rgCopytoRange as Range)
    rgFilterRange.AdvancedFilter xlFilterCopy, rgCriteriaRange, rgCopytoRange
    End Sub
    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!
    thay đổi nội dung bởi: Đào Việt Cường, 20-06-06 lúc 01:05 PM

  3. Có 13 thành viên cảm ơn Đào Việt Cường về bài viết này:


  4. #3
    Tham gia ngày
    06 2006
    Bài gởi
    3,257
    Cảm ơn
    5,988
    Được cảm ơn 6,292 lần trong 2,740 bài viết

    Arrow =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:
    Code:
    Sub LocNhSu() ' Keyboard Shortcut: Ctrl+Shift +N
     Dim SChu As String  ' Loc Du Lieu & Copy Sang Sheet 'S0"
         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
    End Sub
    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!
    thay đổi nội dung bởi: SA_DQ, 08-12-06 lúc 02:08 PM Lý do: Thêm [code] & [/code]

  5. Có 8 thành viên cảm ơn SA_DQ về bài viết này:


  6. #4
    Tham gia ngày
    06 2006
    Nơi Cư Ngụ
    Nha Trang
    Bài gởi
    528
    Cảm ơn
    185
    Được cảm ơn 741 lần trong 289 bài viết
    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":

    Public Sub AutoFill_ByForNext()
    Dim rgSothutu As Range
    Dim i As Long
    Set rgSothutu = Range("A10:A30010")
    For i = 1 To 30000
    rgSothutu.Cells(i) = i
    Next
    End Sub
    và thủ tục:

    Public Sub AutoFill_ByFormula()
    Dim rgSothutu As Range
    Set rgSothutu = Range("A10:A3009")
    rgSothutu.Formula ="=Row()-9"
    End Sub
    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ị!

  7. Có 8 thành viên cảm ơn Đào Việt Cường về bài viết này:


  8. #5
    Tham gia ngày
    06 2006
    Bài gởi
    56
    Cảm ơn
    7
    Được cảm ơn 201 lần trong 37 bài viết
    Trích Nguyên văn bởi Đào Việt Cường
    [/COLOR]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ị!
    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ể.

  9. Có 5 thành viên cảm ơn workman về bài viết này:


  10. #6
    Tham gia ngày
    04 2007
    Bài gởi
    29
    Cảm ơn
    24
    Được cảm ơn 12 lần trong 5 bài viết
    Trích Nguyên văn bởi Đào Việt Cường
    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!
    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.

  11. Có 2 thành viên cảm ơn cuonghp về bài viết này:


  12. #7
    Tham gia ngày
    07 2007
    Bài gởi
    14
    Cảm ơn
    3
    Được cảm ơn 4 lần trong 3 bài viết

    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 ý),
    thay đổi nội dung bởi: binhlt, 17-08-07 lúc 01:31 PM

  13. Có 2 thành viên cảm ơn binhlt về bài viết này:


  14. #8
    Tham gia ngày
    08 2006
    Bài gởi
    3,815
    Cảm ơn
    4,143
    Được cảm ơn 4,433 lần trong 2,271 bài viết
    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.

  15. #9
    Trích Nguyên văn bởi binhlt
    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 ý),
    Từ CSDL của bạn, hứng lên tôi tạo một loạt sổ luôn.
    Tập tin đính kèm Tập tin đính kèm

  16. Có 6 thành viên cảm ơn Nguyễn Duy Tuân về bài viết này:


  17. #10
    Tham gia ngày
    07 2007
    Bài gởi
    14
    Cảm ơn
    3
    Được cảm ơn 4 lần trong 3 bài viết

    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
    thay đổi nội dung bởi: binhlt, 17-08-07 lúc 01:31 PM

  18. Thành viên sau cảm ơn binhlt về bài viết này:


Trang 1/3 1 2 3 cuốicuối

Thông tin về chủ đề này

Users Browsing this Thread

Hiện có 1 người đang xem đề tài này. (0 thành viên và 1 khách)

Đề tài tương tự

  1. Nhờ viết Macro tách dòng trong Excel
    Viết bởi syquan trong chuyên mục Lập Trình với Excel
    Trả lời: 5
    Bài mới gởi: 18-04-14, 06:47 PM
  2. Hỏi: Cách tạo Macro trong excel?
    Viết bởi kamelot trong chuyên mục Hỏi đáp về VBA - Macro
    Trả lời: 20
    Bài mới gởi: 01-07-13, 12:14 AM
  3. Việc không hiên record macro trong ribbon Developer trong excel 2007
    Viết bởi tuanaitvietnam trong chuyên mục Hàm và công thức Excel
    Trả lời: 2
    Bài mới gởi: 20-05-13, 12:07 AM
  4. Hỏi về sự khác nhau giữa Macro, code và hàm trong excel. Excel và VBA
    Viết bởi vdtam_08 trong chuyên mục Lập Trình với Excel
    Trả lời: 4
    Bài mới gởi: 12-07-11, 10:22 PM
  5. Viết Macro đọc 1 cột dữ liệu trong excel
    Viết bởi haingo08tlt trong chuyên mục Hỏi đáp về VBA - Macro
    Trả lời: 8
    Bài mới gởi: 25-02-10, 12:04 PM

Bookmarks

Bookmarks

Quyền Sử Dụng Ở Diễn Ðàn

  • Bạn không thể đăng đề tài mới
  • Bạn không thể đăng trả lời
  • Bạn không thể đăng file đính kèm.
  • Bạn không thể sửa bài viết.
  •  

Mudim v0.8 Tắt VNI Telex Viqr Tổng hợp
Chính tả Bỏ dấu kiểu mới  [Bật/Tắt (F9)]