Anpassung von Zahlen, um bestimmte Summe zu erhalten
#1
Lightbulb 
Hallo,
ich wende mich nun an euch mit einer Problemstellung, die mir seit Tagen Kopfzerbrechen bereitet. Eine vereinfachte Beispieldatei hänge ich an. Es geht um folgendes:

Ich habe in Spalte B eine Reihe von Erwartungswerten für jeden Monat, die sich im Original aus den Istkosten von Vorjahren ergeben. In Summe ergeben diese fast 31 Mio. €. Gibt man in B2 ein Planbudget ein, werden diese Erwartungswerte in Spalte C angepasst auf das angegebene Budget (hier 25 Mio.).

Nun zu meiner Problemstellung: Es kann im Jahr zu einer Budgetkürzung kommen, die ich in E2 und E3 dargestellt habe. Das bedeutet in dem Fall, dass ab April nur noch 20 Mio. zur Verfügung stehen (anstatt 25 Mio. wie in der Ausgangssituation). Somit muss der Erwartungswert an diese 20 Mio. erneut angepasst werden, damit wir am Ende des Jahres als Summe 20 Mio. in C18 stehen haben. Mein Problem hierbei ist: Wenn die Kürzung zum Beispiel im April stattfindet, kann ich die Werte von Januar - März nicht mehr ändern. Diese müssen auf die 25 Mio. angepasst bleiben und man kann nur April - Dezember auf die 20 Mio. anpassen. Die Summe muss also 20 Mio, ergeben, aber die fehlenden 5 Mio. können nur in den zukünftigen Monaten ausgeglichen werden. Das muss natürlich variieren je nach eingetragener Kürzung und Monat. Wenn ich beim Monat eine 6 statt einer 4 eintrage, müssen die Werte von Januar - Mai auf die 25 Mio. angepasst bleiben und erst ab Juni können die fehlenden 5 Mio. ausgeglichen werden. Ich hoffe ich konnte mich einigermaßen verständlich ausdrücken.

Hat jemand vielleicht eine Idee?  Confused

Vielen Dank schon einmal und liebe Grüße!


Angehängte Dateien
.xlsx   Erwartungswert_Hilfe.xlsx (Größe: 11,43 KB / Downloads: 3)
Antworten Top
#2
Hallo,

hier einmal ein manueller Lösungsweg! Ich meine, dass das mit Formeln kaum machbar wäre und bin nur für eine VBA-Lösung!


Angehängte Dateien
.xlsx   Kopie von Erwartungswert_Hilfe.xlsx (Größe: 11,42 KB / Downloads: 3)
[Bild: attachment-190.gif]
Gruß Günter
aus der Helden-, Messe-, Musik-, Buch-, Universitäts- und Autostadt Leipzig
Antworten Top
#3
Moin

Klar ist das mit Formeln machbar.
Arbeitsblatt mit dem Namen 'Ausgangssituation (2)'
ABCDE
1Budget Version123
2Budget25.000.00020.000.00018.000.000
3ab Monat146
4Faktor0,8090614890,5907208770,478361327
5
6
7
8MonatErwartungwertVersion 1Version 2Version 3
912.500.0002.022.6542.022.6542.022.654
1022.600.0002.103.5602.103.5602.103.560
1132.900.0002.346.2782.346.2782.346.278
1242.560.0002.071.1971.512.2451.512.245
1352.540.0002.055.0161.500.4311.500.431
1462.900.0002.346.2781.713.0911.387.248
1572.600.0002.103.5601.535.8741.243.739
1682.300.0001.860.8411.358.6581.100.231
1792.600.0002.103.5601.535.8741.243.739
18102.900.0002.346.2781.713.0911.387.248
19112.200.0001.779.9351.299.5861.052.395
20122.300.0001.860.8411.358.6581.100.231
21Ergebnis30.900.00025.000.00020.000.00018.000.000

ZelleFormel
C4=C2/SUMMEWENNS(Tabelle3[Erwartungwert];Tabelle3[Monat];">="&C3)
D4=(D2-SUMMEWENNS(Tabelle3[Version 1];Tabelle3[Monat];"<"&D$3))/SUMMEWENNS(Tabelle3[Erwartungwert];Tabelle3[Monat];">="&D$3)
E4=(E2-SUMMEWENNS(Tabelle3[Version 2];Tabelle3[Monat];"<"&E$3))/SUMMEWENNS(Tabelle3[Erwartungwert];Tabelle3[Monat];">="&E$3)
C9=[@Erwartungwert]*$C$4
D9=WENN([@Monat]<D$3;[@[Version 1]];[@Erwartungwert]*D$4)
E9=WENN([@Monat]<E$3;[@[Version 2]];[@Erwartungwert]*E$4)
Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016
Diese Tabelle wurde mit Tab2Html (v2.6.2) erstellt. ©Gerd alias Bamberg
Wir sehen uns!
... Detlef

Meine Beiträge können Ironie oder Sarkasmus enthalten.

[-] Folgende(r) 1 Nutzer sagt Danke an shift-del für diesen Beitrag:
  • Vanessa1
Antworten Top
#4
Hallo shift-del,

ist ja toll, dass man das auch mit Formeln einfach und bequem erledigen kann!
Nur würden mir ein paar Erläuterungen zu Deinen Formeln fehlen: z.B. was und wo ist Tabelle3 ? Hinweis auf Matrixformeln.
[Bild: attachment-190.gif]
Gruß Günter
aus der Helden-, Messe-, Musik-, Buch-, Universitäts- und Autostadt Leipzig
Antworten Top
#5
Moin Günter

Tabelle3 ist der Name der formatierten Tabelle.
Wir sehen uns!
... Detlef

Meine Beiträge können Ironie oder Sarkasmus enthalten.

Antworten Top
#6
Hallo,
entschuldigt die verspätete Antwort. Ich konnte den Lösungsweg von shift-del in meiner Original-Tabelle umsetzen und alles hat funktioniert wie gewünscht. Vielen Dank!!
Antworten Top


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste