Mittelwert und Fehler von mehreren Zeilen

[Queediab]

Moin,
folgendes Problem in Excel. Ich will Average und Stdev haben für die Zahlen in der letzten Reihe basierend auf den Zahlen in der ersten und zweiten Reihe. Hab das unten mal gemacht (manuell):
Start Ende (egal) Average Stdev Einzelwerte

221​	228​	1​	0.529554​	0.075593​	0.585552​
221​	228​	1​			0.559543​
221​	228​	1​
221​	228​	1​			0.443568​
221​	229​	1​	0.914529​		0.914529​
221​	230​	2​	1.373647​		1.373647​
222​	226​	1​	0.652897​	0.085835​	0.65243​
222​	226​	1​			0.545582​
222​	226​	1​			0.657914​
222​	226​	1​			0.755664​
222​	229​	1​	1.176412​		1.176412​
222​	232​	2​	2.488596​		2.488596​
235​	247​	1​	0.718772​	0.365605​	0.527537​
235​	247​	1​			0.615457​
235​	247​	1​			0.472107​
235​	247​	1​			1.259986​
240​	244​	1​	0.404152​		0.404152​

Also es müssen z.B. alle Zahlen für 221 und 228 gemittelt werden. Einzelne Werte (z.B. 221 und 229 müssten einfach so in Spalte 4 auftauchen) Ich kann das per Hand machen, aber das dauert...
Lässt sich so etwas per Makro bewerkstelligen, oder vielleicht mit if oder averageif?
Vielen Dank im Voraus & MfG

 

[steve1da]

Lade eine Exceldatei hoch. Ich kann allerdings nicht erkennen was Daten sind und was die Ergebnisse.

 

[Majestro1337]

Hallo,

So ganz verstehe ich die Anforderung noch nicht.

Also es gibt eine andere Liste, die viele Zeilen enthält, die in irgend einer Spalte diese Start / Ende Zahl stehen haben richtig?
Ist die aufsteigend nach der Nummer aus Start / Ende sortiert ? Dürfte man sie sortierten? (Würde alles erheblich beschleunigen)

Davon ausgehend könnte man das average mit summewenn() / zählenwenn() als Formel lösen.
Die Standardabweichung kann man vielleicht mit einer matrixformel lösen - da die aber bei großen Datenmengen unendlich langsam sind würde ich auch auf vba setzen.

Was genau bei "Einzelwerte" rein soll hab ich nicht so ganz verstanden. Alle Werte die in der Spanne Start bis Ende liegen sollen da kommagetrennt rein oder wie?

 

[Queediab]

Sorry falls es unklar war. Hier ist eine kleine Excel Datei.
Letzte Spalte sind die Daten und das Ergebnis ist Mittelwert und Stabw in Spalten 4 & 5.

summewenn() / zählenwenn() klingt interessant. Damit spiel ich mal etwas rum.

MfG

P.S. In der Excel datei sind die Einzelwerte in Spalte L und Mittelwert & Stabw sind in Spalte C&D. Mittelwert und Stabw sollen basierend auf den Zahlen in A & B berechnet werden.

 

[steve1da]

Ich hab grad kein Excel, aber Mittelwertwenns() und http://www.office-loesung.de/ftopic234203_0_0_asc.php sollte helfen.

 

[Mirakel]

Muss das in die gleiche Tabelle?
Über ne Pivot-Tabelle wäre es wohl am einfachsten

Ansonsten, hätte ich folgende Lösung (geht vllt auch anders):
Mittelwert ist einfach, mit MITTELWERTWENNS
Die Standardabweichung ist komplizierter: Leere Einzelwerte aus der Tabelle löschen, Hilfsspalte aus Anfang und Ende erstellen und dann mit einer Matrixformel arbeiten.
Matrixformeln ohne geschweifte Klammern eingeben und mit Strg+Shift+Enter abschließen

Z.B. so

Start (A)	Ende (B)	egal (C)]	Avg (D)	Stdev (E)	Einzelw (F)	Hilfsspalte (G)
221	228		=MITTELWERTWENNS(F:F;A:A;A2;B:B;B2)	{=STABW.S(WENN(VERKETTEN(A2;B2)=G:G;F:F))}		=verketten(start;ende)

Das kannst du dann einfach in jede Zeile schreiben.
Wenn du das nur in die Zeile vom ersten Vorkommen aus der Kombination von Start und Ende haben willst, könntest du die Zeilen z.B. irgendwie Kennzeichnen und dann die Formeln nur dort hinkopieren.
Ich würde es über eine PivotTabelle lösen.

 

[Queediab]

Uffda, hier sitzt der Trottel vor dem Computer. Brauche erstmal ne Weile das alles auszuprobieren.
Vielen Dank schonmal :-)
MfG

So also Mittelwertwenns (in meinem Falle ists Averageifs) funktioniert. Ich starre es gerade noch etwas ungläubig an :-). Dann noch dasselbe mit Stabw hinmehren...

 

[Majestro1337]

Muss das in die gleiche Tabelle?
Über ne Pivot-Tabelle wäre es wohl am einfachsten

Ansonsten, hätte ich folgende Lösung (geht vllt auch anders):
Mittelwert ist einfach, mit MITTELWERTWENNS
Die Standardabweichung ist komplizierter: Leere Einzelwerte aus der Tabelle löschen, Hilfsspalte aus Anfang und Ende erstellen und dann mit einer Matrixformel arbeiten.
Matrixformeln ohne geschweifte Klammern eingeben und mit Strg+Shift+Enter abschließen

Z.B. so

Start (A)	Ende (B)	egal (C)]	Avg (D)	Stdev (E)	Einzelw (F)	Hilfsspalte (G)
221	228		=MITTELWERTWENNS(F:F;A:A;A2;B:B;B2)	{=STABW.S(WENN(VERKETTEN(A2;B2)=G:G;F:F))}		=verketten(start;ende)

Das kannst du dann einfach in jede Zeile schreiben.
Wenn du das nur in die Zeile vom ersten Vorkommen aus der Kombination von Start und Ende haben willst, könntest du die Zeilen z.B. irgendwie Kennzeichnen und dann die Formeln nur dort hinkopieren.
Ich würde es über eine PivotTabelle lösen.

Oder so ohne Hilfsspalte:

Start (A)	Ende (B)	egal (C)]	Avg (D)	Stdev (E)	Einzelw (F)	Hilfsspalte (G)
221	228		=SUMMEWENNS(L:L;A:A;A2;B:B;B2)/ZÄHLENWENNS(A:A;A2;B:B;B2)	{=STABW(WENN(($A$2:$A$1000=A2)*($B$2:$B$1000=B2);($L$2:$L$1000)))}		=verketten(start;ende)

achtung: geht nur bis zeile 1000, wird die tabelle länger dann die formel für die standardabweichung ändern

Ergänzung (26. November 2021)

edit: mit stabw.s kann man das sogar noch etwas einfacher machen und es ist dann auch nciht mehr auf 1000 zeilen begrenzt:

Start (A)	Ende (B)	egal (C)]	Avg (D)	Stdev (E)	Einzelw (F)
221	228		=SUMMEWENNS(L:L;A:A;A2;B:B;B2)/ZÄHLENWENNS(A:A;A2;B:B;B2)	{=STABW.S(WENN((A:A=A2)*(B:B=B2);(L:L)))}

 

[Queediab]

{=STABW.S(WENN((A:A=A2)*(B:B=B2);(L:L)))}

scheint nicht zu funktionieren. Es spuckt leider die Standardabweichung für die komplette Spalte aus und sortiert nicht nach den A:A=A2 und B:B=B2 Konditionen. Ich denke, dass ich es richtig in mein englisches Excel übertragen habe:
=STDEV.S(IF((A:A=A2)*(B:B=B2),(L:L)))

MfG

 

[Majestro1337]

also das funktioniert - hast du es auch mit STRG + SHIFT + ENTER eingegeben, damit es als matrixformel mit den geschweiften klammern drin steht?

ansonsten ist das bei excel 2007 und älter nicht verfügbar.

was anderes fällt mir nicht ein...

 

[Queediab]

also das funktioniert - hast du es auch mit STRG + SHIFT + ENTER eingegeben, damit es als matrixformel mit den geschweiften klammern drin steht?

Da lag das Problem. Es funktioniert so. 1000x Dank!!!! Das spart mir soviel Zeit. Die Duplikate kann ich dann leicht danach löschen.
MfG