Diagramme mit Lücken

Liniendiagramm mit Datenlücken

Excel, alle Versionen

Folgende Situation ist gar nicht einmal so selten: Umsatzdaten werden von einer Software (SAP, Oracle, …) als Excel-Datei (*.xls, *.xlsx, eventuell auch *.csv) für beispielsweise jeden Monat erstellt. Diese einzelnen Berichte mit den Daten, die in jedem Fall als Zahlen und nicht als berechneten Formelergebnissen in den Zellen vorliegen, werden dann von Hand oder per Makro in eine neue Excel-Mappe eingefügt oder importiert. Aus diesen in einzelnen Tabellenblättern vorliegenden Monatsberichten wir danach in einem einzelnen Sheet ein Halbjahresbericht und auf wiederum dieser Daten anschließend ein Liniendiagramm erstellt. Der hier für dieses Script vorliegende Stand ist, dass nur die einzelnen fremderzeugten Excel-Dateien vorliegen. Die zu importierenden Daten finden Sie hier als *.zip-Datei.

Hinweis: In dieser Musterlösung wird in erster Linie mit Excel 2010 gearbeitet. Die vorrangigen Abbildungen sind mit dieser Version erstellt worden. – Weiterhin gilt, dass die Datenbasis stets eine *.xlsx – Datei ist. Das ältere Format (*.xls) und auch das *.csv – Format werden nicht besonders angesprochen. – Und last but not least: Sie werden vermehrt den Hinweis auf ein Seminar, auf ein Training lesen. Sofern Sie sich diese Unterlage aus dem Netz geholt haben, ist das für Sie natürlich nicht relevant.

▲ nach oben …

Tabelle anlegen, Daten importieren

So weit, so gut. Nun kommt es aber immer wieder einmal vor, dass die Daten unvollständig sind. Für diesen oder jenen Monat ist für das Produkt X oder den Artikel Y kein Umsatz angegeben. „Kein Umsatz“ bedeutet in diesem Fall nicht Null Euro (oder jede andere Währung), sondern die Zahl fehlt einfach, die Zelle ist wirklich leer, auch nicht mit dem Inhalt "". Diese Unterscheidung ist wichtig, denn es ist ja durchaus bedeutend, ob in einem Bericht ein Produkt in einem Monat gar nicht verkauft wurde oder ob der vorhandene Umsatz noch nicht gemeldet ist. Nach dem Import oder Einfügen in das jeweilige Monats-Arbeitsblatt der Mappe sehen die Monatsdaten dann so aus, wie hier für Januar und Februar gezeigt:

Umsatz Januar

Umsatz Januar

Umsatz Februar

Umsatz Februar

 Die fehlenden Daten (Februar, B3) treten in diesem Projekt öfter einmal auf, der Demonstration wegen. Um dem Ziel einen kleinen Schritt näher zukommen, legen Sie nun erst einmal ein Arbeitsblatt in einer leeren Mappe an, das so aussieht:

Die leere Datentabelle

Die leere Datentabelle

Benennen Sie die Tabelle im Register um, sie soll den Namen Tabelle0 (Null, nicht o) haben. Übertragen Sie nun aus den einzelnen Excel-Dateien (01-Jan.xlsx, 02-Feb.xlsx, 03-Mär.xlsx, …) aus dem jeweils ersten und einzigen Tabellenblatt die Daten in diese eben erstellte Arbeitsmappe. Wie Sie da vorgehen, bleibt Ihnen überlassen. Es sollte aber für jeden Monat eine einzelne Tabelle (Register, Arbeitsblatt) existieren. Alternativ können Sie auch dieses Datei öffnen, wo (im Format Excel 2007/2010) alle Tabellenblätter der Monate der ersten sieben Monate bereits eingefügt sind. Die einzelnen Monate sind in dieser Datei als *.zip gepackt.

Wenn Sie neugierig sind, wie ich mir das Leben mit solchen Zusammenstellungen leichter mache, dann sollten Sie in jedem Fall die Datei öffnen und sich die Tabelle1 anschauen. Das erforderliche Passwort bekommen Sie von uns per e-Mail. Vorher aber sollten Sie in Eigenregie versuchen, die Werte der einzelnen Monate in die Tabelle0 zu übertragen. Das sind maximal 5 Minuten, die Sie besser einschätzen lassen, ob der Aufwand mit den von mir verwendeten Formeln und Funktionen sich lohnt oder nicht.

Die Grundlage ist gelegt. Und bevor Sie mehr oder weniger intensives Interesse zeigen: Ja, es gibt noch mehrere versteckte Tabellenblätter. Und Sie werden diese innerhalb eines Seminars mit Sicherheit zu Vergleichszwecken zu gegebener Zeit sehen und nutzen. Wenn Sie diese Musterlösung aus dem Internet haben (www.excel-ist-sexy.de), dann reicht eine (eventuell) zweite Mail, um die erforderlichen Passworte zu bekommen.

▲ nach oben …

Leere Zellen …

Wenn Sie die Daten aus den einzelnen Monaten per copy&paste in die einzelnen Spalten der Tabelle0 eingefügt haben, werden Sie das folgende Problem kaum nachvollziehen können: Zellen, die im Ursprungsblatt leer sind, werden auch im Zielbereich wirklich leer sein. Das hätte dann Auswirkungen, welche ganz unten beim eigentlichen Thema „Lücken im Diagramm“ beschrieben.

Aber wenn Sie sich das Leben leicht machen wollen, weil vielleicht jede Woche neue Daten generiert werden, dann sieht das schon anders aus. Um das im Falle eines Falles nachvollziehen zu können, geben Sie (immer noch in Tabelle0) in die erste Datenzelle des Februar (C2) diese Formel ein: =Februar!B2 und kopieren Sie die Formel nach unten. Der Erfolg: In C3 steht eine Null, obwohl die verknüpfte Zelle im Arbeitsblatt Februar leer ist. Und das passiert mit allen verknüpften Zellen. Auch wenn Sie derzeit noch nicht den tieferen Sinn dieses Hinweises erkennen, sollten Sie das im Bewusstsein behalten.

Um eine gemeinsame Grundlage im Seminar zu haben, blenden Sie nun die Tabelle1 ein. Dort sind bei den Umsatz-Daten nur verknüpfte Zellen, allerdings wurde die Formel nur ein einziges Mal eingegeben. Danach habe ich diese Formel einfach nur in alle anderen Zellen kopiert. Falls Sie zu den Usern gehören, die sich das Ganze autodidaktisch erarbeiten, hier die Formel aus der Zelle B2:

=INDIREKT(B$1&"!"&ADRESSE(ZEILE();2))

Dabei ist zu berücksichtigen, dass die einzelnen Tabellenblätter (Register) den voll ausgeschriebenen Namen des jeweiligen Monats haben. Der Name ist identisch mit dem Text der jeweiligen Spalte in Zeile 1.

In Tabelle1 werden Sie rasch erkennen, dass ich schon einige Formatierungen vorgenommen habe. Einerseits sind dieses die Währungsbezeichnungen (das „T€“ habe ich mir verkniffen, es ähnelt zu sehr dem geflügelten Wort des „Teuro“). Andererseits habe ich alle Zellen mit Nullwerten farbig hinterlegt, einige davon sind so formatiert, dass die Zelle leer erscheint. Dennoch steht der Wert Null in der Zelle, sie ist nicht leer.

▲ nach oben …

Ein Linien-Diagramm erstellen

Gut, erstellen Sie aus den Daten des ersten Halbjahres ein Liniendiagramm. Die einfache Form reicht erst einmal aus und es soll kein getrenntes Arbeitsblatt sein, sondern als Objekt im Tabellenblatt erscheinen. Das Ganze wird dann etwa so aussehen:

Diagramm Januar

Diagramm Januar

Sie erkennen, dass für Februar, März und Juni Null-Umsätze des einen oder anderen Produkts ausgewiesen sind. Die Linie stößt unten auf der Grundlinie des Diagramms auf. Und das ist ja auch ganz logisch, denn wie eben bereits erwähnt, ist beispielsweise für das Produkt 2 im Februar ein Umsatz von 0 € in der Tabelle0 „berechnet“ worden. Der März hat da eine Sonderstellung, aber dazu gleich mehr.

Falls Sie die gleiche Basis wie in diesem Script verwenden wollen, dann blenden Sie sich bitte die Tabelle1a ein, dort ist das Diagramm schon erstellt. Das Passwort gibt es natürlich beim Trainer … Und Autodidakten, bitte einfach eine Mail senden, dann schicke ich die Liste mit allen Passworten zu.

▲ nach oben …

Fehlende Umsatzzahlen korrekt übertragen

Die fehlenden Umsätze sind ja falsch übernommen worden. Also doch die copy and paste – Methode? Nein, es geht (natürlich) auch anders. Dazu bedarf es nur etwas mehr an Aufwand, was die Verknüpfungs-Formel betrifft. Zur Erinnerung: In Tabelle1 haben Sie die Daten in Zelle B1 so übernommen:

=INDIREKT(B$1&"!"&ADRESSE(ZEILE();2))

Das Ergebnis war unbefriedigend, denn bei fehlendem Umsatz wurde immer ein Umsatz von 0,00 € gewertet. Und das ist ja mit einer einzigen Ausnahme nicht korrekt. Da ja der Bezug zum anderen Tabellenblatt korrekt ist, muss es an der Auswertung liegen. Und die Formel wird durch eine Anpassung um einiges komplexer. Hier wiederum die Formel für B1, welche Sie anschließend in alle restlichen Zellen kopieren können. (Die Formel gehört in 1 Zeile der Editierzeile, was auch für alle weiteren Formeln gilt):

=WENN(INDIREKT(B$1&"!" & ADRESSE(ZEILE();2)) <>""; INDIREKT(B$1 & "!" & ADRESSE(ZEILE(); 2)); "")

Erst einmal trocken schlucken. Und dann werden Sie erkennen, dass die Formel in weiten Teilen der vorherigen gleicht. Es wird ja bekanntlich selten so heiß gegessen, wie gekocht wird. Ich selbst habe der Übersichtlichkeit halber die Tabelle1 kopiert, in Tabelle2 umbenannt und hier die Formeln geändert. Anschließend habe ich wiederum ein Diagramm erzeugt, hier mit den Werten des Juli (auch wenn der Monat nicht zum ersten Halbjahr gehört). Da mir die Farbgebung nun gar nicht gefallen hat, habe ich die Datenansicht meinem Geschmack angepasst. Das Ergebnis sehen Sie in Tabelle2. Das Passwort, Sie ahnen es, gibt Ihnen wiederum Ihr Trainer oder Sie haben es bereits. Der Seminarleiter hilft Ihnen auch bei der farblichen Gestaltung, falls Sie das nachvollziehen wollen und Hilfe brauchen.

Das Ergebnis ist erst einmal enttäuschend, verwirrend. Wenn die Formel wirklich das tut, was sie tun soll, dann müsste ja beispielsweise in C3 nichts stehen, also ein Leerwert und keine Null. Gleiches gilt für G2, G4 und H3. Aber was die fehlenden Umsätze betrifft, da ist wiederum der Wert 0 (0) Null angenommen worden:

Fehlende und Nullwerte werden als null angezeigt

Fehlende und Nullwerte werden als null angezeigt

Die Formel müsste richtig sein. Aber wenn Sie in eine der betroffenen Zellen klicken, werden Sie auch nur die Formel und nicht die eigentliche Auswertung sehen. Die eine Möglichkeit wäre: Sie ändern das Zahlenformat für den Bereich B2:H4:

#.##0" Tsd.€";[Rot]- #.##0" Tsd.€"

Dann sehen Sie tatsächlich in D2 0 Tsd.€ als Ergebnis stehen. Und die leeren Zellen sind wirklich leer, also (noch) ohne Umsatzangabe. Falls Sie das nicht möchten, sehen Sie sich doch einfach einmal Tabelle2a an. Diese wird automatisch mit angezeigt, wenn Sie Tabelle2 einblenden. Mit einer einfachen Funktion und bedingter Formatierung ist eine Kontrolle möglich, ob eine scheinbar leere Zelle den wegen der Zahlenformatierung nicht angezeigten Wert Null enthält oder wirklich leer ist.

Bleibt die Frage der Fragen: Wie schaffe ich es, dass die fehlenden Umsätze nicht als Null angezeigt werden? Hier ein Wink mit dem Zaunpfahl: Die fehlenden Umsätze sind doch Nicht Verfügbar, richtig? Und da war doch was … Richtig, es gibt da eine Fehlermeldung #NV. Und genau die machen wir und zunutze, um dem Problem Herr zu werden. Ändern Sie nun letztmalig alle Formeln so um, wie hier für B2 aufgeführt:

