 |
Excel als Werkzeug Die Verwendung von Excel zur Herstellung von Präsentationen - Referat von PS-Trainer Excel als Werkzeug - Homepage von PS-Trainer - an PS-Trainer Aufgabe 6: Erstellen einer XY-Grafik Aufgabe - Hinweise - Lösungsvorschlag |
|
|
Excel als Werkzeug Die Verwendung von Excel zur Herstellung von Präsentationen - Referat von PS-Trainer Excel als Werkzeug - Homepage von PS-Trainer - an PS-Trainer Aufgabe 6: Erstellen einer XY-Grafik Aufgabe - Hinweise - Lösungsvorschlag |
|
| Ziel dieser Übung ist die Erstellung einfacher XY-Grafiken und die Darstellung einer Ausgleichsgeraden. | Zeitbedarf: Zum Erlernen max. 1 Stunde, bei Gebrauch max. 20 Minuten. |
| Wozu XY-Grafiken ? Sie beherrschen bereits die Erstellung von Säulen- und Linien-Grafiken, warum sollten sie zusätzlich XY-Grafiken erlernen ? In Säulen- und Linien-Grafiken wird vom Programm angenommen, dass die jeweiligen Werte "äquidistant" sind, d.h. gleich weit voneinander entfernt liegen. Das trifft z.B. zu, wenn sie Verkaufzahlen der Jahre 1995 bis 1999 darstellen wollen - alle 5 "Säulen" haben voneinander jeweils 1 Jahr Abstand. |
Nehmen sie an, dass sie an älteren Verkaufszahlen nur mehr die Jahre 1985 und 1990 verfügbar haben. Wenn sie versuchen, das in einer Säulengrafik darzustellen, ergibt sich ein Fehler: Zwischen den Säulen für 1985 und 1990 müsste viel mehr Abstand sein als zwischen 1998 und 1999. In diesem Fall benötigen sie zur Darstellung eine XY-Grafik: Nur diese Art von Diagramm ist in der Lage, unterschiedliche Abstände der jeweiligen Datenpunkte darzustellen. |
 |
 |
 |
| Säulendiagramm (äquidistant) | Liniendiagramm (äquidistant) | XY-Diagramm |
| Wozu Ausgleichsgerade ? Reale Daten "streuen" in vielen Fällen um die erwarteten Werte. Wenn sie versuchen, solcherart schwankende Datenpunkte mit einer Linie zu verbinden, so enthält diese viel zu viele Knickpunkte - Es fehlt sozusagen der große Überblick, damit man unabhängig von kleinen Schwankungen den "Trend" mehrerer Datenpunkte erkennen kann. |
Hier helfen sie sich, indem sie die Datenpunkte überhaupt
nicht mit einer Linie verbinden, sondern zusätzlich zu den einzeln
dargestellten Punkten eine "Ausgleichsgerade" berechnen lassen.
Sie erreichen das, indem sie Excel ein XY-Diagramm mit mehreren Spalten erstellen lassen. Die erste Spalte liefert Datenpunkte, die zweite die Ausgleichs-Gerade. |
 |
|
| Datenpunkte mit einer Ausgleichs-Geraden | |
|
Erstellung einer XY-Grafik: Die Rohdaten können sie hier auf Mausklick laden. Die Zahlen enthalten jeweils die Einnahmen (linke Spalte) und die Ausgaben (rechte Spalte) für ein Jahr, z. B. 2001. (Aufgabe 3). Erstellen Sie eine Ausgleichsgerade für den Saldo, eine Prognose für den Saldo der 6 Folge-Monate sowie eine XY-Grafik, die für den Saldo IST-Daten, Ausgleichsgerade und Prognose aufweist. Sonderaufgaben: Fügen sie die Grafik in ein anderes Dokument ein (Textverarbeitung, Layout). Wandeln sie die Grafik in das Standardformat GIF um und verwenden sie die Grafik in einer Internet-Seite. |
| Tabelle formatieren:
Formatieren sie die vorhandenen Daten: Fügen sie Überschriften dazu (Monat, Einnahmen, Ausgaben, Saldo), ergänzen sie die Spalte <Monate> für Jänner 2001...Juni 2002, formatieren sie Monate und Zahlen, fügen sie Rahmen und Farben hinzu. Falls sie das ausgearbeitete Beispiel verwenden wollen, setzen sie die Monate in den Bereich (K7:K24), die Einnahmen in (L7:L18), die Ausgaben in (M7:M18). Die Daten in Zelle K7 lauten <1.1.2001> und werden als Datum formatiert, dann bis Zelle K24 ausgefüllt. |
Saldo berechnen:
Wie in Aufgabe Nr. 3 berechnen sie in einer eigenen Spalte (z. B. N7:N18) den monatlichen Saldo <=Einnahmen - Ausgaben> und formatieren die Spalte <Saldo>. Die Formel in Zelle N7 lautet nach unserem Beispiel <=L7-M7> und wird bis zu Zelle N18 nach unten ausgefüllt. |
| Ausgleichsgerade:
wird mit der Excel-Formel <TREND> berechnet. Diese Funktion benötigt einige (mindestens 2) XY-Datenpaare und berechnet daraus die Y-Werte für beliebige X-Werte. Das ist einfacher, als es zunächst aussieht: Unsere X-Werte sind die Monate, die Y-Werte der jeweilige Monats-Saldo. Zu jedem Monat (x) gehört ein Saldo (Y), die beiden Zahlen bilder ein XY-Datenpaar. Sie geben der Formel <TREND> alle bekannten XY-Datenpaare bekannt, das sind die Daten der Monate Jänner 2001...Dezember 2001. Die Formel berechnet daraus den Y-Wert (den "ausgeglichenen" Monats-Saldo) für einen beliebigen Monat (X). Wenn X in das Jahr 2001 fällt, dann entsteht eine Ausgleichsgerade bekannter Daten, wenn X außerhalb fällt, dann entsteht als Verlängerung der Ausgleichsgerade eine (lineare) Prognose - das allerdings nur unter der Annahme, dass sich der Saldo gleichmäßig (linear) weiterentwickelt. Eröffnen sie rechts neben der Spalte für den Saldo eine neue Spalte <Prognose>. |
Markieren sie die oberste Zelle der Spalte Prognose (rechts
neben dem berechneten Saldo für Jänner 2001, z.B. Zelle O7).
Hier soll der Punkt der Ausgleichs-Geraden für Jänner 2001 berechnet
werden. Befehlen sie <Einfügen / Funktion> und wählen sie <Funktionskategorie (links) = Alle (oder Statistik)> sowie <Name der Funktion (rechts) = TREND>. Sie erhalten das graue Hilfe-Fenster der Funktion <TREND>. Verschieben sie es mit der Maus an eine bequeme Stelle und geben sie die Argumente ein (alle Daten, die diese Formel benötigt): Y-Werte = Bereich der bekannten Salden, z. B. N7:N18. X-Werte = Bereich der bekannten Monate (Jänner 2001...Dezember 2001), z.B. K7...K18. Neue x-Werte = Monat der gleichen Zeile (Jänner 2001), z.B. K7. Konstante bleibt leer. Mit OK wird das Fenster geschlossen und die Formel berechnet. Formatieren sie die Zelle (z.B. ohne Kommastellen). Die Zelle enthält die Formel <=TREND(N7:N18,K7:K18,K7)>. Beim Ausfüllen der Spalte <Prognose> nach unten dürfen sich die Adressen der IST-Datenpaare nicht ändern. Die Formel in Zelle O7 muss daher teilweise asolut adressiert werden: <=TREND(N$7:N$18,K$7:K$18,K7)>. Nun kann die korrekte Formel nach unten ausgefüllt werden. |
| XY-Diagramm:
Damit Excel eine Grafik erstellen kann, müssen sie zuerst die entsprechenden Daten markieren: Sie benötigen die Monate (X), die Salden (Y1) und die Prognose (Y2). Diese Spalten liegen nicht nebeneinander. Möglichkeit 1: Sie legen eine Hilfstabelle an, welche die benötigten Daten enthält und erstellen von dieser die Grafik. Möglichkeit 2 (eleganter): Sie markieren zunächst die Monate (z.B. K6:K24), halten die Taste <Strg> gedrückt und markieren die Salden (N6:N18) und die Prognose (O6:O24). Wenn die benötigten Daten markiert sind, befehlen sie <Einfügen / Diagramm>, sie sehen das Fenster des Diagramm-Assistenten: (1) Wählen sie Reiter <Standardtypen>, Diagrammtyp (links) Punkt, Diagramm-Untertyp Punkte mit Linien. Klicken sie <Weiter> (2) Sie sollten in der Voransicht die beiden Datenreihen <Saldo> und <Prognose> sehen. Falls nicht, entfernen sie alle weiteren Datenreihen, und stellen sie ein: Saldo: Name=N6 (klicken sie einfach in die betr. Zelle), X-Werte=K7:K18, Y-Werte=N7:N18. Prognose: Name=O6, X-Werte=K7:K24, Y-Werte=O7:O24. Die Voransicht zeigt nun bereits ihr Diagramm. (3) Löschen sie Titel und Legende nach Belieben. (4) Fügen sie das Diagramm als Objekt in ihr Arbeitsblatt ein. |
Ziehen sie das Diagramm mit der Maus an eine geeignete Stelle und stellen sie durch Ziehen an den Anfassern die richtige Größe ein. Markieren sie die Achsenbeschriftungen und stellen sie mit <Format / Markierte Achse / Schrift> die gewünschte Schrift ein. Schalten sie die automatische Skalierung ab, damit sich die Schriftgröße später nicht mehr ändert. Die X-Achse liegt in der Mitte des Diagramms. Ändern sie das durch Markieren der Y-Achse und Befehl <Format / Markierte Achse / Skalierung>: Hier können sie das angezeigte Minimum, Maximum und den Schnittpunkt der X-Achse automatisch festlegen lassen (Optionen angekreuzt) oder selbst eintragen. Vorschlagswerte: Minimum=-10.000, Maximum=10.000, Hauptintervall=2.500, Größenachse (X) schneidet bei -10.000. Einstellung der Farben und Formen:  Hintergrund
(grau) markieren, <Format / Markierte Zeichnungsfläche>, Farbe
der Fläche einstellen. Datenreihe
<Salden> anklicken, <Format / Markierte Datenreihen>, Linie
= ohne, Markierung nach ihrer Wahl (Vorschlag: Vordergrund schwarz, Hintergrund
hell, Größe ca. 8 Punkt). Datenreihe
<Prognose> anklicken, entsprechend formatieren. (Vorschlag: Linie
= dick und rot, Markierung = ohne. |
| Erweiterte Formatierung:
Die beiden Achsen lassen sich nach Markierung mit <Format / Markierte Achse> hervorheben, nicht jedoch einzelne Linien. Hier wäre die Hervorhebung der horizontalen Null-Linie erwünscht - 2 Möglichkeiten: (1) Wählen sie < Ansicht / Symbolleisten / Zeichnen>, nehmen sie aus der Symbolleiste das Werkzeug <Linie> und ziehen sie manuell eine dicke Null-Linie. wenn sie dabei die Großbuchstaben-Taste (shift) gedrückt halten, wird die Linie genau horizontal. (2) Erstellen sie eine zusätzliche Spalte, die lauter Nullen enthält, z.B. P7=0, usw. bis P24=0. Markieren sie die Spalte, erzeugen sie damit eine weitere Datenreihe: <Bearbeiten / Kopieren>, Diagramm markieren, <Bearbeiten / Inhalte einfügen / Neue Datenreihe (Werte aus Spalten) >. Formatieren sie diese Datenreihe als dicke schwarze Linie ohne Markierung. |
Die Symbolleiste <Zeichnen> bietet weitere Werkzeuge zur Ergänzung ihrer Grafik, z. B. Pfeile und Textfelder.  Textfelder
sind flexibler als Diagramm-Titel, da sie sich überall dort platzieren
lassen, wo es sinnvoll ist, und wo Platz dafür ist. Wählen sie das Werkzeug <Textfeld>, ziehen sie über dem Diagramm ein Textfeld auf. Geben sie den Text <Saldo 2001> ein. Text markieren: <Format / ausgewähltes Objekt> stellt Schrift, Größe etc, ein. Textfeld markieren (am dicken grauen Rand anklicken): Lage und Größe einstellen, <Format / Ausgewähltes Objekt> erlaubt Einstellungen. Reiter <Farben und Linien>: Vorschlagswerte: Linie = Keine Linie, Ausfüllen = Keine Farbe - Das Textfeld wird durchsichtig. Bei Ausfüllen = Hintergrund-Farbe deckt das Textfeld die Diagramm-Linien dort ab, wo die Schrift steht. |
| Lösungsvorschlag zu Aufgabe 6: Vergessen Sie nicht, daß diese Version nur ein Vorschlag ist - sie haben volle Freiheit der Gestaltung ! |
Um das ausgearbeitete Lösungs-Muster als Excel-Dokument zu erhalten, klicken sie hier, für die in ein Word-Dokument eingesetzte Word-Lösung (als ZIP-komprimiertes Archiv) klicken sie hier. |
 |
 Tipp: Hier werden 3 Spalten dargestellt: Die Saldo-Zahlen (dunkelblau, mit Datenpunkten), die Prognose (rot) und die Null-Linie (schwarz), beide ohne Datenpunkte, dafür über die Ist-Daten hinaus verlängert. Zusätzlich wurden erklärende Text-Felder und ein Pfeil eingefügt. |
| Aktuelle Daten dieser Seite | Letzte Änderung: 06/23/2025 00:05:24 |
|