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.

Dynamischer Mittelwert mit Leerstellen und Vergleich/Wenn Dann Funktion
#1
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









Durchschnittserträge






                                                                                                                                                                                               
               
Spalte1WeizenRapsGerste  style='mso-spacerun:yes'> Rübe  style='mso-spacerun:yes'> RoggenSilomaisKörnermais
Durchschnitts-ertrag9138,75
201610042
201710052
2018100
20197030
202085
202131
2022







Antwortento top
#2
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


Angehängte Dateien
.xlsx   Durchschnittsertrag_fs.xlsx (Größe: 14,29 KB / Downloads: 1)
[-] Folgende(r) 1 Benutzer sagt Danke an Fred11 für diesen Beitrag:
  • Eike
Antwortento top
#3
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.
Antwortento top
#4
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...)

Zitat:durch das Wenn berechnet der nur die Mittelwerte von Zellen >0
So ist es

Gruß
Fred


Angehängte Dateien Thumbnail(s)
   

.xlsx   Durchschnittsertrag_fs2.xlsx (Größe: 28,29 KB / Downloads: 1)
[-] Folgende(r) 1 Benutzer sagt Danke an Fred11 für diesen Beitrag:
  • Eike
Antwortento top


Gehe zu:


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