Datum in Spalte zählen

[RPP63]

Ich habe auch noch nie mit Pivot-Table gearbeitet und kenne mich da garnicht aus.

Dann wäre es jetzt doch ein prima Zeitpunkt, ich damit zu beschäftigen, Michael.
Eine Kurzanleitung habe ich Dir ja gegeben.
Markiere E1:E1000, Einfügen Pivot-Table, dann weiter wie oben.
Mich hat die Erstellung ca. 30 Sekunden gekostet.
Du darfst gerne 30 Minuten investieren.
Glaube mir:
Ist ein mächtiges Werkzeug und ist vergleichsweise einfach zu erlernen.

Gruß Ralf

[EA1950]

Hallo,

Das hätte ich so nie hinbekommen. Ich dachte nicht, dass es so kompliziert ist etwas mit Datum zu berechnen/zählen.

was ist da so kompliziert? Oder wäre es dir lieber gewesen, ich hätte die ZÄHLENWENNS-Funktion x-mal verschachtelt, statt sie ganz einfach Schritt für Schritt in einer LET-Funktion aufzubauen?

N)ehmen wir die erste Funktion her: Anzahl Termine dieses Monats (zukünftig):
Die Formel lautet:
=LET(
  h;HEUTE();
  me;MONATSENDE(h;0);
  tm;Termine!$E$2:$E$25000;
  ZÄHLENWENNS(tm;">"&h;tm;"<="&me)
)
Es wird die LET-Funktion eingesetzt, die ein schrittweises Berechnen des gewünschten Ausdrucks erlaubt.
Der Variable "h" wird der Wert der HEUTE()-Funktion zugewiesen - also hat diese den Datumswert 19.10.2024
Der Variable "me" wird der Wert der Funktion MONATSENDE(h;0) zugewiesen, also hat diese den Datumswert 31.10.2024
Der Variable "tm" wird der Zellbereich Termine!E2:$E25000 zugewiesen, denn dort hast du deine Termine stehen.
Zum Schluss werden diese Variablen verwendet, um mit der ZÄHLENWENNS-Funktion deine Termine zu zählen, die zwischen dem 20.10.2024 und 31.10.2024 liegen im Zellbereich Termine!E2:E25000.

Das ist die ganze Hexerei hinter der so "komplizierten" Formel.

[Blacky1980]

Hallo Sigi, Ralf und Anton

@Sigi
Dir auch vielen Dank für die hilfe.
Werde mir dies auch mal genauer anschauen


@Ralf
Danke werde ich mir auf jeden Fall mal genauer anschauen.
 

@Anton
Also wenn ich es richtig verstanden habe, ist die Funktion LET dafür da dass man bestimmte Formeln abkürzen kann.
Wie jetzt bei Dir z.B h=Heute() dann bräuchte ich nur noch h schreiben oder tm = Termine!$E$2:$E$25000 für den Bereiche.
 
Dann wäre die Formel ohne LET so und man müsste alles auschreiben.

Code:

=ZÄHLENWENNS(Termine!$E$2:$E$25000;">"&HEUTE();Termine!$E$2:$E$25000;"<="&MONATSENDE(HEUTE();0))

 
Ist es dann auch egal welche Bezeichnung ich nehme z.B. d = Heute() und t = Termine!$E$2:$E$25000 oder sind diese fest vorgegeben?
 
Gruß Michael

[BoskoBiati]

Hi,

Code:

=ZÄHLENWENNS(Termine!$E$2:$E$25000;">"&HEUTE();Termine!$E$2:$E$25000;"<="&MONATSENDE(HEUTE();0))

wird zu:

Code:

=let(xa; Termine!$E$2:$E$25000 ;xb;heute();zählenwenns(xa;">"&xb;xa;"<="&Monatsende(xb;0))

[EA1950]

Hallo,

Ist es dann auch egal welche Bezeichnung ich nehme z.B. d = Heute() und t = Termine!$E$2:$E$25000 oder sind diese fest vorgegeben?

die Variablennamen (also das "d" oder das "t", wie du es verwendet hast) sind nur innerhalb der LET-Funktion gültig. Man kann also von außerhalb (also von einer anderen Zelle) nicht auf diese Namen zugreifen - die Programmierer würden sagen: Diese Namen haben nur lokale Gültigkeit.

Andererseits darf man für diese Namen keine solchen Bezeichner verwenden, die in Excel bereits existieren. 
1) Dazu zählen in Excel alle Zellnamen, also Zelle A1, B20, XFD99, usw.
2) Dazu zählen auch alle Namen, die du im Namensmanager bereits festgelegt hast.

Um mit Punkt 1) keinen Konflikt zu erzeugen, sollte man daher keine Namen verwenden, die am Schluss eine Zahl haben (zB. A7, BA99, ...)
Darüber hinaus darf man nur solche Namen verwenden, die aus Buchstaben (also "a"..."z", oder "A"..."Z", oder "ä"..."ß") oder Ziffern (diese also nicht am Ende!!) bestehen. Also sind Sonderzeichen verboten (das ist eine allgemeine Konvention in der Programmiererbranche).

Anmerkung: In der LET-Funktion darfst du natürlich nicht das "="-Zeichen verwenden, um einen Wert an eine dieser Variablen zuzuweisen, sondern bei einem deutschen Excel nur das Semikolon (=Strichpunkt).

[Blacky1980]

Ich euch allen für die Hilfe und die erklärugen. Nun habe ich wieder etwas dazu gelernt.


Vielen vielen Dank


Gruß Michael

---

Jetzt habe ich doch noch eine frage.

Kann man auch h, tm und me auf ein gesamtes Tabellenblatt anwenden?

Wenn ja wie würde das gehen?

Sorry sind doch zwei fragen geworden.


Gruß Michael

[EA1950]

Hallo,

Kann man auch h, tm und me auf ein gesamtes Tabellenblatt anwenden?

du kannst diesen Variablen einen Wert zuweisen, zB. wie bereits gehabt:
=LET(h;HEUTE(); tm;Termine!E2:E25000; ...)

Man wählt natürlich für die jeweiligen Namen solche aus, die etwas mit dem Wert zu tun haben, den sie erhalten.
Man soll ja die Formel auch einmal später (nach einem halben Jahr) noch möglichst leicht verstehen können, auch wenn man nicht mehr so total im jeweiligen Problem zu Hause ist.
Ich habe daher "h" deswegen gewählt, weil es an die HEUTE()-Funktion erinnert; ferner "tm" deswegen, weil sie (zumindest mich) an Termine erinnert. Ich hätte auch wählen können "dtH" (Datumswert von HEUTE()) oder "dtTm" für den Terminebereich.
Da man diesen Bezeichnern Werte mit unterschiedlichen Typen zuweisen kann, ist es auch oft ein beliebtes Verfahren, einen Typhinweis mit in den Bezeichner aufzunehmen (z.B. "dtH", "intJ": die sogenannte Ungarische Notation-Konvention).

Man kann einer solchen Variablen innerhalb einer LET-Funktion nur 1x einen Wert zuweisen. Versuchte man das ein 2. Mal, erhält man eine Fehlermeldung. Man kann die Variable, sobald sie durch Wertzuweisung erzeugt ist, jedoch mehrmals innerhalb ein und derselben LET-Funktion verwenden in einem Ausdruck.
Ich könnte zB. folgendes LET konstruieren:
=LET(h; HEUTE();  tm;Termine!$E$2:$E$25000; htm;VSTAPELN(h;tm); ....)
wodurch in der Variablen "htm" ein Array erzeugt wird, das zuoberst das HEUTE()-Datum hat, gefolgt von den Termindatumsangaben aus Termine!$E$2:$E$25000. Diese Variable "htm" enthielte damit 25000 Datumswerte.

Dem letzten Ausdruck in einer LET-Funktion darf aber kein Name vorausgehen, dem er zugewiesen werden könnte - einfach deshalb weil das der Ausdruck ist, den die LET-Funktion als Funktionswert zurückliefert.
In meinem Beispiel ist das die ZÄHLENWENNS(..)-Funktion, der kein Name vorausgeht, denn diese soll ja das Zählergebnis aus der LET-Funktion zurückliefern.

Siehe weiters folgenden deutschen Microsoft-Hilfe-Link (optisch ausnahmsweise gut aufbereitet, mit einem ergänzenden Video):
LET-Funktion - Microsoft-Support

[Blacky1980]

Hallo Anton
 
Soweit habe ich es verstanden. Ich dachte nur es gibt sowas auch was auch auf andere Tabellen-Bereichen anzuwenden geht.
 
Ich habe mich da mal versucht jetzt das Ganze zu erweitern, was auch funktioniert hat.


Wenn ich jetzt nur die vergangenen Termine vom laufenden Jahr haben möchte, habe ich es so gemacht.
 

Code:

=LET(h;HEUTE();j;JAHR(h);anf;DATUM(j;1;1); end;DATUM(j;12;31);tm;Veranstaltungen!$E$2:$E$25000; ZÄHLENWENNS(tm;">="&anf;tm;"<="&h))

 
 
Für alle Jahre davor habe ich es so gemacht 

Code:

=LET(h;HEUTE();j;JAHR(h);anf;DATUM(j;1;1);end;DATUM(j;12;31);tm;Veranstaltungen!$E$2:$E$25000;ZÄHLENWENNS(tm;"<"&h))

Ob es richtig kein Plan aber es funktioniert und Ergebnisse scheinen auch zu stimmen


Gruß Michael

[BoskoBiati]

Hi,

am meisten Sinn macht die LET-Formel, wenn man mehrfach vorkommende Terme nur einmal schreiben will.

Abgesehen davon, dass Deine Formel für dieses Jahr nur bis heute zählt ("<="&h), kann man das auch so schreiben:

Code:

=LET(j;JAHR(heute());anf;DATUM(j;1;1);
end;DATUM(j;12;31);tm;Veranstaltungen!$E$2:$E$25000;
ZÄHLENWENNS(tm;">="&anf;tm;"<="&end))

Für vergangene Jahre reicht aber auch das:

Code:

=Zählenwenn( Veranstaltungen!$E$2:$E$25000 ;"<1.1."&Jahr(heute())

Bei ZÄHLENWENN(S) kann man ruhig auch mit ganzen Spalten arbeiten.

Code:

=Zählenwenn( Veranstaltungen!$E:$E;"<1.1."&Jahr(heute())

[Blacky1980]

Danke Edgar