Dieses Forum nutzt Cookies
Dieses Forum verwendet Cookies, um deine Login-Informationen zu speichern, wenn du registriert bist, und deinen letzten Besuch, wenn du es nicht bist. Cookies sind kleine Textdokumente, die auf deinem Computer gespeichert werden. Die von diesem Forum gesetzten Cookies werden nur auf dieser Website verwendet und stellen kein Sicherheitsrisiko dar. Cookies aus diesem Forum speichern auch die spezifischen Themen, die du gelesen hast und wann du zum letzten Mal gelesen hast. Bitte bestätige, ob du diese Cookies akzeptierst oder ablehnst.

Ein Cookie wird in deinem Browser unabhängig von der Wahl gespeichert, um zu verhindern, dass dir diese Frage erneut gestellt wird. Du kannst deine Cookie-Einstellungen jederzeit über den Link in der Fußzeile ändern.

Formatbedingt Summenbildung
#1
Hallo Excel-Freunde,

das Jahr 2017 naht und ich habe wieder mal ein komplexes Problem mit der Auftragsplanung.

Also:
Ich habe feste Aufträge mit einer bestimmten Laufzeit, die sich, wenn nicht zu einem bestimmten Zeitpunkt gekündigt werden, automatisch verlängern ( mit 90 % des Auftragswertes )und zwar unbegrenzt. Die feste Laufzeit soll grün unterlegt werden, die automatische Verlängerung soll hellgrün unterlegt werden. Geben ich die neue feste Laufzeit ein, so soll der Betrag für die Laufzeit wieder grün unterlegt werden.

In Zeile 9 sollen die festen ( grün unterlegten Aufträge summiert werden.)
In Zeile 10 sollen die festen Aufträge 100 % und die automatisch verlängerten Aufträge ( 90 % ) summiert werden
In Zeile 12 sollen die restlichen Farben summiert werden.

Ich hoffe, meine Mustertabelle zeigt die Problemstellung.

Ist das machbar ?

Vielen lieben Dank für eure Hilfe, wenns machbar ist.

Wolfgang


Angehängte Dateien
.xlsx   Muster Salespipe.xlsx (Größe: 17,49 KB / Downloads: 12)
Antworten Top
#2
Wenn ich die Frage richtig verstanden habe, lässt sich das meinem Verständnis nach nur über VBA lösen. Bedingte Formatierungen können sich nur auf ihre eigene Zelle beziehen.
Ich würde es machen, indem ich im Skript die Einfärbung der Zellen vom Kündigungsdatum abhängen lasse, und die Zellenwerte in verschiedenen Arrays zusammenfasse. Das lässt sich dann auch relativ problemlos zusammenzählen.
Möchte hier aber auch keinem der Gurus etwas vorweg nehmen, vielleicht gibt es ja eine einfachere Lösung.
Antworten Top
#3
Hallo Hobbes95,

leider habe ich ü b e r h a u p t    keine Ahnung von VBA !!! :22:

LG Bonnie
Antworten Top
#4
Hallo,

welche Deiner Aufträge haben eine feste Laufzeit, woran sehe ich das?
Deine Summewenn-Formeln ergeben allesamt einen Zirkelbezug, wenn in der Zeile in K 100% steht!
Erstelle bitte ein Muster mit den Wunschergebnissen, auf mehreren Zeilen mit unterschiedlichen Vorgaben.
Das geht sicher mit Formeln, man muß nur die Bedingungen, die zu der Farbe führen, in die Formel übernehmen.
Gruß

Edgar

Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
Antworten Top
#5
Hallo Edgar,

die feste Laufzeit siehst du in den Spalten H und I die Laufzeit in Monaten siehst du in Spalte Q.
Bereits wenn die feste Laufzeit eingegeben ist -  siehe Zeile 13 Spalte T bis X - soll die automatische Verlängerung bewertet mit 90 % dargestellt werden - siehe Spalte Y - AQ.

Sobald dann nach Ablauf der festen Laufzeit eine neue feste Laufzeit eingegeben wird, in diese Falle dann 6 Monate vom 01.06. - 01.11. ( 6 Monate siehe Spalte Q ) soll, die erste Laufzeit in grün weiter stehen bleiben und die Spalten Y - AD auch in dunkelgrün mit 100 % erscheinen. Die restliche Darstellung dann wieder in hellgrün mit 90 % des Auftragswertes.

Ich hoffe, du kannst mit diesen Angaben etwas anfangen.

Liebe Grüße
Wolfgang
Antworten Top
#6
Hallöchen,,

ich nehme an, da Du keine extra Spalte für die Auftragssumme hast, dass Du in jedem Fall in Spalte T eine Zahl per Hand eintragen willst und musst. Im Prinzip kannst Du die hellgrünen Zahlen ab Spalte U so ermitteln, z.B. U9:

=WENN(MONAT(U$8)>MONAT($S$13);T9*0,9;T9)
Die Formel kannst Du dann in Zeile 8 durchziehen.

Für Zeile 9 nimmst Du
=WENN(MONAT(U$9)>MONAT($S$14);T9*0,9;T9)

Dann kopierst Du U8:U9 nach U13, und ziehst sie unten durch.

Die Teile mit MONAT(U$8)>MONAT($S$13) kannst Du dann auch für die bedingte Formatierung verwenden.
.      \\\|///      Hoffe, geholfen zu haben.
       ( ô ô )      Grüße, André aus G in T  
  ooO-(_)-Ooo    (Excel 97-2019+365)
Antworten Top
#7
Hallo Andre,

leider darf ich nicht zwei Zeilen verwenden, mit 2 Zeilen hab ich die Auftragsplanung schon vorgestellt. ist leider abgelehnt worden.

Liebe Grüße
Wolfgang
Antworten Top
#8
Hallo Wolfgang,

versteh ich jetzt nicht. Wo werden 2 Zeilen nicht beschränkt? Wie sieht das dann aus?
.      \\\|///      Hoffe, geholfen zu haben.
       ( ô ô )      Grüße, André aus G in T  
  ooO-(_)-Ooo    (Excel 97-2019+365)
Antworten Top
#9
Hallo Andre,

ich steh jetzt vollkommen auf dem Schlauch, was du meinst.

Vielleicht erklär ich am besten nochmals die Problemstellung:
1. Problem
Spalte H-I = feste Auftragslaufzeit ( dunkelgrün )
Spalte J = fester Auftragswert
Spalte Q = feste Auftragslaufzeit  --->  danach soll die autom. Verlängerung ( hellgrün ) mit 90 % aus E6 bis unendlich dargestellt werden.

- wenn feste Laufzeit vorbei ist und neue Laufzeit eingegeben wird bleibt die vorherige feste Laufzeit mit dunkelgrün stehen und die neue feste Laufzeit soll in dunkelgrün dargestellt werden.

2. Problem
Zeile 9 = kumulierter fester Auftragswert pro Monat ( dunkelgrün )
Zeile 10 = kumulierter fester Auftragswert + Auftragswert autom. Verlängerung ( dunkelgrün + hellgrün )
Zeile 11 = kumulierte Summe der Zeile 17,18,19  (  werden natürlich viel mehr )

So, ich hoffe, dass ich mich einigermaßen klar ausgedrückt habe und du mir evtl. die Problemstellung elegant lösen kannst.
Was ich mit den zwei Zeilen gemeint habe, ich hatte schon mal eine Version gemacht, in der ich in der ersten Zeile nur die 100 %dargestellt habe und in der 2. Zeile die automatischen Verlängerungen mit 90 % pro Auftrag.

Das wurde aber abgelehnt.

Im voraus vielen Dank für deine Hilfe.

Wolfgang
Antworten Top
#10
Hi Wolfgang,

ich gehe mal einzeln auf deine Bedingungen ein:


Zitat:1. Problem
Spalte H-I = feste Auftragslaufzeit ( dunkelgrün )
Spalte J = fester Auftragswert
Spalte Q = feste Auftragslaufzeit  --->  danach soll die autom. Verlängerung ( hellgrün ) mit 90 % aus E6 bis unendlich dargestellt werden.

Laufzeitbeginn = manuelle Eingabe. Laufzeitende habe ich mit der Formel
Code:
=EDATUM(H13;Q13)-1
berechnet. Ausgehend vom Laufzeitbeginn wird hier die in Q13 angegebene Laufzeit an Monaten addiert und ein Tag abgezogen.

Momentan sehen deine Zeilen 13 und 14 so aus:

Salespipe ( Info )

HIJKLMNOPQRSTUVWXYZAAABACADAE
8Monat von (TT.MM.JJ)Monat bis (TT.MM.JJ)AngebotspreisStatus12345LaufzeitKündigungsfrist in WochenKündigungsterminJan. 16Feb. 16Mrz. 16Apr. 16Mai. 16Jun. 16Jul. 16Aug. 16Sep. 16Okt. 16Nov. 16Dez. 16
1301.01.201630.06.2016400,00100% x6123.06.16400,00400,00400,00400,00400,00400,00360,00360,00360,00360,00360,00360,00
1401.09.201630.11.2016250,00100% x3402.11.16250,00250,00250,00225,00
Formeln der Tabelle
ZelleFormel
T13=WENN(T$8<$H13;"";WENN((T$8>=DATUM(JAHR($H13);MONAT($H13);))*(T$8<=$I13);$J13*$K13;$J13*0,9))
Bedingte Formatierungen der Tabelle
ZelleNr.: / BedingungFormat
T131. / Formel ist =T13=$J13Abc

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Zitat:- wenn feste Laufzeit vorbei ist und neue Laufzeit eingegeben wird bleibt die vorherige feste Laufzeit mit dunkelgrün stehen und die neue feste Laufzeit soll in dunkelgrün dargestellt werden.

Das ist m.E ausschließlich mit VBA darstellbar. Bei einer normalen bedingten Formatierung würden sich bei Laufzeitänderungen auch die Farben wieder ändern. Da ich bei VBA absolut nicht helfen kann (wir haben hier aber einige Spezialisten), habe ich die weiteren Zeilen des zweiten Problems noch nicht beachtet. Hier wäre eventuell nochmals ein Gespräch zwischen dir und deinem Auftraggeber vonnöten, ob er nicht den bisherigen Vorschlägen folgend den Tabellenaufbau überdenkt.
Gruß Günter
Jeder Fehler erscheint unglaublich dumm, wenn andere ihn begehen.
angebl. von Georg Christoph Lichtenberg (1742-1799)
[-] Folgende(r) 1 Nutzer sagt Danke an WillWissen für diesen Beitrag:
  • bonnie
Antworten Top


Gehe zu:


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