Spreadsheets

Lernziele

Um Daten zu verarbeiten verwendet man meist Tabellenkalkulationsprogramms (auf Englisch Spreadsheets genannt) wie Microsoft Excel oder Google Sheets/Tabelle. Spreadsheets, die mit diesen beiden Programmen erstellt wurden, sind auch miteinander kompatibel.

Arbeite entweder mit Google Sheets oder mit Microsoft Excel.

1. Login Google Account (Erstelle einen solchen falls nötig)
2. Erstelle in Google Drive einen Ordner für die Aufgaben, z.B. 'Informatik'
3. Öffne darin eine leere 'Tabelle'
4. Einstellungen:
   1. Dezimalpunkt anstelle Komma: Sprache/Land auf Schweiz umstellen (Datei / Einstellungen)

• In Edit-Mode wechseln: Doppelklick oder F2

Ziel: Grundlagen von Google Sheets / Microsoft Excel kennenlernen

Lade das folgende Dokument herunter und importiere dieses in ein Google Sheet Dokument. Für jede Teilaufgabe hat es ein eigenes Tabellenblatt (unten auswählen), welches bearbeitet werden soll. Studiere immer zuerst den zugehörigen Theorieblock und löse dann die Aufgabe.

aufgabe_a_template.xlsx

Lösung

Ein Spreadsheet ist eine Tabelle, welche aus einer Vielzahl von Zellen besteht:

Der Inhalt einer Zelle kann auf unterschiedliche Weisen interpretiert werden. Zum Beispiel kann der Inhalt 42 interpretiert werden als:

• Zahl $42$, mit der gerechnet werden kann
• Text 42, mit dem man nicht rechnen kann (ähnlich wie Ints und Strings in Python)
• Datum 10.02.1900, da es der 42. Tag nach dem Datum 30.12.1899 ist.

Löse die Teilaufgabe im Tabellenblatt „a) Zellen formatieren“. In der grünen Zelle B1 soll eine Zahl eingegeben werden. In den roten Zellen (B3, B4 usw.) darunter soll dieser Wert wie angegeben formatiert werden. Beachte, dass die roten Zellen einen Verweis auf B1 beinhalten. Ändert man den Wert in B1, passt sich der Wert in B3, B4 usw. daran an (mehr dazu weiter unten). Ändere den Inhalt der roten Zellen also nicht, formatiere diesen nur.

Möchte man von einer Zelle auf den Inhalt einer anderen Zelle zugreifen, so kann man einen Verweis auf diese Zelle (auch Zellbezug oder Referenz) erstellen. Ändert man den Wert der Zelle, so verändern sich auch die Werte aller Zellen, die auf diese verweisen, zum Beispiel =D4.

Wird eine Zeile ausgewählt, so erscheint unten recht von der Zelle ein kleiner Kreis. Mit diesem kann man den Zelleninhalt auf die benachbarten Zellen übertragen (Autovervollständigung), wobei die Verweise angepasst werden. Zieht man den Verweis =D4 um eins nach unten, so passt sich die Zeile des Verweises auf =D5 an. Zieht man sie hingegen nach rechts, so passt sich die Spalte an, also =E4. Möchte man dies nicht, so kann man die Zeile und/oder Spalte mit einem Dollarsymbol $ fixieren. Verwendet man ein $, so spricht man von absoluten Zellbezügen resp. von relativen Zellbezügen, wenn man keine verwendet.

Mit Spreadsheets kann einfach gerechnet werden: Beginne dazu den Inhalt einer Zelle mit einem Gleichheitszeichen =. Verweise sind bespielsweise nützlich, um mit den Werten in den Zellen zu rechnen. Zum Beispiel kann man mit =B1+C1 die Werte, die in B1 und C1 stehen addieren.

Löse die Teilaufgabe im Tabellenblatt „b) Verweise“. In den roten Zellen soll die Summen der jeweiligen Zahlen in den grünen Zellen („Zahl 1“ + „Zahl 2“) berechnet werden. In den Blauen Zellen soll immer das Produkt der jeweiligen „Zahl 1“ x Faktor berechnet werden. Passt man die Werte in den grünen Zellen an, sollen sich die Resultate in den roten und blauen Zellen entsprechend anpassen.

Tipps:

• Schreibe eine Rechnung nur 1x. Ziehe sie dann nach unten.
• An einer Stelle brauchst du einen fixierten Verweis.

Es gibt auch viele vordefinierte Funktionen wie =SUM(...) mit der man einfach die Summe vieler Zellen berechnen kann. Im folgenden Beispiel wird die Summe aller Zellen von A1 bis und mit A6 berechnet:

Beachte, dass die Funktionsnamen in Microsoft Excel (leider) auf Deutsch übersetzt wurden. Beispiel: SUM(...) in Google Sheets entspricht SUMME(...) in Excel.

Berechne in „c) Einfache Funktionen“ die Summe aller Zahlen in den grünen Zellen, sowie die Quadratwurzeln aller Zahlen in der ersten Spalte.

Die Autovervollständigung haben wir bereits oben angetroffen. Mit ihr kann man den Inhalt einer Zelle ganz einfach auf die benachbarten Zellen übertragen. Verweise werden entsprechend angepasst, falls Zeilen/Spalten nicht mit $ fixiert werden. Es gibt aber auch andere intelligente Arten der Autovervollständigung. Beinhalten zum Beispiel Zellen A1 und A2 die Werte $1$ und $2$. Markiert man beide Zellen gleichzeitig und wendet man die Autovervollständigung an, so werden die Zellen darunter mit den Werten $3, 4, 5, \ldots$ vervollständigt.

