Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1636to1640
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Berechnung von Hitzeindizes

Berechnung von Hitzeindizes
05.08.2018 18:54:08
Hitzeindizes
Liebes Forum,
ich habe eine paar Klimareihen täglicher Maximaltemperaturen die ich dazu nutzen möchte, um die größten Hitzeepisoden dieser Reihen zu identifizieren. Dazu möchte ich folgende Definition verwenden: eine Episode startet nur, wenn an mindestens 3 Tagen Maximaltemperaturen von mindestens 30,0°C erreicht werden. Sie dauert solange an, wie die Mitteltemperatur der Episode nicht unter 30,0°C sinkt. Sie endet ebenfalls, wenn ein einzelner Tag den Wert von 25,0°C unterschreitet. Letztlich möchte ich die Temperatursumme über 30,0°C einer Episode berechnen und damit die intensivste Episode bestimmen.
Nun überlege ich bereits seit Stunden, wie ich diese Aufgabe in Excel umsetzen kann. Die Definition des Auslösekriteriums sowie der Unterschreitung der 25°C ist kein Problem, aber wie beziehe ich mich immer wieder zurück zum Auslösekriterium und wie berechne ich den Mittelwert einer nicht konstanten Anzahl von Tagen? Ist dies ohne VBA lösbar? Ich habe probiert über verschiedene Excel-Blätter auf eine Lösung zu kommen, bin aber auch daran gescheitert. Gibt es Ideen?
Für konstruktive Rückmeldungen wäre ich sehr dankbar.
Beste Grüße,
Andreas

30
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: (D)eine Beispieldatei wäre sinnvoll ...
05.08.2018 19:18:48
neopa
Hallo Andreas,
... und zwar mit Daten für etwa 14 Tage, die auszuwerten sind und Erklärung/Kennzeichnung ausgewählter (Tages-?)Temperaturen, die Du für die Mittelwertbildung herangezogen haben möchtest. Denn eine Tagesmittelwerttemperatur dürfte mE wohl selbst in der aktuellen Hitzeperiode kaum die 30 Grad überschreiten.
Die gewünschte Ermittlung ist auch ohne VBA möglich. Aber ich werde heute wohl kaum noch dazu kommen.
Gruß Werner
.. , - ...
AW: (D)eine Beispieldatei wäre sinnvoll ...
05.08.2018 21:52:23
Andreas
https://www.herber.de/bbs/user/123153.xlsx
Hallo Werner,
oben habe ich Dir den Link zur Beispieldatei hinkopiert. Sie umfasst einen Datensatz einer Station als Beispiel - man kann daraus gern 14 Tage oder mehr als Beispiel herausnehmen. Ich möchte wie geschrieben Maximaltemperaturen auswerten, wie oben beschrieben. Mein dritter Satz war allerdings etwas missverständlich, ich möchte nicht Mitteltemperaturen, sondern Mitteltemperaturen des Temperaturmaximums verwenden. Dies bezieht sich auf alle Daten, ich verwende nur Daten des Temperaturmaximums.
Ich glaube, dass die Umsetzung nicht trivial sein wird (lasse mich aber gern eines Besseren belehren). Wenn es noch Fragen gibt wie ich meine Fragestellung genau gemeint hab geb ich gern noch weitere Infos.
Beste Grüße und vielen Dank für Deine Unterstützung,
Andreas
Anzeige
AW: die Bedingungen sind mE zu hinterfragen ...
06.08.2018 19:27:07
neopa
Hallo Andreas,
... und zwar insbesondere in der von Dir angegebenen Kombination. Denn damit werden Perioden wie z.B.: 29, 31, 32, 29, 31, 33, 28 nicht als Hitzeperiode ermittelt, obwohl die Durchschnittstemperatur ständig über 30 Grad liegt.
Deshalb sollte mE geprüft werden, ob "an mindestens 3 aufeinander folgenden Tagen der Durchschnitt der Maximaltemperaturen mindestens 30,0°C" gewährleistet ist.
Gruß Werner
.. , - ...
AW: die Bedingungen sind mE zu hinterfragen ...
06.08.2018 19:45:06
Andreas
Hallo Werner,
danke für Deine Antwort. Die Definition macht durchaus Sinn, denn es geht hier um Hitzewellen mit deutlichen Auswirkungen auf die betroffenen Menschen, die häufig v.a. durch aufgeheizte Räume entstehen. Daher gibt es ein recht starkes Auslösekriterium, welches erlaubt im Nachhinein auch hitzeschwächerere Tage mit einzubeziehen. Deine Definition ist natürlich auch auswertbar, allerdings etwas schwächer definiert. Über Variationen der 30°-Schwelle (z.B. über Perzentile) möchte ich die bisherige Definition auch variieren, mir geht es nur erst einmal um die technische Umsetzbarkeit meiner Idee...
viele Grüße, Andreas
Anzeige
AW: hätte weiterhin Bedenken ...
06.08.2018 19:56:45
neopa
Hallo Andreas,
... dazu zum Vergleich zwei Beispiele gegenübergestellt:
1.) 29,30,30,30,29 und 2.) 29,33;34,29,29
Mit Deinen Bedingungen wird nur 1.) als Hitzeperiode berücksichtigt. Die 2.) nicht aber die Belastung dürfte aber mE bei dieser höher als bei der 1.) sein.
Gruß Werner
.. , - ...
AW: hätte weiterhin Bedenken ...
06.08.2018 20:46:06
Andreas
Hallo Werner,
ich verstehe und Dein Beispiel macht Sinn, aber es ist halt so mit Definitionen, dass sie niemals perfekt sind. Ich werd mal verschiedene durchspielen und überlegen... Momentan beschäftige ich mich erstmal mit der technischen Umsetzbarkeit - wenn ich die verstanden habe hab ich mehr Möglichkeiten an den Definitionen zu spielen. Danke!
Anzeige
AW: gerade Deine neu eingestellte Datei zeigt ...
07.08.2018 11:27:04
neopa
Hallo Andreas,
... doch die Problematik Deiner Bedingungsdefinition auf. Auf die hatte ich Dich ja schon hingewiesen.
Sieh mal: Ersetze z.B. die 32 in B17 durch eine 31 und lupos Formel ergibt wieder das von Dir gewünschte auch in C3:C8 aber mE noch nicht das was realitätsnahe wäre. Aber das liegt dann nicht an lupos Formel sondern eben an Deinen Bedingungsvorgaben.
Deine Argumentationen für Deine Bedingungsdefinition überzeugten mich bisher nicht.
Nachfolgend eine Formel, die Deine beiden Bedingungen D2 und D3 voll berücksichtigt und damit im Ergebnis die "Hitzeperioden" so ermittelt, wie ich sie momentan sehe.
Formel E2 nach unten kopiert (E1 leer oder 0).
 ABCDE
1DatumMaxBedF  
201.07.201821024,20
302.07.201830029,921
403.07.2018340 2
504.07.2018470 3
605.07.2018320 4
706.07.2018290 5
807.07.2018250 6
908.07.2018240 0
1009.07.2018240 0
1110.07.2018250 0
1211.07.2018320 1
1312.07.2018310 2
1413.07.2018290 3
1514.07.2018304 4
1615.07.2018313 5
1716.07.2018322 6
1817.07.2018281 7
1918.07.2018280 0
2019.07.2018300 1
2120.07.2018300 2
2221.07.2018290 0
2322.07.2018303 1
2423.07.2018302 2
2524.07.2018301 3
2625.07.2018290 0
2726.07.2018290 0

Formeln der Tabelle
ZelleFormel
C2{=MIN(VERGLEICH(WAHR;B3:B101<D$2;); MAX(WENNFEHLER(C1-1;); (MIN(B2:B4)>=D$3)*(MIN(B1:B3)<D$3)*MIN(WENN(TEILERGEBNIS(1;BEREICH.VERSCHIEBEN(B2;;;ZEILE($1:$99)))<D$3;ZEILE($1:$99)-1))))}
E2=WENN((MITTELWERT(INDEX(B:B;WENNFEHLER(MAX(VERWEIS(9;1/(B$2:B2>=D$3)/(B$1:B1<D$3); ZEILE(B$2:B2)); WENNFEHLER(VERWEIS(9;1/(B$2:B2<D$2); ZEILE(B$2:B2)); 2)); ZEILE())):B2)>=D$3)*(B2>D$2)=0;0;E1+1)
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen

Bedingte Formatierungen der Tabelle
ZelleNr.: / BedingungFormat
A21. / Formel ist =$E1Abc
B21. / Formel ist =$E1Abc
C21. / Formel ist =C1Abc


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Damit werden zwar auch Ein- und Zweitagesperioden ermittelt, doch stört dies?
Auf jeden Fall ermittele ich so eine mE realitätsnahe Perioden. So auch die ab den 11.7. und nicht erst ab dem 14.7. Dies deshalb, da der Durchschnitt der Maximal-Temperaturen ab dem 11.7. auch am 13.7 den Grenzwert in D3 überschreitet.
Das die so ermittelte Periode mit dieser Auswertungsformel am 18.7 und 25.7 unterbrochen ist, liegt daran, dass Deine Grenzwert D2 für die Ermittlung der Durchschnittstemperatur strikt eingehalten wurde, wonach diese immer erst ab einen Tag ermittelt wird, an dem die Temperatur D2 überschritten wurde.
Dies hab ich jetzt mal so belassen, obwohl ich auch dies etwas anders sehe. Denn der Durchschnitt der Maximal-Temperaturen liegt ab dem 11.7. bis zum 25.7. konstant über dem von Dir vorgegebenen Grenzwert in D3. Somit wäre diese Periode sogar nicht nur wie hier ermittelt max 7 sondern 15 Tage!
Auf Grundlage vorliegender Auswertung, lässt sich auch eine realitätsnahe Ermittlung der max Dauer einer solchen Periode je Jahr und auch der mit der größten Maximal-Temperatursumme ermitteln. Aber dazu musst Du Dich zunächst entscheiden, wie Du auswerten willst.
Gruß Werner
.. , - ...
Anzeige
AW: gerade Deine neu eingestellte Datei zeigt ...
07.08.2018 14:22:52
Andreas
Hallo Werner,
vielen Dank für Deine Antwort! Ich sehe gerade, dass ich wohl bezogen auf das Niveau dieses Forums doch eher Excel-Laie als "gut" bin, von Profi ganz zu schweigen, da es mir wirklich schwer fällt, die Formeln im Detail nachzuvollziehen. Da bräuchte ich mal einen Intensiv-Excel-Kurs auf diesem Niveau... Ich versuche mich aber tapfer zu schlagen...
Ich habe noch ein paar Rückfragen/Diskussionsbedarf zu einigen Deiner Aussagen:
Damit werden zwar auch Ein- und Zweitagesperioden ermittelt, doch stört dies?
--- Ja dies stört sogar sehr, da es im Hitzeperioden geht, und diese sollen erst ab 3 Tage über z.B. 30°C ausgelöst werden. Dieser Faktor ist für mich essentiell. Gerade auch, wenn Anzahl der Hitzeperiodentage und die Temperatursummen mal über ein Jahr aufsummiert werden und nicht nur die größte Periode verwendet wird.
Auf jeden Fall ermittele ich so eine mE realitätsnahe Perioden. So auch die ab den 11.7. und nicht erst ab dem 14.7. Dies deshalb, da der Durchschnitt der Maximal-Temperaturen ab dem 11.7. auch am 13.7 den Grenzwert in D3 überschreitet.
--- das ist in diesem Fall (32, 31, 29) sicherlich sehr sinnvoll, aber wenn die Abfolge z.B. (33, 25, 33) wäre würde die Hitzeperiode ebenfalls starten, und dies halte ich für problematisch. Wie auch immer, ich kann erst einmal damit leben.
Das die so ermittelte Periode mit dieser Auswertungsformel am 18.7 und 25.7 unterbrochen ist, liegt daran, dass Deine Grenzwert D2 für die Ermittlung der Durchschnittstemperatur strikt eingehalten wurde, wonach diese immer erst ab einen Tag ermittelt wird, an dem die Temperatur D2 überschritten wurde. Dies hab ich jetzt mal so belassen, obwohl ich auch dies etwas anders sehe. Denn der Durchschnitt der Maximal-Temperaturen liegt ab dem 11.7. bis zum 25.7. konstant über dem von Dir vorgegebenen Grenzwert in D3. Somit wäre diese Periode sogar nicht nur wie hier ermittelt max 7 sondern 15 Tage!
--- Diese Unterbrechnungen sollten nicht sein, da die auslösende Temperatur definitiv immer D3 sein muss (nicht D2)!. Damit startet die Periode nicht am 10. sondern 11.7., da die Durchschnittstemperatur immer über D3 liegt. Auch nach meiner Berechnung würde die Periode also genau vom 11. bis 25. Juli gehen, wenn ich mich davon überzeugen lasse, dass auch ein 3-Tages-Durchschnitt über 30°C (statt 3-Tages-Maxima über 30°C) ausreichen.
Hab ich mich klar ausgedrückt und es ist klargeworden, welche Intention ich verfolge? Ich denke was die Hitzeperiodentage betrifft sind wir doch auf einem guten Weg. Vielen herzlichen Dank für all die bisherige Hilfe und Diskussion.
Beste Grüße, Andreas
Anzeige
AW: hierzu ...
07.08.2018 15:01:48
neopa
Hallo Andreas,
... die Auswertung mit den Dir bisher bereitgestellten Formeln ist aus meiner Sicht nur eine "Zwischenauswertung", denn damit ist lediglich eine visuelle Ergebnisdarstellung z.B. über bedingte Formatierung oder normale Sicht möglich. Insofern stören die damit evtl. ermittelten Ein- und Zweitagesperioden überhaupt nicht, weil diese bei der "Endauswertung" formelmäßig "ausgefiltert" werden können.
Deine Bedenken bzgl. einer "Periode" aus (33, 25, 33) kann ich schon eher nachvollziehen. Eine Möglichkeit derartiges nicht als "Periode" anzuerkennen wäre z.B. dass man einen zusätzlichen "Grenzwert" definiert für den Fall das ein solcher Wert wenn unter D3 z.B. max 10% unter dem des Vortagswertes abfallen dürfte.
Das die auslösende Temperatur definitiv immer D3 (nicht D2) ist, ist in meiner bisherigen Formel bereits gewährleistet. Ich hatte mich diesbzgl. lediglich im Text verschrieben.
Wenn Du Dich entschieden hast, dass auch ein 3-Tages-Durchschnitt über den D3-Wert (statt 3-Tages-Maxima über den D3-Wert) ausreicht, so lass es uns wissen. Dann würde ich nochmal über (m)eine Formelanpassung nachdenken. Ob dass allerdings heute oder morgen wird, kann und will ich nicht zusagen, denn schließlich liegen wir aktuell zur Zeit mit 35° schon weit über 10% über dem Grenzwert D3 ;-)
Gruß Werner
.. , - ...
Anzeige
AW: hierzu ...
07.08.2018 16:26:06
Andreas
Hallo Werner, hallo Lupo,
vielen Dank für Eure Unterstützung und die intensive Diskussion. Beides - zusammen mit der aktuellen Hitze - hat nun auch dazu geführt, dass ich mir nach einem längeren In-mich-gehen eine in meinen Augen robuste Definition überlegt habe (Nr. 1 & 2), die ich gern anwenden würde. Gleichfalls habe ich mir zwei Auswertemethoden überlegt, die sowohl Hitzewellenlänge- (Nr. 3) als auch intensität (Nr. 4) berücksichtigen.
1. Auslöse: Eine Hitzewelle wird ausgelöst, wenn an einem Tag eine Maximaltemperatur von 30,0°C überschritten wurde sowie beginnend an diesem Tag an 3 Tagen ein Mittelwert des Temperaturmaximums von 30,0°C nicht unterschritten wird.
2. Andauer: Eine Hitzewelle dauert so lange an, wie ein Mittelwert der Maximaltemperatur von 30,0°C nicht unterschritten wird. Allerdings beendet ein vorheriges Temperaturmaximum unter 25,0°C die Hitzewelle.
3. Hitzewellenlänge: Ausgewertet wird die Zeitreihe der längsten jährlichen Hitzewellen eines Standortes (Andauer in Tagen über 30,0°C).
4. Hitzewellenintensität: Ausgewertet wird die Zeitreihe der intensivsten jährlichen Hitzewellen eines Standortes (Temperatursumme in K über 30,0°C).
Es ist durchaus möglich, dass die gegenwärtige Hitzeepisode an vielen Stellen im Norden, Westen und Osten Deutschlands die bisherigen Rekordhalter (vermutlich zumeist aus den Jahren 2003 und 1994) schlägt. In Süddeutschland wird das Jahr 2003, ggf. auch das Jahr 2015 vermutlich nicht zu fassen sein.
Wenn ihr mir bei der Berechnung der Hitzewellenintensität (Nr. 4) aufgrund der Definition von Nr. 1-2 behilflich sein könntet wäre ich Euch sehr dankbar. Die Hitzewellenlänge (Nr. 3) würde ich anhand der Tage mit Temperatursummen über 0K selbst bestimmen können. Die einzige Fehlerquelle hier würden noch Start- und Folgetage mit genau 30,0°C sein, aber dies würde nur sehr geringe Unsicherheiten erzeugen.
Und keine Eile. Ich bin selbst die nächsten Tage sehr beschäftigt, so dass ich nicht immer innerhalb weniger Stunden werde antworten können.
Beste Grüße,
Andreas
Anzeige
AW: dazu noch nachgefragt ...
07.08.2018 17:30:04
neopa
Hallo Andreas,
... Deine Angaben zur Hitzewellenlänge unter 3. und 4. widersprechen teilweise Deinen Angaben sowohl aus 1. als auch aus 2.
Meinst Du: Die längste Hitzewellenlänge (3.) bzw. die intensivste, die sich aus den Bedingungen von 1. und 2. ergibt?
Und meinst Du zu 4. gar die die Summe aller Maimal-Temperaturen evtl. mehrerer Hitzeperioden die alle den Bedingungen zu 1. und 2. genügen? Und analog die Auflistung aller Hitzeperioden?
Gruß Werner
.. , - ...
AW: dazu noch nachgefragt ...
08.08.2018 11:35:45
Andreas
Hallo Werner,
vielen Dank für Deine Antwort. Was ich meine ist folgendes: Nr. 1 und 2 sind die generellen Bedingungen für eine Hitzewelle. Daraus kann man dann sowohl die längste Hitzewelle als auch die intensivste Welle berechnen. Also ja, ich meine sowohl die längste Hitzewellenlänge (3.) als auch die intensivste Hitzwelle, die sich aus den Bedingungen von 1. und 2. ergibt.
Was 4. betrifft meine ich nur die Summe, die in einer! Hitzeperiode erreicht wird. Natürlich ist es möglich dann am Jahresende alle Summen verschiedener Hitzewellen zusammenzurechnen, aber dies soll hier a) kein Thema sein da erstmal nicht im Fokus und b) leicht zu berechnen. Gleiches gilt für die Summe aller Hitzeperiodentage.
Hab ich mich verständlich und klar ausgedrückt?
viele Grüsse und frohes Schwitzen!
Andreas
Anzeige
AW: zu meinem momentanen Lösungsvorschlag ...
08.08.2018 16:35:05
neopa
Hallo Andreas,
... bedurfte es nur einer kleinen Anpassung meiner bisher eingestellten Formel für die Hilfsspalte. Und natürlich darauf basierend dann noch die eigentliche Auswertung. Die hab ich so vorgenommen wie schon angedeutet und zusätzlich hab ich mittels bedingter Formatierung auch in der Hilfsspalte die Ein- und Zweitage-Perioden "unsichtbar" gemacht. Das erschien mir die einfachste Lösung zu sein.
Die unten stehenden Datenwerte hab ich bewusst etwas abgeändert, um damit auch aufzuzeigen, dass 1. diese die Ein- und Zweitage-Perioden "unsichtbar" sind und zweitens auch, dass gleichlange "längste" Perioden in einem Jahr gelistet werden. Die Wahrscheinlichkeit, dass eine auch nur um einen Tag kürzere Periode die größte Temperatursumme hat dürfte mE weit unter 1%% liegen.
Die Formel C3 bis zum Datenende kopieren und die Formeln F3:I3 vielleicht max 3 Zeilen.
Das der bedingten Formatierung zugewiesen benutzerdefinierte Zahlenformat (wird von der Excel Jeanie nicht korrekt dargestellt) ist: ;;; (also einfach nur "unsichtbar" gemacht).
 ABCDEFGHI
1DatumMaxHSpGrenzw. längste(n) Hitzeperiode(n)
201.0721 24,20 vonbisTageTemp.-Summe
302.0730129,92 11.0716.076193
403.07342  19.0724.076194
504.07313      
605.07324      
706.07275      
807.0724       
908.0732       
1009.0732       
1110.0724       
1211.07301      
1312.07312      
1413.07293      
1514.07334      
1615.07385      
1716.07326      
1817.0724       
1918.0728       
2019.07301      
2120.07312      
2221.07293      
2322.07354      
2423.07365      
2524.07336      
2625.0724       
2726.0729       
28         

Formeln der Tabelle
ZelleFormel
C3=WENN((MITTELWERT(INDEX(B:B;WENNFEHLER(MAX(VERWEIS(9;1/(B$2:B3>=D$3)/(B$1:B2<D$3*(C$1:C2=0)); ZEILE(B$2:B3)); WENNFEHLER(VERWEIS(9;1/(B$2:B3<D$2); ZEILE(B$2:B3)); 2)); ZEILE())):B3)>=D$3)*(B3>D$2)=0;0;C2+1)
F3=WENN(H3="";"";AGGREGAT(15;6;A$2:A$367/(C$2:C$367=H3); ZEILE(A1))-H3+1)
G3=WENN(H3="";"";F3+H3-1)
H3=WENN((KGRÖSSTE(C:C;ZEILE(A1))=MAX(C:C))*(KGRÖSSTE(C:C;ZEILE(A1))>2); MAX(C:C); "")
I3=WENN(H3="";"";SUMMEWENNS(B:B;A:A;">="&F3;A:A;"<="&G3))

Bedingte Formatierungen der Tabelle
ZelleNr.: / BedingungFormat
C31. / Formel ist =(C1=1)*(C3=0)+(C1=2)*(C2=0)Abc


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...
Anzeige
AW: zu meinem momentanen Lösungsvorschlag ...
08.08.2018 18:40:09
Andreas
Hallo Werner,
vielen herzlichen Dank für Deine Mühe, ich kann da garnicht genug danken!!!
Ich habe Deine Formel am Beispiel getestet und nachvollzugen, und danach auf eine Zeitreihe mit täglichen Daten über ca. 70 Jahre angewendet und bin fast am Ziel - soll heißen, ich kann den Rest nun fast alleine machen. Eine Bitte hätte ich noch - wenn es irgend möglich ist, müssten die 1- und 2-Tagesperioden noch aus der Formel C3 verschwinden. Denn ich würde die Formel gern in ein anderes als hier vorgeschlagenes Auswertesystem integrieren - dafür reicht aber ein Ausblenden über bedingte Formatierung nicht aus, da ich gern über 3 verschiedene Excel-Blätter alle eine Hitzeperiode charakterisierenden Eigenschaften auswerten würde und darüber auch die Möglichkeit haben möchte, Jahressummen zu bilden und weitere Auswertungen zu machen. Könntest Du diese Anpassung noch einbauen, oder ist das zu viel zusätzliche Arbeit?
Eine kurze inhaltliche Anmerkung: Du schriebst "Die Wahrscheinlichkeit, dass eine auch nur um einen Tag kürzere Periode die größte Temperatursumme hat dürfte mE weit unter 1%% liegen." Hier muss ich inhaltich widersprechen - das kommt sicher recht häufig vor. Denn stell Dir mal die folgenden 2 Perioden vor: P1 30, 30, 31, 29, 32, 28: Summe über 30°C = 3, Länge = 6; P2 34, 36, 37: Summe über 30°C = 17, Länge = 3. Die von Dir vorgestellte Auswertung der längsten Hitzeperioden ist schön, ich würde hier aber einen anderen Ansatz über mein eigenes Auswertesystem bevorzugen, so dass eine aufwendige Anpassung in den Spalten F bis I nicht notwendig ist.
Ich könnte meine Auswertungen als Beispiel-Zeitreihengrafik (PDF etc.) sowohl für Hitzeperiodenlänge als auch -intensität später zur Verfügung stellen, wenn hier inhaltliches Interesse besteht. Mein dafür verwendetes Excel-Auswertesystem ist formelmäßig allerdings sicher für dieses extrem hochkarätige Forum nicht so interessant.
Vielen Dank noch einmal für die unschätzbare Hilfe hier, die ich bisher erlebt habe.
Mit allerbesten Grüßen,
Andreas
AW: jetzt klar, was mit Summe über 30° gemeint ...
09.08.2018 10:39:24
neopa
Hallo Andreas,
... und zwar bzgl. der Temperatursumme über dem Grenzwert. Du möchtest nicht deren sondern "nur" die Summe der (Differenz-)Temperaturen über den Grenzwert ermitteln. Dann ist es natürlich möglich, dass z.B. die kürzeste Hitzeperiode, die aus Deiner Sicht intensivste ist. Um das zu verdeutlichen hab ich nachfolgend Beispieltemperaturwerte nochmals etwas abgeändert.
Nachfolgend hab die Auswertungsformel der Hilfsspalte komplett überarbeitet, so dass nun keine Ein- und Zweitagesperioden mehr "gelistet" werden. Dabei habe ich festgestellt, dass mit der neuen Formel auch ein bisher verdeckter Auswertungsfehler der alten Formel korrigiert ist (wenn wie jetzt in B4 nur noch 29° steht). Es bedarf nun keiner bedingten Formatierung zum Ausblenden unerwünschter Ergebnisse mehr und die Formel ist auch wieder einfacher geworden.
Jedoch geh die Formel davon davon aus, dass Dein 1. auszuwertenden Temperaturwert (bei Dir ja der 01.01. oder?) den Grenzwert in D3 mit großer Wahrscheinlichkeit nicht überschreitet. Wenn doch könntest Du (z. B. für Auswertung einer anderen Region) auch das einfach dadurch berücksichtigen, indem nach der Überschriftszeile eine (ausblendbare) Leerzeile eingefügt wird.
Der Vollständigkeit halber hab ich auch meine Gesamtauswertung in F:I auch entsprechend angepasst.
 ABCDEFGHI
1DatumMaxHSpGrenzw. Hitzeperiode(n)
201.0721 24,20 vonbisTageSum über D3
302.0730129,92 11.0716.0763,32
403.07292  24.0729.0764,32
504.07313  02.0706.0753,24
605.07324  19.0722.0747,32
706.07285    0 
807.0724     0 
908.0732       
1009.0732       
1110.0724    
1211.07301      
1312.07312      
1413.07293      
1514.07314      
1615.07295      
1716.07316      
1817.0724       
1918.0728       
2019.07301      
2120.07332      
2221.07323      
2322.07324      
2423.0724       
2524.07301      
2625.07312      
2726.07293      
2827.07314      
2928.07325      
3029.07296      
3130.0726       
3231.0724       
33         

Formeln der Tabelle
ZelleFormel
C3=((B3>D$3)*(C2=0)*(MIN(B4:B5)>D$2)*(MITTELWERT(B3:B5)>D$3))+(C2=1)*(B4>D$2)*(MITTELWERT(B2:B4)>D$3)*2+(B3>D$2)*(C2>1)*(MITTELWERT(INDEX(B:B;VERWEIS(9;1/(C$1:C2=0); ZEILE(C$1:C2))+1):B3)>D$3)*(C2+1)
F3=WENN(H3=0;"";AGGREGAT(15;6;A$2:A$367/(C$2:C$367=H3)/(C$3:C$368=0); ZÄHLENWENN(H$3:H3;H3))-H3+1)
G3=WENN(H3=0;"";F3+H3-1)
H3=AGGREGAT(14;6;C$1:C$367/(C$2:C$368=0); ZEILE(A1))
I3=WENN(H3=0;"";SUMMEWENNS(B:B;A:A;">="&F3;A:A;"<="&G3;B:B;">"&D$3)-ZÄHLENWENNS(A:A;">="&F3;A:A;"<="&G3;B:B;">"&D$3)*D$3)

Bedingte Formatierungen der Tabelle
ZelleNr.: / BedingungFormat


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...
AW: jetzt klar, was mit Summe über 30° gemeint ...
09.08.2018 19:05:08
Andreas
Hallo Werner,
vielen herzlichen Dank! Ich habe gerade etwas probiert - die Testdatei funktioniert einwandfrei, in meinem eigenen System gibt es noch Probleme. Ich möchte im hinteren Teil der Formel C3 den Teil B:B sowie B3 auf ein anderes Tabellenblatt beziehen (so wie im vorherigen Teil der Formel auch), und hier kommt ein Fehler #Name bzw. #Wert. Die Umstellung des vorderen Teils klappt problemlos. Ich muss da wohl mal mit mehr Zeit rangehen und schauen, woran es liegt dass das nicht funktioniert - ebenso möchte ich die Formeln der weiteren Zeilen in mein System übertragen. Leider bin ich momentan unterwegs und nicht in der Lage zusammenhängend zu arbeiten und in Ruhe zu probieren, sobald ich dies getan habe, melde ich mich wieder. Ich hoffe es ist OK, den Thread solange offen zu lassen.
Beste Grüße, Andreas
AW: thread dürfte am WE schon archiviert sein ...
09.08.2018 20:11:19
neopa
Hallo Andreas,
... das bedeutet, dass man auf diesen thread vorwiegend noch lesend Zugriff hat, man aber normalerweise keinen neuen Beitrag mehr verfassen kann ((gewusst wie, geht es noch ca. 6 Tage länger).
Entweder Du machst dann einen neuen thread auf und verlinkst auf diesen hier: https://www.herber.de/forum/archiv/1636to1640/t1637672.htm und schreibt in die Betreffzeile des neuen thread einen entsprechenden Text (z.B.: "@neopa, Fortsetzungthread ..."), der mir dann auch auffällt.
Da mich aber Dein Anliegen interessiert hat und noch tut, biete ich Dir an, dass Du mich auch direkt anschreiben kannst, wenn Du das möchtest. Meine Mailadresse bekommst Du, wenn Du in eine leere Standardexcelzelle folgende Formel schreibst: =WECHSELN("neopaCode";"Co";ZEICHEN(64)&"email.")
Solltest Du mir eine Mail senden, dann schreibe in den Betreff der Mail bitte: herber-forum thread: Hitzeperioden (damit ich die Mail darüber aus meinem Spamordner herausfiltern kann).
Wenn Du eine Mail abgesendet hast und Du noch die Möglichkeit haben solltest in Deinem thread hier einen Beitrag zu verfassen, dann schreibe bitte hier, dass Du eine Mail versendet hast.
Bis in Bälde dann.
Gruß Werner
.. , - ...
AW: thread dürfte am WE schon archiviert sein ...
09.08.2018 20:32:59
Andreas
Hallo Werner,
vielen herzlichen Dank für Dein Angebot, welches ich später (siehe unten) noch annehmen werde.
Ich konnte es nicht lassen und habe doch noch weiter versucht, meine Datei zum Laufen zu bringen - und prinzipiell hat das auch durch eine Beschränkung der B:B auf B$2:B$367 geklappt. Aber: die Ergebnisse stimmen leider in meiner Auswertetabelle nicht. Ich lade meine entschlackte Datei mit zwei Tabellen einmal hoch, mit dem Beispieljahr 1949. Schau mal auf den August. Hier werden die Perioden nicht richtig berechnet, obwohl die Formel stimmen sollte. Ganz am Anfang im Januar (zu Testzwecken) ist die Periodenberechnung richtig. Hast Du eine Ahnung, woran das liegen könnte? Ich habe in meiner Testdatei die Grenzwerte 30/25 verwendet.
Datei: https://www.herber.de/bbs/user/123241.xlsx
Wenn ich mein System, ich denke irgendwann nächste Woche, zum Laufen gebracht habe, lasse ich Dir eine Auswertung zukommen. Dürfte von den Ergebnissen her spannend werden, zumal die 2018er Hitzewelle ja irgendwann zuende gehen sollte.
viele Grüße, Andreas
AW: wahrscheinlich ist es günstig ...
10.08.2018 10:04:58
neopa
Hallo Andreas,
... Du schaust Dir das kommende Woche noch einmal in Ruhe an.
Entweder hast Du mir nicht Deine aktuellste Datei gesendet oder hast meine aktuellsten Formellösungsvorschlag (Beitrag von 09.08.18 10:39) übersehen, denn in Deiner hier gesendeten Datei in A-HW!!BC ist noch der alte eingesetzt.
Auch Deine weitere Auswertung ist wohl nochmal zu überdenken. Dazu können wir uns aber auch nächste Woche noch verständigen.
Gruß Werner
.. , - ...
AW: wahrscheinlich ist es günstig ...
10.08.2018 19:13:05
Andreas
Hallo Werner,
ok, ich melde mich nächste Woche per Email, wenn ich mehr Ruhe habe.
Bis dahin, vielen Dank und viele Grüße!!!
Andreas
P.S.: hab den Thread nicht mehr unter die offenen Fragen genommen
AW: dann bis in Bälde owT
11.08.2018 09:02:58
neopa
Gruß Werner
.. , - ...
AW: dann bis in Bälde owT
14.08.2018 19:26:39
Andreas
Email versendet. Gruß Andreas
Fehlerkorrektur 1
06.08.2018 09:49:05
lupo1
Richtig ist
C2: {=MIN(VERGLEICH(WAHR;B3:B101&lt25;);MAX(WENNFEHLER(D1-1;);(MIN(B2:B4)&gt=30)*(MIN(B1:B3)&lt30)*MIN(WENN(TEILERGEBNIS(1;BEREICH.VERSCHIEBEN(B2;;;ZEILE($1:$99)))&lt30;ZEILE($1:$99)-1))))} mit Nr. 5
statt
{=MAX(WENNFEHLER(C1-1;);(MIN(B2:B4)&gt=30)*(MIN(B1:B3)&lt30)*MIN(WENN(TEILERGEBNIS(1; BEREICH.VERSCHIEBEN(B2;;;ZEILE($1:$99)))&lt30;ZEILE($1:$99)-1))) }
Die 99 in der (nunmehr zu korrigierenden) Datei ist auch in B3:B101 entsprechend anzupassen, bei 999 also auf B3:B1001.
Fehlerkorrektur 2: C1 ok statt D1 false i.d.Formel
06.08.2018 09:53:18
lupo1
AW: Fehlerkorrektur 2: C1 ok statt D1 false i.d.Formel
06.08.2018 18:03:47
Andreas
Hallo Lupo,
vielen herzlichen Dank für Deine Mühe!!!
Die Excel-Datei hilft mir schon sehr weiter! Ein Fehler ist momentan noch drin (1), und dann hätte ich noch ein paar weitere Fragen (2-4), um meine Analysen zu optimieren:
1) Woher weiß die Formal, dass 25 die untere Grenze ist? Ich habe keine 25 als unteren Grenzwert in der Formel gefunden und mit anderen Testzahlen festgestellt, dass die 25°C als Minumumkriterium nicht funktionieren. Wenn die Hitzewelle stark genug ist, werden daher momentan sogar noch ein paar Tage unter 20°C mitgezählt. In Deinem Beispiel einfach mal am 4.7 die Zahl "47" einsetzen, dann ergibt sich eine Hitzewelle von 23 Tagen vom 2.7. bis zum 24.7.
2) ist es möglich die Zählung umzudrehen (also z.B. die 6 Tage nicht am 2.7 sondern am 7.7. erscheinen zu lassen = vorwärts statt rückwärts zu zählen?
2) Die Anzahl der Tage ist eine interessante Information die Hitzebelastung betreffend, allerdings interessiert mich noch eine andere Information: ich würde gern die Temperatursumme über 30°C über den Verlauf der Hitzeepisode berechnen, das wäre also in Episode 1 "6", in Episode 2 "3" und in Episode 3 "0". Gibt es eine Möglichkeit, dies ebenfalls zu berechnen?
3) Ich wollte den Wert "30" durch einen anderen Wert ersetzen, den ich in Zelle D2 gespeichert habe (z.B. "29"). Also habe ich die "30" durch "D2" - einmal mit, einmal ohne Anführungszeichen ersetzt, aber nur Nullen geerntet - heißt, es funktioniert so wohl nicht. Ideen wie es klappt?
viele Grüße, Andreas
Lesen hilft
06.08.2018 18:18:03
lupo1
1) war doch gerade meine Korrektur 1, da ich die 25 vergessen hatte. Völlig stumpf beim Lesen von Beiträgen?
2) Meine Hilfsspalte für die BedF ist nur zufällig Deine Zählung. Verwende also dankbar, was Du hast :-)
3) Du sollst auch aus anderem Grund dankbar sein, da Du mit der Rückwärtszählung den Mittelwert oder auch die Summe abzüglich 30 rechnen kannst. Wie das geht, hast Du nicht gefragt (mit INDEX). Versuche Dich dran.
4) D$2 statt D2
AW: Lesen hilft
06.08.2018 19:38:04
Andreas
Hallo Lupo,
danke für Deine Antwort und Deine Hilfe.
1: mein Fehler, sorry.
2: ok
3: ich bekomme es hin, mit Index einen Mittelwert oder eine Summe für feste Bezüge zu berechnen. Allerdings wäre ich für einen Tipp sehr dankbar, wo ich denn die Index-Funktion in Deiner Formel unterbringe, die ja keine festen, sondern variable Zeitreihenlängen enthält.
4: danke, das klappt jetzt - bedingt. Für Werte ab 29,93 ohne Probleme, zwischen 29,61 und 29,93 mit Problemen und darunter garnicht - siehe Beispieldatei Feld D3. Was mache ich nur falsch?
Ich häng die bearbeitete Beispieldatei meinerseits mal an: https://www.herber.de/bbs/user/123174.xlsx
viele Grüße, Andreas
P.S.: ich bin erst seit gestern hier im Forum angemeldet und bitte um Entschuldigung für etwaige Missinterpretationen. Z.B. hatte ich fälschlicherweise angenommen, dass die Deiner letzten Antwort anhängende Excel-Datei die finale Version ist und war daher etwas verwirrt. Bin auf jeden Fall für jede Hilfe hier SEHR dankbar, ohne Frage!
nee, zu viele Dateien verwirren nur ...
06.08.2018 20:17:01
lupo1
... daher immer die nachrichtlichen Ergänzungen im Ast.
Ich habe die 47 durch 40 ersetzt. Auch damit ergibt sich ein Fehler. Bei 39 geht es. Den Grund findet man in der Statuszeile bei Mittelwert, wenn man die bedF-Zellen markiert. Bei 40+ läuft die bedF nämlich in die nächste BedF hinein. Daher müsste ich die Formel entsprechend ändern, wozu mir im Moment die Geisteskraft fehlt.
Vermutlich wird mich das aber quälen und mit Alpträumen verfolgen. Ich werde heute Nacht schweißgebadet gegen 1:30 Uhr im Bett senkrecht aufsitzen und mir einen Energiedrink reinpfeifen. Und dann werde ich vermutlich drüber nachdenken, über diese Hitze und ihre Mittelwertwellen.
Die bedF habe ich übrigens schon aufgefrischt. Die verschiebt sich gern, was mir im bisherigen Upload auch passiert war. Käme dann, wenn das andere auch passt, in einer neuen Datei.
AW: nee, zu viele Dateien verwirren nur ...
06.08.2018 20:51:44
Andreas
ok vielen herzlichen Dank! Vielleicht kann ich mich ja, wenn die Formel dann passt, mit einer Statistik der Top10 Hitzewellen Deines Wohnortes seit Aufzeichnungsbeginn revanchieren. Da ist 2018 sicherlich vorn mit dabei. Und die Statistik der intensivsten Ereignisse sollte auch bei noch nicht endgültig geklärten Definitionsfragen aussagekräftig sein, zumindest solange Du nicht irgendwo wohnst, wo die 40 Grad auch mal leicht überschritten werden können ;-)
viele Grüße, Andreas

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige