Google Sheets for (advanced) beginners

Auf dieser Seite lernst du die wichtigsten Tricks zur Bedienung von Google Sheets oder Microsoft Excel.

Sheets und Excel sind sich in Bedienung und Funktionalität sehr ähnlich: Kannst du Sheets bedienen, so kannst du auch Excel bedienen (und umgekehrt). Wir konzentrieren uns hier auf das frei verfügbare Google Sheets. Die folgenden Anleitungen beziehen sich auf die englischsprachige Version von Google Sheets. Du kannst den Anleitungen am leichtesten folgen, wenn du für Sheets ebenfalls Englisch als Benutzersprache wählst.

Benutzersprache¹⁾ auf Englisch setzen:

• In Google Sheets: Datei → Einstellungen → Anzeigesprache
• In Microsoft Excel: Am besten gleich die UI-Sprache des Betriebssystems umstellen…

Das hängt davon ab, wieviel Erfahrung du bereits hast mit Excel/Sheets:

• Anfänger:innen: Wenn du noch nie oder nur wenige Male mit Programmen wie Excel oder Sheets gearbeitet hast: Lies diese Seite genau und löse Aufgabe 1 Schritt für Schritt.
• Fortgeschrittene: Wenn du dich gut auskennst und schon oft Funktionen wie SUM(), AVERAGE(), ROUND() etc. für deine Tabellen genutzt hast: Konzentriere dich auf das, was für dich neu ist. Vielleicht hast du schon viel in Excel gemacht und möchtest nun Sheets nutzen: Dann ist der erste Schritt für dich hilfreich. Falls dir alles klar ist: Löse Aufgabe 1 direkt.

Plane deine achttägige Rundreise durch die Schweiz. Du übernachtest jede Nacht in einem andern Ort:

• Zuerst erstellst du eine Tabelle (Aufgabe1) die du
• dann in Absprache mit deiner Gruppe ausfüllst (Aufgabe 2).

Erstelle eine Tabelle, die alle folgenden Anforderungen erfüllt. Anfänger:innen folgen den Schritten unten. Fortgeschrittene können die Schritte bei Bedarf zur Hilfe nehmen.

1. Die Titel sind fettgedruckt; hier zweizeilig dargestellte Titel müssen in der Tabelle ebensfalls zweizeilig sein.
2. Unter der Titelzeile erfolgen zunächst 8 Zeilen; für jeden Reisetag eine, normale Schrift, siehe folgende Punkte.
3. Spalte A: Enthält die Tage 1…8 –> 1 und 2 eintragen, markieren, bis 8 runterziehen.
4. Spalte B: Nur das Datum in Zelle B2 wird manuell eingetragen, die restlichen Daten werden berechnet: Obiges Datum + 1.
5. Spalte B: Das Datum wird im Format DD.MM.YY angezeigt (Punkte als Trennzeichen).
6. Spalte C: Nur der Ort in Zelle C2 wird manuell eingetragen, die folgenden Orte werden übernommen: Startort = Ankunftsort des Vortages.
7. Spalten F und G: Die Uhrzeiten werden im 24-Stunden-Format HH:MM angezeigt (Doppelpunkt als Trennzeichen).
8. Spalte H: Die Dauer wird jeweils aus Start- und Ankunftszeit berechnet.
9. Spalte I: Die Kilometer werden berechnet: Das Produkt der Reisedauer in Minuten und einem Faktor, der in Zelle D11 eingetragen wird. Wie alle sich wiederholenden Formeln trägst du auch diese nur einmal ein und überträgst sie per Autofill („Runterziehen“) in die restlichen Zeilen.
10. Spalten K und M: Die Kosten werden im Format CHF12.30 angezeigt.
11. Spalte C enthält ab Zelle C11 folgende Einträge untereinander:
    • C11) Km / Min. (Schätzwert):
    • C12) Personen:
    • C13) Durchschnit:
    • C14) Summe:
    • C15) Kosten pro Kilometer:
    • C16) –
    • C17) Kosten Total:
