Spreadsheets [KSR Wiki]

Diese Seite ist nicht editierbar. Du kannst den Quelltext sehen, jedoch nicht verändern. Kontaktiere den Administrator, wenn du glaubst, dass hier ein Fehler vorliegt.
====== Spreadsheets ======

++++Lernziele|

Prüfungsrelevant ist alles, was in den Lektionen und Übungen behandelt wurde. Die Lernziele unten dienen als Gradmesser und sind nicht unbedingt komplett.

   * Spreadsheet ansprechend **stylen** können (Zellenbreite/höhe, Farbe, Schriftgrösse, Linien, ...)
   * **Zellen formatieren** können
   * Erklären, *was* **Zellbezüge** sind, *wofür* man sie braucht (mache Beispiele) und *wie* man sie verwendet.
   * Vergleiche absolute zu relativen Zellbezügen. *Wofür* und *wie* braucht man welche?
   * **Autovervollständigung** anwenden.
   * Die wichtigsten (math.) **Funktionen** kennen und anwenden können: SUM,SQRT,COUNT,COUNTIF,AVERAGE,ROUND,MROUND
   * **Verzweigungen** anwenden können: IF,IFS
   * **Logische Operatoren** anwenden können: AND,OR,XOR
   * Rechnung / Text in Zelle **nur anzeigen**, wenn gewisse Bedingung erfüllt ist.
   * Wissen, was **bedingte Formatierungen** sind und wie man sie anwendet



++++

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**.

=== Google Sheets ===

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

=== Tipps ===

   * In Edit-Mode wechseln: Doppelklick oder F2


===== Aufgabe A: Grundlagen =====

**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.

{{ :gf_informatik:daten_sca:aufgabe_a_template.xlsx |}}

++++Lösung|

{{ :gf_informatik:daten_sca:aufgabe_a_lsg.xlsx |}}

++++

==== Zellen formatieren ====

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

{{ :gf_informatik:daten_sca:google_sheets.png?1000 |}}

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.

<box 100% round green|**Zelle formatieren**>
Je nachdem, als was ein Wert interpretiert werden soll, musst die **Zelle formatiert** werden: 

   * Google: Zelle(n) auswählen / "Format" / "123 Zahl" / Auswahl aus "Nur Text", "Zahl", "Prozent", "Datum" uvw. oder direkt hier: {{:gf_informatik:daten_sca:google_sheets_format.png?150|}}
   * Excel: Zelle(n) auswählen / CTRL + 1 / ...

</box>

=== Aufgabe A1 ===

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.

==== Verweise auf Zellen & einfache Rechnungen ====

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.

<box 100% round green|**Zellbezüge**>

   * **Verweis** auf Zelle: `=<Bezeichnung der Zelle>`, zum Beispiel `=D4`
   * Verweis mit **fixierter** ...
     * Zeile: `=D$4`
     * Spalte: `=$D4`
     * Zeile und Spalte: `=$D$4`

</box>

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.

=== Aufgabe A2 ===

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.



==== Einfache Funktionen ====

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:

{{:gf_informatik:daten_sca:google_sheets_sum.png?300|}}

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

<box 100% round green|**Einfache Funktionen**>
Einige der wichtigsten einfache Funktionen sind:

   * SUM/SUMME: Summe der angegebenen Zellen berechnen
   * SQRT/WURZEL: Quadratwurzel
   * COUNT/ANZAHL: Gibt an, in wie vielen der angegebenen Zellen Zahlen drin stehen

</box>

=== Aufgabe A3 ===

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

==== Autovervollständigung ====

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.

<box 100% round green|**Autovervollständigung**>

Tipp: Nutze wann immer möglich die Autovervollständigung. Vermeide mühsames Copy-Paste von Zelle zu Zelle.

</box>


=== Aufgabe A4 ===

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

{{ :gf_informatik:daten_sca:google_sheets_mult.png?800 |}}

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|

   * Verwende Autovervollständigung für die grünen Zellen
   * Schreibe den Code für die roten Zellen nur für *genau eine Zelle*.
   * Kopiere diesen dann auch wieder mit Autovervollständigung auf die anderen Zellen.
   * Damit dies funktioniert, musst du bei den Verweisen die Spalten/Reihen korrekt fixieren.

++++

===== Warm-up Funktionen =====

Zusammen lösen:

{{ :gf_informatik:daten_sca:warmup_funktionen_template.xlsx |}}

===== Aufgabe B: Notentabelle =====

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

== Auftrag ==

   * 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.

{{ :gf_informatik:daten_sca:aufgabe_b_notentabelle_template.xlsx |}}

==== Teil I ====

{{ :gf_informatik:daten_sca:notentabelle_teil_1.png?600 |}}

== Ziele ==

   * 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.

== Kriterien ==

^ **KRITERIEN** ^ **ERFÜLLT?** ^
| **Darstellung**||
| Schriftgrösse, normal/fett stimmen überein | $\square$ |
| gewisse Zellen eingefärbt | $\square$ |
| passende Zeilenhöhe & Spaltenbreite | $\square$ |
| Spalten für einzelne Noten haben alle *gleiche* Breite | $\square$ |
| Zelleninhalte horizontal richtig (z.T. links, z.T. mittig) ausgerichtet | $\square$ |
| Zelleninhalte vertikal mittig ausgerichtet | $\square$ |
| passende Rahmen mit untersch. Dicken | $\square$ |
| Durchschnitt mit 2 NKS angezeigt (Stichwort: Zelle formatieren) | $\square$ |
|**Logik**||
| für jedes Fach einzeln Mittelwert berechnet | $\square$ |
| Mittelwert-Berechnung funktioniert für 1-20 Noten (also nicht nur für die gerade eingetragenen) | $\square$ |
| Zeugnisnote auf 0.5 Noten *gerundet*, Noten wie $5$ werden ohne NKS dargestellt | $\square$ |

== Theorie ==

<box 100% round green|**Spaltenbreite & Zeilenhöhe anpassen**>

   * einzelne Spalte/Zeile: oben/links zw. zwei Spalten/Reihen -> mit Maus verschieben
   * mehrere Spalten/Reihen mit *gleicher* Breite/Höhe: ganze Spalte/Reihe markieren -> dann mit Maus verschieben

{{ :gf_informatik:daten_sca:google_sheets_spaltenbreite.png?400 |}}

</box>

<box 100% round green|**Durchschnitt / Mittelwert bestimmen**>

Berechne mit `=AVERAGE(...)` (dt. `=MITTELWERT()`) den **Mittelwert** oder **Durchschnitt** mehrerer Zellen.

</box>

<box 100% round green|**Runden**>
Folgende zwei Befehle zum Runden sind sehr praktisch:

   * `=ROUND()`: **normales Runden**, z.B. `=ROUND(3.14159,2)` rundet auf 2 Nachkommastellen
   * `=MROUND(...)` (de: `=VRUNDEN(...)`): **Runden auf Vielfaches** von Zahl, z.B. `=MROUND(5.28,0.5)` rundet auf $5.5$

</box>

==== Teil II ====

{{ :gf_informatik:daten_sca:notentabelle_teil_2.png?600 |}}

== Ziele ==

   * 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>).`


== Kriterien ==

^ **Darstellung** ^ **Erfüllt?** ^
| die gerundeten Zeugnisnoten mit formatierten Bedingungen einfärben (rot falls UG, grün falls G) | |

^ **Logik** ^ **Erfüllt?** ^
| Berechnete Noten werden nur angezeigt, wenn Noten vom Fach vorhanden sind | $\square$ |
| Mittelwerte von "Durchschnitte" und "Zeugnisnoten" der einzelnen Fächer berechnen | $\square$ |
| Berechnung der Plus- und Minuspunkte | $\square$ |
| Berechne Summe der Plus- und Minuspunkte | $\square$ |
| Berechnete Werte werden nur angezeigt, wenn überhaupt Noten vorliegen | $\square$ |


== Theorie ==

<box 100% round green|**Bedingte Formatierung**>

Die Formatierung einer Zelle (z.B. Hintergrundfarbe) kann man vom Inhalt abhängig machen. Z.B. kann eine Zelle die Hintergrundfarbe rot haben, falls der Wert darin kleiner als 4 ist.

Vorgehen: Rechte Maustaste auf Zelle -> "Weitere Aktionen für Zellen aufrufen" -> "Bedingte Formatierung" -> Einstellungen vornehmen

</box>

<box 100% round green|**IF-Verzweigungen**>

Soll der Inhalt einer Zelle von einer Bedingung abhängen, verwenden wir die **IF-Funktion** (dt. WENN). Diese hat immer die Form:

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

Dies entspricht dem **if-else** in Python (siehe unten).

Beispiel: `=IF(B2>=4,"Genügend","Ungenügend")`, in der Zelle wird "Genügend" oder "Ungenügend" angezeigt, je nachdem, welchen Wert die Zelle B2 hat.

Für eine Verzweigung mit **mehreren Bedingungen** verwendet man die **IFS/WENNS-Funktion:**

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

Als ähnliche Alternative gibt es noch die **SWITCH-Funktion**.

Beachte: Das hier wird im Gegensatz zu den meisten Programmiersprachen ein *einfaches Gleichheitszeichen* verwendet, um Gleichheit zu überprüfen.
</box>

Das IF in Spreadsheets, entspricht in etwa dem if-else in Python:
<code python>
if <Bedingung>:
   # Code falls Bedingung erfüllt
else:
   # Code falls Bedingung nicht erfüllt
</code>

<box 100% round green|**Anzahl Werte zählen**>

Mit dem folgenden Befehl zählt man, wie viele der angegebenen Zellen Zahlen beinhalten:

<code>
`=COUNT(<Zellenbereich>)`
</code>

Dies ist besonders nützlich, wenn man eine Berechnung nur dann durchführen will, wenn die dafür benötigten Zellen nicht leer sind.

</box>

==== Teil III ====

{{ :gf_informatik:daten_sca:notentabelle_teil_3.png?600 |}}

== Ziele ==

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

== Kriterien ==

^ **KRITERIEN** ^ **ERFÜLLT?** ^
| **Darstellung**||
| Zellen bei "Promotion", in Legende und bei Name/Klasse/Semester passend zu grossen Zellen verbunden | $\square$ |
| Text "(KEINE) PROMOTION" wird vertikal dargestellt | $\square$ |
| Legende eingefärbt | $\square$ |
| Farbe der Zellen zur "Promotion" mit bedingter Formatierung grün / rot eingefärbt | $\square$ |
| Für Anzahl UGs wird benutzerdefinierte Zellenformatierungen verwendet | $\square$ |
|**Logik**||
| Anzahl UG (siehe Theorie zu COUNTIF unten) | $\square$ |
| Bei "Promotion" wird je nach Noten "PROMOTION" oder "KEINE PROMOTION" angezeigt (siehe Tipps unten) | $\square$ |
|  | $\square$ |

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.


== Theorie ==

<box 100% round green|**Logische Operatoren**>

Mit den **logischen Operatoren AND, OR und XOR** kann man mehrere Bedingungen miteinander verknüpfen. Der folgende Ausdruck nimmt den Wert "True" ("False") an, falls beide Bedingungen erfüllt sind:
<code>
`=AND(<Bedingung 1>,<Bedingung 2>`
</code>
Den OR-Operator benutzt man genau gleich, nur reicht es hier, dass eine der Bedingungen erfüllt ist. Typischerweise verwendet man diese logischen Operatoren in der IF-Funktion.

Beispiel: Mit dem folgenden Befehl kann man ermitteln, ob in beiden Zellen A1 und B1 positive Zahlen stehen:
<code>
=IF(AND(A1>=0,B1>=0),"beide Zellen positiv","mind. eine Zelle negativ")
</code>

Erinnerung: Diese logischen Operatoren entsprechen `and`, `or` und `^` in Python.

</box>

<box 100% round green|**Zählen wenn Bedingung erfüllt**>

Mit den Befehl **COUNTIF / ZÄHLENWENN** kann man in einem Bereich die Anzahl Zellen zählen, die eine bestimmte Bedingung erfüllen:

<code>
=COUNTIF(<Zellenbereich>,<Bedingung in Anführungs- & Schlusszeichen>)
</code>

Zum Beispiel zählt man mit dem Befehl `=COUNTIF(A1:A10,">0")` die Anzahl Zellen im Bereich A1:A10, welche Werte grösser Null beinhalten.

</box>

==== Teil IV (optional) ===

{{ :gf_informatik:daten_sca:notentabelle_teil_4.png?600 |}}

== Ziele ==

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

== Kriterien ==

^ **KRITERIEN** ^ **ERFÜLLT?** ^
| **Darstellung**||
| Bedingte Formatierung für alle drei Fälle der Promotion | $\square$ |
| Zelle Z6 mit Überschrift "Promotion" wird auch bedingt formatiert (siehe Theorie unten) | $\square$ |
|**Logik**||
| Auch Fall "KNAPP PROMOVIERT" implementiert | $\square$ |

== Theorie ==

<box 100% round green|**Bedingte Formatierung mit benutzerdefinierter Formel**>

Die Formatierung einer Zelle kann vom Inhalt einer anderen Zelle abhängen. Verwende dazu eine bedingte Formatierung mit "Benutzerdefinierte Formel ist". Ins Formel-Feld kann dann eine Formel getippt werden, die von einer anderen Zelle abhängt, in der Form `=IF(<Bedingung>,1,0)`. Falls die Bedingung (nicht) erfüllt ist, nimmt nimmt der Befehl den Wert 1 (0) an und die bedingte Formatierung wird (nicht) aktiviert.

</box>

== Erweiterungen ==

Erweitere deine Notentabelle weiter, z.B.:

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





===== Zusatzaufgaben =====

==== Geburtstagsrechner ====

{{ :gf_informatik:daten_sca: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?

<box 100% round green|**Arbeiten mit Daten**>

   * Zahlen können als **Daten interpretiert** werden. Die Zahl $42$ entspricht dem Datum 10.02.1900, da dieses der 42. Tag nach dem Datum 30.12.1899 ist.
   * Mit Nachkommazahlen kann man weiter Uhrzeiten der Daten angeben.
   * Die Funktion `=TODAY()` / `=HEUTE()` gibt den Wert des heutigen Datums an (ändert sich jeden Tag).

</box>

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