Excel-Datenanalyse - Datenvisualisierung

Sie können Ihre Datenanalyseberichte auf verschiedene Arten in Excel anzeigen. Wenn Ihre Datenanalyseergebnisse jedoch als Diagramme dargestellt werden können, die die bemerkenswerten Punkte in den Daten hervorheben, kann Ihre Zielgruppe schnell erfassen, was Sie in die Daten projizieren möchten. Dies wirkt sich auch positiv auf Ihren Präsentationsstil aus.

In diesem Kapitel erfahren Sie, wie Sie Excel-Diagramme und Excel-Formatierungsfunktionen für Diagramme verwenden, mit denen Sie Ihre Datenanalyseergebnisse mit Schwerpunkt darstellen können.

Daten mit Diagrammen visualisieren

In Excel werden Diagramme verwendet, um eine grafische Darstellung eines beliebigen Datensatzes zu erstellen. Ein Diagramm ist eine visuelle Darstellung der Daten, in der die Daten durch Symbole wie Balken in einem Balkendiagramm oder Linien in einem Liniendiagramm dargestellt werden. Excel bietet Ihnen viele Diagrammtypen. Sie können einen auswählen, der zu Ihren Daten passt, oder Sie können die Option Excel Recommended Charts verwenden, um an Ihre Daten angepasste Diagramme anzuzeigen und einen davon auszuwählen.

Weitere Informationen zu Diagrammtypen finden Sie im Tutorial Excel-Diagramme.

In diesem Kapitel lernen Sie die verschiedenen Techniken kennen, die Sie mit den Excel-Diagrammen verwenden können, um Ihre Datenanalyseergebnisse effektiver hervorzuheben.

Kombinationsdiagramme erstellen

Angenommen, Sie haben das Ziel und die tatsächlichen Gewinne für das Geschäftsjahr 2015-2016, die Sie aus verschiedenen Regionen erzielt haben.

Für diese Ergebnisse erstellen wir ein gruppiertes Säulendiagramm.

Wie Sie beobachten, ist es schwierig, den Vergleich zwischen den Zielen und den tatsächlichen Zielen in diesem Diagramm schnell zu visualisieren. Es hat keinen wirklichen Einfluss auf Ihre Ergebnisse.

Eine bessere Möglichkeit zur Unterscheidung von zwei Datentypen zum Vergleichen der Werte ist die Verwendung von Kombinationsdiagrammen. In Excel 2013 und den oben genannten Versionen können Sie Combo-Diagramme für denselben Zweck verwenden.

Verwenden Sie vertikale Spalten für die Zielwerte und eine Linie mit Markierungen für die tatsächlichen Werte.

• Klicken Sie auf der Multifunktionsleiste unter der Registerkarte CHART TOOLS auf die Registerkarte DESIGN.
• Klicken Sie in der Gruppe Typ auf Diagrammtyp ändern. Das Dialogfeld Diagrammtyp ändern wird angezeigt.

• Klicken Sie auf Kombinieren.

• Ändern Sie den Diagrammtyp für die Serie "Ist" in "Linie mit Markierungen". Die Vorschau wird unter Benutzerdefinierte Kombination angezeigt.

• OK klicken.

Ihr benutzerdefiniertes Kombinationsdiagramm wird angezeigt.

Wie Sie im Diagramm sehen, befinden sich die Zielwerte in Spalten und die tatsächlichen Werte sind entlang der Linie markiert. Die Datenvisualisierung ist besser geworden, da sie Ihnen auch den Trend Ihrer Ergebnisse zeigt.

Diese Art der Darstellung funktioniert jedoch nicht gut, wenn die Datenbereiche Ihrer beiden Datenwerte erheblich variieren.

Erstellen eines Kombinationsdiagramms mit Sekundärachse

Angenommen, Sie haben die Daten zur Anzahl der versendeten Einheiten Ihres Produkts und den tatsächlichen Gewinn für das Geschäftsjahr 2015-2016, den Sie aus verschiedenen Regionen erzielt haben.

Wenn Sie dasselbe Kombinationsdiagramm wie zuvor verwenden, erhalten Sie Folgendes:

In der Grafik sind die Daten von No. of Units ist nicht sichtbar, da die Datenbereiche erheblich variieren.

In solchen Fällen können Sie ein Kombinationsdiagramm mit der Sekundärachse erstellen, sodass die Primärachse einen Bereich und die Sekundärachse den anderen anzeigt.

• Klicken Sie auf die Registerkarte EINFÜGEN.
• Klicken Sie in der Gruppe Diagramme auf Kombinieren.
• Klicken Sie in der Dropdown-Liste auf Benutzerdefiniertes Kombinationsdiagramm erstellen.

Das Dialogfeld Diagramm einfügen wird mit hervorgehobener Kombination angezeigt.

Wählen Sie als Diagrammtyp -

• Linie mit Markierungen für die Seriennummer der Einheiten

• Clustered Column für die Serie Actual Profits

• Aktivieren Sie das Kontrollkästchen Sekundärachse rechts neben der Serienanzahl der Einheiten und klicken Sie auf OK.

Eine Vorschau Ihres Diagramms wird unter Benutzerdefinierte Kombination angezeigt.

Ihr Kombinationsdiagramm wird mit der Sekundärachse angezeigt.

Sie können die Werte für die tatsächlichen Gewinne auf der Primärachse und die Werte für die Anzahl der Einheiten auf der Sekundärachse beobachten.

Eine wichtige Beobachtung in der obigen Grafik betrifft das dritte Quartal, in dem die Anzahl der verkauften Einheiten höher ist, die tatsächlich erzielten Gewinne jedoch geringer sind. Dies könnte wahrscheinlich den Werbekosten zugeordnet werden, die zur Steigerung des Umsatzes anfallen. Die Situation hat sich im vierten Quartal verbessert, mit einem leichten Umsatzrückgang und einem deutlichen Anstieg der tatsächlich erzielten Gewinne.

Unterscheiden von Serien- und Kategorieachsen

Angenommen, Sie möchten die tatsächlichen Gewinne der Jahre 2013-2016 projizieren.

Erstellen Sie eine gruppierte Spalte für diese Daten.

Wie Sie beobachten, ist die Datenvisualisierung nicht effektiv, da die Jahre nicht angezeigt werden. Sie können dies überwinden, indem Sie das Jahr in eine Kategorie ändern.

Entfernen Sie das Kopfjahr im Datenbereich.

Jetzt wird das Jahr als Kategorie und nicht als Serie betrachtet. Ihr Diagramm sieht wie folgt aus:

Diagrammelemente und Diagrammstile

Diagrammelemente geben Ihren Diagrammen mehr Beschreibungen und helfen so, Ihre Daten aussagekräftiger zu visualisieren.

• Klicken Sie auf das Diagramm

Neben der oberen rechten Ecke des Diagramms werden drei Schaltflächen angezeigt.

• 
  Diagrammelemente
• 
  Diagrammstile
• 
  Diagrammfilter

Eine ausführliche Erläuterung dieser Informationen finden Sie im Tutorial zu Excel-Diagrammen.

• Klicken Sie auf Diagrammelemente.
• Klicken Sie auf Datenbeschriftungen.

• Klicken Sie auf Diagrammstile
• Wählen Sie einen Stil und eine Farbe, die Ihren Daten entsprechen.

Mit Trendline können Sie Trends in Daten grafisch anzeigen. Sie können eine Trendlinie in einem Diagramm über die tatsächlichen Daten hinaus erweitern, um zukünftige Werte vorherzusagen.

Datenaufkleber

Excel 2013 und spätere Versionen bieten Ihnen verschiedene Optionen zum Anzeigen von Datenbeschriftungen. Sie können ein Datenetikett auswählen, es nach Ihren Wünschen formatieren und dann mit Aktuelles Etikett klonen die Formatierung in die restlichen Datenetiketten im Diagramm kopieren.

Die Datenbeschriftungen in einem Diagramm können Effekte mit unterschiedlichen Formen und Größen haben.

Es ist auch möglich, den Inhalt einer Zelle als Teil der Datenbeschriftung mit Feld Datenbeschriftung einfügen anzuzeigen.

Schnelles Layout

Mit dem Schnelllayout können Sie das Gesamtlayout des Diagramms schnell ändern, indem Sie eine der vordefinierten Layoutoptionen auswählen.

• Klicken Sie auf das Diagramm.
• Klicken Sie unter CHART TOOLS auf die Registerkarte DESIGN.
• Klicken Sie auf Schnelllayout.

Verschiedene mögliche Layouts werden angezeigt. Wenn Sie mit den Layoutoptionen fortfahren, ändert sich das Diagrammlayout zu dieser bestimmten Option.

Wählen Sie das gewünschte Layout. Das Diagramm wird mit dem gewählten Layout angezeigt.

Verwenden von Bildern in Säulendiagrammen

Sie können Ihre Datenpräsentation stärker betonen, indem Sie anstelle von Spalten ein Bild verwenden.

• Klicken Sie im Säulendiagramm auf eine Spalte.

• Klicken Sie in der Format Data Series auf Fill.

• Wählen Sie Bild.

• Geben Sie unter Bild einfügen von den Dateinamen oder optional die Zwischenablage ein, wenn Sie zuvor ein Bild kopiert haben.

Das von Ihnen ausgewählte Bild wird anstelle der Spalten im Diagramm angezeigt.

Band Chart

Möglicherweise müssen Sie Kundenumfrageergebnisse eines Produkts aus verschiedenen Regionen präsentieren. Band Chart ist für diesen Zweck geeignet. Ein Banddiagramm ist ein Liniendiagramm mit einem zusätzlichen schattierten Bereich, um die oberen und unteren Grenzen von Datengruppen anzuzeigen.

Angenommen, Ihre Kundenumfrageergebnisse aus den Regionen Ost und West lauten monatlich:

Hier sind in den Daten <50% niedrig, 50% - 80% mittel und> 80% hoch.

Mit Band Chart können Sie Ihre Umfrageergebnisse wie folgt anzeigen:

Erstellen Sie aus Ihren Daten ein Liniendiagramm.

Ändern Sie den Diagrammtyp in -

• Ost- und Westserie zur Ausrichtung mit Markern.
• Niedrige, mittlere und hohe Serie bis gestapelte Säule.

Ihr Diagramm sieht wie folgt aus.

• Klicken Sie auf eine der Spalten.
• Ändern Sie die Lückenbreite in Format Data Series auf 0%.

Sie erhalten Bands anstelle von Spalten.

Um das Diagramm präsentabler zu machen -

• Diagrammtitel hinzufügen.
• Passen Sie den Bereich der vertikalen Achse an.
• Ändern Sie die Farben der Bänder in Grün-Gelb-Rot.
• Fügen Sie Labels Labels hinzu.

Das Endergebnis ist das Banddiagramm mit den definierten Grenzen und den Umfrageergebnissen, die über die Bänder hinweg dargestellt werden. Aus der Grafik kann man schnell und deutlich erkennen, dass die Umfrageergebnisse für die Region West zwar zufriedenstellend sind, die für die Region Ost im letzten Quartal jedoch rückläufig sind und Aufmerksamkeit erfordern.

Thermometer-Diagramm

Wenn Sie einen Zielwert und einen tatsächlichen Wert darstellen müssen, können Sie einfach ein Thermometerdiagramm in Excel erstellen, das diese Werte nachdrücklich anzeigt.

Mit dem Thermometer-Diagramm können Sie Ihre Daten wie folgt anzeigen:

Ordnen Sie Ihre Daten wie unten gezeigt an -

• Wählen Sie die Daten aus.
• Erstellen Sie ein gruppiertes Säulendiagramm.

Wie Sie sehen, ist die rechte Spalte Ziel.

• Klicken Sie im Diagramm auf eine Spalte.
• Klicken Sie in der Multifunktionsleiste auf Zeile / Spalte wechseln.

