Excel Differenzwert von jeweils zwei Werten aus langer Liste

[Xs0nic]

Hi liebe Community,

ich habe eine relativ lange liste mit einer variablen Ergebniszelle welche sich durch das ändern einer Variable in ihrer gesamten Struktur ändert.
Ich müsste jetzt von jeweils dem ersten und letzten Wert die Differenz errechnen. Ich habe jetzt die Tabelle so Strukturiert das vorher und nachher immer eine 0 erscheint, leider komme ich hier nicht weiter da die Zelle ziemlich lang ist und mehrere Werte jeweils zu errechnen sind.

Zelle 1 welche sich stänig ändert schaut in etwa so aus (im Anhang als bild)

     A             B
1   0
2   0
3   0
4   0
5   1000     <---------  
6   0
7   0
8   0
9   2000      Differenz von Zeile 5 und Zeile 9
10 0
11 0
12 200        <-------
13 0
14 0
15 0
16 0
17 50          Differenz aus Zeile 12 und 17
18 0
19 0


Danke vorab für alle Helfende Hände!!!!
Gruß Chris

[neopa]

Hallo Chris,

mit Kombination von INDEX(), AGGREGAT() und ZEILE() lässt sich das wie folgt ermitteln.

Die erste Differenz der Werte in Spalte A:

=WENNFEHLER(INDEX(A:A;AGGREGAT(15;6;ZEILE(A$1:$A999)/(A$1:A$999<>0);(ZEILE(A1)*2-1)))-INDEX(A:A;AGGREGAT(15;6;ZEILE(A$1:$A999)/(A$1:A$999<>0);(ZEILE(A1)*2)));"")

die weiteren Differenzen erhältst Du durch herunter kopieren der Formel

[Fennek]

Hallo,

falls bei einer langen Liste die Performance von Aggregate etwas nachlassen sollte, kann man es auch per VBA lösen.

mfg

(ohne weitere Infos über den Sinn/Hintergründe liegt das Thema "unter dem Horizont")

[lupo1]

B2: =WENNFEHLER((A2<>0)*(A2-VERWEIS(2;1/(A$1:A1<>0);A$1:A1))*ISTGERADE(ZÄHLENWENN(A$1:A2;"<>0"));) mit Werten ab A1 gefüllt!

[neopa]

Hallo Xs0nic,

wenn als Differenz der zweite Wert minus dem ersten Wert gesucht sein sollte,
dann müsste meine Formel  nur umgestellt werden zu :

=WENNFEHLER(INDEX(A:A;AGGREGAT(15;6;ZEILE(A$1:$A999)/(A$1:A$999<>0);(ZEILE(A1)*2)))-INDEX(A:A;AGGREGAT(15;6;ZEILE(A$1:$A999)/(A$1:A$999<>0);(ZEILE(A1)*2-1)));"")

Der Vorteil meiner Lösungsvariante liegt darin, dass Du in kompakter Form lediglich die Differenzwerte aufgelistet bekommst und sofort den Überblick über diese hast und so auch weniger Formeln berechnet werden müssen.

Wenn Du aber trotzdem so etwas anstrebst, wie es lupo1 aufgezeigt hat, würde ich die Formel noch kürzen zu:

=WENN((A2=0)+(REST(ZÄHLENWENN(A$1:A2;"<>0");2)=1);"";(A2-VERWEIS(2;1/(A$1:A1<>0);A$1:A1)))

[lupo1]

Sonst bin ich ggü AGGREGAT eher skeptisch. Hier aber ist es eine gute Lösung:

A1:A9999: =KÜRZEN(ZUFALLSZAHL()*1,2)*ZUFALLSBEREICH(-4;8) ergibt Testdaten im Mittel jede 6. Zeile
B2:B9999: meine Formel benötigt 7 Sekunden (wegen ZÄHLENWENN über 50.000.000 Auswertungen)
B2:B999: neopas Formel (auf 9999 statt 999 erweitert) benötigt 1 Sekunde. Der Erwartungswert für Ergebnisse ist jede 12. Zeile; also muss man nur 1/12 der Zellen nach unten ausfüllen (hier einfachheitshalber 1/10).

Meine Formel lässt Excel bei 100.000 Verwendungen abstürzen (wegen ZÄHLENWENN über 5.000.000.000 Auswertungen). Ob das bei neopas Formel bei 10.000 Verwendungen auch passiert, habe ich nicht getestet.

Allerdings stehen die Ergebnisse von neopa lokal noch nicht am richtigen Platz. Nimmt man seine Formel nun als Hilfsspalte und jagt einen normalen VERGLEICH darüber neben A:A runter, könnte die Lösung tatsächlich immer noch schneller sein.