Verweisformel aus xl funktioniert in LO nicht

[LORI]

Das neue LO akzeptiert ja Adressen wie Zeile(1:1), ich arbeite mit LO Version: 5.1.3.2 auf Windows 7.

ich habe jetzt aber in xl folgende Formel: =VERWEIS(9^9;FINDEN("Q";A1;SPALTE(1:1))) , in A1 steht abcQrstQuv

Diese Formel liefert mir in xl 8, die Position des letzten "Q". Beim Öffnen mit LO steht da aber nur #WERT!

Der Versuch, mit Zeile zu arbeiten liefert mir aber nur die Position des 1. "Q"

Auch mit $-Setzungen komm ich zwar zum 2. "Q", aber nur, wenn ich die Zellen runterkopiere und nur in dem Bereich zwischen 1. und 2. Auftreten (5...8), später dann wieder #WERT!

In xl dagegen kann ich bei entsprechenden Absolutsetzungen die Spaltenformel beliebig nach unten oder rechts kopieren.

Da ich den gesamten Formelmechanismus nicht durchschaue, bitte ich um eine Erklärung mit Funktionalitäten, das wäre nett.

Und natürlich um eine -ggf ganz andere alternative- kürzere Lösung. (Die xlformel =TEIL(A1;FINDEN("#";WECHSELN(A1;"Q";"#";LÄNGE(A1)-LÄNGE(WECHSELN(A1;"Q";""))))+1;FINDEN("PC";A1)-FINDEN("#";WECHSELN(A1;"Q";"#";LÄNGE(A1)-LÄNGE(WECHSELN(A1;"Q";""))))-1) kenne ich, aber die Verweisformel wäre kürzer)

danke, LORI

[HeritzP]

Hallo,

wenn nur die Position von "Q" im Text gesucht wird, dann reicht auch das.

Code: Alles auswählen

=FINDEN("Q";$A$1;WENN(ISTTEXT(A1);1;A1+1))

Gruß HeritzP

[LORI]

danke erst mal für überhaupt eine Antwort!
Ich wollte aber das LETZTE Q finden bzw (kam nur in der komplizierten Zusatzformel zum Ausdruck) die Zahl zwischen dem letzten "Q" und dem die Zahl abschließenden Partikel "PC".
Ausserdem durchblick ich nach wie vor die xl-Formel mit Verweis nicht, geschweige denn den Grund, warum das in LO nicht funktioniert!

[craig]

Hallo Lori,

folgende Funktion durchsucht die Zelle A1 von rechts und liefert die Stelle 8 für "Q"

Code: Alles auswählen

=VERGLEICH("Q";RECHTS(A1;SPALTE(1:1));-1)

Zur Erklärung der Funktion VERGLEICH siehe in der LO-HIlfe unter VERGLEICH (Funktion) nach.

Die Verweis-Funktion funktioniert bei mir auch nicht. Habe verschiedenes versucht, siehe auch:
http://www.excelformeln.de/formeln.html?welcher=108

Ein Erklärung für die Funktion kann ich DIr nicht liefern.

Gruß

Craig

[lorbass]

Ich versuch's mal, auch wenn's vielleicht ein wenig holperig ist. Ein Beispiel zum Verständnis ist im Anhang dargestellt. Vielleicht hilft die Anschauung beim Verständnis

• Topic_16413.ods

  Anschauungsbeispiel

  (15.32 KiB) 374-mal heruntergeladen

