zur Verfügung stehende Dateien Pruefungsdateien

77
76
75
74
73
72
71
70
69
68
67
66
65
64
63
62
61
60
59
58
57
56
55
54
53
52
51
50
49
48
47
46
45
44
43
42
41
40
39
38
37
36
35
34
33
32
31
30
29
28
27
26
25
24
23
22
21
20
19
18
17
16
15
14
13
12
11
10
9
8
7
6
5
4
3
2
1
6.0
5.9
5.9
5.8
5.7
5.7
5.6
5.5
5.5
5.4
5.4
5.3
5.2
5.2
5.1
5.0
5.0
4.9
4.8
4.8
4.7
4.6
4.6
4.5
4.4
4.4
4.3
4.2
4.2
4.1
4.1
4.0
3.9
3.9
3.8
3.7
3.7
3.6
3.5
3.5
3.4
3.3
3.3
3.2
3.1
3.1
3.0
2.9
2.9
2.8
2.8
2.7
2.6
2.6
2.5
2.4
2.4
2.3
2.2
2.2
2.1
2.0
2.0
1.9
1.8
1.8
1.7
1.6
1.6
1.5
1.5
1.4
1.3
1.3
1.2
1.1
1.1
Prüfung Excel, Seiten 86–203, Lehrmittel EDUGUIDER Excel 2013
Name
.......................................................... Note .......................................
Vorname......................................................... Punkte .................von 77 Pkt.
zur Verfügung stehende Dateien
4 Dateien von Ihnen zu speichern
Lösungen für Lehrpersonen
1.
Pruefungsdateien Excel.xlsx
Runden.xlsx
Netto.xlss
Zinstabelle.xlsx
Name_Vorname_Pruefungsdateien Excel.xlsx
Name_Vorname_Runden.xlsx
Name_Vorname_Netto.xlsx
Name_Vorname_Zinstabelle.xlsx
als ausgeblendeter Text hinterlegt
Erklären Sie folgende drei Begriffe (Rechnen mit Prozenten):
3 Punkte
Grundwert
Prozentsatz
Prozentwert
2.
Wie heisst die Formel, wenn ich Text, welcher in 2 verschiedenen Zellen liegt,
verknüpfen will? Z. B. A1: Peter A2: Müller
2 Punkte
______________________________________________________________________________________
3.
Bei den "Wenn-Funktionen" kann auch nach Texten gefragt werden.
Welche Zeichen muss ich setzen, wenn in der Zeile Text angezeigt werden soll?
2 Punkte
______________________________________________________________________________________
5.
Öffnen Sie die Exceldatei Pruefungsdateien Excel.xlsx.

(10 Punkte)
Tabellenblatt Funk Tab A: Zentrieren Sie den Titel, unterlegen Sie ihn gelb und
vergrössern Sie die Schrift auf 12 pt. Formatieren Sie alle Zellen wie unten dargestellt
sind.
Seite 1 von 5

Tabellenblatt Funk Tab B: Ändern Sie die Farbe der blauen Zellen in gelb. Berechnen Sie
diese Zellen nun, indem Sie die richtigen Formeln einsetzen.

Tabellenblatt Funk Tab C: Richten Sie dieses Tabellenblatt so ein, dass es mit den
Spalten- und Zeilenüberschriften sowie Gitternetzlinien ausgedruckt würde. Drucken Sie
es aber nicht aus! Schauen Sie sich die Seite auf der Seitenansicht an. Berechnen Sie
dann alle blauen Zellen.

Tabellenblatt Funk Tab D: Berechnen Sie alle blauen und gelben Zellen. Speichern Sie
diese Tabellenblatt nun so ab, dass auf dem Bildschirm weder die Gitternetzlinien noch
die Zeilen- und Spaltenüberschriften zu sehen sind.

Tabellenblatt Funk Tab E: Hier müssen Sie auf den 5er runden! Berechnen Sie alle blauen
Zellen.
Speichern Sie die Datei ab mit: Name_Vorname_Pruefungsdateien Excel.xlsx.
6.
Öffnen Sie die Exceldatei Runden.xlsx. und lösen Sie die folgenden Aufgaben: (10 Punkte)
 Tabellenblatt Werte: Berechnen Sie die blauen Zellen.
 Tabellenblatt Prüfungen: Berechnen Sie die blauen Zellen.
 Tabellenblatt Büromöbel: Lösen Sie diese Aufgaben, in dem Sie am richtigen Ort die
Bezüge absolut setzen.
 Tabellenblatt Wette: Berechnen Sie die blauen Zellen.
 Tabellenblatt Löhne: Berechnen Sie die Monatslöhne in Prozenten, auf 5 Rappen,
10 Rappen und auf Franken gerundet sowie auf ganze Franken aufgerundet.
 Tabellenblatt Noten: Übertragen Sie die Mittelwerte der Tabellen Informatik, Deutsch,
Englisch, Französisch und Geschichte auf halbe Noten gerundet auf dieses Tabellenblatt.
Berechnen Sie auch den Ø für jede/n Schüler/in aus.
 Fügen Sie bei den Tabellenblättern Werte, Prüfungen, Büromöbel und Wette gleichzeitig
oberhalb der Zeile 1 eine neue Zeile ein und schreiben Sie in die Zelle A1: eingefügte
Zeile. Die Zeilenhöhe soll 31.50 pt. sein, die Schriftgrösse 15 und fett. Verbinden Sie nun
die Zellen A1 – C1; Zentrieren Sie den Titel sowohl vertikal wie horizontal.
 Tabellenblatt Bonus: Berechnen Sie den Bonus in % und in CHF aus.
 Tabellenblatt Umsätze: Hier müssen Sie die WENN Funktion verschachtelt einsetzen.
Speichern Sie die Tabelle ab mit: Name_Vorname_Runden.xlsx
Seite 2 von 5
7.
Kleine Buchhaltung berechnen
(10 Punkte)
 Öffnen Sie die Tabelle Kleine Buchhaltung Ihrer Datei Name_ Vorname_Prüfungsdateien.
 Berechnen Sie in E6 den Betrag aus Anzahl und Einzelpreis und kopieren Sie die Formel
nach unten bis zur Zelle E30.
 In H1 wird der 17.10.2010 angezeigt. Dieses Datum soll für dieses Beispiel das aktuelle
Datum sein. In der Spalte G offen sollen nur die Beträge angezeigt werden, die noch
nicht bezahlt sind. Tipp: Benutzen Sie die Wenn-Funktion. Wenn in der linken Nachbarzelle
kein Bezahl-Datum eingetragen ist, also leer ist (F6=““), soll der Betrag aus Spalte E
eingetragen werden, sonst der Text ok.
 Kopieren Sie die Formel bis zur Zelle G30.
 In der Spalte H fällig sollen nur die Beträge angezeigt werden, die 30 Tage nach
Rechnungsstellung noch nicht bezahlt sind. Tipp: Benutzen Sie eine verschachtelte WennFunktion. Wenn in der Spalte G (offen) ok eingetragen wurde (G6=“ok“), so soll kein
Betrag erscheinen (““). Wenn kein ok eingetragen ist und die Differenz zwischen
aktuellem Datum und Rechnungsdatum größer 30 ist, soll der Betrag aus Spalte E
eingetragen werden, sonst nichts.
 Achten Sie vor dem Kopieren der Formel bis zu H30, ob Zellbezüge in dieser Formel
absolut genommen werden müssen.
 In der Spalte I Mahnung soll auf alle Rechnungen mit dem Eintrag Mahnen hingewiesen
werden, die schon länger als 14 Tage fällig sind. Tipp: Benutzen Sie eine verschachtelte
Wenn-Funktion. Beispielsweise so: Wenn in der Zelle der Spalte F ein Datum steht, also
ungleich leer ist (F6<>““), soll kein Hinweis erscheinen. Wenn in dieser Zelle jedoch noch
kein Datum eingetragen wurde und die Differenz zwischen aktuellem Datum und
Rechnungsdatum größer 44 ist, soll der Text Mahnen erscheinen.
 Um in G3 die Anzahl der zu schreibenden Mahnungen berechnen zu können, benötigen
wir in der Spalte J Anzahl Mahnungen immer dann eine 1, wenn in der linken
Nachbarzelle der Eintrag Mahnen erscheint, sonst nichts.
 Kopieren Sie die Formel bis zur Zelle J30.
 Geben Sie in B1 eine Formel ein, die alle eingetragenen und alle noch einzutragenden
Beträge bis zur Zelle E30 addiert.
 Geben Sie in B2 und B3 Formeln ein, die alle eingetragenen und alle noch
einzutragenden offenen und fälligen Beträge bis zur Zelle G30 bzw. H30 addiert.
Verfahren Sie mit der Anzahl der zu schreibenden Mahnungen in Zelle G3 entsprechend.
 Blenden Sie die Spalte J über das Kontextmenü aus.
 Schützen Sie alle Zellen mit Formeln. Markieren Sie dazu die Zellbereiche, die freigegeben
werden sollen, also A6:D30, und deaktivieren Sie dann in der Dialogbox Zellen
formatieren, Register Schutz das Kontrollkästchen Gesperrt. Schützen Sie dann im Register
Überprüfen das Blatt.
Speichern Sie die Datei nochmals ab.
Seite 3 von 5
8.
Öffnen Sie die Tabelle Autohaus Burger Ihrer Datei
Name_ Vorname_Püefungsdateien.xlsx
10 Punkte
Umsatz und Prämien sollen berechnet werden.
 Berechnen Sie die Stückzahlen für das 1. Quartal.
 Kopieren Sie die Tabelle Stück (A1 bis E5) in die Zellen A13 bis E17.
 Überschreiben Sie A13 mit Umsatz. Löschen Sie die Zahlen aus den Zellen B14:E17, und
berechnen Sie alle Monatsumsätze. Formatieren Sie die Umsätze ohne Dezimalstellen
und mit 1.000-Trennzeichen.
 Kopieren Sie die Tabelle Umsatz (A13:E17) nach A19:E23 und überschreiben Sie A19 mit
Prämie1.
 Entfernen Sie den Inhalt aus den Zellen B20:E23.
 Berechnen Sie die monatlichen Prämien, wenn für Umsätze, die grösser oder gleich
400.000 sind, eine Prämie von 500 Euro ausgeschüttet werden sollen.
 Hinweis: Um mit den Variablen Bedingung, Dann-Wert und Sonst-Wert „spielen“ zu
können, gibt man diese in Zellen ein und verwendet dann in der Formel absolute
Zellverweise.
Speichern Sie Ihre Datei nochmals ab.
4.
Statistik berechnen
10 Punkte
 Öffnen Sie die Tabelle Städteführer Statistik Ihrer Datei Name_
Vorname_Pruefungsdateien.xlsx.
 Berechnen Sie in D1 die Anzahl der verkauften Exemplare.
 Berechnen Sie in B5 bis B8 die Anzahl der verkauften Exemplare der verschiedenen
Städteführer. Verwenden Sie dazu die Funktion Summewenn mit den Argumenten
Bereich, Suchkriterium und Summenbereich, in dem sich die tatsächlich zu addierenden
Zahlen befinden: =Summewenn(Bereich; Suchkriterium; Summenbereich).
 Berechnen Sie in C5 bis C8 die Anzahl der Bestellungen der verschiedenen Städteführer.
Verwenden Sie dazu die Funktion Zählenwenn mit den Argumenten Bereich und
Suchkriterium: =Zählenwenn(Bereich; Suchkriterium).
 Berechnen Sie in E5 bis E8 die Anzahl der verkauften Exemplare von den verschiedenen
Verkäufern.
 Berechnen Sie in F5 bis F8 die Anzahl der Bestellungen bei den verschiedenen
Verkäufern.
 Berechnen Sie in E10 bis E14 die angegebenen statistischen Informationen.
Speichern Sie Ihre Datai nochmals ab.
Seite 4 von 5
Erstellen Sie ein Säulendiagramm
5.
10 Punkte
a) Öffnen die Mappe Netto.
b) Erstellen Sie aus der Tabelle ein Säulendiagramm.
c) Legen Sie als X-Achsenbeschriftung die Monatsnamen Januar bis März und
als Legende die Namen Schulze, Gärtner, Bergengrün und Lehmann fest.
d) Vergeben Sie als Diagrammtitel Umsatzentwicklung 1. Quartal 2010 und
als Beschriftung der Y-Achse in Euro.
e) Verändern Sie Grösse und Position des Diagramms entsprechend der Tabellenbreite.
f)
Formatieren Sie die Zahlen der y-Achse ohne Dezimalstellen.
g) Formatieren Sie die einzelnen Elemente des Diagramms nach eigenem Geschmack.
Die Lösung Speichern Sie mit Name Vorname Netto ab.
Umsatz 1. Quartal 2010
25,000
20,000
in TEuro
Schulze
15,000
Gärtner
Bergengrün
10,000
Lehmann
5,000
0
Januar
Februar
März
Seitenlayout einrichten
6.
10 Punkte
a) Öffnen Sie die Mappe Zinstabelle. Geben Sie für die Seitenränder oben, unten,
links und rechts jeweils 3 cm ein.
b) Erstellen Sie eine Kopfzeile. Linker Abschnitt: ETW Müllerstr. 18, Rechter Abschnitt:
Darlehen Deutsche Bank, Schrift: Times New Roman, 11pt, kursiv
c) Erstellen Sie eine Fusszeile. Linker Abschnitt: Dateiname, aktuelles Datum, Rechter
Abschnitt: Seitennummerierung von Gesamtseitenanzahl, Schrift: Times New Roman, 9pt,
kursiv.
d) Passen Sie den Ausdruck auf eine Seite breit und 5 Seiten hoch an.
e) Um Zeilenüberschriften auf jeder Seite auszudrucken, müssen Sie Drucktitel einrichten.
Bestimmen Sie die ersten sieben Zeilen als Drucktitel.
f)
Fügen Sie in der Spalte E einen vertikalen Seitenumbruch ein.
Speichern Sie die Zinstabelle mit Name_Vorname_Zinstabelle ab.
Seite 5 von 5