Bỏ qua nội dung chính

Tìm, đánh dấu, lọc, đếm, xóa các bản sao trong Excel

Trong Excel, dữ liệu trùng lặp xảy ra hết lần này đến lần khác khi chúng ta ghi dữ liệu theo cách thủ công, sao chép dữ liệu từ các nguồn khác hoặc vì các lý do khác. Đôi khi, các bản sao là cần thiết và hữu ích. Tuy nhiên, đôi khi các giá trị trùng lặp dẫn đến lỗi hoặc hiểu nhầm. Ở đây, bài viết này sẽ giới thiệu các phương pháp nhanh chóng xác định, đánh dấu, lọc, đếm, xóa các bản sao theo công thức, quy tắc định dạng có điều kiện, bổ trợ của bên thứ ba, v.v. trong Excel.

Mục lục


1. Tìm và đánh dấu các bản sao

Khi gặp các giá trị trùng lặp trong một cột hoặc dải ô, bạn có thể muốn nhanh chóng tìm ra các giá trị trùng lặp. Ở đây, phần này nói về cách nhanh chóng tìm hoặc xác định dữ liệu trùng lặp trong các cột và đánh dấu các ô, hàng hoặc đánh dấu các hàng trùng lặp dựa trên các bản sao trong một cột nhất định trong Excel.

1.1 Tìm các bản sao bằng công thức

Phần này giới thiệu một số công thức để nhanh chóng tìm hoặc xác định các giá trị trùng lặp trong một hoặc hai cột.

1.1.1 Tìm các ô trùng lặp trong một cột bằng công thức

Khi chỉ tìm thấy các ô trùng lặp trong một cột hoặc danh sách, chúng ta có thể áp dụng COUNTIF chức năng nhanh chóng tìm ra và đếm dữ liệu trùng lặp một cách dễ dàng.
1. Chọn một ô trống bên cạnh cột mà bạn sẽ tìm thấy các bản sao bên trong.

2. Nhập công thức = COUNTIF ($ C $ 3: $ C $ 12, C3)> 1và nhấn đăng ký hạng mục thi Chìa khóa.

3. Kéo tay cầm Tự động điền của ô công thức để sao chép công thức này sang các ô khác.

Ghi chú: Trong công thức = COUNTIF ($ C $ 3: $ C $ 12, C3)> 1,
(1) $ C $ 3: $ C $ 12 là cột hoặc danh sách mà bạn sẽ tìm thấy các giá trị trùng lặp bên trong. Vì cột là tĩnh khi chúng ta sao chép công thức sang các ô khác, nên nó thường là tham chiếu tuyệt đối với “$”.
(2) C3 là ô đầu tiên của cột được chỉ định. Nó là tham chiếu tương đối vì chúng ta cần nó thay đổi tự động khi chúng ta sao chép công thức sang các ô khác.
(3) Công thức này sẽ trả về TRUE or SAI LẦM. TRUE nghĩa là giá trị tương ứng bị trùng lặp, trong khi KHÔNG ĐÚNG cho biết giá trị tương ứng là duy nhất trong cột.
(4) Đôi khi, sự ĐÚNG hoặc SAI có thể không được hiểu bằng trực giác. Chúng ta có thể kết hợp công thức gốc và hàm IF để trả về Trùng lặp trực tiếp.
= IF (COUNTIF ($ C $ 3: $ C $ 12, C3)> 1, "Bản sao", "")

1.1.2 Tìm các ô trùng lặp trong hai cột bằng công thức

Trong một số trường hợp, bạn cần so sánh hai cột và tìm ra các giá trị trùng lặp. Ví dụ: bạn có hai danh sách tên và muốn tìm ra các tên trùng lặp trong danh sách thứ hai so với danh sách đầu tiên. bạn có thể áp dụng hàm Vlookup và hàm IFERROR để thực hiện dễ dàng.

1. Chọn một ô trống bên cạnh danh sách tên thứ hai.

2. Nhập công thức = IFERROR (VLOOKUP (D3, $ B $ 3: $ B $ 18,1,0), "")và nhấn đăng ký hạng mục thi Chìa khóa.

3. Kéo tay cầm Tự động điền của ô công thức để sao chép công thức sang các ô khác khi bạn cần.

Ghi chú: Trong công thức trên,
(1) D3 là ô đầu tiên trong danh sách tên thứ hai. Tham chiếu cần tự động thay đổi khi chúng ta sao chép công thức sang các ô khác, do đó, nó là tương đối.
(2) $ B $ 3: $ B $ 18 là danh sách tên đầu tiên. Đây là tham chiếu tuyệt đối vì phạm vi cần giữ tĩnh khi chúng ta sao chép công thức sang các ô khác.
(3) Khi một tên trùng lặp với các tên trong danh sách đầu tiên, công thức sẽ trả về tên; nếu không nó sẽ trả về trống.
(4) Bạn cũng có thể áp dụng công thức = IF (COUNTIF ($ B $ 3: $ B $ 18, D3)> 0, "Bản sao", "") để tìm ra các tên trùng lặp trong danh sách thứ hai so với danh sách đầu tiên. Công thức này sẽ trả về "Bản sao" nếu tên tương ứng là trùng lặp.

(5) Nếu bạn cần tìm các bản sao trong hai cột trên hai trang tính, bạn chỉ cần thêm tên trang tính trước tham chiếu của cột được so sánh. Trong ví dụ của chúng tôi, chỉ cần thay đổi $ B $ 3: $ B $ 18 thành Sheet1! $ B $ 3: $ B $ 18 trong công thức.

1.1.3 Tìm các ô trùng lặp phân biệt chữ hoa chữ thường với công thức

Các công thức được giới thiệu ở trên không phù hợp với trường hợp tìm thấy các bản sao, cho biết họ coi "apple" là bản sao với "APPLE". Tại đây, bạn có thể áp dụng công thức mảng để tìm các giá trị trùng lặp trong một cột với các trường hợp phù hợp.

1. Chọn một ô trống bên cạnh cột mà bạn sẽ tìm thấy các bản sao bên trong.

2. Nhập công thức mảng = IF (SUM ((- EXACT ($ C $ 3: $ C $ 12, C3))) <= 1, "", "Trùng lặp"), và hãy nhấn Ctrl + sự thay đổi + đăng ký hạng mục thi phím.

3. Kéo tay cầm Tự động điền của ô công thức để sao chép công thức mảng sang các ô khác.

Ghi chú: Trong công thức mảng ở trên,
(1) $ C $ 3: $ C $ 12 là cột mà bạn cần tìm các giá trị trùng lặp. Tham chiếu là tuyệt đối vì tham chiếu là tĩnh khi sao chép công thức mảng sang các ô khác.
(2) C4 là ô đầu tiên trong cột. Tham chiếu là tương đối, vì tham chiếu cần tự động thay đổi khi sao chép công thức mảng sang các ô khác.
(3) Nếu ô tương ứng bị trùng lặp, công thức mảng sẽ trả về "Trùng lặp", nếu không nó sẽ trả về giá trị trống.

1.2 Tìm và đánh dấu các bản sao bằng định dạng có điều kiện

Đôi khi, bạn có thể cần phải đánh dấu các giá trị hoặc hàng trùng lặp để nhắc nhở hoặc cảnh báo bản thân hoặc người đọc của bạn. Phần này sẽ hướng dẫn bạn làm nổi bật các ô hoặc hàng bổ sung với các quy tắc định dạng có điều kiện.

1.2.1 Tìm và đánh dấu các ô trùng lặp với định dạng có điều kiện

Bạn có thể áp dụng Định dạng có điều kiện tính năng đánh dấu nhanh các ô trùng lặp trong một cột hoặc một dải ô.

1. Chọn cột mà bạn sẽ đánh dấu các ô trùng lặp.

2. Nhấp chuột Trang Chủ > Định dạng có điều kiện > Đánh dấu các quy tắc về ô > Giá trị trùng lặp. Xem ảnh chụp màn hình bên dưới:

3. Trong hộp thoại Giá trị trùng lặp hiện ra, hãy chọn Bản sao từ danh sách thả xuống đầu tiên, hãy chọn một kịch bản nổi bật từ danh sách thả xuống thứ hai và nhấp vào OK .

Chú ý: Nếu các tình huống đánh dấu cài sẵn không thể đáp ứng nhu cầu của bạn, bạn có thể chọn Định dạng tùy chỉnh từ danh sách thả xuống thứ hai, rồi chọn màu tô sáng, phông chữ và đường viền ô khi bạn cần trong hộp thoại Định dạng Ô hiện ra.

Sau đó, bạn sẽ thấy tất cả các ô trùng lặp được đánh dấu trong cột đã chọn như hình minh họa bên dưới.

Chú ý:  
(1) Sau khi đánh dấu các ô trùng lặp, chúng tôi có thể dễ dàng lọc các ô trùng lặp này. (Bấm để xem cách làm)
(2) Sau khi đánh dấu các ô trùng lặp, chúng tôi cũng có thể xóa hàng loạt các ô trùng lặp một cách dễ dàng. (Bấm để xem cách làm)

1.2.2 Tìm và đánh dấu các hàng dựa trên các ô trùng lặp

Một số người dùng thích đánh dấu các hàng dựa trên các ô trùng lặp trong một cột nhất định. Trong tình huống này, chúng ta có thể tạo quy tắc định dạng có điều kiện tùy chỉnh để hoàn thành việc đó.

1. Chọn phạm vi (không bao gồm hàng tiêu đề) mà bạn sẽ đánh dấu các hàng dựa trên các ô trùng lặp.

2. Nhấp chuột Trang Chủ > Định dạng có điều kiện > Quy tắc mới.

3. Trong hộp thoại Quy tắc Định dạng Mới,
(1) Nhấp để chọn Sử dụng công thức để xác định ô cần định dạng Tùy chọn;
(2) Nhập công thức = COUNTIF ($ C $ 3: $ C $ 12, $ C3)> 1 trong Định dạng các giá trị trong đó công thức này đúng cái hộp;
Lời khuyên: Trong công thức, $ C $ 3: $ C $ 12 là cột chứa các ô trùng lặp và $ C3 là ô đầu tiên trong cột.
(3) Nhấp vào Định dạng .

4. Trong hộp thoại Định dạng Ô, chỉ định màu tô, phông chữ, đường viền ô nếu bạn cần và bấm vào OK liên tiếp để lưu cài đặt.

Cho đến nay, trong phạm vi đã chọn, các hàng được đánh dấu dựa trên các ô trùng lặp trong cột được chỉ định. Xem ảnh chụp màn hình:

1.2.3 Tìm và đánh dấu các hàng trùng lặp với định dạng có điều kiện

Để đánh dấu các hàng trùng lặp trong một phạm vi nhất định, bạn có thể áp dụng Định dạng có điều kiện để đạt được nó quá.

1. Chọn phạm vi ngoại trừ hàng tiêu đề.

2. Nhấp chuột Trang Chủ > Định dạng có điều kiện > Quy tắc mới.

3. Trong hộp thoại Quy tắc Định dạng Mới:
(1) Nhấp để chọn Sử dụng công thức để xác định ô cần định dạng Tùy chọn;
(2) Trong Định dạng các giá trị trong đó công thức này đúng hộp, nhập công thức =COUNTIFS($B$3:$B$12,$B3,$C$3:$C$12,$C3,$D$3:$D$12,$D3)>1;
(3) Nhấp vào Định dạng .

Chú ý: Trong công thức =COUNTIFS($B$3:$B$12,$B3,$C$3:$C$12,$C3,$D$3:$D$12,$D3)>1:
(1) $ B $ 3: $ B $ 12 là cột đầu tiên trong phạm vi và $ B3 là ô đầu tiên trong cột này;
(2) $ C $ 3: $ C $ 12 là cột thứ hai trong phạm vi và $ C3 là ô đầu tiên trong cột;
(3) $ D $ 3: $ D $ 12 là cột thứ ba trong phạm vi và $ D3 là ô đầu tiên trong cột;
(4) Nếu có nhiều cột hơn trong phạm vi của bạn, bạn có thể thêm liên tiếp các tham chiếu cột và các ô đầu tiên của chúng trong công thức.

4. Trong hộp thoại Định dạng Ô, chỉ định màu tô sáng, phông chữ, đường viền ô, v.v. khi bạn cần, rồi bấm OK liên tiếp để lưu cài đặt.

Cho đến nay, các hàng trùng lặp được xác định và đánh dấu trong phạm vi đã chọn. Xem ảnh chụp màn hình:

1.2.4 Tìm và đánh dấu các bản sao ngoại trừ các bản sao đầu tiên

Bạn có thể nhận thấy rằng tất cả các bản sao đều được xác định hoặc đánh dấu bằng các phương pháp trên. Đôi khi, bạn chỉ muốn xem những mục nào bị trùng lặp ngoại trừ những trường hợp đầu tiên. Bạn cũng có thể hoàn thành nó với Định dạng có điều kiện tính năng và một công thức khác.

1. Chọn cột có tiêu đề.
Lời khuyên: Nếu bạn cần đánh dấu các hàng dựa trên các bản sao trong một cột ngoại trừ các trường hợp đầu tiên, hãy chọn phạm vi không có hàng tiêu đề.

2. Nhấp chuột Trang Chủ > Định dạng có điều kiện > Quy tắc mới.

3. Trong hộp thoại Quy tắc Định dạng Mới:
(1) Nhấp để đánh dấu Sử dụng công thức để xác định ô cần định dạng Tùy chọn;
(2) Trong Định dạng các giá trị trong đó công thức này đúng hộp, nhập công thức = COUNTIF ($ C $ 3: $ C3, C3)> 1;
Lời khuyên: Để đánh dấu các hàng dựa trên các bản sao trong một cột, hãy nhập công thức = COUNTIF ($ C $ 3: $ C3, $ C3)> 1.
(3) Nhấp vào Định dạng .

4. Trong hộp thoại Định dạng Ô hiện ra, chỉ định màu tô sáng, phông chữ, đường viền ô, v.v. khi bạn cần, rồi bấm OK để lưu cài đặt.

Sau đó, bạn sẽ thấy các ô trùng lặp ngoại trừ các trường hợp đầu tiên trong cột đã chọn (hoặc các hàng dựa trên các bản sao trong cột được chỉ định) được đánh dấu. Xem ảnh chụp màn hình:

1.3 Tìm và đánh dấu các bản sao bằng các màu khác nhau

Khi chúng tôi đánh dấu các ô hoặc hàng trùng lặp với Định dạng có điều kiện tính năng, tất cả các bản sao được đánh dấu bằng cùng một màu. Tuy nhiên, nếu các chuỗi giá trị trùng lặp khác nhau được đánh dấu bằng các màu khác nhau, thì sẽ dễ đọc và nhận được sự khác biệt hơn. Tại đây, VBA có thể giúp bạn hoàn thành công việc đó trong Excel.

1. nhấn Khác + F11 các phím với nhau để mở cửa sổ Microsoft Visual Basic for Applications.

2. Trong cửa sổ, nhấp vào Chèn > Mô-đun, và sau đó dán mã bên dưới vào cửa sổ mô-đun mới.

VBA: Đánh dấu các ô trùng lặp bằng các màu khác nhau:

Sub HighlightDuplicatesInDifferentColors()
'Update by Extendoffice 20201013
Dim xURg, xRg, xFRg, xRgPre As Range
Dim xAddress As String
Dim xDt As Object
Dim xFNum, xCInt As Long
Dim xBol As Boolean
Dim xWs As Worksheet
Dim xSArr
Set xRg = Application.ActiveWindow.RangeSelection
If xRg.Count > 1 Then
xAddress = xRg.AddressLocal
Else
xAddress = xRg.Worksheet.UsedRange.AddressLocal
End If
On Error Resume Next
Set xURg = Application.InputBox("Select range:", "Kutools for Excel", xAddress, , , , , 8)
If xURg Is Nothing Then Exit Sub
Set xURg = Intersect(xURg.Worksheet.UsedRange, xURg)
Set xDt = CreateObject("scripting.dictionary")
Set xWs = xURg.Worksheet
xCInt = 5
xBol = Application.ScreenUpdating
Application.ScreenUpdating = False
For xFNum = 1 To xURg.Count
Set xFRg = xURg.Item(xFNum)
If Not IsError(xFRg) Then
If xFRg.Value <> "" Then
If xDt.exists(xFRg.Text) Then
xSArr = Split(xDt(xFRg.Text), ";")
If xSArr(1) = "Only" Then
xCInt = xCInt + 1
xSArr(1) = xCInt
Set xRgPre = xWs.Range(xSArr(0))
xRgPre.Interior.ColorIndex = xCInt
xDt(xFRg.Text) = xSArr(0) & ";" & xSArr(1)
End If
xFRg.Interior.ColorIndex = xSArr(1)

