automatisches Monat-weises Saldo in Buchungstabelle

JeanLuc · Beitrag von **JeanLuc** » Mo, 06.02.2006 12:32

Hallo, Ich bin der Neue!

Trotz Suche nichts wirklich passendes im Forum gefunden!

Was will ich also:
In einer Buchungstabelle, mit Datumsspalte, jeweils am Monatsende (und nur dort) in einer extra Spalte über bestimmte Spalten mit Buchungen eine Summe (Monats-Saldo) anzeigen lassen.

Ich hoffe das ist klar soweit. Ich weiss das mit Matrixform von Summe und Monat() das gesuchte für eine vorgegebene Zelle geht. Aber erstens will ich mich nicht um diese Zelle separat kümmern sondern der "Spalte" selbst überlassen die Monatsgrenzen zu erkennen und zweitens sind die Buchungstage in verschiedenen Monaten unteschiedlich viele.

Danke vorab, JeanLuc

Lenu · Beitrag von **Lenu** » Mo, 06.02.2006 12:58

In Spalte B steht das Datum, in Spalte C steht der Betrag

=SUMMENPRODUKT(MONAT($B$2:$B$32000)=MONAT(B2);JAHR($B$2:$B$32000)=JAHR(B2);$C$2:$C$32000)

Das kannst Du jetzt noch in eine WENN-Abfrage einbinden, z.B.:
=WENN(TAG(B2+1)=1;obigeFormel;"")

Diese Formel kannst Du einfach nach unten ziehen.
Aufpassen bei den absoluten und relativen Adressen!

Ist natürlich auch eine Matrixformel, aber ich verstehe nicht recht, was Du mit "der Spalte überlassen" meinst

Lenu · Beitrag von **Lenu** » Mo, 06.02.2006 21:06

Lenu hat geschrieben:
Das kannst Du jetzt noch in eine WENN-Abfrage einbinden, z.B.:
=WENN(TAG(B2+1)=1;obigeFormel;"")

Wahrscheinlich hast Du ja mehrere Zeilen pro Tag, dann musst Du die WENN-Formel etwas erweitern, z.B.:
=WENN(UND(TAG(B2+1)=1;B2<>B3);obigeFormel;"")

JeanLuc · Beitrag von **JeanLuc** » Mo, 06.02.2006 22:12

Danke

Lenu hat geschrieben: aber ich verstehe nicht recht, was Du mit "der Spalte überlassen" meinst

Damit meinte ich genau deine angedachte Lösung einfach die Formel runter zu ziehen und mittels WENN anzuzeigen.
Da ich aber nicht garantieren kann, dass mein neuer Monat mit ner Buchung am Ersten beginnt mache ich das so:

WENN((TAG(A24)>TAG(A25));...;"")

Was ich nicht hin bekam war deine Variante mit

=WENN(TAG(B2+1)=1;obigeFormel;"")

Sollte doch auch mit

WENN((TAG(A24)>TAG(A24+1));...;"")

funktioniern? Tuts aber nicht!

Was mich auch ein wenig stört ist die Tatsache das in der gesamten Formel das Feld A24 bzw. die Datumszelle einer jeden Zeile 3x addressiert wird (mit deinem Vorschlag gar viermal). Kann man/frau nicht beim ersten Referenzieren eine Variable zuweisen und diese weiter verwenden?
Oder läufts intern eh so!

Eigentlich will ich sagen das mich beim grafischen editieren der Formel die vielen bunten Bereiche nerven, gleichwohl sie alle das selbe aussagen sollen.

Lenu · Beitrag von **Lenu** » Di, 07.02.2006 07:47

JeanLuc hat geschrieben: Damit meinte ich genau deine angedachte Lösung einfach die Formel runter zu ziehen und mittels WENN anzuzeigen.

Die Formel war nicht nur angedacht. Ich habe ein Testfile erstellt und sie getestet.

Was ich nicht hin bekam war deine Variante mit
=WENN(TAG(B2+1)=1;obigeFormel;"")
Sollte doch auch mit
WENN((TAG(A24)>TAG(A24+1));...;"")

Ja, muss auch so funktionieren.
Voraussetzung: Du arbeitest mit Version 2. In der 1er-Version gehen solche Matrix-Formeln noch nicht

Was mich auch ein wenig stört ist die Tatsache das in der gesamten Formel das Feld A24 bzw. die Datumszelle einer jeden Zeile 3x addressiert wird (mit deinem Vorschlag gar viermal). Kann man/frau nicht beim ersten Referenzieren eine Variable zuweisen und diese weiter verwenden?

Du kannst natürlich auch indirekt adressieren:
=WENN(TAG(INDIREKT(ADRESSE(ZEILE();SPALTE()-2)))>TAG(INDIREKT(ADRESSE(ZEILE()+1;SPALTE()-2))); SUMMENPRODUKT(MONAT($A$3:$A$32000)=MONAT(INDIREKT(ADRESSE(ZEILE();SPALTE()-2))); JAHR($A$3:$A$32000)=JAHR(INDIREKT(ADRESSE(ZEILE();SPALTE()-2)));$B$3:$B$32000);"")

Wenn Du jetzt das hier:
INDIREKT(ADRESSE(ZEILE();SPALTE()-2))
als Name speicherst (einfügen/Namen), z.B. DatumLinks
und das hier:
INDIREKT(ADRESSE(ZEILE()+1;SPALTE()-2))
als DatumUnten
dann kannst Du die Formel so schreiben:
=WENN(TAG(DatumLinks)>TAG(DatumUnten);SUMMENPRODUKT(MONAT($A$3:$A$32000)=MONAT(DatumLinks);JAHR($A$3:$A$32000)=JAHR(DatumLinks);$B$3:$B$32000);"")
Und die anderen Komponenten kannst Du auch als Namen auslagern:
VglMonat: MONAT($A$3:$A$32000)=MONAT(DatumLinks)
VglJahr: JAHR($A$3:$A$32000)=JAHR(DatumLinks)
VglSumme: $B$3:$B$32000
(Jahr, Monat, Summe würde ich nicht als Namen empfehlen)
=WENN(TAG(DatumLinks)>TAG(DatumUnten);SUMMENPRODUKT(VglMonat;VglJahr;VglSumme);"")
Oder auch:
Saldo: SUMMENPRODUKT(VglMonat;VglJahr;VglSumme)
Dann heisst die Formel noch:
=WENN(TAG(DatumLinks)>TAG(DatumUnten);Saldo;"")
Da kannst Du frei experimentieren

Eigentlich will ich sagen das mich beim grafischen editieren der Formel die vielen bunten Bereiche nerven

Deine Probleme möchte ich haben!

Aber versuchs doch mal mit dem Funktions-Assistenten. Da kannst Du einerseits die Formel in zerlegten Teil-Strings Bearbeiten oder als schwarzen Text, ohne bunte Darstellung.

...gleichwohl sie alle das selbe aussagen sollen.

Da scheint mir ein Denkfehler vorzuliegen. Du benutzt innerhalb der Formel verschiedene Funktionen, die alle wieder einen Zellbezug benötigen. Dass der nun immer der gleiche ist, liegt am konkreten Formelaufbau. Die gleiche Formel könnte aber auch an jedem Zellbezugspunkt einen anderen Eintrag haben, insofern ist es nicht immer das gleiche, auch wenn jetzt bei Deiner Formel zufällig immer A2 steht.
Und da Du nunmal immer einen Zellbezug angeben musst, macht es ja keinen Unterschied, ob Du 3x A2 schreibst oder 3x einen Variablennamen.
Relative Zelladressen sind ja eigentlich Variablen.
Wenn Du mit Namen arbeitest, wie im obigen Beispiel, dann sind das keine Variablen mehr, sondern eigentlich Konstanten, die sich nur durch die indirekte Adressierung teilweise "austricksen" lassen. Wenn Du aber Spalten einschiebst, muss der Bezug separat angepasst werden: SPALTE()-2 nach SPALTE()-3
Lange Formeln kann man mit Namen übersichtlicher gestalten, aber für den vorliegenden Fall scheint mir die Definition dieser Namen umständlicher als die paar Zelladressen. Und man muss noch etwas bedenken:
Wenn Du einen Zellbezug wie: A2 ersetzt durch etwas wie: INDIREKT(ADRESSE(ZEILE();SPALTE()-2)) wird die Formel ja zunächst um einiges länger. Es passiert daher öfter, dass man den Fehlercode 512 bekommt, weil die Formel die zulässige Gesamtlänge überschreitet, auch wenn in der Zelle selbst nur noch ein kurzer Ausdruck sichtbar ist.

PS:
WENN((TAG(A24)>TAG(A24+1))
genügt unter Umständen nicht. Evtl. musst Du den Monat auch noch mit einbeziehen. Wenn z.B. die letzte Buchung eines Monats am 13.Februar liegt und die nächste am 20. März

JeanLuc · Beitrag von **JeanLuc** » Di, 07.02.2006 12:21

Lenu hat geschrieben: Deine Probleme möchte ich haben!
Aber versuchs doch mal mit dem Funktions-Assistenten. Da kannst Du einerseits die Formel in zerlegten Teil-Strings Bearbeiten oder als schwarzen Text, ohne bunte Darstellung.

Wenn ich das richtig überflogen habe sind das aber keine inline Definitionen, müssen also irgendwo im Dokument abgelegt werden

Wie du siehst taucht hier verdammt oft das Datumsfeld auf "A24".
Was ich jetzt wollte war sowas wie beim ersten auftreten des Feldes im Code eine Variable zu definieren
z.B. "A24:=§" oder sonst wie, und dann nur noch "§" zu verwenden.

Code: Alles auswählen

=WENN((A24<(A24)+1));
      (SUMMENPRODUKT(MONAT($A$4:$A$32002)=MONAT(A24);
                      JAHR($A$4:$A$32002)=JAHR(A24);
                           $H$4:$H$32002)
      +VERWEIS(DATUM(JAHR(A24);MONAT(A24);1)-1; %arbeitet intern offenbar wie Vergleich
               $A$4:$A$32002;                   %findet letztes datum aus Vormonat
               $I$4:$I$32002)
      );"")
 +VORLAGE(WENN(NICHT(ISTLEER(AKTUELL()));"Monats-Saldo(grau)"))

Da aber wenn der Code einmal steht keine Änderungen mehr notwendig sind, hat sich das Problem tatsächlich erledigt. Du kannst es also jetzt haben.

Lenu hat geschrieben: PS:
WENN((TAG(A24)>TAG(A24+1))
genügt unter Umständen nicht. Evtl. musst Du den Monat auch noch mit einbeziehen. Wenn z.B. die letzte Buchung eines Monats am 13.Februar liegt und die nächste am 20. März

Da hast du natürlich recht, aber kommt nicht vor!
Was aber noch viel einfacher ist und da funzt plötzlich auch das oben erwähnte:

WENN((A5<(A5+1);...)

PS: "angedacht" im Sinne von "für mich ausarbeiten" und dabei an mich gedacht

Lenu · Beitrag von **Lenu** » Di, 07.02.2006 12:40

JeanLuc hat geschrieben:
Lenu hat geschrieben: Aber versuchs doch mal mit dem Funktions-Assistenten. Da kannst Du einerseits die Formel in zerlegten Teil-Strings Bearbeiten oder als schwarzen Text, ohne bunte Darstellung.
Wenn ich das richtig überflogen habe sind das aber keine inline Definitionen, müssen also irgendwo im Dokument abgelegt werden

Keine Ahnung, was "inline-Definitionen" sind. Der Funtkionsautopilot oder -assistent ist einfach ein Werkzeug, mit dem Du die aktuelle Formel grafisch bearbeiten kannst. Er stellt sie in einer Struktur dar, in der Du jede Funktion einzeln bearbeiten kannst, also nicht immer in dem ganzen Formel-Schlauch rumfummeln musst.

Wie du siehst taucht hier verdammt oft das Datumsfeld auf "A24".
Was ich jetzt wollte war sowas wie beim ersten auftreten des Feldes im Code eine Variable zu definieren
z.B. "A24:=§" oder sonst wie, und dann nur noch "§" zu verwenden.

Was Du damit gewinnst, wenn du statt A24 "nur" $ schreibst, erschliesst sich mir nicht so recht. Aber vielleicht hilft ja das mit den Namen, was ich oben geschrieben habe. Aber nicht vergessen: variabler als eine relative Zelladresse ist nichts. Nur sie passt sich beim Kopieren oder beim Einschieben von Zeilen/Spalten automatisch an.

Du kannst es also jetzt haben.

Weiss zwar jezt grad nicht wofür, aber trotzdem: danke

Was aber noch viel einfacher ist und da funzt plötzlich auch das oben erwähnte: WENN((A5<A5+1);...)

Ohne TAG - was nützt Dir das? Der 3. Februar ist ja auch kleiner als der 4. Februar, aber da willst Du ja keine Formelanzeige, oder?

Lenu · Beitrag von **Lenu** » Di, 07.02.2006 12:50

Hier übrigens noch ein Link zum Arbeiten mit Namen:
http://www.ooowiki.de/NamenF%C3%BCrFormeln

Achtung: beim "Überfliegen" wird womöglich wieder eine inline-Definition daraus

Lenu · Beitrag von **Lenu** » Di, 07.02.2006 13:07

JeanLuc hat geschrieben:
Lenu hat geschrieben: Aber versuchs doch mal mit dem Funktions-Assistenten. Da kannst Du einerseits die Formel in zerlegten Teil-Strings Bearbeiten oder als schwarzen Text, ohne bunte Darstellung.
Wenn ich das richtig überflogen habe sind das aber keine inline Definitionen, müssen also irgendwo im Dokument abgelegt werden

Nach nochmaligem Durchlesen habe ich jetzt wohl verstanden, was Du meinst. du beziehst Dich gar nicht auf den Funktions-Assistenten, sondern auf die Definition von Namen.

Ja, die müssen ausserhalb der Formel gespeichert werden. Dafür sind sie global verfügbar, in allen Tabellen eines Arbeitsblattes. Aber mit sowas wie "$" (falls das überhaupt geht) werden Formeln nicht lesbarer. Und wenns ums schnelle Schreiben geht:
Da geht nichts über das "Zusammenklicken" mit dem Funktionsautopiloten. Ich habe schon öfter Formeln schreiben müssen, die bis zu 1000 Zeichen lang waren. Kein Problem mit dem Funktionsautopiloten. Auch das Kopieren innerhalb eines langen Formelstrings. Und Tippfehler gibts dann auch nicht mehr.

Lenu · Beitrag von **Lenu** » Di, 07.02.2006 13:33

JeanLuc hat geschrieben: +VORLAGE(WENN(NICHT(ISTLEER(AKTUELL()));"Monats-Saldo(grau)"))

Dass das geht, hätte ich jetzt auch nicht gedacht. Ich hätte angenommen, dass in dem Moment, wenn eine Formel in einer Zelle steht, diese nicht mehr als LEER erkannt wird.

Aber OO (oder seine Väter) ist eben klüger als ich, habe ich schon mehrfach feststellen müssen.