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
Tipp: 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
wirdB
und ausB
wirdC
- Verschiebung nach unten um 2 Zeilen: aus
2
wird4
und aus4
wird6
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:
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
.
![](/m27d/assets/cell-range.Bm1ub6dA.png)
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) |
Tipp: 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.
- 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.
- 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.
- Das Punktetotal ergibt sich, indem die Punkte aus Hochsprung und Weitsprung addiert werden. Füge in Spalte O eineentsprechende Formeln ein.
- Sortiere die Tabelle nach dem Punktetotal absteigend.
- Schreibe den Rang in die Spalte A.
- Füge in der Zeile 14 Mittelwerte für den Hochsprung-Versuch und den besten Weitsprung-Versuch ein.
- 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
Aufgabe: OneYearFitBitData.xlsx
Berechne Summe, Durchschnitt und maximaler Wert übers ganze Jahr von
Calories
,Steps
,Floors
undDistance
.Ü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: MITTELWERTWENNFüge neben der Spalte
Calories
ein SpalteKJ
ein. Rechne die Kalorien in Joules um, indem du sie mit dem Faktor4.1868
multiplizierst. Am Besten fügst du den Umrechnungsfaktor in eine freie Zelle rechts on der Tabelle und gibst ihr einen Namen!
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.
![](/m27d/assets/fill.BP0CtCUh.png)
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.
![](/m27d/assets/fill-diff.CtIq3Y_X.png)
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:
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:
- falls nicht vorhanden: wir erstellen ein neue Spalte mit deren Werte die Gruppen festgelegt werden.
- 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
Zusatzaufgabe: Notenliste
Falls du’s noch nicht gemacht hast: verwende Formeln, um den Schnitt in den Fächern zu berechnen.
Es zählen nicht immer alle Noten gleich. Wie könnte man eine Gewichtung der Note einbauen? Diese Gewichtung müsste natürlich beim Berechnen des Schnittes auch berücksichtigt werden.