Relativ oder Absolut oder nur $ im Blick

Nein, dies ist kein Artikel über die Vermehrung von grünen Scheinchen sondern der Einstieg in relative Tiefen und absolute Höhen, von welchen selbst Einstein nur träumte.

Der Unterschied zwischen relativen und absoluten Bezügen ist gerade für den Anfänger ein Greuel.
Ich behaupte mal, dass aber selbst Fortgeschrittene immer wieder ein Problem damit haben und selbst Profis bei Formel darauf kurzfristig hereinfallen können.

Deswegen wollen wir uns hier mal den Hintergrund anschauen.

Ein kleines Beispiel, welches uns den Hintergrund von relativen Bezügen näherbringen soll:

A B C D E 1           2   1 2 3   3   3 4 7   4           Formeln der Tabelle D2 : =B2+C2 D3 : =B3+C3

Stellen Sie dieses Beispiel her und zwar nur mit der Formel in D2.

Wenn Sie nun, wie Sie sicherlich kennen, die Formel in D2 nach D3 herunterziehen, ergibt sich die Formel in D3. Aber was passiert dabei? Zuerst muss man eines verinnerlichen:
Das Ziehen einer Formel ist nichts anderes als ein Kopieren!

Dies wollen wir gleich testen: Löschen Sie die Zelle D3, kopieren Sie nun die Zelle D2, markieren Sie die Zelle D3 und fügen Sie ein.
Voilá, wie versprochen.

Ich kopiere eine Zelle - in unserem Beispiel die Zelle D2. In dieser steht =B2+C2.
Nun füge ich dies in D3 ein.

Excel passt die kopierte Formel in ihren Zellbezügen an und zwar abhängig vom Abstand der Zielzelle zur Quellzelle.
Und zwar wie folgt:
Alle relativen Bezüge werden in ihrer Spaltenanzahl und ihrer Zeilenanzahl angepasst.

Der Abstand von D2 nach D3 ist : 0 Spalten und +1 Zeile.
Deswegen wird aus B2+C2 -> B3+C3, da der Spaltenabstand 0 und der Zeilenabstand +1 ist.

Bringen wir ein wenig Power in die Sache.

Stellen Sie sich folgendes vor:
Sie kopieren die Zelle D2 und fügen diese in die Zelle G8 ein. Welche Formel wird dann in Zelle G8 stehen?

Versuchen Sie es erst selbst auszuarbeiten - z.B. auf einem Blatt Papier, bevor Sie weiterlesen.

Untersuchen wir die Aufgabe:
Der Abstand von D nach G sind +3 Spalten und + 6 Zeilen.
In D2 stand =B2+C2 -> Aus B wird E (+3Spalten) aus 2 wird 8 (+6Zeilen) das Pluszeichen bleibt, ist ja kein Bezug dann wird aus C ->F und aus 2 wieder 8.
Unsere Formel sollte also lauten: =E8+F8

Und ? Gleich hingekriegt? Bereit für etwas komplizierteres? Na dann wollen wir mal.

A B C D E F 1             2             3             4   2         5     3       6       4     7         14   8             Formeln der Tabelle E7 : =B4+C5*D6

Diesmal gleich drei Aufgaben:

1. Die Zelle E7 wird nach H11 kopiert, welche Formel steht dann in H11?
2. Die Zelle E7 wird nach G5 kopiert, welche Formel steht dann in G5?
3. Die Zelle E7 wird nach H2 kopiert, welche Formel steht dann in H2?

Versuchen Sie es wieder auf einem Blatt Papier bevor Sie weiterlesen.

1. Der Abstand von E7 nach H11 ist +3Spalten und +4 Zeilen.
Unsere Formel: =B4+C5*D6
B+ 3 -> E, 4 +4 -> 8, C+3 -> F, 5+4 ->9 , D+3->G und 6+4: 10 also:

=E8+F9*G10

