Über Themen mit S Testbericht

der Funktion / Formel SUMMEWENN

Anmerkung vor ab:

Ich habe mehrfach versucht, Yopi dazu zu bewegen, hier eine entsprechende Rubrik einzurichten, wenigstens aber mehrere Berichte zu MS Excel / MS Word zuzulassen.

Da dies immer wieder abgelehnt worden ist, habe ich jetzt lt. aktueller Antwort die Info erhalten, ich möchte die Berichte doch in der Lounge „Über Themen mit S“ veröffentlichen.

Tja, das habe ich also hier und jetzt getan …

kurzes Vorwort, muss ja schließlich ´mal wieder sein ...

Was macht man, wenn man von einem Freund gebeten, Hilfe in MS Excel zu leisten, der nun wirklich so gut wie keine Erfahrungen / Ahnungen mit dem Programm hat ?? Der nur das Nötigste vernünftig eingeben kann ??

1.
Richtig, man guckt erst einmal sehr doof und dumm aus der Wäsche.

2.
Ich habe mir etwas einfallen lassen, was einerseits relativ einfach ist, andererseits gut funktionieren sollte.

Folgendes war geschehen:
vor einigen Tagen sprach mich ein Freund auf ein für ihn vorhandenes Excel-Problemchen an.

Der Hintergrund war seine genehmigte Nebentätigkeit bzw. jetzt freiberufliche Tätigkeit, die er mittlerweile ausübt. Nach seiner Aussage wollte das örtliche Finanzamt wohl eine sog. Einnahme - Ausgabe - Überschussberechnung mit monatlichen Aufstellungen von ihm haben.
Er dürfe also nicht über bestimmte monatliche Grenzen beim Gewinn hinauskommen, da sonst die monatliche Vorsteueranmeldung fußen würde, die noch mehr Arbeit verursachen würde.

Mein Freund hat sich bereits die Mühe gemacht, 3 Tabellenblätter anzulegen. Eines sollte für die Einnahmen, eines für die Ausgaben und das dritte für die monatliche Übersicht dienen.
Die Tabelle sah bereits folgende Informationen vor: Einnahmen mit Datum, Grund und Endbetrag), Ausgaben mit Datum, Grund, Endbetrag, Gesamtwerte für jeden Monat getrennt.
All dies hatte er mangels Wissen bislang manuell bedient.

Was, wie und warum hat mich eigentlich weniger interessiert. Ich wollte ihm einfach nur helfen.

Da ich das Problemchen, wie er es schilderte, und auch die mögliche Lösung recht interessant fand, habe ich mich dazu entschlossen diese Lösung als Bericht hier zu veröffentlichen.

Er kann vielleicht dem Einen oder Anderen als Hilfe oder wenigstens als Ansatzpunkt für eigene Probleme dienen.
Er soll auch einfach nur dazu dienen, einmal mehr aufzuzeigen, wie komplex MS Excel einerseits ist, wie einfach aber auch andererseits Lösungen gefunden werden können.

Da die Vorgaben doch eigentlich bereits klar waren, viel mir sofort der mögliche „Missbrauch“ der sog. Summewenn-Funktion ein.

Gliederung:

1. allgemeine Erklärung zum Funktionsprinzip der Formel / Funktion SUMMEWENN

2. theoretische Lösung anhand des / eines Beispiels ...

3. praktische Lösung anhand des / eines Beispiels mit den verschiedenen Schritten ...

4. mein Fazit (einmal in etwas anderer Art)

1. allgemeine Erklärung zum Funktionsprinzip der Formel / Funktion SUMMEWENN

Wer diese Funktion nicht kennen sollte, nachstehend eine kurze Erklärung:

Diese Funktion summiert, wie es der Name bereits aussagt, alle Werte innerhalb eines Zellbereiches. Und zwar summiert sie nur die Werte, die eine bestimmte Vorgabe erfüllen.

Sie besitzt drei Bereiche, die auch entsprechend ausgefüllt werden müssen, nämlich:

Bereich 1 = das ist der Bereich, der geprüft werden soll.

Bereich 2 = das Suchkriterium. Das ist also die Information, die Zelle, die Zahl, die Formel, der Text, der definiert werden muss um eine entsprechende Auswahl zu erhalten.

Bereich 3 = Summe-Bereich, sind die Werte in einem definierten Bereich, berechnet also konkret aufsummiert werden sollen, wenn das Suchkriterium erfüllt ist.

Diese Infos sind vielleicht für den einen oder anderen Nutzer jetzt noch etwas schwammig, daher ...

2. theoretische Lösung anhand des / eines Beispiels ...

Stellen wir uns eine Mappe vor, die in den Tabellen Einnahmen und Ausgaben der jeweiligen Spalten A in den Zellen A1 bis A50 gültige Datumswerte des laufenden Jahres enthält. In den Spalten B ist der Grund für die Zahlung bzw. die Einnahme aufgeführt. In den Spalten C werden die jeweiligen Endbeträge aufgelistet.

Die Listen (Tabellenblätter) könnten dann z. B. wie folgt aussehen:

Tabelle Ausgaben:
23.04.2008 | Kauf eines Schweißgerätes | 235,00 EURO
03.05.2008 | EDV-Papier mit Rechnungsvordrucken | 13,95 EURO
12.02.2008 | Telefongebühren | 45,33 EURO
10.05.2008 | Benzinkosten für Kundenfahrten | 120,00 EURO
06.06.2008 | Büroklammern | 8,88 EURO
12.09.2008 | Kauf eines Akku-Schraubers | 45,99 EURO
31.05.2008 | Arbeitsmaterial, wie Zangen, Hämmer etc | 50,00 EURO

Tabelle Einnahmen:
30.04.2008 | Handwerkerrechnung Treppengeländer | 45,90 EURO
10.05.2008 | Reparatur einer Eingangstür| 18,90 EURO
12.02.2008 | Pflasterarbeiten lt. Angebot | 350,80 EURO
08.06.2008 | Schweißarbeiten | 100,00 EURO
01.09.2008 | Verkauf gebrauchter Hard- und Software | 88,88 EURO
30.05.2008 | Ausarbeitung einer Dokumentation in PowerPoint | 150,00 EURO

Anmerkung:
a)
Es versteht sich von selbst, dass diese Infos und Werte völlig aus der Luft gegriffen sind.
b)
Die Datumswerte sind mit Absicht durcheinander aufgelistet worden. Man kann einerseits in Excel schnell sortierten, andererseits soll dieses „Durcheinander“ auch aufzeigen, dass es der Funktion ziemlich egal ist, wie sortiert bzw. unsortiert die Daten vorliegen.
Wichtig allein ist, dass sie gültige Werte enthalten und natürlich die Werte in den richtigen Spalten vorhanden sind.

Nun sollen alle Beträge, die z.B. im Mai des lfd. Jahres eingegeben worden sind, getrennt nach Einnahmen und Ausgaben summiert werden.

3. praktische Lösung anhand des / eines Beispiels mit den verschiedenen Schritten ...

Ein wenig Vorüberlegungen musste ich dann doch anstellen, da ich die Angelegenheit möglichst flexibel gestalten wollte. Die Tabelle sollte für jedes Jahr immer wieder neu genutzt werden; lediglich bei den Einnahme- und den Ausgabewerten müssen die Infos gelöscht werden.

Da die Funktion SUMMEWENN lediglich nur einen Prüfparameter aufnehmen kann, die Monate aber variable Tage aufweisen, der Februar einmal 28 Tage, einmal 29 Tage, die anderen Monate entweder 30 oder 31 Tage, war natürlich ein bischen „Trickserei“ erforderlich.

Aus dem Grunde hatte ich mich entschieden:
a)
die Funktion / Formel SUMMEWENN einfach zweimal zu nutzen. Einmal für die Ermittlung des Monatsanfangs und einmal für die Ermittlung des Monatsende.
b)
die Angelegenheit mit weiteren Formeln ein wenig „aufzupeppen“ um wirklich eine größtmögliche Automatik zu bekommen.

Alle Schritte werden – so hoffe ich jedenfalls – von mir kurz und eindeutig erklärt.

Da ich ja – wie bereits erwähnt - die Infos und Berechnungen ja möglichst flexibel gestalten wollte, sind noch einige weitere Formeln / Funktionen von Nöten, die ich vorab kurz vorstellen werde:

Funktion HEUTE()
Hiermit wird MS Excel angewiesen, immer das aktuelle Tagesdatum zu ermitteln.

Funktion JAHR() ,im konkreten Fall: JAHR(HEUTE())
Mit der Funktion JAHR( Eingabe des Datums ) allgemein, lässt sich die Jahreszahl aus einem Datum extrahieren. Ich habe sie als Kombination mit der Funktion HEUTE() genutzt, um immer das aktuelle Jahr ermitteln zu können, da dieses später noch benötigt wird.

Funktion DATUM() ,im konkreten Fall: DATUM(JAHR(HEUTEMonat;Tag)
Mit Hilfe dieser Funktionskombination werden die jeweiligen Anfangswerte der Monate ermittelt. Es gibt natürlich auch noch andere Möglichkeiten; mir war diese jedoch als Erstes eingefallen, so dass ich sie genutzt habe.

So, nun geht’s an Eingemachte …

Wie erwähnt, müssen ja die jeweiligen Monatsanfangs- und Endwerte ermittelt werden. Da diese ja unterschiedlich sind, bin ich wie folgt vorgegangen:

ACHTUNG:
Zur Verdeutlichung und besseren Lesbarkeit ist die Formel mit Leerzeichen versehen worden. Dies ist bei einer möglichen Nachahmung oder Nachprüfung natürlich nicht gestattet.
Alle Werte werden direkt hintereinander geschrieben !!

Formelkombination: = DATUM ( JAHR ( HEUTE ( ) ) ; 5 ; 1 )
Diese Kombination ermittelt immer den 1.5. des jeweils aktuellen Jahres. So lässt sich natürlich auch jeder andere Datumswert erzeugen.

Für die anderen Monate müssen lediglich die Werte ausgetauscht werden:
=DATUM(JAHR(HEUTE1;1) ergäbe den 1. Januar des lfd. Jahres
=DATUM(JAHR(HEUTE2;1) ergäbe den 1. Februar des lfd. Jahres
=DATUM(JAHR(HEUTE3;1) ergäbe den 1. März des lfd. Jahres
usw.
=DATUM(JAHR(HEUTE12;1) ergäbe den 1. Dezember des lfd. Jahres

Formelkombination: = DATUM ( JAHR ( HEUTE ( ) ) ; 6 ; 1 ) - 1
Diese Kombination ermittelt immer den 31.5. des jeweils aktuellen Jahres. So lässt sich natürlich auch jeder andere Datumswert erzeugen.

Für die anderen Monate müssen lediglich die Werte ausgetauscht werden:
=DATUM(JAHR(HEUTE2;1)-1 ergäbe den 31. Januar des lfd. Jahres
=DATUM(JAHR(HEUTE3;1)-1 ergäbe den letzten Tag im Februar des lfd. Jahres
=DATUM(JAHR(HEUTE4;1)-1 ergäbe den 31. März des lfd. Jahres
usw.

Wer nun denkt, beim 31.12. des lfd. Jahres funktioniert es genauso, der irrt.
Hier muss die Formel etwas geändert werden, und zwar wie folgt (auf das Jahr wird erst fiktiv ein Jahr aufaddiert):

=DATUM(JAHR(HEUTE1;1;1)-1 ergäbe den 31. Dezember des lfd. Jahres

Um nun mit der SUMMEWENN-Funktion Ergebnisse ermitteln zu können, muss – wie gesagt - ein wenig getrickst werden.
Da die Funktion nur einen einzigen Zustand kennt (man kann ja leider nicht eingeben: prüfe mir von bis, also vom 1.5. bis 31.5.), muss man sie einfach zweimal nutzen.

Der Trick funktioniert wie folgt:

In einer ersten SUMMEWENN-Funktion werden alle Ergebnisse ermittelt, die im vorliegenden Fall alle bis zum 31. Mai des lfd. Jahres vorhanden sind.
In der zweiten SUMMEWENN-Funktion werden nun alle Ergebnisse ermittelt, die im vorliegenden Falle alle bis zum 1. Mai des lfd. Jahres vorhanden sind. Der 1. Mai darf natürlich nicht mit berechnet werden.

So erhält man logischer Weise zwei Werte, bei denen der erste Wert immer größer oder maximal gleich groß zum zweiten Wert sein kann.
Zieht man nun von diesem ersten Wert den zweiten Wert ab, erhält man automatisch das Ergebnis, welches im Mai vorliegt.

Die erste SUMMEWENN-Funktion müsste daher wie nachstehend lauten:

=SUMMEWENN(A1:A14;"<="&DATUM(JAHR(HEUTE6;1)-1;B1:B14)

Was passiert ??
Frei übersetzt wird mit dieser Formel alles aufaddiert, was an Werten im Bereich bis zum Datum 31. Mai – also kleiner oder gleich dem 31.5. - des lfd. Jahres vorhanden ist.

Die zweite SUMMEWENN-Funktion müsste nun logischer Weise daher wie nachstehend lauten:

=SUMMEWENN(A1:A14;"<"&DATUM(JAHR(HEUTE5;1);B1:B15)

Was passiert hierbei nun ??
Frei übersetzt wird auch mit dieser Formel wieder alles aufaddiert, was an Werten im Bereich kleiner als der 1. Mai des lfd. Jahres vorhanden ist.

Man kann diese Formeln / Funktionen zwar in zwei unterschiedlichen Hilfszellen unterbringen und in einer dritten Zelle dann jeweils den Differenzbetrag ermitteln, dies finde ich aber aus verschiedenen Gründen nicht besonders schick.

Somit lautet die komplette Funktion / Formel wie folgt:

=SUMMEWENN(A1:A14;"<="&EDATUM(D1;1)-1;B1:B14)-SUMMEWENN(A1:A14;"<"&DATUM(JAHR(HEUTE5;1);B1:B14)

Anmerkung:
Diese doch etwas komplexere Funktion / Formel muss natürlich hintereinander gelesen werden.

Es passiert genau das, was ich zuvor geschildert habe.
Bis zum Beginn der zweiten SUMMEWENN-Funktion wird einfach alles addiert, was bis einschließlich des 31.5 angefallen ist. Im zweiten Bereich wird lediglich bis zum 30.4 aufaddiert. Danach wird dann vom größeren Wert der kleinere Wert abgezogen.
Man erhält die Einnahmen und Ausgaben für den Monat Mai des lfd. Jahres.

4. mein Fazit (einmal in etwas anderer Art)

Mein Freund, für den ich dies kurz erstellt habe, freut sich, da er einiges an Zeit spart.

Im Original sind natürlich weitere Sicherheiten eingebaut worden (nur gültige Datumswerte, nur gültige Zahlenwerte etc.)

Auch hier lässt sich die SUMMEWENN-Funktion natürlich auch für andere Bereich nutzen …
Man muss nur ein wenig „links herum denken“.

So, das war es ´mal wieder von mir ...

Ich hoffe, ich konnte der / dem einen oder anderen ein wenig helfen, und freue mich natürlich über jedes Statement und natürlich über jede andere, gute Idee. Über entsprechendes Feedback (als GB-Eintrag etc.) freue ich mich natürlich ganz besonders.

Sollten Fragen bestehen, sollten ggf. Probleme existieren; ich versuche zu helfen, wenn ich kann.
Man lasse mir also ggf. dann einfach eine entsprechende Anfrage / Nachricht zukommen.

Zum Schluss (wie immer):
Mir wünsche ich viele Kommentare und natürlich möglichst viele positive Bewertungen.
(Aber nur dann, wenn Ihnen der Bericht wirklich gefallen hat. Frei nach dem Motto: der Bericht hat Ihnen gefallen, dann sagen Sie es weiter. Der Bericht hat Ihnen nicht gefallen, dann sagen Sie es mir!).

Bis zum nächsten Bericht, man liest sich ?!

PS: meine Berichte erscheinen auch auf anderen Plattformen …

Wer liest sollte auch bewerten, oder ??