=WENN(INDIREKT(B$1&"!"&ADRESSE(ZEILE();2))<>""; INDIREKT(B$1&"!"&ADRESSE(ZEILE(); 2)); #NV)

Hier einmal ganz langsam zum Überdenken: Wenn in den Quelldaten in der gleichen Zelle des Monats dieser Spalte ein Wert ungleich "" steht (die Zelle ist nicht leer), dann nimm den Wert der Zelle, sonst (also leere Zelle) nimm den Fehlerwert #NV. Jetzt steht in der auszuwertenden Tabelle (bei mir Tabelle3) entweder nichts (was Null entspricht) oder der Hinweis, dass der Wert nicht verfügbar ist. Und das Ergebnis im Diagramm:

Durch #NV interpolierte Lücken

Durch #NV interpolierte Lücken

Natürlich können Sie auch das eine oder andere daran ändern. Wenn Sie beispielsweise den riesigen Ausreißer für Produkt 1 im März mit dem Null-Umsatz etwas ausblenden wollen, damit die restlichen Zahlen besser dargestellt werden, geht es auch so:

Eine veränderte Darstellung der Werte

Eine veränderte Darstellung der Werte

Schauen Sie sich gerne das Excel-Blatt an. Dort habe ich auch einige Kontroll-Felder eingefügt, damit Sie etwas mehr zu rätseln haben. Ich garantiere Ihnen, die Datenquelle ist die gleiche. Und dennoch ist der Leerraum bei März und das, obwohl der Ausgangswert ganz klar 0 ist. Es scheint irritierend zu sein. Dabei ist die Lösung wirklich offen sichtbar oder offensichtlich: Die Skalierung der Y-Achse beginnt nicht bei 0 sondern bei 4.000 Tsd.€. – Sie erkennen aber auch in beiden Diagrammen, dass die fehlenden Umsätze mit einer Linie verbunden worden sind. Und das war ja auch der Sinn der Sache. Die einzige Ausnahme: Im Juli ist die Linie für Produkt 2 nicht fortgeführt worden. Es gibt ja auch keinen Endpunkt, zu dem verbunden werden kann.

▲ nach oben …

Lücken im Diagramm

Falls Sie Daten per Hand eingeben, ist die eine oder andere Zelle wirklich leer. Damit ist gemeint, dass dort weder ein 0-Wert drinnen steht noch eine Formel oder Funktion. Auch kein Text der Länge Null (entspricht „“) ist dort enthalten. Dann sind in einem solchen Diagramm tatsächlich Lücken in der Datenlinie zu sehen. Sie können das nachvollziehen, wenn Sie irgendwo im Februar bis Mai eine Zelle markieren und Entf drücken. Auch in diesen Fällen hilft es, in die Zelle den Fehlerwert #NV direkt per Hand einzutragen. Damit wird die Lücke geschlossen. Im folgenden Diagramm habe ich zwar die 0-Werte übernommen aber keinen Fehlerwert eingetragen. So entstehen dann die wirklichen Lücken im Diagramm, die auch eine gewisse Aussagekraft haben können:

Echte Lücken im Diagramm

Echte Lücken im Diagramm

Sie sehen es auch in Tabelle4. Unter Umständen allerdings ist eine kleine Anpassung der Einstellungen erforderlich. Dazu klicken Sie am besten auf eine Datenlinie, dann über die rechte Maustaste im Kontextmenü den Punkt Daten auswählen… anklicken:

Auswählen der Datenquelle

Auswählen der Datenquelle

Hier die untere, linke Schaltfläche Ausgeblendete und leere Zellen klicken und es erscheint dieses Fenster:

Es sollen Lücken erscheinen

Es sollen Lücken erscheinen

Wählen Sie bei den Optionsfeldern die Lücken (oben bereits ausgewählt) und dann immer mit OK bestätigen. Fertig.

▲ nach oben …

 

Dieser Beitrag wurde unter Fehlerbehandlung, Musterlösungen, Ohne Makro/VBA, Tabelle und Zelle veröffentlicht. Setze ein Lesezeichen auf den Permalink.