PDA

Vollständige Version anzeigen : Auswertung von Daten über Zeitraum


Spartacus
11.07.2014, 16:26
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" (<= Enddatum) gezählt werden muss.
hat jemand eine Idee?
Danke,
Spartacus

-RaBe-
14.07.2014, 09:38
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?

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

Spalte A: Datensatz 1..n
Spalte B: Startdaum
Spalte C: Enddatum
Spalte E: Monat im Format 01.01.2014...01.12.2014
Spalte F: =SUMMENPRODUKT((MONAT(B$2:B$99)<=MONAT(E2))*(MONAT(C$2:C$99)>=MONAT(E2))*(B$2:B$99>0))

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

Mc Santa
14.07.2014, 19:22
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

Spartacus
15.07.2014, 08:21
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.

Mc Santa
15.07.2014, 09:21
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.

<br/><b><em>Tabelle1</em></b><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "><colgroup><col style="font-weight:bold; width:40px;" /><col style="width:82px;" /><col style="width:97px;" /><col style="width:97px;" /><col style="width:73px;" /></colgroup><tr style="background-color:#99CCFF; text-align:center; font-weight:bold; "><td>&nbsp;</td><td>A</td><td>B</td><td>C</td><td>D</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">1</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Datensatz</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Start</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Ende</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Status</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">2</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">1</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">01.01.2014</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">21.04.2014</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">aktiv</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">3</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">2</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">01.03.2014</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">07.10.2014</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">inaktiv</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">4</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">3</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">04.05.2014</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">03.06.2014</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">aktiv</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">5</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">4</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">03.04.2014</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">13.04.2014</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">aktiv</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">6</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">5</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">20.03.2014</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">25.12.2014</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">aktiv</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">7</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">6</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">12.01.2014</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">19.09.2014</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">aktiv</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">8</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">7</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">01.01.2014</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">01.04.2014</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">aktiv</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">9</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">8</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">01.02.2014</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">22.04.2014</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">inaktiv</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">10</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">9</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">03.04.2014</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">11.08.2014</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">inaktiv</td></tr></table><table cellspacing="0" cellpadding="0"><tr style="text-align:left; font-weight:bold; " class="style21"><td style="text-align:left; font-size: xx-small" ><a href="http://excel-inn.de/dateien/vba_beispiele/tabellenanzeige_in_html_addin.zip" >Excel-Inn.de</a></td></tr> <tr style="text-align:left; font-weight:bold; " class="style21"><td style="text-align:left; font-size: xx-small" ><a href='http://Hajo-Excel.de/tools.htm' >Hajo-Excel.de</a></td></tr><tr style="text-align:left; font-weight:bold;" ><td style="text-align:left; font-size: xx-small" >XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007</td></tr><tr style="text-align:left; font-weight:bold; " ><td style="text-align:left; font-size: xx-small" > Add-In-Version 18.01 einschl. 64 Bit</td></tr></table><br/>

<br/><b><em>Tabelle1</em></b><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "><colgroup><col style="font-weight:bold; width:40px;" /><col style="width:60px;" /><col style="width:60px;" /><col style="width:60px;" /><col style="width:60px;" /><col style="width:60px;" /><col style="width:60px;" /><col style="width:60px;" /><col style="width:60px;" /><col style="width:60px;" /><col style="width:60px;" /><col style="width:60px;" /><col style="width:60px;" /><col style="width:60px;" /><col style="width:60px;" /><col style="width:60px;" /></colgroup><tr style="background-color:#99CCFF; text-align:center; font-weight:bold; "><td>&nbsp;</td><td>F</td><td>G</td><td>H</td><td>I</td><td>J</td><td>K</td><td>L</td><td>M</td><td>N</td><td>O</td><td>P</td><td>Q</td><td>R</td><td>S</td><td>T</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">1</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Dez 13</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Jan 14</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Feb 14</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Mrz 14</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Apr 14</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Mai 14</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Jun 14</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Jul 14</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Aug 14</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Sep 14</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Okt 14</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Nov 14</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Dez 14</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Jan 15</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Jan 15</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">2</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">3</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">3</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">4</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">5</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">3</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">3</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">2</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">2</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">2</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">1</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">1</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">1</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">0</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td></tr></table><br/><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px;font-size:11pt; background-color:#ffffff; width:800px;padding-left:2pt; padding-right:2pt; "><tr style="background-color:#FFCC66; text-align:center; font-weight:bold; "><td colspan="3" > verwendete Formeln </td><td> </td></tr><tr valign="top" style="background-color:#FFCC66; text-align:center; font-weight:bold; "><td> Zelle </td><td> Formel</td><td> Bereich </td> <td>N/A</td></tr><tr><td>G2:S2</td><td>=SUMMENPRODUKT(N($B$2:$B$10&lt;H1);N($C$2:$C$10&gt;=G1);N($D$2:$D$10="aktiv"))</td><td>&nbsp;</td><td>&nbsp;</td></tr></table><table cellspacing="0" cellpadding="0"><tr style="text-align:left; font-weight:bold; " class="style21"><td style="text-align:left; font-size: xx-small" ><a href="http://excel-inn.de/dateien/vba_beispiele/tabellenanzeige_in_html_addin.zip" >Excel-Inn.de</a></td></tr> <tr style="text-align:left; font-weight:bold; " class="style21"><td style="text-align:left; font-size: xx-small" ><a href='http://Hajo-Excel.de/tools.htm' >Hajo-Excel.de</a></td></tr><tr style="text-align:left; font-weight:bold;" ><td style="text-align:left; font-size: xx-small" >XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007</td></tr><tr style="text-align:left; font-weight:bold; " ><td style="text-align:left; font-size: xx-small" > Add-In-Version 18.01 einschl. 64 Bit</td></tr></table><br/>

Hilft dir das weiter?
Fragen dazu gerne :)
VG

Spartacus
15.07.2014, 14:36
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

Mc Santa
15.07.2014, 14:57
Hallo,

anbei auch noch einmal als Datei, Fragen dazu gerne :)

VG

Spartacus
15.07.2014, 18:46
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.

Spartacus
18.07.2014, 12:56
Hallo,
ich habe doch noch ein Problem. Die Formel funktioniert einwandfrei.
=SUMMENPRODUKT(N($B$2:$B$10<F3);N($C$2:$C$10>=F2);N($D$2:$D$10="aktiv");N($E$2:$E$10="A"))

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:
=SUMMENPRODUKT(N(SP_Report!H:H<F3);N($C$2:$C$10>=F2);N($D$2:$D$10="aktiv");N($E$2:$E$10="A"))
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.

Mc Santa
18.07.2014, 13:01
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

Spartacus
18.07.2014, 13:11
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

Spartacus
18.07.2014, 14:17
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

Mc Santa
18.07.2014, 14:26
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:
SUMMENPRODUKT(N($B$2:$B$10<H1)+N($B$2:$B$10="");N($C$2:$C$10>=G1);N($D$2:$D$10="aktiv"))

Hilft dir einer der Ansätze?
VG

Spartacus
18.07.2014, 15:14
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.

Mc Santa
18.07.2014, 15:33
Hallo,

mir wurde die Formel abgeschnitten, sie sollte so aussehen:
SUMMENPRODUKT(N($B$2:$B$10<H1)+N($B$2:$B$10="");N($C$2:$C$10>=G1);N($D$2:$D$10="aktiv"))

Spartacus
21.07.2014, 07:54
Hallo,
Besten Dank! Aber stimmt m.E. immer noch nicht!
Gehen wir mal von Deiner Tabelle auf der ersten Seite als Datenbasis aus.

Da steht für Datensatz1:
Start: 01.01.2014
Ende: 21.04.2014
Für Januar werden 3 Datensätze gezählt, das ist korrekt!
Lösche ich nun das Ende Datum für Datensatz 2, so dürfte sich die Anzahl nicht ändern. Bei mir werden mit der neuen Formel aber nur 2 Datensätze für Januar gezählt. Wo habe ich denn jetzt den Fehler gemacht!

Sparatcus

Mc Santa
21.07.2014, 07:57
Hallo,

lade mal eine kleine Tabelle mit Problemfällen hoch: Eine Spalte mit der Formel und eine Spalte danaben mit deinem Wunschergebnis.
Dann probiere ich ein wenig herum :)

Viele Grüße

Spartacus
21.07.2014, 08:43
Hallo,
Zunächst noch mal ganz lieben Dank für Deine tolle Unterstützung!

Ich habe mal die Datei angefügt und im Kommentarfeld beschrieben, welche Datensätze gezählt werden sollten.

Spartacus

Mc Santa
21.07.2014, 08:53
Hallo,

ok das war ein Fehler in meiner Formel. Ich habe richtig gedacht, aber falsch angewendet :P
Hier die korrigierte Version:
<br/><b><em>Tabelle1</em></b><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "><colgroup><col style="font-weight:bold; width:40px;" /><col style="width:54px;" /><col style="width:57px;" /><col style="width:57px;" /><col style="width:54px;" /><col style="width:56px;" /><col style="width:54px;" /><col style="width:50px;" /><col style="width:58px;" /><col style="width:58px;" /><col style="width:56px;" /><col style="width:56px;" /><col style="width:58px;" /><col style="width:54px;" /><col style="width:54px;" /></colgroup><tr style="background-color:#99CCFF; text-align:center; font-weight:bold; "><td>&nbsp;</td><td>G</td><td>H</td><td>I</td><td>J</td><td>K</td><td>L</td><td>M</td><td>N</td><td>O</td><td>P</td><td>Q</td><td>R</td><td>S</td><td>T</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">1</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">Jan 14</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">Feb 14</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">Mrz 14</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">Apr 14</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">Mai 14</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">Jun 14</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">Jul 14</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">Aug 14</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">Sep 14</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">Okt 14</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">Nov 14</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">Dez 14</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">Jan 15</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">Feb 15</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">2</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">1</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">1</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">3</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">4</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">4</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">4</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">3</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">3</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">3</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">3</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">2</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">2</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">1</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td></tr></table><br/><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px;font-size:11pt; background-color:#ffffff; width:800px;padding-left:2pt; padding-right:2pt; "><tr style="background-color:#FFCC66; text-align:center; font-weight:bold; "><td colspan="3" > verwendete Formeln </td><td> </td></tr><tr valign="top" style="background-color:#FFCC66; text-align:center; font-weight:bold; "><td> Zelle </td><td> Formel</td><td> Bereich </td> <td>N/A</td></tr><tr><td>G2:S2</td><td>=SUMMENPRODUKT(N($B$2:$B$10&lt;H1);N($C$2:$C$10&gt;=G1)+N($C$2:$C$10="");N($D$2:$D$10="aktiv"))</td><td>&nbsp;</td><td>&nbsp;</td></tr></table><table cellspacing="0" cellpadding="0"><tr style="text-align:left; font-weight:bold; " class="style21"><td style="text-align:left; font-size: xx-small" ><a href="http://excel-inn.de/dateien/vba_beispiele/tabellenanzeige_in_html_addin.zip" >Excel-Inn.de</a></td></tr> <tr style="text-align:left; font-weight:bold; " class="style21"><td style="text-align:left; font-size: xx-small" ><a href='http://Hajo-Excel.de/tools.htm' >Hajo-Excel.de</a></td></tr><tr style="text-align:left; font-weight:bold;" ><td style="text-align:left; font-size: xx-small" >XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007</td></tr><tr style="text-align:left; font-weight:bold; " ><td style="text-align:left; font-size: xx-small" > Add-In-Version 18.01 einschl. 64 Bit</td></tr></table><br/>


Wichtig ist, dass es = "" prüft (ob Leer) und nicht = " " (ob ein Leerzeichen). Und du musst bei den Spalten immer einen Monat mehr in deine Tabelle nehmen, als du auswertest, da sich die Formel auch auf den Monat rechts daneben bezieht.

Funktioniert es bei dir?


VG

Spartacus
21.07.2014, 11:23
Hallo Mc Santa,
Bingo! So wie es aussieht, wird jetzt korrekt gezählt! Habe es an mehreren Datensätzen durchgespielt.
Ganz lieben Dank!

Eine Frage noch!
Ich lasse mir die Daten nun in einem Liniendiagramm anzeigen. Jetzt würde es aber Sinn machen, wenn die x-Achse (Zeitachse) des Diagramms immer automatisch bis zum jeweils aktuellen Monat laufen würde; momentan also auf "Juli" begrenzt würde! Geht so etwas auch? Ich habe zwar die Möglichkeit gefunden, die Werte im Diagramm zu Fuß zu filtern, wäre aber schöner, wenn man nicht jedesmal zu Fuß eingreifen müsste!

geplantes Szenario:

Start der Excel-Datei
Daten werden automatisch vom SP geladen und aktualisiert (das funzt schon!)
Auf dem Dashboard wird die Grafik automatisch bis zum aktuellen Kalendermonat angezeigt.


Geht wahrscheinlich nur mit Programmcode, oder?
Sparatcus.

Mc Santa
21.07.2014, 11:53
Hallo,

das geht ebenfalls noch ohne VBA, indem du dynamische Diagramme benutzt. Die Lösung ist allerdings nicht sehr intuitiv, ich habe dir einmal ein Beispiel erstellt. (siehe Anhang)

Definiere Namen:
Anzahl: =Tabelle1!$G$2:INDEX(Tabelle1!$G$2:$T$2;;VERGLEICH(HEUTE();Tabelle1!$G$1:$T$1;1) )
Monate: =Tabelle1!$G$1:INDEX(Tabelle1!$G$1:$T$1;;VERGLEICH(HEUTE();Tabelle1!$G$1:$T$1;1) )

Hilft dir das weiter?
VG

Spartacus
21.07.2014, 13:40
Hallo,
tja! kriege ich nicht hin!. Irgendetwas mache ich noch falsch!

Mit Strg F3 kann ich die Namen überprüfen.
Das habe ich gemäß Deinem Bsp. umgesetzt. Der Datenbereich geht von B4 bis C39 (bis Dez. 2016)
Monate: =Tabelle1!$B$4:INDEX(Tabelle1!$B$4:$B$39;;VERGLEICH(HEUTE();Tabelle1!$B$4:$B$39; 1))
Anzahl: =Tabelle1!$C$4:INDEX(Tabelle1!$C$4:$C$39;;VERGLEICH(HEUTE();Tabelle1!$C$4:$C$39; 1))


Daraus lasse ich ein Liniendiagramm erstellen. Nun gehe ich in das Diagramm -> Datenquelle auswählen, und ändere den "horizontalen Achsenbeschriftungsbereich" von
=Tabelle1!$B$4:$B$39
auf
=Tabelle1!Monate

...und dann kommt ein "Formel-Bezugsfehler"!
Spartacus

Mc Santa
21.07.2014, 13:49
Hallo,

du hast die Auswertung untereinander stehen und nicht nebeneinander, wie in meiner Datei, ist das richtig?

VG

Spartacus
21.07.2014, 14:03
Hi,
ja, das ist richtig! In meiner produktiven Umgebung ist das alles untereinander...

Sparatacus

Mc Santa
21.07.2014, 14:04
Gut, dann probiere es einmal so:
(Details sind wichtig)
Monate: =Tabelle1!$B$4:INDEX(Tabelle1!$B$4:$B$39;VERGLEICH(HEUTE();Tabelle1!$B$4:$B$39;1 ))
Anzahl: =Tabelle1!$C$4:INDEX(Tabelle1!$C$4:$C$39;VERGLEICH(HEUTE();Tabelle1!$B$4:$B$39;1 ))

Geht es so?
VG

Spartacus
21.07.2014, 14:41
Hi,
nein, irgendetwas mache ich anders!. Er nimmt die Änderung des Datenbereichs schon gar nicht an, wenn ich das in "!Monate" bzw. !Anzahl" ändere. Und das hängt mit der Summenproduktformel zusammen, die die Spalten C und D bzw. H und I berechnet. Die Rohdaten sind in dem Tabellenblatt "Report" und werden vom SP geladen.
(=SUMMENPRODUKT(N(Report!$H:$H<$B5);N(Report!$L:$L>=$B4)+N(Report!$L:$L="");N(Report!$F:$F="A");N(Report!$E:$E="B")))

Wenn ich die Formelndurch Werte ersetzte (siehe Anlage) dann funzt das mit dem Diagramm und die Daten werden bis Juli angezeigt.

Spartacus

Mc Santa
21.07.2014, 14:48
Hallo,

also die genannte Tabelle habe ich nicht gefunden. Dafür aber das Diagramm angepasst, schau dir mal die Formeln und Verweise in der Datei an.
An der Formel kann es eigentlich nicht liegen, bei meinem ursprünglichen Beispiel mit Formel hat es auch funktioniert.
Ansonsten kannst du auch noch einmal die Datei mit Formeln hochladen

Hilft dir das weiter?

VG

Spartacus
21.07.2014, 14:53
Hallo,
ich kann den Unterschied zu meiner 2ten Excel-Tabelle (...bisJuli) aus letzte Post nicht erkennen. Das Diagramm schaut identisch aus!

Oder was sehe ich da nicht?
Spartacus

Mc Santa
21.07.2014, 14:57
Wie müsste es denn aussehen? In deiner zweiten Tabelle ist es dynamisch.

Spartacus
21.07.2014, 15:06
Hi,
ja genau so! Die Ansicht in der 2.ten Tabelle ist völlig korrekt!
Ich habe ja auch ich die Formeln in Spalte C und D gegen Werte ersetzt.
Sobald ich die Spalten C und D mit Hilfe der Summenproduktformel errechnen lasse (Rohdaten in Tabellenblatt "Report") kann ich die variable "Monat" nicht verwenden und er kommt mit dem Formelbezugsfehler!

Ich werde die Rohdaten mal anonymisieren und als Bsp.- Datei posten...

Sparatcus

Mc Santa
21.07.2014, 15:09
Alles klar, ich werde dir dann helfen :)

(PS: in beiden deiner letzten Dateien waren Werte. Vielleicht ist da beim Kopieren etwas schief gegangen)

VG

Spartacus
21.07.2014, 15:30
Hallo,
Besten Dank!
Ich habe jetzt genau die Situation nachgestellt, die den Fehler verursacht. Die Daten aus Tabellenblatt "Report" werden im produktiven Umfeld vom SP geladen (die DataConnection habe ich jetzt entfernt)

Ziel ist es, für jede Tabelle (A und B) auf der Dashboard Seite ein Liniendiagramm mit 3 Linien zu erstellen:
Linie1: Reihe 1
Linie2: Reihe 2
Linie3: Summe

Sobald ich aber versuche, im abgebildeten Diagramm den Datenbereich zu ändern (durch Variable Monate), kommt die besagte Fehlermeldung!

Ich hoffe du findest den Fehler!

Sparatcus

Mc Santa
21.07.2014, 15:43
Hallo,

ich habe erneut die gleichen Änderungen vorgenommen:
Vorher:
Anzahl: =Dashboard!$C$4:INDEX(Dashboard!$C$4:$C$39;;VERGLEICH(HEUTE();Dashboard!$C$4:$C$ 39;1))
Monat: =Dashboard!$B$4:INDEX(Dashboard!$B$4:$B$39;;VERGLEICH(HEUTE();Dashboard!$B$4:$B$ 39;1))

Nachher:
Semikolon gelöscht, ein Vergleich angepasst. Beide Vergleiche müssen sich auf die Datumsspalte beziehen! auch wenn sich Index einmal auf die Anzahl bezieht.
Anzahl: =Dashboard!$C$4:INDEX(Dashboard!$C$4:$C$39;VERGLEICH(HEUTE();Dashboard!$B$4:$B$3 9;1))
Monate: =Dashboard!$B$4:INDEX(Dashboard!$B$4:$B$39;VERGLEICH(HEUTE();Dashboard!$B$4:$B$3 9;1))

Anschließend werden die Namen vom Diagramm übernommen.

Ich hoffe es geht jetzt bei dir auch.

VG

Spartacus
21.07.2014, 16:50
Hallo,
nochmals ganz lieben Dank! Es funktioniert! Ganz tolle Unterstützung! Wenn Du mal in der Nähe bist (Raum Dortmund) komm gerne auf ein Glas Bier vorbei :-)

Ich habe die Formeln über eine Remotesession in Notepad kopiert und nicht gemerkt, dass er immer das doppelte Semikolon dareingefummelt hat! Junge, Junge, war das eine Geburt!

Gruß,
Sparatcus

Mc Santa
21.07.2014, 17:43
Danke für das Angebot, aber Dortmund ist zu weit weg. Immerhin bin ich Dortmund-Fan, falls das hilft? ^^

Zur Formel:
Als der Bereich noch waagerecht war, brauchte man beide Semikolon ;)

VG

Spartacus
21.07.2014, 19:27
Hallo,
ach so! Und ich dachte es wäre beim Kopieren passiert! Bin natürlich auch ein Dortmund Fan! Wünschen wir den Dortmundern einen guten Auftakt in die neue Bundesliga Saison! Vielleicht klappt es ja mal wieder...
Viele Grüße,
Spartacus