Microsoft Excel: Zelladresse zur Verwendung in einer Formel herausfinden

Hallo zusammen,
für eine Auswertung brauche ich eine Formel, die in einer Zelle eine Differenz aus zwei Werten bildet. Soweit, so einfach.
Erschwerend: Die Differenz soll aus den Zellen in der Spalte B gebildet werden, wenn in Spalte A ein bestimmter Wert steht. Auch kein Problem.

Herausforderung: Es soll nicht für jeden Treffer in der Spalte A die Differenz gebildet werden, sondern nur für die beiden jeweils letzten Treffer.

Beispiel:

Tag	Wert	Differenz
Montag	100
Dienstag	110
Mittwoch	120
Donnerstag	130
Freitag	140
Montag	110	Montag 2 - Montag 1 (= 110 - 100)
Dienstag	120	Dienstag 2 - Dienstag 1 (= 120 - 110)
Mittwoch	130	Mittwoch 2 - Mittwoch 1 (= 130 - 120)
Donnerstag	140	Donnerstag 2 - Donnerstag 1 (= 140 - 130)
Freitag	150	Freitag 2 - Freitag 1 (= 150 - 140)
Montag	120	Montag 3 - Montag 2 (= 120 - 110)
Dienstag	130	Dienstag 3 - Dienstag 2 (= 130 - 120)
Mittwoch	140	Mittwoch 3 - Mittwoch 2 (= 140 - 130)
Donnerstag	150	Donnerstag 3 - Donnerstag 2 (= 150 - 140)
Freitag	160	Freitag 2 - Freitag 1 (= 160 - 150)

Die Liste liegt nicht zwingend so vollständig vor, es können auch mal einzelne Tage ausfallen.

Ich brauche also die Differenz vom Wert in der Zeile für den Tag mit der größten Zeilennummer und vom Wert in der Zeile für den Tag mit der zweitgrößten Zeilennummer.

Vielleicht hat ja jemand 'ne Idee von euch.

Grüße
Jörg

Bitte markiere auch die Kommentare, die zur Lösung des Beitrags beigetragen haben

Content-ID: 279388

Url: https://administrator.de/contentid/279388

Ausgedruckt am: 22.11.2024 um 00:11 Uhr

4 Kommentare

Neuester Kommentar

Hallo Joerg,
das lässt sich z.B. mit einer Matrix-Formel lösen:
kopiere folgende Formel nach deinem obigen Beispiel in Zelle C7 und schließe sie mit STRG+SHIFT+ENTER ab. Danach kannst du sie durch nach unten ziehen kopieren.

=WENNFEHLER($B7-INDEX($B$2:$B6;VERGLEICH(1;1/($A7=$A$2:$A6);1));"")

BITTE BEACHTEN: Dies ist eine Matrix-Formel. Man muss die Formel mit STRG-SHIFT-ENTER abschließen anstatt nur mit einem "einfachen" Enter. Eine Matrixformel erkennt man in Excel daran das in der Formelzeile die Formel mit geschweiften Klammern eingefasst wird.

Grüße Uwe

Moin Vancouverona,

ich habe die Aufgabenstellung etwas anders verstanden als colinardo - ich interpretiere es so:

wenn in der letzten Zeile ein bestimmter Tag steht, zB. Montag, dann willst du die Differenz der Werte von genau diesem Montag und dem letzen Motag davor in der Spalte
allerdings besteht die Tages-Abfolge nicht immer aus allen 7 Tagen (wie Colinardo mit der Matrixformel es umgesetzt ha)t, sondern es gibt Lücken (Beispiel: in letzter Zeile steht "Montag", der vorletzte erfasste Montag steht aber nicht 7 Zeilen, sondern 18 Zeilen davor).

Demnach würde ich es mit kleiner Vorarbeit - dem Benennen von Bereichen und einer Hilfsspalte - angehen.
Also:

wenn die "Tage" in Spalte A stehen, dann A1:A500 benennen als "DieTage"
wenn die "Werte" in Spalte B stehen, dann B1:B500 benennen als "DieWerte"
dann noch eine Variable definieren mit dem Namen "Suchtag" und der Formel ==INDEX(DieTage;VERGLEICH("";DieTage;-1))

In dieser Variable (oder einem "Suchtag" benannten Feld, in dem diese Formel steht), erscheint dann der Text "Montag", falls in der letzten Zeile der Montag steht.

Dann bitte noch eine freie Spalte opfern (ich nehme mal Spalte C).
In Zelle C2 die Formel =ZÄHLENWENN(A$1:A2;Suchtag) tippen und nach unten kopieren, soweit nötig.

wenn die Hilfsspalte in Spalte C steht, dann C1:C500 benennen als "DerNteTag"

So, der Rest nach diesem ganzen Vorbereiten ist dafür einfach und lesbar.
Der Wert in der letzten Zeile (in Spalte B) ist
=INDEX(DieWerte;VERGLEICH("";DieTage;-1))

Der Wert der letzten vorangegangenen gleichnamigen Tages-Zeile (also z.B. der vorangegangene Montag 18 Zeilen höher) ist
=INDEX(DieWerte;VERGLEICH(ZÄHLENWENN(DieTage;Suchtag)-1;DerNteTag;0);1)

Die Differenz zwischen beiden... bekommst du hin.

Die Hilfsspalte C (oder welche auch immer kannst du ausblenden, wenn sie optisch stört.
Die benannten Bereiche habe ich in Länge 500 Zeilen angenommen - ggf. anpassen.

Okay, etwas aufwendiger als die eine Matrixformelzeile - sollte aber zum richtigen Ergebnis führen.
[Edit]
Okay, ganz vergessen - wichtiger als die Kompaktheit der Formel ist natürlich der Plan, mit dem ich die Lösung anstrebe.
Deshalb in Stichworten:
- Wenn der letzte Tag in meiner Liste ein "Montag" ist, dann zähle ich, der "wievielte" Montag es insgesamt ist und bekomme zB den Wert 4.
- Der "vorangegangene Montag ist damit logischerweise der 3. in der Liste
-> von dem ziehe ich Adresse und den Wert aus der Spalte B.
[/Edit]

Grüße
Biber

Hallo Biber,

allerdings besteht die Tages-Abfolge nicht immer aus allen 7 Tagen (wie Colinardo mit der Matrixformel es umgesetzt ha)t, sondern es gibt Lücken (Beispiel: in letzter Zeile steht "Montag", der vorletzte erfasste Montag steht aber nicht 7 Zeilen, sondern 18 Zeilen davor).

dem kann ich so nicht beipflichten, denn die Formel berücksichtigt keinen festen Bereich von 7 Tage, sondern sie ist flexibel (siehe das fehlende Dollarzeichen in der Bereichsdefinition für die Zeilen und das festsetzen von $A$2). Der Suchbereich ist also immer fest von $A$2 bis eine Zelle vor der gesuchten Zelle wenn man die Formel runter kopiert, der Bereich verschiebt sich also nicht sondern vergrößert sich nur nach unten hin. Aus diesem Bereich wird dann immer der letzte gefundene Tag genommen, so wie es der TO nach meiner Meinung haben wollte.

Grüße Uwe