Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1216to1220
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Year-to-date

Year-to-date
Jana
Hallo Forumsteilnehmer,
ich komme mit einer Formel nicht weiter, um das year-to-date zu berechnen und baue auf Eure Hilfe.
Anbei die Excel-Datei zum besseren Verständnis.
https://www.herber.de/bbs/user/75344.xlsx
Für jeden Monat fallen in einem Bereich Kosten an (Ist-Kosten). Fortlaufend soll das year-to-date, d.h. die Summe der Ist-Kosten zum aktuellen Monat berechnet werden.
Der aktuelle Monat wird durch =Monat(Jetzt()) am Ende der Tabelle dargestellt. Darafufhin sollte ein Vergleich angestellt werden und z.B. wenn der aktuelle Monat der Juni ist, alle vorherigen Ist-Kosten von Januar bis Juni addiert werden.
Das Problem ist, dass die einzelnen Spalten nicht direkt nebeneinander stehen. Dieses Format muss leider aber auch eingehalten werden.
Für Eure schnelle Hilfe wäre ich sehr dankbar.
Viele Grüße
Jana

9
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Benutzer
Anzeige
Bedingte Summe mit SUMMENPRODUKT
19.06.2011 12:55:17
Erich
Hi Jana,
das sollte passen:
 ALAM
1Year to date - PlanYear to date - Ist
2  
31230
43042

Formeln der Tabelle
ZelleFormel
AL3=SUMMENPRODUKT($B3:$AJ3*(REST(SPALTE($B3:$AJ3); 3)=2)*(SPALTE($B3:$AJ3)<4*MONAT(HEUTE())-4))
AM3=SUMMENPRODUKT($B3:$AJ3*(REST(SPALTE($B3:$AJ3); 3)=0)*(SPALTE($B3:$AJ3)<4*MONAT(HEUTE())-4))
AL4=SUMMENPRODUKT($B4:$AJ4*(REST(SPALTE($B4:$AJ4); 3)=2)*(SPALTE($B4:$AJ4)<4*MONAT(HEUTE())-4))
AM4=SUMMENPRODUKT($B4:$AJ4*(REST(SPALTE($B4:$AJ4); 3)=0)*(SPALTE($B4:$AJ4)<4*MONAT(HEUTE())-4))

Die beiden Formeln kannst du von Zeile 3 nach unten kopieren.
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort
Anzeige
AW: Bedingte Summe mit SUMMENPRODUKT
19.06.2011 13:29:37
Jana
Hallo Erich,
vielen Dank für deine schnelle Antwort. Für das angegebene Workbook funktioniert die Formel. Für meine andere kompliziertere Datei kann ich sie leider nicht übertragen.
Könntest du mir den Aufbau der Formel erklären? Was bedeutet die Abfrage kleiner 4? Gilt die Formel immer entsprechend dem aktuellen Monat aktualisiert? Wird z.B. im Dezember mit der gleichen Formel das richtige Ergebnis berechnet?
Vielen Dank vorab
Jana
Sorry, war falsch!
19.06.2011 14:06:22
Erich
Hi Jana,
die Abfrage war nicht "kleiner 4", sondern "kleiner 4*aktMonat - 4".
Genau an der Stelle war aber auch mein Fehler: Nicht alle 4, sondern alle 3 Spalten kommt ein neuer Monat,
also muss da 3*aktMonat statt 4*aktMonat rein.
Hier die neuen Formeln:
 ALAM
1Year to date - PlanYear to date - Ist
2  
31230
43042

Formeln der Tabelle
ZelleFormel
AL3=SUMMENPRODUKT($B3:$AJ3*(REST(SPALTE($B3:$AJ3); 3)=2)*(SPALTE($B3:$AJ3)<3*MONAT(HEUTE())+1))
AM3=SUMMENPRODUKT($B3:$AJ3*(REST(SPALTE($B3:$AJ3); 3)=0)*(SPALTE($B3:$AJ3)<3*MONAT(HEUTE())+1))
AL4=SUMMENPRODUKT($B4:$AJ4*(REST(SPALTE($B4:$AJ4); 3)=2)*(SPALTE($B4:$AJ4)<3*MONAT(HEUTE())+1))
AM4=SUMMENPRODUKT($B4:$AJ4*(REST(SPALTE($B4:$AJ4); 3)=0)*(SPALTE($B4:$AJ4)<3*MONAT(HEUTE())+1))

Addiert werden jetzt alle Spalten mit Spaltennr < 3*MONAT(HEUTE())+1.
Für Juni ist das 3*6+1 = 19, also links von Spalte S.
Für Septermber wäre es das 3*9+1 = 28, also links von Spalte AB.
Das gilt also für alle Monate gleichermaßen.
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort
Anzeige
AW: Sorry, war falsch!
19.06.2011 17:52:28
Jana
Hi Erich,
alles klar. Aber wenn ich Spalten vorne einfüge, dann stimmt die Formel nicht mehr. Siehe angehängte Datei:
https://www.herber.de/bbs/user/75355.xlsx
Was bedeutet denn REST(())=2 oder =0?
Wäre nett, wenn du mir noch einmal weiterhelfen könntest.
Viele Grüße
Jana
neue Struktur, neue Formeln
19.06.2011 18:55:55
Erich
Hi Jana,
REST(())=2 oder =0 diente dazu, nur die Plan- bzw. die Ist-Spalten auszuwählen.
Ich schicke dir hier eine Beispielmappe mit den neuen Formeln und den Zeilen 4 bis 6,
die unnötig sind, aber die Geschichte erklären.
Hier erst einmal die neuen Formeln:
 CACB
166
277,777755,5555
34230

Formeln der Tabelle
ZelleFormel
CA1=MONAT(HEUTE())
CB1=MONAT(JETZT())
CA2=SUMMENPRODUKT($G2:$BY2*(REST(SPALTE($G2:$BY2); 6)=1)*(SPALTE($G2:$BY2)<6*MONAT(HEUTE())+6))
CB2=SUMMENPRODUKT($G2:$BY2*(REST(SPALTE($G2:$BY2); 6)=5)*(SPALTE($G2:$BY2)<6*MONAT(HEUTE())+6))
CA3=SUMMENPRODUKT($G3:$BY3*(REST(SPALTE($G3:$BY3); 6)=1)*(SPALTE($G3:$BY3)<6*MONAT(HEUTE())+6))
CB3=SUMMENPRODUKT($G3:$BY3*(REST(SPALTE($G3:$BY3); 6)=5)*(SPALTE($G3:$BY3)<6*MONAT(HEUTE())+6))

Statt =MONAT(JETZT()) brauchst du nur =MONAT(HEUTE()), das JETZT() liefert doch nur die Uhrzeit mit.
Und hier die Mappe: https://www.herber.de/bbs/user/75358.xlsx
Ein paar Beispielwerte habe ich geändert, damit man mehr sieht.
Wenn Du Fragen dazu hast - bitte melden!
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort
Anzeige
AW: neue Struktur, neue Formeln
19.06.2011 20:05:52
Jana
Hi Erich,
bis jetzt klappt alles ganz gut. Dein Excel-File hat mir fürs Verständnis viel geholfen.
Falls noch etwas schief geht, melde ich mich gerne :)
Viele Grüße
Jana
AW: Sorry, war falsch!
21.06.2011 23:32:02
jana
Hey Erich,
vielleicht hast du noch einmal Zeit mir auf die Sprünge zu helfen. So ganz habe ich die Formel noch nicht verstanden, insbesondere, wenn es darum geht sie anzupassen.
https://www.herber.de/bbs/user/75396.xlsx
Ich habe mal im folgenden Excel-Workbook verschiedene Fälle konzipiert. Könntest du mir jeweils mit der Formel weiterhelfen?
Wenn ich es anpasse, passt es immer nicht genau...
Vielen Dank
Jana
Anzeige
teils ok, teils nicht
22.06.2011 09:49:25
Erich
Hi Jana,
bei den ersten beiden Tabellen klappt die Formalanpassung nicht, weil die Struktur nicht stimmig ist:
Der Januar verbraucht eine Spalte mehr als die anderen Monate.
Die letzten beiden Tabellen sind hier erledigt:

Die Datei https://www.herber.de/bbs/user/75403.xlsx wurde aus Datenschutzgründen gelöscht


Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort
AW: Bedingte Summe mit SUMMENPRODUKT
19.06.2011 14:07:43
Jana
Nachtrag:
Die Zahlen sind natürlich nicht für jeden Monat identisch.
Aus vereinfachenden Gründen habe ich hier immer 5 und 7 eingetragen, so dass viele identische Matrizen entstanden sind.
Leztlich ändern sich die Werte jeden Monat und es soll immer vom jetztigen Monat aus die kumulierte Summe der Vormonate berechnet werden.
Hoffe jemand nimmt sich noch meines Problemes an.
Gruß Jana
Anzeige

35 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige