Formel gesucht !!!

[erniyvonne]

Hallo,

ich hätte da folgendes Probleme:

in Spalte B stehen in verschiedenen Zeilen die Werte "L" und "B" - dabei kommen diese Werte in beliebiger Reihenfolge und Anzahl vor:

L
B
L
L
L
B

Für eine Auswertung ist es nun notwendig, dass ich den letzten Wert von
"B" und den davorliegenden Wert "B" ermitteln kann. Dabei ist es mir egal, ob ich dann als Rückmeldung den Wert aus Spalte D jeweils bekommen und zur die Zeile.

Irgendjemand einen Lösungsansatz? Ich hab's bis jetzt mit SVERWEIS, WVERWEIS, VERGLEICH und deren Kombination versucht. Ich habe jetzt keinen Lösungsansatz mehr! Geht das überhaupt?

[erniyvonne]

Ich habe mir da mal was ausgedacht - allerdings mit Fehler 508 (Fehler in der Klammerung) - aber ich seh' keinen Fehler in der Klammerung ...

=VERGLEICH("B";ADRESSE(VERGLEICH("B";B1:B51;0);2;4):B51;0)

Wo ist also der Fehler in der Klammerung ???

[Eia]

Falls Du die Tabelle nach dieser Spalte sortieren kannst, liefert:
=VERGLEICH("B";B1:B999;-1)
die letzte Position von B. Wenn der Bereich bei B1 beginnt, entspricht das der Zeilennummer, sonst musst Du entsprechend hinzurechnen, mit ADRESSE bekommst Du dann die vollständige Zelladresse.

Die vorletzte Fundstelle liegt dann entweder genau eine Zeile davor oder es gibt sie nicht (wenn "B" nur einmal vorkommt)

Aber wie gesagt: das Ganze bedingt, dass die Tabelle nach dieser Spalte sortiert ist.

[Toxitom]

Hey erniyvonne

Wo ist also der Fehler in der Klammerung ???

Der Fehler dürfte nicht in der Klammerung liegen - eher in der Kombination Adresse():B51 - denn Adresse liefert die Zelladresse als String zurück, also soetwas wie "B1", erwartet wird aber ein Zellbezug (Formeladresse).
Also, eine einfache Lösung haben ich aktuell auch nicht, aber das dürfte Dein Fehler sein.

Gruss
Thomas

[FranzX]

Hallo erniyvonne,

Probier mal folgendes:

In A2 steht die Zahl 0
in B2 der gesuchte Buchstabe z.B.: L, zu dem die Werte gesucht werden.

ab Zeile 3 beginnen deine Einträge, wobei in der Spalte A die Zahlen und in der Spalte B die Buchstaben stehen.

Schreibe in eine Zelle die Formel:

=INDIREKT(ADRESSE(MAX(WENN($B$2:$B$100=B2;ZEILE($A$2:$A$100);""));1))

und schließe sie mit Strg+Umschalt+Enter ab (Matrixformel).
Diese Formel ergibt die letzte Zahl des Buchstabens.
Die Zelladresse dazu liefert die Formel:

=ADRESSE(MAX(WENN($B$2:$B$100=B2;ZEILE($A$2:$A$100);""));1)
Schließe sie mit Strg+Umschalt+Enter ab (Matrixformel).

In eine andere Zelle schreibst du die Formel:

=INDIREKT(ADRESSE(KGRÖSSTE(WENN($B$2:$B$100=B2;ZEILE($A$2:$A$100);"");2);1))
und schließt auch diese Formel mit Strg+Umschalt+Enter ab (Matrixformel).
Diese Formel liefert den vorletzten Wert.
Die Zelladresse dazu:

=ADRESSE(KGRÖSSTE(WENN($B$2:$B$100=B2;ZEILE($A$2:$A$100);"");2);1)
Schließe sie mit Strg+Umschalt+Enter ab (Matrixformel).

Gruß
FranzX

[erniyvonne]

Spitzenmässig! Super vielen Dank!

Doch jetzt geht's weiter:

Aus den beiden ermittelten Werten bilde ich die Differenz. Kommt jetzt aber ein Eintrag hinzu verändern sich die Werte (ich weiss - toller Satz - ist aber schon spät!) Die gebildete Differenz soll sich aber nicht mehr ändern - da gibt's doch was von Calc, oder?

Vielleicht noch ein paar Hintergrundinformationen:

Das ganze soll eine Art "Tankbuch" werden - aber: ich bin Gasfahrer (LPG) und brauche zusätzlich Startbenzin. Jetzt könnte ich den Tageskilometerstand auch von Hand eingeben - aber das wäre ja zu einfach. Deshalb habe ich die Spalte mit den (B)enzin und (L)PG angelegt. Oberhalb dieser Spalte lasse ich mir jetzt die Ergebnisse der Formeln

=INDIREKT(ADRESSE(KGRÖSSTE(WENN($B$22:B51="B";ZEILE($E$22:E51);"");2);5;4))
und
=INDIREKT(ADRESSE(MAX(WENN($B$22:$B$36="B";ZEILE(E22:E36);""));5;4))

anzeigen (in Spalte E steht der Gesamtkilometerstand) und bilde in der Spalte G die Differenz. Und diese soll sich beim nächsten Benzin-Tanken nicht ändern.

[FranzX]

Hallo erniyvonne,

irgendwie verstehe ich dein Problem nicht ganz.
Wenn du in jeder Eintragszeile deine Differenz-Berechnung durchführst, wieso soll sich dann der vorhergehende Eintrag ändern? - Die Berechnung erfolgt doch mit einer neuen Formel - oder?

Übrigens ist es ja Sinn einer Formel, dass sie sich dynamisch an veränderte Eingaben anpasst und die veränderten Ergebnisse ausweist.
Wenn du das nicht willst, musst du die Zellinhalte in statische Werte umwandeln entweder über Kopieren und dann über Bearbeiten|Inhalte einfügen - Zahlen, ohne Häkchen bei Formel, den Wert fixieren, oder das ganze mit einem Makro erledigen, das du auch auf eine Symbolschaltfläche legen kannst.

Mit dem Makro:

Code: Alles auswählen

rem ------------------------------
rem Wert einer Zelle festschreiben
rem ------------------------------
sub FesterWert 
dim document   as object
dim dispatcher as object
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())
dim args3(5) as new com.sun.star.beans.PropertyValue
args3(0).Name = "Flags"
args3(0).Value = "SVDNT"
args3(1).Name = "FormulaCommand"
args3(1).Value = 0
args3(2).Name = "SkipEmptyCells"
args3(2).Value = false
args3(3).Name = "Transpose"
args3(3).Value = false
args3(4).Name = "AsLink"
args3(4).Value = false
args3(5).Name = "MoveMode"
args3(5).Value = 4
dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args3())
end sub

... kannst du zum Beispiel den Inhalt einer markierten Zellen oder eines markierten Bereichs in statische Werte umwandeln.



Nachtrag:


TIPP:

Sehr einfach (wenn mal es einmal kapiert hat!) kann man Tabellen mit der Funktion VERSCHIEBUNG() auswerten.

Mit dieser Funktion kannst du zum Beispiel dein Fahrtenbuch für jeden beliebigen Zeitraum auswerten und analysieren.

Beispiel:
In der Zeile 14 stehen die Spaltenüberschriften:
Datum | KM-Stand | Getankt | Bezahlt | Gefahrene KM | Durchschn. Verbrauch

Ab Zeile 15 beginnt die Datenerfassung.

In der Zelle I4 steht das Anfangsdatum des auszuwertenden Zeitraums
In der Zelle I5 steht das Enddatum des auszuwerten Zeitraums

Die gefahrenen KM des oben ausgewählten Zeitraums erhältst du mit der Formel:

=SUMME(VERSCHIEBUNG(A14;(VERGLEICH(I4;A14:A501;1));4;(VERGLEICH(I5;A14:A501;1)-VERGLEICH(I4;A14:A501;1));1))

Den Verbrauch in Litern zeigt die Formel:

=SUMME(VERSCHIEBUNG(A14;(VERGLEICH(I4;A14:A501;1));2;(VERGLEICH(I5;A14:A501;1)-VERGLEICH(I4;A14:A501;1));1))

Die gesamte Tankkosten errechnet die Formel:

=SUMME(VERSCHIEBUNG(A14;(VERGLEICH(I4;A14:A501;1));3;(VERGLEICH(I5;A14:A501;1)-VERGLEICH(I4;A14:A501;1));1))

Wie du siehst, ist es immer die gleiche Formel, einzig der Spaltenparameter hat sich geändert.

Übrigens: das Start- bzw. Enddatum kannst du auch über Daten|Gültigkeit und Auswahl Zellbereich z.B.: A14:A500 auswählen.

Vielleicht nützt dir diese Anregung.
Gruß
FranzX