[Excel] Arbritsstände lfd. vergleichen mit Diagramm

[Onatik]

[Excel] Arbeitsstände lfd. vergleichen mit Diagramm

Hallo Leute!

Ich nutzte das CB-Forum sehr gerne, dass liegt vor allem an euch Mitgliedern. Ihr habt immer wieder sehr tolle Lösungen, Informationen etc. für alle Interessierten. Vielleicht konnte ich dem Einen oder Anderen ja auch schon einmal helfen.

So, genug der Einführung, nun zu meinem Problem.

Ich darf in Excel Ursprungsdaten aus einer Datentabelle auswerten, welche zudem immer aktualisiert wird.

Nun möchte ich auch die täglichen Arbeitsstände auswerten und darstellen (Diagramm).

Die relevanten Spalten sehen wie folgt aus:

A------------------B
Datum----------Datum
01.11.2015---27.11.2015
05.11.2015---07.11.2015
30.12.2015---14.01.2016
05.01.2016---12.01.2016
14.01.2016
...

Ihr seht, die Daten in Spalte A sind aufsteigend sortiert, in Spalte B nicht. Aber das sollte nicht relevant sein.

Nun möchte ich darstellen lassen, wie viele Vorgänge (Anzahl) am 27.11., 07.11.2015, 12.01. usw. offen sind.

Pivotauswertungen gibt es schon einige, wobei andere Korrelationen dargestellt werden.

Habt ihr Ideen?

Ich stehe gerade ein wenig auf dem Schlauch. Außerdem schreibe ich gerade vom Smartphone und kann leider keiner Beispieltabelle anfügen.

Vielen Dank

 

[Knufu]

Im Moment kann ich leider keinerlei Zusammenhänge zwischen dem Datum in beiden Spalten und zur Anzahl möglicher Vorgänge sehen....

 

[Merle]

Willst du das Grafisch?
Oder suchst du dir nur einzelne Daten raus?

*edit: Wenn ich es nicht grafisch brauche, sondern das Eintippen eines Datums reicht, dann würde ich es mit 1 Hilfsspalte machen und 2 Hilfszellen.

Hilfsspalte1:

=WENN(A1<=$I$1;WENN(B1>=$I$1;WAHR;FALSCH);FALSCH)

(A ist dein A, B ist dein B, I1 ist die Hilfszelle2, in der ich das abzufragende Datum eintrage.)
Hilfszelle1:

=ZÄHLENWENN(D:D;WAHR)

(D ist meine Hilfsspalte1)
Hilfszelle2: Datum eintragen

 

[TB30]

Wenn ich dich richtig verstanden habe, sollte folgende Formel das richtige Ergebnis liefern:

=ZÄHLENWENNS($A$2:$A$100;"<="&C1;$B$2:$B$100;">"&C1)

In A2:A100 und B2:B100 stehen dabei jeweils die Anfangs- und Enddaten; C1 ist die Zelle, in der das Datum steht, zu dem die Zahl der offenen Vorgänge bestimmt werden soll.

 

[Onatik]

AW: [Excel] Arbeitsstände lfd. vergleichen mit Diagramm

Danke für eure Antworten.

Ich denke, dass ich mit den ZÄHLENWENN/S Funktionen nicht weiterkomme, da ich ja ein/en Verlauf(sdiagramm) brauche, welcher/s sich am jeweiligen Erledigungsdatum (Spalte B) orientiert.

Also im Ergebnis waren 1.546 Vorgänge am 12.01.2016 erledigt.
Alle bis zum 12.01.2016 eingegangenen Vorgänge und die bis (!) zum 12.01.2016 erledigten (!) Vorgänge. So wären dann auch noch 5.673 Vorgänge offen; z. B. die Differenz oder alle bis zum 12.01.2016 eingegangenen aber noch bis zum 12.01.2016 offenen bzw. unerledigten Vorgänge.

Ich schaue mal, dass ich eine Beispieltabelle hochlade, wenn ich zu Hause bin.

Danke aber schon mal für eure Vorschläge.

Ergänzung (14. Januar 2016)

So, jetzt habe ich die Beispieldatei: Anhang anzeigen Arbeitsstände lfd. vergleichen mit Diagramm.xlsx mal hochgeladen.

Wer hat noch Ideen?

 

[simpsonsfan]

Ich werde aus deinen Schilderungen nicht so ganz schlau.
Ich würde einfach mal von folgendem ausgehen:
Also, du hast zu jedem Vorgang ein Eingangs- und ein Erledigungsdatum. Und nun willst du in einem Diagramm die Anzahl der offenen, d.h. eingegangenen aber noch nicht erledigten, Vorgänge über der Zeit darstellen. Korrekt?

In dem Fall erstelle doch einfach irgendwo zwei Hilfsspalten. In der einen sollte dann ein fortlaufendes Datum stehen, in der anderen dann die Formel von TB30 in Post#4, wobei statt C1 eben auf die Hilfsspalte nebenan mit dem Datum verwiesen wird.
Dann plottest du schlicht die zweite Spalte über der ersten.

 

[Onatik]

Ja @simpsonsfan, ganz korrekt.

Ich habe jetzt 2 Spalten angefügt und in Spalte 1, die Formel von @TB30 (Post #4) eingefügt.
Spalte 2 ist frei, da ich ein globales

=HEUTE()

in Zelle K1 stehen habe.

Das Ergebnis von Spalte 1 ist immer "0", auch mit 2 Extra-Zeilen und aktuellem Datum.

Aktuelle Tabelle Anhang anzeigen Arbeitsstände lfd. vergleichen mit Diagramm1.xlsx

Noch einmal zur Info, die Daten befinden sich in einer Daten-Tabelle (Extra-Formatierung und automatische Erweiterung).

 

[simpsonsfan]

Na die 0 rührt daher, dass du auf die leere Spalte zwei verweist.
Ich dachte in etwa an sowas (beispielhaft für die letzten 30 Tage; siehe Anhang)

 

[Onatik]

Hey @simpsonsfan, das schaut schon gut aus.

Vielen Dank.

Aber die Ergebnisse stimmen nicht.

Ich habe mal Daten aus der aktuellen Vergangenheit genommen.
Wenn ich z. B. einen Eingang am 20.12.2015 habe und keine Erledigung, dann ist am 20.12.2015 genau 1 Vorgang offen.
Das selbe dann im Verlauf: Bis 29.12.2015 sind es 10 Eingänge, abzüglich bis zum 29.12.2015 erledigte Vorgänge (2 Stück), sind noch 8 offen (ohne Datum) bzw. nach dem 29.12.2015 erledigt (Dat. Erl. > 29.12.2015).

Ich hänge die neue Datei mit an: Anhang anzeigen Kopie vo Arbeitsstände lfd. vergleichen mit Diagramm1.xlsx

Danke nochmal für deine Mühe.

 

[simpsonsfan]

Die Zellen, bei denen noch kein Erledigungsdatum eingetragen war, wurden nicht gezählt - weil dort das Erledigungsdatum nicht größer dem abgefragten Datum war. Einfach aus

=ZÄHLENWENNS(Stat.!$B$2:$B$12;"<="&A1;Stat.!$H$2:$H$12;">"&A1))

=ZÄHLENWENNS(Stat.!$B$2:$B$12;"<="&A1;Stat.!$H$2:$H$12;">"&A1)+ZÄHLENWENNS(Stat.!$B$2:$B$12;"<="&A1;Stat.!$H$2:$H$12;"")

machen, also das andere noch dazu addieren.

Beachte auch, dass in Zelle H11 der "1.1.2006" steht, somit passen die jetzt angezeigten 7 beim 29.12.15

 

[Onatik]

JA @simpsonsfan!!! Das ist es!
Du bist SUPER!!!

Vielen, vielen Dank.

H11=01.01.2006

war ein Fehler von mir. Da sollte 01.01.2016 drin stehen. Aber das war ja nicht relevant. Nach Korrektur kommen auch die 8 Tage, so wie sie müssten, raus.

Die Hilfs-Spalte A auf dem Blatt "Diagramm"

=HEUTE()

brauche ich nicht, da die Auswertung sich sozusagen nur an den Erledigungs-Daten orientieren soll und wenn zB. heute (17.01.2016) Vorgänge erledigt worden wären, hätte man ja auch dieses Datum bzw. diese Daten (wenn mehr als ein Vorgang erledigt worden ist). Zudem müsste man dann bei Tageswechsel auch nicht mehr Hand anlegen und die Formel verlängern.

In der Ursprungsversion der Arbeitsmappe gibt es schon eine ganze Reihe von Auswertungen (auf Pivot basierend), die die Erledigungen (Eingänge etc.) an einem Datum zählen, das sollte dann genügen.

Der Code im Blatt "Stat." würde dann für die Spalte I wie folgt ausschauen:

=ZÄHLENWENNS([@Datum];"<="&[@[Dat. Erl.]];[@[Dat. Erl.]];">"&[@[Dat. Erl.]])+ZÄHLENWENNS([@Datum];"<="&[@[Dat. Erl.]];[@[Dat. Erl.]];"")

Daher auch immer der Hinweis, dass es eine Datentabelle ist.

Edith stellt fest: Leider funktioniert die Formel nicht, es kommt immer wieder nur 0 heraus....

Hier nochmal die von mir angepasste Anhang anzeigen Kopie 3 Arbeitsstände lfd. vergleichen mit Diagramm1.xlsx

PS: Wie bekommst du es hin, dass deine Anhänge in einem Code-Kasten angezeigt werden?

 

[simpsonsfan]

Dein @ verkleinert den Bereich jeweils auf die eine Zeile.
Mit

=ZÄHLENWENNS([Datum];"<="&[@[Dat. Erl.]];[Dat. Erl.];">"&[@[Dat. Erl.]])+ZÄHLENWENNS([Datum];"<="&[@[Dat. Erl.]];[Dat. Erl.];"")

klappt es. Es darf nämlich natürlich dort, wo alle Zeilen gezählt werden sollen, der Bereich nicht auf die aktuelle Zeile beschränkt werden.

Und was die Anhänge hier im Forum angeht: Das ist Standardverhalten. einfach Datei hochladen, "Fertig" drücken. Ich vermute, du hast immer "An Cursorposition einfügen" gedrückt. Diese Funktion ist eigentlich eher für Bilder, die man an der Stelle im Text haben will.

 

[Onatik]

AW: [Excel] Arbeitsstände lfd. vergleichen mit Diagramm

Guten Morgen und Danke.
Langsam steige ich durch die Syntax.

Sitze gerade vor dem Problem, deine Formel arbeitet nur ohne [@] vor dem Parameter [Dat. Erl.]

Ich lasse also die [@] komplett weg.

Was mit gestern noch einfiel: Kann ich nicht auch die Funktion =INDIREKT für Vergleiche heranziehen?

Sorry, ich schreibe gerade über Tapatalk vom Smartphone, weil mein AG nicht unbedingt meine Login-Daten mitbekommen soll...

PS: Mit fällt noch auf, die leeren Zeilen in "Dat. Erl." werden nicht als heute noch offen gezählt. Damit stimmt der Arbeitsstand nicht.

 

[simpsonsfan]

Nein, ganz weglassen darfst du die @-Zeichen nicht. An den Stellen, an denen mit dem jeweiligen Datum verglichen wird, müssen sie drin bleiben, da bezieht sich die Formel ja auf genau eine Zelle.
Eigentlich müsste es, mit dem, was ich in Post #12 geschrieben habe funktionieren, also drei mal mit und vier mal ohne @.
Die leeren Zellen in Dat.Erl. werden eigentlich mit dem zweiten Teil der Formel, den wir in #10 hinzugefügt haben, berücksichtigt.
Dort vergleicht die Formel ja nach "", also einer leeren Zelle. Falls da Leerzeichen in den Zellen drinstünden, müsste es entsprechend " " sein.

 

[Onatik]

Hi @simpsonsfan.

Ich kam gestern nicht an meinen Rechner.
Habe noch ein wenig getestet.
Zur Info: Im Büro habe ich Excel 2007, die @-Zeichen bekomme ich dort nicht in die Formel... Liegt vielleicht daran.

Ich versuche es aber noch einmal.

Hier noch eine Version mit ein paar dokumentierten Auffälligkeiten...

Anhang anzeigen Arbeitsstände_2016-01-19.xlsx

PS: Keine Ahnung, mit den Anhängen bekomme ich das nicht anders hin.
Beitrag verfassen / Erweitert /Anhänge / Datei hochladen / Datei auswählen / Hochladen und Fertig bringt dann keinen Anhang zum Beitrag nur An Cursorposition einfügen (1) klappt...
Aber das ist jetzt nicht so wichtig.

Danke dir 1.000 Mal!!!

 

[Onatik]

AW: [Excel] Arbeitsstände lfd. vergleichen mit Diagramm

Guten Morgen!

Bin gerade im Büro und habe deine Version vom 18.01. geladen.
Dort wurden die @-Zeichen, welche ich auch zu Hause am Rechner mit Excel 2010 (oder 2013?) sehe, in #-Zeichen geändert.

Das nur kurz als Info.

 

[simpsonsfan]

Ach, diese Versionsunterschiede sind schon ein Ärgernis. Ich hab die Version, glaube ich, auch mit Excel 2010 bearbeitet.
Ich selbst habe bisher eigentlich sowieso nicht viel mit benannten Bereichen gearbeitet, habe da nur auf deine Tabelle aufgebaut.
Da die @ Zeichen aber schon aus deiner Bsptabelle kamen, muss es ja mit deiner Version im Büro eigentlich auch irgendwie klappen. Kann aber auch sein, dass die Bereiche dort leicht anders angesprochen werden. Wenn man da nicht grade die passende Version zum Ausprobieren hat, ist das alles etwas aufwändiger.
Hab mir deine Datei von gestern auch noch nicht angeschaut, mache ich bei Gelegenheit noch.

Ach ja, den Dateianhang siehst du übrigens im Vorschaufenster von deinem Beitrag nicht, aber wenn du auf Antworten drückst, dann wird er dem Beitrag beigefügt. Zumindest verhält es sich bei mir so.

 

[Onatik]

AW: [Excel] Arbeitsstände lfd. vergleichen mit Diagramm

Mach' das. So wie du Zeit & Lust hast.

Meine in der Version vom 19.01. dargestellten Probleme bestehen auch mit den # anstelle der @.

Ich weiß nicht, ob ich es schon geschrieben hatte, aber mir fällt noch die Funktion =INDIREKT für die Vergleiche ein. Bin aber noch nicht weiter.

 

[Onatik]

AW: [Excel] Arbeitsstände lfd. vergleichen mit Diagramm

Hi.

Habe mich nochmal intensiv mit den Funktionen von Excel 2007 beschäftigt. Offenbar gibt es die Zellenzuweisung mit @ (noch) nicht.

Jetzt gäbe es 2 Möglichkeiten:

1) Ich extrahierte die relevanten Daten (Datum und Dat. Erl.) in eine Pivot-Tabelle, was mir lieber wäre, da die Auswertungen in separaten Blättern stattfinden und benutzte eine andere Formel zum Auswerten.

2) An einer anderen Formel geht's wohl nicht vorbei.

Schönes WE schon mal 😀

 

[simpsonsfan]

Also,

zunächst einmal finde ich es recht unübersichtlich, dass die Spalte Daten-1 und die Zählspalten alle in der Pivot-Tabelle mit drin sind, obwohl sie eigentlich unabhängig von den anderen Zeilen sind. Da würde ich eine extra Tabelle erstellen.
Zweitens, nennen wir die Spalte "Daten-1" nun zur besseren Übersichtlichkeit "Auswertedatum".
Dass am 7.1. 0 statt drei stand, müsste wohl daran liegen, dass der Vergleich für die leeren Zellen hier wieder mit dem leeren Erledigungsdatum vollzogen wurde. Ich hatte das in #12 so geschrieben, weil ich da dachte, dass du das Erledigungsdatum als Auswertedatum hernehmen wolltest. Das war ein Gedankenfehler meinerseits, da es bei leerem Erledigungsdatum ja wieder keinen Vergleich gibt. (in Post #10 war's noch richtig, in der extra Tabelle)
Im Vergleich hinten sollte dann also stehen:

ZÄHLENWENNS(Eingansdatum];"<="&Auswertedatum;Erledigungsdatum;"")

Drittens: Auch ohne @ und Pivottabellen kann man Bereiche benennen zur einfacheren Verwendung in Formeln. Dazu im Reiter Formeln auf Namensmanager. Wenn ein Bereich sich automatisch erweitern soll, so kann man dazu die Formel Bereich.Verschieben verwenden.
So könntest du dann eine extra Tabelle machen (zweites Blatt z.B.) und dort jeweils auf die kompletten Bereiche Eingangsdatum und Erledigungsdatum verweisen. Das Verhgleichsdatum wäre dann wieder eine Spalte in der neuen Tabelle (d.h. quasi wie bei mir in #10 jeweils Stat.!$B$2:$B$12 und Stat.!$H$2:$H$12 durch die neuen Bereichsnamen Eingangsdatum und Ausgangsdatum ersetzen.)