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)

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

Hướng dẫn học Excel cơ bản

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

Tải về tài liệu kèm theo bài học