Hallo,
habe ein Problem mit der Umsetzung einer Berechnung in Excel, wo mir momentan die Ideen ausgegangen sind.
Vielleicht kann mir jemand weiterhelfen.
Entschuldigung für die nun folgende relativ lange Schilderung meines Problems, aber in kürzerer Form liess sich das leider nicht anschaulich darstellen.
Ich möchte (muss) eine Prognose für verschiedene Einrichtungen (nachfolgend E) anfertigen.
Jede E hat 4 Stufen mit jeweils einer Teilnehmerzahl. Mit jedem Jahreswechsel treten in Stufe 1 neue Teilnehmer ein, Teilnehmer der Stufe 4 verlassen die E und die Teilnehmer der Stufen 2 und 3 werden höhergestuft.
Die Prognose für die Stufen 2 bis 4 (aufsteigende Stufen) soll auf den durchschnittlichen Übergangsquoten aus 3 Vorjahren basieren.
Die Grundlagedaten für die Quotenberechnung befinden sich in einem Tabellenblatt nach folgendem Schema:
bestehende E’en in einem Tabellenblatt (Tabelle1):
Einrichtung Jahr Stufe1 Stufe2 Stufe3 Stufe4
E1 Jahr1 19 18 31 20
E1 Jahr2 24 19 23 27
E1 Jahr3 39 44 39 39
E1 Jahr4 36 38 44 37
E2 ...
Die durchschnittlichen Übergangsquoten der letzten drei Jahre in die jeweils nächsthöhere Stufe werden derzeit in einem extra Tabellenblatt nach dem folgenden Prinzip berechnet:
Summe(Stufe x+1 von Jahr2 bis Jahr4)/Summe(Stufe x von Jahr1 bis Jahr3).
Dies habe ich bereits in Excel umgesetzt.
Für eine bereits erfolgte Prognose hat dies auch wunderbar funktioniert.
Nun tritt allerdings das Problem auf, dass in den letzten Jahren zahlreiche E’en aufgelöst wurden und verbleibende Teilnehmer anderen E’en zugeordnet wurden (daher bspw. die Erhöhung in E1 ab Jahr3)
Das ist ein einmaliges Ereignis, welches die Durchschnittswerte stark verfälscht, so dass diese nicht mehr für eine Prognose verwertbar sind.
Ich muß daher die Einzelwerte beginnend mit dem Jahr nach der Auflösung einer Einrichtung bereinigen, dass heißt die Teilnehmer der aufgelösten Einrichtung (bspw. E3) müssen im Jahr der Zuordnung und auch danach unberücksichtigt bleiben.
Die Bereinigung würde prinzipiell so verlaufen:
Stufe (x+1) im Jahr (y+1) der E(aufnehmend) MINUS Stufe x im Jahr y der E(aufgelöst),
für das Folgejahr
Stufe (x+2) im Jahr (y+2) der E(aufnehmend) MINUS Stufe x im Jahr y der E3(aufgelöst) u.s.w.
(bspw. Stufe 2 der Einrichtung1 im Jahr3 MINUS Stufe 1 der Einrichtung3 im Jahr2,
für das Folgejahr
Stufe 3 der Einrichtung1 im Jahr 4 MINUS Stufe 1 der Einrichtung3 im Jahr2)
Als Datengrundlage für die Bereinigung habe ich die letzten Teilnehmerzahlen der aufgelösten E’en in einem extra Tabellenblatt (Tabelle 2) mit einer Angabe zur aufnehmenden E erfasst:
abgegeb. E aufnehm. E Jahr Stufe1 Stufe2 Stufe3 Stufe4
E3 E1 Jahr2 22 19 18 22
E4 E2 ...
Ich hoffe, ich konnte die Ausgangslage einigermaßen verdeutlichen.
Mein Ziel ist es, aus den gegebenen Grunddaten (Tabelle1 und Tabelle2) eine Tabelle analog Tabelle 1 zu erstellen, in der nicht die tatsächlichen Teilnehmerzahlen enthalten sind, sondern nach oben genannter Schilderung bereinigte hypothetische Zahlen, die direkt für die Berechnung der Durchschnittsquote verwendet werden können.
Dazu benötige ich Hinweise, Denkanregungen oder Lösungsvorschläge, wie sich dies umsetzen lässt. Möchte möglichst Formeln verwenden, und wenig manuell machen, da es sich um ca. 80 bestehende (mit jeweils 4 Datensätzen) und ca. 30 aufgelöste Einrichtungen handelt.
(Vielleicht geht da mit Matrixformeln was??)
Bin für jeden Vorschlag dankbar.
Viele Grüße
tester