Else
xDt(xFRg.Text) = xFRg.Address & ";Only"
End If

End If
End If
Next
xURg.Worksheet.Active
xURg.Select
Application.ScreenUpdating = xBol
End Sub

3. nhấn F5 phím hoặc nhấp vào chạy biểu tượng để chạy VBA này.

4. Trong hộp thoại Kutools cho Excel hiện ra, hãy chọn cột nơi bạn sẽ đánh dấu các ô trùng lặp với các màu khác nhau và nhấp vào OK .



Sau đó, bạn sẽ thấy từng chuỗi giá trị trùng lặp được đánh dấu bằng một màu.

1.4 Tìm và đánh dấu các bản sao bằng phần bổ trợ của bên thứ ba

Trong phần này, một số công cụ dễ sử dụng do tiện ích của bên thứ ba cung cấp sẽ được khuyến nghị để nhanh chóng tìm, chọn, đánh dấu các ô hoặc hàng trùng lặp dựa trên các bản sao trong một cột một cách dễ dàng.

1.4.1 Tìm và đánh dấu các ô trùng lặp trong một cột

Công cụ đầu tiên tôi sẽ giới thiệu là Chọn các ô trùng lặp & duy nhất tính năng, được cung cấp bởi Kutools cho Excel. Tính năng này có thể nhanh chóng tìm ra các ô duy nhất hoặc trùng lặp một cách dễ dàng.

Kutools cho Excel - Được đóng gói với hơn 300 công cụ cần thiết cho Excel. Tận hưởng bản dùng thử MIỄN PHÍ 30 ngày đầy đủ tính năng mà không cần thẻ tín dụng! Tải ngay!

1. Chọn cột hoặc phạm vi nơi bạn muốn tìm và đánh dấu các ô trùng lặp.

2. Nhấp chuột Kutools > Chọn > Chọn các ô trùng lặp & duy nhất.

3. Trong hộp thoại Chọn các ô trùng lặp & duy nhất, hãy kiểm tra các tùy chọn khi bạn cần và nhấp vào Ok để kết thúc hoạt động.

Chú ý: Trong hộp thoại Chọn ô trùng lặp & duy nhất,
(1) Nếu bạn cần chọn hoặc đánh dấu tất cả các bản sao ngoại trừ các trường hợp đầu tiên, hãy kiểm tra Bản sao (Trừ cái đầu tiên) Lựa chọn. Nếu không, hãy kiểm tra Tất cả các bản sao (Bao gồm cả bản đầu tiên) tùy chọn.
(2) Nếu bạn cần đánh dấu các bản sao, hãy đánh dấu vào Tô màu nền và chỉ định màu đánh dấu khi bạn cần.
(3) Nếu bạn muốn chọn hoặc đánh dấu các hàng dựa trên các bản sao trong cột đã chọn, hãy đánh dấu vào Chọn toàn bộ hàng tùy chọn.
(4) Nếu bạn muốn chọn hoặc đánh dấu các giá trị trùng lặp với các trường hợp phù hợp, hãy đánh dấu vào Trường hợp nhạy cảm tùy chọn.

1.4.2 Tìm và đánh dấu các ô trùng lặp trong hai cột hoặc trang tính

Kutools cho Excel cũng cung cấp một công cụ tuyệt vời - So sánh các ô, để giúp chúng tôi dễ dàng tìm và đánh dấu các ô trùng lặp trong hai cột.

Kutools cho Excel - Được đóng gói với hơn 300 công cụ cần thiết cho Excel. Tận hưởng bản dùng thử MIỄN PHÍ 30 ngày đầy đủ tính năng mà không cần thẻ tín dụng! Tải ngay!

1. Nhấp chuột Kutools > So sánh các ô để mở hộp thoại Chọn ô giống & khác nhau.

2. Trong hộp thoại Chọn các ô giống nhau & khác nhau, chỉ định hai cột trong Tìm giá trị trongTheo hộp, kiểm tra Các giá trị giống nhau và đánh dấu các tùy chọn khác khi bạn cần.

Ghi chú:
(1) Nếu bạn cần tìm các hàng trùng lặp, hãy kiểm tra Từng hàng Lựa chọn; và để tìm các ô trùng lặp, hãy kiểm tra Đơn bào tùy chọn trong Dựa trên phần;
(2) Đánh dấu vào Tô màu nền tùy chọn và chỉ định màu đánh dấu nếu bạn cần đánh dấu các hàng hoặc ô trùng lặp;
(3) Đánh dấu vào Đã chọn toàn bộ hàng tùy chọn nếu bạn cần chọn hoặc đánh dấu toàn bộ hàng dựa trên các bản sao;
(4) Đánh dấu vào Trường hợp nhạy cảm nếu bạn muốn tìm hoặc đánh dấu các bản sao phân biệt chữ hoa chữ thường.

3. Nhấp chuột Ok liên tiếp để hoàn tất cài đặt.

Sau đó, bạn sẽ thấy các bản sao trong cột mà bạn đã chỉ định trong Tìm giá trị trong hộp được xác định và đánh dấu.


2. Lọc các bản sao

Đôi khi, các bản sao xảy ra trong một cột và chúng tôi chỉ muốn xem các bản ghi liên quan đến dữ liệu trùng lặp. Vì vậy, trong phần này, tôi sẽ giới thiệu hai giải pháp để lọc ra dữ liệu trùng lặp.

2.1 Lọc các bản sao với định dạng có điều kiện

Phương pháp này sẽ hướng dẫn bạn xác định và đánh dấu các ô trùng lặp theo quy tắc định dạng có điều kiện, sau đó lọc theo màu đánh dấu một cách dễ dàng trong Excel.

1. Áp dụng định dạng có điều kiện để tìm và đánh dấu các bản sao trong cột được chỉ định. (Bấm để xem cách làm)

2. Bấm để chọn tiêu đề cột của cột được chỉ định và bấm Ngày > Lọc.

3. Tiếp tục nhấp vào biểu tượng bộ lọc  trong tiêu đề cột và chọn Lọc theo màu, rồi chọn màu định dạng có điều kiện được chỉ định trong danh sách thả xuống. Xem ảnh chụp màn hình:

Sau đó, bạn sẽ chỉ thấy các hàng có ô trùng lặp được lọc ra. Xem ảnh chụp màn hình:

2.2 Lọc các bản sao bằng cột trợ giúp

Ngoài ra, chúng tôi cũng có thể xác định các bản sao bằng công thức trong cột trợ giúp, sau đó lọc các bản sao bằng cột trợ giúp một cách dễ dàng trong Excel.

1. Bên cạnh dữ liệu gốc, hãy thêm một cột trợ giúp và nhập Bản sao làm tiêu đề cột.

2. Chọn ô trống đầu tiên dưới tiêu đề cột, nhập công thức = IF (COUNTIF ($ C $ 3: $ C $ 12, C3)> 1, "Trùng lặp", "")và kéo ô điều khiển Tự động điền của ô này để sao chép công thức này sang các ô khác.

Chú ý: Trong công thức trên, $ C $ 3: $ C $ 12 là cột chứa dữ liệu trùng lặp và C3 là ô đầu tiên (ngoại trừ ô tiêu đề) trong cột.

3. Nhấp để chọn tiêu đề cột - Bản sao và nhấp vào Ngày > Lọc.

4. Sau đó nhấp vào biểu tượng bộ lọc  trong tiêu đề cột, chỉ đánh dấu Bản sao, và nhấp vào OK cái nút. Xem ảnh chụp màn hình:

Cho đến nay, các hàng có giá trị trùng lặp chỉ được lọc ra. Xem ảnh chụp màn hình:


3. Đếm các bản sao

Phần này sẽ hướng dẫn bạn đếm số lượng giá trị trùng lặp trong Excel. Nó sẽ giới thiệu các phương pháp đếm các bản sao với các tiêu chí, đếm tổng số các bản sao, đếm các bản sao chỉ một lần và đếm từng giá trị trùng lặp hàng loạt, v.v.

3.1 Đếm các giá trị trùng lặp với các tiêu chí

Nói chung, chúng ta có thể áp dụng = COUNTIF (phạm vi, tiêu chí) để đếm tổng số của một giá trị nhất định xuất hiện trong phạm vi được chỉ định. Nói đếm số lần “Quả táo” xuất hiện trong danh sách A2: A10, Chúng ta có thể áp dụng công thức = COUNTIF (A2: A10, "Quả táo") để đếm số lượng giá trị trùng lặp này.

Tuy nhiên, công thức = COUNTIF (phạm vi, tiêu chí) chỉ tính giá trị trùng lặp được chỉ định. Làm thế nào về việc đếm giá trị trùng lặp với hai hoặc nhiều tiêu chí? Và điều gì sẽ xảy ra nếu đếm các bản sao có phân biệt chữ hoa chữ thường với các tiêu chí? Các phương pháp dưới đây có thể giúp bạn giải quyết những vấn đề này.

3.1.1 Đếm các bản sao có phân biệt chữ hoa chữ thường với các tiêu chí

Chúng ta có thể áp dụng công thức mảng để đếm các giá trị trùng lặp phân biệt chữ hoa chữ thường với các tiêu chí trong Excel. Ví dụ: để đếm số lần giá trị "Apple" xuất hiện trong danh sách B2: B21 với các trường hợp phù hợp, bạn có thể thực hiện như sau:

1. Chọn một ô trống.

2. Nhập công thức = SUM (- EXACT (B2: B20, E2)).

3. nhấn Ctrl + sự thay đổi + đăng ký hạng mục thi để trả về kết quả đếm.

Chú ý: Trong công thức mảng,
(1) B2: B20 là cột mà bạn sẽ đếm các bản sao trong đó.
(2) E2 là ô chứa giá trị được chỉ định mà bạn muốn đếm số lần xuất hiện. Bạn có thể thay đổi tham chiếu ô thành giá trị bằng dấu ngoặc kép, nói "Táo".

3.1.2 Đếm các bản sao với nhiều tiêu chí

Đôi khi, bạn có thể muốn đếm các bản sao với hai hoặc nhiều tiêu chí. Bạn có thể áp dụng QUẬN để hoàn thành công việc.
Ví dụ, có một bảng bán trái cây như hình ảnh chụp màn hình bên dưới. Ở đây chúng ta cần đếm số lần lặp lại của quả táo, được bán vào ngày 7/5/2020 và số lượng bán hàng lớn hơn 300. Bạn có thể đếm số lần lặp lại với các tiêu chí sau:

1. Chọn một ô trống.

2. Nhập công thức =COUNTIFS(B3:B20,G4,C3:C20,G3,D3:D20,">300").

3. Nhấn nút đăng ký hạng mục thi phím để lấy kết quả đếm.

Chú ý: Trong công thức trên,
(1) B3: B20 là cột ngày (đầu tiên) và G4 là tiêu chí ngày tháng;
(2) C3: C20 là cột trái cây (thứ hai) và G3 là tiêu chí trái cây;
(3) D3: D20 là cột số tiền (thứ ba) và "> 300" là tiêu chí số lượng.
(4) Nếu có nhiều cột và tiêu chí hơn trong bảng của bạn, bạn có thể thêm tham chiếu cột và tiêu chí.

3.2 Đếm tổng số bản sao trong một cột

Giả sử có một chuỗi giá trị trong một cột, bạn muốn đếm tổng số các giá trị trùng lặp trong danh sách, bạn có thể xử lý như thế nào? Tại đây, phần này sẽ chỉ cho bạn hướng dẫn đếm tổng số giá trị trùng lặp trong một cột trong Excel.

3.2.1 Đếm các bản sao trong một cột trừ lần xuất hiện đầu tiên

Để đếm tất cả các bản sao trong một cột ngoại trừ những lần xuất hiện đầu tiên, vui lòng thực hiện như sau:

1. Chọn một ô trống bên cạnh cột.

2. Nhập công thức = IF (COUNTIF ($ B $ 3: B3, B3)> 1, "CÓ", ""), rồi kéo tay cầm Tự động điền xuống để sao chép công thức này sang các ô khác.

Chú ý: Trong công thức trên,
(1) $ B $ 3: B3 là phạm vi bạn đếm các bản sao trong đó. Trong $ B $ 3: B3, B3 sẽ tự động thay đổi khi bạn sao chép công thức sang các ô khác.
(2) B3 là ô đầu tiên trong cột được chỉ định.
(3) Công thức này sẽ trả về CÓ hoặc trống. YES cho biết giá trị tương ứng là trùng lặp, trong khi trống có nghĩa là duy nhất.

Sau đó, tất cả các bản sao trong cột được chỉ định sẽ được xác định. Chúng ta có thể đếm kết quả công thức để có được tổng số lần lặp lại.

3. Chọn một ô trống.

4. Nhập công thức = COUNTIF (C3: C16, "CÓ")và nhấn đăng ký hạng mục thi Chìa khóa.

Chú ý: Trong công thức trên,
(1) C3: C16 là phạm vi mà chúng tôi đã áp dụng công thức để xác định các bản sao ở bước trước.
(2) là giá trị được trả về bởi công thức cuối cùng.

Sau đó, chúng tôi nhận được tổng số giá trị trùng lặp trong cột được chỉ định. Tổng số bản sao không bao gồm các lần xuất hiện đầu tiên.

3.2.2 Đếm các bản sao trong một cột bao gồm cả lần xuất hiện đầu tiên

Để đếm số lượng tất cả các bản sao bao gồm cả những lần xuất hiện đầu tiên trong Excel, bạn có thể áp dụng công thức mảng để đạt được nó.

1. Chọn một ô trống.

2. Nhập công thức =ROWS(B3:B16)-SUM(IF(COUNTIF(B3:B16,B3:B16) =1,1,0)).

3. nhấn Ctrl + sự thay đổi + đăng ký hạng mục thi để trả về kết quả đếm.

Chú ý: Trong công thức trên, B3: B16 là cột được chỉ định mà chúng tôi muốn đếm các bản sao bao gồm cả những lần xuất hiện đầu tiên bên trong.

3.2.3 Đếm các bản sao trong một cột bao gồm / loại trừ các lần xuất hiện đầu tiên

Để đơn giản hóa công việc của bạn và giảm bớt việc ghi nhớ các công thức dài nhàm chán, bạn có thể thử Chọn các ô trùng lặp & duy nhất tính năng, cung cấp bởi Kutools cho Excel, để nhanh chóng đếm số lượng giá trị trùng lặp trong danh sách hoặc cột được chỉ định loại trừ hoặc bao gồm các giá trị xuất hiện đầu tiên.

Kutools cho Excel - Được đóng gói với hơn 300 công cụ cần thiết cho Excel. Tận hưởng bản dùng thử MIỄN PHÍ 30 ngày đầy đủ tính năng mà không cần thẻ tín dụng! Tải ngay!

1. Chọn cột nơi bạn sẽ đếm số lượng giá trị trùng lặp và nhấp vào Kutools > Chọn > Chọn các ô trùng lặp & duy nhất.

2. Trong hộp thoại Chọn ô trùng lặp & duy nhất, hãy chọn Bản sao (Trừ cái đầu tiên) or Tất cả các bản sao (Bao gồm cả bản đầu tiên) tùy chọn khi bạn cần và nhấp vào Ok .

3. Sau đó, tất cả các giá trị trùng lặp bao gồm hoặc loại trừ các lần xuất hiện đầu tiên được chọn, đồng thời một hộp thoại xuất hiện và hiển thị số lượng ô đã được chọn. Xem ảnh chụp màn hình ở trên.

3.3 Đếm các bản sao trong hai cột

3.3.1 Đếm các bản sao giữa hai cột bằng công thức

Nói rằng bạn muốn so sánh hai danh sách tên và đếm số lượng trùng lặp giữa chúng, làm thế nào bạn có thể giải quyết vấn đề này một cách nhanh chóng? Chúng tôi cũng có thể thực hiện nó bằng một công thức trong Excel.

1. Chọn một ô trống.

2. Nhập công thức = SUMPRODUCT (- (ISNUMBER (MATCH (B3: B12, D3: D18,0)))).

3. Nhấn nút đăng ký hạng mục thi Chìa khóa.

Chú ý: Trong công thức trên,
(1) B3: B12 là cột tên đầu tiên bạn sẽ đếm các tên trùng lặp trong đó.
(2) D3: D18 là cột tên thứ hai mà bạn sẽ đếm các tên trùng lặp dựa trên.

3.3.2 Đếm các bản sao giữa hai cột bằng phần bổ trợ của bên thứ ba

Ngoài ra, chúng tôi có thể sử dụng tiện ích bổ sung của bên thứ ba, Kutools cho Excel, để đếm nhanh tổng số ô trùng lặp giữa hai cột một cách dễ dàng.

Kutools cho Excel - Được đóng gói với hơn 300 công cụ cần thiết cho Excel. Tận hưởng bản dùng thử MIỄN PHÍ 30 ngày đầy đủ tính năng mà không cần thẻ tín dụng! Tải ngay!