• Klicken Sie mit der rechten Maustaste auf die Zielspalte.
• Klicken Sie auf Datenreihen formatieren.
• Klicken Sie auf Sekundärachse.

Wie Sie beobachten, haben die Primärachse und die Sekundärachse unterschiedliche Bereiche.

• Klicken Sie mit der rechten Maustaste auf die Primärachse.
• Geben Sie in den Optionen zum Formatieren der Achse unter Grenzen 0 für Minimum und 1 für Maximum ein.
• Wiederholen Sie dies für die Sekundärachse.

Sowohl die Primärachse als auch die Sekundärachse werden auf 0% - 100% eingestellt. Die Zielspalte verbirgt die tatsächliche Spalte.

• Klicken Sie mit der rechten Maustaste auf die sichtbare Spalte (Ziel).
• Wählen Sie unter Datenreihen formatieren die Option aus
  • Keine Füllung für FILL
  • Durchgezogene Linie für BORDER
  • Blau für Farbe

• Deaktivieren Sie in Diagrammelemente die Option
  • Achse → Primär horizontal
  • Achse → Sekundärvertikal
  • Gridlines
  • Diagrammtitel
• Klicken Sie im Diagramm mit der rechten Maustaste auf Primäre vertikale Achse
• Klicken Sie in den Optionen zum Formatieren der Achse auf TICK MARKS
• Wählen Sie als Haupttyp Innen aus

• Klicken Sie mit der rechten Maustaste auf den Diagrammbereich.
• Wählen Sie in den Optionen Diagrammbereich formatieren die Option aus
  • Keine Füllung für FILL
  • Keine Linie für BORDER

Ändern Sie die Größe des Diagrammbereichs, um die Form eines Thermometers zu erhalten.

Sie haben Ihr Thermometer-Diagramm erhalten, in dem der tatsächliche Wert gegenüber dem Zielwert angezeigt wird. Sie können dieses Thermometer-Diagramm mit einigen Formatierungen eindrucksvoller machen.

• Fügen Sie eine Rechteckform ein, die den blauen rechteckigen Teil im Diagramm überlagert.
• Wählen Sie unter Formatformoptionen die Option -
  • Verlaufsfüllung für FILL
  • Linear für Typ
  • 180 ⁰ für Winkel
• Stellen Sie die Verlaufsstopps auf 0%, 50% und 100% ein.
• Wählen Sie für die Verlaufsstopps bei 0% und 100% die Farbe Schwarz.
• Wählen Sie für den Gradientenstopp bei 50% die Farbe Weiß.

• Fügen Sie unten eine ovale Form ein.
• Formatieren Sie die Form mit denselben Optionen.

Das Ergebnis ist das Thermometer-Diagramm, mit dem wir begonnen haben.

Gantt-Diagramm

Ein Gantt-Diagramm ist ein Diagramm, in dem eine Reihe horizontaler Linien den Arbeitsaufwand in bestimmten Zeiträumen im Verhältnis zum Arbeitsaufwand für diese Zeiträume zeigt.

In Excel können Sie ein Gantt-Diagramm erstellen, indem Sie einen gestapelten Balkendiagrammtyp so anpassen, dass er Aufgaben, Aufgabendauer und Hierarchie darstellt. Ein Excel-Gantt-Diagramm verwendet normalerweise Tage als Zeiteinheit entlang der horizontalen Achse.

Betrachten Sie die folgenden Daten, in denen die Spalte -

• Aufgabe repräsentiert die Aufgaben im Projekt
• Start gibt die Anzahl der Tage ab dem Startdatum des Projekts an
• Die Dauer gibt die Dauer der Aufgabe an

Beachten Sie, dass der Start einer Aufgabe der Beginn der vorherigen Aufgabe + Dauer ist. Dies ist der Fall, wenn sich die Aufgaben in einer Hierarchie befinden.

• Wählen Sie die Daten aus.
• Erstellen Sie ein gestapeltes Balkendiagramm.

• Klicken Sie mit der rechten Maustaste auf Serie starten.
• Wählen Sie unter Optionen für Datenreihen formatieren die Option Keine Füllung aus.

