Excel Kurs

Teil III

Zinsberechnungen ( Sparbuch, Entnahmeplan, Monatszins ) Das große 1 x 1

( Berechnungen mit Namen, die Matrixformel ),Blattregister, umbenennen, hinzufügen, löschen, verschieben,

Spalten und Zeilen hinzufügen, löschen, kopieren, verschieben, ausblenden Zellen und Bereichen, löschen , kopieren, verschieben

F. Bäumer

2 / 15

Excel-Kurs III

Zinsberechnungen

Kochbuch zur Erstellung eines Sparbuches• bereiten Sie die Tabelle wie links gezeigt vor.

• klicken Sie die Zelle B4 ( Wert für den Zinssatz ) an und schreiben in das Namenfeld „Zinssatz“ hinein.

• schreiben Sie folgende Formeln in die entsprechenden Zellen

• nun dieses Formeln in wieder entsprechenden Zellen schreiben

• markieren Sie die Zellen B8 und C8und füllen Sie die Zellen nach der Ausfüllmethode herunter

Die Tabelle in der Ergebnisansicht

3 / 15

Excel-Kurs III

Zinsberechnungen

Der Entnahmeplan

Zelle B5 anklicken und ins Namenfeld Auszahlung schreiben

Zelle B6 anklicken und ins Namenfeld Zinssatz schreiben

Die Formeln der 9. Zeile

Die Formeln der 10. Zeile

Zellen B10 und C10 für das automatische Ausfüllen vorbereiten und runterziehen

4 / 15

Excel-Kurs III

ZinsberechnungenMonatsverzinsung

Bei einigen Geldanlagen erfolgt die Verzinsung nicht pro Jahr, sondern pro Monat.Es stellt sich also die Frage, mit welchem Zins muss gerechnet werden, um den Monatszins zu ermitteln

Behauptung:

Der Monatszins ist 1/12 vom Jahreszins

Genauere Betrachtung:

Der Kapitalzuwachs nach einer entsprechenden Verzinsung ist pro Periode:

ZinssatzAltkapitalAltkapitalNeukapital *

)1(* ZinssatzAltkapitalNeukapital

)1(*)1(* ZinssatzZinssatzAltkapitalNeukapital

2)1(* ZinssatzAltkapitalNeukapital

oder

Zinsen nach 2 Perioden:

oder

5 / 15

Excel-Kurs III

ZinsberechnungenMonatsverzinsung

Nach 12 Perioden ( oder Monaten ) heißt die Formel:

12)1(* satzMonatsZinsAltkapitalNeukapital

)1(* satzJahresZinsAltkapitalNeukapital

)1(*)1(* 12 satzJahresZinsAltkapitalsatzMonatsZinsAltkapital

)1()1( 12 satzJahresZinssatzMonatsZins

1)1(12 satzJahresZinssatzMonatsZins

Wird der Jahreszins eingesetzt lautet die Formel:

Werden die Formeln glich gesetzt, ergibt sich

Und umgerechnet auf den Monatszins erhalten wir:

Unsere Excelformel lautet dann:

1)12/1()^1( JahreszinssatzMonatsZins

6 / 15

Excel-Kurs III

Aufgabe: Es soll das große 1 x 1 als Tabellen dargestellt werden.

In den Kreuzungsbereichen aus Spaltenüberschrift und der Zeilenüberschrift soll das entsprechende Ergebnis stehen.

Bedingung: Es soll nur einmal eine Formel geschrieben werden, dann soll mit der Möglichkeit des automatischen Ausfüllens nach unten und nach rechts die Tabelle mit den entsprechenden Formeln gefüllt werden.

Das große 1 x 1

7 / 15

Excel-Kurs III

Das große 1 x 1 ( relative und absolute Bezüge )

Vorüberlegung:

Durch die Formel =B4*C3 in der Zelle C4 würden durch das automatische Ausfüllen falsche Formeln in den unteren, sowie in den Nachbarzeilen entstehen.

Das Problem ist die Automatische Zeilenerhöhung im Bezug C3 und die automatische Spaltenerhöhung des Bezuges B4

Lösung:

Durch ein Dollarzeichen vor dem Zeilenbezug im Bezug C$3, bleibt beim automatischen Ausfüllen der Bezug erhalten.

Das gleiche gilt für das Dollarzeichen vor dem Spaltenbezug im Bezug $B4 beim automatischen Ausfüllen nach rechts.

8 / 15

Excel-Kurs III

Das große 1 x 1 ( Berechnungen mit Namen )

Klicken Sie die Zeile 3 an und benennen Sie diese Zeile im Namenfeld als „Zeile“

Vorbereitung

Klicken Sie die Spalte B an und benennen Sie diese Spalte im Namenfeld als „Spalte“

Nun brauchen wir nur noch folgende Formel in Zelle C4 schreiben: =Zeile*Spalte und die Tabelle mit der Möglichkeit des automatischen Ausfüllens komplettieren.

9 / 15

Excel-Kurs III

Namenerstellung unter Excel

Zellen, Zeilen, Spalten oder Bereiche lassen sich sehr einfach im Namenfeld in einen frei definierten Namen umwandeln

Um Namen zu löschen oder zu ändern , bedarf es den Weg über das sogenannte „Namen definieren“ -Fenster“, welches wie folgt aufgerufen wird:

10 / 15

Excel-Kurs III

Namenerstellung unter Excel

Namen löschen

Namen ändern

Um einen Namen aus der Liste der Namen zu löschen, muss dieser markiert werden.

Anschließend wir er mittels „Löschen“ – Schaltfläche entfernt.

Für die Namensänderung muss wiederum der entsprechende Name angewählt werden.Dann kann im sogenannten Bezugsfeld der Bezug zu diesem Namen geändert werden.

11 / 15

Excel-Kurs III

Das große 1 x 1 ( Die Matrixformel )

Vorgehensweise:

• markieren Sie den Tabelleninhalt C4 bis V23

• drücken Sie nun ein Gleichheitszeichen

• wählen Sie mit der Maus den Bereich B4:B23

• setzen Sie ein Stern ( * ) für die Multiplikation

• wählen Sie den Bereich C3:V3

• drücken Sie nun folgende Tastenkombination:

Shift + Strg + Return

• Die Tabelle ist mit den entsprechenden Zahlen gefüllt

12 / 15

Excel-Kurs III

Blattregister umbenennen

Vorgehensweise:

• mit der rechten Maustaste auf das entsprechende Blattregister klicken

• aus dem nun sichtbaren Kontextmenü die Option „Umbenennen“ wählen

• den nun dunkel hinterlegten Text des Blattregisters durch Eintippen eines neuen Namen ändern.

Hinweis:

schneller geht’s, wenn das Blattregister doppelt angeklickt wird, dann kann der Name sofort geändert werden.

13 / 15

Excel-Kurs III

Blattregister löschen

Blattregister einfügen

Vorgehensweise:

• mit der rechten Maustaste auf das entsprechende Blattregister klicken

• es ist nun vor dem aktuellen Blattregister ein neues Blattregister angelegt worden.

• aus dem Kontextmenü die Option „Einfügen“ wählen

• aus dem „Einfügen“-Fenster die Tabelle anklicken

• aus dem Kontextmenü die Option „Löschen“ wählen

14 / 15

Excel-Kurs III

Blattregister verschieben

Vorgehensweise:

• mit der linken Maustaste auf das entsprechende Blattregister klicken und geklickt halten

Blattregister kopieren

• nun die Maus nach rechts verschieben bis die Einfügemarke ( kleines schwarzes Dreieck an der richtigen Position ist.

• dann die Maustaste loslassen

Vorgehensweise:

• die Vorgehensweise ist die gleiche, wie beim Verschieben eines Blattregisters, nur das bei diesem Vorgang die Strg-Taste gedrückt und gedrückt gehalten werden muss

15 / 15

Excel-Kurs III

Blattregister ausblenden

Blattregister einblenden

Über die Menüleiste die Option „Format“, „Blatt“, „Ausblenden“ wählen

Über die Menüleiste die Option „Format“, „Blatt“, „Einblenden“ wählen

Aus dem nun eingeblendeten Fenster das entsprechende Blattregister auswählen ( falls mehrere vorhanden sind )