1. Nhấp chuột Kutools > Chọn > Chọn các ô giống nhau và khác nhau.

2. Trong hộp thoại Chọn ô giống & khác biệt,
(1) Chỉ định hai cột trong Tìm giá trị trongTheo hộp riêng biệt.
(2) Kiểm tra Đơn bào tùy chọn.
(3) Kiểm tra Các giá trị giống nhau tùy chọn.

4. Nhấn vào Ok .

Sau đó, tất cả các ô trùng lặp trong cột đầu tiên được chọn, đồng thời hộp thoại sẽ nhắc và hiển thị có bao nhiêu ô trùng lặp đã được chọn. Xem ảnh chụp màn hình:

Chú ý: Tính năng này sẽ đếm tổng số giá trị trùng lặp trong cột mà bạn đã chỉ định trong Tìm giá trị trong trong hộp thoại Chọn ô giống & khác nhau. Nếu bạn cần đếm tổng số giá trị trùng lặp trong cột thứ hai, hãy áp dụng Chọn các ô giống nhau và khác nhau làm nổi bật lại với việc chỉ định cột thứ hai trong Tìm giá trị trong cái hộp.

3.4 Chỉ đếm các bản sao một lần

Đôi khi, có các giá trị trùng lặp trong cột. Khi chúng ta đếm các giá trị trong cột, chúng ta cần đếm các bản sao một lần. Lấy một ví dụ, có một loạt các giá trị A, A, B, C, C, C, D, E, E và chúng ta yêu cầu đếm các giá trị và nhận được 5 (A, B, C, D, E). Ở đây, phần này sẽ giới thiệu hai công thức để giải bài toán này.

3.4.1 Đếm từng giá trị trùng lặp một lần với công thức

Bạn có thể nhanh chóng đếm từng giá trị trùng lặp một lần với công thức như sau:

1. Chọn một ô trống.

2. Nhập công thức =SUMPRODUCT((C3:C19<>"")/COUNTIF(C3:C19,C3:C19&"")).

3. Nhấn nút đăng ký hạng mục thi phím để lấy kết quả đếm.

Chú ý: Trong công thức trên, C3: C19 là cột được chỉ định mà bạn muốn đếm từng giá trị trùng lặp một lần.

3.4.2 Đếm giá trị trùng lặp phân biệt chữ hoa chữ thường một lần với công thức mảng

Khi đếm danh sách, mỗi giá trị trùng lặp có thể được đếm một lần với các trường hợp phù hợp, bạn có thể áp dụng công thức mảng để thực hiện dễ dàng trong Excel.

1. Chọn một ô trống.

2. Nhập công thức mảng = SUM (IFERROR (1 / IF (C3: C19 <> "", FREQUENCY (IF (EXACT (C3: C19, TRANSPOSE) (C3: C19)), MATCH (ROW (C3: C19), ROW (C3: C19) ), ""), MATCH (ROW (C3: C19), ROW (C3: C19))), 0), 0)).

3. nhấn Ctrl + sự thay đổi + đăng ký hạng mục thi các phím với nhau để trả về kết quả đếm.

Chú ý: Trong công thức mảng ở trên, C3: C19 là cột được chỉ định nơi bạn sẽ đếm từng chuỗi các bản sao một lần với các trường hợp phù hợp.

3.4.3 Đếm từng giá trị trùng lặp một lần với phần bổ trợ của bên thứ ba

Nếu bạn có Kutools cho Excel được cài đặt, bạn cũng có thể áp dụng Đếm ô có giá trị duy nhất tính năng đếm nhanh từng chuỗi giá trị trùng lặp một lần trong Excel.

Kutools cho Excel - Được đóng gói với hơn 300 công cụ cần thiết cho Excel. Tận hưởng bản dùng thử MIỄN PHÍ 30 ngày đầy đủ tính năng mà không cần thẻ tín dụng! Tải ngay!

1. Chọn một ô trống.

2. Nhấp chuột Kutools > Công thức trợ giúp > Thống kê > Đếm các ô có giá trị duy nhất (bao gồm giá trị trùng lặp đầu tiên).

3. Trong hộp thoại Trình trợ giúp công thức, chỉ định cột bạn sẽ đếm các bản sao một lần trong Phạm vi và nhấp vào Ok .

Sau đó, kết quả đếm được điền vào ô đã chọn ngay lập tức.

3.5 Đếm từng giá trị trùng lặp trong một cột

Nói chung, chúng ta có thể sử dụng COUNTIF hàm đếm từng giá trị trùng lặp và lặp lại các thao tác để đếm từng giá trị trùng lặp khác. Tuy nhiên, giải pháp này sẽ lãng phí rất nhiều thời gian cho nhiều bản sao. Ở đây, phần này sẽ giới thiệu ba giải pháp để nhanh chóng hoàn thành công việc này một cách dễ dàng trong Excel.

3.5.1 Đếm từng giá trị trùng lặp trong một cột bằng hàm SUBTOTAL

Chúng tôi có thể áp dụng Subtotal tính năng đếm từng chuỗi giá trị trùng lặp trong một cột trong Excel.

1. Chọn cột mà bạn sẽ đếm từng chuỗi giá trị trùng lặp bên trong và nhấp vào Ngày > Sắp xếp A đến Z or Sắp xếp từ Z đến A.

2. Trong hộp thoại Cảnh báo Sắp xếp hiện ra, hãy chọn Mở rộng sự lựa chọn và nhấp vào Sắp xếp .

Sau đó, bạn sẽ thấy lựa chọn đã được sắp xếp theo các giá trị trùng lặp của cột được chỉ định.

3. Giữ lựa chọn được chọn và nhấp vào Ngày > Subtotal.

4. Trong hộp thoại Tổng phụ,
(1) Chọn cột được chỉ định từ Tại mỗi thay đổi trong danh sách thả xuống;
(2) Chọn Đếm từ sử dụng chức năng danh sách thả xuống;
(3) Chỉ đánh dấu vào cột được chỉ định trong Thêm tổng phụ vào hộp danh sách;
(4) Nhấp vào OK .

Sau đó, bạn sẽ thấy từng chuỗi giá trị trùng lặp được đếm và kết quả đếm được thêm vào bên dưới mỗi chuỗi giá trị trùng lặp, xem ảnh chụp màn hình ở trên.

3.5.2 Đếm từng giá trị trùng lặp trong một cột bằng PivotTable

Chúng ta cũng có thể tạo một bảng tổng hợp để đếm nhanh từng chuỗi giá trị trùng lặp một cách dễ dàng trong Excel.

1. Chọn phạm vi chứa cột được chỉ định và nhấp vào Chèn > bảng tổng hợp.

2. Trong hộp thoại Tạo PivotTable, chỉ định vị trí bạn sẽ định vị bảng tổng hợp mới và bấm vào OK .

3. Trong ngăn Trường PivotTable, hãy kéo cột được chỉ định vào cả hai HàngCác giá trị các phần. Sau đó, bạn sẽ thấy từng chuỗi giá trị trùng lặp trong cột được chỉ định được tính hàng loạt. Xem ảnh chụp màn hình:

3.5.3 Đếm từng giá trị trùng lặp trong một cột bằng một công cụ tuyệt vời

Nếu bạn đã cài đặt Kutools cho Excel rồi, bạn có thể áp dụng nó dễ sử dụng Các hàng kết hợp nâng cao tính năng đếm nhanh từng chuỗi giá trị trùng lặp trong cột được chỉ định.

Kutools cho Excel - Được đóng gói với hơn 300 công cụ cần thiết cho Excel. Tận hưởng bản dùng thử MIỄN PHÍ 30 ngày đầy đủ tính năng mà không cần thẻ tín dụng! Tải ngay!

Chú ý: Các Các hàng kết hợp nâng cao tính năng sẽ sửa đổi phạm vi đã chọn và xóa các hàng dựa trên các giá trị trùng lặp trong cột khóa chính được chỉ định. Để lưu dữ liệu của bạn, bạn nên sao lưu hoặc sao chép dữ liệu của mình sang nơi khác trước khi thực hiện các thao tác dưới đây.

1. Thêm một cột trống ngay vào phạm vi dữ liệu ban đầu và đặt tên cho cột mới là Đếm.

2. Chọn cùng nhau phạm vi dữ liệu gốc và cột mới, rồi nhấp vào Kutools > Hợp nhất & Tách > Các hàng kết hợp nâng cao.

3. Trong hộp thoại Các hàng Kết hợp Nâng cao,
(1) Nhấp để chọn cột được chỉ định mà bạn sẽ đếm từng chuỗi giá trị trùng lặp và nhấp vào Khóa chính.
(2) Bấm để chọn cột mới (Đếm), rồi bấm Tính toán > Đếm.
(3) Chỉ định kết hợp hoặc kiểu tính toán cho các cột khác nếu cần.
(4) Nhấp vào Ok .

Sau đó, bạn sẽ thấy mỗi chuỗi giá trị trùng lặp trong cột được chỉ định được tính hàng loạt. Xem ảnh chụp màn hình:

3.6 Đếm các bản sao theo thứ tự

Cho biết có một danh sách các loại trái cây trong một cột. Trong danh sách, một số loại trái cây xuất hiện nhiều lần. Bây giờ bạn cần đánh dấu từng trái cây trùng lặp theo thứ tự xuất hiện, bạn có thể giải quyết nó như thế nào? Ở đây, phần này sẽ giới thiệu một công thức để thực hiện nó một cách dễ dàng trong Excel.

1. Thêm một cột trống ngay vào dữ liệu ban đầu.

2. Nhập công thức =IF(COUNTIF($C$3:$C$14,C3)>1,COUNTIF(C$3:C3,C3),"") trong ô đầu tiên của cột trống đã thêm.

3. Kéo tay cầm Tự động điền của ô công thức này để sao chép công thức sang các ô khác.

Chú ý: Trong công thức trên,
(1) $ C $ 3: $ C $ 14 là cột được chỉ định mà bạn muốn đếm các giá trị trùng lặp theo thứ tự.
(2) C3 là ô đầu tiên trong cột được chỉ định.
(3) Nếu giá trị tương ứng là trùng lặp, công thức này sẽ trả về số thứ tự 1, 2, 3… dựa trên thứ tự xuất hiện; nếu giá trị tương ứng là duy nhất, công thức này sẽ trả về trống.


4. Xóa các bản sao

Khi một số giá trị trùng lặp xếp chồng lên nhau trong một cột hoặc một dải ô, một số người dùng tìm kiếm các cách dễ dàng để nhanh chóng loại bỏ các giá trị trùng lặp. Ở đây, phần này sẽ giới thiệu nhiều giải pháp để xóa các giá trị trùng lặp một cách dễ dàng trong Excel.

4.1 Xóa các bản sao ngoại trừ một trong một cột

Phần này sẽ chỉ cho bạn hướng dẫn để nhanh chóng loại bỏ các giá trị trùng lặp ngoại trừ lần xuất hiện đầu tiên khỏi một cột hoặc danh sách trong Excel.

4.1.1 Xóa các bản sao ngoại trừ một bản sao với tính năng Remove Duplicates

Bạn có thể áp dụng Loại bỏ các bản sao tính năng loại bỏ trực tiếp tất cả các giá trị trùng lặp ngoại trừ những lần xuất hiện đầu tiên.

1. Chọn cột mà bạn muốn xóa tất cả các giá trị trùng lặp ngoại trừ những lần xuất hiện đầu tiên.

2. Nhấp chuột Ngày > Loại bỏ các bản sao.

3. Trong hộp thoại Xóa cảnh báo trùng lặp, hãy chọn Tiếp tục với lựa chọn hiện tại và nhấp vào Loại bỏ các bản sao .

Lời khuyên: Để loại bỏ các hàng dựa trên các giá trị trùng lặp trong vùng chọn, hãy chọn Mở rộng sự lựa chọn tùy chọn.

4. Trong hộp thoại Xóa bản sao, chỉ kiểm tra cột được chỉ định và nhấp vào OK .

Lời khuyên: Nếu bạn đã kiểm tra Mở rộng sự lựa chọn ở bước cuối cùng, tất cả các cột sẽ được liệt kê ở đây. Mặc dù vậy, bạn chỉ cần kiểm tra cột được chỉ định.

5. Sau đó, một hộp thoại nhắc nhở và hiển thị có bao nhiêu giá trị trùng lặp đã được loại bỏ. Nhấn vào OK để đóng nó.

4.1.2 Xóa các bản sao ngoại trừ một bản sao bằng tính năng Bộ lọc nâng cao

Bạn cũng có thể áp dụng Bộ lọc nâng cao tính năng loại bỏ tất cả các giá trị trùng lặp khỏi cột được chỉ định một cách dễ dàng.

1. Nhấp chuột Ngày > Nâng cao.

2. Trong hộp thoại Bộ lọc nâng cao,
(1) Kiểm tra Sao chép sang vị trí khác Tùy chọn;
(2) Trong Danh sách phạm vi , chọn cột được chỉ định mà bạn sẽ loại bỏ các giá trị trùng lặp;
(3) Trong Sao chép đến , chỉ định phạm vi bạn sẽ dán cột vào;
(4) Đánh dấu vào Chỉ các bản ghi duy nhất tùy chọn.
(5) Nhấp vào OK .

Sau đó, bạn sẽ thấy cột được chỉ định được dán vào phạm vi đã chỉ định với tất cả các giá trị trùng lặp đã được loại bỏ ngoại trừ các lần xuất hiện đầu tiên. Xem ảnh chụp màn hình:

4.1.3 Xóa các bản sao ngoại trừ một bản sao bằng VBA

Bạn cũng có thể áp dụng VBA để nhanh chóng loại bỏ các giá trị trùng lặp ngoại trừ các lần xuất hiện đầu tiên khỏi một cột trong Excel.

1. nhấn Khác + F11 để mở cửa sổ Microsoft Visual Basic for Application.

2. Nhấp chuột Chèn > Mô-đun, rồi dán mã VBA bên dưới vào cửa sổ mô-đun mới.

VBA: Loại bỏ các giá trị trùng lặp ngoại trừ những lần xuất hiện đầu tiên

Sub ExtendOffice_RemoveAllDeplicate () Dim xRg As Range Dim xURg, xFRg, xFFRg As Range Dim xI, xFNum, xFFNum As Integer Dim xDc As Object Dim xDc_keys Dim xBol As Boolean Dim xStr As String Dim xWs As Worksheet Dim xURgAddress As Next String On xRg = Application.InputBox ("Chọn phạm vi:", "Kutools cho Excel", "",,,, 8) Nếu xRg Không có gì thì Thoát Bộ phụ xURg = Intersect (xRg.Worksheet.UsedRange, xRg) Đặt xWs = xURg.Worksheet Set xDc = CreateObject ("scripting.dictionary") xURgAddress = xURg.Address xBol = Application.ScreenUpdating Application.ScreenUpdating = False For xFNum = 1 To xURg.Count Set xFRg = xURg.Item (xFNrror) If (Not IsError) (xFRg)) Sau đó Nếu xFRg.Value <> "" Và (Không phải IsError (xFRg)) Thì Đối với xFFNum = xFNum + 1 Đến xURg. Số lượng Đặt xFFRg = xURg.Item (xFFNum) Nếu Không phải Lỗi (xFFRg) Thì Nếu xFFRg .Value = xFRg.Value Sau đó xDc (xFFRg.Address) = "" Kết thúc Nếu Kết thúc Nếu Tiếp theo Kết thúc Nếu Kết thúc Nếu Tiếp theo xStr = "" xDc_keys = xDc.Keys Đối với xI = 1 Đến UBound (xDc_keys) Nếu xStr = "" Sau đó xStr = xDc_keys (xI) ĐặtxURg = xWs.Range (xStr) Khác xStr = xStr & "," & xDc_keys (xI) Đặt xURg = Application.Union (xWs.Range (xDc_keys (xI)), xURg) End If Next Debug. In xStr xWs.Activate xURg.Select Selection.Delete Shift: = xlUp xWs.Range (xURgAddress) .Select Application.ScreenUpdating = xBol End Sub

3. nhấn F5 phím hoặc nhấp vào chạy để chạy VBA này.

4. Trong hộp thoại bật ra, chỉ định phạm vi bạn sẽ xóa các giá trị trùng lặp và nhấp vào OK .

Sau đó, tất cả các giá trị trùng lặp ngoại trừ các lần xuất hiện đầu tiên sẽ bị xóa ngay lập tức khỏi phạm vi được chỉ định.

Lưu ý: Mã VBA này phân biệt chữ hoa chữ thường.

4.2 Xóa các bản sao và bản gốc

Nói chung, chúng tôi thường tìm ra các giá trị trùng lặp và loại bỏ các giá trị trùng lặp ngoại trừ những lần xuất hiện đầu tiên. Tuy nhiên, trong một số trường hợp, một số người dùng muốn loại bỏ tất cả các giá trị trùng lặp bao gồm cả các giá trị gốc. Và phần này mang đến một số giải pháp để đối phó với vấn đề này.

4.2.1 Xóa tất cả các bản sao và giá trị gốc bằng Định dạng có Điều kiện

Chúng tôi có thể đánh dấu tất cả các giá trị trùng lặp bao gồm các lần xuất hiện đầu tiên trong cột hoặc danh sách bằng quy tắc định dạng có điều kiện, sau đó lọc ra tất cả các giá trị trùng lặp bằng màu tô sáng. Sau đó, chúng ta có thể chọn tất cả các ô trùng lặp đã lọc ra rồi loại bỏ hàng loạt.

1. Áp dụng định dạng có điều kiện để đánh dấu các giá trị trùng lặp. (Bấm để xem cách làm)

2. Chọn cột mà bạn sẽ xóa các giá trị trùng lặp (bao gồm cả những lần xuất hiện đầu tiên) và nhấp vào Ngày > Lọc.

3. Nhấp vào biểu tượng Bộ lọc  trong tiêu đề cột của cột được chỉ định. Trong menu thả xuống, hãy chọn Lọc theo màu, và sau đó chỉ định màu tô sáng từ menu con.

Sau đó, tất cả các giá trị trùng lặp được lọc ra.

4. Chọn tất cả các ô đã lọc, nhấp chuột phải và chọn Xóa hàng từ menu ngữ cảnh. Và trong hộp thoại xác nhận lại bật ra, hãy nhấp vào OK để tiếp tục.

5. Sau đó, tất cả các giá trị trùng lặp được loại bỏ hàng loạt. Giữ danh sách đã lọc được chọn và nhấp vào Lọc > Ngày một lần nữa để hủy bộ lọc.

Cho đến bây giờ, bạn sẽ thấy tất cả các ô trùng lặp bao gồm cả các ô lần đầu tiên được xóa hàng loạt và chỉ còn lại các giá trị duy nhất.

Chú ý: Phương pháp này sẽ loại bỏ các hàng dựa trên các giá trị trùng lặp trong cột được chỉ định.

4.2.2 Xóa tất cả các bản sao và các giá trị gốc bằng cột trợ giúp

Chúng tôi cũng có thể áp dụng công thức để xác định các giá trị trùng lặp bao gồm các lần xuất hiện đầu tiên trong cột trợ giúp, sau đó lọc ra các giá trị trùng lặp theo kết quả công thức và cuối cùng loại bỏ hàng loạt các giá trị trùng lặp đã lọc ra.

1. Thêm cột trợ giúp bên cạnh cột được chỉ định, nhập công thức = COUNTIF ($ B $ 3: $ B $ 11, B3) vào ô đầu tiên của cột trợ giúp, sau đó kéo ô điều khiển Tự động điền xuống để sao chép công thức này sang các ô khác. Xem ảnh chụp màn hình:

Chú thích: Trong công thức trên, $ B $ 3: $ B $ 11 là cột được chỉ định mà bạn sẽ xóa các giá trị trùng lặp, và B3 là ô đầu tiên trong cột được chỉ định.

2. Chọn cột trợ giúp và nhấp vào Ngày > Lọc.

3. Nhấp vào biểu tượng bộ lọc  trong tiêu đề cột của trình trợ giúp, sau đó trong menu thả xuống, hãy kiểm tra tất cả các giá trị ngoại trừ 1, và nhấp vào OK cái nút. Xem ảnh chụp màn hình:

4. Bây giờ tất cả các giá trị trùng lặp đều được lọc ra. Chọn các ô đã lọc ra trong cột trợ giúp, nhấp chuột phải và chọn Xóa hàng trong trình đơn ngữ cảnh.

5. Trong hộp thoại xác nhận lại bật ra, hãy nhấp vào OK để tiếp tục.

6. Giờ đây, tất cả các giá trị trùng lặp và các hàng của chúng sẽ bị loại bỏ hàng loạt. Tiếp tục nhấp vào Ngày > Lọc một lần nữa để hủy bộ lọc.

Sau đó, bạn sẽ thấy tất cả các giá trị trùng lặp bao gồm cả những lần xuất hiện đầu tiên đều bị xóa hàng loạt.

4.2.3 Xóa tất cả các bản sao và giá trị gốc bằng một công cụ tuyệt vời

Nếu bạn có Kutools cho Excel được cài đặt, bạn cũng có thể áp dụng Chọn các ô trùng lặp & duy nhất tính năng nhanh chóng chọn và xóa các giá trị trùng lặp bao gồm hoặc loại trừ các giá trị xuất hiện đầu tiên một cách dễ dàng trong Excel.

Kutools cho Excel - Được đóng gói với hơn 300 công cụ cần thiết cho Excel. Tận hưởng bản dùng thử MIỄN PHÍ 30 ngày đầy đủ tính năng mà không cần thẻ tín dụng! Tải ngay!

1. Chọn cột bạn sẽ xóa các bản sao.

2. Nhấp chuột Kutools > Chọn > Chọn các ô trùng lặp & duy nhất.

3. Trong hộp thoại Chọn ô trùng lặp & duy nhất, hãy chọn Tất cả các bản sao (Bao gồm cả bản đầu tiên) và nhấp vào Ok .

Ghi chú:
(1) Để chọn và xóa các giá trị trùng lặp không bao gồm các lần xuất hiện đầu tiên, hãy kiểm tra Bản sao (Trừ cái đầu tiên) tùy chọn.
(2) Để chọn và xóa các hàng dựa trên các giá trị trùng lặp trong cột được chỉ định, hãy chọn Chọn toàn bộ hàng tùy chọn.
(3) Để chọn và xóa các giá trị trùng lặp với các trường hợp phù hợp, hãy chọn Trường hợp nhạy cảm tùy chọn.
(4) Để chọn, đánh dấu và xóa các ô hoặc hàng trùng lặp, hãy chọn Tô màu nền or Tô màu phông chữ tùy chọn và chỉ định màu tô hoặc màu phông chữ khi bạn cần.

4. Sau đó, một hộp thoại nhắc và hiển thị bao nhiêu ô đã được chọn, hãy nhấp vào OK để đóng nó.

5. Nhấp chuột phải vào các ô đã chọn và chọn Xóa bỏ từ trình đơn ngữ cảnh.

6. Trong hộp thoại Xóa sắp tới, hãy chọn Chuyển ô lên trên và nhấp vào OK .

Cho đến nay, tất cả các giá trị trùng lặp bao gồm cả các giá trị xuất hiện đầu tiên đã bị loại bỏ hàng loạt.

4.3 Xóa các hàng dựa trên các bản sao trong một cột

Trong hầu hết các trường hợp, chúng tôi xác định các giá trị trùng lặp trong một cột, sau đó loại bỏ toàn bộ các hàng theo các giá trị trùng lặp. Thành thật mà nói, thao tác này khá giống với việc loại bỏ các giá trị trùng lặp khỏi một cột. Do đó, chúng tôi có thể sử dụng các giải pháp tương tự để xóa các hàng dựa trên các bản sao trong cột được chỉ định.

Phương pháp đầu tiên là áp dụng Loại bỏ các bản sao tính năng loại bỏ các hàng trùng lặp trong cột được chỉ định. Chỉ cần chọn phạm vi bạn sẽ loại bỏ các hàng, nhấp vào Ngày > Loại bỏ các bản sao để bật tính năng này, chỉ đánh dấu vào cột được chỉ định trong hộp thoại Loại bỏ các bản sao, sau đó bấm OK để kết thúc thao tác gỡ bỏ.

Chúng tôi cũng có thể áp dụng tính năng Định dạng và Lọc có Điều kiện để loại bỏ các hàng dựa trên các giá trị trùng lặp trong cột được chỉ định. Trước hết, đánh dấu các hàng dựa trên các giá trị trùng lặp trong một cột nhất định theo quy tắc hình thành có điều kiện (bấm để xem cách). Thứ hai, lọc phạm vi theo màu sắc. Thứ ba, xóa tất cả các hàng đã lọc một cách dễ dàng. Cuối cùng, hãy xóa hoặc hủy bộ lọc và bạn sẽ chỉ thấy các hàng có giá trị duy nhất trong cột được chỉ định còn lại.

Ngoài ra, bạn có thể thêm cột trợ giúp và áp dụng công thức = COUNTIF ($ C $ 3: $ C $ 21, C3) để xác định các bản sao trong cột được chỉ định. Sau đó, lọc ra các số lớn hơn 1 trong cột trợ giúp và dễ dàng loại bỏ tất cả các hàng đã lọc ra. Sau khi xóa bộ lọc, bạn sẽ thấy các hàng có giá trị duy nhất trong cột được chỉ định chỉ còn lại.

Phần bổ trợ của bên thứ ba Kutools cho Excel cũng mang đến một tính năng tiện dụng đặc biệt, Chọn các ô trùng lặp & duy nhất, để nhanh chóng chọn các hàng dựa trên các giá trị trùng lặp trong cột được chỉ định và sau đó bạn có thể nhanh chóng xóa các hàng đã chọn này bằng cách nhấp chuột phải vào menu một cách dễ dàng.

Kutools cho Excel's Các hàng kết hợp nâng cao tính năng cũng có thể nhanh chóng loại bỏ các hàng dựa trên các giá trị trùng lặp trong cột khóa chính được chỉ định.

4.4 Xóa các bản sao trong hai cột

Đôi khi, chúng ta cần so sánh hai danh sách hoặc cột, sau đó loại bỏ các bản sao giữa chúng trong Excel. Ở đây, phần này mang đến hai giải pháp cho bạn.

4.4.1 Xóa các bản sao trong hai cột bằng cột trợ giúp

Chúng ta có thể thêm cột trợ giúp và áp dụng công thức để xác định các giá trị trùng lặp giữa hai cột, sau đó lọc và xóa các giá trị trùng lặp một cách dễ dàng.

1. Thêm một cột trống bên cạnh cột được chỉ định mà bạn sẽ xóa các giá trị trùng lặp.

2. Trong ô đầu tiên của cột trợ giúp (ngoại trừ ô tiêu đề), hãy nhập công thức = IF (ISERROR (MATCH (C2, $ A $ 2: $ A $ 13,0)), "Duy nhất", "Trùng lặp"), rồi kéo tay cầm Tự động điền xuống để sao chép công thức sang các ô khác.

Chú ý: Trong công thức trên,
(1) C2 là ô đầu tiên trong cột được chỉ định mà bạn sẽ xóa các giá trị trùng lặp khỏi;
(2) $ A $ 2: $ A $ 13 là cột khác mà chúng ta cần so sánh.
(3) Công thức này sẽ trả về Bản sao nếu giá trị tương ứng trùng lặp với các giá trị trong cột khác và trả về Độc đáo nếu khác với các giá trị trong cột khác.

3. Chọn cột trợ giúp và nhấp vào Ngày > Lọc.

4. Nhấp vào biểu tượng bộ lọc  trong tiêu đề cột trợ giúp, sau đó trong menu thả xuống chỉ kiểm tra Bản sao, và nhấp vào OK .

5. Bây giờ tất cả các giá trị trùng lặp đều được lọc ra. Chọn các ô đã lọc, nhấp chuột phải và chọn Xóa hàng từ menu ngữ cảnh. Sau đó nhấp vào OK trong hộp thoại xác nhận lại bật lên.

6. Sau đó, tất cả các giá trị trùng lặp sẽ bị xóa khỏi cột được chỉ định. Tiếp tục nhấp vào Ngày > Lọc một lần nữa để hủy bộ lọc.

Sau đó, bạn sẽ chỉ thấy các giá trị duy nhất còn lại trong cột được chỉ định. Bạn có thể xóa cột trợ giúp khi cần.

Chú ý: Phương pháp này sẽ loại bỏ toàn bộ các hàng dựa trên các giá trị trùng lặp trong cột được chỉ định.

4.4.2 Xóa các bản sao trong hai cột bằng một công cụ tuyệt vời

Nếu bạn có Kutools cho Excel được cài đặt, bạn có thể sử dụng tuyệt vời của nó Chọn các ô giống nhau và khác nhau tính năng để nhanh chóng chọn các giá trị trùng lặp giữa hai cột và sau đó loại bỏ chúng một cách dễ dàng.

Kutools cho Excel - Được đóng gói với hơn 300 công cụ cần thiết cho Excel. Tận hưởng bản dùng thử MIỄN PHÍ 30 ngày đầy đủ tính năng mà không cần thẻ tín dụng! Tải ngay!

1. Nhấp chuột Kutools > Chọn > Chọn các ô giống nhau và khác nhau để kích hoạt tính năng này.

2. Trong hộp thoại Chọn các ô giống nhau & khác nhau, chỉ định cả hai cột trong Tìm giá trị trongTheo các hộp riêng biệt, kiểm tra Đơn bàoCác giá trị giống nhau tùy chọn và nhấp vào Ok cái nút. Xem ảnh chụp màn hình:

3. Sau đó, tất cả các giá trị trùng lặp số tiền hai cột được chọn trong cột đầu tiên (cột bạn đã chỉ định trong Tìm giá trị trong cái hộp). Và nhấp vào OK trong hộp thoại bật ra.

4. Bạn có thể nhấn Xóa bỏ để xóa các giá trị trùng lặp này trực tiếp hoặc nhấp chuột phải vào chúng và chọn Xóa bỏ từ trình đơn ngữ cảnh.


Các bài viết khác ...

Công cụ năng suất văn phòng tốt nhất

🤖 Trợ lý AI của Kutools: Cách mạng hóa việc phân tích dữ liệu dựa trên: Thực thi thông minh   |  Tạo mã  |  Tạo công thức tùy chỉnh  |  Phân tích dữ liệu và tạo biểu đồ  |  Gọi các hàm Kutools...
Các tính năng phổ biến: Tìm, đánh dấu hoặc xác định các bản sao   |  Xóa hàng trống   |  Kết hợp các cột hoặc ô mà không làm mất dữ liệu   |   Vòng không có công thức hữu ích. Cảm ơn !
Siêu tra cứu: Nhiều tiêu chí VLookup    VLookup Nhiều Giá Trị  |   VLookup trên nhiều trang tính   |   Tra cứu mờ ....
Danh sách thả xuống nâng cao: Tạo nhanh danh sách thả xuống   |  Danh sách thả xuống phụ thuộc   |  Danh sách thả xuống nhiều lựa chọn ....
Trình quản lý cột: Thêm một số cột cụ thể  |  Di chuyển cột  |  Chuyển đổi trạng thái hiển thị của các cột ẩn  |  So sánh dãy và cột hữu ích. Cảm ơn !
Các tính năng nổi bật: Tiêu điểm lưới   |  Chế độ xem thiết kế   |   Thanh công thức lớn    Trình quản lý sổ làm việc & trang tính   |  Thư viện tài nguyên (Văn bản tự động)   |  Bảng chọn ngày   |  Kết hợp các bảng tính   |  Mã hóa/Giải mã ô    Gửi email theo danh sách   |  Siêu lọc   |   Bộ lọc đặc biệt (lọc in đậm/nghiêng/gạch ngang...) ...
15 bộ công cụ hàng đầu12 bản văn CÔNG CỤ (thêm văn bản, Xóa ký tự,...)   |   50 + Biểu đồ Các loại (Biểu đồ Gantt,...)   |   40+ Thực tế Công thức (Tính tuổi dựa trên ngày sinh,...)   |   19 chèn CÔNG CỤ (Chèn mã QR, Chèn ảnh từ đường dẫn,...)   |   12 Chuyển đổi CÔNG CỤ (Số thành từ, Chuyển đổi tiền tệ,...)   |   7 Hợp nhất & Tách CÔNG CỤ (Các hàng kết hợp nâng cao, Chia ô,...)   |   ... và nhiều hơn nữa

Nâng cao kỹ năng Excel của bạn với Kutools for Excel và trải nghiệm hiệu quả hơn bao giờ hết. Kutools for Excel cung cấp hơn 300 tính năng nâng cao để tăng năng suất và tiết kiệm thời gian.  Bấm vào đây để có được tính năng bạn cần nhất...

Mô tả


Tab Office mang lại giao diện Tab cho Office và giúp công việc của bạn trở nên dễ dàng hơn nhiều

  • Cho phép chỉnh sửa và đọc theo thẻ trong Word, Excel, PowerPoint, Publisher, Access, Visio và Project.
  • Mở và tạo nhiều tài liệu trong các tab mới của cùng một cửa sổ, thay vì trong các cửa sổ mới.
  • Tăng 50% năng suất của bạn và giảm hàng trăm cú nhấp chuột cho bạn mỗi ngày!
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations