Tham chiếu chéo các trang tính Excel cho các bản sao

Excel 2013 có thể định vị các hàng trùng lặp miễn là một cột chứa mã định danh duy nhất cho mỗi bản ghi, chẳng hạn như ID sản phẩm hoặc số tài khoản. Bằng cách tham chiếu mã định danh này, bạn có thể tìm thấy các bản sao ngay cả khi dữ liệu trong các cột khác không khớp hoàn toàn do lỗi đánh máy hoặc các trường bị bỏ qua. Mã định danh duy nhất cũng ngăn không cho các hàng khớp ngẫu nhiên dựa trên các thuật ngữ phổ biến, chẳng hạn như tên hoặc họ. Khi tập hợp dữ liệu thứ hai nằm trên một bảng tính hoặc sổ làm việc riêng biệt, bạn sẽ cần điều chỉnh công thức để tham chiếu các vị trí khác nhau

1. 1

   Nhập công thức sau vào ô đầu tiên của một cột trống

   =IF[ISERROR[MATCH[A1,Records2. A. A,0]],"","Trùng lặp"]

2. 2

   Thay đổi tham chiếu của công thức để trỏ đến các cột thích hợp. Trong ví dụ trên, công thức so sánh mã định danh duy nhất trong ô A1 với bất kỳ trường nào trong cột A trong bảng tính Records2. Nếu bộ bản ghi thứ hai nằm trong sổ làm việc riêng, hãy thêm "[tên tệp. xlsx]" không có dấu ngoặc kép ngay trước tên trang tính. Ví dụ, để tham chiếu Backup. sổ làm việc xlsx, hãy nhập

   =IF[ISERROR[MATCH[A1,[Dự phòng. xlsx]Bản ghi2. A. A,0]],"","Trùng lặp"]

   Để điều này hoạt động, cả hai tệp phải được lưu trong cùng một thư mục trên máy tính của bạn. Tuy nhiên, bạn có thể tham chiếu một vị trí khác bằng cách bao quanh đường dẫn chính xác trong dấu nháy đơn, chẳng hạn như

   =IF[ISERROR[MATCH[A1,'C. [Sao lưu. xlsx]Bản ghi2'. A. A,0]],"","Trùng lặp"]

3. 3

   Nhấp vào ô chứa công thức bạn vừa nhập, nếu nó chưa được chọn

4. 4

   Bấm đúp vào núm điều khiển điền, là một hình vuông nhỏ, màu đen ở góc dưới bên phải của ô, để tự động sao chép công thức xuống toàn bộ cột. Excel dừng sao chép khi đạt đến khoảng trống. Nếu dữ liệu của bạn chứa nhiều khoảng trống, hãy nhấn "Ctrl-C" để sao chép công thức, giữ "Shift", nhấp vào ô cuối cùng trong cột rồi nhấn "Ctrl-V" để sao chép công thức theo cách thủ công

5. 5

   Quét cột để tìm bất kỳ mục nhập "Trùng lặp" nào, cho biết các hàng trùng lặp trong tập dữ liệu khác

   So sánh hai tệp Excel [hoặc so sánh hai trang tính trong cùng một tệp] có thể phức tạp vì sổ làm việc Excel chỉ hiển thị một trang tính tại một thời điểm

   Điều này trở nên khó khăn và dễ sai sót hơn khi bạn có nhiều dữ liệu cần so sánh

   Rất may, có một số tính năng thú vị trong Excel cho phép bạn mở và dễ dàng so sánh hai tệp Excel

   Trong hướng dẫn Excel này, tôi sẽ chỉ cho bạn nhiều cách để so sánh hai tệp [hoặc trang tính] Excel khác nhau và kiểm tra sự khác biệt. Phương pháp bạn chọn sẽ tùy thuộc vào cách dữ liệu của bạn được cấu trúc và loại so sánh bạn đang tìm kiếm

   Bắt đầu nào

   Hướng dẫn này bao gồm

   • So sánh hai trang tính Excel trong các tệp Excel riêng biệt [Cạnh nhau]
   • So sánh hai trang tính [Cạnh nhau] trong cùng một sổ làm việc Excel
   • So sánh hai trang tính và đánh dấu sự khác biệt [Sử dụng định dạng có điều kiện]
   • So sánh hai tệp/trang tính Excel và nhận sự khác biệt bằng công thức
   • So sánh hai tệp/trang tính Excel và nhận sự khác biệt bằng VBA
   • Sử dụng công cụ của bên thứ ba – Bộ so sánh XL

   So sánh hai trang tính Excel trong các tệp Excel riêng biệt [Cạnh nhau]

   Nếu bạn muốn so sánh hai tệp Excel riêng biệt cạnh nhau [hoặc hai trang tính trong cùng một sổ làm việc], thì có một tính năng tích hợp sẵn trong Excel để thực hiện việc này

   Đó là tùy chọn View Side by Side

   Điều này chỉ được khuyến nghị khi bạn có một tập dữ liệu nhỏ và việc so sánh các tệp này theo cách thủ công có thể sẽ tốn ít thời gian hơn và dễ bị lỗi hơn. Nếu bạn có một tập dữ liệu lớn, tôi khuyên bạn nên sử dụng phương pháp có điều kiện hoặc phương pháp công thức được trình bày sau trong hướng dẫn này

   Hãy xem cách sử dụng tính năng này khi bạn phải so sánh hai tệp riêng biệt hoặc hai trang tính trong cùng một tệp

   Giả sử bạn có hai tệp cho hai tháng khác nhau và bạn muốn kiểm tra giá trị nào khác nhau trong hai tệp này

   Theo mặc định, khi bạn mở một tệp, tệp đó có khả năng chiếm toàn bộ màn hình của bạn. Ngay cả khi bạn giảm kích thước, bạn luôn thấy một tệp Excel ở trên cùng

   Với tùy chọn xem cạnh nhau, bạn có thể mở hai tệp rồi sắp xếp chúng theo chiều ngang hoặc chiều dọc. Điều này cho phép bạn dễ dàng so sánh các giá trị mà không cần chuyển đổi qua lại

   Dưới đây là các bước để căn chỉnh hai tệp cạnh nhau và so sánh chúng

   1. Mở các tệp mà bạn muốn so sánh
   2. Trong mỗi tệp, hãy chọn trang tính mà bạn muốn so sánh
   3. Nhấp vào tab Xem
   4. Trong nhóm Windows, nhấp vào tùy chọn ‘View Side by Side’. Tính năng này chỉ khả dụng khi bạn mở hai tệp Excel trở lên

   Ngay khi bạn nhấp vào tùy chọn View side by side, Excel sẽ sắp xếp sổ làm việc theo chiều ngang. Cả hai tệp sẽ hiển thị và bạn có thể tự do chỉnh sửa/so sánh các tệp này khi chúng được sắp xếp cạnh nhau

   Trong trường hợp muốn sắp xếp các file theo chiều dọc, click vào tùy chọn Arrange All [trong tab View]

   Thao tác này sẽ mở hộp thoại 'Sắp xếp Windows' nơi bạn có thể chọn 'Dọc'

   Tại thời điểm này, nếu bạn cuộn xuống một trong các trang tính, trang tính còn lại sẽ vẫn như cũ. Bạn có thể thay đổi điều này để khi bạn cuộn trong một trang tính, trang tính kia cũng cuộn cùng lúc. Điều này giúp dễ dàng thực hiện so sánh từng dòng và phát hiện bất kỳ sự khác biệt nào

   Nhưng để làm được điều này, bạn cần kích hoạt tính năng Cuộn đồng bộ

   Để bật Cuộn đồng bộ, bấm vào tab Xem [trong bất kỳ sổ làm việc nào] rồi bấm vào tùy chọn Cuộn đồng bộ. Đây là một nút bật tắt [vì vậy nếu bạn muốn tắt nó, chỉ cần nhấp vào nó một lần nữa]

   So sánh nhiều trang tính trong các tệp Excel riêng biệt [Cạnh nhau]

   Với tùy chọn 'Xem cạnh nhau', bạn chỉ có thể so sánh hai tệp Excel cùng một lúc

   Trong trường hợp bạn mở nhiều tệp Excel, khi bạn nhấp vào tùy chọn Xem cạnh nhau, nó sẽ hiển thị cho bạn hộp thoại 'So sánh cạnh nhau', nơi bạn có thể chọn tệp bạn muốn so sánh với sổ làm việc đang hoạt động

   Trong trường hợp bạn muốn so sánh nhiều hơn hai tệp cùng một lúc, hãy mở tất cả các tệp này rồi nhấp vào tùy chọn Sắp xếp tất cả [nằm trong tab Xem]

   Trong hộp thoại Sắp xếp Windows, chọn Dọc/Ngang rồi bấm OK

   Điều này sẽ sắp xếp tất cả các tệp Excel đang mở theo thứ tự đã chọn [dọc hoặc ngang]

   So sánh hai trang tính [Cạnh nhau] trong cùng một sổ làm việc Excel

   Trong trường hợp bạn muốn so sánh hai trang tính riêng biệt trong cùng một sổ làm việc, bạn không thể sử dụng tính năng Xem cạnh nhau [vì tính năng này chỉ hoạt động với các tệp Excel riêng biệt]

   Nhưng bạn vẫn có thể so sánh song song

   Điều này có thể thực hiện được nhờ tính năng 'Windows mới' trong Excel, cho phép bạn mở hai phiên bản trên cùng một sổ làm việc. Sau khi bạn mở hai phiên bản, bạn có thể sắp xếp các phiên bản này cạnh nhau và sau đó so sánh chúng

   Giả sử bạn có một sổ làm việc Excel có hai trang tính cho hai tháng khác nhau [tháng 1 và tháng 2] và bạn muốn so sánh các trang này cạnh nhau để xem doanh số bán hàng trên mỗi cửa hàng đã thay đổi như thế nào

   Dưới đây là các bước để so sánh hai trang tính trong Excel

   1. Mở sổ làm việc có các trang tính mà bạn muốn so sánh
   2. Nhấp vào tab Xem
   3. Trong nhóm Cửa sổ, nhấp vào tùy chọn 'Cửa sổ mới'. Thao tác này sẽ mở phiên bản thứ hai của cùng một sổ làm việc
   4. Trong tab 'Xem', nhấp vào 'Sắp xếp tất cả'. Thao tác này sẽ mở hộp thoại Sắp xếp Windows
   5. Chọn ‘Dọc’ để so sánh dữ liệu theo cột [hoặc chọn Ngang nếu bạn muốn so sánh dữ liệu theo hàng]
   6. Nhấp vào OK

   Các bước trên sẽ sắp xếp cả hai phiên bản của sổ làm việc theo chiều dọc

   Tại thời điểm này, cả hai sổ làm việc sẽ có cùng một trang tính được chọn. Trong một trong các sổ làm việc, hãy chọn trang tính khác mà bạn muốn so sánh với trang tính đang hoạt động

   Cái này hoạt động ra sao?

   Khi bạn bấm vào Cửa sổ mới, nó sẽ mở lại cùng một sổ làm việc với một tên hơi khác. Ví dụ: nếu tên sổ làm việc của bạn là 'Kiểm tra' và bạn bấm vào Cửa sổ mới, nó sẽ đặt tên cho sổ làm việc đã mở là 'Kiểm tra - 1' và phiên bản thứ hai là 'Kiểm tra - 2'

   Lưu ý rằng đây vẫn là cùng một sổ làm việc. Nếu bạn thực hiện bất kỳ thay đổi nào trong bất kỳ sổ làm việc nào trong số này, nó sẽ được phản ánh trong cả hai

   Và khi bạn đóng bất kỳ phiên bản nào của tệp đang mở, tên sẽ trở lại ban đầu

   Bạn cũng có thể bật tính năng cuộn đồng bộ nếu muốn [bằng cách nhấp vào tùy chọn 'Cuộn đồng bộ' trong tab 'Chế độ xem']

   So sánh hai trang tính và đánh dấu sự khác biệt [Sử dụng định dạng có điều kiện]

   Mặc dù bạn có thể sử dụng phương pháp trên để sắp xếp các sổ làm việc lại với nhau và duyệt từng dòng dữ liệu theo cách thủ công, nhưng đó không phải là cách hay trong trường hợp bạn có nhiều dữ liệu

   Ngoài ra, thực hiện mức độ so sánh này theo cách thủ công có thể dẫn đến rất nhiều lỗi

   Vì vậy, thay vì thực hiện việc này theo cách thủ công, bạn có thể sử dụng sức mạnh của Định dạng có điều kiện để nhanh chóng đánh dấu bất kỳ sự khác biệt nào trong hai trang tính Excel

   Phương pháp này thực sự hữu ích nếu bạn có hai phiên bản ở hai trang tính khác nhau và bạn muốn kiểm tra nhanh những gì đã thay đổi

   Lưu ý rằng bạn KHÔNG THỂ so sánh hai trang tính trong sổ làm việc khác nhau

   Vì Định dạng có điều kiện không thể tham chiếu đến tệp Excel bên ngoài nên các trang tính bạn cần so sánh cần phải nằm trong cùng một sổ làm việc Excel. Trong trường hợp không có, bạn có thể sao chép một trang tính từ tệp khác vào sổ làm việc đang hoạt động, rồi thực hiện phép so sánh này

   Trong ví dụ này, giả sử bạn có tập dữ liệu như hình bên dưới cho hai tháng [tháng 1 và tháng 2] ở hai trang tính khác nhau và bạn muốn so sánh nhanh dữ liệu trong hai trang tính này và kiểm tra xem giá của các mặt hàng này có thay đổi hay không.

   Dưới đây là các bước để làm điều này

   1. Chọn dữ liệu trong trang tính mà bạn muốn đánh dấu các thay đổi. Vì tôi muốn kiểm tra giá thay đổi như thế nào từ tháng 1 đến tháng 2 nên tôi đã chọn dữ liệu trong sheet tháng 2
   2. Nhấp vào tab Trang chủ
   3. Trong nhóm Kiểu, nhấp vào 'Định dạng có điều kiện'
   4. Trong các tùy chọn hiển thị, nhấp vào 'Quy tắc mới'
   5. Trong hộp thoại 'Quy tắc định dạng mới', nhấp vào 'Sử dụng công thức để xác định ô cần định dạng'
   6. Trong trường công thức, nhập công thức sau. =B2Tháng 1. B2
   7. Nhấp vào nút Định dạng
   8. Trong hộp thoại Định dạng ô hiển thị, nhấp vào tab 'Điền' và chọn màu mà bạn muốn đánh dấu dữ liệu không khớp
   9. Nhấp vào OK
   10. Nhấp vào OK

   Các bước trên sẽ ngay lập tức làm nổi bật bất kỳ thay đổi nào trong tập dữ liệu ở cả hai trang tính

   Cái này hoạt động ra sao?

   Định dạng có điều kiện làm nổi bật một ô khi công thức đã cho cho ô đó trả về giá trị TRUE. Trong ví dụ này, chúng ta đang so sánh từng ô trong một trang tính với ô tương ứng trong trang tính khác [được thực hiện bằng cách sử dụng toán tử không bằng trong công thức]

   Khi định dạng có điều kiện tìm thấy bất kỳ sự khác biệt nào trong dữ liệu, nó sẽ làm nổi bật điều đó trong trang tính tháng 1 [trang tính mà chúng tôi đã áp dụng định dạng có điều kiện

   Lưu ý rằng tôi đã sử dụng tham chiếu tương đối trong ví dụ này [A1 chứ không phải $A$1 hoặc $A1 hoặc A$1]

   Khi sử dụng phương pháp này để so sánh hai trang tính trong Excel, hãy nhớ những điều sau;

   • Phương pháp này rất tốt để nhanh chóng xác định sự khác biệt, nhưng bạn không thể sử dụng nó liên tục. Ví dụ: nếu tôi nhập một hàng mới vào bất kỳ bộ dữ liệu nào [hoặc xóa một hàng], nó sẽ cho tôi kết quả không chính xác. Ngay sau khi tôi chèn/xóa hàng, tất cả các hàng tiếp theo được coi là khác nhau và được đánh dấu tương ứng
   • Bạn chỉ có thể so sánh hai trang tính trong cùng một tệp Excel
   • Bạn chỉ có thể so sánh giá trị [không phải sự khác biệt về công thức hoặc định dạng]

   So sánh hai tệp/trang tính Excel và nhận sự khác biệt bằng công thức

   Nếu bạn chỉ quan tâm đến việc nhanh chóng so sánh và xác định sự khác biệt giữa hai trang tính, bạn có thể sử dụng công thức để chỉ tìm nạp những giá trị khác nhau

   Đối với phương pháp này, bạn sẽ cần có một bảng tính riêng để bạn có thể tìm nạp các điểm khác biệt

   Phương pháp này sẽ hoạt động nếu muốn so sánh hai sổ làm việc Excel hoặc trang tính riêng biệt trong cùng một sổ làm việc

   Để tôi chỉ cho bạn một ví dụ mà tôi đang so sánh hai bộ dữ liệu trong hai trang tính [trong cùng một sổ làm việc]

   Giả sử bạn có tập dữ liệu như hình dưới đây trong một trang tính có tên là Jan [và dữ liệu tương tự trong một trang tính có tên là Feb] và bạn muốn biết những giá trị nào khác nhau

   Để so sánh hai trang tính, trước tiên, hãy chèn một trang tính mới [hãy gọi trang tính này là 'Sự khác biệt']

   Trong ô A1, nhập công thức sau

   =IF[Jan!A1Feb!A1,"Jan Value:"&Jan!A1&CHAR[10]&"Feb Value:"&Feb!A1,""]

   Sao chép và dán công thức này cho một dải ô để nó bao phủ toàn bộ tập dữ liệu trong cả hai trang tính. Vì tôi có một tập dữ liệu nhỏ, tôi sẽ chỉ sao chép và dán công thức này vào A1. phạm vi B10

   Công thức trên sử dụng điều kiện IF để kiểm tra sự khác biệt. Trong trường hợp không có sự khác biệt trong các giá trị, nó sẽ trả về giá trị trống và trong trường hợp có sự khác biệt, nó sẽ trả về các giá trị từ cả hai trang tính trong các dòng riêng biệt trong cùng một ô

   Điều tốt với phương pháp này là nó chỉ cung cấp cho bạn sự khác biệt và cho bạn thấy chính xác sự khác biệt là gì. Trong ví dụ này, tôi có thể dễ dàng thấy rằng giá trong ô B4 và B8 là khác nhau [cũng như giá trị chính xác trong các ô này]

   So sánh hai tệp/trang tính Excel và nhận sự khác biệt bằng VBA

   Nếu bạn cần so sánh các tệp hoặc trang tính Excel khá thường xuyên, bạn nên chuẩn bị sẵn mã VBA macro Excel và sử dụng nó bất cứ khi nào bạn cần thực hiện so sánh

   Bạn cũng có thể thêm macro vào Thanh công cụ truy cập nhanh để bạn có thể truy cập bằng một nút duy nhất và biết ngay các ô khác nhau trong các tệp/trang tính khác nhau

   Giả sử bạn có hai sheet Jan và Feb và bạn muốn so sánh và làm nổi bật sự khác biệt trong sheet Jan, bạn có thể sử dụng mã VBA dưới đây

   Sub CompareSheets[]
   Dim rngCell As Range
    
   For Each rngCell In Worksheets["Jan"].UsedRange
   
       If Not rngCell = Worksheets["Feb"].Cells[rngCell.Row, rngCell.Column] Then
           rngCell.Interior.Color = vbYellow
       End If
   
   Next rngCell
   
   End Sub

   Đoạn mã trên sử dụng vòng lặp For Next để đi qua từng ô trong trang tính tháng 1 [toàn bộ phạm vi được sử dụng] và so sánh nó với ô tương ứng trong trang tính tháng 2. Trong trường hợp nó tìm thấy sự khác biệt [được kiểm tra bằng câu lệnh Nếu-Thì], nó sẽ đánh dấu các ô đó bằng màu vàng

   Bạn có thể sử dụng mã này trong một mô-đun thông thường trong Trình soạn thảo VB

   Và nếu bạn cần làm điều này thường xuyên, thì tốt hơn là lưu mã này vào sổ làm việc Macro cá nhân rồi thêm mã đó vào thanh công cụ Truy cập nhanh. Theo những cách đó, bạn sẽ có thể thực hiện so sánh này chỉ bằng một nút bấm

   Dưới đây là các bước để tải Sổ làm việc Macro Cá nhân trong Excel [không có sẵn theo mặc định nên bạn cần bật nó]

   Dưới đây là các bước để lưu mã này vào Sổ làm việc Macro cá nhân

   Và ở đây bạn sẽ tìm thấy các bước để thêm mã macro này vào QAT

   Sử dụng công cụ của bên thứ ba – Bộ so sánh XL

   Một cách nhanh chóng khác để so sánh hai tệp Excel và kiểm tra sự trùng khớp và khác biệt là sử dụng công cụ miễn phí của bên thứ ba chẳng hạn như Bộ so sánh XL

   Đây là một công cụ dựa trên web nơi bạn có thể tải lên hai tệp Excel và nó sẽ tạo một tệp so sánh có dữ liệu chung [hoặc dữ liệu khác nhau dựa trên tùy chọn bạn đã chọn

   Giả sử bạn có hai tệp có tập dữ liệu khách hàng [chẳng hạn như tên và địa chỉ email] và bạn muốn kiểm tra nhanh xem khách hàng là gì ở tệp 1 và không có ở tệp 2

   Dưới đây là cách bạn so sánh hai tệp Excel và tạo báo cáo so sánh

   1. Mở https. //www. so sánh xl. mạng lưới/
   2. Sử dụng tùy chọn Chọn tệp để tải lên hai tệp [kích thước tối đa của mỗi tệp có thể là 5MB]
   3. Nhấp vào nút Tiếp theo
   4. Chọn cột chung trong cả hai tệp này. Công cụ sẽ sử dụng cột chung này để tìm kiếm sự phù hợp và khác biệt
   5. Chọn một trong bốn tùy chọn, cho dù bạn muốn nhận dữ liệu phù hợp hay dữ liệu khác nhau [dựa trên Tệp 1 hoặc Tệp 2]
   6. Nhấp vào Tiếp theo
   7. Tải xuống tệp so sánh sẽ có dữ liệu [dựa trên tùy chọn bạn đã chọn ở bước 5]

   Dưới đây là video hướng dẫn cách thức hoạt động của công cụ XL Comparator

   Một mối quan tâm mà bạn có thể gặp phải khi sử dụng công cụ của bên thứ ba để so sánh các tệp Excel là về quyền riêng tư

   Nếu bạn có dữ liệu bí mật và quyền riêng tư thực sự quan trọng đối với dữ liệu đó, thì tốt hơn là sử dụng các phương pháp khác được hiển thị ở trên

   Lưu ý rằng trang web XL Comparator đề cập rằng họ xóa tất cả các tệp sau 1 giờ thực hiện so sánh

   Đây là một số phương pháp bạn có thể sử dụng để so sánh hai tệp Excel khác nhau [hoặc trang tính trong cùng một tệp Excel]. Hy vọng bạn thấy hướng dẫn Excel này hữu ích

   Bạn cũng có thể thích các hướng dẫn Excel sau đây

   • Cách so sánh hai cột trong Excel [đối với sự trùng khớp và sự khác biệt]
   • Cách Xóa các bản sao trong Excel
   • Cách so sánh văn bản trong Excel [Công thức dễ dàng]
   • Tách từng trang tính Excel thành các tệp riêng biệt [từng bước]
   • Kết hợp dữ liệu từ nhiều sổ làm việc trong Excel
   • Kết hợp dữ liệu từ nhiều trang tính thành một trang tính duy nhất trong Excel
   • Cách so sánh ngày tháng trong Excel [Lớn hơn/Nhỏ hơn, Không khớp]

2 suy nghĩ về "Cách so sánh hai trang tính Excel [để biết sự khác biệt]"

1. Kev

   Tháng Mười Một 2020 lúc 6. 36 giờ chiều

   Cảm ơn vì điều này, rất hữu ích. Tuy nhiên, với 50 hàng và 20 cột để so sánh, e. g. JanValue20. FebValue16 vẫn ổn, ngoài ra tôi phải quay lại trang tính tháng 2 theo cách thủ công để xem sản phẩm nào trong ô R45 đã thay đổi giá trị