Excel Tipp

Tabellenanalyse leicht gemacht – Gerade Auswertungen von verkauften Artikelmengen werden erst dann aussagefähig, wenn man neben den absoluten Werten in der Auswertungstabelle direkt daneben die relativen Werte in Prozent der verkauften Mengen abbildet. Wie Sie in Ihrer Pivottabelle relative Werte in Prozent des Spaltenergebnisses darstellen, zeigt Ihnen der heutige Excel-Tipp.

Einfügen der Prozentspalte
Um in Ihre erstellte Datentabelle die Prozentspalte einzufügen, müssen Sie lediglich zwei Mal die Spalte „Menge“ aus den PivotTable-Feldern in den Bereich „Ʃ-WERTE“ einfügen. Nun erscheint in Ihrer Pivottabelle zu jedem Jahr die Spalten „Summe von Menge“ und „Summe von Menge2“.

Darstellung in Prozent des Spaltenergebnisses
Nun klicken Sie in den PivotTable-Feldern im Bereich „Ʃ-WERTE“ auf den Dropdown Button des Buttons „Summe von Menge2“ und wählen die „Wertfeldeinstellungen“ aus. Hier klicken Sie auf den Reiter „Werte anzeigen als“ und stellen die Auswahlliste in der „Keine Berechnung“ steht auf „% des Spaltenergebnisses“ um.

Jetzt werden alle Werte in der Spalte „Summe von Menge2“ in Ihrer Pivottabelle wie gewünscht in Prozent dargestellt. Zur besseren Übersicht benennen Sie die Spalte „Summe von Menge“ in „abs“ für absolut und die Spalte „Summe von Menge2“ in „%“ für Prozent durch direktes Überschreiben der Zelle in der Pivottabelle um.

HINWEIS:
Da es sich um eine Auswertung handelt, die „Übersicht“ schaffen soll empfehle ich Ihnen, nach Möglichkeit auf die Nachkommastellen der Prozentwerte zu verzichten, da diese für den Leser nicht hilfreich sind und das Auge des Lesers unnötig belasten. Die Tabelle wirkt durch das Weglassen der Nachkommastellen gleichzeitig klarer und strukturierter.

Autor: Ralf Greiner

Kennen Sie das? Sie möchten lange Datenlisten zum einen übersichtlich gestalten und zum anderen mit den Daten rechnen. Dazu eigenen sich sehr gut Pivot-Tabellen, da diese die Filter- und Rechenfunktion wunderbar kombinieren. Der Nachteil ist, dass beim Filtern nach mehr als einem Element für den Betrachter nicht mehr ersichtlich wird, nach was die Pivot-Tabelle gefiltert wurde. Im Filter steht lediglich „mehrere Elemente“. Wie Sie mit dem Analysewerkzeug „Datenschnitt“ Ihre Pivot-Tabellen filterbar und die ausgewählten Filter sichtbar machen, zeigt Ihnen der heutige Excel-Tipp.

Sichtbarmachen von Filtern mittels „Datenschnitt“
Um die Funktion Datenschnitt zu nutzen klicken Sie an eine beliebige Stelle in Ihrer bereits eingefügten Pivot-Tabelle. Nun erscheinen in der Reiterleiste die sogenannten PIVOTTABLETOOLS. Dort wählen Sie im Reiter ANALYSIEREN / im Bereich Filtern Datenschnitt einfügen. Nun bekommen Sie das Auswahlmenü „Datenschnitt auswählen“ angezeigt und können jedes Element das Sie als Filter sichtbar machen möchten, durch setzen eines Häkchens auswählen:

Das sieht bei mir anders aus.

Und wenn ich dort auf Einfügen à Filter à Datenschnitt klicke, kommt das folgende Fenster.
Sie müssen erst in die Pivottabelle klicken und dann über Pivotable Tools /Analysieren/Datenschnitt einfügen. Sie haben nicht in die Pivotabelle geklickt, daher erscheint bei Ihrer Variante, die auch möglich ist das folgende Feld.

Durch Drücken der OK Buttons erscheinen die ausgewählten Filter neben Ihrer Pivot-Tabelle. Nun können Sie durch Anklicken der gewünschten Jahre oder Kunden Ihre Tabelle filtern.

Nach mehreren Elementen im Datenschnitt filtern
Wenn Sie mehrere Elemente/Parameter im Datenschnitt auswählen möchten ist dies durch gleichzeitiges Drücken der STRG-Taste während der Auswahl möglich. Der Empfänger kann nun sofort sehen, nach welchen Elementen die Tabelle gefiltert wurde, da diese farbig (blau) hinterlegt werden. Die ausgewählten Filter in den einzelnen Datenschnitten können durch Drücken des „Filter-Löschen-Buttons“ oben rechts im Datenschnitt-Feld wieder aufgehoben werden.

Autor: Ralf Greiner

Sie wollen mit Daten aus langen Listen rechnen und Summen nach bestimmten Kriterien bilden? – Dann sind Pivottabellen das richtige Werkzeug für Sie. Der große Nutzen von Pivotabellen ist, dass sie zum „rechnen mit gefilterten Daten“ gemacht sind und somit direkt genutzt werden sollten, wenn Sie Daten nach bestimmten Kriterien filtern und aufsummieren wollen. Der nächste Excel-Tipp zeigt Ihnen wie Sie eine Pivottabelle einfügen:

Von der Datenliste zur Pivottabelle
Um nun aus einer langen Datenliste eine Pivottabelle zu erstellen klicken Sie in eine beliebige Zelle in Ihrer Datentabelle. Dann klicken Sie Über den Reiter EINFÜGEN/ im Bereiche Tabellen auf den Knopf Pivot Table. Excel umrahmt automatisch die komplette Datenliste und öffnet das „Pivot Table erstellen“ Dialogfeld. Im Feld „Tabelle/Bereich:“ wird Ihnen der Datenbereich angezeigt, auf den die Pivottabelle nun zugreift. In unserem Beispiel ist es A1:E34. Sie können den Datenbereich auch beliebig ändern oder erweitern. Ferner haben Sie die Möglichkeit die Pivottabelle in einem „neuen“ oder „vorhandenen“ Arbeitsblatt einzufügen. In unserem Beispiel wählen wir das „neue“ Arbeitsblatt aus, da es übersichtlicher ist Daten und Pivottabelle voneinander zu trennen, und bestätigen mit der OK-Taste.

Erstellen einer Pivottabelle
Im sich neu geöffneten Arbeitsblatt finden wir nun eine noch leere Pivottabelle vor, dessen Aussehen wir mittels der sogenannten „PivotTable-Felder“ (Früher „Feldliste“ genannt) bestimmen können. Im oberen Bereich der PivotTable-Felder finden Sie die Spaltenüberschriften aus Ihrer Datentabelle wieder. Diese ziehen Sie mit gedrückter linker Maustaste in die jeweiligen Felder FILTER, SPALTEN, ZEILEN und WERTE.

Damit Sie die Kontrolle über die Darstellung der Pivottabelle behalten, hilft folgender Grundsatz: Sie müssen genau wissen, was Sie wie darstellen wollen, z.B. „Kategorie+Art pro Jahr“. Dann muss immer das Wort, was nach dem „pro“ steht (Jahr) in den SPALTEN-Bereich gezogen werden. Die Wörter, die vor dem „pro“ stehen (Kategorie+Art) kommen in den ZEILEN-Bereich. Die Spalte in der die Werte stehen mit denen gerechnet werden soll (in unserem Beispiel Spalte „Menge“) zieht man immer in den ƩWERTE Bereich. Alle Daten, die in erster Linie keine Rolle spielen (Kunde) ziehen Sie dann in den Bereich FILTER.

Eine weitere Besonderheit bei Pivottabellen ist, dass Sie die Daten, auf welche die Pivottabelle zugreift, aktualisieren können, ohne eine neue Pivottabelle einfügen zu müssen. Somit können Sie eine einmal erstellte Pivottabelle im Rahmen des Monatsabschlusses immer wieder nutzen indem Sie lediglich die Daten aktualisieren.

Autor: Ralf Greiner, https://www.ralf-greiner.com/

Mit der Filterfunktion „nach Farbe filtern“ können Sie schnell eine Übersicht über die wichtigsten Markierungen in einem Report gewinnen. Stellen Sie sich vor Sie haben lange Datenlisten und möchten schnell eine Übersicht gewinnen. Dazu ist die Möglichkeit „Sortieren und Filtern“ wunderbar geeignet. Datenfilter zeigen Ihnen für jede Spalte die unterschiedlichen „Begriffe“ an, nach denen gefiltert werden kann. Neben der Möglichkeit nach einzelnen Jahren, Kunden, Kategorien oder Arten zu filtern, gibt es auch die Möglichkeit mach Farbe zu filtern.  Wie Sie aus langen Datenlisten nach den Zeilen filtern können, die Sie farbig markiert haben, zeigt der folgende Excel Tipp.

So aktivieren Sie die Filterfunktion
Um die Filtermöglichkeit zu aktivieren markieren Sie die obere Zeile mit den Spaltennamen. Im Beispiel ist es A1 bis E1. Im Anschluss wählen Sie über den Reiter START/ im Bereich Bearbeiten / Sortieren und Filtern / das FILTER Symbol:

Nun entstehen im rechten Teil der Zellen von A1 bis E1 sogenannte Dropdown Buttons, mit deren Hilfe Sie Daten filtern oder sortieren können. Der Unterschied zwischen „filtern“ und „sortieren“ ist, dass beim Filtern nur die Zeilen angezeigt werden nach denen gefiltert wird, die anderen Zeilen werden ausgeblendet.

Daten nach Farbe filtern
Um nun die Daten nach Farbe zu filtern, klicken Sie auf einen Dropdown Button einer beliebigen Spalte und wählen im Bereich „nach Farbe filtern“ die Farbe aus, nach der Sie filtern möchten.

HINWEIS: In unserem Beispiel wurde nach der Farbe „gelb“ gefiltert. Bitte beachten Sie, dass die Filterfunktion wunderbar dazu dient einen schnellen Überblick zu bekommen. Sollten Sie mit den gefilterten Daten weiterrechnen wollen, empfehle ich Ihnen anstatt der Filterfunktion direkt mit Pivot-Tabellen zu arbeiten.

Autor: Ralf Greiner, https://www.ralf-greiner.com/

Mit Lügenfaktor werden hier alle absichtlich oder unbeabsichtigt missgestalteten Diagramme bezeichnet, die man heute leider in zahlreichen Geschäftsberichten, in der Werbung und auch in der Wirtschaftspresse findet. Meistens handelt es ich hierbei um Manipulationen an den Achsen. Das folgende Beispiel soll Ihnen anschaulich zeigen was mit dem „Lügenfaktor“ gemeint ist und wie man mit der Formel MAX den richtigen Maßstab für seine Diagrammreihe findet:

Dem Lügenfaktor auf der Spur

Im oberen Beispiel sehen wir den Absatz an Pralinen für die Jahre 2014 bis 2016 in 1.000 Stück. Für die drei Pralinensorten Rum, Nuss und Kakao wurde jeweils ein Diagramm erstellt. Obwohl die Diagramme nach den Hichert’schen SUCCESS Regeln gestaltet und somit sehr stark vereinfacht wurden, fehlt noch ein entscheidendes Qualitätsmerkmal nach Hichert, „CHECK“. Vergleicht man das Jahr 2016 der Diagramme Kakao mit Rum, fällt auf, dass beide Säulen nahezu gleich groß sind, obwohl die Säule Kakao mit 335 Stück viel größer sein müsste als die Säule Rum mit 135 Stück. Hierbei ist ganz wichtig festzuhalten, dass die Diagramme inhaltlich zwar korrekt sind, jedoch die Darstellung falsch ist. Ein optischer Vergleich der drei Pralinensorten ist so für den Betrachter leider nicht möglich, da ein einheitlicher Maßstab fehlt.

Den richtigen Maßstab mit der Formel MAX finden

Die Formel MAX sucht in einem Zahlenbereich den höchsten Wert heraus und gibt Ihn wieder. In unserem Beispiel sucht die Formel MAX aus dem Bereich B9 bis D11 den Maximalwert heraus und gibt die Zahl 335 aus.

Den Maximalwert als Maßstab in das Diagramm einfügen

Nachdem wir mit der Formel MAX den Maximalwert unserer Zahlenreihe ermittelt haben, fügen wir diesen Wert (335) als Maßstab in jedes Diagramm ein. Dazu fügen wir eine neue Datenreihe in das Diagramm ein: mit der Maus über das Diagramm fahren, rechte Maustaste, Daten auswählen. Im sich öffnenden Menü „Datenquelle auswählen“ den Button Hinzufügen anklicken. Das Dialogfeld „Datenreihe bearbeiten“ öffnet sich:

Jetzt legen Sie als Reihenname A12 fest, da hier der Name unserer neuen Datenreihe „Maßstab“ steht. Als Reihenwert wählen Sie die Zelle B12 aus, da hier unser Maximalwert ermittelt wurde, der uns als Maßstab dienen soll. Nach drücken der OK Taste finden Sie die neue Datenreihe „Maßstab“ im Menü „Datenquelle auswählen“.

Nach dem Sie auch hier wieder OK drücken wird sich Ihr Diagramm automatisch an die neue Maßstab-Säule anpassen und den neuen Datenbalken in rot anzeigen.

Damit die neue „Maßstab-Säule“ unsichtbar wird und nicht die anderen Säulen einengt (dünner macht) stellen wir im Reiter Start über den Button Füllfarbe die Füllung der roten Säule auf „keine Füllung“. Ferner überlappen wir die Maßstabsäule mit der Säule des Jahres 2014: Rechtsklick auf die Maßstabsäule/ Datenreihen formatieren / und stellen die Reihenachsenüberlappung auf +100%. Im Anschluss wiederholen Sie das für die beiden weiteren Diagramme Nuss und Kakao.

Am Ende erhalten Sie drei optisch vergleichbare Säulendiagramme, die den Betrachter ein „wahres“ Bild über den Absatz der Pralinensorten vermitteln. Auch hier wird das Credo „weg vom lesen hin zum sehen von Diagrammen“ umgesetzt. Die Feststellung von Unterschieden ist sofort visuell für den Betrachter möglich.

Autor: Ralf Greiner, https://www.ralf-greiner.com/

Tipps für das Arbeiten mit Excel geben – zusammengefasst von Ralf Greiner

Kennen Sie das: Nicht enden wollende Datentabellen, die analysiert werden möchten und nach wenigen Minuten anstrengendem Hinschauens langsam aber sicher vor dem Auge verschwimmen? Machen Sie mehr aus Ihren Tabellen und sagen Sie langen Zahlentapeten den Kampf an.

„Die Bedingte Formatierung Datenbalken“ erleichtert Abweichungsanalysen

Laut der Studie einer renommierten Heidelberger Universität braucht das menschliche Auge keine Gitternetzlinien in Tabellen, um die richtigen Werte einer Zeile zuzuordnen. Reduzieren Sie daher den Einsatz von Gitternetzlinien. Der Empfänger wird es Ihnen danken. Ferner empfiehlt es sich mit einer zusätzlichen Spalte zu arbeiten, welche die Abweichungen als Datenbalken darstellt. Hierdurch wird der Leser visuell auf die größten Abweichungen hingewiesen und „sieht“ diese sofort, anstatt erst mühsam die ganze Tabelle „lesen“ zu müssen:

Und so fügen Sie die bedingte Formatierung „Datenbalken“ ein

Übertragen Sie in Spalte F der Datentabelle alle Abweichungen, die Sie als Datenbalken darstellen möchten und markieren Sie diese. Öffnen Sie dann über START / BEDINGTE FORMATIERUNG / DATENBALKEN / WEITERE REGELN das Menü zur bedingten Formatierung „Datenbalken“.

Wählen Sie den ersten Regeltyp „Alle Zellen basierend auf ihren Werten formatieren“ aus. Setzen Sie im Bereich „Regelbeschreibung bearbeiten“ einen Haken bei „Nur Balken anzeigen“. Nun können Sie im Bereich „Balkendarstellung“ noch die Farbe „grün“ für die positiven Abweichungen auswählen. Das ist alles. Die negativen Abweichungen werden automatisch „rot“ eingefärbt (eine Änderung ist über den Button „Negativer Wert und Achse…“ möglich).

HINWEIS: Ich empfehle Ihnen bei der Hervorhebung von Abweichungen mittels „Datenbalken“ immer die Zahlenwerte in einer separaten Spalte stehen zu lassen (im Beispiel Spalte E) und die Datenbalken daneben einzufügen (Spalte F). Andernfalls überschneiden sich Balken und Zahlenbeschriftung, was die Lesbarkeit erschwert (siehe folgende Abbildung).

In loser Reihenfolge möchten wir Ihnen auf dem Blog auch wieder Tipps für das Arbeiten mit Excel geben – erstellt von Ralf Greiner

Beim Aufbereiten von Datentabellen kann ein kleines Diagramm wunderbar dazu dienen, einen Trend noch besser zu erkennen. Wie Sie Ihre Tabellen noch aussagefähiger gestalten, ohne ein herkömmliches Diagramm einfügen zu müssen, erfahren Sie im folgenden Excel-Tipp.

„Sparklines – die Trendlinien Funktion“
Ab der Version Excel 2010 wird die neue Funktion SPARKLINES angeboten. Sie bietet selbst für den Tabellen- und Zahlenliebhaber eine gelungene Aufwertung seiner Daten, da Trends einfach besser wahrgenommen werden können. Sparklines ermöglichen, visuelle Darstellungen von Daten mit nur einem Klick zu erstellen. Dadurch lassen sich Daten leichter erfassen, und die Leitung der Finanzabteilung kann Trends direkt analysieren.

Sparklines einfügen
Um diese „Trendlinien“ einzufügen, wählt man über den Reiter Einfügen/ Spalte Sparklines die Variante „Säule“ aus. Dabei öffnet sich das Fenster „Sparklines erstellen“:

Nun kann im Feld „Datenbereich“ die Zelle ausgewählt werden, in der die Sparklines eingefügt werden sollen, ausgewählt werden. Im Beispiel ist es die Zelle X9. Im Feld Positionsbereich wählt man nun den Zellenbereich aus, der als Sparkline dargestellt werden soll. Dies ist immer der Zahlenbereich. Im Beispiel ist es T9:W9, welcher die Zahlen der Eigenkapitalquote enthält. (siehe Abbildung)

HINWEIS: Es empfiehlt sich beim Einfügen von Sparklines auf die Variante „Linien“ zu verzichten und lediglich die Variante „Säule“ zu benutzen, da ein einfaches Säulendiagramm den Unterschied von einem Datenpunkt zum anderen anschaulicher aufzeigt als eine Linie, bei der kleinere Unterschiede optisch gar nicht wahrgenommen werden können. Ferner ist im Vorfeld zu überlegen, für welche Kennzahlen ein Trend für den Empfänger sinnvoll ist, da bei zu vielen Trendlinien (mehr als 12 pro Seite/ Ansicht) eine Überfrachtung eintritt. In der Regel sind 4-6 Trendlinien für die wichtigsten Kennzahlen eine gelungene Möglichkeit zur Hervorhebung und ein Stilmittel welches hilft Daten „zu sehen“ anstatt sie nur „zu lesen“.

Möchten Sie sich spezifische Datensätze aus verschiedenen Excelblättern anzeigen lassen um damit weiter zuarbeiten? Wie das funktioniert, zeigt unser heutiger Excel-Tipp zur Formel SVERWEIS.

Beim Scrollen nach unten stellen Sie fest, dass die Spaltennamen verschwinden. Damit Sie nicht immer zurück nach oben scrollen müssen zeigt Ihnen der heutige Excel Tipp wie man Zeilen und Spalten feststellt.