• Klicken Sie mit der rechten Maustaste auf Kategorienachse.
• Wählen Sie unter Achsenoptionen formatieren Kategorien in umgekehrter Reihenfolge aus.

• Deaktivieren Sie in Diagrammelemente die Option
  • Legend
  • Gridlines
• Formatieren Sie die horizontale Achse auf
  • Passen Sie den Bereich an
  • Wichtige Häkchen im Abstand von 5 Tagen
  • Kleinere Häkchen im Abstand von 1 Tag
• Formatieren Sie Datenreihen, damit sie beeindruckend aussehen
• Geben Sie einen Diagrammtitel an

Wasserfallkarte

Waterfall Chart ist eines der beliebtesten Visualisierungstools für kleine und große Unternehmen. Wasserfalldiagramme sind ideal, um zu zeigen, wie Sie zu einem Nettowert wie dem Nettoeinkommen gekommen sind, indem Sie den kumulativen Effekt positiver und negativer Beiträge aufschlüsseln.

Excel 2016 bietet den Typ Wasserfalldiagramm. Wenn Sie frühere Versionen von Excel verwenden, können Sie dennoch ein Wasserfalldiagramm mithilfe eines gestapelten Säulendiagramms erstellen.

Die Spalten sind farbcodiert, sodass Sie schnell positive von negativen Zahlen unterscheiden können. Die Anfangs- und Endwertspalten beginnen auf der horizontalen Achse, während die Zwischenwerte schwebende Spalten sind. Aufgrund dieses Aussehens werden Wasserfalldiagramme auch als Brückendiagramme bezeichnet.

Betrachten Sie die folgenden Daten.

• Bereiten Sie die Daten für das Wasserfalldiagramm vor

• Stellen Sie sicher, dass sich die Spalte Netto-Cashflow links von der Spalte Monate befindet (Dies liegt daran, dass Sie diese Spalte beim Erstellen des Diagramms nicht einbeziehen).

• 2 Spalten hinzufügen - Erhöhen und Verringern für positive bzw. negative Cashflows

• Hinzufügen einer Spalte Start - Die erste Spalte im Diagramm mit dem Startwert im Netto-Cashflow

• Hinzufügen einer Spalte Ende - Die letzte Spalte im Diagramm mit dem Endwert im Netto-Cashflow

• Fügen Sie eine Spalte Float hinzu - die die Zwischenspalten unterstützt

• Berechnen Sie die Werte für diese Spalten wie folgt

• Fügen Sie in der Spalte Float am Anfang und am Ende eine Zeile ein. Platzieren Sie n einen beliebigen Wert von 50000. Dies dient nur dazu, links und rechts vom Diagramm etwas Platz zu haben

Die Daten werden wie folgt sein.

• Wählen Sie die Zellen C2: H18 aus (Spalte Netto-Cashflow ausschließen)
• Erstellen Sie ein gestapeltes Säulendiagramm

• Klicken Sie mit der rechten Maustaste auf die Float-Serie.
• Klicken Sie auf Datenreihen formatieren.
• Wählen Sie unter Optionen für Datenreihen formatieren die Option Keine Füllung aus.

• Klicken Sie mit der rechten Maustaste auf Negative Serie.
• Wählen Sie Füllfarbe als Rot.

• Klicken Sie mit der rechten Maustaste auf Positive Serie.
• Wählen Sie Füllfarbe als Grün.

• Klicken Sie mit der rechten Maustaste auf Serie starten.
• Wählen Sie Füllfarbe als Grau.
• Klicken Sie mit der rechten Maustaste auf End Series.
• Wählen Sie Füllfarbe als Grau.
• Löschen Sie die Legende.

• Klicken Sie mit der rechten Maustaste auf eine Serie
• Wählen Sie unter Optionen für Datenreihen formatieren unter Serienoptionen die Option Lückenbreite als 10% aus

Geben Sie den Diagrammtitel an. Das Wasserfalldiagramm wird angezeigt.

Sparklines

Sparklines sind winzige Diagramme in einzelnen Zellen, die jeweils eine Datenzeile in Ihrer Auswahl darstellen. Sie bieten eine schnelle Möglichkeit, Trends zu erkennen.

Sie können Sparklines mit dem Quick Analysis-Tool hinzufügen.

• Wählen Sie die Daten aus, für die Sie Sparklines hinzufügen möchten.
• Halten Sie eine leere Spalte rechts neben den Daten für die Sparklines.

Die Schaltfläche

für die Schnellanalyse wird unten rechts in den ausgewählten Daten angezeigt.

• Klicken Sie auf die

  
  Schaltfläche Schnellanalyse. Die Symbolleiste für die Schnellanalyse wird mit verschiedenen Optionen angezeigt.

Klicken SPARKLINES. Die angezeigten Diagrammoptionen basieren auf den Daten und können variieren.

Klicken Line. In der Spalte rechts neben den Daten wird ein Liniendiagramm für jede Zeile angezeigt.

PivotCharts

Pivot-Diagramme werden verwendet, um Daten grafisch zusammenzufassen und komplizierte Daten zu untersuchen.

Ein PivotChart zeigt Datenreihen, Kategorien und Diagrammachsen wie ein Standarddiagramm an. Darüber hinaus erhalten Sie interaktive Filtersteuerelemente direkt im Diagramm, sodass Sie schnell eine Teilmenge Ihrer Daten analysieren können.

PivotCharts sind nützlich, wenn Sie Daten in einer großen PivotTable oder in vielen komplexen Arbeitsblattdaten haben, die Text und Zahlen enthalten. Ein PivotChart kann Ihnen helfen, diese Daten zu verstehen.

Sie können ein PivotChart aus erstellen

• Eine PivotTable.
• Eine Datentabelle als eigenständige Tabelle ohne PivotTable.

PivotChart von PivotTable

Führen Sie die folgenden Schritte aus, um ein PivotChart zu erstellen:

• Klicken Sie auf die PivotTable.
• Klicken Sie auf der Multifunktionsleiste unter PIVOTTABLE TOOLS auf ANALYZE.
• Klicken Sie auf PivotChart. Das Dialogfeld Diagramm einfügen wird angezeigt.

Wählen Sie Clustered Column aus der Option Column.

OK klicken. Das PivotChart wird angezeigt.

Das PivotChart verfügt über drei Filter: Region, Verkäufer und Monat.

• Klicken Sie auf die Option Regionsfiltersteuerung. Das Suchfeld wird mit der Liste aller Regionen angezeigt. Kontrollkästchen werden neben Regionen angezeigt.

• Wählen Sie die Optionen Ost und Süd.

Die gefilterten Daten werden sowohl im PivotChart als auch in der PivotTable angezeigt.

PivotChart ohne PivotTable

Sie können ein eigenständiges PivotChart erstellen, ohne eine PivotTable zu erstellen.

• Klicken Sie auf die Datentabelle.
• Klicken Sie auf die Registerkarte Einfügen.
• Klicken Sie in der Gruppe Diagramme auf PivotChart. Das Fenster PivotChart erstellen wird angezeigt.
• Wählen Sie die Tabelle / den Bereich aus.
• Wählen Sie den Speicherort aus, an dem das PivotChart platziert werden soll.

Sie können eine Zelle im vorhandenen Arbeitsblatt selbst oder in einem neuen Arbeitsblatt auswählen. OK klicken.

Ein leeres PivotChart und eine leere PivotTable werden zusammen mit der PivotChart-Feldliste angezeigt, um das PivotChart zu erstellen.

• Wählen Sie die Felder aus, die dem PivotChart hinzugefügt werden sollen

• Ordnen Sie die Felder an, indem Sie sie in FILTER, LEGENDE (SERIE), ACHSE (KATEGORIEN) und WERTE ziehen

• Verwenden Sie die Filtersteuerelemente auf dem PivotChart, um die Daten auszuwählen, die auf dem PivotChart platziert werden sollen

Excel erstellt automatisch eine gekoppelte PivotTable.