Office: (Office 2013) Auswertung von Daten über Zeitraum

Hallo,
ich möchte eine Auswertung von mehreren Datensätzen machen, die ich vom Sharepoint in Excel lade.
Spalte1-Spalte2-Spalte3
Datensatz-Startdatum-Enddatum

Ziel ist es eine Diagramm zu erstellt, welches auf der x-Achse einen Zeitraum in Monaten und auf der Y-Achse die Anzahl aller Datensätze anzeigt, die in dem entsprechenden Monat noch aktiv sind,
also kleiner gleich Enddatum

Bsp:
Datensatz1: 01.01.2014 31.03.2014
Datensatz2: 01.01.2014 30.04.2014
Datensatz3: 01.03.2014 30.06.2014

Im Diagramm soll nun über die Monate folgende Anzahl abgebildet werden:
Januar: 1 (Datensatz1)
Februar: 2 (Datensatz1 und Datensatz2)
März: 3 (Datensatz 1, Datensatz2 und Datensatz3)
April: 2 (Datensatz2 und Datensatz3)
Mai: 1 (Datensatz3)
Juni: 1 (Datensatz3)
Juli: 0 (da spätestes Endedatum im Juni war)

Ich habe jetzt viel probiert mit "Zählenwenns" und "Summenproduk"t aber ich kriege es einfach nicht hin. Mit Pivor komme ich auch nicht zum richtigen Ergebnis, da ein Datensatz in mehreren Monaten "aktiv" (

:)

 

Hallo Spartacus,

umfassen die Zeiträume in deinen Datensätzen immer komplette Monate?

Gruß
-RaBe-

PS: in deinem Beispiel sollte der 2.Datensatz wohl das Beginndatum 1.2.2014 haben?

 

Hallo,
nein, die Daten können beliebige Start und Enddaten haben. Ich bin inzwischen ein Stück weiter.

Code:

Jetzt muss ich aber noch weitere Bedingungen hinzubauen. In Spalte D soll ein Status ("aktiv"/"inaktiv/"angehalten") gesetzt werden. In Abhängigkeit des Status wird dann aufsummiert. Das kriege ich in mein Summenprodukt noch nicht rein...
Spartacus

 

Hallo,

ich denke die Auswertung bekomme ich für dich hin, aber wie bewertest du Teilmonate: Wie fließt 27.4.2014 - 13.7.2014 in die Statistik ein?

VG

 

Hallo,
danke für Deine Antowort. Das Bsp. würde so aussehen, dass April bis einschl. Juli gezählt würde, wenn in der Spalte "D" "aktiv" gesetzt ist. Bei "inaktiv" würde der Zeitraum nicht ausgewertet.

Ziel ist es ein Diagramm zu erstellen, welches die Anzahl aller aktiven Datensätze im jeweiligen Monat darstellt. Das heißt, das o.a. Bsp. wird über die Dauer von April bis Juli gezählt und angezeigt.

Meine Formel macht das auch schon, allerdings ohne die BEdingung "aktiv" auszuwerten. Es wird noch weitere Bedingungen geben, die ich in die Formel einbauen muss. z.B. "Ort".
Dann zeigt Linie 1 alle aktiven Datensätze an Ort 1 an
Linie 2 alle Datensätze an Ort 2
Linie 3 die Summe (Ort 1 und Ort 2)

Sparatcus.

 

Hallo,

ich habe folgende Lösung für dich. Weitere Bedingungen kannst du nach dem Vorbild von ="aktiv" hinten anhängen.
Ein Datum muss als Datum in der Zelle stehen. Bei Monaten als erster des Monats.


Tabelle1

	A	B	C	D
1	Datensatz	Start	Ende	Status
2	1	01.01.2014	21.04.2014	aktiv
3	2	01.03.2014	07.10.2014	inaktiv
4	3	04.05.2014	03.06.2014	aktiv
5	4	03.04.2014	13.04.2014	aktiv
6	5	20.03.2014	25.12.2014	aktiv
7	6	12.01.2014	19.09.2014	aktiv
8	7	01.01.2014	01.04.2014	aktiv
9	8	01.02.2014	22.04.2014	inaktiv
10	9	03.04.2014	11.08.2014	inaktiv

Excel-Inn.de

Hajo-Excel.de

XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007

Add-In-Version 18.01 einschl. 64 Bit

Tabelle1

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

1

Dez 13

Jan 14

Feb 14

Mrz 14

Apr 14

Mai 14

Jun 14

Jul 14

Aug 14

Sep 14

Okt 14

Nov 14

Dez 14

Jan 15

Jan 15

2

3

3

4

5

3

3

2

2

2

1

1

1

0

verwendete Formeln
Zelle	Formel	Bereich	N/A
G2:S2	=SUMMENPRODUKT(N($B:$B=G1);N($D:$D="aktiv"))

Excel-Inn.de

Hajo-Excel.de

XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007

Add-In-Version 18.01 einschl. 64 Bit

Hilft dir das weiter?
Fragen dazu gerne *Smilie
VG

 

Hallo,
vielen Dank für die schnelle Lösung. Ich muss das jetzt erst einmal nachvollziehen. Ich melde mich später noch einmal dazu...

Ganz lieben Dank,
Spartatcus

 

Hallo,

anbei auch noch einmal als Datei, Fragen dazu gerne *Smilie

VG

 

Hallo Mc Santa,
ganz große Klasse! Und ganz herzlichen Dank. Das funktioniert super!
Werde es morgen dann in meine Umgebung einbauen und die Diagramme erstellen.

Da wäre ich im Leben nicht drauf gekommen!

Herzlichen Dank und Gruß,
Spartacus.

 

Hallo,
ich habe doch noch ein Problem. Die Formel funktioniert einwandfrei.
Code:

Nun habe ich eine Data Connection auf einen Sharepoint und der Bereich $B$2:$B$10 soll auf den Bereich der Daten zugreifen, die vom SP gelesen werden.
also habe ich folgendes gemacht:
Code:

Der Bereich ist ebenfalls ein Datumsfeld. Leider zeigt er mir im Ergebnis der Formel immer #Wert an. und ich weiß nicht woran das liegt.

Hat jemand eine Idee?
Spartacus.

 

Hallo,

damit Summenprodukt rechnen kann, müssen alle Spalten gleich lang sein. Du musst also auch bei H:H noch die Zeilennummern ergänzen.

Behebt das schon den Fehler?
VG

 

Hallo,
nee leider nicht! Habe jetzt H2 bis H50 eingegeben und der Fehler bleibt. Wenn ich den Bereich in der Formel mit der Maus auswähle, dann ändert er es in der Formel auch automatisch in
"Tabelle_owssvr[Date entered]" was den aktuellen Bereich der Daten vom SP mit der Headline "Date entered" entspricht. Das muss ja auch so sein, da sich die Bereichsgröße ändert, wenn Daten auf dem SP hinzukommen und man diese mit "Alle aktualisieren" vom SP läd!
Er scheint irgendwie mit dem Datum nicht klar zu kommen.

ok! Kommando zurück. Alle einzelnen Bereiche in der Formel müssen identisch sein.
Das hatte ich so nicht verstanden...
Spartacus

 

Hallo,
ok! Das geht jetzt mit dem SP Bereich!

Es ist aber immer noch ein logischer Fehler drin.

Nehmen wir mal an, wir sind im aktuellen Monat Juli und haben im Juli 4 Datensätze mit Startdaten am 07.07/08.07/08.07/14.07.

Die Datensätze haben noch kein Ende-Datum, da es noch in der Zukunft liegt (z.B im August)
Nun müssten eigentlich die 4 Datensätze im Juli angezeigt werden! Für Juli werden aber keine Datensätze angezeigt. Das ist so noch nicht korrekt!
Spartacus

 

Hallo,

die Formeln decken derzeit nur Zeiträume ab. Offenes Ende ist eine neue Anforderung.

Zwei Varianten:
schnellste Lösung: 31.12.9999 als Ende für offene Zeiträume eingeben.

Ungetestet geht vielleicht auch diese Formel:

PHP:

         SUMMENPRODUKT(N($B$2:$B$10<H1)+N($B$2:$B$10="");N($C$2:$C$10>=G1);N($D$2:$D$10="aktiv"))


 

Hi,
das mit dem fiktiven Datum wäre ein Workaround der funktioniert.

Allerdings muss ich dann im SP etwas ändern.
Das Ende Datum muss dann automatisch auf dasfiktive Datum z.B. (01.01.2200) gesetzt werden, wenn ein neuer Datensatz erzeugt wird.

Wird der Status des Datensatzes im Sharepoint dann von "open/aktiv" auf "close/inaktiv" gesetzt, muss das fiktive Ende Datum mit dem aktuellen Ende Datum überschrieben werden.

Der andere Weg läuft m.E. nicht, da die Daten, die inaktiv ("close") sind auch in ihrem "aktiven" Zeitraum (bis zum Ende Datum) nicht mehr gezählt würden!

Mal gucken, wie ich das im SP löse, wahrscheinlich mit einem SP-Workflow...
Spartacus.