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

Bộ lọc Unique là một cách hiệu quả để có được nhiều mục nhập trong một 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 loại bỏ các dữ liệu trùng lặp, bất kể chúng tôi gọi nó là gì. Trong bài viết này, chúng tôi 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ử chúng ta có ba cột đơn giản trong tập dữ liệu Excel chứa Ngày đặt hàng , Danh mục và Sản phẩm . Chúng tôi muốn các sản phẩm được đặt hàng duy nhất trong toàn bộ tập dữ liệu.

Tải xuống Sổ làm việc Excel

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

Phương pháp 1:Sử dụng Tính năng Xóa các Bản trùng lặp của Excel để Lọc các Giá trị Duy nhất

Để hiểu rõ các mục nhập trong một tập dữ liệu khổng lồ, đôi khi chúng ta cần xóa các mục trùng lặp. Excel cung cấp tính năng Xóa bản sao trong Dữ liệu để bỏ qua các mục nhập trùng lặp khỏi tập dữ liệu. Trong trường hợp này, chúng tôi muốn xóa các bản sao khỏi Danh mục và Sản phẩm cột. Do đó, chúng tôi có thể sử dụng tính năng Xóa bản sao tính năng để làm như vậy.

Bước 1: Chọn phạm vi (tức là Danh mục và Sản phẩm ) sau đó đi tới Dữ liệu Tab> Chọn Xóa từ trùng lặp (từ Công cụ dữ liệu phần).

Bước 2: Xóa từ trùng lặp cửa sổ xuất hiện. Trong phần Xóa từ trùng lặp cửa sổ,

Đã kiểm tra tất cả các cột.

Đánh dấu vào tùy chọn Dữ liệu của tôi có tiêu đề .

Nhấp vào OK .

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

Nhấp vào OK .

Tất cả các bước đều dẫn đến những hậu quả sau như thể hiện trong hình ảnh bên dưới.

Phương pháp 2:Sử dụng Định dạng có Điều kiện để Lọc các Giá trị Duy nhất

Một cách khác để lọc giá trị duy nhất là Định dạng có điều kiện . Excel Định dạng có điều kiện có thể định dạng các ô với nhiều tiêu chí. Tuy nhiên, trong trường hợp này, chúng tôi sử dụng công thức để định dạng có điều kiện các ô trong một phạm vi (tức là Sản phẩm cột). Chúng tôi có hai tùy chọn để áp dụng Định dạng có điều kiện ; một là định dạng có điều kiện để lọc các giá trị duy nhất và định dạng còn lại là để ẩn các giá trị trùng lặp khỏi phạm vi.

2.1. Định dạng có điều kiện để lọc các giá trị duy nhất

Trong trường hợp này, chúng tôi sử dụng công thức trong Định dạng có điều kiện các tùy chọn để Excel lọc các mục nhập duy nhất.

Bước 1: Chọn phạm vi (tức là Sản phẩm 1 ) sau đó đi tới Trang chủ Tab> Chọn Định dạng có điều kiện (từ Kiểu )> Chọn Quy tắc mới .

Bước 2: Quy tắc định dạng mới cửa sổ bật lên. Trong Quy tắc định dạng mới Cửa sổ,

Chọn Sử dụng công thức để xác định ô cần định dạng trong Chọn loại quy tắc tùy chọn.

Nhập công thức sau trong phần Chỉnh sửa mô tả quy tắc tùy chọn.

Trong công thức, chúng tôi đã hướng dẫn Excel đếm từng ô trong D cột là Duy nhất (tức là bằng 1 ). Nếu các mục nhập khớp với điều kiện áp đặt, nó sẽ trả về TRUE và Định dạng màu các ô.

Nhấp vào Định dạng .

Bước 3: Trong giây lát, Định dạng ô cửa sổ xuất hiện. Trong Định dạng ô cửa sổ,

Trong Phông chữ section- Chọn bất kỳ màu định dạng nào như được mô tả trong hình ảnh bên dưới.

Sau đó, nhấp vào OK .

Bước 4: Nhấp vào OK trong bước trước đó sẽ đưa bạn đến Quy tắc định dạng mới cửa sổ lại. Trong Quy tắc định dạng mới cửa sổ, bạn có thể xem bản xem trước của các mục nhập duy nhất.

Nhấp vào OK .

Cuối cùng, bạn sẽ có được các mục nhập duy nhất được định dạng màu như bạn muốn, tương tự như hình bên dưới.

2.2. Định dạng có điều kiện để ẩn các bản sao

Không can thiệp vào các giá trị duy nhất, chúng tôi có thể chỉ cần ẩn các giá trị trùng lặp bằng cách sử dụng Định dạng có điều kiện . Để ẩn các bản sao, chúng ta phải áp dụng cùng một công thức như đã làm để lọc ra các bản duy nhất ngoại trừ việc gán chúng cho các giá trị lớn hơn 1 . Sau khi chọn Phông chữ trắng màu sắc, chúng tôi có thể ẩn chúng khỏi phần còn lại của các mục nhập.

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

Công thức hướng dẫn Excel đếm từng ô trong D cột dưới dạng Bản sao (tức là lớn hơn 1 ). Nếu các mục nhập khớp với điều kiện áp đặt, nó sẽ trả về TRUE và Định dạng màu (tức là Ẩn ) các ô.

Nhấp vào Định dạng .

Bước 2: Nhấp vào Định dạng sẽ đưa bạn đến Định dạng ô cửa sổ. Trong Định dạng ô cửa sổ,

Chọn Phông chữ màu Trắng .

Sau đó, nhấp vào OK .

Bước 3: Sau khi chọn Phông chữ màu sắc, Nhấp vào OK đưa bạn đến Quy tắc định dạng mới cửa sổ lại. Bạn có thể thấy bản xem trước là không rõ ràng vì chúng tôi chọn Màu trắng làm Phông chữ màu sắc.

Nhấp vào OK .

Làm theo tất cả các bước sẽ dẫn bạn đến một mô tả tương tự như hình ảnh bên dưới cho các giá trị trùng lặp.

Bạn phải chọn Trắng dưới dạng Phông chữ nếu không thì màu các mục trùng lặp sẽ không ẩn.

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

Phương pháp 3:Sử dụng Tính năng Bộ lọc Nâng cao của Tab Dữ liệu để Lọc các Giá trị Duy nhất

Các phương pháp trước đó xóa hoặc loại bỏ các mục nhập khỏi tập dữ liệu để lọc duy nhất. Điều đó khá nguy hiểm khi chúng tôi làm việc trên một số bộ dữ liệu nhất định. Có thể có những trường hợp chúng tôi không thể thay đổi tập dữ liệu thô, trong những trường hợp đó, chúng tôi có thể sử dụng Bộ lọc nâng cao tùy chọn để lọc duy nhất ở vị trí mong muốn.

Bước 1: Chọn phạm vi (tức là Sản phẩm cột). Sau đó, chuyển đến Dữ liệu Tab> Chọn Nâng cao (từ Sắp xếp &Bộ lọc phần).

Bước 2: Bộ lọc nâng cao cửa sổ xuất hiện. Trong Bộ lọc nâng cao cửa sổ,

Chọn Sao chép sang vị trí khác hành động trong Hành động quyền mua. Bạn có thể chọn Lọc danh sách tại chỗ hoặc Sao chép sang một vị trí khác tuy nhiên, chúng tôi đang chọn cái sau để không thay đổi dữ liệu thô.

Chỉ định một vị trí (tức là F4 ) trong mục Sao chép vào tùy chọn.

Đã kiểm tra Chỉ bản ghi duy nhất tùy chọn.

Nhấp vào OK .

Nhấp vào OK mang lại cho bạn các giá trị duy nhất ở vị trí định sẵn như được hướng dẫn trong các bước.

Phương pháp 4:Lọc các giá trị duy nhất bằng hàm UNIQUE trong Excel

Việc hiển thị các giá trị duy nhất trong một cột khác cũng có thể đạt được bằng UNIQUE hàm số. DUY NHẤT hàm tìm nạp một danh sách các mục nhập duy nhất từ ​​một phạm vi hoặc mảng. Cú pháp của UNIQUE chức năng là

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

Các đối số,

mảng ; dải ô hoặc mảng mà từ đó các giá trị duy nhất được trích xuất từ ​​đó.

[by_col] ; cách so sánh và trích xuất các giá trị bằng row =FALSE ( mặc định ) và bởi column =TRUE . [tùy chọn]

[precision_once] ; các giá trị xuất hiện một lần = TRUE và các giá trị duy nhất hiện có = FALSE (theo mặc định ). [tùy chọn]

Bước 1: Nhập công thức sau vào bất kỳ ô trống nào (tức là E5 ).

Bước 2: Nhấn ENTER sau đó trong một giây, tất cả các mục nhập duy nhất sẽ bật lên trong cột tương tự như hình bên dưới.

DUY NHẤT hàm tràn tất cả các mục nhập duy nhất tại một thời điểm. Tuy nhiên, bạn không thể sử dụng UNIQUE hàm khác với Excel 365 phiên bản.

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 Bộ lọc trong Excel (4 Phương pháp)
• Lối tắt cho Bộ lọc Excel (3 Sử dụng Nhanh với Ví dụ)
• Cách Sử dụng Bộ lọc Văn bản trong Excel (5 Ví dụ)

Phương pháp 5:Sử dụng các hàm DUY NHẤT và LỌC (với Tiêu chí)

Trong phương pháp 4, chúng tôi sử dụng UNIQUE chức năng để tràn ra các giá trị duy nhất. Điều gì sẽ xảy ra nếu chúng ta muốn các mục nhập duy nhất tùy thuộc vào một điều kiện? Giả sử chúng tôi muốn Sản phẩm độc đáo tên của một Danh mục nhất định từ tập dữ liệu của chúng tôi.

Trong trường hợp này, chúng tôi muốn Sản phẩm duy nhất tên của Thanh (tức là E4 ) danh mục từ tập dữ liệu của chúng tôi.

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

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

Bước 2: Nhấn ENTER . Sau đó, các sản phẩm trong Thanh , xuất hiện trong các ô của Thanh như được hiển thị trong ảnh chụp màn hình sau.

Bạn có thể chọn bất kỳ Danh mục nào để lọc các sản phẩm duy nhất từ. Đó là một cách khá hiệu quả để xử lý tập dữ liệu bán hàng khổng lồ. BỘ LỌC chỉ có sẵn trong Excel 365.

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

Phương pháp 6:Sử dụng hàm MATCH và INDEX (Công thức mảng)

Để trình bày đơn giản hơn, chúng tôi sử dụng tập dữ liệu không có khoảng trống hoặc các mục nhập phân biệt chữ hoa chữ thường. Vì vậy, làm thế nào chúng ta có thể xử lý một tập dữ liệu có khoảng trống và các mục nhập phân biệt chữ hoa chữ thường? Trước khi thể hiện một cách giải quyết, hãy lọc phạm vi không trống (tức là Sản phẩm 1 ) sử dụng công thức kết hợp. Trong trường hợp này, chúng tôi sử dụng MATCH và INDEX các chức năng để lọc duy nhất.

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

Chúng tôi có thể thấy không có ô trống nào hiện có trong phạm vi Sản phẩm 1.

Bước 1: Nhập công thức sau vào ô G5 để lọc ra cái duy nhất.

Theo công thức,

Đầu tiên, COUNTIF ($ G $ 4:G4, $ D $ 5:$ D $ 19) ; đếm số ô trong phạm vi (tức là $ G $ 4:G4 ) tuân theo điều kiện (tức là $ D $ 5:$ D $ 19) . COUNTIF trả về 1 nếu nó tìm thấy $ G $ 4:G4 trong phạm vi khác 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 cùng, INDEX ($ D $ 5:$ D $ 19, MATCH (0, COUNTIF ($ G $ 4:G4, $ D $ 5:$ D $ 19), 0)); trả về các mục nhập ô đáp ứng điều kiện.

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

Bước 2: Vì công thức là một công thức mảng, hãy nhấn CTRL + SHIFT + ENTER hoàn toàn. Tất cả các mục nhập duy nhất từ ​​ Sản phẩm 1 phạm vi xuất hiện.

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

Bây giờ, trong Sản phẩm 2 phạm vi, chúng ta có thể thấy nhiều ô trống tồn tại. Để lọc ra giá trị duy nhất trong số các ô trống, chúng tôi phải chèn NGÂN HÀNG chức năng.

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

Công thức này hoạt động theo cách giống như chúng tôi đã mô tả trong 6.1. phần . Tuy nhiên, IF bổ sung hoạt động với kiểm tra logic của ISBLANK hàm cho phép công thức bỏ qua bất kỳ ô trống nào trong phạm vi.

Bước 2: Nhấn CTRL + SHIFT + ENTER và công thức bỏ qua các ô trống và tìm nạp tất cả các mục nhập duy nhất như được mô tả trong hình sau.

6.3. Chức năng MATCH và INDEX để lọc các giá trị duy nhất từ ​​một phạm vi phân biệt chữ hoa chữ thường

Nếu tập dữ liệu của chúng tôi có các mục nhập phân biệt chữ hoa chữ thường, chúng tôi phải sử dụng FREQUENCY cùng với chức năng TRANSPOSE và ROW các chức năng để lọc ra cái duy nhất.

Bước 1: Áp dụng công thức dưới đây trong ô I5 .

Các phần của công thức,

• VẬN CHUYỂN ($ I $ 4:I4); chuyển các giá trị trước đó bằng cách chuyển đổi dấu chấm phẩy thành dấu phẩy. ( tức là TRANSPOSE ({“giá trị duy nhất (phân biệt chữ hoa chữ thường)”; Whole Wheat ”}) trở thành {“ giá trị duy nhất (phân biệt chữ hoa chữ thường) ”,“ Whole Wheat ”}
• CHÍNH XÁC ($ F $ 5:$ F $ 19, VẬN CHUYỂN ($ I $ 4:I4); kiểm tra xem các chuỗi có giống nhau và có phân biệt chữ hoa chữ thường hay không.
• IF (CHÍNH XÁC ($ F $ 5:$ F $ 19, VẬN CHUYỂN ($ I $ 4:I4)), TRẬN ĐẤU (ROW ($ F $ 5:$ F $ 19), ROW ($ F $ 5:$ F $ 19 )); trả về vị trí tương đối của một chuỗi trong mảng nếu TRUE .
• TẦN SỐ (IF (CHÍNH XÁC ($ F $ 5:$ F $ 19, VẬN CHUYỂN ($ I $ 4:I4)), TRẬN ĐẤU (ROW ($ F $ 5:$ F $ 19), ROW ($ F $ 5:$ F $ 19)), “”) ; tính toán số lần một chuỗi hiện diện trong mảng.
• TRẬN ĐẤU (0, FREQUENCY (NẾU (CHÍNH XÁC ($ F $ 5:$ F $ 19, VẬN CHUYỂN ($ I $ 4:I4)), TRẬN ĐẤU (ROW ($ F $ 5:$ F $ 19), ROW ($ F $ 5:$ F $ 19)), “”), MATCH (ROW ($ F $ 5:$ F $ 19), ROW ($ F $ 5:$ F $ 19))), 0)) ; tìm thấy False đầu tiên (tức là Rỗng ) các giá trị trong mảng.
• CHỈ SỐ ($ F $ 5:$ F $ 19, TRẬN ĐẤU (0, FREQUENCY (NẾU (CHÍNH XÁC ($ F $ 5:$ F $ 19, VẬN CHUYỂN ($ I $ 4:I4)), TRẬN ĐẤU (ROW ($ F) $ 5:$ F $ 19), ROW ($ F $ 5:$ F $ 19)), “”), MATCH (ROW ($ F $ 5:$ F $ 19), ROW ($ F $ 5:$ F $ 19))), 0 )) ; trả về các giá trị duy nhất từ ​​mảng.

Bước 2: Bạn cần nhấn CTRL + SHIFT + ENTER hoàn toàn và các giá trị duy nhất có phân biệt chữ hoa chữ thường xuất hiện trong các ô.

Vì vậy, toàn bộ tập dữ liệu trông giống như hình dưới đây sau khi sắp xếp tất cả các loại mục nhập trong các cột tương ứng của chúng.

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

Phương pháp 7:Excel Lọc các Giá trị Duy nhất Sử dụng Mã Macro VBA

Từ tập dữ liệu, chúng tôi biết rằng chúng tôi có cột Sản phẩm và chúng tôi muốn các giá trị duy nhất từ ​​cột. Để đạt được công việc, chúng tôi có thể sử dụng VBA Mã macro. Chúng ta có thể viết một mã chỉ định các giá trị từ lựa chọn sau đó gửi nó qua các vòng lặp trừ khi nó loại bỏ tất cả các bản sao.

Trước khi chúng tôi áp dụng VBA Mã macro, hãy đảm bảo rằng chúng tôi có tập dữ liệu thuộc loại sau và chúng tôi chọn phạm vi mà chúng tôi muốn lọc duy nhất.

Bước 1: Để viết mã macro, nhấn ALT + F11 để mở Microsoft Visual Basic cửa sổ. Trong cửa sổ, đi tới Chèn (trong Thanh công cụ )> Chọn Mô-đun .

Bước 2: Mô-đun cửa sổ xuất hiện. Trong Mô-đun , Dán đoạ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 khi khai báo các biến, mrf =CreateObject (“scripting.dictionary”) tạo một đối tượng được chỉ định cho mrf .

Lựa chọn được chỉ định cho Phạm vi . Đối với Vòng lặp lấy từng ô sau đó khớp với Phạm vi cho các bản sao. Sau đó, mã xóa Lựa chọn và xuất hiện với . duy nhất

Bước 3: Nhấn F5 để chạy macro, sau đó quay lại trang tính, bạn sẽ thấy tất cả các giá trị duy nhất từ ​​vùng chọn.

Phương pháp 8:Sử dụng Bảng tổng hợp để lọc các giá trị duy nhất

Bảng tổng hợp là một công cụ mạnh để xuất một danh sách các mục duy nhất từ ​​các ô đã chọn. Trong Excel, chúng ta có thể dễ dàng chèn Pivot Table và đạt được những gì chúng ta mong muốn ở đây.

Bước 1: Chọn một phạm vi nhất định (tức là Sản phẩm ). Sau đó, đi tới Chèn Tab> Chọn Bảng tổng hợp (từ Bảng phần).

Bước 2: PivotTable từ một bảng hoặc dải ô cửa sổ xuất hiện. Trong cửa sổ,

Phạm vi (tức là D4:D19 ) sẽ được chọn tự động.

Chọn Trang tính hiện có là nơi bạn muốn đặt PivotTable tùy chọn.

Nhấp vào OK .

Bước 3: Trường PivotTable cửa sổ xuất hiện. Trong Trường PivotTable cửa sổ, chỉ có một trường (tức là Sản phẩm ).

Đã kiểm tra Sản phẩm để làm cho danh sách sản phẩm duy nhất xuất hiện như trong hình bên dưới.

Đọc thêm: Cách lọc bảng tổng hợp Excel

Kết luận

Bộ lọc duy nhất là một hoạt động phổ biến để thực hiện trong Excel. Trong bài viết này, chúng tôi sử dụng các tính năng, chức năng khác nhau, chẳng hạn như DUY NHẤT , BỘ LỌC , TRẬN ĐẤU , INDEX cũng như VBA Macro mã để lọc ra các giá trị duy nhất. Các hàm giữ nguyên dữ liệu thô và hiển thị các giá trị kết quả trong một cột hoặc đích khác. Tuy nhiên, các tính năng thay đổi dữ liệu thô bằng cách xóa vĩnh viễn các mục nhập khỏi tập dữ liệu. Tôi hy vọng bài viết này cung cấp cho bạn một khái niệm sáng suốt về việc xử lý các bản sao trong tập dữ liệu của bạn và trích xuất các giá trị duy nhất. Bình luận, nếu bạn có thêm thắc mắc hoặc có điều gì đó để thêm. Hẹn gặp lại các bạn trong bài viết tiếp theo của tôi.

Bài đọc thêm

• Cách Thực hiện Bộ lọc Tùy chỉnh trong Excel (5 Cách)
• Lọc theo màu trong Excel (2 Ví dụ)
• Cách Lọc Ô bằng Công thức trong Excel (2 Cách)
• Tìm kiếm Nhiều Mục trong Bộ lọc Excel (2 Cách)