Wie kann man schnell $ in Excel-Formeln einfügen?

In Excel sind Zellbezüge in Formeln standardmäßig relativ, wie z. B. "A1". Das bedeutet, wenn Sie eine Formel in eine andere Position kopieren oder ziehen, werden die Bezüge automatisch aktualisiert, um ihre neuen Positionen widerzuspiegeln. Zum Beispiel, wenn Sie die Formel =A1+B1 in Zelle C1 eingeben und dann nach H12 kopieren, wird sich die Formel automatisch anpassen zu =F12+G12. Dieses Verhalten ist in vielen Situationen hilfreich, kann aber Fehler verursachen, wenn Sie immer auf bestimmte Zellen verweisen müssen, unabhängig davon, wo die Formel platziert wird. Um diese automatische Anpassung zu verhindern, müssen Sie die Zellbezüge durch Hinzufügen des Dollarzeichens ($) in absolute Bezüge ändern, wie $A$1. Absolute Bezüge bleiben beim Kopieren oder Ziehen konstant, was sie für Berechnungen mit festen Werten oder Bereichen unerlässlich macht.

Dieser Leitfaden stellt mehrere praktische Methoden vor, um schnell $-Zeichen zu Excel-Formeln hinzuzufügen, damit Sie besser steuern können, wie Ihre Formeln auf Zellen verweisen. Die Wahl einer geeigneten Methode hängt von Ihrer Arbeitsblattstruktur, der Anzahl der zu konvertierenden Formeln, Ihrer Excel-Version und persönlichen Arbeitsabläufen ab. Im Folgenden finden Sie eine Zusammenfassung der in diesem Leitfaden behandelten Methoden:

$ mit Tastenkombinationen zur Formel hinzufügen

$ mit Kutools für Excel zur Formel hinzufügen

Verwenden Sie ein VBA-Makro, um Zellbezüge im Batchverfahren in absolute Bezüge umzuwandeln

$ mit Tastenkombinationen zur Formel hinzufügen

Sie können ganz einfach zwischen verschiedenen Arten von Zellbezügen – relativ, absolut und gemischt – mit Tastenkombinationen wechseln. Um einen Zellbezug zu ändern, wählen Sie einfach die Zielzelle aus, klicken Sie in die Formelleiste und platzieren Sie den Cursor innerhalb oder neben dem Zellbezug, den Sie ändern möchten. Drücken Sie dann die F4 Taste, um durch die verfügbaren Bezugstypen zu blättern. Jeder Druck auf F4 aktualisiert den Bezug in dieser Reihenfolge: relativ (A1), absolut ($A$1), gemischt (Spalte fixiert) (A$1) und gemischt (Zeile fixiert) ($A1).

Diese Methode eignet sich zum schnellen Aktualisieren einzelner Bezüge innerhalb einer Formel. Allerdings erfordert sie das manuelle Auswählen jedes Zellbezugs, was zeitaufwendig sein kann, wenn Sie viele oder komplexe Formeln mit mehreren Zellbezügen haben. Stellen Sie außerdem sicher, dass Sie die Formel direkt in der Formelleiste oder in der Zelle bearbeiten. Wenn die Tastenkombination nicht funktioniert, überprüfen Sie, ob die Funktionssperre (Fn) auf Ihrer Tastatur korrekt eingestellt ist, da einige Tastaturen möglicherweise Fn + F4 erfordern.

$ mit Kutools für Excel zur Formel hinzufügen

Die F4-Tastenkombination kann jeweils nur einen Zellbezug bearbeiten, was ineffizient ist, wenn Sie mehrere Zellen oder einen ganzen Bereich von Formeln aktualisieren müssen. Wenn Sie mehrere Formeln verarbeiten oder Bezugstypen in einem Arbeitsblatt standardisieren müssen, bietet Kutools für Excel eine effiziente Lösung mit seinem Zellreferenzen-Konvertierungstool.

Nach der kostenlosen Installation Kutools für Excel können Sie die folgenden Schritte ausführen:

1. Wählen Sie alle Formelzellen aus, denen Sie $-Zeichen hinzufügen möchten (Bezüge absolut machen). Gehen Sie dann zu Kutools > Mehr > Zellreferenzen konvertieren. Siehe Screenshot:

2. Wählen Sie im erscheinenden Dialogfeld den benötigten Bezugstyp aus. Um alle Zellbezüge absolut zu machen, wählen Sie die Option „In absolut“.

3. Klicken Sie OK, und alle ausgewählten Formelzellbezüge werden entsprechend aktualisiert.
Wenn Ihre Formeln Daten von externen Blättern oder Arbeitsmappen referenzieren, überprüfen Sie die Änderungen, um sicherzustellen, dass die verknüpften Daten weiterhin korrekt sind, da in einigen Szenarien bestimmte Bezüge relativ bleiben müssen. Mit Kutools können Sie Änderungen auch leicht rückgängig machen und Bezüge bei Bedarf in relative oder gemischte Typen umwandeln.

Dieser Ansatz ist vorteilhaft für die Verarbeitung großer Mengen von Formeln oder bei Bedarf an Batchoperationen. Kutools bietet auch eine Rückgängig-Funktion, sodass Sie Aktionen bei Fehlern leicht rückgängig machen können. 

Verwenden Sie ein VBA-Makro, um Zellbezüge im Batchverfahren in absolute Bezüge umzuwandeln

Wenn Sie häufig alle Zellbezüge innerhalb von Formeln in absoluten Bezügen ($A$1 Format) über einen Bereich oder das gesamte Arbeitsblatt hinweg konvertieren müssen, ist die Verwendung von VBA-Makros eine effiziente und flexible Lösung. Diese Methode ist besonders nützlich für fortgeschrittene Excel-Benutzer oder bei der Arbeit mit großen Datensätzen, da sie den Konvertierungsprozess automatisiert und komplexe Formelstrukturen in einem Durchgang verarbeiten kann. Wenn Sie Formeln haben, die auf externe Blätter oder Arbeitsmappen verlinken, stellen Sie sicher, dass absolute Bezüge für Ihr Szenario angemessen sind – manchmal werden gemischte Bezüge bevorzugt, um Flexibilität der Formeln zu ermöglichen.

1. Gehen Sie auf der Excel-Menüleiste zu Entwicklertools > Visual Basic. Klicken Sie im Fenster Microsoft Visual Basic für Applikationen auf Einfügen > Modul und fügen Sie das folgende Makro in das neue Modul ein:

Sub ConvertToAbsoluteReferences()
    Dim rng As Range
    Dim cell As Range
    Dim formulaStr As String
    Dim i As Integer
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set rng = Application.Selection
    Set rng = Application.InputBox("Select the range to convert formulas to absolute references", xTitleId, rng.Address, Type:=8)
    
    Application.ScreenUpdating = False
    
    For Each cell In rng
        If cell.HasFormula Then
            formulaStr = cell.Formula
            For i = 1 To 10 ' Protects from infinite looping
                formulaStr = Replace(formulaStr, "([A-Za-z]+)([0-9]+)", "$$1$$2") ' Basic pattern replacement
            Next i
            cell.Formula = Application.ConvertFormula(formulaStr, xlA1, xlA1, xlAbsolute)
        End If
    Next
    
    Application.ScreenUpdating = True
End Sub

2. Klicken Sie danach auf Ausführen, um den Code auszuführen. Ein Dialogfeld wird angezeigt, das Sie auffordert, den Bereich auszuwählen, den Sie verarbeiten möchten. Nach Bestätigung werden alle gültigen Zellbezüge in Formeln innerhalb des Bereichs automatisch in den absoluten Bezugstil (mit $-Zeichen) umgewandelt.

Wenn Sie auf Fehler stoßen, überprüfen Sie, ob Ihr ausgewählter Bereich Formeln enthält. Dieses Makro wandelt Bezüge in Standard-Excel-Formeln um; spezielle Array-Formeln oder nicht standardisierte Funktionen werden möglicherweise nicht wie erwartet behandelt, daher überprüfen Sie die betroffenen Zellen nach der Ausführung. Speichern Sie immer eine Sicherungskopie Ihrer Arbeitsmappe, bevor Sie Makros ausführen, insbesondere bei wichtigen oder sensiblen Daten.

Die Verwendung von VBA ermöglicht vollständige Stapelverarbeitung, was es für Poweruser, IT-Mitarbeiter oder solche, die in Umgebungen arbeiten, in denen Installationen eingeschränkt sind, geeignet macht. Der Betrieb von Makros kann jedoch aufgrund von Sicherheitseinstellungen auf einigen Systemen deaktiviert sein, in diesem Fall müssen Sie Makros in Ihren Excel-Vertrauenseinstellungen aktivieren.

Zusammenfassende Empfehlung: Wenn Sie entscheiden, wie Sie Zellbezüge in Excel in absolute Bezüge umwandeln, berücksichtigen Sie die Anzahl der zu ändernden Formeln und Ihre Vertrautheit mit Excel-Funktionen. Für einzelne Formeln oder Ad-hoc-Änderungen sind Tastenkombinationen schnell und bequem. Für Batchoperationen sparen Kutools für Excel und VBA-Makros erheblich manuelle Arbeit. Überprüfen Sie die Ergebnisse nach der Konvertierung immer doppelt, insbesondere in komplexen Arbeitsblättern oder bei Verwendung automatisierter Tools. Falls Sie auf Fehler oder unerwartete Ergebnisse stoßen, stellen Sie aus Ihrer Sicherung wieder her oder verwenden Sie wann immer möglich die Rückgängig-Funktion.