Ich habe mir jetzt ein Seniorennavi zugelegt. Es sagt mir nicht nur die Strecke und das Ziel an, sondern auch, was ich dort wollte.

Hallo Herr Martin,

ich habe eine Frage zum Liniendiagramm, leider stimmen meine Werte nicht und ich bekomme es nicht hin ohne das die Linien aus meinem Diagramm verschwinden.

Vielleicht könne sie mir spontan helfen.

Danke

Hallo Frau D.,

Sie müssen die Reihe mit den „großen Zahlen“, also Jergl, Michal, Hans und Veitli auf eine Sekundärachse legen. Und dann möglicherweise die Skalierung der beiden Achsen anpassen.

Da leckt man einmal das Messer ab und alle gucken komisch! – Jens, 32, Chirurg.

Excelstammtisch. Wir diskutieren über Histogramme, Häufigkeitsverteilung und Stabdiagramme. Karin stellt eine interessante Frage: sie hat eine Reihe von Daten mit einem nicht-äquidistanten Abstand. Zu diesen Daten hat sie Werte. Wie kann sie diese in einem Diagramm darstellen?

Sofort fallen mir Datenreihen ein, die Datumsangaben verwenden. Man kann entweder die X-Achse als Datumsachse formatieren (also so tun, als wären die Jahreszahlen Datumswerte, das heißt: die Zahl 1985 wird als 07.06.1905 interpretiert; 2019 als 11.07.1905).

Oder man wählt den Diagrammtyp Punkt (XY):

Karin möchte Säulen haben. Auch kein Problem, oder? – Also: Datumsachse:

Allerdings: Ihre x-Werte liegen nicht im ganzzahligen Bereich, so dass sie als Tage interpretiert werden können, sondern liegen im Dezimalbereich. Hier scheitert die Datumsachse:

Schritt 1: Das Säulendiagramm wird erstellt:

Schritt 2: Datumsachse:

Die einzige Lösung, die wir gefunden haben, besteht darin, die Werte in den ganzzahligen Bereich „zu heben“ – in unserem Beispiel mit 1.000 zu multiplizieren. Dann klappt es:

Erlkönig 2.0: Mein Vater, mein Vater, jetzt warte doch mal – ich hab hier im Wald kein Mobilfunksignal.

Lieber René,

mein Name ist Irmi, und ich bin begeisterte Leserin deiner Website. Ich wende mich an Dich, weil ich eine besondere Excel-Herausforderung habe und ich auch nach tagelanger Recherche immer noch nicht zu einer Lösung gekommen bin. Du hast bestimmt viel zu tun, vielleicht hast du aber einen schnellen Gedanken dazu, Du würdest mir damit sehr weiterhelfen.

Ich hätte gerne einen Linienchart, in dem Nullwerte (=0) als Lücken angezeigt werden und nicht so unschön auf den die x-Achse kracht, sobald ein Wert mal Null ist. Gibt es dazu eine einfache Lösung?

Habe schon einiges probiert, auch mit einer Hilfstabelle und die Nullwerte als leere Zellen anzeigen lassen, hat nicht funktioniert.

Wäre Dir wirklich sehr dankbar, wenn Du eine Idee hättest!

Ganz liebe Grüße Irmi

Hallo Irmi,

du musst die Nullwerte in den Fehlerwert #NV umwandeln (entweder mit einer WENN-Funktion oder durch suchen und ersetzen) – dann klappt es.

Excel kann zwar Leerzellen auf verschiedene Arten darstellen, aber leider nicht den Wert 0. Der ist halt die Zahl 0.

Schau mal:
https://www.excel-nervt.de/warum-haben-24-stunden-tankstellen-schloesser-an-den-tueren/

Hilft das?

Liebe Grüße :: Rene

Hallo René,

klasse, das hat geholfen! Super, ich hab tagelang darüber nachgegrübelt. Großes Dankeschön! 🙂

Ich bin wirklich begeistert, dass Du mir sofort geantwortet hast, nicht nur, dass ich wirklich eine Lösung dafür gebraucht hab, sondern dass Du mir einfach so hilfst, ohne mich zu kennen. Find das so richtig cool von Dir! Nochmals ganz großes Dankeschön 🙂 Weiterhin viel Erfolg und noch einen schönen Abend 🙂 Liebe Grüße Irmi

So tun, als ob man sich beim Abendessen verschluckt hat, damit man das Bier auf Ex trinken kann …

Hallo René,

ich habe ein paar Online-Trainings zugesagt, für die ich erst jetzt die Themeninhalte bekommen habe und vielleicht kannst Du mir kurz sagen, was folgende Bezeichnung bedeutet? – Diagramme: mehrzeilige Achsenbeschriftung …

Hallo Angelika,

man kann die einzelnen Elemente der Achse weder editieren, also ändern noch formatieren. Ich kann nicht eine Jahreszahl einfärben oder fett formatieren. Ich kann auch nicht die Ausrichtung ändern.

Aber man kann in den Zellen einen Umbruch einschalten ([Alt] + [Enter]) und somit einen Umbruch in der Beschriftung der Achsen erzeugen. Ich schicke dir mal ein (uraltes) Diagramm:

Übrigens: hast du es gelesen? – Messi will seinen Vertrag mit dem FC Barcelona auflösen. Fabrizio Romano schreibt, dass Barcelona auf der Ausstiegsklausel über 700 Millionen Euro bestehe.

