Cách tìm kiếm và lọc dữ liệu không trùng nhau trong Excel

Trong bài viết ngày hôm nay, Gitiho sẽ cùng các bạn tìm hiểu phương pháp tìm kiếm và lọc dữ liệu không trùng nhau trong Excel. Với phương pháp này, các bạn có thể dễ dàng đánh dấu các giá trị duy nhất và các giá trị khác biệt tồn tại trong bảng tính của mình.

Xem thêm: Thành thạo mọi kỹ năng tìm kiếm và lọc dữ liệu nhờ khóa học Excel này

Cách tìm các giá trị duy nhất/khác biệt

Trong phần đầu tiên của bài viết, chúng ta sẽ tìm hiểu phương pháp tìm các giá trị duy nhất và các giá trị khác biệt trong một cột Excel. Vậy trước hết, các khái niệm này nghĩa là gì?

• Giá trị duy nhất: Là các giá trị chỉ xuất hiện duy nhất một lần trong trang tính.
• Giá trị khác biệt: Là các giá trị khác nhau xuất hiện trong trang tính, có thể xuất hiện nhiều lần nhưng giá trị khác biệt chỉ tính cho lần xuất hiện đầu tiên.

Bây giờ chúng ta đã nắm được khái niệm 2 thuật ngữ này rồi, hãy khám phá nội dung bài học thôi!

Cách tìm các giá trị duy nhất/khác biệt

Giả sử chúng ta có một bảng dữ liệu như trong hình dưới đây với yêu cầu lọc giá trị không trùng nhau trong Excel, hay nói cách khác là xác định các giá trị duy nhất và các giá trị đặc biệt:

Để xác định các tên xuất hiện 1 lần duy nhất trong cột A, chúng ta sẽ kết hợp hàm IF và hàm COUNTIF để tạo hàm đếm dữ liệu không trùng trong Excel như sau:

=IF(COUNTIF($A$2:$A$10, $A2)=1, “Duy nhất”, “”)

Công thức này hoạt động như sau: Nếu hàm COUNTIF đếm số lần xuất hiện trong cột A của giá trị bằng 1 thì hàm IF trả về kết quả "Duy nhất", tức giá trị này là giá trị duy nhất. Ngược lại, nếu giá trị xuất hiện đến lần thứ 2 trở lên, hàm IF trả vê giá trị rỗng.

 

Đối với các giá trị khác biệt, chúng ta vẫn sử dụng công thức hàm đếm dữ liệu không trùng trong Excel bằng cách kết hợp hàm COUNTIF và hàm IF.

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

Các bạn có thể thấy công thức này có sự khác biệt nho nhỏ tại địa chỉ ô trong tham số hàm COUNTIF. Chính sự khác biệt nho nhỏ này lại tạo ra một thay đổi to lớn đối với cách hoạt động của hàm. Nếu như ở công thức hàm xác định các giá trị duy nhất, chúng ta sử dụng tham chiếu tuyệt đối, thì sang đến hàm xác định các giá trị khác biệt, chúng ta sử dụng tham chiếu tương đối. Thao tác cho phép Excel linh hoạt mở rộng dải đối chiếu từ ô đầu tiên (ô cố định $A$2) đến ô chứa giá trị cần xét tại cột A.

Như vậy, chúng ta đã hoàn tất quá trình lọc dữ liệu không trùng nhau trong Excel với kết quả là các giá trị duy nhất và giá trị khác biệt như trên.

Xem thêm: Hướng dẫn hai cách lấy địa chỉ ô Excel đơn giản nhất

Cách tìm các hàng duy nhất/khác biệt

Nếu phạm vi tham chiếu của bạn được mở rộng ra nhiều hơn một cột, bạn vẫn có thể phát triển công thức hàm đếm dữ liệu không trùng trong Excel dựa trên các công thức trong phần trên. Tuy nhiên, thay vì hàm COUNTIF, bạn sẽ cần đến hàm COUNTIFS để đánh giá đồng thời các giá trị tại 2 cột.

Để xác định các hàng duy nhất trong bảng, chúng ta sử dụng công thức lọc dữ liệu trong Excel như sau:

=IF(COUNTIFS($A$2:$A$10,$A2,$B$2:$B$10,$B2)=1,"Duy nhất","")

Tương tự đối với yêu cầu tìm các hàng khác biệt trong bảng, chúng ta có công thức hàm đếm dữ liệu không trùng trong Excel:

=IF(COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2)=1,"Khác biệt","")

Cách tìm các giá trị duy nhất/khác biệt với điều kiện phân biệt chữ hoa chữ thường

Các hàm chúng ta sử dụng trong các công thức trên đều có chung đặc điểm không phân biệt chữ hoa và chữ thường. Do đó, trong trường hợp bạn cần lọc dữ liệu không trùng nhau trong Excel xét đến cả điều kiện phân biệt chữ hoa và chữ thường, bạn sẽ cần đến một công thức mảng.

Hàm được sử dụng chính là hàm SUM và hàm EXACT. Công thức kết hợp xác định các giá trị duy nhất của chúng ta như sau:

=IF(SUM((--EXACT($A$2:$A$10,A2)))=1,"Duy nhất","")

Công thức tìm các giá trị riêng biệt trong bảng như sau:

=IF(SUM((--EXACT($A$2:$A2,$A2)))=1,"Khác biệt","")

Lưu ý: Vì các công thức kết hợp hàm SUM và hàm EXACT là công thức mảng, bạn phải nhấn Ctrl+Shift+Enter sau khi kết thúc nhập liệu công thức thay vì nhấn Enter thông thường.

Xem thêm: Cách dùng hàm VLOOKUP lọc trùng dữ liệu trong Excel nhanh chóng

Cách lọc các giá trị duy nhất/khác biệt

Cách lọc các giá trị duy nhất/khác biệt

Sau khi đã xác định được các giá trị duy nhất và các giá trị khác biệt trong bảng dữ liệu của mình, chúng ta sẽ xét riêng các giá trị này bằng cách áp dụng bộ lọc dữ liệu không trùng nhau trong Excel. Bạn hãy tiến đến thẻ Data > nhóm Sort & Filter > Filter để Excel hiện bộ lọc cho bảng dữ liệu.

Bạn sẽ thấy các dấu mũi tên xuất hiện trên các ô đầu tiên của mỗi cột. Tại đây, bạn có thể mở hộp thoại lọc dữ liệu trong Excel để hiển thị các giá trị muốn xem. Giả sử bạn muốn xét riêng các giá trị khác biệt trong bảng, bạn tích vào ô "Khác biệt" và nhấn OK.

Lúc này, bạn sẽ thấy trên bảng dữ liệu Excel chỉ có những giá trị được xác định là giá trị khác biệt. Toàn bộ các giá trị còn lại đã bị ẩn đi. 

Cách chọn các giá trị duy nhất/khác biệt từ bộ lọc

Thao tác này tưởng chừng có vẻ đơn giản sau khi bạn đã áp dụng bộ lọc dữ liệu trong Excel, nhưng với các bảng dữ liệu khổng lồ với số hàng lên đến 3 chữ số, có thể bạn sẽ gặp chút ít khó khăn.

Để chọn toàn bộ dữ liệu sau khi lọc, bạn hãy sử dụng phím tắt Ctrl+A. Trong trường hợp bạn cần chọn các giá trị sau khi lọc dữ liệu không trùng nhau trong Excel mà không có tiêu đề cột, hãy chọn ô đầu tiên chứa dữ liệu và nhấn tổ hợp phím Ctrl+Shift+End để mở rộng phạm vi chọn đến ô cuối cùng chứa dữ liệu.

Nếu bạn đang gặp tình trạng lỗi sau khi lọc mà nhấn Ctrl+A chọn tất cả dữ liệu bao gồm những giá trị đã bị ẩn, đây là cách khắc phục: Nhấn tổ hợp Ctrl+A hoặc Ctrl+Shift+End trước tiên, sau đó nhấn tổ hợp Alt+; để Excel hiểu bạn chỉ chọn các giá trị đang hiển thị.

Một giải pháp khác cho rắc rối trên nếu bạn chưa thể nhớ được hết các thao tác với phím tắt trong Excel là sử dụng công cụ Find & Select. Hãy chọn toàn bộ danh sách dữ liệu đã lọc, vào thẻ Home > nhóm Editing > Find & Select > Go To Special > tích chọn Visible cells only.

Cách sao chép và dán các giá trị duy nhất/khác biệt từ bộ lọc

Nếu bạn cần sao chép danh sách các giá trị duy nhất/khác biệt từ bộ lọc dữ liệu trong Excel, bạn chỉ cần thực hiện các thao tác: Chọn dữ liệu, nhấn tổ hợp Ctrl+C, chọn một vị trí để dán dữ liệu rồi nhấn tổ hợp Ctrl+V. Thật đơn giản phải không nào?

Xem thêm: Tổng hợp các phím tắt Excel thần thánh

Cách đánh dấu các giá trị duy nhất/khác biệt

Cách đánh dấu các giá trị duy nhất với Conditional Formatting

