Excel: Absoluter Bezug und relativer Bezug

DARUM GEHT’S

Sie arbeiten häufig mit Excel? Absoluter Bezug  und relativer Bezug sind Funktionen, die Ihnen beispielsweise in folgender Situation helfen können: Sie möchten, dass beim Kopieren einer Formel mittels AutoAusfüllen ein Zellbezug nicht angepasst wird, sondern unverändert bleibt. Mit der Funktion AutoAusfüllen werden allerdings Zellbezüge jeweils um eine Zelle verschoben. Dies kann zu einer Fehlermeldung oder einem falschen Ergebnis führen. Damit beim Verwenden der Funktion AutoAusfüllen ein Bezug nicht angepasst wird, müssen Sie einen absoluten Bezug erstellen.
Wie das geht und was Sie dabei beachten müssen, erfahren Sie in diesem Lernobjekt.

SO WIRD’S GEMACHT

Im folgenden Beispiel wird in der Formel in Zelle C4 auf einen Wert in der Zelle F4 zugegriffen. In der Bearbeitungszeile sehen Sie diese Formel:

Absoluter Bezug 1

Wenn Sie nun die Formel von C4 kopieren, wird der Bezug auf die Zelle F4 jeweils eine Zelle nach unten verschoben.

Sie erhalten folgendes Ergebnis:

Absoluter Bezug

In diesem Beispiel kann für die Zellen C5:C9 keine Mehrwertsteuer errechnet werden, da zu einer leeren Zelle wie beispielsweise F5 kein Bezug hergestellt werden kann. Beim AutoAusfüllen hat Excel bei jedem Zellbezug in der Formel die Zeile um eine Zelle nach unten verschoben: C4 -> F4, C5 -> F5.

Absoluter Bezug

Dass der erste Zellbezug in der Formel bei AutoAusfüllen um eine Zeile nach unten verschoben wurde, ist gewünscht. Dass auch der zweite Zellbezug in gleicher Weise verschoben wird allerdings nicht. Um nun zu verhindern, dass beim AutoAusfüllen der Zellbezug auf Zelle F4 verändert wird, müssen Sie diesen vor dem AutoAusfüllen zu einem absoluten Bezug machen.

Das Ergebnis von Excel „absoluter Bezug in relativen Bezug konvertiert“ erhalten Sie folgendermaßen: Sie wandeln einen relativen Bezug in einen absoluten Bezug um, indem Sie vor die Bezeichnungen für die Zeile oder Spalte ein Dollarzeichen ($) setzen. In diesem Beispiel wird F4 zu $F$4:

Absoluter Bezug

TIPP

Sie können einen relativen Bezug auch in einen absoluten Bezug umwandeln, indem Sie in der Bearbeitungsleiste in den Zellbezug klicken und dann die [F4]-Taste drücken.

Absoluter Bezug

Der Bezug auf die Zelle F4 ist somit in einen absoluten Bezug umgewandelt. Wenn nun die Formel mit dem absoluten Bezug aus Zelle C4 via AutoAusfüllen auf die Zellen darunter übertragen wird, wird dieser Zellbezug nicht angepasst:

Absoluter Bezug

TIPP zu Excel – absoluter Bezug

Es gibt für die Verwendung von absoluten Bezügen eine Faustregel: Absolute Bezüge werden dann verwendet, wenn es in Ihrer Tabelle einen Wert gibt, auf den alle Berechnungen zugreifen sollen.

ACHTUNG

Die Nicht-Verwendung von absoluten Bezügen ist eine sehr häufige Fehlerursache. Falls Sie nach dem Kopieren einer Formel unerwartete oder offensichtlich falsche Ergebnisse erhalten, sollten Sie als erstes prüfen, ob Sie einen absoluten Bezug hätten verwenden sollen.

Neben den relativen und den absoluten Bezügen gibt es noch eine dritte Form: die gemischten Bezüge. Von einem gemischten Bezug spricht man, wenn nur ein Bestandteil eines Zellbezugs absolut ist, der andere relativ, also zum Beispiel A$1 oder $B4. Um einen gemischten Bezug zu erhalten, können Sie die Dollarzeichen entweder entsprechend eintippen oder die [F4]-Taste so oft drücken, bis der gewünscht Bezug angezeigt wird.
Wenn der Cursor (entweder in der Bearbeitungsleiste oder im Bearbeitungsmodus in der Zelle direkt) in, direkt vor oder direkt hinter dem Bezug platziert ist, können Sie mit Hilfe der [F4]-Taste durch die verschiedenen Bezugsmöglichkeiten schalten.

Absoluter Bezug

Absoluter Bezug

Absoluter Bezug

Durch mehrmaliges Drücken der [F4]-Taste können Sie den gewünschten Bezug erstellen:

Zellinhalt Drücken der [F4]-Taste neuer Zellinhalt
=B5 F4 =$B$5(absoluter Bezug)
=$B$5 F4 =B$5 (gemischter Bezug)
=B$5 F4 =$B4 (gemischter Bezug)
=$B4 F4 =B5 (relativer Bezug)

 

Beim Kopieren von Formeln mit gemischten Bezügen wird jeweils der Bezug konstant gehalten, vor dem das Dollarzeichen steht. Bei B$5 wird beispielsweise immer Zeile 5 angesprochen, beim Kopieren nach rechts aber die Spalte in C, D usw. angepasst. Bei $B5 dagegen wird immer auf Spalte B verwiesen, beim Kopieren der Formel nach unten wird aber die Zeile auf 6, 7 usw. verändert.

Hier ein Beispiel, um die Funktionsweise von gemischten Bezügen zu erläutern:
In einer Tabelle sollen jeweils die Werte einer Zeile und Spalte miteinander addiert werden. In Zelle B2 ist dafür schon die passende Formel eingetragen:

Absoluter Bezug

Wenn Sie diesen Wert nun via AutoAusfüllen auf die übrigen Zellen kopieren, würde das Ergebnis so aussehen:

Absoluter Bezug

In Zelle C4 steht beispielsweise als Ergebnis 4, obwohl Sie dort eigentlich als Ergebnis eine 3 (2+1) stehen haben möchten. Dies hat folgende Ursache: Beim AutoAusfüllen nach rechts wurden die Zellbezüge der Formel jeweils um eine Spalte nach rechts verschoben. Somit wird in der Zelle C2 nicht die Zelle A2 mit der Zelle C1 addiert, sondern die Zelle B2 mit der Zelle C1. Gleiches gilt für das AutoAusfüllen nach unten: In Zelle C3 müsste eigentlich das Ergebnis 5 stehen, da aber der Zellbezug um eine Zelle nach unten verschoben wurde, ist das Ergebnis 8 (C2+B3 also 4+4).

Absoluter Bezug

Um nun festzulegen, dass immer auf die Spalte A zugegriffen wird, müssen Sie beim Erstellen der Formel gemischte Bezüge setzen: der korrekte Eintrag für die Zelle B2 muss lauten: =$A2+B$1. So legen Sie fest, dass beim AutoAusfüllen der erste Wert immer aus der Spalte A genommen wird und nur die Zeilennummer angepasst wird und für den zweiten Wert immer auf die Zeile 1 zurückgegriffen wird und nur die Spaltennummer angepasst wird. Wenn Sie die Formel mit den gemischten Bezügen nun auf alle Zellen übertragen haben, sieht das Ergebnis so aus:

Absoluter Bezug

Wenn Sie sich nun die Formeln anzeigen lassen (Durch Klick auf Formeln anzeigen im Register Formeln in der Gruppe Formelüberwachung), sehen Sie, dass die Zeile 1 und die Spalte A in allen Formeln unverändert sind:

Absoluter Bezug

SCHRITT FÜR SCHRITT

Hier erfahren Sie beispielhaft die Arbeitsschritte für Excel: Absoluter Bezug und relativer Bezug können dadurch noch einmal nachvollzogen werden! Berechnen Sie nun die Mehrwertsteuer für die jeweiligen Warengruppen. Erstellen Sie dafür eine Formel, die auf den in Zelle F4 eingetragenen Mehrwertsteuersatz zugreift. Bevor Sie die Formel kopieren, wandeln Sie den Bezug auf diese Zelle in einen absoluten Bezug um. Damit Sie die folgenden Schritte nachvollziehen können, öffnen Sie bitte die Arbeitsmappe umsatzliste_3_4_4_3.xlsx.

Absoluter Bezug

1. Markieren Sie die Zelle C4.

2. Klicken Sie in die Bearbeitungsleiste hinter “19%”.

3. Löschen Sie die Angabe “19%”. Achten Sie darauf, dass das Multiplikationszeichen erhalten bleibt. Excel hat nun in den Zeigemodus umgeschaltet.

4. Klicken Sie auf die Zelle F4. Der Bezug wird in die Formel übernommen.

5. Drücken Sie die Taste [F4]. Der Bezug wird umgewandelt in $F$4.

6. Schließen Sie die Formel mit der [Enter]-Taste ab.

7. Kopieren Sie die Formeln mit der AutoAusfüllen-Funktion in die darunter liegenden Zellen.

Ihr Ergebnis sieht nun so aus:

Absoluter Bezug

Wenn Sie Ihr Ergebnis mit der Musterlösung vergleichen möchten, öffnen Sie hierzu die Arbeitsmappe umsatzliste_3_4_4_3_e.xlsx.

UND JETZT SIND SIE DRAN!

Jetzt machen Sie sich selbständig an die Arbeit mit Excel! Absoluter Bezug und relativer Bezug werden so für Sie noch klarer. Öffnen Sie dazu bitte die Arbeitsmappe mieterhoehung_3_4_4_3_u.xlsx. Berechnen Sie in der Spalte C für jedes Objekt den Betrag, um den die Miete erhöht wird, sowie in Spalte D den neuen Mietpreis nach der Erhöhung. Vergleichen Sie Ihr Ergebnis mit der Datei mieterhoehung_3_4_4_3_ue.xlsx.

Absoluter Bezug

 

Dieser Artikel gefällt Ihnen?

Dann teilen Sie ihn mit Freunden oder erhalten Sie mit unserem Newsletter die neuesten Infos.

Weitere Tipps& Tricks, die Sie interessieren könnten:

  1. Microsoft Excel
  2. Dateiformate in Excel
  3. Programmierung

Ihr Kommentar zu diesem Artikel

Connect with Facebook

*