Lange Kolonnen von Euro-Beträgen wollen summiert werden, eine Liste mit Prüfungsnoten wird aussagekräftiger, wenn eine Durchschnittsnote und die Verteilung der Noten sichtbar werden. Beides ist mit Calc kein Problem.

In der letzten Ausgabe haben wir die Grundfunktionen der Tabellenkalkulation OpenOffice Calc vorgestellt [1] – da ging es ums Ausfüllen der Tabellenzellen, kleine Formatierungen und ganz einfache Formeln, mit denen Sie z. B. die Summe zweier Zellen berechnen können. Was für OpenOffice [2] gilt, können Sie übrigens auch direkt im neuen LibreOffice [3] nutzen: Das neue Office-Paket, das u. a. die neue OpenSuse-Version 11.4 ausliefert, hat nur einen neuen Namen (und eine andere Struktur, was Verwaltung und Entwicklung angeht), die Features sind aber die gleichen geblieben.

In diesem Artikel werfen wir einen Blick auf Funktionen, mit denen Sie komplexere Berechnungen erledigen können. Wir fangen mit den einfachsten Funktionen zum Addieren und Mittelwertbilden an und arbeiten uns dann zu den anspruchsvolleren Aufgaben vor.

Summe und Durchschnitt

Für die folgenden Beispiele gehen wir davon aus, dass Sie ein Tabellendokument in Calc erstellt und darin die erste Spalte A mit Werten gefüllt haben.

Setzen Sie nun mit der Maus den Cursor in das leere Feld unter all den Werten und geben Sie dort zunächst =SUMME( (mit einem Gleichheitszeichen am Anfang und einer geöffneten Klammer am Ende) ein. Dann klicken Sie mit der Maus in die erste Zelle mit Inhalt, halten die Maustaste gedrückt und ziehen die Maus in die letzte Inhaltszelle. Jetzt ist der ganze Wertebereich markiert. Lassen Sie die Maus los, landet in der aktuellen Zelle hinter =SUMME( ein Ausdruck der Form A1:A20 – geben Sie noch eine schließende Klammer ein, so dass in der Zelle nun =SUMME(A1:A20) steht. Drücken Sie die Eingabetaste, verschwindet diese Formel, und stattdessen erscheint dort die Summe der vorher ausgewählten Werte.

Das geht sogar noch einfacher, denn Calc ist auf das Berechnen von Summen als häufigste Anwendung vorbereitet: Löschen Sie die Summenzelle wieder, indem Sie dort hinein klicken und [Rückschritt] drücken. Platzieren Sie den Cursor erneut in dieser Zelle und klicken Sie in der Symbolleiste auf das Icon mit dem griechischen Sigma – das ist in der Mathematik das Standardsymbol für Summenberechnungen. Das Ergebnis ist dasselbe: In der Zelle unter der Wertespalte erscheint die Summe.

Der Trick mit dem Summen-Icon funktioniert allerdings nicht, wenn sich eine leere Zelle im aufzuaddierenden Bereich befindet; dann geht die Summe nur bis zur letzten Zelle vor diesem leeren Feld. Wollen Sie über einen derart unterbrochenen Bereich summieren, können Sie trotzdem das Summensymbol verwenden: Markieren Sie dann zunächst mit der Maus den ganzen Bereich und klicken Sie dann auf das Icon – dann erscheint wieder unter den Werten die Summe.

Außer der Summe ist oft auch der Mittelwert interessant. Um diesen für eine ganze Spalte zu berechnen, gehen Sie wie oben für die Summe beschrieben vor, verwenden aber als Berechnungsfunktion nicht SUMME(), sondern MITTELWERT(). Beachten Sie hier, dass diese Funktion zwischen leeren Zellen und solchen, in denen der Wert 0 steht, unterscheidet: Der Mittelwert von 2, 0 und 4 ist (2+0+4)/3 = 2, während der Mittelwert von 2 und 4 und einer leeren Zelle als (2+4)/2 = 3 berechnet wird (Abbildung 1).

Abbildung 1: Mittelwerte berücksichtigen leere Zellen nicht. Es ist also nicht egal, ob eine Zelle leer ist oder den Wert 0 enthält.

Wollen Sie bei der Durchschnittsbildung leere Zellen als 0 interpretieren, berechnen Sie stattdessen die Summe und Teilen durch die Anzahl der Zellen; die passende Formel für das Beispiel wäre dann =SUMME(C2:C4) / 3.

Achten Sie bei der Formeleingabe immer darauf, dass am Anfang ein Gleichheitszeichen steht – wenn Sie dieses Zeichen weglassen, interpretiert Calc den eingebenen Text nicht als Formel, sondern zeigt ihn als normalen Text an.

Klausurnoten

Wir wollen nun ein komplexeres Beispiel betrachten. Das Szenario dafür ist eine Klausur, die zehn Schüler oder Studenten geschrieben haben; es gab vier Aufgaben, und die Tabelle soll die erreichten Punkte aller Klausurteilnehmer in allen Aufgaben, die Gesamtpunkte absolut und in Prozent sowie die vergebenen Noten enthalten. Um die Berechnung übersichtlich zu halten, werden nur die einfachen Schulnoten von 1 (sehr gut) bis 5 (mangelhaft) vergeben; es gibt keine Zwischennoten (1,3 etc.) und keine 6.

Der Bewertungsschlüssel soll für dieses Beispiel so gestaltet sein, dass der Prüfling ab 90% der erreichten Punkte eine 1 erhält, ab 80% eine 2, ab 65% eine 3 und ab 50% eine 4, bei weniger als 50% eine 5.

Das Ergebnis, auf das wir zusteuern, ist die in Abbildung 2 dargestellte Notentabelle.

Abbildung 2: Das ist das Ziel: Die Tabelle berechnet aus den eingegebenen Punkten in den Teilaufgaben automatisch die Gesamtpunkte und die Note.

In einem ersten Schritt geben Sie die Namen der Teilnehmer und die erreichten Punkte in den Aufgaben ein – also alles, was in den Zellen zwischen A1 und E13 steht. Tragen Sie in Zeile 15 außerdem die maximal erreichbaren Punkte für jede Aufgabe ein.

Anschließend berechnen Sie für jeden Prüfling die Gesamtpunkte. Die Summenformel erzeugen Sie dabei nur einmal für den ersten Schüler (im Beispiel Anton); klicken Sie in Zelle F4 und dann auf das Summen-Icon in der Symbolleiste: Calc schlägt dann automatisch die korrekte Formel =SUMME(B4:E4) vor. Um diese Formel automatisch für alle anderen Teilnehmer zu übernehmen, klicken Sie in die Zelle mit der Summe. Die schwarze Umrandung (welche die ausgewählte Zelle hervorhebt) hat am unteren rechten Rand ein kleines schwarzes Quadrat. Fassen Sie es mit der Maus an und ziehen Sie dann mit gedrückter Maustaste den Mauszeiger bis in die letzte Summenzelle (F13). Sobald Sie die Maustaste loslassen, erscheinen automatisch die korrekten Summen: Calc vermutet ganz richtig beim Kopieren einer Formel, dass Sie nicht in jeder Zelle denselben Wert (die Summe aus Zeile 4) sehen möchten, sondern die jeweiligen Zeileninhalte aufsummieren wollen. Die Summe der Maximalpunkte (F15) erstellen Sie auf gewohntem Weg (Klick in die Zelle und auf das Summen-Icon).

Im Beispiel haben wir auch halbe Punkte vergeben, dadurch wirken die Punktlisten etwas unordentlich, weil z. B. “8” und “8,5” nicht einheitlich ausgerichtet sind. Das Problem ist schnell behoben, indem alle Zahlen einheitlich mit einer Nachkommastelle (also “8” als “8,0”) erscheinen. Markieren Sie dazu die Zellen mit Punktwerten (B4–F15) und klicken Sie mit rechts in eine der markierten Zellen. Aus dem Kontextmenü wählen Sie dann Zellen formatieren. Auf dem Reiter Zahlen wählen Sie unter Kategorie zunächst die Zahl und dann rechts daneben die Variante -1234,1 – das steht für Zahlen mit einer Nachkommastelle. Unten links im Feld Format-Code erscheint dann der Eintrag 0,0. Möchten Sie eine nicht vorgesehene Variante (wie etwa drei Nachkommastellen) erreichen, geben Sie dort direkt z. B. 0,000 ein.

Weiter geht es mit der Umrechung in Prozentwerte: Um eine Gesamtpunktzahl in Prozente umzurechnen, müssen Sie diesen Wert durch die maximal erreichbaren Punkte teilen – die stehen in der Zelle F15. Für den ersten Eintrag (von Anton) gehört darum in Zelle G4 die Formel =F4/F15 – das würde funktionieren, die übrigen Einträge in der Spalte müssten dann =F5/F15, =F6/F15 usw. lauten. Wenn Sie so vorgehen, funktioniert aber das automatische Kopieren der Formel nicht; Calc würde dann in weiter unten liegen Zellen auch F15 anpassen und nach unten schieben, also durch F16, F17 usw. teilen, was falsch ist. Sie können in der Formel aber angeben, dass F15 eine feste Adresse ist, die sich nicht verschieben soll: Dazu schreiben Sie $F$15 statt F15, setzen also jeweils ein Dollar-Zeichen vor die Zeilen- und Spaltenangaben.

Haben Sie in Zelle G4 die nun gefundene Formel =F4/$F$15 eingetragen, können Sie diese Zelle mit dem weiter oben beschriebenen Trick automatisch in die Zellen G5 bis G13 kopieren. Allerdings sehen Sie noch keine Prozentwerte, sondern Dezimalzahlen, die kleiner als 1 sind (etwa: 0,921). Das liegt daran, dass die Zellen noch als normale Zahlen formatiert sind. Markieren Sie darum die Zellen G4–G13, rufen Sie über das Kontextmenü wieder Zellen formatieren auf und wählen Sie diesmal unter Kategorie den Eintrag Prozent. Varianten ganz ohne und mit zwei Nachkommastellen gibt es bereits; wählen Sie z. B. die Variante -12,95% aus, erscheint unten der Format-Code 0,00%. Wenn Sie daraus noch eine der beiden Nullen hinter dem Komma entfernen, erreichen Sie die Darstellung aus Abbildung 2 mit nur einer Nachkommastelle (Abbildung 3).

Abbildung 3: Prozente …

Von Prozenten zu Noten

Jetzt fehlt nur noch ein Schritt: die Bestimmung der Noten aus den Prozentwerten. Prinzipiell könnten Sie versuchen, eine Funktion zu bestimmen, die Prozentwerte direkt in Noten umwandelt. Ein mathematischer Ausdruck der Form (100-Prozent)/10 geht in die richtige Richtung, z. B. ergibt sich für 90%: (100-90)/10 = 10/10 = 1 und für 50%: (100-50)/10 = 50/10 = 5; mit etwas Finetuning und der Funktion Calc-RUNDEN(), die Zahlen auf ganze Zahlen auf- und abrundet, könnten Sie so eine “lineare” Notenberechnung erreichen. Damit wäre es aber nicht möglich, die Grenzwerte, ab denen Sie eine bestimmte Note vergeben, individuell anzupassen.

Wir gehen darum für dieses Beispiel einen anderen Weg und legen zunächst eine Bewertungsskale an, die Abbildung 2 in den Zellen K4–K7 zeigt: Dort stehen die Prozentwerte, ab denen die nebenstehende Note vergeben werden soll. Möchten Sie auch Zwischennoten (1, 1,3, 1,7, 2, 2,3, …) vergeben, gestalten Sie diese Auflistung ausführlicher.

In die Zellen K4–K7 tragen Sie die für die Schulnoten 1 bis 4 nötigen Prozentzahlen ein; wir haben im Beispiel in die Spalte J auch die Noten eingefügt, das dient aber nur der Übersichtlichkeit.

Zur automatischen Notenbestimmung ist nun eine Fallunterscheidung nötig: Spalte H soll die Noten aufnehmen, und die Berechnungsvorschrift wäre in normaler Sprache: “Wenn in Spalte G ein Prozentwert von mindestens 90% steht, dann trage in H eine 1 ein; stehen dort mindestens 80%, dann trage eine 2 ein, usw.”

Nehmen wir zunächst an, dass es nur die beiden Noten 1 und 2 gibt, dann können wir die Aufgabe mit der WENN()-Funktion von Calc lösen: Diese erwartet als erstes Argument eine Bedingung und als zweites und drittes Argument je einen Wert. Ist die Bedingung erfüllt (wahr), dann trägt Calc das zweite Argument ein, ansonsten das dritte.

Für die erste Notenzeile (Zeile 4) sieht die passende Formel so aus:

=WENN(G4 >= $K$4; 1; 2)

Beachten Sie, dass wir hier wieder die Zelle K4 (in der die 90% stehen) absolut adressieren, indem jeweils ein Dollar-Zeichen vor dem K und vor der 4 steht: Die Adressangabe soll sich nicht ändern, wenn wir die Formel später in die übrigen Zellen in Spalte H kopieren. Der Bezug auf Zelle G4 ist hingegen “normal” (ohne Dollar-Zeichen): Beim Kopieren in die darunter liegenden Zellen soll daraus später G5, G6, G7 usw. werden.

Die Bedingung G4 >= $K$4 vergleicht den Inhalt der Zelle G4 mit dem von K4 – in G4 steht der Prozentwert für Teilnehmer Anton (92,1%), in K4 steht die untere Schranke für die Note 1 (90%). Da 92,1% größer als 90% ist, ist diese Bedingung erfüllt: >= ist in Calc-Notation der richtige Code für “größer oder gleich”.

Um im nächsten Schritt die Note 3 in die Formel aufzunehmen, ist eine so genannte “geschachtelte Fallunterscheidung” nötig: Der Anfang bleibt gleich (“wenn mindestens 90%, dann 1”), der alternative Fall (“weniger als 90%”) wird etwas komplexer. Abbildung 4 zeigt den “Entscheidungsbaum”, der sich ergibt, wenn drei Noten möglich sind.

Abbildung 4: Der “Entscheidungsbaum” für die Noten 1, 2 und 3 besteht aus zwei geschachtelten Fallunterscheidungen.

Die geschachtelte WENN()-Formel sieht in Calc nun so aus:

=WENN(G4>=$K$4;1;WENN(G4>=$K$5;2;3))

Programmierer würden diesen Ausdruck zur besseren Übersicht in mehrere Zeilen schreiben, das sähe dann so aus:

=WENN(
   G4 >= $K$4 ;
   1 ;
   WENN(
      G4 >= $K$5 ;
      2 ;
      3
   )
)

Dabei stehen dann für jeden Aufruf der WENN()-Funktion die drei Argumente in separaten Zeilen bzw. Blöcken von Zeilen. Calc erwartet aber die durchgehende Eingabe in einer einzigen Zeile.

Die Schachtelung müssen Sie nun noch zweimal erweitern, um auch die Noten 4 und 5 finden zu können. Das führt schließlich zur Formel

=WENN(G4>=$K$4;1;WENN(G4>=$K$5;2;WENN(G4>=$K$6;3;WENN(G4>=$K$7;4;5))))

die Sie in Zelle H4 eintragen. Der Rest ist wieder wie gehabt: Kopieren Sie durch Anfassen der Zellumrandung am kleinen schwarzen Quadrat und anschließendes Ziehen der Maus diese Formel in die Zellen H5–H13. Danach stehen in der Spalte H alle Noten: Sie haben nun die Vorgabe aus Abbildung 1 erreicht.

Notenübersicht

Als Ergänzung soll nun noch eine Übersicht entstehen, aus der Sie schnell ablesen können, wie viele Einser, Zweier etc. es in der Prüfung gibt. Bei nur zehn Prüflingen könnte man das schnell von Hand machen, für eine größere Zahl von Teilnehmern ist es aber sinnvoller, automatisch zu zählen. Wir nutzen dazu eine weitere Calc-Funktion, die in einem Zellbereich z. B. die Anzahl der 1-Werte zählen kann: Sie heißt ZÄHLENWENN(). Tragen Sie in Zelle L4 die folgende Formel ein:

=ZÄHLENWENN($H$4:$H$13; 1)

Sie schaut in die Zellen H4–H13 und zählt, wie oft dort der Wert 1 steht; diese Anzahl landet dann in der Formelzelle. Im Beispiel gibt es drei Einser, also ergibt sich der Wert 3. Den Zellbereich haben wir in der Formel wiederum nicht in der relativen Form (H4:H13), sondern in der absoluten Form ($H$4:$H$13) angegeben, damit sich beim späteren Kopieren der Formel die Werte nicht ändern.

Wir nutzen nun noch die Tatsache aus, dass wir in Spalte J die Noten 1–5 eingetrage haben: Da in Zelle J4 die 1 steht, können wir die Formel auch so schreiben:

=ZÄHLENWENN($H$4:$H$13; J4)

Jetzt können wir die Formel auch in die Zellen L5–L8 kopieren und erhalten damit die gesuchte Übersicht.

1. Markieren Sie zunächst die fünf Noten (Zellen J4–J8) und dann mit gedrückter Strg-Taste zusätzlich die fünf Anzahlzellen (L4–L8) – es sind jetzt also zehn Zellen markiert, die nicht direkt zusammenhängen, weil sich dazwischen noch die Prozentspalte befindet.
2. Rufen Sie den Menüpunkt Einfügen / Diagramm auf und entscheiden Sie sich unter Diagrammtyp für die Säulen – diese Option ist auch voreingestellt. Klicken Sie auf Weiter.
3. Jetzt geht es um die Frage, wie die markierten Werte zu interpretieren sind. Wählen Sie die Option Datenreihen in Spalten und setzen Sie ein Häkchen vor Erste Spalte als Beschriftung. Im Hintergrund sehen Sie bereits eine Vorschau des Diagramms mit fünf Säulen. Klicken Sie auf Fertig stellen.

Das frisch eingefügte Diagramm können Sie nun noch verkleinern und an eine geeignete Position verschieben. Abbildung 5 zeigt das Ergebnis. Wenn Sie das Diagramm per Doppelklick auswählen, zeigt Calc auch die Zellen an, welche die verwendeten Daten enthalten: Sie erkennen das an den blauen Umrandungen der Zellbereiche J4–J8 und L4–L8.

Abbildung 5: Das eingefügte Diagramm zeigt die Verteilung der Noten auch grafisch an.

Damit ist die Arbeit an der Notentabelle abgeschlossen. Durch zwei Tricks, die wir angewandt haben, ist sie auch für größere Notenliste geeignet: Wenn Sie zusätzliche Bewertungen vor (!) Zeile 13 einfügen, ist nur ein Anpassungsschritt nötig: Markieren Sie in einer der “vollständigen” Zeilen die Zellen, in denen Summe, Prozentwert und Note stehen, und kopieren Sie diese auf die neuen Zeilen. Alle Berechnungen, einschließlich der Notenverteilung, passen sich dann automatisch an. Es ist nur wichtig, dass Sie die zusätzlichen Zeilen nicht am Ende, sondern “mittendrin” einfügen. Stehen die Schülernamen nach diesem Schritt in falscher Reihenfolge, können Sie den ganzen Block mit den Daten und Berechnungen (also die relevanten Zeilen in den Spalten A–H) markieren und über den Menüpunkt Daten / Sortieren nach Spalte A alphabetisch sortieren. Durch das Einfügen zusätzlicher Zeilen verzerrt sich eventuell die Darstellung des Diagramms; wenn das passiert, markieren Sie das Diagramm und ziehen es über einen der Anfasser am Rand wieder auf die richtige Höhe.

Viel mehr Funktionen

Der Einstieg in die Arbeit mit Funktionen in Calc ist leicht. Das Programm hat aber noch viel mehr als einfache Summen, Durchschnittswerte und Fallunterscheidungen zu bieten. Über den Menüpunkt Einfügen / Funktionsliste können Sie am rechten Rand eine Liste der verfügbaren Funktionen einblenden – klicken Sie eine der Funktionen an, erscheint unten eine minimale Beschreibung. In der Online-Hilfe, die Sie über [F1] erreichen, erhalten Sie eine ausführlichere Beschreibung, indem Sie den Funktionsnamen (z. B. SUMME) in den Suchdialog eingeben.