9
EXCEL Addieren von Werten in Matrix - Suche nach Inhalten und diese dann addieren
Hallo Leute
ich habe folgendes vor. In einer Tabelle stehen meine Ein- und Ausgaben, die ich das Jahr über habe. Siehe Bild:
Die untere Tabelle zeigt an, wie viel, ich in welchem Monat ausgegeben habe. Um nicht das immer händisch Makieren zu müssen würde ich das gern über eine Funktion oder einen Scribt lösen....
1. Ich würde gerne die ganzen Einnahmen/ Monat in die Tabelle darunter eintragen. Dies soll für Januar, Februar ... Dezember gelten. Ich denke man muss hier mit einer Suchfunktion arbeiten und dann die Einnahmen bzw. Ausgaben/€ addieren. Habe es über den SVERWEIS probiert aber das war glaube nicht das Richtige.
2. Ich würde gerne, und ich glaube das ist dann nur im Punkt 1.die Funktion abgeändert, die Tabelle nach dem Inhalt "Tanken" überprüfen und dann die Beträge für den Monat addieren.
Hoffe ich habe es verständlich geschildert. Ich habe ein wenig Erfahrung in VBA aber ich habe nicht wirklich eine Ahnung, wie ich das Problem lösen könnte.
Grüße und schonmal Danke,
Jens
ich habe folgendes vor. In einer Tabelle stehen meine Ein- und Ausgaben, die ich das Jahr über habe. Siehe Bild:
Die untere Tabelle zeigt an, wie viel, ich in welchem Monat ausgegeben habe. Um nicht das immer händisch Makieren zu müssen würde ich das gern über eine Funktion oder einen Scribt lösen....
1. Ich würde gerne die ganzen Einnahmen/ Monat in die Tabelle darunter eintragen. Dies soll für Januar, Februar ... Dezember gelten. Ich denke man muss hier mit einer Suchfunktion arbeiten und dann die Einnahmen bzw. Ausgaben/€ addieren. Habe es über den SVERWEIS probiert aber das war glaube nicht das Richtige.
2. Ich würde gerne, und ich glaube das ist dann nur im Punkt 1.die Funktion abgeändert, die Tabelle nach dem Inhalt "Tanken" überprüfen und dann die Beträge für den Monat addieren.
Hoffe ich habe es verständlich geschildert. Ich habe ein wenig Erfahrung in VBA aber ich habe nicht wirklich eine Ahnung, wie ich das Problem lösen könnte.
Grüße und schonmal Danke,
Jens
Share on Facebook
Share on Twitter
Share on Reddit
Share on LinkedinPlease also mark the comments that contributed to the solution of the article
Content-Key: 181384
Url: https://administrator.de/contentid/181384
Printed on: April 26, 2024 at 21:04 o'clock
9 Comments
Latest comment
Hallo jens-3456!
Das könnte so gehen: Zunächst in A15 bis A26 die Monatszahlen 1 bis 12 eintragen, die Summen aus Zeile 10 in Zeile 11 verschieben und dann folgende "Array"-Formeln verwenden:
B15:
C15:
E15:
Die Eingabe dieser Formeln (auch nach jeder Bearbeitung) muss mit der Tastenkombination
Die Formeln lassen sich dann nach unten kopieren. Um weitere Zeilen in die Einnahmen-Ausgaben-Übersicht aufnehmen zu können, wird auch die Zeile 10 in die Berechnung mit einbezogen - so können neue Werte hinzugefügt werden, indem vor der Zeile 10 eine neue Zeile eingefügt wird.
Grüße
bastla
Das könnte so gehen: Zunächst in A15 bis A26 die Monatszahlen 1 bis 12 eintragen, die Summen aus Zeile 10 in Zeile 11 verschieben und dann folgende "Array"-Formeln verwenden:
B15:
=SUMME(WENN(MONAT($B$2:$B$10)=$A15;$C$2:$C$10;0))C15:
=SUMME(WENN(MONAT($E$2:$E$10)=$A15;$F$2:$F$10;0))E15:
=SUMME(WENN(MONAT($E$2:$E$10)=$A15;$F$2:$F$10;0)*($G$2:$G$10="Tanken"))Die Eingabe dieser Formeln (auch nach jeder Bearbeitung) muss mit der Tastenkombination
Strg + Umsch + Enter erfolgen - in der Bearbeitungszeile wird die Formel dann zwischen geschwungenen Klammern angezeigt.Die Formeln lassen sich dann nach unten kopieren. Um weitere Zeilen in die Einnahmen-Ausgaben-Übersicht aufnehmen zu können, wird auch die Zeile 10 in die Berechnung mit einbezogen - so können neue Werte hinzugefügt werden, indem vor der Zeile 10 eine neue Zeile eingefügt wird.
Grüße
bastla
Moin Jens-3456,
ergänzend zu bastlas funktionierender Lösung noch die Anmerkungen
Die Formeln für SUMMENPRODUKT (die wie üblich etwas von der Syntax der Excel-Hilfe abweichen) mal als Beispiel:
Anders als bastla habe ich die Texte "JAN", "FEB", "MRZ"..."DEZ" als nu' ma' leider gegeben hingenommen.
Damit ich einen Text "JAN" vergleichen kann mit einem Datumswert "02. Jan 12" --> verwende ich die Funktion TEXT(datumswert; "MMM"), dann passt dat.
Die obigen Formeln liefern übrigens, wenn die Matrizen mit Semikola aneinandergereiht werden wie laut Excel-Hilfe
Deshalb statt Verkettung die Multiplikation mit "*"
Alternative wäre die Verkettung mit ";" plus zusätzlichem Voranstellen des ebenfalls undokumentierten "--".
Falls nur du allein diese Excel-Tabelle bearbeitest und nicht durch die Gegend mailst und du ein Excel 2007 oder höher hast--> Nimm SUMMEWENNS()
Die funktioniert wie im obigen Link beschrieben.
Grüße
Biber
ergänzend zu bastlas funktionierender Lösung noch die Anmerkungen
- matrixorientiert, aber ohne Eingabe mit
Strg + Umsch + Enterginge es mit SUMMENPRODUKT in allen Excelversionen - und in den neueren Versionen ab Excel 2007 (laut M$ auch schon mit 2003?) elegant mit SUMMEWENNS()
Die Formeln für SUMMENPRODUKT (die wie üblich etwas von der Syntax der Excel-Hilfe abweichen) mal als Beispiel:
B15 =SUMMENPRODUKT((TEXT($B$2:$B$10;"MMM")=$A15)*$C$2:$C$10)
C15 =SUMMENPRODUKT((TEXT($E$2:$E$10;"MMM")=$A15)*$F$2:$F$10)
E15 =SUMMENPRODUKT((TEXT($E$2:$E$10;"MMM")=$A15)*$F$2:$F$10*($G$2:$G$10="Tanken"))
Damit ich einen Text "JAN" vergleichen kann mit einem Datumswert "02. Jan 12" --> verwende ich die Funktion TEXT(datumswert; "MMM"), dann passt dat.
Die obigen Formeln liefern übrigens, wenn die Matrizen mit Semikola aneinandergereiht werden wie laut Excel-Hilfe
SUMMENPRODUKT( Matrix1; Matrix2;..MatrixN) immer 0.Deshalb statt Verkettung die Multiplikation mit "*"
Alternative wäre die Verkettung mit ";" plus zusätzlichem Voranstellen des ebenfalls undokumentierten "--".
E15 =SUMMENPRODUKT(--(TEXT($E$2:$E$10;"MMM")=$A15);$F$2:$F$10;--($G$2:$G$10="Tanken"))
Falls nur du allein diese Excel-Tabelle bearbeitest und nicht durch die Gegend mailst und du ein Excel 2007 oder höher hast--> Nimm SUMMEWENNS()
Die funktioniert wie im obigen Link beschrieben.
Grüße
Biber
[OT] @Biber
Ich hätte ja schon fast selbst
Wie sieht übrigens Deine Lösung unter Verwendung von
Grüße
bastla
[/OT]
Ich hätte ja schon fast selbst
SUMMENPRODUKT() erwähnt, war mir aber ziemlich sicher, dass Du noch vorbeischauen würdest ... Wie sieht übrigens Deine Lösung unter Verwendung von
SUMMEWENNS() aus? Ich hatte da nicht wirklich den richtigen Zugang gefunden und deshalb die Array-Formel verwendet ...Grüße
bastla
[/OT]
Moin bastla,
eine Lösung ein Ansatz mit der SUMMEWENNS() könnte beispielsweise so aussehen:
was zugegebenermaßen auch nicht viel weniger kryptisch anmutet als die Formeln oben.
Deshalb würde ich ohnehin -egal ob mit Arrayformeln oder SUMMENPRODUKT() oder mit SUMMEWENNS() mit "benamsten Bereichen" arbeiten.
Dann sieht das etwas lesbarer aus.
Der (relative) Charme von SUMMEWENNS() ist bei der Erweiterung der Formel in Zelle C15 hin nach E15 zu erkennen.
Grüße
Biber
P.S. Ist keine Fertiglösung, da die runterkopierten Formeln zumindest im Monat Dezember angepasst werden müssen, da hier kein Folgemonat in der Liste $A15:$A26 auftaucht.
Hier kann dann aber einfach das jeweils zweite Kriteriumspärchen aus der Formel gelöscht werden.
B15 =SUMMEWENNS($C$2:$C$10;$B$2:$B$10;">=01. "& $A15; $B$2:$B$10;"<01. "& $A16)
C15 =SUMMEWENNS($F$2:$F$10;$E$2:$E$10;">=01. "& $A15; $E$2:$E$10;"<01. "& $A16)
E15 =SUMMEWENNS($F$2:$F$10;$E$2:$E$10;">=01. "& $A15; $E$2:$E$10;"<01. "& $A16;$G$2:$G$10;"Tanken")
Deshalb würde ich ohnehin -egal ob mit Arrayformeln oder SUMMENPRODUKT() oder mit SUMMEWENNS() mit "benamsten Bereichen" arbeiten.
Dann sieht das etwas lesbarer aus.
-- Bereiche benennen -hart oder dynamisch mit BEREICH.VERSCHIEBEN()
-- Tabellenblatt heisst "AbXls2007", weil wegen...
DatumRaus =AbXls2007!$E$2:$E$10
DatumRein =AbXls2007!$B$2:$B$10
KohleRaus =AbXls2007!$F$2:$F$10
KohleRein =AbXls2007!$C$2:$C$10
WegenWattDenn =AbXls2007!$G$2:$G$10
B15 =SUMMEWENNS(KohleRein;DatumRein;">=01. "& $A15; DatumRein;"<01. "& $A16)
C15 =SUMMEWENNS(KohleRaus;DatumRaus;">=01. "& $A15; DatumRaus;"<01. "& $A16)
E15 =SUMMEWENNS(KohleRaus;DatumRaus;">=01. "& $A15; DatumRaus;"<01. "& $A16;WegenWattDenn;"Tanken")
- Der zu summierende Bereich "KohleRaus" muss nur einmal als erstes Argument angegeben werden, egal ob 1 oder 3 oder 7 Kriterien folgen.
- das Hinzufügen eines weiteren Kriteriums wie von C15 nach E15 macht kein Problem -> es bleibt (den Umständen entsprechend) lesbar und wartbar
Grüße
Biber
P.S. Ist keine Fertiglösung, da die runterkopierten Formeln zumindest im Monat Dezember angepasst werden müssen, da hier kein Folgemonat in der Liste $A15:$A26 auftaucht.
Hier kann dann aber einfach das jeweils zweite Kriteriumspärchen aus der Formel gelöscht werden.
Hallo Biber!
Ah ja, jetzt weiß ich wieder, warum ich schon
Um die Formel unverändert auch für den Dezember verwenden zu können, sollte das etwa so gehen:
- es wird hier die Ermittlung des letzten Tages eines Monats als 0. Tag des Folgemonats vorgenommen ...
Grüße
bastla
Ah ja, jetzt weiß ich wieder, warum ich schon
SUMMEWENN() nie so wirklich mochte ... Um die Formel unverändert auch für den Dezember verwenden zu können, sollte das etwa so gehen:
=SUMMEWENNS($C$2:$C$10;$B$2:$B$10;">=01. "&$A15;$B$2:$B$10;"<="&DATUM(JAHR(HEUTE());$A15+1;0))
Grüße
bastla
[OT]
Moin bastla,
Einfach den 31.12.2012 umdefinieren als den 0.1.2013 -> schon klappt alles.
Bin ich froh, das dieses Jahr nur noch bis zum 23.12. geht... *fg
Grüße zurück
Biber
[/OT]
Moin bastla,
Zitat von @bastla:
- es wird hier die Ermittlung des letzten Tages eines Monats als 0. Tag des Folgemonats vorgenommen ...
Genau, treffender hätte ich auch nicht beschreiben können, wie man/frau mit dieser Redmonder PraktikantInnenlogik umspringen muss, um irgendwas zum Fliegen zu bringen.. - es wird hier die Ermittlung des letzten Tages eines Monats als 0. Tag des Folgemonats vorgenommen ...
Einfach den 31.12.2012 umdefinieren als den 0.1.2013 -> schon klappt alles.
Bin ich froh, das dieses Jahr nur noch bis zum 23.12. geht... *fg
Grüße zurück
Biber
[/OT]
Vielen lieben Dank =) hat mir echt weiter geholfen. Jetzt geht es nur noch darum, diese Funktion (SUMMENPRODUKT() und Dergleichen) zu verstehen.
Moin jens-3456,
das Verstehen-Wollen von abgedrehten Spezialfunktionen ist ja auch durchaus lobenswert.
Aber Und ergänzend dazu ist es manchmal nützlich, zwischendurch mal zwei Schritt zurückzutreten und das Konzept/den Aufbau nochmals aus etwas selbstkritischer Distanz anzuschauen.
Oben in deinem bisherigen Tabellenblatt ist der Aufbau momentan:
--> Zwei Tabellen untereinander
--> davon die erste Einnahmen/Ausgabentabelle variabel lang und mit abendfüllenden Details
--> die zweite Extrakt/Ergebnistabelle immer von konstanter Länge (Überschrift + genau 12 Monatszeilen + ggf Summenzeilen) und mit den interessanteren Infos
--> und die zweite interessante Tabelle wird dir ca. im April aus dem Gesichtsfeld wandern, es sei denn, du kaufst dir einen 72x192-Zoll-Monitor (sind im Moment noch ziemlich teuer!).
Ordne das Layout umgekehrt an, dann hast du auch viel weniger Stress mit den Formenl & dem Nach-unten-Kopieren und irgendwo eine Zeile einfügen etc.
Grüße
Biber
[Edit & P.S]
@jens-3456
Ergänzend zu den vortrefflichen Erklärungen bastlas nochmals der Hinweis auf die "benannten Bereiche".
Dann wird (s.o.) aus
... das etwas verständlichere ...
Grüße und Dank an bastla
[Edit & P.S]
das Verstehen-Wollen von abgedrehten Spezialfunktionen ist ja auch durchaus lobenswert.
Oben in deinem bisherigen Tabellenblatt ist der Aufbau momentan:
--> Zwei Tabellen untereinander
--> davon die erste Einnahmen/Ausgabentabelle variabel lang und mit abendfüllenden Details
--> die zweite Extrakt/Ergebnistabelle immer von konstanter Länge (Überschrift + genau 12 Monatszeilen + ggf Summenzeilen) und mit den interessanteren Infos
--> und die zweite interessante Tabelle wird dir ca. im April aus dem Gesichtsfeld wandern, es sei denn, du kaufst dir einen 72x192-Zoll-Monitor (sind im Moment noch ziemlich teuer!).
Ordne das Layout umgekehrt an, dann hast du auch viel weniger Stress mit den Formenl & dem Nach-unten-Kopieren und irgendwo eine Zeile einfügen etc.
Grüße
Biber
[Edit & P.S]
@jens-3456
Ergänzend zu den vortrefflichen Erklärungen bastlas nochmals der Hinweis auf die "benannten Bereiche".
Dann wird (s.o.) aus
=SUMMENPRODUKT((TEXT($E$2:$E$10;"MMM")=$A15)*$F$2:$F$10*($G$2:$G$10="Tanken"))... das etwas verständlichere ...
=SUMMENPRODUKT((TEXT(DatumRaus;"MMM")=$A15)*KohleRaus*(WegenWattDenn="Tanken"))Grüße und Dank an bastla
[Edit & P.S]
Hallo jens-3456!
Na, so eine Hexerei ist das auch wieder nicht - am Beispiel der längsten Variante
Immer schön von "innen" nach "außen":
lässt sich übersetzen in "Vergleiche die auf das Format 'Monat abgekürzt' formatierten Inhalte der Zellen E2:E10 jeweils mit dem Wert von A15 (also 'JAN')" - wenn es eine Übereinstimmung gibt, ist das Ergebnis 1, wenn nicht (weil das Datum aus einem anderen Monat stammt), dann 0
Analog dazu
Auch hier ergibt sich 1, wenn in der jeweils betrachteten Zelle (aus dem Bereich G2 bis G10) "Tanken" steht, und 0, wenn eben nicht.
Diese beiden Teilergebnisse liefern miteinander multipliziert nur dann 1, wenn in beiden Fällen eine Übereinstimmung gefunden wurde (also "Datum aus dem mit 'JAN' abgekürzten Monat" und "Zweck ist 'Tanken')", und nur dann ergibt die abschließende Multiplikation mit
also der entsprechenden Zelle aus F2 bis F10 das Ergebnis "Wert der Zelle in Spalte F" - in allen anderen Fällen liefert die Multiplikation den Wert 0.
(das besser
... daher nochmals:
Es wird geprüft, ob E2 als umformatiertes Datum 'JAN' liefert und ob G2 'Tanken' enthält, und nur wenn beides zutrifft, wird F2 in die Summenberechnung aufgenommen.
Für E3, G3 und F3 sowie alle weiteren Zeilen bis 10 läuft genau das Gleiche ab, und am Ende sind nur die passenden Werte im Ergebnis (= der Summe) enthalten.
Grüße
bastla
Na, so eine Hexerei ist das auch wieder nicht - am Beispiel der längsten Variante
=SUMMENPRODUKT((TEXT($E$2:$E$10;"MMM")=$A15)*$F$2:$F$10*($G$2:$G$10="Tanken"))
(TEXT($E$2:$E$10;"MMM")=$A15)
Analog dazu
($G$2:$G$10="Tanken")
Diese beiden Teilergebnisse liefern miteinander multipliziert nur dann 1, wenn in beiden Fällen eine Übereinstimmung gefunden wurde (also "Datum aus dem mit 'JAN' abgekürzten Monat" und "Zweck ist 'Tanken')", und nur dann ergibt die abschließende Multiplikation mit
*$F$2:$F$10
=SUMMENPRODUKT()
PRODUKTSUMME() heißen sollte - aber das ist ein anderes Thema) schließlich dient nur dazu, die genannten Berechnungen für alle Zellen der angegebenen Bereiche auszuführen ...... daher nochmals:
Es wird geprüft, ob E2 als umformatiertes Datum 'JAN' liefert und ob G2 'Tanken' enthält, und nur wenn beides zutrifft, wird F2 in die Summenberechnung aufgenommen.
Für E3, G3 und F3 sowie alle weiteren Zeilen bis 10 läuft genau das Gleiche ab, und am Ende sind nur die passenden Werte im Ergebnis (= der Summe) enthalten.
Grüße
bastla