Teileweise reichen die vorhanden Daten nicht – man möchte basierend darauf neue Werte berechnen, z.B. einen Notenschnitt, einen Zins oder eine Gesamtsumme.

In diesem Fall schreibt man in eine Zelle eine Formel. Excel zeigt dann den berechneten Wert an, also das ausgewertete Resultat der Formel. Gespeichert ist aber die Formel – das berechnete Ergebnis passt sich automatisch an, sollte ein verwendeter Wert ändern.

Grundrechenoperationen

Excel kennt die mathematischen Grundrechenoperatoren – wir können damit Formeln erstellen.

Formel eingeben

Eine Formel beginnt immer mit einem Gleichheitszeichen:

=39+2

Damit weiss Excel, dass in diesem Feld etwas berechnet werden soll!

Operationen

In Formeln können die Grundrechenoperationen sowie die Potenz verwendet werden:

Operation	Zeichen	Beispiel
Addition	+	=5+2
Subtraktion	-	=5-2
Multiplikation	*	=5*2
Division	/	=5/2
Potenz	^	=5^2

Dabei gelten die üblichen Assoziativgesetze der Mathematik.
Runde Klammern können auch eingebaut werden.

Zellbezüge

Um einen Wert aus einer anderen Zelle in einer Formel zu verwenden, wird die Bezeichnung der Zelle angegeben. Die folgende Formel addiert die Werte in den Zellen A2 und B4:

=A2+B4

Maus

Während der Eingabe einer Formel, kann ein Zellbezug durch einen Mausklick auf die entsprechende Zelle eingefügt werden.

relative Zellbezüge

Wird eine Formel in eine andere Zelle kopiert oder verschoben, so passen sich die Zellbezüge automatisch an, und zwar um die relative Verschiebung:

Schreibt man in die Zelle A1 die folgende Formeln

=A2+B4

und verschiebt diese in die Zelle B3, dann passen sich die Zellbezüge an:

• Verschiebung nach rechts um 1 Spalte: aus A wird B und aus B wird C
• Verschiebung nach unten um 2 Zeilen: aus 2 wird 4 und aus 4 wird 6

Die verschobene Formel in Zelle B3 lautet also:

=B4+C6

Zellen benennen

Um das Anpassen der Zellbezüge zu verhindern, können wir Zellen benennen. Dazu markieren wir die zu benennende Zelle und geben ihr oben links einen Namen (dort wo die Zellbezugsadresse der aktuellen Zelle angezeigt wird).
Anschliessend können wir in einer Formel an Stelle des Zellbezugs den Namen der Zelle verwenden.

absoluter Zellbezug

Alternativ kann mit einem absoluten Zellbezug gearbeitet werden: schreiben wir bei einem Zellbezug vor den Buchstaben und/oder die Zahl ein $-Zeichen, so wird die Spalte und/oder die Zeile des Bezugs fixiert und passt sich dann beim Kopieren/Verschieben nicht mehr an.

ursprüngliche Formel	Verschiebung	verschobene Formel
=A2	von A1 nach B3	=B4
=$A2	von A1 nach B3	=$A4
=A$2	von A1 nach B3	=B$2
=$A$2	von A1 nach B3	=$A$2

Fehlermeldungen

Wenn das Tabellenkalkulationsprogramm den Wert einer Formel nicht berechnen kann, wird eine Fehlermeldung in der Zelle angezeigt.

Fehler	Bedeutung
#WERT!	eine verwendete Zelle enthält nicht einen Zahlenwert
Err:522	es besteht ein zirkulärer Bezug
#BEZUG!	es besteht ein zirkulärer Bezug

Ein zirkulärer Bezug bedeutet, dass die Resultate von zwei Formeln gegenseitig voneinander abhängen, Beispielsweise, wenn in der Zelle A1 die Formel =B2 und in der Zelle B2 die Formel =A1 steht:

Zirkularbezug

Funktionen

Mit Funktionen können Berechnungen durchgeführt werden, welche über die Grundrechenoperationen hinausgehen. Beispielsweise kann mit der Funktion WURZEL(x) die Quadratwurzel einer Zahl berechnet werden. Die folgende Formel berechnet die Quadratwurzel von 2:

=WURZEL(2)

In einer Funktion können natürlich auch Zellbezüge verwendet werden. So wird die Quadratwurzel des Wertes in Zelle A1 berechnet:

=WURZEL(A1)

Der Wert, welcher der Funktion übergeben wird, nennt man Argument. Manche Funktionen benötigen mehr als ein Argument. Diese werden durch einen Strichpunkt ; getrennt.

Die Funktion RUNDEN rundet einen Wert auf eine bestimmte Anzahl Dezimalstellen. Dazu muss als erstes Argument der zu rundende Wert und als zweites Argument die Anzahl Dezimalstellen angegeben werden. Die folgende Formel rundet den Wert in A1 auf zwei Dezimalstellen:

=RUNDEN(A1; 2)

Eine weitere nützliche Funktion ist VRUNDEN. Sie rundet einen Wert auf ein Vielfaches eines zweiten Wertes. Um beispielsweise den Wert in der Zelle A1 auf ein Vielfaches von 0.2 zu runden, schreibt man:

=VRUNDEN(A1; 0.2)

Hier ist eine Übersicht der besprochenen Funktionen:

Operation	Funktion	Beispiel
Quadratwurzel	WURZEL	=WURZEL(A1)
Runden auf Dezimalstellen	RUNDEN	=RUNDEN(A1; 2)
Runden auf Vielfaches	VRUNDEN	=VRUNDEN(A1; 0.2)

Funktionen für Zellbereiche

Manche Funktionen werden nicht auf einzelne Werte angewendet, sondern auf Zellbereiche. Ein Zellbereich wird angegeben, indem die Bezeichnung der oberen linken Zelle und der unteren rechten Zelle durch ein Doppelpunkt verbunden werden.

Um den Zellbereich von B2 bis D5 anzugeben, schreibt man B2:D5.

Der Zellbereich B2:D5

Die folgenden Funktionen können auf Zellbereiche angewendet werden:

Operation	Funktion	Beispiel
Summe	SUMME	=SUMME(A1:A10)
Mittelwert	MITTELWERT	=MITTELWERT(A1:A10)
Maximum	MAX	=MAX(A1:A10)
Minimum	MIN	=MIN(A1:A10)

Maus

Zellbereiche können während der Formeleingabe mit der Maus festgelegt werden, indem auf die Startzelle geklickt wird und dann bei gedrückter Maustaste der Bereich durch Verschieben der Maus ausgewählt wird.

Aufgabe «Sporttag»

Speichere die Arbeitsmappe «sporttag.xlsx» auf deinem Computer.

1. Die Punktzahl beim Hochsprung ergibt sich aus der Höhe multipliziert mit einem Faktor. Gib in den Zellen der Spalte F Formeln ein, um die Punktzahl für jede/n Teilnehmer/in zu berechnen.
2. Beim Weitsprung wird nur der beste Versuch berücksichtigt. Verwende in der Spalte K eine Funktion, um den besten Sprung für jede/n Teilnehmer/in zu bestimmen.
3. Das Punktetotal ergibt sich, indem die Punkte aus Hochsprung und Weitsprung addiert werden. Füge in Spalte O eine entsprechende Formeln ein.
4. Sortiere die Tabelle nach dem Punktetotal absteigend.
5. Schreibe den Rang in die Spalte A.
6. Füge in der Zeile 14 Mittelwerte für den Hochsprung-Versuch und den besten Weitsprung-Versuch ein.
7. Schreib in die Spalte Q eine Formel, welche für jede/n Teilnehmer/in berechnet wo sie/er besser ist, resp. mehr Punkte geholt hat – in Weit- oder Hochsprung. Tipp: Excel Hero (opens new window)

Aufgabe «OneYearFitBitData.xlsx»

1. Berechne Summe, Durchschnitt und maximaler Wert übers ganze Jahr von Calories, Steps, Floors und Distance.

2. Überlege dir, wie du Null-Einträge ignorieren kannst. (Wir nehmen an, dass an einem Tag mit 0 Schritten keine Aufzeichnung stattgefunden hat, da der Tracker nicht getragen wurde.)
   Tipp: MITTELWERTWENN (opens new window)

3. Füge neben der Spalte Calories ein Spalte KJ ein. Rechne die Kalorien in Joules um, indem du sie mit dem Faktor 4.1868 multiplizierst. Am Besten fügst du den Umrechnungsfaktor in eine freie Zelle rechts on der Tabelle und gibst ihr einen Namen!

Aufgabe «OneYearFitBitDataSleep.xlsx»

• Berechne die gemessene Schlafdauer übers ganze Jahr in Stunden und in Prozent vom ganzen Jahr

Automatisches Ausfüllen

Mit dem automatischen Ausfüllen können Tabellenbereiche schnell mit einem vordefinierten Wert oder einer Formel ausgefüllt werden.

Um einen Tabellenbereich mit einem konstanten Wert zu füllen, wird dieser Wert in die erste Zelle des Bereichs geschrieben. Anschliessend wird das kleine Quadrat unten rechts der Zellmarkierung gefasst und in die gewünschte Richtung gezogen.

Automatisches Ausfüllen mit einem konstanten Wert

Ein Tabellenbereich kann auch mit einer auf- oder absteigenden Folge von Werten gefüllt werden. Dazu werden die zwei ersten Werte in zwei benachbarte Zellen geschrieben. Anschliessend werden beide Zellen ausgewählt. Nun wird wieder das kleine Quadrat unten rechts der Zellmarkierung gefasst und in die gewünschte Richtung gezogen.

Automatisches Ausfüllen mit einer Differenz

Automatisches Ausfüllen klappt auch mit Formeln. Dabei passen sich aber Zellbezüge an, ausser sie werden durch ein vorangestelltes $-Zeichen fixiert. In den meisten Fällen ist dies jedoch gewünscht – Die Punkte jedes SuS sollen ja aus den eigenen Leistungen berechnet werden:

Automatisches Ausfüllen für Formeln

Aufgabe «Automatisches Ausfüllen»

Erstelle ein neues Excel-Dokument und teste das automatische Ausfüllen wie es in den oberen zwei Screenshots beschrieben ist.

Gruppieren

Mit speziellen Funktionen wie MITTELWERTWENN können wir Daten gruppieren, also z.B. nicht den Mittelwert über alle 365 Tage, sondern nur über solche, welche eine spezielle Bedingung erfüllen, berechnen. Wir könnten z.B. nach Monate oder Wochentage gruppieren. Dazu gehen wir wie folgt vor:

1. falls nicht vorhanden: wir erstellen ein neue Spalte mit deren Werte die Gruppen festgelegt werden.
2. wir machen für jede Gruppe eine Funktion MITTELWERTWENN die nur Elemente der entsprechenden Gruppe berücksichtigt.

Aufgabe «OneYearFitBitData.xlsx»

• Gruppieren nach Monate:
  • Erstelle eine neue Spalte mit dem Titel «Monat»
  • Berechne Werte für diese Spalte mit der Funktion MONAT basierend auf dem Datum
  • Berechne für jeden Monat mit MITTELWERTWENN den entsprechenden Durchschnitt
  • Stelle die berechneten Daten in Form eines geeigneten Diagramms dar
• Gruppieren nach Wochentage:
  • Erstelle eine neue Spalte mit dem Titel «Wochentag»
  • Berechne Werte für diese Spalte mit der Funktion WOCHENTAG basierend auf dem Datum
  • Berechne für jeden Monat mit MITTELWERTWENN den entsprechenden Durchschnitt
  • Stelle die berechneten Daten in Form eines geeigneten Diagramms dar