Archiv des LibreOffice- und OpenOffice.org-Wiki

[ÜberSicht] [TitelIndex] [WortIndex] [SeiteFinden

(./) OOo2.3

Editieren

In diesem Artikel geht es nicht darum, eine fertige Vorlage zur Verfügung zu stellen – obwohl aus dem beigefügten Beispieldokument eine solche abgeleitet werden kann.1 Eigentlicher Inhalt ist jedoch die Vorstellung einiger Formeln (Schwerpunkt: SUMMENPRODUKT), mit denen Datenreihen unter verschiedenen Aspekten zu Kategorie-Totalen zusammengezogen und ausgewertet werden können.

1. Das Journal

Die einfachste und wohl auch flexibelste Art, Kontobewegungen zu erfassen, ist ein Journal : jede Einnahme oder Ausgabe wird in einer eigenen Zeile erfasst. Die Unterscheidung zwischen Einnahmen und Ausgaben kann auf verschieden Arten vorgenommen werden. Man könnte verschiedene Spalten benutzen oder Ausgaben als negative und Einnahmen als positive Beträge erfassen – im vorliegenden Beispiel verwenden wir eine gemeinsame Betragsspalte, geben alle Werte positiv ein und benutzen eine zusätzliche Spalte, welche die Bewegung als Einnahme oder Ausgabe klassifiziert. Damit schaffen wir gleichzeitig ein Selektionskriterium, das für Auswertungen verwendet werden kann.

1.1. Die Eingabespalten

Pro Bewegung benötigen wir:

A

B

C

D

E

1

Datum

Betrag

E/A

Konto

Bemerkungen

2

01.01.2007

1'238,00

E

Anfangssaldo

3

01.01.2007

3'600.00

E

Lohn

4

01.01.2007

1'200,00

A

Wohnung

5

03.01.2007

28,40

A

Essen

1.2. Spaltennamen

Mit „Spaltennamen“ sind hier nicht die Titel der Spalten gemeint. Da wir uns in diversen Formeln auf ganze Spalten beziehen müssen, vergeben wir diesen Spalten Namen, die wir anstelle von Zelladressen benutzen können. Folgende Spaltennamen werden über Einfügen → Namen → Festlegen 2 vergeben:

Transaktionsdatum

$A$2:$A$5000

Betrag

$B$2:$B$5000

Art

$C$2:$C$5000

Konto

$D$2:$D$5000

2. Eingabehilfen

2.1. Spaltentitel fixieren

Damit die Spaltentitel am Bildschirm sichtbar bleiben, auch wenn das Journal sehr lang wird, setzt man den Cursor in die Zeile unterhalb der Spaltentitel und aktiviert Fenster → Fixieren.

Für die Spalten Einnahmen/Ausgabenklassifizierung und Konten möchten wir DropDown-Felder erstellen, aus denen der aktuelle Eintrag ausgewählt werden kann. Das erleichtert nicht nur die Datenerfassung sondern stellt auch sicher, dass keine Tippfehler anfallen, was zu falschen Kontobezeichnungen führen und die Auswertungen verfälschen würde.

Dazu benutzen wir Daten → Gültigkeit…. Auf dem Register Kriterien werden die zulässigen Varianten definiert, auf dem Register Fehlermeldung wird die Aktion festgelegt, die eine Falscheingabe auslösen soll.

gueltigkeit-kriterien.png

gueltigkeit-fehlermeldung.png

Die Kontenbezeichnungen könnte man auf gleiche Art erfassen. Der Vorschlag hier aber lautet:
In einem unbenutzten Tabellenbereich werden die Kontenbezeichnungen untereinander in eine Spalte geschrieben. Die Spalte bekommt den Namen Kontenstamm und wird etwas größer definiert als derzeit Kontenbezeichnungen vorhanden sind. Kommen später neue Konten hinzu, fügt man sie einfach an die bestehende Liste an und so sind sie sofort überall vorhanden, wo eine Funktion sich auf diesen Namen bezieht. Für die Gültigkeitsprüfung wird im Kriterienbereich unter Zulassen die Option Zellbereich aktiviert und als Quelle der Name Kontenstamm eingetragen.

Die Gültigkeitsprüfung wird beim Kopieren von Zellen wie eine Formatierung übernommen.

3. Die Formelspalten

3.1. Saldo

Um jederzeit den aktuellen Stand zu kennen, wird eine Saldo-Spalte mitgeführt. In der ersten Zeile übernimmt man einfach den Betrag aus der Spalte „Betrag“.

A

B

C

F

1

Datum

Betrag

E/A

Saldo

Formel in Spalte F

2

01.01.2007

1'238,00

E

1'238,00

=B2

3

01.01.2007

3'600.00

E

4'838,00

=F2+WENN(C3="E";+B3;-B3)

4

01.01.2007

1'200,00

A

3'638,00

=F3+WENN(C4="E";+B4;-B4)

5

03.01.2007

28,40

A

3'609,60

=F4+WENN(C5="E";+B5;-B5)

Die Formelspalte kann einfach kopiert werden, da es sich um relative Adressen handelt, passen sich die Zelladressen beim Kopieren an.

3.2. Monatstotale

Für die Ermittlung der monatlichen Ausgaben und Einnahmen benötigen wir aus der Datumsspalte den aktuellen Monat und das aktuelle Jahr. Beides liefern uns die Funktionen MONAT() und JAHR().

Wir vergleichen daher pro Zeile Monat und Jahr mit der gesamten Datumsspalte und summieren monatlich alle Beträge aus der Betragsspalte, getrennt nach Einnahmen und Ausgaben. Die Formel, welche die Einnahmen gemäß dem Monat in Zeile 2 addiert, lautet SUMMENPRODUKT(). Statt Bereichsangaben wie $B$1:$B$5000 verwenden wir die zuvor festgelegten Spaltennamen:

=SUMMENPRODUKT(MONAT(Transaktionsdatum)=MONAT($A2);JAHR(Transaktionsdatum)=JAHR($A2);Art="E";Betrag)

Entsprechend heißt die Formel, welche die Ausgaben gemäß dem Monat in Zeile 2 addiert:

=-SUMMENPRODUKT(MONAT(Transaktionsdatum)=MONAT($A2);JAHR(Transaktionsdatum)=JAHR($A2);Art="A";Betrag)

Um das Journal übersichtlicher zu gestalten, sollen diese Monatstotale nur am Monatsletzten sichtbar sein. Wir binden die Formel daher jeweils in eine WENN-Abfrage ein, welche den Monat der aktuellen Zeile mit demjenigen der Nachfolgerzeile vergleicht und nur, wenn diese unterschiedlich sind, wird das Ergebnis ermittelt und angezeigt:

=WENN(MONAT(A2)=MONAT(A3);"";SUMMENPRODUKT(...))

A

B

C

D

E

F

G

H

I

J

1

Datum

Betrag

E/A

Konto

Bemerkungen

Saldo

Monat

Einnahmen

Ausgaben

Monats-Total

2

01.01.2007

1'238,00

E

Anfangssaldo

1'238,00

3

01.01.2007

3'600.00

E

Lohn

4'838,00

4

01.01.2007

1'200,00

A

Wohnung

3'638,00

5

03.01.2007

28,40

A

Essen

3'609,60

...

32

31.01.2007

14,25

A

Nicht zugeordnet

1'772,74

Jan. 2007

4'838,00

-3'065,26

1'772,74

33

01.02.2007

3'600,00

E

Lohn

5'372,74

34

01.02.2007

1'200,00

A

Wohnung

4'172,74

4. Auswertungen

Für die Auswertungen benutzen wir ein eigenes Tabellenblatt.

4.1. Total pro Konto/Monat

4.1.1. Tabelle

A

B

C

D

E

F

G

H

1

Ausgaben

2

Konten/Monat

Jan. 2007

Feb. 2007

Mrz. 2007

Apr. 2007

Mai 2007

Jun. 2007

Jul. 2007

3

nicht zugeordnet

67,30

137,51

60,94

0,00

0,00

0,00

110,00

4

Wohnung

1238,90

1270,50

1317,00

1237,50

1239,80

1242,50

1281,60

5

Auto

426,11

50,14

65,93

391,02

77,39

38,12

24,17

Für die Spalte mit den Kontobezeichnungen verwenden wir die gleiche Gültigkeitsformel wie im Journal und erfassen jedes Ausgabenkonto in einer Zeile.

Für die Spaltentitel benötigen wir ein („echtes“) Datumsformat, da wir anschließend aus der Beziehung Konto-Monat die Daten zusammenziehen. Der Spaltentitel für den ersten Monat in der Auswertungstabelle ergibt sich aus dem kleinsten Datum aus der Journalspalte Datum:3

=MIN(Transaktionsdatum)

Für die zweite Datumsspalte extrahieren wir Jahr und Monat aus dem Datum der ersten Spalte, erhöhen den Monat um 1, ergänzen um „Tag 1“ und generieren daraus das Datum für den zweiten Monat:

=DATUM(JAHR(B2);MONAT(B2)+1;1)

Diese Formel wird soweit nach rechts gezogen, wie Datumsspalten gewünscht sind.

Für die Ermittlung der Monatstotale pro Konto benutzen wir wiederum die Funktion SUMMENPRODUKT(). Aus dem Journal sollen jeweils alle Beträge summiert werden, welche dem Konto der jeweiligen Zeile zugeordnet werden können und deren Transaktionsdatum im gleichen Monat liegt wie Monat/Jahr der zuständigen Auswertungsspalte.

In B3 schreiben wir folgende Formel:

=SUMMENPRODUKT(Konto=$A3;Betrag;MONAT(Transaktionsdatum)=MONAT(B$2);JAHR(Transaktionsdatum)=JAHR(B$2))

Beachten Sie die unterschiedliche Verwendung von relativer/absoluter Adresse:
Diese Formel kann nach rechts und nach unten kopiert werden, wobei sich Monat und Konto zeilen- und spaltengerecht anpassen. Statt Zellbereichsadressen verwenden wir wieder die Namen, die wir den Spalten zuvor zugewiesen haben.

4.1.2. Diagramm

Für die Darstellung der Auswertungs-Tabelle eignet sich u.a. ein gestapeltes Flächendiagramm:

flaechendiagramm.png

Es wurde mit folgenden Schritten mit dem Diagramm-Assistenten (Einfügen → Diagramm…) erzeugt, nachdem der auszuwertende Bereich markiert wurde:

Im Schritt 1, Diagrammtyp, wählen Sie Flächen und die Option Gestapelt.

diagrammtyp.png

Im Schritt 2, Datenbereich, wird der Datenbereich automatisch aus der Selektion übernommen. Wählen Sie die Optionen Datenreihen in Zeilen, Erste Zeile als Beschriftung und Erste Spalte als Beschriftung.

datenbereich.png

Der Schritt 3, Datenreihen, kann in der Regel einfach übersprungen werden. Im Schritt 4, Diagrammelemente, können Sie noch einen Titel für das Diagramm eingeben, Gitternetzlinien ein-/ausschalten und die Position der Legende festlegen.

diagrammelemente.png

Für eine Veranschaulichung der Verteilung der Ausgaben (in einem Monat) kann auch ein Kreisdiagramm (auch „Tortendiagramm“ genannt) geeignet sein. Dazu wird (nach Auswahl eines geeigneten Bereichs) im 1. Schritt des Diagramm-Assistenten der Diagrammtyp Kreis gewählt.4

kreisdiagramm.png

4.2. Vergleich Einnahmen ↔ Ausgaben

Einnahmen und Ausgaben werden ebenfalls mit Hilfe der Funktion SUMMENPRODUKT() pro Monat summiert. Als Selektionskriterium dient die Klassifizierung als E (Einnahmen) bzw. A (Ausgaben)

A

B

C

D

1

Monat

Einnahmen

Ausgaben

Saldo

2

Jan 2007

4'838,00

3'065,26

1'772,74

3

Feb 2007

3'600,00

3'513,13

86,87

4

Mrz 2007

3'600,00

2'841,35

758,65

5

Apr 2007

3'600,00

3'331,04

268,96

Formel in B2:

=SUMMENPRODUKT(Betrag;MONAT(Transaktionsdatum)=MONAT($A3);JAHR(Transaktionsdatum)=JAHR($A3);Art="E")

Formel in C2:

=SUMMENPRODUKT(Betrag;MONAT(Transaktionsdatum)=MONAT($A3);JAHR(Transaktionsdatum)=JAHR($A3);Art="A")

Ob man dabei die Ausgaben negativ anzeigt oder erst in der Saldospalte die Differenz deutlich macht, ist Geschmacksache. Für das nachfolgende Diagramm wurden Einnahmen und Ausgaben als positive Werte zugrunde gelegt; als Diagrammtyp wurde ein Verbunddiagramm aus Linien und Säulen gewählt:

saeulen-und-linien-diagramm.png

Es wurde mit folgenden Schritten mit dem Diagramm-Assistenten (Einfügen → Diagramm…) erzeugt, nachdem der auszuwertende Bereich markiert wurde:

Im Schritt 1, Diagrammtyp, wählen Sie Säulen und Linien und die Option Säulen und Linien.

diagrammtyp-verbunddiagramm.png

Im Schritt 2, Datenbereich, wird der Datenbereich automatisch aus der Selektion übernommen. Wählen Sie die Optionen Datenreihen in Spalten, Erste Zeile als Beschriftung und Erste Spalte als Beschriftung.

datenbereich-verbunddiagramm.png

Der Schritt 3, Datenreihen, kann in der Regel einfach übersprungen werden. Im Schritt 4, Diagrammelemente, können Sie noch einen Titel für das Diagramm eingeben, Gitternetzlinien ein-/ausschalten und die Position der Legende festlegen.

diagrammelemente-verbunddiagramm.png

(!) Jede Änderung wird sofort in das Diagramm im Tabellendokument übernommen; Sie haben damit also immer eine Vorschau auf das endgültige Ergebnis. Die einzelnen Schritte im Diagramm-Assistenten können dabei in beliebiger Reihenfolge abgearbeitet werden (klicken Sie einfach links auf den gewünschten Punkt), sodass Sie z.B. zuerst die richtigen Optionen aus dem Schritt Datenbereich auswählen und dann mit verschiedenen Diagrammtypen (1. Schritt) experimentieren können.

5. Musterdatei zum Herunterladen


KategorieCalc KategorieHowto KategorieChart

  1. Die Zahlen in der Beispieldatei sind willkürlich mit den Funktionen ZUFALLSZAHL() und ZUFALLSBEREICH() generiert worden. Es kann daher sein, dass das Journal etwas wirklichkeitsfremd erscheint. (1)

  2. Wir gehen hier von 5000 als maximal notwendiger Anzahl Zeilen aus. Wenn das bei Ihnen nicht genügt, geben Sie eine höhere Zeilennummer ein. Diese Bereichsdefinition kann aber auch zu einem beliebigen späteren Zeitpunkt geändert werden. Wichtig ist, dass sie für alle Spalten gleich groß ist. (2)

  3. Geht das Journal über mehrere Jahre und die Auswertung soll nicht mit dem ersten Journal-Monat beginnen, so erfasst man einfach das Datum des Monatsersten desjenigen Monats, mit welchem die Auswertung beginnen soll. Eine andere Variante wäre z.B.: =DATUM(JAHR(HEUTE());MONAT(1);TAG(1)) womit die Auswertung mit dem Januar des aktuellen Jahres beginnen würde. (3)

  4. Beachten Sie jedoch bitte, dass es einem Betrachter gewöhnlich leichter fällt, Längen als Flächen zu vergleichen (vgl. Stevenssche_Potenzfunktion), sodass ein Säulen- oder Balkendiagramm für einen exakten Vergleich besser geeignet sein kann. (4)


LizenzBedingungen | AnbieterKennzeichnung | DatenschutzErklärung | Stand: 2013-04-28