Rút trích dữ liệu trong excel

     

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

Để lọc ra một danh sách những giá trị tốt nhất trong Excel chúng ta có thể sử dụng cách xác định các quý giá duy nhất đó, lọc riêng rồi coppy chúng. Tuy nhiên, sử dụng cách làm cho này đã tốn những thời gian, để thực hiện nhanh hơn, bạn có thể sử dụng một công thức đặc biệt 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: Rút trích dữ liệu trong excel

Xem nhanh

Cách thanh lọc ra những giá trị nhất trong ExcelCách thanh lọc ra những giá trị khác biệt trong Excel (duy duy nhất + những dữ liệu giống nhau đã chỉ mở ra 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ữ thường 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ông giống biệt

Cách lọc ra các giá trị độc nhất trong Excel

Để tránh nhầm lẫn, đầu tiên, bọn họ cần hiểu phần lớn giá trị nào được hotline là là các giá trị duy nhất trong Excel. Cực hiếm duy 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 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 phần đông giá trị độc nhất (hoàn thành công xuất sắc thức xong xuôi ta thừa 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 tiếp tục (hoàn thành công thức ngừng 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 list duy nhất trừ đi 1. Trong ví dụ như này, bọn chúng ta bắt đầu danh sách những giá trị tuyệt nhất ở B2, và bởi đó họ đưa B1 vào bí quyết (B2-1 = B1). Nếu như danh sách những giá trị độc nhất của bạn bắt đầu ở ô C3, ta sẽ biến đổi $B$1:B1 thành $C$2:C2.


*

Chú thích. Bởi vì công thức tham chiếu ô bên trên ô trước tiên 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 bạn cần đảm bảo an toàn rằng tiêu đề của bạn là một tên độc nhất không xuất hiện thêm ở bất cứ đâu trong cột.

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

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

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

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

Như bạn đã đoán ra từ tiêu đề của phần này, những giá trị khác hoàn toàn trong Excel là toàn bộ các giá bán trị khác nhau trong danh sách, có nghĩa là những giá trị đó chỉ lộ diện một lần duy nhất dù nó tất cả bao nhiêu nhân bạn dạng đi chăng nữa. Ví dụ:

Để giành được một danh sách các giá trị khác hoàn toàn trong Excel, ta sử dụng công thức sau.

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

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

Học xây dựng VBA trong Excel sinh hoạt đâu?

Công thức trích xuất các 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 phiên bản riêng biệt bỏ lỡ số và không gian Theo phương pháp tương tự, bạn có thể nhận được một danh sách những giá trị riêng biệt trừ các ô 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 danh sách riêng biệt.

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

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

Khi thao tác làm việc với những tài liệu nhạy cảm như trong trường đúng theo 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ị biệt lập nhạy cảm cùng với từng chữ. Khi đó, bạn có thể sử dụng cách làm mảng sau đây, cùng với A2:A10 là list nguồn, cùng B1 là ô nằm ở ô đầu tiên của list riêng biệt:

Công thức mảng nhằm nhận những giá trị đơn lẻ theo từng trường 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 những giá trị duy nhất/ khác biệt

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

Việc trích xuất những giá trị duy nhất và khác hoàn toàn trong Excel thực sự chưa 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 toàn bộ các công thức đều được dựa trên cùng một giải 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.

Xem thêm: Top 7 Phần Mềm Ghi Đĩa Dvd Video Từ File Mp4 Vào Đĩa Dvd Bằng Nero

Để phân tích sâu hơn, bọn họ sẽ cùng sử 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ì tất cả các bí quyết khác được luận bàn trong lý giải này là các công thức đã được cách tâ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, 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 đích duy độc nhất để loại bỏ lỗi # N/A khi số ô mà chúng ta đã sao chép công thức vượt quá số lượng các giá trị biệt lập trong danh sách nguồn.

Và bây giờ, họ cùng khám phá về mọi phần căn bản trong bí quyết tìm cực hiếm khác biệt:

1. COUNTIF(range, criteria) hàm này sẽ trả về số ô trong một phạm vi đáp ứng nhu cầu được đk cụ thể. Như làm việc trong 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ị như thế 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 quý giá được tìm thấy, phương pháp sẽ trả về 1, nếu không kiếm thấy đang 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 có mục như thế nào trong danh sách nguồn (tiêu chí) mở ra trong phạm vi tương xứng 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 độc nhất vô nhị – “Distinct”. 2. MATCH (lookup_value, lookup_array, ) trả về vị trí tương đối của cực hiếm đang kiếm tìm trong mảng. Trong lấy ví dụ như này, lookup_value là 0, phải 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ì tác dụng MATCH nhấn về giá chỉ trị trước tiên cũng đó là giá trị bằng với giá trị vẫn đang tra cứu kiếm (mà quý hiếm đang 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 trên hàng đang được chỉ định và hồ hết số cột (tùy ý). Trong ví dụ như 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 sao chép công thức quý phái ô B3, COUNTIF($B$1:B1, $A$2:$A$10) biến hóa thành COUNTIF($B$1:B2, $A$2:$A$10), với 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 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í kha khá của số 0 thứ nhất trong mảng. Cuối cùng, INDEX($A$2:$A$10, 2) trả về quý giá từ hàng sản phẩm 2, đó là “David”.

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

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

Trích xuất những giá trị biệt lập từ một cột áp dụng Bộ lọc nâng cấp – Advanced Filter của Excel nếu khách hàng không mong mỏi lãng phí thời gian để hiểu cặn kẽ các công thức giá trị khác biệt, bạn cũng có thể nhanh chóng dành được danh sách các giá trị không giống biệt bằng cách 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. Chọn cột dữ liệu bạn muốn trích xuất những giá trị khác biệt. 2. đưa sang tab Data > Sort và Filter cùng nhấp vào nút Advanced: 3. Trong vỏ hộp thoại Advanced Filter, các bạn chọn các tuỳ chọn sau: – kiểm tra nút Copy lớn another location. – vào hộp danh mục range, xác minh rằng phạm vi nguồn được hiển thị thiết yếu xác. – Trong hộp Copy to, nhập phạm vi trên cùng của dải ô bạn muốn sao chép tài liệu sang. Chú ý là bạn chỉ rất có thể sao chép dữ liệu đã được thanh lọc vào trang tính đang hoạt động. – Chọn chất lượng records only.

qqlive| j88