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.

Ausgabe der aktuellsten Werte ohne 0
#1
Lightbulb 
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.


.pdf   Beispiel Excel.pdf (Größe: 100,33 KB / Downloads: 11)
Antworten Top
#2
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.
.      \\\|///      Hoffe, geholfen zu haben.
       ( ô ô )      Grüße, André aus G in T  
  ooO-(_)-Ooo    (Excel 97-2019+365)
[-] Folgende(r) 1 Nutzer sagt Danke an schauan für diesen Beitrag:
  • McFish
Antworten Top
#3
Hallo McFish,

hier ein Vorschlag mit zwei Hilfszelen:

Arbeitsblatt mit dem Namen 'Tabelle1'
ABCD
1MontagDienstagMittwoch
2KW 2166,22,3
3KW 207,49,311,2
4KW 198,21,50
5KW 181,78,21,6
6KW 175,10,57,4
7KW 163,507,8
8KW 1510,54,20
9KW 141,410,31,4
10KW 137,92,71,3
11KW 127,39,711,1
12Größe Bereich8910
13kleinster Wert1,40,51,3
14Mittelwert4,744,564,1

ZelleFormel
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
[-] Folgende(r) 1 Nutzer sagt Danke an Fred11 für diesen Beitrag:
  • McFish
Antworten Top
#4
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
Antworten Top
#5
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
Antworten Top


Gehe zu:


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