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