Bài khuyên bảo này cho thấy thêm cách thực hiện bộ lọc nâng cao trong Excel và cung ứng vài lấy ví dụ như vùng điều kiện nâng cấp để tạo bộ lọc minh bạch dạng chữ, để tìm điểm giống và khác thân hai cột, bí quyết xuât làm hồ sơ khớp với danh sách nhỏ tuổi hơn, và nhiều không dừng lại ở đó nữa.
Bạn đang đọc: Lọc nâng cao trong excel 2010
Trong nội dung bài viết trước, bọn họ đã bàn thảo các khía cạnh khác biệt của bộ lọc cải thiện trong Excel và cách áp dụng nó nhằm lọc hàng với hàm lôgic và và OR. Bây giờ bạn đã có kiến thức cơ bản, hãy coi xét những ví dụ vùng điều kiện phức hợp hơn nhé – các ví dụ này hoàn toàn có thể hữu ích cho các bước của các bạn đấ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À vào TUẦN
CÀI ĐẶT VÙNG ĐIỀU KIỆN DỰA TRÊN CÔNG THỨC
Vì nhiều phần các lấy một ví dụ về vùng đk được bàn luận trong bài hướng dẫn này sẽ sở hữu được các bí quyết khác nhau, hãy bước đầu bằng cách xác minh quy tắc thiết yếu để setup chúng một cách phù hợp. Tin tôi đi, mẩu con kiến thức nhỏ dại nhoi này để giúp bạn máu kiệm không ít thời gian và bạn không hề phải chống mặt khi phải khẳng định và sửa lỗi đến vùng điều kiện tinh vi nữa – vùng điều kiện có không ít điều kiện dựa trên công thức.
Công thức bạn thực hiện cho vùng đk phải biết review TRUE xuất xắc FALSE.
Vùng đk phải có ít nhất 2 ô: ô công thức và ô tiêu đề.
Ô tiêu đề của điều kiện dựa trên phương pháp hoặc là để trống hoặc là khác với bất kỳ tiêu đề làm sao của bảng (dải ô danh sách).
Để công thức được reviews trong mỗi ô của dải ô danh sách, hãy tham chiếu ô trên cùng cùng với dữ liệu bằng phương pháp sử dụng tham chiếu ô tương đối chẳng hạn như A1.
Để công thức được reviews chỉ cho một ô hay 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 đối chẳng hạn như $A$1.
Khi tham chiếu dải ô danh sách trong công thức, hãy luôn sử dụng tham chiếu ô hay đối.
Khi áp dụng nhiều điều kiện, hãy nhập toàn bộ điều khiếu nại trên cùng một hàng và link chúng bởi toán tử AND, tốt nhập từng điều kiện vào một hàng không giống nhau và links 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ụ sau đây sẽ chỉ chúng ta cách tạo cỗ lọc trong Excel nhằm xử lý những nhiệm vụ phức hợp hơn – những trọng trách mà bộ lọc tự động Excel thông thường không giải pháp xử lý được.
BỘ LỌC PHÂN BIỆT DẠNG CHỮ đến GIÁ TRỊ DẠNG KÝ TỰ
Giống như cỗ lọc auto Excel, pháp luật Bộ lọc cải thiện cũng không sáng tỏ dạng chữ, tức là nó không riêng biệt ký từ bỏ viết hoa với viết thường khi lọc quý hiếm dạng ký tự. Mặc dù nhiên, bạn có thể dễ dàng tìm kiếm kiếm trong lúc vẫn phân biệt dạng chữ bằng phương pháp sử dụng hàm EXACT trong điều kiện lọc nâng cao.
Ví dụ, nhằm lọc hàng cất Chuối, không phân biệt là CHUỐI tốt 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 tên danh mục, cùng hàng 5 số 1 tiên cất dư liệu.
Sau đó, hãy thực hiện Bộ lọc nâng cao Excel bằng cách nhấp nút Advanced bên 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ư ảnh chụp màn hình hiển thị dưới đây:
Lưu ý. Hình hình ảnh trên cũng giống như các hình ảnh chụp màn hình khác trong bài hướng dẫn này hiển thị cách làm trong ô vùng điều kiện nhằm giúp phần đông thứ trở nên rõ ràng hơn. Trong trang tính của bạn, ô công thức nên làm trả về TRUE hoặc FALSE, tùy vào ô thứ nhất 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 giỏi NHỎ HƠN GIÁ TRỊ TRUNG BÌNH vào MỘT CỘT
Khi lọc cực hiếm số, thông thường chúng ta cũng có thể muốn hiển thị chỉ số đông ô lớn hơn hay bé dại hơn quý hiếm trung bình trong cột. Ví dụ:
Để lọc hàng tất cả tổng to hơn giá trị trung bình, hãy sử dụng công thức dưới đây trong vùng điều kiện:
=F5>AVERAGE($F$5:$F$50)
Để thanh lọc hàng có tổng nhỏ dạ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 tài chính 1 thầy 1 trò
LỌC HÀNG TRỐNG tốt CÓ CHỨA DỮ LIỆU
Có lẽ mọi người đã biết, bộ lọc Excel bao gồm một kĩ năng sẵn gồm để lọc các ô trống. Bằng cách chọn hay quăng quật chọn (Trống) các hộp lưu lại trong bảng chọn bộ lọc tự động của Excel, chúng ta có thể hiển thị chỉ đầy đủ hàng có những ô trống xuất xắc chứa tài liệu trong một hay những cột. Vấn đề tại đây đó là bộ lọc ô trống tất cả sẵn vào Excel chỉ chạy khi được dùng với hàm lôgic AND.
Nếu bạn muốn lọc các ô trống hay ô bao gồm chứa tài liệu với hàm lôgic OR, hay được dùng điều kiện cho các ô trống hay những ô có chứa tài liệu cùng với đôi nét kiện khác nữa, thì hãy thiết đặt vùng điều kiện bộ lọc cải thiện cùng với trong những công thức sau:
Lọc những ô trống:
ô_trên_cùng=””
Lọc các ô gồm 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 tốt B, hay ở 2 cột, hãy lập thông số kỹ thuật vùng đk Bộ lọc nâng cao theo biện pháp 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
Để phát âm thêm cách Bộ lọc nâng cao trong Excel chuyển động khi có rất nhiều điều kiện, hãy lọc các hàng vào bảng chủng loại theo các điều khiếu nại sau:
Hoặc là Vùng miền (cột A) hoặc là hạng mục (cột B) gồm chứa dữ liệu, và
Tổng doanh thu (cột C) phải lớn hơn 900.
Xem thêm: Hướng Dẫn Sử Dụng Java Jdbc Kết Nối Java Với Mysql Bằng Java Jdbc Driver
Nói giải pháp khác, bọn họ muốn hiển thị những hàng đáp ứng nhu cầu các đk sau:
(Tổng doanh thu>900 & Vùng miền=có chứa dữ liệu) OR (Tổng doanh thu>900 & Danh mục=có cất dữ liệu)
Như các bạn đã biết, vào vùng đk của cỗ lọc nâng cao Excel, điều kiện kết hợp với hàm lôgic & nên được nhập vào cùng một hàng, và đk kết hợp với hàm lôgic OR – ở các hàng không giống nhau:
Bởi bởi một đk trong ví dụ này được miêu tả bằng bí quyết (có đựng dữ liệu) và điều kiện còn lại sở hữu chứa toán tử so sánh (Tổng doanh thu>900), hãy để tôi nhắc chúng ta một vài ba điều:
Điều khiếu nại được lập cùng với toán tử đối chiếu nên gồm tiêu đề giống 100% tiêu đề của bảng, hệt 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 vào công thức nên có hoặc là ô title trống hoặc là tiêu đề không giống với ngẫu nhiên tiêu đề nào của bảng, giống hệt như điều kiện bao gồm chứa tài 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ẽ chúng ta đã biết, bộ lọc Số tất cả sẵn vào Excel có nhân tài hiển thị 10 hạng mục đầu xuất xắc cuối. Nhưng nếu như khách hàng cần thanh lọc 3 cực hiếm đầu hay 5 quý hiếm cuối thì sao? vào trường đúng theo này, cỗ lọc nâng cấp trong Excel sẽ có sẵn các công thức dưới đây:
Lọc N danh 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)
Ví dụ, để lọc 3 tổng lợi nhuận đầu, hãy chế tác vùng đk cùng với cách làm này:
=F5>=LARGE($F$5:$F$50,3)
Để xuất 3 tổng lợi nhuận cuối, hãy thực hiện công thức này:
=F5>=SMALL($F$5:$F$50,3)
Trong đó, F5 là ô bên trên cùng tất cả chứa tài liệu trong cột Tổng lợi nhuận (không tính tiêu đề của cột).
Ảnh chụp màn hình dưới đây cho thấy thêm công thức thanh lọc 3 tổng lệch giá đầu:
Lưu ý. nếu dải danh sách chứa vài hàng có cùng cực hiếm thuộc N list đầu/cuối, toàn bộ hàng như thế đều sẽ tiến hành hiển thị, như ảnh chụp màn hình hiển thị 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 trong những nội dung bài viết trước của bọn họ đã phân tích và lý giải những cách khác biệt nhằm so sánh hai cột trong Excel rồi kiếm tìm điểm giống với điểm khác giữa chúng. Sát bên các bí quyết Excel, quy quy định định dạng có đk và pháp luật Duplicate Remover đang được kể tới trong bài bác hướng dẫn trên, bạn có thể sử dụng cỗ lọc nâng cao Excel để xuất những hàng có các giá trị tương tự hay khác biệt trong nhị hay những cột. Để có tác dụng điều này, hãy nhập một trong những công thức đơn giản dễ dàng sau đây vào vùng điều kiện:
Lọc điểm kiểu như (sự trùng lặp) giữa hai cột:
=B5=C5
Lọc điểm khác (giá trị độc nhất) giữa hai cột:
=B5C5
Trong đó, B5 với C5 là những ô trên cùng tất cả chứa dữ liệu thuộc nhị cột mà bạn muốn so sánh.
Lưu ý. hình thức Bộ lọc nâng cấp chỉ hoàn toàn có thể tìm điểm giống cùng điểm khác trên cùng một hàng. Để tìm toàn bộ giá trị thuộc cột A cơ mà 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ử chúng ta có một bảng lớn có hàng trăm hay hàng chục ngàn hàng, và chúng ta nhận được một danh sách ngắn lại chỉ chứa những danh mục đề nghị dùng tại một thời điểm. Thắc mắc đặt ra là – làm cố nào chúng ta cũng có thể tìm tất cả danh mục trong bảng – những danh mục xuất hiện hay không xuất hiện tại 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 nguồn – các danh mục này cũng phía bên trong danh sách, hãy thực hiện 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à các danh mục trong bảng rất cần phải so sánh với danh sách đó thì bên trong cột B bắt đầu từ hàng 10, công thức gồm dạng tiếp sau đây (hãy để ý cách dùng tham chiếu tuyệt đối hoàn hảo và tương đối):
=COUNTIF($D$2:$D$7,B10)
Tất nhiên bạn không bị bó buộc trong bài toá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, cơ mà chỉ các vùng miền bắc bộ thôi, hãy nhập hai đk này vào và một hàng nhằm chúng tất cả thể chuyển động khi 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ũng có thể thấy trong ảnh chụp màn hình dưới đây, chỉ tất cả hai làm hồ sơ trong bảng khớp cùng với hai điều kiện mà thôi:
Lưu ý. Trong lấy ví dụ này, họ sử dụng điểm giống tuyệt đối hoàn hảo cho giá chỉ trị cam kết tự: =”=Miền Bắc” nhằm tìm đa số ô như nhau với chuỗi ký tự xác định. Nếu như khách hàng chỉ nhập điều kiện Vùng miền như thế này miền bắc (không bao gồm dấu bằng và vết ngoặc kép), thì Microsoft Excel đã tìm toàn bộ danh mục bắt đầu với chuỗi ký tự xác đị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
Để tìm toàn bộ danh mục vào bảng ko thuộc danh sách nhỏ hơn, hãy xem xem hiệu quả của phương pháp COUNTIF có bằng 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 – hạng mục không xuất hiện 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 ko trùng khớp: =COUNTIF($D$2:$D$7,B10)=0Lưu ý.
Nếu list cần so sánh nằm sinh hoạt trang tính khác, hãy có lẽ rằng bạn 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 muốn xuất công dụng sang một trang tính khác, hãy sử dụng Bộ lọc cải thiện ở trang tính mà bạn muốn xuất kết quả.LỌC CÁC NGÀY CUỐI TUẦN VÀ vào TUẦN
Nãy giờ, ví dụ như về vùng điều kiện Bộ lọc nâng cao phần phệ xử lý quý hiếm số và giá trị cam kết tự. Bây giờ, là lúc cho chính mình thấy cách xử lý tháng ngày năm.
Bộ thanh lọc Ngày tháng Năm tất cả sẵn vào Excel cung ứng nhiều lựa chọn tương quan đến các tình huống. Nhiều, nhưng chưa hẳn tất cả! Ví dụ, nếu khách hàng có danh sách ngày tháng năm với được yêu cầu buộc phải 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ụ nào?
Như các bạn đã biết, Microsoft Excel cung cấp hàm WEEKDAY đặ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 lại sẵn. Và đấy là hàm mà họ sẽ thực hiện 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 EXCELHãy ghi nhớ rằng, trong thuật ngữ WEEKDAY, 1 là Chủ nhật với 7 là sản phẩm công nghệ bảy, phương pháp lọc ngày 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 ví dụ như này, chúng ta lọc tháng ngày năm vào cột B bắt đầu từ mặt hàng 5, vì vậy công thức thanh lọc ngày vào 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 thay đổi công thức trên đễ nó ko 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 tháng năm)1)
Đối cùng với bảng mẫu của bọn chúng ta, cách làm sau bổ í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 tháng năm vào trang tính của công ty theo bí quyết khác, hãy tra cứu hàm Date liên quan và cứ tự nhiên dùng nó trong vùng đk Bộ lọc nâng cấp Excel.
À thì, đây là cách bạn dùng bộ lọc nâng cấp Excel cho những điều kiện phức tạp. Tất nhiên, những lựa chọn của bạn không bị số lượng giới hạn bởi đều ví dụ được đàm đạo trong bài bác hướng dẫn này, mục đích của shop chúng tôi là cung cấp cho mình vài ý tưởng có thể truyền cảm giác để chúng ta đi đúng hướng. Hãy nhớ rằng tuyến 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 chạm chán lại các bạn vào tuần tới!
Hiện tại, bugthecao.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 đế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ẽ có đến cho 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 đang có nhiều chươ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 trần trừ gì nữa mà ko đăng kí tức thì thôi. Thông tin chi tiết xem tại:
