Absolute Referenz vs. relative Referenz Excel erklärt

18. Dezember 2021 391 Ansichten Absolute Referenz vs. relative Referenz Excel

Post-Themen durchsuchen

Was ist eine absolute Referenz in Excel?

Mit der absoluten Referenz in Excel können Sie auf eine bestimmte Zeile oder Spalte verweisen, auch wenn Sie in andere Zellen kopieren. Dies bedeutet, dass sich die Formeln NICHT mit den sich ändernden Zellen ändern, wenn Sie eine Zelle kopieren oder durch Zellen ziehen.

Was ist Relative Referenz Excel?

Andererseits bedeutet ein relativer Bezug einer Zelle, dass sich Zellen automatisch ändern. Wenn Sie Zellen kopieren, um neue Zellen durchzuziehen, ändert sich die Formel mit neuen Zellen.

In diesem Tutorial erfahren Sie, wie Sie:

  • So verwenden Sie absolute Referenz in Excel
  • So verwenden Sie Relative Reference Excel
  • Unterschiede zwischen den beiden mit Beispielen
  • So konvertieren Sie relative Verweise in absolute Verweise in Excel

Einführung der absoluten Referenz in Excel

Lassen Sie mich dieses Tutorial mit einem einfachen Beispiel beginnen.

Absolutes Referenz-Excel vs. relatives Referenz-Excel

Siehe Bild oben. Angenommen, Sie möchten Zelle B1 zu C3 hinzufügen und das Ergebnis in Zelle D3 anzeigen. Fügen Sie dann Zelle B1 zu Zelle C4 hinzu und zeigen Sie das Ergebnis in Zelle D4 und so weiter. Sie erstellen die folgende Formel in Zelle D3:

|_+_|

So erhalten Sie das gewünschte Ergebnis: 100 plus 10 ergibt 110.

Absolutes Referenz-Excel vs. relatives Referenz-Excel

Um dieselbe Formel in den Zellen D4 bis D7 zu replizieren, sollten Sie im Allgemeinen einfach durch die Zellen D4 bis D7 ziehen). Leider führt dies nicht zum gewünschten Ergebnis.

Das Ergebnis der Replikation der Formel in Zelle D3 durch Ziehen durch die Zellen D4 bis D7 wird unten angezeigt:

Absolutes Referenz-Excel vs. relatives Referenz-Excel

Das hervorgehobene Ergebnis ist falsch. Denken Sie daran, dass wir B1 zu C4 summieren wollten. Das hat offensichtlich nicht funktioniert!

Was schief gelaufen ist?

Als Sie die Formel in Zelle D3 über D4 nach D7 gezogen haben, hat Excel die relative Referenz von Zelle B1 verwendet. Es fügte einfach B2 zu C4 hinzu. Es fügte auch B3 zu C4 hinzu und so weiter.

Absolutes Referenz-Excel vs. relatives Referenz-Excel

Wie Sie im obigen Bild sehen können, ist das Ergebnis in D4 eine Summe aus B2 und C4. Wie ich bereits sagte, hat Excel eine relative Referenz erstellt. Dies ist die Standardeinstellung, aber nicht das, was Sie erreichen wollten.

Wie lösen Sie das Problem?

Um das oben identifizierte Problem zu lösen, erstellen Sie stattdessen eine absolute Referenz in Excel. Markieren Sie dazu die Formel in Zelle D3. Fügen Sie dann vor und nach dem Zellenbuchstaben D ein Dollarzeichen ($) hinzu.

Die Formel ändert sich nun wie folgt:

|_+_|

Siehe das Bild unten.

Indem Sie das Dollarzeichen ($) wie gezeigt hinzufügen, zwingen Sie Excel, auf Zelle B1 zu verweisen.

Wichtiger Tipp
Beachten Sie, dass Sie den absoluten Bezug hinzufügen (indem Sie ein Dollarzeichen ($) in die erste Zelle einfügen.

Wenn Sie nun die Formel in Zelle D3 replizieren, indem Sie sie durch die Zellen D4 bis D7 ziehen, erhalten Sie das gewünschte Ergebnis. Siehe das Bild unten.

Bingo! Das Hinzufügen einer absoluten Referenz in Excel hat funktioniert! Excel verwies einfach in allen Zellen auf dieselbe Zelle.

Ein bisschen mehr über relative Referenzen in Excel

Bevor ich Sie durch einige Beispiele für absolute Referenzen in Excel führe, lassen Sie mich über Relative Referenzen sprechen.

Es gibt Umstände, unter denen Sie einen relativen Bezug benötigen.

Angenommen, Sie haben zwei Spalten mit einer Reihe von Zahlen, wie unten gezeigt. Die Verwendung relativer Referenzen in Excel ist besser anwendbar.

In diesem Beispiel summieren Sie A3 und B3 in C3. Um die Formel in den Zellen C4 bis C7 zu replizieren, markieren Sie die Formel in Zelle C3 und ziehen Sie sie dann bis zu Zelle C7.

Hier ist das Ergebnis.

In diesem Fall sind die Ergebnisse wie erwartet. Durch Ziehen der Formel in Zelle C3 durch die Zellen C4 bis C7 verwendete Excel den relativen Bezug, um die Formel zu replizieren. In diesem Fall müssen wir den Zellbezug in der Formel nicht konstant halten.

Reales Beispiel für absolute Referenz und relative Referenz in Excel

In diesem Abschnitt möchte ich Ihnen eine reale Anwendung von absoluten und relativen Referenz-Excel in der Wirtschaft zeigen. Beginnen Sie mit der Überprüfung der Excel-Tabelle in diesem Bild unten:

Angenommen, Sie führen ein Unternehmen oder arbeiten für ein Unternehmen und müssen den Verkaufspreis anhand des Gewinnaufschlags und der Umsatzsteuer ermitteln. Angenommen, Sie müssen auch alle Kosten, den Verkaufspreis zuzüglich Steuern, den Gewinn und die Umsatzsteuer zusammenrechnen.

Sie benötigen Kenntnisse sowohl der absoluten Referenz in Excel als auch der relativen Referenz in Excel.

Basierend auf dem, was Sie bisher in diesem Tutorial gelernt haben, können Sie sehen, dass der Gewinnaufschlag und die Umsatzsteuer mit absoluter Referenz referenziert werden. Andererseits erfordert das Erstellen von Formeln für die Summen eine relative Referenz.

Mein Excel-Tabellenbeispiel erklärt

Lassen Sie uns zunächst erklären, wie ich zu den Ergebnissen in der obigen Excel-Tabelle gekommen bin. Beginnen wir mit Zelle D7.

Hier ist der Screenshot.

Die Zelle hat die folgende Formel:

|_+_|

Um Ihnen zu helfen, die scheinbar komplexe Formel zu verstehen, werde ich sie unten aufschlüsseln:

  • Der erste Teil der Formel, C7+C7*$C, berechnet den Gewinn von Produkt A. Dies wird erreicht, indem der Einstandspreis in C7 zur Gewinnspanne (C7*$C) addiert wird. Beachten Sie, dass C7 eine relative Referenz hat.
  • Die Gewinnspanne wird berechnet, indem der Einstandspreis mit dem absoluten Wert der Gewinnspanne in Zelle C4 multipliziert wird.
  • Um die endgültigen Produktkosten zu bestimmen, muss ich jetzt die Umsatzsteuer hinzufügen.
Wichtiger Tipp
Die Umsatzsteuer (oder in einigen Ländern die Mehrwertsteuer) wird normalerweise berechnet, indem Sie Ihren Verkaufspreis mit dem Umsatzsteuerprozentsatz aufschlagen.

Wie ich den Umsatzsteueraufschlag hinzugefügt habe

  • Basierend auf dem obigen Tipp ist der endgültige Verkaufspreis (Produktverkaufspreis) + (Produktverkaufspreis) multipliziert mit dem Mehrwertsteueraufschlag. So kam ich zu der Formel ((C7+C7 $€4))+((C7+C7 4€))*5€
  • (C7+C7 $€4) ist der Verkaufspreis des Produkts. ((C7+C7 $C))*$C ist der Verkaufspreisaufschlag.
  • Beachten Sie, dass ich für diesen zweiten Teil der Berechnung des endgültigen Verkaufspreises auch auf den absoluten Wert der Verkaufssteuer in Zelle C5 verwiesen habe
  • Um die Formel in Zelle D7 zu replizieren, habe ich einfach die Formel in D7 hervorgehoben und dann in Zelle D11 gezogen.
  • Die gleiche Logik gilt für die Formel, die ich zur Berechnung des Gewinns verwendet habe (Spalte E). Die Formel in Zelle E7 lautet C7*$C. Dabei wird die relative Referenz der Produktpreiszelle C7 multipliziert mit der absoluten Referenz des Gewinnaufschlags Zelle C4 verwendet.
  • Andererseits wird die Umsatzsteuerspalte F nur anhand relativer Werte berechnet. Die Formel in Zelle C7 lautet D7-C7-E7 (endgültiger Verkaufspreis minus Einstandspreis , minus Gewinn).

Ich hoffe, Sie können den Nutzen dieses Wissens in einer realen Geschäftssituation erkennen.

So konvertieren Sie relative Referenzen in absolute Referenzen in Excel mit F4

Meine letzte Aufgabe in diesem Tutorial besteht darin, Ihnen zu zeigen, wie Sie mit F4 schnell eine relative Referenzformel in einen absoluten Wert umwandeln können. Dieses Wissen ist wichtig, wenn Sie eine Formel mit relativen Werten erstellen. Sie müssen die Formel dann in absolute Werte umwandeln.

Um dies zu demonstrieren, möchte ich Ihnen zeigen, wie ich den Gewinn in Zelle E7 berechnet habe.

  • Zuerst fügte ich das Gleichheitszeichen in die Zelle ein. Dann habe ich die Produktpreiszelle C7 ausgewählt und das Multiplikationszeichen (*) eingefügt. Als Nächstes wählte ich die Gewinnspannenzelle C4 aus. Wie Sie oben sehen können, haben sowohl C7 als auch C4 ihre relativen Werte.
  • Um Zelle C4 in Excel in einen absoluten Wert umzuwandeln, habe ich einfach meine Maus neben dem Zellenbuchstaben in der Formelleiste gehalten und dann F4 gedrückt. Das Dollarzeichen ($) wurde automatisch hinzugefügt. Siehe Screenshot unten.

Das ist es. Absolutes Referenz-Excel vs. relatives Referenz-Excel.

Ich hoffe, Sie fanden diese S-Zone hilfreich.

Alternativ können Sie uns auch Feedback geben, indem Sie das Formular zum Hinterlassen einer Antwort am Ende dieser Seite verwenden.

Weitere E-Mails und Produktivitäts-S-Zonen finden Sie auf unserer Seite „E-Mails und Produktivität – Erläuterung“.