Jeder weiß, dass Sie eine Formel in Excel eingeben können. (Was wäre eine Tabelle ohne Formeln?) Wenn Sie Adressreferenzen in einer Formel verwenden, werden diese Referenzen automatisch aktualisiert, wenn Sie Zellen, Zeilen oder Spalten einfügen oder löschen. Diese Änderungen wirken sich in irgendeiner Weise auf die Adressreferenz aus. Betrachten Sie zum Beispiel die folgende einfache Formel:

=IF(A7=B7,"YES","NO")

Wenn Sie eine Zelle über B7 einfügen, wird die Formel von Excel automatisch so angepasst, dass sie folgendermaßen aussieht:

=IF(A7=B8,"YES","NO")

Was ist, wenn Excel die Formel jedoch nicht anpassen soll? Sie können versuchen, der Adresse einige Dollarzeichen hinzuzufügen, dies wirkt sich jedoch nur auf Adressen in Formeln aus, die später kopiert werden. Es wirkt sich nicht auf die Formel selbst aus, wenn Sie Zellen einfügen oder löschen, die sich auf die Formel auswirken.

Der beste Weg, um die Formelreferenzen „nicht anpassbar“ zu machen, besteht darin, die Formel selbst zu ändern, um verschiedene Arbeitsblattfunktionen zu verwenden. Sie können diese Formel beispielsweise in Zelle C7 verwenden:

=IF(INDIRECT("A"&ROW(C7))=INDIRECT("B"&ROW(C7)),"YES","NO")

Diese Formel erstellt eine Adresse basierend auf der Zelle, in der die Formel angezeigt wird. Die ROW-Funktion gibt die Zeilennummer der Zelle zurück (in diesem Fall C7, der zurückgegebene Wert ist also 7), und dann wird die INDIRECT-Funktion verwendet, um auf die erstellte Adresse zu verweisen. wie A7 und B7. Wenn Sie Zellen über A7 oder B7 einfügen (oder löschen), wird die Referenz in Zelle C7 nicht gestört, da nur eine brandneue Adresse erstellt wird.

Ein anderer Ansatz besteht darin, die OFFSET-Funktion zu verwenden, um einen ähnlichen Referenztyp zu erstellen:

=IF(OFFSET($A$1,ROW()-1,0)=OFFSET($B$1,ROW()-1,0),"YES","NO")

Diese Formel betrachtet einfach, wo sie sich befindet (in Spalte C) und vergleicht die Werte in den Zellen links davon. Diese Formel ist ähnlich ungestört, wenn Sie Zellen in Spalte A oder B einfügen oder löschen.

Ein letzter Ansatz (und vielleicht der schickste) ist die Verwendung benannter Formeln. Dies ist eine Funktion der Benennungsfunktionen von Excel, die von den meisten Benutzern nur selten verwendet wird. Befolgen Sie diese Schritte:

  1. Wählen Sie Zelle C2 aus.

  2. Zeigen Sie die Registerkarte Formeln des Menübands an.

  3. Klicken Sie in der Gruppe Definierte Namen auf das Werkzeug Name definieren. Excel zeigt das Dialogfeld Neuer Name an. (Siehe Abbildung 1.)

  4. Geben Sie im Feld Name den Namen CompareMe ein. (Sie können auf Wunsch einen anderen Namen verwenden.)

  5. Löschen Sie alles, was sich im Feld Verweise auf befindet, und ersetzen Sie es durch die folgende Formel:

  6. OK klicken.

Zu diesem Zeitpunkt haben Sie Ihre benannte Formel erstellt. Sie können es jetzt in jeder Zelle in Spalte C folgendermaßen verwenden:

=CompareMe

Es vergleicht alles, was sich in den beiden Zellen links davon befindet, genau so, wie es Ihre ursprüngliche Formel entworfen hat. Besser noch, die Formel wird beim Einfügen oder Löschen von Zellen nicht automatisch angepasst.

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

Dieser Tipp (12348) gilt für Microsoft Excel 2007, 2010, 2013 und 2016.

Eine Version dieses Tipps für die ältere Menüoberfläche von Excel finden Sie hier:

Nicht anpassende Referenzen in Formeln.