Verwendung relativer, absoluter und gemischter Verweise in Excel

Wenn Sie in Excel mit vielen Daten arbeiten, können Sie relative, absolute oder gemischte Bezüge verwenden, um sich wiederholende manuelle Arbeit zu ersparen, um sicherzustellen, dass Ihre Formeln korrekt funktionieren, und um große Datenmengen mit nur wenigen Handgriffen zu erfassen. Im Folgenden erfahren Sie, wie Sie diese Excel-Referenztypen verwenden können.

Was sind Excel-Zellbezüge?

Sie verwenden einen Zellverweis, wenn Sie Informationen erfassen möchten, die in einer anderen Zelle oder einem anderen Zellbereich enthalten sind. Die folgende Formel bezieht sich zum Beispiel auf die Zellen A1 und A2 und addiert deren Inhalt, um ein Ergebnis zu erhalten.

=SUMME(A1 A2)

Sie können die folgenden Arten von Zellbezügen verwenden, um bestimmte Ergebnisse zu erzielen:

• Standardmäßig sind Bezüge in Excel relativ, was sich auf die relative Position der Zelle bezieht. Wenn Sie eine Formel in Zelle A1 eingeben, die die Zelle A2 einbezieht, beziehen Sie sich auf die Zelle, die sich eine Zelle unter der Zelle befindet, in der Sie gerade tippen. Würden Sie die Formel in eine andere Zelle kopieren, würde der Verweis automatisch die gleiche relative Aktion ausführen wie an der vorherigen Stelle.
• Sie können auch einen absoluten Bezug verwenden, der sich nicht ändert, wenn Sie die Formel in eine andere Zelle kopieren. Wenn Sie eine Formel erstellen, die sich mit einem absoluten Verweis auf eine Zelle bezieht, wird auf diese Zelle verwiesen, wo immer die Formel verwendet wird.
• Schließlich können Sie einen gemischten Bezug verwenden, der eine Kombination aus beiden ist.

Schauen wir uns diese Möglichkeiten genauer an.

Verwendung relativer Referenzen in Excel

In dem hier gezeigten Beispiel können Sie sehen, wie viel die Person in jedem Monat für die einzelnen Posten ausgegeben hat. Sie möchten nun die Gesamtausgaben für jeden Monat berechnen.

Klicken Sie zunächst auf die Zelle, in der die erste Berechnung durchgeführt werden soll (in diesem Fall H2), und geben Sie die Formel ein, mit der diese Berechnung erstellt werden soll. In diesem Beispiel würden Sie die folgende Formel verwenden:

=SUM(E2 E12 E22)

Damit haben Sie innerhalb der Berechnung auf die Zellen E2, E12 und E22 verwiesen und diese addiert. Dabei handelt es sich um einen relativen Verweis - die Formel in Zelle H2 hat auf die Zellen verwiesen, die sich drei Spalten weiter links (E2), drei Spalten weiter links und zehn Zeilen tiefer (E12) bzw. drei Spalten weiter links und zwanzig Zeilen tiefer (E22) befinden.

Wenn Sie nun dieselbe Formel kopieren und an anderer Stelle einfügen, werden die Werte übernommen, die sich an denselben relativen Positionen befinden wie die, an denen Sie die Formel eingefügt haben. Sie können auch die AutoFill-Funktion von Excel verwenden, um den Rest Ihrer Daten mit demselben relativen Bezug zu vervollständigen.

Wie Sie hier sehen können, lautet die Formel in Zelle H11:

=SUM(E11 E21 E31)

Der Grund dafür ist, dass der relative Bezug neun Zeilen tiefer als ursprünglich platziert wurde. Anstatt die Formel für jeden Monat einzugeben, müssen Sie sie jetzt nur noch einmal eingeben und dann denselben relativen Bezug an jeder beliebigen Stelle Ihres Excel-Arbeitsblatts anwenden.

Verwendung absoluter Bezüge in Excel

In diesem Beispiel möchte der Buchhalter des Unternehmens berechnen, wie viel er den Mitarbeitern auf der Grundlage der geleisteten Arbeitsstunden und eines festen Stundensatzes zahlen muss.

Klicken Sie zunächst auf die Zelle, in der die erste Summe berechnet werden soll (in diesem Fall E2), und beginnen Sie mit der Eingabe der entsprechenden Formel. In diesem Beispiel möchte der Buchhalter den Stundensatz in Zelle A2 mit der Gesamtzahl der von Jacinda geleisteten Arbeitsstunden in D2 multiplizieren.

=SUMME(A2

Wenn wir hier jedoch einen relativen Bezug verwenden, bezieht sich die Formel bei der Anwendung auf die Summen der anderen Mitarbeiter auf die Zelle A3 für Raul, A4 für Lucy usw., so dass Sie nicht die gewünschten Ergebnisse erhalten.

Wir müssen also dafür sorgen, dass Excel weiterhin auf die richtige Zelle verweist, die in diesem Fall A2 ist. Zu diesem Zweck müssen Sie das Dollar-Symbol vor jedem Teil des Verweises einfügen:

=SUMME($A$2

Damit wird Excel mitgeteilt, dass bei Verwendung der gleichen Formel an anderer Stelle immer auf A2 verwiesen wird.

Sie können nun Ihre Formel vervollständigen. In diesem Fall wird sie wie folgt lauten:

=SUMME($A$2*D2)

Beachten Sie, dass im zweiten Teil der Formel (D2) kein absoluter Bezug verwendet wird. Das liegt daran, dass wir die Zelle A2 mit den jeweiligen Stunden jedes Mitarbeiters multiplizieren wollen, also relativ zu den Stunden der einzelnen Personen in Spalte D.

Sie können die Formel nun auf die anderen Zellen anwenden, indem Sie sie kopieren und einfügen oder die AutoAusfüllen-Funktion von Excel verwenden, wobei Sie wissen, dass die Zelle A2 absolut referenziert ist.

Verwendung gemischter Bezüge in Excel

Es kann vorkommen, dass Sie in Ihrer Formel zwar die Zeile, nicht aber die Spalte sperren müssen, oder umgekehrt. In diesem Fall möchte der Buchhalter beispielsweise berechnen, wie viel Steuern jeder Mitarbeiter auf der Grundlage seiner individuellen Steuersätze zu zahlen hat.

Daher müssen wir die Werte in Spalte E (die Steuersätze) sperren, aber nicht die Zeilen, da sie für jeden Arbeitnehmer unterschiedlich sind, und nicht die Verweise auf die Jahre, da sie anpassbar sein müssen.

Klicken Sie auf die Zelle, in der die erste Berechnung durchgeführt werden soll (in diesem Fall Zelle G2), und geben Sie Ihre Formel ein. Im obigen Beispiel möchte der Buchhalter zunächst die zu zahlende Steuer für Arjun im Jahr 2021 berechnen und gibt daher die folgende Formel ein

=SUMME(B2*E2)

Wir müssen nun die Spalte E sperren. Wenn wir ohne Änderung der Formel automatisch nach rechts ausfüllen würden, würde Arjuns Steuerschuld für 2022 nicht korrekt berechnet werden; die Formel würde zwar die korrekten Jahreseinkommenssummen erfassen (weil wir die Zelle B2 nicht sperren), aber sie würde auch auf die Zelle F2 verweisen. Fügen Sie daher ein Dollar-Symbol vor dem Verweis auf Spalte E in Ihrer Formel ein:

=SUMME(B2*$E2)

Wir wollen hier keinen absoluten Bezug verwenden, da dies bedeuten würde, dass bei der Berechnung der für die anderen Arbeitnehmer zu zahlenden Steuern der Steuersatz von Arjun anstelle der individuellen Steuersätze der einzelnen Arbeitnehmer verwendet wird. Im Wesentlichen bedeutet das Sperren nur der Spalte E, dass wir die Steuersätze beibehalten, uns aber relativ an die unterschiedlichen Steuersätze der einzelnen Personen anpassen können.

Wir können nun die Funktion AutoAusfüllen verwenden, um Arjuns zu zahlende Steuerbeträge zu vervollständigen, da wir wissen, dass Spalte E in unseren Berechnungen gesperrt bleibt, während sich die Summen für jedes Jahr relativ zu der Stelle bewegen, an der die Formel platziert wird.

Da wir die Spalte E gesperrt haben, aber die Zeilen in unserer Formel relativ belassen haben, können wir die Gesamtsummen der übrigen Arbeitnehmer auf der Grundlage ihrer individuellen Steuersätze ausfüllen. Denken Sie daran, dass Sie auf eine beliebige Zelle in Ihren Ergebnissen klicken und die Formel in der Formelleiste sehen können, um zu überprüfen, ob die richtigen Zellen referenziert sind.

Wie man in Excel zwischen verschiedenen Arten von Zellbezügen umschaltet

Wenn Sie in Ihrer Excel-Tabelle einfach zwischen relativen, absoluten und gemischten Bezügen umschalten möchten, müssen Sie nicht jedes Mal das Dollar-Symbol eingeben. Klicken Sie stattdessen auf die Zelle, die Sie ändern möchten, und klicken Sie dann in der Formelleiste auf den Teil der Formel, den Sie umschalten möchten (Sie können direkt vor dem Bezug, in der Mitte des Bezugs oder direkt nach dem Bezug klicken).

In diesem Fall wollen wir zwischen den Zellbezugsarten für die Zelle G11 umschalten, also müssen wir darauf achten, dass wir auf oder neben den Zellbezug klicken.

Drücken Sie dann F4, und Sie werden sehen, dass sich der Typ des Zellbezugs ändert.

Zunächst wird zu einem absoluten Bezug gewechselt:

$G$11

Dann eine gemischte Referenz mit gesperrter Zeile:

G$11

Dann eine gemischte Referenz mit der Spalte gesperrt:

$G11

Und schließlich wird auf die Standardeinstellung, eine relative Referenz, zurückgegriffen:

G11

Wenn Sie mehrere Bezüge in derselben Formel gleichzeitig umschalten wollen, markieren Sie einfach die entsprechenden Bezüge in der Formel und führen Sie die gleichen Schritte aus.

Und schon sind Sie fertig! Wer hätte gedacht, dass das bloße Vorhandensein eines Dollar-Symbols Ihnen helfen kann, Ihre Zellbezüge in Excel schnell und genau zu organisieren?