Rick hat zwei Arbeitsblätter, die er miteinander vergleichen muss, um Unterschiede hervorzuheben. Der Vergleich muss nicht vergleichen, was angezeigt wird, sondern die Formeln in jeder der Zellen. Auf diese Weise hofft Rick herauszufinden, wo sich die Formeln auf jedem Arbeitsblatt unterscheiden.

Es gibt verschiedene Möglichkeiten, Formeln zu vergleichen. In bestimmten Versionen von Excel haben Sie Zugriff auf ein Vergleichs-Add-In, das die Aufgabe für Sie erledigen kann. Wenn Sie Office Professional Plus 2013 oder Office 365 ProPlus verwenden, können Sie das Add-In Spreadsheet Compare verwenden.

Informationen zu diesem Add-In finden Sie hier:

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

Wenn Sie Office Professional Plus 2013 oder Office 365 verwenden, können Sie auch das Add-In Spreadsheet Inquire verwenden. Informationen zu diesem Add-In finden Sie hier:

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

Da Sie erkennen, dass nicht jeder eine dieser Excel-Versionen verwendet oder möglicherweise kein Add-In verwenden möchte, können Sie andere Maßnahmen ergreifen. In Excel 2013 und späteren Versionen gibt es eine praktische Arbeitsblattfunktion namens FORMULATEXT. Mit dieser Funktion können Sie die in einer Zelle gespeicherte Formel folgendermaßen abrufen:

=FORMULATEXT(A7)

Dies gibt die in Zelle A7 enthaltene Formel zurück (in diesem Fall). Wenn die Zelle keine Formel enthält, wird ein # N / A-Fehler zurückgegeben. Mit diesem Verhalten können Sie in einem „Vergleichsarbeitsblatt“ einen Indikator dafür erstellen, ob die Formeln gleich sind oder nicht. Erstellen Sie einfach das neue Arbeitsblatt und platzieren Sie es in Zelle A1:

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

Kopieren Sie die Formel so weit nach unten und so weit wie gewünscht nach rechts. Es markiert Unterschiede zwischen den entsprechenden Zellen in Blatt1 und Blatt2.

Denken Sie daran, dass FORMULATEXT in Excel 2013 eingeführt wurde, sodass dieser Ansatz in älteren Excel-Versionen nicht funktioniert. Wenn Sie eine andere Version verwenden (oder sogar Excel 2013 verwenden), können Sie ein Makro verwenden, um die Unterschiede zwischen Arbeitsblättern zu markieren. Es gibt viele Makroansätze, die Sie verwenden können. Das Folgende ist eine kurze Möglichkeit, den Vergleich durchzuführen.

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

Um dieses Makro zu verwenden, zeigen Sie die Arbeitsmappe an, die Sie vergleichen möchten. Es wird davon ausgegangen, dass Sie mit denselben Zellen in einem Arbeitsblatt namens „Master“ vergleichen möchten.

(Sie können dies natürlich im Makro ändern, wenn Ihr „Standard“ -Arbeitsblatt einen anderen Namen hat.) Jede Zelle im aktuellen Arbeitsblatt wird mit der entsprechenden Zelle im „Master“ -Arbeitsblatt verglichen. Wenn die Zellen Formeln enthalten und diese Formeln unterschiedlich sind, wird die Hintergrundfarbe der Zelle im aktuellen Arbeitsblatt in Rot geändert.

Ein solcher Ansatz ändert offensichtlich die Formatierung des zu vergleichenden Arbeitsblatts. Wenn Sie die Formatierung nicht ändern möchten, sondern einfach eine Liste von Zellen mit Unterschieden wünschen, können Sie die folgende Variante des Makros verwenden:

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

Sie können auch eine benutzerdefinierte Funktion (UDF) erstellen, die Bereiche für die Vergleiche akzeptiert. Auf diese Weise können Sie es auf verschiedene Arten verwenden.

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

Wenn Sie nur bestätigen möchten, dass ein Zellbereich in beiden Arbeitsblättern identische Formeln hat, können Sie einfach Folgendes verwenden:

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

Die Funktion gibt TRUE zurück, wenn alle Zellen identische Formeln haben, FALSE, wenn eine der Zellen unterschiedliche Formeln hat, oder #Value error, wenn die beiden Bereiche nicht dieselbe Größe haben.

Wenn Sie Unterschiede hervorheben möchten, können Sie die UDF innerhalb einer bedingten Formatierungsregel verwenden. Angenommen, Sie möchten das bedingte Format auf die Zellen in Sheet1 anwenden, geben Sie einfach an, dass die Regel eine Formel verwenden soll, und verwenden Sie diese dann als Formel:

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

Wenn eine der Zellen in Sheet1 nicht mit den entsprechenden Zellen in Sheet2 übereinstimmt, werden sie gemäß der in der bedingten Formatierungsregel definierten Formatierung formatiert.

_Hinweis: _

Wenn Sie wissen möchten, wie die auf dieser Seite (oder auf einer anderen Seite der ExcelTips-Websites) beschriebenen Makros verwendet werden, habe ich eine spezielle Seite vorbereitet, die hilfreiche Informationen enthält.

Klicken Sie hier, um diese spezielle Seite in einem neuen Browser-Tab zu öffnen.

ExcelTips ist Ihre Quelle für kostengünstige Microsoft Excel-Schulungen.

Dieser Tipp (13400) gilt für Microsoft Excel 2007, 2010, 2013, 2016, 2019 und Excel in Office 365.