Studium und PC - effektiver studieren

www.studium-und-pc.de

- einfacher Studieren mit Computerkenntnissen

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.

Quartalszahlen Beispielfirma Seifenblasenfirma Bubble-Fix

Wir bilden nun die Gesamtsumme, also B2 bis B6 markieren ...

Bereich markieren, über den die Summe gebildet wird

und das Summenzeichen anklicken.

Summenformel erstellen über Summen-Icon

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 + #

umschalten der Anzeige für Formeln in den Zellen anstatt der Ergebnisse

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.

Formel kopieren - relative Zellbezüge werden automatisch angepasst

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“.

Zeile einfügen - Formeln werden automatisch angepasst

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)“.

Zeile einfügen - realtive Zellbezüge sind automatisch angepasst

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.

Formel zur Berechnung des Gewinns

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.

anhand der Markierungen sofort ersichtlich - Formel falsch

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.

Umstellen von relativen auf absoluten Bezug mit F4 in Excel

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.

kopieren von Formel mit absoluten Bezug

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.

kopieren von Formeln mit absoluten Bezug durch ziehen mit der Maus

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:

Aufgabe für absolute Bezüge

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.

Mischform - relativer absoluter Bezug

Auch hier kann schnell die Formeln auf alle Felder kopiert werden, die benötigt werden.

kopieren von vielen Formeln in einem Block

Anhand des kleinen Quadrats unter der Formel nach rechts ziehen.

Anhand der kleinen Kästchen nach rechts ziehen

Anhand des kleinen Quadrats unter der Formel nach unten ziehen. Beide Zellen müssen noch markiert sein!

Anhand der kleinen Kästchen nach unten ziehen

Alle relativen Zellbezüge sind in den Formeln angepasst - schneller kann man nicht viele Formeln erstellen.

alle relativen Zellbezüge sind in den Formeln angepasst

Die Formeln funktionieren natürlich auch – die Anzeige von Formel auf Ergebnis wird wieder aktiviert, wenn man „SHIFT + #“ tippt.

Ergebnisse anzeigen

Ü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 $A1
Rechnen und automatische FormelerstellungSeitenanfangNamen für Zellen oder Bereiche
günstig Microsoft Office kaufen
www.studium-und-pc.de - einfacher studieren mit Computerkenntnissen
Word, Excel, PowerPoint wissenschaftlich einsetzen