1. A1 enthält gemäß Vorgabe den Wert abcQrstQuv.
2. Dieser Wert wird in der inneren Funktion =FINDEN( "Q"; A1; SPALTE(1:1) ) ausgewertet. Dies ist eine Matrix-Funktion, die als einzeilige 1024-spaltigen Matrix, d.h. als 1024 spaltiger Vektor zu verstehen ist. Die Spalten enthalten also die Ergebnisse der Funktionen
    – »finde das nächste Q in abcQrstQuv, beginnend an Stelle 1«,
    – »finde das nächste Q in abcQrstQuv, beginnend an Stelle 2«,
    – …
    – »finde das nächste Q in abcQrstQuv, beginnend an Stelle 1024«.
   Nach der zum letzten Q gehörenden Spalte enthält dieser FINDEN-Verktor nur noch die Werte #WERT!
   In meinem LO 4.2.8.2 ist das Konstrukt 1:1 noch nicht bekannt. Seine Bedeutung – »alle 1024 Spalten der Zeile 1« – müsste ich eigentlich durch A1:AMJ1 ersetzten. Für den persistenten Vektor begnüge ich mich aus Übersichtlichkeitsgründen mit den ersten 10 Spalten, die ich zudem in die Zeile 4 verlegt habe. Außerdem habe ich in Zeile 3 die den einzelnen Spalten zuzuordnenden Zeichen aus abcQrstQuv. I4 und J4 haben beide den Wert #WERT!, da nach dem zweiten Q in Spalte 8 kein weiteres Q folgt.
3. Mit der äußeren Funktion =VERWEIS( 9^9; FINDEN-Vektor ) wird's jetzt allerdings kryptisch. Die Syntax lautet
    VERWEIS( Suchkriterium; Suchvektor; Ergebnisvektor )
   Akzeptiert wird, auch
    VERWEIS( Suchkriterium; Suchvektor )
   In dem Fall wird der Suchvektor stillschweigend auch als Ergebnisvektor verwendet, jedenfalls solange ich den Suchvektor explizit angebe. Das Suchkriterium 9^9 ist eigentlich weit über's Ziel hinausgeschossen. Gesucht wird nach der größten Zahl (Position), die kleiner ist als 9^9 (= 387.420.489). Die Länge der Zeichenkette in A1 reicht eigentlich völlig aus.
4. Warum nun deine Funktion =VERWEIS( 9^9; FINDEN-Vektor ) (bei mir mit LO 4.2.8.2) immer nur mit dem Wert #WERT! endet, sobald ich den persistenten Vektor A4:I4 durch den transienten FINDEN-Vektor ersetze, ist mir ein Rätsel, das ich nicht lösen konnte.

Gruß
lorbass

[paljass]

Hallo allerseits,
@craig
Deine Vergleich-Formel funktioniert unter AOo nicht; sie gibt im Beispielsfall 7 statt 8 aus - möglicherweise ist das Verhalten unter LO ja ein anderes, das kann ich aber nicht testen.
Gänzlich falsch wird das Ergebnis, wenn ich das letzte Q lösche - dann ergibt deine Formel 6 statt 4 (vermutl weil von Rechts aus gesucht wird.


Ich habe auf der Basis von lorbass Finden-Formel ein wenig probiert und bin auf

Code: Alles auswählen

=MAX(WENN(ISTFEHLER(FINDEN( "Q"; A15; SPALTE(A15:Z15)));0;FINDEN( "Q"; A15; SPALTE(A15:Z15))))

als Matrixformel (Eingabe abschließen mit Strg-Umschalt-Enter) gkommen.

Gruß
paljass

[craig]

Hallo,
@lorbass
Vielen Dank für Deine Ausführung. Hat mir sehr geholfen.
Eine Vermutung:
Das die Funktion nicht funktioniert hat ggf. etwas damit zu tun, das Excel die leere Zellen anders behandelt als LO.
Siehe dazu unter LO-HIlfe --> Verweis(Funktion) und darunter befindet sich ein Link zu --> "Behandeln von leeren Zellen"

@paljass
Danke für die Berichtgung meiner abgeschriebenen Funktion.
Habe dies gestern Nacht auch festgestellt, war aber zu müde eine Berichtigung zu schreiben.

Deine Funktion durchsucht die Matrix von links nach rechts.
Beim String "abcQrstQuv" wird mir 4 angezeigt.
Der Wunsch von Lori war das Suchen von rechts nach links.

Ich wollte aber das LETZTE Q

Wenn ich wie folgt schreibe, wird mir das richtige Ergebnis angezeigt:

Code: Alles auswählen

=VERWEIS(100;FINDEN("Q";A15;SPALTE(A1:H1)))

Analyse:

Code: Alles auswählen

=VERWEIS(LÄNGE(A15);FINDEN("Q";A15;SPALTE(A1:A1)))	Ergebnis = 4
=VERWEIS(LÄNGE(A15);FINDEN("Q";A15;SPALTE(A1:D1)))	Ergebnis = 4

=VERWEIS(LÄNGE(A15);FINDEN("Q";A15;SPALTE(A1:E1)))	Ergebnis = 8
=VERWEIS(LÄNGE(A15);FINDEN("Q";A15;SPALTE(A1:H1)))	Ergebnis = 8

=VERWEIS(LÄNGE(A15);FINDEN("Q";A15;SPALTE(A1:I1)))	Ergebnis = #WERT!

Gruß

Craig

[balu]

Hallo Leute,

wie wärs denn mit dieser Formel -{KEINE Matrixformel }-?

Code: Alles auswählen

=SUCHEN("(|Q)[^Q]+$";A1)

Sie stammt wohl nicht von mir, aber in einer ähnlichen Weise hatte ich sie schon erfolgreich eingesetzt. Da suchte ich aber nicht nach dem Buchstaben Q sondern nach dem letzten Slash / in einer Hyperlinkadresse. Und nachdem ich mir die Regulären Ausdrücke angeschaut hatte, dachte ich mir das ich den / durch Q ersetze, und schwups bekam ich die Position des letzten Q angezeigt. Und wenn Q nur einmal vorhanden ist, dann wird die dementsprechende Position des einmaligem Q angegeben.

Ich weiß aber nicht ob die auch in Excel funktioniert, in OOo 3.2.1, AOO 4.1.2 und LO 4.2.1.1 funktioniert sie tadellos.

ABER ACHTUNG!!!

In den Optionen müssen die 'Regulären Ausdrücke in Formeln' aktiviert sein, denn sonst kommt es zur Fehlermeldung #WERT!.

Es spielt auch keine Rolle ob Q oder q gesucht wird, da ja SUCHEN die Groß-/Kleinschreibung ignoriert.

Viel Spaß



Gruß
balu

[paljass]

Hi,
@craig

Der Wunsch von Lori war das Suchen von rechts nach links.

Stimmt so nocht. Er schrieb

Ich wollte aber das LETZTE Q finden

Ich schrieb ja als Matrixformel (Eingabe abschließen mit Strg-Umschalt-Enter) Mach es und freu dich über das Ergebnis.
Nach wie vor gibt deine Formel nur bei mehreren gleichen Buchstaben den richtigen Wert aus. Wenn der gesuchte Buchstabe nur einmal vorhanden ist, wird#Wert ausgeben, weil du noch keine Fehlerbehandlung eingebaut hast.

@balu
Die Formel hab ich auch im Fundus - auch abgeschrieben
Weil aber reguläre Ausdrücke für viel schwer durchschaubar sind - für mich auch und offensichtlich auch für dich - , hab ich die Formellösung bevorzugt.

Gruß
paljass

[balu]

Hallo paljass,

Die Formel hab ich auch im Fundus - auch abgeschrieben

Karolus!?

Weil aber reguläre Ausdrücke für viel schwer durchschaubar sind - für mich auch und offensichtlich auch für dich -

Och Du, bei der geht das noch relativ leicht.
Wenn man eine funktionierende hat, die auch real angewendet wird, dann kan man sich besser und leichter damit auseinander setzen. Klar, verstehe ich noch nicht alles, aber für so eine kleinigkeit reicht es.

hab ich die Formellösung bevorzugt.

Die ja auch so weit funktioniert.
Der einzige Punkt der mir persönlich nicht gefällt, was aber wirklich Geschmacksache ist, ist der, das Du FINDEN anwendest während ich lieber mit SUCHEN arbeite weil dabei die Groß-/Kleinschreibung ignoriert wird.


Gruß
balu