4.3 Relative und absolute Bezüge 

Um die Art und Weise, wie Excel mit Formeln arbeitet, noch etwas näher kennen zu lernen, soll zunächst ein einfaches Beispiel im Detail behandelt werden: In der folgenden Tabelle sind die Umsätze und die Kosten einer Firma für verschiedene Artikelgruppen zusammengestellt.

Umsätze und Kosten für verschiedene Artikelgruppen

4.3.1 Arbeit mit relativen Bezügen 

In dieser Tabelle sind zwei Berechnungen erforderlich, die Bildung von Gesamtsummen für die Spalten und die Berechnung des Rohgewinns durch Abzug der Kosten vom Umsatz.

Anstatt in einer Formel die Zahlen direkt einzugeben, arbeiten Sie mit den Adressen der Zellen. Auf den Inhalt der Zellen wird dabei über die Angabe ihrer Adressen Bezug genommen. Eine Formel holt sich dann jedes Mal den Wert, der unter dieser Adresse abgelegt ist. Anstelle von Konstanten benutzen Sie also Variable.

Bildung der Gesamtsummen 

Für die Bildung der Gesamtsummen bietet Excel die schon angesprochene Funktion SUMME. Diese Funktion ist in der Lage, alle numerischen Werte in einem angegebenen Bereich von Zellen zu addieren. Excel muss dafür nur wissen, wie groß der Bereich ist. Der Bereich muss der Funktion als Bereichsadresse übergeben werden. Eine Bereichsadresse ist eine Angabe in der Art »Von_Zelle:Bis_Zelle«. Der Bereich wird also definiert über die Adresse der Zelle, mit der der Bereich beginnt, und die Adresse der Zelle, mit der der Bereich endet, getrennt durch einen Doppelpunkt. Wenn Sie in der Beispieltabelle die Zelle B10 auswählen, könnten Sie eintragen

=SUMME(B6:B9)

Wie oben beschrieben ist es in diesem Fall aber praktischer, das Summensymbol doppelt anzuklicken. Statt die Summe für jede Spalte einzeln zu bilden, können Sie die Sache auch in einem Zug erledigen: Ziehen Sie mit gedrückter linker Maustaste über die Zellen B10 bis B12. Klicken Sie doppelt auf das Summensymbol.

Berechnung des Gewinns 

Um den Rohgewinn zu ermitteln, werden einfach vom Umsatz die Kosten abgezogen. Die Formel für die erste Zeile ist:

=B6-C6

Zelle D6 liefert das verlangte Ergebnis. Wenn die Zelle D6 noch markiert ist, müssen Sie nur das Ausfüllkästchen doppelt anklicken, um die Formeln für die anderen Artikelgruppen zu erzeugen. Statt des Doppelklicks kann das Ausfüllkästchen auch mit der Maus nach unten gezogen werden. Der Doppelklick ist insbesondere bei langen Spalten praktischer.

Wenn Sie den Mauszeiger auf eine der neuen Formeln setzen, sehen Sie, dass Excel bei den verwendeten Zelladressen jedes Mal die Zeilennummer geändert hat. Beim Kopieren werden die Adressen der Umsatz- und Kosten-Zellen also nicht als fixe Adressen behandelt, sondern sinngemäß angepasst.

Diese Anpassung ist meist erwünscht, aber nicht immer. Wenn Sie z. B. den Prozentsatz einzelner Artikelgruppen am Gesamtgewinn berechnen wollen, würde diese Anpassung zu Fehlern führen. Excel 2007 unterscheidet deshalb grundsätzlich zwei verschiedene Bezugsarten: relative und absolute Bezüge. Abgeleitet davon sind die Mischbezüge, bei denen ein Teil des Bezugs relativ, ein anderer absolut ist.

Was heißt relativ? 

Bei einem relativen Bezug notiert Excel intern die Position der Zelle, auf die Bezug genommen wird, in der Form, dass die relative Entfernung von der Zelle gemessen wird, die den Bezug enthält. Nehmen Sie noch einmal die erste Gewinnformel in der Zelle D6:

=B6-C6

Excel übersetzt diese Formel in folgende Anweisung: Ziehe von dem Wert in der Zelle, die zwei Zellen weiter links liegt, den Wert ab, der eine Zelle weiter links liegt. Wenn Sie diese Formel nach unten kopieren, kann diese Beschreibung der Rechenaufgabe unverändert bleiben, nur bezieht sie sich nun auf die Zellen B7 und C7 etc. Wenn Sie einen relativen Bezug verwenden, weisen Sie also Excel an, diesen Bezug automatisch zu verändern, wenn die Formel an eine andere Stelle versetzt oder kopiert wird.

4.3.2 Absolute und gemischte Bezüge 

Bei einem absoluten Bezug auf eine Zelle dagegen ist immer genau diese Zelle gemeint und keine andere. Wenn Sie einen absoluten Bezug verwenden, bestimmen Sie, dass der Bezug beim Kopieren und Versetzen nicht verändert werden darf, sodass er sich immer auf dieselbe Zelle bezieht. Relative und absolute Bezüge können in einer Adresse auch gemischt werden. Gemischte oder teilabsolute Bezüge werden beim Kopieren folgendermaßen behandelt: Der absolute Teil des Bezugs bleibt unverändert, der relative Teil des Bezugs wird angepasst.

Mögliche Bezugsarten

Wann welche Bezugsart? 

Der relative Bezug ist für Excel der Normalfall. Jeder einfache Zellbezug ist ein relativer Bezug. Dagegen werden bei Bezügen auf benannte Bereiche absolute Bezüge verwendet. Wird der benannte Bereich durch Löschen von Zeilen oder Spalten oder durch Einfügen von Zeilen oder Spalten verkleinert oder vergrößert, werden die Bereichsadressen aber automatisch angepasst. Wird ein benannter Bereich verschoben, werden die Bezüge ebenfalls automatisch angepasst.

Wann ist es notwendig, mit absoluten oder gemischten Bezügen zu arbeiten? Der Fall der Prozentrechnung wurde schon angesprochen. Wenn Sie in unserem Beispiel in Zelle E6 die Formel

=B6/B10

eingeben, um den Anteil des Gruppenumsatzes am Gesamtumsatz zu berechnen, erhalten Sie zunächst ein durchaus brauchbares Ergebnis. Klicken Sie in der Gruppe Start/Zahl auf das Symbol Prozentformat, um die Prozentdarstellung zu erreichen.

Ein Problem tritt erst auf, wenn Sie per Doppelklick auf das Ausfüllkästchen versuchen, die Formel nach unten zu kopieren. Excel gibt statt der Ergebnisse Fehlerwerte aus. Wenn Sie den Zellzeiger auf die Zelle E7 setzen, erscheint eine Schaltfläche, die zunächst einen Hinweis auf den Fehler gibt.

Die kopierten Formeln melden einen Fehler.

Wenn Sie die Schaltfläche anklicken, werden Ihnen verschiedene Optionen angeboten, auf den Fehler zu reagieren oder genauere Informationen dazu einzuholen.

Das Menü zum Fehler

Wenn Sie die Option In Bearbeitungsleiste bearbeiten wählen, wird der Fehler sofort erkennbar.

Offensichtlich ist ein Bezug der Formel falsch.

Die Formel bezieht sich zwar zunächst korrekt auf die zweite Artikelgruppe, dann aber nicht auf das Gesamtergebnis in B10, sondern auf die nächste Zelle B11, die leer ist. Leere Zellen nimmt Excel aber als Zellen mit dem Wert Null. Die Tatsache, dass auch der Bezug auf das Gesamtergebnis als relativer Bezug eingegeben worden ist, führt deshalb zu dem Fehlerwert #DIV/0!, da ja eine Division durch Null nach Adam Riese nicht erlaubt ist. Um den Fehler zu beheben, muss deshalb in allen Formeln der Bezug auf das Gesamtergebnis absolut gesetzt werden. Nur dann lässt sich die Formel kopieren.

Bezugsart ändern 

2 Mit können Sie den Bezug in einen absoluten Bezug verwandeln.

3 Mit »=B6/$B$10« erhalten Sie eine Formel, die nun ohne Probleme bis zur Zelle E9 kopiert werden kann.

4 Klicken Sie doppelt auf das Ausfüllkästchen oder ziehen Sie es bis zur Zelle E9.

