Einführung

1.Grundlagen - Zahlenqualitäten

Aufgaben

Beantworten Sie folgende Fragen:
  • Erklären sie in Ihrem Codebuch, die folgenden Lernschrittinhalte!
    • Tabellen einfügen, umbenennen, löschen.
    • Formeln, Zahlenqualitäten (%, Währung, Datum/Zeit) eingeben, kopieren, einfügen
    • Zeilen/Spalten markieren, löschen, einfügen, Navigieren mit Strg-Taste und Maus bzw. Richtungstasten (Pfeiltasten).
    • Format-Einstellungen, Formatschablone, Anzeige Nullwerte, Rundungsfehlerbehandlung
  • Markiertechniken

Übung

Bearbeiten sie die Datei in den Unterlagen

  • Rechtklick in Tabellenregister im Fenster unten, Register Start Zellen
  • Es gibt nur Währung z.B. €,$ und % Werte - alle anderen Zahlenqualitäten müssen als Format(schablone) ausgezeichnet werden - Datum wird erkannt an __.__.__ oder Zeit __:__
  • Kopieren als Copy/Paste oder über Knopf der aktiven Zelle
  • Navigieren: Pfeiltasten - Markieren Umschalt Pfeiltasten - Schnellnavi/Schnellmarker Strg+(Umschalt)+Pfeiltasten
  • Formatieren
    • Register Start Zahl oder Rechtsklick im Kontextmenü
    • Formatschablone
      positive Zahlenschablone ; negative Zahlenschablone ; Null Schablone
      #.## 0,000 "kg";[rot] #.##-0.000 "kg" ; ""
    • #.## Tausendertrennung, "kg" Zahlenqualitätsanzeige (immer eingefasst in "), [rot] Farbe
    • Das Prozentzeichen % ist ein Operator für die Division durch 100 (/100) Ein Prozentformat gleicht den %-Operator durch eine Multiplikation mit 100 aus!
  • Markiertechniken: Zellenblöcke C2:H5;
    unzusammenhängende Blöcke C2:C9 Strg B2:B9

2.Tabellenkalkulationsformeln erstellen

Lernschritt

Unterlagen

Aufgabe 1

  • Setzen sie das im Lernschritt gezeigte Tabellenmodell zur Berechnung von Mischungen aus 2 Komponenten (Lösungen) um!
    Erweitern sie das Modell zur Berechnung von Mischungen mit drei Komponenten.
    Kopieren sie die Tabellenvorlage in ihre Tabellenkalkulation. Wenn sie Hinweise benötigen, halten sie den Mauszeiger auf die Zelle deren Formel sie sehen möchten!

Aufgabe 2

  • Bearbeiten sie in den Unterlagen den Lernpfad
    Mischungsformel Programmierung und Anwendung
    Entwickeln sie ein Arbeitsblatt, wo in Spalte B die Eingabewerte eingegeben werden und der zu berechnende Wert als Fragezeichen die Berechnung in Spalte C auslöst.
  • k = Konzentration (k1,k2,k3)
  • m = Menge (m1,m2,m3)
  • km = Konzentration Mischung

Umsetzung der Mischungsformel mit 2 oder 3 Komponenten

Welche Menge einer 25%igen Lösung 3 müssen sie zugeben um die Mischung auf 10% einzustellen?

3.Prozentrechnen Formeln erstellen

Aufgabe

  • Setzen sie das im Lernschritt gezeigte Tabellenmodell zur Berechnung von Prozentaufgaben um.
    Alle Aufgaben, die auf Prozentrechnung basieren sind in einem Block beginnend mit dem Grundwert 100% als lesbarer Satz über 3 Zeilen anzulegen
  • gleiche Zahlenqualitäten in Spalten
  • Lernen sie die verwendeten Formeln in einem Prozentrechenblock auswendig!
In den Unterlagen finden sie als Unterstützung einen Lernpfad auf den Seiten 1 und 2 der die Aufgabenstellung kommentiert und umsetzt.

Hinweise

Untersuchen und programmieren sie die Formeln zur Berechnung des Salzgehaltes (SG) im Kochschinken:
  • Vergeben sie Namen für A14:=LK, D14:=SK und A17:=KV!
  • Formel Formel Formel Formel Formel
  • Grundwert Prozentwert Prozentsatz
  • Mengen-Betrachtung:
    257,1 kg Kochschinken enthalten 5,25 kg Salz SG=
  • Prozent-Betrachtung:
    102,85%(kg) Kochschinken enthalten 2,1%(kg) Salz SG =
  • Machen sie sich mit dem Rechenweg vertraut bei Prozentrechenprozessen in den Formeln nur mit Prozentwerten zu rechnen und auf Zwischenergebnisse, wie z.B. Prozent-Mengen, zu verzichten
  • Worin besteht der Unterschied in den aufgezeigten Rechenwegen?
  • Formulieren sie eine Formel, die den Salzgehalt im Produkt nur mit den per
    Namen benannten Parametern berechnet.

  • Für den Prozess Rohwurst berechnen sie den Fettgehalt des Endproduktes!
  • Formel Formel Formel Formel
  • Entwickeln sie eine Mengenformel E30 indem sie die Fettmengen berechnen und die Endproduktmenge ermitteln.
  • Entwickeln sie auch eine Formel D30 die sich ausschließlich auf Prozentwerte bezieht!
  • Geben sie eine Formel für die Berechnung des Fettgehaltes an E31, die ohne Zwischenergebnisse auskommt:


  • Stellen sie die Formel so um, dass sie die Speckzugabe berechnet entsprechend des in C31 vorgegebenen Fettgehalts (35%) im Endprodukt. Je nach Eingabe eines Fettgehaltes in C31 soll die dafür notwendige Speckzugabe SZ berechnet werden:

Übung

Bearbeiten sie die Aufgaben in Tabelle Prozent Übung!
Entwickeln sie eine Tabellenstruktur, die die Leserichtung einhält.
Gleiche Daten in Zeilen oder Spalten einordnen
Prozentaufgaben nach Prozentwert = Grundwert * Prozentsatz durchsuchen und zuordnen.
Für Mischungen ggf. ein Mischungskreuz aufstellen.
In Tabelle Mengen-Prozent finden Sie eine Musterlösung!
Über den Schalter 'Nächster Schritt' wird ein Rechenmodell schrittweise erstellt.
Dazu muss die Ausführung von Makros zugelassen werden
(Rücksprache entsprechend Programmversion)

Erstellen Sie Prozesstabellen zur Lösung der Aufgaben 1,4,5,6



Ergebnis
Formeln

Register Formel | Formelüberwachung | Formeln anzeigen

4.Absolute und relative Adressen anwenden

Aufgaben

In diesem Lernschritt bauen sie einen Tilgungsplan für ein Darlehen auf.
  • Darlehenssumme 100.000,-€
  • der Zinssatz wurde zu 4,5% vereinbart
  • Das Darlehen zahlen sie mit einer monatlichen Rate von 1870,-€ zurück
  • Laufzeit: Wie lange dauert die Rückzahlung?
  • Die Tilgung soll so berechnet werden, dass die Laufzeit ohne über- oder Unterzahlung endet
  • Variieren sie die Eingabewerte und stellen sie eigene Überlegungen zur Anwendung der Tilgungstabelle auf.
  • Wie könnte die Funktion ZÄHLENWENN die Wenn-Abfragen in Spalte A ersetzen?
Absolute Adressen enthalten Fixis $ für Zeile und oder Spalte.
Die Verwendung von Fixis müssen sie vor einem Kopiervorgang abklären.
WENN-Funktion Beispiele und Übungen
Bearbeiten sie die Lernschritt Index Wenn-Funktion . Einführung Wenn-Abfragen programmieren und anwenden an Beispielen! Die im Lernschritt kurz gezeigten Funktionen INDEX bzw. ZÄHLENWENN werden im nächsten Lernpfad-Kapitel ausführlich besprochen. Informieren sie sich über die folgenden Artikel der Office-Hilfe!

Die Übung XLS 'Absolute Adressen'
benötigt eine Makrofreigabe zur Ergebniskontrolle







In den folg. Formeln zur Tilgungstabelle finden absolute Adressen (mit $-Zeichen) und relative Adressen Verwendung. Der Formelsatz von A12:D12 wurde z.B. nach A13:D13 kopiert. Genauer über eine Zeile nach unten kopiert. Dabei kann man unschwer feststellen, dass die Zellenadressen OHNE $-Zeichen verändert wurden:
aus A11 > A12, aus B11 > B13, aus C11 > C12…

   A B C D
1 Tilgungsplan      
3 Darlehen 100.000,00 €    
4 Zinssatz 4,50%    
5 Rate 2.000,00 €    
6 Laufzeit 56 4 Jahre 8 Monate
7 Laufzeitende 120 -143160,02  
10 Monat Restschuld Tilgung Zinsen
11 1 100.000,00 € 1.495,00 € 375,00 €
11 1 =B3 =$B$5-D11 =B11*$B$4/12
12 =A11+1 =B11-C11 =$B$5-D12 =B12*$B$4/12
13 =A12+1 =B12-C12 =$B$5-D13 =B13*$B$4/12
... Wenn Restschuld > 0 1 Monat weiterzählen Stopp zählen
99 =WENN(B99>0  ; A98+1 ; 0 ) DANN SONST

