4
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:
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
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
Share on Facebook
Share on Twitter
Share on Reddit
Share on LinkedinPlease also mark the comments that contributed to the solution of the article
Content-Key: 279388
Url: https://administrator.de/contentid/279388
Printed on: April 26, 2024 at 06:04 o'clock
4 Comments
Latest comment
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.
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
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));"")
Grüße Uwe
Moin Vancouverona,
ich habe die Aufgabenstellung etwas anders verstanden als colinardo - ich interpretiere es so:
Demnach würde ich es mit kleiner Vorarbeit - dem Benennen von Bereichen und einer Hilfsspalte - angehen.
Also:
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
So, der Rest nach diesem ganzen Vorbereiten ist dafür einfach und lesbar.
Der Wert in der letzten Zeile (in Spalte B) ist
Der Wert der letzten vorangegangenen gleichnamigen Tages-Zeile (also z.B. der vorangegangene Montag 18 Zeilen höher) ist
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
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,
Grüße Uwe
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
Moin colinardo,
ja, sorry - hast Recht.
Deine Formel funktioniert auch bei Lücken - hatte zu flüchtig draufgeschaut.
Mea culpa.
Grüße
Biber
ja, sorry - hast Recht.
Deine Formel funktioniert auch bei Lücken - hatte zu flüchtig draufgeschaut.
Mea culpa.
Grüße
Biber
