Office: (Office 2013) Diagramm ohne Urlaubstage / Wochenende

Hallo zusammen,

ich habe ewig im Internet nachgeforscht, wie mein Problem zu lösen ist.
Es handelt sich um einen Stundenrechner, welcher mir automatisch meine Arbeitsstunden berechnet und mir in einem Balkendiagramm darstellt.

Das Problem:
Ich war eine Woche im Urlaub, somit war nach dem 25.8. der nächste Eintrag der 8.9.. Excel trägt in dem Balkendiagramm diese Werte als 0 ein, obwohl ich niemals eine 0 eingetragen habe. Das gleiche gilt für Wochenenden. (siehe Bild)



Ich habe gelesen, dass ein dynamisches Diagramm die Lösung sein könnte, war aber nicht in der Lage eines zu erstellen.

Um mein Anliegen besser verstehen zu können, habe ich die Datei einmal angehängt.
Den Anhang Stundenrechner.xlsx betrachten

 

Hallo,

meinst du also eine "Textachse" statt einer "Datumsachse" ?

 

Hi, danke für die schnelle Antwort!

Diese Funktion hatte ich schon gesehen. Jedoch hätte ich gerne dass das Diagramm nur die eingetragenen Daten anzeigt und die restlichen Zellen, welche aktuell noch leer sind, nicht als eine 0 interpretiert.
Das Diagramm soll sich quasi sobald ein neuer Eintrag vorgenommen wurde um diesen Eintrag erweitern. --> Dynamisches Diagramm?

 

Hallo,

es gibt 2 verschiedene Methoden, um ein sogenanntes "dynamisches" Diagramm anzulegen, d.h. ein Diagramm, das sich automatisch an die Anzahl Zeilen des Datenbereiches anpasst:

1) Den Datenbereich in eine sogenannte "Tabelle" umwandeln:
1a) dazu habe ich zB. im Arbeitsblatt "August 2015" die leeren Zeilen samt der Summenzeile entfernt, sodass ein Datenbereich 'August 2015'!$A$4:$G$11 inklusive Kopfzeile übrigblieb.
1b) Diesen Bereich habe ich über das Menüband > Tabulator "EINFÜGEN" > Gruppe "Tabellen" > Button "Tabelle" in eine Tabelle mit Kopfzeile umgewandelt.
1c) Diese Tabelle habe ich über das Menüband > Kontexttabulator "TABELLENTOOLS/ENTWURF" > Gruppe "Eigenschaften" > Textfeld "Tabellenname" umbenannt in "TabAug2015".
1d) Das Diagramm hatte sich bereits im Anschluss an Punkt 1b) an die neue Zeilenanzahl automatisch umgestellt.
1e) Falls du eventuell die Summenzeile vermisst - auch diese ist weiterhin möglich, sie kann eingeblendet/ausgeblendet werden, wenn du im Menüband > Kontexttabulator "TABELLENTOOLS/ENTWURF" > Gruppe "Optionen für Tabellenformat" > Checkbox "Ergebniszeile" ein Häkchen setzt: dann wird eine zusätzliche Zeile angezeigt, in der man für jede Spalte separat über DropDowns die entsprechenden Optionen einstellen kann. Diese Einstellarbeit braucht man nur 1x zu machen, Excel merkt sich diese, sodass nach einem weiteren Aus/Einblenden mittels Häkchen diese wieder sofort da sind.
1f) Vor einem weiteren Anfügen von Datenzeilen sollte man praktischerweise diese Summenzeile aus Punkt 1e) zuerst mittels Häkchen ausblenden, die weitere Datenzeile unten in die nächste leere Zeile anfügen. Die Tabelle erweitert daraufhin automatisch ihren Datenbereich - und natürlich aktualisiert sich auch das Diagramm automatisch, indem es einen weiteren Balken hinzufügt. Die Summenzeile kann dann wieder über das Häkchen aus Punkt 1e) eingeblendet werden (die Summen sind natürlich auch automatisch aktualisiert).

2) Den Datenbereich in einen sogenannten "dynamischen" Bereich umwandeln (das ist die ältere Methode):
2a) Zuerst den Schritt aus Punkt 1a) durchführen, um einen bereinigten Datenbereich zu erhalten.
2b) Im Namensmanager einen neuen Namen einfügen für den Datenbereich, zB. "BerAug2015", Bereich "August 2015", jedoch muss man dann anstelle einer festen Bezugsangabe im Feld "Bezieht sich auf:" eine Formel eingeben, die damit dem Bereichsnamen automatisch den passenden Zellbereich zuordnet:
=BEREICH.VERSCHIEBEN($A$4;1;0;Anzahl(A:A);7)
Dadurch würde beim jetzigen Datenbestand dem Bereichsnamen "BerAug2015" der Zellbezug "August 2015!$A$5:$G$11" dynamisch zugeordnet.
2c) Klickt man im Diagramm auf einen Datenbalken, wird in der Formeleditierzeile folgende Formel angezeigt:
=DATENREIHE('August 2015'!$G$4;'August 2015'!$A$5:$A$11;'August 2015'!$G$5:$G$11;1)
Diese muss man wie folgt modifizieren:
=DATENREIHE('August 2015'!$G$4;BerAug2015!$A:$A;BerAug2015!$G:$G;1)
wobei der 1.Parameter die Diagrammüberschrift angibt, der 2. Parameter die Rubrikenachse oder X-Achse (das sind die Datumsangaben) und der 3. Parameter die Diagrammwerte oder Y-Werte (das ist die Balkenhöhe).
Der 4. Parameter gibt die Nr. der Balkenreihe wieder, nur wichtig, falls mehrere Balkenreihen anzuzeigen wären.

Man sollte dem 1. Verfahren den Vorzug geben, denn das 2. Verfahren hat einen nicht unwichtigen Nachteil: Es verwendet die Funktion "BEREICH.VERSCHIEBEN". Diese bewirkt, dass bei jeder (noch so kleinen) Datenänderung im Arbeitsblatt der dynamische Bereichsname neu berechnet und aktualisiert wird. Das ist bei 1 oder 2 Diagrammen keine große Sache, bei mehreren/vielen Diagrammen oder großen Datenbereichen kann sich das doch deutlich bemerkbar machen.

Angefügt habe ich nach Methode 1 die Excel-Datei mit den dynamischen Diagrammen:

 

Hi,

1. setze die Achse von "Automatisch" auf "Textachse"
2. definiere einen Namen z.B. ZeitSept und trage als Bezug diese Formel ein =$G$5:INDEX($G$5:$G$31;ANZAHL($A$5:$A$31);1), Excel setzt dann noch automatisch den Namen des Tabellenblattes vor die Zelladressen
3. gib im Diagramm für die Datenreihe Arbeitszeit(formatiert) als Reihenwerte =Stundenrechner.xlsx!ZeitSept an

Die Funktion BEREICH.VERSCHIEBEN ist eine volative Funktion und wird bei jeder Berechnung im Tabellenblatt neu berechnet, auch wenn sie sich nicht auf die berechnete Zelle bezieht - das beeinträchtigt die Performance und macht die Arbeitsmappe langsam. Verwende stattdessen INDEX, denn diese Funktion ist nicht volatil.

Bis später,
Karin

 

Wow vielen Dank - das war die Lösung nach welcher ich gesucht hatte!