Lọc có điều kiện trong excel

Bài giải đáp này cho thấy thêm phương pháp sử dụng cỗ thanh lọc cải thiện trong Excel với cung cấp vài ví dụ vùng điều kiện nâng cấp để sinh sản bộ lọc sáng tỏ dạng văn bản, để tìm điểm giống như với không giống giữa nhị cột, cách xuât hồ sơ khớp với danh sách nhỏ rộng, và những hơn thế nữa.

You watching: Lọc có điều kiện trong excel

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

Công thức bạn thực hiện mang lại vùng điều kiện phải biết Đánh Giá TRUE hay FALSE.

Vùng ĐK phải bao gồm tối thiểu 2 ô: ô bí quyết với ô title.

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

Để cách làm được review trong mỗi ô của dải ô danh sách, hãy tsi mê chiếu ô bên trên thuộc cùng với tài liệu bằng phương pháp thực hiện tđam mê chiếu ô kha khá chẳng hạn như A1.


*

Để phương pháp được Đánh Giá chỉ cho 1 ô hay 1 dải ô xác định, hãy tahm chiếu ô hay dải ô kia bằng cách áp dụng tmê mẩn chiếu ô tuyệt vời ví dụ như $A$1.

Lúc tham mê chiếu dải ô list vào cách làm, hãy luôn luôn thực hiện tsi mê chiếu ô tuyệt đố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 cùng một hàng cùng links bọn chúng bởi toán tử AND, hay nhập mỗi điều kiện vào một hàng không giống nhau với links bọn chúng bởi toá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 sẽ chỉ các bạn giải pháp chế tạo cỗ lọc trong Excel nhằm xử trí các nhiệm vụ phức hợp hơn – phần nhiều nhiệm vụ mà Bộ lọc Tự đụng Excel thông thường ko cách xử lý được.

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

Giống nlỗi Sở thanh lọc Tự động Excel, mức sử dụng Sở lọc Nâng cao cũng không khác nhau dạng chữ, tức là nó ko rành mạch ký trường đoản cú viết hoa với viết hay khi thanh lọc quý hiếm dạng ký từ bỏ. Tuy nhiên, bạn có thể dễ ợt search tìm trong khi vẫn phân minh dạng chữ bằng phương pháp sử dụng hàm EXACT vào ĐK thanh lọc cải thiện.

Ví dụ, để thanh lọc mặt hàng chứa Chuối, ko phân minh là CHUỐI tuyệt chuối, hãy nhập cách làm sau vào vùng điều kiện:

=EXACT(B5, “Chuối”)

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

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

Lưu ý. Hình ảnh bên trên cũng giống như các hình họa chụp screen khác trong bài bác lý giải này hiển thị công thức vào ô vùng ĐK nhằm góp số đông máy trlàm việc bắt buộc cụ thể rộng. Trong trang tính của người tiêu dùng, ô cách làm nên làm trả về TRUE hoặc FALSE, tùy thuộc vào ô đầu tiên chứa tài liệu bao gồm khớp 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 quý giá số, thường thì bạn có thể mong hiển thị chỉ phần đông ô to hơn tuyệt nhỏ hơn quý hiếm vừa phải vào cột. Ví dụ:

Để thanh lọc mặt hàng có tổng to hơn cực hiếm mức độ vừa phải, hãy áp dụng công thức tiếp sau đây trong vùng điều kiện:

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

Để lọc hàng bao gồm tổng nhỏ tuổi rộng quý hiếm vừa phải, hãy sử dụng công thức sau đây:

=F5

Excel kế toán 1 thầy 1 trò

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

Có lẽ đều fan vẫn biết, Sở thanh lọc Excel có một kĩ năng sẵn bao gồm để thanh lọc các ô trống. Bằng giải pháp lựa chọn giỏi bỏ chọn (Trống) những hộp đánh dấu vào bảng chọn Bộ thanh lọc Tự cồn của Excel, chúng ta cũng có thể hiển thị chỉ đa số sản phẩm bao gồm những ô trống giỏi đựng dữ liệu trong một hay các cột. Vấn đề ở đây chính là cỗ lọc ô trống gồm sẵn trong Excel chỉ chạy lúc được dùng với hàm lôgic AND.

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

Lọc những ô trống:

ô_trên_cùng=””

Lọc những ô tất cả 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 hàng gồm ô trống hoặc sinh hoạt cột A xuất xắc 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 cách này:

=A6=""=B6=""

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

Để đọc thêm phương pháp Sở lọc Nâng cao trong Excel vận động lúc có khá nhiều ĐK, hãy lọc các sản phẩm vào bảng chủng loại theo những điều kiện sau:

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

Tổng lệch giá (cột C) đề xuất to hơn 900.

See more: Xiaomi Redmi Note 5 Pro Đánh Giá, Xiaomi Redmi Note 5 Pro Review: The New Budget

Nói biện pháp khác, chúng ta ao ước hiển thị các sản phẩm đáp ứng các ĐK sau:

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

Như chúng ta vẫn biết, vào vùng ĐK của Sở lọc Nâng cao Excel, ĐK kết hợp với hàm lôgic AND đề nghị được nhập vào và một sản phẩm, và điều kiện kết phù hợp với hàm lôgic OR – nghỉ ngơi những sản phẩm khác nhau:

Bởi bởi một ĐK trong ví dụ này được diễn tả bởi phương pháp (tất cả đựng dữ liệu) và điều kiện còn sót lại bao gồm cất tân oán tử đối chiếu (Tổng doanh thu>900), hãy để tôi đề cập chúng ta một vài điều:

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

Điều kiện dựa vào phương pháp nên có hay là ô title trống Hay là tiêu đề rất khác cùng với ngẫu nhiên title làm sao của bảng, giống như ĐK Có đựng dữ liệu trong hình ảnh chụp màn hình trên.

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

Có lẽ bạn đang biết, Sở thanh lọc Số tất cả sẵn vào Excel tất cả kĩ năng hiển thị 10 hạng mục đầu tốt cuối. Nhưng nếu như khách hàng phải thanh lọc 3 cực hiếm đầu giỏi 5 giá trị cuối thì sao? Trong ngôi trường đúng theo này, Bộ thanh lọc Nâng cao trong Excel sẽ sở hữu được sẵn những công thức 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 ví dụ, nhằm lọc 3 tổng lệch giá đầu, hãy chế tạo ra vùng điều kiện cùng rất phương pháp này:

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

Để xuất 3 tổng doanh thu cuối, hãy sử dụng phương pháp này:

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

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

Hình ảnh chụp màn hình tiếp sau đây cho thấy phương pháp lọc 3 tổng lợi nhuận đầu:

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

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

=B5=C5

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

=B5C5

Trong đó, B5 và C5 là những ô trên thuộc có đựng tài liệu nằm trong nhị cột nhưng bạn muốn đối chiếu.

Lưu ý. Công cụ Sở lọc Nâng cao chỉ hoàn toàn có thể tìm điểm tương đương cùng điểm khác trên và một mặt hàng. Để tìm toàn bộ quý giá trực thuộc cột A tuy thế ko lộ diện 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 tất cả một bảng Khủng có hàng trăm xuất xắc hàng chục ngàn sản phẩm, cùng bạn nhận thấy một list ngắn lại chỉ đựng những danh mục cần sử dụng trên một thời điểm. Câu hỏi đặt ra là – có tác dụng vậy làm sao bạn cũng có thể tìm toàn bộ danh mục vào bảng – các hạng mục lộ diện hay là không xuất hiện thêm trong list nđính thêm đó?

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

Để tra cứu tất cả hạng mục trong bảng mối cung cấp – các danh mục này cũng nằm 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ử list ngắn lại phía trong dải ô D2:D7, với những hạng mục trong bảng cần được đối chiếu cùng với list kia thì phía trong cột B ban đầu tự hàng 10, phương pháp tất cả dạng sau đây (hãy chăm chú cách dùng tham chiếu tuyệt vời cùng 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 Việc chỉ thanh lọc bảng với một điều kiện.

lấy một ví dụ, để thanh lọc các hàng phía bên trong list, tuy nhiên chỉ những vùng miền Bắc thôi, hãy nhập nhì ĐK này vào cùng một mặt hàng để chúng hoàn toàn có thể hoạt độ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 trong hình họa chụp screen sau đây, chỉ bao gồm hai hồ sơ trong bảng khớp cùng với nhị điều kiện nhưng mà thôi:

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

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 thuộc danh sách bé dại hơn, hãy coi coi hiệu quả của cách làm COUNTIF tất cả bởi 0 không:

COUNTIF(list_to_match, top_data_cell)=0

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

Lưu ý.

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

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

Nlỗi chúng ta đang biết, Microsoft Excel hỗ trợ hàm WEEKDAY đặc biệt quan trọng – hàm này trả về ngày trong tuần dựa vào tháng ngày năm đến sẵn. Và đấy là hàm nhưng mà bọn họ đang sử dụng trong vùng điều kiện Sở lọc Nâng cao Excel.

See more: Mua Xe Máy Có Cần Sổ Hộ Khẩu Không ? Thủ Tục Đăng Ký Xe Ở Nơi Tạm Trú Năm 2020

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

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

AND(WEEKDAY(ngày tháng 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ọ, công thức sau có ích cực kỳ nhiều:

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

Trong khi, chúng ta cũng có thể thêm một điều kiện để lọc các ô trống: =B5””

Để thanh lọc tháng ngày năm trong trang tính của doanh nghiệp theo cách không giống, hãy kiếm tìm hàm Date tương quan cùng cứ tự nhiên và thoải mái dùng nó trong vùng ĐK Sở lọc Nâng cao Excel.

À thì, đấy là bí quyết chúng ta sử dụng Bộ thanh lọc Nâng cao Excel cho các ĐK phức hợp. Tất nhiên, các chọn lựa của người sử dụng không xẩy ra giới hạn bởi vì phần nhiều ví dụ được đàm luận trong bài bác lí giải này, mục tiêu của Cửa Hàng chúng tôi là hỗ trợ cho chính mình vài ba ý tưởng phát minh có thể truyền xúc cảm nhằm chúng ta đi đúng phía. Hãy hãy nhớ là con đường phát triển thành Chuyên Viên luôn luôn đi đôi thực hành. Cảm ơn các bạn vẫn hiểu nội dung bài viết và hứa hẹn gặp gỡ lại chúng ta vào tuần tới!

Lúc Này, cheohanoi.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 lại nâng cao với các chuyên gia hàng đầu tại Việt Nam. Chúng tôi sẽ với đến mang đế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 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ằ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: