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!
[
attachment=46380]
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 |
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.
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.
Vielen Dank euch drei! Das ist genau wie ich es brauche.
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
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).
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.
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.