Hàm Liệt Kê Danh Sách Trong Excel

Trong bài viết này Học Excel Online sẽ lý giải chúng ta biện pháp lọc ra một danh sách các quý giá hiếm hoi độc nhất vào cột bằng phương pháp sử dụng một cách làm với phương pháp điều khiển và tinh chỉnh công thức đó cho các bộ tài liệu khác nhau. Trong khi, Học Excel Online cũng trở nên chỉ cho chúng ta phương pháp hối hả tuyệt nhất để sở hữu được một danh sách đơn lẻ bằng phương pháp thực hiện Sở lọc nâng cấp của Excel, hàm liệt kê danh sách trong excel với có tác dụng cầm làm sao nhằm trích xuất các mặt hàng độc nhất vô nhị sử 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 những cực hiếm độc nhất vào Excel bạn cũng có thể áp dụng giải pháp xác minh những giá trị duy nhất kia, lọc riêng biệt rồi xào luộc chúng. Tuy nhiên, áp dụng biện pháp làm cho này sẽ tốn nhiều thời hạn, để tiến hành nkhô cứng hơn, chúng ta có thể sử dụng một cách làm đặc biệt với một vài kinh nghiệm khác cơ mà Học Excel Online sẽ chia sẻ cùng với chúng ta tức thì tiếp sau đây.


Xem nhanh


Cách lọc ra những giá trị duy nhất trong ExcelCách lọc ra những cực hiếm khác hoàn toàn trong Excel (độc nhất + số đông tài liệu như là nhau sẽ chỉ xuất hiện một lần)Cách trích xuất các quý hiếm khác nhau rành mạch chữ hoa chữ thường xuyên trong ExcelCách buổi giao lưu của phương pháp trích xuất các giá trị duy nhất/ không giống biệt

Cách lọc ra những quý giá tốt nhất vào Excel

Để tránh lầm lẫn, thứ nhất, chúng ta đề xuất gọi đa số cực hiếm như thế nào được điện thoại tư vấn là là các quý hiếm duy nhất vào Excel. Giá trị độc nhất vô nhị là các cực hiếm chỉ lâu dài độc nhất vô nhị một lượt 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ể thực hiện một trong các cách làm sau đây.

Công thức mảng để tìm thấy đầy đủ giá trị nhất (chấm dứt cách làm dứt 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á nhất thường xuyên (hoàn thành bí quyết kết thúc 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 – list nguồn B1 – ô đầu của danh sách duy nhất trừ đi 1. Trong ví dụ này, chúng ta bước đầu list những giá trị nhất nghỉ ngơi B2, với cho nên vì thế bọn họ chuyển B1 vào phương pháp (B2-1 = B1). Nếu danh sách những quý giá độc nhất vô nhị của doanh nghiệp bắt đầu sinh hoạt ô C3, ta đang chuyển đổi $B$1:B1 thành $C$2:C2.


*

Chụ thích. Bởi bởi cách làm tmê mệt chiếu ô bên trên ô thứ nhất của list những quý hiếm duy nhất, thường xuyên là title cột (nlỗi vào ví dụ này là B1), cần bạn phải bảo đảm rằng tiêu đề của khách hàng là 1 trong thương hiệu độc nhất không mở ra sống bất cứ đâu vào cột.

Tại ví dụ này vẫn trích xuất những thương hiệu duy nhất tự cột A (chính xác hơn là trong phạm vi A2: A20), và hình họa chụp màn hình sau vẫn minch họa mang lại công thức sẽ dùng:

Dưới đấy là phân tích và lý giải một giải pháp cụ thể về vấn đề sử dụng công thức để trích xuất các quý giá duy nhất vào bảng tính Excel của bạn:

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

Cách thanh lọc ra các quý hiếm khác hoàn toàn trong Excel (tuyệt nhất + những tài liệu giống như nhau đã chỉ lộ diện một lần)

Nlỗi chúng ta sẽ đân oán ra tự tiêu đề của phần này, các quý hiếm khác hoàn toàn trong Excel là tất cả những quý giá không giống nhau trong list, có nghĩa là đầy đủ quý hiếm kia chỉ lộ diện một đợt độc nhất dù nó tất cả bao nhiêu nhân bản đi chăng nữa. Ví dụ:

Để có được một danh sách các cực hiếm khác hoàn toàn vào Excel, ta sử dụng bí quyết sau.

Công thức mảng để kiếm tìm phần đông quý hiếm khác hoàn toàn (dìm 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 chỉnh để tìm hầu như quý giá 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 Bmột là ô nằm phía bên trên của danh sách những quý giá biệt lập. Trong ví dụ này, danh sách những quý hiếm khác biệt ban đầu sống ô B2 (đấy là ô trước tiên bạn nhập công thức).

Trích xuất các cực hiếm khác biệt vào vào một cột cùng bỏ qua mất các ô trống Nếu danh sách nguồn của người tiêu dùng đựng ô trống, thì những công thức bọn chúng ra vừa nêu sống trên vẫn trả về một trong những ko cho từng mặt hàng trống. Để khắc chế điều này, bọn họ yêu cầu cách tân bí quyết thêm một ít.

Học xây dựng VBA vào Excel sinh sống đâu?

Công thức trích xuất các quý giá biệt lập không bao gồm khoảng chừ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 list những quý hiếm văn uống bản hiếm hoi bỏ lỡ số cùng khoảng trống Theo bí quyết tựa như, bạn có thể cảm nhận một list những quý giá cá biệt trừ các ô và ô trống có đựng 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 số đó, A2: A10 là list mối cung cấp, và Bmột là ô nằm ở bên trên ô trước tiên của danh sách riêng biệt.

Hình ảnh chụp màn hình dưới đây cho biết thêm tác dụng của cả nhì cách làm trên:

Cách trích xuất những quý hiếm khác nhau phân biệt chữ hoa chữ hay vào Excel

Lúc thao tác với phần đông tài liệu mẫn cảm nhỏng trong trường phù hợp nlỗi mật khẩu đăng nhập, tên người tiêu dùng hoặc thương hiệu tệp, các bạn sẽ rất cần được gồm danh sách các giá trị biệt lập nhạy cảm cùng với từng chữ. Khi kia, bạn cũng có thể thực hiện công thức mảng sau đây, với A2:A10 là danh sách nguồn, với B1 là ô vị trí ô trước tiên của list riêng biệt:

Công thức mảng để nhấn các giá trị riêng lẻ theo từng trường đúng theo (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 những quý hiếm duy nhất/ khác biệt

Phần này được viết riêng biệt cho những người mong muốn nắm rõ về mọi bí quyết này một phương pháp ví dụ tốt nhất.

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

Để so sánh sâu hơn, chúng ta đang cùng áp dụng công thức mảng nhằm trích ra một danh sách các quý hiếm khác hoàn toàn chính vì tất cả những cách làm không giống được đàm luận vào chỉ dẫn này là những phương pháp đã có được cách tân hoặc trở thành thể từ bí quyết cơ 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 bắt đầu bắt đầu, chúng ta có thể làm lơ hàm IFERROR, hàm này được thực hiện với cùng 1 mục đích tốt nhất nhằm đào thải lỗi # N/A Lúc số ô cơ mà chúng ta đang sao chép cách làm quá quá con số những quý giá khác hoàn toàn vào list nguồn.

Và hiện nay, họ thuộc tìm hiểu về đông đảo phần chủ đạo trong cách làm tìm kiếm quý giá không giống 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 ĐK rõ ràng. Nhỏng ngơi nghỉ trong ví dụ này, COUNTIF($B$1:B1, $A$2:$A$10) trả về mảng 1 với 0 dựa vào ngẫu nhiên quý hiếm nào trong danh sách mối cung cấp ($A$2:$A$10) lộ diện trong danh sách các giá trị khác biệt ($B$1:B1). Nếu cực hiếm được tra cứu thấy, phương pháp sẽ trả về 1, giả dụ không kiếm thấy đã trả về – 0.

Cụ thể, trong ô B2, COUNTIF($B$1:B1, $A$2:$A$10 trngơi nghỉ 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

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

trlàm việc thành:

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

Và trả về 1

Bởi vày chức năng MATCH dìm về quý giá thứ nhất cũng đó là quý giá bởi với mức giá trị vẫn sẽ tra cứu tìm (nhưng quý giá đang kiếm tìm là 0). 3. INDEX(array, row_num, ) trả về một cực hiếm phía trong một mảng dựa vào sản phẩm đã có hướng đẫn cùng đều số cột (tùy ý). Trong ví dụ này, INDEX($A$2:$A$10, 1)

Trsống 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 cực hiếm biệt lập ($B$1:B1) vẫn không ngừng mở rộng hơn bởi tsay mê chiếu thêm ô sản phẩm hai (B1) là 1 trong những tđam mê chiếu kha khá – biến đổi theo địa chỉ kha khá của ô Lúc cách làm dịch rời thanh lịch ô khác.

Xem thêm: Tổng Hợp Cách Sửa Iphone Bị Lỗi Đầy Bộ Nhớ Khắc Phục Như Thế Nào?

Do kia, lúc 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 trnghỉ ngơi 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ì gồm một “Ronnie” được search thấy vào 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à địa điểm kha khá của số 0 thứ nhất vào mảng. Cuối thuộc, INDEX($A$2:$A$10, 2) trả về quý giá tự mặt hàng thứ hai, đó là “David”.

Mẹo: để nắm rõ rộng do xúc tích của phương pháp, bạn cũng có thể chọn những phần khác nhau của bí quyết vào thanh khô cách làm với nhận f9 giúp thấy Reviews về phần đó: Nlỗi đã nhắc, các cách làm khác được bàn luận trong bài bác gợi ý này dựa vào lô ghích giống như, tuy thế có thêm chút cố kỉnh đổi:

Công thức giá trị tốt nhất – chứa thêm 1 hàm COUNTIF loại bỏ các mục lộ diện các lần trong list nguồn ngoài danh sách những giá trị duy nhất: ngoài list tốt nhất tất cả các mục mở ra vào danh sách mối cung cấp nhiều lần: COUNTIF($A$2:$A$10, $A$2:$A$10)1.

Các quý giá khác hoàn toàn bỏ lỡ khoảng chừng trắng – tại đây bạn thêm một hàm IF để ngăn không cho các ô trống được chế tạo danh sách riêng biệt: IF($A$2:$A$13=””,1,0).

Các quý giá vnạp năng lượng phiên bản biệt lập bỏ lỡ những con số – bạn áp dụng tác dụng ISTEXT để khám nghiệm coi cực hiếm liệu có phải là văn bản hay không, và tác dụng IF để vứt bỏ tất cả các nhiều loại giá trị không giống, bao hàm những ô trống: IF(ISTEXT($A$2:$A$13)=FALSE,1,0).

Trích xuất các quý giá khác biệt xuất phát từ 1 cột sử dụng Bộ lọc nâng cấp – Advanced Filter của Excel Nếu bạn không thích tiêu tốn lãng phí thời gian nhằm gọi cặn kẽ các bí quyết quý giá khác hoàn toàn, bạn cũng có thể mau lẹ đã đạt được danh sách các quý hiếm khác biệt bằng phương pháp sử dụng Sở lọc nâng cao bằng phương pháp tiến hành quá trình cụ thể tiếp sau đây.

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

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

Trích xuất hồ hết sản phẩm tuyệt nhất với khác hoàn toàn thực hiện Duplicate Remover

Trong phần cuối cùng của bài xích trả lời này, Học Excel Online sẽ chỉ cho bạn phương án tìm kiếm với trích xuất các cực hiếm riêng lẻ với độc nhất vô nhị trong các trang tính của Excel. Giải pháp này kết hợp linh hoạt các bí quyết Excel cùng một trong những ứng dụng đơn giản dễ dàng của advanced filter. Dường như, phương án này còn hỗ trợ một trong những nhân kiệt hết sức rất dị như:

– Tìm và trích xuất những mặt hàng độc nhất / khác biệt dựa vào những quý hiếm trong một hoặc các cột. – Tìm, lưu lại cùng sao chép cực hiếm tuyệt nhất cho ngẫu nhiên địa chỉ làm sao khác, trong và một bảng tính hoặc thanh lịch bảng tính khác.

Giờ họ sẽ cùng coi buổi giao lưu của hiện tượng Duplicate Remover.

Giả sử các bạn bao gồm một bảng nắm tắt được tạo nên bằng cách hợp nhất dữ liệu trường đoản cú một trong những bảng khác. cũng có thể thấy cụ thể rằng bảng bắt tắt cất không ít hàng giống nhau và trọng trách của người sử dụng là trích xuất mặt hàng độc nhất vô nhị lộ diện vào bảng duy nhất lần hoặc những sản phẩm khác biệt bao hàm số đông quý hiếm mở ra tốt nhất một lần với một lượt mở ra phổ biến mang lại hầu hết quý giá đụng hàng. Sử dụng Duplicate Remover các bước sẽ được hoàn tất trong 5 bước sau:

1. Chọn ngẫu nhiên ô nào vào bảng nguồn của công ty và nhấp nút ít Duplicate Remover bên trên tab Ablebits Data, vào nhóm Dedupe. Thủ thuật Duplicate Remover đã chạy cùng chọn tổng thể bảng. Vì vậy, chỉ việc nhấp vào Next nhằm thường xuyên bước tiếp theo.

Chọn các loại quý hiếm bạn có nhu cầu search với nhấp vào Next:

Phú trực thuộc vào nhu cầu trích xuất tài liệu của doanh nghiệp nhằm lựa chọn Unique (duy nhất) hoặc Unique +1st occurrences (distinct) (Những cực hiếm duy nhất với một lượt xuất hiện tầm thường mang đến đa số giá trị trùng lặp).

Nhỏng vào ví dụ này, họ mong mỏi trích xuất các hàng xuất hiện thêm một đợt duy nhất vào bảng nguồn đề nghị ta sẽ chọn Unique:

Mẹo: Nlỗi chúng ta vẫn thấy vào hình họa chụp màn hình nghỉ ngơi bên trên, tất cả hai tuyển lựa dành riêng cho đều quý hiếm xuất hiện thêm các lần, nhưng bạn phải hãy nhớ là ko được nhầm lẫn cùng với số đông bảng tính khác.

3. Chọn một hoặc nhiều cột để kiểm tra lại quý hiếm độc nhất. Nlỗi trong ví dụ này, họ đề nghị đưa ra một hàng duy nhất dựa vào các quý giá trong cả tía cột (Order number, First name and Last name), chính vì thế bọn họ đã lựa chọn tất cả. 4. Chọn ra tùy chọn yêu cầu vận dụng cho gần như giá trị nhất kiếm được dựa vào các chắt lọc có sẵn sau đây: – Highlight quality values – Đánh giá chỉ các giá trị độc nhất – Select unique values – Chọn những quý hiếm độc nhất vô nhị – Identify in a status column – Xác định vào cột tinh thần – Copy lớn another location – Sao chép lịch sự địa điểm không giống Vì chúng ta vẫn đề nghị tinh chiết các mặt hàng độc nhất, lựa chọn Copy khổng lồ another location cùng kế tiếp chỉ định vị trí đúng mực nên coppy bọn chúng – vị trí đó cần là 1 bảng tính đã vận động (lựa chọn tuỳ chọn Custom location và hướng đẫn ô bên trên cùng của dải đích), hoặc vị trí mong coppy đó đề xuất là bảng tính bắt đầu.

Trong ví dụ này, họ nên chọn trang tính mới: 5. Chọn Finish, như thế là vẫn thực hiện hoàn thành.

Hy vọng bài bác chỉ dẫn này góp chúng ta cũng có thể dễ dàng với lập cập trích xuất được các quý giá độc nhất vô nhị hoặc các sản phẩm bạn phải vào Excel.

———

Hình như nhằm ứng dụng Excel vào quá trình một cách công dụng thì các bạn còn phải thực hiện giỏi các hàm, những lao lý khác của Excel.

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

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

Một số chính sách hay sử dụng như:

Định dạng theo ĐK cùng với Conditional formattingThiết lập ĐK nhập tài liệu với Data ValidationCách đặt Name với áp dụng Name trong công thứcLập report cùng với Pivot Table…

Rất những kiến thức và kỹ năng bắt buộc ko nào? Toàn cỗ hầu hết kiến thức này chúng ta hầu hết rất có thể học tập được trong khóa học EX101 – Excel từ bỏ cơ bạn dạng cho tới chăm gia của Học Excel Online. Đây là khóa học giúp bạn khối hệ thống kiến thức một giải pháp không hề thiếu, cụ thể. hơn nữa không còn gồm số lượng giới hạn về thời hạn học hành cần bạn có thể dễ chịu và thoải mái học bất cứ thời gian làm sao, dễ dàng tra cứu vớt lại kiến thức và kỹ năng khi bắt buộc. Lúc bấy giờ khối hệ thống đang có ưu tiên không nhỏ cho chính mình lúc ĐK tđê mê gia khóa huấn luyện và đào tạo. Chi máu coi tại: cbmobile.mobi.Online