Allgemein sind Zellenangaben relativ, d.h. in Zelle A12 bedeutet die Formel
[A12]=A11+1
→ nimm den Wert der Zelle eine Zeile darüber und addiere eine Eins
.
Wenn man diese Formel also nach, sagen wir, G8 kopiert, dann erhält man die Formel [G8]=G7+1. Eben wieder die Aussage: addiere auf die Zelle eine Zeile darüber eine Eins.

Im der Formel =$B$5-D11 ist die Angabe $B$5 absolut, d.h. wohin man diese Formel auch kopiert der Bezug auf B5 wird nicht verändert – er bleibt absolut immer auf der gleichen Zeile und Spalte. Streng genommen würde die Formel =B$5-D11 den gleichen Zweck erfüllen, weil die Formel nur über die Zeile kopiert wird – was ein Anpassung der Zeilennummer nach sich ziehen würde und die Spalte keine Anpassung erfährt.

Bei der Konstruktion von Formeln ist also zu überlegen, ob die Formel kopiert werden soll:

  • Ja, dann sind absolute Adressen als solche zu Kennzeichnen.
    Die Zeile und oder die Spalte sind mit einem $-Zeichen festzusetzen - Tastenschalter F4.

  • Nein, die Formel kann direkt ohne absolute Adressen formuliert werden
  • Betrachten und vergleichen sie die folgenden Kopien der Formel D11:
Tastenfolge Fixis anbringen
| =A12*B4 [F4] ● =A12*$B$4 [F4] ● =A12*B$4 [F4] ● =A12*$B4 [F4] ● =A12*B4 |

5.Tabellenmodelle - Prozesstabellen


Funktionen SUMMEWENN SUMMENPRODUKT Beispiele und Übungen
Bearbeiten sie die Lernschritte SUMMEWENN und SUMMENPRODUKT.

F1 - Office Hilfe zur SUMMENPRODUKT-Funktion

F1 - Office Hilfe zur SUMMEWENN-Funktion

Microsoft Kurs: Die Funktion SUMMEWENN

Microsoft Kurs: ZÄHLENWENNS und SUMMEWENNS

' Markiere Prozess 1-Prozent-Summen-Spaltenbereich +STRG+
' Markiere Gehalt 1-Prozent-Spaltenbereich +STRG+
'....
' Markiere Prozess n -Prozent-Summen-Spaltenbereich +STRG+
' Markiere Gehalt n -Prozent-Spaltenbereich +STRG+
' Markiere Zelle für Gehaltformel mit Namen
' Makroaufruf generiert die Namen für
' Summen-Formel sumP1...sumPn und
' Summenprodukt-Formel spdP1X...spdPnX
' Name X aus Zelle über Prozess 1-Prozent-Spaltenbereich

Viele Rezepturmodelle setzen sich aus einer Abfolge von Prozessschritten zusammen.
Jeder Prozessschritt beginnt mit 100% und es wird entweder ein Zuschlag oder Abschlag verrechnet!
Produkt = Material * (Prozesszuschlag) *...* (Prozesszuschlag) * ( Prozessabschlag)

Merkbuch Prozesstabellen

Protokoll des eingesetzten Formelsatzes (halte Mauszeiger auf gewünschte Formel)!
Prozessfaktor = SUMME(B2:B9)*SUMME(B11:B17)*SUMME(B19:B20)

Fettgehalt (wenn nur Prozess Fleischsortierung Fett enthält)
Fettgehalt = SUMMENPRODUKT(B2:B9;E2:E9)/Prozessfaktor

Fettgehalt (wenn die Prozesse Fleischsortierung UND Brätherstellung Fett enthalten)
Fettgehalt = (SUMMENPRODUKT(B2:B9;E2:E9) + SUMME(B2:B9)*SUMMENPRODUKT(B11:B17;E11:E17))/Prozessfaktor
Oder kürzer mit den entsprechenden Namen im Namensmanager:
Fettgehalt = (spdP1 + sumP1 * spdP2)/(sumP1 * sumP2 * sumP4)

Merkbuch Prozesstabellen

Formel-Fettgehalt

A B C D E F
1 Prozess Menge_kg Material Fett FettG
2 Fleischsortierung 100,00% 30,000 S1 6,00% 6,00%
3 33,33% 10,000 S4 18,00% 18,00%
4 80,00% 24,000 S9 60,00% 60,00%
5 33,33% 10,000 R2 6,00% 6,00%
6 0,000
7 246,67% 246,67% 74,000 Prozess 1 25,14%
8 Brätherstellung 100,00% 74,000 Fleisch/Fett 25,14%
9 33,78% 25,000 Eis
10 2,00% 1,480 Salz
11 0,50% 0,370 KHM
12 0,50% 0,370 Würzung
13 0,000 Käse 28,00% 28,00%
14 0,000
15 136,78% 136,78% 101,220 Prozess 2 18,38%
16 Abfüllung 100,00% 101,220 Brät
17 -5,00% -5,061 Abfüllverlust
18 95,00% 95,00% 96,159 Prozess 3 18,38%
19 Kochen/Räuchern 100,00% 96,159 Wurst 18,38%
20 -2,00% -1,923 Wasserverlust
21 98,00% 98,00% 94,236 Prozess 4 18,75% 18,75%
22 Produkt
23 Prozessfaktor  246,67% * 136,78% * 95,00% * 98,00%  314,12%  
24 Aus 30,0 kg S1 wird hergestellt 314,32 % Produkt = 94,236 kg 
25 Vorwärtskalkulation  S1 * Prozessfaktor = Produkt          (30*314,12% = 94,236)
26 Rückkalkulation  S1 = Produkt / Prozessfaktor            (30 = 94,236 /314,12%)
27 Produkt
       
28 Herzustellen 94,236 kg Produkt benötigt S1 30,000 kg
29 Fettgehalt 18,75%


Übung Prozesstabellen
Übung Rechenmodelle Aufgabe 1.3 (Geflügelsalat)

Übung Rechenmodelle Aufgabe 1.5 (Pflaumenmus)

6.Mit Datum und Zeit rechnen

Aufgaben

Datumsangaben sind in einer Tabellenkalkulation ein ganz eigenes Kapitel.
Die Grundeinheit von Datum ist 1 Tag und die Zählung beginnt, aus
welchen Gründen auch immer, am 1.1.1900!

Beantworten Sie folgende Fragen zum Lernschrittvideo:
  • Umrechnungsformel von Excelzeit in Dezimalzeit?
  • Umrechnungsformel von Dezimalzeit in Excelzeit?
  • Welche Funktionen zum Rechnen mit Datum-/Zeitangaben?
  • Welche Formatschablonen für Datum-/Zeitangaben und wie werden sie angewendet?

Physikaufgabe

  • Zug B verlässt Stadt B um 10:00 und fährt mit 80km/h nach A.
    Zug A verlässt Stadt A um 12:00 und fährt mit 120km/h nach B.
    Wann und nach zurückgelegten welchen Strecken treffen sich die Züge.
  • Ein Auto B fährt um 12:00 mit 80km/h los und ein Wagen A folgt um 12:30
    mit 120km/h. Wann holt Fahrzeug A das Fahrzeug B ein?

Übung Kalender (Bedingte Formatierung)

Ostertermin und Feiertage berechnen
Monats-Kalender erstellen
Gestaltungsoptionen:
Sonn- und Feiertage bzw. Ferientage farblich hervorheben, Datumsformate

F1 - Office Hilfe Datums- und Uhrzeitfunktionen (Referenz)

F1 - Office Hilfe Bedingte-Formatierung

Beispiel Eingabe Formatschablone Formel
Dezimalzeit 6,5 Standard Dezimalzeit = Excelzeit*24
Excelzeit 6:30 hh:mm Excelzeit = Dezimalzeit/24
Zusammentreffen Zug A Weg A+ Weg B = 400 Zug B
v va=120 km/h Entfernung vb=80 km/h
Abfahrt ta=12:00 sab=400 tb=10:00
Uhrzeit Treffpunkt =t+ta =tb+sb/vb/24
Fahrzeit Treffpunkt t=(24*vb*(tb-ta)+sab)/(24*(va+vb)) =sb/vb/24
km Treffpunkt sa=va*t*24 sb=vb*(t+(ta-tb))*24
Formel Zug va*t*24 + vb*(t+(ta-tb))*24 = 400 Weg A + Weg B = 400 Berechne t
Formel Auto va*t*24 = vb*(t+(ta-tb))*24 Weg A = Weg B Berechne t
Merkbuch zur bedingten Formatierung

7.Diagramme erstellen

Aufgaben

Erstellen sie das im Lernschritt-Video dokumentierte Diagramm
Fragen:
  • Mit markierten Datenreihen den Diagrammtyp auswählen
  • Welche bearbeitbaren Diagrammbestandteile hat ein Diagramm und wie wählen sie die ggf. aus?
  • Wie stellen sie eine zweite (sekundäre) Diagrammachse ein?
  • Wie ändern sie die Datenreihen: hinzufügen, entfernen?
  • Bearbeiten sie die Beispiel Daten in der Unterlagen-Mappe!

Tipp

Wenn sie ein Diagramm aus zwei Datenreihen mit x-Achse und y-Achse erstellen wollen, starten sie mit einem x-y-Diagramm - das ordnet automatisch die erste Datenreihe der x-Achse zu. Ändern anschließend den Diagrammtyp auf die gewünschte Diagramm-Form ab.

  • Erstellen sie das gezeigte Diagramm - setzen sie dabei folgende Elemente, möglichst nahe am Vorbild, um
  • Entnehmen sie alle benötigten Angaben der Abbildung
  • Einteilung der y-Achse: Beginnt bei 4 endet bei 9
  • Einteilung der x-Achse: Uhrzeit beginnt bei 9:00 endet 14:30
  • Beschriftung der Datenreihenpunkte
  • Datenreihenlinie blau mit Datenpunkten und leuchten gelb
  • Tabelle in eine Tabellenmappe kopieren
  • Spalte E F-Wert berechnen (Namensmanager t=B8:B29)
  • Spalte F F-Wert Summe berechnen (Namensmanager Ti=D8:D29)
  • Diagramm erstellen aus Daten Spalte Soll(blau), Ist(rot), F-Wert-Summe(grün - F-Wert auf sekundärer Achse), optische Vorgaben siehe Abbildung
  • Formeln durch (An)Halten des Mauszeigers
Hinweise: Bedeutung des F-Wertes - Dokumentation des Tabellenaufbaus
Prozessablauf D70 = 3 min für F=36 min bei z=10°C
B C D E F
4     Wertigkeit
5   Soll Ist min bei 70 °C F-Wert
6 t Ts Ti DF  
7 0,00 20 °C 20,0 °C dt*10^((Ti-70)/z)  
8 5,00 70 °C 34,4 °C 0,0014 0,0014
9 10,00 70 °C 43,3 °C 0,0107 0,0121
10 15,00 70 °C 50,0 °C 0,0500 0,0621
11 20,00 70 °C 53,7 °C 0,1172 0,1793
12 25,00 70 °C 60,0 °C 0,5000 0,6793
13 30,00 70 °C 63,0 °C 0,9976 1,6769
14 35,00 70 °C 66,5 °C 2,2334 3,9103
15 40,00 70 °C 69,2 °C 4,1588 8,0691
16 45,00 20 °C 70,0 °C 5,0000 13,0691
17 50,00 20 °C 70,0 °C 5,0000 18,0691
18 55,00 20 °C 70,0 °C 5,0000 23,0691
19 60,00 20 °C 70,0 °C 5,0000 28,0691
20 65,00 20 °C 70,0 °C 5,0000 33,0691
21 70,00 20 °C 69,6 °C 4,5601 37,6292
22 75,00 20 °C 60,7 °C 0,5874 38,2166
23 80,00 20 °C 44,7 °C 0,0148 38,2314
24 85,00 20 °C 32,8 °C 0,0010 38,2324
25 90,00 20 °C 26,0 °C 0,0002 38,2326
26 95,00 20 °C 20,0 °C 0,0001 38,2326
27 100,00 20 °C 20,0 °C 0,0001 38,2327
28 105,00 20 °C 20,0 °C 0,0001 38,2327
29 110,00 20 °C 20,0 °C 0,0001 38,2328

8.Übung - Komplexaufgabe

Übung Folienlänge berechnen

Geben sie eine Formel zur Berechnung der Lauflänge einer Folienrolle an. Betrachten sie die Flächenquerschnitte der Rolle und der abgerollten Folie:

Rollenaufgabe

Aufgabe

Dieses Kapitel bearbeiten sie selbständig. Es beinhaltet eine Arbeitsanweisung, zur Erstellung einer Anwendungsaufgabe. In den Unterlagen finden sie die Dokumentation, die ihnen die Programmierung der Aufgabe erläutert:
  • Aufbau der Tabellen
  • Arbeiten mit dem Namenmanager
  • Datenfluss: WENN, VERWEIS
  • Eingabekontrolle
  • Blatt schützen
Lesen sie die Arbeitsanweisungen, führen sie die gestellten Aufgaben durch und fragen sie ggf. nach, wenn sie nicht mehr weiter kommen...