Live-Forum - Die aktuellen Beiträge
Datum
Titel
29.03.2024 13:14:12
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1320to1324
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

Treppenfunktion Mittelwert einer Treppenstufe

Treppenfunktion Mittelwert einer Treppenstufe
21.07.2013 11:54:28
Kraichtaler
Hallo zusammen,
ich stehe gerade vor einer Herausforderung an der ich jetzt schon ewig knoble und einfach nicht weiterkomme.
Ich habe eine Zahlenreihe von bis zu 6000 Zeilen in Spalte A stehen. Die Werte beginnen irgendwo bei Null und laufen dann auf bestimmtes Level wo der Wert für ca 100 Zeilen fast stabil bleibt und dann laufen die Zahlen weiter hoch auf das nächste Level und so weiter. Nach der 10. "Treppenstufe" ist das ganze dann wieder rückläufig. Das heißt die Zahlen werden kleiner und durchlaufen immer wieder gewisse Niveaus in denen die Werte auf einem Level bleiben bis eben zurück auf Null.
Mein Problem ist nun, dass ich
a) diese Stufen aus den ca. 6000 Zeilen finden muss
b) von diesen Stufen eine Mittelwertbildung machen muss....allerdings nicht mit allen Werten der Stufe, sondern nur mit den 80sten bis 94sten Wert der Stufe... Der Grund ist der, dass die Werte kurz nach erreichen der Stufe noch nicht so ganz stabil sind und daher hätte ich gern erst den 80sten Wert und dann die folgenden 14 Werte verwendet.
Was ich bisher erreicht habe ist folgendes:
- Die Stufen finde ich mithilfe einer Formel in einer Hilfsspalte raus, indem ich über eine Wenn-Funktion sage: Wenn Differenz des Vorwertes zum aktuellen Wert in Spalte A kleiner 0,01 ist, dann liefere mir eine "1, ansonsten eine leere Zelle mit "".
- Jetzt hab ich versucht diese Einsen zu zählen, aber stellt sich mir schon das erste Problem. Wie kann ich die Einsen hochzählen und bei der nächsten Stufe wieder von 1 loszählen ? Wahrscheinlich führt diese Lösung auf einen Holzweg.
Mein Gedanke war, dass ich an jeder Stufe dann eine Hilfsspalte 1-ca.100 habe, und dann mir den Wert aus der Zeile mit der 80 nehme und dann einen Mittelwert bilde bis zu der Zeile 94. Aber das geht so irgendwie nicht, weil ich ja dann mehrfach in meiner Hilfsspalte diese Werte hätte.
Ziemlich verworren, aber vielleicht habt ihr mir ja eine viel einfachere Lösung. Wär super.
vielen Dank schon mal.
Viele Grüße
Jürgen

10
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Treppenfunktion Mittelwert einer Treppenstufe
21.07.2013 12:21:35
Kraichtaler
zum besseren Verständnis lade ich mal mein bisheriges File hoch wie weit ich bisher gekommen bin.
https://www.herber.de/bbs/user/86488.xlsx
Grüße
Jürgen

Treppenfunktion - Mittelwert pro Stufe
21.07.2013 13:12:09
Erich
Hi Jürgen,
das hier könnte in die richtige Richtung gehen:
 ABCDE
1WertHilf  Vorgaben
2-0,001561 Schwelle0,01
3-0,001542 Anz. Zeilen15
4-0,001753 bis Zeile94
5-0,001474   
6-0,001695   
7-0,001816 bis ZeileMittelw
8-0,001637 95-0,0013180
9-0,001318 6034,5000120
10-0,001289 7809,0000100
11-0,0016110 95413,4999993
12-0,0016111 113217,9999907
13-0,0017612 132022,5000060
14-0,0016813 150527,0000060
15-0,0014514 169831,4999947
16-0,0014315 188136,0000100
17-0,0014916 205740,4999440
18-0,0013617 222444,4999980
19-0,001718 247740,4999313
20-0,0015919 263936,0000473
21-0,0017620 280231,5000320
22-0,0016121 297026,9999507
23-0,0018422 314222,4999900
24-0,0016823 331818,0000020
25-0,0015424 349813,4999747
26-0,0012325 36859,0000120
27-0,0019826 38814,4999460
28-0,0016227 #ZAHL!#ZAHL!

Formeln der Tabelle
ZelleFormel
B2=(ABS(A3-A2)<E$2)*SUMME(B1;(ABS(A3-A2)<E$2))
B3=(ABS(A4-A3)<E$2)*SUMME(B2;(ABS(A4-A3)<E$2))
D8{=KKLEINSTE(WENN(B$2:B$9999=$E$4;ZEILE(B$2:B$9999)); ZEILE(A1))}
E8=MITTELWERT(INDEX(A:A;D8-E$3+1):INDEX(A:A;D8))
D9{=KKLEINSTE(WENN(B$2:B$9999=$E$4;ZEILE(B$2:B$9999)); ZEILE(A2))}
E9=MITTELWERT(INDEX(A:A;D9-E$3+1):INDEX(A:A;D9))
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Die Zählung kann noch um eins verschoben sein - habe ich mir noch nicht genauer überlegt.
Aber vielleicht reicht das ja schon aus. :-)
Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich

Anzeige
letzte Stufe fehlt
21.07.2013 13:32:46
Kraichtaler
Hallo Erich,
phänomenal kann ich da nur sagen. Hab das alles jetzt mal so eingegeben .....und es funktioniert.....mit einer kleinen Ausnahme. Wie schon in Deinem eingefügten Beispiel zu sehen, wird die letzte Stufe, also wenn der Wert wieder auf Null ist nicht mehr berechnet. Da ich die Formel nicht wirklich verstehe (Mit Matrixformeln bin ich überkreuz) kann ich nicht nachvollziehen, woran das liegt. Kannst Du mir da noch weiterhelfen ?
Vorab aber schon mal ein dickes Dankeschön. Das hätt ich so nie hingekriegt.
Viele Grüße
Jürgen

AW: letzte Stufe fehlt
21.07.2013 13:37:27
Kraichtaler
ah halt.....ich sehs grade.....das liegt ja daran, weil die Nullwerte nicht so viele sind, dass es bis 80 bzw. 94 hochzählt. Hm das ist natürlich ein Problem, denn die Daten werden von einer Maschine generiert der ich nicht so einfach sagen kann, dass sie mehr Werte produzieren soll. Zumindest müsste ich da mal forschen, ob das nicht irgendwie geht.
Vielleicht kann man in die Formel ja aber auch einbauen, dass wenn die Werte nicht so hoch laufen, dass er dann die letzten 15 Werte nimmt ?
Viele Grüße
Jürgen

Anzeige
inkomplette letzte Stufe
21.07.2013 14:01:13
Erich
Hi Jürgen,
so einfach scheint es mir doch nicht zu sein.
Sicher, man könnte die letzten 15 Werte mitteln. Aber was sagt das aus?
Die letzten Werte könnten so aussehen:
... 1 3 5 5,001 3 2,995 4
Da liegen viele Differenzen über dem Schwellenwert. Worüber sollte hier gemittelt werden?
Kann man hier überhaupt von einer Stufe sprechen?
Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich

AW: inkomplette letzte Stufe
21.07.2013 14:28:13
Kraichtaler
Hallo Erich,
die letzten Werte der Stufe in meinem Beispiel sehen doch recht nah beinander aus. Also ich sähe da kein Problem. Die Stufe wird ja in jedem Fall erreicht, auch wenn sie unter Umständen nicht bis zu 100 Werten hat.
Ich kann aber morgen mal schauen, ob ich der Maschine am Messende mehr Werte entlocken kann.
viele Grüße
Jürgen

Anzeige
Berechnung mit inkompletter letzter Stufe
21.07.2013 17:42:14
Erich
Hi Jürgen,
ja, in deinem Beispiel sähe ich da auch kein Problem. Aber so eine Formel sollte nicht nur bei
"hinreichnend schönen" Daten vernünftige Ergebnisse liefern.
Manchmal ist die Realität eben doch hässlich - und was macht die Formel dann?
Hier eine Erweiterung, bei der nach dem letzten 94er noch geprüft wird, ob der unterste Wert in Spalte B
mindestens 15 ist - dann wird damit eine inkomplette Stufe erkannt und über die 15 Werte gemittelt.
Ist der letzte B-Wert kleiner als 15, gibt es da keine Stufe:
 ABCDE
1WertHilf  Vorgaben
2-0,001561 Schwelle0,01
3-0,001542 Anz. Zeilen15
4-0,001753 bis Zeile94
5-0,001474   
6-0,001695   
7-0,001816 bis ZeileMittelw
8-0,001637 95-0,0013180
9-0,001318 6034,5000120
10-0,001289 7809,0000100
11-0,0016110 95413,4999993
12-0,0016111 113217,9999907
13-0,0017612 132022,5000060
14-0,0016813 150527,0000060
15-0,0014514 169831,4999947
16-0,0014315 188136,0000100
17-0,0014916 205740,4999440
18-0,0013617 222444,4999980
19-0,001718 247740,4999313
20-0,0015919 263936,0000473
21-0,0017620 280231,5000320
22-0,0016121 297026,9999507
23-0,0018422 314222,4999900
24-0,0016823 331818,0000020
25-0,0015424 349813,4999747
26-0,0012325 36859,0000120
27-0,0019826 38814,4999460
28-0,0016227 4064-0,0033247
29-0,0015628   

Formeln der Tabelle
ZelleFormel
B2=(ABS(A3-A2)<E$2)*SUMME(B1;(ABS(A3-A2)<E$2))
D8{=WENN(ZEILE(D1)<=ZÄHLENWENN(B$2:B$9999;$E$4); KKLEINSTE(WENN(B$2:B$9999=$E$4;ZEILE(B$2:B$9999)); ZEILE(A1)); WENN((ZEILE(D1)=1+ZÄHLENWENN(B$2:B$9999;$E$4))*(VERWEIS(9^99;B:B)>=$E$3); VERGLEICH(-9^99;B:B;-1); ""))}
E8=WENN(ISTZAHL(D8); MITTELWERT(INDEX(A:A;D8-E$3+1):INDEX(A:A;D8)); "")
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen

Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich

Anzeige
AW: Berechnung mit inkompletter letzter Stufe
21.07.2013 18:04:53
Kraichtaler
Hallo Erich,
top, absolut top. Die Lösung, dass mindestens 15 Werte für eine vollständige Stufe da sein müssen ist einfach perfekt. So passt es zu 100%. Ich kann Dir gar nicht sagen wie happy ich bin.
Vielen, vielen Dank. Du hast mir perfekt weitergeholfen.
Viele Grüße und noch einen schönen Restsonntag
Jürgen
PS: Mein Beispiel ist eigentlich schon repräsentativ. Wenn die Stufe angefahren wird, dann schwanken die Werte nicht in einem beängstigendem Bereich, als dass eine Mittelwertbildung keinen Sinn mehr machen würde. Das verhält sich dann schon so wie im Beispiel.

Anzeige
AW: Berechnung mit inkompletter letzter Stufe
22.07.2013 14:46:01
Kraichtaler
Hallo Erich,
ich habe gerade noch ein Problem mit einer Formel von Dir.....und zwar mit der langen Matrixformel. Das Problem ist, dass die Werte nicht wie in dem von Dir abgewandelten Beispiel in der zweiten Zeile anfangen, sondern erst in Zeile 8. Jetzt versuch ich schon wieder ewig rauszufinden, wo ran das hakt und wo das in der Formel versteckt ist, aber ich finde es nicht und so arbeitet die Matrixformel so, dass sie die ersten 6 Werte überspringt...... Keine Ahnung warum. Für Dich sicher kein Problem, oder ? Wär Dir nochmal superdankbar wenn Du mir nochmal helfen könntest.
viele Grüße
Jürgen

Anzeige
AW: Berechnung mit inkompletter letzter Stufe
22.07.2013 14:50:28
Kraichtaler
Hallo Erich,
schon witzig....jetzt war ich so am Verzweifeln.....aber in einem letzten Versuch hab ichs jetzt doch glatt selber gefunden. In der Formel (heißt es "Zeile(A1), was in meiner Formel durch kopiererei irgendwie zu a8 geworden ist. Wieder zu A1 gemacht.....und siehe da .....alles worked....
viele Grüße
Jürgen

299 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige