Absolute Excel-Referenz (Erstellung und Verwendung)

Beim Verweisen auf eine Zelle in einer Formel in Excel ist der Standardreferenztyp eine relative Referenz. Diese Referenzen ändern sich, wenn die Formel in andere Zellen kopiert wird, basierend auf deren relativer Spalte und Zeile. Wenn Sie eine Referenz unabhängig davon konstant halten möchten, wohin die Formel kopiert wird, müssen Sie eine absolute Referenz verwenden.

• Was ist ein absoluter Bezug
• So erstellen Sie absolute Bezüge
• Verwendung absoluter Bezüge anhand von Beispielen
• Prozentsatz des Gesamtwerts berechnen
• Einen Wert suchen und den zugehörigen Übereinstimmungswert zurückgeben
• 2 Klicks, um Zellbezüge mit Kutools batchweise absolut zu machen
• Relativer und gemischter Bezug
• Wichtige Hinweise

Laden Sie die Beispieldatei kostenlos herunter

Video: Absoluter Bezug

Was ist ein absoluter Bezug

Eine absolute Referenz ist ein Zellbezugstyp in Excel.

Im Gegensatz zu einer relativen Referenz, die sich beim Kopieren einer Formel in andere Zellen entsprechend ihrer relativen Position anpasst, bleibt eine absolute Referenz stets unverändert – egal, wohin Sie die Formel kopieren oder verschieben.

Eine absolute Referenz erstellen Sie, indem Sie vor die Spalten- und Zeilenangaben in der Formel jeweils ein Dollarzeichen ($) setzen. Um beispielsweise eine absolute Referenz auf Zelle A1 zu erzeugen, schreiben Sie $A$1.

Absolute Referenzen sind ideal, wenn Sie in einer Formel, die auf mehrere Zellen kopiert wird, stets auf dieselbe feste Zelle oder denselben festen Bereich verweisen möchten – ohne dass sich die Referenz dabei ändert.

Beispielsweise enthält der Bereich A4:C7 die Produktpreise, und Sie möchten für jedes Produkt die zu zahlende Steuer anhand des in Zelle B2 angegebenen Steuersatzes berechnen.

Wenn Sie in der Formel eine relative Referenz wie „=B5*B2“ verwenden, liefert das Ziehen des AutoAusfüll-Griffs nach unten falsche Ergebnisse – denn die Referenz auf Zelle B2 passt sich relativ zur jeweiligen Zeile an. So lautet die Formel in Zelle C6 dann „=B6*B3“ und in Zelle C7 „=B7*B4“.

Wenn Sie hingegen eine absolute Referenz auf Zelle B2 mit der Formel „=B5*$B$2“ verwenden, bleibt der Steuersatz beim Herunterziehen der Formel mit dem AutoAusfüll-Griff für alle Zellen gleich – und die Ergebnisse sind korrekt.

Verwendung eines relativen Bezugs		Verwendung eines absoluten Bezugs

So erstellen Sie absolute Bezüge

Um eine absolute Referenz in Excel zu erstellen, fügen Sie Dollarzeichen ($) vor die Spalten- und Zeilenbezeichnungen in einer Formel ein. Es gibt zwei Möglichkeiten, eine absolute Referenz zu erstellen:

Dollarzeichen manuell zur Zellreferenz hinzufügen

Sie können beim Eingeben der Formel in eine Zelle manuell Dollarzeichen ($) vor die Spalten- und Zeilenbezüge setzen, die absolut sein sollen.

Wenn Sie beispielsweise die Zahlen in den Zellen A1 und B1 addieren und beide Bezüge absolut setzen möchten, geben Sie die Formel als „=$A$1+$B$1“ ein. Dadurch bleibt sichergestellt, dass die Zellbezüge beim Kopieren oder Verschieben der Formel in andere Zellen unverändert bleiben.

Alternativ können Sie, um Referenzen in einer bestehenden Formel in absolute Referenzen zu verwandeln, einfach die Zelle auswählen und dann in der Formelleiste die Dollarzeichen ($) hinzufügen.

Tastenkürzel F4 verwenden, um relative Referenzen in absolute umzuwandeln

1. Doppelklicken Sie auf die Zelle mit der Formel, um in den Bearbeitungsmodus zu wechseln;
2. Platzieren Sie den Cursor auf den Zellbezug, den Sie absolut machen möchten;
3. Drücken Sie die Taste „F4“, um so lange zwischen den Bezugsarten zu wechseln, bis Dollarzeichen sowohl vor dem Spalten- als auch vor dem Zeilenbezug stehen;
4. Drücken Sie die Eingabetaste, um den Bearbeitungsmodus zu verlassen und Ihre Änderungen zu übernehmen.

Die Taste F4 wechselt abwechselnd zwischen relativen, absoluten und gemischten Bezügen.

A1 → $A$1 → A$1 → $A1 → A1

Möchten Sie alle Referenzen in einer Formel absolut setzen, markieren Sie die gesamte Formel in der Bearbeitungsleiste und drücken Sie die Taste „F4“, bis Dollarzeichen sowohl vor die Spalten- als auch vor die Zeilenbezeichnungen eingefügt wurden.

A1+B1 → $A$1++$B$1 → A$1++B$1 → $A1+$B1 → A1+B1

Verwendung absoluter Bezüge anhand von Beispielen

Dieser Abschnitt enthält zwei Beispiele, die veranschaulichen, wann und wie absolute Bezüge in einer Excel-Formel eingesetzt werden.

Beispiel 1: Prozentualen Anteil am Gesamtwert berechnen

Angenommen, Sie haben einen Datenbereich (A3:B7) mit den Verkäufen jeder Frucht und in Zelle B8 den Gesamtumsatz dieser Früchte. Jetzt möchten Sie den prozentualen Anteil jedes Fruchtverkaufs am Gesamtwert berechnen.

Allgemeine Formel zur Berechnung des prozentualen Anteils am Gesamtwert:

Percentage = Sale/Amount

Verwenden Sie die relative Referenz in der Formel, um den prozentualen Anteil der ersten Frucht wie folgt zu berechnen:

=B4/B8

Beim Ziehen des AutoAusfüll-Griffs nach unten zur Berechnung des prozentualen Anteils anderer Früchte werden #DIV/0!-Fehler angezeigt.

Denn beim Ziehen des AutoAusfüll-Griffs zum Kopieren der Formel in darunterliegende Zellen passt sich die relative Referenz B8 automatisch entsprechend ihrer relativen Position an – zu B9, B10 und B11. Da diese Zellen jedoch leer (null) sind und eine Division durch null einen Fehler verursacht, gibt die Formel einen Fehler zurück.

Um die Fehler zu beheben, müssen Sie in diesem Fall die Zellreferenz B8 in der Formel absolut ($B$8) machen, damit sie beim Verschieben oder Kopieren der Formel unverändert bleibt. Die Formel wird nun aktualisiert zu:

=B4/$B$8

Ziehen Sie anschließend den AutoAusfüll-Griff nach unten, um die prozentualen Anteile der übrigen Früchte zu berechnen.

Beispiel 2: Einen Wert suchen und den zugehörigen passenden Wert zurückgeben

Angenommen, Sie möchten in der Namensliste D4:D5 suchen und die zugehörigen Gehälter basierend auf den Mitarbeiternamen sowie dem entsprechenden Jahresgehalt im Bereich A4:B8 abrufen.

Allgemeine Suchformel:

=VLOOKUP(lookup_value, table_range, column_index, logical)

Wenn Sie die relative Referenz in der Formel verwenden, um einen Wert zu suchen und den zugehörigen passenden Wert wie folgt zurückzugeben:

=VLOOKUP(D4,A4:B8,2,FALSE)

Ziehen Sie anschließend den AutoAusfüll-Griff nach unten, um den darunterliegenden Wert zu suchen – es wird ein Fehler zurückgegeben.

Beim Ziehen des Ausfüllgriffs nach unten, um die Formel in die darunterliegende Zelle zu kopieren, passen sich die Bezüge in der Formel automatisch um eine Zeile nach unten an. Dadurch wird aus dem ursprünglichen Tabellenbereich A4:B8 der Bereich A5:B9. Da Lisa im Bereich A5:B9 nicht gefunden wird, gibt die Formel einen Fehler zurück.

Um Fehler zu vermeiden, verwenden Sie stattdessen die absolute Referenz $A$4:$B$8 anstelle der relativen Referenz A4:B8 in der Formel:

=VLOOKUP(D4,$A$4:$B$8,2,FALSE)

Ziehen Sie anschließend den AutoAusfüll-Griff nach unten, um Lisas Gehalt zu übernehmen.

2 Klicks, um Zellreferenzen mit Kutools stapelweise absolut zu machen

Unabhängig davon, ob Sie manuell eingeben oder die F4-Tastenkombination verwenden, können Sie in Excel jeweils nur eine Formel gleichzeitig ändern. Wenn Sie Zellreferenzen in Hunderten von Formeln absolut machen möchten, hilft Ihnen das Werkzeug „Zellreferenzen konvertieren“ von „Kutools für Excel“ dabei, die Aufgabe mit 2 Klicks zu erledigen.

Um Zellreferenzen in mehreren Formeln absolut zu machen, wählen Sie die entsprechenden Formelzellen aus und klicken Sie auf „Kutools“ > „Mehr“ > „Zellreferenzen konvertieren“. Wählen Sie anschließend die Option „In absolut umwandeln“ und bestätigen Sie mit „OK“ oder „Anwenden“. Alle Zellreferenzen in den markierten Formeln sind nun in absolute Referenzen konvertiert.

Relativer und gemischter Bezug

Neben der absoluten Referenz gibt es zwei weitere Referenztypen: die relative und die gemischte Referenz.

Relative Referenz ist der Standardreferenztyp in Excel – hier stehen keine Dollarzeichen ($) vor Zeilen- oder Spaltenangaben. Wird eine Formel mit relativen Referenzen in andere Zellen kopiert oder verschoben, passen sich die Bezüge automatisch an ihre neue relative Position an.

Wenn Sie beispielsweise die Formel „=A1+1“ in eine Zelle eingeben und den AutoAusfüll-Griff nach unten ziehen, um sie in die nächste Zelle zu übertragen, passt Excel die Formel automatisch an – sie lautet dann „=A2+1“.

Gemischte Referenz kombiniert eine absolute und eine relative Referenz. Das heißt: Bei einer gemischten Referenz wird das Dollarzeichen ($), um beim Kopieren oder Ausfüllen einer Formel entweder die Zeile oder die Spalte festzulegen.

Nehmen Sie zum Beispiel eine Multiplikationstabelle, deren Zeilen und Spalten die Zahlen von 1 bis 9 enthalten – genau die Zahlen, die Sie miteinander multiplizieren möchten.

Beginnen Sie zunächst in Zelle C3 mit der Formel „=B3*C2“, um die 1 in Zelle B3 mit der Zahl (1) in der ersten Spalte zu multiplizieren. Wenn Sie jedoch den AutoAusfüll-Griff nach rechts ziehen, um die übrigen Zellen auszufüllen, werden Sie feststellen, dass alle Ergebnisse – bis auf das erste – falsch sind.

Dies liegt daran, dass sich beim Kopieren der Formel nach rechts die Zeilenposition nicht ändert, die Spaltenposition jedoch von B3 auf C3, D3 usw. verschiebt. Folglich lauten die Formeln in den Zellen rechts (D3, E3 usw.) „=C3*D2“, „=D3*E2“ usw., obwohl Sie eigentlich „=B3*D2“, „=B3*E2“ usw. benötigen.

In diesem Fall müssen Sie ein Dollarzeichen ($) hinzufügen, um die Spaltenreferenz in „B3“ zu fixieren. Verwenden Sie dazu folgende Formel:

=$B3*C2

Wenn Sie die Formel nun nach rechts ziehen, sind die Ergebnisse korrekt.

Anschließend multiplizieren Sie die Zahl 1 in Zelle C2 mit den Zahlen in den darunterliegenden Zeilen.

Beim Kopieren der Formel nach unten bleibt die Spaltenposition von Zelle C2 unverändert, während sich die Zeilenposition von C2 auf C3, C4 usw. anpasst. Dadurch lauten die Formeln in den darunterliegenden Zellen „=$B4*C3“, „=$B5*C4“ usw., was zu falschen Ergebnissen führt.

Um dieses Problem zu lösen, ändern Sie „C2“ in „C$2“, damit die Zeilenreferenz beim Ziehen des AutoAusfüll-Handles nach unten konstant bleibt und die Formeln korrekt ausgefüllt werden.

=$B3*C$2

Ziehen Sie jetzt das AutoAusfüll-Handle nach rechts oder unten, um alle Ergebnisse zu erhalten.

Wichtige Hinweise

• Zusammenfassung der Zellbezüge

  Typ	Beispiel	Zusammenfassung
  Absoluter Bezug	$A$1	Ändert sich niemals, wenn die Formel in andere Zellen kopiert wird
  Relativer Bezug	A1	Sowohl Zeilen- als auch Spaltenbezug ändern sich entsprechend der relativen Position, wenn die Formel in andere Zellen kopiert wird
  Gemischter Bezug	$A1/A$1	Der Zeilenbezug ändert sich beim Kopieren der Formel in andere Zellen, während der Spaltenbezug fixiert bleibt / Der Spaltenbezug ändert sich beim Kopieren der Formel in andere Zellen, während der Zeilenbezug fixiert bleibt;

• Im Allgemeinen bleiben absolute Bezüge unverändert, wenn eine Formel verschoben wird. Werden jedoch am oberen Rand oder links im Arbeitsblatt Zeilen oder Spalten eingefügt oder gelöscht, passen sich absolute Bezüge automatisch an. Beispiel: Die Formel „=$A$1+1“ ändert sich automatisch zu „=$A$2+1“, sobald eine Zeile am oberen Rand des Arbeitsblatts eingefügt wird.

  

• Mit der Taste „F4“ wechseln Sie ganz einfach zwischen relativem, absolutem und gemischtem Bezug.