Hướng dẫn từng bước sử dụng VBA để triển khai lọc dữ liệu cho báo cáo chi tiết

Bạn có bao giờ đặt ra những vấn đề sau đây trong công việc không?

  • Cách thực hiện báo cáo chi tiết như thế nào?
  • Bạn làm sao có thể rút dữ liệu từ bảng với các tiêu chí cụ thể?
  • Làm cách nào để lấy dữ liệu phù hợp với điều kiện và đưa nó qua bảng mới?

Bài viết này sẽ cung cấp câu trả lời cho những câu hỏi trên và cả một phương pháp mới mẻ sử dụng VBA nhằm lọc dữ liệu và dẫn chúng vào Báo cáo chi tiết.

* Đặt ra nhiệm vụ: Từ bảng dưới đây, hãy thu thập dữ liệu ở Bảng chi tiết dựa trên điều kiện thay đổi tại các ô từ I2:I4 (Thời gian và nhà cung cấp)

Bước 1: Ghi lại macro cho các thao tác

Trên tab Developer, bạn hãy chọn Record Macro

  • Thao tác đầu tiên: Áp dụng Data / Filter lên bảng dữ liệu
  • Thao tác thứ hai: Lựa chọn lọc dữ liệu tại cột Nhà cung cấp dựa theo tên được đưa ra ở ô I4
  • Thao tác thứ ba: Lọc dữ liệu ngày trong cột Ngày theo dữ liệu nhận được từ ô I2 và I3

Bước 2: Kiểm tra macro được ghi lại

Xem và phân tích nội dung macro đã được ghi từ cửa sổ VBA

Macro ghi lại việc lựa chọn vùng từ A2 đến F2, sau đó kích hoạt Auto filter

Lệnh macro sau đây là việc lọc dữ liệu tại cột NCC với điều kiện “Anh Tuấn”. Do VBA không hoàn toàn hỗ trợ tiếng Việt nên xuất hiện dấu ?

Macro cũng lọc dữ liệu ngày với tiêu chí là sau ngày 01/10/2017 và trước hoặc vào ngày 31/10/2017

Bước 3: Làm gọn macro để sử dụng linh hoạt hơn

Cần liên kết điều kiện lọc từ VBA với khu vực điều kiện trong Bảng chi tiết. Như vậy, mỗi khi dữ liệu thay đổi trong Bảng chi tiết, macro sẽ tự động lọc theo dữ liệu mới.

Đặc biệt chú ý đến phần điều kiện (Criteria1, Criteria2) trong VBA

* Câu lệnh lọc NCC

ActiveSheet.Range(“$A$2:$F$47″).AutoFilter Field:=2, Criteria1:=”Anh Tu?n”

Khi đó thay “Anh Tu?n” thành giá trị tại ô I4 (dòng 4, cột 9, sheet 1) trong sheet1:

* Câu lệnh lọc Ngày

ActiveSheet.Range(“$A$2:$F$47”).AutoFilter Field:=1, Criteria1:= _
“>=01/10/2017″, Operator:=xlAnd, Criteria2:=”<=31/10/2017”

Chúng ta cũng thay đổi “>01/10/2017” và “<=31/10/2017" tương ứng.

Có thể biến đổi ngày tháng trong Excel từ dạng số qua CLng(…)

* Rút gọn và hoàn thiện mã VBA

Range(“A2:F2”).Select + Selection.AutoFilter = Range(“A2:F2”).AutoFilter

Dòng Range(“B2”).Select có thể loại bỏ

Bước 4: Sao chép dữ liệu và dán qua bảng chi tiết và những chỉnh sửa cuối cùng trong code

Sau khi đã lọc xong bảng dữ liệu, sao chép toàn bộ và dán nó vào bảng chi tiết.

Đây là code cho bước này: (Có thể thực hiện record macro và chỉnh sửa lại code)

