Lọc Dữ Liệu Có Điều Kiện Trong Excel

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

Bạn đang xem: Lọc dữ liệu có điều kiện trong excel

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


Xem nhanh


VÍ DỤ VÙNG ĐIỀU KIỆN trong BỘ LỌC EXCEL NÂNG CAOCÁCH XUẤT N HỒ SƠ ĐẦU/CUỐILỌC CÁC HÀNG DỰA TRÊN CÁC MỤC GIỐNG NHAU trong DANH SÁCHLỌC CÁC NGÀY CUỐI TUẦN VÀ trong TUẦN

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

Vì nhiều phần các lấy ví dụ về vùng đk được bàn luận trong bài xích hướng dẫn này sẽ có các phương pháp khác nhau, hãy bước đầu bằng cách xác minh quy tắc thiết yếu để thiết lập chúng một cách phù hợp. Tin tôi đi, mẩu loài kiến thức bé dại nhoi này để giúp đỡ bạn ngày tiết kiệm không ít thời gian với bạn không còn phải chóng mặt khi phải xác minh và sửa lỗi mang lại vùng điều kiện phức hợp nữa – vùng điều kiện có tương đối nhiều điều kiện dựa vào 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 tốt FALSE.

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

Ô tiêu đề của điều kiện dựa trên bí quyết hoặc là để trống hay những khác với ngẫu nhiên tiêu đề nào của bảng (dải ô danh sách).

Để bí quyết được reviews trong từng ô của dải ô danh sách, hãy tham chiếu ô bên 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.


*

Để bí quyết được đánh giá chỉ cho 1 ô hay 1 dải ô xác định, hãy tahm chiếu ô xuất xắc dải ô đó bằng phương pháp sử dụng tham chiếu ô tuyệt đối chẳng hạn như $A$1.

Khi tham chiếu dải ô list trong công thức, hãy luôn luôn sử dụng tham chiếu ô xuất xắc đối.

Khi vận dụng nhiều điều kiện, hãy nhập tất cả điều kiện trên và một hàng và links chúng bằng toán tử AND, xuất xắc nhập từng điều kiện vào một hàng không giống nhau và links chúng bằng toán tử OR.

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

Các ví dụ sau đây sẽ chỉ các bạn cách tạo bộ lọc trong Excel để xử lý những nhiệm vụ tinh vi hơn – những trọng trách mà cỗ lọc tự động hóa Excel thường thì không cách xử lý được.

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

Giống như cỗ lọc auto Excel, hình thức Bộ lọc nâng cao cũng không minh bạch dạng chữ, tức là nó không tách biệt ký từ viết hoa cùng viết thường khi lọc giá trị dạng cam kết tự. Tuy nhiên, bạn cũng có thể dễ dàng kiếm tìm kiếm trong khi vẫn rõ ràng dạng chữ bằng cách sử dụng hàm EXACT trong đk lọc nâng cao.

Ví dụ, để lọc hàng đựng Chuối, không sáng tỏ là CHUỐI giỏi chuối, hãy nhập bí quyết sau vào vùng điều kiện:

=EXACT(B5, “Chuối”)

Trong đó B là cột đựng tên danh mục, cùng hàng 5 bậc nhất tiên đựng dư liệu.

Sau đó, hãy thực hiện Bộ lọc cải thiện 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 ô list và dải ô Điều khiếu nại như hình ảnh chụp màn hình hiển thị dưới đây:

Lưu ý. Hình hình ảnh trên cũng giống như các hình ảnh chụp screen khác trong bài hướng dẫn này hiển thị cách làm trong ô vùng điều kiện nhằm mục đích giúp phần lớn thứ trở nên ví dụ hơn. Vào trang tính của bạn, ô công thức nên làm trả về TRUE hoặc FALSE, tùy thuộc theo ô đầ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 vào MỘT CỘT

Khi lọc cực hiếm số, thông thường bạn cũng có thể muốn hiển thị chỉ hầu hết ô to hơn hay nhỏ dại hơn giá trị trung bình vào cột. Ví dụ:

Để lọc hàng gồm tổng to hơn giá trị trung bình, hãy thực hiện công thức sau đây trong vùng điều kiện:

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

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

=F5

Excel kế toán tài chính 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, cỗ lọc Excel tất cả một nhân tài sẵn tất cả để lọc những ô trống. Bằng phương pháp chọn hay vứt chọn (Trống) các hộp khắc ghi trong bảng chọn bộ lọc auto của Excel, bạn cũng có thể hiển thị chỉ đều hàng có các ô trống hay chứa tài liệu trong một hay các cột. Vấn đề ở chỗ này đó là bộ lọc ô trống gồm sẵn vào Excel chỉ chạy khi được dùng với hàm lôgic AND.

Nếu bạn có nhu cầu lọc những ô trống hay ô gồm chứa dữ liệu với hàm lôgic OR, hay được dùng điều kiện cho những ô trống hay các ô có chứa tài liệu cùng với vài điều kiện không giống nữa, thì hãy setup vùng đk bộ lọc cải thiện cùng với trong những công thức sau:

Lọc các ô trống:

ô_trên_cùng=””

Lọc các ô gồm chứa dữ 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 sinh sống cột A tuyệt B, hay ở hai cột, hãy lập thông số kỹ thuật vùng đk Bộ lọc cải thiện theo cách này:

=A6=""=B6=""

Trong đó, 6 là mặt hàng trên cùng gồm 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

Để gọi thêm giải pháp Bộ lọc nâng cao trong Excel hoạt động khi có không ít điều kiện, hãy lọc những hàng trong bảng mẫu mã 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) gồm chứa dữ liệu, và

Tổng lợi nhuận (cột C) phải lớn hơn 900.

Xem thêm:

Nói biện pháp khác, chúng ta muốn hiển thị những hàng đáp ứng nhu cầu các đk sau:

(Tổng doanh thu>900 và Vùng miền=có đựng dữ liệu) OR (Tổng doanh thu>900 and Danh mục=có cất dữ liệu)

Như bạn đã biết, trong vùng đk của bộ lọc cải thiện Excel, đk kết phù hợp với hàm lôgic and nên được nhập vào và một hàng, và đk kết hợp với hàm lôgic OR – ở những hàng khác nhau:

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

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

Điều kiện dựa vào công thức nên bao gồm hoặc là ô tiêu đề trống hay là tiêu đề không giống với ngẫu nhiên tiêu đề làm sao của bảng, giống như điều kiện tất 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ẽ các bạn đã biết, bộ lọc Số tất cả sẵn vào Excel có thiên tài hiển thị 10 danh mục đầu hay cuối. Nhưng nếu như khách hàng cần thanh lọc 3 quý hiếm đầu hay 5 quý giá cuối thì sao? trong trường vừa lòng này, bộ lọc nâng cao trong Excel sẽ sở hữu đượ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 danh mục cuối:

ô_trên_cùngdải ô, N)

Ví dụ, nhằm lọc 3 tổng lợi nhuận đầu, hãy chế tạo ra vùng đk cùng với phương pháp này:

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

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

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

Trong đó, F5 là ô bên trên cùng tất cả chứa dữ liệu trong cột Tổng doanh thu (không tính title 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 lợi nhuận đầu:

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

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

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

Một vào những bài viết trước của họ đã 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 kiếm điểm giống với điểm khác thân chúng. Sát bên các bí quyết Excel, quy chính sách định dạng có đk và qui định Duplicate Remover đã được kể tới trong bài hướng dẫn trên, bạn có thể sử dụng bộ lọc nâng cấp Excel để xuất các hàng có các giá trị kiểu như hay khác biệt trong nhì hay những cột. Để làm điều này, hãy nhập trong những công thức đơn giản sau đây vào vùng điều kiện:

Lọc điểm như là (sự trùng lặp) thân hai cột:

=B5=C5

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

=B5C5

Trong đó, B5 cùng C5 là các ô bên trên cùng gồm chứa tài liệu thuộc hai cột mà bạn có nhu cầu so sánh.

Lưu ý. lý lẽ Bộ lọc nâng cấp chỉ hoàn toàn có thể tìm điểm giống cùng điểm không giống trên và một hàng. Để tìm toàn bộ giá trị trực thuộc cột A cơ mà không mở ra trong cột B, hãy áp 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ử các bạn có một bảng béo có hàng ngàn hay hàng chục ngàn hàng, và bạn nhận được một danh sách ngắn thêm một đoạn chỉ chứa các danh mục yêu cầu dùng tại 1 thời điểm. Câu hỏi đặt ra là – làm cố gắng nào bạn cũng có thể tìm toàn bộ danh mục trong bảng – các danh mục xuất hiện hay không xuất hiện nay 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 mối cung cấp – các danh mục này cũng bên trong danh sách, hãy thực hiện công thức COUNTIF sau đây:

COUNTIF(list_to_match, top_data_cell)

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

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

Tất nhiên chúng ta không bị gò bó trong bài toán chỉ lọc bảng với 1 điều kiện.

Ví dụ, nhằm lọc những hàng phía trong danh sách, cơ mà chỉ các vùng miền bắc bộ thôi, hãy nhập hai điều kiện này vào và một hàng để chúng bao gồm thể vận động khi sử dụng hàm lôgic AND:

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

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

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

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

Để tìm toàn bộ danh mục vào bảng không thuộc danh sách nhỏ tuổi hơn, hãy xem xem kết quả của cách làm 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 – hạng mục không mở ra trong danh sách, hãy sử dụng đk sau:

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

Lưu ý.

Nếu list cần đối chiếu nằm sống trang tính khác, hãy có lẽ bạn thêm thương hiệu trang tính vào công thức, ví dụ =COUNTIF(Sheet2!$A$2:$A$7,B10).Nếu bạn có nhu cầu xuất công dụng sang một trang tính khác, hãy thực hiện Bộ lọc cải thiện ở 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ờ, lấy ví dụ về vùng điều kiện Bộ lọc nâng cấp phần mập xử lý quý hiếm số cùng giá trị cam kết tự. Bây giờ, là lúc cho mình thấy biện pháp xử lý ngày tháng năm.

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

Như các bạn đã biết, Microsoft Excel cung cấp hàm WEEKDAY quan trọng đặc biệt – hàm này trả về ngày trong tuần dựa trên ngày mon năm mang đến sẵn. Và đó là hàm mà bọn họ sẽ áp dụng trong vùng đk Bộ lọc nâng cấp Excel.

CÁCH LỌC NGÀY CUỐI TUẦN vào EXCEL

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

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

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

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

CÁCH LỌC NGÀY vào TUẦN trong EXCEL

Để lọc những ngày vào tuần, hãy chuyển đổi công thức bên trên đễ nó ko trả về tác dụng 1 (Chủ nhật) cùng 7 (Thứ bảy):

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

Đối cùng với bảng chủng loại của bọn chúng ta, công thức sau bổ ích rất nhiều:

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

Bên cạnh đó, bạn cũng có thể thêm một đk để lọc các ô trống: =B5””

Để lọc ngày tháng năm trong trang tính của người tiêu dùng theo bí quyết khác, hãy kiếm tìm hàm Date tương quan và cứ tự nhiên và thoải mái dùng nó vào vùng điều kiện Bộ lọc nâng cao Excel.

À thì, đó là cách bạn dùng cỗ lọc nâng cấp Excel cho những điều kiện phức tạp. Vớ nhiên, các lựa chọn của doanh nghiệp không bị giới hạn bởi những ví dụ được trao đổi trong bài xích hướng dẫn này, mục tiêu của cửa hàng chúng tôi là cung cấp cho mình vài ý tưởng có thể truyền cảm xúc để các bạn đi đúng hướng. Hãy ghi 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 mặt lại các bạn vào tuần tới!

Hiện tại, appmobiles.info 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. Công ty 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 vào 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 đến các học viên đăng kí trong tháng này, vậy còn è trừ gì nữa mà ko đăng kí ngay lập tức thôi. Thông tin chi tiết xem tại: