Excel - Wie erweitert man den Zellbereich in der Formel automatisch?
Hallo,
siehe Bild: D7 / E7 wurden neu hinzugefügt. Wie stelle ich es so sein, dass die neuen Werte in der Formel der Zelle H2 auch berücksichtigt werden? Aktueller Fehler: Die Formel schließt nicht alle angrenzenden Zellen ein. Das jedes mal manuell zu machen, ist mir für meinen Anwendungsfall zu umständlich. Das Ziel ist es einfach, die Werte des Monats x zu summieren.
Vielen Dank im Voraus.
3 Antworten
Spricht was dagegen in der Formel die Bereiche von vornherein z. B. auf D2:D10000 bzw. E2:E10000 zu setzen?
Ich habs mir nun angeschaut und würde es so lösen (ich bezihe mich dabei mal auf dein Bild):
Spalte C wird zu Hilfsspalte. In Zelle C2 kommt die Formel "=MONAT(D2)". Das einfach nach belieben nach unten erweitern. Dadruch ist aus dem Datum erstmal der Monat erstellt.
In Zelle H2 kommt nun diese Formel: =SUMMEWENN(C2:C8;2;E2:E8) In diesem Fall werden nun alle Beträge zum Monat Februar aufaddiert.
Da ja noch viele weitere Zeilen folgen sollen, kannst du auch z.B. =SUMMEWENN(C2:C1000;2;E2:E1000) schreiben.
Ich persönlich würde ja =SUMMEWENN(C2:C1000;G2;E2:E1000) schreiben. In der Zelle G2 würde ich dann eine Zahl zwischen 1-12 eintragen, je nachdem welchen Monat ich gerade sehen will. Oder man macht in den Zellen H3, H4, H5 etc. für jeden Monat eine eigenen Formel.
Achtung: Das ganze berücksichtigt nur Monate und nur Jahre. Sollte es mal dazu kommen, dass z.B. ein Datum 02.02.2023 hinzukommt, wird der natürlich dann auch dazu gezählt.
Entweder du machst es so, dass du für jedes Jahr eine eigenen Spalte benutzt oder du erweiterst die Formel in Spalte C.
Dort könnte man dan eintragen =VERKETTEN(MONAT(D2);"/";JAHR(D2)) und in Zelle H2 wäre das Suchkriterium nun nicht mehr 2, sondern 2/2022, also so: =SUMMEWENN(C2:C8;"2/2022";E2:E8).
Ggf. gibt es auch noch elegantere Möglichkeiten zur Umsetzung. Es gibt hier eben mehrere Möglichkieten um zum Ziel zu kommen.
Mit Summewenn habe ich es nicht hinbekommen. Habe dafür ein funktionierendes Beispiel aus dem Internet mit Summe und Wenn gefunden. Wenn du weißt, wie ich das oben in Summewenn ausdrücke, würde ich das verwenden, anstatt etwas verschachteltes.
Wenn ich Zeit habe, guck ich mir das morgen einmal genauer an.
Was ist denn überhaupt das Ziel des Ganzen? In Spalte D wird fortlaufend ein Datum eingegeben und in Spalte E ein Geldbetrag.
Und in der Zelle H2 soll dann die summe für einen bestimmten Monat, z. B. Februar, gebildet werden? Sehe ich das richtig?
Gute Idee, aber dann müsste ich permanent aufpassen, dass nicht etwas fälschlicherweise in die Summe fließt, weil die Bedingung erfüllt wird, wenn ich in dieser Spalte arbeite.
Kann sein. Ich weiß ja nicht, was da noch für Werte eingetragen werden könnten und die jetzigen bedingungen auch erfüllen, ob wohl die nicht gewertet werden sollen.
Noch ein Tipp: Warum verwendest du nicht die Funktion "Summewenn" statt "Summe" und "wenn"?
Bei SUMMEWENN ergibt sich das Problem, die Suchkriterien korrekt anzugeben. Man kann größer, kleiner oder gleich einem Wert arbeiten, aber keine Formel benutzen, die innerhalb von SUMMEWENN ausgewertet wird.
D2:D7 ist ein Bereich mit Datumswerten. Diese sollen verglichen werden. Vergleicht man sie mit Monaten, hat man ein Problem. Extrahiert man aus dem Bereich im ersten Argument den Monat, geht es auch nicht, weil im ersten Argument ein Bereich erwartet wird und keine Ganzzahlen.
Man könnte es so lösen, dass man eine Hilfsspalte generiert, in der die Monate passend zur Zeile stehen und darauf dann SUMMEWENN anwenden.
Ab März wird es ein Problem mit dem Formelergebnis geben
Anbieten würde sich, den Monat in einer Hilfsspalte anzeigen zu lassen oder wie
Antwortvon Suboptimierer Usermod Community-Experte Excel vor 21 Stunden
eine Pivot-Tabelle zu nutzen.
Die Formel an sich funktioniert nur, wenn du die Eingabe mit Strg+Shift+Enter abschließt. Das ist dir bewusst?
Du kannst die Formel beliebig erweitern, wenn du willst sogar auf die gesamten Spalten D und E.
=SUMME(WENN(MONAT(D:D)=2;E:E))
Wie du siehst, benötigt man GANZZAHL nicht zwingend, weil MONAT den Monat eines Datums als Ganzzahl zurück gibt.
Kann Excel den Bereich automatisch erweitern, d. h. die Werte in der Summe berücksichtigen, wenn ich eine weitere Reihe eingebe, z. B., 09.02.2022 20 €? Ich möchte nicht, dass die ganze Spalte abgefragt wird. Wenn ich es aktuell mache kommt folgender Fehler: Aktueller Fehler: Die Formel schließt nicht alle angrenzenden Zellen ein.
Excel erweitert die Forrmeln nicht automatisch. Das musst du manuell machen.
Es gibt bestimmt Möglichkeiten, einen Bereich dynamisch zu defineren, zum Beispiel mittels BEREICH.VERSCHIEBEN und ANZAHL2, aber das macht die Formel in der Regel ziemlich unübersichtlich.
Was sich hier anbieten würde, ist mit Pivottabellen zu arbeiten.
Vergib deinen Spalten Überschriften, zum Beispiel Datum und Wert, markiere den Bereich, dann Einfügen → Tabellen → Pivot Tabelle, Einstellungen so lassen und okey.
Im neu eingefügten Tabellenblatt ziehst du im Pivot-Einstellungsbereich Wert unter die Spalten, Datum unter Zeilen und in den Summenbereich ´Wert per Drag & Drop.
Genau.