So sánh hai cột để biết kết quả khớp và sự khác biệt trong Excel

So sánh hai cột để biết kết quả khớp và sự khác biệt trong Excel

Hướng dẫn này nói về cách so sánh hai cột trong Excel, đây là một công việc Excel bình thường trong công việc hàng ngày của chúng ta. So sánh hai cột có thể được thực hiện theo các cách khác nhau trong Excel, chẳng hạn như so sánh hai cột theo hàng hoặc từng ô theo ô, so sánh hai cột để làm nổi bật các kết quả phù hợp hoặc khác biệt, v.v. Ở đây bài viết này đề cập đến hầu hết các trường hợp có thể xảy ra khi so sánh hai cột mà bạn có thể gặp gỡ, và hy vọng nó có thể giúp bạn.

Chú thích

Trong hướng dẫn này, nó cung cấp một số dữ liệu ví dụ để giải thích tốt hơn các phương pháp so sánh hai cột trong nhiều trường hợp. Dựa trên tập dữ liệu của bạn, bạn có thể cần thay đổi hoặc điều chỉnh một số nội dung (tài liệu tham khảo), tuy nhiên, các nguyên tắc cơ bản sẽ được giữ nguyên. Hoặc tải trực tiếp các mẫu của từng trường hợp nếu bạn chỉ muốn xác minh xem các phương pháp có hoạt động hay không.

1. So sánh hai cột từng hàng

Dưới đây là tập dữ liệu mà tôi cần kiểm tra trong cùng một hàng xem tên trong cột A có giống với tên trong cột B hay không.

Nhấp để tải xuống tệp mẫu

1.1 So sánh các ô trong cùng một hàng để khớp chính xác

Nói chung, nếu bạn muốn so sánh hai cột từng hàng để khớp chính xác, bạn có thể sử dụng công thức dưới đây:

= B2 = C2

Ấn Bản đăng ký hạng mục thi và kéo chốt điền xuống ô D8. Nếu công thức trả về TRUE, giá trị của hai cột là hoàn toàn giống nhau, nếu trả về FALSE, chúng khác nhau.

1.2 So sánh các ô trong cùng một hàng để đối sánh chính xác hoặc không phân biệt chữ hoa chữ thường (sử dụng công thức IF)

Nếu bạn muốn so sánh hai cột từng hàng để phân biệt chữ hoa chữ thường hoặc nhận thêm mô tả như Khớp, Không khớp, bạn có thể sử dụng hàm IF.

So sánh các ô trong cùng một hàng để khớp chính xác

Nếu bạn muốn sử dụng văn bản “Đối sánh” và “Không khớp” để mô tả kết quả so sánh, vui lòng sử dụng công thức dưới đây:

=IF(EXACT(B2,C2),”Match”,”Mismatch”)

Ấn Bản đăng ký hạng mục thi để nhận kết quả đầu tiên, sau đó kéo ô điều khiển tự động điền vào ô D8.

So sánh các ô trong cùng một hàng để đối sánh không phân biệt chữ hoa chữ thường

Nếu bạn muốn so sánh các ô không phân biệt chữ hoa chữ thường, bạn có thể sử dụng công thức dưới đây:

=IF(B2=C2,”Match”,”Mismatch”)

Ấn Bản đăng ký hạng mục thi để nhận kết quả đầu tiên, sau đó kéo ô điều khiển tự động điền vào ô E8.

chú ý

Trong các công thức trên, bạn có thể thay đổi văn bản “Khớp” và “Không khớp” thành mô tả của riêng bạn.

1.3 So sánh các ô trong cùng một hàng và đánh dấu dữ liệu khớp hoặc không khớp (sử dụng Định dạng có điều kiện)

Nếu bạn muốn đánh dấu các giá trị phù hợp hoặc khác nhau, Định dạng có điều kiện tính năng có thể giúp bạn.

1. Chọn hai cột được sử dụng để so sánh với (B2: C8, không bao gồm tiêu đề cột), sau đó nhấp vào Trang chủ > Định dạng có điều kiện > Quy tắc mới.

2. Trong popping Quy tắc định dạng mới hộp thoại, nhấp để chọn Sử dụng công thức để xác định ô cần định dạng trong Chọn một loại quy tắc phần, sau đó nhập = $ B2 = $ C2 vào hộp văn bản của Định dạng các giá trị trong đó công thức này đúng.

3. Bây giờ hãy nhấp vào Định dạng để hiển thị Format Cells hộp thoại, sau đó dưới Điền , chọn một màu bạn cần để đánh dấu các kết quả phù hợp.

Hoặc bạn có thể thay đổi kích thước phông chữ, cỡ chữ, đường viền ô hoặc định dạng số để phù hợp hơn khi bạn cần trong các tab khác.

4. nhấp chuột OK > OK để đóng hộp thoại, thì các ô trong cùng một hàng sẽ được tô sáng nếu chúng giống nhau.

Nếu bạn muốn đánh dấu các giá trị không khớp, bạn có thể sử dụng giá trị này trong = $ B2 <> $ C2 vào Định dạng các giá trị trong đó công thức này đúng hộp văn bản trong Chỉnh sửa quy tắc định dạng thoại.

Khi đó sự khác biệt của hai cột trong cùng một hàng sẽ được tô sáng bằng một màu xác định.

1.4 So sánh hai cột theo hàng và đánh dấu các giá trị không khớp (sử dụng VBA)

Nếu bạn muốn so sánh hai cột từng hàng với mã VBA, hướng dẫn này đáp ứng cho bạn.

1. Bật trang tính có chứa hai cột được sử dụng để so sánh, nhấn Alt + F11 các phím để kích hoạt Microsoft Visual Basic cho các ứng dụng cửa sổ.

2. Trong hộp thoại bật lên, hãy nhấp vào Chèn > Mô-đun.

3. Sau đó sao chép và dán macro bên dưới trong tập lệnh mô-đun mới.

VBA: So sánh hai cột từng hàng và đánh dấu sự khác biệt

Sub ExtendOffice_HighlightColumnDifferences()
'UpdatebyKutools20201016
Dim xRg As Range

Dim xWs As Worksheet

Dim xFI As Integer

On Error Resume Next

SRg:

Set xRg = Application.InputBox("Select two columns:", "Kutools for Excel", , , , , , 8)

If xRg Is Nothing Then Exit Sub

 

If xRg.Columns.Count <> 2 Then

    MsgBox "Please select two columns"

    GoTo SRg

End If

 

Set xWs = xRg.Worksheet

For xFI = 1 To xRg.Rows.Count

    If Not StrComp(xRg.Cells(xFI, 1), xRg.Cells(xFI, 2), vbBinaryCompare) = 0 Then

        xWs.Range(xRg.Cells(xFI, 1), xRg.Cells(xFI, 2)).Interior.ColorIndex = 7 'you can change the color index as you need.

End If

Next xFI

End Sub

4. nhấn F5 để chạy mã, sau đó một hộp thoại bật ra để chọn hai cột.

5. nhấp chuột OK. Sau đó, sự khác biệt của hai cột đã được đánh dấu bằng màu nền.

chú ý

Bạn có thể thay đổi màu đánh dấu dựa trên nhu cầu của riêng mình bằng cách thay đổi chỉ số màu trong mã, tham chiếu Chỉ số màu:

2． So sánh hai cột trong ô và chọn hoặc đánh dấu dữ liệu trùng lặp hoặc duy nhất

Trong phần này, tập dữ liệu được hiển thị như bên dưới và bạn muốn tìm tất cả các giá trị nằm trong cả cột B và cột C đồng thời hoặc tìm các giá trị chỉ trong cột B.

Nhấp để tải xuống tệp mẫu

2.1 So sánh hai cột theo từng ô và hiển thị kết quả so sánh trong một cột khác (sử dụng công thức)

Ở đây bạn có thể sử dụng công thức được kết hợp với hàm IF và COUNTIF để so sánh hai cột và tìm các giá trị nằm trong cột B nhưng không nằm trong cột C.

=IF(COUNTIF($C$2:$C$8, $B2)=0, “No in C”, “Yes in C”)

Ấn Bản đăng ký hạng mục thi và kéo chốt tự động điền sang ô D8.

chú ý

1. Công thức này so sánh hai cột không phân biệt chữ hoa chữ thường.

2. Bạn có thể thay đổi mô tả “Không trong C” và “Có trong C” cho những người khác.

3. Nếu bạn muốn so sánh toàn bộ hai cột, hãy thay đổi phạm vi cố định $ C $ 2: $ C $ 8 thành $ C: $ C.

2.2 So sánh và đánh dấu dữ liệu trùng lặp hoặc duy nhất (sử dụng Định dạng có điều kiện)

Mô hình Định dạng có điều kiện tính năng trong Excel rất mạnh mẽ, ở đây bạn có thể sử dụng nó để so sánh hai cột theo từng ô và sau đó đánh dấu sự khác biệt hoặc kết hợp khi bạn cần.

Đánh dấu tất cả các giá trị trùng lặp hoặc duy nhất trong hai cột

1. Chọn hai cột mà bạn sẽ so sánh, sau đó nhấp vào 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.

2. Trong popping Giá trị trùng lặp , hãy chọn định dạng đánh dấu bạn cần từ danh sách giá trị thả xuống.

3. nhấp chuột OK. Sau đó, các bản sao trong hai cột đã được đánh dấu.

chú ý

Nếu bạn muốn đánh dấu các giá trị duy nhất (sự khác biệt) trong hai cột, hãy nhấp vào 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 để hiển thị Giá trị trùng lặp hộp thoại, thay đổi Bản sao đến Độc đáo trong danh sách thả xuống bên trái, sau đó chọn định dạng khác từ các giá trị có danh sách thả xuống, nhấp vào OK.

Các giá trị duy nhất sẽ được đánh dấu.

Tìm và đánh dấu các giá trị trong cột B nếu nó cũng nằm trong cột C

Nếu bạn muốn đánh dấu các giá trị trong cột B cũng nằm trong cột C,định dạng có điều kiện tính năng này cũng có thể giúp bạn một việc.

1. Chọn cột B2: B8, nhấp vào Trang chủ > Định dạng có điều kiện > Quy tắc mới.

2. bên trong Quy tắc định dạng mới hộp thoại, chọn Sử dụng công thức để xác định ô cần định dạng từ Chọn một loại quy tắc phần, sau đó nhập = COUNTIF ($ C $ 2: $ C $ 8, $ B2)> 0 vào hộp văn bản của Định dạng các giá trị trong đó công thức này đúng.

3. nhấp chuột Định dạng đi đến Format Cells hộp thoại, dưới Điền , chọn một màu để đánh dấu các kết quả phù hợp.

Bạn có thể sử dụng các định dạng khác để đánh dấu các giá trị trong các tab Phông chữ, Số, Đường viền.

4. nhấp chuột OK > OK. Sau đó, các giá trị trong cột B cũng tồn tại trong cột C đã được đánh dấu bằng màu cụ thể.

Nếu bạn muốn đánh dấu các giá trị chỉ có trong cột B mà không có trong cột C, hãy lặp lại các bước trên, nhưng thay đổi công thức ở bước 2 thành = COUNTIF ($ C $ 2: $ C $ 8, $ B2) = 0 trong Quy tắc định dạng mới thoại.

Sau đó, chọn một màu khác để định dạng các giá trị.

chú ý

Ở đây, Định dạng có Điều kiện so sánh hai cột mà không phân biệt chữ hoa chữ thường.

2.3 So sánh và chọn hoặc đánh dấu dữ liệu trùng lặp hoặc duy nhất (sử dụng một công cụ tiện dụng)

Đôi khi, sau khi so sánh hai cột, bạn có thể thực hiện các hành động khác đối với các kết quả phù hợp hoặc khác biệt, chẳng hạn như lựa chọn, xóa, sao chép, v.v. Trong trường hợp này, một công cụ tiện dụng – Chọn các ô giống nhau và khác nhau of Kutools cho Excel có thể trực tiếp chọn các kết quả phù hợp hoặc khác biệt để thực hiện tốt hơn hoạt động tiếp theo, cũng có thể trực tiếp đánh dấu các giá trị.

Sau cài đặt miễn phí Kutools cho Excel, vui lòng làm như sau:

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

2. Sau đó trong Chọn các ô giống nhau và khác nhau , vui lòng làm như bên dưới:

1) Trong Tìm giá trị trong va Theo chọn hai cột mà bạn muốn so sánh với;

2) Chọn Từng hàng Tùy chọn;

3) Chọn Các giá trị giống nhau or Các giá trị khác nhau như bạn cần;

4) Nếu bạn muốn làm nổi bật các giá trị, hãy chọn màu bạn cần.

3. nhấp chuột Ok, một hộp thoại bật ra để nhắc nhở bạn số lượng giá trị đã được tìm thấy, hãy nhấp vào OK để đóng hộp thoại. Và đồng thời, các giá trị đã được chọn, bây giờ bạn có thể xóa hoặc sao chép hoặc thực hiện các thao tác khác.

Nếu bạn kiểm tra Tô màu nền và Tô màu phông chữ hộp kiểm, kết quả được hiển thị như sau:

chú ý

Nếu bạn muốn so sánh với phân biệt chữ hoa chữ thường, hãy kiểm tra Trường hợp nhạy cảm tùy chọn.

Công cụ này hỗ trợ so sánh hai cột trong các trang tính khác nhau.

2.4 So sánh hai cột và liệt kê các bản sao chính xác trong một cột khác (sử dụng mã VBA)

Nếu bạn muốn liệt kê các giá trị phù hợp trong một cột khác sau khi so sánh hai cột theo từng ô, thì mã macro dưới đây có thể giúp bạn.

1. Bật trang tính mà bạn muốn so sánh hai cột, sau đó nhấn Alt + F11 các phím để hiển thị Microsoft Visual Basic cho các ứng dụng cửa sổ.

2. nhấp chuột Chèn > Mô-đun trong Microsoft Visual Basic cho các ứng dụng cửa sổ.

3. Sau đó sao chép và dán mã bên dưới vào tập lệnh mô-đun trống mới.

VBA: Liệt kê các bản sao trong cột bên cạnh sau khi so sánh hai cột

Sub ExtendOffice_FindMatches()
'UpdatebyKutools20201019
Dim xRg, xRgC1, xRgC2, xRgF1, xRgF2 As Range

Dim xIntSR, xIntER, xIntSC, xIntEC As Integer

On Error Resume Next

SRg:

Set xRgC1 = Application.InputBox("Select first column:", "Kutools for Excel", , , , , , 8)

If xRgC1 Is Nothing Then Exit Sub

If xRgC1.Columns.Count <> 1 Then

    MsgBox "Please select single column"

    GoTo SRg

End If

SsRg:

Set xRgC2 = Application.InputBox("Select the second column:", "Kutools for Excel", , , , , , 8)

If xRgC2 Is Nothing Then Exit Sub

If xRgC2.Columns.Count <> 1 Then

    MsgBox "Please select single column"

    GoTo SsRg

End If

Set xWs = xRg.Worksheet

 

For Each xRgF1 In xRgC1

    For Each xRgF2 In xRgC2

        If xRgF1.Value = xRgF2.Value Then xRgF2.Offset(0, 1) = xRgF1.Value

    Next xRgF2

Next xRgF1

End Sub

4. nhấn F5 để chạy mã, có hai hộp thoại bật ra từng cái một để bạn chọn hai cột riêng biệt.

Chú thích: Trước tiên hãy chọn cột bên trái, sau đó chọn cột bên phải trong hộp thoại thứ hai, nếu không các dữ liệu trùng lặp sẽ thay thế dữ liệu gốc trong cột thứ hai.

5. nhấp chuột OK > OK, sau đó các trận đấu đã được tự động liệt kê ở cột bên phải của hai cột.

chú ý

Mã VBA so sánh hai cột có phân biệt chữ hoa chữ thường.

2.5 So sánh hai cột và đánh dấu các bản sao (sử dụng mã VBA)

Nếu bạn muốn so sánh hai cột theo từng ô và sau đó đánh dấu các kết quả phù hợp, bạn có thể thử mã bên dưới.

1. Bật trang tính mà bạn muốn so sánh hai cột, sau đó nhấn Alt + F11 các phím để hiển thị Microsoft Visual Basic cho các ứng dụng cửa sổ.

2. nhấp chuột Chèn > Mô-đun trong Microsoft Visual Basic cho các ứng dụng cửa sổ.

3. Sao chép mã macro bên dưới và dán chúng vào ô trống mới Mô-đun kịch bản.

VBA: So sánh hai cột và đánh dấu các bản sao

Sub ExtendOffice_CompareTwoRanges()
'UpdatebyKutools20201019

Dim xRg, xRgC1, xRgC2, xRgF1, xRgF2 As Range

SRg:

Set xRgC1 = Application.InputBox("Select the column you want compare according to", "Kutools for Excel", , , , , , 8)

If xRgC1 Is Nothing Then Exit Sub

