Làm chủ Excel: 8 cách đơn giản lọc giá trị duy nhất (Unique Values)

Lọc duy nhất (Filter Unique) là cách hiệu quả xử lý nhiều mục trong tập dữ liệu. Excel cung cấp nhiều tính năng lọc dữ liệu duy nhất hoặc xóa trùng lặp, bất kể gọi là gì. Bài viết sẽ trình bày các cách lọc dữ liệu duy nhất từ tập dữ liệu mẫu.

Giả sử có 3 cột đơn giản trong tập dữ liệu Excel chứa Ngày đặt hàng (Order Date), Danh mục (Category), và Sản phẩm (Product). Muốn sản phẩm đặt hàng duy nhất trong toàn bộ tập dữ liệu.

Tải file Excel thực hành

8 cách dễ dàng lọc giá trị duy nhất trong Excel

Phương pháp 1: Dùng tính năng Remove Duplicates (Xóa trùng lặp) của Excel lọc giá trị duy nhất

Để hiểu các mục trong tập dữ liệu lớn, đôi khi cần xóa trùng lặp. Excel cung cấp Remove Duplicates trong tab Data (Dữ liệu) để bỏ qua mục trùng lặp. Trong trường hợp này, muốn xóa trùng lặp từ cột Category và Product. Do đó, dùng Remove Duplicates.

Bước 1: Chọn phạm vi (ví dụ: Category và Product) rồi Vào tab Data > Chọn Remove Duplicates (từ phần Data Tools).

Bước 2: Cửa sổ Remove Duplicates hiện ra. Trong cửa sổ:

• Tích tất cả cột.
• Tích tùy chọn My data has headers (Dữ liệu có tiêu đề).
• Nhấn OK.

Bước 3: Hộp thoại xác nhận hiện ra: 8 giá trị trùng lặp tìm thấy và xóa; 7 giá trị duy nhất còn lại.

Nhấn OK.

Tất cả bước dẫn đến kết quả như ảnh:

Phương pháp 2: Dùng Conditional Formatting (Định dạng có điều kiện) lọc giá trị duy nhất

Cách khác lọc duy nhất là Conditional Formatting. Excel Conditional Formatting định dạng ô với nhiều tiêu chí. Ở đây, dùng công thức định dạng có điều kiện cho phạm vi (ví dụ: cột Product). Có 2 tùy chọn: định dạng có điều kiện lọc giá trị duy nhất và ẩn giá trị trùng lặp.

2.1. Conditional Formatting lọc giá trị duy nhất

Trường hợp này dùng công thức trong Conditional Formatting để Excel lọc mục duy nhất.

Bước 1: Chọn phạm vi (ví dụ: Product 1) rồi Vào tab Home > Chọn Conditional Formatting (từ phần Styles) > Chọn New Rule (Quy tắc mới).

Bước 2: Cửa sổ New Formatting Rule hiện ra. Trong cửa sổ:

• Chọn Use a formula to determine which cells to format (Dùng công thức xác định ô định dạng) dưới tùy chọn Select a Rule Type.
• Gõ công thức sau dưới tùy chọn Edit the Rule Description:

Trong công thức, hướng dẫn Excel đếm mỗi ô cột D là Duy nhất (bằng 1). Nếu khớp điều kiện, trả về TRUE và Định dạng màu (Color Format) ô.

Nhấp Format (Định dạng).

Bước 3: Ngay lập tức cửa sổ Format Cells hiện ra. Trong cửa sổ:

• Phần Font - Chọn màu định dạng bất kỳ như ảnh.
• Nhấn OK.

Bước 4: Nhấn OK ở bước trước quay lại cửa sổ New Formatting Rule. Bạn thấy xem trước các mục duy nhất.

Nhấn OK.

Cuối cùng, nhận các mục duy nhất được định dạng màu như ý muốn giống ảnh:

2.2. Conditional Formatting ẩn bản sao (Hide Duplicates)

Không can thiệp vào giá trị duy nhất, có thể đơn giản ẩn giá trị trùng lặp dùng Conditional Formatting. Để ẩn trùng lặp, áp dụng cùng công thức như lọc duy nhất nhưng gán giá trị lớn hơn 1. Sau khi chọn màu font Trắng (White), chúng ta ẩn chúng khỏi các mục còn lại.

Bước 1: Lặp lại Các bước 1 đến 2 của phương pháp 2.1 nhưng thay công thức chèn bằng công thức dưới:

Công thức hướng dẫn Excel đếm mỗi ô cột D là Trùng lặp (lớn hơn 1). Nếu khớp điều kiện, trả về TRUE và Định dạng màu (Color Format) (tức Ẩn) ô.

Nhấp Format.

Bước 2: Nhấp Format đưa đến cửa sổ Format Cells. Trong cửa sổ:

• Chọn màu Font Trắng (White).
• Nhấn OK.

Bước 3: Sau chọn màu Font, nhấp OK đưa về cửa sổ New Formatting Rule. Xem trước bị mờ vì chọn Trắng làm màu Font.

Nhấn OK.

Làm theo tất cả bước dẫn đến hình ảnh tương tự cho giá trị trùng lặp:

Bạn bắt buộc chọn Trắng làm màu Font nếu không giá trị trùng lặp sẽ không ẩn.

Đọc thêm: Cách lọc dữ liệu trong Excel dùng công thức

Phương pháp 3: Dùng tính năng Advanced Filter (Bộ lọc nâng cao) của tab Data lọc giá trị duy nhất

Các phương pháp trước xóa/mất mục từ tập dữ liệu để lọc duy nhất. Điều này nguy hiểm khi làm việc với tập dữ liệu nhất định. Có trường hợp không thể thay đổi dữ liệu gốc, lúc đó dùng tùy chọn Advanced Filter để lọc duy nhất tại vị trí mong muốn.

Bước 1: Chọn phạm vi (ví dụ: cột Product). Vào tab Data > Chọn Advanced (Nâng cao) (từ phần Sort & Filter).

Bước 2: Cửa sổ Advanced Filter hiện ra. Trong cửa sổ:

• Chọn hành động Copy to another location (Sao chép đến vị trí khác) dưới tùy chọn Action. Có thể chọn Filter the list, in-place (Lọc tại chỗ) hoặc Copy to another location, tuy nhiên chọn cái sau để không thay đổi dữ liệu gốc.
• Gán vị trí (ví dụ: F4) ở tùy chọn Copy to.
• Tích Unique records only (Chỉ bản ghi duy nhất).
• Nhấn OK.

Nhấn OK nhận giá trị duy nhất tại vị trí đích như hướng dẫn:

Phương pháp 4: Lọc giá trị duy nhất dùng hàm UNIQUE của Excel

Hiển thị giá trị duy nhất ở cột khác cũng đạt được bằng hàm UNIQUE. Hàm UNIQUE lấy danh sách mục duy nhất từ phạm vi hoặc mảng. Cú pháp:

UNIQUE (array, [by_col], [exactly_once])

Các đối số:

• array; phạm vi hoặc mảng trích xuất giá trị duy nhất.
• [by_col]; cách so sánh và trích xuất, theo hàng = FALSE (mặc định) và theo cột = TRUE. [tùy chọn]
• [exactly_once]; giá trị xuất hiện một lần = TRUE và giá trị duy nhất hiện có = FALSE (mặc định). [tùy chọn]

Bước 1: Gõ công thức sau vào ô trống (ví dụ: E5).

Bước 2: Nhấn ENTER rồi giây lát tất cả mục duy nhất bật lên cột như ảnh:

Hàm UNIQUE tràn (spill) tất cả mục duy nhất cùng lúc. Tuy nhiên, không dùng UNIQUE ngoài phiên bản Excel 365.

Bài đọc tương tự

• Lọc dữ liệu Excel dựa trên giá trị ô (6 cách hiệu quả)
• Cách thêm Filter trong Excel (4 phương pháp)
• Phím tắt cho Excel Filter (3 dùng nhanh với ví dụ)
• Cách dùng Text Filter trong Excel (5 ví dụ)

Phương pháp 5: Dùng hàm UNIQUE và FILTER (có tiêu chí)

Ở phương pháp 4, dùng UNIQUE tràn giá trị duy nhất. Nhưng nếu muốn mục duy nhất tùy thuộc điều kiện? Giả sử muốn tên Product duy nhất của một Category nhất định từ tập dữ liệu.

Trong trường hợp này, muốn tên Product duy nhất của danh mục Bars (ví dụ: ô E4).

Bước 1: Viết công thức dưới vào ô bất kỳ (ví dụ: E5).

Công thức hướng dẫn lọc phạm vi D5:D19, áp đặt điều kiện trên phạm vi C5:C19 bằng ô E4.

Bước 2: Nhấn ENTER. Sản phẩm dưới danh mục Bars hiện ra ở cột Bars như ảnh:

Bạn có thể chọn bất kỳ Category để lọc sản phẩm duy nhất. Cách khá hiệu quả xử lý tập dữ liệu bán hàng lớn. Hàm FILTER chỉ có trong Excel 365.

Đọc thêm: Lọc nhiều tiêu chí trong Excel

Phương pháp 6: Dùng hàm MATCH và INDEX (Công thức mảng)

Để minh họa đơn giản, dùng tập dữ liệu không có ô trống hoặc phân biệt chữ hoa/thường. Làm sao xử lý tập dữ liệu có ô trống và phân biệt chữ hoa/thường? Trước khi demo cách giải quyết, lọc phạm vi không trống (ví dụ: Product 1) dùng công thức kết hợp. Ở đây dùng MATCH và INDEX lọc duy nhất.

6.1. MATCH và INDEX lọc giá trị duy nhất từ phạm vi không trống

Thấy không có ô trống trong phạm vi Product 1.

Bước 1: Gõ công thức sau vào ô G5 lọc duy nhất:

Theo công thức:

• Trước, COUNTIF($G$4:G4, $D$5:$D$19); đếm số ô trong phạm vi (ví dụ: $G$4:G4) tuân theo điều kiện (ví dụ: $D$5:$D$19). COUNTIF trả về 1 nếu tìm thấy $G$4:G4 trong phạm vi, ngược lại 0.
• Thứ hai, MATCH(0, COUNTIF($G$4:G4, $D$5:$D$19), 0)); trả về vị trí tương đối của sản phẩm trong phạm vi.
• Cuối, INDEX($D$5:$D$19, MATCH(0, COUNTIF($G$4:G4, $D$5:$D$19), 0)); trả về nội dung ô đáp ứng điều kiện.

Hàm IFERROR hạn chế công thức hiển thị lỗi trong kết quả.

Bước 2: Vì công thức mảng, nhấn CTRL+SHIFT+ENTER cùng lúc. Tất cả mục duy nhất từ phạm vi Product 1 xuất hiện.

6.2. MATCH và INDEX lọc giá trị duy nhất từ ô trống hiện có trong phạm vi

Giờ, trong phạm vi Product 2, thấy nhiều ô trống. Để lọc duy nhất giữa ô trống, chèn hàm ISBLANK.

Bước 1: Dán công thức dưới vào ô H5.

Công thức hoạt động giống như phần 6.1. Tuy nhiên, hàm IF thêm với kiểm tra logic ISBLANK cho phép công thức bỏ qua ô trống.

Bước 2: Nhấn CTRL+SHIFT+ENTER và công thức bỏ qua ô trống, lấy tất cả mục duy nhất như ảnh:

6.3. MATCH và INDEX lọc giá trị duy nhất từ phạm vi phân biệt chữ hoa/thường (Case-Sensitive)

Nếu tập dữ liệu có mục phân biệt chữ hoa/thường, dùng hàm FREQUENCY cùng TRANSPOSE và ROW để lọc duy nhất.

Bước 1: Áp dụng công thức dưới vào ô I5.

Các phần công thức:

• TRANSPOSE($I$4:I4); chuyển vị giá trị trước bằng cách chuyển dấu chấm phẩy thành phẩy. (ví dụ: TRANSPOSE({“giá trị duy nhất (phân biệt chữ)”;“Whole Wheat”}) thành {“giá trị duy nhất (phân biệt chữ)”,“Whole Wheat”})
• EXACT($F$5:$F$19, TRANSPOSE($I$4:I4); kiểm tra chuỗi có giống và phân biệt chữ hoa/thường không.
• IF(EXACT($F$5:$F$19, TRANSPOSE($I$4:I4)), MATCH(ROW($F$5:$F$19), ROW($F$5:$F$19)); trả về vị trí tương đối của chuỗi trong mảng nếu TRUE.
• FREQUENCY(IF(EXACT($F$5:$F$19, TRANSPOSE($I$4:I4)), MATCH(ROW($F$5:$F$19), ROW($F$5:$F$19)), “”); tính tần suất chuỗi xuất hiện trong mảng.
• MATCH(0, FREQUENCY(IF(EXACT($F$5:$F$19, TRANSPOSE($I$4:I4)), MATCH(ROW($F$5:$F$19), ROW($F$5:$F$19)), “”), MATCH(ROW($F$5:$F$19), ROW($F$5:$F$19))), 0)); tìm giá trị False đầu tiên (tức Rỗng) trong mảng.
• INDEX($F$5:$F$19, MATCH(0, FREQUENCY(IF(EXACT($F$5:$F$19, TRANSPOSE($I$4:I4)), MATCH(ROW($F$5:$F$19), ROW($F$5:$F$19)), “”), MATCH(ROW($F$5:$F$19), ROW($F$5:$F$19))), 0)); trả về giá trị duy nhất từ mảng.

Bước 2: Cần nhấn CTRL+SHIFT+ENTER cùng lúc và giá trị duy nhất phân biệt chữ hoa/thường xuất hiện.

Toàn bộ tập dữ liệu trông như ảnh sau sau khi sắp xếp mọi loại mục vào cột tương ứng.

Bạn có thể thay đổi bất kỳ kiểu dữ liệu Product nào để đáp ứng nhu cầu và áp dụng công thức theo đó.

Phương pháp 7: Excel lọc giá trị duy nhất dùng mã VBA Macro

Từ tập dữ liệu, biết có cột Product, muốn giá trị duy nhất từ cột. Để làm việc, dùng mã VBA Macro. Viết mã gán giá trị từ lựa chọn rồi cho qua vòng lặp trừ khi loại bỏ hết trùng lặp.

Trước khi áp dụng mã VBA Macro, đảm bảo có tập dữ liệu kiểu sau và chọn phạm vi muốn lọc duy nhất.

Bước 1: Để viết mã macro, nhấn ALT+F11 mở cửa sổ Microsoft Visual Basic. Trong cửa sổ, Vào tab Insert (trong Toolbar) > Chọn Module.

Bước 2: Cửa sổ Module hiện ra. Trong Module, Dán mã sau:

Sub Unique_Values() 
Dim Range As Variant, prdct As Variant 
Dim mrf As Object 
Dim i As Long 
Set mrf = CreateObject("scripting.dictionary") 
Range = Selection 
For i = 1 To UBound(Range) 
mrf(Range(i, 1) & "") = "" 
Next 
prdct = mrf.keys 
Selection.ClearContents 
Selection(1, 1).Resize(mrf.Count, 1) = Application.Transpose(prdct) 
End Sub

Trong mã Macro,

Sau khai báo biến, mrf = CreateObject(“scripting.dictionary”) tạo đối tượng gán cho mrf.

Selection gán cho Range. Vòng lặp For lấy từng ô so khớp với Range cho trùng lặp. Sau đó, mã xóa Selection và hiện ra với các giá trị duy nhất.

Bước 3: Nhấn F5 chạy macro rồi quay lại trang tính, thấy tất cả giá trị duy nhất từ lựa chọn.

Phương pháp 8: Dùng Pivot Table (Bảng xoay) lọc giá trị duy nhất

Pivot Table là công cụ mạnh xuất danh sách mục duy nhất từ ô đã chọn. Trong Excel, dễ dàng chèn Pivot Table và đạt mục đích.

Bước 1: Chọn phạm vi nhất định (ví dụ: Product). Sau đó, Vào tab Insert > Chọn Pivot Table (từ phần Tables).

Bước 2: Cửa sổ PivotTable from a table or range hiện ra. Trong cửa sổ:

• Phạm vi (ví dụ: D4:D19) tự động được chọn.
• Chọn Existing Worksheets (Trang tính hiện có) làm where you want the PivotTable to be placed (nơi đặt PivotTable).
• Nhấn OK.

Bước 3: Cửa sổ PivotTable Fields hiện ra. Trong cửa sổ, chỉ có một trường (ví dụ: Product).

• Tích trường Product để danh sách sản phẩm duy nhất hiện ra như ảnh:

Đọc thêm: Cách lọc Excel Pivot Table

Kết luận

Lọc duy nhất là thao tác phổ biến trong Excel. Bài viết dùng nhiều tính năng, hàm như UNIQUE, FILTER, MATCH, INDEX cũng như mã VBA Macro để lọc giá trị duy nhất. Hàm giữ nguyên dữ liệu gốc và hiển thị kết quả ở cột/đích khác. Tuy nhiên, tính năng thay đổi dữ liệu gốc bằng cách xóa mục vĩnh viễn. Hy vọng bài viết đưa ra khái niệm rõ ràng xử lý trùng lặp trong tập dữ liệu và trích xuất giá trị duy nhất. Bình luận nếu có thắc mắc hoặc bổ sung. Hẹn gặp lại bài sau.

Đọc thêm

• Cách thực hiện Custom Filter trong Excel (5 cách)
• Lọc theo màu trong Excel (2 ví dụ)
• Cách lọc ô có công thức trong Excel (2 cách)
• Tìm kiếm nhiều mục trong Filter Excel (2 cách)