2. Der Abstand von E7 nach G5 ist +2 Spalten und -2 Zeilen!
B+2->D, 4-2->2, C+2->E, 5-2->3, D+2->F, 6-2-> 4 also:

D2+E3*F4

3. Muss Unsinn rauskommen (#Bezug) warum?
Klar, haben Sie sofort gesehen. Der Zeilenabstand ist -5!
Und aus B4 kann natürlich nicht E-1 werden.

Probieren Sie nun selbst weiter, auch mit kompakteren Formeln, um ein Gefühl dafür zu bekommen.
Man muss einfach nur verstehen, dass bei relativen Bezügen die Zellbezüge an den Abstand der Quell - und Zielzelle angepasst werden.

Absolute Bezüge

Betrachten wir folgendes Beispiel:

A B C D E F 1             2   2   9 18   3   4   6     4   6   2     5             Formeln der Tabelle E2 : =D2*MIN(B2:B4)

Wir haben die unheimlich sinnvolle Aufgabe, die Zahlen des Blauen Bereiches jeweils mit dem Minimum des Grünen Bereiches zu multiplizieren.

Die Formel in E2 ist schnell erstellt:
=D2 * Min(B2:B4)

Wenn wir aber nun die Formel nach unten kopieren, werden wir aber unangenehm überrascht (Natürlich nicht mehr wirklich überrascht)

Denn aus Min(B2:B4) wird auf in E3 wie oben gesehen Min(B3:B5) und dies ist nun gar nicht schön.
Das was uns eigentlich beim Formeln kopieren so gut gefällt, hat hier ganz negative Auswirkungen.

Überlegen wir uns also, was wir eigentlich haben wollen:

Nochmals die Ursprungsformel in E2: =D2 * Min(B2:B4)

Beim Kopieren möchten wir natürlich, dass sich D2 anpasst. Also beim Kopieren nach E3 soll daraus D3 werden, wie gehabt.

Was wir aber gar nicht mögen, ist das sich B2:B4 verändert !

Deswegen müssen wir diese Bezüge einfrieren - absolut setzen!

Dies kann ich erreichen, indem ich vor jedem Spalten und Zeilenbezug ein Dollarzeichen setze.
In unserem Beispiel : $B$2:$B$4

Wichtig ist hier zu verstehen, dass ich sowohl die Spalten als auch die Zeilenbezeichnung beachten muss!

Ein Dollarzeichen vor einem Bezug heißt nichts anderes: Liebes Excel, ändere diesen Bezug beim Kopieren nicht!


Ändern Sie nun die Formel in E2 entsprechend ab in =D2*MIN($B$2:$B$4) und testen Sie.


Kurze Verständnisfrage: Haben wir wirklich alle diese Dollarzeichen gebraucht ?

Sie haben natürlich vollkommen recht. Die Dollarzeichen vor den Spalten hätten wir weglassen können, da zwischen unserer Quellzelle und unserer Zielzelle es ja keinen Spaltenabstand gibt
(Von E nach E = 0 Spalten)
Also hätte auch :B$2:B$4 gereicht.

In den meisten Fällen wird man aber automatisch beides Einfrieren, obwohl es durchaus Situationen gibt, in welchem man dies ganz gezielt macht.
Aber erstmal ein Kasten:

Typische Fehler durch fehlende absolute Bezüge tauchen bei S-Verweisen, Matrizen, allgemein Bereichsbearbeitungen auf.

Wenn Sie fehlerhafte Zahlen erhalten, sollten Sie immer erst überprüfen ob es nicht am fehlenden absoluten Bezug liegen kann.

Glauben Sie mir, wenn komische Werte auftauchen:

Es lohnt sich immer, zuerst zu schauen ob man richtig absolutiert hat!

Sie sollten jetzt ausführlich testen. Einfach mal Formeln erstellen und irgendwohin kopieren.

Mit Dollarzeichen an verschiedenen Bezugspositionen und sich anschauen, was aus der Formel jeweils wird.
Es lohnt sich !!!