Clever-Excel-Forum

Normale Version: Zellen vergleichen und Abweichungen ab x% erkennen
Du siehst gerade eine vereinfachte Darstellung unserer Inhalte. Normale Ansicht mit richtiger Formatierung.
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
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
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
Hallo Andrej,

zum Beispiel so:

Arbeitsblatt mit dem Namen 'Tabelle1'
ABCDEFGHIJKLMNOPQ
1Jahr20102011201220132014201520162017201820192020ErgebnisSchwellenwert-0,75
2Verkaufszahl1900.000800.0001.000.000500.000500.000450.000440.000390.000180.0001502018
3Verkaufszahl2500600400300200201918102014
4Verkaufszahl390008000700060005000400040040402015

ZelleFormel
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