2449606

LibreOffice Calc: So nutzen Sie Formeln richtig

29.08.2019 | 08:01 Uhr

Mit smarten Formeln kombinieren Sie beliebige Inhalte aus verschiedenen Zellen und führen auf Wunsch sogar komplexe Abfragen im Handumdrehen durch.


Zellen mit anderen Zellinhalten verknüpfen 

Zellen mit anderen Zellinhalten verknüpfen (1)
Vergrößern Zellen mit anderen Zellinhalten verknüpfen (1)

1. Als Basis für die nachfolgenden Schritte dient ein Adressverzeichnis mit Anrede, Vor- und Nachnamen. Aus diesen Daten wollen Sie gerne einen Anredeblock erstellen, um diesen in Briefe oder E-Mails zu integrieren. 

Zellen mit anderen Zellinhalten verknüpfen (2)
Vergrößern Zellen mit anderen Zellinhalten verknüpfen (2)

2. Markieren Sie zunächst die erste freie Zelle in Zeile 1 (im Beispiel D1) und geben Sie folgende Formel ein =WENN (A1="Herr"; "Sehr geehrter Herr "; "Sehr geehrte Frau ". 

Diese logische Abfrage prüft den Inhalt der Zelle A1 und gibt im Fall von „Herr“ die Zeichenkette „Sehr geehrter Herr “ (mit einem Leerzeichen am Ende) aus. Nach dem Semikolon steht die Zeichenkette, die ausgegeben werden soll, wenn der Inhalt von Zelle A1 nicht „Herr“ ist – also „Sehr geehrte Frau “. Die Syntax im Detail: „WENN (QUELLE=BEDINGUNG; DANN; SONST)“. Im Bild unten wurde die Formel schon auf alle vorhandenen Zeilen übertragen, indem die rechte untere Ecke der Zelle nach unten gezogen wurde. Die Formel in Zelle D1 wurde zur Ansicht per Doppelklick geöffnet. 

Zellen mit anderen Zellinhalten verknüpfen (3)
Vergrößern Zellen mit anderen Zellinhalten verknüpfen (3)

3. In eine logische Formel lässt sich auch Text aus beliebigen anderen Zellen integrieren. Öffnen Sie die Formel in Zelle D1 per Doppelklick und fügen Sie am Ende nach der Klammer folgenden Text hinzu: &C1&"," 

Zellen mit anderen Zellinhalten verknüpfen (4)
Vergrößern Zellen mit anderen Zellinhalten verknüpfen (4)

4. Die „&“-Zeichen fügen den nachstehenden Inhalt zur Ausgabe hinzu – im Beispiel ist dies der Nachname aus der Zelle C1 und ein Komma, das den Anredeblock abschließt. Sie können über weitere „&“-Zeichen beliebige Inhalte aus Ihrer Tabelle hinzufügen.

Teile aus Inhalt einer Zelle extrahieren

Über eine Formel können Sie auch einzelne Buchstaben, Wörter oder sonstige Inhalte aus einer Zelle entnehmen und an anderer Stelle einfügen. 

Soll wie im Beispiel nur der erste Buchstabe des Vornamens aus den Zellen in Spalte B extrahiert und mit einem Punkt versehen werden, nutzen Sie folgende Formel: =TEIL(B2;1;1)&“.“ 

Die Argumente nach dem Zellenverweis stehen für die Position innerhalb der Zelle, ab der die Zeichen entnommen werden sollen (1), und geben an, wie viele Zeichen rechts von dieser Position übertragen werden sollen (ebenfalls 1). Aus „Michael“ wird somit „M.“ und aus „Silke“ wird „S.“ 


Einfache Formeln eingeben

Formeln – auch Funktionen genannt – sind die Essenz einer jeden Tabellenkalkulation. Damit rechnen Sie Werte aus, stellen Vergleiche an und arbeiten wichtige Informationen aus großen Zahlenkolonnen heraus.

Einfache Formeln eingeben (1)
Vergrößern Einfache Formeln eingeben (1)

1. Legen Sie die nachfolgende kleine Testtabelle an, um die Arbeit mit einfachen Formeln sofort auszuprobieren. In Zeile 1/Spalte A geben Sie bitte Wert A ein, in Zeile 1/Spalte B Wert B und in Zeile 1/Spalte C Wert C. 

Einfache Formeln eingeben (2)
Vergrößern Einfache Formeln eingeben (2)

2. In die zweite Zeile tragen Sie daraufhin in der Spalte A unter „Wert A“ die Zahl 10 ein, gefolgt von der Zahl 20 in der Spalte B unterhalb von „Wert B“. 

Einfache Formeln eingeben (3)
Vergrößern Einfache Formeln eingeben (3)

3. Markieren Sie als Nächstes die Zelle C2 unterhalb von „Wert C“ per Mausklick und wählen Sie „Einfügen –› Funktion“ oder alternativ die Tastenkombination Strg-F2. 

Einfache Formeln eingeben (4)
Vergrößern Einfache Formeln eingeben (4)

4. Nun erscheint der Dialog „Funktions-Assistent“ mit einer Fülle von Auswahlmöglichkeiten. Im Beispiel soll die Summe aus den Zellen A2 und B2 in der aktuell markierten Zelle C2 ausgegeben werden. Geben Sie in das Feld „Suchen“ das Wort Summe ein und wählen Sie den Treffer aus der Liste „Funktion“ aus.

Einfache Formeln eingeben (5)
Vergrößern Einfache Formeln eingeben (5)

5. Klicken Sie jetzt im Dialog „Funktions-Assistent“ unten auf die Schaltfläche „Weiter“. Dann gelangen Sie zur Zellenauswahl, die Ihnen zwei verschiedene Herangehensweisen zur Auswahl der Zellen bietet. Am einfachsten ist die Auswahl per Maus: Klicken Sie in Zelle A2, halten Sie die Maustaste gedrückt und ziehen Sie die Auswahl bis zur Zelle B2. Auf diese Weise können Sie auch mehr als zwei Zellen für eine Funktion auswählen.

Einfache Formeln eingeben (6)
Vergrößern Einfache Formeln eingeben (6)

6. Unter „Zahl 1“ wird nun automatisch „A2:B2“ eingetragen. Der Doppelpunkt bedeutet so viel wie „bis“. Im Bereich „Formel“ sehen Sie die ausgeschriebene Funktion: „=SUMME(A2:B2)“. Bestätigen Sie mit „OK“, und die korrekte Summe „30“ erscheint in Zelle C2. 

Einfache Formeln eingeben (7)
Vergrößern Einfache Formeln eingeben (7)

7. Machen Sie die Aktion mit Strg-Z rückgängig und rufen Sie den Funktions-Assistenten mit Strg-F2 erneut auf, um die zweite Eingabevariante zu testen. Wählen Sie erneut die Funktion „SUMME“ aus und klicken Sie in das Feld „Zahl 1“, gefolgt von einem Klick in die Zelle A2. Dann wählen Sie das Feld neben „Zahl 2“ aus und klicken in die Zelle B2. Daraus ergibt sich die Formel „=SUMME(A2;B2)“, wobei der Strichpunkt quasi für „und“ steht. Nach dem Klick auf „OK“ erscheint das korrekte Ergebnis „30“ erneut in Zelle A3. 

8. Statt Zellenverweisen können Sie auch direkt Zahlen in die Eingabefelder tippen. Auf diese Weise hilft Ihnen der Funktions-Assistent bei der Berechnung komplexer Funktionen wie beispielsweise Quadratwurzeln oder Mittelwerten. Die entsprechende Formel erscheint dann mit festen Zahlenwerten in der Übersicht. 

Einfache Formeln eingeben (9)
Vergrößern Einfache Formeln eingeben (9)

9. Für einfache Rechenoperationen wie die Grundrechenarten können Sie auf die Hilfe des Funktions-Assistenten verzichten und die entsprechenden Daten direkt in die Zelle eintragen. Klicken Sie in Zelle C2 und geben Sie =A2+B2 ein, gefolgt von der Enter-Taste. Alternativ geben Sie = ein, klicken auf die Zelle A2, geben + ein und klicken auf Zelle B2. 

Einfache Formeln eingeben (10)
Vergrößern Einfache Formeln eingeben (10)

10. Neben dem Addieren stehen Ihnen auch die anderen Grundrechenarten zur Verfügung: „=A2-B2“ subtrahiert die Zellen voneinander, „=A2/B2“ teilt A2 durch B2 und „=A2*B2“ multipliziert die beiden Werte. 

Einfache Formeln eingeben (11)
Vergrößern Einfache Formeln eingeben (11)

11. Enthält eine Zelle eine Funktion – wie im Beispiel unsere Zel- le C3, wird die Formel in der oberen Inhaltsleiste angezeigt, während in der Zelle selbst lediglich das Ergebnis zu sehen ist. Um eine Formel zu bearbeiten, klicken Sie entweder in die Inhaltsleiste oder doppelt auf die entsprechende Zelle. Praktisch: Die für die Berechnung herangezogenen Zellen werden farblich markiert. Auf diese Weise können Sie alle an einer Formel beteiligten Zellen auf einen Blick erkennen. 


Mit Formeln rechnen 

Mithilfe von Libre Office Calc und Formeln können Sie langwierige Berechnungen aus großen Datenbeständen automatisieren und damit viel Zeit sparen. Wie das geht, lernen Sie am Beispiel für eine Tabelle, die den monatlichen und jährlichen Stromverbrauch berechnet. 

Mit Formeln rechnen (1)
Vergrößern Mit Formeln rechnen (1)

1. Tragen Sie zunächst einmal in die Zellen der ersten Zeile die Rubriken Datum, Zählerstand, Verbrauch (kWh), Jahresverbrauch sowie Verbrauch pro Tag ein.

Mit Formeln rechnen (2)
Vergrößern Mit Formeln rechnen (2)

2. In die Zelle A2 direkt unter „Datum“ tragen Sie daraufhin das Startdatum ein – im Beispiel 01.01.19. Rechts daneben in Zelle B2 geben Sie bitte den Zählerstand ein. 

Mit Formeln rechnen (3)
Vergrößern Mit Formeln rechnen (3)

3. Die Zählerablesung soll monatlich erfolgen. Also markieren Sie die Zellen A2 bis A14 mit gedrückt gehaltener Maustaste gemeinsam und wählen daraufhin „Tabelle –› Zellen ausfüllen –› Reihen“. Hier sollte bereits „Richtung: Unten“, „Reihentyp: Datum“ und „Zeiteinheit: Monat“ voreingestellt sein. Ein Klick auf „OK“ füllt nun die Spalte A mit aufeinanderfolgenden Monaten. 

Mit Formeln rechnen (4)
Vergrößern Mit Formeln rechnen (4)

4. Als Nächstes tragen Sie die Zählerstände vom 1. Februar 2019 bis zum 1. Januar 2020 ein. In der Praxis würden Sie diese Eingaben natürlich nicht auf einmal, sondern Monat für Monat vornehmen. Für das Beispiel sollten Sie beachten, dass der Stromverbrauch zum August hin immer niedriger wird und dann wieder ansteigt. 

Mit Formeln rechnen (5)
Vergrößern Mit Formeln rechnen (5)

5. Im Folgenden geht es an das Eintragen der Berechnungsformeln. In Zeile 2 (Datum: 01.01.19) wird bis auf den Ausgangszählerstand nichts weiter eingetragen. Die erste Berechnung findet in der Februar-Zeile in Zelle C3 unter „Verbrauch (kWh)“ statt. Tippen Sie =B3-B2 ein. Damit wird der Ausgangszählerstand in B2 vom aktuellen Zählerstand in B3 abgezogen (10.400-10.000). In der Zelle B3 erscheint im Anschluss daran der korrekte Monatsverbrauch für den Januar in Höhe von 400 kWh. 

Mit Formeln rechnen (6)
Vergrößern Mit Formeln rechnen (6)

6. In der Verbrauchszelle für den März (C4) müsste nun der Zählerstand vom 1. Februar (in Zelle B3) vom Zählerstand am 1. März (in Zelle B4) abgezogen werden, sodass die korrekte Formel für Zelle C4 =B4-B3 lautet. Sie können sich die Arbeit des manuellen Ausfüllens allerdings sparen, indem Sie auf Zelle C3 klicken, die Maustaste gedrückt halten und die Markierung bis Zelle C14 herunterziehen. Wählen Sie sodann im Menü „Tabelle“ die Funktion „Zellen ausfüllen –› Unten“. 

Mit Formeln rechnen (7)
Vergrößern Mit Formeln rechnen (7)

7. Die Funktion „Zellen ausfüllen“ geht intelligent vor: Für jede nachfolgende Zeile werden die Formeln automatisch angepasst, sodass immer der Wert der darüberliegenden Zeile vom Wert in der aktuellen Zeile abgezogen wird. Auf diese Weise erhalten Sie alle korrekten Monatsverbräuche mit nur einem Mausklick. 

Mit Formeln rechnen (8)
Vergrößern Mit Formeln rechnen (8)

8. Nun markieren Sie bitte die Zelle D3, um die erste Formel für den Jahresverbrauch einzugeben: =B3-10000. Sie verwenden hier den ersten Zählerstand als fixe Zahl anstatt als Zellenverweis, um bei dem nachfolgenden automatischen Ausfüllen sicherzustellen, dass immer der Ausgangswert verwendet und nicht dynamisch angepasst wird. Markieren Sie anschließend die Zelle D3 mit der Formel, ziehen Sie wie in Schritt 6 beschrieben die Markierung mit der Maus bis zur Zelle D14 und nutzen Sie diesmal die Tastenkombination Strg-D für das Aktivieren der Funktion „Zellen ausfüllen –› Unten“. In der Folge erscheint der kumulierte Jahresverbrauch in jeder Monatszeile der Beispieltabelle. 

Mit Formeln rechnen (9)
Vergrößern Mit Formeln rechnen (9)

9. Den „Verbrauch pro Tag“ in Spalte E ermitteln Sie durch das Teilen des Monatsverbrauchs in Spalte C mit der Anzahl der Tage des Monats. So tragen Sie in Zelle E3 die Formel =C3/31 ein, da der Januar 31 Tage hat. In Zelle F4 kommt =C4/28 für die 28 Tage des Februars, gefolgt von =C5/31 in F5 für den März und so weiter.

Mit Formeln rechnen (10)
Vergrößern Mit Formeln rechnen (10)

10. Unterhalb der Tabelle fügen Sie einen Auswertungsblock ein. Dieser beginnt mit dem Jahresverbrauch in Zelle C16, welcher mit der Formel =SUMME(C3:C14) aus den monatlichen Verbrauchswerten addiert wird. 

Mit Formeln rechnen (11)
Vergrößern Mit Formeln rechnen (11)

11. Die gesamten Stromkosten in Zelle C17 werden mit der Formel =C16*0,3 ermittelt, wobei wir einen Strompreis von 30 Cent („0,3“) pro Kilowattstunde herangezogen haben. Die durchschnittlichen Stromkosten pro Monat berechnen Sie durch das Teilen der Jahreskosten in Zelle C17 durch 12, was mit der Formel =C17/12 in Zelle C18 erledigt wird.

Mit Formeln rechnen (12)
Vergrößern Mit Formeln rechnen (12)

12. Mit diesen Daten können Sie bereits im Vorfeld berechnen, in welcher Höhe die Nach- oder die Rückzahlung in der Jahresabrechnung ausfallen wird. Dazu tragen Sie in Zelle C19 Ihre monatliche Abschlagszahlung ein und multiplizieren diese dann in Zelle D19 mit 12. In Zelle D20 ziehen Sie die tatsächlichen jährlichen Kosten aus Zelle C17 von den summierten Abschlagszahlungen in Zelle D19 ab. Hierfür tragen Sie die Formel =D19-C17 ein. 


Diagramme einfügen 

Ein Diagramm verwandelt komplexe und damit schwer erfassbare Zahlenkolonnen in eine übersichtliche Grafik, in der sich Werte und Trends auf einen Blick erkennen lassen. Basierend auf der Stromabrechnung aus dem vorangegangenen Workshop visualisieren Sie nun die enthaltenen Daten,  indem Sie ein Diagramm einfügen.

Diagramme einfügen (1)
Vergrößern Diagramme einfügen (1)

1. Klicken Sie in der Tabelle zunächst in die Zelle A1 („Datum“), halten Sie die Maustaste gedrückt und ziehen Sie die Markierung nach unten bis zur Zelle A14 („01.01.2020“). Dann halten Sie zusätzlich die Strg-Taste gedrückt und fügen mittels Anklicken die Zellen C1 („Verbrauch“) bis C14 der Markierung hinzu. 

Diagramme einfügen (2)
Vergrößern Diagramme einfügen (2)

2. Holen Sie als Nächstes mit dem Befehl „Einfügen –› Diagramm“ den „Diagramm-Assistent“ auf den Bildschirm. 

Diagramme einfügen (3)
Vergrößern Diagramme einfügen (3)

3. Wählen Sie unter „Schritt 1“ den Diagrammtyp aus – in unserem Beispiel verwenden Sie die Vorlage „Säulendiagramm“. Bestätigen Sie danach mit „Weiter“. 

Diagramme einfügen (4)
Vergrößern Diagramme einfügen (4)

4. In „Schritt 2“ legen Sie den Datenbereich fest. Aufgrund der zuvor getätigten Auswahl (siehe Schritt 1) werden die Datenbereiche A1 bis A14 sowie C1 bis C14 als Datenquellen herangezogen. 

Diagramme einfügen (5)
Vergrößern Diagramme einfügen (5)

5. Setzen Sie die Häkchen neben „Erste Zeile als Beschriftung“ und „Erste Spalte als Beschriftung“. Jetzt werden die Überschriften „Datum“ und „Verbrauch“ sowie die einzelnen Datumseinträge als Beschriftung der Diagrammachsen eingefügt. 

Diagramme einfügen (6)
Vergrößern Diagramme einfügen (6)

6. In Schritt 3 können Sie unter mehreren Datenreihen wählen. Da unsere Tabelle mit den Verbrauchswerten jedoch nur eine Datenreihe enthält, klicken Sie einfach auf „Weiter. In Schritt 5 geben Sie nun unter „Titel“ den Diagrammtitel Stromverbrauch 2019 ein. Dieser wird groß über dem Diagramm angezeigt. Alternativ können Sie auch alle Achsen individuell beschriften. Im Dialog rechts aktivieren Sie die „Legende“ durch Setzen eines Häkchens. Damit sie platzsparend unter dem Diagramm angezeigt wird, klicken Sie auf die Option „Unten“. Mit einem Klick auf die Schaltfläche „Fertigstellen“ beenden Sie den Diagramm-Assistenten. 

Diagramme einfügen (7)
Vergrößern Diagramme einfügen (7)

7. Klicken Sie in einen Balken, sodass die gesamte Datenreihe markiert wird. Sie erkennen dies an kleinen Quadraten, die innerhalb aller Balken erscheinen. Öffnen Sie per Rechtsklick das Kontextmenü und wählen Sie „Datenreihe formatieren“. Im Register „Optionen“ verringern Sie den Wert „Abstand“ auf „50%“, damit die Balken breiter werden und näher zusammenrücken.

Diagramme einfügen (8)
Vergrößern Diagramme einfügen (8)

8. Wechseln Sie zum Register „Fläche“, um die Farbe der Balken anzupassen. Im Dialog ganz oben haben Sie die Wahl zwischen verschiedenen Füllvarianten wie „Farbe“, „Bild“ oder „Muster“. Für das Beispiel wählen Sie die Variante „Farbverlauf“. 

Diagramme einfügen (9)
Vergrößern Diagramme einfügen (9)

9. Die Verlaufsvariante „Sonnenschein“ aus dem Bereich „Farbverlauf“ eignet sich mit ihren Orange- und Gelbtönen sehr gut zur Illustration des jährlichen Stromverbrauchs. Mithilfe von „OK“ bestätigen Sie die Änderung. 

Diagramme einfügen (10)
Vergrößern Diagramme einfügen (10)

10. Um den gelb-orangen Balken maximalen Kontrast zu verleihen, färben Sie den Diagrammhintergrund in der Komplementärfarbe Blau ein. Hierzu klicken Sie rechts in die weiße Diagrammfläche, wählen „Diagrammfläche formatieren“, wechseln dann zum Register „Fläche“, klicken auf „Farbe“ und wählen ein kräftiges Blau. 

Diagramme einfügen (11)
Vergrößern Diagramme einfügen (11)

11. Die schwarze Schrift hebt sich schlecht vom blauen Hintergrund ab. Deshalb klicken Sie mit der rechten Maustaste in die einzelnen Schriftelemente, wählen „Formatieren“ und stellen unter „Schrifteffekte“ die „Schriftfarbe“ Weiß ein. Das fertige Diagramm positionieren Sie wahlweise neben oder unterhalb der Tabelle.

Die Themen in Tech-up Weekly #163:

► Bericht: Microsoft-Dienstleister belauschen intime Skype-Gespräche: www.pcwelt.de/news/Bericht-Microsoft-Dienstleister-belauschen-intime-Skype-Gespraeche-10643395.html

► Tausende Amazon-Mitarbeiter hören Alexa-Mitschnitte ab: www.pcwelt.de/news/Tausende-Amazon-Mitarbeiter-hoeren-Alexa-Mitschnitte-ab-10573982.html

► Google Assistant: Mitarbeiter belauschen Gespräche: www.pcwelt.de/news/Google-Assistant-Mitarbeiter-belauschen-Gespraeche-10627132.html

► Apple will Siri-Aufnahmen nur noch mit Einwilligung auswerten: www.macwelt.de/news/Apple-will-Siri-Aufnahmen-nur-noch-mit-Einwilligung-auszuwerten-10640695.html


► Gamescom-Tickets gewinnen: www.pcwelt.de/news/Preise-fuer-8000-Euro-abgreifen-Die-HMX-2-Team-Hoelle-auf-der-Gamescom-2019-10636572.html (unten auf der Seite)

► Intel: Neue 56-Kern-CPU im ersten Halbjahr 2020: www.pcwelt.de/news/Intel-Neue-56-Kern-CPU-im-ersten-Halbjahr-2020-10642604.html

Quick-News:

► Facebook benennt WhatsApp und Instagram um:
www.pcwelt.de/news/Facebook-benennt-Whatsapp-und-Instagram-um-10641012.html

► Roblox hat jetzt mehr Spieler als Minecraft: www.pcwelt.de/news/Roblox-hat-jetzt-mehr-Spieler-als-Minecraft-10641294.html

► Frech: Google will mit EU-Entscheidung Geld verdienen: www.pcwelt.de/news/Frech-Google-will-mit-EU-Entscheidung-Geld-verdienen-10641021.html

► Microsoft stopft heimlich neue Spectre-Lücke in CPUs: www.pcwelt.de/news/Microsoft-stopft-heimlich-neue-Spectre-Luecke-in-CPUs-10642457.html

► Xiaomi plant Smartphone mit 108 Megapixel Kamera: www.pcwelt.de/news/Xiaomi-plant-Smartphone-mit-108-Megapixel-Kamera-10642839.html

► Galaxy Book S: Windows 10 Laptop mit 23 Stunden Akku-Laufzeit: www.pcwelt.de/news/Galaxy-Book-S-Windows-10-Laptop-mit-23-Stunden-Akku-10643356.html

Kommentar der Woche:

► Unser Hands-on zum Galaxy Note 10/10+
www.youtube.com/watch?v=0MUcrI8YSyU

► Dennis & Simon im Sparmag-Video: www.youtube.com/watch?v=1_5atf_0vQY&t=8m0s

Fail der Woche:

► Speedtest-Ranking: Deutschland nur auf Platz 33: www.pcwelt.de/news/Speedtest-Ranking-Deutschland-nur-auf-Platz-33-10642127.html

► Zum PC-WELT T-Shirt-Shop:
www.pcwelt.de/fan

PC-WELT Marktplatz

2449606