Clever-Excel-Forum

Hallo Leute,  :32:

ich habe ein Problem mit einer großen Datenmenge. 
Ich versuche zurzeit den Durchschnitt der mittleren 80% von einzelnen Spalten zu berechnen. Dadurch, dass ich so viele Spalten habe, kann ich die Zellen nicht durch die "Filtern"-Anwendung in eine Reihenfolge bringen.

Hier mal ein Beispiel (in Spalte A sind Namen und in Spalte B sind Punktzahlen) ich möchte hierbei die obersten 10% und die untersten 10% der Punktzahlen in der Durchschnittsberechnung nicht berücksichtigen, da diese Aussreißer sind:

      A        B      
1    a        5
2    b        3
3    c        9
4    d       17
5    e       1100
6    f        0
7    g       19
8    h       11
9    i        13
10  j        4


Vielen Dank im Voraus!

LG
Ludwig

Hi Ludwig,

Fragen zu Excel bitte auch im Excelforum stellen – das Unterforum 2019 ist für unser Treffen in diesem Jahr reserviert. Ich hab's verschoben.

Hi,

tut mir leid.
Habe ich wohl übersehen.
Danke auf jeden Fall fürs Verschieben.

LG
Ludwig

Nehmen wir mal die 80%:

Welche Werte sollen dann (statt bei 10) bei 9 oder 11 Werten eingeschlossen sein, ggflls anteilig?

Hallo,

nur als Idee (also ohne Nachbau und ausprobieren)

Mit dem Autofilter to Topt10% und Bottom10% filtern. Dann mit "Aggregate(Mittelwert) nur Sichtbare" rechnen.

mfg

Hi Ludwig,

@Fennek: der TE schrieb schon, dass Autofilter nicht möglich sei.

Hier mal eine Lösung mit 4 (ausblendbaren) Hilfsspalten:

Arbeitsblatt mit dem Namen 'Tabelle2'
	A	B	C	D	E	F	G	H
1	Namen	Pkte	HS1	HS2			HS3	HS4
2	Name1	710	3	#NV	29,2222222		3312	0
3	Name2	3	#NV	2			1100	3
4	Name3	9	#NV	8			710	4
5	Name4	17	#NV	#NV			424	5
6	Name5	1100	2	#NV			330	6
7	Name6	0	#NV	1			321	7
8	Name7	19	#NV	#NV			220	8
9	Name8	11	#NV	9			122	9
10	Name9	13	#NV	#NV			112	11
11	Name10	14	#NV	#NV			110	12
12	Name11	12	#NV	10
13	Name12	424	4	#NV
14	Name13	24	#NV	#NV
15	Name14	4	#NV	3
16	Name15	5	#NV	4
17	Name16	6	#NV	5
18	Name17	7	#NV	6
19	Name18	8	#NV	7
20	Name19	55	#NV	#NV
21	Name20	66	#NV	#NV
22	Name21	33	#NV	#NV
23	Name22	22	#NV	#NV
24	Name23	12	#NV	10
25	Name24	122	8	#NV
26	Name25	321	6	#NV
27	Name26	112	9	#NV
28	Name27	110	10	#NV
29	Name28	220	7	#NV
30	Name29	330	5	#NV
31	Name30	3312	1	#NV

Zelle	Formel
C2	{=VERGLEICH(B2;$G$2:$G$11;0)}
D2	=VERGLEICH(B2;$H$2:$H$11;0)
E2	=MITTELWERTWENNS($B$2:$B$31;$C$2:$C$31;#NV;$D$2:$D$31;#NV)
G2	=KGRÖSSTE($B$2:$B$31;ZEILE(A1))
H2	=KKLEINSTE($B$2:$B$31;ZEILE(A1))

Achtung, Matrixformel enthalten!

Die geschweiften Klammern{} werden nicht eingegeben.

Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.

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

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

Der Matrixabschluss ist nicht nötig, war von anderen Versuchen übrig.

Wäre da nicht die Funktion "Gestutztmittel" möglich?
https://support.office.com/de-de/article...d988f511d3

Hi Cadmus,

ich hab' mir mal die Fkt. angesehen und ausprobiert (bislang noch nicht benötigt), bin mir aber nicht sicher, ob das Ergebnis wirklich korrekt ist. Hier mal der Vergleich:

Arbeitsblatt mit dem Namen 'Tabelle2'
	E	F
2	165,16	163,23

Zelle	Formel
E2	=RUNDEN(MITTELWERTWENNS($B$2:$B$101;$C$2:$C$101;#NV;$D$2:$D$101;#NV);2)
F2	=RUNDEN(GESTUTZTMITTEL($B$2:$B$101;0,2);2)

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

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

Ich habe, wie vom TE angegeben, 100 Zahlen genommen und entsprechend der Beschreibung von MS 0,2 (20%) eingesetzt. Das Ergebnis siehst du oben, wobei ich der Meinung bin, dass ersteres  das genauere ist.

Hallo Günter,

warum sollte eine Excel-Funktion nicht das tun, was in der Beschreibung steht.

In deinen Beispielzahlen im Beitrag #6 kommt der 10tkleinste Wert zweimal vor. Es werden also 21 Werte (statt 20) nicht berücksichtigt.

Hallo Helmut,

ich habe tatsächlich übersehen, dass doppelte Werte vorhanden waren - jetzt passt natürlich alles und der TE müsste glücklich sein.