Absolute in relative Zellbezüge umwandeln und umgekehrt

Mit den folgenden VBA-Makros wandeln Sie absolute und relative Zellbezüge in Excel schnell und zuverlässig um. So vereinheitlichen Sie Formeln, erleichtern das Kopieren von Berechnungen und halten Arbeitsblätter konsistent. Das Hauptziel besteht darin, absolute und relative Zellbezüge automatisiert zu konvertieren, ohne jede Formel manuell anpassen zu müssen.

Was sind absolute und relative Zellbezüge

1. Beim Kopieren einer Formel mit absoluten Bezügen zeigen die Verweise weiterhin exakt auf dieselben Zellen. Gemischte Bezüge fixieren nur Spalte oder Zeile, zum Beispiel $A1 oder A$
2. Die hier gezeigten Makros konvertieren vollständig in absolute oder vollständig in relative Bezüge.

Zellbezüge per VBA automatisch umwandeln

Das Makro absolut wandelt alle Formeln im ausgewählten Bereich in absolute Zellbezüge um. Das Makro realativ wandelt absolute in relative Zellbezüge um. Beide Makros berücksichtigen nur Zellen mit Formeln, die Auswahl entscheidet also, wo konvertiert wird. Möchten Sie nur einen bestimmten Zellbereich umwandeln, selektieren Sie diesen zunächst oder weisen Sie der Variablen conRange einen Bereich im Format Range("A1:B20") zu.

Makro für absolute Zellbezüge

Dieses Makro stellt alle Bezüge der ausgewählten Formeln auf absolute Zellbezüge um.

Option Explicit
Dim conRange As Range
Dim i%
Sub absolut()
Set conRange = Selection.SpecialCells(Type:=xlFormulas)
For i = 1 To conRange.Areas.Count
conRange.Areas(i).Formula = _
Application.ConvertFormula _
(Formula:=conRange.Areas(i).Formula, _
FromReferenceStyle:=xlA1, _
ToReferenceStyle:=xlA1, ToAbsolute:=xlAbsolute)
Next
End Sub

Makro für relative Zellbezüge

Dieses Makro stellt alle Bezüge der ausgewählten Formeln auf relative Zellbezüge um.

Sub realativ()
Set conRange = Selection.SpecialCells(Type:=xlFormulas)
For i = 1 To conRange.Areas.Count
conRange.Areas(i).Formula = _
Application.ConvertFormula _
(Formula:=conRange.Areas(i).Formula, _
FromReferenceStyle:=xlA1, _
ToReferenceStyle:=xlA1, ToAbsolute:=xlRelative)
Next
End Sub

Schritt-für-Schritt-Anleitung

1. Markieren Sie den Bereich, in dem die Zellbezüge angepasst werden sollen. Wenn Sie das gesamte Blatt konvertieren möchten, drücken Sie Strg A, um alles zu markieren.
2. Starten Sie den VBA-Editor mit Alt F11, fügen Sie ein Standardmodul ein und übernehmen Sie die Makros.
3. Kehren Sie zu Excel zurück und öffnen Sie den Makrodialog mit Alt F8.
4. Wählen Sie das gewünschte Makro aus. absolut für absolute Zellbezüge oder realativ für relative Zellbezüge.
5. Bestätigen Sie mit Ausführen. Nur Zellen mit Formeln innerhalb der Auswahl werden konvertiert.

Praxisbeispiele

1. Umwandlung in absolute Zellbezüge. Ausgangsformel in C1. =A1+B1. Nach Ausführung von absolut. =$A$1+$B$
2. Beim Kopieren der Formel nach rechts oder unten bleiben die Bezüge exakt auf A1 und B1 gerichtet.
3. Umwandlung in relative Zellbezüge. Ausgangsformel in C1. =$A$1+$B$
4. Nach Ausführung von realativ. =A1+B1. Beim Kopieren nach C2 wird daraus =A2+B2.

Nur einen bestimmten Bereich umwandeln

Die Makros arbeiten mit Selection.SpecialCells(Type:=xlFormulas). Um nur einen Teilbereich zu konvertieren, markieren Sie vor dem Start den gewünschten Bereich, zum Beispiel A1:B20. Das Makro findet darin automatisch die Formelzellen. Alternativ können Sie im Code der Variablen conRange einen festen Bereich zuweisen, zum Beispiel Range("A1:B20"), wenn Sie wiederkehrend immer denselben Abschnitt verarbeiten möchten.

Typische Fehlerquellen und hilfreiche Hinweise

Keine Formeln in der Auswahl. Selection.SpecialCells(Type:=xlFormulas) löst einen Laufzeitfehler aus, wenn im markierten Bereich keine Formeln vorhanden sind. Markieren Sie in diesem Fall einen Bereich mit Formeln oder das gesamte Blatt.

Gemischte Bezüge. Die gezeigten Makros konvertieren vollständig in absolute oder vollständig in relative Bezüge. Selektiv nur Zeile oder Spalte zu fixieren ist damit nicht abgedeckt.

Arrayformeln und große Bereiche. In großen Tabellen kann die Ausführung länger dauern, insbesondere bei vielen Bereichen innerhalb der Auswahl. Arbeiten Sie nach Möglichkeit abschnittsweise.

Strukturierte Verweise und Namen. Formeln mit Tabellenverweisen oder definierten Namen werden von Application.ConvertFormula in der Regel korrekt behandelt. Prüfen Sie das Ergebnis stichprobenartig, insbesondere bei komplexen Modellen oder externen Verknüpfungen.

Formatierungen und Konstanten. Nur Formelzellen werden geändert. Werte, Formate und Konstanten bleiben unverändert, ebenso Inhalte von Zellen ohne Formeln.

Fazit

Mit den beiden VBA-Makros stellen Sie absolute und relative Zellbezüge in Excel schnell um und sparen viel Zeit bei der Pflege von Formeln. Wählen Sie den relevanten Bereich aus, führen Sie das passende Makro aus und prüfen Sie das Ergebnis kurz nach. So bleiben Ihre Arbeitsmappen konsistent und gut wartbar.