Bài hướng dẫn này cho thấy cách sử dụng bộ lọc nâng cao trong Excel và cung cấp vài ví dụ vùng điều kiện nâng cao để tạo bộ lọc phân biệt dạng chữ, để tìm điểm giống và khác giữa hai cột, cách xuât hồ sơ khớp với danh sách nhỏ hơn, và nhiều hơn thế nữa.

Trong bài viết trước, chúng ta đã thảo luận các khía cạnh khác nhau của Bộ lọc Nâng cao trong Excel và cách sử dụng nó để lọc hàng cùng với hàm lôgic AND và OR. Bây giờ bạn đã có kiến thức cơ bản, hãy xem xét các ví dụ vùng điều kiện phức tạp hơn nhé – các ví dụ này có thể hữu ích cho công việc của bạn đấy.

CÀI ĐẶT VÙNG ĐIỀU KIỆN DỰA TRÊN CÔNG THỨC

Vì phần lớn các ví dụ về vùng điều kiện được thảo luận trong bài hướng dẫn này sẽ có các công thức khác nhau, hãy bắt đầu bằng cách xác định quy tắc thiết yếu để cài đặt chúng một cách phù hợp. Tin tôi đi, mẩu kiến thức nhỏ nhoi này sẽ giúp bạn tiết kiệm rất nhiều thời gian và bạn không còn phải đau đầu khi phải xác định và sửa lỗi cho vùng điều kiện phức tạp nữa – vùng điều kiện có nhiều điều kiện dựa trên công thức.

Công thức bạn sử dụng cho vùng điều kiện phải biết đánh giá TRUE hay FALSE.

Vùng điều kiện phải có ít nhất 2 ô: ô công thức và ô tiêu đề.

Ô tiêu đề của điều kiện dựa trên công thức hoặc là để trống hoặc là khác với bất kỳ tiêu đề nào của bảng (dải ô danh sách).

Để công thức được đánh giá trong mỗi ô của dải ô danh sách, hãy tham chiếu ô trên cùng cùng với dữ liệu bằng cách sử dụng tham chiếu ô tương đối chẳng hạn như A1.

Để công thức được đánh giá chỉ cho một ô hay một dải ô xác định, hãy tahm chiếu ô hay dải ô đó bằng cách sử dụng tham chiếu ô tuyệt đối chẳng hạn như $A$1.

Khi tham chiếu dải ô danh sách trong công thức, hãy luôn sử dụng tham chiếu ô tuyệt đối.

Khi áp dụng nhiều điều kiện, hãy nhập tất cả điều kiện trên cùng một hàng và liên kết chúng bằng toán tử AND, hay nhập mỗi điều kiện vào một hàng khác nhau và liên kết chúng bằng toán tử OR.

Xem thêm: Học Excel nhanh với các chuyên gia

VÍ DỤ VÙNG ĐIỀU KIỆN TRONG BỘ LỌC EXCEL NÂNG CAO

Các ví dụ dưới đây sẽ chỉ bạn cách tạo bộ lọc trong Excel để xử lý các nhiệm vụ phức tạp hơn – những nhiệm vụ mà Bộ lọc Tự động Excel thông thường không xử lý được.

BỘ LỌC PHÂN BIỆT DẠNG CHỮ CHO GIÁ TRỊ DẠNG KÝ TỰ

Giống như Bộ lọc Tự động Excel, công cụ Bộ lọc Nâng cao cũng không phân biệt dạng chữ, có nghĩa là nó không phân biệt ký tự viết hoa và viết thường khi lọc giá trị dạng ký tự. Tuy nhiên, bạn có thể dễ dàng tìm kiếm trong khi vẫn phân biệt dạng chữ bằng cách sử dụng hàm EXACT trong điều kiện lọc nâng cao.

Ví dụ, để lọc hàng chứa Chuối, không phân biệt là CHUỐI hay chuối, hãy nhập công thức sau vào vùng điều kiện:

=EXACT(B5, “Chuối”)

Trong đó B là cột chứa tên danh mục, và hàng 5 hàng đầu tiên chứa dư liệu.

Sau đó, hãy sử dụng Bộ lọc Nâng cao Excel bằng cách nhấp nút Advanced trên tab Data, rồi định cấu hình cho dải ô Danh sách và dải ô Điều kiện như ảnh chụp màn hình dưới đây:

Lưu ý. Hình ảnh trên cũng như các ảnh chụp màn hình khác trong bài hướng dẫn này hiển thị công thức trong ô vùng điều kiện nhằm giúp mọi thứ trở nên rõ ràng hơn. Trong trang tính của bạn, ô công thức chỉ nên trả về TRUE hoặc FALSE, tùy vào ô đầu tiên chứa dữ liệu có khớp với điều kiện hay không:

LỌC GIÁ TRỊ LỚN HƠN HAY NHỎ HƠN GIÁ TRỊ TRUNG BÌNH TRONG MỘT CỘT

Khi lọc giá trị số, thông thường bạn có thể muốn hiển thị chỉ những ô lớn hơn hay nhỏ hơn giá trị trung bình trong cột. Ví dụ:

Để lọc hàng có tổng lớn hơn giá trị trung bình, hãy sử dụng công thức sau đây trong vùng điều kiện:

=F5>AVERAGE($F$5:$F$50)

Để lọc hàng có tổng nhỏ hơn giá trị trung bình, hãy sử dụng công thức sau đây:

=F5<AVERAGE($F$5:$F$50)

Xin hãy lưu ý rằng chúng ta sử dụng tham chiếu tương đối để tham chiếu ô trên cùng có dữ liệu (ô F5), và tham chiếu ô tuyệt đối để xác định toàn bộ dải ô – dải ô mà bạn muốn tính giá trị trung bình, không tính tiêu đề cột ($F$5:$F$50).

Ảnh chụp màn hình dưới đây cho thấy công thức lọc giá trị lớn hơn giá trị trung bình trong thực tế:

Những ai đã quen với Bộ lọc Số trong Excel có thể tự hỏi, tại sao lại phải sử dụng bộ lọc nâng cao trong khi bộ lọc số có sẵn đã có lựa chọn Above average và Below average? Đúng rồi đấy, nhưng bộ lọc có sẵn trong Excel không thể sử dụng với hàm lôgic OR!

Vì thế, hãy tận dụng ví dụ này, hãy lọc hàng có tổng (cột F) HAY Doanh thu Tháng chín (cột E) lớn hơn giá trị trung bình. Về việc này, hãy thiết lập vùng điều kiện có hàm lôgic OR bằng cách nhập mỗi điều kiện vào mỗi hàng khác nhau. Kết quả là, bạn sẽ có một danh sách lớn hơn giá trị trung bình nằm ở cột F hay cột E:

Excel kế toán 1 thầy 1 trò

LỌC HÀNG TRỐNG HAY CÓ CHỨA DỮ LIỆU

Có lẽ mọi người đã biết, Bộ lọc Excel có một tính năng sẵn có để lọc các ô trống. Bằng cách chọn hay bỏ chọn (Trống) các hộp đánh dấu trong bảng chọn Bộ lọc Tự động của Excel, bạn có thể hiển thị chỉ những hàng có các ô trống hay chứa dữ liệu trong một hay nhiều cột. Vấn đề ở đây đó là bộ lọc ô trống có sẵn trong Excel chỉ chạy khi được dùng với hàm lôgic AND.

Nếu bạn muốn lọc các ô trống hay ô có chứa dữ liệu với hàm lôgic OR, hay sử dụng điều kiện cho các ô trống hay các ô có chứa dữ liệu cùng với vài điều kiện khác nữa, thì hãy cài đặt vùng điều kiện bộ lọc nâng cao cùng với một trong những công thức sau:

Lọc các ô trống:

ô_trên_cùng=””

Lọc các ô có chứa dữ liệu:

ô_trên_cùng<>””

LỌC CÁC Ô TRỐNG VỚI HÀM LÔGIC OR

Để lọc các hàng có ô trống hoặc ở cột A hay B, hay ở cả hai cột, hãy lập cấu hình vùng điều kiện Bộ lọc Nâng cao theo cách này:

• =A6=""
• =B6=""

Trong đó, 6 là hàng trên cùng có chứa dữ liệu.

LỌC CÁC Ô CHỨA DỮ LIỆU VỚI HÀM LÔGIC OR VÀ HÀM LÔGIC AND

Để hiểu thêm cách Bộ lọc Nâng cao trong Excel hoạt động khi có nhiều điều kiện, hãy lọc các hàng trong bảng mẫu theo các điều kiện sau:

Hoặc là Vùng miền (cột A) hoặc là Danh mục (cột B) có chứa dữ liệu, và

Tổng doanh thu (cột C) phải lớn hơn 900.

Nói cách khác, chúng ta muốn hiển thị các hàng đáp ứng các điều kiện sau:

(Tổng doanh thu>900 AND Vùng miền=có chứa dữ liệu) OR (Tổng doanh thu>900 AND Danh mục=có chứa dữ liệu)

Như bạn đã biết, trong vùng điều kiện của Bộ lọc Nâng cao Excel, điều kiện kết hợp với hàm lôgic AND nên được nhập vào cùng một hàng, và điều kiện kết hợp với hàm lôgic OR – ở các hàng khác nhau:

Bởi vì một điều kiện trong ví dụ này được diễn tả bằng công thức (có chứa dữ liệu) và điều kiện còn lại có chứa toán tử so sánh (Tổng doanh thu>900), hãy để tôi nhắc bạn một vài điều:

Điều kiện được lập với toán tử so sánh nên có tiêu đề giống 100% tiêu đề của bảng, giống như điều kiện Tổng doanh thu trong ảnh chụp màn hình trên.

Điều kiện dựa trên công thức nên có hoặc là ô tiêu đề trống hoặc là tiêu đề không giống với bất kỳ tiêu đề nào của bảng, giống như điều kiện Có chứa dữ liệu trong ảnh chụp màn hình trên.

CÁCH XUẤT N HỒ SƠ ĐẦU/CUỐI

Có lẽ bạn đã biết, Bộ lọc Số có sẵn trong Excel có tính năng hiển thị 10 danh mục đầu hay cuối. Nhưng nếu bạn cần lọc 3 giá trị đầu hay 5 giá trị cuối thì sao? Trong trường hợp này, Bộ lọc Nâng cao trong Excel sẽ có sẵn các công thức dưới đây:

Lọc N danh mục đầu:

ô_trên_cùng>=LARGE(dải ô, N)

Lọc N danh mục cuối:

ô_trên_cùng<=SMALL(dải ô, N)

Ví dụ, để lọc 3 tổng doanh thu đầu, hãy tạo vùng điều kiện cùng với công thức này:

=F5>=LARGE($F$5:$F$50,3)

Để xuất 3 tổng doanh thu cuối, hãy sử dụng công thức này:

=F5>=SMALL($F$5:$F$50,3)

Trong đó, F5 là ô trên cùng có chứa dữ liệu trong cột Tổng doanh thu (không tính tiêu đề của cột).

Ảnh chụp màn hình dưới đây cho thấy công thức lọc 3 tổng doanh thu đầu:

Lưu ý. Nếu dải danh sách chứa vài hàng có cùng giá trị thuộc N danh sách đầu/cuối, tất cả hàng như thế đều sẽ được hiển thị, như ảnh chụp màn hình dưới đây:

Tìm hiểu ngay: Tài liệu Excel cơ bản

LỌC ĐIỂM GIỐNG VÀ ĐIỂM KHÁC GIỮA HAI CỘT

Một trong những bài viết trước của chúng ta đã giải thích những cách khác nhau nhằm so sánh hai cột trong Excel rồi tìm điểm giống và điểm khác giữa chúng. Bên cạnh các công thức Excel, quy luật định dạng có điều kiện và công cụ Duplicate Remover đã được nói đến trong bài hướng dẫn trên, bạn cũng có thể sử dụng Bộ lọc Nâng cao Excel để xuất các hàng có các giá trị giống hay khác nhau trong hai hay nhiều cột. Để làm điều này, hãy nhập một trong những công thức đơn giản sau đây vào vùng điều kiện:

Lọc điểm giống (sự trùng lặp) giữa hai cột:

=B5=C5

Lọc điểm khác (giá trị độc nhất) giữa hai cột:

=B5<>C5

Trong đó, B5 và C5 là các ô trên cùng có chứa dữ liệu thuộc hai cột mà bạn muốn so sánh.

Lưu ý. Công cụ Bộ lọc Nâng cao chỉ có thể tìm điểm giống và điểm khác trên cùng một hàng. Để tìm tất cả giá trị thuộc cột A nhưng không xuất hiện trong cột B, hãy sử dụng công thức này.

LỌC CÁC HÀNG DỰA TRÊN CÁC MỤC GIỐNG NHAU TRONG DANH SÁCH

Giả sử bạn có một bảng lớn có hàng trăm hay hàng ngàn hàng, và bạn nhận được một danh sách ngắn hơn chỉ chứa các danh mục cần dùng tại một thời điểm. Câu hỏi đặt ra là – làm thế nào bạn có thể tìm tất cả danh mục trong bảng – các danh mục xuất hiện hay không xuất hiện trong danh sách ngắn đó?

LỌC CÁC HÀNG CÓ CHỨA DANH MỤC THUỘC DANH SÁCH

Để tìm tất cả danh mục trong bảng nguồn – các danh mục này cũng nằm trong danh sách, hãy sử dụng công thức COUNTIF sau đây:

COUNTIF(list_to_match, top_data_cell)

Giả sử danh sách ngắn hơn nằm trong dải ô D2:D7, và các danh mục trong bảng cần được so sánh với danh sách đó thì nằm trong cột B bắt đầu từ hàng 10, công thức có dạng sau đây (hãy chú ý cách dùng tham chiếu tuyệt đối và tương đối):

=COUNTIF($D$2:$D$7,B10)

Tất nhiên bạn không bị bó buộc trong việc chỉ lọc bảng với một điều kiện.

Ví dụ, để lọc các hàng nằm trong danh sách, nhưng chỉ các vùng miền Bắc thôi, hãy nhập hai điều kiện này vào cùng một hàng để chúng có thể hoạt động khi dùng hàm lôgic AND:

• Vùng miền: ="=Miền Bắc"
• Danh mục giống nhau: =COUNTIF($D$2:$D$7,B10)

Như bạn có thể thấy trong ảnh chụp màn hình dưới đây, chỉ có hai hồ sơ trong bảng khớp với hai điều kiện mà thôi:

Lưu ý. Trong ví dụ này, chúng ta sử dụng điểm giống tuyệt đối cho giá trị ký tự: =”=Miền Bắc” để tìm những ô giống hệt với chuỗi ký tự xác định. Nếu bạn chỉ nhập điều kiện Vùng miền như thế này Miền Bắc (không có dấu bằng và dấu ngoặc kép), thì Microsoft Excel sẽ tìm tất cả danh mục bắt đầu với chuỗi ký tự xác định, ví dụ Miền Bắc Trung Bộ.

LỌC HÀNG KHÔNG CHỨA DANH MỤC THUỘC DANH SÁCH

Để tìm tất cả danh mục trong bảng không thuộc danh sách nhỏ hơn, hãy xem xem kết quả của công thức COUNTIF có bằng 0 không:

COUNTIF(list_to_match, top_data_cell)=0

Ví dụ, để lọc danh mục vùng miền Bắc trong bảng – danh mục không xuất hiện trong danh sách, hãy sử dụng điều kiện sau:

• Vùng miền: ="=Miền Bắc"
• Danh mục không trùng khớp: =COUNTIF($D$2:$D$7,B10)=0

Lưu ý.

• Nếu danh sách cần so sánh nằm ở trang tính khác, hãy chắc rằng bạn thêm tên trang tính vào công thức, ví dụ =COUNTIF(Sheet2!$A$2:$A$7,B10).
• Nếu bạn muốn xuất kết quả sang một trang tính khác, hãy sử dụng Bộ lọc Nâng cao ở trang tính mà bạn muốn xuất kết quả.

LỌC CÁC NGÀY CUỐI TUẦN VÀ TRONG TUẦN

Nãy giờ, ví dụ về vùng điều kiện Bộ lọc Nâng cao phần lớn xử lý giá trị số và giá trị ký tự. Bây giờ, là lúc cho bạn thấy cách xử lý ngày tháng năm.

Bộ lọc Ngày Tháng Năm có sẵn trong Excel cung cấp nhiều lựa chọn liên quan đến nhiều tình huống. Nhiều, nhưng không phải tất cả! Ví dụ, nếu bạn có danh sách ngày tháng năm và được yêu cầu phải lọc các ngày cuối tuần và trong tuần, bạn sẽ làm thế nào?

Như bạn đã biết, Microsoft Excel cung cấp hàm WEEKDAY đặc biệt – hàm này trả về ngày trong tuần dựa trên ngày tháng năm cho sẵn. Và đây là hàm mà chúng ta sẽ sử dụng trong vùng điều kiện Bộ lọc Nâng cao Excel.

CÁCH LỌC NGÀY CUỐI TUẦN TRONG EXCEL

Hãy nhớ rằng, trong thuật ngữ WEEKDAY, 1 là Chủ nhật và 7 là Thứ bảy, công thức lọc ngày cuối tuần có dạng như sau:

OR(WEEKDAY(ngày tháng năm)=7, WEEKDAY(ngày tháng năm)=1)

Trong ví dụ này, chúng ta lọc ngày tháng năm trong cột B bắt đầu từ hàng 5, vì thế công thức lọc ngày cuối tuần có dạng như sau:

=OR(WEEKDAY(B5)=7, WEEKDAY(B5)=1)

CÁCH LỌC NGÀY TRONG TUẦN TRONG EXCEL

Để lọc các ngày trong tuần, hãy thay đổi công thức trên đễ nó không trả về kết quả 1 (Chủ nhật) và 7 (Thứ bảy):

AND(WEEKDAY(ngày tháng năm)<>7, WEEKDAY(ngày tháng năm)<>1)

Đối với bảng mẫu của chúng ta, công thức sau có ích rất nhiều:

=AND(WEEKDAY(B5)<>7, WEEKDAY(B5)<>1)

Bên cạnh đó, bạn có thể thêm một điều kiện để lọc các ô trống: =B5<>””

Để lọc ngày tháng năm trong trang tính của bạn theo cách khác, hãy tìm hàm Date liên quan và cứ tự nhiên dùng nó trong vùng điều kiện Bộ lọc Nâng cao Excel.

À thì, đây là cách bạn dùng Bộ lọc Nâng cao Excel cho các điều kiện phức tạp. Tất nhiên, các lựa chọn của bạn không bị giới hạn bởi những ví dụ được thảo luận trong bài hướng dẫn này, mục đích của chúng tôi là cung cấp cho bạn vài ý tưởng có thể truyền cảm hứng để bạn đi đúng hướng. Hãy nhớ rằng con đường trở thành chuyên gia luôn đi đôi thực hành. Cảm ơn các bạn đã đọc bài viết và hẹn gặp lại các bạn vào tuần tới!

Hiện tại, Blog Học Excel Online đã mở rất nhiều các khóa học đào tạo về Học Excel cơ bản đến nâng cao với các chuyên gia hàng đầu tại Việt Nam. Chúng tôi sẽ mang đến cho bạn những buổi học bổ ích, hiệu quả có thể sử dụng thành thạo Excel để áp dụng trong công việc. Ngoài ra, chúng tôi đang có nhiều chương trình khuyến mại lớn cho các học viên đăng kí trong tháng này, vậy còn trần trừ gì nữa mà không đăng kí ngay thôi. Thông tin chi tiết xem tại: