Lọc Dữ Liệu Với Nhiều Điều Kiện Trong Excel

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

Bài hướng dẫn này cho thấy biện pháp áp dụng bộ thanh lọc nâng cấp trong Excel cùng hỗ trợ vài ba ví dụ vùng ĐK cải thiện để sản xuất cỗ thanh lọc biệt lập dạng chữ, để kiếm tìm điểm giống như cùng không giống giữa hai cột, phương pháp xuât làm hồ sơ khớp với list nhỏ rộng, với các hơn thế nữa.

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

Trong nội dung bài viết trước, bọn họ sẽ bàn luận các chu đáo khác biệt của Sở thanh lọc Nâng cao vào Excel với phương pháp thực hiện nó nhằm lọc hàng cùng với hàm lôgic AND và OR. Bây tiếng bạn đã sở hữu kỹ năng cơ bản, hãy chú ý các ví dụ vùng ĐK tinh vi hơn nhé – những ví dụ này rất có thể bổ ích mang lại 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 nhiều các ví dụ về vùng ĐK được trao đổi trong bài xích khuyên bảo này sẽ có những công thức không giống nhau, hãy bắt đầu bằng phương pháp khẳng định quy tắc thiết yếu nhằm setup bọn chúng một giải pháp cân xứng. Tin tôi đi, mẩu kỹ năng và kiến thức bé dại nhoi này để giúp đỡ các bạn tiết kiệm ngân sách và chi phí không hề ít thời gian và chúng ta không còn đề nghị hoa mắt Lúc yêu cầu xác minh và sửa lỗi mang lại vùng ĐK tinh vi nữa – vùng điều kiện có khá nhiều ĐK dựa vào bí quyết.


*

Công thức các bạn áp dụng mang lại vùng ĐK phải ghi nhận review TRUE tuyệt FALSE.

Vùng điều kiện đề xuất có tối thiểu 2 ô: ô phương pháp với ô title.

Ô title của ĐK dựa trên phương pháp Hay là nhằm trống Hay là khác cùng với bất kỳ tiêu đề nào của bảng (dải ô danh sách).

Để cách làm được Reviews trong mỗi ô của dải ô danh sách, hãy tsay đắm chiếu ô bên trên thuộc cùng với dữ liệu bằng phương pháp áp dụng ttê mê chiếu ô kha khá ví dụ như A1.

Để công thức được đánh giá chỉ cho một ô hay như là 1 dải ô khẳng định, hãy tahm chiếu ô xuất xắc dải ô đó bằng phương pháp áp dụng tsay đắm chiếu ô tuyệt vời chẳng hạn như $A$1.

Khi tđắm đuối chiếu dải ô list vào cách làm, hãy luôn luôn áp dụng tđắm đuối chiếu ô hoàn hảo.

Khi áp dụng các điều kiện, hãy nhập tất cả ĐK bên trên cùng một sản phẩm với links bọn chúng bằng tân oán tử AND, tuyệt nhập từng ĐK vào một sản phẩm không giống nhau và link chúng bằng toán thù tử OR.

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

Các ví dụ sau đây đã chỉ chúng ta biện pháp chế tạo ra cỗ lọc vào Excel để xử trí các trọng trách tinh vi rộng – những trách nhiệm cơ mà Sở thanh lọc Tự hễ Excel thường thì không giải pháp xử lý được.

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

Giống nhỏng Bộ thanh lọc Tự động Excel, dụng cụ Bộ lọc Nâng cao cũng ko khác nhau dạng chữ, Có nghĩa là nó ko riêng biệt cam kết tự viết hoa cùng viết hay Khi lọc quý giá dạng ký kết từ bỏ. Tuy nhiên, chúng ta cũng có thể thuận tiện tìm kiếm tìm trong lúc vẫn minh bạch dạng chữ bằng phương pháp sử dụng hàm EXACT vào ĐK lọc nâng cấp.

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

=EXACT(B5, “Chuối”)

Trong số đó B là cột cất thương hiệu danh mục, cùng sản phẩm 5 bậc nhất tiên cất dư liệu.

