Gesamtbeispiel Kursname: ExcelGrundlagen Beispielname: Betriebskosten Abrechnung Voraussetzung(en): Grundlagen Excel Benötigte Datei(en): Übung_Betriebskosten.doc (Dieses Dokument) Ziel der Übung In diesem Beispiel werden folgende Funktionen von Excel geübt: Dateneingabe in Zellen Benutzerdefinierte Zahlenformate Formatieren von Zellen Erstellen von Diagrammen Erstellen von Formeln mit absolutem Bezug Kopieren / Ausfüllen von Formeln Tabellenblatt kopieren, benennen Seiten einrichten und drucken Beschreibung der Aufgabe ***Achtung***Achtung***Achtung***Achtung***Achtung***Achtung***Achtung***Achtung*** Zur Erinnerung: Absolute Zellbezüge stellen Sie in einer Formel mit der Taste „F4“ her, es erscheint dann ein $-Zeichen vor der Zellbezeichnung!!! 1. Erstellen Sie folgende Tabelle in einer neuen leeren Arbeitsmappe. (Die Zellen mit dem Inhalt „Formel“ bleiben leer). Betriebskostenabrechnung 2010 Personenname Wohnungsgröße laut Vertrag Betriebskosten ohne Ust Maierhofer 45 Formel Haas 80 Formel Stein 90 Formel Fischer 110 Formel Gruber 90 Formel Gesamt Formel Formel Die Betriebskosten für 2010 im Hause "Sackgasse 12" betrugen: Betriebskosten 2010 Wasser 10000 Heizung 17000 © bit media e-Learning solution Seite 1 von 3 Strom 8500 Müll 5750 Summe Formel 2. Weisen Sie der Überschrift folgende Schriftformate zu: Arial Black, 12 Punkt, fett, kursiv, unterstrichen, Farbe blau. 3. Formatieren Sie den Text „Die Betriebskosten für 2010 im Hause ...“ mit dem Schriftformat: Times New Roman, 8pt. 4. Ändern Sie die Spaltenbreite der Spalten A, B und C so ab, dass alle 3 Spalten gleich breit sind und der lange Text „Die Betriebskosten für 2010 im Hause.. “ in diesen 3 Spalten Platz findet. 5. Bei den Spaltenüberschriften „Wohnungsgröße...“ und „Betriebskosten...“ soll der Text zentriert ausgerichtet sein und ein Zeilenumbruch erfolgen. 6. Berechnen Sie mit der Summenfunktion die gesamten Wohnungsgrößen und die Summe der Betriebskosten. 7. Berechnen Sie die Betriebskosten der Person „Maierhofer“ folgendermaßen: =Summe der Kosten / Gesamte Wohnungsgröße * Wohnungsgröße der Person (Beachten Sie, dass die Formel nach unten kopiert wird und es sich daher nicht nur um relative Zellbezüge handelt. Geben Sie die Formel mit den richtigen Zellbezügen und Bezugsarten in die Zelle ein. ) 8. Kopieren Sie die Formel nach unten, um alle Betriebskosten zu berechnen. 9. Die berechneten Betriebskosten und die Betriebskosten für 2010 sollen im Währungsformat € mit 2 Dezimalstellen dargestellt werden. 10. Für die Wohnungsgrößen ist ein benutzerdefiniertes Zahlenformat „m²“ zu erstellen und die Zahlen sind damit darzustellen (z.B.: 90 m²). – Wählen sie dazu ein „Benutzerdefiniertes Zahlenformat“ und geben Sie folgendes ein: 0 „m²“ ein! 11. Fügen Sie vor der Zeile „Betriebskosten 2010“ eine leere Zeile ein. 12. Fügen Sie vor der Person „Fischer“ eine neue Zeile ein, und geben Sie folgende Daten ein: (Die Formel kann von einer bestehenden hineinkopiert werden.) Maier 80 m² Formel 13. Tragen Sie neben der Spaltenüberschrift „Betriebskosten ohne Ust“ neue Überschriften ein: Spalte D „UST“ und Spalte E „Betriebskosten mit Ust“. 14. Die neuen Spaltenüberschriften sollen gleich den davor liegenden formatiert sein. 15. Berechnen Sie die „Ust“ und die “Betriebskosten mit Ust“ pro Person. (Für die „Ust“ Berechnung gilt: = Betriebskosten ohne Ust* 20%) 16. Die beiden Spalten sind ebenso mit dem Währungsformat € mit 2 Dezimalstellen zu formatieren und die Spaltenbreiten sind einzurichten. © bit media e-Learning solution Seite 2 von 3 17. Gestalten Sie die Überschrift des Tabellenblattes zentriert über der gesamten Liste. Die Listenüberschriften „Personenname, Wohnungsgröße...“ sowie „Betriebskosten 2010“ sollen fett formatiert werden. Ändern Sie, wenn nötig, die Spaltenbreiten. 18. Schreiben Sie in die Zelle E14 die Überschrift „Ust-Satz“ und in der Zelle darunter den Wert 20%. Zentrieren Sie den Eintrag in beiden Zellen. 19. Die „Ust“ Formel der Person „Maierhofer“ bleibt unverändert. Alle Ust-Formeln der weiteren Personen sollen so abgeändert werden, dass ein absoluter Zellbezug zum Ust-Betrag der Zelle E15 besteht. 20. Errechnen Sie jeweils die Gesamtsummen mit Hilfe der Summenfunktion. 21. Erstellen Sie ein gruppiertes zweidimensionales Säulendiagramm ohne Legende, in dem die „Betriebskosten mit Ust“ aller Personen dargestellt werden. Die Personennamen werden natürlich als Achsenbeschriftung angezeigt. Positionieren Sie das Diagramm in voller Listenbreite darunter. 22. Erstellen Sie ein 3D Kreisdiagramm, welches die Aufteilung der Betriebskosten des Hauses „Sackgasse 12“ darstellt. Die Kostenfaktoren (Wasser, Heizung...) sollen als Beschriftung in der Legende dienen. Pro Kreissegment soll auch der %Wert als Datenbeschriftung angezeigt werden. Das Kreisdiagramm ist unter dem Säulendiagramm in selber Breite zu positionieren. 23. Im Säulendiagramm ist die Beschriftung der Größenachse so zu ändern, dass keine Dezimalstellen angezeigt werden. Zusätzlich sollen die Datenreihen mit einem Fülleffekt (Schwarzweiß kariertes Muster) gefüllt sein. 24. Rahmen Sie die Liste der „Personen und ihrer Wohnungsgrößen, Betriebskosten, UST...“ sowie die Liste der „Betriebskosten 2010 ,Wasser, Heizung....“ mit Linien außen und innen (alle Rahmenlinien) ein. 25. Kontrollieren Sie die Seitenansicht und positionieren Sie Ihre Daten und Diagramme um alle Informationen ideal auf einer Seite darzustellen. 26. In der Fußzeile sollen im linken Bereich Ihr Name (fett formatiert), in der Mitte der Text „gedruckt am:“ sowie das aktuelle Druckdatum und im rechten Bereich der Text „Seite“ sowie die aktuelle Seitenzahl angezeigt werden. 27. Benennen Sie das Tabellenblatt „Betriebskosten2010“. 28. Drucken Sie das Tabellenblatt „Betriebskosten 2010“ auf Ihrem Drucker aus. 29. Speichern Sie die Arbeitsmappe auf Ihrer Festplatte in einem (idealen) Ordner mit dem Namen „Betriebskosten Übung“ ab. (Sie sollten die Daten wiederfinden!!) © bit media e-Learning solution Seite 3 von 3