LF05 - Technische Systeme automatisieren

Sie benötigen für den Unterricht in den Lernfeldern LF03 und LF05 einen aktuellen Laptop mit den folgenden Mindestanforderungen:

  • intel i5 oder höher
  • 8 GB RAM
  • 128 GB SSD + 500GB HDD
  • Windows 10
  • normaler Grafikchip (z.B. NVidea Gforce) reicht aus, keine CAD Grafikkarte


1 Portfolio

Erstellen Sie begleitend ein persönliches Portfolio, das zu jedem Kapitel folgende Punkte behandeln soll:

  • eine kurze Zusammenfassung des Themas mit eigenen Worten
  • ein anschauliches Beispiel als Screenshot, oder ähnliches
  • ein persönliches Resumé, in dem Sie auch über Ihre Schwierigkeiten und Ihre Lösungsstrategien schreiben

Die Abgabe erfolgt am Ende der Unterrichtsreihe (09.02.2019) als pdf-Datei per Mail an: s.goebel@gs-ldk.de

2 Tabellenkalkulation mit EXCEL

  • Excel 2013 Grundkurs von Andreas Thehos:
  1. Oberfläche und Menüs
  2. Speichern, Öffnen & Kompatibilität
  3. Eingabe, Korrektur und AutoAusfüllen
  4. Einfache Berechnungen und Formeln
  5. Drucken und Seitenlayout
  6. Einfache Funktionen - SUMME & Co.

2.1 Einfache Berechnungen und Gestaltung

  1. Erstellen Sie eine Tabelle in der das Datum, der Kilometerstand, die getankten Liter und der Preis pro Liter in den Spalten A bis D eingetragen werden.
    Berechnet werden sollen die gefahrenen Kilometer seit dem letzten Tanken, der Gesamtpreis des Tankens, der Verbrauch in Liter auf 100 Kilometer und die Kosten je 100 Kilometer in den Spalten E bis H.
    Gestalten Sie die Tabelle ansprechend und übersichtlich. Stellen Sie das Seitenlayout so ein, dass die Tabelle auf einer Seite im Querformat ausgedruckt werden kann.
    Entwerfen Sie weitere, sinnvolle Berechnungen.
    Speichern Sie Mappe unter dem Namen '1-1-Tankliste.xlsx' ab.
  2. Erstellen Sie eine Tabelle, in der das Drehmoment durch Eingabe einer senkrechten Kraft und des Abstands vom Drehpunkt berechnet werden kann. Visualisieren Sie die einzugebenden Größen mithilfe einer Grafik (EXCEL: Einfügen - Formen). Stellen Sie die Formel in einem Formelfeld und eine ausführliche Erläuterung in einem Textfeld dar.
    Erstellen Sie zwei weitere Eingabe- und Rechenbereiche, in denen die Unbekannten F und l berechnet werden können.
    Speichern Sie Mappe unter dem Namen '1-2-Drehmoment.xlsx' ab.
  3. In einer EXCEL-Arbeitsmappe soll der Versuch zur Zugfestigkeit aus Werkstoffkunde abgebildet werden (siehe Vorlage). Übernehmen Sie die Werte aus dem Versuch, die in den umrandeten Feldern eingetragen werden sollen. Die übrigen Werte werden berechnet. Hängen Sie die Einheiten über eine Benutzerdefinierte Formatierung an. Tragen Sie auch die Formeln in der Tabelle ein.
    Speichern Sie die Datei unter dem Namen '1-3-Zugversuch.xlsx' ab.
  4. (Optionale Aufgabe:) In einer EXCEL-Arbeitsmappe sollen auf dem ersten Tabellenblatt unterschiedliche Flächen (Rechteck, Parallelogramm, Trapez, rechtwinkliges Dreieck, Kreis und Ellipse) berechnet werden können. Benennen Sie das Tabellenblatt in 'Flächenberechnung' um.
    Benennen Sie das zweite Tabellenblatt in 'Volumenberechnung' um und stellen Sie in gleicher Form die Berechnungen von Volumen (Quader, Prisma, Pyramide mit quadratischer Grundfläche, Zylinder, Kegel und Kugel) dar.
    Visualisieren Sie die Fläche und Körper, beschriften die Größen, die zur Berechnung erforderlich sind und schreiben Sie die Formeln auf.
    Speichern Sie Mappe unter dem Namen '1-4-Flächen und Volumen.xlsx' ab.

