LibreOffice – Calc ist ein Teil der „Free Office Suite“ (siehe WebSeite der  „Document  Foundion„).

Speziell der Teil „Calc“ hat für mich sehr viel „zu bieten“. Einige Funktionen sind nicht so ganz „offensichtlich“.

Ich möchte hier am Beispiel der Addition zeigen, welche versteckten Funktionen in LibreOffice – Calc man dabei nutzen kann.

Beispiels-Tabelle

Stellen wir uns eine kleine Tabelle mit Haushalts-Ausgaben vor:

5,40	ÖV
81,95	Drogerie
19,50	Lebensmittel
5,40	ÖV
24,45	Drogerie
30,00	Telefon
5,40	ÖV
27,20	Lebensmittel
250,00	Dienstreise England
250,00	Dienstreise
75,00	Dienstreise Italien
75,00	Dienstreise
5,40	ÖV
6,80	Bahnticket
32,70	FastFood
307,70	Bekleidung
30,50	Spezialitäten
29,60	FastFood
18,80 €	Geschenke
27,90 €	FastFood
15,20 €	Dienstreise

Einfache Addition/Subtraktion

Die Additions- bzw. Subtraktions-Funktion hat an sich „keine grossen Geheimnisse“ zu bieten:

Man

• klickt in ein leeres Feld an, in dem das „Rechen-Ergebnis“ erscheinen soll (z.B. unter den Beträgen..)
• tippt „=Summe(“ ein
• und wählt
  • entweder mit der Maus die Datenfelder, (anklicken-ziehen-bis..) und abschliessen mit „Return-Taste“
  • oder gibt den Bereich mit „Koordinaten“ (z.B. F15:F33 oder G18:J18, oder ..)
• den Bereich der Felder, die verrechnet werden sollen.

Das macht Sinn, wenn es sich um gleichartige Zahlen handelt. Z.B. man will die Summe aller Ausgaben ermitteln.

Bedingte Addition/Subtraktion

Möchte man aus der Tabelle jetzt jedoch „alle Ausgaben für Lebensmittel“, „alle Kfz-Ausgaben“, „alle Ausgaben für Bekleidung“ herausrechnen, geht das nicht ganz so einfach.

Man kann mit der oben beschriebenen Methode die passenden Felder anwählen und „hardcoded“ addieren. ok, umständlich:

=Summe(F3,F6,F8,F15)

Mit der Funktion „Bedingte Summation“ geht es in LibreOffice – Calc flexibler:

=SummeWenn(<Bereich-mit-Beschreibung> ; „<zu-suchender-Text>“ ; <Bereich-zu-addieren>)

Feste Suchtexte in LibreOffice – Calc

als Beispiel dafür:

=SUMMEWENN(F29:F49;“Lebensmittel“;E29:E49)

Jetzt werden

• die Beträge in den Feldern E29 bis E49 addiert,
• WENN die Beschreibung im jeweiligen Feld (aus dem Bereich E29 bis E49)
• mit dem zu suchenden Text („Lebensmittel“) überein stimmt.

Das kann man mit anderen Ergebnisfelden beliebig wiederholen. Somit entsteht ein „Summen-Bereich“ mit „alle allg. ÖVs“, alle Bahntickets, alle „Lebensmittel“ usw.

Oder-Suchtexte

Das ganze funktioniert auch, wenn man z.B. die Summe der Ausgaben von „ÖV“ und „Bahnticket“ errechnen möchte. Im Klartext heisst das, die Beschreibung enthält „entweder ÖV oder Bahnticket“:

Dann wäre die Funktion

=SUMMEWENN(F29:F49;“ÖV|Bahnticket“;E29:E49)

Jetzt wird wie oben der Beschreibungsbereich abgesucht. „Treffer“ wäre jedoch ein Feld das „ÖV“ enhtält oder ein Feld, das „Bahnticket“ enthält.

Wildcard-Suchtexte in LibreOffice – Calc

Möchte man jetzt aus meiner kleinen Tabelle die Kosten aller Dienstreisen (nach England UND nach Italien) ermitteln, kann man mit „Platzhaltern“ (Punkt-Stern – zu suchender Text – Stern-Punkt) suchen:

=SUMMEWENN(F29:F49;“.*Dienstreise.*„;E29:E49)

Jetzt werden die Ausgaben berücksichtigt, wenn in der Beschreibung „irgendetwas-mit-Dienstreise“ steht.

Wer mehr über diese Platzhalter, Verfeinerung, andere Möglichketen, ..  erfahren möchte, kann unter Regular Expressions oder Reguläre Ausdrücke nachsehen . Eine Liste dazu ist hier zu finden. Man kann sehr viel damit machen, darauf „hier“ einzugehen, würde jedoch zu weit führen.

Suche mit Referenzen/Verlinkung

Etwas „unschön“ ist jedoch, dass die Zahlen „nackt“ in der Aufstellung stehen. D.h. ohne eine Beschreibung.

Eine Erläuterung (Lebensmittel, ÖV, ..) muss man noch manuell dazu in die Tabelle schreiben. Bei Korrekturen der Formel schleichen sich da schnell Fehler bei der Beschreibung ein (oder umgekehrt..)

Einfache wäre es, wenn der Suchtext gleichzeitig die Beschreibung für die Zahl wäre:

Meine Tabelle wird „so“ dargestellt, links die Beschreibung, rechts die Summe der Ausgaben:

Bekleidung|Schuhe

256,42 €

Das Feld, das den Betrag (hier 256,42 €) anzeigt, enthält als Formel

=SUMMEWENN(F29:F49;H33;E29:E49)

Die „Koordinate“ H33 zeigt auf das Feld davor, das den Text

Bekleidung|Schuhe

enthält.

„Übersetzt“ bedeutet die Formel:

• suche im Bereich von F29 bis F49
• nach dem Text, der in Feld H33 abgelegt ist
  • (dort steht ja „Bekleidung|Schuhe“, s.o.)
• wenn es passt, addiere den Inhalt des jeweiligen Feldes aus dem Bereich E29 bis E49

Korrektur, Abgleich, Fehlersuche

Die Referenzen, Bereiche die in Formeln genutzt werden, etc. kann ich mir in Calc „optisch“ anzeigen lassen. (Und ggf. auch mit der Maus „anpacken“ und korrigieren)

Wenn ich in der „Kopf-Zeile“ in die Formel hinein klicke, wird der Bereich (oder mehere Bereiche farblich getrennt) in der Tabelle angezeigt:

Felder oder Formeln kopieren

Felder kopieren

Wenn man ein FELD mit einer Formel anklickt, kopiert und an anderer Stelle wieder einfügt, werden die Koordinaten entsprechend angepasst.

Bespiel

Wenn man dieses Summations-Feld kopiert und z.B.

 

um 3 Felder nach rechts

um 4 Felder nach unten

einfügt, ändert sich die Formel

• von =SUMME(F119:F153)
• in =SUMME(I123:I157)

Spalte „F“ + 3 → wird zu „I“

Zeile „119“ + 4 → 123

d.h. die Formel addiert jetzt einen anderen Bereich (der ggf. „leer“ ist, Hardcopy)

Formel kopieren

Wenn man ein solches Feld anklickt, jedoch den „Inhalt des Feldes“ (in der Text-Zeile ganz oben in Calc, s. folgende Hardcopy) kopiert …

… und in einem anderen Feld wieder einfügt, bleiben die Koordinaten erhalten. Man erhält die selbe Summe, jedoch in einem anderen Feld angezeigt.

Probleme mit importierten Daten

Wenn man Daten impoertiert (z.B. *.csv-Dateien) kann es passieren, dass man mit Datumsfeldern ein Problem bekommt. Z.B. wird oft ein „Datum“ als Text und nicht als „eigentliches Datum“ importiert.

Folge: Sortieren nach dieser Spalte geht schief.

Eine Umformatierung „Format“ -> „Zelle“ -> „Datum“ bringt nichts. Die Ursache ist ein kleines Hochkomma (‚) am Anfang des Datumsfeldes, das die Umformatierung blockt und stur bei „Text“ bleibt.

In einem Formum von LibreOffice habe ich den Tipp gefunden, alle Datumszellen mit Hilfe einer Regular Expression (s.o.) umzuwandeln. Dort wird beschrieben dass man  „… bei Suche nach „^.*$“ (ohne Anführungszeichen) und bei Ersetzen durch „&“ (kaufmännischen und (Umschalt + 6)  ..“ benutzen soll. – hat bei mir geklappt 😉