Natürlich können Sie die Dollarzeichen auch manuell eintippen, aber es ist bequemer, dafür die Taste zu verwenden. Dabei ist es egal, ob die Einfügestelle direkt vor oder direkt hinter dem Bezug steht oder ob der Bezug insgesamt mit Doppelklick markiert ist. Wenn Sie mehrfach drücken, werden die möglichen Bezugsarten kreisförmig gewechselt.

Tabelle mit der korrigierten Prozentformel

4.3.3 Teilabsolute Bezüge 

In diesem Beispiel ist der Bezug auf die Zelle, die das Gesamtergebnis enthält, zunächst absolut gesetzt worden. Es hätte auch ausgereicht, nur die Zeilennummer absolut zu setzen, da die Spaltenbezeichnung beim Kopieren gleich geblieben ist. Die Adresse

=B$10

ist ein teilabsoluter Bezug. Angenommen, Sie wollen nicht nur die Prozentanteile in Bezug auf den Umsatz, sondern auch in Bezug auf die Kosten und den Gewinn ermitteln.

2 Diese Formel lässt sich ohne weitere Änderungen auf den Bereich E6:G9 kopieren. Klicken Sie dazu auf das Symbol Kopieren.

3 Markieren Sie E6 bis G9.

4 Klicken Sie auf das Symbol Einfügen.

Anteil der Artikelgruppen an den Gesamtergebnissen

Das Ergebnis zeigt die Anteile der Artikelgruppen an den verschiedenen Gesamtergebnissen. Dadurch, dass in der Formel die Spaltenbuchstaben relativ benutzt werden, lässt sich die Formel in E6 gleich auf alle drei Spalten kopieren. An diesem Beispiel können Sie auch sehen, dass Sie ohne weiteres eine Zelle auf einen ganzen Bereich kopieren können, wobei die Originalzelle auf sich selbst kopiert wird.

4.3.4 Aufsummierung durch Mischbezüge 

Durch eine geschickte Mischung von Bereichsbezügen können z. B. Aufsummierungen vorgenommen werden. Hier das einfache Beispiel einer Wochenauswertung mit einer Spalte von Quersummen, die jeweils die Vortagsergebnisse mit enthalten. Die erste Quersumme in der Zelle G5 enthält die Formel

=SUMME($B$5:F5)

Mit dem ersten Argument wird ein fixer Ausgangspunkt gesetzt. Sie können diese Formeln nach unten kopieren. In der Zelle G6 steht dann die Formel

=SUMME($B$5:F6)

die das aufgelaufene Ergebnis für beide Zeilen liefert.

Beispiel für Aufsummierung

4.3.5 Verknüpfte Bereiche und Schnittmengen 

Excel 2007 kann Bereichsbezüge auch verknüpfen bzw. Schnittmengen von Bereichen bearbeiten. Die Bereichsverknüpfung erfolgt mit dem Semikolon. In der Formel

=SUMME(B4:B12;C4:C12)

wird die Gesamtsumme der beiden Zeilen geliefert.

Um die Schnittmenge zweier Bereiche für eine Formel zu erhalten, kann mit dem Schnittmengenoperator, dem Leerzeichen, gearbeitet werden. Die Abbildung zeigt ein Beispiel für die Verwendung einer Schnittmenge. Die Schnittmenge ist durch eine andere Farbe hervorgehoben, die Bereiche durch einen Rahmen.

Schnittmengenbezüge sind interessant, wenn auf Teilmengen in einer Tabelle zugegriffen werden soll. Die Arbeit mit Schnittmengen ist besonders effektiv, wenn die Bereiche selbst vorher benannt wurden. Darauf wird im folgenden Kapitel noch näher eingegangen.

Beispiel für die Summierung einer Schnittmenge

Statt der in der Abbildung verwendeten Formel könnte auch eine Formel benutzt werden, die nur die Zeilennummern und die Spaltenbeschriftungen verwendet. Der Bereich wäre dann die Schnittmenge zweier kompletter Spalten und Zeilen.

=Summe(C:D 7:8)

liefert dasselbe Ergebnis wie die abgebildete Formel.

Ihre Meinung

Wie hat Ihnen das Openbook gefallen? Wir freuen uns immer über Ihre Rückmeldung. Schreiben Sie uns gerne Ihr Feedback als E-Mail an kommunikation@rheinwerk-verlag.de.