Trích lọc có điều kiện trong excel

     

Trong nội dung bài viết này học tập Excel Online đã hướng dẫn chúng ta cách lọc ra một danh sách những giá trị đơn nhất độc nhất trong cột bằng phương pháp sử dụng một cách làm và cách điều khiển công thức đó cho những bộ dữ liệu khác nhau. Ko kể ra, Học Excel Online cũng trở nên chỉ cho chúng ta cách mau lẹ nhất để có được một danh sách riêng biệt bằng cách sử dụng bộ lọc nâng cao của Excel, hàm liệt kê list trong excel và làm gắng nào nhằm trích xuất những hàng duy nhất sử dụng Duplicate Remover.Bạn vẫn xem: bí quyết trích list trong excel

Để lọc ra một danh sách các giá trị tuyệt nhất trong Excel chúng ta có thể sử dụng cách xác định các cực hiếm duy tốt nhất đó, thanh lọc riêng rồi coppy chúng. Mặc dù nhiên, thực hiện cách làm cho này vẫn tốn những thời gian, để tiến hành nhanh hơn, chúng ta cũng có thể sử dụng một công thức đặc biệt quan trọng cùng với cùng 1 vài kỹ năng khác mà Học Excel Online sẽ share với chúng ta ngay sau đây.

Bạn đang xem: Trích lọc có điều kiện trong excel

Xem nhanh

Cách lọc ra các giá trị độc nhất vô nhị trong ExcelCách lọc ra những giá trị khác biệt trong Excel (duy tốt nhất + những tài liệu giống nhau sẽ chỉ xuất hiện một lần)Cách trích xuất các giá trị khác biệt phân biệt chữ hoa chữ hay trong ExcelCách hoạt động vui chơi của công thức trích xuất các giá trị duy nhất/ khác biệt

Cách lọc ra những giá trị nhất trong Excel

Để kiêng nhầm lẫn, đầu tiên, chúng ta cần hiểu phần đa giá trị nào được điện thoại tư vấn là là những giá trị tuyệt nhất trong Excel. Giá trị duy độc nhất là những giá trị chỉ tồn tại tuyệt nhất một lần trong cục bộ danh sách. Ví dụ:

*

Để trích xuất một danh sách các giá trị độc nhất trong Excel, chúng ta cũng có thể sử dụng một trong số công thức sau đây.

Công thức mảng nhằm tìm ra phần đông giá trị duy nhất (hoàn thành công xuất sắc thức kết thúc ta nhấn Ctrl + Shift + Enter):

= IFERROR (INDEX ($ A $ 2: $ A $ 10, MATCH (0, COUNTIF ($ B $ 1: B1, $ A $ 2: $ A $ 10) + (COUNTIF ($ A $ 2: $ A $ 10, $ A $ 2: $ A $ 10) 1), 0)), “”)

Công thức giá trị duy nhất tiếp tục (hoàn thành công xuất sắc thức xong xuôi ta thừa nhận Enter):

= IFERROR (INDEX ($ A $ 2: $ A $ 10, MATCH (0, INDEX (COUNTIF ($ B $ 1: B1, $ A $ 2: $ A $ 10) + (COUNTIF ($ A $ 2: $ A $ 10, $ A $ 2 : $ A $ 10) 1), 0,0), 0)), “”)

Trong đó:

A2: A10 – danh sách nguồn B1 – ô đầu của danh sách duy độc nhất trừ đi 1. Trong lấy ví dụ này, chúng ta bắt đầu danh sách những giá trị độc nhất ở B2, và bởi vì đó họ đưa B1 vào bí quyết (B2-1 = B1). Giả dụ danh sách những giá trị tốt nhất của bạn bước đầu ở ô C3, ta sẽ biến đổi $B$1:B1 thành $C$2:C2.


*

Chú thích. Cũng chính vì công thức tham chiếu ô bên trên ô thứ nhất của danh sách các giá trị duy nhất, thường xuyên là title cột (như trong ví dụ như này là B1), nên các bạn cần bảo đảm rằng tiêu đề của công ty là một tên độc nhất không lộ diện ở bất cứ đâu vào cột.

Ở lấy ví dụ này vẫn trích xuất những tên tốt nhất từ cột A (chính xác rộng là vào phạm vi A2: A20), và ảnh chụp screen sau đã minh họa cho bí quyết sẽ dùng:

Dưới đấy là giải phù hợp một cách chi tiết về việc sử dụng công thức nhằm trích xuất những giá trị độc nhất vô nhị trong bảng tính Excel của bạn:

– Lựa lựa chọn 1 trong những cách làm dựa theo dữ liệu của bạn. – Nhập bí quyết vào ô trước tiên của danh sách các giá trị duy nhất (trong ví dụ đang là ô B2). – nếu bạn đang sử dụng công thức mảng, nhấn Ctrl + Shift + Enter. Nếu như bạn chọn công thức bình thường, hãy nhận phím Enter như hay lệ. – sao chép công thức xuống càng xa càng tốt bằng cách kéo chốt xử lý. Bởi cả hai phương pháp giá trị tuyệt nhất được gói gọn trong hàm IFERROR, bạn cũng có thể sao chép cách làm đến cuối bảng cơ mà không có tác dụng lộn xộn tài liệu vì ngẫu nhiên lỗi như thế nào dù các giá trị duy nhất đã được trích xuất ra sao.

Cách thanh lọc ra những giá trị khác biệt trong Excel (duy độc nhất vô nhị + những dữ liệu giống nhau vẫn chỉ lộ diện một lần)

Như các bạn đã đoán ra từ title của phần này, các giá trị khác hoàn toàn trong Excel là tất cả các giá trị khác nhau trong danh sách, tức là những quý hiếm đó chỉ xuất hiện một lần tuyệt nhất dù nó bao gồm bao nhiêu nhân bạn dạng đi chăng nữa. Ví dụ:

Để có được một danh sách các giá trị khác biệt trong Excel, ta thực hiện công thức sau.

Công thức mảng để tìm hầu như giá trị biệt lập (nhấn Ctrl + Shift + Enter):

=IFERROR(INDEX($A$2:$A$10, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$10), 0)), “”)

Công thức chuẩn để tìm hầu hết giá trị không giống biệt

=IFERROR(INDEX($A$2:$A$10, MATCH(0, INDEX(COUNTIF($B$1:B1, $A$2:$A$10), 0, 0), 0)), “”)

Trong đó:

A2:A10 là list nguồn B1 là ô nằm bên trên của danh sách các giá trị khác biệt. Trong lấy một ví dụ này, danh sách các giá trị không giống biệt bắt đầu ở ô B2 (đây là ô thứ nhất bạn nhập công thức).

Trích xuất các giá trị biệt lập vào vào một cột và quăng quật qua những ô trống Nếu list nguồn của người tiêu dùng chứa ô trống, thì những công thức bọn chúng ra vừa nêu sinh hoạt trên vẫn trả về một trong những không cho mỗi hàng trống. Để khắc chế điều này, chúng ta cần đổi mới công thức thêm 1 chút.

Học thiết kế VBA vào Excel sống đâu?

Công thức trích xuất những giá trị khác hoàn toàn không bao gồm khoảng trống:

=IFERROR(INDEX($A$2:$A$10, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$10&””) + IF($A$2:$A$10=””,1,0), 0)), “”)

Trích xuất danh sách các giá trị văn bạn dạng riêng biệt làm lơ số và khoảng trống Theo biện pháp tương tự, bạn cũng có thể nhận được một danh sách các giá trị riêng lẻ trừ các ô với ô trống bao gồm chứa số:

=IFERROR(INDEX($A$2:$A$10, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$10&””) + IF(ISTEXT($A$2:$A$10)=FALSE,1,0), 0)), “”)

Trong đó, A2: A10 là danh sách nguồn, và B1 là ô nằm ở trên ô thứ nhất của list riêng biệt.

Ảnh chụp screen sau đây cho biết kết quả của cả hai bí quyết trên:

Cách trích xuất các giá trị khác nhau phân biệt chữ hoa chữ thường trong Excel

Khi làm việc với những tài liệu nhạy cảm như trong trường hòa hợp như mật khẩu, tên người tiêu dùng hoặc tên tệp, bạn sẽ cần phải tất cả danh sách các giá trị khác biệt nhạy cảm cùng với từng chữ. Khi đó, chúng ta có thể sử dụng phương pháp mảng sau đây, với A2:A10 là list nguồn, cùng B1 là ô nằm ở ô thứ nhất của list riêng biệt:

Công thức mảng nhằm nhận những giá trị riêng biệt theo từng trường thích hợp (nhấn Ctrl + Shift + Enter)

=IFERROR(INDEX($A$2:$A$10, MATCH(0, FREQUENCY(IF(EXACT($A$2:$A$10,TRANSPOSE($B$1:B1)), MATCH(ROW($A$2:$A$10), ROW($A$2:$A$10)), “”), MATCH(ROW($A$2:$A$10), ROW($A$2:$A$10))), 0)), “”)

Cách buổi giao lưu của công thức trích xuất các giá trị duy nhất/ khác biệt

Phần này được viết riêng cho tất cả những người muốn nắm rõ về những cách làm này một cách cụ thể nhất.

Việc trích xuất các giá trị duy nhất và biệt lập trong Excel thực sự không hẳn một thao tác đơn giản. Nhưng chúng ta cũng có thể thấy rằng tất cả các công thức đều được dựa vào cùng một bí quyết tiếp cận – áp dụng INDEX / MATCH kết hợp với hàm COUNTIF, hoặc COUNTIF + IF.

Xem thêm: Hiện Thanh Công Cụ Trên Excel Đơn Giản Nhất, Các Cách Ẩn, Hiển Thị Thanh Công Cụ Trong Excel

Để đối chiếu sâu hơn, bọn họ sẽ cùng thực hiện công thức mảng nhằm trích ra một danh sách các giá trị khác hoàn toàn bởi vì tất cả các công thức khác được bàn luận trong chỉ dẫn này là những công thức đã được cách tân hoặc vươn lên là thể từ công thức cơ phiên bản sau:

=IFERROR(INDEX($A$2:$A$10, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$10), 0)), “”) Đối với những người mới bắt đầu, bạn cũng có thể bỏ qua hàm IFERROR, hàm này được sử dụng với một mục tiêu duy tuyệt nhất để loại trừ lỗi # N/A khi số ô mà bạn đã xào luộc công thức thừa quá con số các giá bán trị khác hoàn toàn trong list nguồn.

Và bây giờ, họ cùng mày mò về mọi phần then chốt trong bí quyết tìm giá trị khác biệt:

1. COUNTIF(range, criteria) hàm này đang trả về số ô trong một phạm vi thỏa mãn nhu cầu được đk cụ thể. Như sống trong lấy ví dụ như này, COUNTIF($B$1:B1, $A$2:$A$10) trả về mảng 1 với 0 dựa trên bất kỳ giá trị nào trong list nguồn ($A$2:$A$10) xuất hiện thêm trong danh sách các giá trị khác biệt ($B$1:B1). Nếu cực hiếm được search thấy, công thức sẽ trả về 1, nếu không kiếm thấy vẫn trả về – 0.

Cụ thể, vào ô B2, COUNTIF($B$1:B1, $A$2:$A$10 trở thành:

COUNTIF(“Distinct”, “Ronnie”; “David”; “Sally”; “Jeremy”; “Robert”; “David”; “Robert”; “Tom”; “Sally”)

Và trả về:

0;0;0;0;0;0;0;0;0

Ở đây, không có mục nào trong list nguồn (tiêu chí) lộ diện trong phạm vi cân xứng với điều kiện tìm. Trong trường vừa lòng này, phạm vi ($B$1:B1) gồm 1 mục tốt nhất – “Distinct”. 2. MATCH (lookup_value, lookup_array, ) trả về vị trí tương đối của quý hiếm đang tìm trong mảng. Trong lấy một ví dụ này, lookup_value là 0, cần ta có: MATCH(0,COUNTIF($B$1:B1, $A$2:$A$10), 0)

trở thành:

MATCH(0, 0;0;0;0;0;0;0;0;0,0)

Và trả về 1

Bởi vì công dụng MATCH nhấn về giá trị trước tiên cũng đó là giá trị bằng với giá trị đã đang search kiếm (mà quý giá đang tìm là 0). 3. INDEX(array, row_num, ) trả về một giá chỉ trị bên trong một mảng dựa vào hàng vẫn được hướng dẫn và chỉ định và gần như số cột (tùy ý). Trong lấy ví dụ này, INDEX($A$2:$A$10, 1)

Trở thành:

INDEX(“Ronnie”; “David”; “Sally”; “Jeremy”; “Robert”; “David”; “Robert”; “Tom”; “Sally”, 1)

Và trả về “Ronnie”.

Do đó, khi coppy công thức quý phái ô B3, COUNTIF($B$1:B1, $A$2:$A$10) đổi khác thành COUNTIF($B$1:B2, $A$2:$A$10), cùng trở thành:

COUNTIF(“Distinct”;”Ronnie”, “Ronnie”; “David”; “Sally”; “Jeremy”; “Robert”; “David”; “Robert”; “Tom”; “Sally”), 0)), “”)

trả về:

1;0;0;0;0;0;0;0;0

Vì bao gồm một “Ronnie” được tra cứu thấy trong phạm vi $B$1:B2.

Từ đó, MATCH(0,1;0;0;0;0;0;0;0;0,0) trả về 2, chính vì 2 là vị trí tương đối của số 0 trước tiên trong mảng. Cuối cùng, INDEX($A$2:$A$10, 2) trả về cực hiếm từ hàng vật dụng 2, đó là “David”.

Mẹo: để làm rõ hơn vì súc tích của công thức, chúng ta có thể chọn các phần không giống nhau của bí quyết trong thanh bí quyết và nhấn f9 nhằm xem reviews về phần đó: Như vẫn đề cập, những công thức không giống được bàn bạc trong bài xích hướng dẫn này dựa trên xúc tích và ngắn gọn tương tự, nhưng gồm thêm chút cụ đổi:

Công thức quý hiếm duy tuyệt nhất – chứa thêm một hàm COUNTIF thải trừ những mục mở ra nhiều lần trong list nguồn khỏi danh sách những giá trị duy nhất: khỏi list duy nhất toàn bộ các mục xuất hiện thêm trong danh sách nguồn nhiều lần: COUNTIF($A$2:$A$10, $A$2:$A$10)1.

Các giá chỉ trị biệt lập bỏ qua khoảng tầm trắng – tại chỗ này bạn thêm một hàm IF để ngăn không cho các ô trống đạt thêm vào list riêng biệt: IF($A$2:$A$13=””,1,0).

Các quý hiếm văn bản khác biệt vứt qua các con số – bạn sử dụng tính năng ISTEXT để kiểm tra xem giá chỉ trị có phải là văn bản hay không, và tính năng IF để loại trừ tất cả các loại quý giá khác, bao hàm các ô trống: IF(ISTEXT($A$2:$A$13)=FALSE,1,0).

Xem thêm: Giáo Án Mầm Non Đề Tài Vẽ Ngôi Nhà Của Bé (Đề Tài), Giáo Án Tạo Hình : Vẽ Ngôi Nhà Của Bé

Trích xuất những giá trị khác hoàn toàn từ một cột thực hiện Bộ lọc cải thiện – Advanced Filter của Excel nếu như khách hàng không ao ước lãng phí thời gian để đọc cặn kẽ những công thức cực hiếm khác biệt, bạn cũng có thể nhanh chóng có được danh sách các giá trị khác biệt bằng cách sử dụng bộ lọc nâng cao bằng phương pháp thực hiện công việc chi tiết dưới đây.

1. Lựa chọn cột dữ liệu bạn có nhu cầu trích xuất những giá trị không giống biệt. 2. Chuyển sang tab Data > Sort & Filter cùng nhấp vào nút Advanced: 3. Trong vỏ hộp thoại Advanced Filter, chúng ta chọn những tuỳ chọn sau: – chất vấn nút Copy khổng lồ another location. – trong hộp list range, xác minh rằng phạm vi nguồn được hiển thị chủ yếu xác. – Trong hộp Copy to, nhập phạm vi trên thuộc của dải ô bạn có nhu cầu sao chép tài liệu sang. Chú ý là chúng ta chỉ rất có thể sao chép dữ liệu đã được thanh lọc vào trang tính sẽ hoạt động. – Chọn chất lượng records only.