Trong bài viết này Học Excel Online sẽ hướng dẫn các bạn cách lọc ra một danh sách các giá trị riêng biệt độc nhất trong cột bằng cách sử dụng một công thức và cách tinh chỉnh công thức đó cho các bộ dữ liệu khác nhau. Ngoài ra, Học Excel Online cũng sẽ chỉ cho các bạn cách nhanh chóng 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ê danh sách trong excel và làm thế nào để trích xuất các hàng duy nhất sử dụng Duplicate Remover. Show
Để lọc ra một danh sách các giá trị duy nhất trong Excel bạn có thể sử dụng cách xác định các giá trị duy nhất đó, lọc riêng rồi sao chép chúng. Tuy nhiên, sử dụng cách làm này sẽ tốn nhiều 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 một vài kĩ thuật khác mà Học Excel Online sẽ chia sẻ với các bạn ngay sau đây. Cách lọc ra các giá trị duy nhất trong ExcelĐể tránh nhầm lẫn, đầu tiên, chúng ta cần hiểu những giá trị nào được gọi là là các giá trị duy nhất trong Excel. Giá trị duy nhất là các giá trị chỉ tồn tại duy nhất một lần trong toàn bộ danh sách. Ví dụ: Để trích xuất một danh sách các giá trị duy nhất trong Excel, bạn 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 những giá trị duy nhất (hoàn thành công thức xong 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 thường xuyên (hoàn thành công thức xong 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
Chú thích. Bởi vì công thức tham chiếu ô bên trên ô đầu tiên của danh sách các giá trị duy nhất, thường là tiêu đề cột (như trong ví dụ này là B1), nên bạn cần đảm bảo rằng tiêu đề của bạn là một tên duy nhất không xuất hiện ở bất cứ đâu trong cột. Ở ví dụ này đang trích xuất các tên duy nhất từ cột A (chính xác hơn là trong phạm vi A2: A20), và ảnh chụp màn hình sau sẽ minh họa cho công thức sẽ dùng: Dưới đây là giải thích một cách chi tiết về việc sử dụng công thức để trích xuất các giá trị duy nhất trong bảng tính Excel của bạn: – Lựa chọn một trong những công thức dựa theo dữ liệu của bạn. Cách lọc ra các giá trị khác biệt trong Excel (duy nhất + những dữ liệu giống nhau sẽ chỉ xuất hiện một lần)Như bạn đã đoán ra từ tiêu đề của phần này, các giá trị khác biệt trong Excel là tất cả các giá trị khác nhau trong danh sách, tức là những giá trị đó chỉ xuất hiện một lần duy nhất dù nó có bao nhiêu nhân bản đ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 sử dụng công thức sau. Công thức mảng để tìm nhữ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 nhữ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à danh sách nguồn Trích xuất các giá trị khác biệt vào trong một cột và bỏ qua các ô trống
Công thức trích xuất các 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 các giá trị văn bản riêng biệt bỏ qua số và khoảng trống =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 ngay trên ô đầu tiên của danh sách riêng biệt. Ảnh chụp màn hình 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ác nhau phân biệt chữ hoa chữ thường trong ExcelKhi làm việc với những dữ liệu nhạy cảm như trong trường hợp như mật khẩu, tên người dùng hoặc tên tệp, bạn sẽ cần phải có danh sách các giá trị khác biệt nhạy cảm với từng chữ. Khi đó, bạn có thể sử dụng công thức mảng sau đây, với A2:A10 là danh sách nguồn, và B1 là ô nằm trên ô đầ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 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ệtPhần này được viết riêng cho những người muốn hiểu rõ về những công thức này một cách cụ thể nhất. Việc trích xuất các giá trị duy nhất và khác biệt trong Excel thực sự không phải một thao tác đơn giản. Nhưng bạn 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 cách tiếp cận – sử dụng INDEX / MATCH kết hợp với hàm COUNTIF, hoặc COUNTIF + IF. Để phân tích sâu hơn, chúng ta sẽ cùng sử dụng công thức mảng để trích ra một danh sách các giá trị khác biệt bởi vì tất cả các công thức khác được thảo luận trong hướng dẫn này là các công thức đã được cải tiến hoặc biến thể từ công thức cơ bản sau: =IFERROR(INDEX($A$2:$A$10, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$10), 0)), “”) Và bây giờ, chúng ta cùng tìm hiểu về những phần cốt lõi trong công thức tìm giá trị khác biệt: 1. COUNTIF(range, criteria) hàm này sẽ trả về số ô trong một phạm vi đáp ứng được điều kiện cụ thể. 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 nào trong danh sách nguồn (tiêu chí) xuất hiện trong phạm vi phù hợp với điều kiện tìm. Trong trường hợp này, phạm vi ($B$1:B1) bao gồm một mục duy nhất – “Distinct”. trở thành: MATCH(0, {0;0;0;0;0;0;0;0;0},0) Và trả về 1 Bởi vì chức năng MATCH nhận về giá trị đầu tiên cũng chính là giá trị bằng với giá trị đang đang tìm kiếm (mà giá trị đang tìm là 0). Trở thành: INDEX({“Ronnie”; “David”; “Sally”; “Jeremy”; “Robert”; “David”; “Robert”; “Tom”; “Sally”}, 1) Và trả về “Ronnie”. Khi công thức đượ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 vì tham chiếu thêm ô thứ hai (B1) là một tham chiếu tương đối – thay đổi theo vị trí tương đối của ô khi công thức di chuyển sang ô khác. Do đó, khi sao chép 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), và 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ì có một “Ronnie” được tìm 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 đầu tiên trong mảng. Mẹo: để hiểu rõ hơn vì logic của công thức, bạn có thể chọn các phần khác nhau của công thức trong thanh công thức và nhấn f9 để xem đánh giá về phần đó: Công thức giá trị duy nhất – chứa thêm một hàm COUNTIF loại bỏ những mục xuất hiệ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 nhiều lần: COUNTIF($A$2:$A$10, $A$2:$A$10)<>1. Các giá trị khác biệt bỏ qua khoảng trắng – ở đây bạn thêm một hàm IF để ngăn không cho các ô trống được thêm vào danh sách riêng biệt: IF($A$2:$A$13=””,1,0). Các giá trị văn bản khác biệt bỏ qua các con số – bạn sử dụng chức năng ISTEXT để kiểm tra xem giá trị có phải là văn bản hay không, và chức năng IF để loại bỏ 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). Trích xuất các giá trị khác biệt từ một cột sử dụng Bộ lọc nâng cao – Advanced Filter của Excel 1. Chọn cột dữ liệu bạn muốn trích xuất các giá trị khác biệt. Trích xuất những hàng duy nhất và khác biệt sử dụng Duplicate RemoverTrong phần cuối cùng của bài hướng dẫn này, Học Excel Online sẽ chỉ cho bạn giải pháp tìm và trích xuất các giá trị riêng biệt và độc nhất trong các 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 số ứng dụng đơn giản của advanced filter. Ngoài ra, giải pháp này còn cung cấp một số tính năng rất độc đáo như: – Tìm và trích xuất các hàng duy nhất / khác biệt dựa trên các giá trị trong một hoặc nhiều cột. Giờ chúng ta sẽ cùng xem hoạt động của công cụ Duplicate Remover.Giả sử bạn có một bảng tóm tắt được tạo ra bằng cách hợp nhất dữ liệu từ một số bảng khác. Có thể thấy rõ ràng rằng bảng tóm tắt chứa rất nhiều hàng trùng lặp và nhiệm vụ của bạn là trích xuất hàng duy nhất xuất hiện trong bảng chỉ một lần hoặc các hàng khác biệt bao gồm những giá trị xuất hiện duy nhất một lần và một lần xuất hiện chung cho những giá trị trùng lặp. Sử dụng Duplicate Remover công việc sẽ được hoàn tất trong 5 bước sau: 1. Chọn bất kỳ ô nào trong bảng nguồn của bạn và nhấp nút Duplicate Remover trên tab Ablebits Data, trong nhóm Dedupe. Chọn loại giá trị bạn muốn tìm và nhấp vào Next: Phụ thuộc vào nhu cầu trích xuất dữ liệu của bạn để chọn Unique (duy nhất) hoặc Unique +1st occurrences (distinct) (Những giá trị duy nhất và một lần xuất hiện chung cho những giá trị trùng lặp). Như trong ví dụ này, chúng ta muốn trích xuất các hàng xuất hiện một lần duy nhất trong bảng nguồn nên ta sẽ chọn Unique: Mẹo: Như bạn đã thấy trong ảnh chụp màn hình ở trên, có hai lựa chọn dành cho những giá trị xuất hiện nhiều lần, nhưng bạn cần nhớ là không được nhầm lẫn với những bảng tính khác.
3. Chọn một hoặc nhiều cột để kiểm tra lại giá trị duy nhất. Trong ví dụ này, chúng ta hãy chọn trang tính mới: Hy vọng bài hướng dẫn này giúp bạn có thể dễ dàng và nhanh chóng trích xuất được các giá trị duy nhất hoặc các hàng bạn cần trong Excel. ——— Ngoài ra để ứng dụng Excel vào công việc một cách hiệu quả thì bạn còn phải sử dụng tốt các hàm, các công cụ khác của Excel. Một số hàm cơ bản thường gặp như:
Một số công cụ hay sử dụng như:
Rất nhiều kiến thức phải không nào? Toàn bộ những kiến thức này các bạn đều có thể học được trong khóa học EX101 – Excel từ cơ bản 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 một cách đầy đủ, chi tiết. Hơn nữa không hề có giới hạn về thời gian học tập nên bạn có thể thoải mái học bất cứ lúc nào, dễ dàng tra cứu lại kiến thức khi cần. Hiện nay hệ thống đang có ưu đãi rất lớn cho bạn khi đăng ký tham gia khóa học. Chi tiết xem tại: HocExcel.Online |