Trong công việc hàng ngày, bạn có bao giờ gặp những câu hỏi như:
- Làm thế nào để lập báo cáo chi tiết?
- Cách lấy dữ liệu từ một bảng dựa theo những yêu cầu nhất định?
- Làm thế nào để trích xuất dữ liệu theo điều kiện để đưa sang một bảng khác?
Bài viết này sẽ giúp bạn trả lời những câu hỏi đó và khám phá một cách làm mới rất hay khi sử dụng VBA vào việc lọc dữ liệu và lập báo cáo chi tiết.
Chúng ta có yêu cầu như sau: Dựa vào bảng dưới đây, hãy lấy dữ liệu ở Bảng chi tiết dựa theo điều kiện thay đổi ở các ô từ I2:I4 (Thời gian và nhà cung cấp)
Biểu diễn dữ liệu trong Excel
Bước 1: Ghi macro các thao tác
Tại tab Developer, bạn chọn nút lệnh Record Macro.
Ghi macro trong Excel
- Thao tác 1: Chọn chức năng Data / Filter cho bảng dữ liệu.
- Thao tác 2: Lọc cột Nhà cung cấp theo tên Nhà cung cấp ở ô I4.
- Thao tác 3: Lọc ngày ở cột Ngày theo thông tin ngày ở ô I2 và I3.
Quá trình ghi macro trong Excel
Kỹ năng Excel là rất quan trọng trong công việc. Nếu bạn chưa vững Excel, hãy để tôi giúp bạn, đăng ký khóa học Excel để có nền tảng vững chắc!
Bước 2: Đọc nội dung macro
Mở cửa sổ VBA, chúng ta xem nội dung Macro vừa ghi được.
Range(“A2:F2”).Select
Selection.AutoFilterNội dung này là: Chọn vùng ô từ A2 đến F2, mở chức năng Auto filter.
ActiveSheet.Range(“$A$2:$F$47″).AutoFilter Field:=2, Criteria1:=”Anh Tuấn”Nội dung này lệnh lọc dữ liệu ở cột thứ 2 (cột NCC), điều kiện lọc là “Anh Tuấn”.
Bước 3: Tinh gọn macro để sử dụng
Cần thay đổi điều kiện lọc trong VBA để liên kết tới vùng điều kiện trong Bảng chi tiết. Khi đó thay đổi dữ liệu trong Bảng chi tiết thì macro sẽ tự động lọc theo nội dung đó.
Chúng ta chú ý vào các vùng điều kiện (Criteria1, Criteria2) ở trong câu lệnh trong VBA.
Câu lệnh lọc NCC
ActiveSheet.Range(“$A$2:$F$47″).AutoFilter Field:=2, Criteria1:=”Anh Tuấn”Thay “Anh Tuấn” bằng ô I4 (dòng 4, cột 9, sheet 1) như sau:
ActiveSheet.Range(“$A$2:$F$47”).AutoFilter Field:=2, Criteria1:=Sheet1.Cells(4, 9).ValueCâu lệnh lọc Ngày
ActiveSheet.Range(“$A$2:$F$47”).AutoFilter Field:=1, Criteria1:=”>=01/10/2017″, Operator:=xlAnd, Criteria2:=””Thay các mục “>=01/10/2017” và “”
ActiveSheet.Range(“$A$2:$F$47”).AutoFilter Field:=1, Criteria1:=”>=” & CLng(Sheet1.Cells(2, 9).Value), Operator:=xlAnd, Criteria2:=””Vì giá trị ngày tháng trong Excel về bản chất là dạng số nên có thể chuyển đổi về dạng CLng(…).
Bước 4: Copy dữ liệu và paste sang bảng chi tiết và hoàn thiện code
Từ bảng dữ liệu đã được lọc, chúng ta copy toàn bộ kết quả đã lọc rồi dán vào Bảng chi tiết để lấy kết quả.
Code cho bước này như sau: (Các bạn có thể record macro cho thao tác này rồi chỉnh sửa code)
Vì bảng chi tiết chỉ cần lấy nội dung Tên hàng, số lượng, đơn giá, thành tiền nên nội dung sẽ lấy từ cột C tới cột F, bắt đầu từ ô C3 tới F47 (cuối bảng). Chỉ copy những giá trị xuất hiện sau khi lọc.
ActiveSheet.Range(“$C$3:$F$47”).SpecialCells(xlVisible).CopyPaste dữ liệu: Dán vào Bảng chi tiết, bắt đầu từ ô H6, chỉ dán dữ liệu dạng Value (giá trị).
Range(“H6”).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=FalseSau khi Paste dữ liệu xong, chúng ta sẽ bỏ lệnh Copy và Filter đi bằng 2 dòng lệnh:
Application.CutCopyMode = False
Range(“A2:F2”).AutoFilterLặp lại thao tác này để bỏ filter khi không dùng đến nữa (trả về trạng thái ban đầu khi chưa dùng Filter).
Bước 5: Kiểm tra code bằng phím F8
Sau khi hoàn thành xong code trong VBA, chúng ta bấm nút F8 để kiểm tra xem code đó hoạt động ra sao.
Nếu code hoạt động đúng thì sẽ ra kết quả như sau:
Kết quả lệnh macro trong Excel
Bước 6: Gán macro vào sự kiện thay đổi dữ liệu ở vùng ô I2:I4
Trong cửa sổ VBA, bạn double click vào Sheet1.
Trong cửa sổ VBA làm việc với Sheet1, chọn sự kiện Change (thay đổi nội dung). Khi chúng ta thay đổi nội dung trong sheet này thì sẽ có điều gì xảy ra.
Sự kiện thay đổi trong VBA
Ở đây chúng ta quan tâm tới sự thay đổi dữ liệu ở ô I2:I4 mới làm ảnh hưởng tới báo cáo chi tiết. Do đó chúng ta chỉ xét sự thay đổi ở vùng này.
Thao tác như sau:
Dòng lệnh “If Not Application.Intersect(Range(“I2:I4”), Range(Target.Address)) Is Nothing Then” được hiểu là: Nếu có sự thay đổi dữ liệu ở trong vùng I2:I4 xảy ra thì…
Khi thay đổi dữ liệu ở vùng I2:I4 thì chúng ta muốn cập nhật nội dung của báo cáo chi tiết. Do đó chúng ta sẽ gọi ra Macro vừa hoàn thành ở phần trên.
Bổ sung:
Do mỗi điều kiện sẽ cho kết quả nhiều / ít khác nhau, do đó để có thể xác định rõ kết quả của Bảng chi tiết chỉ đúng với điều kiện được chọn, chúng ta cần làm sạch vùng Bảng chi tiết trước khi dán dữ liệu vào.
Chèn dòng Code xóa dữ liệu lên đầu Macro:
Range(“H6:K100”).ClearContentslà làm sạch dữ liệu trong vùng H6:K100 (là vùng kết quả dữ liệu của bảng chi tiết).
Kết luận
Những nội dung học được qua bài này là:
- Cách Record macro và tinh gọn code từ thao tác Record.
- Cách đọc hiểu code trong VBA.
- Cách gán Macro vào sự kiện xảy ra trong Sheet (ví dụ với sự kiện thay đổi một số nội dung trong sheet).
- Trình tự logic của câu lệnh trong VBA.
Trong công việc chúng ta sẽ gặp phải những việc này rất nhiều, được ứng dụng nhiều trong thực tế.
Bài viết này sẽ tạo tiền đề cho các bạn làm quen với VBA, cách học VBA rất đơn giản và làm quen dần với kỹ thuật VBA giúp tự động hóa khi sử dụng Excel.
Cảm ơn các bạn đã theo dõi.
Xem ngay: khóa học lập trình VBA trong excel
Tải về tài liệu kèm theo bài học