Excel Training – Gemischter Bezug

Artikel bewerten

Gemischte Bezüge sind eine Kombination aus relativen und absoluten Bezügen. Mithilfe von gemischten Bezüge können Sie Ihre Berechnungen sowohl nach unten als auch nach rechts kopieren.


Dies benötigen Sie, wenn Sie mit solchen Tabellen konfrontiert werden.

Gemischte Bezüge

Analyse Zinsbelastung

Unser Unternehmen möchte eine Lagerhalle bauen, dafür benötigen wir ca. 100.000 EUR Kredit. Bei dem folgenden Beispiel wollen wir die monatliche Zinsbelastung für verschiedene Szenarien ausrechnen. Dabei variieren die Zinsen und der Kreditbetrag.

Mit unserem momentanen Kenntnisstand würden wir das Problem wie folgt angehen. Wir gehen in die Zelle B4 und beginnen die Berechnung mit dem GLEICH Zeichen. Dabei multiplizieren wir den Betrag in der Zelle B3 mit dem Zinssatz in der Zelle A4. Da wir die monatliche Belastung ausrechnen wollen, teilen wir das Ganze durch 12. Anschließend bestätigen wir die Eingabe mit der ENTER Taste.

Formel für die Zinsbelastung

Berechnung der Zinsbelastung

Wenn wir nun unsere Formel mit Doppelklick nach unten kopieren, sehen wir, dass das Ergebnis keinen Sinn ergibt.

Falsche Formel

Kopie produziert falsche Ergebnisse

Die Zelle B3 muss mit einem absoluten Bezug fixiert werden. Dazu gehen wir in die Formel, wählen B3 aus und betätigen die F4 Taste um den absoluten Bezug zu setzen.

Bezug fixiert

Absoluten Bezug setzen

Wenn wir die Formel nun erneut per Doppelklick kopieren macht das Ergebnis auch einen Sinn.

Zinsbelastung richtig berechnet

Raten für einen Betrag korrekt berechnet

Nun wollen wir die Raten für andere Darlehensbeträge ausrechnen und ziehen die Formel nach rechts.

Falsche Ergebnisse

Horizontale Kopie liefert falsche Ergebnisse

Sie sehen schon auf den ersten Blick, dass das Ergebnis falsch ist. Wenn wir uns die Formel genauer anschauen, sehen wir, dass statt Zinsen die absoluten Werte multipliziert werden. Das ergibt natürlich keinen Sinn, denn wir wollen ja den jeweiligen Betrag mit dem Prozentsatz multiplizieren.

Wir machen das Ganze rückgängig indem wir in der oberen linken Ecke auf das Pfeil Symbol klicken.

Tastenkürzel STRG+Z

Aktion rückgängig machen

Um die Raten für den Darlehensbetrag von 92.500 EUR zu berechnen, gehen wir nun in die Zelle C4. Dort beginnen wir die Berechnung wie gewohnt mit dem GLEICH Zeichen, wählen die Betrag in der Zelle C3 aus, fixieren diesen, und multiplizieren mit dem Prozentsatz in der Zelle A4. Anschließend teilen wir durch zwölf um die monatlichen Raten zu bekommen. Mit dem Doppelklick können wir die Formel in die Zeilen kopieren.

Das müssten Sie nun in allen Spalten wiederholen, was ziemlich mühselig ist.

Raten Schritt für Schritt berechnen

Nächste Raten berechnen

Es gibt aber auch einen einfacheren Weg. Zunächst löschen wir aber die alten Berechnungen, indem wir sie alle markieren und die Entf Taste drücken.

Wir fangen ganz einfach an. Zunächst beziehen wir uns auf die Zelle A4 und kopieren diese einfache Formel mit einem Doppelklick nach unten.

Bezüge in Zeilen angepasst

Vertikale Bezüge werden angepasst


Die Bezüge werden korrekt in den Zeilen angepasst. Wir versuchen nun die Formel nach rechts zu ziehen. Dabei werden aber die Spalten angepasst: aus A wird B, aus B wird C und so weiter. Dieses Verhalten ist in unserem Beispiel unerwünscht. Wir wollen uns ja immer auf die Spalte A beziehen.

Bezüge für Spalten fälschlicherweise angepasst

Spalten werden angepasst

Dabei soll die Spalte fest bleiben und die Zeile sich ändern. Daher fixieren wir die Spalte A mit dem absoluten Bezug indem wir wiederholt die Taste F4 drücken oder vor die Spalte A ein Dollarzeichen schreiben.

Absoluter Bezug für die Spalte

Spalte A wird fixiert

Diese Eingabe bestätigen wir mit der ENTER Taste und kopieren die Formel mit einem Doppelklick nach unten. Anschließend ziehen wir die Formel nach rechts.

Richtige Werte

Horizontale und vertikale Kopie korrekt

Sowohl die Zeilen als auch die Spalten zeigen nun den korrekten Zinssatz an.

Bei dem Betrag verfahren wir analog. Zunächst löschen wir alle Berechnungen und starten wieder in der Zelle B4. Dort beziehen wir uns auf die Zelle B3, die den Kreditbetrag enthält. Wir bestätigen die Eingabe mit ENTER und kopieren die Formel mit Doppelklick in die darunterliegenden Zeilen.

Es fällt auf, dass die Zeilen angepasst werden: aus der 3 wird 4, aus 4 wird 5 und so weiter.

Verschiebung der Zeilen

Zeilen ändern sich

Für den Betrag brauchen wir aber immer die dritte Zeile, daher müssen wir diese fixieren.

Absoluter Bezug für die Zeile

Zeile wird fixiert

Mit einem absoluten Bezug auf die Zeile 3 bleibt diese starr. Gleichzeitig kann sich die Spalte anpassen wenn wir die Formel nach rechts kopieren.

Richtige Beträge

Korrekte Beträge werden dargestellt

Mit dem neu gewonnen Wissen ausgestattet können wir die eigentliche Berechnung starten. Die Spalte für den Zins und die Zeile für den Betrag sollen fix bleiben, während andere Elemente flexibel sind.

Richtige Formel mit gemischten Bezug

Korrekte Formel für die Berechnung

Diese Formel lässt sich sowohl vertikal als auch horizontal kopieren und liefert stets das korrekte Ergebnis.

Komplette Tabelle

Endergebnis

Beeindrucke deinen Chef mit coolen Excel Tricks!

  • Lasse dich von deinen Kollegen nicht abhängen!
  • Spare Zeit und nutze diese für angenehmere Dinge
  • Steigere deine Produktivität und Effizienz
  • Es ist leicht - Schritt für Schritt Anleitungen die jeder versteht
Fordere kostenlos 20 Excel Tricks an

Fordere das kostenlose eBook an



* Pflichtfeld

Kein SPAM. 100% sicher

Beeindrucke deinen Chef mit coolen Excel Tricks!

x

Fordere jetzt das kostenlose eBook an um deine Produktivität und Effizienz ganz leicht zu steigern.

Nein, danke. Ich möchte keine Zeit sparen.
Denis Reis gibt als Autor sein umfassendes Wissen rund um Excel und VBA weiter. Der aus Düsseldorf stammende Familienmensch zählt zur denjenigen, die auf komplizierte Darstellungen verzichten und das Ganze auf den Punkt bringen.

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind markiert *