Các giá trị khác biệt và duy nhất trong Excel: cách tìm kiếm, lọc, lựa chọn và đánh dấu

Trong bài viết lần này, Học Excel Online sẽ chỉ ra cho các bạn cách hiệu quả nhất để tìm kiếm, chọn lọc và đánh dấu các giá trị duy nhất và khác biệt trong Excel.

Học Excel Online từng hướng dẫn các bạn các đếm các giá trị khác biệt và duy nhất trong Excel ở bài viết trước. Tuy nhiên, đôi khi bạn còn muốn được nhìn tận mắt những giá trị riêng biệt hoặc duy nhất trong một cột, không phải chỉ nhìn vào số lượng mà nhìn vào những giá trị thực tế. Trước khi tìm hiểu sâu hơn, chúng ta hãy cùng tìm hiểu xem giá trị khác biệt là gì và giá trị duy nhất là gì?

Giá trị duy nhất là các mục xuất hiện trong tập dữ liệu duy nhất một lần. 

Giá trị khác biệt là tất cả các mục khác nhau trong danh sách, nghĩa là giá trị duy nhất và lần xuất hiện thứ nhất của các giá trị nhân bản.

Bây giờ, chúng ta hãy cùng nghiên cứu các kỹ thuật hiệu quả nhất áp dụng cho các giá trị khác biệt và duy nhất trong bảng Excel.

Tìm các giá trị duy nhất trong một cột

Để tìm các giá trị khác biệt và duy nhất trong một danh sách, bạn sử dụng một trong các công thức sau đây, trong đó A2 là ô đầu tiên và A10 là ô cuối cùng có dữ liệu.

Cách tìm các giá trị duy nhất trong Excel:

=IF(COUNTIF($A$2:$A$10, $A2)=1, “Unique”, “”)

Cách tìm các giá trị khác biệt trong Excel:

=IF(COUNTIF($A$2:$A2, $A2)=1, “Distinct”, “”)

Trong công thức tìm giá trị khác biệt chỉ có một độ lệch nhỏ trong tham chiếu ô thứ hai nhưng lại tạo ra một sự khác biệt lớn:

Tìm hàng duy nhất / khác biệt trong Excel

Theo cách tương tự, bạn có thể tìm các hàng duy nhất trong bảng Excel dựa trên các giá trị trong 2 cột trở lên. Trong trường hợp này, bạn cần phải sử dụng hàm COUNTIFS thay vì COUNTIF để đánh giá các giá trị trong một vài cột (có thể đánh giá được tới 127 phạm vi/ cặp tiêu chuẩn trong một công thức đơn).

Ví dụ: để tìm tên duy nhất hoặc khác biệt trong danh sách, hãy sử dụng các công thức sau:

Công thức để tìm các hàng duy nhất:

=IF(COUNTIFS($A$2:$A$10, $A2, $B$2:$B$10, $B2)=1, “Unique row”, “”)

Công thức để tìm các hàng khác biệt:

=IF(COUNTIFS($A$2:$A2, $A2, $B$2:$B2, $B2)=1, “Distinct row”, “”)

Tìm các giá trị duy nhất / khác biệt trong Excel phân biệt chữ hoa và chữ thường

Nếu bạn đang làm việc với một bộ dữ liệu trong trường hợp có vấn đề, bạn cần một công thức mảng phức tạp hơn một chút.

Tìm các giá trị duy nhất phân biệt chữ hoa và chữ thường:

=IF(SUM((–EXACT($A$2:$A$10,A2)))=1,”Unique”,””)

Tìm các giá trị riêng biệt phân biệt chữ hoa và chữ thường:

=IF(SUM((–EXACT($A$2:$A2,$A2)))=1,”Distinct”,””)

Vì cả hai đều là công thức mảng, nên bạn cần nhấn Ctrl + Shift + Enter để hoàn tất chúng một cách chính xác.

Khi tìm thấy các giá trị duy nhất hoặc khác biệt, bạn có thể dễ dàng lọc, chọn và sao chép chúng như được trình bày dưới đây.

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

Để xem riêng các giá trị duy nhất hoặc khác biệt trong danh sách, chúng ta cần lọc chúng ra bằng cách thực hiện các bước sau.

– Áp dụng một trong các công thức trên để xác định các giá trị hoặc hàng duy nhất / khác biệt.
– Chọn dữ liệu của bạn và nhấp vào nút Filter trên tab Data. Hoặc nhấp vào Sort & Filter > Filter trên tab Trang chủ trong nhóm Editing.
– Nhấp vào mũi tên Filter trong tiêu đề của cột chứa công thức của bạn và chọn các giá trị bạn muốn xem:

Cách chọn các giá trị khác biệt / độc nhất

Nếu bạn có một danh sách các giá trị duy nhất / khác biệt tương đối nhỏ, bạn chỉ cần chọn nó theo cách thông thường bằng cách dùng chuột. Nếu danh sách được lọc có hàng trăm hoặc hàng nghìn hàng, bạn có thể sử dụng một trong số các phím tắt để tiết kiệm thời gian.

Để nhanh chóng, bạn chọn danh sách duy nhất hoặc khác biệt bao gồm tiêu đề cột, lọc giá trị duy nhất, nhấp vào bất kỳ ô nào trong danh sách duy nhất, sau đó nhấn Ctrl + A.

Để chọn các giá trị khác biệt hoặc duy nhất mà không có tiêu đề cột, hãy lọc giá trị duy nhất, chọn ô đầu tiên với dữ liệu và nhấn Ctrl + Shift + End để mở rộng vùng chọn vào ô cuối cùng.

Mẹo. Trong một số trường hợp hiếm hoi, chủ yếu là các trường hợp làm việc với dữ liệu quá lớn, các phím tắt trên có thể sẽ chọn cả những ô hiển thị và không hiển thị. Để khắc phục điều này, nhấn Ctrl + A hoặc Ctrl + Shift + End đầu tiên, và sau đó nhấn Alt +; Để chỉ chọn các ô hiển thị, bỏ qua hàng ẩn.

Nếu bạn gặp khó khăn khi nhớ nhiều phím tắt, hãy sử dụng cách làm trực quan này: chọn toàn bộ danh sách duy nhất / khác biệt, sau đó đi tới tab Trang chủ> Find & Select > đi đến phần Special và chọn Visible cells only.

Sao chép các giá trị duy nhất hoặc khác biệt đến vị trí khác

Để sao chép danh sách các giá trị duy nhất sang vị trí khác, chỉ cần thực hiện theo các bước sau:

– Chọn các giá trị được lọc bằng chuột hoặc các phím tắt đã đề cập ở trên.
– Nhấn Ctrl + C để sao chép các giá trị đã chọn.
– Chọn ô trên cùng bên trái trong dải đích (nó có thể nằm trên cùng một tờ hoặc khác tờ), và nhấn Ctrl + V để dán giá trị.

Cách làm nổi bật các giá trị duy nhất và khác biệt trong Excel

Bất cứ khi nào bạn cần làm nổi bật bất cứ điều gì trong Excel dựa trên một điều kiện nhất định, thì cần áp dụng tính năng định dạng có điều kiện.

Đánh dấu các giá trị duy nhất trong một cột (quy tắc built-in)

Cách nhanh nhất và dễ dàng nhất để làm nổi bật các giá trị duy nhất trong Excel là áp dụng quy tắc định dạng có điều kiện cho sẵn:

1. Chọn cột dữ liệu mà bạn muốn đánh dấu các giá trị duy nhất.
2. Trên tab Trang chủ, trong nhóm Styles, chọn Conditional Formatting > Highlight Cells Rules >Duplicate Values…

3. Trong cửa sổ hộp thoại Duplicate Values, chọn Unique trong hộp bên tay trái và chọn định dạng mong muốn trong hộp bên phải, sau đó nhấn OK.

Mẹo. Nếu bạn không hài lòng với bất kỳ định dạng nào được thiết lập trước, hãy nhấp vào Custom Format…(mục cuối cùng trong danh sách thả xuống) và đặt màu và/ hoặc phông chữ theo ý thích của bạn.

Như bạn thấy, làm nổi bật các giá trị duy nhất trong Excel là một trong những nhiệm vụ vô cùng đơn giản. Tuy nhiên, quy tắc được xây dựng trong Excel chỉ hoạt động cho các mục xuất hiện trong danh sách duy nhất một lần. Nếu bạn cần làm nổi bật các giá trị khác biệt – gồm 1 lần xuất hiện đại diện cho những giá trị trùng lặp – bạn sẽ phải tạo quy tắc của riêng mình dựa trên công thức. Bạn cũng cần phải tạo một quy tắc tùy chỉnh để đánh dấu các hàng duy nhất dựa trên các giá trị trong một hoặc nhiều cột.

Đánh dấu các giá trị duy nhất và khác biệt trong Excel (quy tắc tùy chỉnh)

Để làm nổi bật các giá trị duy nhất hoặc khác biệt trong một cột, hãy chọn dữ liệu nhưng không bao gồm tiêu đề cột (Hẳn là bạn cũng không muốn làm nổi bật tiêu đề cột) Và tạo ra một quy tắc định dạng có điều kiện với một trong các công thức sau.

Đánh dấu các giá trị duy nhất

Để làm nổi bật các giá trị xuất hiện trong một danh sách duy nhất một lần, sử dụng công thức sau:

=COUNTIF($A$2:$A$10,$A2)=1

Với A2 là ô đầu tiên và A10 là ô cuối cùng của phạm vi áp dụng.

Đánh dấu các giá trị khác biệt
Để làm nổi bật tất cả các giá trị khác nhau trong một cột, tức là giá trị duy nhất và 1 lần xuất hiện đại diện cho những giá trị trùng lặp, hãy làm theo công thức sau:

=COUNTIF($A$2:$A2,$A2)=1

Với A2 là ô trên cùng của dải.

Cách tạo quy tắc dựa trên công thức

Để tạo một quy tắc định dạng có điều kiện dựa trên công thức, hãy thực hiện theo các bước sau:

– Chuyển đến tab Trang chủ> Styles, chọn Conditional Formatting > New rule > Use a formula to determine which cells to format.
– Nhập công thức của bạn vào phần Format values where this formula is true.
– Nhấp vào nút Format… và chọn màu hoặc/ và phông chữ mà bạn muốn.
– Cuối cùng, nhấp vào nút OK để áp dụng quy tắc.

Ảnh chụp màn hình bên dưới sẽ cho bạn thấy cách hoạt động của quy tắc trên:

Đánh dấu toàn bộ các hàng dựa trên các giá trị duy nhất / khác biệt trong một cột

Để đánh dấu toàn bộ các hàng dựa trên các giá trị duy nhất trong một cột cụ thể, hãy sử dụng các công thức cho các giá trị duy nhất và khác biệt mà chúng ta đã sử dụng trong ví dụ trước, nhưng áp dụng quy tắc của bạn cho toàn bộ bảng chứ không phải là một cột đơn.

Ảnh chụp màn hình sau minh họa quy tắc làm nổi bật các hàng dựa trên số liệu riêng biệt trong cột A:

Cách làm nổi bật các hàng duy nhất trong Excel

Nếu bạn muốn làm nổi bật các hàng dựa trên các giá trị trong 2 hoặc nhiều cột, hãy sử dụng chức năng COUNTIFS cho phép chỉ định một số tiêu chí trong một công thức.

Làm nổi bật các hàng duy nhất
=COUNTIFS($A$2:$A$10,$A2, $B$2:$B$10,$B2)=1

Làm nổi bật các hàng khác biệt (các hàng duy nhất cùng với 1 lần xuất hiện đại diện cho những hàng trùng lặp)
=COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2)=1

Cách nhanh và dễ dàng để tìm và đánh dấu các giá trị duy nhất trong Excel

Như bạn đã thấy, Microsoft Excel cung cấp khá nhiều tính năng hữu ích có thể giúp bạn xác định và đánh dấu các giá trị duy nhất trong bảng tính của bạn. Tuy nhiên, tất cả những giải pháp đó khó có thể gọi là trực quan và dễ sử dụng vì chúng đòi hỏi phải ghi nhớ một số công thức khác nhau. Tất nhiên, Với các chuyên gia Excel thì việc ghi nhớ chẳng có gì khó khăn cả. Nhưng đối với những người dùng Excel thì có một cách nhanh chóng và đơn giản để tìm các giá trị duy nhất trong Excel.

Trong phần cuối cùng của hướng dẫn hôm nay, Học Excel Online sẽ sử dụng tiện ích Duplicate Remover cho Excel. Xin đừng nhầm lẫn bởi tên của công cụ này. Ngoài các hồ sơ trùng lặp, add-in có thể hoàn toàn xử lý các mục duy nhất và khác biệ một cách nhanh chóng.
1. Chọn bất kỳ ô nào trong bảng mà bạn muốn tìm các giá trị duy nhất và nhấn vào nút Duplicate Remover trên tab Ablebits Data trong nhóm Dedupe.

Trình hướng dẫn sẽ chạy và toàn bộ bảng sẽ được chọn tự động. Vì vậy, chỉ cần nhấp vào Next để tiếp tục bước tiếp theo.

Mẹo. Khi sử dụng add-in lần đầu tiên, bạn nên kiểm tra hộp Create a backup copy.
2. Tùy thuộc vào mục tiêu của bạn, hãy chọn một trong các tùy chọn sau, sau đó bấm Next:
Unique (Duy nhất)
Unique +1st occurrences (distinct) (Duy nhất và khác biệt)

3. Chọn một hoặc nhiều cột mà bạn muốn kiểm tra các giá trị.
Trong ví dụ này, chúng ta cần tìm các tên duy nhất dựa trên các giá trị trong 2 cột (First name and Last name), do đó ta sẽ chọn cả hai.

Mẹo. Nếu bảng của bạn có tiêu đề, hãy chọn My table has headers. Và nếu bảng của bạn có thể có ô trống, phải kiểm tra lại phần Skip empty cells. Cả hai tùy chọn đều nằm ở phần trên của cửa sổ hộp thoại và thường được chọn theo mặc định.

Chọn một trong các thao tác sau để thực hiện trên các giá trị đã tìm thấy:

Highlight unique values with color – Đánh dấu các giá trị duy nhất dùng màu sắc

Select unique values – Chọn 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 đến vị trí khác

Nhấn Finish và xem kết quả:

Nếu việc tìm kiếm các giá trị trùng lặp và duy nhất trong Excel là một phần phổ biến trong công việc hàng ngày của bạn thì hy vọng rằng bài viết này có nhiều phần bổ ích, giúp công việc của bạn thuận lợi hơn. Cảm ơn các bạn đã theo dõi bài viết này.