Hallo,
ich benötige dringend eure Hilfe bei einem vermeintlich schweren Thema.
Ich suche nach einer Lösung, die den Mittelwert aus zuvor ausgewählten Werten angibt.
Dabei müssen zunächst die Werte nach folgender Logik gefiltert werden:
1. Aus den letzten 10 Wochen sollen nur die 8 aktuellsten Wochen verwendet werden. Außer es ist ein 0 Wert enthalten, dann muss Woche 9 hinzugenommen werden. Sollten zwei 0 Werte innerhalb der ersten 8 Wochen enthalten sein, muss die 10. Woche hinzugenommen werden. Sollten mehr als zwei 0 Werte innerhalb der 10 Wochen vorkommen, sind nur die Werte >0 zu verwenden.
2. Aus den zuvor gefilterten Werte muss das System die zwei höchsten und den niedrigsten Wert streichen.
3. Aus den restlichen Werten muss nun der Mittelwert gebildet werden.
Punkt 2. würde sich z.B. Über kgrößte lösen lassen und anschließend den Mittelwert bilden. Das Problem ist Punkt 1.
Hat jemand eine Idee?
Vielen Dank im Voraus.
[
attachment=32062]
Hallöchen,
da 1 das Hauptproblem ist mal dazu ein Ansatz. Per "Hilfsspalte" könntest Du eine Liste ohne 0 erzeugen. Schau Dir dazu mal den
Autofilter per Formel an.
Hallo McFish,
hier ein Vorschlag mit zwei Hilfszelen:
| Arbeitsblatt mit dem Namen 'Tabelle1' |
| A | B | C | D |
| 1 | | Montag | Dienstag | Mittwoch |
| 2 | KW 21 | 6 | 6,2 | 2,3 |
| 3 | KW 20 | 7,4 | 9,3 | 11,2 |
| 4 | KW 19 | 8,2 | 1,5 | 0 |
| 5 | KW 18 | 1,7 | 8,2 | 1,6 |
| 6 | KW 17 | 5,1 | 0,5 | 7,4 |
| 7 | KW 16 | 3,5 | 0 | 7,8 |
| 8 | KW 15 | 10,5 | 4,2 | 0 |
| 9 | KW 14 | 1,4 | 10,3 | 1,4 |
| 10 | KW 13 | 7,9 | 2,7 | 1,3 |
| 11 | KW 12 | 7,3 | 9,7 | 11,1 |
| 12 | Größe Bereich | 8 | 9 | 10 |
| 13 | kleinster Wert | 1,4 | 0,5 | 1,3 |
| 14 | Mittelwert | 4,74 | 4,56 | 4,1 |
| Zelle | Formel |
| B12 | =8+MIN(2;ZÄHLENWENN(B2:B9;0)+(B10=0)*(ZÄHLENWENN(B2:B9;0)>=1)) |
| B13 | =AGGREGAT(15;6;BEREICH.VERSCHIEBEN(B2;;;B12)/(BEREICH.VERSCHIEBEN(B2;;;B12)>0);1) |
| B14 | =MITTELWERTWENNS(BEREICH.VERSCHIEBEN(B2;;;B12);BEREICH.VERSCHIEBEN(B2;;;B12);">0";BEREICH.VERSCHIEBEN(B2;;;B12);"<"&KGRÖSSTE(BEREICH.VERSCHIEBEN(B2;;;B12);2);BEREICH.VERSCHIEBEN(B2;;;B12);">"&B13) |
| Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010 |
| Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg |
In der ersten Hilfszeile wird mit ZÄHLENWENN geschaut, wie oft in dem Bereich eine 0 vorkommt und daraus dann die Größe des Bereiches ermittelt (=> Anzahl der Zeilen).
Mithilfe diesen Wertes kann dann mit BEREICH.VERSCHIEBEN dynamisch der auszuwertende Bereich ermittelt werden.
In der zweiten Hilfszeile wird der kleinste Wert (ohne die 0) im jeweiligen Bereich ermittelt.
Und dann kann man mithilfe der Werten in den Hilfsspalten mit MITTELWERTWENNS den Mittelwert ausrechnen.
Man kann das theoretisch auch alles in eine einzige Formel packen, ohne die Hilfszeilen, aber dann wird die Formel sehr lang und unübersichtlich.
Gruß
Fred
Vielen Dank Fred!!!
Es hat funktioniert. Ich weiß zwar noch nicht genau wie die Aggregatfunktion funktioniert, aber dies werde ich mir nochmals in Ruhe durchlesen ;)
Deine Antwort war sehr hilfreich.
Hallo André,
sehr guter Ansatz, den ich auch bereits versucht hatte in ähnlicher Form umzusetzen.
Leider bin ich daran gescheitert, dass meine Tabelle nicht bei A1 sondern mitten im Tabellenblatt beginnt.
Dies hat dazu geführt, dass bei mir immer die dritte Zahl in der obersten Zeile der Hilfsspalte aufgeführt wird.
Nachdem ich die Spaltenbezeichnungen aus Zeile A gelöscht habe, wurde es korrekt angezeigt.
Ich konnte mir allerdings nicht erklären, wo genau das Problem liegt in der Mittes des Tabellenblattes bzw. z.B. in Zeile B zu beginnen.
Trotzdem auch dir vielen Dank für deine schnelle und kompetente Antwort.
Gruß McFish
AGGREGAT(15,...) entspricht der Funktion KKLEINSTE.
Da aber in dem Bereich ja auch 0 vorkommen kann, kann man die normale KKLEINSTE-Funktion hier nicht verwenden, da sie, sobald in dem Bereich eine 0 vorkommt, die 0 zurückliefert (weil 0 ja schliesslich auch der kleinste Wert ist) - und du möchtest ja aber den kleinsten Wert. der größer als 0 ist, finden. Also muss man da ein bisschen tricksen.
Mann kann das auch mit einer Matrixformel mit KKLEINSTE(WENN....) erreichen.
Stichwort für Internetrecherche:
"KKLEINSTE ohne 0"
Gruß
Fred