Excel ist eine intelligente Software und stets bemüht, uns lästige Arbeit abzunehmen. Dies gilt auch beim Kopieren von Formeln. Geben wir eine Formel in eine Zelle ein, und kopieren diese in eine andere Zelle, so passen sich die Bezüge der Formel automatisch an – was meistens sinnvoll ist. Durch relative Bezüge wird sichergestellt, dass Funktionen flexibel angepasst werden. Um nicht eine Formel immer wieder einzugeben und anzupassen, können wir durch die Zieh-Funktion mühelos ganze Datenreihen erstellen. Hin und wieder kommt es vor, dass diese Flexibilität nicht erforderlich ist und die Zellbezüge stets auf dieselbe Zeile, Spalte oder Zelle beziehen sollen. Um aus relative absolute Bezüge zu machen, verwenden wir das $-Zeichen (Dollar) vor dem jeweiligen Zellbezug. Falls eine Formel mehrere Bezüge beinhaltet, so können alle individuell angepasst werden.
In unserem Beispiel wird gezeigt, wie ein bestimmter Prozentsatz als absoluter Bezug in eine Tabelle integriert wird und zu welchen unterschiedlichen Ergebnissen Formeln mit absoluten und relativen Bezügen führen.
Die untere Tabelle zeigt in Spalte A eine Aufzählung von Matratzen (Produkte), die Anzahl (Spalte B) und den Preis ohne Mehrwertsteuer (Spalte C).
Wir tragen in Spalte D die Formel =B5*C5 ein. Das heißt, dass Spalte D die Anzahl (Spalte B) mit den Nettopreisen (Spalte C) multipliziert.
Excel zeigt uns in der Zelle das Ergebnis an: 67,06 €. Das ist der Nettoumsatz durch zwei verkaufte Klappmatratzen Vitalis. Wir klicken auf das kleine grüne Kästchen rechts unten in der Zelle und halten die Maustaste gedrückt.
Mit gedrückter Maustaste ziehen wir die Formel bis an das untere Ende der Tabelle (wie im Bildschirmausschnitt) und lassen die Maus los.
Excel verwendet stets relative Bezüge, das heißt, dass sich der Zeilenbezug im Gleichschritt mit ändert. In Zeile 10 heißt die Formel B10*C10.
Als Nächstes möchten wir in Spalte E den Bruttopreis (Netto plus MwSt) berechnen. Aus diesem Grund tragen wir die Mehrwertsteuer als Zahl in Zelle D2 ein, damit Excel weiß, wie hoch die MwSt ist.
Die Formel in Spalte E lautet: D5*D2+D5, was übersetzt heißt, multipliziere den Nettobetrag (D5) mit dem Mehrwertsteuersatz von 19% (D2) und addieren den Nettobetrag dazu, um den Bruttobetrag zu erhalten.
Wir sehen, dass die Formel funktioniert und einen korrekten Wert von 79,80 € anzeigt.
Wie in Spalte D ziehen wir mit der gleichen Technik die Formel bis ans Ende der Tabelle, um die Bruttopreise für die anderen Produkte zu berechnen.
Spalte E zeigt uns mehrere Fehlmeldungen an. Was ist passiert?
Da die Formel auf relativen Bezügen basiert, wird jedes Mal, wenn sie um eine Zeile nach unten verschoben wird, auch der Zellbezug für den Mehrwertsteuersatz, der eigentlich bei D5 liegt, nach unten verschoben. Ein Beispiel: Schauen wir uns Zelle E10 an. Der Nettopreis wird korrekt bezogen (D10, blauer Kasten), jedoch stimmt der Bezug zum MwSt-Satz nicht. Excel nimmt Zelle D7 als Mehrwertsteuersatz, weil dieser Bezug mit nach unten wanderte, was falsch ist. Der Bezug muss bei D5 bleiben, egal wohin die Formel gezogen wird.
Die Lösung: wir gehen einen Schritt zurück und erstellen eine neue Formel in E5. Wir wissen, dass die Zelle D5 nicht relativ verschoben werden darf, weshalb wir Sie mit absoluten Bezügen “festnageln”. Die Formel heißt nun D5*$D$2+D5.
Das bedeutet, dass die Bezüge zum Nettopreis (D5) relativ bleiben, jedoch die Zelle D5 eisern auf dieser Position verharrt.
[box color=”lblue”]Hinweis: falls sich bei einer Formel die Spalten, aber nicht die Zeilen verschieben sollen, so setzen wir nur ein $-Zeichen auf den Zeilenbezug: D$5. Falls sich die Zeilen, aber nicht die Spalten, relativ verschieben dürfen, so setzten wir das $-Zeichen in den Spaltenbezug $D5. Wenn sich der Bezug überhaupt nicht verschieben soll, werden die $-Zeichen so gesetzt: $D$5. Dies funktioniert auch bei Bereichen oder Matrizen. [/box]
Im nächsten Schritt kopieren wir die Formel und sehen, dass in Zelle E10 die Mehrwertsteuer korrekt berechnet wird und trotz Verschiebung auf D2 fixiert bleibt.
