Hàm Lọc Có Đ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 lý giải này cho biết thêm giải pháp thực hiện bộ lọc nâng cấp trong Excel cùng cung cấp vài ba ví dụ vùng ĐK nâng cao để sinh sản bộ lọc phân minh dạng chữ, nhằm kiếm tìm điểm như là với khác thân hai cột, bí quyết xuât làm hồ sơ khớp cùng với list nhỏ tuổi hơn, cùng nhiều chưa dừng lại ở đó nữa.

Bạn đang xem: Hàm lọc có điều kiện trong excel

Trong nội dung bài viết trước, bọn họ đang đàm đạo những chu đáo không giống nhau của Bộ thanh lọc Nâng cao vào Excel cùng cách sử dụng nó để lọc sản phẩm cùng rất hàm lôgic AND với OR. Bây giờ đồng hồ bạn đã sở hữu kiến thức cơ bản, hãy chú ý các ví dụ vùng ĐK phức hợp rộng nhé – các ví dụ này hoàn toàn có thể bổ ích đến các bước của khách hàng đấy.

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

Vì phần nhiều những ví dụ về vùng điều kiện được bàn bạc vào bài giải đáp này sẽ có được những bí quyết khác biệt, hãy ban đầu bằng phương pháp xác định nguyên tắc rất cần thiết nhằm setup chúng một bí quyết cân xứng. Tin tôi đi, mẩu kỹ năng nhỏ tuổi nhoi này sẽ giúp bạn tiết kiệm chi phí không ít thời gian và bạn không còn cần đau đầu khi yêu cầu xác minh với sửa lỗi mang lại vùng ĐK phức hợp nữa – vùng điều kiện có không ít ĐK dựa trên công thức.


*

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

Vùng điều kiện bắt buộc tất cả tối thiểu 2 ô: ô cách làm cùng ô tiêu đề.

Ô title của điều kiện dựa trên cách làm hay là nhằm trống hay những 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 những ô của dải ô danh sách, hãy tsay đắm chiếu ô trên cùng cùng rất tài liệu bằng phương pháp áp dụng tsay mê chiếu ô kha khá chẳng hạn như A1.

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

lúc tmê man chiếu dải ô list vào bí quyết, hãy luôn sử dụng tham mê chiếu ô hoàn hảo và tuyệt vời nhất.

lúc vận dụng nhiều điều kiện, hãy nhập toàn bộ ĐK bên trên và một hàng và link chúng bằng toán thù tử AND, xuất xắc nhập mỗi điều kiện vào một trong những mặt hàng không giống nhau với 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

Các ví dụ sau đây vẫn chỉ các bạn giải pháp tạo cỗ thanh lọc vào Excel để cách xử lý những trách nhiệm tinh vi rộng – phần đa nhiệm vụ nhưng mà Bộ lọc Tự đụng Excel thường thì không cách 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ộ lọc Tự rượu cồn Excel, phép tắc Sở thanh lọc Nâng cao cũng ko tách biệt dạng chữ, tức là nó ko rõ ràng cam kết từ viết hoa và viết thường khi thanh lọc cực hiếm dạng ký từ bỏ. Tuy nhiên, bạn có thể dễ ợt kiếm tìm kiếm trong những lúc vẫn rõ ràng dạng chữ bằng cách áp dụng hàm EXACT vào điều kiện lọc cải thiện.

Ví dụ, nhằm thanh lọc mặt hàng chứa Chuối, ko rành mạch là CHUỐI xuất xắc chuối, hãy nhập phương pháp 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 mặt hàng 5 hàng đầu tiên đựng dư liệu.

Sau đó, hãy thực hiện Sở thanh 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 thông số kỹ thuật cho dải ô Danh sách với dải ô Điều khiếu nại như hình ảnh chụp màn hình dưới đây:

Lưu ý. Tấm hình bên trên tương tự như những ảnh chụp màn hình không giống vào bài hướng dẫn này hiển thị cách làm vào ô vùng điều kiện nhằm giúp đa số máy trsinh hoạt bắt buộc rõ ràng rộng. Trong trang tính của người sử dụng, ô bí quyết chỉ nên trả về TRUE hoặc FALSE, tùy vào ô trước tiên chứa dữ liệu tất cả khớp với ĐK giỏi 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ì chúng ta có thể ý muốn hiển thị chỉ phần đông ô lớn hơn tốt bé dại hơn quý hiếm trung bình trong cột. Ví dụ:

Để thanh lọc mặt hàng có tổng lớn hơn cực hiếm trung bình, hãy áp dụng cách làm sau đây vào vùng điều kiện:

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

Để thanh lọc hàng bao gồm tổng bé dại hơn quý giá mức độ vừa phải, hãy thực hiện cách làm 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 bạn vẫn biết, Bộ thanh lọc Excel gồm một thiên tài sẵn bao gồm nhằm lọc những ô trống. Bằng cách chọn tuyệt vứt lựa chọn (Trống) các hộp đánh dấu vào bảng lựa chọn Sở lọc Tự đụng của Excel, bạn cũng có thể hiển thị chỉ hầu như mặt hàng có các ô trống tốt cất tài liệu trong một xuất xắc 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 dùng cùng với hàm lôgic AND.

Nếu bạn có nhu cầu lọc những ô trống tốt ô tất cả chứa tài liệu cùng với hàm lôgic OR, thường dùng ĐK cho những ô trống giỏi những ô bao gồm chứa tài liệu với vài điều khiếu nại khác nữa, thì nên thiết lập vùng điều kiện cỗ thanh lọc nâng cao cùng với một trong số những bí quyết sau:

Lọc những ô trống:

ô_trên_cùng=””

Lọc các ô tất cả 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 mặt hàng bao gồm ô trống hoặc sinh sống cột A tốt B, xuất xắc ở cả 2 cột, hãy lập thông số kỹ thuật vùng ĐK Bộ thanh lọc Nâng cao Theo phong cách này:

=A6=""=B6=""

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

Để phát âm thêm phương pháp Bộ lọc Nâng cao vào Excel chuyển động lúc có rất nhiều điều kiện, hãy lọc những sản phẩm trong bảng mẫu theo những ĐK sau:

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

Tổng lợi nhuận (cột C) nên to hơn 900.

Xem thêm: Sự Khác Nhau Giữa Adobe Photoshop Cc Khác Cs6, Bingung Antara Adobe Cs Dan Cc

Nói phương pháp khác, chúng ta mong muốn hiển thị những sản phẩm đáp ứng nhu cầu các ĐK sau:

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

Nlỗi bạn đã biết, trong vùng ĐK của Bộ thanh lọc Nâng cao Excel, ĐK kết hợp với hàm lôgic AND cần được nhập vào và một hàng, và ĐK kết phù hợp với hàm lôgic OR – sinh sống những mặt hàng không giống nhau:

Bởi do một ĐK vào ví dụ này được diễn tả bởi cách làm (tất cả cất dữ liệu) cùng điều kiện còn lại gồm chứa toán thù tử đối chiếu (Tổng doanh thu>900), hãy nhằm tôi đề cập các bạn một vài điều:

Điều kiện được lập với toán tử so sánh cần bao gồm tiêu đề giống 100% title của bảng, giống như điều kiện Tổng doanh thu trong hình ảnh chụp màn hình trên.

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

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

Có lẽ bạn vẫn biết, Sở thanh lọc Số có sẵn vào Excel gồm tác dụng hiển thị 10 danh mục đầu xuất xắc cuối. Nhưng nếu như bạn yêu cầu thanh lọc 3 giá trị đầu tốt 5 quý hiếm cuối thì sao? Trong ngôi trường hòa hợp này, Bộ thanh lọc Nâng cao vào Excel sẽ sở hữu được sẵn các bí quyết bên 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 ví dụ như, để lọc 3 tổng lợi nhuận đầu, hãy sinh sản 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 doanh thu cuối, hãy áp dụng phương pháp này:

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

Trong đó, F5 là ô bên trên cùng bao gồm đựng dữ liệu trong cột Tổng lệch giá (ngoại trừ tiêu đề của cột).

Hình ảnh chụp screen dưới đây cho thấy cách làm lọc 3 tổng lợi nhuận đầu:

Lưu ý. Nếu dải danh sách chứa vài ba hàng bao gồm cùng quý giá ở trong N list đầu/cuối, toàn bộ sản phẩm như thế các sẽ được hiển thị, như hình ảnh chụp màn hình hiển thị bên 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 bọn họ sẽ giải thích các cách khác nhau nhằm so sánh nhì cột vào Excel rồi tìm điểm giống với điểm khác giữa chúng. Bên cạnh các công thức Excel, quy luật format có ĐK cùng hình thức Duplicate Remover đã được nói tới vào bài bác khuyên bảo trên, chúng ta có thể sử dụng Bộ lọc Nâng cao Excel để xuất những mặt hàng có các quý giá giống như giỏi không giống nhau trong nhị giỏi những cột. Để làm cho vấn đề này, hãy nhập một giữa những cách làm dễ dàng và đơn giản tiếp sau đây vào vùng điều kiện:

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

=B5=C5

Lọc điểm khác (giá trị độc nhất) giữa nhị cột:

=B5C5

Trong số đó, B5 cùng C5 là các ô trên cùng bao gồm chứa tài liệu nằm trong hai cột cơ mà bạn muốn đối chiếu.

Lưu ý. Công thay Bộ thanh lọc Nâng cao chỉ rất có thể tìm kiếm điểm giống với điểm không giống bên trên và một hàng. Để tra cứu toàn bộ quý giá trực thuộc cột A nhưng lại ko mở ra trong cột B, hãy thực hiện 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 gồm một bảng bự bao gồm hàng trăm ngàn hay hàng vạn hàng, cùng bạn nhận được một danh sách ngắn lại hơn nữa chỉ cất những danh mục nên dùng tại một thời điểm. Câu hỏi đề ra là – có tác dụng chũm làm sao chúng ta cũng có thể tìm tất cả danh mục trong bảng – những hạng mục lộ diện hay là không mở ra vào danh sách nlắp đó?

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

Để search tất cả hạng mục trong bảng mối cung cấp – các danh mục này cũng phía bên trong list, hãy thực hiện công thức COUNTIF sau đây:

COUNTIF(list_to_match, top_data_cell)

Giả sử list ngắn lại hơn nữa phía bên trong dải ô D2:D7, và các danh mục trong bảng cần được so sánh cùng với danh sách đó thì phía bên trong cột B bắt đầu tự sản phẩm 10, cách làm gồm dạng sau đây (hãy để ý bí quyết dùng tmê man chiếu tuyệt vời và 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 vấn đề chỉ thanh lọc bảng với một ĐK.

Ví dụ, nhằm lọc các hàng bên trong list, tuy nhiên chỉ các vùng miền Bắc thôi, hãy nhập hai ĐK này vào cùng một hàng nhằm chúng rất 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 tương tự nhau: =COUNTIF($D$2:$D$7,B10)

Nhỏng chúng ta có thể thấy trong ảnh chụp màn hình dưới đây, chỉ có nhì làm hồ sơ vào bảng khớp cùng với nhì ĐK nhưng thôi:

Lưu ý. Trong ví dụ này, họ áp dụng điểm tương tự hoàn hảo mang lại quý giá cam kết tự: =”=Miền Bắc” nhằm tra cứu hầu hết ô giống hệt cùng với chuỗi ký trường đoản cú xác định. Nếu chúng ta chỉ nhập ĐK Vùng miền như thế này Miền Bắc (không có vết bằng cùng lốt ngoặc kép), thì Microsoft Excel đã tìm tất cả hạng mục bắt đầu cùng với chuỗi ký kết từ bỏ khẳng định, ví dụ Miền Bắc Trung Sở.

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

Để tìm toàn bộ hạng mục trong bảng không ở trong list nhỏ dại rộng, hãy coi coi công dụng của cách làm COUNTIF bao gồm bởi 0 không:

COUNTIF(list_to_match, top_data_cell)=0

lấy ví dụ như, để thanh lọc danh mục vùng miền Bắc trong bảng – hạng mục ko xuất hiện trong list, hãy áp dụng ĐK 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 đề xuất so sánh nằm ở trang tính không giống, hãy chắc hẳn rằng chúng ta 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 có nhu cầu xuất kết quả qua một trang tính không giống, hãy thực hiện Bộ thanh lọc Nâng cao sinh sống trang tính cơ 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 tiếng, ví dụ về vùng điều kiện Bộ lọc Nâng cao đa phần xử lý quý hiếm số và quý hiếm ký từ bỏ. Bây tiếng, là thời điểm cho bạn thấy bí quyết xử lý ngày tháng năm.

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

Nhỏng bạn đang biết, Microsoft Excel cung ứng hàm WEEKDAY đặc biệt – hàm này trả về ngày trong tuần dựa trên tháng ngày năm mang đến sẵn. Và đấy là hàm mà bọn họ đã áp dụng trong vùng ĐK 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à, vào thuật ngữ WEEKDAY, 1 là Chủ nhật với 7 là Thứ đọng bảy, công thức lọc ngày vào ngày cuối tuần tất cả dạng nlỗi sau:

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

Trong ví dụ này, họ lọc ngày tháng năm trong cột B bước đầu tự sản phẩm 5, chính vì như vậy phương pháp lọc ngày vào 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 những ngày vào tuần, hãy thay đổi công thức bên trên đễ nó không trả về kết quả 1 (Chủ nhật) cùng 7 (Thđọng 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 bọn họ, phương pháp sau bổ ích cực kỳ nhiều:

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

Trong khi, bạn có thể thêm một ĐK để lọc những ô trống: =B5””

Để lọc ngày tháng năm vào trang tính của khách hàng Theo phong cách không giống, hãy tìm kiếm hàm Date tương quan và cứ đọng thoải mái và tự nhiên cần sử dụng nó trong vùng điều kiện Sở thanh lọc Nâng cao Excel.

À thì, đó là cách bạn dùng Sở thanh lọc Nâng cao Excel cho các ĐK phức tạp. Tất nhiên, những sàng lọc của công ty không bị số lượng giới hạn vày rất nhiều ví dụ được đàm đạo trong bài khuyên bảo này, mục tiêu của Shop chúng tôi là cung cấp cho mình vài phát minh có thể truyền cảm xúc nhằm các bạn đi đúng phía. Hãy nhớ là con phố đổi mới Chuyên Viên luôn luôn đi đôi thực hành thực tế. Cảm ơn chúng ta sẽ gọi nội dung bài viết và hẹn gặp mặt lại chúng ta vào tuần tới!

Hiện tại, goldmarkcity-hanoi.com 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 cho nâng cao với các chuyên gia hàng đầu tại Việt Nam. Chúng tôi sẽ có đến mang lại khách 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 vào công việc. Ngoài ra, chúng tôi sẽ có nhiều cmùi hương trình khuyến mại lớn cho các học viên đăng kí trong tháng này, vậy còn nai lưng trừ gì nữa mà không đăng kí ngay lập tức thôi. tin tức cụ thể coi tại: