Trong bài viết trước, tất cả chúng ta đã bàn luận những góc nhìn 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 và kỹ năng cơ bản, hãy xem xét những ví dụ vùng điều kiện kèm theo phức tạp hơn nhé – những ví dụ này hoàn toàn có thể có ích cho việc làm của bạn đấy .
Mục lục
CÀI ĐẶT VÙNG ĐIỀU KIỆN DỰA TRÊN CÔNG THỨC
Vì hầu hết những ví dụ về vùng điều kiện kèm theo được đàm đạo trong bài hướng dẫn này sẽ có những công thức khác nhau, hãy khởi đầu bằng cách xác lập quy tắc thiết yếu để setup chúng một cách tương thích. Tin tôi đi, mẩu kỹ năng và kiến thức nhỏ nhoi này sẽ giúp bạn tiết kiệm chi phí rất nhiều thời hạn và bạn không còn phải đau đầu khi phải xác lập và sửa lỗi cho vùng điều kiện kèm theo phức tạp nữa – vùng điều kiện kèm theo có nhiều điều kiện kèm theo 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 kèm theo phải có tối thiểu 2 ô : ô công thức và ô tiêu đề .
Ô tiêu đề của điều kiện kèm theo 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ìn nhận trong mỗi ô của dải ô danh sách, hãy tham chiếu ô trên cùng cùng với tài liệu bằng cách sử dụng tham chiếu ô tương đối ví dụ điển hình như A1 .Để công thức được nhìn nhận chỉ cho một ô hay một dải ô xác lập, hãy tahm chiếu ô hay dải ô đó bằng cách sử dụng tham chiếu ô tuyệt đối ví dụ điển hình như USD A USD 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 vận dụng nhiều điều kiện kèm theo, hãy nhập tổng thể điều kiện kèm theo trên cùng một hàng và link chúng bằng toán tử AND, hay nhập mỗi điều kiện kèm theo vào một hàng khác nhau và link chúng bằng toán tử OR .
Xem thêm : Học Excel nhanh với những chuyên viên
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 để giải quyết và xử lý những trách nhiệm phức tạp hơn – những trách nhiệm mà Bộ lọc Tự động Excel thường thì không giải quyết và 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 kèm theo :
= EXACT ( B5, “ Chuối ” )
Trong đó B là cột chứa tên hạng mục, và hàng 5 hàng tiên phong 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 thông số kỹ thuật cho dải ô Danh sách và dải ô Điều kiện như ảnh chụp màn hình hiển thị dưới đây :
Lưu ý. Hình ảnh trên cũng như những ảnh chụp màn hình hiển thị 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 kèm theo nhằm mục đích 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 ô tiên phong chứa tài liệu có khớp với điều kiện kèm theo 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ì bạn hoàn toà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 kèm theo :
= F5 > AVERAGE ( USD F USD 5 : USD F USD 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
Xin hãy chú ý quan tâm rằng tất cả chúng ta sử dụng tham chiếu tương đối để tham chiếu ô trên cùng có tài liệu ( ô F5 ), và tham chiếu ô tuyệt đối để xác lập hàng loạt dải ô – dải ô mà bạn muốn tính giá trị trung bình, không tính tiêu đề cột ( USD F USD 5 : USD F USD 50 ) .
Ảnh chụp màn hình hiển thị 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 tiễn :
Những ai đã quen với Bộ lọc Số trong Excel hoàn toàn 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 hề 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 kèm theo có hàm lôgic OR bằng cách nhập mỗi điều kiện kèm theo vào mỗi hàng khác nhau. Kết quả là, bạn sẽ có một list 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 những ô trống. Bằng cách chọn hay bỏ chọn ( Trống ) những hộp ghi lại trong bảng chọn Bộ lọc Tự động của Excel, bạn hoàn toàn có thể hiển thị chỉ những hàng có những ô trống hay chứa tài 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 những ô trống hay ô có chứa tài liệu với hàm lôgic OR, hay sử dụng điều kiện kèm theo cho những ô trống hay những ô có chứa tài liệu cùng với vài điều kiện kèm theo khác nữa, thì hãy thiết lập vùng điều kiện kèm theo bộ lọc nâng cao cùng với một trong những công thức sau :
Lọc những ô trống :
ô_trên_cùng=””
Lọc những ô có chứa tài liệu :
ô_trên_cùng<>””
LỌC CÁC Ô TRỐNG VỚI HÀM LÔGIC OR
Để lọc những hàng có ô trống hoặc ở cột A hay B, hay ở cả hai cột, hãy lập thông số kỹ thuật vùng điều kiện kèm theo 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 tài 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 giải trí khi có nhiều điều kiện kèm theo, hãy lọc những hàng trong bảng mẫu theo những điều kiện kèm theo sau :
Hoặc là Vùng miền ( cột A ) hoặc là Danh mục ( cột B ) có chứa tài liệu, và
Tổng doanh thu ( cột C ) phải lớn hơn 900 .
Nói cách khác, tất cả chúng ta muốn hiển thị những hàng cung ứng những điều kiện kèm theo sau :
( Tổng doanh thu > 900 AND Vùng miền = có chứa tài liệu ) OR ( Tổng doanh thu > 900 AND Danh mục = có chứa tài liệu )
Như bạn đã biết, trong vùng điều kiện kèm theo của Bộ lọc Nâng cao Excel, điều kiện kèm theo phối 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èm theo tích hợp với hàm lôgic OR – ở những hàng khác nhau :
Bởi vì một điều kiện kèm theo trong ví dụ này được miêu tả bằng công thức ( có chứa tài liệu ) và điều kiện kèm theo 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 kèm theo Tổng doanh thu trong ảnh chụp màn hình hiển thị 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 kèm theo Có chứa tài liệu trong ảnh chụp màn hình hiển thị 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 hạng 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 những công thức dưới đây :
Lọc N hạng mục đầu :
ô_trên_cùng>=LARGE(dải ô, N)
Lọc N hạng 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 kèm theo cùng với công thức này :
= F5 > = LARGE ( USD F USD 5 : USD F USD 50,3 )
Để xuất 3 tổng doanh thu cuối, hãy sử dụng công thức này :
= F5 > = SMALL ( USD F USD 5 : USD F USD 50,3 )
Trong đó, F5 là ô trên cùng có chứa tài 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 hiển thị 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 tất cả chúng ta đã lý giải những cách khác nhau nhằm mục đích 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 những công thức Excel, quy luật định dạng có điều kiện kèm theo và công cụ Duplicate Remover đã được nói đến trong bài hướng dẫn trên, bạn cũng hoàn toàn có thể sử dụng Bộ lọc Nâng cao Excel để xuất những hàng có những 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 thuần sau đây vào vùng điều kiện kèm theo :
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à những ô trên cùng có chứa tài 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 list ngắn hơn chỉ chứa những hạng mục cần dùng tại một thời gian. Câu hỏi đặt ra là – làm thế nào bạn hoàn toàn có thể tìm toàn bộ hạng mục trong bảng – những hạng mục Open hay không Open trong list ngắn đó ?
LỌC CÁC HÀNG CÓ CHỨA DANH MỤC THUỘC DANH SÁCH
Để tìm toàn bộ hạng mục trong bảng nguồn – những hạng mục này cũng nằm trong list, hãy sử dụng công thức COUNTIF sau đây :
COUNTIF(list_to_match, top_data_cell)
Giả sử list ngắn hơn nằm trong dải ô D2 : D7, và những hạng mục trong bảng cần được so sánh với list đó thì nằm trong cột B mở màn từ hàng 10, công thức có dạng sau đây ( hãy chú ý quan tâm 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ị gò bó trong việc chỉ lọc bảng với một điều kiện kèm theo .
Ví dụ, để lọc những hàng nằm trong list, nhưng chỉ những vùng miền Bắc thôi, hãy nhập hai điều kiện kèm theo này vào cùng một hàng để chúng hoàn toàn có thể hoạt động giải trí 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 hoàn toàn có thể thấy trong ảnh chụp màn hình hiển thị dưới đây, chỉ có hai hồ sơ trong bảng khớp với hai điều kiện kèm theo 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ổng thể hạng mục trong bảng không thuộc list nhỏ hơn, hãy xem xem tác dụng 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 hạng mục vùng miền Bắc trong bảng – hạng mục không Open trong list, hãy sử dụng điều kiện kèm theo 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 kèm theo Bộ lọc Nâng cao phần nhiều giải quyết và xử lý giá trị số và giá trị ký tự. Bây giờ, là lúc cho bạn thấy cách giải quyết và xử lý ngày tháng năm .
Bộ lọc Ngày Tháng Năm có sẵn trong Excel cung ứng nhiều lựa chọn tương quan đến nhiều trường hợp. Nhiều, nhưng không phải tổng thể ! Ví dụ, nếu bạn có list ngày tháng năm và được nhu yếu phải lọc những 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 phân phối hàm WEEKDAY đặc biệt quan trọng – 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à tất cả chúng ta sẽ sử dụng trong vùng điều kiện kèm theo 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, tất cả chúng ta lọc ngày tháng năm trong cột B mở màn từ hàng 5, do đó 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 những ngày trong tuần, hãy biến hóa công thức trên đễ nó không trả về hiệu 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 tất cả 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 hoàn toàn có thể thêm một điều kiện kèm theo để lọc những ô 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 những điều kiện kèm theo phức tạp. Tất nhiên, những lựa chọn của bạn không bị số lượng giới hạn bởi những ví dụ được đàm đạo trong bài hướng dẫn này, mục tiêu của chúng tôi là cung ứng cho bạn vài ý tưởng sáng tạo hoàn toàn 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 viên luôn đi đôi thực hành thực tế. Cảm ơn những bạn đã đọc bài viết và hẹn gặp lại những bạn vào tuần tới !
Hiện tại, Blog Học Excel Online đã mở rất nhiều những khóa học giảng dạy về Học Excel cơ bản đến nâng cao với những chuyên viên số 1 tại Nước Ta. Chúng tôi sẽ mang đến cho bạn những buổi học có ích, hiệu suất cao hoàn toàn có thể sử dụng thành thạo Excel để vận dụng trong việc làm. Ngoài ra, chúng tôi đang có nhiều chương trình khuyến mại lớn cho những 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. tin tức cụ thể xem tại :
Source: https://vn.exp.gg
Category: Thông tin
