Tự động hoá Advanced Filter với Recod Macro

Trong một bài viết gần đây, bostonenglish đã giới thiệu tới các bạn cách sử dụng chức năng AutoFilter hiệu quả hơn, tự động hơn qua việc kết hợp với VBA, bạn có thể xem lại bài viết đó tại đây. Cũng liên quan tới trích lọc dữ liệu thì trong bài viết này mình xin giới thiệu tới các bạn việc sử dụng kết hợp Advanced Filter với Record Macro để tự động hóa hơn trong việc trích lọc dữ liệu.

Trích lọc nâng cao (Advanced Filter) là một trong những công cụ rất mạnh được Excel hỗ trợ giúp cho người dùng có thể trích lọc ra các thông tin, báo cáo với những điều kiện trích lọc khác nhau, sẽ hiệu quả hơn như thế nào nếu chúng ta kết hợp với việc Record Macro?.

cách dùng advanced filter

cách dùng advanced filter

Tất tần tật về cách dùng Advanced Filter trong Excel có ví dụ chi tiết

So sánh AutoFilter và Advanced Filter trong Excel

Thông thường, khi chúng ta sử dụng bộ lọc trong Excel thì sẽ nghĩa đến việc dùng Auto Filter. Công cụ này cho phép lọc theo nhiều kiểu dữ liệu khác nhau như chuỗi, số, ngày tháng,… Tuy nhiên, trong thực tế công việc sẽ có những trường hợp mà Auto Filter không giải quyết được vấn đề. Do đó chúng ta cần sử dụng công cụ khác là Advanced Filter.

Nếu so sánh giữa 2 công cụ này thì có một số sự khác biệt như sau:

Advanced Filter Auto Filter
– Yêu cầu thiết lập về vùng dữ liệu và vùng điều kiện trước khi lọc.

– Cho phép lọc dữ liệu thỏa mãn nhiều điều kiện trong nhiều cột.

– Cho phép copy dữ liệu thoả mãn điều kiện lọc ra một vùng khác.

– Cho phép lọc dữ liệu dựa trên các bộ lọc có sẵn.

– Chỉ cho phép tạo tối đa 2 điều kiện lọc, thiết lập qua Custom AutoFilter.

Cách dùng Advanced Filter trong Excel

Chuẩn bị dữ liệu để lọc

Có 2 điều cần phải chú ý để mang lại kết quả lọc chính xác khi dùng Advanced Filter trong Excel là:

  • Tiêu đề các cột dữ liệu trong bảng dữ liệu là duy nhất, các cột dữ liệu không được có tiêu đề giống nhau.
  • Bảng dữ liệu cần lọc không chứa dòng trống. Các bạn nên xóa dòng trống trong Excel trước khi bắt đầu lọc bằng Advanced Filter.
Tất tần tật về cách dùng Advanced Filter trong Excel có ví dụ chi tiết

Thiết lập vùng điều kiện lọc

Một số lưu ý đáng nhớ để thiết lập vùng điều kiện lọc là:

  1. Tiêu đề của vùng điều của và tiêu đề của bảng dữ liệu phải giống nhau.
  2. Hai điều kiện được đặt trên cùng một dòng thì sử dụng điều kiện VÀ tức là dòng phải thỏa mãn cả 2 điều kiện cùng lúc mới xuất hiện trong phần kết quả.
  3. Hai điều kiện được đặt trên 2 dòng khác nhau tương ứng với điều kiện HOẶC tức là dòng thỏa 1 trong 2 điều kiện là đã xuất hiện trong kết quả.

Ví dụ: Chúng ta cần lọc ra những dòng của Miền Bắc và có Sub-total lớn hơn hoặc bằng 900 thì vùng điều kiện của Advanced Filter trong Excel có dạng như sau:

Tất tần tật về cách dùng Advanced Filter trong Excel có ví dụ chi tiết

Áp dụng Advanced Filter trong Excel

Bước 1: Chuẩn bị vùng điều kiện. Tiếp theo, chọn một ô bất kỳ trong vùng dữ liệu cần lọc.

Bước 2: Vào thẻ Data => chọn mục Advanced trong nhóm Sort & Filter.

Tất tần tật về cách dùng Advanced Filter trong Excel có ví dụ chi tiết

Bước 3: Khi hộp thoại Advanced Filter hiện lên thì các lựa chọn theo hướng dẫn sau:

  • Mục Action: Chọn Filter the list, in-place để lọc dữ liệu tại chỗ, các dòng không thỏa mãn điều kiện sẽ bị ẩn sau khi lọc; chọn Copy to another location để lọc dữ liệu rồi sao chép sang ô khác trên bảng tính.
  • Mục List Range: Vùng dữ liệu sẽ lọc, bao gồm cả tiêu đề. Đó là lý do chúng ta cần thực hiện bước 1 để Excel tự nhận diện vào tạo List Range mà bạn không phải chọn thủ công. Trong trường hợp muốn thay đổi vùng chọn thì bạn bấm vào dấu mũi tên ở bên phải mục này rồi quét chuột để tạo vùng chọn khác.
  • Mục Criteria range: Các bạn bấm vào mũi dấu mũi tên ở bên phải mục này rồi quét chuột chọn toàn bộ vùng điều kiện mà chúng ta đã đặt ra.
  • Mục Copy to: Mục này sẽ sáng lên nếu trong mục Action bạn chọn là Copy to another location. Cũng tương tự như các khác, bạn có thể bấm vào dấu mũi tên ở bên phải mục này rồi quét chuột để chọn vùng bạn muốn dán kết quả sau khi lọc vào đó.
  • Mục Unique records only: Nếu bạn muốn Advanced Filter lọc ra các kết quả duy nhất thì tích vào ô vuông đầu mục này.
Tất tần tật về cách dùng Advanced Filter trong Excel có ví dụ chi tiết

Bước 4: Bấm OK và xem kết quả lọc

Tất tần tật về cách dùng Advanced Filter trong Excel có ví dụ chi tiết

Sử dụng Advanced Filter trong Excel theo vùng điều kiện

Ở phần hướng dẫn sử dụng phía trên có thể bạn sẽ thấy Advanced Filter cũng không có gì đặc biệt. Kết quả lọc như trên chúng ta dùng Auto Filter cũng được. Tuy nhiên, điều thể hiện sự nâng cao của tính năng này nằm ở việc thiết lập vùng điều kiện. Dưới đây là một số tình huống cụ thể để các bạn tham khảo.

Advanced Filter điều kiện so sánh số và ngày tháng

Giả sử, chúng ta có yêu cầu là phải lọc ra được các dòng thỏa mãn tất cả các điều kiện sau:

  • Thuộc khu vực Miền Bắc
  • Ngày nhận hàng: Trong khoảng từ 01/07/2022 đến 30/07/2022
  • Thành tiền: Lớn hơn 800
Tất tần tật về cách dùng Advanced Filter trong Excel có ví dụ chi tiết

Lọc dữ liệu chuỗi bằng Advanced Filter

Advanced Filter không chỉ lọc được dữ liệu số và ngày tháng mà còn lọc được cả chuỗi văn bản. Một số lưu ý khi thiết lập vùng điều kiện lọc chuỗi như sau:

Điều kiện Ý nghĩa
=”=text” Lọc dữ liệu bằng “text”
text Lọc dữ liệu bắt đầu bằng “text”
<>text Lọc dữ liệu trong ô khác “text”
>text Lọc dữ liệu xuất hiện sau “text” trong bảng chữ cái
<text Lọc dữ liệu xuất hiện trước “text” trong bảng chữ cái

Ví dụ 1: Lọc dữ liệu text chính xác

Để lọc ra các hàng có giá trị là “Chuối” thì điều kiện lọc mà chúng ta cần nhập vào ô A2 là =”=Chuối”. Khi đó ô A2 sẽ hiển thị là =Chuối

Tất tần tật về cách dùng Advanced Filter trong Excel có ví dụ chi tiết

Ví dụ 2: Lọc dữ liệu text bắt đầu bởi

Để lọc các dòng dữ liệu được bắt đầu bởi một chữ cái nào đó thì chúng ta chỉ cần điền nó vào vùng điều kiện là được. Ví dụ: Ở đây chúng mình muốn lọc những dòng bắt đầu bằng chữ C.

Tất tần tật về cách dùng Advanced Filter trong Excel có ví dụ chi tiết

Sử dụng công thức trong vùng điều kiện Advanced Filter

Advanced Filter cho phép chúng ta sử dụng công thức trong vùng điều kiện. Đây là điều là Auto Filter không làm được. Các bạn cần lưu ý một số điều sau để Advanced Filter hoạt động chính xác:

  1. Công thức sử dụng cho vùng điều kiện trả về kết quả TRUE hoặc FALSE.
  2. Vùng điều kiện bao gồm ít nhất 2 ô: ô chứa công thức và ô tiêu đề.
  3. Ô tiêu đề của vùng điều kiện phải có nội dung khác với tiêu đề các cột trong vùng dữ liệu.
  4. Nếu công thức áp dụng cho mỗi dòng dữ liệu, sử dụng tham chiếu tương đối và tham chiếu tới dòng dữ liệu đầu tiên trong vùng dữ liệu.
  5. Tham chiếu tới vùng dữ liệu luôn là tham chiếu tuyệt đối (nên đặt tên cho vùng dữ liệu – sử dụng Named Range)

Trong ví dụ dưới đây, chúng mình đang cần lọc ra các dòng dữ liệu mà doanh thu tháng 7 lớn hơn tháng 8. Khi đó, công thức nằm ở vùng điều kiện sẽ là =C7>D7 do dòng thứ 7 là dòng đầu kiên của vùng dữ liệu.

Tất tần tật về cách dùng Advanced Filter trong Excel có ví dụ chi tiết

Advanced Filter với điều kiện VÀ

Trong hình ảnh dưới đây các bạn có thể vùng điều kiện có 2 điều kiện nằm cùng một dòng. Do đó khi chạy chế độ lọc Advanced Filter trong Excel thì dòng dữ liệu phải thỏa mãn cả 2 điều kiện thì mới xuất hiện trong kết quả.

Tất tần tật về cách dùng Advanced Filter trong Excel có ví dụ chi tiết

Advanced Filter với điều kiện HOẶC

Trong hình ảnh dưới đây các bạn có thể vùng điều kiện có 2 điều kiện nằm khác dòng. Do đó khi chạy chế độ lọc Advanced Filter trong Excel thì dòng dữ liệu thỏa mãn 1 trong 2 điều kiện sẽ xuất hiện trong bảng kết quả.

Tất tần tật về cách dùng Advanced Filter trong Excel có ví dụ chi tiết

Advanced Filter với điều kiện hỗn hợp

Trên thực tế, chúng ta có thể kết hợp cả điều kiện VÀ và điều kiện HOẶC trong cùng một lần lọc.

Ví dụ: Trong hình ảnh dưới đây là chúng mình đang tạo vùng điều kiện để lọc ra các dòng dữ liệu (thuộc vùng Miền Bắc và Sub-total >= 900) hoặc (thuộc vùng Miền Bắc và Sub-total >= 300).

Bạn đang xem bài viết tại: https://bostonenglish.edu.vn/

Tất tần tật về cách dùng Advanced Filter trong Excel có ví dụ chi tiết

Tự động hoá Advanced Filter với Recod Macro

Chuẩn bị ban đầu

Để cho nhanh thì mình xem phép lấy lại dữ liệu từ bài: Sử dụng AutoFilter hiệu quả hơn với VBA

Ta thực hiện việc trích lọc ra các thông tin về số lượng, doanh thu, chiết khấu của khu vực mà ta sẽ lựa chọn trong ô V3

  • Vùng dữ liệu (List range) của ta ở đây là: B5:E25
  • Điều kiện trích lọc (Criteria range) là vùng V2:V3
  • Và vùng U5:W5 là nơi ta đưa ra kết quả trích lọc (Copy to)

Sau khi đã xác định rõ ràng như trên thì bước tiếp theo  chúng ta sẽ thực hiện việc Record Macro là lưu lại quá trình Excel thực hiện Advanced Filter.

Bạn đang xem bài viết tại: https://bostonenglish.edu.vn/

Record Macro quá trình thực hiện Advanced Filter

Tại nhóm Code trong Tab Developer bạn tìm tới Record Macro và bắt đầu thực hiện Advanced Filter để Excel lưu lại các đoạn Code.

Sau khi thực hiện xong thao tác trích lọc nâng cao bạn cho dừng Record, lúc này VBA đã thực hiện ghi lại toàn bộ các thao tác trong quá trình vừa rồi của bạn, để kiểm tra bạn mở VBA lên để xem. Bây giờ mỗi lần thay đổi khu vực bạn chỉ cần chạy lại đoạn Code đó thì dữ liệu tương ứng của khu vực đó sẽ được trích lọc ra mà bạn không phải thao tác lại việc Advanced Filter.

Lưu ý: Có thể dòng Code mà bạn ghi lại được sẽ không giống hoàn toàn như hình trên, nhưng dòng Code mình đóng khung đó thì sẽ cần phải giống

Tới đây thì công việc của ta đã hoàn thành. Ngoài ra, để tự động hoá hơn nữa, thay vì mỗi lần thay đổi khu vực bạn lại phải ấn để chạy lại Code thì bạn muốn việc chạy Code để lấy ra dữ liệu này VBA sẽ tự làm, tức là bạn chỉ cần thay đổi khu vực trong ô V3 thì Code sẽ chạy, bạn hoàn toàn có thể thực hiện điều đó bằng cách tại Sheet1 bạn tạo 1 sự kiện Worksheet_Change và thêm đoạn Code như sau:

Để hiểu kỹ hơn về các sự kiện trong VBA bạn có thể tham khảo bài viết:

Tìm hiểu về ý nghĩa và cách dùng các sự kiện trong vba excel

Như vậy qua bài viết này của Học Excel Online bạn đã biết thêm được cách sử dụng Advanced Filter kết hợp với việc Record Macro để có thể tự động quá hơn trong sử dụng công cụ trích lọc nâng cao, hy vọng bạn sẽ áp dụng vào công việc một cách hiệu quả. Để có thể hiểu và nắm được rõ hơn một số ứng dụng công cụ này bạn có thể tham khảo thêm.

Sử dụng Advanced Filter để lọc dữ liệu trùng trong Excel

Điều kiện để sử dụng chức năng Advanced Filter:

  • Tiêu đề bảng dữ liệu chỉ được sử dụng một dòng duy nhất.
  • Trong bảng, không được merge bất kì một ô nào.

Cho bảng số liệu sau:

Bạn đang xem bài viết tại: https://bostonenglish.edu.vn/

Bước 1: Chọn tiêu đề cột cần làm điều kiện lọc, sau đó copy, nhấn tổ hợp phím Ctrl + C rồi sau đó dán, Ctrl+V vào ô  điều kiện có màu vàng ở trên.

Ví dụ, chọn cột năm sinh làm cột điều kiện, ta copy tiêu đề Năm sinh và dán vào ô được tô màu vàng.

Bước 2: Nhập điều kiện lọc.

Ví dụ: Điều kiện là sinh năm 1992 và giới tính là Nam

Bước 3: Vào Data -> Advanced=> Hộp thoại Advanced Filter xuất hiện.

Trong đó cần quan tâm đến:

– Phần Action.

– List range: Vùng chứa dữ liệu.

– Criteria range: Vùng chứa điều kiện.

  • Chọn Filter the list, in place tại mục Action, lọc dữ liệu ngay trên bảng.

Bạn đang xem bài viết tại: https://bostonenglish.edu.vn/

=> Nhấp OK được kết quả:

  • Chọn Copy to another location tại mục Action khi muốn lọc dữ liệu sang một vị trí khác ngoài bảng dữ liệu đã cho. Phần Copy to là nơi mà bạn muốn đưa dữ liệu mới đến.

Bạn đang xem bài viết tại: https://bostonenglish.edu.vn/

=> Nhấp OK ta được kết quả.

Qua bài viết này, chúng ta có thể thấy được chỉ cần biết cách thiết lập vùng điều kiện đúng là có thể dùng Advanced Filter trong Excel cho nhiều mục đích khác nhau. Hãy thử ứng dụng kiến thức mà chúng mình chia sẻ vào công việc hàng ngày của bạn để nâng cao hiệu quả nhé. Chúc các bạn áp dụng thành công!

Bạn đang xem bài viết tại: https://bostonenglish.edu.vn/

Bostonenglish.edu.vn cũng giúp giải đáp những vấn đề sau đây:

  • Advanced Filter nhiều điều kiện
  • Advanced Filter là gì
  • Advanced Filter ngày tháng
  • Trong hợp thoại Advanced Filter thì List Range là
  • Advanced Filter nâng cao
  • Lọc Advanced Filter
  • Advanced Filter trong VBA
  • Advanced Filter Excel
See more articles in the category: Wiki

Leave a Reply