Relativer und absoluter Bezug, gemischte Bezüge in Excel
In Excel sind relative Bezüge der Normalfall. Was ist ein relativer Bezug?
In einem Beispiel werden die Quartalszahlen einer Firma notiert. Wichtig für das Verständnis ist das selber Testen der Beispiele! Wir erstellen nun unsere Beispiel-Seifenblasenfirma Bubble-Fix mit den Quartalszahlen 54.123 € im ersten Quartal, 74.519 € im 2. Quartal, 60.231 € im 3. Quartal und 30123 € im 4. Quartal für das Jahr 2006.
Wir bilden nun die Gesamtsumme, also B2 bis B6 markieren ...
und das Summenzeichen anklicken.
Man sieht zwar in der Bearbeitungsleiste auch die hinterlegte Formel, wenn man sich auf dem entsprechenden Feld befindet, besser zum Lernen ist allerdings, sich für alle Felder die Formeln anzuzeigen.
Um die Formel direkt anzuzeigen, die hinter jedem Feld liegt, drücken Sie das Tastaturkürzel STRG + #
Dass sich es bei der Formel um einen rein relativen Bezug handelt, sieht man daran, dass in den Zellbezügen keinerlei Dollarzeichen auftaucht!
Wir wollen nun auch für das Jahr 2007 die Gesamtsumme für die Quartalszahlen erhalten. Also kopieren wird die Formel von B6: rechte Maustaste auf B6, dann den Punkt kopieren auswählen und mit der Maus in das Feld C6 dort wieder über die rechte Maustaste und einfügen die Formel einfügen.
Wir sehen hier nun schon sehr schön, dass die Zellbezüge sich automatisch angepasst haben. Sie zeigen schon auf C2 bis C5 – der Bereich wird auch blau hervorgehoben (Cursor in C6), auf den sich die Formel bezieht.
Wie praktisch diese relativen Zellbezüge sind, sieht man auch, wenn wir nun für unsere Überschrift eine weitere Zeile einfügen. Dazu auf die 1 mit der rechten Maustaste klicken (neben dem Feld A1). Wir erhalten dann das Pulldownmenü und wählen dort den Punkt „Zellen einfügen“.
Es wird eine neue Zeile eingefügt und die Formeln passen sich automatisch an. Aus der Formel in C6, die nun nach C7 rutscht wir aus dem „=SUMME(C2:C5)“ ein „=SUMME(C3:C6)“.
Die Formeln funktionieren natürlich auch – die Anzeige von Formel auf Ergebnis wird wieder aktiviert, wenn man SHIFT + # tippt.
Absolute Zellbezüge – wozu werden diese benötigt?
Wir nehmen an, dass der Betrieb 10 % Gewinn vom Umsatz hat – diese 10 % tragen wir in das Feld B1 ein. In D3 soll nun der Betrag des Gewinns erscheinen – als mathematische Formel sehr einfach (Umsatz * 10 %) – allerdings soll die Angabe der Prozente aus dem Feld B1 entnommen werden.
Das würde mit dieser Formel klappen (die weiterhin nur relative Zellbezüge hat). Was passiert aber, wenn wir die Formel kopieren? Denn auch für das zweite Quartal soll der Gewinn anzeigt werden.
Wenn wir sehen, was hier durch die relativen Bezüge gerechnet wird, passt es nicht mehr. Das ergibt dann als Ergebnis den „=Umsatz * Jahreszahl“. Man könnte zwar von Hand die Formel richtig schreiben, was bei 4 Formeln vielleicht noch machbar ist, aber bei 12 schon Arbeit wird. Wir wollen weiterhin die Formel kopieren können und einfach immer die richtigen Werte haben. Also müssen wir von der Formel den zweiten Part (den Bezug auf B1) absolut setzen.
Dazu gehen wir in die Formel in der Zelle D3 (entweder doppelt klicken oder mit F2 den Bearbeitungs-Modus aktivieren. Wichtig, ist das der Cursor in der Formel hinter dem Mal auf B1 steht (wie im folgenden Bild zu sehen). Wenn wir nun F4 drücken, taucht vor dem B ein $ auf und auch vor der 1 ein $. Somit haben wir einen absoluten Zellbezug erstellt.
Wenn wir die Formel bestätigen, sind wir im normalen Modus. Jetzt können wir mit der rechten Maustaste die Formel wieder kopieren und unter D4 einfügen.
Anhand der Rahmen sieht man sauber, dass der blaue Rahmen auf B4 mit gerutscht ist (relativer Zellbezug), der grüne Rahmen aber weiterhin auf B1 steht (absoluter Zellbezug).
Das Kopieren der Formel kann auch wie bei der automatischen Reihenergänzung gezeigt geschehen. Mit dem Cursor auf D3 klicken, den Anfasser schnappen und bis D6 ziehen.
Nach dem Loslassen sind die Formeln wie gewünscht vorhanden.
Aufgabe: Absolute Bezüge
Berechnen Sie von unserer Beispielfirma für das Jahr 2006 die prozentualen Anteile der jeweiligen Monate – die mathematische Formel lautet: = Quartalsumsatz * 100 / Jahresumsatz
Nutzen Sie unbedingt absolute Bezüge, damit die Formel kopiert werden kann!
Das fertiges Ergebnis:
relativer + absoluter Zellbezug
Es gibt auch eine Mischform, die teilweise je nach Aufgabenstellung sinnvoll ist. Nehmen wir für unser Beispiel an, dass der Gewinn im Jahr 2007 nicht mehr 10 % sondern 15 % beträgt. Sie wollen aber die Formel aus D3 verwenden können. Also wäre es schön, wenn die Formel zwar immer in Zeile 1 bleibt (also dort absolut ist), aber die Spalte relativ gehandhabt wird.
Das ist möglich – je nachdem, wo das Dollar-Zeichen sitzt, wird die Spalte oder Reihe absolut gehalten. In unserem Fall muss also B relativ sein, die Zeile 1 aber absolut – der Zellbezug sieht dann wie folgt aus: B$1.
Gehen wir wie vorher in die Formel D3 auf den Bereich $b$1 und drücken mehrmals F4 bis die Form „B$1“ dasteht. Diese kopieren wir dann in E3.
Auch hier kann schnell die Formeln auf alle Felder kopiert werden, die benötigt werden.
Anhand des kleinen Quadrats unter der Formel nach rechts ziehen.
Anhand des kleinen Quadrats unter der Formel nach unten ziehen. Beide Zellen müssen noch markiert sein!
Alle relativen Zellbezüge sind in den Formeln angepasst - schneller kann man nicht viele Formeln erstellen.
Die Formeln funktionieren natürlich auch – die Anzeige von Formel auf Ergebnis wird wieder aktiviert, wenn man SHIFT + # tippt.
Übersicht relativer Bezug, absoluter Bezug, gemischte Bezüge
Art des Bezugs | auf Bildschirm | Bsp. nach kopieren |
---|---|---|
Relativer Bezug: nach dem Kopieren sind sowohl Spaltenbuchstabe wie Zeilennummer angepasst | A1 | B2 |
Absoluter Bezug: durch Kopieren erfolgt keinerlei Veränderung | $A$1 | $A$1 |
Gemischter Bezug: relativer Spaltenbuchstabe, absolute Zeilennummer | A$1 | B$1 |
Gemischter Bezug: absoluter Spaltenbuchstabe, relative Zeilennummer | $A1 | $A2 |
Buchempfehlungen zum Thema
Links zu Amazon - Sie unterstützen unsere Website - Danke
Buchempfehlungen zum Thema
Die im Notizzettel aufgeführten Bücher passen zum Thema.
Die Links führen zu Amazon und mit einem Kauf unterstützen Sie unsere Website. Wir erhalten eine kleine Provision, wenn Sie über den Link bestellen - das Buch kostet aber gleich viel für Sie. Danke für Ihre Unterstützung.