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

Bạn đang xem: Hàm liệt kê danh sách trong excel

Để lọc ra một danh sách các giá trị độc nhất trong Excel bạn có thể sử dụng cách xác minh các cực hiếm duy nhất đó, thanh lọc riêng rồi sao chép chúng. Tuy nhiên, áp dụng cách làm cho này sẽ tốn nhiều thời gian, để tiến hành nhanh hơn, bạn có thể sử dụng một công thức quan trọng cùng với 1 vài kĩ thuật khác nhưng Học Excel Online sẽ share với chúng ta ngay sau đây.


Xem nhanh


Cách lọc ra các giá trị tốt nhất trong ExcelCách lọc ra những giá trị khác hoàn toàn trong Excel (duy tuyệt nhất + những tài liệu giống nhau đang chỉ xuất hiện một lần)Cách trích xuất các giá trị không giống nhau phân biệt chữ hoa chữ hay trong ExcelCách buổi giao lưu của công thức trích xuất các giá trị duy nhất/ không giống biệt

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

Để tránh nhầm lẫn, đầu tiên, bọn họ cần hiểu mọi giá trị nào được call là là các giá trị tốt nhất trong Excel. Quý giá duy nhất là những giá trị chỉ tồn tại tuyệt nhất một lần trong tổng thể danh sách. Ví dụ:

*

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

Công thức mảng để tìm ra rất nhiều giá trị độc nhất (hoàn thành công xuất sắc thức hoàn thành 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 quý giá duy nhất liên tiếp (hoàn thành công thức xong 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 vô nhị trừ đi 1. Trong ví dụ như này, bọn chúng ta bắt đầu danh sách những giá trị độc nhất ở B2, và vày đó họ đưa B1 vào bí quyết (B2-1 = B1). Trường hợp danh sách các giá trị tốt nhất của bạn ban đầu ở ô C3, ta sẽ chuyển đổi $B$1:B1 thành $C$2:C2.


*

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

Ở lấy một ví dụ này sẽ trích xuất những tên nhất từ cột A (chính xác rộng là trong phạm vi A2: A20), và hình ảnh chụp màn hình hiển thị sau sẽ minh họa cho phương pháp sẽ dùng:

Dưới đây là giải ưa thích một cách cụ thể về việc áp dụng công thức nhằm trích xuất những giá trị duy nhất trong bảng tính Excel của bạn:

– Lựa lựa chọn 1 trong những bí quyết dựa theo dữ liệu của bạn. – Nhập bí quyết vào ô thứ nhất của danh sách những giá trị tuyệt nhất (trong ví dụ đang là ô B2). – nếu bạn đang áp dụng công thức mảng, thừa nhận Ctrl + Shift + Enter. Nếu khách hàng chọn phương pháp bình thường, hãy dấn phím Enter như thường 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ý. Vì 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 nhưng không có tác dụng lộn xộn tài liệu vì bất kỳ lỗi nào dù những giá trị duy nhất đã có trích xuất ra sao.

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

Như chúng ta đã đoán ra từ tiêu đề của phần này, những giá trị biệt lập trong Excel là tất cả các giá trị khác biệt trong danh sách, tức là những cực hiếm đó chỉ xuất hiện thêm một lần độc nhất dù nó bao gồm bao nhiêu nhân bạn dạng đi chăng nữa. Ví dụ:

Để đạt được một danh sách các giá trị biệt lập trong Excel, ta áp dụng công thức sau.

Công thức mảng nhằm tìm phần đông giá trị khác biệt (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 rất nhiều giá trị khác 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 những giá trị không giống biệt. Trong lấy một ví dụ này, danh sách các giá trị khác biệt bắt đầu ở ô B2 (đây là ô trước tiên bạn nhập công thức).

Trích xuất những giá trị biệt lập vào vào một cột và bỏ qua những ô trống Nếu danh sách nguồn của chúng ta chứa ô trống, thì những công thức chúng ra vừa nêu nghỉ ngơi trên đã trả về một trong những không cho mỗi hàng trống. Để khắc phục và hạn chế điều này, họ cần cách tân công thức thêm một chút.

Học lập trình sẵn VBA vào Excel làm việc đâu?

Công thức trích xuất các giá trị khác biệt không bao hà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 riêng biệt làm lơ số và không gian Theo bí quyết tương tự, bạn có thể nhận được một danh sách những giá trị lẻ tẻ trừ những ô và ô trống tất cả 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à list nguồn, và B1 là ô nằm ngay trên ô đầu tiên 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ông giống nhau phân biệt chữ hoa chữ hay trong Excel

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

Công thức mảng nhằm nhận các giá trị riêng lẻ theo từng trường phù 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 hoạt động 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 làm rõ về những cách làm này một cách rõ ràng nhất.

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

Để so với sâu hơ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 những giá trị biệt lập bởi vì tất cả các công thức khác được đàm luận trong gợi ý này là các công thức sẽ được cải tiến hoặc biến đổi thể từ cách làm 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 người mới bắt đầu, chúng ta cũng có thể bỏ qua hàm IFERROR, hàm này được sử dụng với một mục đích duy duy 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á trị khác hoàn toàn trong danh sách nguồn.

Và bây giờ, họ cùng khám phá về phần lớn phần chính yếu trong công thức tìm quý giá khác biệt:

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

Cụ thể, trong ô 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 tồn tại mục như thế nào trong list nguồn (tiêu chí) mở ra 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 một mục độc nhất vô nhị – “Distinct”. 2. MATCH (lookup_value, lookup_array, ) trả về vị trí kha khá của quý giá đang tra cứu trong mảng. Trong lấy ví dụ này, lookup_value là 0, yêu cầu 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ị đầu tiên cũng đó là giá trị bằng với mức giá trị đã đang search kiếm (mà quý giá đang kiếm tìm là 0). 3. INDEX(array, row_num, ) trả về một giá bán trị nằm trong một mảng dựa vào hàng đang được chỉ định và phần đông 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”.

Khi bí quyết được sa chép xuống cột, danh sách các giá trị khác biệt ($B$1:B1) sẽ mở rộng hơn bởi tham chiếu thêm ô lắp thêm hai (B1) là 1 trong những tham chiếu kha khá – thay đổi theo vị trí tương đối của ô khi công thức dịch chuyển sang ô khác.

Xem thêm: Top 3 Khách Sạn Cần Thơ 5 Sao, Khách Sạn 5 Sao Thành Phố Cần Thơ

Do đó, khi coppy công thức lịch sự ô 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 search 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, bởi 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ề quý hiếm từ hàng đồ vật 2, đó là “David”.

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

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

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

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

Trích xuất những giá trị khác hoàn toàn từ một cột sử dụng Bộ lọc nâng cấp – Advanced Filter của Excel nếu như bạn không mong muốn lãng phí thời hạn để phát âm cặn kẽ những công thức quý 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 phương pháp sử dụng cỗ lọc nâng cao bằng phương pháp thực hiện công việc chi tiết bên dưới đây.

1. Lựa chọn cột dữ liệu bạn có nhu cầu trích xuất các giá trị không giống biệt. 2. Chuyển sang tab Data > Sort và 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: – soát sổ nút Copy lớn another location. – vào hộp list range, xác minh rằng phạm vi nguồn được hiển thị chính 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 dữ liệu sang. Chú ý là bạn chỉ có thể sao chép dữ liệu đã được thanh lọc vào trang tính sẽ hoạt động. – Chọn quality records only.

*
4. Cuối cùng, bạn nhấn OK và bình chọn lại kết quả: bạn cần chú ý rằng, mặc dù tùy chọn của Advanced Filter được lấy tên là “Unique records only” (Chỉ có những phiên bản ghi duy nhất) mặc dù nó vẫn trích xuất hầu hết giá trị không giống biệt, nghĩa là bao gồm các giá trị chỉ mở ra một lần duy nhất với một lần lộ diện chung cho hồ hết giá trị trùng lặp.

Trích xuất hồ hết hàng độc nhất và khác hoàn toàn sử dụng Duplicate Remover

Trong phần sau cùng của bài xích hướng dẫn này, học tập Excel Online vẫn chỉ mang đến bạn giải pháp tìm cùng trích xuất những giá trị riêng lẻ và độc nhất trong những trang tính của Excel. Giải pháp này kết hợp linh hoạt các công thức Excel và một trong những ứng dụng đơn giản dễ dàng của advanced filter. Ko kể ra, phương án này còn hỗ trợ một số kỹ năng rất lạ mắt như:

– Tìm với trích xuất những hàng tuyệt nhất / khác biệt dựa trên các giá trị trong một hoặc các cột. – Tìm, đánh dấu và coppy giá trị tuyệt nhất đến bất kỳ vị trí nào khác, trong và một bảng tính hoặc sang trọng bảng tính khác.

Giờ bọn họ sẽ cùng xem hoạt động của công vắt Duplicate Remover.

Giả sử các bạn có một bảng cầm tắt được tạo ra ra bằng phương pháp hợp nhất dữ liệu từ một số trong những bảng khác. Rất có thể thấy cụ thể rằng bảng nắm tắt chứa rất nhiều hàng trùng lặp và nhiệm vụ của người tiêu dùng là trích xuất hàng duy nhất lộ diện trong bảng có một lần hoặc những hàng không giống biệt bao hàm những giá bán trị lộ diện duy độc nhất một lần với một lần mở ra chung cho hầu hết giá trị trùng lặp. Sử dụng Duplicate Remover các bước sẽ được hoàn toàn trong 5 bước sau:

1. Chọn ngẫu nhiên ô như thế nào trong bảng nguồn của khách hàng và nhấp nút Duplicate Remover trên tab Ablebits Data, trong team Dedupe. thủ pháp Duplicate Remover sẽ chạy với chọn cục bộ bảng. Vì vậy, chỉ cần nhấp vào Next để liên tiếp bước tiếp theo.

Chọn các loại giá trị bạn muốn tìm cùng nhấp vào Next:

Phụ ở trong vào nhu cầu trích xuất dữ liệu của bạn để chọn chất lượng (duy nhất) hoặc unique +1st occurrences (distinct) (Những giá trị duy nhất và một lần mở ra chung cho số đông giá trị trùng lặp).

Như trong lấy ví dụ như này, họ muốn trích xuất những hàng xuất hiện một lần tốt nhất trong bảng nguồn phải ta sẽ lựa chọn Unique:

Mẹo: Như các bạn đã thấy trong ảnh chụp màn hình hiển thị ở trên, có hai lựa chọn giành cho những giá chỉ trị lộ diện nhiều lần, nhưng bạn cần nhớ là không được nhầm lẫn với gần như bảng tính khác.

3. Chọn một hoặc những cột để chất vấn lại giá trị duy nhất. Như trong lấy ví dụ như này, bọn họ cần tìm thấy một hàng tuyệt nhất dựa trên những giá trị vào cả cha cột (Order number, First name and Last name), do thế chúng ta sẽ lựa chọn tất cả. 4. Chọn ra tùy chọn cần vận dụng cho đông đảo giá trị duy nhất tìm được dựa trên các lựa chọn bao gồm sẵn sau đây: – Highlight chất lượng values – Đánh giá các giá trị nhất – Select chất lượng values – Chọn những giá trị nhất – Identify in a status column – xác định trong cột tâm lý – Copy to lớn another location – sao chép sang địa điểm khác Vì họ đang đề xuất chiết xuất những hàng duy nhất, chọn Copy lớn another location và kế tiếp chỉ định vị trí đúng đắn cần sao chép chúng – địa chỉ đó phải là 1 bảng tính đang chuyển động (chọn tuỳ lựa chọn Custom location và chỉ định và hướng dẫn ô trên thuộc của dải đích), hoặc vị trí muốn xào luộc đó bắt buộc là bảng tính mới.

Trong lấy ví dụ này, họ hãy lựa chọn trang tính mới: 5. Lựa chọn Finish, bởi vậy là đã triển khai xong.

Hy vọng bài bác hướng dẫn này giúp bạn có thể dễ dàng và lập cập trích xuất được những giá trị tốt nhất hoặc các hàng bạn phải trong Excel.

———

Ngoài ra để ứng dụng Excel vào các bước một cách hiệu quả thì chúng ta còn đề xuất sử dụng giỏi các hàm, các công vậy khác của Excel.

Một số hàm cơ bản thường chạm mặt như:

SUMIF, SUMIFS để tính tổng theo 1 điều kiện, các điều kiệnCOUNTIF, COUNTIFS nhằm thống kê, đếm theo một điều kiện, nhiều điều kiệnCác hàm xử lý dữ liệu dạng chuỗi, dạng ngày tháng, dạng số…Các hàm dò search tham chiếu Index+Match, hàm SUMPRODUCT…

Một số công cụ thường được sử dụng như:

Định dạng theo đk với Conditional formattingThiết lập điều kiện nhập dữ liệu với Data ValidationCách để Name và sử dụng Name vào công thứcLập báo cáo với Pivot Table…

Rất nhiều kỹ năng và kiến thức phải ko nào? cục bộ những kỹ năng và kiến thức này các bạn đều hoàn toàn có thể học được trong khóa học EX101 – Excel tự cơ bạn dạng tới chuyên gia của học Excel Online. Đây là khóa học giúp bạn hệ thống kiến thức và kỹ năng một bí quyết đầy đủ, chi tiết. Rộng nữa không hề có giới hạn về thời hạn học tập nên bạn cũng có thể thoải mái học bất cứ lúc nào, dễ dàng tra cứu vớt lại kỹ năng khi cần. Hiện nay hệ thống đang có ưu đãi hết sức lớn cho chính mình khi đk tham gia khóa học. Chi tiết xem tại: suachuadieuhoa68.com.vn.Online