12. In Zelle D11 wird ein Schätzwert für die Kilometer pro Minute eingetragen, in Zelle D12 die Anzahl Personen.
13. In den Spalten H, I, J, K und M wird auf Zeile 13 jeweils der Durchschnitswert aus den Zeilen 2…9 berechnet. Schriftfarbe blau.
14. In den Spalten H, I, J, K und M wird auf Zeile 14 jeweils die Summe aus den Zeilen 2…9 berechnet. Schriftfarbe grün.
15. Der Wert in Zelle D15 wird berechnet (aus Gesamt- oder Durchnschnittskosten und -Kilometern). Format CHF12.30.
16. Der Wert in Zelle D17 wird berechnet: Anzahl Personen multipliziert mit der Summe der Reise- und Übernachtungskosten.
17. C17 und D17 werden fettgedruckt angezeigt.
18. Über bedingte Formatierung (conditional formatting) wird erreicht, dass:
    1. in Spalte H eine Zelle mit einer Reisedauer ab 10 Stunden rot gefüllt ist,
    2. diejenigen Zeilen, deren Datum (in Spalte B) ein Sonntag ist, hellblau gefüllt sind und
    3. diejenigen Zeilen, deren Datum (in Spalte B) ein Samstag ist, rosa gefüllt sind.

1. Einigt euch auf eine Herausforderung, zum Beispiel: Wer plant die günstigste/teuerste Reise? Wer plant die Reise mit kürzester Reisezeit oder mit möglichst wenigen Zugwechseln (Umsteigen). Es muss etwas Zählbares sein.
2. Einigt euch in der Gruppe auf 7 Städte in der Schweiz, die mit Blick auf euere Herausforderung spannend sind (wenn es z.B. um die Anzahl Zugwechsel geht, sind die Grossstädte nicht spannend)
3. Jetzt plant jede:r für sich die bestmögliche Reise. Vom SBB-Fahrplan überträgt ihr Zugnummer (Linie), Startzeit, Ankunftszeit und Preis für 1/2.
4. Am Ende vergleicht ihr, schaut, wer gewonnen hat und in welchen Bereichen Vor- und Nachteile liegen.

1. Falls du keinen Gmail- oder Google-Account hast: Erstelle einen Google-Account (hierfür kannst du deine bestehende KSR-E-Mail nutzen).
2. Gehe auf http://sheets.new – hier musst du dich evtl. anmelden.
   1. Später wirst du deine Tabelle unter https://drive.google.com finden.
   2. Die Variante mit Microsoft Excel verwendet http://excel.new für eine neue Tabelle, sie wird dann unter https://excel.office.com gefunden.
3. Klicke ganz oben auf Untitled spreadsheet und gib deiner Tabelle einen Namen – z. B.: „Rundreise Schweiz“. sheets4beginners_02.png

In diesem Schritt verwendest du folgende Elemente:

1. Trage in der ersten Zeile obige Titel ein: Kopieren, Zelle A1 anklicken, einfügen.
   1. Über Format –> Wrapping kannst du einstellen, ob ein zu breiter Text umbricht, unter oder über die benachbarte Zelle läuft. Umbrüche in der Zelle kannst du auch mit alt+Enter erzeugen.
2. Trage in Spalte A unter „Tag“ die ersten beiden Zahlen ein: 1 und 2.
3. Markiere die beiden Zellen, klicke auf das blaue Kästchen unten rechts und ziehe daran nach unten bis zu Zeile 9 –> Autofill. (Falls dir das neu ist: probiere kurz aus, was passiert, wenn du nach rechts ziehst.)
4. In Zelle B trägst du das Datum (z. B. der 14. Juli) wie folgt ein: 7/14/22 (also im Format M/DD/YY). Jetzt weiss Sheets, dass es sich um ein Datum handelt.
5. Nun änderst du das Zahlenformat in DD.MM.YY: Format –> Number –> Custom date and time; hier kannst du eine ähnliche Vorlage auswählen und im Feld oben anpassen, dann Apply drücken. sheets4beginners_03.png
6. Zelle B3 soll einfach das Datum aus B2 übernehmen und um einen Tag erhöhen:
   1. Wenn du in B3 „=B2“ einträgst, stellst du einen relativen Zellbezug (relative Reference) her. Nun enthält B3, was immer B2 enthält.
   2. Füge einfach ein „+1“ an, um den Wert aus Zelle B2 um eins zu erhöhen. Du hast nun eine einfache Formel erstellt, die sagt, das der Wert in B3 eins höher ist jener in B2.
7. Ziehe mit Autofill Zelle B3 nach unten. Beachte: Weil „=B2“ ein relativer Zellbezug ist, merkt sich Sheets nicht die Zelle B2, sondern die relative Entferung von B3 zu B2: diese ist 0 in x- und -1 in y-Richtung. Wenn nun dieser relative Bezug mit Autofill übertragen wird, übernimmt jede Zelle das Datum der nächstoberen.

In diesem Schritt kommen folgende Elemente hinzu:

1. Trage in C2 und D2 jeweils eine Ortschaft in der Schweiz ein.
2. C3 soll nun D2 entsprechen –> relativer Zellbezug, siehe Schritt B.
3. Mit Autofill übernimmst du die Bezüge bis C9, siehe Schritt B.
4. Trage in F2 und G2 jeweils eine Zeit im Format HH:MM ein. Damit wird die Zahlenformat Zeit übernommen.
5. In H2 trägst du eine Formel mit zwei relativen Zellbezügen ein: Vom Wert in G2 soll der Wert in F2 subtrahiert werden.
6. Mit Autofill übernimmst du die Formel bis H9.
7. Spalte I wird im nächsten Schritt behandelt.
8. Trage in K2 und M2 einen Franken-Betrag ein, zum Beispiel 12.50.
9. Markiere K2 und M2 (mit gedrückter Ctrl-/Cmd-Taste wählst du mehrere Zellen, mit gedückter Shift-Taste wählst du ganze Bereiche zwischen Zellen) und ändere das Format über Fomrat –> Number –> Custum currency.
   1. Hier wählst du CHF1,000.00 und klickst auf Apply.
   2. Wenn dich das Komma nach der Tausender-Stelle stört und du lieber einen schönen Apostroph hättest, kannst du das Zahlenformat nun über Fomrat –> Number –> Custum number format detailliert anpassen und so das Format CHF1'000.00 erzeugen.
10. Übernehme die Formatierungen jeweils für Zeilen 3…9 mit Autofill (Runterziehen) und lösche anschliessend die so erzugten Einträge (Zellen markieren und Taste Delete oder Backspace (←) drücken). Damit werden zwar die Werte gelöscht, die Formatierung bleibt aber erhalten.

Unter den 9 Zeilen deiner Tabelle kommen nun einige weitere Angaben hinzu:

1. Trage in C11 bis C17 die Texte gemäss den Anforderungen oben (11. Punkt) ein.
2. Trage in D11 einen Faktor ein (z.B. 1.5).
3. Und in D12 eine Anzahl Personen (z.B. 2).

In diesem Schritt kommen folgende Elemente hinzu:

In Spalte I soll das Resultat der folgenden Rechnung angezeigt werden: Minuten mal Faktor km/Min. Das machst du in zwei Teilschritten: zuerst erstellst du eine Formel, die die Reisedauer in Spalte H in Minuten umrechnet. Dann erweiterst du diese Formel mit dem Faktor in D11.

1. Hierzu benötigst du Funktionen: Funktionen werden von Sheets/Excel bereitgestellt; du kannst sie nutzen und so komplizierte Berechnungen dem Programm überlassen. Wie in python folgen dem Funktionsnamen immer Klammern. In den Klammern werden die Argumente übergeben. Zum Beispiel für die Funktion SUM(): =SUM(A1,B1) gibt die Summe der Werte in Zellen A1 und B1 zurück.
2. Aber woher weisst du, welche Funktionen es gibt oder welche Funktion für dein Problem hilfreich sein könnte? Entweder du googelst oder du rätst:
   1. Trage das Gleichheitszeichen = ein und tippe den ersten Buchstaben deiner Funktion ein:
   2. Du willst was mit Minuten machen? Tippe mal ein M…
   3. Es erscheinen Vorschläge für Funktionen: Mit den Pfeil-Tasten scrollst du durch die Liste und liest die Beschreibungen, mit der Tabulator-Taste (→|) wählst du sie aus.
   4. Erscheinen die richtige Vorschläge noch nicht, tippst du weitere Buchstaben ein: „Minu…“
3. Erstelle nun in Zelle I2 eine Formel, die die Anzahl Minuten aus der Zeitdauer in Zelle H2 berechnet. Die Formel soll die Funktionen Minute() und Hour() enthalten. (Diese Funktionen geben den Minuten- bzw. den Stundenteil einer Zeit zurück).

1. Jetzt kannst du deine Formel einfach um den Faktor in D11 erweitern, also die gesamte Formel mal * D11 rechnen. Beachte Folgendes:
   1. Die Grundregeln der Mathematik gelten auch hier: Punkt vor Strich und so. Verwende Klammern, damit die Multiplikation stimmt.
   2. Würdest du deine Formel mit Autofill auf die untenstehenden Zellen übertragen, so würde dein relativer Zellbezug auf D11 laufend erhöht werden, sodass die Formel in H3 sich auf D12 bezöge, H4 auf D13 etc. Das vermeidest du, indem Du einen absoluten Zellbezug (absoulte reference) zu C11 erzeugst. Das machst du mit dem Dollar-Zeichen:
      1. \$D$11 = absoluter Bezug auf Spalte D und auf Zeile 11.
      2. $D11 = absoulter Bezug auf Spalte D, relatvier Bezug auf Zeile 11
      3. D$11 = relativer Bezug auf Spalte D, absoluter Bezug auf Zeile 11
2. Übertrage die Formel mit absouluten Bezug auf den Faktor in D11 mit Autofill bis I9.

1. Trage in H13 eine Formel ein, die den Durchschnittswert der Werte in H2 bis H9 mittels einer geeigneten Funktion berechnet („H2:H9“ versteht Sheets als „H2 bis H9“).
2. Trage in H14 eine Formel ein, die die Summe der Werte in H2 bis H9 mittels einer geeigneten Funktion berechnet. (Sobald du die Funktion mit (→|) gewählt hast, kannst die Zellen für die Argumente auch direkt in deiner Tabelle markieren).
3. Kopiere die beiden Formeln und füge sie auf gleicher Zeilenhöhe für die Spalten I, J, K und M ein. Oder du überträgst die Formeln mit Autofill ziehend nach rechts und löschst die Einträge in Spalte L.
4. Ändere die Schriftfarbe in Zeile 13 in blau: Ganz links Zeilennummer markieren, dann auf das Symbol Text color (grosses A) in der Symbolleiste klicken.
5. Ändere die Schriftfarbe in Zeile 14 in grün.
6. Das Zahlenformat in D15 und D17 soll dem in Spalte K und M entsprechen. (Am schnellsten: Kopieren, Einfügen, Werte löschen. Alternativ: Format kopieren.)
7. Trage in D15 eine Formel ein, die die Kosten pro Kilometer berechnet. Zum Beispiel aufgrund der Summen der Kilometer und der Reisekosten.
8. Trage in D17 eine Formel ein, die das Produkt aus D12 und der Summe der gesamten Reise- und Übernachtungskosten berechnet.
9. Markiere Zeile 17 und ändere den Schriftstil in fettgedruckt (bold).

Mit der bedingten Formatierung kannst du die Formatierung (Farbe, Schriftstil etc.) von Zellen abhängig von den Werten in diesen oder anderen Zellen setzen.

In Spalte H sollen die Zellen rot werden, wenn die Werte darin grösser oder gleich 10 Stunden sind. Dazu gehst du wie folgt vor:

1. Markiere Zellen H2 bis H9 und wähle Format → Conditional formatting.
2. Wähle unter Format rules den Eintrag Greater than or equal to.
3. Trage im Feld Value or formula den Wert 10:00 ein.
4. Ändere unter Formatting style die Füllfarbe auf rot und klicke auf Done.
5. Probiere es aus, indem du in einer Zeile die Startzeit und Ankunftszeit so setzt, dass eine Reisedauer von grösser 10 Stunden entsteht.

Im Bereich A2 bis M9 sollen alle Zellen einer Zeile, bei der das Datum in Spalte B ein Sonntag ist, hellblau gefüllt werden. Dazu muss die Formattierung aufgrund einer Formel erfolgen. Gehe wie folgt vor:

1. Markiere den Bereich A2 bis M9, wähle Format → Conditional formatting und klicke dann auf „+ Add another rule“.
2. Unter Formula rules wählst du Custom formula is.
3. Hier fügst du eine Formel ein, die eine Bedingung prüft. (Ein Beispiel für eine solche Formel ist =A2=4: Das Resultat ist TRUE, wenn der Wert in Zelle A2 eine 4 ist, sonst FALSE):
   1. Deine Formel beginnt mit dem Gleichheitszeichen =.
   2. Du benötigst die Funktion WEEKDAY(); dieser wird ein Datum übergeben und sie gibt den Wochentag dieses Datums als Zahl zurück: Für Sonntag eine 1, für Samstag eine 7.
   3. Erstelle nun die Formel, die prüft, ob das Datum in B2 ein Sonntag ist und beachte:
      1. Diese Formel wird auf jede Zelle im markierten Bereich angewendet. Die Formel soll zwar immer auf Spalte B schauen, aber nur auf die aktuelle Zeile.
      2. Das heisst, dass der Bezug auf die Spalte absolut sein muss, der Bezug auf die Zeile aber relativ.
4. Ändere unter Formatting style die Füllfarbe auf hellblau und klicke auf Done.
5. Wiederhole obige Schritte für Samstag und die Füllfarbe rosa.