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

• Was ist eine absolute Referenz?
• Wie man absolute Bezüge herstellt
• Verwenden Sie absolute Referenzen mit Beispielen
• Berechnen Sie den Prozentsatz der Gesamtsumme
• Suchen Sie nach einem Wert und kehren Sie zum entsprechenden Übereinstimmungswert zurück
• 2 Klicks, um Zellreferenzen mit Kutools stapelweise absolut zu machen
• Relative Referenz und gemischte Referenz
• Dinge zu erinnern

Laden Sie die Beispieldatei kostenlos herunter

Video: Absolute Referenz

Was ist eine absolute Referenz?

Ein absoluter Bezug ist eine Art Zellbezug in Excel.

Im Vergleich zu einem relativen Bezug, der sich basierend auf seiner relativen Position ändert, wenn eine Formel in andere Zellen kopiert wird, bleibt ein absoluter Bezug konstant, unabhängig davon, wohin die Formel kopiert oder verschoben wird.

Eine absolute Referenz wird erstellt, indem in der Formel vor den Spalten- und Zeilenreferenzen ein Dollarzeichen ($) hinzugefügt wird. Um beispielsweise eine absolute Referenz für Zelle A1 zu erstellen, sollten Sie diese als $A$1 darstellen.

Absolute Bezüge sind nützlich, wenn Sie auf eine feste Zelle oder einen festen Bereich in einer Formel verweisen möchten, die in mehrere Zellen kopiert wird, sich der Bezug aber nicht ändern soll.

Der Bereich A4:C7 enthält beispielsweise Produktpreise und Sie möchten die zu zahlende Steuer für jedes Produkt basierend auf dem Steuersatz in Zelle B2 ermitteln.

Wenn Sie in der Formel relative Referenzen wie „=B5*B2“ verwenden, werden einige falsche Ergebnisse zurückgegeben, wenn Sie den Ziehpunkt für das automatische Ausfüllen nach unten ziehen, um diese Formel anzuwenden. Da sich der Verweis auf Zelle B2 relativ zu den Zellen in der Formel ändert. Nun lautet die Formel in Zelle C6 „=B6*B3“ und die Formel in Zelle C7 „=B7*B4“.

Wenn Sie jedoch einen absoluten Verweis auf Zelle B2 mit der Formel „=B5*$B$2“ verwenden, wird sichergestellt, dass der Steuersatz für alle Zellen gleich bleibt, wenn die Formel mit dem Auto-Fill-Ziehpunkt nach unten gezogen wird. Die Ergebnisse sind korrekt.

Relative Referenz verwenden		Mit absoluter Referenz

Wie man absolute Bezüge herstellt

Um in Excel einen absoluten Bezug herzustellen, müssen Sie vor den Spalten- und Zeilenverweisen in der Formel Dollarzeichen ($) hinzufügen. Es gibt zwei Möglichkeiten, einen absoluten Bezug zu erstellen:

Fügen Sie der Zellreferenz manuell Dollarzeichen hinzu

Sie könnten manuell Dollarzeichen ($) vor den Spalten- und Zeilenverweisen hinzufügen, die Sie absolut machen möchten, während Sie die Formel in eine Zelle eingeben.

Wenn Sie beispielsweise die Zahlen in Zelle A1 und B1 addieren und beide absolut machen möchten, geben Sie die Formel einfach als „=$A$1+$B$1“ ein. Dadurch wird sichergestellt, dass die Zellbezüge konstant bleiben, wenn die Formel kopiert oder in andere Zellen verschoben wird.

Oder wenn Sie Referenzen in einer vorhandenen Formel in einer Zelle in absolute ändern möchten, können Sie die Zelle auswählen und dann zur Formelleiste gehen, um die Dollarzeichen ($) hinzuzufügen.

Verwenden Sie die Tastenkombination F4, um einen relativen Bezug in einen absoluten Bezug umzuwandeln

1. Doppelklicken Sie auf die Zelle mit der Formel, um in den Bearbeitungsmodus zu gelangen.
2. Platzieren Sie den Cursor an der Zellreferenz, die Sie absolut machen möchten;
3. Pressemitteilungen F4 Taste auf der Tastatur, um die Referenztypen zu wechseln, bis die Dollarzeichen vor den Spalten- und Zeilenreferenzen hinzugefügt werden;
4. Pressemitteilungen Weiter Mit der Taste verlassen Sie den Bearbeitungsmodus und übernehmen die Änderungen.

Mit der Taste F4 kann die Referenz zwischen relativer Referenz, absoluter Referenz und gemischter Referenz umgeschaltet werden.

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

Wenn Sie alle Bezüge in einer Formel absolut machen möchten, wählen Sie die gesamte Formel in der Bearbeitungsleiste aus und drücken Sie F4 Taste zum Umschalten der Referenztypen, bis die Dollarzeichen vor den Spalten- und Zeilenreferenzen hinzugefügt werden.

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

Verwenden Sie absolute Referenzen mit Beispielen

Dieser Teil enthält zwei Beispiele, die zeigen, wann und wie absolute Bezüge in einer Excel-Formel verwendet werden.

Beispiel 1 Berechnen Sie den Prozentsatz der Gesamtsumme

Angenommen, Sie haben einen Datenbereich (A3:B7), der die Verkäufe jeder Frucht enthält, und die Zelle B8 enthält die Gesamtverkaufsmenge dieser Früchte. Jetzt möchten Sie den Prozentsatz jedes Obstverkaufs am Gesamtumsatz berechnen.

Die generische Formel zur Berechnung des Prozentsatzes der Gesamtsumme:

Percentage = Sale/Amount

Verwenden Sie den relativen Bezug in der Formel, um den Prozentsatz der ersten Frucht wie folgt zu erhalten:

=B4/B8

Wenn Sie den Ziehpunkt für die automatische Füllung nach unten ziehen, um den Prozentsatz anderer Früchte zu berechnen, wird #DIV/0! Fehler werden zurückgegeben.

Wenn Sie den Griff zum automatischen Ausfüllen ziehen, um die Formel in die Zellen darunter zu kopieren, wird der relative Bezug B8 automatisch an andere Zellbezüge (B9, B10, B11) basierend auf deren relativen Positionen angepasst. Und die Zellen B9, B10 und B11 sind leer (Nullen). Wenn der Divisor Null ist, kehrt die Formel zu einem Fehler zurück.

Um die Fehler zu beheben, müssen Sie in diesem Fall den Zellbezug B8 in der Formel absolut ($B$8) machen, um zu verhindern, dass er sich ändert, wenn Sie die Formel an eine beliebige Stelle verschieben oder kopieren. Jetzt wird die Formel aktualisiert auf:

=B4/$B$8

Ziehen Sie dann den Ziehpunkt für die automatische Füllung nach unten, um den Prozentsatz anderer Früchte zu berechnen.

Beispiel 2 Suchen Sie nach einem Wert und kehren Sie zum entsprechenden Übereinstimmungswert zurück

Angenommen, Sie möchten nach der Namensliste in D4:D5 suchen und die entsprechenden Gehälter basierend auf den Mitarbeiternamen und dem entsprechenden Jahresgehalt im Bereich (A4:B8) zurückgeben.

Die allgemeine Formel zum Nachschlagen lautet:

=VLOOKUP(lookup_value, table_range, column_index, logical)

Wenn Sie die relative Referenz in der Formel verwenden, um einen Wert zu suchen und den entsprechenden Übereinstimmungswert wie folgt zurückzugeben:

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

Ziehen Sie dann den Griff zum automatischen Ausfüllen nach unten, um den Wert unten zu suchen. Es wird ein Fehler zurückgegeben.

Wenn Sie den Füllpunkt nach unten ziehen, um die Formel in die Zelle darunter zu kopieren, werden die Bezüge in der Formel automatisch um eine Zeile nach unten angepasst. Dadurch wird der Verweis auf den Tabellenbereich A4:B8 zu A5:B9. Da „Lisa: im Bereich A5:B9 nicht gefunden werden kann, gibt die Formel einen Fehler zurück.

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

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

Ziehen Sie dann den Griff zum automatischen Ausfüllen nach unten, um das Gehalt von Lisa zu erhalten.

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 ändern. Wenn Sie Zellbezüge in Hunderten von Formeln in Excel absolut machen möchten, ist dies der Fall Referenzen konvertieren Werkzeug von Kutools for Excel kann Ihnen helfen, den Job mit 2 Klicks zu erledigen.

Wählen Sie die Formelzellen aus, deren Zellbezüge Sie absolut machen möchten, und klicken Sie auf Kutoolen > Mehr (fx) > Referenzen konvertieren. Dann wählen Sie die Zu absolut Option und klicken Sie auf Ok or Hinzufügen. Jetzt wurden alle Zellbezüge der ausgewählten Formeln in absolute umgewandelt.

Relative Referenz und gemischte Referenz

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

Relativer Bezug ist der Standardverweistyp in Excel, der ohne Dollarzeichen ($) vor Zeilen- und Spaltenverweisen steht. Und wenn eine Formel mit relativen Bezügen kopiert oder in andere Zellen verschoben wird, ändern sich die Bezüge automatisch basierend auf ihrer relativen Position.

Wenn Sie beispielsweise eine Formel wie „=A1+1“ in eine Zelle eingeben und dann den Autofill-Griff nach unten ziehen, um diese Formel in die nächste Zelle zu füllen, ändert sich die Formel automatisch in „=A2+1“.

Gemischte Referenz besteht sowohl aus einer absoluten Referenz als auch aus einer relativen Referenz. Mit anderen Worten: Beim gemischten Verweis wird beim Kopieren oder Ausfüllen einer Formel das Dollarzeichen ($) verwendet, um entweder die Zeile oder die Spalte zu fixieren.

Nehmen Sie als Beispiel eine Multiplikationstabelle. In den Zeilen und Spalten sind die Zahlen von 1 bis 9 aufgeführt, die Sie miteinander multiplizieren.

Zunächst können Sie die Formel „=B3*C2“ in Zelle C3 verwenden, um 1 in Zelle B3 mit der Zahl (1) in der ersten Spalte zu multiplizieren. Wenn Sie jedoch den Ziehpunkt für das automatische Ausfüllen nach rechts ziehen, um die anderen Zellen zu füllen, werden Sie feststellen, dass alle Ergebnisse außer dem ersten falsch sind.

Dies liegt daran, dass sich beim Kopieren der Formel nach rechts die Zeilenposition nicht ändert, die Spaltenposition jedoch von B3 zu C3, D3 usw. ändert. Dadurch werden die Formeln in den rechten Zellen (D3, E3, usw.) in „=C3*D2“, „=D3*E2“ usw. ändern, wenn Sie eigentlich möchten, dass sie „=B3*D2“, „=B3*E2“ usw. sind.

In diesem Fall müssen Sie ein Dollarzeichen ($) hinzufügen, um die Spaltenreferenz „B3“ zu sperren. Verwenden Sie die Formel wie folgt:

=$B3*C2

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

Dann müssen Sie die Zahl 1 in Zelle C2 mit den Zahlen in den Zeilen darunter multiplizieren.

Wenn Sie die Formel nach unten kopieren, ändert sich die Spaltenposition von Zelle C2 nicht, aber die Zeilenposition ändert sich von C2 zu C3, C4 usw. Infolgedessen ändern sich die Formeln in den Zellen darunter zu „=$B4C3“. „=$B5C4“ usw., was zu falschen Ergebnissen führt.

Um dieses Problem zu lösen, ändern Sie „C2“ in „C$2“, um zu verhindern, dass sich die Zeilenreferenz ändert, wenn Sie den Ziehpunkt für die automatische Ausfüllung nach unten ziehen, um die Formeln auszufüllen.

=$B3*C$2

Jetzt können Sie den Ziehpunkt für die automatische Füllung nach rechts oder unten ziehen, um alle Ergebnisse zu erhalten.

Dinge zu erinnern

• Zusammenfassung der Zellreferenzen

  Typ	Beispiel	Zusammenfassung
  Absolute Referenz	$ A $ 1	Niemals ändern, wenn die Formel in andere Zellen kopiert wird
  Relative Referenz	A1	Sowohl die Zeilen- als auch die Spaltenreferenz ändern sich basierend auf der relativen Position, wenn die Formel in andere Zellen kopiert wird
  Gemischte Referenz	$A1/A$1	Der Zeilenbezug ändert sich, wenn die Formel in andere Zellen kopiert wird, der Spaltenbezug jedoch festgelegt ist./Der Spaltenbezug ändert sich, wenn die Formel in andere Zellen kopiert wird, der Zeilenbezug jedoch festgelegt ist.

• Im Allgemeinen ändern sich absolute Bezüge nie, wenn eine Formel verschoben wird. Absolute Bezüge werden jedoch automatisch angepasst, wenn eine Zeile oder Spalte oben oder links im Arbeitsblatt hinzugefügt oder entfernt wird. Wenn Sie beispielsweise in einer Formel „=$A$1+1“ eine Zeile oben auf dem Blatt einfügen, ändert sich die Formel automatisch in „=$A$2+1“.

  

• Das F4 Mit der Taste kann zwischen relativer Referenz, absoluter Referenz und gemischter Referenz umgeschaltet werden.