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.
CÀI ĐẶT VÙNG Đ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.
VÍ DỤ VÙNG ĐIỀU KIỆN TRONG BỘ LỌC EXCEL NÂNG CAO
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”)
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:
Để 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).
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:
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:
Trong đó, 6 là hàng trên cùng có chứa dữ liệu.
Để 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:
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:
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:
Đ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
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:
Để xuất 3 tổng doanh thu cuối, hãy sử dụng công thức này:
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).
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:
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ỌC CÁC HÀNG DỰA TRÊN CÁC MỤC GIỐNG NHAU TRONG DANH SÁCH
LỌC CÁC HÀNG CÓ CHỨA DANH MỤC THUỘC DANH SÁCH
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:
LỌC HÀNG KHÔNG CHỨA DANH MỤC THUỘC DANH SÁCH
Vùng miền: ="=Miền Bắc"
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.
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)
Để 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):
Đối với bảng mẫu của chúng ta, công thức sau có ích rất nhiều: