Trích danh sách trong excel

     

Trong nội dung bài viết này học Excel Online đã hướng dẫn các bạn cách lọc ra một danh sách các giá trị đơn lẻ độc tốt nhất trong cột bằng cách sử dụng một công thức và cách điều khiển công thức đó cho những bộ tài liệu khác nhau. Không tính ra, Học Excel Online cũng sẽ chỉ cho các bạn cách nhanh lẹ nhất để có được một list riêng biệt bằng cách sử dụng cỗ lọc nâng cao của Excel, hàm liệt kê danh sách trong excel và làm cố nào để trích xuất các hàng duy nhất sử dụng Duplicate Remover.Bạn đang xem: phương pháp 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 minh các quý hiếm duy độc nhất đó, thanh lọc riêng rồi coppy chúng. Tuy nhiên, thực hiện cách làm cho này vẫn tốn những thời gian, để triển khai nhanh hơn, bạn cũng có thể sử dụng một công thức đặc biệt cùng với 1 vài kinh nghiệm khác nhưng Học Excel Online sẽ chia sẻ với các bạn ngay sau đây.

Bạn đang xem: Trích danh sách trong excel

Xem nhanh

Cách lọc ra những giá trị độc nhất vô nhị trong ExcelCách lọc ra những giá trị biệt lập trong Excel (duy tuyệt nhất + những dữ liệu giống nhau vẫn chỉ mở ra một lần)Cách trích xuất những giá trị khác nhau 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 thanh lọc ra các giá trị duy nhất trong Excel

Để kiêng nhầm lẫn, đầu tiên, bọn họ cần hiểu phần lớn giá trị làm sao được hotline là là những giá trị tuyệt nhất trong Excel. Quý hiếm duy tốt nhất là các giá trị chỉ tồn tại 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 vô nhị trong Excel, chúng ta 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 đa số giá trị độc nhất vô nhị (hoàn thành công xuất sắc thức xong xuôi 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ý hiếm duy nhất liên tục (hoàn thành công thức ngừng ta 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ụ như này, chúng ta bước đầu danh sách các giá trị nhất ở B2, và vì đó chúng ta đưa B1 vào cách làm (B2-1 = B1). Trường hợp danh sách các giá trị nhất của bạn bước đầu ở ô C3, ta sẽ biến hóa $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 những giá trị duy nhất, thường là title cột (như trong lấy ví dụ như này là B1), nên chúng ta cần bảo đảm rằng tiêu đề của công ty là một tên tuyệt nhất không xuất hiện ở bất cứ đâu vào cột.

Ở lấy một ví dụ này đã trích xuất những tên duy 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 sau đã minh họa cho cách làm sẽ dùng:

Dưới đó là giải ưa thích một cách cụ thể về việc thực hiện công thức để trích xuất các giá trị tốt nhất trong bảng tính Excel của bạn:

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

Cách thanh lọc ra những giá trị khác hoàn toàn trong Excel (duy tuyệt nhất + những dữ liệu giống nhau đang chỉ mở ra một lần)

Như các bạn đã đoán ra từ title của phần này, các giá trị biệt lập trong Excel là tất cả các giá bán trị khác nhau trong danh sách, tức là những quý giá đó chỉ xuất hiện thêm một lần độ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 những giá trị khác hoàn toàn trong Excel, ta áp dụng công thức sau.

Công thức mảng nhằm tìm hầu hết 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 chỉnh để tìm phần lớn 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à danh sách nguồn B1 là ô nằm bên trên của danh sách những giá trị khác biệt. Trong lấy ví dụ này, danh sách các giá trị khác biệt ban đầu ở ô B2 (đây là ô thứ nhất bạn nhập công thức).

Trích xuất các giá trị khác biệt vào trong một cột và vứt qua những ô trống Nếu danh sách nguồn của người tiêu dùng chứa ô trống, thì những công thức chúng ra vừa nêu ở trên đã trả về một số 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 một chút.

Học thiết kế VBA trong Excel sinh hoạt đâu?

Công thức trích xuất những giá trị khác biệt 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 những giá trị văn bạn dạng riêng biệt bỏ lỡ số và khoảng trống Theo bí quyết tương tự, bạn có thể nhận được một danh sách các giá trị lẻ tẻ trừ các ô với ô trống 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à danh sách nguồn, với B1 là ô nằm ở trên ô thứ nhất của danh sách riêng biệt.

Ảnh chụp màn hình sau đây cho biết kết quả của tất cả hai công thức trên:

Cách trích xuất các giá trị khác biệt phân biệt chữ hoa chữ thường xuyên 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, các bạn sẽ cần phải gồm danh sách các giá trị khác hoàn toàn nhạy cảm với từng chữ. Lúc đó, chúng ta có thể sử dụng phương pháp mảng sau đây, với A2:A10 là list nguồn, và B1 là ô vị trí ô đầu tiên của danh sách riêng biệt:

Công thức mảng để nhận các 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 hoạt động của công thức trích xuất những giá trị duy nhất/ không giống biệt

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

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

Xem thêm: Giải Bài 10 Sinh 8 Bài 10 Bảng 10 : Hoạt Động Của Cơ, Sinh Học Lớp 8

Để phân tích sâu hơn, chúng ta sẽ cùng áp dụng công thức mảng nhằm trích ra một danh sách các giá trị biệt lập bởi vì toàn bộ các bí quyết khác được đàm luận trong giải đáp này là những công thức đang được cải tiến hoặc biến đổi thể từ phương pháp cơ 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ó 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 để thải trừ lỗi # N/A lúc số ô mà chúng ta đã coppy công thức quá quá số lượng các giá bán trị biệt lập trong danh sách nguồn.

Và bây giờ, chúng ta cùng tò mò về hầu như phần chính yếu trong bí quyết tìm quý giá khác biệt:

1. COUNTIF(range, criteria) hàm này vẫn trả về số ô vào một phạm vi thỏa mãn nhu cầu được điều kiện cụ thể. Như nghỉ ngơi trong ví dụ như này, COUNTIF($B$1:B1, $A$2:$A$10) trả về mảng 1 cùng 0 dựa trên bất kỳ giá trị như thế nào trong list nguồn ($A$2:$A$10) lộ diện trong danh sách các giá trị biệt lập ($B$1:B1). Nếu quý hiếm được tìm thấy, bí quyết sẽ trả về 1, nếu không kiếm thấy đã 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 nào trong danh sách nguồn (tiêu chí) xuất hiện thêm trong phạm vi phù hợp với đk tìm. Trong trường hòa hợp này, phạm vi ($B$1:B1) bao 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 cực hiếm đang search trong mảng. Trong lấy ví dụ này, lookup_value là 0, nê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á bán trị đầu tiên cũng đó là giá trị bằng với cái giá trị sẽ đang tìm kiếm (mà quý giá đang tìm là 0). 3. INDEX(array, row_num, ) trả về một giá chỉ trị nằm trong một mảng dựa trên hàng đã được hướng dẫn và chỉ định và phần nhiều số cột (tùy ý). Trong 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 sang ô B3, COUNTIF($B$1:B1, $A$2:$A$10) thay đổi 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ì 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, bởi vì 2 là vị trí kha khá của số 0 thứ nhất trong mảng. Cuối cùng, INDEX($A$2:$A$10, 2) trả về cực hiếm từ hàng sản phẩm công nghệ 2, sẽ là “David”.

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

Công thức cực hiếm duy tốt nhất – cất thêm một hàm COUNTIF đào thải những mục xuất hiện thêm nhiều lần trong list nguồn ngoài danh sách các giá trị duy nhất: khỏi list duy nhất tất cả 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á trị biệt lập bỏ qua khoảng chừng trắng – ở đây bạn thêm 1 hàm IF để ngăn 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 cực hiếm văn bản khác biệt bỏ qua những con số – bạn sử dụng công dụng ISTEXT để chất vấn xem giá bán 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 giá trị khác, bao gồm các ô trống: IF(ISTEXT($A$2:$A$13)=FALSE,1,0).

Xem thêm: Tất Cả Các Loại Sữa Hộp Sữa Bột Pha Sẵn, Sữa Hạt Tăng Cân Cho Bé

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

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