Löse die Aufgabe in „d) Berechne in „d) Autovervollständigung“. Ziel ist, die folgende Tabelle mit möglichst wenig Aufwand zu reproduzieren:

Die Werte der roten Zellen sind die Produkte der beiden Zahlen in den grünen Zellen der jeweiligen Spalte und Reihe. Ändert man also die Werte in den grünen Zellen, passen sich die roten an.

Tipps

Zusammen lösen:

warmup_funktionen_template.xlsx

Ziel: Erstelle in eine Notentabelle, ähnlich zu derjenigen, die dir die Schule zur Verfügung stellt.

• Starte mit dem Template unten. Die Noten darfst du natürlich anpassen. Am besten nimmst du gleich deine aktuellen Noten, dann kannst du immer mit der offiziellen Notentabelle vergleichen.
• Die Aufgabe besteht aus mehreren Teilen.
• Studiere in jedem Teil zuerst die angegebene Theorie …
• … und bearbeite dann das Template so, dass es wie im Screenshot angezeigt aussieht.
• Stelle sicher, dass deine Lösung alle Kriterien in der Tabelle erfüllt.

aufgabe_b_notentabelle_template.xlsx

• Tabelle schön gestalten (Schrifgrössen, Farbe, Ränder, Zellenbreite- & Höhe, Ausrichtung …)
• Mittelwerte berechnen
• Werte runden

Tipps:

• Die Durchschnittsberechnung soll sich auf alle 20 möglichen Noten des Fachs beziehen und nicht nur auf diejenigen, die aktuell in der Tabelle stehen, so dass diese auch funktioniert, wenn man weitere Noten hinzufügt.
• Bei Fächern, bei denen noch keine Noten stehen, tritt bei den Notenberechnungen die Fehlermeldung “#DIV/0!„ auf. Um diese kümmern wir uns später.

• Verzweigungen (IF) verwenden
• mit bedingten Formatierungen arbeiten
• Berechnungen nur ausführen, wenn dafür benötigte Zellen Werte beinhalten (COUNT)

Tipps:

• Durchschnittsnoten sollen nur berechnet und angezeigt werden, falls Noten vorliegen. Liegen in einem Fach keine Note vor, sollen die Zellen „Durchschnitt“ und „Zeugnisnote“ leer sein und nicht den Fehler “#DIV/0„ anzeigen. Dazu kann man die COUNT-Funktion verwenden (siehe Tipps unten). Mit dieser kann man zählen, wie viele Noten vorliegen. Falls keine Noten vorliegen (Wert also 0 ist), so gibt man den leeren String "" aus. Anderenfalls berechnet man den Durchschnitt. Für diese Fallunterscheidung benötigen wir die IF-Funktion (siehe Theorie unten), In Pseudocode sieht das etwa wie folgt aus: =IF(<Anzahl Noten = 0>,"",<Berechnung Durchschnitt>).

`=IF(<Bedingung>,<Zelleninhalt falls erfüllt>,< Zelleninhalt falls nicht>`

`IFS(<Bedingung 1>,<Wert 1>,<Bedingung 2>,<Wert 2>,...)`

Das IF in Spreadsheets, entspricht in etwa dem if-else in Python:

if <Bedingung>:
   # Code falls Bedingung erfüllt
else:
   # Code falls Bedingung nicht erfüllt

`=COUNT(<Zellenbereich>)`

• Zellen verbinden
• Benutzerdefinierte Zellenformatierungen
• Verknüpfen von zwei Bedingungen mit AND, OR

Bemerkungen:

• In einer ersten, vereinfachten Version unterscheiden wir nur zwischen den beiden Fällen „PROMOTION“ und „KEINE PROMOTION“:
  • „PROMOTION“: Punktzahl 0 oder positiv (d.h. alle Minuspunkte doppelt kompensiert) und höchstens 3 UG.
  • „KEINE PROMOTION“: sonst, also falls Punktzahl negativ oder mehr als 3 UG (eine der beiden Bedingungen reicht, so dass Promotion nicht erfüllt).
  • Tipp: Dazu benötigt man AND oder OR (siehe Theorie unten).
  • Den Fall „KNAPP PROMOVIERT“ implementieren wir später.

• Fallunterscheidungen mit mehr als zwei Fällen
• Bedingte Formatierungen mit benutzerdefinierten Formeln

Erweitere deine Notentabelle weiter, z.B.:

• Gewichtungen implementieren (wie in KSR-Notentabelle)
• eigene Ideen

geburtstagsrechner.xlsx

Mit Spreadsheets kann man ganz einfach Rechnungen mit Daten machen. Damit kannst du Fragen wie die folgenden ganz einfach beantworten.

• Wie viele Tage lebe ich bereits?
• Wie viele Tage ist meine Mama älter als ich?
• An welchem Datum wird (oder war) mein Papa genau doppelt so alt wie ich?
• Wie alt bin ich am 4.2.2042?

Beispiel: Der 17.04.2023 entspricht der Zahl $45033$. Addiert man nun $365$ zu dieser Zahl, entspricht diese dem Datum 16.04.2024 (weil 2024 ein Schaltjahr ist).

Schreibe nun ein Programm, in dem man die Geburtsdaten zweier Personen eintippen kann: Person 1 ist älter als Person 2. Der Code berechnet und gibt aus:

• Altersunterschied in Tagen
• Altersunterschied in Jahren
• Anzahl Tage, die Person 1 schon lebt
• Datum, wann Person 1 doppelt so alt war/sein wird wie Person 2