Hướng dẫn cách lọc dữ liệu vào Báo cáo chi tiết trong Excel bằng VBA
Trong công việc, bạn có từng gặp phải những câu hỏi này không:
- Lập báo cáo chi tiết như thế nào?
- Làm thế nào để lấy dữ liệu từ một bảng theo những yêu cầu nhất định?
- Cách trích xuất dữ liệu theo điều kiện để đưa sang 1 bảng khác?
Bài viết sau đây sẽ giúp bạn trả lời những câu hỏi đó. Đồng thời bạn cũng có thể khám phá ra 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ào Báo cáo chi tiết.
* Chúng ta có yêu cầu 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)
Xem nhanh
Bước 1: Ghi macro các thao tác
Tại tab Developer, các bạn chọn nút lệnh Record Macro
- 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
Dù đã có phần mềm, nhưng kỹ năng Excel vẫn cực kỳ quan trọng với kế toán, bạn đã vững Excel chưa? Hãy để tôi giúp bạn, đăng ký khoá học Excel:
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.AutoFilter
Nộ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à: Lọc dữ liệu ở cột thứ 2 (cột NCC), điều kiện lọc là “Anh Tuấn” => VBA không hỗ trợ tiếng việt đầy đủ nên có dấu ?
ActiveSheet.Range(“$A$2:$F$47”).AutoFilter Field:=1, Criteria1:= _
“>=01/10/2017″, Operator:=xlAnd, Criteria2:=”<=31/10/2017”
Nội dung này là: Lọc dữ liệu ở cột thứ 1 (cột Ngày), điều kiện lọc là Lớn hơn hoặc bằng ngày 01/10/2017, và nhỏ hơn hoặc bằng 31/10/2017
Đừng bỏ lỡ: lớp học Excel kế toán với các chuyên gia
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) trong sheet1 như sau:
ActiveSheet.Range(“$A$2:$F$47”).AutoFilter Field:=2, Criteria1:=Sheet1.Cells(4, 9).value
* 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”
Thay các mục “>=01/10/2017” và “<=31/10/2017” như sau:
ActiveSheet.Range(“$A$2:$F$47”).AutoFilter Field:=1, Criteria1:= _
“>=” & CLng(Sheet1.Cells(2, 9).value), Operator:=xlAnd, Criteria2:=”<=” & CLng(Sheet1.Cells(3, 9).value)
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(…)
* Tinh gọn và hoàn thiện Code VBA
Range(“A2:F2”).Select + Selection.AutoFilter = Range(“A2:F2”).AutoFilter
Range(“B2”).Select Dòng này có thể bỏ đi
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 đượ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ọn lọc 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).Copy
Paste 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:=False
Sau 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 ‘Hủy bỏ chế độ Cut Copy trong excel (khi bạn Cut/Copy thì Excel sẽ lưu nội dung đó trong bộ nhớ, và tô đường viền nhấp nháy ở nội dung đó. Khi không dùng đến bạn có thể bỏ đi)
Range(“A2:F2”).AutoFilter ‘Hủy bỏ chế độ Filter. Lặp lại thao tác này để hủy bỏ chế độ 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:
Bước 6: Gán macro vào sự kiện thay đổi điều kiện ở 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.
Ở đâ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.
Đặt dòng Code xóa dữ liệu lên đầu Macro:
range(“H6:K100”).ClearContents là 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 dễ dàng 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
Khóa học mới xuất bản
