Dynamischer Mittelwert mit Leerstellen und Vergleich/Wenn Dann Funktion

[Eike]

Moin liebe Community,


im Zuge der neuen Düneverordnung, möchte ich die Durchschnittsertragsrechnung etwas automatisieren.

Gefordert wird ein fortlaufender Mittelwert der Erträge der letzten 5 Jahre. Dies konnte ich durch euer Forum schon lösen: =MITTELWERT(INDEX(C3:C100;ANZAHL2(C3:C100)-4):C100)
Zwei zusätzliche Bedingungen werden benötigt:

1. Gibt es allerdings in den letzten 5 Jahren nicht jedes Jahr einen Ertrag, so soll der der Mittelwert lediglich aus den vorhandenen Jahren ermittelt werden:
(z.B. 2016: 80 2017: 0 2018: 0 2019: 0 2020 :80) --> dann den Mittelwert von 16 & 20 und nur durch 2 teilen und nicht durch 5.

2. Zusätzlich kommt aber noch die Bedingung, dass man den Erträge mit dem jeweiligen Vojahresertrag vergleicht. Ist dort eine negative Abweichung <20 % so soll der Vorjahresertrag gewählt werden.

Dazu eine Erläuterung:
Ja (DüV Anlage 4 Tab. 3), weicht das Ertragsniveau in einem der letzten fünf Jahre um mehr als 20 % vom Ertragsniveau des jeweils vorangegangenen Jahres ab, kann statt des tatsächlichen Ertragsniveaus, das im Jahr der Abweichung erreicht wurde, das Ertragsniveau des jeweils vorangegangenen Jahres für die Ermittlung der Ertragsdifferenz herangezogen werden.
Beispiel 1:
Erträge der Vorjahre: 2015 – 100 dt/ha, 2016 – 100 dt/ha, 2017 - 100 dt/ha; 2018 - 70 dt/ha; 2019 - 85 dt/ha
Damit fällt der Ertrag 2018 um > 20% niedriger aus als 2017 und damit kann zur Ermittlung des Ertragsniveaus das Jahr 2017 doppelt herangezogen werden. Berechnung Ertragsniveau 2020 = (100+100+100+100+85)/5 = 97 dt/ha
Beispiel 2:
Erträge der Vorjahre: 2015 – 100 dt/ha 2016 – 100 dt/ha, 2017 - 100 dt/ha; 2018 - 75 dt/ha (> -20%); 2019 - 53 dt/ha (> - 20%).
Damit fällt der Ertrag 2018 um > 20% niedriger aus als 2017 und der Ertrag 2019 um > 20% niedriger als 2018. Somit kann zur Ermittlung des Ertragsniveaus das Jahr 2017 doppelt und das Jahr 2018 einfach herangezogen werden.
Berechnung Ertragsniveau 2020 = (100+100+100+100+75)/5 = 95 dt/ha


Könnt ihr mir helfen, die o.b. Funktion zu erweitern, dass die Bedingungen erfüllt werden? Über zweckmäßigere Lösungen freue ich mich auch.

Besten Dank und viele Grüße
Eike


xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns="http://www.w3.org/TR/REC-html40">

<meta http-equiv=Content-Type content="text/html; charset=windows-1252">
<meta name=ProgId content=Excel.Sheet>
<meta name=Generator content="Microsoft Excel 15">
<style id="Durchschnittsertrag_24672_Styles">

</style>

Durchschnittserträge

                                                                                                                                                                                               
               

Spalte1	Weizen	Raps	Gerste   style='mso-spacerun:yes'>	Rübe   style='mso-spacerun:yes'>	Roggen	Silomais	Körnermais
Durchschnitts-ertrag	91	38,75
2016	100	42
2017	100	52
2018	100
2019	70	30
2020	85
2021		31
2022

[Fred11]

Hallo Eike,

ich würde die Bedingung 2 mit einer Hilfsspalte für jede Anbauart lösen.
In dieser Hilfsspalte mit einer WENN-Funktion prüfen, ob der Ertrag weniger als 80% des Vorjahres beträgt.

Bedingung 1 lässt sich mit MITTELWERTWENN (statt MITTELWERT) lösen. Da kann man zusätzlich eine Bedingung (>0) eingeben.
Hinweis:
Wenn es in einem Jahr keinen Ertrag gibt, muss eine 0 eingetragen werden (die Zelle darf nicht leer sein), sonst stimmt die Ermittlung der letzten 5 Erträge mit ANZAHL2 nicht. 

Siehe Beispieldatei.

Gruß
Fred

[Eike]

Hallo Fred,
das ist super.
Die Idee mit der Hilfsspalte kam mir tatsächlich noch selber gestern Nachmittag. Bei mir hatte es dann nur nicht mit den Leerzeilen funktioniert.

Vielen Dank für die Hilfe!

Gruß
Eike

P.s. Kannst du einmal anhand des Beispiels erläutern was diese Formel macht?
=MITTELWERTWENN(INDEX(G3:G100;ANZAHL2(F3:F100)-4):G100;">0")
Wenn ich das richtig sehe bilde ich eine Matrix in der dann die Anzahl der nicht leeren Zellen ausgegeben wird. Wieso subtrahiere ich dann da 4 von ab? Das wird ja mit den 5 letzten Werten zusammenhängen. (G3:G100;ANZAHL2(F3:F100)-4) der Teil gibt mir ja dann quasi die letzten 5 Werte, welche ich dann durch G:100 teile. Wieso G100? Und durch das Wenn berechnet der nur die Mittelwerte von Zellen >0.

[Fred11]

Hallo Eike,

im Prinzip funktioniert die Formel so, wie du es beschrieben hast.
In der angehängten Datei habe ich die Formel aufgedröselt und ein paar Erläuterungen dazugeschrieben/-gemalt.

Der Teil
INDEX(C3:C20;ANZAHL2(B3:B20)-4)
ermittelt dynamisch (abhängig von der Anzahl der Einträge) die Startzelle für den Bereich für MITTELWERTWENN

Durch die -4 werden immer die letzten 5 Werte berücksichtigt

Die 100 sind recht willkürlich gewählt.( Das kann auch 200 oder 1000 sein) Bis zu diesem Bereich werden Einträge für die Mittelwertberechnung berücksichtigt; Einträge ab Zeile 101 werden in der Formel nicht mehr berücksichtigt. Das funktioniert, weil die Funktion MITTELWERT bzw MITTELWERTWENN leere Zellen generell ignoriert.
In der angehängten Beispieldatei habe ich hier 20 genommen, zur besseren Veranschaulichung.
(Es gibt noch andere Möglichkeiten, einen Zellbereich dynamisch zu ermitteln, z.B. mit der Funktion BEREICH.VERCHIEBEN...)

durch das Wenn berechnet der nur die Mittelwerte von Zellen >0

So ist es

Gruß
Fred