Ziel der Übung: Komplexe Formeln und Funktionen einsetzen und anwenden
Vorraussetzung: Speichern Sie zuerst die Ausgangsdatei im Ordner Eigene Dateien ab:
- Klicken Sie hierfür den Link der Ausgangsdatei mit der rechten Maustaste an
- klicken Sie anschließend auf Ziel speichern unter…
- wählen Sie dann einen Speicherordner z. B. Eigene Dateien
- und betätigen Sie dann die Schaltfläche Speichern.
Ausgangsdatei: Apfelernte-Ausgangsdatei.xls (19 KByte)
- Starten Sie Excel und stellen Sie wenn notwendig unter Ansicht/Symbolleisten/Anpassen/Optionen die Standard- und Formatsymbolleiste so ein, dass sie auf zwei Zeilen aufgeteilt sind.
- Öffnen Sie die Ausgangsdatei Apfelernte-Ausgangsdatei.xls.
- Öffnen Sie das Tabellenblatt Anlieferungen und fixieren Sie die erste Zeile, indem Sie die zweite Zeile markieren und mit Fenster/Fenster fixieren eine Fixierung einstellen.
- Markieren Sie die Spalte E und stellen Sie mit Format/Zellen/Zahlen/Benutzerdefiniert folgenden Formatcode: #.##0″ kg“ ein.
- Sortieren Sie die Tabelle aufsteigend nach Datum, indem Sie ein einziges Datum anklicken und dann Aufsteigend sortieren
wählen. - Öffnen Sie das Tabellenblatt Preis pro Sorte.
- Geben Sie in der Zelle C2 eine Formel ein, welche vom Tabellenblatt Anlieferungen die Mengen der Apfelsorte Gala zusammenzählt. Benutzen Sie hierfür die Funktion summewenn:
=summewenn(Anlieferungen!D:D;’Preis pro Sorte‘!A2;Anlieferungen!E:E)
Sie können auch den Funktionsassistenten verwenden. - Kopieren Sie die eben eingegeben Formel mit dem Formelkopierer bis in die Zeile 5 hinunter.
- Formatieren Sie den Zellbereich C2:C5 benutzerdefiniert auf kg (siehe Punkt 4).
- Geben Sie in der Zelle D2 eine Formel ein, welche den Gesamtpreis pro Sorte berechnet.
=B2*C2
Kopieren Sie diese Formel mit dem Formelkopierer bis in die Zeile 5 hinunter. - Öffnen Sie das Tabellenblatt Ertrag pro Hektar.
- Geben Sie in der Zelle C2 eine Formel ein, welche die Gesamtmenge der Apfelsorte Gala berechnet. Beziehen Sie sich dafür einfach auf die Menge im Tabellenblatt Preis pro Sort.
=’Preis pro Sorte‘!C2
Kopieren Sie diese Formel mit dem Formelkopierer bis in die Zeile 5 hinunter. - Übertragen Sie die Formatierung der Zelle C2 des Tabellenblattes Preis pro Sorte auf den Zellbereich C2:D5 des Tabellenblattes Ertrag pro Hektar. Gehen Sie dabei wie folgt vor: Klicken Sie das Tabelleblatt Preis pro Sorte an, klicken Sie die Zelle C2 an, klicken Sie Format übertragen
an, klicken Sie das Tabellenblatt Ertrag pro Hektar an und markieren Sie anschließend den Zellbereich C2:D5. - Geben Sie in der Zelle D2 des Tabellenblattes Ertrag pro Hektar eine Formel ein, welche den Ertrag pro Hektar errechnet.
=C2/B2*10000
Kopieren Sie diese Formel mit dem Formelkopierer bis in die Zeile 5 hinunter. - Tippen Sie in der Zelle E1 den Text Preis/ha ein und formatieren Sie die Zelle fett.
- Geben Sie in der Zelle E2 eine Formel ein, welche den Preis pro Hektar errechnet. Dafür beziehen Sie sich auf den Ertrag/ha und auf den Preis/kg des Tabellenblattes Preis pro Sorte.
=D2*’Preis pro Sorte‘!B2
Kopieren Sie diese Formel mit dem Formelkopierer bis in die Zeile 5 hinunter. - Geben Sie in der Zelle D6 eine Formel ein welche den Mittelwert der Erträge/ha ermittelt.
=mittelwert(D2:D5) - Kopieren Sie diese Formel mit dem Formelkopierer in die Zelle E6.
- Übertragen Sie das Format der Zelle D5 auf D6 und das Format der Zelle E5 auf E6.
- Verschieben Sie das Tabellenblatt Statistik ans Ende und arbeiten Sie darin weiter.
- Geben Sie in der Zelle B2 eine Formel ein welche das Startdatum der Ernte berechnet.
=min(Anlieferungen!A:A)
und formatieren Sie die Zelle B2 so, dass das Datum in der Form 20. August 2004 angezeigt wird. - Geben Sie in der Zelle B3 eine Formel ein welche das Enddatum der Ernte berechnet.
=max(Anlieferungen!A:A) - Übertragen Sie das Format der Zelle B2 auf B3 und verbreiten Sie anschließend, sofern notwendig, die Breite der Spalte B.
- Geben Sie in der Zelle B4 eine Formel ein welche die Anzahl der Lieferungen der Ernte berechnet.
=anzahl(Anlieferungen!B:B) - 25. Geben Sie in der Zelle B5 eine Formel ein welche die Anzahl der gelieferten Kisten der Ernte berechnet.
=summe(Anlieferungen!F:F) - Kopieren Sie den Zellbereich A1:E5 des Tabellenblattes Ertrag pro Hektar zur Zelle A7 des Tabellenblattes Statistik: Ertrag pro Hektar anklicken, Zellbereich A1:E5 markieren, rechte Maustaste kopieren, Statistik anklicken, Zelle A7 anklicken, rechte Maustaste einfügen.
- Löschen Sie die Inhalte des Zellbereiches D8:E11.
- Löschen Sie die Spalte C, indem Sie sie mit rechts anklicken und Zellen löschen wählen.
- Ändern Sie Ertrag/ha in Abweichung Durchschnittsertrag und Preis/ha in Abweichung Durchschnittspreis.
- Stellen Sie die Spalten C und D auf die optimale Breite ein (Doppelklick zwischen den Spaltenköpfen).
- Geben Sie in der Zelle C8 eine Formel ein welche die Abweichung vom Durchschnittsertrag berechnet. Achten Sie darauf dass die Formel anschließend mit dem Formelkopierer kopiert werden muss. Darum muss bezüglich des Durchschnittswert eine absoluter Bezug (F4) erstellt werden.
=’Ertrag pro Hektar‘!D2-‚Ertrag pro Hektar‘!$D$6 - Kopieren Sie die Formel bis in die Zelle C11 hinunter.
- Geben Sie auch in der Zelle D8 eine entsprechende Formel ein, wobei aber das Ergebnis auf null Dezimalstellen gerundet sein soll!
=runden(‚Ertrag pro Hektar‘!E2-‚Ertrag pro Hektar‘!$E$6;0)
und kopieren Sie die Formel bis in die Zelle D11 hinunter. - Geben Sie in der Zelle A13 den Text Bemerkung ein.
- Geben Sie in der Zelle B13 eine Formel ein, welche anhand der Prüfung Differenz des Enddatums und Startdatums größer als 75 Tage den Text Lange Erntedauer ausgibt ansonst den Text Normale Erntedauer ausgibt:
=wenn(B3-B2>75;“Lange Erntedauer“;“Normale Erntedauer“)
Beispiellösung: Apfelernte-Beispielloesung.xls (23KByte)