Excel, alle Ver­sio­nen

Fol­gende Sit­u­a­tion ist gar nicht ein­mal so sel­ten: Umsatz­dat­en wer­den von ein­er Soft­ware (SAP, Ora­cle, …) als Excel-Datei (*.xls, *.xlsx, eventuell auch *.csv) für beispiel­sweise jeden Monat erstellt. Diese einzel­nen Berichte mit den Dat­en, die in jedem Fall als Zahlen und nicht als berech­neten Formel­ergeb­nis­sen in den Zellen vor­liegen, wer­den dann von Hand oder per Makro in eine neue Excel-Mappe einge­fügt oder importiert. Aus diesen in einzel­nen Tabel­len­blät­tern vor­liegen­den Monats­bericht­en wir danach in einem einzel­nen Sheet ein Hal­b­jahres­bericht und auf wiederum dieser Dat­en anschließend ein Lin­ien­di­a­gramm erstellt. Der hier für dieses Script vor­liegende Stand ist, dass nur die einzel­nen fremderzeugten Excel-Dateien vor­liegen. Die zu importieren­den Dat­en find­en Sie hier als *.zip-Datei.

Hin­weis: In dieser Muster­lö­sung wird in erster Lin­ie mit Excel 2010 gear­beit­et. Die vor­rangi­gen Abbil­dun­gen sind mit dieser Ver­sion erstellt wor­den. – Weit­er­hin gilt, dass die Daten­ba­sis stets eine *.xlsx – Datei ist. Das ältere For­mat (*.xls) und auch das *.csv – For­mat wer­den nicht beson­ders ange­sprochen. – Und last but not least: Sie wer­den ver­mehrt den Hin­weis auf ein Sem­i­nar, auf ein Train­ing lesen. Sofern Sie sich diese Unter­lage aus dem Netz geholt haben, ist das für Sie natür­lich nicht rel­e­vant.

▲ nach oben …

Tabelle anlegen, Daten importieren

So weit, so gut. Nun kommt es aber immer wieder ein­mal vor, dass die Dat­en unvoll­ständig sind. Für diesen oder jenen Monat ist für das Pro­dukt X oder den Artikel Y kein Umsatz angegeben. „Kein Umsatz” bedeutet in diesem Fall nicht Null Euro (oder jede andere Währung), son­dern die Zahl fehlt ein­fach, die Zelle ist wirk­lich leer, auch nicht mit dem Inhalt "". Diese Unter­schei­dung ist wichtig, denn es ist ja dur­chaus bedeu­tend, ob in einem Bericht ein Pro­dukt in einem Monat gar nicht verkauft wurde oder ob der vorhan­dene Umsatz noch nicht gemeldet ist. Nach dem Import oder Ein­fü­gen in das jew­eilige Monats-Arbeits­blatt der Mappe sehen die Monats­dat­en dann so aus, wie hier für Jan­u­ar und Feb­ru­ar gezeigt:

Umsatz Jan­u­ar

Umsatz Feb­ru­ar

 Die fehlen­den Dat­en (Feb­ru­ar, B3) treten in diesem Pro­jekt öfter ein­mal auf, der Demon­stra­tion wegen. Um dem Ziel einen kleinen Schritt näher zukom­men, leg­en Sie nun erst ein­mal ein Arbeits­blatt in ein­er leeren Mappe an, das so aussieht:

Die leere Datentabelle

Benen­nen Sie die Tabelle im Reg­is­ter um, sie soll den Namen Tabelle0 (Null, nicht o) haben. Über­tra­gen Sie nun aus den einzel­nen Excel-Dateien (01-Jan.xlsx, 02-Feb.xlsx, 03-Mär.xlsx, …) aus dem jew­eils ersten und einzi­gen Tabel­len­blatt die Dat­en in diese eben erstellte Arbeitsmappe. Wie Sie da vorge­hen, bleibt Ihnen über­lassen. Es sollte aber für jeden Monat eine einzelne Tabelle (Reg­is­ter, Arbeits­blatt) existieren. Alter­na­tiv kön­nen Sie auch dieses Datei öff­nen, wo (im For­mat Excel ²⁰⁰⁷⁄₂₀₁₀) alle Tabel­len­blät­ter der Monate der ersten sieben Monate bere­its einge­fügt sind. Die einzel­nen Monate sind in dieser Datei als *.zip gepackt.

Wenn Sie neugierig sind, wie ich mir das Leben mit solchen Zusam­men­stel­lun­gen leichter mache, dann soll­ten Sie in jedem Fall die Datei öff­nen und sich die Tabelle1 anschauen. Das erforder­liche Pass­wort bekom­men Sie von uns per e‑Mail. Vorher aber soll­ten Sie in Eigen­regie ver­suchen, die Werte der einzel­nen Monate in die Tabelle0 zu über­tra­gen. Das sind max­i­mal 5 Minuten, die Sie bess­er ein­schätzen lassen, ob der Aufwand mit den von mir ver­wen­de­ten Formeln und Funk­tio­nen sich lohnt oder nicht.

Die Grund­lage ist gelegt. Und bevor Sie mehr oder weniger inten­sives Inter­esse zeigen: Ja, es gibt noch mehrere ver­steck­te Tabel­len­blät­ter. Und Sie wer­den diese inner­halb eines Sem­i­nars mit Sicher­heit zu Ver­gle­ich­szweck­en zu gegeben­er Zeit sehen und nutzen. Wenn Sie diese Muster­lö­sung aus dem Inter­net haben (www.excel-ist-sexy.de), dann reicht eine (eventuell) zweite Mail, um die erforder­lichen Pass­worte zu bekom­men.

▲ nach oben …

Leere Zellen …

Wenn Sie die Dat­en aus den einzel­nen Monat­en per copy&paste in die einzel­nen Spal­ten der Tabelle0 einge­fügt haben, wer­den Sie das fol­gende Prob­lem kaum nachvol­lziehen kön­nen: Zellen, die im Ursprungs­blatt leer sind, wer­den auch im Ziel­bere­ich wirk­lich leer sein. Das hätte dann Auswirkun­gen, welche ganz unten beim eigentlichen The­ma „Lück­en im Dia­gramm” beschrieben.

Aber wenn Sie sich das Leben leicht machen wollen, weil vielle­icht jede Woche neue Dat­en gener­iert wer­den, dann sieht das schon anders aus. Um das im Falle eines Fall­es nachvol­lziehen zu kön­nen, geben Sie (immer noch in Tabelle0) in die erste Daten­zelle des Feb­ru­ar (C2) diese Formel ein: =Februar!B2 und kopieren Sie die Formel nach unten. Der Erfolg: In C3 ste­ht eine Null, obwohl die verknüpfte Zelle im Arbeits­blatt Feb­ru­ar leer ist. Und das passiert mit allen verknüpften Zellen. Auch wenn Sie derzeit noch nicht den tief­er­en Sinn dieses Hin­weis­es erken­nen, soll­ten Sie das im Bewusst­sein behal­ten.

Um eine gemein­same Grund­lage im Sem­i­nar zu haben, blenden Sie nun die Tabelle1 ein. Dort sind bei den Umsatz-Dat­en nur verknüpfte Zellen, allerd­ings wurde die Formel nur ein einziges Mal eingegeben. Danach habe ich diese Formel ein­fach nur in alle anderen Zellen kopiert. Falls Sie zu den Usern gehören, die sich das Ganze auto­di­dak­tisch erar­beit­en, hier die Formel aus der Zelle B2:

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

Dabei ist zu berück­sichti­gen, dass die einzel­nen Tabel­len­blät­ter (Reg­is­ter) den voll aus­geschriebe­nen Namen des jew­eili­gen Monats haben. Der Name ist iden­tisch mit dem Text der jew­eili­gen Spalte in Zeile 1.

In Tabelle1 wer­den Sie rasch erken­nen, dass ich schon einige For­matierun­gen vorgenom­men habe. Ein­er­seits sind dieses die Währungs­beze­ich­nun­gen (das „T€” habe ich mir verknif­f­en, es ähnelt zu sehr dem geflügel­ten Wort des „Teu­ro”). Ander­er­seits habe ich alle Zellen mit Null­w­erten far­big hin­ter­legt, einige davon sind so for­matiert, dass die Zelle leer erscheint. Den­noch ste­ht der Wert Null in der Zelle, sie ist nicht leer.

▲ nach oben …

Ein Linien-Diagramm erstellen

Gut, erstellen Sie aus den Dat­en des ersten Hal­b­jahres ein Lin­ien­di­a­gramm. Die ein­fache Form reicht erst ein­mal aus und es soll kein getren­ntes Arbeits­blatt sein, son­dern als Objekt im Tabel­len­blatt erscheinen. Das Ganze wird dann etwa so ausse­hen:

Dia­gramm Jan­u­ar

Sie erken­nen, dass für Feb­ru­ar, März und Juni Null-Umsätze des einen oder anderen Pro­duk­ts aus­gewiesen sind. Die Lin­ie stößt unten auf der Grundlin­ie des Dia­gramms auf. Und das ist ja auch ganz logisch, denn wie eben bere­its erwäh­nt, ist beispiel­sweise für das Pro­dukt 2 im Feb­ru­ar ein Umsatz von 0 € in der Tabelle0 „berech­net” wor­den. Der März hat da eine Son­der­stel­lung, aber dazu gle­ich mehr.

Falls Sie die gle­iche Basis wie in diesem Script ver­wen­den wollen, dann blenden Sie sich bitte die Tabelle1a ein, dort ist das Dia­gramm schon erstellt. Das Pass­wort gibt es natür­lich beim Train­er … Und Auto­di­dak­ten, bitte ein­fach eine Mail senden, dann schicke ich die Liste mit allen Pass­worten zu.

▲ nach oben …

Fehlende Umsatzzahlen korrekt übertragen

Die fehlen­den Umsätze sind ja falsch über­nom­men wor­den. Also doch die copy and paste – Meth­ode? Nein, es geht (natür­lich) auch anders. Dazu bedarf es nur etwas mehr an Aufwand, was die Verknüp­fungs-Formel bet­rifft. Zur Erin­nerung: In Tabelle1 haben Sie die Dat­en in Zelle B1 so über­nom­men:

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

Das Ergeb­nis war unbe­friedi­gend, denn bei fehlen­dem Umsatz wurde immer ein Umsatz von 0,00 € gew­ertet. Und das ist ja mit ein­er einzi­gen Aus­nahme nicht kor­rekt. Da ja der Bezug zum anderen Tabel­len­blatt kor­rekt ist, muss es an der Auswer­tung liegen. Und die Formel wird durch eine Anpas­sung um einiges kom­plex­er. Hier wiederum die Formel für B1, welche Sie anschließend in alle restlichen Zellen kopieren kön­nen. (Die Formel gehört in 1 Zeile der Edi­tierzeile, was auch für alle weit­eren Formeln gilt):

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

Erst ein­mal trock­en schluck­en. Und dann wer­den Sie erken­nen, dass die Formel in weit­en Teilen der vorheri­gen gle­icht. Es wird ja bekan­ntlich sel­ten so heiß gegessen, wie gekocht wird. Ich selb­st habe der Über­sichtlichkeit hal­ber die Tabelle1 kopiert, in Tabelle2 umbe­nan­nt und hier die Formeln geän­dert. Anschließend habe ich wiederum ein Dia­gramm erzeugt, hier mit den Werten des Juli (auch wenn der Monat nicht zum ersten Hal­b­jahr gehört). Da mir die Far­bge­bung nun gar nicht gefall­en hat, habe ich die Date­nan­sicht meinem Geschmack angepasst. Das Ergeb­nis sehen Sie in Tabelle2. Das Pass­wort, Sie ahnen es, gibt Ihnen wiederum Ihr Train­er oder Sie haben es bere­its. Der Sem­i­narleit­er hil­ft Ihnen auch bei der far­blichen Gestal­tung, falls Sie das nachvol­lziehen wollen und Hil­fe brauchen.

Das Ergeb­nis ist erst ein­mal ent­täuschend, ver­wirrend. Wenn die Formel wirk­lich das tut, was sie tun soll, dann müsste ja beispiel­sweise in C3 nichts ste­hen, also ein Leer­w­ert und keine Null. Gle­ich­es gilt für G2, G4 und H3. Aber was die fehlen­den Umsätze bet­rifft, da ist wiederum der Wert 0 (0) Null angenom­men wor­den:

Fehlende und Null­w­erte wer­den als null angezeigt

Die Formel müsste richtig sein. Aber wenn Sie in eine der betrof­fe­nen Zellen Klick­en, wer­den Sie auch nur die Formel und nicht die eigentliche Auswer­tung sehen. Die eine Möglichkeit wäre: Sie ändern das Zahlen­for­mat für den Bere­ich B2:H4:

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

Dann sehen Sie tat­säch­lich in D2 0 Tsd.€ als Ergeb­nis ste­hen. Und die leeren Zellen sind wirk­lich leer, also (noch) ohne Umsatzangabe. Falls Sie das nicht möcht­en, sehen Sie sich doch ein­fach ein­mal Tabelle2a an. Diese wird automa­tisch mit angezeigt, wenn Sie Tabelle2 ein­blenden. Mit ein­er ein­fachen Funk­tion und bed­ingter For­matierung ist eine Kon­trolle möglich, ob eine schein­bar leere Zelle den wegen der Zahlen­for­matierung nicht angezeigten Wert Null enthält oder wirk­lich leer ist.

Bleibt die Frage der Fra­gen: Wie schaffe ich es, dass die fehlen­den Umsätze nicht als Null angezeigt wer­den? Hier ein Wink mit dem Zaunpfahl: Die fehlen­den Umsätze sind doch Nicht Verfüg­bar, richtig? Und da war doch was … Richtig, es gibt da eine Fehler­mel­dung #NV. Und genau die machen wir und zunutze, um dem Prob­lem Herr zu wer­den. Ändern Sie nun let­zt­ma­lig alle Formeln so um, wie hier für B2 aufge­führt:

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

Hier ein­mal ganz langsam zum Über­denken: Wenn in den Quell­dat­en in der gle­ichen Zelle des Monats dieser Spalte ein Wert ungle­ich "" ste­ht (die Zelle ist nicht leer), dann nimm den Wert der Zelle, son­st (also leere Zelle) nimm den Fehler­w­ert #NV. Jet­zt ste­ht in der auszuw­er­tenden Tabelle (bei mir Tabelle3) entwed­er nichts (was Null entspricht) oder der Hin­weis, dass der Wert nicht ver­füg­bar ist. Und das Ergeb­nis im Dia­gramm:

Durch #NV inter­polierte Lück­en

Natür­lich kön­nen Sie auch das eine oder andere daran ändern. Wenn Sie beispiel­sweise den riesi­gen Aus­reißer für Pro­dukt 1 im März mit dem Null-Umsatz etwas aus­blenden wollen, damit die restlichen Zahlen bess­er dargestellt wer­den, geht es auch so:

Eine verän­derte Darstel­lung der Werte

Schauen Sie sich gerne das Excel-Blatt an. Dort habe ich auch einige Kon­troll-Felder einge­fügt, damit Sie etwas mehr zu rät­seln haben. Ich garantiere Ihnen, die Daten­quelle ist die gle­iche. Und den­noch ist der Leer­raum bei März und das, obwohl der Aus­gangswert ganz klar 0 ist. Es scheint irri­tierend zu sein. Dabei ist die Lösung wirk­lich offen sicht­bar oder offen­sichtlich: Die Skalierung der Y‑Achse begin­nt nicht bei 0 son­dern bei 4.000 Tsd.€. – Sie erken­nen aber auch in bei­den Dia­gram­men, dass die fehlen­den Umsätze mit ein­er Lin­ie ver­bun­den wor­den sind. Und das war ja auch der Sinn der Sache. Die einzige Aus­nahme: Im Juli ist die Lin­ie für Pro­dukt 2 nicht fort­ge­führt wor­den. Es gibt ja auch keinen End­punkt, zu dem ver­bun­den wer­den kann.

▲ nach oben …

Lücken im Diagramm

Falls Sie Dat­en per Hand eingeben, ist die eine oder andere Zelle wirk­lich leer. Damit ist gemeint, dass dort wed­er ein 0‑Wert drin­nen ste­ht noch eine Formel oder Funk­tion. Auch kein Text der Länge Null (entspricht „”) ist dort enthal­ten. Dann sind in einem solchen Dia­gramm tat­säch­lich Lück­en in der Daten­lin­ie zu sehen. Sie kön­nen das nachvol­lziehen, wenn Sie irgend­wo im Feb­ru­ar bis Mai eine Zelle markieren und Entf drück­en. Auch in diesen Fällen hil­ft es, in die Zelle den Fehler­w­ert #NV direkt per Hand einzu­tra­gen. Damit wird die Lücke geschlossen. Im fol­gen­den Dia­gramm habe ich zwar die 0‑Werte über­nom­men aber keinen Fehler­w­ert einge­tra­gen. So entste­hen dann die wirk­lichen Lück­en im Dia­gramm, die auch eine gewisse Aus­sagekraft haben kön­nen:

Echte Lück­en im Dia­gramm

Sie sehen es auch in Tabelle4. Unter Umstän­den allerd­ings ist eine kleine Anpas­sung der Ein­stel­lun­gen erforder­lich. Dazu Klick­en Sie am besten auf eine Daten­lin­ie, dann über die rechte Maus­taste im Kon­textmenü den Punkt Dat­en auswählen… anklick­en:

Auswählen der Daten­quelle

Hier die untere, linke Schalt­fläche Aus­ge­blendete und leere Zellen Klick­en und es erscheint dieses Fen­ster:

Es sollen Lück­en erscheinen

Wählen Sie bei den Options­feldern die Lück­en (oben bere­its aus­gewählt) und dann immer mit OK bestäti­gen. Fer­tig.

[NachOben­Let­zte Verweis=„ML: Dia­gramm mit Lück­en”]