Lọc nâng cao trong excel 2010

Bài khuyên bảo này cho thấy thêm cách sử dụng bộ lọc nâng cao trong Excel và hỗ trợ vài lấy ví dụ như vùng điều kiện nâng cao để tạo bộ lọc biệt lập dạng chữ, để tìm điểm giống với khác thân hai cột, biện pháp xuât làm hồ sơ khớp cùng với danh sách bé dại hơn, với nhiều không những thế nữa.

Bạn đang xem: Lọc nâng cao trong excel 2010

Trong bài viết trước, họ đã đàm luận các khía cạnh khác nhau của bộ lọc nâng cấp trong Excel và cách áp dụng nó để lọc hàng cùng rất hàm lôgic and và OR. Bây giờ bạn đã sở hữu kiến thức cơ bản, hãy xem xét các ví dụ vùng điều kiện phức tạp hơn nhé – các ví dụ này hoàn toàn có thể hữu ích cho công việc của các bạn đấy.


Xem nhanh


VÍ DỤ VÙNG ĐIỀU KIỆN vào 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 lớn các ví dụ như về vùng đk được thảo luận trong bài hướng dẫn này sẽ sở hữu các phương pháp khác nhau, hãy bắt đầu bằng cách khẳng định quy tắc cần thiết để thiết đặt chúng một biện pháp phù hợp. Tin tôi đi, mẩu loài kiến thức nhỏ nhoi này để giúp bạn máu kiệm tương đối nhiều thời gian với bạn không thể phải đau đầu khi phải khẳng đị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ó rất nhiều điều kiện dựa vào công thức.

Công thức bạn thực hiện cho vùng điều kiện phải biết review TRUE tốt FALSE.

Vùng điều kiện phải có ít nhất 2 ô: ô cách làm và ô tiêu đề.

Ô title của điều kiện dựa trên bí quyết hoặc là nhằm trống hay những khác 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 từng ô của dải ô danh sách, hãy tham chiếu ô bên trên cùng cùng rất dữ liệu bằng cách sử dụng tham chiếu ô tương đối ví dụ như A1.


*

Để công thức được reviews chỉ cho 1 ô hay như là một dải ô xác định, hãy tahm chiếu ô giỏi dải ô đó bằng cách sử dụng tham chiếu ô tuyệt vời chẳng hạn như $A$1.

Khi tham chiếu dải ô list trong công thức, hãy luôn luôn sử dụng tham chiếu ô giỏi đối.

Khi áp dụng nhiều điều kiện, hãy nhập tất cả điều khiếu nại trên và một hàng và liên kết chúng bằng toán tử AND, hay nhập từng điều kiện vào trong 1 hàng không giống nhau và link 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ụ dưới đây sẽ chỉ chúng ta cách tạo cỗ lọc vào Excel để xử lý các nhiệm vụ tinh vi hơn – những trọng trách mà bộ lọc tự động Excel thường thì không xử trí được.

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

Giống như bộ lọc tự động Excel, vẻ ngoài Bộ lọc nâng cấp cũng không rõ ràng dạng chữ, tức là nó không sáng tỏ ký trường đoản cú viết hoa và viết hay khi lọc quý hiếm dạng ký tự. Tuy nhiên, bạn cũng có thể dễ dàng tra cứu kiếm trong những lúc vẫn tách biệt dạng chữ bằng cách sử dụng hàm EXACT trong điều kiện lọc nâng cao.

Ví dụ, nhằm lọc hàng chứa Chuối, không rành mạch là CHUỐI tốt chuối, hãy nhập bí quyết sau vào vùng điều kiện:

=EXACT(B5, “Chuối”)

Trong kia B là cột chứa tên danh mục, cùng hàng 5 bậc nhất tiên đựng dư liệu.

Sau đó, hãy sử dụng Bộ lọc cải thiện Excel bằng cách nhấp nút Advanced trên tab Data, rồi định thông số kỹ thuật cho dải ô list và dải ô Điều khiếu nại như hình ảnh chụp screen dưới đây:

Lưu ý. Hình hình ảnh trên cũng tương tự các hình ảnh chụp màn hình khác trong bài bác hướng dẫn này hiển thị bí quyết trong ô vùng điều kiện nhằm mục đích giúp các thứ trở nên cụ thể hơn. Vào trang tính của bạn, ô công thức nên làm trả về TRUE hoặc FALSE, tùy thuộc vào ô trước tiên chứa dữ liệu có khớp với điều kiện 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

Khi lọc quý hiếm số, thông thường chúng ta có thể muốn hiển thị chỉ đều ô to hơn hay nhỏ tuổi hơn cực hiếm trung bình vào cột. Ví dụ:

Để thanh lọc hàng gồm tổng lớn hơn giá trị trung bình, hãy sử dụng công thức sau đây trong vùng điều kiện:

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

Để thanh lọc hàng gồm tổng nhỏ tuổi hơn quý hiếm trung bình, 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 tốt CÓ CHỨA DỮ LIỆU

Có lẽ mọi bạn đã biết, cỗ lọc Excel bao gồm một tính năng sẵn bao gồm để lọc các ô trống. Bằng phương pháp chọn hay bỏ chọn (Trống) các hộp lưu lại trong bảng chọn cỗ lọc auto của Excel, bạn có thể hiển thị chỉ hồ hết hàng có những ô trống xuất xắc chứa dữ liệu trong một hay nhiều cột. Vấn đề tại đây đó là bộ lọc ô trống bao gồm sẵn vào Excel chỉ chạy khi được sử dụng với hàm lôgic AND.

Nếu bạn có nhu cầu lọc những ô trống hay ô có chứa dữ liệu với hàm lôgic OR, hay sử dụng điều kiện cho các ô trống hay các ô có chứa tài liệu cùng với đôi điều kiện khác nữa, thì hãy cài đặt vùng đk bộ lọc cải thiện cùng với một trong những công thức 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 hàng bao gồm ô trống hoặc sống cột A hay B, hay ở cả 2 cột, hãy lập cấu hình vùng đk Bộ lọc nâng cao theo cách này:

=A6=""=B6=""

Trong đó, 6 là sản phẩm 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

Để hiểu thêm bí quyết Bộ lọc nâng cấp trong Excel hoạt động khi có nhiều điều kiện, hãy lọc những hàng vào bảng mẫu theo những điều khiếu nại sau:

Hoặc là Vùng miền (cột A) hoặc là danh mục (cột B) bao gồm chứa dữ liệu, và

Tổng lệch giá (cột C) phải lớn hơn 900.

Xem thêm: Lời Bài Hát Cung Song Ngư Ling, Cung Song Ngư

Nói cách khác, bọn họ muốn hiển thị những hàng đáp ứng nhu cầu các điều kiện sau:

(Tổng doanh thu>900 & Vùng miền=có đựng dữ liệu) OR (Tổng doanh thu>900 and Danh mục=có đựng dữ liệu)

Như các bạn đã biết, vào vùng đk của bộ lọc nâng cao Excel, đk kết hợp với hàm lôgic và nên được nhập vào và một hàng, và điều kiện kết hợp với hàm lôgic OR – ở những hàng khác nhau:

Bởi do một đk trong ví dụ này được diễn tả bằng phương pháp (có đựng dữ liệu) và điều kiện còn lại sở hữu chứa toán tử đối chiếu (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 tử đối chiếu nên gồm tiêu đề tương tự 100% tiêu đề của bảng, y như điều khiếu nại Tổng lệch giá trong hình ảnh chụp màn hình trên.

Điều kiện dựa trên công thức nên bao gồm hoặc là ô title trống hay những tiêu đề rất khác với bất kỳ tiêu đề nào của bảng, giống như điều kiện tất cả chứa dữ liệu trong hình ảnh chụp màn hình hiển thị trên.

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

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

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

Để xuất 3 tổng lệch giá cuối, hãy áp dụng công thức này:

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

Trong đó, F5 là ô bên trên cùng bao gồm chứa tài liệu trong cột Tổng lệch giá (không tính title của cột).

Ảnh chụp screen dưới đây cho biết thêm công thức lọc 3 tổng lệch giá đầu:

Lưu ý. nếu như dải danh sách chứa vài ba hàng gồm cùng quý giá thuộc N list đầu/cuối, toàn bộ hàng như thế đều 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 nhì CỘT

Một vào những bài viết trước của chúng ta đã lý giải những cách khác nhau nhằm so sánh hai cột trong Excel rồi search điểm giống cùng điểm khác giữa chúng. Lân cận các cách làm Excel, quy mức sử dụng định dạng có điều kiện và dụng cụ Duplicate Remover vẫn được kể đến trong bài hướng dẫn trên, chúng ta cũng có thể sử dụng bộ lọc nâng cấp Excel nhằm xuất những hàng có các giá trị giống như hay khác nhau trong nhị hay nhiều cột. Để có tác dụng điều này, hãy nhập trong những công thức đơn giản sau đây vào vùng điều kiện:

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

=B5=C5

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

=B5C5

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

Lưu ý. dụng cụ Bộ lọc nâng cấp chỉ rất có thể tìm điểm giống cùng điểm không giống trên và một hàng. Để tìm tất cả giá trị trực thuộc cột A mà lại không 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 có một bảng to có hàng trăm ngàn hay hàng vạn hàng, và chúng ta nhận được một danh sách ngắn lại chỉ chứa các danh mục nên dùng tại 1 thời điểm. Thắc mắc đặt ra là – làm núm nào bạn có thể tìm toàn bộ danh mục vào bảng – những danh mục xuất hiện hay không xuất hiện nay trong danh sách ngắn đó?

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

Để tìm tất cả danh 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ông thức COUNTIF sau đây:

COUNTIF(list_to_match, top_data_cell)

Giả sử danh sách ngắn lại hơn nữa nằm trong dải ô D2:D7, và những danh mục vào bảng cần phải so sánh với danh sách đó thì phía trong cột B bước đầu từ mặt hàng 10, công thức gồm dạng sau đây (hãy để ý cách cần sử dụng tham chiếu tuyệt vời và hoàn hảo nhất và tương đối):

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

Tất nhiên bạn không bị bó buộc trong vấn đề chỉ thanh lọc bảng với cùng 1 điều kiện.

Ví dụ, nhằm lọc những hàng phía trong danh sách, tuy nhiên chỉ các vùng miền bắc bộ thôi, hãy nhập hai điều kiện này vào cùng một hàng để chúng bao gồm thể chuyển động khi sử dụng hàm lôgic AND:

Vùng miền: ="=Miền Bắc" hạng mục giống nhau: =COUNTIF($D$2:$D$7,B10)

Như chúng ta có thể thấy trong hình ảnh chụp màn hình dưới đây, chỉ có hai hồ sơ trong bảng khớp với hai điều kiện mà thôi:

Lưu ý. Trong lấy ví dụ này, bọn họ sử dụng điểm giống tuyệt vời cho giá bán trị cam kết tự: =”=Miền Bắc” để tìm đông đảo ô giống hệt với chuỗi cam kết tự xác định. Nếu bạn chỉ nhập đk Vùng miền như vậy này khu vực miền bắc (không bao gồm dấu bởi và vết ngoặc kép), thì Microsoft Excel vẫn tìm toàn bộ danh mục bước đầu với chuỗi ký kết tự xác định, ví dụ miền bắc bộ Trung Bộ.

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

Để tìm tất cả danh mục vào bảng không thuộc danh sách nhỏ tuổi hơn, hãy xem xem công dụng của công thức COUNTIF có bởi 0 không:

COUNTIF(list_to_match, top_data_cell)=0

Ví dụ, nhằm lọc danh mục vùng miền bắc trong bảng – danh mục không mở ra trong danh sách, hãy sử dụng đ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 cần so sánh nằm sinh sống trang tính khác, hãy chắc rằng bạn thêm thương hiệu trang tính vào công thức, ví dụ =COUNTIF(Sheet2!$A$2:$A$7,B10).Nếu bạn muốn xuất hiệu quả sang một trang tính khác, hãy áp dụng Bộ lọc nâng cấp ở trang tính mà bạn có nhu cầu xuất kết quả.

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

Nãy giờ, ví dụ như về vùng đk Bộ lọc cải thiện phần to xử lý quý hiếm số cùng giá trị cam kết tự. Bây giờ, là lúc cho bạn thấy biện pháp xử lý tháng ngày năm.

Bộ lọc Ngày mon Năm tất cả sẵn vào Excel cung cấp nhiều lựa chọn liên quan đến những tình huống. Nhiều, nhưng chưa hẳn tất cả! Ví dụ, nếu như bạn có danh sách ngày mon năm cùng được yêu cầu nên lọc những ngày vào cuối tuần và vào tuần, các bạn sẽ làm cố gắng nào?

Như chúng ta đã biết, Microsoft Excel cung cấp hàm WEEKDAY đặc biệt – hàm này trả về ngày trong tuần dựa trên ngày mon năm mang lại sẵn. Và đây là hàm mà bọn họ sẽ áp dụng trong vùng điều kiện Bộ lọc nâng cao Excel.

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

Hãy lưu giữ rằng, trong thuật ngữ WEEKDAY, 1 là Chủ nhật cùng 7 là lắp thêm bảy, công thức lọc ngày vào cuối tuần có dạng như sau:

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

Trong lấy một ví dụ này, họ lọc tháng ngày năm vào cột B bước đầu từ hàng 5, chính vì như vậy công thức thanh lọc ngày vào ngày 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 những ngày trong tuần, hãy biến đổi công thức trên đễ nó không trả về hiệu quả 1 (Chủ nhật) cùng 7 (Thứ bảy):

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

Đối cùng với bảng mẫu mã của chúng ta, phương pháp sau có ích rất nhiều:

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

Bên cạnh đó, 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 ngày mon năm vào trang tính của người sử dụng theo biện pháp khác, hãy search hàm Date tương quan và cứ thoải mái và tự nhiên dùng nó trong vùng điều kiện Bộ lọc nâng cấp Excel.

À thì, đây là cách bạn dùng cỗ lọc nâng cao Excel cho các điều khiếu nại phức tạp. Tất nhiên, các lựa chọn của công ty không bị giới hạn bởi đầy đủ ví dụ được đàm luận trong bài xích hướng dẫn này, mục tiêu của công ty chúng tôi là cung cấp cho mình vài ý tưởng có thể truyền xúc cảm để chúng ta đi đúng hướng. Hãy ghi nhớ rằng con phố trở thành chuyên viên luôn đi đôi thực hành. Cảm ơn chúng ta đã đọc 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, vidabo.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 đến nâng cao với các chuyên viên hàng đầu tại Việt Nam. Shop chúng tôi sẽ sở hữu đến mang đế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 vào công việc. Ngoài ra, chúng tôi sẽ có nhiều chương trình khuyến mại lớn mang lại các học viên đăng kí trong tháng này, vậy còn è cổ trừ gì nữa mà ko đăng kí ngay lập tức thôi. Thông tin cụ thể xem tại: