Rick có hai bảng tính mà anh ấy cần so sánh với nhau để làm nổi bật sự khác biệt. So sánh không cần phải so sánh những gì được hiển thị, mà là các công thức trong mỗi ô. Bằng cách này, Rick hy vọng sẽ khám phá ra điểm khác nhau của các công thức trên mỗi trang tính.

Có một số cách bạn có thể sử dụng để so sánh các công thức. Trong các phiên bản Excel nhất định, bạn có quyền truy cập vào một bổ trợ so sánh có thể xử lý công việc cho bạn. Nếu bạn đang sử dụng Office Professional Plus 2013 hoặc Office 365 ProPlus, bạn có thể sử dụng bổ trợ So sánh Bảng tính.

Thông tin về bổ trợ này có thể được tìm thấy tại đây:

https://support.office.com/en-us/article/Overview-of-Spreadsheet-Compare-13fafa61-62aa-451b-8674-242ce5f2c986

Nếu bạn đang sử dụng Office Professional Plus 2013 hoặc Office 365, bạn cũng có thể sử dụng bổ trợ Yêu cầu bảng tính. Thông tin về bổ trợ này có tại đây:

https://support.office.com/en-nz/article/What-you-can-do-with-Spreadsheet-Inquire-ebaf3d62-2af5-4cb1-af7d-e958cc5fad42

Thừa nhận rằng không phải ai cũng đang sử dụng một trong các phiên bản Excel này hoặc có thể không muốn sử dụng bổ trợ, bạn có thể làm những việc khác. Trong Excel 2013 và các phiên bản mới hơn, có một hàm trang tính tiện dụng được gọi là FORMULATEXT. Bạn có thể sử dụng hàm này để truy xuất công thức được lưu trữ trong một ô, theo cách này:

=FORMULATEXT(A7)

Điều này trả về công thức có trong ô A7 (trong trường hợp này). Nếu ô không chứa công thức, thì nó sẽ trả về lỗi # N / A. Bạn có thể sử dụng hành vi này để tạo trong “trang tính so sánh” một chỉ báo về việc các công thức có bằng nhau hay không. Chỉ cần tạo trang tính mới và đặt nó vào ô A1:

=IF(FORMULATEXT(Sheet1!A1)=FORMULATEXT(Sheet2!A1),"","Different")

Sao chép công thức xuống phía dưới và bên phải theo ý muốn. Nó đánh dấu sự khác biệt giữa các ô tương ứng trên Sheet1 và Sheet2.

Hãy nhớ rằng FORMULATEXT đã được giới thiệu trong Excel 2013, vì vậy phương pháp này sẽ không hoạt động trong các phiên bản Excel cũ hơn. Nếu bạn đang sử dụng phiên bản khác (hoặc thậm chí, nếu bạn đang sử dụng Excel 2013), bạn có thể sử dụng macro để đánh dấu sự khác biệt giữa các trang tính. Có nhiều cách tiếp cận vĩ mô bạn có thể sử dụng; sau đây là một cách ngắn gọn để thực hiện so sánh.

Sub ComparaFormulas1()

Dim Check As Worksheet     Dim Master As Worksheet     Dim c As Range

Set Check = ActiveSheet     Set Master = Worksheets("Master")



For Each c In Check.UsedRange         If c.HasFormula Then             If c.Formula <> Master.Range(c.Address).Formula Then                 c.Interior.Color = RGB(255, 0, 0)

End If         End If     Next c End Sub

Để sử dụng macro này, hãy hiển thị sổ làm việc bạn muốn so sánh. Nó giả định rằng bạn muốn so sánh với các ô giống nhau trong một trang tính được gọi là “Master”.

(Rõ ràng bạn có thể thay đổi điều đó trong macro nếu trang tính “chuẩn” của bạn có tên khác.) Mỗi ​​ô trên trang tính hiện tại được so sánh với ô tương ứng trên trang tính “chính”. Nếu các ô chứa công thức và các công thức đó khác nhau, thì màu nền của ô sẽ được thay đổi thành màu đỏ trong trang tính hiện tại.

Cách tiếp cận như vậy rõ ràng sẽ thay đổi định dạng của trang tính đang được so sánh. Nếu bạn không muốn thay đổi định dạng mà chỉ muốn có một danh sách các ô có sự khác biệt, bạn có thể sử dụng biến thể sau trên macro:

Sub ComparaFormulas2()

Dim Check As Worksheet     Dim Master As Worksheet     Dim c As Range     Dim sTemp As String     Dim lDif As Long

Set Check = ActiveSheet     Set Master = Worksheets("Master")

sTemp = ""

lDif = 0

For Each c In Check.UsedRange         If c.HasFormula Then             If c.Formula <> Master.Range(c.Address).Formula Then                 lDif = lDif + 1                 sTemp = sTemp & vbCrLf & lDif & ": " & c.Address             End If         End If     Next c     If lDif > 0 Then         sTemp = "These were the differences" & vbCrLf & sTemp     Else         sTemp = "There were no differences"

End If     MsgBox sTemp End Sub

Bạn cũng có thể tạo một hàm do người dùng xác định (UDF) chấp nhận các phạm vi cho phép so sánh. Bằng cách đó, bạn có thể sử dụng nó theo nhiều cách khác nhau.

Function CompareFormulas3(rng1 As Range, rng2 As Range)

Dim x As Long

If rng1.Count <> rng2.Count Then         'Range sizes do not match         CompareFormulas = CVErr(xlValue)

Else         CompareFormulas = True    ' Assume all the same         For x = 1 To rng1.Count             If rng1(x).Formula <> rng2(x).Formula Then                 'Formulas do not match                 CompareFormulas = False                 x = rng1.Count    ' No need to keep comparing             End If         Next x     End If End Function

Nếu bạn chỉ muốn xác nhận rằng một loạt các ô trong cả hai trang tính có công thức giống hệt nhau, bạn chỉ cần sử dụng một cái gì đó như:

=CompareFormulas3(Sheet1!A1:Z1000,Sheet2!A1:Z1000)

Hàm trả về TRUE nếu tất cả các ô có công thức giống nhau, FALSE nếu bất kỳ ô nào có công thức khác nhau hoặc lỗi #Value nếu 2 phạm vi không cùng kích thước.

Nếu bạn muốn làm nổi bật sự khác biệt, bạn có thể sử dụng UDF trong quy tắc định dạng có điều kiện. Giả sử bạn muốn áp dụng định dạng có điều kiện cho các ô trong Sheet1, chỉ cần chỉ định rằng quy tắc nên sử dụng một công thức và sau đó sử dụng nó làm công thức:

=NOT(CompareFormulas3(Sheet2!A1,A1))

Nếu bất kỳ ô nào trong Trang tính1 không khớp với các ô tương ứng trong Trang tính2, chúng được định dạng theo bất kỳ định dạng nào bạn đã xác định trong quy tắc định dạng có điều kiện.

_Lưu ý: _

Nếu bạn muốn biết cách sử dụng các macro được mô tả trên trang này (hoặc trên bất kỳ trang nào khác trên trang ExcelTips), tôi đã chuẩn bị một trang đặc biệt bao gồm thông tin hữu ích.

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (13400) áp dụng cho Microsoft Excel 2007, 2010, 2013, 2016, 2019 và Excel trong Office 365.