Vì bảng chi tiết chỉ cần thông tin Tên hàng, số lượng, đơn giá, thành tiền nên sẽ chỉ lấy dữ liệu từ cột C đến F, từ ô C3 đến F47. Chỉ sao chép những dữ liệu sau khi lọc.

Dán dữ liệu: Dán vào bảng chi tiết,

Từ ô H6 trở đi, hãy nhớ chỉ nên dán dữ liệu kiểu Value (giá trị)

Ở cùng trong bước hoàn tất dán dữ liệu, chúng ta sẽ gỡ bỏ Copy và Filter qua hai dòng code sau đây:

Application.CutCopyMode = False – Đây là cách loại bỏ chế độ Cut Copy trên Excel (Excel giữ lại phạm vi được Cut/Copy và thể hiện bằng viền nhấp nháy. Khi không cần nữa, bạn có thể hủy chúng)

Range(“A2:F2”).AutoFilter – Lệnh này giúp hủy bỏ chức năng Filter. Bạn nên lặp lại việc này mỗi khi muốn tắt chức năng Filter (để trả lại trạng thái như khi chưa kích hoạt Filter)

Bước 5: Kiểm tra mã lệnh bằng phím F8

Sau khi mã lệnh trong VBA được viết xong, sử dụng phím F8 để thử nghiệm và xem chúng hoạt động như thế nào

Nếu mã lệnh hoạt động chính xác, kết quả sẽ hiển thị như sau:

Bước 6: Áp dụng Macro vào sự kiện thay đổi điều kiện tại khu vực I2:I4

Trong giao diện VBA, bạn hãy thực hiện nháy đúp vào Sheet1

Trên cửa sổ VBA làm việc với Sheet1, chọn sự kiện Change (Thay đổi). Sự kiện này xảy ra khi có thay đổi trong Sheet.

Chú ý rằng, chỉ khi có sự thay đổi ở vùng I2:I4 thì nó mới ảnh hưởng đến báo cáo chi tiết, vì vậy, ta sẽ chỉ theo dõi thay đổi trong phạm vi này.

Các bước thực hiện như dưới đây:

Câu lệnh “If Not Application.Intersect(Range(“I2:I4”), Range(Target.Address)) Is Nothing Then” nghĩa là nếu có bất kỳ thay đổi nào xảy ra trong khu vực I2:I4 thì sẽ…

Để cập nhật nội dung của báo cáo chi tiết khi có thay đổi ở I2:I4, ta sẽ gọi Macro đã được hoàn thành ở bước trước.

* Thêm vào:

Vì điều kiện khác nhau sẽ sinh ra kết quả không giống nhau, chúng ta cần làm sạch bảng chi tiết trước khi cập nhật dữ liệu mới.

Bằng cách thêm dòng Code xoá dữ liệu vào đầu Macro:

range(“H6:K100”).ClearContents – Lệnh này sẽ giúp loại bỏ dữ liệu trong vùng H6:K100, là nơi chứa kết quả dữ liệu của bảng chi tiết

Kết luận

Bài học từ bài viết này bao gồm:

  • Cách thiết lập và chỉnh sửa Macro từ quá trình Record
  • Kỹ năng đọc và hiểu code trong VBA
  • Phương pháp liên kết Macro với các sự kiện trong Sheet (như thay đổi giá trị)
  • Cách thức xây dựng logic trong các câu lệnh VBA

Những kỹ năng này sẽ rất hữu ích trong công việc thực tế và ứng dụng rộng rãi trong Excel.

Bài viết này được hy vọng như một bước đệm giúp bạn tiếp cận với VBA một cách dễ dàng và tiến bộ dần qua việc tự động hóa các tác vụ bằng Excel.

Xin chân thành cảm ơn các bạn đã theo dõi bài viết.

 

Trương Thành Tài

    [submission_id id-lien-he]

    0
      0
      Đơn hàng
      Đơn hàng trốngQuay lại Shop