Phương pháp nhanh gọn nhất để đánh dấu các giá trị duy nhất/khác biệt chính là áp dụng công cụ Conditional Formatting trong Excel. Trước hết, bạn hãy chọn toàn bộ bảng dữ liệu của mình, sau đó vào thẻ Home > nhóm Styles > Conditional Formatting > Highlight Cells Rules > Duplicate Values.

Hộp thoại Duplicate Values hiện lên. Nếu bạn cần làm nổi bật các giá trị duy nhất trong danh sách, hãy chọn "Unique" và một tùy chọn đánh dấu theo ý thích. Ở đây, chúng mình chọn "Green Fill with Dark Green Text" (ô màu xanh lá và văn bản màu xanh đậm). Như vậy, bạn sẽ thấy bảng dữ liệu đã tự động lọc dữ liệu không trùng nhau trong Excel và tô màu cho chúng.

Nếu bạn muốn đánh dấu các giá trị duy nhất theo quy tắc riêng của mình, hãy chọn Custom Format ở cuối dải tùy chọn trong hợp thoại Duplicate Values và thiết lập màu ô, màu chữ theo mong muốn nhé.

Xem thêm: Hướng dẫn tạo biểu đồ Gantt trong Excel bằng Conditional Formatting

Cách đánh dấu các giá trị khác biệt với Conditional Formatting

Thao tác đánh dấu các giá trị duy nhất trong bảng dữ liệu không có gì khó khăn với Conditional Formatting vì công cụ này đã được cài đặt sẵn tính năng lọc dữ liệu trong Excel để xác định các giá trị xuất hiện một lần duy nhất. Tuy nhiên, với các giá trị khác biệt xuất hiện nhiều hơn một lần thì cách đánh dấu bằng Conditional Formatting sẽ trở nên phức tạp hơn một chút.

Chúng ta cần tự tay tạo một quy tắc định dạng để hệ thống lọc dữ liệu không trùng nhau trong Excel và chạy Conditional Formatting. Bạn hãy chọn Conditional Formatting > New Rule > Use a formula to determine which cells to format (Sử dụng một công thức để xác định ô cần định dạng).

Tại hộp thoại New Formatting Rule, chúng ta sẽ sử dụng công thức hàm đếm dữ liệu không trùng trong Excel tương tự như trong phần đầu của bài viết:

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

Sau đó, bạn hãy nhấn nút Format và tự mình thiết lập định dạng sẽ đánh dấu lên các giá trị khác biệt trong ô. Khi đã hài lòng với thiết lập của mình, nhấn OK để đóng hộp thoại.

Với thiết lập định dạng Conditional Formatting trong Excel như trên, chúng ta sẽ nhận được kết quả dưới đây.

Cách đánh dấu các hàng duy nhất/khác biệt với Conditional Formatting

Tương tự với cách đánh dấu các giá trị khác biệt, chúng ta sẽ phải tạo một quy luật định dạng mới với Conditional Formatting trong Excel để có thể đánh dấu các hàng duy nhất/khác biệt. Bạn hãy mở hộp thoại New Formatting Rule > Use a formula to determine which cells to format.

Công thức được sử dụng để xác định các hàng duy nhất như sau:

=COUNTIFS($A$2:$A$10,$A2,$B$2:$B$10,$B2)=1

Nếu bạn cần làm nổi bật các hàng khác biệt, hãy sử dụng công thức hàm đếm dữ liệu không trùng trong Excel như sau:

=COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2)=1

Vậy là chúng ta đã sử dụng các công thức đã học ở các phần trên để tạo một điều kiện định dạng mới trong Conditional Formatting. Nếu bạn còn chưa chắc chắn về công thức hàm lọc dữ liệu không trùng nhau trong Excel thì hãy cuộn lại lên phần đầu bài viết để đọc lại nhé.

Xem thêm: Hướng dẫn sử dụng hàm so sánh trong Excel kiểm tra sai lệch dữ liệu

Tổng kết

Như vậy, chúng ta đã tìm hiểu thủ thuật lọc dữ liệu không trùng nhau trong Excel và đánh dấu các giá trị duy nhất/khác biệt với công cụ Conditional Formatting. Gitiho hy vọng các bạn có thể dễ dàng ứng dụng kiến thức tin học văn phòng trong bài viết vào công việc của mình. Hãy cho chúng mình biết trong phần bình luận nhé! Đừng quên truy cập blog Gitiho để học thêm thật nhiều thủ thuật Excel hữu ích khác nhé.

Gitiho cảm ơn bạn đọc và chúc bạn thành công!