If xRgC1.Columns.Count <> 1 Then

    MsgBox "Please select a single column"

    GoTo SRg

End If

SsRg:

Set xRgC2 = Application.InputBox("Select the column you want to highlight duplicates in:", "Kutools for Excel", , , , , , 8)

If xRgC2 Is Nothing Then Exit Sub

If xRgC2.Columns.Count <> 1 Then

    MsgBox "Please select a single column"

    GoTo SsRg

End If

 

    For Each xRgF1 In xRgC1

        For Each xRgF2 In xRgC2

            If xRgF1.Value = xRgF2.Value Then

               xRgF2.Interior.ColorIndex = 38 '(you can change the color index as you need)

            End If

        Next

    Next

End Sub

4. nhấn F5 phím để chạy mã. Trong hộp thoại bật ra đầu tiên, hãy chọn cột mà bạn muốn so sánh các giá trị trùng lặp theo.

5. nhấp chuột OK. Trong hộp thoại thứ hai, chọn cột bạn muốn đánh dấu các giá trị trùng lặp.

6. nhấp chuột OK. Bây giờ các bản sao trong cột thứ hai đã được đánh dấu bằng màu nền sau khi so sánh với cột đầu tiên.

chú ý

1. Đoạn mã so sánh các cột có phân biệt chữ hoa chữ thường.

2. Bạn có thể thay đổi màu đánh dấu dựa trên nhu cầu của riêng mình bằng cách thay đổi chỉ số màu trong mã, tham chiếu Chỉ số màu:

3． So sánh nhiều cột trong cùng một hàng

Đôi khi, bạn có thể muốn so sánh nhiều hơn hai cột trong cùng một hàng, chẳng hạn như tập dữ liệu như hình minh họa bên dưới. Ở đây trong phần này, nó liệt kê các phương pháp khác nhau để so sánh nhiều cột.

Nhấp để tải xuống tệp mẫu

3.1 Tìm các kết quả phù hợp đầy đủ trong tất cả các ô trong cùng một hàng (sử dụng công thức IF)

Để tìm các kết quả khớp đầy đủ trên các cột trong cùng một hàng, công thức IF dưới đây có thể giúp bạn.

=IF(AND(B2=C2, B2=D2), “Full match”, “Not”)

 

Nếu các ô trong cùng một hàng khớp với nhau, “Hoàn toàn khớp” được hiển thị hoặc “Không” được hiển thị.

Ấn Bản đăng ký hạng mục thi để nhận kết quả so sánh đầu tiên, sau đó kéo ô điều khiển tự động điền vào ô E7.

chú ý

1. Công thức so sánh các cột không phân biệt chữ hoa chữ thường.

2. Nếu bạn cần so sánh nhiều hơn hoặc bằng ba cột, bạn có thể sử dụng công thức dưới đây:

=IF(COUNTIF($B2:$D2, $B2)=3, “Full match”, “Not”)

 

Trong công thức, 3 là số cột, bạn có thể thay đổi nó để đáp ứng nhu cầu của bạn.

3.2 Tìm đối sánh trong hai ô bất kỳ trong cùng một hàng (sử dụng công thức IF)

Đôi khi, bạn muốn tìm xem có hai cột nào trong cùng một hàng được khớp hay không, bạn có thể sử dụng công thức IF bên dưới.

=IF(OR(B2=C2, C2=D2, B2=D2), “Match”, “Not”)

 

Trong công thức này, bạn cần so sánh bất kỳ một cặp ô nào trong cùng một hàng. “Khớp” cho biết rằng có hai ô bất kỳ được khớp, nếu không có ô nào khớp, công thức sẽ hiển thị “Không phải”, bạn có thể thay đổi văn bản khi cần.

Ấn Bản đăng ký hạng mục thi và kéo chốt điền vào ô E7.

chú ý

1. Công thức này không hỗ trợ phân biệt chữ hoa chữ thường.

2. Nếu cần so sánh nhiều cột, để so sánh bất kỳ một cặp ô nào trong công thức có thể quá dài. Trong trường hợp này, bạn có thể cân nhắc sử dụng công thức dưới đây kết hợp các hàm IF và COUNTIF.

=IF(COUNTIF(C11:E11,B11)+COUNTIF(D11:E11,C11)+(D11=E11)=0,”Not”,”Match”)

 

3.3 Tìm các kết quả phù hợp đầy đủ trong tất cả các ô trong cùng một hàng và đánh dấu (sử dụng Định dạng có Điều kiện)

Nếu bạn muốn đánh dấu các hàng mà tất cả các ô đều khớp với nhau, bạn có thể sử dụng Định dạng có điều kiện tính năng trong Excel.

1. Chọn phạm vi mà bạn sử dụng, sau đó nhấp vào Trang chủ > Định dạng có điều kiện > Quy tắc mới.

2. bên trong Quy tắc định dạng mới hộp thoại, chọn Sử dụng công thức để xác định ô cần định dạng từ Chọn một loại quy tắc , thì bạn có thể sử dụng một trong các công thức dưới đây trong Định dạng các giá trị trong đó công thức này đúng hộp văn bản.

=AND($B2=$C2, $B2=$D2)

Or

=COUNTIF($B2:$D2, $B2)=3

 

Chú thích: Nếu số cột nhiều hơn ba, giả sử là 5, công thức phải được thay đổi thành:

=AND($B2=$C2, $B2=$D2, $B2=$E2, $B2=$F2)

Or

=COUNTIF($B2:$F2, $B2)=5

3. nhấp chuột Định dạng để đi đến hộp thoại Định dạng Ô, sau đó chọn một màu tô hoặc định dạng ô khác để vượt trội so với các hàng.

4. nhấp chuột OK > OK, bây giờ chỉ các hàng trong đó tất cả các ô phù hợp sẽ được đánh dấu.

chú ý

Các công thức trên không hỗ trợ phân biệt chữ hoa chữ thường.

3.4 So sánh nhiều cột và đánh dấu sự khác biệt của hàng

Nếu bạn muốn làm nổi bật sự khác biệt hàng, có nghĩa là nó so sánh từng ô cột và tìm các ô khác nhau theo cột đầu tiên, bạn có thể sử dụng tính năng tích hợp sẵn của Excel-Đi đặc biệt.

1. Chọn phạm vi mà bạn muốn đánh dấu sự khác biệt hàng và nhấp vào Trang chủ > Tìm & Chọn > Đi đặc biệt.

2. Trong popping Đi đặc biệt hộp thoại, kiểm tra Sự khác biệt hàng tùy chọn.

3. nhấp chuột OK. Bây giờ sự khác biệt hàng đã được chọn.

4. Bây giờ giữ các ô đã chọn, nhấp vào Trang chủ > Tô màu để chọn một màu từ menu thả xuống.

chú ý

Phương pháp này so sánh các ô không phân biệt chữ hoa chữ thường.

4. So sánh hai cột và tìm các điểm dữ liệu bị thiếu

Giả sử có hai cột, cột B dài hơn và cột C ngắn hơn như hình minh họa bên dưới. So sánh với cột B, làm thế nào để tìm ra dữ liệu còn thiếu trong cột C?

Nhấp để tải xuống tệp mẫu

4.1 So sánh và tìm các điểm dữ liệu bị thiếu (sử dụng công thức VLOOKUP hoặc MATCH)

Nếu bạn chỉ muốn xác định dữ liệu nào bị thiếu sau khi so sánh hai cột, bạn có thể sử dụng một trong các công thức dưới đây:

=ISERROR(VLOOKUP(B2,$C$2:$C$10,1,0))

Or

=NOT(ISNUMBER(MATCH(B2,$C$2:$C$10,0)))

 

Ấn Bản đăng ký hạng mục thi , sau đó kéo ô điều khiển điền tự động qua ô D10. Bây giờ nếu dữ liệu nằm trong cả cột B và cột C, công thức trả về FALSE, nếu dữ liệu chỉ ở cột B nhưng bỏ sót trong cột C, công thức trả về TRUE.

chú ý

Hai công thức trên so sánh dữ liệu không phân biệt chữ hoa chữ thường.

4.2 So sánh hai cột và liệt kê dữ liệu bị thiếu bên dưới (sử dụng công thức INDEX)

Nếu bạn muốn liệt kê dữ liệu bị thiếu bên dưới cột ngắn hơn sau khi so sánh hai cột, công thức mảng INDEX có thể giúp bạn.

Trong ô bên dưới của cột ngắn hơn, giả sử là ô C7, hãy nhập công thức bên dưới:

=INDEX($B$2:$B$10,MATCH(TRUE,ISNA(MATCH($B$2:$B$10,$C$2:C6,0)),0))

 

Ấn Bản Shift + Ctrl + Nhập để lấy dữ liệu bị thiếu đầu tiên, sau đó kéo chốt tự động điền xuống cho đến khi nó trả về giá trị lỗi # N / A.

Sau đó, bạn có thể xóa giá trị lỗi và tất cả dữ liệu bị thiếu đã được liệt kê bên dưới cột ngắn hơn.

chú ý

Công thức này so sánh các ô không phân biệt chữ hoa chữ thường.

4.3 So sánh và tìm các điểm dữ liệu còn thiếu và liệt kê chúng trong một cột khác (sử dụng một công cụ tiện dụng)

Nếu bạn muốn thực hiện một số thao tác tiếp theo đối với dữ liệu bị thiếu sau khi so sánh hai cột, chẳng hạn như liệt kê dữ liệu bị thiếu trong một cột khác hoặc bổ sung dữ liệu bị thiếu bên dưới cột ngắn hơn, bạn có thể thử một công cụ tiện dụng-Chọn các ô giống nhau và khác nhau of Kutools cho Excel.

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

2. bên trong Chọn các ô giống nhau và khác nhau , hãy làm như bên dưới:

1 trong Tìm giá trị trong , chọn cột dài hơn chứa danh sách đầy đủ.
In Theo , chọn cột ngắn hơn bỏ sót một số dữ liệu.

2) Chọn Từng hàng tùy chọn.

3) Chọn Các giá trị khác nhau tùy chọn.

3. nhấp chuột Ok, một hộp thoại bật ra để nhắc nhở bạn số lượng dữ liệu bị thiếu, hãy nhấp vào OK để đóng nó. Sau đó, dữ liệu bị thiếu đã được chọn.

Bây giờ bạn có thể nhấn Ctrl + C các phím để sao chép dữ liệu bị thiếu đã chọn và dán chúng bằng cách nhấn Ctrl + V các phím bên dưới cột ngắn hơn hoặc một cột mới khác khi bạn cần.

chú ý

Bắn cái Trường hợp không nhạy cảm trong hộp thoại Chọn các ô giống nhau & khác nhau sẽ so sánh hai cột có phân biệt chữ hoa chữ thường.

4.4 So sánh hai cột và liệt kê dữ liệu bị thiếu bên dưới (sử dụng VBA)

Đây là mã macro có thể điền vào dữ liệu bị thiếu bên dưới hai cột.

1. Mở trang tính bạn sử dụng, nhấn Alt + F11 phím để mở Microsoft Visual Basic cho các ứng dụng cửa sổ.

2. nhấp chuột Chèn > Mô-đun để tạo một mô-đun mới để đặt bên dưới mã VBA vào.

VBA: So sánh hai cột và điền dữ liệu bị thiếu

Sub ExtendOffice_PullUniques()
'UpdatebyKutools20201021

Dim xRg, xRgC1, xRgC2, xFRg1, xFRg2 As Range

Dim xIntR, xIntSR, xIntER, xIntSC, xIntEC As Integer

Dim xWs As Worksheet

On Error Resume Next

SRg:

Set xRg = Application.InputBox("Select two columns:", "Kutools for Excel", , , , , , 8)

If xRg Is Nothing Then Exit Sub

If xRg.Columns.Count <> 2 Then

    MsgBox "Please select two columns as a range"

    GoTo SRg

End If

Set xWs = xRg.Worksheet

 

xIntSC = xRg.Column

xIntEC = xRg.Columns.Count + xIntSC - 1

xIntSR = xRg.Row

xIntER = xRg.Rows.Count + xIntSR - 1

 

Set xRg = xRg.Columns

Set xRgC1 = xWs.Range(xWs.Cells(xIntSR, xIntSC), xWs.Cells(xIntER, xIntSC))

Set xRgC2 = xWs.Range(xWs.Cells(xIntSR, xIntEC), xWs.Cells(xIntER, xIntEC))

xIntR = 1

For Each xFRg In xRgC1

    If WorksheetFunction.CountIf(xRgC2, xFRg.Value) = 0 Then

        xWs.Cells(xIntER, xIntEC).Offset(xIntR) = xFRg

        xIntR = xIntR + 1

    End If

Next

xIntR = 1

For Each xFRg In xRgC2

    If WorksheetFunction.CountIf(xRgC1, xFRg) = 0 Then

        xWs.Cells(xIntER, xIntSC).Offset(xIntR) = xFRg

        xIntR = xIntR + 1

    End If

Next

End Sub

3. Sau đó nhấn F5 để chạy mã, một hộp thoại bật ra để chọn hai cột so sánh.

4. nhấp chuột OK. Bây giờ dữ liệu bị thiếu đã được liệt kê bên dưới hai cột.

chú ý

Mã so sánh các ô không phân biệt chữ hoa chữ thường.

5． So sánh hai cột (ngày) nếu lớn hơn hoặc nhỏ hơn

Nếu có hai cột ngày như hình ảnh chụp màn hình bên dưới, bạn có thể muốn so sánh ngày nào sau đó nằm trong cùng một hàng.

Nhấp để tải xuống tệp mẫu

5.1 So sánh hai cột nếu lớn hơn hoặc nhỏ hơn (sử dụng công thức)

Bạn có thể sử dụng công thức đơn giản để nhanh chóng tìm xem ngày 1 có muộn hơn ngày 2 trong mỗi hàng hay không.

=IF(B2>C2,”Yes”,”No”)

 

Ấn Bản đăng ký hạng mục thi để nhận kết quả được so sánh đầu tiên, sau đó kéo ô điều khiển điền tự động qua ô C6 để nhận tất cả kết quả.

chú ý

1. Trong Excel, ngày tháng được lưu trữ dưới dạng chuỗi số, trên thực tế chúng là số. Do đó, bạn áp dụng công thức để so sánh ngày trực tiếp.

2. Nếu bạn muốn so sánh xem ngày 1 có sớm hơn ngày 2 trong mỗi hàng hay không, hãy thay đổi ký hiệu > đến < trong công thức.

5.2 So sánh hai cột nếu lớn hơn hoặc nhỏ hơn thì định dạng (sử dụng Định dạng có Điều kiện)

Nếu bạn muốn đánh dấu các ô trong cột Ngày 1 nếu lớn hơn Ngày 2, bạn có thể sử dụng Định dạng có điều kiện tính năng trong Excel.

1. Chọn ngày trong cột B (Date1), sau đó nhấp vào Trang chủ > Định dạng có điều kiện > Quy tắc mới.

2. bên trong Quy tắc định dạng mới hộp thoại, chọn Sử dụng công thức để xác định ô cần định dạng trong Chọn một loại quy tắc phần, sau đó nhập công thức = $ B2> $ C2 vào hộp văn bản của Định dạng các giá trị trong đó công thức này đúng.

Nếu bạn muốn đánh dấu các ô trong cột B nhỏ hơn các ô trong cột C, hãy sử dụng công thức = $ B2 <$ C2.

3. nhấp chuột Định dạng để mở Format Cells , sau đó chọn kiểu định dạng bạn cần.

4. nhấp chuột OK > OK. Sau đó, các ô trong cột Date1 lớn hơn các ô trong cột Date2 đã được đánh dấu.

6． So sánh hai cột và trả về giá trị trong một cột khác

Ví dụ, có hai bảng, bây giờ bạn muốn so sánh cột B và cột E, sau đó tìm giá tương đối từ cột C và trả lại chúng trong cột F.

Nhấp để tải xuống tệp mẫu

6.1 Kéo dữ liệu khớp chính xác (sử dụng công thức hoặc một công cụ tiện dụng)

Ở đây nó giới thiệu một số công thức hữu ích và một công cụ để giải quyết công việc này.

Phương pháp công thức

Trong ô F2 (ô bạn muốn đặt giá trị trả về), hãy sử dụng một trong các công thức dưới đây:

=VLOOKUP(E2,$B$2:$C$8,2,0)

Or

=INDEX($B$2:$C$8,MATCH(E2,$B$2:$B$8,0),2)

 

Ấn Bản đăng ký hạng mục thi và giá trị đầu tiên đã được tìm thấy. Sau đó kéo chốt tự động điền xuống ô F6, tất cả các giá trị đã được trích xuất.

chú ý

1. Các công thức không hỗ trợ phân biệt chữ hoa chữ thường.

2. Số 2 trong công thức cho biết rằng bạn tìm thấy các giá trị phù hợp trong cột thứ hai của mảng bảng.

3. Nếu công thức không thể tìm thấy giá trị tương đối, nó sẽ trả về giá trị lỗi # N / A.

Một phương pháp công cụ tiện dụng

Nếu bạn bối rối với các công thức, bạn có thể thử công cụ tiện dụng – Công thức trợ giúp of Kutools cho Excel, chứa nhiều công thức để giải hầu hết các vấn đề trong Excel. Với nó, bạn chỉ cần chọn phạm vi nhưng không cần nhớ cách sử dụng các công thức.

Sau cài đặt miễn phí Kutools cho Excel, vui lòng làm như sau:

1. Chọn ô F2 (ô bạn muốn đặt giá trị trả về) và nhấp vào Kutools > Công thức trợ giúp > Tra cứu & Tham khảo > Tìm kiếm một giá trị trong danh sách.

2. bên trong Trình trợ giúp công thức hộp thoại, ở bên phải Đầu vào đối số , chọn phạm vi mảng bảng, giá trị tra cứu và chỉ định cột nào bạn muốn tìm kiếm giá trị trả về.

Mẹo: Hãy nhớ thay đổi tham chiếu tuyệt đối trong Lookup_value hộp tương đối, hoặc bạn chỉ có thể tìm thấy một giá trị đầu tiên.

3. nhấp chuột Ok, giá trị đầu tiên đã được tìm thấy, sau đó kéo chốt tự động điền xuống ô F6.

6.2 Kéo dữ liệu khớp từng phần (sử dụng công thức)

Nếu có một số khác biệt nhỏ giữa hai cột được so sánh như ảnh chụp màn hình bên dưới, các phương pháp trên không thể hoạt động.

Vui lòng chọn một trong các công thức dưới đây để giải quyết công việc này:

=VLOOKUP(“*”&E2&”*”,$B$2:$C$8,2,0)

Or

=INDEX($B$2:$C$8,MATCH(“*”&E2&”*”,$B$2:$B$8,0),2)

 

Ấn Bản đăng ký hạng mục thi , sau đó kéo chốt tự động điền xuống ô F5, tất cả các giá trị đã được tìm thấy.

chú ý

1. Các công thức không hỗ trợ phân biệt chữ hoa chữ thường.

2. Số 2 trong công thức cho biết rằng bạn tìm thấy các giá trị trả về trong cột thứ hai của mảng bảng.

3. Nếu công thức không thể tìm thấy giá trị tương đối, nó sẽ trả về giá trị lỗi # N / A.

4. * trong công thức là một ký tự đại diện được sử dụng để chỉ ra bất kỳ ký tự hoặc chuỗi nào.

7． So sánh hai cột và đếm các kết quả phù hợp hoặc chênh lệch

Tập dữ liệu dưới đây là một ví dụ để so sánh và đếm các kết quả phù hợp hoặc khác biệt.

Nhấp để tải xuống tệp mẫu

7.1 So sánh hai cột và đếm kết quả phù hợp (sử dụng công thức SUMPRODUCT)

Công thức SUMPRODUCT có thể nhanh chóng đếm các kết quả phù hợp trong hai cột.

=SUMPRODUCT(–(ISNUMBER(MATCH(B2:B8,C2:C8,0))))

 

Ấn Bản đăng ký hạng mục thi phím để nhận kết quả.

chú ý

Công thức đếm các ô không phân biệt chữ hoa chữ thường.

7.2 So sánh hai cột và đếm các kết quả phù hợp hoặc chênh lệch (sử dụng một công cụ tiện dụng)

Nếu bạn muốn đếm các kết quả phù hợp hoặc chênh lệch giữa hai cột, công cụ tiện dụng – Chọn các ô giống nhau và khác nhau.

Sau cài đặt miễn phí Kutools cho Excel, vui lòng làm như sau:

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

2. bên trong Chọn các ô giống nhau & khác biệt hộp thoại, chọn phạm vi hai cột trong Tìm giá trị trong và Theo các phần riêng biệt, sau đó chọn Từng hàng, và lựa chọn Các giá trị giống nhau or Các giá trị khác nhau tùy chọn như bạn cần.

3. nhấp chuột Ok. Một hộp thoại bật ra để cho bạn biết có bao nhiêu ô phù hợp hoặc khác nhau được chọn.

Khớp các ô

Các ô khác nhau

số 8. So sánh hai cột bằng ký tự đại diện

Giả sử đây là danh sách dữ liệu trong cột B và bạn muốn đếm các ô có chứa “Apple” hoặc “Candy” trong cột D như hình minh họa bên dưới:

Nhấp để tải xuống tệp mẫu

Để đếm xem một ô có chứa một hoặc nhiều giá trị hay không, bạn có thể sử dụng công thức có ký tự đại diện để giải quyết vấn đề này.

=SUM(COUNTIF(B2,”*” & $D$2:$D$3 & “*”))

 

Ấn Bản Shift + Ctrl + Nhập để nhận lần kiểm tra đầu tiên, sau đó kéo ô điều khiển tự động điền xuống ô F8.

Bây giờ nếu ô liên quan chứa một hoặc nhiều giá trị trong cột D, kết quả hiển thị số lớn hơn 0, nếu nó không chứa bất kỳ giá trị nào trong cột D, nó sẽ trả về 0.

Nếu bạn muốn đếm tổng số ô chứa các giá trị trong cột D, hãy sử dụng công thức bên dưới của ô F8:

chú ý

1. Ngoài ra, bạn có thể sử dụng công thức để đếm nếu ô chứa các giá trị trong cột khác

=SUMPRODUCT(COUNTIF(B2,”*” &$D$2:$D$3& “*”))

Công thức này chỉ cần nhấn đăng ký hạng mục thi rồi kéo chốt tự động điền.

2. Trong các công thức, * là ký tự đại diện cho biết bất kỳ ký tự hoặc chuỗi nào.

9. Xóa sự khác biệt hoặc so khớp

Nếu bạn muốn xóa sự khác biệt hoặc so khớp sau khi so sánh hai cột như hình ảnh chụp màn hình bên dưới:

Nhấp để tải xuống tệp mẫu

Trước tiên, bạn có thể sử dụng công thức để tìm ra sự khác biệt hoặc so khớp:

=IF(ISERROR(MATCH(B2,$D$2:$D$8,0)),”Difference”,”Match”)

Ấn Bản đăng ký hạng mục thi và kéo chốt điền tự động xuống ô D8.

Sau đó, áp dụng Lọc tính năng trong Excel để lọc ra sự khác biệt hoặc giá trị phù hợp.

Chọn cột công thức, sau đó nhấp vào Ngày > Lọc.

Sau đó, nút bộ lọc xuất hiện trong cột C, nhấp vào nút bộ lọc để mở rộng trình đơn thả xuống, chọn Sự khác biệt or Trận đấu như bạn cần. Sau đó nhấp vào OK kêt thuc.

Bây giờ chỉ có sự khác biệt hoặc trùng khớp đã được lọc ra. Bạn có thể chọn chúng và nhấn Xóa bỏ để loại bỏ chúng.

Bây giờ bấm Ngày > Lọc một lần nữa để xóa bộ lọc.

Loại bỏ cột công thức nếu bạn không cần nó nữa.

10. So sánh hai cột và tính toán phần trăm thay đổi giữa

Có hai cột, một cột chứa giá gốc và cột kia chứa giá ưu đãi. Bây giờ phần này giới thiệu một công thức để so sánh hai cột này và sau đó tính toán phần trăm thay đổi giữa hai cột.

Nhấp để tải xuống tệp mẫu

Bạn có thể sử dụng công thức dưới đây để tìm ra phần trăm thay đổi giữa hai mức giá trong cùng một hàng.

=(C2-B2)/B2

 

Ấn Bản đăng ký hạng mục thi để lấy một số, sau đó kéo ô điều khiển điền tự động xuống ô D7.

Sau đó, định dạng kết quả công thức dưới dạng phần trăm. Chọn kết quả, nhấp vào Trang chủ tab và đi đến Con số nhóm để nhấp Phần trăm kiểu.

Kết quả công thức đã được định dạng dưới dạng phần trăm.

11. So sánh hai dãy

Bây giờ bạn đã biết cách so sánh hai cột sau khi đọc các phương pháp trên. Tuy nhiên, trong một số trường hợp, bạn có thể muốn so sánh hai phạm vi (hai chuỗi có nhiều cột) Bạn có thể sử dụng các phương pháp trên (công thức hoặc định dạng có điều kiện) để so sánh chúng theo từng cột, nhưng ở đây giới thiệu một công cụ hữu ích: Kutools cho Excel có thể giải quyết công việc này trong các trường hợp khác nhau một cách nhanh chóng với công thức miễn phí.

Nhấp để tải xuống tệp mẫu

11.1 So sánh hai phạm vi theo ô

Đây là hai phạm vi cần thiết để được so sánh bởi các ô, bạn có thể sử dụng Chọn các ô giống nhau và khác nhau tiện ích của Kutools cho Excel để xử lý nó.

Sau cài đặt miễn phí Kutools cho Excel, vui lòng làm như sau:

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

2. Trong popping Chọn các ô giống nhau và khác nhau , hãy làm như bên dưới:

1) Trong Tìm giá trị trong , chọn phạm vi mà bạn muốn tìm ra các kết quả phù hợp hoặc khác biệt sau khi so sánh hai phạm vi.

2) Trong Theo , chọn phạm vi khác được sử dụng để so sánh phạm vi.

3 trong Dựa trên phần, chọn Đơn bào.

4) Sau đó, trong Tìm kiếm , chọn loại ô bạn muốn chọn hoặc đánh dấu.

5) Trong Xử lý kết quả , bạn có thể tô sáng các ô bằng cách tô màu nền hoặc màu chữ, nếu không cần tô sáng thì không đánh dấu vào ô chọn.

3. nhấp chuột Ok. Một hộp thoại bật ra và nhắc nhở có bao nhiêu ô / hàng đã được chọn, nhấp vào OK để đóng nó. Bây giờ các ô khác với các ô trong phạm vi khác đã được chọn và đánh dấu.

Đánh dấu các giá trị giống nhau

chú ý

Nếu bạn muốn so sánh hai phạm vi theo hàng, bạn cũng có thể áp dụng Chọn các ô giống nhau và khác nhau nhưng trong trường hợp này, hãy chọn Từng hàng tùy chọn.

11.2 So sánh hai phạm vi nếu dữ liệu theo cùng một thứ tự

Nếu bạn muốn so sánh hai phạm vi theo hàng, Tính năng So sánh các ô của Kutools cho Excel có thể giúp bạn.

Sau cài đặt miễn phí Kutools cho Excel, vui lòng làm như sau:

Giả sử, phạm vi F2: H7 là một mô hình, bây giờ bạn muốn tìm xem dữ liệu trong phạm vi B2: D7 có đúng thứ tự theo phạm vi F2: H7 hay không.

1. nhấp chuột Kutools > So sánh các ô.

2. bên trong So sánh các ô hộp thoại, đặt như bên dưới:

1) Chọn hai phạm vi vào Tìm giá trị trong và Theo hộp riêng biệt.

2) Chọn loại ô bạn muốn đánh dấu trong Tìm kiếm phần.

3) Chọn kiểu đánh dấu trong Xử lý kết quả phần.

3. nhấp chuột Ok. Một hộp thoại bật ra và nhắc nhở có bao nhiêu ô đã được chọn, nhấp vào OK để đóng nó. Bây giờ các ô khác với các ô trong phạm vi khác đã được chọn và đánh dấu.