HÀM LỌC CÓ ĐIỀU KIỆN

  -  
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 trả lời này cho thấy giải pháp thực hiện bộ lọc nâng cấp trong Excel với hỗ trợ vài ba ví dụ vùng ĐK cải thiện để chế tạo bộ lọc rõ ràng dạng văn bản, nhằm kiếm tìm điểm giống cùng không giống giữa hai cột, phương pháp xuât làm hồ sơ khớp cùng với danh sách bé dại rộng, cùng nhiều không dừng lại ở đó nữa.

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

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


*

Công thức chúng ta áp dụng mang đến vùng điều kiện phải biết Review TRUE giỏi FALSE.

Vùng điều kiện nên gồm ít nhất 2 ô: ô bí quyết với ô title.

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

Để phương pháp được đánh giá trong mỗi ô của dải ô danh sách, hãy tsay mê chiếu ô trên cùng cùng với tài liệu bằng cách thực hiện tmê mẩn chiếu ô kha khá ví dụ như A1.

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

Lúc tsay đắm chiếu dải ô list vào cách làm, hãy luôn luôn sử dụng tyêu thích chiếu ô hoàn hảo và tuyệt vời nhất.

Khi vận dụng những điều kiện, hãy nhập tất cả điều kiện bên trên cùng một hàng với links bọn chúng bằng toán thù tử AND, tốt nhập từng ĐK vào một mặt hàng không giống nhau và liên kết chúng bởi 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ụ dưới đây vẫn chỉ các bạn phương pháp chế tác cỗ lọc vào Excel nhằm giải pháp xử lý những trách nhiệm tinh vi hơn – các nhiệm vụ mà Bộ thanh lọc Tự hễ Excel thường thì ko xử trí đượ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ự rượu cồn Excel, nguyên tắc Sở lọc Nâng cao cũng ko phân minh dạng chữ, Có nghĩa là nó ko sáng tỏ ký tự viết hoa với viết hay khi thanh lọc quý hiếm dạng cam kết trường đoản cú. Tuy nhiên, chúng ta có thể dễ ợt tìm tìm trong những khi vẫn rõ ràng dạng chữ bằng cách sử dụng hàm EXACT vào điều kiện lọc cải thiện.

lấy một ví dụ, nhằm thanh lọc hàng đựng Chuối, không minh bạch là CHUỐI tốt chuối, hãy nhập công thức sau vào vùng điều kiện:

=EXACT(B5, “Chuối”)

Trong số đó B là cột chứa tên hạng mục, và hàng 5 hàng đầu tiên cất dư liệu.

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

Lưu ý. Hình ảnh trên cũng giống như các hình ảnh chụp màn hình hiển thị khác vào bài xích gợi ý này hiển thị bí quyết vào ô vùng điều kiện nhằm mục tiêu góp hồ hết lắp thêm trnghỉ ngơi yêu cầu rõ ràng rộng. Trong trang tính của doanh nghiệp, ô phương pháp chỉ nên trả về TRUE hoặc FALSE, tùy từng ô đầu tiên chứa tài liệu bao gồm khớp với ĐK xuất xắc 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ường chúng ta cũng có thể ao ước hiển thị chỉ rất nhiều ô to hơn hay bé dại hơn quý hiếm mức độ vừa phải trong cột. Ví dụ:

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

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

Để thanh lọc mặt hàng tất cả tổng bé dại hơn cực hiếm trung bình, 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ẽ đầy đủ bạn đang biết, Sở lọc Excel tất cả một công dụng sẵn có để lọc các ô trống. Bằng cách chọn xuất xắc bỏ chọn (Trống) các vỏ hộp khắc ghi trong bảng lựa chọn Sở lọc Tự hễ của Excel, chúng ta cũng có thể hiển thị chỉ những mặt hàng tất cả các ô trống xuất xắc chứa dữ liệu vào một tuyệt các cột. Vấn đề ở chỗ này đó là cỗ lọc ô trống tất cả sẵn vào Excel chỉ chạy Khi được dùng cùng với hàm lôgic AND.

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

Lọc các ô trống:

ô_trên_cùng=””

Lọc các ô có đựng dữ liệu:

ô_trên_cùng””

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

Để lọc các hàng tất cả ô trống hoặc sống cột A hay B, hay ở cả hai 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 số đó, 6 là 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

Để gọi thêm cách Sở lọc Nâng cao vào Excel hoạt động Lúc có không ít điều kiện, hãy lọc các sản phẩm vào bảng mẫu mã theo những ĐK 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 doanh thu (cột C) yêu cầu to hơn 900.

Xem thêm: Cách Vào Facebook Bằng Google Chrome Không Vào Được Facebook (100% Thành Công)

Nói cách không giống, chúng ta mong muốn hiển thị những sản phẩm đáp ứng nhu cầu các điều kiện sau:

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

Nlỗi chúng ta đã biết, vào vùng điều kiện 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 cần được nhập lệ và một mặt hàng, và đ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 bởi vì một ĐK vào ví dụ này được biểu đạt bởi bí quyết (gồm chứa dữ liệu) cùng ĐK sót lại tất cả đựng tân oán tử đối chiếu (Tổng doanh thu>900), hãy nhằm tôi nói các bạn một vài ba điều:

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

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

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

Có lẽ bạn sẽ biết, Sở lọc Số tất cả sẵn vào Excel tất cả chức năng hiển thị 10 hạng mục đầu tốt cuối. Nhưng nếu bạn bắt buộc lọc 3 quý hiếm đầu xuất xắc 5 quý giá cuối thì sao? Trong ngôi trường hợp này, Bộ thanh lọc Nâng cao trong Excel sẽ sở hữu được sẵn những bí quyết 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ư, nhằm lọc 3 tổng doanh thu đầu, hãy sản xuất vùng điều kiện với bí quyết 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 bí quyết này:

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

Trong số đó, F5 là ô bên trên thuộc tất cả đựng tài liệu trong cột Tổng lệch giá (ngoại trừ title của cột).

Hình ảnh chụp màn hình hiển thị sau đây cho thấy thêm cách làm thanh lọc 3 tổng lợi nhuận đầu:

Lưu ý. Nếu dải list cất vài ba hàng gồm cùng quý giá thuộc N list đầu/cuối, tất cả hàng như thế đầy đủ sẽ tiến hành hiển thị, nlỗi hình họa chụp màn hình dưới đây:

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

Lọc điểm giống (sự trùng lặp) giữa nhị cột:

=B5=C5

Lọc điểm khác (cực hiếm độc nhất) thân nhị cột:

=B5C5

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

Lưu ý. Công gắng Sở lọc Nâng cao chỉ rất có thể tra cứu điểm tương tự với điểm không giống bên trên và một sản phẩm. Để tra cứu toàn bộ quý giá trực thuộc cột A tuy nhiên ko 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ử chúng ta có một bảng to tất cả hàng trăm ngàn tốt hàng chục ngàn mặt hàng, với các bạn nhận thấy một list ngắn thêm một đoạn chỉ đựng các hạng mục buộc phải sử dụng trên 1 thời điểm. Câu hỏi đề ra là – làm vậy nào bạn cũng có thể search tất cả danh mục vào bảng – những danh mục mở ra hay là không mở ra trong list nlắp đó?

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

Để search toàn bộ danh mục vào bảng mối cung cấp – 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ử danh sách ngắn thêm một đoạn phía trong dải ô D2:D7, và những danh mục vào bảng rất cần phải đối chiếu với list đó thì phía trong cột B bắt đầu tự hàng 10, cách làm tất cả dạng sau đây (hãy chăm chú giải pháp sử dụng tyêu thích chiếu hoàn hảo và tuyệt vời nhất cùng tương đối):

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

Tất nhiên các bạn không trở nên gò bó vào việc chỉ lọc bảng với 1 ĐK.

lấy ví dụ như, để thanh lọc các hàng phía trong list, dẫu vậy 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 sản phẩm để bọn chúng có thể vận động khi sử dụng hàm lôgic AND:

Vùng miền: ="=Miền Bắc" Danh mục như là nhau: =COUNTIF($D$2:$D$7,B10)

Nhỏng bạn cũng có thể thấy vào hình ảnh chụp màn hình hiển thị sau đây, chỉ bao gồm nhị làm hồ sơ trong bảng khớp cùng với hai điều kiện mà lại thôi:

Lưu ý. Trong ví dụ này, chúng ta sử dụng điểm giống như hoàn hảo nhất đến cực hiếm cam kết tự: =”=Miền Bắc” nhằm kiếm tìm gần như ô như nhau cùng với chuỗi ký kết từ bỏ xác minh. 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ới vết ngoặc kép), thì Microsoft Excel sẽ search tất cả danh 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

Để tra cứu tất cả danh mục vào bảng không nằm trong danh sách nhỏ rộng, hãy xem coi tác dụng của phương pháp COUNTIF có bởi 0 không:

COUNTIF(list_to_match, top_data_cell)=0

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

Bộ thanh lọc Ngày Tháng Năm tất cả sẵn trong Excel hỗ trợ các chọn lọc liên quan đến nhiều tình huống. Nhiều, nhưng lại không hẳn tất cả! Ví dụ, nếu như khách hàng gồm list tháng ngày năm cùng được đề nghị yêu cầu lọc những ngày vào cuối tuần và vào tuần, các bạn sẽ làm cho cố kỉnh nào?

Như bạn vẫ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 vào tuần dựa vào ngày tháng năm mang đến sẵn. Và đây là hàm mà họ đang áp dụng trong vùng ĐK Sở lọc Nâng cao Excel.

Xem thêm: Vở Bài Tập Toán Cơ Bản Lớp 5 00 Bài Tập Toán Chọn Lọc Lớp 5 (Cơ Bản Và Nâng Cao)

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à 7 là Thứ bảy, cách làm thanh lọc ngày vào cuối tuần gồm dạng như sau:

OR(WEEKDAY(ngày tháng 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ừ bỏ mặt hàng 5, vì vậy phương pháp 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 TRONG TUẦN TRONG EXCEL

Để lọc các ngày trong 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) và 7 (Thứ 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 bọn họ, bí quyết sau có lợi khôn cùng nhiều:

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

Dường như, chúng ta có thể thêm 1 ĐK nhằm lọc các ô trống: =B5””

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

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

Hiện tại, backlinks.vn 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 đến 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 mang đến quý 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 trong 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í vào tháng này, vậy còn trần trừ gì nữa mà không đăng kí ngay lập tức thôi. Thông tin chi tiết coi tại: