Office: (Office 2010) suche Formelvereinfachung - in Summenprodukt zwei Bedingungen zusammenfassen

Servus Forum,

mein Wissen über Formeln ist relativ begrenzt. Zwar habe ich alle gewünschten Funktionalitäten erreichen können, aber vermutlich unnötig kompliziert. Vielleicht hat ja jemand eine Idee wie man noch was vereinfachen könnte. Ich denke da speziell an das im Titel erwähnte (folgt später).

Erstmal zu Erklärung:
Unser "Stoppelhopser" (16 Jahre alt) bekommt sein Taschengeld und andere Zahlungen monatlich auf sein eigenes Girokonto, muss davon aber auch monatliche Zahlungen (Daueraufträge/Abbuchungen) leisten. Zusätzlich redet er immer davon, sich monatlich ein wenig Geld beiseite zu legen, um bestimmte Ziele erreichen zu können. Natürlich hat er absolut keinen Überblick, und deshalb darf ich mittels Excel ein wenig Hilfestellung geben, damit er das Haushalten lernt.
Von Excel hat er leider absolut keinen Plan, aber auch kein Interesse sich damit zu beschäftigen, also muss es ganz einfach und offensichtlich gehalten sein.
Ein komplettes Haushaltsbuch wäre viel zuviel des Guten, es reicht eine Auflistung der regelmäßigen monatlichen Ein- und Ausgänge, sowie eine Übersicht, wieviel er wann für welchen Zweck angespart hat (damit er weiß wieviel er auf dem Konto lassen muss).

Aufbau Blatt "Übersicht":
- In den Spalten B-D werden die regelmäßigen Ein- und Ausgänge aufgelistet > kann so bleiben.
- In den Spalten F-K (obere Tabelle) kann er eintragen für was er sparen möchte/muss, und Monat bzw. Jahr auswählen um so zu sehen, wieviel er für welchen Zweck angespart hat, bzw. wieviel insgesamt immer auf dem Konto stehen bleiben muss. (Die Spalten I & J sind ohne Bedeutung)
- In den Spalten F-K (untere Tabelle) muss er dann in einer fortlaufenden Liste eintragen, wieviel er in welchem Monat für welchen Verwendungszweck zurücklegt oder ausgegeben hat. (als Verwendungszweck können nur Einträge aus der oberen Liste gewählt werden) > kann eigentlich auch alles so bleiben

Aufbau Blatt "Hilfsdaten":
- Hier wird pro Verwendungszweck das Ansparguthaben für jeden einzelnen Monat errechnet, um es im Blatt "Übersicht" darstellen zu können.
- Ist das Guthaben des Vormonats im Plus, so wird es addiert, ist es im Minus wird es ignoriert > hat er für einen Zweck mehr Geld ausgegeben als er angespart hatte, muss er das nicht ausgleichen, sondern startet wieder bei Null.
- In den Formeln ist auch "Indirekt" enthalten, weil ich auf eine vorformatierte Tabelle referenziere, und sonst den Zellbezug nicht festschreiben kann um die Formel seitlich ziehen zu können (Autoausfüllen).
- Dieses Blatt wird in der Originaldatei ausgeblendet.

Meine Fragen:
1. Um exakte und vergleichbare Eingaben zu gewährleisten, habe ich die Eingabe von Monat und Jahr im Blatt "Übersicht" mittels Datenüberprüfung auf entsprechende Listen begrenzt. Demzufolge habe ich als Jahr "2015" und als Monat "Mai" dort stehen.
Diese Werte muss ich per Formel aber wieder in ein Datum (bzw. den Datwert) umwandeln, damit ich im Blatt "Hilfsdaten" nur die Ein- und Ausgänge des jeweiligen Monats berücksichtige. Dadurch wird die Formel lang und unübersichtlich.
Kann man das vielleicht einfacher (schlauer) lösen?

2. Die Formeln im Blatt "Hilfsdaten" prüfen (unter anderem) ob ein Datwert größer/gleich als der 1. des jeweiligen Monats ist, und ob er kleiner als der 1. des nächsten Monats ist. Kann man diese beiden Bedingungen innerhalb "Summenprodukt" eventuell zusammenfassen? Quasi als "Und", mir fällt nur nichts ein wie.

Ich hoffe Euch hat der lange Text nicht abgeschreckt, und Sinn und Zweck, sowie Problemstellung sind verständlich.

Den Anhang Finanzübersicht - Kopie.xlsx betrachten

 

Hallo Achim,

Dein Problem kann ganz einfach gelöst werden.
Lege auf dem Blatt eine Liste an mit den Datumswerten für die Monate (1.1.15;1.2.15...). Diese formatierst Du in MMMM und setzt die Gültigkeit auf diese Zellen. Die Spalte in der Liste (in Deinem Bsp. Spalte G) formatierst Du ebenso.
Somit steht in der Auswahl nur der Monat und in der Liste auch, jedoch im Hintergrund steht es für XL auswertbarer dar. Somit benötigst Du keine DATWERT(e) und kannst SUMMENPRODUKT() normal laufen lassen.

 

Hallo René,

lieben Dank für Deine Antwort.
So in der Art war auch mein erster Ansatz, habe aber da die Anzeige in der Datenüberprüfung nicht ordentlich hinbekommen.
Das klappt aber nun.
So kann ich mir nun eine Spalte bzw. pro Zeile ein DropDown sparen, und die Formeln sind auch kürzer und übersichtlicher.

Code:

aus
=WENN(H3<>"";WENN(WVERWEIS(DATUM($F$2;MONAT("1."&$G$2);1);Hilfsdaten!$2:$13;ZEILE(K3)-1;FALSCH)>0;WVERWEIS(DATUM($F$2;MONAT("1."&$G$2);1);Hilfsdaten!$2:$13;ZEILE(K3)-1;FALSCH);0);"")
wurde
=WENN(G3<>"";WENN(WVERWEIS($F$2;Hilfsdaten!$C$2:$BR$13;ZEILE(J3)-1;FALSCH)>0;WVERWEIS($F$2;Hilfsdaten!$C$2:$BR$13;ZEILE(J3)-1;FALSCH);0);"")

und aus
=SUMMENPRODUKT((INDIREKT("Tabelle1[für]")=$B3)*((DATWERT("01."&MONAT("1."&INDIREKT("Tabelle1[Monat]"))&"."&JAHR("01.01."&INDIREKT("Tabelle1[Jahr]"))))<E$2)*((DATWERT("01."&MONAT("1."&INDIREKT("Tabelle1[Monat]"))&"."&JAHR("01.01."&INDIREKT("Tabelle1[Jahr]"))))>=D$2)*(INDIREKT("Tabelle1[Ein]")-INDIREKT("Tabelle1[Aus]")))+WENN(C3>0;C3;0)
wurde
=SUMMENPRODUKT((INDIREKT("Tabelle1[für]")=$B3)*(INDIREKT("Tabelle1[Monat]")<E$2)*(INDIREKT("Tabelle1[Monat]")>=D$2)*(INDIREKT("Tabelle1[Ein]")-INDIREKT("Tabelle1[Aus]")))+WENN(C3>0;C3;0)

Besteht in "Summenprodukt" jetzt noch die Möglichkeit, die folgenden beiden Bedingungen zusammen zu fassen?

Code:

(INDIREKT("Tabelle1[Monat]")<E$2)*(INDIREKT("Tabelle1[Monat]")>=D$2)

Den Anhang Finanzübersicht - Kopie2.xlsx betrachten

 

Hallo René,

hab die zweite Sache auch noch hinbekommen.
Mit dem jetzigen Aufbau kann ich ja direkt auf Gleichheit prüfen und brauche garnicht mehr beide Bedingungen:

Code:

=SUMMENPRODUKT((INDIREKT("Tabelle1[für]")=$B3)*(INDIREKT("Tabelle1[Monat]")=D$2)*(INDIREKT("Tabelle1[Ein]")-INDIREKT("Tabelle1[Aus]")))+WENN(C3>0;C3;0)

 

Hallo Achim,

bei SUMMENPRODUKT() kannst Du gleich auf die Spalten referenzieren. Dazu musst Du nicht mit INDIRKT() arbeiten, denn in dem Bereich dürfte ja nur die Kombinationen innerhalb der Tabelle auftauchen. Allerdings müsste es wohl eine Abfrage dann mehr geben, die den #WERT Fehler abfängt.
Ich schaue heute abend nochmal in Deine Tabelle...

 

Hallo René,

klar kann ich direkt auf die Spalten referenzieren, aber wenn ich die Summenproduktformel dann im Blatt Übersicht von Spalte C bis Spalte BR nach rechts "ziehe" (Autoausfüllen), dann wandert die Referenz ja auch nach rechts. Und in den vorformatierten Tabellen kann man nicht wie gewohnt mit dem Dollarzeichen einen Bezug fixieren. Oder täusche ich mich da?

Bzw. meinst Du mit "gleich auf die Spalten referenzieren" die Spaltenbuchstaben? Dann muss ich aber wieder einen Zeilenbereich angegeben, da darüber ja eine andere Tabelle ist. Und dieser Bereich müsste sich nach unten automatisch erweitern sobald neue Einträge hinzukommen.
Die Lösung über "Indirekt" ist das einzige, was ich dazu im Netz gefunden habe.

Hier jetzt nochmal der aktuelle Zustand der Datei:
Den Anhang Finanzübersicht - Kopie3.xlsx betrachten

 

Hallo Achim,

Arbeitsblatt mit dem Namen 'Hilfsdaten'
	B	C	D
1
2	angespart für	Mai 2015	Juni 2015
3	irgendwas	2,50 €	5,00 €
4	was anderes	0,00 €	0,00 €
5		0,00 €	0,00 €

Zelle	Formel
C3	=SUMMENPRODUKT((Übersicht!$F$17:$F$20<=C$2)*(Übersicht!$G$17:$G$20=$B3)*(Übersicht!$H$17:$H$20))-SUMMENPRODUKT((Übersicht!$F$17:$F$20<=C$2)*(Übersicht!$G$17:$G$20=$B3)*(Übersicht!$I$17:$I$20))

Du musst die Formel einmal händisch eingeben, um "TABELLE1[ein]" u.a. zu vermeiden. Dann kannst Du sie "ziehen".
Und wenn ein neuer Eintrag in der Tabelle erfolgt, dann erweitert sich die Formel automatisch (wenn in Optionen eingestellt).

 

Hallo René,

mit Deiner Formel klappt alles wie gewünscht.
Und wenn ich Sie (genauso wie vorher auch bei meiner Version) kürze, und für die Subtraktion nicht ein extra Summenprodukt bilde, ist Deine Formal sogar nochmal kürzer als meine. Vor allem aber habe ich INDIREKT() umgangen.
Ganz lieben Dank dafür.

Code:

=SUMMENPRODUKT((Übersicht!$F$17:$F$20=C$2)*(Übersicht!$G$17:$G$20=$B3)*((Übersicht!$H$17:$H$20)-(Übersicht!$I$17:$I$20)))

Den Anhang Finanzübersicht - Kopie4.xlsx betrachten