Dùng macro lọc dữ liệu bảng trong excel

Lập báo cáo chi tiết như thế nào?Làm nuốm nào để đem dữ liệu xuất phát điểm từ 1 bảng theo số đô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 để 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 giải pháp làm mới rất thú vị khi sử dụng VBA vào vấn đề lọc dữ liệu vào report chi tiết.

Bạn đang xem: Dùng macro lọc dữ liệu bảng trong excel

* chúng ta có yêu cầu sau: dựa vào bảng dưới đây, hãy mang dữ liệu ở Bảng chi tiết dựa theo điều khiếu nại thay đổi ở các ô từ bỏ I2:I4 (Thời gian cùng nhà cung cấp)

*


Xem nhanh


Bước 1: Ghi macro những 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 tác dụng Data / Filter đến bảng dữ liệuThao tác 2: thanh lọc cột Nhà cung cấp theo tên đơn vị cung cấp ở ô I4Thao tác 3: thanh lọc ngày ở cột Ngày theo thông tin ngày ở ô I2 với I3

*


Dù đã bao gồm 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 vàng Excel chưa? Hãy nhằm tôi giúp bạn, đk khoá học Excel:


*


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

Bước 2: Đọc văn bản macro

Mở hành lang cửa số VBA, bọn họ xem văn bản Macro vừa ghi được

*

Range(“A2:F2”).SelectSelection.AutoFilter

Nội dung này là : lựa chọn vùng ô trường đoản cú A2 đến F2, mở chức năng tự động hóa filter

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

Nội dung này là: thanh lọc dữ liệu ở cột thứ hai (cột NCC), điều kiện lọc là “Anh Tuấn” => VBA không cung ứng tiếng việt đầy đủ nên bao gồm dấu ?

Đừng quăng quật lỡ: lớp học tập Excel kế toán với những chuyên gia

Bước 3: Tinh gọn macro để sử dụng

Cần thay đổi điều khiếu nại lọc vào VBA để links tới vùng điều khiếu nại trong Bảng cụ thể => Khi đó thay đổi tài liệu trong Bảng cụ thể thì macro đang tự động lọc theo nội dung đó.

Chúng ta chú ý vào các vùng điều kiện (Criteria1, Criteria2) ở vào câu lệnh vào 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 thanh lọc Ngày

ActiveSheet.Range(“$A$2:$F$47”).AutoFilter Field:=1, Criteria1:= _“>=” & CLng(Sheet1.Cells(2, 9).value), Operator:=xlAnd, Criteria2:=”

Vì quý hiếm ngày tháng trong Excel về bản chất là dạng số nên tất cả thể chuyển đổi về dạng CLng(…)

* Tinh gọn và hoàn thành xong Code VBA

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

Range(“B2”).Select mẫu này có thể bỏ đi

Bước 4: Copy dữ liệu và paste sang bảng cụ thể và triển khai xong code

Từ bảng dữ liệu đã được lọc, chúng ta copy toàn cục kết quả đã lọc được rồi dán vào Bảng đưa ra tiết để lấy kết quả.

Code cho đoạn 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 bỏ ra tiết chỉ việc lấy ngôn từ Tên hàng, số lượng, đơn giá, thành tiền buộc phải nội dung đang 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 đầy đủ giá trị lộ diện sau khi lọc

ActiveSheet.Range(“$C$3:$F$47”).SpecialCells(xlVisible).Copy

Paste dữ liệu: dán vào Bảng bỏ ra tiết, bắt đầu từ ô H6, chỉ dán tài liệu dạng Value (giá trị)

Range(“H6”).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _:=False, Transpose:=False

Sau lúc Paste tài liệu xong, bọn họ sẽ bỏ lệnh Copy cùng Filter đi bởi 2 mẫu lệnh:

Application.CutCopyMode = False ‘Hủy quăng quật chế độ Cut Copy trong excel (khi chúng ta Cut/Copy thì Excel đã lưu nội dung đó trong bộ nhớ, cùng tô đường viền nhấp nháy ở nội dung đó. Lúc không dùng đến chúng ta cũng có thể bỏ đi)

Range(“A2:F2”).AutoFilter ‘Hủy quăng quật chế độ Filter. Lặp lại thao tác này để hủy vứt chế độ filter khi không dùng đến nữa (trả về tinh thần ban đầu khi chưa dùng Filter)

Bước 5: bình chọn code bằng phím F8

Sau khi chấm dứt 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ì đang ra công dụng 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 hành lang cửa số VBA làm câu hỏi với Sheet1, chọn sự khiếu nại Change (thay đổi nội dung). Khi chúng ta thay đổi ngôn từ trong sheet này thì vẫn có điều gì xảy ra.

*

Ở đây chúng ta quan tâm đến sự thay đổi dữ liệu ở ô I2:I4 new làm ảnh tận hưởng tới báo cáo chi tiết. Do đó họ chỉ xét sự thay đổi ở vùng này.

Xem thêm: Hiện Mật Khẩu Wifi Iphone, Ipad Đơn Giản, Làm Thế Nào Để Xem Lại Mật Khẩu Wifi Trên Iphone

Thao tác như sau:

*

Dòng lệnh “If Not Application.Intersect(Range(“I2:I4”), Range(Target.Address)) Is Nothing Then” được gọi 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ì bọn họ muốn update nội dung của báo cáo chi tiết. Do đó bọn họ sẽ call ra Macro vừa hoàn thành ở phần trên.

* té sung:

Do mỗi điều kiện đang cho kết quả nhiều / ít khác nhau, do đó để có thể xác định rõ tác dụng của Bảng cụ thể chỉ đúng với điều kiện được chọn, bọn họ cần làm sạch vùng Bảng chi tiết trước khi dán dữ liệu vào.

Đặt mẫu Code xóa tài liệu lên đầu Macro:

*

range(“H6:K100”).ClearContents là làm cho sạch dữ liệu trong vùng H6:K100 (là vùng tác dụng dữ liệu của bảng đưa ra tiết)

Kết luận

Những văn bản học được qua bài này là:

Cách Record macro cùng tinh gọn gàng code từ thao tác RecordCách đọc hiểu code trong VBACá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 trong những nội dung trong sheet)Trình tự súc tích của câu lệnh vào VBA

Trong công việc chúng ta sẽ gặp gỡ phải những việc này cực kỳ 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 thân quen với VBA, biện pháp học VBA tiện lợi và có tác dụng quen dần dần với kỹ thuật VBA giúp tự động hóa khi sử dụng Excel.