Gestutztmittel mit Wenn kombinieren

Forumthread: Gestutztmittel mit Wenn kombinieren

14.11.2016 16:04:16

Marcus

Hallo Zusammen,
ich brauche mal wieder dringend eure Hilfe.
Ich habe eine Tabelle Von A bis K. In Spalte "D" steht eine Kategorie. In Spalte "J" eine Bearbeitungsdauer. Ich brauche das gestutzte Mittel zu jeder Kategorie.
Mit "=MITTELWERTWENN($D:$J;D2;$J:$J)" komme ich ohne Probleme an den Mittelwert ran. Allerdings habe ich so starke Ausreißer, dass ich das gestutze Mittel brauche. Da bietet Excel allerdings leider kein =Gestutztmittelwenn an. =(
Wichtig ist, dass ich die Formel ohne Probleme die restlichen Zeilen "runterziehen" kann. Es muss dann ja bei jeder Kategorie, der gleiche gestutzte Mittelwert stehen.
Jemand eine Idee?
Vielen Dank im Voraus.

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Gestutztmittel mit Wenn kombinieren

14.11.2016 16:10:44

Fennek

Hallo,
wie definierst du den "Ausreisser"?
Nach einer Definition, z.B. "größer als 2 Sigma" kann man das rechnen.
mfg

AW: Gestutztmittel mit Wenn kombinieren

14.11.2016 16:16:54

Marcus

Hier der Link zu einer Beispieldatei: https://www.herber.de/bbs/user/109412.xlsx
Sind in Wirklichkeit ca. 10.000 Datensätze. Unterschied zwischen Mittelwert und Gestutztmittel liegt bei bis zu 30%.
Manuell habe ich es bereits getestet. Will es aber automatisieren.
Wenn ich für das Mittel 20% Ausreißer definiere, bekomme ich realistische Werte.
mfg

AW: Gestutztmittel mit Wenn kombinieren

14.11.2016 16:24:18

UweD

meinst du das so?
Kommt aber das selbe raus wie Mittelwert.

Tabelle1

Sonstige Daten

KeywordPath

Sonstige daten

Sonstige Daten

Time Sum korrekt

Mittelwert

Gestutztmittel

Kat1

5:37:23

6:36:32

Kat2

2:59:05

3:35:32

Kat3

1:53:37

1:11:49

Kat2

9:03:04

3:35:32

Kat2

2:42:58

3:35:32

Kat3

0:15:07

1:11:49

Kat1

3:03:56

6:36:32

Kat1

4:15:24

6:36:32

Kat3

1:02:02

1:11:49

Kat1

28:31:01

6:36:32

Kat2

7:15:58

3:35:32

Kat2

2:20:14

3:35:32

Kat1

0:30:14

6:36:32

Kat3

1:44:24

1:11:49

Kat2

0:17:14

3:35:32

Kat1

2:21:34

6:36:32

Kat3

1:03:54

1:11:49

Kat1

1:56:09

6:36:32

Kat2

0:30:08

3:35:32

verwendete Formeln
Zelle	Formel	Bereich	N/A
K2:K20	=MITTELWERTWENN($D:$J;D2;$J:$J)
L2:L20	{=GESTUTZTMITTEL(WENN(D: D=D2;J:J;"");0,2)}	$L$2

{} Matrixformel mit Strg+Umschalt+Enter abschließen
Matrixformeln sind durch geschweifte Klammern {} eingeschlossen
Diese Klammern nicht eingeben!!

http://excel-inn.de/dateien/vba_beispiele/tabellenanzeige_in_html_addin.zip

http://Hajo-Excel.de/tools.htm

XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007

Add-In-Version 21.10 einschl. 64 Bit

LG UweD

AW: Gestutztmittel mit Wenn kombinieren

14.11.2016 16:29:35

Marcus

Hallo Uwe,
Vielen Dank. Ich werde es mal testen mit 0,4 für den Test. Die Datenmenge ist zu gering, als dass er etwas als Ausreißer definiert. Eben manuell getestet.
mfg

AW: Gestutztmittel mit Wenn kombinieren

14.11.2016 17:19:07

Marcus

Hallo,
jetzt tritt ein bzw. zwei Probleme auf...
1. Wenn ich die Formel jetzt einfach mit einem Doppelklick bis nach ganz unten durchkopiere, zeigt er mir überall den Wert aus der ersten Zeile an.
2. WEnn ich die Formel auf einem leistungsstärkeren Rechner durchführe, erhalte ich in der selben Tabelle auf einmal #Name als Ergebnis.
Jemand eine Idee, warum?
mfg

Wir wissen nicht, was du gemacht hast, ...

14.11.2016 21:00:04

Luc:-?

…Marcus,
aber Uwes Fml ist eine 1zellige (singulare) MatrixFml, deren Eingabe mit [strg][umsch][enter] abgeschlossen wdn muss. Dann kann sie so auch per KreuzKlick bis ans TabEnde übertragen wdn. Allerdings würde ich empfehlen, die Bereiche auf ein Optimum fest zu legen, weil es sonst zu einer RessourcenMangel-Meldung kommen kann.
#NAME?-Fehler sollte nicht auftreten, wenn du nicht noch etwas falsch gemacht hast, oder hat den gleichen Grund → RessourcenMangel. Die alten XlFktt sind kaum für übergroße Bereiche (ab Xl12/2007) optimiert (MS macht das ggf nur sukzessive).
Im Übrigen sind deine Testdaten so mickrig, dass ein Stutzwert von 29% gewählt wdn muss, damit wenigstens bei 2 Kategorien was zu merken ist.
Außerdem sollte es doch reichen, wenn du das GestutztMittel 1× pro Kategorie bildest und nicht flfd immer wieder neu. Das ist wenig performant und bringt keine neue Info. So baut man keine AuswertungsTab auf!
Gruß, Luc :-?
Besser informiert mit …

AW: Gestutztmittel mit Wenn kombinieren

14.11.2016 16:31:12

Fennek

Hallo,
die Tabelle verstehe ich nicht.
Wie wäre es mit einer Hilfsspalte

abs(x(i) - x(quer)). Und dann mittels Aggregate den 80% Wert suchen, und mit einer weiteren  _
hilfssplte alle werte > 80% ausschließen.

mfg

Infobox / Tutorial

Schritt-für-Schritt-Anleitung

Um das gestutzte Mittel in Excel zu berechnen, kannst Du die folgende Methode verwenden:

Definiere zunächst, was für Dich ein Ausreißer ist. Dies kann beispielsweise ein Wert sein, der mehr als 20% vom Mittelwert abweicht.
Füge eine Hilfsspalte hinzu, um die Differenz zwischen jedem Wert und dem Mittelwert zu berechnen. Diese Formel könnte in Spalte L (angenommen, die Werte stehen in Spalte J) so aussehen:
```
=ABS(J2-MITTELWERT(J:J))
```
Bestimme den Schwellenwert für Ausreißer. In diesem Beispiel verwenden wir 20% des Mittelwerts:
```
=0.2*MITTELWERT(J:J)
```
Berechne das gestutzte Mittel, indem Du die Matrixformel in eine Zelle eingibst. Beispiel in Spalte M:
```
{=GESTUTZTMITTEL(WENN(L:L<Schwellenwert;J:J;""))}
```
Drücke Strg + Umschalt + Enter, um die Formel als Matrixformel einzugeben.
Ziehe die Formel nach unten, um das gestutzte Mittel für alle Kategorien zu berechnen.

Häufige Fehler und Lösungen

Wert aus der ersten Zeile wird überall angezeigt:
- Stelle sicher, dass Du die Formel als Matrixformel eingegeben hast (Strg + Umschalt + Enter), da sonst nur der erste Wert kopiert wird.
#NAME?-Fehler:
- Überprüfe die Schreibweise der Funktionen und achte darauf, dass alle verwendeten Bereiche korrekt sind. Ein Ressourcenmangel kann ebenfalls zu diesem Fehler führen, insbesondere bei großen Datenmengen.

Alternative Methoden

Falls die oben genannten Schritte nicht funktionieren, kannst Du auch folgende Alternativen ausprobieren:

Hilfsspalte für Ausreißer: Berechne die Ausreißer in einer zusätzlichen Spalte und schließe diese dann in der Berechnung des gestutzten Mittelwerts aus.
Verwendung von AGGREGAT: Diese Funktion kann helfen, Werte zu ignorieren, die die definierten Kriterien nicht erfüllen.

Praktische Beispiele

Ein Beispiel für die Berechnung des gestutzten Mittelwerts könnte so aussehen:

Kategorie	Bearbeitungsdauer	Differenz zum Mittelwert	Gestutztes Mittel
Kat1	6:36:32	0:00:00	6:36:32
Kat2	3:35:32	0:00:00	3:35:32
Kat3	9:03:04	0:26:32	3:35:32

Die Differenzen und das gestutzte Mittel können dann automatisiert berechnet werden.

Tipps für Profis

Achte darauf, dass Du die Bereiche in Deinen Formeln auf ein sinnvolles Maß beschränkst, um Ressourcen zu sparen.
Teste Deine Formeln mit unterschiedlichen Ausreißer-Kriterien, um zu sehen, wie sich das gestutzte Mittel verändert.
Dokumentiere Deine Formeln und deren Logik, um später Änderungen leichter vornehmen zu können.

FAQ: Häufige Fragen

1. Was ist ein gestutzter Mittelwert?
Ein gestutzter Mittelwert berechnet den Durchschnitt eines Datensatzes, während extreme Werte (Ausreißer) ausgeschlossen werden, um verzerrte Ergebnisse zu vermeiden.

2. Wie kann ich die Formel für große Datenmengen optimieren?
Verwende spezifische Zellbereiche anstelle ganzer Spalten und prüfe die Anzahl der Datenpunkte, um sicherzustellen, dass die Berechnungen effizient bleiben.

Beliebteste Forumthreads (12 Monate)

Gestutztmittel mit Wenn kombinieren

Forumthread: Gestutztmittel mit Wenn kombinieren

7 Beiträge zum Forumthread Beiträge zu diesem Forumthread

VBA Beispiele zu diesem und ähnlichen Themen

Infobox / Tutorial

Gestutzte Mittelwerte in Excel berechnen

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

7
Beiträge zum Forumthread

Beiträge zu diesem Forumthread