Bạn đang xem bài viết Hướng Dẫn So Sánh Hai Cột Dữ Liệu Trong Excel Để Xóa, Highlight, Tô Màu Dữ Liệu Trùng Lặp được cập nhật mới nhất trên website Hoisinhvienqnam.edu.vn. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất.
Excel là một ứng dụng rất mạnh và thực sự tuyệt vời để tạo và xử lý các mảng dữ liệu lớn. Bây giờ, bạn có rất nhiều bảng tính và dữ liệu, hoặc có thể chỉ là một bảng tính rất lớn, bạn muốn so sánh 2 cột để tìm các giá trị trùng lắp và thao tác trên những phần lặp được tìm thấy, ví dụ xóa hàng bị trùng, tô màu phần trùng hoặc xóa nội dung của ô bị trùng. Hai cột này có thể nằm trong một bảng, liền kề hoặc không liền kề, hoặc chúng có thể nằm trong 2 trang tính khác nhau, thậm chí là hai bảng tính khác nhau.
Giả sử bạn có 2 cột tên người – 5 tên trong cột A và 3 tên trong cột B. Bạn muốn so sánh dữ liệu giữa hai cột này để tìm các tên bị trùng lắp. Đây chỉ là ví dụ. Trong các bảng tính thực, dữ liệu để so sánh thường có hàng ngàn, hàng vạn mục.
Trường hợp A: Cả hai cột đều trong một trang tính, cùng một bảng dữ liệu: Cột A và cột B.
Trường hợp B: Hai cột được đặt trên hai trang tính khác nhau: cột A trong Sheet2 và cột A trong Sheet3.
SO SÁNH HAI CỘT ĐỂ TÌM GIÁ TRỊ TRÙNG BẰNG CÔNG THỨC:Trong ô trống đầu tiên, ô C1, nhập công thức: =IF(ISERROR(MATCH(A1,$B$1:$B$10000,0)),”Unique”,”Duplicate”)
Trường hợp 1: cả hai cột cùng một danh sách
Trong công thức, A1 là ô đầu tiên của cột đầu tiên mà chúng ta muốn sử dụng để so sánh. $B$1 và $B$10000 là địa chỉ của ô đầu tiên và cuối cùng của cột thứ hai mà bạn muốn so sánh. Hãy chú ý cố định tham chiếu ô – ký hiệu đô la ($) trước các ký tự cột và số hàng. Tôi sử dụng tham chiếu tuyệt đối nhằm giữ nguyên địa chỉ ô khi sao chép công thức.
Nếu bạn muốn tìm các giá trị trùng trong cột B, hoán đổi tên cột như sau: =IF(ISERROR(MATCH(B1,$A$1:$A$10000,0)),”Unique”,”Duplicate”)
Thay vì “Unique”/”Duplicate” (duy nhất/trùng lắp), bạn cũng có thể tự đặt nhãn cho mình như “Not found”/”Found” (Tìm thấy/Không tìm thấy) hoặc “Duplicate” và gõ “” thay vì “Unique”. Trong cách thứ hai, bạn sẽ có một ô trống kế bên ô mà giá trị trùng lắp không được tìm thấy.
Nhấn giữ chuột trái, kéo đường viền ô xuống để chọn tất cả các ô mà bạn muốn sao chép công thức vào. Khi tất cả các ô đã được chọn, thả chuột trái:
Tất cả các ô chứa giá trị trùng đều được đánh dấu là “Duplicate”.
Chú ý. Trong bảng lớn, có một cách nhanh hơn để sao chép công thức là sử dụng phím tắt. Nhấp vào ô C1 để chọn nó và nhấn (để sao chép công thức vào ), sau đó nhấn Ctrl + Shift + End (để chọn tất cả những ô chứa dữ liệu trong cột C), cuối cùng, nhấn (để dán công thức vào tất cả ô tính đã chọn).
Trong ô đầu tiên của cột trống đầu tiên trong Sheet2 (cột B), nhập công thức: =IF(ISERROR(MATCH(A1,Sheet3!$A$1:$A$10000,0)),””,”Duplicate”)
Trường hợp B: hai cột nằm trên hai trang tính (bảng tính) khác nhau.
Tương tự như bước trong trường hợp A.
Chúng ta có kết quả sau đây:
Sheet3! là tên trang tính có chứa cột thứ 2, $A$1:$A$10000 là địa chỉ của ô tính đầu tiên và ô tính cuối cùng trong cột thứ 2.
XỬ LÝ CÁC GIÁ TRỊ TRÙNG LẮP ĐƯỢC TÌM THẤY:Chúng ta đã tìm thấy những giá trị trong cột thứ nhất (cột A) cũng trùng với cột thứ hai (cột B). Bây giờ chúng ta cần làm vài thứ với chúng.
Thật không hiệu quả và tốn thời gian khi tìm kiếm trên toàn bộ bảng và xem xét từng giá trị trùng lắp một cách thủ công. Có nhiều cách thực hiện ưu việt hơn.
CHỈ HIỂN THỊ HÀNG BỊ TRÙNG TRONG CỘT A:Nhấp phải vào hàng được chọn và chọn từ danh sách tùy chọn:
Đặt tên cho cột, ví dụ “Name”, “Duplicate?”… Sau đó mở thẻ và nhấp chọn :
Sau khi nhấp chuột vào mũi tên nhỏ màu xám kế bên “Duplicate?” để mở danh sách tùy chọn, bỏ chọn tất cả các mục ngoại trừ trong danh sách đó, nhấn OK:
Như vậy, bạn chỉ thấy những ô tính trong cột A có chứa giá trị trùng lắp với cột B. Có 3 ô tính như vậy trong trang tính ví dụ.
TÔ MÀU HOẶC HIGHLIGHT NHỮNG GIÁ TRỊ TRÙNG TÌM THẤY:Ngoài gắn nhãn “Duplicate”, bạn cũng có thể đánh dấu các giá trị trùng bằng một cách khác như định dạng màu chữ hoặc bôi màu nó.
Lọc các giá trị trùng lặp như giải thích ở trên, chọn các ô tính đã lọc và nhấn để mở hộp thoại Format Cells. Ví dụ: hãy thay đổi màu nền của các hàng bị trùng sang màu vàng tươi. Tất nhiên, bạn có thể thay đổi màu nền của các ô này bằng cách sử dụng tùy chọn trên thẻ , nhưng ưu thế của hộp thoại Format Cells là nó cho phép bạn thực hiện tất cả các thay đổi định dạng cùng lúc.
Bây giờ, bạn sẽ không bỏ sót bất kỳ ô chứa giá trị trùng nào.
LOẠI BỎ GIÁ TRỊ TRÙNG LẶP TỪ CỘT ĐẦU TIÊN:Lọc bảng dữ liệu để hiển thị các giá trị trùng lặp, chọn tất cả những ô đó.
Nếu hai cột được so sánh nằm trên hai trang tính khác nhau, cụ thể là trong hai bảng dữ liệu riêng biệt, nhấp phải vào vùng dữ liệu được chọn và chọn từ danh sách tùy chọn.
Khi Excel yêu cầu bạn xác nhận bạn có thật sự muốn xóa toàn bộ hàng này không, nhấn OK và bỏ bộ lọc. Như vậy, chỉ những hàng chứa các giá trị duy nhất được hiển thị:
Lọc bảng để các giá trị trùng lặp được hiển thị và chọn tất cả những ô đó. Nhấp phải vào vùng được chọn và chọn Clear Contents.
Bỏ bộ lọc.
Chọn tất cả ô tính trong cột A bắt đầu từ ô A1 đến ô cuối cùng có chứa dữ liệu.
Di chuyển đến thẻ , nhấp chọn . Trong hộp thoại mở ra, chọn Continue with the current selection và nhấn
Xóa cột chứa công thức, chỉ để lại “Uniques”
Như vậy, cột A chỉ còn những dữ liệu không bị trùng lặp trong cột B.
Nếu hai cột nằm trên cùng một trang tính, hai cột liền kề hoặc không liền kề, việc xóa những giá trị trùng lắp sẽ phức tạp hơn. Chúng ta không thể xóa toàn bộ hàng chứa chúng bởi vì như vậy sẽ xóa những ô tương ứng trong cột 2. Vì vậy, để hiển thị những giá trị duy nhất trong cột A, thực hiện theo các bước sau:
Những kiến thức bạn đang xem thuộc khóa học Tuyệt đỉnh Excel của Gitiho Việt Nam. Khóa học này cung cấp cho bạn kiến thức một cách đầy đủ và có hệ thống về các hàm, các công cụ trong excel, ứng dụng excel trong công việc… Hiện nay hệ thống đang có nhiều ưu đãi khi bạn đăng ký tham gia khóa học này. Hãy tham gia ngay tại địa chỉ: Gitiho.com
Với sứ mệnh: ” Mang cơ hội phát triển kỹ năng, phát triển nghề nghiệp tới hàng triệu người “, đội ngũ phát triển đã và đang làm việc với những học viện, trung tâm đào tạo, các chuyên gia đầu ngành để nghiên cứu và xây dựng lên các chương trình đào tạo từ cơ bản đến chuyên sâu xung quanh các lĩnh vực: Tin học văn phòng, Phân tích dữ liệu, Thiết kế, Công nghệ thông tin, Kinh doanh, Marketing, Quản lý dự án…
50+ khách hàng doanh nghiệp lớn trong nhiều lĩnh vực như: Vietinbank, Vietcombank, BIDV, VP Bank, TH True Milk, VNPT, FPT Software, Samsung SDIV, Ajinomoto Việt Nam, Messer,…
Gitiho tự hào khi được đồng hành cùng:
Tìm Và Xóa Dữ Liệu Trùng Lặp Trong Excel
Trong quá trình làm việc với bảng tính Excel sẽ có những file Excel có khối lượng dữ liệu lớn nên việc trùng lặp dữ liệu rất hay xảy ra. Có những trường hợp dữ liệu trùng lặp là hữu ích, nhưng nhiều khi các giá trị trùng lặp không giúp ích cho các bạn mà nó còn làm rối dữ liệu trong bảng tính hơn. Vậy làm sao để tìm và loại bỏ các dữ liệu trùng lặp đó? Các bạn có thể sử dụng định dạng có điều kiện để tìm và tô màu nổi bật vùng dữ liệu trùng lặp hoặc sử dụng Visual Basic để tìm dữ liệu trùng lặp. Sau đó các bạn thực hiện xóa dữ liệu trùng lặp trong Excel.
TÌM DỮ LIỆU TRÙNG LẶP TRONG EXCEL Cách 1: Sử dụng Highlight Cells Rules trong Định dạng có điều kiệnBước 1: Chọn (bôi đen) vùng dữ liệu trên bảng tính Excel mà bạn muốn tìm dữ liệu trùng lặp.
Có 6 kiểu đánh dấu mà bạn có thể áp dụng để đánh dấu cho các giá trị trùng:
Light Red Fill with Dark Red Text: đổ nền đỏ nhạt với chữ màu đỏ đậm.
Yellow Fill with Dark Yellow Text: đổ nền màu vàng với chữ màu vàng đậm.
Green Fill with Dark Green Text: đổ nền màu xanh lá cây với chữ màu xanh đậm.
Light Red Fill: đổ nền màu đỏ nhạt.
Red Text: chữ màu đỏ.
Red Border: đường viền màu đỏ.
Sau khi đã chọn kiểu đánh dấu xong các bạn chọn OK để đánh dấu.
Dim ws As Worksheet Dim cell As Range Dim myrng As Range Dim clr As Long Dim lastcell As Range Dim lastrow As Long Set ws = ThisWorkbook.ActiveSheet 'Vung can danh dau gia tri trung nhau Set myrng = ws.Range("A1:C18") With myrng Set lastcell = .Cells(.Cells.Count) End With myrng.Interior.ColorIndex = xlNone clr = 3 For Each cell In myrng 'Kiem tra so gia tri trung nhau trong vung, neu co hai gia tri trung nhau tro len thi thuc hien 'Neu la o dau tien cua cac gia tri trung nhau trong vung If myrng.Find(what:=cell, lookat:=xlWhole, MatchCase:=False, after:=lastcell).Address = cell.Address Then 'Thiet lap mau cell.Interior.ColorIndex = clr Else 'Thiet lap mau tu o thu 2 voi cac gia tri trung nhau cell.Interior.ColorIndex = myrng.Find(what:=cell, lookat:=xlWhole, MatchCase:=False, after:=lastcell).Interior.ColorIndex End If End If NextLưu ý: Trong phần thiết lập vùng tìm giá trị trùng nhau, giá trị bôi đỏ A1:C18 các bạn thay bằng vùng dữ liệu cần tìm giá trị trùng nhau phù hợp với dữ liệu trong bảng tính Excel của bạn.
Sau khi đã tìm được các dữ liệu trùng lặp trong Excel, nếu các bạn muốn xóa dữ liệu trùng lặp thì các bạn có thể xóa thủ công dựa vào các vùng dữ liệu trùng lặp đã được đánh dấu. Hoặc các bạn có thể thực hiện các bước sau:
Bước 1: Chọn vùng dữ liệu cần xóa các giá trị trùng lặp.
Ví dụ cột Ngày công T2 có nhiều giá trị trùng nhưng chúng tôi không muốn xóa, vì vậy chúng tôi sẽ bỏ chọn trong cột Ngày công T2.
Cách Tìm Và Xóa Dữ Liệu Trùng Lặp Của Ô Trong Excel
Tôi nghĩ ai trong chúng ta cũng đều gặp phải việc nhận được kết quả kỳ lạ do dữ liệu trùng lặp trong dải ô. Điều này có thể dễ dàng khiến các hàm VLOOKUP cho kết quả sai hay khiến việc tính tổng bị sai. Đừng lo lắng vì có rất nhiều cách để kiểm tra và thậm chí là xóa hàng dữ liệu trùng lặp trong bảng tính Excel của bạn. Hãy thư giãn đi.
CÁCH 1: SỬ DỤNG CÔNG THỨCVới cách này, tôi sử dụng hàm COUNTIF để xác định liệu có 2 hay nhiều dữ liệu trong ô bị trùng lặp hay không. Nếu các dữ liệu trùng lặp được tìm thấy, thì các hàng đó được dán nhãn “trùng” (viết tắt của “trùng lặp”), nhưng bạn có thể khiến nhãn đó có bất kỳ từ nào bạn muốn.
CÁCH 2: SỬ DỤNG ĐỊNH DẠNG CÓ ĐIỀU KIỆNBạn có biết rằng bạn có thể thiết lập trước định dạng có điều kiện để đánh dấu trùng lặp? Trong một khoảng thời gian dài, tôi không hề biết gì về điều này và tôi vẫn còn nhớ rõ sự hoài nghi về phương pháp đơn giản này. Tôi không nghĩ rằng nó đã có sẵn từ rất lâu! Những gì bạn cần làm đó là đánh dấu dữ liệu trong ô rồi đi đến
Từ đó, bạn chỉ cần nhấn nút OK khi hộp thoại xuất hiện và bất cứ ô nào trùng lặp đều sẽ được đánh dấu bằng cách tô hết ô bằng màu đỏ. Có hàng ngàn hàng và bạn không muốn phải cuộn xuống từng ô để kiểm tra ô nào có màu đỏ? À thì bạn có thể dễ dàng lọc dữ liệu và xem xem liệu bạn có thể lọc các ô có màu đỏ hay không.
Đây là phương pháp rất tuyệt vời. Cách này có thể nhanh chóng dùng cho dữ liệu để xem xem có bất kỳ sự trùng lặp nào không. Tôi có thói quen sử dụng quá trình này để kiểm tra các cột chứng minh nhân dân của mình. Các cột này là kết quả của hàm VLOOKUP, nên tôi chắc rằng tôi có các giá trị đặc biệt trong cột.
Sau khi chạy mã VBA Macro này, bạn sẽ thấy một hộp tin nhắn nói rằng bạn có bao nhiều ô trùng lặp được tìm thấy và nó sẽ hỏi liệu bạn có muốn tô vàng các ô này. Đây sẽ là một sự bổ sung tuyệt vời cho Macro Workbook cá nhân của bạn (dĩ nhiên là tôi đã có mã này rồi!).
If Not rngFind Is Nothing Then ‘Record first instance found FirstAddress = rngFind.Address
‘Find any next instances of value Do Set rngFind = rng.FindNext(rngFind) If rngFind.Address = FirstAddress Then Exit Do DupAddresses = DupAddresses & rngFind.Address & “,” Loop
End Sub
Hãy lưu ý rằng mã này không đánh dấu ô đầu tiên có dữ liệu trùng lặp. Chỉ ô thứ hai trở đi mới được đánh dấu.
CÁCH 1: SỬ DỤNG NÚT REMOVE DUPLICATESBạn có biết rằng có một nút giúp xóa dữ liệu trùng lặp trên thanh Ribbon không? Đây là một tính năng thật sự tuyệt vời vì nó giúp bạn tiết kiệm rất nhiều thời gian. Những điều bạn cần làm đó là đi đến tab Data trên thanh Ribbon. Bạn có thể nhấp vào nút Remove Duplicates rồi điền vào hộp thoại để xóa các dữ liệu trùng lặp tùy vào nhu cầu của bạn.
Sub DeleteDuplicates()
Dim rng As Range Dim rngFind As Range Dim cell As Range Dim DupAddresses As String Dim SearchList As String Dim Delimiter As String
‘Setup Variables Set rng = Selection Delimiter = “-;;-“
Set rngFind = rng.Find(what:=cell.Value, LookIn:=xlValues, _ lookat:=xlWhole, searchdirection:=xlNext)
If Not rngFind Is Nothing Then ‘Record first instance found FirstAddress = rngFind.Address
‘Find any next instances of value Do Set rngFind = rng.FindNext(rngFind) If rngFind.Address = FirstAddress Then Exit Do Set rngFind = rngFind.Resize(1, rng.Columns.Count) DupAddresses = DupAddresses & rngFind.Address & “,” Loop
UserAnswer = MsgBox(rng.Count & ” duplicate values were found,” _ & ” would you like to delete any duplicate rows found?”, vbYesNo) If UserAnswer = vbYes Then Selection.Delete Shift:=xlUp Else MsgBox “No duplicate cell values were found” End If
End Sub
CÒN PHƯƠNG PHÁP NÀO MÀ TÔI BỎ SÓT KHÔNG?
Hàm Đếm Dữ Liệu Trùng Trong Excel Cách Đếm Dữ Liệu Trùng Trong Excel
Hàm đếm dữ liệu trùng lặp trong Excel sẽ giúp bạn lọc giá trị trùng lặp để không có nội dung nào được lặp lại hai lần. Tuy nhiên, không phải lúc nào cũng dễ dàng lọc các dữ liệu trùng lặp vì cơ sở để xác định các dữ liệu trùng có thể phức tạp hơn ta tưởng.
1. Ví dụ với bảng số liệu sau:Cách thực hiện như sau:
Bước 1: Phân tích dữ liệu, căn cứ vào các điều kiện sau:
Ngày: Nếu các mặt hàng khác nhau hoặc số lượng hàng hóa khác nhau được bán trong cùng một ngày, chúng sẽ không bị trùng.
Tên hàng: Tên sản phẩm giống nhau được bán vào các ngày khác nhau hoặc được bán trong cùng một ngày nhưng với số lượng bán khác nhau thì không trùng.
Số lượng: Nếu số lượng bằng nhau nhưng khác ngày bán hoặc các mặt hàng khác nhau, chúng sẽ không bị trùng.
Để xác định sự trùng nhau, ba điều kiện trên phải được xem xét đồng thời: Cùng ngày, cùng mặt hàng, cùng số lượng bán hàng.
Bước 2: Sử dụng hàm đếm dữ liệu trùng là hàm COUNTIFS. Bước 3: Viết hàm.
Ngày: cột ngày: Criteria_Range1 là, ngày tại dòng kiểm tra: Criteria1.
Tên hàng: Tên hàng: Criteria_Range2, tên mặt hàng tại dòng kiểm tra: Criteria2.
Số lượng: Số lượng: Criteria_Range3, số lượng tại dòng kiểm tra: Criteria3.
Vậy ta có: G2 = COUNTIFS($A$2:A2,A2,$B$2:B2,B2,$D$2:D2,D2)
Bắt đầu từ ô G2, filldown công thức cho tới ô G25.
* Giải thích: tại sao lại dùng $A$2:A2 chứ không phải $A$2:$A$25
Ta dùng $A$2:$A$25 để lúc filldown công thức ở cột G, có thể cố định được vùng tham chiếu. Như vậy thì các giá trị trùng/lặp sẽ được tính bằng tổng số giá trị trùng/lặp.
Như vậy rất đơn giản thôi chúng ta đã có thể lọc được những giá trị trùng trong Excel. Hàm đếm dữ liệu trùng lặp trong Excel – hàm COUNTIFS rất thường xuyên được sử dụng, vì vậy bạn hãy đọc kỹ hướng dẫn, sử dụng thành thạo nó để áp dụng tốt vào công việc và học tập.
Cập nhật thông tin chi tiết về Hướng Dẫn So Sánh Hai Cột Dữ Liệu Trong Excel Để Xóa, Highlight, Tô Màu Dữ Liệu Trùng Lặp trên website Hoisinhvienqnam.edu.vn. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất. Chúc bạn một ngày tốt lành!