Dieses Forum nutzt Cookies
Dieses Forum verwendet Cookies, um deine Login-Informationen zu speichern, wenn du registriert bist, und deinen letzten Besuch, wenn du es nicht bist. Cookies sind kleine Textdokumente, die auf deinem Computer gespeichert werden. Die von diesem Forum gesetzten Cookies werden nur auf dieser Website verwendet und stellen kein Sicherheitsrisiko dar. Cookies aus diesem Forum speichern auch die spezifischen Themen, die du gelesen hast und wann du zum letzten Mal gelesen hast. Bitte bestätige, ob du diese Cookies akzeptierst oder ablehnst.

Ein Cookie wird in deinem Browser unabhängig von der Wahl gespeichert, um zu verhindern, dass dir diese Frage erneut gestellt wird. Du kannst deine Cookie-Einstellungen jederzeit über den Link in der Fußzeile ändern.

Durchschnitt der mittleren 80% berechnen
#1
Hallo Leute,  :32:

ich habe ein Problem mit einer großen Datenmenge.  Huh
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
Antworten Top
#2
Hi Ludwig,

Fragen zu Excel bitte auch im Excelforum stellen – das Unterforum 2019 ist für unser Treffen in diesem Jahr reserviert. WinkIch hab's verschoben.
Gruß Günter
Jeder Fehler erscheint unglaublich dumm, wenn andere ihn begehen.
angebl. von Georg Christoph Lichtenberg (1742-1799)
Antworten Top
#3
Hi,

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

LG
Ludwig
Antworten Top
#4
Nehmen wir mal die 80%:

Welche Werte sollen dann (statt bei 10) bei 9 oder 11 Werten eingeschlossen sein, ggflls anteilig?
Antworten Top
#5
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
Antworten Top
#6
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'
ABCDEFGH
1NamenPkteHS1HS2HS3HS4
2Name17103#NV29,222222233120
3Name23#NV211003
4Name39#NV87104
5Name417#NV#NV4245
6Name511002#NV3306
7Name60#NV13217
8Name719#NV#NV2208
9Name811#NV91229
10Name913#NV#NV11211
11Name1014#NV#NV11012
12Name1112#NV10
13Name124244#NV
14Name1324#NV#NV
15Name144#NV3
16Name155#NV4
17Name166#NV5
18Name177#NV6
19Name188#NV7
20Name1955#NV#NV
21Name2066#NV#NV
22Name2133#NV#NV
23Name2222#NV#NV
24Name2312#NV10
25Name241228#NV
26Name253216#NV
27Name261129#NV
28Name2711010#NV
29Name282207#NV
30Name293305#NV
31Name3033121#NV

ZelleFormel
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. Wink
Gruß Günter
Jeder Fehler erscheint unglaublich dumm, wenn andere ihn begehen.
angebl. von Georg Christoph Lichtenberg (1742-1799)
Antworten Top
#7
Wäre da nicht die Funktion "Gestutztmittel" möglich?
https://support.office.com/de-de/article...d988f511d3
[-] Folgende(r) 1 Nutzer sagt Danke an Cadmus für diesen Beitrag:
  • Ludwig.sp
Antworten Top
#8
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'
EF
2165,16163,23

ZelleFormel
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.
Gruß Günter
Jeder Fehler erscheint unglaublich dumm, wenn andere ihn begehen.
angebl. von Georg Christoph Lichtenberg (1742-1799)
Antworten Top
#9
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.
helmut

Für mich ist die Möglichkeit in Excel an Zellen und Bereichen Namen zu vergeben die wichtigste Funktionalität.
Sie macht Formeln und den VBA-code verständlicher. Für Makros gilt die Regel: "Nur über benannte Bereiche auf den Inhalt der Zellen zugreifen."
Und wofür sind Regeln da? Um nachzudenken bevor man sie bricht.





Antworten Top
#10
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. Wink
Gruß Günter
Jeder Fehler erscheint unglaublich dumm, wenn andere ihn begehen.
angebl. von Georg Christoph Lichtenberg (1742-1799)
[-] Folgende(r) 1 Nutzer sagt Danke an WillWissen für diesen Beitrag:
  • Ludwig.sp
Antworten Top


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste