ZÄHLENWENN aber nicht doppelt für gleiches Datum

[TJ_09]

Hallo, ich bin leider kein Excel-Experte und hoffe daher hier Hilfe für meine Frage zu finden.

Ich habe eine Tabelle mit Temperaturmesswerten in halbstündlicher Auflösung und würde nun gerne die Tage zählen an denen die Minimumtemperatur <0°C ist, also Spalte C < 0. Heißt, nicht für jeden Zeitschritt, sondern nur einmal pro Tag zählen lassen, also im Prinzip, sobald ein negativer Wert gefunden wird zum nächsten Datum weiterspringen. 
Ich finde leider keine passende Formel dafür. 

Idealerweise würden diese Tage als Datum mir dann auch noch in einer Liste ausgegeben werden, das aber nur optional, falls es nicht kompliziert ist.

Ich hoffe, dass ist soweit verständlich. Vielen Dank für jede Hilfe im Voraus!

   

[WillWissen]

Hi,
bei deiner XL-Version, falls ich dein Anliegennicht missverstanden habe, so:

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D	E	F
1	Datum	Zeit	Temperatur[°C]
2	01.01.2003	00:30	-6,3		Datum
3	01.01.2003	01:00	-6		01.01.2003	28
4	01.01.2003	01:30	-5,6		02.01.2003	0
5	01.01.2003	02:00	-5,3		03.01.2003	0
6	01.01.2003	02:30	-4,9		04.01.2003	40
7	01.01.2003	03:00	-4,7		05.01.2003	48
8	01.01.2003	03:30	-4,5		06.01.2003	48
9	01.01.2003	04:00	-4,4		07.01.2003	48
10	01.01.2003	04:30	-4,3		08.01.2003	48
11	01.01.2003	05:00	-4,3		09.01.2003	48
12	01.01.2003	05:30	-4,3		10.01.2003	48
13	01.01.2003	06:00	-4,3		11.01.2003	48
14	01.01.2003	06:30	-4,2		12.01.2003	48
15	01.01.2003	07:00	-3,8		13.01.2003	34
16	01.01.2003	07:30	-3,7		14.01.2003	0
17	01.01.2003	08:00	-3,6		15.01.2003	0
18	01.01.2003	08:30	-3,5		16.01.2003	0

Zelle	Formel
E2	=EINDEUTIG(A:A)
F3	=ZÄHLENWENNS(A:A;E3;C:C;"<0")

Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

[BoskoBiati]

Hi,

z.B. so:



  bsp.xlsx (Größe: 89,97 KB / Downloads: 10)

[LCohen]

47 Einträge

=LET(xx;LAMBDA(XQuelle;[YAuswahl];LET(
XZähler;SEQUENZ(ZEILEN(XQuelle));
XSort;SORTIEREN(XQuelle;{1.2});
XBez;INDEX(XSort;;1);
XWert;INDEX(XSort;;2);
XVorIdent;VSTAPELN(FALSCH;WEGLASSEN(XBez;1)=WEGLASSEN(XBez;-1));
XErster;NICHT(XVorIdent);
XLetzter;NICHT(VSTAPELN(WEGLASSEN(XVorIdent;1);FALSCH));
XVon;XErster*XZähler;
XBis;XLetzter*XZähler;
XKumSum;SCAN(0;XWert;LAMBDA(a;c;a+c));
YBez;EINDEUTIG(XBez);
YVon;FILTER(XVon;XErster);
YBis;FILTER(XBis;XLetzter);
YAnz;YBis-YVon+1;
YKumSum;FILTER(XKumSum;XLetzter);
YSum;LET(x;VSTAPELN(0;YKumSum);WEGLASSEN(x;1)-WEGLASSEN(x;-1));
YMittelw;YSum/YAnz;
YMin;INDEX(XWert;YVon);
YMax;INDEX(XWert;YBis);
YMedian;(INDEX(XWert;(YVon+YBis)/2)+INDEX(XWert;(YVon+YBis+1)/2))/2;
Y;HSTAPELN(YBez;YVon;YBis;YAnz;YKumSum;YSum;YMittelw;YMin;YMax;YMedian);
z;WENN(WURDEAUSGELASSEN(YAuswahl);Y;SPALTENWAHL(Y;YAuswahl));
z))(SPALTENWAHL(A2:C4321;{1.3});{1.8});
FILTER(xx;SPALTENWAHL(xx;2)<0))

Die Formel ist so lang, weil Du auch mehrere andere Auswertungen mit ausgeben kannst, z.B. mit {1.8.7} zusätzlich den Mittelwert.

Die Formel ist so schnell, weil sie anders als bei WillWissen und Bosko nicht exponenziell verseucht ist.

Die Formel ist trotzdem noch etwas langsam, weil Du mit Pivot-Tabelle schneller zum Ziel kommst.

[LCohen]

Mit dem Link kannst Du Dir eine Funktion =PVT() erstellen. Dann ist der lange Code versteckt, und:

=SPALTENWAHL(LET(zz;A2:C4321;
xx;PVT(SPALTENWAHL(zz;{1.3});{1.8.7.4});
yy;PVT(SPALTENWAHL(FILTER(zz;INDEX(zz;;3)<0);{1.3});{1.8.7.4});
HSTAPELN(FILTER(xx;INDEX(xx;;2)<0);FILTER(yy;INDEX(yy;;2)<0)));{1.2.3.4.7.8})

ergibt dann zwei zusätzliche Spalten, nämlich Mittelwert und Anzahl nur der Minustemperaturen der Tage, die mindestens 1mal Minus haben.

Die Spalten davor zeigen hingegen Mittelwert und Anzahl aller Werte, also auch Pluswerte eingeschlossen, der Tage, die mindestens 1mal Minus haben.

Man beachte, dass PVT aufgrund der verschiedenen Aussagen 2mal ausgeführt wird - und es trotzdem sehr schnell geht.

[TJ_09]

Vielen Dank euch drei! Das ist genau wie ich es brauche.

[RPP63]

Moin!
Auch bei mir ist es eine einzige Formelzelle, die zweispaltig spillt.
Disclaimer: Auch sie ist "exponentiell verseucht"!

	A	B	C	D	E	F
1	Datum	Zeit	Temperatur[°C]		Datum	Anz_<0
2	01.01.2003	00:30	-6,3		01.01.2003	28
3	01.01.2003	01:00	-6		02.01.2003	0
4	01.01.2003	01:30	-5,6		03.01.2003	0
5	01.01.2003	02:00	-5,3		04.01.2003	40
6	01.01.2003	02:30	-4,9		05.01.2003	48
7	01.01.2003	03:00	-4,7		06.01.2003	48

Zelle	Formel
E2	=LET(anz;ANZAHL2(A:A); a;A2:INDEX(A:A;anz); c;C2:INDEX(C:C;anz); x;SORTIEREN(EINDEUTIG(a)); y;ZÄHLENWENNS(a;x;c;"<0"); WAHL({1.2};x;y))

Und wenn man den Bereich nicht dynamisch wie oben ermittelt, wird die Formel kürzer (aber sowas liegt mir eher nicht):
=LET(x;EINDEUTIG(A2:A4321);
 y;ZÄHLENWENNS(A2:A4321;x;C2:C4321;"<0");
 WAHL({1.2};x;y))

Gruß Ralf

[LCohen]

Da es nur 47 Tage mit Minusgraden gibt, hätte bei der verseuchten Lösung ein anfängliches FILTER<0 noch mal die Hälfte an Rechenzeit gespart ;) Dann ginge es außerdem auch mit ZÄHLENWENN statt ZÄHLENWENNS (ein Mini-Vorteil).

[RPP63]

Ob es etwas bringen würde, wenn statt
innerhalb einer ZÄHLENWENNS() mittels Bereich;"<0"
außerhalb einer ZÄHLENWENN() mittels FILTER(Bereich;Bereich<0)
gefiltert wird, halte ich für eine akademische Diskussion.

[BoskoBiati]

Hi,

super, was man mit den neuen Funktionen machen kann. Allerdings halte ich das, was da teilweise an Formelgedöns entsteht, eher für akademischen Unfug als für  Otto-Normalverbraucher anwendbare Geschichten. Die Formel von LCohen kann der normale User nur kopieren, ohne sie zu verstehen. Da hört bei mir der Spaß auf.
Vielleicht bin ich auch zu alt, um meinen Kopf mit sowas zu belasten. Spillbare Formeln mögen zwar das Erstellen einer Auswertung vereinfachen, aber dafür ist der Aufwand zum Konstruieren der Formel deutlich höher.

Nur meine Meinung dazu.