Effizienterer Vergleich mit Beispielwerten

Deutsch
#1

Hallo zusammen,

ausgehend von diesem Beispiel habe ich 2 Listen von Schlüsselwörtern und versuche herauszufinden, ob eine Zelle kein Wort aus beiden Listen enthält oder nur ein Wort aus der ersten Liste oder nur ein Wort aus der 2. Liste oder je ein Wort aus beiden Listen. Zur Prüfung werden die Worte aus den Listen konkateniert, mit " umfasst und mittels | getrennt. Zur Prüfung hab ich dafür reguläre Ausdrücke in den Optionen aktiviert. D.h., der Abgleich mit je einer Liste für sich ist nicht das Problem, sondern herauszufinden, ob ein Teil der zu untersuchenden Zelle in einer, in beiden oder in keiner von beiden Listen vorhanden ist.

Das Ergebnis funktioniert zwar, aber ist vielleicht zu umständlich. Fällt euch da noch was Besseres ein?

Die zu untersuchende Zelle hier ist L2. Die erste Liste ist in Blatt Keywords in den Zellen C1 bis C40. Die zweite Liste ebenfalls im Blatt Keywords in den Zellen D1 bis D29.
Die Formel arbeitet so:
Wenn ein Wort aus der ersten Liste enthalten ist, dann prüfe ob auch noch ein Wort aus der 2. Liste enthalten ist. Falls ja, gib “OT/NT” aus, anderenfalls gib “OT” aus. Ist kein Wort aus der ersten Liste enthalten, dann prüfe, ob eines aus der 2. Liste enthalten ist. Falls ja, gib “NT” aus, falls nein gib “UNKNOWN” aus.

=WENN(ISTZAHL(SUCHEN(VERBINDEN("|";1;"""";$Keywords.$C$1:$C$40;"""");L2));WENN(ISTZAHL(SUCHEN(VERBINDEN("|";1;"""";$Keywords.$D$1:$D$29;"""");L2));“OT/NT”;“OT”);WENN(ISTZAHL(SUCHEN(VERBINDEN("|";1;"""";$Keywords.$D$1:$D$29;"""");L2));“NT”;“UNKNOWN”))

Freue mich auf eure Tipps!

#2

Ich würde als erstes mal an SVERWEIS denken. Damit kannst Du direkt den Bereich der Schlüsselwörter Durchsuchen, ohne Verketten etc. Achtung: Parameter für unsortierte Listen verwenden, sonst gibt es keinen Fehlercode bei nicht vorhandenen Einträgen.

#3

Crosspost im englischen Teil, was meine erste Reaktion überflussig macht, da dort von @erAck als VLOOKUP genannt:

#4

Hier gehts mir mehr um die Effizienz, als das “wie”. Da ich ja mehrere Bedingungen in Abhängigkeit auswerte. Hilft da VLOOKUP/SVERWEIS in irgend einer Art? Und wie das SVERWEIS bzw. VLOOKUP helfen soll, wenn eines der Worte in den Keywords nur ein Teil des Inhaltes der zu prüfenden Zelle ist hab ich leider noch nicht verstanden.
In dem englischen Thread wurden da ja auch verschiedene Lösungsvarianten aufgezeigt, die funktionieren (VLOOKUP war der erste Vorschlag, danach kam ja der AGGREGATE-Teil usw.).
Hier habe ich dann eine der funktionierenden Varianten genommen und mit mehreren geschachtelten WENNs verarbeitet. Daher die Frage, ob dies auch effizienter ginge.

#5

Weil ich die Frage nicht so verstanden hatte wie sie gemeint war.

#6

Das Verbinden in D ist doppelt, kann also in eine Hilfszelle ausgelagert werden.
.
Wenn die genannte Formel in mehreren Zellen steht, lohnt es sich auch Verbinden in C auszulagern.
.
Statt 3 mal zu suchen beide Vergleichsstrings verketten und nur einmal suchen.
Nicht gefunden → in beiden Listen nicht drin.
Fundstelle>Läge des ersten Vergleichstring → nur in Liste2
Sonst nochmal nur in Liste 2 suchen
.
Datenanalyse, falls möglich. Häufigste in Listen gefundene Werte vorne in den Suchstring.

#7

Das geht mir hier genauso. Und auch beim zweiten Lesen habe ich die wichtige Randbedingung nicht in der Frage gefunden:

Genau das tut es auch nicht, aber ich finde das eben nicht in Deiner Frage. Wenn Du also Uhr auch in Taschenuhr und Armbanduhr finden willst, dann musst Du in Strings suchen (und in diesen Fall noch über Gross- und Kleinschreibung nachdenken…)

… und deshalb sollte man solche Vorab-Threads in der Frage verlinken.

#8

Oh, tut mir leid. Habe den Link in der Eingangsfrage nun noch ergänzt und versucht, die Frage zu konkretisieren.

Das Thema mit dem Auslagern hab ich verstanden. Da ich allerdings die Formel auf mehrere 1000 Einträge anwende, dann müsste ich auch für jeden dieser Einträge eigene Hilfszellen bereitstellen nehme ich an?
Wie allerdings das Verketten der beiden Suchstrings und damit verbunden der Abgleich funktionieren soll, das hab ich noch nicht verstanden.

Hier mal ein Beispiel (hab die restlichen Sachen entfernt für mehr Übersicht):
Examples.ods (16.0 KB)

#9

also drei Referenzen in Spalte L sind wahrlich etwas wenig!
Wahrlich_wenig_information.ods (20.8 KB)
Die irrelevanten Spalten sind ausgeblendet.
Es gibt zwei benannte Bereiche:

  1. »old« für die Alt-testamentlichen Schlüssel
  2. »new« für die Neu-testamentlichen Schlüssel

in Spalte Q habe ich die Suchbegriffe gekürzt ohne die Kapitelnummern:

=LINKS(L2;SUCHEN(" \d";L2)-1)

daraus ergibt sich in Spalte R:

=INDEX({"OT";"NT";"NIX"};WENNFEHLER(1*VERGLEICH(Q2;old;0)>0;2*WENNFEHLER(VERGLEICH(Q2;new;0)>0;3)))
#10

@karolus
Ist die Formel ganz korrekt?

Fehler
Fehler1077×306 48.1 KB

#11

Offenbar nicht :see_no_evil:
Edit:

=WENNFEHLER(INDEX({"OT";"NT"};WENNNV(1*VERGLEICH(Q2;old;0)>0;2*(VERGLEICH(Q2;new;0)>0)));"nix")
#12

Vielen Dank!

Ich hab die Beispiele extra knapp gehalten für mehr Übersicht. Anbei die volle Liste:
Examples.ods (497.9 KB)

Kurze Nachfrage: Ändern die sich denn automatisch mit, wenn z.B. mal Sachen ergänzt oder gelöscht werden müssen (ist bei diesen Beispielen eher unwahrscheinlich aber ich habe noch andere Listen und Abgleiche für die Kartendaten)?

Hab die Formel noch nicht selbst auf alle Einträge ausprobiert. Vom Verständnis her ist sie zumindest für mich nicht unebdingt einfacher lesbar/verständlich. Bin kein Experte, aber ist sie dann deutlich effizienter als geschachtelte WENNs?

#13

Wenn du schon nach Effizienz fragst, dann habe ich im Englischen Teil deines Mega-Threads eine Datenbank angehängt: How to check if a cell contains any of the values of another range of cells? - #23 by Villeroy
Das sollte mit zigtausenden Wörtern und Sätzen einigermaßen flutschen. Falls nicht, ließe sich diese Datenbank in eine wesentlich effizientere konvertieren. Und ja, es ist wesentlich einfacher als mit einer dummen Tabellenkalkulation.

#14

Vielen Dank! Muss ich mich mal rein arbeiten. Bisher habe ich noch nichts mit LibreOffice DBs zu tun gehabt, muss ich daher erstmal ans Laufen bringen.
Da die Quelle Spreadsheet-Daten sind, bin ich bisher dabei geblieben (aber das muss ja nicht für immer so bleiben :slight_smile: ).

#15

Nimm deine Tabellenkalkulation und kopiere die langen Sätze (in denen Worte vorkommen können) in eine Liste mit einer Spalte, max. 100 Zeichen. Die Matrixformel =MAX(LEN(A2:A99)) – eingegeben mit Strg+Umschalt+Enter – ermittelt die maximale Länge der Texte in A2:A99. Angenommen, dass in A1 ein Spaltenname steht.
Falls das Limit von 100 überschritten wird: Rechts-click Datenbanktabelle “Sentences”>Bearbeiten… und unten das Zeichenlimit auf das erforderliche Maß vergrößern. Je kleiner desto effizient.
Kopiere die Liste, klicke auf das Icon der Tabelle (nicht öffnen) und Strg+V.
Tabellenname: “Sentences”
Aktion: “Daten anhängen”
Kreuze an, ob du den Spaltennamen mitkopiert hast oder nicht.
[Fertig]

  • Öffne die Tabelle und lösche meine 4 Testzeilen mit Namen und Früchten.
  • Öffne die Tabelle “WORDS” und lösche alle meine Zeilen.
  • Öffne die Tabelle “GROUPS” und lösche alle meine Zeilen oder überschreibe die Gruppen-Namen. Trage deine eigenen Gruppennamen ein. Diese bezeichnen verschiedene Wortlisten. Schließe die Tabelle, kopiere das Icon und füge es in die Tabellenkalkulation ein. Die Datenbank vergleicht eine lange Wortliste mit verschiedenen Gruppen-IDs anstelle von verschiedenen Spalten. Auf diese Weise kannst du jederzeit einfach eine neue Liste anlegen, ohne irgendwelche Abfragen (“Formeln”) zu verändern, indem Du im Formular “Word Groups” eine neue Gruppe einträgst und dann die zugehörigen Worte.
  • Jetzt nimm deine erste Wortliste und setze rechts daneben die entsprechende ID-Nummer aus der Gruppenliste.
    Wort ID
    Eimer 2
    Besen 2
    Schrubber 2
    Setze die nächste Liste von Wörtern mit ID=3 direkt darunter. Wichtig ist, dass die ID-Nummern in der “GROUPS”-Tabelle vorkommen und so auf einen Gruppennamen verweisen.
    Kopiere die zwei Spalten Wörter und Gruppen-IDs und füge sie ein auf das Icon der Tabelle “WORDS” wie oben bei “Sentences” beschrieben.

Die Wortlängen und Gruppennamen haben ein Limit von je 30. Gegebenenfalls erhöhen wie bei den Sentences.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.