Schatz, sieht man es mir an, dass ich schon lange nicht mehr beim Friseur war? – Kinder kommt mal her – der Wischmopp kann sprechen!

So nicht! Wer so etwas macht, wird auf meiner Seite excel-nervt an den Pranger gestellt:

Danke an Mourad Louha für das Bild. Und den Hinweis für alle, die es nicht gesehen haben: die Säule mit 116 ist höher als die Nachbarsäulen mit 114 und 129 und 190. Übrigens: auch das bitte nicht:

Gefunden:

http://freerangestats.info/blog/2020/04/06/crazy-fox-y-axis?fbclid=IwAR1Eajr0InBJwFqTzvyyZwn1jA3VteRPDd95YI7OKzszFwDnQkTmer7bO9M

Und wie macht man so etwas in Excel? Nun – entweder einfach die Datenbeschriftung per Hand ändern:

Oder mit zwei Spalten, wobei die zwei Säulenreihe transparent formatiert wird und „hinter“ die erste, sichtbare Reihe geschoben wird. Von der unsichtbaren werden die Werte angezeigt.

Und noch einmal deutlich: WER DAS MACHT WIRD AN DEN PRANGER GESTELLT!

Wenn der Staubsauger den Vorhang fressen will, kämpft ihr auch bis zum Limit anstatt ihn auszuschalten?

Hallo Herr Martin,

folgende Frage zu Excel 2019:

ich habe im Diagramm das Gitternetz relativ eng gewählt und möchte die Beschriftung der Achsen weniger eng wählen. Die Beschreibung von MS ist leider für mich nicht zielführend.

Können Sie mir eine Hilfestellung geben?

Viele Grüße

p. s.: ich kenne mich an sich einigermaßen mit Excel aus

Hallo Herr A.,

Sie können doch ein Hauptgitter und ein Hilfsgitter einschalten:

Die Abstände der Achsenbeschriftung werden über die Achsenoptionen festgelegt. Dort finden Sie auch die Abstände der Hilfsgitternetzlinien:

Hilft Ihnen das?

Hallo Herr Martin,

so einfach ist es, wenn man es weiß.

Recht vielen Dank für Ihre Hilfe.

Viele Grüße

Frohe Weihnachten

Man sollte einen Wettbewerb ausrufen. Wer hat Lust mitzumachen? Die schönsten Excel-Weihnachtsbäume. Ich fange man an:

Weihnachtsbaum I.
Mit der Funktion WIEDERHOLEN

Oder mit einem Punkte-Diagramm (XY-Diagramm):

Oder mit der Bedingten Formatierung. Damit kann man grüne Kästchen erzeugen (mit den Funktionen ZEILE() und SPALTE(), aber auch die Ampeln als Christbaumkugeln verwenden. Eine Funktion ZUFALLSZAHL() oder ZUFALLSBEREICH hilft:

Und schließlich mit einem Liniendiagramm dessen Flächen ausgefüllt sind:

„So viel Geld für einen Panzer? da krieg ich ja schon einen gebrauchten Jagdbomber.“

Auf unseren Exceltagen 2019, die am 18. und 19. Oktober in München stattfinden werden, wird Heike Hofert (Der-Lerncoach.de) einige spannende Tricks rund um Diagramme präsentieren. Beispielsweise, wie man mit Hilfe eines Pfeils ins Spiel, der eigentlich gar kein Pfeil ist, sondern eine Linie, die im Diagramm einen Anfangs- und einen Endpunkt besitzt, ein Diagramm besser visualisieren kann.

Übrigens: es sind noch Plätze auf unseren Exceltagen frei.

Wenn ich Du wäre, wäre ich gerne ich.

Guten Morgen lieber Rene,

ich hoffe Dir gehts gut und Du zehrst noch ein bisschen an der Erholung Deines Urlaubs 🙂 Mich plagt seit zwei Tagen ein kleines Excel-Problem, vll. hast Du aus dem Stehgreif einen Tipp? Ich finde nichts im Internet.

Gestapeltes Säulendiagramm, ich zeige die Entwicklung von Autos und Motorrädern. Die Gesamtzahl habe ich mir schon ergaunert (nicht in diesem Beispiel), in dem ich eine unsichtbare dritte (=gesamt) Datenreihe hinzugefügt habe und die Beschriftung unten angesetzt habe.

Mein Problem: Ich kann mir toll die absoluten Werte von Auto und Motorrad anzeigen lassen, ABER: ich möchte stattdessen Prozent (noch besser: beides) sehen. Hast Du ne Idee wie das geht?

Hallo Marius,

Zu deinem Problem: Ich sehe zwei Lösungen: Du musst entweder die Daten anders anordnen oder anders formatieren. Im zweiten Beispiel habe ich die Prozentwerte berechnete (Einzelwert / SUMME * 100) um ähnlich Zahlen zu erhalten wie die Originalwerte. Diese habe ich als Säulen darstellen lassen; die Säulen transparent formatiert und die Zahlen als 0,00 „%“ formatiert.

Das einzige Problem beim Nichtstun ist, dass man nie weiß, wann man fertig ist.

Hallo Herr Martin,

ich habe eine kleine Tabelle:

Erste Zeile Datum

Zweite Zeile Gewicht

 

Mit der bedingten Formatierung wird in der ersten Zeile das Gewicht unter 80 kg rot und fett dargestellt.

Zu diesen Daten gibt es ein Liniendiagramm.

Wenn ich auf die Zahlen (Datenbeschriftung) im Diagramm klicke, ist die Funktion Bedingte Formatierung deaktiviert.

Gibt es dennoch eine Möglichkeit bei der Datenbeschriftung eine bestimmte Art einer bedingten Formatierung anzuwenden? Also wenn man in der zweiten Zeile eine Zahl unter 80 einträgt, soll im Liniendiagramm diese Zahl (Datenbeschriftung) ebenfalls automatisch rot und fett werden.

#####

Hallo Herr F.,

Diagramme kennen keine bedingten Zahlenformate. Aber Sie können die „alten“ benutzerdefinierten Zahlenformate verwenden, beispielsweise:

[Rot][>80]0;Standard

Leider geht dies nur mit Farben – nicht mit fett

Schöne Grüße

Rene Martin

Warum haben 24-Stunden-Tankstellen Schlösser an den Türen?

Christian ist genervt. Er hat eine Tabelle mit Werten aus denen er ein Kreisdiagramm erzeugt. Christian lässt sich die Datenbeschriftungen anzeigen – die Werte mit 0 werden auch im Diagramm angezeigt. Eigentlich sind sie obsolet.

Ein manuelles Löschen bringt auch nicht viel, denn wenn die Werte sich ändern, würde nun nichts mehr angezeigt werden. Wir benötigen also eine Art „bedingte Anzeige“ – bei 0 wild keine Beschriftung angezeigt; bei jedem anderen Wert schon.

Christian findet die Lösung: #NV

Man muss die Daten etwas modifizieren – statt der Zahl 0 wird nun #NV angezeigt. Denn – dieser Wert wird übergangen.

Und jetzt ist Christian nicht mehr genervt.

Die Überschrift des Diagrammtitels kann man übrigens aus dem Textfeld mit

= [Klick auf die Zelle]

verknüpfen.

Danke, Christian, für diesen guten Tipp mit #NV.

 

Da entdeckt man die to-do-Liste von 2010 und siehe da: sie ist noch brandaktuell.

Gestern in der Excel-Schulung haben wir eine Alterspyramide erstellt. Natürlich muss man den einen Zwei negativ, den anderen positiv darstellen.

Es kam die Frage, wie man die Achse manipulieren könne, damit der negative Zwei auch mit positiven Zahlen beschriftet ist. Die Antwort: Man muss das Zahlenformat ändern in 0;0. Die erste 0 steht für positive Zahlen, die zweite für negative. Sie werden nun ohne Minuszeichen dargestellt.

Seitdem ich das Wort Dingsbums kenne, kann ich alles erklären.

Excel kann in Diagrammen nicht die Beschriftung der y-Achse nummerieren. Also machen wir das per Hand: Ich füge eine Form ein, beschrifte sie mit den Zahlen von eins bis zehn.

Da die Abstände nicht stimmen, füge ich eine Absatzmarke ein. Um sie auf den korrekten Zeilenabstand zu bekommen, verkleinere ich die Schriftgröße auf 1 pt. Nun kann ich mehrere Absatzmarken einfügen und erhalte so etwas den korrekten Abstand.

Zugegeben: gut ist es nicht. Elegant auch nicht. Aber eine einfache Lösung.

Keiner bewegt sich – Mutti hat geputzt!

Besteht eine Liste aus einer Datenreihe, wird die Überschrift ins Diagramm übernommen:

Besteht eine Liste jedoch aus mehreren Spalten, werden Überschriften nicht im Titel angezeigt (nur in der Legende):

Die Kamera ist nicht unbedingt das eleganteste Werkzeug.

Abhilfe schafft ein Verweis auf die Zelle, in der sich der Text befindet. Das funktioniert in der Titelzeile

oder auch eine Form, die in das Diagramm eingefügt wurde.

Achtung: Die Eingabe muss über die Bearbeitungsleiste funktionieren! Und: man muss den Tabellenblattnamen angeben!

Von Nichts kommt nichts

Ich habe in meinem Beitrag über das „Nichts“ in Excel schon darauf hingewiesen: Excel ist nicht konsequent in der Behandlung von 0, leeren Zelle und „“. Bedauerlicherweise fehlt auch ein Wert NULL, wie er aus Datenbanken bekannt ist. Das stiftet Verwirrung und produziert Probleme.

Ein Liniendiagramm zu erstellen ist nicht schwierig:

Ein einfaches Liniendiagramm

Ein einfaches Liniendiagramm

Schwierig wird es jedoch, wenn die Werte nicht – wie oben – eingegeben wurden, sondern berechnet wurden. Liefert die Formel „“ oder 0, so wird dieser Wert als 0 interpretiert und auf der Achse als 0 angezeigt.

Ich wüsste nur zwei Lösungen, um dies zu umgehen: Per Programmierung (VBA) alle Formeln suchen, die den Wert 0 oder „“ liefern und diese Formeln löschen oder im Diagramm alle Datenpunke durchlaufen. Sollten sie den Wert 0 besitzen, werden sie ausgeblendet (transparent formatiert). Beides ist mit einem Aufwand verbunden, wenn man nicht die Werte oder das Diagramm manuell anpassen möchte.

Ein "unglückliches" Liniendiagramm - Unglücklich, weil in den Jahren 2008 - 2011 keine Werte für den Cash Flow vorlagen. 0 ist also falsch!

Ein „unglückliches“ Liniendiagramm – Unglücklich, weil in den Jahren 2008 – 2011 keine Werte für den Cash Flow vorlagen. 0 ist also falsch!

X, Y und ein bisschen Text – das ist doch nicht zuviel verlangt, oder?

Hallo Herr Martin,

ich möchte gerne in einem XY-Diagramme (Punktediagramm) die Beschriftung der Daten anzeigen lassen. Geht das?

X und Y und Beschriftung

X und Y und Beschriftung

Man muss die Daten „richtig“ anordnen, sich die Datenbeschriftungen anzeigen lassen und dann – leider für jede Beschriftung einzeln – statt des Wertes die Beschriftung verwenden.

Das heißt: auch das ist ebenso mühsam, wie die Beschriftungen per Hand einzugeben. Die XY-Diagramme weisen einige Schwächen auf.

Wasserfall

Mit etwas Rechnen und Schummeln konnte man schon in den alten Excel-Versionen ein Wasserfall-Diagramm erstellen. Nun kam die Frage auf, wie man eine Linie zwischen den Balken erzeugen kann. Auch hier: mit etwas Schmummeln. Man benötigt die Werte zwei Mal (kann man ja verknüpfen) und muss dann den teil des Diagramms von Säule in Linie verwandeln. Eine Säulenreihe wird weiß formatiert und verdeckt die dahinterstehende Reihe.

20160315Wasserfall

Von Äpfeln und Birnen

Eigentlich geht es nicht. In einer Liste stehen Werte im Einerbereich (1,68, 1,39 und 1,54). Diese sollen in einem Diagramm mit Werten im Millionenbereich dargestellt werden (38.989.000, 42.125.000 und 31.515.000).

Nun – mit einem Trick funktioniert es: Man „hievt“ kleinen Daten hoch – sie werden mit 100.000.000 multipliziert. Das Diagramm stellt die Balken nun so dar, damit man die Werte vergleichen kann. Damit nun auch die Zahl „korrekt“ dargestellt wird, muss man sie formatieren:

#… stellt nur die ersten Ziffern dar.

#…,00 zeigt zwei Nachkommastellen. Erstaunlich übrigens, dass Excel dieses Format in das mit gänzlich unverständliche Zahlenformat

#,00…

Sei’s drum – das Diagramm funktioniert:

geht doch, ihr Äpfel, ihr Birnen.

geht doch, ihr Äpfel, ihr Birnen.

Die gute alte Mark – quatsch! – so gut war die gar nicht!

In einem Diagramm werden Euro-Preise angezeigt. da einige ältere Preise vorhanden sind, sollen diese in Euro umgerechnet dargestellt werden.

Euro und DM

Euro und DM

Nun wäre es ja schön, wenn die DM-Preise auch im Diagramm dargestellt wären – das heißt: die ersten Punkte sollen sowohl mit DM als auch mit Euro beschriftet sein. Dazu wird der Datenbereich erweitert:

20160314DM02

Die Linie wird auf eine Sekundärachse gelegt und die Skalierung dieser Achse verdoppelt (wir erinnern uns: 1 Euro entspricht 1,95583 DM)

20160314DM03

Die Linie wird transparent formatiert; die Markierungspunkte entfernt, das ganze noch etwas formatiert – und voilà – das Ergebnis:

20160314DM04

20160314DM05

Diagramme schummeln XVIII

Blasen – mehr als drei Dimensionen?

Mit einem Blasendiagramm kann man drei Dimensionen darstellen – nicht sehr übersichtlich, wie ich finde, aber es funktioniert: x-Achse, y-Achse, Blasendurchmesser.

Das fertige Diagramm - die Beschriftung wurde per Hand eingetragen.

Das fertige Diagramm – die Beschriftung wurde per Hand eingetragen.

Geht eine vierte Dimension? Die Antwort: ja – mit Schummeln! Man muss die Daten des Blasendurchmessers 1 kumulieren mit den neuen Werten (2). Und nun eine zweite Datenreihe anlegen. Diese „hinter“ die erste schieben. Klappt:

Geht doch!

Geht doch!

Zugegeben: mit einigen WENN-Funktionen bekommt man auch eine „bedingte Formatierung“ hin. Allerdings: es ist schon etwas mühsam!

Ist ja schon sehr geschummelt ...

Ist ja schon sehr geschummelt …

Diagramme schmummeln XVII

Um ein bestimmtes Diagramm erzeugen zu können, ist es manchmal nötig, die Werte auf zwei Zeilen (oder Spalten) aufzuteilen. Dabei ergibt sich nun die nicht ganz schöne Lösung, dass die Beschriftung der Zeile vor einem Wert steht, aber nicht im der Mitte zwischen beiden Balken:

nicht ganz gelungen

nicht ganz gelungen

Meine Überlegung war nun, eine Leerspalte einzufügen. Den Beschriftungen zwei Spalten zuzuweisen:

1. Idee

1. Idee

Allerdings ignoriert Excel die leere Spalte. Trägt man nun Daten ein, werden beide Informationsspalten angezeigt. Benötigt wird aber nur die erste.

Clever ist er nicht – aber der Trick funktioniert: Trägt man in die zweite Spalte ein Leerzeichen ein, wird dies als Inhalt interpretiert und die erste Spalte wird um 90° gedreht in der Mitte sämtlicher Balken dargestellt.

geht doch!

geht doch!

Diagramme schmummeln XVI

Eine hübsche Frage in einer Excel-Schulung:

„Wir erstellen mit Hilfe einer Pivottabelle eine Liste. Darin befinden sich Geldbeträge – positive und negative. Im Diagramme, das auf diesen Daten aufsetzt, sollen die negativen allerdings nicht nach unten zeigen, sondern mit den positiven nach oben weisen, damit man sie miteinander vergleichen kann.“

nach oben und unten - so nicht!

nach oben und unten – so nicht!

Meine erste Antwort war: „Dann multiplizieren Sie diese Daten mit -1 – dann funktioniert es.“ Dann habe ich überlegt, ob es nicht noch eine andere Lösung gibt.

„Was“, so war meine Überlegung – „wenn man die Daten aufsplittet? Die positiven in einer Spalte und die negativen in einer anderen Spalte? Das kann man leicht mit einer WENN-Funktion erreichen.“

Die Daten werden "getrennt".

Die Daten werden „getrennt“.

Darauf wird eine Pivottabelle aufgesetzt. Nun befinden sich die Daten in zwei Kategorien:

die Pivottabelle

die Pivottabelle

Auf ihr kann man – wie zuvor – ein Diagramm aufsetzen:

Diagramm20151021_5

Der Unterschied: die nach unten zeigenden Stalaktiten können als eine Datenreihe markiert werden und auf eine Sekundärachse geschoben werden:

Sekundärachse

Sekundärachse

Nun hängt der Balken allerdings nach unten. Kein Problem – man kann die Richtung der Sekundärachse umkehren:

Datenrichtung umdrehen

Datenrichtung umdrehen

Ich habe zwei Mal hinsehen müssen. Dann habe ich entdeckt, dass die Primärachse in einem anderen Datenbereich läuft als die Sekundärachse. Das muss natürlich unbedingt angepasst werden!

die Wertebereiche sind nun gleich groß

Und nun kann das Diagramm formatiert werden – DAS ist dann sicherlich nicht mehr schwierig!

das fertige Diagramm

das fertige Diagramm

Negative Balken rot

Können Sie mir mal bitte helfen? Wie macht mein Kollege denn das? Die Balken, die nach unten hängen, also die Stalaktiten, denen negative Werte zugrunde liegen, werden rot eingefärbt. Muss ich die einzeln markieren und formatieren?

Rote Stalaktiten

Rote Stalaktiten

Die Antwort: Nein. In Excel 2013 gibt es eine Option „invertieren, falls negativ“. Wenn Sie diese Option auswählen, können Sie neben der Farbe für die Stalagmiten auch die Frabe für die Stalaktiten auswählen. Die Schriftfarbe für die Beschriftung müssen Sie allerdings „per Hand“ formatieren. Und: die Beschriftung der Achse können Sie leider nicht ändern.

Diagrammteile löschen oder ausblenden

Zugegeben: Ganz konsequent ist es nicht: Viele Teile (Elemente, Objekte) in einem Diagramm kann man löschen; einige jedoch nicht. Sollen sie verschwinden, muss man sie „transparent“ formatieren.

Beispiele: Titel, Legende, Gitternetzlinien, Datenreihen, Achsen und Beschriftungen können gelöscht werden. Einzelne Datenpunkte jedoch nicht, wie beispielsweise ein Kreissegment (Bundestag) oder ein Datenpunkt in einem Liniendiagramm (Tangens). Diese müssen transparent formatiert werden. Ebenso die Sekundärachse – wird sie nicht benötigt, darf man sie nicht löschen – sonst werden alle Daten wieder auf die erste Achse „geschoben“. Auch sie muss man wegformatieren.

Jeweils ein Datenpunkt wurde wegformatiert.

Jeweils ein Datenpunkt wurde wegformatiert.

Diagramme20150725_1

Sekundäre x-Achse und ihre Probleme

Heute morgen habe ich ein interessantes Diagramm in der Zeitung gesehen und versucht es nachzubauen. Dabei bin ich (mal wieder) an die Grenzen von Excel gestoßen:

In einem Balkendiagramm werden Werte aus unterschiedlichen Bereichen miteinander verglichen. Man kann die beiden Balkenreihen zwar hintereinander legen, aber nicht zueinander verschieben. Damit wäre es unbrauchbar, oder?

Der hintere wird verdeckt.

Der hintere wird verdeckt.

Die Lösung: Man muss die Daten versetzt zueinander anordnen. Dann kann man eine der beiden Balken auf eine sekundäre Achse legen. Man kann die Balkendicke verändern, die Werte anzeigen lassen, die Skalierung der Achsen verändern und das Ganze formatieren.

Geht doch!

Geht doch!

Zeilenumbruch

Hallo Herr Dr. Marting,

vielen Dank noch mal für die informative PowerPoint-Schulung bei uns im Haus!

Ich hätte gleich mal noch eine Frage… 😉

Ich habe jetzt meine kopierte Grafik rausgenommen und durch eine Grafik in PowerPoint ersetzt. Bei der Datenbeschriftung habe ich jetzt aber das Problem, dass ich die Beschriftung nicht anpassen kann, denn ich bekomme nicht die Möglichkeit, die Textfelder anzupassen (s. u. zweites Bild).

Haben Sie da noch eine Idee, wie man das lösen kann oder muss ich doch manuell ein Textfeld statt der Datenbeschriftung einfügen?
Vielen Dank und viele Grüße

####

Schlechte Trennung

Schlechte Trennung

Hallo Herr S.,

PowerPoint 2013 hat so eine Option – DORT kann ich das Textfeld kleiner ziehen (übrigens sogar durch eine andere Form ändern). Aber das nützt Ihnen nichts – Sie haben PP 2010.

Neben Schriftgröße verkleinern können Sie auch in Excel mit der Tastenkombination [Alt]+[Enter] Einen Zeilenumbruch organisieren – DER wird in PP übernommen:

Manueller Zeilenumbruch

Manueller Zeilenumbruch

ABC-Analyse im Diagramm

Mein Chef möchte unbedingt mit eines Diagramms eine ABC-Analyse visualisiert haben. Das Problem: Ich erhalte jede Woche neue Daten unserer Produkte. Das bedeutet: Ich müsste jede Woche die Daten neu sortieren, berechnen, das Diagramm erstellen und die kumulierten Bereich <80%, beziehungsweise <95% neu formatieren.

ABC-Analyse

ABC-Analyse

Die Antwort: Wenn Sie sich mit Funktionen ein wenig auskennen, funktioniert es auch dynamisch:

* Berechnen Sie mit der Funktion =KGRÖSSTE die größte Zahl. Verwenden Sie hierzu die Funktion ZEILE(), damit Sie die Funktion nach unten ziehen können.

* Lassen Sie sich mit VERGLEICH die Zeilennummer anzeigen

* Ermitteln Sie die Artikelbezeichnung mit INDEX

* Berechnen Sie den Prozentwert

* Berechnen Sie den kumulierten Prozentwert

* Lassen Sie sich mit einer WENN-Funktion in einer neuen Spalte die Zahlen anzeigen, die <80% sind. Die übrigen erhalten den Wert 0.

* Lassen Sie sich mit einer WENN-Funktion in einer neuen Spalte die Zahlen anzeigen, die sowohl >=80% als auch <95% sind (mit einem UND). Die übrigen erhalten den Wert 0.

* Lassen Sie sich mit einer WENN-Funktion in einer neuen Spalte die Zahlen anzeigen, die >=95% sind. Die übrigen erhalten den Wert 0.

Die berechneten Werte

Die berechneten Werte

Wenn Sie nun neue Werte erhalten, wird das Diagramm „neu gezeichnet“.

Veränderte Werte

Veränderte Werte

Diagramme schummeln XIV

Ich würde es ja anders machen. Aber mein Chef besteht darauf. Er möchte unbedingt ein Netzdiagramm haben. Ich konnte ihn nicht überzeugen, dass ein Balken- oder Liniendiagramm besser geeignet wäre. Nun – wie mache ich es, dass die Linienenden nicht miteinander verbunden sind, sondern als Stäbe aus der Mitte herausragen?

Gewünschtes Netzdiagramm

Gewünschtes Netzdiagramm

Die Antwort: Sie müssen die Daten etwas anders anordnen – schreiben Sie jeweils unter jeden Wert eine Null – dann wird das Ende des Stabes mit dem Mittelpunkt verbunden. Den Rest können Sie sicherlich formatieren.

Gewünschtes Netzdiagramm

Das fertige Diagramm

Bedingte Formatierung bei Diagrammen

Hallo Du. Ich liebe die bedingte Formatierung. Klasse Sache. Kann ich die eigentlich auch bei Diagrammen einsetzen?

Die Antwort: Nicht direkt. Aber mit einem kleinen Trick funktioniert es.

Bedingte Formatierung bei Diagrammen

Bedingte Formatierung bei Diagrammen

Man muss zusätzlich zu der Spalte der Werte noch eine berechnete Spalte einfügen. Wenn man beispielsweise das Maximum und das Minimum farblich kennzeichnen möchte, kann man berechnen:

=WENN(B2=MAX($B:$B);B2;0)

=WENN(B2=MIN($B:$B);B2;0)

Diese Reihen übernimmt man in das Diagramm und legt sie vor die anderen Balken. Die Werte 0 befinden sich ja am Boden – sind also nicht sichtbar. Werden nun die drei Reihen mit jeweils unterschiedlichen Farben formatiert, erkennt man gut den oder die Spitzenwerte und das Minimum oder die kleinsten Werte.

Mit einem Trick funktioniert es.

Mit einem Trick funktioniert es.

Übrigens: Mit einem ähnlichen Trick kann man sich auch nur den größten und den kleinsten Wert als Zahl anzeigen lassen. Leider funktioniert die bedingte Formatierung hier nicht. Werden die Zahlen jedoch benutzerdefiniert mit

0;0;““;““

formatiert, dann werden die Nullwerte ausgeblendet. Und die Diagramme übernehmen es als Beschriftung.

Nur die größten und kleinsten Zahlen werden angezeigt.

Nur die größten und kleinsten Zahlen werden angezeigt.

Selbstverständlich ist das Diagramm dynamisch. Das heißt: Nach Ändern eines Wertes erscheinen die entsprechenden Zahlen und Farben.

Das Diagramm ist dynamisch.

Das Diagramm ist dynamisch.

Diagramme schummeln XII

Das Beispieldiagramm aus dem Beitrag „Balken mit Werten und Prozenten“sieht schon ganz gut aus. Aber kann ich im Vergleich dazu noch eine gestapelte Säulenreihe auf dem gleichen Diagramm haben. Also etwa so:

So sollte es aussehen

So sollte es aussehen

Die Antwort: dafür sind einige Schritte nötig. Schreiben Sie die Daten wie folgt in die Tabelle und erstellen daraus ein Diagramm mit gruppierten Säulen:

Die Daten

Die Daten

Schritt I

Schritt I

Anschließend wechseln Sie Zeile und Spalte, damit die Beschriftungen bei der x-Achse verwendet werden:

Zeile und Spalte wechseln

Zeile und Spalte wechseln

Schließlich legen Sie die die eine Balkenreihe auf eine Sekundärachse

Sekundärachse

Sekundärachse

und verkleinern bei der anderen Achse das Maximum auf 1 (= 100%).

Maximum: 100% (=1)

Maximum: 100% (=1)

Schalten Sie die Datenbeschriftung ein und formatieren sie:

diagramme20150223_6

diagramme20150223_7

Datenbeschriftung

Datenbeschriftung

Verändern Sie die Abstandsbreite, damit die eine Balkenreihe vor der anderen steht:

Abstandsbreite

Abstandsbreite

Wandeln Sie das Diagramm nun in ein gestapeltes Diagramm um. Die prozentualen Werte wurden transparent formatiert.

Gestapeltes Diagramm (100%)

Gestapeltes Diagramm (100%)

Lassen Sie sich dort die Werte anzeigen:

Beschriftungen

Beschriftungen

Die Achsenbeschriftungen kann man wegformatieren:

Ohne Achsenbeschriftung

Ohne Achsenbeschriftung

oder natürlich auch anzeigen lassen:

Mit einer Achse

Mit einer Achse

Wichtig: Löschen Sie die Dummy-Überschrift „xxx“!

Den Rest nach Gusto zu formatieren dürfte dann kein Problem mehr darstellen:

Das (fast) fertige Diagramm

Das (fast) fertige Diagramm

 

Balken mit Werten und Prozenten

Hallo zusammen. Mal ne Frage. Mein Kollege hat ein supertolles Diagramm gemacht, das sich auch ändert, wenn die Daten geändert werden. Aber ich dachte immer, dass es nicht möglich ist, auf einem Balken Prozentwerte UND Absolutzahlen anzuzeigen. Wie hat er denn das gemacht?

Balken - prozentual und absolu

Balken – prozentual und absolut

Die Antwort: Es ist auch nicht möglich. Aber man kann sich mit einem Trick behelfen: Schreiben Sie die Daten in Excel und berechnen Sie die Prozentwerte. Erstellen Sie ein Diagramm „gruppierte Säulen“, also die Säulen nebeneinander.

Gruppierte Säulen

Gruppierte Säulen

Die Balken liegen hintereinander. Markieren Sie eine Reihe und weise Sie dieser eine Sekundärachse zu. Nun erscheinen auf der einen Achse die Prozentwerte, auf der anderen die Absolutwerte.

Sekundärachse

Sekundärachse

Lassen Sie sich die Werte anzeigen. Wenn Sie möchten, können Sie die „großen“ Zahlen um 90° drehen, die Prozentwerte über die Balken stellen.

Beschriftung

Beschriftung

Formatieren Sie nun die Sekundärachse weg (Achtung: Nicht löschen!). Der Rest dürfte dann kein Problem mehr darstellen.

Sekundärachse wegformatieren

Sekundärachse wegformatieren

 

Diagramme schummeln X

Das ist ein lustiges Diagramm – es zeigt die Verteilung der Noten. Wie haben die denn das gemacht?

Notenverteilung

Notenverteilung

Die Antwort: Wenn Sie das Diagramm markieren und über Entwurf / Daten / Diagrammtyp ändern nachsehen, dann stellen Sie fest, dass es hierbei nicht um ein xy-Diagramm handelt, sondern um ein Balkendiagramm. Am Ende der Balken werden die Noten als Wert angezeigt und die Balken transparent formatiert.

Diagramme schmummeln X

Ich habe vor Kurzem ein interessantes Diagramm gesehen. Dort laufen Balken von einem Wert bis zu einem Wert, das heißt sie bestimmen die Spanne. Auf den Balken befinden sich Länderkennzeichen, die die gewichtete Mitte darstellen. Kann man das in Excel realisieren?

Diagramm mit Spannweite und gewichteter Mitte

Diagramm mit Spannweite und gewichteter Mitte

Ja. So etwas kann man machen. Gehen Sie folgendermaßen vor:

1. Tragen Sie die Daten ein. Wählen Sie ein 2D-Balkendiagramm mit gruppierten Balken:

Das Diagramm wird erstellt.

Das Diagramm wird erstellt.

2. Ändern Sie die Kategorien in umgekehrter Reihenfolge

3. Ändern Sie die Reihenachsenüberlappung auf 100%. Dann liegen die Balken hintereinander.

Die Balken liegen hintereinander.

Die Balken liegen hintereinander.

4. Schieben Sie den hinten liegenden Balken nach vorne, indem Sie Entwurf / Daten / Daten auswählen die Daten „nach unten“, beziehungsweise „nach oben“ schieben.

Die Reihenfolge wird geändert.

Die Reihenfolge wird geändert.

5. Formatieren Sie den ersten Balken mit weißer Füllfarbe und ohne Rahmenlinie.

Weiße Balken

Weiße Balken

6. Formatieren Sie die mittleren Balken ohne Füllfarbe und fügen Sie ihnen die Rubrikennamen als Beschriftung hinzu.

DIe mittleren Balken werden transparent.

Die mittleren Balken werden transparent.

Und der Rest ist dann etwas Fleißarbeit:

Diagramm mit Spannweite und gewichteter Mitte

Diagramm mit Spannweite und gewichteter Mitte

 

Diagramme schummeln IX

Ich habe nicht von jedem Jahr einen (Meß-)Wert. Kann ich die Daten trotzdem so abtragen, dass die Abstände der Datumsangaben auf der x-Achse korrekt sind, das heißt: im Verhältnis zu den wirklichen Abständen?

das wäre falsch!

das wäre falsch!

Ja. Sie müssen entweder die x-Achse als Datumsachse formatieren oder statt eines Liniendiagramms den Typ xy-Diagramm wählen.

das ist korrekt

das ist korrekt

oder so: ein xy-Diagramm

oder so: ein xy-Diagramm

Diagramme schummeln VIII

Kann man die Texte der Achsenbeschriftungen in Diagrammen umbrechen lassen?

Die Antwort: Nein! Aber wenn Sie in der Tabellen per Hand einen Umbruch mit der Tastenkombination [Alt]+[Enter] erzeugen, wird dieser Umbruch im Diagramm übernommen.

Die Texte der Achsenbeschriftung wurden umgebrochen.

Die Texte der Achsenbeschriftung wurden umgebrochen.

Diagramme schummeln VII

Manchmal ist es nötig, dass in einem Diagramm der kleinere Wert über dem größeren stehen muss. Kann man das?

Der kleinere Wert (Linie) steht über dem größeren Wert (Säule).

Der kleinere Wert (Linie) steht über dem größeren Wert (Säule).

Klar. Im ersten Schritt muss man eine der beiden Reihen auf eine Sekundärachse platzieren. Anschließend muss die Skalierung der Linie, die nach oben geschoben werden soll, weiter „unten“ beginnen, also beispielsweise nicht bei 0, sondern bei -5. Und schließlich muss die Säulenreihe nach unten gedrückt werden, indem das Maximum angehoben wird, also beispielsweise von 40 auf 80.

Schritt 1: Sekundärachse

Schritt 1: Sekundärachse

Schritt 2: Minimum absenken

Schritt 2: Minimum absenken

Schritt 3: Maximum der anderen Achse erhöhen

Schritt 3: Maximum der anderen Achse erhöhen

Diagramme schmummeln VI

Eine Kurve (beispielsweise Tangens) läuft ein einem Punkt Richtung +∞. Kommt man von der anderen Seite, läuft er gegen -∞. Kann man das verhindern?

Nein, verhindern kann man es nicht, aber man kann es transparent wegformatieren. Markieren Sie den Linienabschnitt und wählen Sie das Linienformat „Kein Rahmen“.

Geht das weg? Der Sprung zwischen +∞ und -∞?

Geht das weg? Der Sprung zwischen + und -?

Man kann den Sprung "wegformatieren".

Man kann den Sprung „wegformatieren“.

Diagramme schummeln IV

Eigentlich geht es nicht. Man kann bei einem Balkendiagramm nicht die y-Achse (die Kategorienachse) linksbündig formatieren.

Mit einem Trick geht es doch: Man könnte hinter die Kategorien (hier: Länder) Leerzeichen eingeben. Das ist allerdings werde elegant noch sauber noch schnell erledigt.

Eigentlich kann die y-Achse nicht linksbündig formatiert werden.

Eigentlich kann die y-Achse nicht linksbündig formatiert werden.

Man könnte hinter die Texte Leerzeichen eingeben - allerdings ist das keine gute Lösung.

Man könnte hinter die Texte Leerzeichen eingeben – allerdings ist das keine gute Lösung.

Diagramme schummeln III

Kann man in einem Liniendiagramm waagrechte Linien erzeugen? Wenn man beispielsweise darstellen möchte, dass ein Beitragssatz oder Prozentsatz vom Jahr x bis zum Jahr y unverändert geblieben ist?

Die Antwort ja – wenn man die Daten geschickt einträgt – für jedes Jahr den Wert den er bis zum Sprung und nach dem Sprung gemacht hat. Und zusätzlich ein XY-Diagramm verwendet.

Diagramme schummeln III

Diagramme schummeln III

Diagramme schummeln III

Diagramme schummeln III

Diagramme schummeln II

Eine Teilnehmerin in einer Excelschulung hat mich gefragt, wie man ein Säulendiagramm erstellen kann, bei dem eine Säule differenziert wird in drei Teile, die daneben stehen, allerdings übereinander gestapelt. Oder wie man eine Säule mit drei gestapelten Säulen vergleichen kann. Meine erste Reaktion: Das geht nicht! Dann habe ich ein wenig überlegt und die Daten richtig angeordnet und war verblüfft, dass es doch funktioniert. Man kann sogar eine „Lücke“ zwischen den einzelnen Säulengruppen lassen. Man muss nur die Daten vor dem Erstellen eines Diagramms markieren.

Unmögliche Diagramme?

Unmögliche Diagramme?

Man muss die Daten richtig gruppieren.

Man muss die Daten richtig gruppieren.