Cách lọc ra một danh sách các giá trị duy nhất và khác biệt trong Excel

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.

Để lọc ra một list những giá trị duy nhất trong Excel bạn hoàn toàn có thể sử dụng cách xác lập những 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 hạn, để thực thi nhanh hơn, bạn hoàn toàn có thể sử dụng một công thức đặc biệt quan trọng cùng với một vài kĩ thuật khác mà Học Excel Online sẽ san sẻ với những bạn ngay sau đây .

Cách lọc ra những 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 xong công thức xong ta nhấn Ctrl + Shift + Enter ) :

= IFERROR ( INDEX ( $ A $ 2 : USD A $ 10, MATCH ( 0, COUNTIF ( USD B USD 1 : B1, $ A $ 2 : USD A $ 10 ) + ( COUNTIF ( $ A $ 2 : USD A $ 10, $ A $ 2 : USD A $ 10 ) < > 1 ), 0 ) ), “ ” )

Công thức giá trị duy nhất tiếp tục ( hoàn thành xong công thức xong ta nhấn Enter ) :

= IFERROR ( INDEX ( $ A $ 2 : USD A $ 10, MATCH ( 0, INDEX ( COUNTIF ( USD B USD 1 : B1, $ A $ 2 : USD A $ 10 ) + ( COUNTIF ( $ A $ 2 : USD A $ 10, $ A $ 2 : USD A $ 10 ) < > 1 ), 0,0 ), 0 ) ), “ ” )

Trong đó :

A2: A10 – danh sách nguồn
B1 – ô đầu của danh sách duy nhất trừ đi 1. Trong ví dụ này, chúng ta bắt đầu danh sách các giá trị duy nhất ở B2, và do đó chúng ta đưa B1 vào công thức (B2-1 = B1). Nếu danh sách các giá trị duy nhất của bạn bắt đầu ở ô C3, ta sẽ thay đổ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 ô tiên phong của list những 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 bảo vệ rằng tiêu đề của bạn là một tên duy nhất không Open ở bất kể đâ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à lý giải một cách chi tiết cụ thể về việc sử dụng công thức để trích xuất những 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.
– Nhập công thức vào ô đầu tiên của danh sách các giá trị duy nhất (trong ví dụ sẽ là ô B2).
– Nếu bạn đang sử dụng công thức mảng, nhấn Ctrl + Shift + Enter. Nếu bạn chọn công thức bình thường, hãy nhấ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 công thức giá trị duy nhất được gói gọn trong hàm IFERROR, bạn có thể sao chép công thức đến cuối bảng mà không làm lộn xộn dữ liệu vì bất kỳ 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ị độc lạ trong Excel ( duy nhất + những tài liệu giống nhau sẽ chỉ Open 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 list những giá trị độc lạ trong Excel, ta sử dụng công thức sau .

Công thức mảng để tìm những giá trị độc lạ ( nhấn Ctrl + Shift + Enter ) :

= IFERROR ( INDEX ( $ A $ 2 : USD A $ 10, MATCH ( 0, COUNTIF ( USD B USD 1 : B1, $ A $ 2 : USD A $ 10 ), 0 ) ), “ ” )

Công thức chuẩn để tìm những giá trị độc lạ

= IFERROR ( INDEX ( $ A $ 2 : USD A $ 10, MATCH ( 0, INDEX ( COUNTIF ( USD B USD 1 : B1, $ A $ 2 : USD A $ 10 ), 0, 0 ), 0 ) ), “ ” )

Trong đó :

A2:A10 là danh sách nguồn
B1 là ô nằm phía trên của danh sách các giá trị khác biệt. Trong ví dụ này, danh sách các giá trị khác 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 trong 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ì những công thức chúng ra vừa nêu ở trên sẽ trả về một số không cho mỗi hàng trống. Để khắc phục điều này, chúng ta cần cải tiến công thức thêm một chút.

Công thức trích xuất những giá trị độc lạ không gồm có khoảng trống :

= IFERROR ( INDEX ( $ A $ 2 : USD A $ 10, MATCH ( 0, COUNTIF ( USD B USD 1 : B1, $ A $ 2 : USD A $ 10 và ” ” ) + IF ( $ A $ 2 : USD 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
Theo cách tương tự, bạn có thể nhận được một danh sách các giá trị riêng biệt trừ các ô và ô trống có chứa số:

= IFERROR ( INDEX ( $ A $ 2 : USD A $ 10, MATCH ( 0, COUNTIF ( USD B USD 1 : B1, $ A $ 2 : USD A $ 10 và ” ” ) + IF ( ISTEXT ( $ A $ 2 : USD A $ 10 ) = FALSE, 1,0 ), 0 ) ), “ ” )
Trong đó, A2 : A10 là list nguồn, và B1 là ô nằm ngay trên ô tiên phong của list riêng không liên quan gì đến nhau .

Ả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 những giá trị khác nhau phân biệt chữ hoa chữ thường trong Excel

Khi thao tác với những tài 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ó list những giá trị độc lạ nhạy cảm với từng chữ. Khi đó, bạn hoàn toàn có thể sử dụng công thức mảng sau đây, với A2 : A10 là list nguồn, và B1 là ô nằm trên ô tiên phong của list riêng không liên quan gì đến nhau :

Công thức mảng để nhận những giá trị riêng không liên quan gì đến nhau 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 giải trí của công thức trích xuất những giá trị duy nhất / độc lạ

Phầ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 đơn cử nhất .
Việc trích xuất những giá trị duy nhất và độc lạ trong Excel thực sự không phải một thao tác đơn thuần. Nhưng bạn hoàn toàn có thể thấy rằng tổng thể những 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 tích hợp với hàm COUNTIF, hoặc COUNTIF + IF .
Để nghiên cứu và phân tích sâu hơn, tất cả chúng ta sẽ cùng sử dụng công thức mảng để trích ra một list những giá trị độc lạ do tại tổng thể những công thức khác được đàm đạo trong hướng dẫn này là những công thức đã được nâng cấp 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)), “”)

Đối với người mới bắt đầu, chúng ta có thể bỏ qua hàm IFERROR, hàm này được sử dụng với một mục đích duy nhất để loại bỏ lỗi # N/A khi số ô mà bạn đã sao chép công thức vượt quá số lượng các giá trị khác biệt trong danh sách nguồn.

Và giờ đây, tất cả chúng ta cùng tìm hiểu và khám phá về những phần cốt lõi trong công thức tìm giá trị độc lạ :

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ể.
Như ở trong ví dụ này, COUNTIF($B$1:B1, $A$2:$A$10) trả về mảng 1 và 0 dựa trên bất kỳ 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ị khác biệt ($B$1:B1). Nếu giá trị được tìm thấy, công thức sẽ trả về 1, nếu không tìm thấy sẽ trả về – 0.

Cụ thể, trong ô B2, COUNTIF ( USD B USD 1 : B1, $ A $ 2 : USD 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”.
2. MATCH (lookup_value, lookup_array, [match_type]) trả về vị trí tương đối của giá trị đang tìm trong mảng.
Trong 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ì 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).
3. INDEX(array, row_num, [column_num]) trả về một giá trị nằm trong một mảng dựa trên hàng đã được chỉ định và những 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 ” .
Khi công thức được sa chép xuống cột, list những giá trị độc lạ ( USD B USD 1 : B1 ) sẽ lan rộng ra hơn vì tham chiếu thêm ô thứ hai ( B1 ) là một tham chiếu tương đối – biến hóa theo vị trí tương đối của ô khi công thức vận động và di chuyển sang ô khác .
Do đó, khi sao chép công thức sang ô B3, COUNTIF ( USD B USD 1 : B1, $ A $ 2 : USD A $ 10 ) đổi khác thành COUNTIF ( USD B USD 1 : B2, $ A $ 2 : USD 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 khoanh vùng phạm vi USD B USD 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.
Cuối cùng, INDEX($A$2:$A$10, 2) trả về giá trị từ hàng thứ 2, đó là “David”.

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 đó:

Như đã đề cập, các công thức khác được thảo 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 thay đổi:

Công thức giá trị duy nhất – chứa thêm một hàm COUNTIF vô hiệu những mục Open nhiều lần trong list nguồn khỏi list những giá trị duy nhất : khỏi list duy nhất tổng thể những mục Open trong list nguồn nhiều lần : COUNTIF ( $ A $ 2 : USD A $ 10, $ A $ 2 : USD A $ 10 ) < > 1 .
Các giá trị độc lạ bỏ lỡ khoảng chừng trắng – ở đây bạn thêm một hàm IF để ngăn không cho những ô trống được thêm vào list riêng không liên quan gì đến nhau : IF ( $ A $ 2 : USD A $ 13 = ” ”, 1,0 ) .
Các giá trị văn bản độc lạ bỏ lỡ những số lượng – bạn sử dụng tính năng ISTEXT để kiểm tra xem giá trị có phải là văn bản hay không, và tính năng IF để vô hiệu toàn bộ những loại giá trị khác, gồm có những ô trống : IF ( ISTEXT ( $ A $ 2 : USD 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
Nếu bạn không muốn 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ó thể nhanh chóng có được danh sách các giá trị khác biệt bằng cách sử dụng Bộ lọc nâng cao bằng cách thực hiện các bước chi tiết 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, bạn chọn các tuỳ chọn sau:
– Kiểm tra nút Copy to another location.
– Trong 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 cùng của dải ô bạn muốn sao chép dữ liệu sang. Lưu ý là bạn chỉ có thể sao chép dữ liệu đã được lọc vào trang tính đang hoạt động.
– Chọn Unique records only.

4. Cuối cùng, bạn nhấn OK và kiểm tra lại kết quả:

Bạn cần lưu ý rằng, dù tùy chọn của Advanced Filter được đặt tên là “Unique records only” (Chỉ có những bản ghi duy nhất) tuy nhiên nó vẫn trích xuất những giá trị khác biệt, nghĩa là bao gồm các giá trị chỉ xuất hiện một lần duy nhất và một lần xuất hiện chung cho những giá trị trùng lặp.

Trích xuất những hàng duy nhất và độc lạ sử dụng Duplicate Remover

Trong phần ở đầu cuối 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 những giá trị riêng không liên quan gì đến nhau và độc nhất trong những trang tính của Excel. Giải pháp này phối hợp linh động những công thức Excel và một số ít ứng dụng đơn thuần của advanced filter. Ngoài ra, giải pháp này còn phân phối 1 số ít tính năng rất độc lạ 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.
– Tìm, đánh dấu và sao chép giá trị duy nhất đến bất kỳ vị trí nào khác, trong cùng một bảng tính hoặc sang bảng tính khác.

Giờ tất cả chúng ta sẽ cùng xem hoạt động giải trí 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 tài liệu từ 1 số ít 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à trách nhiệm của bạn là trích xuất hàng duy nhất Open trong bảng chỉ một lần hoặc những hàng độc lạ gồm có những giá trị Open duy nhất một lần và một lần Open chung cho những giá trị trùng lặp. Sử dụng Duplicate Remover việc làm 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.

Thủ thuật Duplicate Remover sẽ chạy và chọn toàn bộ bảng. Vì vậy, chỉ cần nhấp vào Next để tiếp tục bước tiếp theo.

Chọn loại giá trị bạn muốn tìm và nhấp vào Next :
Phụ thuộc vào nhu yếu trích xuất tài liệu của bạn để chọn Unique ( duy nhất ) hoặc Unique + 1 st occurrences ( distinct ) ( Những giá trị duy nhất và một lần Open 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 hiển thị ở trên, có hai lựa chọn dành cho những giá trị Open 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 .

Xem ngay : thủ pháp Excel hay mọi thời đại

3. Chọn một hoặc nhiều cột để kiểm tra lại giá trị duy nhất.
Như trong ví dụ này, chúng ta cần tìm ra một hàng duy nhất dựa trên các giá trị trong cả ba cột (Order number, First name and Last name), vì thế chúng ta sẽ chọn tất cả.

4. Chọn ra tùy chọn cần áp dụng cho những giá trị duy nhất tìm được dựa trên các lựa chọn có sẵn sau đây:
– Highlight unique values – Đánh giá các giá trị duy nhất
– Select unique values – Chọn các giá trị duy nhất
– Identify in a status column – Xác định trong cột trạng thái
– Copy to another location – Sao chép sang vị trí khác
Vì chúng ta đang cần chiết xuất các hàng duy nhất, chọn Copy to another location và sau đó chỉ định vị trí chính xác cần sao chép chúng – vị trí đó phải là một bảng tính đang hoạt động (chọn tuỳ chọn Custom location và chỉ định ô trên cùng của dải đích), hoặc vị trí muốn sao chép đó phải là bảng tính mới.

Trong ví dụ này, chúng ta hãy chọn trang tính mới:

5. Chọn Finish, như vậy là đã thực hiện xong.

Hy vọng bài hướng dẫn này giúp bạn hoàn toàn có thể thuận tiện và nhanh gọn trích xuất được những giá trị duy nhất hoặc những 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ư :

• SUMIF, SUMIFS để tính tổng theo 1 điều kiện, nhiều điều kiện
• COUNTIF, COUNTIFS để thống kê, đếm theo một điều kiện, nhiều điều kiện
• Cá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ò tìm tham chiếu Index+Match, hàm SUMPRODUCT…

Một số công cụ hay sử dụng như :

• Định dạng theo điều kiện với Conditional formatting
• Thiết lập điều kiện nhập dữ liệu với Data Validation
• Cách đặt Name và sử dụng Name trong công thức
• Lập báo cáo với Pivot Table…

Rất nhiều kiến thức và kỹ năng phải không nào ? Toàn bộ những kiến thức và kỹ năng này những bạn đều hoàn toàn có thể học được trong khóa học EX101 – Excel từ cơ bản tới chuyên viên của Học Excel Online. Đây là khóa học giúp bạn mạng lưới hệ thống kiến thức và kỹ năng một cách khá đầy đủ, chi tiết cụ thể. Hơn nữa không hề có số lượng giới hạn về thời hạn học tập nên bạn hoàn toàn có thể tự do học bất kể khi nào, thuận tiện tra cứu lại kiến thức và kỹ năng khi cần. Hiện nay mạng lưới hệ thống đang có khuyến mại rất lớn cho bạn khi ĐK tham gia khóa học. Chi tiết xem tại : HocExcel. Online

Source: https://bem2.vn
Category: TỔNG HỢP