Summieren bis Summe in anderer Zeile 15 ist

[MT1986]

Hallo zusammen,
ich habe folgendes Problem in meiner Excel Tabelle (Excel-Datei ist angehängt):
Im Reiter "Milestone Dates" werden alle Projekte mit Daten angezeigt. Dabei wird unterschieden ob das Datum nur geplant oder wirklich durchgeführt wurde. Wie viele Daten im jeweiligen Monat geplant und durchgeführt wurden, wird im Reiter "MilestoneFirstPassRate" angezeigt (Zeile 3 und 4). Nun möchte ich mir immer für einen Monat anzeigen lassen (Zeile 6), wie viele der letzten 15 (!) Daten nicht nur geplant sondern auch durchgeführt wurden. Dafür muss man ja im Prinzip "nur" die durchgeführten Daten durch die letzten 15 Daten nehmen. Diese Formel bekomme ich aber absolut nicht hin. Ich habe das jetzt immer händisch gemacht, was aber auf Dauer sehr mühselig ist. Die Brechnung erfolgt nur im Reiter "MilestoneFirstPassRate", den anderen Reiter habe ich nur benötigt um die Daten rauszuholen und in Zeile 3 un4 zu packen.
Als Beispiel nehmen wir den Jan 18: Man muss vom Jan 18 zurück rechnen bis 15 Daten geplant sind. Also Jan18 Dez17 Nov17 Okt17 Sep17 Aug17 Juli17 Juni17 Mai17 Apr17 Mrz17 Feb17 Jan17 (2+0+3+0+2+1+1+1+0+2+1+1+1=15), dann zählt man die durchgeführten Daten aus Zeile 4 dazu zusammen (1+0+3+0+2+1+1+1+0+1+1+1+0=12). Demnach müsste 12/15--> 80%.
Ich weiß nicht wie man das hinbekommt, vor allem auch dieses "rückwärts rechnen" bis 15 ist mir ein Rätsel. Eine Besonderheit ist noch, wenn man bei 14 geplanten ist und der nächste Monat z.B. 3 geplante Daten hat, dann bekommt man insg. 17 geplante Daten. Das ist so in Ordnung, es sollen also min 15 Daten sein.
Es wäre super wenn mir jemand helfen könnte.
Vielen Dank im Voraus.
Schöne Grüße
Michael

[Elex]

Hi

Das mit den verbunden Zellen ist meistens keine gute Idee.

Im Anhang mal ein Versuch dein Problem zu lösen.

  Summieren_wenn2.xlsx (Größe: 137,65 KB / Downloads: 16)

Gruß Elex

[MT1986]

Danke für deine Mühen und die Lösung, die auch soweit zu funktionieren scheint
Die Lösung mit verbundenen Zellen ist nicht möglich? Weil sehr viele von diesen Tabellen bereits existieren und da auch Makros hinter stecken und der Aufwand für die Änderung sehr hoch wäre.
Wenn in Zukunft weitere Monate dazu kommen, müsste die Hilfstabelle wahrscheinlich auch immer weiter angepasst werden oder?
Die Lösung mit den verwendeten Formeln verstehe ich ehrlich gesagt überhaupt nicht. Könntest du mir diese erklären? Also die Formeln in den Hilfsspalten und für die Prozentberechnung.
Schöne Grüße

[WillWissen]

Hi,

bzgl. deiner Frage zu verbundenen Zellen kann ich dir diesen Lesestoff  http://www.clever-excel-forum.de/thread-142.html  anbieten.

[Elex]

Hi

Die Lösung mit verbundenen Zellen ist nicht möglich?
Weil sehr viele von diesen Tabellen bereits existieren und da auch Makros hinter stecken und der Aufwand für die Änderung sehr hoch wäre.

Wenn in Zukunft weitere Monate dazu kommen, müsste die Hilfstabelle wahrscheinlich auch immer weiter angepasst werden oder?

Nicht nur die Hilfstabelle, deine Tabelle und die Makros doch dann auch oder?

 
Momentan bildest du 3 Jahre ab. Was bedeutet hinzufügen? Noch evtl. zwei drei Monate oder kontinuierlich und der Zeitraum wächst auf 10 (15, 20) Jahre.

Gruß Elex

[MT1986]

Das Makro holt glaube ich nur aus anderen Dateien die Daten für den zweiten Reiter. Demnach dürfte das ja sogar kein Problem darstellen wenn ich die Zellen nicht mehr verbinde.
Ja es soll eigentlich jeden Monat der aktuelle Monat hinzugefügt werden, also in Zukunft dann mehrere Jahre. Also auch händisch denke ich. Ich bin leider nicht der Ersteller der Datei, aber dieser ist nicht mehr im Unternehmen.

[Elex]

Hi

Ok dann wäre das mit den verbunden Zellen geklärt.

Ich bin leider nicht der Ersteller der Datei, aber dieser ist nicht mehr im Unternehmen.

Da du jetzt der jenige bist der für die Datei zuständig ist.
Ich weiß nicht warum der Kollege damals den Zeitverlauf von alt nach neu gewählt hat. Würde ich einfach drehen.
Vorteile:
- aktuellen Werte bleiben links und du brauchst nicht nach rechts scrollen oder Spalten ausblenden.
- Erweiterung um Monate recht einfach durch ziehen (glaub ich im Moment zumindest)
- etwas bessere nachvollziehbarkeit der Formel in der Hilfstabelle

[MT1986]

Erweiterung um Monate recht einfach durch ziehen (glaub ich im Moment zumindest)

So wie es ist, ist es glaube ich am einfachsten. Man muss ja einfach nur nach rechts ziehen weil da ja alles leer ist. Anders herum müsste man ja eine Spalte einfügen oder verstehe ich das falsch?!
Könntest du mir kurz erklären was genau die einzelnen Formeln machen? Das wäre hilfreich wenn ich dann mal eine Kleinigkeit ändern möchte:
Hilfsspalte 1:
einfach nur eine Durchnummerierung?! Wofür?
Hilfsspalte 2 Beispiel G35: =INDEX($G$3:$AQ$3;;ZEILE(A26))
was wird damit gemacht? A26 zB ist ja einfach nur leer
Hilfsspalte 3 Beispiel H35: VERGLEICH(1;1*(TEILERGEBNIS(9;INDIREKT("G"&ZEILE(A35)&":G"&ZEILE($A$10:INDEX(A:A;ZEILE(A35);))))<15);0)-1
da blicke ich überhaupt nicht mehr durch
Berechnung Prozent Beispiel AL6: =WENNFEHLER(SUMME(AL4:INDEX($G$4:AL4;;VERWEIS(SPALTE(AF1);$F$10:$F$46;$H$10:$H$46)))/SUMME(AL3:INDEX($G$3:AL3;;VERWEIS(SPALTE(AF1);$F$10:$F$46;$H$10:$H$46)));"")
verstehe ich auch gar nicht
Ich kann mir vorstellen, wie viel Arbeit und nervig die Erklärung ist, aber damit würdest du mir sehr weiterhelfen :)

[Elex]

Hi

So wie es ist, ist es glaube ich am einfachsten. Man muss ja einfach nur nach rechts ziehen weil da ja alles leer ist. Anders herum müsste man ja eine Spalte einfügen oder verstehe ich das falsch?!

So wie die Tabelle im Moment ist, glaube ich dass sie nur für die Darstellung von einem festen Zeitraum von drei Jahren geplant  war. Denn eine Erweiterung nur durch nach rechts ziehen klappt nicht.
Für den Test alle anderen Anwendungen schließen.
Ziehe die rechte Zelle mit Datum März 2018 weiter nach rechts. Es erscheint Apr. 2018 speichere und schließe die Datei. Jetzt ändere die Systemzeit zur Simulation auf 03.04.2018 um. Öffne die Datei. Ich glaube nicht dass es jetzt noch dem Wunschergebnis entspricht.
 
Also ohne Überarbeitung wird es nichts mit einfach Monate hinzufügen. Und wenn schon Überarbeiten dann doch gleich vorteilhaft.

[MT1986]

Kann hier die Systemzeit leider nicht ändern.
Welchen Vorteil hat denn deine Version:
=MONATSENDE(HEUTE();SPALTE(AJ1)-38)
zu der vorigen
=MONATSENDE(HEUTE();-2)
Wofür brauch man "Spalte(AJ1)", die ist doch leer?!
und ich habe gerade ausprobiert, ob man einfach 31.3.16, 30.4.16 in zwei Zellen eingeben kann und dann nach rechts ziehen, dann nimmt er auch immer das Monatsende. Warum benutzt man dann überhaupt den Befehl mit "Monatsende"?

Und wie kann man einen Monat hinzufügen, wenn man die Monatsanzeige umdreht und den Neuesten ganz vorne hat (wie du vorgeschlagen hast)? Dann muss man doch auch jeden Monat eine neue Spalte ganz vorn hinzufügen oder nicht?