Excel absoluter Verweis (wie man ihn erstellt und verwendet)

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

• Was ist ein absoluter Verweis
• Wie man absolute Verweise erstellt
• Absoluten Verweis mit Beispielen verwenden
• Prozentsatz der Gesamtsumme berechnen
• Einen Wert suchen und den entsprechenden Übereinstimmungswert zurückgeben
• Mit Kutools für Excel können Sie mit 2 Klicks Batch-Zellverweise absolut setzen
• Relativer Verweis und gemischter Verweis
• Dinge, die zu beachten sind

Beispieldatei kostenlos herunterladen

Video: Absoluter Verweis

Was ist ein absoluter Verweis

Ein absoluter Verweis ist eine Art von Zellreferenz in Excel.

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

Ein absoluter Verweis wird erstellt, indem ein Dollarzeichen ($) vor den Spalten- und Zeilenreferenzen in der Formel hinzugefügt wird. Zum Beispiel, um einen absoluten Verweis für Zelle A1 zu erstellen, sollten Sie ihn als $A$1 darstellen.

Absolute Verweise sind nützlich, wenn Sie in einer Formel auf eine feste Zelle oder einen festen Bereich verweisen möchten, die/der in mehrere Zellen kopiert wird, aber Sie möchten nicht, dass sich der Verweis ändert.

Zum Beispiel enthält der Bereich A4:C7 die Preise von Produkten, und Sie möchten die zu zahlende Steuer jedes Produkts basierend auf dem Steuersatz in Zelle B2 erhalten.

Wenn Sie in der Formel einen relativen Verweis wie "=B5*B2" verwenden, werden falsche Ergebnisse zurückgegeben, wenn Sie den Auto-Ausfüllknauf 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-Ausfüllknauf nach unten gezogen wird; die Ergebnisse sind korrekt.

Verwendung eines relativen Verweises		Verwendung eines absoluten Verweises

Wie man absolute Verweise erstellt

Um in Excel einen absoluten Verweis zu erstellen, fügen Sie Dollarzeichen ($) vor den Spalten- und Zeilenreferenzen in einer Formel hinzu. Es gibt zwei Möglichkeiten, einen absoluten Verweis zu erstellen:

Manuelles Hinzufügen von Dollarzeichen zur Zellreferenz

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

Zum Beispiel, wenn Sie die Zahlen in Zelle A1 und B1 addieren und beide absolut machen möchten, geben Sie einfach die Formel als "=$A$1+$B$1" ein. Dies stellt sicher, dass die Zellreferenzen konstant bleiben, wenn die Formel kopiert oder in andere Zellen verschoben wird.

Alternativ, wenn Sie die Referenzen in einer vorhandenen Formel absolut machen möchten, können Sie die Zelle auswählen, dann zur Formelleiste gehen und die Dollarzeichen ($) hinzufügen.

Verwenden der Tastenkombination F4, um relative Referenzen in absolute 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. Drücken Sie die Taste "F4" auf der Tastatur, um die Referenztypen zu wechseln, bis Dollarzeichen vor den Spalten- und Zeilenreferenzen hinzugefügt werden;
4. Drücken Sie die Eingabetaste, um den Bearbeitungsmodus zu verlassen und die Änderungen zu übernehmen.

Die F4-Taste wechselt zwischen relativen, absoluten und gemischten Referenzen.

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

Wenn Sie alle Referenzen in einer Formel absolut machen möchten, wählen Sie die gesamte Formel in der Formelleiste aus, drücken Sie die Taste "F4", um die Referenztypen zu wechseln, bis Dollarzeichen vor den Spalten- und Zeilenreferenzen hinzugefügt werden.

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

Absoluten Verweis mit Beispielen verwenden

Dieser Teil bietet 2 Beispiele, um zu zeigen, wann und wie absolute Verweise in einer Excel-Formel verwendet werden.

Beispiel 1 Prozentsatz der Gesamtsumme berechnen

Angenommen, Sie haben einen Datenbereich (A3:B7), der die Verkäufe jeder Frucht enthält, und die Zelle B8 enthält die Gesamtsumme dieser Früchte. Nun möchten Sie den Prozentsatz des Verkaufs jeder Frucht an der Gesamtsumme berechnen.

Die allgemeine Formel zur Berechnung des Prozentsatzes der Gesamtsumme:

Percentage = Sale/Amount

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

=B4/B8

Wenn Sie den Auto-Ausfüllknauf nach unten ziehen, um den Prozentsatz anderer Früchte zu berechnen, werden #DIV/0! Fehler zurückgegeben.

Da sich beim Ziehen des Auto-Ausfüllknaufs zum Kopieren der Formel in die darunterliegenden Zellen der relative Verweis B8 automatisch an andere Zellreferenzen (B9, B10, B11) basierend auf ihren relativen Positionen anpasst. Und die Zellen B9, B10 und B11 sind leer (Nullen). Wenn der Divisor Null ist, 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 sich nicht ändert, wenn Sie die Formel verschieben oder irgendwohin kopieren. Nun wird die Formel aktualisiert zu:

=B4/$B$8

Dann ziehen Sie den Auto-Ausfüllknauf nach unten, um den Prozentsatz anderer Früchte zu berechnen.

Beispiel 2 Einen Wert suchen und zur entsprechenden Übereinstimmung zurückkehren

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

Die allgemeine Formel zur Suche lautet:

=VLOOKUP(lookup_value, table_range, column_index, logical)

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

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

Dann ziehen Sie den Auto-Ausfüllknauf nach unten, um den Wert darunter zu suchen, wird ein Fehler zurückgegeben.

Wenn Sie den Ausfüllknauf nach unten ziehen, um die Formel in die Zelle darunter zu kopieren, passen sich die Referenzen in der Formel automatisch um eine Zeile nach unten an. Dadurch wird die Referenz 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 den absoluten Verweis $A$4:$B$8 anstelle des relativen Verweises A4:B8 in der Formel:

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

Dann ziehen Sie den Auto-Ausfüllknauf nach unten, um das Gehalt von Lisa zu erhalten.

Mit Kutools für Excel können Sie mit 2 Klicks Batch-Zellverweise absolut setzen

Ob Sie sich entscheiden, manuell einzugeben oder die F4-Tastenkombination zu verwenden, können Sie jeweils nur eine Formel in Excel ändern. Wenn Sie Zellreferenzen in Hunderten von Formeln in Excel absolut machen möchten, kann Ihnen das Tool "Zellreferenzen konvertieren" von "Kutools für Excel" helfen, die Aufgabe mit 2 Klicks zu erledigen.

Um Zellreferenzen in mehreren Formeln absolut zu machen, wählen Sie die Formelzellen aus und klicken Sie auf "Kutools" > "Mehr" > "Zellreferenzen konvertieren". Wählen Sie dann die Option "In absolut" und klicken Sie auf "Ok" oder "Anwenden". Nun wurden alle Zellreferenzen der ausgewählten Formeln in absolute umgewandelt.

Relativer Verweis und gemischter Verweis

Neben dem absoluten Verweis gibt es noch zwei andere Referenztypen: relativer Verweis und gemischter Verweis.

Der relative Verweis ist der Standardreferenztyp in Excel, bei dem keine Dollarzeichen ($) vor den Zeilen- und Spaltenreferenzen stehen. Und wenn eine Formel mit relativen Referenzen in andere Zellen kopiert oder verschoben wird, ändern sich die Referenzen automatisch basierend auf ihrer relativen Position.

Zum Beispiel, wenn Sie eine Formel in eine Zelle wie "=A1+1" eingeben und dann den Autoausfüllknauf nach unten ziehen, um diese Formel in die nächste Zelle zu füllen, ändert sich die Formel automatisch zu "=A2+1".

Ein gemischter Verweis besteht sowohl aus einem absoluten als auch aus einem relativen Verweis. Mit anderen Worten, ein gemischter Verweis verwendet das Dollarzeichen ($), um entweder die Zeile oder die Spalte festzulegen, wenn eine Formel kopiert oder ausgefüllt wird.

Nehmen wir als Beispiel eine Multiplikationstabelle, in der die Zeilen und Spalten die Zahlen von 1 bis 9 auflisten, die Sie miteinander multiplizieren möchten.

Um zu beginnen, können Sie die Formel "=B3*C2" in Zelle C3 verwenden, um die Zahl 1 in Zelle B3 mit der Zahl (1) in der ersten Spalte zu multiplizieren. Wenn Sie jedoch den Autoausfüllknauf nach rechts ziehen, um die anderen Zellen auszufü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, aber die Spaltenposition von B3 zu C3, D3 usw. wechselt. Als Ergebnis ändern sich die Formeln in den rechten Zellen (D3, E3 usw.) zu "=C3*D2", "=D3*E2" usw., während Sie eigentlich möchten, dass sie "=B3*D2", "=B3*E2" usw. lauten.

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

=$B3*C2

Nun sind die Ergebnisse korrekt, wenn Sie die Formel nach rechts ziehen.

Dann müssen Sie die Zahl 1 in Zelle C2 mit den Zahlen in den darunter liegenden Zeilen 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. Dadurch ändern sich die Formeln in den darunter liegenden Zellen zu "=$B4C3", "=$B5C4" usw., was zu falschen Ergebnissen führt.

Um dieses Problem zu lösen, ändern Sie „C2“ in „C$2“, um die Zeilenreferenz konstant zu halten, wenn Sie den Auto-Ausfüllknauf nach unten ziehen, um die Formeln auszufüllen.

=$B3*C$2

Nun können Sie den Auto-Ausfüllknauf nach rechts oder unten ziehen, um alle Ergebnisse zu erhalten.

Dinge, die zu beachten sind

• Zusammenfassung der Zellreferenzen

  Typ	Beispiel	Zusammenfassung
  Absoluter Verweis	$A$1	Ändert sich nie, wenn die Formel in andere Zellen kopiert wird
  Relativer Verweis	A1	Sowohl die Zeilen- als auch die Spaltenreferenz ändern sich basierend auf der relativen Position, wenn die Formel in andere Zellen kopiert wird
  Gemischter Verweis	$A1/A$1	Zeilenreferenz ändert sich, wenn die Formel in andere Zellen kopiert wird, aber die Spaltenreferenz ist fixiert / Spaltenreferenz ändert sich, wenn die Formel in andere Zellen kopiert wird, aber die Zeilenreferenz ist fixiert;

• Generell ändern sich absolute Referenzen nie, wenn eine Formel verschoben wird. Absolute Referenzen passen sich jedoch automatisch an, wenn eine Zeile oder Spalte oben oder links im Arbeitsblatt hinzugefügt oder entfernt wird. Zum Beispiel, in einer Formel "=$A$1+1", wenn Sie eine Zeile am oberen Rand des Blatts einfügen, ändert sich die Formel automatisch zu "=$A$2+1".

  

• Die Taste "F4" kann zwischen relativen, absoluten und gemischten Referenzen wechseln.