2.2 Kopieren, Ausfüllen, Zellbezüge und Standardfunktionen

  1. Speichern Sie die Datei 2-1-Lohnliste.xlsx lokal auf Ihrem PC und öffnen Sie diese.
    Ermitteln Sie:
    1. den Grundlohn pro Monat in Euro (Monat mit vier Arbeitswochen annehmen)
    2. den Überstundenlohn in Euro
    3. den Gesamtlohn pro Monat
    4. Summe, Durchschnitt, Minimum und Maximum
    Formatieren Sie das Blatt, dass es auf einer Seite im Querformat ausgedruckt werden kann und speichern Sie Datei.
  2. Speichern Sie die Datei 2-2-Absolute_Bezuege.xlsx lokal auf Ihrem PC und öffnen Sie diese.
    Erstellen sie auf allen sieben Tabellenblättern Formeln in dafür vorgesehenen Zellen. Achten Sie darauf, dass die Formeln mithilfe der AutoAusfüllen-Funktion in die darunterliegenden Zellen übertragen werden können.
    Beschreiben Sie auf die notwendigen Schritte in einem Textfeld auf dem ersten Tabellenblatt und speichern Sie die Datei.
  3. Füllen Sie auf einem leeren Tabellenblatt die Zellen B1 - K1 und A2 - A11 mithilfe der AutoAusfüllen-Funktion mit den Zahlen 1 - 10.
    Geben Sie nur in der Zelle B2 eine Formel ein, die das Produkt aus A2 und B1 berechnet und sich durch AutoAusfüllen auf die anderen 99 Zellen übertragen lässt. Formatieren Sie die Tabelle nach Ihren Wünschen, achten Sie darauf, dass sie auf einer Seite ausgedruckt werden kann.
    Speichern Sie die Arbeitsmappe unter dem Namen '2-3-Einmaleins.xlsx' ab.
  4. Testen Sie Ihr Wissen: LearningApps

2.3 Berechnungen mit Matrixformeln

  1. Lösen Sie das lineare Gleichungssystem
    5a - b + 7c = 3
    3a + 2b + 5c = 4
    2a + 2b + 8c = 1
    mit drei Unbekannten indem Sie die Inverse der Matrix (=MINV(...)) aus den Faktoren der Unbekannten mit den Konstanten multiplizieren (=MMULT(...)). Markieren Sie vor der Formeleingabe die drei Zellen untereinander, die das Ergebnis enthalten sollen und schließen sie die Eingabe mit 'Strg-Umschalt-Enter' ab.
    Beschreiben Sie die notwendigen Schritte ausführlich ein einem Textfeld und formatieren Sie das Blatt übersichtlich.
    Speichern Sie die Datei unter dem Namen '3-1-lineareGleichungssysteme.xlsx' ab.
  2. Hängen Sie die Koeffizienten a, b, c über eine Benutzerdefinierte Formatierung an.
  3. Erstellen Sie weitere Vorlagen für zwei, vier und fünf Unbekannte.
    Speichern Sie Ihre Änderungen ab.
  4. In einem Kräftesystem wirken vier Kräfte auf einen Punkt. Bei zwei Kräften sind Betrag und Winkel gegeben, bei den beiden anderen Kräften nur der Winkel.
    Bestimmen Sie mit den Matrixfunktionen die Beträge der Kräfte.
  5. Erstellen Sie eine weitere Matrix, mit der ein unbekannter Winkel und eine unbekannte Kraft berechnet werden können.

2.4 Berechnungen mit mathematischen Funktionen

Zu Sinus und Cosinus siehe: [1]

  1. Öffnen Sie die Datei '1-2-Drehmoment.xlsx' und fügen Sie ein neues Tabellenblatt hinzu. Auf dem zweiten Blatt soll das Drehmoment bei nicht senkrechten Kräften berechnet werden. Hierzu ist die Eingabe eines Winkels in Grad erforderlich. Zeigen Sie neben dem Drehmoment auch die beiden Komponenten der Kraft an. Passen Sie die Grafik entsprechend an.
    Beachten Sie, dass die Winkelfunktionen von EXCEL einen Winkel im Bogenmaß erwartet und dieser umgerechnet oder umgewandelt werden muss.
    Speichern Sie die Arbeitsmappe unter dem Namen '4-1-Drehmoment.xlsx' ab.
  2. In einer neuen Arbeitsmappe sollen bis zehn Kräfte in Newton und deren Winkel zur x-Achse in Grad eingegeben werden können. Hängen Sie die Einheiten über eine benutzerdefinierte Formatierung an. Jede einzelne Kraft muss anschließend mit den Winkelfunktionen in ihre x- und y-Anteile zerlegt werden, die anschließend aufsummiert werden. Hieraus werden dann der Betrag und der Winkel der resultierenden Kraft berechnet.
    Speichern Sie die Arbeitsmappe unter dem Namen '4-2-ResultierendeKraft.xlsx' ab.

