Làm cách nào để xóa các giá trị trùng lặp trong excel nhưng vẫn giữ các giá trị duy nhất?

Giá trị trùng lặp trong dữ liệu của bạn có thể là một vấn đề lớn. Nó có thể dẫn đến sai sót đáng kể và đánh giá quá cao kết quả của bạn

Nhưng việc tìm và xóa chúng khỏi dữ liệu của bạn thực sự khá dễ dàng trong Excel

Trong hướng dẫn này, chúng ta sẽ xem xét 7 phương pháp khác nhau để định vị và xóa các giá trị trùng lặp khỏi dữ liệu của bạn

Video hướng dẫn

Giá trị trùng lặp là gì?

Giá trị trùng lặp xảy ra khi cùng một giá trị hoặc tập hợp giá trị xuất hiện trong dữ liệu của bạn

Đối với một tập hợp dữ liệu nhất định, bạn có thể xác định các bản sao theo nhiều cách khác nhau

Trong ví dụ trên, có một bộ dữ liệu đơn giản với 3 cột là Hãng, Kiểu và Năm cho danh sách ô tô

1. Hình ảnh đầu tiên làm nổi bật tất cả các bản sao chỉ dựa trên Kiểu dáng của chiếc xe
2. Hình ảnh thứ hai làm nổi bật tất cả các bản sao dựa trên Kiểu dáng và Kiểu dáng của ô tô. Điều này dẫn đến một bản sao ít hơn
3. Hình ảnh thứ hai làm nổi bật tất cả các bản sao dựa trên tất cả các cột trong bảng. Điều này dẫn đến thậm chí ít giá trị hơn được coi là trùng lặp

Kết quả từ các bản sao dựa trên một cột duy nhất so với toàn bộ bảng có thể rất khác nhau. Bạn phải luôn biết mình muốn phiên bản nào và Excel đang làm gì

Tìm và loại bỏ các giá trị trùng lặp bằng lệnh Remove Duplicates

Loại bỏ các giá trị trùng lặp trong dữ liệu là một nhiệm vụ rất phổ biến. Điều này rất phổ biến, có một lệnh chuyên dụng để thực hiện điều đó trong dải băng

Chọn một ô bên trong dữ liệu mà bạn muốn loại bỏ các bản sao và chuyển đến tab Dữ liệu và nhấp vào lệnh Xóa bản sao

Khi đó Excel sẽ chọn toàn bộ tập hợp dữ liệu và mở cửa sổ Remove Duplicates

1. Sau đó, bạn cần cho Excel biết nếu dữ liệu chứa tiêu đề cột ở hàng đầu tiên. Nếu điều này được chọn, thì hàng dữ liệu đầu tiên sẽ bị loại trừ khi tìm và xóa các giá trị trùng lặp
2. Sau đó, bạn có thể chọn các cột sẽ sử dụng để xác định các cột trùng lặp. Ngoài ra còn có các nút Chọn tất cả và Bỏ chọn tất cả tiện dụng ở trên mà bạn có thể sử dụng nếu bạn có một danh sách dài các cột trong dữ liệu của mình

Khi bạn nhấn OK, Excel sau đó sẽ xóa tất cả các giá trị trùng lặp mà nó tìm thấy và cung cấp cho bạn số liệu tóm tắt về số lượng giá trị đã bị xóa và số lượng giá trị còn lại

Lệnh này sẽ thay đổi dữ liệu của bạn, vì vậy tốt nhất bạn nên thực hiện lệnh trên bản sao dữ liệu của mình để giữ nguyên dữ liệu gốc

Tìm và loại bỏ các giá trị trùng lặp với bộ lọc nâng cao

Ngoài ra còn có một cách khác để loại bỏ bất kỳ giá trị trùng lặp nào trong dữ liệu của bạn khỏi dải băng. Điều này có thể từ các bộ lọc nâng cao

Chọn một ô bên trong dữ liệu và chuyển đến tab Dữ liệu và nhấp vào lệnh Bộ lọc nâng cao

Điều này sẽ mở ra cửa sổ Bộ lọc nâng cao

1. Bạn có thể chọn Lọc danh sách tại chỗ hoặc Sao chép sang vị trí khác. Lọc danh sách tại chỗ sẽ ẩn các hàng chứa bất kỳ bản sao nào trong khi sao chép sang vị trí khác sẽ tạo bản sao của dữ liệu
2. Excel sẽ đoán phạm vi dữ liệu, nhưng bạn có thể điều chỉnh nó trong phạm vi Danh sách. Có thể để trống phạm vi Tiêu chí và trường Sao chép vào sẽ cần được điền nếu tùy chọn Sao chép vào vị trí khác được chọn
3. Chọn hộp cho Chỉ bản ghi duy nhất

Nhấn OK và bạn sẽ loại bỏ các giá trị trùng lặp

Bộ lọc nâng cao có thể là một tùy chọn hữu ích để loại bỏ các giá trị trùng lặp của bạn và đồng thời tạo một bản sao dữ liệu của bạn. Nhưng các bộ lọc nâng cao sẽ chỉ có thể thực hiện việc này trên toàn bộ bảng

Tìm và loại bỏ các giá trị trùng lặp bằng bảng tổng hợp

Bảng tổng hợp chỉ để phân tích dữ liệu của bạn, phải không?

Bạn thực sự có thể sử dụng chúng để loại bỏ dữ liệu trùng lặp

Bạn sẽ không thực sự xóa các giá trị trùng lặp khỏi dữ liệu của mình bằng phương pháp này, bạn sẽ sử dụng bảng tổng hợp để chỉ hiển thị các giá trị duy nhất từ ​​tập dữ liệu

Đầu tiên, tạo một bảng tổng hợp dựa trên dữ liệu của bạn. Chọn một ô bên trong dữ liệu của bạn hoặc toàn bộ phạm vi dữ liệu ➜ chuyển đến tab Chèn ➜ chọn PivotTable ➜ nhấn OK trong hộp thoại Tạo PivotTable

Với bảng tổng hợp trống mới, hãy thêm tất cả các trường vào khu vực Hàng của bảng tổng hợp

Sau đó, bạn sẽ cần thay đổi bố cục của bảng tổng hợp kết quả để nó ở định dạng bảng. Với bảng tổng hợp được chọn, chuyển đến tab Thiết kế và chọn Bố cục Báo cáo. Có hai tùy chọn bạn sẽ cần phải thay đổi ở đây

1. Chọn tùy chọn Hiển thị ở dạng bảng
2. Chọn tùy chọn Lặp lại Tất cả Nhãn Mục

Bạn cũng sẽ cần xóa mọi tổng phụ khỏi bảng tổng hợp. Vào tab Design ➜ chọn Subtotals ➜ chọn Do Not Show Subtotals

Bây giờ bạn có một bảng tổng hợp bắt chước một bộ dữ liệu dạng bảng

Bảng tổng hợp chỉ liệt kê các giá trị duy nhất cho các mục trong khu vực Hàng, vì vậy bảng tổng hợp này sẽ tự động xóa mọi giá trị trùng lặp trong dữ liệu của bạn

Tìm và loại bỏ các giá trị trùng lặp với Power Query

Power Query hoàn toàn là về chuyển đổi dữ liệu, vì vậy bạn có thể chắc chắn rằng nó có khả năng tìm và loại bỏ các giá trị trùng lặp

Chọn bảng giá trị mà bạn muốn loại bỏ các giá trị trùng lặp khỏi ➜ chuyển đến tab Dữ liệu ➜ chọn truy vấn Từ bảng/Phạm vi

Loại bỏ các bản sao dựa trên một hoặc nhiều cột

Với Power Query, bạn có thể loại bỏ các mục trùng lặp dựa trên một hoặc nhiều cột trong bảng

Bạn cần chọn những cột để loại bỏ trùng lặp dựa trên. Bạn có thể giữ Ctrl để chọn nhiều cột

Nhấp chuột phải vào tiêu đề cột đã chọn và chọn Remove Duplicates từ menu

Bạn cũng có thể truy cập lệnh này từ tab Trang chủ ➜ Xóa hàng ➜ Xóa trùng lặp

= Table.Distinct(#"Previous Step", {"Make", "Model"})

Nếu bạn nhìn vào công thức đã được tạo, thì nó đang sử dụng Bảng. Hàm khác biệt với tham số thứ hai tham chiếu cột nào sẽ sử dụng

Loại bỏ các bản sao dựa trên toàn bộ bảng

Để loại bỏ các cột trùng lặp dựa trên toàn bộ bảng, bạn có thể chọn tất cả các cột trong bảng sau đó loại bỏ các cột trùng lặp. Nhưng có một phương pháp nhanh hơn mà không yêu cầu chọn tất cả các cột

Có một nút ở góc trên cùng bên trái của phần xem trước dữ liệu với một loạt các lệnh có thể được áp dụng cho toàn bộ bảng

Bấm vào nút bảng ở góc trên cùng bên trái ➜ rồi chọn Remove Duplicates

= Table.Distinct(#"Previous Step")

Nếu bạn nhìn vào công thức được tạo, nó sẽ sử dụng cùng một Bảng. Hàm riêng biệt không có tham số thứ hai. Không có tham số thứ hai, hàm sẽ hoạt động trên toàn bộ bảng

Giữ các bản sao dựa trên một cột hoặc trên toàn bộ bảng

Trong Power Query còn có các lệnh giữ trùng lặp cho cột đã chọn hoặc cho toàn bộ bảng

Thực hiện theo các bước tương tự như loại bỏ các bản sao, nhưng thay vào đó hãy sử dụng lệnh Keep Rows ➜ Keep Duplicates. Điều này sẽ hiển thị cho bạn tất cả dữ liệu có giá trị trùng lặp

Tìm và loại bỏ các giá trị trùng lặp bằng công thức

Bạn có thể sử dụng một công thức để giúp bạn tìm các giá trị trùng lặp trong dữ liệu của mình

Trước tiên, bạn sẽ cần thêm một cột trợ giúp kết hợp dữ liệu từ bất kỳ cột nào mà bạn muốn dựa trên định nghĩa trùng lặp của mình

= [@Make] & [@Model] & [@Year]

Công thức trên sẽ nối cả ba cột thành một cột duy nhất. Nó sử dụng toán tử dấu và để nối từng cột

= TEXTJOIN("", FALSE , CarList[@[Make]:[Year]])

Nếu bạn có một danh sách dài các cột cần kết hợp, bạn có thể sử dụng công thức trên để thay thế. Bằng cách này, bạn có thể chỉ cần tham chiếu tất cả các cột dưới dạng một phạm vi

Sau đó, bạn sẽ cần thêm một cột khác để đếm các giá trị trùng lặp. Điều này sẽ được sử dụng sau này để lọc ra các hàng dữ liệu xuất hiện nhiều lần

= COUNTIFS($E$3:E3, E3)

Sao chép công thức trên xuống cột và nó sẽ đếm số lần giá trị hiện tại xuất hiện trong danh sách các giá trị trên

Nếu số đếm là 1 thì đây là lần đầu tiên giá trị xuất hiện trong dữ liệu và bạn sẽ giữ giá trị này trong tập hợp các giá trị duy nhất của mình. Nếu số lượng là 2 hoặc nhiều hơn thì giá trị đã xuất hiện trong dữ liệu và đó là giá trị trùng lặp có thể bị xóa

Thêm bộ lọc vào danh sách dữ liệu của bạn

• Chuyển đến tab Dữ liệu và chọn lệnh Bộ lọc
• Sử dụng phím tắt Ctrl + Shift + L

Bây giờ bạn có thể lọc trên cột Count. Lọc trên 1 sẽ tạo ra tất cả các giá trị duy nhất và loại bỏ mọi giá trị trùng lặp

Sau đó, bạn có thể chọn các ô hiển thị từ bộ lọc kết quả để sao chép và dán vào nơi khác. Sử dụng phím tắt Alt + ;

Tìm và loại bỏ các giá trị trùng lặp với định dạng có điều kiện

Với định dạng có điều kiện, có một cách để làm nổi bật các giá trị trùng lặp trong dữ liệu của bạn

Cũng giống như phương pháp công thức, bạn cần thêm một cột trợ giúp kết hợp dữ liệu từ các cột. Định dạng có điều kiện không hoạt động với dữ liệu trên các hàng, vì vậy, bạn sẽ cần cột kết hợp này nếu muốn phát hiện các mục trùng lặp dựa trên nhiều cột

Sau đó, bạn cần chọn cột dữ liệu kết hợp

Để tạo định dạng có điều kiện, hãy chuyển đến tab Trang đầu ➜ chọn Định dạng có điều kiện ➜ Đánh dấu các quy tắc ô ➜ Nhân đôi giá trị

Điều này sẽ mở ra cửa sổ Giá trị trùng lặp định dạng có điều kiện

1. Bạn có thể chọn đánh dấu các giá trị Trùng lặp hoặc Duy nhất
2. Bạn cũng có thể chọn trong số các định dạng ô được xác định trước để đánh dấu các giá trị hoặc tạo định dạng tùy chỉnh của riêng bạn

Cảnh báo. Các phương pháp trước đây để tìm và loại bỏ các giá trị trùng lặp coi lần xuất hiện đầu tiên của một giá trị là trùng lặp và sẽ giữ nguyên giá trị đó. Tuy nhiên, phương pháp này sẽ làm nổi bật lần xuất hiện đầu tiên và sẽ không tạo ra bất kỳ sự khác biệt nào

Với các giá trị được đánh dấu, giờ đây bạn có thể lọc các giá trị trùng lặp hoặc duy nhất bằng tùy chọn lọc theo màu. Đảm bảo thêm bộ lọc vào dữ liệu của bạn. Chuyển đến tab Dữ liệu và chọn lệnh Bộ lọc hoặc sử dụng phím tắt Ctrl + Shift + L

1. Nhấp vào chuyển đổi bộ lọc
2. Chọn Lọc theo màu trong menu
3. Lọc màu được sử dụng trong định dạng có điều kiện để chọn các giá trị trùng lặp hoặc lọc trên Không nền để chọn các giá trị duy nhất

Sau đó, bạn chỉ có thể chọn các ô hiển thị bằng phím tắt Alt + ;

Tìm và loại bỏ các giá trị trùng lặp bằng VBA

Có một lệnh tích hợp trong VBA để loại bỏ các bản sao trong các đối tượng danh sách

Sub RemoveDuplicates() Dim DuplicateValues As Range Set DuplicateValues = ActiveSheet.ListObjects("CarList").Range DuplicateValues.RemoveDuplicates Columns:=Array(1, 2, 3), Header:=xlYes End Sub

Quy trình trên sẽ xóa các bản sao khỏi bảng Excel có tên CarList

Columns:=Array(1, 2, 3)

Phần trên của quy trình sẽ đặt cột nào để phát hiện trùng lặp dựa trên. Trong trường hợp này, nó sẽ nằm trên toàn bộ bảng vì cả ba cột đều được liệt kê

Header:=xlYes

Phần trên của quy trình cho Excel biết hàng đầu tiên trong danh sách của chúng ta chứa tiêu đề cột

Bạn sẽ muốn tạo một bản sao dữ liệu của mình trước khi chạy mã VBA này, vì không thể hoàn tác sau khi mã chạy