So verwenden Sie relative, absolute und gemischte Referenzen in Excel

Schnellzugriffe

• Was sind Excel-Zellbezüge?

• So verwenden Sie relative Bezüge in Excel

• So verwenden Sie absolute Bezüge in Excel

• So verwenden Sie gemischte Bezüge in Excel

• So wechseln Sie zwischen verschiedenen Arten von Zellbezügen in Excel

Wichtige Erkenntnisse

• Excel-Zellbezüge können relativ, absolut oder gemischt sein, um bestimmte Ergebnisse in Formeln zu erzielen.
• Relative Bezüge ändern sich, wenn sie in eine andere Zelle kopiert werden, während absolute Bezüge gleich bleiben.
• Gemischte Bezüge sperren entweder die Spalte oder die Zeile in einer Formel.

Wenn Sie in Excel mit großen Datenmengen arbeiten, können Sie relative, absolute oder gemischte Referenzen verwenden, um sich wiederholte manuelle Arbeit zu sparen, sicherzustellen, dass Ihre Formeln korrekt funktionieren, und große Datenmengen mit nur wenigen schnellen Aktionen zu erfassen. Sehen wir uns an, wie Sie diese Excel-Referenztypen verwenden.

Was sind Excel-Zellreferenzen?

Sie verwenden einen Zellbezug, wenn Sie Informationen erfassen möchten, die in einer anderen Zelle oder einem Zellbereich enthalten sind. Die folgende Formel beispielsweise verweist auf die Zellen A1 und A2 und addiert deren Inhalt, um ein Ergebnis zu erzielen.

=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, die sich auf die relative Position der Zelle bezieht. Wenn Sie eine Formel in Zelle A1 eingeben, die Zelle A2 betrifft, verweisen Sie auf die Zelle, die sich eine Zelle unter der Stelle befindet, an der Sie gerade tippen. Wenn Sie die Formel in eine andere Zelle kopieren würden, würde der Verweis automatisch dieselbe relative Aktion ausführen wie an seiner vorherigen Position.
• Sie können auch einen absoluten Verweis verwenden, der sich nicht ändert, wenn Sie die Formel in eine andere Zelle kopieren. Wenn Sie eine Formel erstellen, die mit einem absoluten Verweis auf eine Zelle verweist, wird überall, wo die Formel verwendet wird, auf dieselbe Zelle verwiesen.
• Schließlich können Sie eine gemischte Referenz verwenden, also eine Kombination aus beiden.

Sehen wir uns das genauer an.

So verwenden Sie relative Referenzen in Excel

Im hier gezeigten Beispiel können Sie sehen, wie viel die Person in jedem Monat für jeden Artikel ausgegeben hat. Jetzt möchte sie 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 zur Erstellung dieser Berechnung ein. In diesem Beispiel würden Sie die folgende Formel verwenden:

=SUMME(E2+E12+E22)

Dadurch werden nun die Zellen E2, E12 und E22 innerhalb der Berechnung referenziert und addiert. Dies ist eine relative Referenz – die Formel in Zelle H2 hat die Zellen referenziert, die drei Spalten links (E2), drei Spalten links und zehn Zeilen weiter unten (E12) bzw. drei Spalten links und zwanzig Zeilen weiter unten (E22) liegen.

Wenn Sie nun dieselbe Formel kopieren und woanders einfügen würden, würde sie die Werte übernehmen, die sich an den gleichen relativen Positionen befinden wie dort, wo Sie sie einfügen. Sie können auch die AutoFill-Funktion von Excel verwenden, um den Rest Ihrer Daten mit der gleichen relativen Referenz zu vervollständigen.

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

=SUMME(E11+E21+E31)

Dies liegt daran, dass der relative Bezug neun Zeilen tiefer als ursprünglich platziert wird. Anstatt die Formel jetzt für jeden Monat einzugeben, müssen Sie sie nur einmal eingeben und dann denselben relativen Bezug überall in Ihrem Excel-Arbeitsblatt anwenden.

So verwenden Sie absolute Bezüge in Excel

In diesem Beispiel möchte der Buchhalter des Unternehmens berechnen, wie viel er den Mitarbeitern auf Grundlage der Anzahl der von ihnen gearbeiteten Stunden und eines festen Stundensatzes zahlen soll.

Klicken Sie zunächst auf die Zelle, in der die erste Summe berechnet werden soll (in diesem Fall E2), und geben Sie die entsprechende Formel ein. In diesem Beispiel möchte der Buchhalter den Stundensatz in Zelle A2 mit der Gesamtzahl der von Jacinda in D2 gearbeiteten Stunden multiplizieren.

=SUMME(A2

Wenn wir hier jedoch einen relativen Bezug verwenden und die Formel auf die Summen der anderen Mitarbeiter anwenden, verweist die Formel auf Zelle A3 für Raul, A4 für Lucy usw., was bedeutet, dass Sie nicht die gewünschten Ergebnisse erhalten.

Wir müssen also sicherstellen, dass Excel weiterhin auf die richtige Zelle verweist, in diesem Fall A2. Dazu müssen Sie vor jedem Teil Ihres Verweises das Dollarzeichen einfügen:

=SUM($A$2

Dadurch wird Excel mitgeteilt, dass, wenn wir die gleiche Formel woanders verwenden, immer auf A2 verwiesen wird.

Anstatt die Dollarzeichen manuell einzugeben, drücken Sie nach dem Eingeben des Zellbezugs F4 (je nach Tastatur müssen Sie möglicherweise lernen, was die Funktionstaste bewirkt), und die Dollarzeichen werden automatisch angezeigt.

Sie können jetzt Ihre Formel vervollständigen. In diesem Fall lautet sie wie folgt:

=SUMME($A$2*D2)

Beachten Sie, dass der zweite Teil der Formel (D2) keinen absoluten Bezug verwendet. Dies liegt daran, dass wir Zelle A2 mit den jeweiligen Stunden jedes Mitarbeiters multiplizieren möchten, sodass dies relativ zu den Stunden jeder Person in Spalte D sein muss.

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

So verwenden Sie gemischte Referenzen in Excel

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

Daher müssen wir die Werte in Spalte E (die Steuersätze) sperren, aber nicht die Zeilen, da diese für jeden Mitarbeiter unterschiedlich sind, und nicht die Verweise auf die Jahre, da diese 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 Arjuns Steuerschuld für 2021 berechnen und würde daher die folgende Formel eingeben:

=SUMME(B2*E2)

Wir müssen nun Spalte E sperren. Wenn wir rechts automatisch ausfüllen würden, ohne die Formel zu ändern, würde Arjuns Steuerschuld für 2022 nicht korrekt berechnet. Zwar würde die Formel die korrekten Jahreseinkommenssummen genau erfassen (weil wir Zelle B2 nicht sperren), aber sie würde auch auf Zelle F2 verweisen. Fügen Sie dazu in Ihrer Formel vor dem Verweis auf Spalte E ein Dollarzeichen ein:

=SUM(B2*$E2)

Wir möchten hier keinen absoluten Bezug verwenden, da dies bedeuten würde, dass bei der Berechnung der zu zahlenden Steuern für die anderen Mitarbeiter Arjuns Steuersatz anstelle der individuellen Steuersätze jedes Mitarbeiters verwendet würde. Im Wesentlichen bedeutet das Sperren nur der Spalte E, dass wir bei den Steuersätzen bleiben, diese aber relativ an die unterschiedlichen Steuersätze jeder Person anpassen können.

Wir können jetzt die AutoFill-Funktion verwenden, um Arjuns zu zahlende Steuersummen zu vervollständigen, wobei wir wissen, dass Spalte E in unseren Berechnungen gesperrt bleibt, während sich die Summen für jedes Jahr relativ zur Position der Formel verschieben.

Da wir die Spalte E gesperrt, die Zeilen aber in unserer Formel relativ gelassen haben, können wir die restlichen Gesamtsummen der Mitarbeiter problemlos auf Grundlage ihrer individuellen Steuersätze vervollständigen. Denken Sie daran, dass Sie in Ihren Ergebnissen auf jede Zelle klicken und die Formel in der Formelleiste anzeigen können, um zu überprüfen, ob auf die richtigen Zellen verwiesen wird.

So wechseln Sie in Excel zwischen verschiedenen Arten von Zellreferenzen

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

In diesem Fall möchten wir zwischen den Zellbezugstypen für Zelle G11 umschalten, also müssen wir sicherstellen, dass wir irgendwo auf oder neben diesen Zellbezug klicken.

Drücken Sie dann F4 und Sie werden sehen, wie sich der Zellbezugstyp ändert.

Zuerst wird zu einem absoluten Bezug gewechselt:

$G$11

Dann zu einem gemischten Bezug mit gesperrter Zeile:

G$11

Dann zu einem gemischten Bezug mit gesperrter Spalte:

$G11

Und schließlich wird auf die Standardeinstellung zurückgegriffen, einen relativen Bezug:

G11

Wenn Sie mehrere Bezüge in der gleichen Formel gleichzeitig umschalten möchten, markieren Sie einfach die entsprechenden Bezüge in der Formel und folgen Sie den gleichen Schritten.

Und fertig! Wer hätte gedacht, dass die bloße Anwesenheit eines Dollarzeichens Ihnen helfen kann, Ihre Zellbezüge in Excel schnell und genau zu organisieren?