2.5 Bedingte Verzweigung

Video zur Erklärung der WENN-Funktion von Andreas Thehos

  1. Speichern Sie die Datei 5-1-Lohnberechnung.xlsx lokal auf Ihrem PC und öffnen Sie diese.
    Ermitteln Sie die folgenden Werte:
    • in der Zelle I4 mithilfe einer Funktion das aktuelle Datum
    • mithilfe der WENN-Funktion den Lohn pro Stück, abhängig von der Anzahl der fehlerfrei gefertigten Teile
    • und die Qualitätsprämie in Abhängigkeit der fehlerhaft produzierten Teile
    • den gesamten Monatslohn. Hierbei werden fehlerhaft produzierte Teile nicht vergütet
    • markieren Sie die Mitarbeiter mit einem 'X', deren Monatslohn über dem Durchschnitt liegt
    • markieren Sie die Mitarbeiter mit einem 'X', die den höchsten Monatslohn bekommen oder die wenigsten fehlerhaften Teile produziert haben
    • in den Zeilen 23 und 24 die geforderten Werte mit geeigneten Funktionen berechnen
    Formatieren Sie das Seitenlayout so, dass die Tabelle auf einer Seite im Querformat ausgedruckt werden kann.
    Speichern Sie Ihre Änderungen ab.
  2. Speichern Sie die Datei 5-2-Wochenlohn.xlsx lokal auf Ihrem PC und öffnen Sie diese.
    Ermitteln Sie die folgenden Werte:
    • in der Zelle H4 mithilfe einer Funktion das aktuelle Datum und die aktuelle Uhrzeit
    • mithilfe der WENNS-Funktion aus der Mitarbeiternummer den passenden Mitarbeiternamen. Bei Fehleingabe soll eine Fehlermeldung angezeigt werden (sollte die WENNS-Funktion nicht zur Verfügung stehen, verwenden Sie die geschachtelte WENN-Funktion)
    • in gleicher Weise den Lohn pro Stück und die Qualitätsprämie
    • den gesamten Wochenlohn. Hierbei werden fehlerhaft produzierte Teile nicht vergütet
    • Mitarbeiter mit einem überdurchschnittlichen Wochenlohn sollen mit einem 'X' gekennzeichnet werden.
    Formatieren alle Währungsangaben in Euro mit zwei Nachkommastellen und das Seitenlayout so, dass die Tabelle auf einer Seite im Querformat ausgedruckt werden kann.
    Speichern Sie Ihre Änderungen ab.
  3. Öffnen Sie die Arbeitsmappe '4-2-ResultierendeKraft.xlsx'. Prüfen Sie vor der Berechnung der beiden Teilkräfte, ob der eingetragene Winkel kleiner oder gleich 360° ist. Für den Fall, dass der Winkel größer ist, geben Sie statt der Berechnung den Text 'Winkel falsch!' aus.
    Erweitern Sie anschließend die Abfrage und prüfen Sie zusätzlich, ob der eingetragene Winkel auch größer oder gleich 0 ist.
    Speichern Sie die Arbeitsmappe unter dem Namen '5-3-ResultierendeKraft.xlsx' ab.
  4. Öffnen Sie die Arbeitsmappe '5-3-ResultierendeKraft.xlsx'. Errechnen Sie abhängig von der x- und y-Komponente der resultierenden Kraft deren Winkel. Unterscheiden Sie hierbei mit einer geschachtelten WENN-Funktion die vier Fälle:
    • Fx ist positiv und Fy ist positiv, dann ist alpha gleich beta
    • Fx ist negativ und Fy ist positiv, dann ist alpha 180° minus beta
    • Fx ist negativ und Fy ist negativ, dann ist alpha 180° plus beta
    • Fx ist positiv und Fy ist negativ, dann ist alpha 360° minus beta
      Möglicher logischer Aufbau.
    Speichern Sie die Arbeitsmappe unter dem Namen '5-4-ResultierendeKraft.xlsx' ab.

2.6 Kombinierte Funktionen

  1. Erfassen Sie in den Spalten A bis C einer leeren Arbeitsmappe die Daten, Anfangs- und Endezeit für verschiedene Tätigkeiten. Rechnen Sie anschließend in Spalte D die Dauer für jede Tätigkeit aus und benennen Sie die Zellen D2 bis D50 mit 'Einzelzeiten'. Erfassen Sie dann in der Spalte E die Kostenstelle, auf die die Arbeitszeit gebucht werden soll und benennen Sie die Zellen E2 bis E50 mit 'Kostenstellen'.
    Summieren Sie dann die Zeiten getrennt für die Kostenstellen mithilfe der SUMMEWENN-Funktion. Verwenden Sie die Namen statt der Zellbezüge in der Funktion.
    Speichern Sie die Mappe unter dem Namen '6-1-Kostenstellen.xlsx' ab.

2.7 Suchfunktionen

  1. Öffnen Sie die Datei '5-2-Wochenlohn.xlsx'.
    Ermitteln Sie die folgenden Werte:
    • in Zelle B11 mithilfe der SVERWEIS-Funktion aus der Mitarbeiternummer den passenden Mitarbeiternamen.
    • Bei Fehleingabe der Mitarbeiternummer soll eine Fehlermeldung angezeigt werden (WENNNV-Funktion oder WENNFEHLER-Funktion)
    • in gleicher Weise in Zelle E11 den Lohn pro Stück und in F11 die Qualitätsprämie
    • die Funktionen sollen mit der AutoAusfüllen-Funktion auf die darunterliegenden Zellen übertragen werden.
    • die übrigen Berechnungen bleiben unverändert.
    Speichern Sie Ihre Änderungen unter dem Dateinamen '7-2-Wochenlohn.xlsx' ab.

2.8 Diagramme

  1. Speichern Sie die Datei 8-1-Diagramme.xlsx lokal auf Ihrem PC und öffnen Sie diese.
    Erstellen Sie zu den Daten auf den einzelnen Tabellenblättern sinnvolle Diagramme. Eine Zusammenstellung der Diagrammarten finden Sie in der Anleitung und der Seite Office Support.
  2. Speichern Sie die Datei 8-2-Bewegung.xlsx lokal auf Ihrem PC und öffnen Sie diese.
    Auf beiden Tabellenblättern befinden sich gemessene Weg-Zeit-Punkte.

2.9 Datums- und Zeitfunktionen

  1. Speichern Sie die Datei 9-1-Personal.xlsx lokal auf Ihrem PC und öffnen Sie diese.
    • Berechnen Sie in der Spalte D das Alter der Mitarbeiter. Nutzen Sie dazu die DATEDIF-Funktion.
    • Ermitteln Sie in der Spalte E das Datum des nächsten Geburtstages.
    • Berechnen Sie in der Spalte F die Anzahl der Tage bis zum nächsten Geburtstag. Hat ein Mitarbeiter heute Geburtstag, soll 0 angegeben werden.
    • Wenn die Anzahl der Tage in Spalte F kleiner oder gleich sieben ist, sollen diese mit Hilfe einer bedingten Formatierung farblich hervorgehoben werden.
    Speichern Sie Ihre Änderungen ab.

2.10 Pivot-Tabellen

  1. Speichern Sie die Datei 10-1-Pivot.xlsx lokal auf Ihrem PC und öffnen Sie diese.
    • Fassen Sie die Umsätze vom Tabellenblatt '1-Datenliste'
      • je Produkt mit einer Pivot-Tabelle zusammen und stellen Sie diese mit einem Diagramm grafisch dar.
      • je Produkt und gruppiert nach Herstellern für die einzelnen Regionen mit einer Pivot-Tabelle zusammen.
      • je Region mit einer Pivot-Tabelle zusammen mit der Möglichkeit zusätzlich nach Firma und Verkäufer zu filtern.
    • Finden Sie für die Tabellenblätter '2-Ergebnisliste' und '3-Datenliste' sinnvolle Zusammenfassungen und visualisiseren Sie diese wenn möglich.
    Speichern Sie Ihre Änderungen ab.