Zellen vergleichen und Abweichungen ab x% erkennen

[AndrejDelany]

Hallo,

ich habe Verkaufszahlen von 2010 - 2019 und man erkennt einen krassen abbruch von 2018 zu 2019. Artikel lief beim Kunden um Jahr 2018 aus und ab 2019 herrscht nur noch ersatzteilbedarf.

Ich suche nun eine Formel, die diesen krassen abbruch erkennt und mit in meinem Beispielt in Zelle M2  das Jahr 2018 ausspuckt.

Ich spiele bereits mit INDEX und VERGLEICH herum, aber ich habe die Lösung noch nicht gefunden, ich hoffe, jemand kann mir hier weiterhelfen

Gruß
Andrej

[PAndersen]

Hallo Andrej.

Hier ist ein Vorschlag von mir.

In Zeile 4 habe ich die prozentuale Veränderung zum Folgejahr mit +(C2-B2)/B2 ausgerechnet.

Jetzt will ich den Befehl VERGLEICH verwenden, der aber nur für Zeilennummer verfügbar ist, und habe deshalb mit MTRANS die Zeilen 1 und 4 in Spalten ab Zeile 9 transferiert. Da es ein Matrixbefehl ist, ist er wenig änderungs-freundlich und empfehle Dir von vornherein Deine Darstellung so umzustellen. Viele Zeilen untereinander sind übersichtlicher als Spalten nebeneinander.  

Nachdem jetzt die Prozent-Veränderungen und die Jahreszahl in C: und D: stehen, verwende ich
 
   KKLEINSTE um Spalte C absteigend zu sortieren
   dann VERGLEICH um zu den absteigenden Prozenten die jeweilige Zeilennummer zu finden 
   und dann INDEX um aus der Zeile die zugehörige Jahreszahl auszuweisen.

Das Ergebnis in Spalte E sieht dann so aus

INDEX(D$9:D$18;VERGLEICH(KKLEINSTE($C$9:$C$18;ZEILE() -8);$C$9:$C$18;0);1) und weist die schlechtesten Jahre zuerst aus.

Ich hoffe, Dir weitergeholfen zu haben.

Fragen und Vorschläge immer gerne.

Peter Andersen

[AndrejDelany]

Hallo Peter,

auf jeden Fall eine sehr interessante Lösung. Da ich ca. 3000 Arikel habe die mit diveresen Werten alle untereinander stehen, konnte ich meine Tabelle nicht drehen (Zeilen/Spalten drehen) 
ich habe es dann relativ ähnlich gelöst und für jedes jahr eine Abfrage gemacht, ob die Veränderng zum Vorjahr größer war als x%. mittels VERGLEICH(MAX konnte ich so die erste große Abweichung und somit das Auslaufsjahr des Artikels ermitteln.

Ist nicht besonders elegant gelöst, aber Zielführend

Vielen Danke für deine Mühe, ich habe mir deine Formel / Beschreibung gesichert und werde sie vielleicht bei einem anderen Fall mal nutzen ;)

Gruß
Andrej

[Fred11]

Hallo Andrej,

zum Beispiel so:

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Jahr	2010	2011	2012	2013	2014	2015	2016	2017	2018	2019	2020		Ergebnis		Schwellenwert	-0,75
2	Verkaufszahl1	900.000	800.000	1.000.000	500.000	500.000	450.000	440.000	390.000	180.000	150			2018
3	Verkaufszahl2	500	600	400	300	200	20	19	18	1	0			2014
4	Verkaufszahl3	9000	8000	7000	6000	5000	4000	400	40	4	0			2015

Zelle	Formel
N2	{=INDEX($B$1:$L$1;VERGLEICH(WAHR;($C2:$M2-$B2:$L2)/$B2:$L2<$Q$1;0))}
N3	{=INDEX($B$1:$L$1;VERGLEICH(WAHR;($C3:$M3-$B3:$L3)/$B3:$L3<$Q$1;0))}
N4	{=INDEX($B$1:$L$1;VERGLEICH(WAHR;($C4:$M4-$B4:$L4)/$B4:$L4<$Q$1;0))}

Achtung, Matrixformel enthalten!

Die geschweiften Klammern{} werden nicht eingegeben.

Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.

Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

Die Formel in N2 (Matrixformel => Eingabe mit Strg+Shift+Enter) ermittelt das erste Jahr, in dem die Verkaufszahl im Folgejahr um mehr als X Prozent (=> Wert in Zelle Q2) verringert ist.
Formel dann nach unten kopieren...

Siehe angehängte Beispieldatei

Gruß
Fred