Để so sánh dữ liệu trên hai cột trong file Excel chúng ta sẽ dùng chức năng Conditional Formating của Excel. Nhưng để các bạn có thể hiểu rõ hơn cơ chế nguyên lý của nó, ThuThuatPhanMem.vn sẽ hướng dẫn các bạn từng bước một.
Hiện tại chúng ta sẽ cùng làm quen với vấn đề này cùng ví dụ sau đây. So sánh hai cột thống kê tên đơn hàng nhập kho trong ngày 1 và ngày 2 xem xét xem có đơn hàng nào được lặp lại hay không.
Để làm được điều này chúng ta cần phải sử dụng hàm COUNTIF. Hàm COUNTIF là hàm đếm dựa theo điều kiện chúng ta đặt ra. Công thức của hàm COUNTIF là:
=COUNTIF[mảng dữ liệu cần đếm, điều kiện phân biệt để đếm]
Ở đây chúng ta cần so sánh từng dòng một nên ở dòng đầu tiên, ta sẽ thử đếm xem "Vở học sinh" – món hàng đầu tiên của ngày nhập kho thứ 2 có sự trùng hợp gì với ngày đầu tiên hay không.
Vậy nên ta có công thức sau:
=COUNTIF[A2:A10;B2]
Và bạn đừng quên bấm F4 sau mảng A2:A10 để nó thành $A$2:$A$10. Việc này giúp cố định mảng đó lại, khi lát nữa chúng ta copy công thức xuống bên dưới nó sẽ không bị biến đổi đơn vị số hàng theo việc copy.
Để chuột ở vị trí góc phải bên dưới của ô công thức rồi kéo dọc xuống dưới để copy công thức xuống.
Nếu bạn click vào xem công thức ở một trong các ô bên dưới thì sẽ thấy yếu tố "điều kiện" nằm ở phía sau sẽ không còn là ô địa chỉ B2 như lúc đầu vì lúc này khi chúng ta copy xuống bên dưới, Excel sẽ tự động tăng số hàng lên với đơn vị tương ứng để biến đổi theo dữ liệu của từng hàng.
Ví dụ như ở hàng số 8 thì phần điều kiện sẽ là B8, ở hàng này chúng a sẽ so sánh "Bảng vẽ" với mảng "Nhập kho ngày 1" chứ không phải là "Vở học sinh" như ban đầu nữa.
Hiện tại nhìn vào bảng dưới và kết quả các bạn sẽ nhận được sau khi sử dụng công thức COUNTIF đã nói ở trên. Các bạn có thể thấy sẽ có hàng là con số 0 nghĩa là Excel không đếm được giá trị nào phù hợp với điều kiện, hay nói cách khác nó không tìm thấy mặt hàng nào ở Nhập kho ngày 1 có sự trùng hợp với món hàng đó tại Nhập kho ngày 2, đồng nghĩa với việc không có sự trùng khớp.
Còn lại những con số khác không đều là số lần trùng lặp của món hàng.
Nếu hiện tại bạn đã hiểu được nguyên lý của công thức COUNTIF trên thì hãy cùng tìm hiểu về vấn đề mà ThuThuatPhanMem.vn đã nói ở ngay đầu bài viết này: Conditional Formating.
Trước hết bạn hãy copy công thức COUNTIF lại vì xuống bên dưới nó sẽ là cốt lõi cho việc sử dụng Conditional Formating của bạn.
Hãy bôi đen một cột cần so sánh dữ liệu, sau đó vào ribbon Home trên thanh công cụ. Bạn sẽ thấy mục Styles có Conditional Formating mà chúng ta cần tìm.
Chức năng Conditional Formating giúp bạn thay đổi định dạng cho ô dữ liệu của bạn dựa trên một điều kiện bạn đặt ra [bình thường người ta sẽ sử dụng nó để tô màu cho ô phù hợp điều kiện, tạo sự khác biệt với những ô không phù hợp với điều kiện khác]. Ở đây chúng ta sẽ thử tô màu xanh cho tất cả những ô nằm ở cột Nhập kho ngày 2 có sự trùng lặp so với Nhập kho ngày 1.
Vì chức năng đặt theo điều kiện của chúng ta dựa vào hàm COUNTIF khá đặc biệt và khác với những kiểu tô màu điều kiện thường thấy của Conditional Formating nên bạn phải lựa chọn New Rule để tự tạo điều kiện tô màu cho mình.
Trong ô hộp thoại chức năng New Formating Rule, bạn click vào dòng Rule a formula to determine which cells to format.
Sau đó ở ô trống Format values where this formula is true [định dạng những ô đúng với công thức sau đây], các bạn paste công thức COUNTIF vừa được copy ở trên vào.
Dựa theo nguyên lý chúng ta đã nói ở trên với công thức COUNTIF, các kết quả bằng 0 với công thức này thì ta sẽ được sự "không trùng lặp" của hai ngày nhập hàng. Chính vì thế, chúng ta cần thêm =0 ở phía sau để tìm các kết quả mà công thức COUNTIF ra 0.
Đừng quên chỉnh định dạng tạo cho ô dữ liệu phù hợp với điều kiện bằng cách click vào Format. Vì chúng ta chỉ cần đổ màu xanh cho ô dữ liệu đó nên các bạn hãy click mở tab Fill rồi chọn màu xanh và ấn OK.
Cuối cùng click vào OK để xác nhận cài đặt tạo định dạng phù màu xanh cho những ô dữ liệu không trùng lặp.
Kết quả bạn nhận được sẽ tương tự như hình dưới đây, các ô dữ liệu không trùng lặp sẽ được tô màu xanh, bao gồm: Thước kẻ, Bút chì, Bảng vẽ, Ghim bấm.
Để đảm bảo về tính thẩm mỹ và không có sự thừa thãi trong trình bày Excel, các bạn có lẽ sẽ không thể có thêm một cột chỉ để đếm sự trùng hàng vậy nên công thức COUNTIF trong Format values where this formula is true ở trên là cần thiết. Nhưng nếu bạn vẫn để nguyên cột đếm trùng hàng COUNTIF này ở ngoài thì khi viết công thức cho Format values where this formula is true, bạn chỉ cần viết =C2=0 mà thôi. Và như thế chúng ta sẽ dựa được vào luôn kết quả COUNTIF đã tạo ra ở ngoài.
Tương tự như vậy, nếu bạn đổi phép toán ở Format values where this formula is true từ =0 thành >0 tức là sẽ tạo định dạng cho những ô chứa dữ liệu giống nhau giữa hai ngày.
Và kết quả nhận được là các ô có dữ liệu giống nhau đó sẽ được đổi định dạng y như những gì bạn cài đặt.
Cảm ơn các bạn đã theo dõi bài viết của ThuThuatPhanMem.vn chúng tôi về cách so sánh dữ liệu trên 2 cột trong file Excel. Hy vọng bài viết đã cho bạn thêm nhiều kiến thức bổ ích về cách so sánh dự liệu. Chúng tôi còn rất nhiều những bài viết khác về thủ thuật phần mềm mà các bạn nhất định sẽ quan tâm, hãy ghé thăm trang chủ của chúng tôi tại ThuThuatPhanMem.vn.
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.
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.
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:
nhấn Đi vào 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"]
nhấn Đi vào để 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"]
nhấn Đi vào để 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 Sub4. 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:
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.
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"]
nhấn Đi vào 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 Sub4. 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 Sub4. 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:
Đô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.
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ị.
nhấn Đi vào để 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.
nhấn Đi vào 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.
Or
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
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.
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?
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]]]
nhấn Đi vào , 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]]
nhấn Shift + Ctrl + Enter để 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 Sub3. 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.
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.
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.
nhấn Đi vào để 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 Đị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 OK. Sau đó, các ô trong cột Date1 lớn hơn các ô trong cột Date2 đã được đánh dấu.
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.
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]
nhấn Đi vào 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]
nhấn Đi vào , 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.
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.
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]]]]
nhấn Đi vào 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
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:
Để đế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 & "*"]]
nhấn Shift + Ctrl + Enter để 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 Đi vào 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.
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:
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"]
nhấn Đi vào 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.
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.
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.
nhấn Đi vào để 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.
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í.
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.