Bugs beim SVERWEIS ?

de-users
#1

Hallo,

weil ich noch nicht sehr lange hier mitlese, möchte ich vor -
wahrscheinlich - ergebnisloser Suche im Archiv einfach mal rundfragen,
ob es bekannte Dysfunktionalitäten beim SVERWEIS von CALC gibt, die
vielleicht schon an anderer Stelle diskutiert worden sind ?

Leider kann ich Euch keinen richtigen Aufhänger für meine Frage geben,
denn die Formel macht nicht annähernd das, was sie leisten soll :frowning:

Sie funktionierte einmal korrekt und nach Änderungen in der Tabelle
liefert sie nun anscheinend das Suchargument zurück ???

#2

Hallo,

Leider kann ich Euch keinen richtigen Aufhänger für meine Frage geben,
denn die Formel macht nicht annähernd das, was sie leisten soll :frowning:

um den Aufhänger wirst Du wohl nicht herumkommen, denn bei /mir/
funktioniert sie, wie /ich/ es erwarte. Vielleicht erläuterst Du einmal
genauer, was die Formel leisten soll, und wie sie derzeit bei Dir
aussieht, dann könnte man vermutlich helfen.

#3

Hallo Tom,

Tom schrieb:

Hallo,

weil ich noch nicht sehr lange hier mitlese, möchte ich vor -
wahrscheinlich - ergebnisloser Suche im Archiv einfach mal rundfragen,
ob es bekannte Dysfunktionalitäten beim SVERWEIS von CALC gibt, die
vielleicht schon an anderer Stelle diskutiert worden sind ?

Leider kann ich Euch keinen richtigen Aufhänger für meine Frage geben,
denn die Formel macht nicht annähernd das, was sie leisten soll :frowning:

Sie funktionierte einmal korrekt und nach Änderungen in der Tabelle
liefert sie nun anscheinend das Suchargument zurück ???

Lies bitte den Hilfetext genau. Da steht etwas über die Voraussetzungen. Beachte, dass die Daten u.U. aufsteigend sortiert vorliegen müssen.

Mit freundlichen Grüßen
Regina

#4

Ich würde die Datei anhängen, aber ich meine, gelesen zu haben, dass der
List-Server hier das nicht mag !??

#5

Hallo,

Ich würde die Datei anhängen, aber ich meine, gelesen zu haben, dass der
List-Server hier das nicht mag !??

das mag so gut wie keine Mailingliste. Schreib doch einfach mal Deine
Formel hier rein, und was Du davon erwartest. Irgendwo muss man ja mal
anfangen.

#6

Hast Du die Reihenfolge der Spalten geändert, oder neue eingefügt o. ä.?
SVERWEIS und WVERWEIS arbeiten ja über den Index für die Spalte bzw.
Zeile, und diese Angabe ist keine Bezugsreferenz, d. h. sie ändert sich
nicht mit.

Wolfgang

#7

Tom schrieb:

Ich würde die Datei anhängen, aber ich meine, gelesen zu haben, dass der
List-Server hier das nicht mag !??

Du kannst deine Datei auch irgendwo im Internet hinterlegen und hier den Link mailen.
Das geht z.B. über http://www.file-upload.net/ oder über deine Dropbox, wenn du eine hast oder...

Aber eine Beschreibung, was du erwartest, musst du auf jeden Fall liefern!

Michael

#8

... genau, und weil es datenmäßig nichts Wildes ist, versuche ich es
einfach mal hier:
(Sollte das nicht klar werden, komme ich gerne auch auf Dropbox zurück)

Struktur:
Index1 Index2 Index3 Index4
A B C D
1 Zoll umrech Preis Marke [Spaltenüberschriften, Kopfzeilen]
2 [hierhin sollen alle Daten des Gerätes mit dem minimalen Preis]
3 [hier beginnen die eingegebenen Daten, ca. 700 Zeilen]

Ich hoffe, das erklärt sich soweit selbst. Ich arbeite also mit
Spaltenüberschriften, demzufolge sind meine "Datensätze" in Zeilen
angeordnet - allerdings NICHT geordnet. [Kann ich hier schon über die
Hinweise von Regina und Stefan stolpern ? <-- natürlich nicht über die
Hinweise als solche ;-)]

Irgendwo wird die Preis-Spalte ihr Minimum haben. Das suche ich mir mit
=MIN(WENN(C3:C799<>0;C3:C799)) und schreibe es in C2 :slight_smile:

Natürlich nützt mir dieser Wert allein knapp die Hälfte, also suche ich
die Zeile, in der das Minimum steht und gebe auch die anderen Werte in
der zweiten Zeile aus (theoretisch !)
z.B. für das Zoll-Maß: =SVERWEIS($C$2;$A$3:$G$800;1)

Seltsamerweise hat das dann auch EINMAL funktioniert. Gebe ich dann
weitere Geräte und Preise ein [Wolfgang: Also keine Änderungen bezüglich
der Spalten], erscheint aber statt der Spalte A der Zeile mit dem
min-Preis hier anscheinend der min-Preis selbst :-o
... und was da erscheint, wenn ich die Indizes 2 und 4 teste, kann ich
nicht mal vermuten :frowning:

#9

Hatte schon befürchtet, dass das mit den TABS keine gute Idee war.
Die 1,2,3 stehen natürlich nicht in Spalte A sondern sind wie A,B,C,D
Teil des Rahmens ...

#10

Hallo,

Irgendwo wird die Preis-Spalte ihr Minimum haben. Das suche ich mir mit
=MIN(WENN(C3:C799<>0;C3:C799)) und schreibe es in C2 :slight_smile:

Natürlich nützt mir dieser Wert allein knapp die Hälfte, also suche ich
die Zeile, in der das Minimum steht und gebe auch die anderen Werte in
der zweiten Zeile aus (theoretisch !)
z.B. für das Zoll-Maß: =SVERWEIS($C$2;$A$3:$G$800;1)

das klappt so nicht, denn SVERWEIS sucht in der ersten Spalte der
Matrix. Der Marke des billigsten erscheint also mit der Formel

=SVERWEIS($C$2;$C$3:$G$800;2;0)

An die Spalten A und B kommt man so nicht heran. Dazu müsste man
entweder die Spalten umsortieren oder die Spalten, die vor der Matrix
liegen, in weiter hinten liegende Spalten "kopieren", also etwa indem
man in E schreibt:

=$A3

und das nach unten ausfüllt, entsprechend in Spalte F:

=$B3

usw., und das dann bspw. in E2 ermittelt mit

=SVERWEIS($C$2;$C$3:$G$800;3;0)

usw. In diesem Beispiel steht die 3 für den Wert in der 3. Spalte der
Matrix (beginnend mit 1), und die 0 für nicht sortierte Matrix.

Die erste Formel für die Minimum-Ermittlung wollte übrigens bei mir
nicht funktionieren und gab hartnäckig ein #WERT zurück; mit obiger
Formel funktioniert es dann aber. Vorausgesetzt, man findet sich damit
ab, dass nur das erste Vorkommen des Minimalwertes berücksichtigt wird,
falls mehrere Marken denselben niedrigsten Preis enthalten.

Probier das mal aus und sag bescheid, ob es dann klappt.

#11

Hallo Tom,
das Suchkriterium (in deinem Fall der Preis) wird in der ersten !!!! Spalte deiner Matrix gesucht. Wenn dort der Preis steht, kannst du rechts daveon in der 4,5,6 ... Spalte einen anderen Wert abgreifen (z.B. Marke). Aber Preis (Suchkriterium) MUSS ganz vorne stehen.

Ralf

PS: Wenn es einmal geklappt hat, waren Zoll und Preis vielleicht zufällig identisch

#12

Struktur:
Index1 Index2 Index3 Index4
A B C D
1 Zoll umrech Preis Marke [Spaltenüberschriften, Kopfzeilen]
2 [hierhin sollen alle Daten des Gerätes mit dem minimalen Preis]
3 [hier beginnen die eingegebenen Daten, ca. 700 Zeilen]

Ich hoffe, das erklärt sich soweit selbst. Ich arbeite also mit
Spaltenüberschriften, demzufolge sind meine "Datensätze" in Zeilen
angeordnet - allerdings NICHT geordnet. [Kann ich hier schon über die
Hinweise von Regina und Stefan stolpern ? <-- natürlich nicht über die
Hinweise als solche ;-)]

Ja; wenn Du keinen 4. Parameter angibst, wir dieser als TRUE angenommen;
und dann muss erstens die Liste sortiert sein, und zweitens bekommst Du
im Fall, daß es das gesuchte Kriterium nicht gibt, den nächstkleineren
Wert zurück (oder um genauer zu sein den Wert /vor/ dem ersten
gefundenen Wert, der /größer/ ist als das Suchkriterium. Bei einer
unsortierten Liste kann das dann ziemlich chaotische Resultate liefern.
Wenn Du z. B. in der Liste (2,3,7,4,6) nach der '5' suchst, bekommst Du
die '3' zurück, weil halt die '7' die erste gefundene Zahl größer als
das Suchkriterium ist, und vor der steht eben die '3').

Irgendwo wird die Preis-Spalte ihr Minimum haben. Das suche ich mir mit
=MIN(WENN(C3:C799<>0;C3:C799)) und schreibe es in C2 :slight_smile:

Dazu brauchst Du doch gar keine Matrixfunktion; den kleinsten Wert einer
Reihe ermittelst Du immer noch mit

=MIN(C3:C799)

BTW: Man sollte immer großzügig sein mit dem Datenbereich; Du weißt nie,
wie viele Datensätze im Lauf der nächsten 20 Jahre <g> /noch/ dazu
kommen; und da üblicherweise auch eher selten [tm] etwas zwischen der
Zeile 799 und der Zeile 1.000 oder 2.000 stehen dürfte ...

BTW 2: Ich hab mir sogar angewöhnt, irgendwas ('!!!!!!!!!!!!!' o. ä.) in
die Zeile (oder mehreren; kost ja nix) /hinter/ den Bereich zu
schreiben, nachdem ich schon mehrfach trotz großzügiger Dimensionierung
unbemerkt über den ursprünglichen Datenbereich hinaus gekommen bin (und
mich dann irgend wann gewundert habe, warum bestimmte Datensätze einfach
nicht gefunden werden) ... :-/

Natürlich nützt mir dieser Wert allein knapp die Hälfte, also suche ich
die Zeile, in der das Minimum steht und gebe auch die anderen Werte in
der zweiten Zeile aus (theoretisch !)
z.B. für das Zoll-Maß: =SVERWEIS($C$2;$A$3:$G$800;1)

Da hast Du ein Problem; SVERWEIS und WVERWEIS suchen *immer* in der
*ersten* Spalte der angegebenen Matrix nach dem Suchkriterium. In deiner
gegebenen Liste steht das Suchkriterium aber in der 3 Spalte.

Du suchst also den Minimalpreis ($C$2) in der ersten Spalte ("Zoll"),
und gibst diese Spalte dann auch aus (3. Parameter = '1'); und das
machst Du noch dazu in einer vermeintlich sortierten Liste (4. Parameter
nicht '0'; oder besser FALSCH, weil dann auch sofort erkennbar ist, daß
es sich hier nicht um einen Zahlangabe handelt).

Also musst Du entweder

- die Spalten der Liste umsortieren, oder
- statt mit SVERWEIS mit VERWEIS arbeiten (das aber zwingend eine
sortierte Liste voraussetzt), oder
- die Spalten /vor/ der Preis-Spalte nochmal /hinter/ diese spiegeln
(kannst Du ja z. B. ausblenden), oder
- statt mit SVERWEIS mit VERGLEICH arbeiten.

Ich würde in Deinem Fall letzteres empfehlen: Zuerst ermittelst Du in
einer Hilfszelle (z. B. E2) den Index des betreffenden Datensatzes:

=VERGLEICH(MIN(C3:C2000);C3:C2000;0)

Und die betreffenden Werte bekommst Du dann am einfachsten mit z. B. für
den "Zoll"-Wert in der Zelle A2:

=INDEX(A3:A2000;$E$2;SPALTE(A2))

oder

=VERSCHIEBUNG(A2;$E$2;0)

oder ...

usw. A2 ist dabei weniger die Zelle, in die der ermittelte Wert hinein
geschrieben werden soll, sondern vielmehr die Zelle, die dem Index '0'
in der Tabelle entspricht.

Seltsamerweise hat das dann auch EINMAL funktioniert. Gebe ich dann
weitere Geräte und Preise ein [Wolfgang: Also keine Änderungen bezüglich
der Spalten], erscheint aber statt der Spalte A der Zeile mit dem
min-Preis hier anscheinend der min-Preis selbst :-o

Das liegt vermutlich hauptsächlich daran, daß Du irgendwo irgendwas
suchst, und allerhöchstens *zufällig* mal /das/ bekommst, was Du
eigentlich willst (s. o.).

Wolfgang

#13

Ich arbeite also mit Spaltenüberschriften, demzufolge sind
meine "Datensätze" in Zeilen angeordnet - allerdings NICHT
geordnet. [Kann ich hier schon über die Hinweise von Regina und
Stefan stolpern ? <-- natürlich nicht über die Hinweise als
solche ;-)]

Ja. Um welchen Hinweis meinerseits es sich handelt, kann von den
Mitlesenden keiner nachvollziehen, weil Tom mich offlist gefragt hat
und mein Hinweis demzufolge offlist an ihn ging. :wink:

Ja; wenn Du keinen 4. Parameter angibst, wir dieser als TRUE
angenommen;

Genau. Das war mein Hinweis. :wink:

Irgendwo wird die Preis-Spalte ihr Minimum haben. Das suche ich
mir mit =MIN(WENN(C3:C799<>0;C3:C799)) und schreibe es in C2
:slight_smile:

Dazu brauchst Du doch gar keine Matrixfunktion; den kleinsten
Wert einer Reihe ermittelst Du immer noch mit

> =MIN(C3:C799)

Wie Tom aber einen Thread früher schrieb, braucht er das Minimum,
wobei Zellen mit dem Wert 0 nicht betrachtet werden sollen.

Gruß

Stefan

#14

Hallo Tom,
das Suchkriterium (in deinem Fall der Preis) wird in der ersten !!!!

... klar, war wahrscheinlich schon immer so und ich hatte es
verdrängt :wink:
Also: Links außen eine Spalte mit den zu suchenden Werten eingefügt !

Spalte deiner Matrix gesucht. Wenn dort der Preis steht, kannst du rechts
daveon in der 4,5,6 ... Spalte einen anderen Wert abgreifen (z.B. Marke).
Aber Preis (Suchkriterium) MUSS ganz vorne stehen.

ABER DAS allein hilft nicht :frowning:
Der min-Wert in Spalten 4 (und Kopie in 1) ist - korrekt gefunden - 6,34

Z.B. der =SVERWEIS($D$2;$A$3:$J$666;2) findet in Spalte 2 den Wert 4,0
oder vielleicht einfach nur die erste Zeile des Matrixbereichs, wo
jedenfalls das Suchkriterium nicht 6,34 ist sondern 0.
Ich musste also explizit die nicht-sortierte Spalte 1 berücksichtigen !

Funktionieren tut's so: =SVERWEIS($D$2;$A$3:$J$666;2;0)

... und dieser vierte Parameter war mir bisher neu. Auf die Wichtigkeit
wurde aber bereits ganz am Anfang des Threads schonmal hingewiesen
(Regina ?).

Danke erstmal an alle Mitschreiber :slight_smile:

#15

=SVERWEIS($C$2;$C$3:$G$800;3;0)

usw. In diesem Beispiel steht die 3 für den Wert in der 3. Spalte der
Matrix (beginnend mit 1), und die 0 für nicht sortierte Matrix.

Das war extrem wichtig und wurde von mir zu lange
übersehen/ignoriert :wink:

Die erste Formel für die Minimum-Ermittlung wollte übrigens bei mir
nicht funktionieren und gab hartnäckig ein #WERT zurück;

=MIN(WENN(D3:D660>1;D3:D660))
meinst Du ?
Hast Du diese seltsame Tastenkombination bei der Eingabe beachtet ?

#16

... und genau das war auch der letzte zu beseitigende Knackpunkt :slight_smile:
Danke an alle, hier insbesondere an Stefan ...

#17

Ja; wenn Du keinen 4. Parameter angibst, wir dieser als TRUE angenommen;

... und das war böse in meinem Fall

und dann muss erstens die Liste sortiert sein, und zweitens bekommst Du

denn die Liste ist nicht nach der Spalte 1 sortiert ...

im Fall, daß es das gesuchte Kriterium nicht gibt, den nächstkleineren

Dass es das gesuchte Kriterium in jedem Fall gibt, stelle ich ja selbst
sicher :slight_smile:

> =MIN(C3:C799)

würde bei mir solange NULL auswerfen, solange die Liste nicht komplett
ist - und ich möchte, dass dass Ganze auch "unterwegs" funktioniert :wink:

Soweit so gut :slight_smile:

#18

Nur wenn in dem Bereich auch tatsächlich Zellen mit dem Wert '0' sind;
dann musst Du in der Tat über eine Matrixfunktion gehen. Aber leere
Zellen werden ignoriert.

Wolfgang

#19

Die Inhalte werden berechnet als Preis/Irgendwas. Solange ich also keine
aktuellen Preise habe (Preis leer) entsteht momentan zwangsweise eine 00
Ich könnte wahrscheinlich diese Nullen vermeiden, aber ich weiß nicht,
ob das nicht genauso kompliziert ist wie das Ausschließen der Nullen aus
der Auswertung ?!!

#20

Hallo Tom . . prost-net.de

ich glaube nicht, daß ich Dein Problem richtig verstanden habe, weil Du
es nicht nachvollziehbar ausgedrückt hast.
Man muß sich schon bemühen, konkret die Problematik darzustellen.
Notfalls mit einem Tab.-Ausschnitt.

Aber vielleicht helfen Dir unten nachstehende Hinweise:

> =MIN(C3:C799)

würde bei mir solange NULL auswerfen, solange die Liste nicht komplett
ist - und ich möchte, dass dass Ganze auch "unterwegs" funktioniert :wink:

Nur wenn in dem Bereich auch tatsächlich Zellen mit dem Wert '0' sind;
dann musst Du in der Tat über eine Matrixfunktion gehen. Aber leere
Zellen werden ignoriert.

Die Inhalte werden berechnet als Preis/Irgendwas. Solange ich also keine
aktuellen Preise habe (Preis leer) entsteht momentan zwangsweise eine 00
Ich könnte wahrscheinlich diese Nullen vermeiden, aber ich weiß nicht,
ob das nicht genauso kompliziert ist wie das Ausschließen der Nullen aus
der Auswertung ?!!

'WENN-Funktion':

lfde.-Nr.-Bildung in F13:
=WENN(ISTLEER(G13);"";(Formel: hier: (F12+1))
Man liest: Wenn in G13 nichts steht, dann schreibe auch nichts, sonst
schreibe in F13 die Summe aus F12+1
bedeutet:
Wenn in G13 ein Wert steht, dann bilde die Summe aus F12+1.
Es wird also geprüft, ob in G13 eine Eingabe erfolgt ist, erst danach
wird die laufende Nr. gebildet.

oder:

=WENN(ISTLEER(D32:D33);"";(SUMME F32+$D$29))
heißt: Wenn die 2-zeilige Matrix D23:D33 leer ist, dann schreibe auch
nichts, sonst bilde die Summe aus F32+D29

Tab.-Beispiel:
Nr. Datum Fahrt von-nach Kosten Platzkarte Fahrkosten Ges.-Kost.
2 22.04.11 Hamm-Halle 19,00 € 2,50 € 21,50 €
       24.04.11 Halle-Hamm 33,00 € 2,50 € 35,50 € 57,00 €

Formel in Zelle Ges.-Kosten:
=WENN(ISTLEER(D57:D58);"";Summe(D57+E57+D58+E58))

Hier wird geprüft, ob in 2-zeilig. Matrix eine Eingabe erfolgt ist, wenn
'N',
dann schreibe nichts, wenn 'J', dann bilde die Summe aus D57 bis E58
als Ges.-Kosten.

Ich hoffe, es wird Dir Einiges klarer.
Mit freundlichem Gruß !
            JoLa