calc - sverweis

de-users
#1

Hallo,
über viele Zeilen kann libreoffice eine Matix mit SVERWEIS abfragen?
ich habe ich eine Datei mit ca. 350000 Zeilen, aus der ich gern
zur vorhandenen Artikelnummer, den Beschreibungstext in meine
Arbeitstabelle eingefügt hätte.
leider bekomme ich nur #NV bzw. fehlerhafte Ausgaben.
Bsp: SVERWEIS(C12;Qelle.$C$2:$D$350000;2)
bring #NV, obwohl diese Artikelnummer in C245878 steht.

Armin

#2

Hallo Armin,

meines Wissens kann LO seit Version 4 theoretisch mit 1 Mio Zeilen
umgehen. Allerdings würde ich bei Deinen Größen die Daten in einer
regulären Datenbank wie MySQL oder PostgreSQL halten und die Infos über
eine Datenbankabfrage in das Sheet übernehmen.

Gruß
Oliver

#3

Hallo,

leider bekomme ich nur #NV bzw. fehlerhafte Ausgaben.
Bsp: SVERWEIS(C12;Qelle.$C$2:$D$350000;2)
bring #NV, obwohl diese Artikelnummer in C245878 steht.

Die SVERWEIS-Funktion hat vier Argumente. Du gibst nur drei an. Im
dem Fall, dass Du nach einer Artikelnummer suchst, ist es eigentlich
nur sinnvoll, wenn Du auch das vierte Argument mit angibst und zwar so:

SVERWEIS(C12;Qelle.$C$2:$D$350000;2;0)

Klappt´s dann?

https://help.libreoffice.org/Calc/Spreadsheet_Functions/de#SVERWEIS

Gruß,
Stefan

#4

Hallo,

leider bekomme ich nur #NV bzw. fehlerhafte Ausgaben.
Bsp: SVERWEIS(C12;Qelle.$C$2:$D$350000;2)
bring #NV, obwohl diese Artikelnummer in C245878 steht.

Die SVERWEIS-Funktion hat vier Argumente. Du gibst nur drei an. Im
dem Fall, dass Du nach einer Artikelnummer suchst, ist es eigentlich
nur sinnvoll, wenn Du auch das vierte Argument mit angibst und zwar so:

SVERWEIS(C12;Qelle.$C$2:$D$350000;2;0)

Klappt´s dann?

https://help.libreoffice.org/Calc/Spreadsheet_Functions/de#SVERWEIS

Gruß,
Stefan

#5

Hallo,

leider bekomme ich nur #NV bzw. fehlerhafte Ausgaben.
SVERWEIS(C12;Qelle.$C$2:$D$350000;2)
bring #NV, obwohl diese Artikelnummer in C245878 steht.

wenn der Hinweis auf die Sortierreihenfolge (4. Parameter) nicht
fruchtet: befindet sich die Quelle in einer anderen Datei? Damit kann LO
seit ein paar Versionen nicht mehr umgehen. Früher hatte das mal
funktioniert, und auch in anderen Office-Paketen klappt es reibungslos,
nur bei der aktuellen LibreOffice nicht mehr. Das wurde wohl auch schon
vor längerer Zeit mal als Bug gemeldet, aber da bis heute nichts
passiert ist, gehe ich davon aus, dass da auch nichts mehr passiert. Das
ist schade, da so einige meiner Anwendungen, die dereinst mal
funktionierten, seitdem für die Tonne sind.

Workaround, soweit es im konkreten Fall machbar ist: alles in einer
großen Datei zusammenziehen. Leider ergeben sich bei größeren
Datenmengen so häufig inakzeptable Laufzeiten.

#6

Früher hatte das mal
funktioniert, und auch in anderen Office-Paketen klappt es reibungslos,
nur bei der aktuellen LibreOffice nicht mehr. Das wurde wohl auch schon
vor längerer Zeit mal als Bug gemeldet, aber da bis heute nichts
passiert ist, gehe ich davon aus, dass da auch nichts mehr passiert.

Das

ist schade, da so einige meiner Anwendungen, die dereinst mal
funktionierten, seitdem für die Tonne sind.

das wäre echt schade

hoffentlich kommt da noch was

Frank

#7

Hallo,
Danke für die Hinweise.
Der Vorschlag mit der Datenbank von Oliver Rath war nicht schlecht,
nur habe ich ein Problem, sie SQL-Abfrage in der jeweiligen Tabellenzelle zu platzieren,
irgendwie fehlt mir da noch die Idee wie das gehen kann.
Ich habe zwar meine Datenbanktabelle mit F4, aber wie gehts weiter?

Hallo,

leider bekomme ich nur #NV bzw. fehlerhafte Ausgaben.
Bsp: SVERWEIS(C12;Qelle.$C$2:$D$350000;2)
bring #NV, obwohl diese Artikelnummer in C245878 steht.

Die SVERWEIS-Funktion hat vier Argumente. Du gibst nur drei an. Im
dem Fall, dass Du nach einer Artikelnummer suchst, ist es eigentlich
nur sinnvoll, wenn Du auch das vierte Argument mit angibst und zwar so:

SVERWEIS(C12;Qelle.$C$2:$D$350000;2;0)

Dein Hinweis mit dem 4. Parameter war gut. So klappt es schon besser.

Wenn ich jedoch einen Wert "links" der C-Spalte suche, bekomme ich falsche Werte

z.B.
SVERWEIS(A12;Quelle.$A$2:$C$350000;1;0) -- geht nicht !

Kann man nur nach "rechts" suchen?

Klappt´s dann?

https://help.libreoffice.org/Calc/Spreadsheet_Functions/de#SVERWEIS

Gruß,
Stefan

Gruß
Armin

#8

Hallo Armin,

SVERWEIS(C12;Qelle.$C$2:$D$350000;2;0)

Dein Hinweis mit dem 4. Parameter war gut. So klappt es schon besser.

Wenn ich jedoch einen Wert "links" der C-Spalte suche, bekomme ich
falsche Werte

z.B.
SVERWEIS(A12;Quelle.$A$2:$C$350000;1;0) -- geht nicht !

Du sagst also, dass
SVERWEIS(C12;Quelle.$C$2:$D$350000;2;0)
funkioniert, während
SVERWEIS(A12;Quelle.$A$2:$C$350000;1;0)
nicht geht.

Bitte spezifiziere genauer, was "geht nicht" konkret bedeutet.

Gruß,
Stefan

#9

Hallo Stefan,
folgende Abfrage steht in meiner Zieltabelle in der Zelle A5
=SVERWEIS(C5;Quelle.$A$2:$C$356404;1;1)

Als Ergebnis erhalte ich 9060875, das entspricht genau der letzten Zelle in der
Spalte A meine Quell-Tabelle.
Das Ergebnis bleibt das auch in den darunter liegenden Zellen der Spalte A meines Zieles
so, wobei der erste Parameter des SVERWEIS sich richtiger weise von auf C6 usw. ändert.

Aufbau Quelle: (356404 Zeilen)
Artikel-Nr; HArtNr, EAN,Bezeichnung, Menge15, ME, ListPreis

Aufbau Ziel: (2612 Zeilen)
Artikel-Nr; HartNr, EAN, Bezeichnung, Menge14, Menge15, ME, ListPreis

Im Ziel stehen nur die Scans der EAN und die Menge14 u. Menge14
Den Rest wollte ich mir aus der Quelle holen.
Dabei hat dank deines Hinweises das Eintragen der Bezeichnung und der Menge2015 funktioniert.

Gruß
Armin

#10

Hallo Armin,

folgende Abfrage steht in meiner Zieltabelle in der Zelle A5
=SVERWEIS(C5;Quelle.$A$2:$C$356404;1;1)

Als Ergebnis erhalte ich 9060875, das entspricht genau der letzten
Zelle in der
Spalte A meine Quell-Tabelle.
Das Ergebnis bleibt das auch in den darunter liegenden Zellen der
Spalte A meines Zieles

Alles deutet darauf hin, dass Du die SVERWEIS-Funktion und
insbesondere die Bedeutung des vierten Parameters noch nicht ganz
verstanden hast. :wink: :smiley:

Probier´s nochmal mit
=SVERWEIS(C5;Quelle.$A$2:$C$356404;1;0)
also mit 0 statt 1 beim letzten Argument. :wink:

Gruß,
Stefan

#11

Hallo Stefan,
danke für deine Geduld,

Hallo Armin,

folgende Abfrage steht in meiner Zieltabelle in der Zelle A5
=SVERWEIS(C5;Quelle.$A$2:$C$356404;1;1)

Als Ergebnis erhalte ich 9060875, das entspricht genau der letzten
Zelle in der
Spalte A meine Quell-Tabelle.
Das Ergebnis bleibt das auch in den darunter liegenden Zellen der
Spalte A meines Zieles

Alles deutet darauf hin, dass Du die SVERWEIS-Funktion und
insbesondere die Bedeutung des vierten Parameters noch nicht ganz
verstanden hast. :wink: :smiley:

Probier´s nochmal mit
=SVERWEIS(C5;Quelle.$A$2:$C$356404;1;0)
also mit 0 statt 1 beim letzten Argument. :wink:

o.k.
Aber die 0 als 4. Argument bringt mir nur #NV
Die gesuchte Artikelnummer zu dieser EAN ist aber vorhanden.

Klappt so noch nicht.

Gruß,
Stefan

Gruß
Armin

#12

Hallo,

Probier´s nochmal mit
=SVERWEIS(C5;Quelle.$A$2:$C$356404;1;0)
also mit 0 statt 1 beim letzten Argument. :wink:

o.k.
Aber die 0 als 4. Argument bringt mir nur #NV
Die gesuchte Artikelnummer zu dieser EAN ist aber vorhanden.

Bist Du Dir da ganz sicher? :wink:

Bist Du Du sicher, dass Du in der Quelle nicht vielleicht die EAN
als Textwert führst, während Du in C5 einen Zahlenwert hast?

Ich kann mir gut vorstellen, dass die Quelle eine importierte Liste
ist. Da wäre es nicht ungewöhnlich, dass durch den Import, die EAN
als Textwerte in Calc importiert wurden, auch wenn diese nur aus
Ziffern bestehen. Wenn Du bei SVERWEIS mit einem Zahlenwert in einer
Liste von Textwerten suchst, ist das Ergebnis #NV.

Gib doch mal in C5 die EAN mit einem voran gestellten Hochkomma ein,
sodass in C5 kein Zahlenwert, sondern ein Textwert steht, also zum
Beispiel:

'1234567890123

Gruß,
Stefan

#13

Wenn ich jedoch einen Wert "links" der C-Spalte suche, bekomme ich
falsche Werte

z.B.
SVERWEIS(A12;Quelle.$A$2:$C$350000;1;0) -- geht nicht !

Inwiefern 'geht nicht'?

Kann es evtl. sein, dass der Wert in A12 gar nicht im Suchvektor
vorhanden ist (bei Sortierreihenfolge = 0 muss der gesuchte Wert
exakt vorhanden sein).

Kann man nur nach "rechts" suchen?

Ja; bei SVWERWEIS muss der Suchvektor immer der erste Spalte stehen, und
der Ergebnisvektor kann nur in dieser Spalte oder rechts davon sein.

Wenn Du wo anders davon suchen willst, musst Du VERWEIS verwenden (da
kannst bzw. musst Du den Such- und Ergbenisvektor jeweils explizit
übergeben).

Wolfgang

#14

Wobei der letzte Parameter optional ist. Wird dieser weg gelassen, wird
der Wert 1 angenommen. Und dieser Wert bedeutet, dass der Suchvektor in
aufsteigender Reihenfolge sortiert sein muss (was bei Artikelnummern
eher nicht unbedingt der Fall ist).

Wolfgang

#15

Hallo Stefan,
folgende Abfrage steht in meiner Zieltabelle in der Zelle A5
=SVERWEIS(C5;Quelle.$A$2:$C$356404;1;1)

Wenn Du den 4. Parameter mit 1 an gibst, dann muss der Suchvektor (also
Quelle.$A$2:$A$356404) in strikt *aufsteigend* *sortierter* Reihenfolge
vorliegen; andernfalls (und das ist in denn /meisten Fällen der Fall)
musst Du den Wert *0* angeben.

BTW, FYI: Bei '1' wird *immer* ein Wert zurück gegeben, entweder (falls
gefunden) der betreffende, andernfalls der zum nächst kleineren Wert
gehörende. Bei '0' dagegen wird nur im Treffer-Fall ein Wert zurück
gegeben, andernfalls wird ein Fehler #NV aus gegeben.

Als Ergebnis erhalte ich 9060875, das entspricht genau der letzten Zelle
in der
Spalte A meine Quell-Tabelle.

<Kristallkugel>
Vermutlich steht da zufälligerweise die höchste Artikelnummer (und die
ist kleiner als der gesuchte Wert).
</Kristallkugel>

Das Ergebnis bleibt das auch in den darunter liegenden Zellen der Spalte
A meines Zieles
so, wobei der erste Parameter des SVERWEIS sich richtiger weise von auf
C6 usw. ändert.

Aufbau Quelle: (356404 Zeilen)
Artikel-Nr; HArtNr, EAN,Bezeichnung, Menge15, ME, ListPreis

Aufbau Ziel: (2612 Zeilen)
Artikel-Nr; HartNr, EAN, Bezeichnung, Menge14, Menge15, ME, ListPreis

Du suchst oben den Wert von Ziel.C5 (also die EAN-Nummer) in der Spalte
Quelle.A (also den Artikel-Nummern); das *kann* nicht klappen.

Wenn Du nach Artikelnummer suchst, dann muss der Aufruf

=SVERWEIS(A5;Quelle.$A$2:$C$356404;1;0)

lauten; wenn Du nach der EAN-Nummer suchst, kannst Du SVERWEIS nicht
verwenden, das nimmt als Suchvektor grundsätzlich immer die 1. Spalte
des übergebenen Vektors an. Da musst Du in dem Fall auf VERWEIS umsteigen:

=VERWEIS(C5;Quelle.$C$2:$C$356404;Quelle.$A$2:$A$356404)

Allerdings *muss* für VERWEIS der Suchvektor ebenfalls in
*aufsteigender* Reihenfolge sortiert sein.

Sollte das nicht der Fall sein, empfehle ich Dir, entweder

- die Quelltabelle entsprechend der Spalte C zu sortieren (Daten =>
Sortieren), oder, falls das aus irgend welchen Gründen unpraktikabel
sein sollte,

- die Reihenfolge der Spalten so zu ändern, dass sich der Suchvektor aka
die EAN-Nummer in der ersten Spalte befindet (dann kannst Du auch wieder
SVERWEIS mit Sortierreihenfolge = 0 benutzen)

Wolfgang

#16

Hallo Wolfgang,
danke für deine Hinweise,
mit deine Hilfe konnte ich die Werte übernehmen.
Das Wichtigste war der Hinweis auf das Sortieren,
offensichtlich ist das bei so langen Dateien wichtiger als bei kurzen.

Gruß
Armin

#17

NAK, das ist generell wichtig, unabhängig von der Länge; bei einer
Abfrage, die eine sortierte Liste voraus setzt, kann eine unsortierte
kurze Liste genauso falsche Werte liefern wie eine unsortierte lange Liste.

Wolfgang

#18

Hallo

Im Hinblick auf die _Laufzeit der Funktion_ ist es umso wichtiger zu sortieren je länger die Suchliste ist:

_Laufzeit im ungünstigsten Fall_:

Listenlänge unsortiert sortiert
10 10 ~4
100 100 ~7
1000 1000 ~10
10000 10000 ~14
100000 100000 ~17
1000000 1000000 ~20

oder allgemein, die Laufzeit mit Sortierparameter 0 steigt linear zur Listenlänge, die Laufzeit auf _sortierten_ Listen mit entsprechenden Sortierparameter steigt nur um log2(Listenlänge)

siehe dazu https://de.wikipedia.org/wiki/Binäre_Suche

Werner

#19

Hallo!

Das gilt natürlich nur für Datensätze die im Stack abgelegt wurden.
https://de.wikipedia.org/wiki/Stapelspeicher

Bei größeren Datenclouds gibt es natürlich völlig andere Sortiermethoden, die
einer Stack- Methode völlig überlgen sind.

Die angesprochene Struktur entspricht einem Stack. Durch eine effiziente Programmierung ließen sich auch hier Sub-Routinen schreiben, die recourcenschonend und zeitsparend sind. Aber auch hierbei ist eine saubere Sortierung das A und O.

In der binären Suche werden bedeutende Aspekte der Hardware unberücksicht gelassen. So wird Im Binärsystem maximal durch I/O mit einer Wurzel 2 gesucht... Ein 32- Bit System kann problemlos auf 50% Prozessorkapazität ausgelastet werden; also Wurzel 16; und ein 64-Bit System auf Wurzel 32; je nach Konfiguration sogar noch höher.

Ein effizienter Suchansatz wäre folgender:
- sortiere die Datei nach der Bezeichnung des möglichen Suchelements und stelle dies voran
- befindet sich das Suchelement überhaupt in der Datei?
wenn nein: Kein Element gefunden
wenn ja:
- teile die Datei durch 16 bzw 32 und bestimme in welchem Teil der Suchbegriff sich befindet
- wiederhole recursiv die Teilung bis Disvisor = 0
- gebe das Ergenis aus

Vorteil dieser Suche:
Es wird nicht jede Zeile durchsucht, sondern das Suchergebnis exponentiell eingeschränkt.
Es wird eine höhere CPU- Auslastung generiert, aber die Suchzeiten sind minimal.
Ein Suchergebnis ist meist innerhalb weniger Suchvorgängen (idR <10) gefunden.

Alle Klarheiten beseitigt?

Grüsse Guido