Sau kia, hãy thực hiện Sở thanh lọc Nâng cao Excel bằng cách nhấp nút Advanced bên trên tab Data, rồi định cấu hình cho dải ô Danh sách cùng dải ô Điều kiện như hình ảnh chụp màn hình hiển thị bên dưới đây:

Lưu ý. Hình ảnh bên trên tương tự như các hình ảnh chụp màn hình khác trong bài khuyên bảo này hiển thị phương pháp trong ô vùng điều kiện nhằm mục tiêu góp hầu hết thiết bị trngơi nghỉ yêu cầu rõ ràng rộng. Trong trang tính của bạn, ô cách làm chỉ nên trả về TRUE hoặc FALSE, tùy thuộc theo ô trước tiên chứa tài liệu gồm khớp cùng với ĐK 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

Lúc lọc cực hiếm số, thường thì chúng ta có thể ước ao hiển thị chỉ hầu như ô lớn hơn xuất xắc nhỏ tuổi rộng cực hiếm vừa đủ trong cột. Ví dụ:

Để lọc hàng tất cả tổng to hơn quý hiếm trung bình, hãy áp dụng công thức dưới đây vào vùng điều kiện:

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

Để thanh lọc hàng tất cả tổng nhỏ tuổi hơn quý giá vừa đủ, hãy sử dụng phương pháp sau đây:

=F5

Excel kế tân oán 1 thầy 1 trò

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

Có lẽ phần đa người vẫn biết, Sở thanh lọc Excel gồm một tác dụng sẵn tất cả để lọc những ô trống. Bằng phương pháp chọn hay bỏ lựa chọn (Trống) những hộp khắc ghi vào bảng chọn Bộ lọc Tự rượu cồn của Excel, bạn có thể hiển thị chỉ đều mặt hàng bao gồm các ô trống tuyệt đựng dữ liệu vào một tốt những cột. Vấn đề tại đây đó là bộ lọc ô trống có sẵn trong Excel chỉ chạy khi được sử dụng cùng với hàm lôgic AND.

Nếu bạn muốn lọc những ô trống xuất xắc ô có đựng dữ liệu cùng với hàm lôgic OR, hay được dùng ĐK cho những ô trống xuất xắc những ô gồm cất dữ liệu cùng rất đôi nét kiện khác nữa, thì hãy thiết đặt vùng điều kiện cỗ lọc nâng cấp cùng với một giữa những phương pháp sau:

Lọc những ô trống:

ô_trên_cùng=””

Lọc những ô bao 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 các mặt hàng tất cả ô trống hoặc làm việc cột A tuyệt B, tốt ở 2 cột, hãy lập thông số kỹ thuật vùng ĐK Sở lọc Nâng cao theo cách này:

=A6=""=B6=""

Trong đó, 6 là sản phẩm bên trên thuộc tất cả đựng 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

Để gọi thêm phương pháp Sở lọc Nâng cao vào Excel vận động Khi có không ít điều kiện, hãy lọc các hàng vào bảng mẫu mã theo những ĐK sau:

Hoặc là Vùng miền (cột A) Hay là Danh mục (cột B) bao gồm cất dữ liệu, và

Tổng doanh thu (cột C) đề nghị lớn hơn 900.

Xem thêm: Các Toán Tử So Sánh Ngày Tháng Trong Excel Để Tính Toán Ngày Tháng Hiệu Quả

Nói giải pháp khác, họ ý muốn hiển thị những hàng thỏa mãn nhu cầu các điều kiện sau:

(Tổng doanh thu>900 AND Vùng miền=bao gồm đựng dữ liệu) OR (Tổng doanh thu>900 AND Danh mục=gồm đựng dữ liệu)

Nhỏng bạn đã biết, trong vùng ĐK của Bộ thanh lọc Nâng cao Excel, điều kiện kết hợp với hàm lôgic AND phải được nhtràn lên và một sản phẩm, cùng điều kiện kết hợp với hàm lôgic OR – sinh hoạt những sản phẩm không giống nhau:

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

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

Điều khiếu nại dựa trên cách làm nên bao gồm hoặc là ô tiêu đề trống hoặc là tiêu đề không giống với ngẫu nhiên tiêu đề như thế nào của bảng, hệt như điều kiện Có chứa dữ liệu trong hình họa chụp screen bên trên.

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

Có lẽ các bạn đang biết, Sở lọc Số có sẵn vào Excel tất cả tài năng hiển thị 10 danh mục đầu tuyệt cuối. Nhưng nếu bạn phải thanh lọc 3 quý giá đầu xuất xắc 5 cực hiếm cuối thì sao? Trong trường thích hợp này, Sở lọc Nâng cao trong Excel sẽ có sẵn các cách làm bên 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ùngdải ô, N)

lấy một ví dụ, nhằm thanh lọc 3 tổng lợi nhuận đầu, hãy sinh sản vùng ĐK cùng với cách làm này:

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

Để xuất 3 tổng doanh thu cuối, hãy áp dụng bí quyết này:

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

Trong đó, F5 là ô bên trên thuộc có cất tài liệu vào cột Tổng doanh thu (xung quanh title của cột).

Ảnh chụp screen sau đây cho biết cách làm lọc 3 tổng lợi nhuận đầu:

Lưu ý. Nếu dải danh sách cất vài ba hàng bao gồm cùng quý giá thuộc N list đầu/cuối, toàn bộ mặt hàng như thế số đông sẽ được hiển thị, nhỏng ảnh chụp screen dưới đây:

Tìm đọc 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

trong số những nội dung bài viết trước của chúng ta đã lý giải các cách khác nhau nhằm mục đích đối chiếu nhì cột trong Excel rồi kiếm tìm điểm kiểu như cùng điểm không giống thân bọn chúng. Bên cạnh các cách làm Excel, quy pháp luật format gồm ĐK và phương tiện Duplicate Remover đã được nói đến vào bài xích gợi ý bên trên, bạn cũng có thể áp dụng Bộ lọc Nâng cao Excel nhằm xuất những mặt hàng gồm những giá trị kiểu như tuyệt không giống nhau trong nhì giỏi những cột. Để làm vấn đề này, hãy nhập một trong những công thức dễ dàng tiếp sau đây vào vùng điều kiện:

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

=B5=C5

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

=B5C5

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

Lưu ý. Công nắm Bộ lọc Nâng cao chỉ rất có thể tìm kiếm điểm giống và điểm khác trên và một sản phẩm. Để tra cứu tất cả quý hiếm ở trong cột A tuy vậy không xuất hiện vào cột B, hãy áp dụng phương pháp 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 gồm một bảng Khủng gồm hàng nghìn giỏi hàng chục ngàn sản phẩm, và chúng ta nhận được một danh sách ngắn thêm chỉ chứa những hạng mục bắt buộc cần sử dụng trên 1 thời điểm. Câu hỏi đề ra là – có tác dụng nỗ lực làm sao bạn cũng có thể search toàn bộ danh mục vào bảng – các danh mục xuất hiện hay là không mở ra trong list nđính đó?

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

Để tìm kiếm toàn bộ danh mục trong bảng mối cung cấp – các hạng mục này cũng bên trong danh sách, hãy thực hiện bí quyết COUNTIF sau đây:

COUNTIF(list_to_match, top_data_cell)

Giả sử list ngắn lại bên trong dải ô D2:D7, với những hạng mục trong bảng cần được so sánh với list đó thì bên trong cột B ban đầu từ mặt hàng 10, bí quyết tất cả dạng sau đây (hãy chú ý biện pháp cần sử dụng tmê man chiếu tuyệt vời nhất và tương đối):

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

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

Ví dụ, để thanh lọc các sản phẩm bên trong list, nhưng lại 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 mặt hàng để chúng có thể vận độ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)

Nlỗi bạn có thể thấy trong hình ảnh chụp màn hình hiển thị sau đây, chỉ có hai làm hồ sơ trong bảng khớp với nhị ĐK nhưng mà thôi:

Lưu ý. Trong ví dụ này, họ áp dụng điểm như là tuyệt đối hoàn hảo đến giá trị ký kết tự: =”=Miền Bắc” để tìm rất nhiều ô tương đồng cùng 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ư vậy này Miền Bắc (không có vết bằng và vệt ngoặc kép), thì Microsoft Excel đang tìm tất cả hạng mục bước đầu cùng với chuỗi ký kết từ khẳng đị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

Để kiếm tìm toàn bộ danh mục trong bảng không ở trong list nhỏ tuổi rộng, hãy xem coi 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ụ, để thanh lọc danh mục vùng miền Bắc vào bảng – hạng mục không mở ra trong danh sách, hãy thực hiện đ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 list buộc phải đối chiếu nằm tại vị trí trang tính không giống, hãy chắc hẳn rằng bạn thêm tên trang tính vào phương pháp, ví dụ =COUNTIF(Sheet2!$A$2:$A$7,B10).Nếu bạn muốn xuất kết quả qua một trang tính khác, hãy sử dụng Sở thanh lọc Nâng cao sinh sống trang tính mà bạn có nhu cầu xuất tác dụng.

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

Nãy tiếng, ví dụ về vùng ĐK Bộ thanh lọc Nâng cao phần nhiều cách xử trí quý hiếm số cùng cực hiếm ký kết trường đoản cú. Bây giờ đồng hồ, là thời điểm cho chính mình thấy giải pháp xử trí tháng ngày năm.

Sở lọc Ngày Tháng Năm tất cả sẵn vào Excel cung ứng những lựa chọn liên quan cho nhiều trường hợp. đa phần, nhưng chưa hẳn tất cả! lấy ví dụ như, nếu như khách hàng bao gồm danh sách ngày tháng năm với được kinh nghiệm cần thanh lọc những ngày cuối tuần cùng trong tuần, các bạn sẽ làm cho núm nào?

Nlỗi bạn đang biết, Microsoft Excel hỗ trợ hàm WEEKDAY đặc biệt quan trọng – hàm này trả về ngày vào tuần dựa trên ngày tháng năm mang lại sẵn. Và đây là hàm mà lại bọn họ đã áp dụng trong vùng ĐK Bộ lọc Nâng cao Excel.

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

Hãy nhớ rằng, vào thuật ngữ WEEKDAY, một là Chủ nhật cùng 7 là Thứ đọng bảy, cách làm thanh lọc ngày vào ngày cuối tuần có dạng nlỗi sau:

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

Trong ví dụ này, chúng ta thanh lọc tháng ngày năm trong cột B ban đầu tự mặt hàng 5, chính vì thế cách làm thanh lọc ngày vào ngày cuối tuần bao gồm dạng như sau:

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

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

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

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

Đối cùng với bảng mẫu của bọn họ, bí quyết sau bổ ích siêu nhiều:

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

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

Để lọc tháng ngày năm trong trang tính của khách hàng theo cách khác, hãy tìm kiếm hàm Date tương quan cùng cđọng tự nhiên sử dụng nó vào vùng ĐK Sở thanh lọc Nâng cao Excel.

À thì, đây là phương pháp các bạn cần sử dụng Bộ thanh lọc Nâng cao Excel cho những điều kiện phức tạp. Tất nhiên, các tuyển lựa của chúng ta không biến thành giới hạn bởi đông đảo ví dụ được đàm luận vào bài bác lý giải này, mục đích của Shop chúng tôi là hỗ trợ cho mình vài ba phát minh hoàn toàn có thể truyền cảm giác để các bạn đi đúng hướng. Hãy hãy nhờ rằng tuyến đường trở nên chuyên gia luôn luôn đi đôi thực hành thực tế. Cảm ơn chúng ta đang hiểu bài viết cùng hứa hẹn chạm chán lại các bạn vào tuần tới!

Bây Giờ, bloginar.net 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 dạng mang lại nâng cao với các Chuyên Viên hàng đầu tại Việt Nam. Chúng tôi sẽ sở hữu đến đến 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 cmùi hương trình khuyến mại lớn mang lại các học viên đăng kí vào tháng này, vậy còn trần trừ gì nữa mà không đăng kí tức thì thôi. tin tức chi tiết coi tại: