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 thực hiện cỗ thanh lọc nâng cao vào Excel với cung ứng vài ví dụ vùng điều kiện nâng cấp nhằm tạo ra cỗ thanh lọc riêng biệt dạng chữ, để tra cứu điểm như thể cùng không giống giữa nhị cột, phương pháp xuât làm hồ sơ khớp cùng với list nhỏ rộng, với các không dừng lại ở đó nữa.

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

Trong bài viết trước, bọn họ đang bàn thảo những điều tỉ mỷ không giống nhau của Sở lọc Nâng cao trong Excel và biện pháp sử dụng nó nhằm lọc mặt hàng cùng với hàm lôgic AND và OR. Bây tiếng bạn đã có kỹ năng cơ bản, hãy chu đáo các ví dụ vùng ĐK tinh vi rộng nhé – những ví dụ này hoàn toàn có thể có ích cho các bước của chúng ta đấy.

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

Vì đa số những ví dụ về vùng điều kiện được thảo luận trong bài xích trả lời này sẽ có những phương pháp khác biệt, hãy bước đầu bằng cách khẳng định luật lệ cần thiết để thiết đặt chúng một biện pháp tương xứng. Tin tôi đi, mẩu kiến thức và kỹ năng bé dại nhoi này sẽ giúp đỡ bạn tiết kiệm ngân sách không hề ít thời gian và các bạn không hề đề xuất đau đầu lúc yêu cầu xác minh cùng sửa lỗi cho vùng ĐK tinh vi nữa – vùng ĐK có rất nhiều ĐK dựa trên cách làm.


*

Công thức bạn sử dụng đến vùng điều kiện phải ghi nhận reviews TRUE tốt FALSE.

Vùng ĐK yêu cầu có tối thiểu 2 ô: ô bí quyết cùng ô title.

Ô tiêu đề của điều kiện dựa vào phương pháp Hay là nhằm trống Hay những khác với ngẫu nhiên tiêu đề làm sao của bảng (dải ô danh sách).

Để công thức được nhận xét trong mỗi ô của dải ô list, hãy tđê mê chiếu ô trên thuộc cùng rất tài liệu bằng phương pháp sử dụng ttê mê chiếu ô tương đối ví dụ như A1.

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

lúc tđam mê chiếu dải ô danh sách trong bí quyết, hãy luôn luôn sử dụng tmê mệt chiếu ô hoàn hảo nhất.

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

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

Các ví dụ sau đây vẫn chỉ bạn cách sinh sản bộ lọc vào Excel để xử trí các trọng trách tinh vi hơn – rất nhiều trọng trách nhưng mà Sở thanh lọc Tự hễ Excel thông thường không cách xử trí được.

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

Giống nlỗi Bộ lọc Tự cồn Excel, chế độ Sở thanh lọc Nâng cao cũng ko rành mạch dạng văn bản, tức là nó không rành mạch ký trường đoản cú viết hoa với viết hay Lúc lọc cực hiếm dạng ký kết trường đoản cú. Tuy nhiên, chúng ta có thể thuận tiện search kiếm trong lúc vẫn rành mạch dạng chữ bằng phương pháp thực hiện hàm EXACT vào điều kiện lọc nâng cao.

lấy một ví dụ, nhằm lọc sản phẩm đựng Chuối, không minh bạch là CHUỐI xuất xắc 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 thương hiệu danh mục, cùng hàng 5 bậc nhất tiên cất dư liệu.

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

Lưu ý. Hình ảnh trên cũng tương tự các hình ảnh chụp màn hình hiển thị khác trong bài bác hướng dẫn này hiển thị công thức trong ô vùng ĐK nhằm mục đích góp đều thiết bị trngơi nghỉ yêu cầu ví dụ rộng. Trong trang tính của khách hàng, ô phương pháp chỉ nên trả về TRUE hoặc FALSE, tùy thuộc vào ô trước tiên đựng dữ liệu tất cả khớp với ĐK tốt 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 thanh lọc giá trị số, thường thì chúng ta có thể ý muốn hiển thị chỉ các ô lớn hơn tốt nhỏ tuổi rộng giá trị vừa phải trong cột. Ví dụ:

Để lọc mặt hàng gồm tổng to hơn cực hiếm mức độ vừa phải, hãy áp dụng cách làm dưới đây vào vùng điều kiện:

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

Để lọc mặt hàng bao gồm tổng nhỏ tuổi rộng quý hiếm mức độ vừa phải, hãy áp dụng công thức 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ẽ hầu hết tín đồ đã biết, Bộ thanh lọc Excel tất cả một tính năng sẵn bao gồm nhằm lọc các ô trống. Bằng biện pháp chọn xuất xắc vứt chọn (Trống) những vỏ hộp khắc ghi trong bảng lựa chọn Bộ thanh lọc Tự hễ của Excel, bạn cũng có thể hiển thị chỉ phần đông hàng tất cả các ô trống tốt cất dữ liệu vào một xuất xắc nhiều cột. Vấn đề tại chỗ này sẽ là cỗ lọc ô trống tất cả sẵn vào Excel chỉ chạy Lúc được dùng với hàm lôgic AND.

Nếu bạn có nhu cầu lọc các ô trống giỏi ô gồm chứa tài liệu cùng với hàm lôgic OR, hay được sử dụng điều kiện cho các ô trống tốt những ô có đựng dữ liệu với đôi nét kiện không giống nữa, thì nên cài đặt vùng ĐK cỗ thanh lọc cải thiện với một trong số những phương pháp sau:

Lọc các ô trống:

ô_trên_cùng=””

Lọc các ô bao gồm cất dữ liệu:

ô_trên_cùng””

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

Để lọc những sản phẩm có ô trống hoặc sinh hoạt cột A giỏi B, tuyệt ở 2 cột, hãy lập cấu hình vùng điều kiện Bộ thanh lọc Nâng cao theo cách này:

=A6=""=B6=""

Trong số đó, 6 là hàng bên trên cùng bao 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 bí quyết Sở lọc Nâng cao vào Excel chuyển động lúc có rất nhiều ĐK, hãy thanh lọc những sản phẩm trong bảng mẫu mã theo các điều kiện sau:

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

Tổng lệch giá (cột C) bắt buộc lớn hơn 900.

Xem thêm: Phần Mềm Quản Lý Công Trình Xây Dựng Bằng Excel Vba, Phần Mềm Quản Lý Công Trình Xây Dựng

Nói biện pháp khác, chúng ta ao ước hiển thị những hàng thỏa mãn nhu cầu các ĐK sau:

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

Nhỏng bạn sẽ biết, vào vùng điều kiện của Sở lọc Nâng cao Excel, điều kiện kết hợp với hàm lôgic AND bắt buộc được nhập vào cùng một hàng, với điều kiện kết hợp với hàm lôgic OR – ngơi nghỉ các hàng khác nhau:

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

Điều khiếu nại được lập với toán thù tử đối chiếu nên tất cả title giống 100% title của bảng, y như ĐK Tổng lợi nhuận vào hình ảnh chụp screen bên trên.

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

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

Có lẽ bạn vẫn biết, Bộ lọc Số tất cả sẵn trong Excel gồm anh tài hiển thị 10 hạng mục đầu giỏi cuối. Nhưng nếu khách hàng cần thanh lọc 3 quý hiếm đầu tốt 5 quý giá cuối thì sao? Trong ngôi trường đúng theo này, Sở thanh lọc Nâng cao trong Excel sẽ sở hữu sẵn những phương pháp 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ùngdải ô, N)

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

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

Để xuất 3 tổng lệch giá cuối, hãy thực hiện phương pháp này:

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

Trong số đó, F5 là ô bên trên thuộc có cất dữ liệu trong cột Tổng lệch giá (kế bên tiêu đề của cột).

Ảnh chụp screen tiếp sau đây cho thấy bí quyết thanh lọc 3 tổng lợi nhuận đầu:

Lưu ý. Nếu dải list đựng vài ba hàng có cùng cực hiếm trực thuộc N list đầu/cuối, toàn bộ mặt hàng như thế phần đa sẽ tiến hành hiển thị, như hình ảnh chụp màn hình 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 HAI CỘT

giữa những bài viết trước của bọn họ đang lý giải những cách khác biệt nhằm mục đích đối chiếu nhị cột trong Excel rồi tìm điểm kiểu như và điểm không giống thân bọn chúng. Bên cạnh những bí quyết Excel, quy nguyên tắc định hình bao gồm ĐK với giải pháp Duplicate Remover đã làm được nói tới trong bài xích trả lời trên, bạn cũng có thể thực hiện Bộ thanh lọc Nâng cao Excel nhằm xuất những sản phẩm tất cả các giá trị giống như giỏi khác biệt vào nhì xuất xắc nhiều cột. Để làm cho vấn đề đó, hãy nhập một giữa những cách làm đơn giản 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 hai cột:

=B5=C5

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

=B5C5

Trong đó, B5 cùng C5 là các ô trên thuộc tất cả cất tài liệu trực thuộc nhì cột cơ mà bạn có nhu cầu so sánh.

Lưu ý. Công nỗ lực Sở thanh lọc Nâng cao chỉ rất có thể tra cứu điểm kiểu như cùng điểm khác trên và một mặt hàng. Để tìm toàn bộ giá trị trực thuộc cột A tuy vậy không mở ra vào cột B, hãy thực hiện cách làm 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 to có hàng trăm hay hàng ngàn sản phẩm, cùng các bạn nhận thấy một list ngắn thêm chỉ đựng các hạng mục yêu cầu dùng tại 1 thời điểm. Câu hỏi đề ra là – làm cố gắng nào bạn có thể kiếm tìm tất cả danh mục trong bảng – các hạng mục xuất hiện thêm hay là không lộ diện trong list ngắn đó?

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

Để tìm tất cả hạng mục vào bảng nguồn – các hạng mục này cũng phía trong danh sách, hãy sử dụng cách làm 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ới những hạng mục vào bảng cần được đối chiếu với list đó thì phía trong cột B bắt đầu trường đoản cú mặt hàng 10, công thức bao gồm dạng sau đây (hãy để ý bí quyết dùng tmê mệt chiếu tuyệt vời và hoàn hảo nhất với tương đối):

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

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

lấy một ví dụ, để thanh lọc những hàng bên trong danh sách, cơ mà chỉ các vùng miền Bắc thôi, hãy nhập hai ĐK này vào và một mặt hàng nhằm chúng hoàn toàn có thể chuyển động khi sử dụng hàm lôgic AND:

Vùng miền: ="=Miền Bắc" Danh mục như thể 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ị dưới đây, chỉ bao gồm nhị hồ sơ trong bảng khớp cùng với nhì điều kiện cơ mà thôi:

Lưu ý. Trong ví dụ này, họ thực hiện điểm như thể tuyệt vời và hoàn hảo nhất đến giá trị cam kết tự: =”=Miền Bắc” nhằm tìm kiếm các ô đồng nhất với chuỗi ký kết trường đoản cú xác minh. Nếu các bạn chỉ nhập ĐK Vùng miền như vậy này Miền Bắc (không tồn tại lốt bằng cùng vệt ngoặc kép), thì Microsoft Excel đang tra cứu tất cả hạng mục bước đầu với chuỗi ký 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

Để search toàn bộ hạng mục vào bảng không ở trong danh sách nhỏ tuổi rộng, hãy xem xem kết quả của cách làm COUNTIF bao gồm bằng 0 không:

COUNTIF(list_to_match, top_data_cell)=0

Ví dụ, nhằm lọc hạng mục vùng miền Bắc trong bảng – danh mục ko lộ diện vào 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 ko trùng khớp: =COUNTIF($D$2:$D$7,B10)=0

Lưu ý.

Nếu danh sách đề nghị so sánh nằm tại trang tính khác, hãy chắc hẳn rằng bạn thêm thương hiệu trang tính vào cách làm, ví dụ =COUNTIF(Sheet2!$A$2:$A$7,B10).Nếu bạn muốn xuất công dụng qua một trang tính khác, hãy thực hiện Bộ lọc Nâng cao sinh hoạt trang tính mà bạn muốn xuất hiệu quả.

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 xử lý quý hiếm số với quý giá ký từ. Bây giờ, là thời điểm cho chính mình thấy biện pháp xử trí tháng ngày năm.

Bộ thanh lọc Ngày Tháng Năm bao gồm sẵn vào Excel cung cấp các tuyển lựa liên quan mang đến nhiều trường hợp. Nhiều, nhưng không hẳn tất cả! lấy một ví dụ, nếu bạn tất cả danh sách tháng ngày năm và được yên cầu đề xuất lọc những ngày vào cuối tuần cùng vào tuần, các bạn sẽ có tác dụng nỗ lực nào?

Nhỏng bạn đã biết, Microsoft Excel cung ứng hàm WEEKDAY quan trọng – hàm này trả về ngày trong tuần dựa trên tháng ngày năm đến sẵn. Và đấy là hàm cơ mà bọn họ sẽ sử dụng trong vùng điều kiện Sở thanh lọc Nâng cao Excel.

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

Hãy hãy nhớ là, trong thuật ngữ WEEKDAY, một là Chủ nhật với 7 là Thứ đọng bảy, công thức lọc ngày vào cuối tuần gồm 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, bọn họ lọc ngày tháng năm trong cột B ban đầu trường đoản cú hàng 5, chính vì như vậy bí quyết thanh lọc ngày vào buổi tối cuối tuần tất cả dạng nhỏng 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 vào tuần, hãy biến hóa công thức trên đễ nó ko trả về tác dụng 1 (Chủ nhật) cùng 7 (Thđọng bảy):

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

Đối cùng với bảng mẫu của họ, bí quyết sau hữu ích rất nhiều:

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

Hình như, chúng ta cũng có thể thêm 1 điều kiện nhằm thanh lọc những ô trống: =B5””

Để thanh lọc tháng ngày năm trong trang tính của người tiêu dùng theo cách không giống, hãy search hàm Date liên quan cùng cứ thoải mái và tự nhiên dùng nó trong vùng điều kiện Sở thanh lọc Nâng cao Excel.

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

Hiện tại, theartinpixels.com Học Excel Online đã mở rất nhiều các khóa học đào tạo về Học Excel cơ phiên bản cho nâng cao với các Chuyên Viên hàng đầu tại Việt Nam. Chúng tôi sẽ có đến đến người mua hàng 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 đã có nhiều cmùi hương trình khuyến mại lớn cho 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 cụ thể xem tại: