Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema ListBox
BildScreenshot zu ListBox ListBox-Seite mit Beispielarbeitsmappe aufrufen

Matrix mit S-Verweis

Betrifft: Matrix mit S-Verweis von: Michael
Geschrieben am: 06.08.2020 13:22:01

Hallo,


ich habe eine Frage zu folgendem Fall:

Sagen wir ich habe 3 Spalten (Zellen A1 bis C4)
Vorname Nachname Zahl

Michael Müller 10

Tina Meier 30

Michael Müller 20


In Zelle B6 steht Michael und in Zelle C6 Müller.

Außerdem schreibe ich folgende Formel, um die Zahl von Michael Müller zu erhalten:

={SVERWEIS(B6;WENN($B$2:$B$4=C6;$A$2:$C$4;““);3)}

Der Rückgabewert ist 20. Und das verstehe ich nicht. Ein S-Verweis nimmt doch immer den ersten Wert den er findet oder nicht? Meine Vermutung ist, dass der S-Verweis auf "Wahr" läuft, aber warum dann der zweite Wert gezogen wird erschließt sich mir trotzdem nicht.

Hier die Datei dazu:

https://www.herber.de/bbs/user/139498.xlsx


Beste Grüße und vielen Dank im Voraus

Michael

Betrifft: AW: Matrix mit S-Verweis
von: SF
Geschrieben am: 06.08.2020 13:32:00

Hola,

wenn du den 4. Parameter nicht setzt nimmt Excel automatisch WAHR.
Das setzt dann aber eine aufsteigend sortierte Matrix voraus und die hast du nicht.

Gruß,
steve1da

Betrifft: AW: Matrix mit S-Verweis
von: Michael
Geschrieben am: 06.08.2020 13:44:40

Hallo ihr beiden,

vielen Dank für eure schnellen Antworten. Leider verstehe ich es immer noch nicht. Der einfache S-Verweise mit "WAHR" gibt doch auch 10 und nicht 20 zurück. Wieso ist das bei einer Matrix-Formel anders?

Beste Grüße
Michael

Betrifft: AW: Matrix mit S-Verweis
von: Michael
Geschrieben am: 06.08.2020 13:50:06

PS: gerade nochmal getestet. WAHR (1) gibt immer 20 zurück, allerdings scheint WAHR nicht immer der Default-Wert für den S-VERWEIS zu sein!? Gerade ein S-Verweis ohne 4ten Parameter eingegeben und Rückgabewert war 10, denke daher, dass 0 hier default war.

Betrifft: AW: Matrix mit S-Verweis
von: SF
Geschrieben am: 06.08.2020 13:57:13

Hola,

WAHR ist schon der Default Wert.
Sortier deine Matrix mal aufsteigend, dann erhälst du auch richtige Ergebnisse. Oder nutz halt FALSCH als Parameter.

Gruß,
steve1da

Betrifft: AW: Matrix mit S-Verweis
von: Michael
Geschrieben am: 06.08.2020 14:02:55

Michael Müller 10
Michael Müller 20
Tina Baum 30

meinst du so? Mit =SVERWEIS(B6;A2:C4;3;) erhalte ich aber 10, da müsste dann doch aber 20 rauskommen oder? Wenn ich stattdessen =SVERWEIS(B6;A2:C4;3;1) eingebe, erhalte ich 20, bei =SVERWEIS(B6;A2:C4;3;0) 10.

Betrifft: AW: Matrix mit S-Verweis
von: SF
Geschrieben am: 06.08.2020 14:16:16

Hola,

warum soll da 20 rauskommen? In B6 steht Michael und der Sverweis findet den ersten Michael und gibt 10 aus.
Der zweite Sverweis gibt übrigens auch 10 aus, jedenfalls in deiner Datei.

Gruß,
steve1da

Betrifft: AW: Matrix mit S-Verweis
von: Michael
Geschrieben am: 06.08.2020 14:49:54

Hmm, komisch

Hier nochmal die Datei https://www.herber.de/bbs/user/139501.xlsx
Habe dort alle 3 Fälle aufgeführt. Aber leider komme ich eben auf den Default-Wert von 0/Falsch und damit auf die Zahl 10, was mich verwirrt.

Falls du dir das anschauen könntest, wäre es mega. Vielen Dank im Voraus!

Betrifft: AW: Matrix mit S-Verweis
von: Daniel
Geschrieben am: 07.08.2020 09:31:36

Hi

du must mit den Defaultwerten schon genau aufpassen, es hängt davon ab, ob das Semikolon nach dem dritten Parameter vorhanden ist oder nicht:
SVerweis(B6;A2:A6;3) : 4. parameter ist WAHR
SVerweis(B6;A2:A6;3;) : 4. parameter ist FALSCH

der SVerweis mit 4. Parameter=FALSCH sucht die Liste von oben nach unten durch und nimmt den ersten Treffer.
der SVerweis mit 4. Parameter=WAHR holt sich den Wert aus der Datenmitte, vergleicht diesen mit dem Suchwert und entscheidet dann, ob der Schwert in der oberen oder unteren Datenhälfte sein muss. Dieser Vorgang wird so oft mit ser jeweiligen Datenhälfte wiederholt, bis nur noch ein Wert übrig ist.

durch deine Matrixformel bekommt der SVerweis folgende erste Spalte zum durchsuchen:
Michael - "" - Michael

der Suchwert ist "Michael", dh. im ersten Vergleich wird "Michael" mit "" verglichen und der SVerweis entscheidet dass "" kleiner ist als "Michael" und daher der Suchwert in der untern Datenhälfte liegen muss, daher dann die 20 als Ergebnis.
würdest du statt "" den Wert "Z" verwenden, wäre 10 das Ergebnis, weil "Michael" kleiner als "Z" ist und dann das Ergebnis in der Ersten Hälfte der Daten liegen muss.

Allerdings verwendest du nicht die normalen Anführungszeichen (Shift 2) sondern andere, welche bei mir den #Name!-Fehler erzeugen. Ist das so von dir gewollt?

Gruß Daniel

Betrifft: AW: Matrix mit S-Verweis
von: Michael
Geschrieben am: 08.08.2020 09:21:32

Wow, das was eine wirklich sehr gute Erklärung Daniel, vielen vielen Dank!



Die Anführungszeichen waren in der Tat nicht gewollt.



Eine Frage hätte ich noch: Wann macht es denn eigentlich Sinn den Parameter "WAHR" bei der Suchen nach Texten zu nutzen?

Betrifft: AW: Matrix mit S-Verweis
von: Daniel
Geschrieben am: 08.08.2020 11:58:49

Wenn die zu durchsuchende Liste sehr lang ist.
Die Suche mit Wahr in sortierten Listen ist wesentlich schneller als die Suche in unsortierten Listen.
Gruß Daniel

Betrifft: AW: Matrix mit S-Verweis
von: Michael
Geschrieben am: 08.08.2020 22:27:11

Vielen Dank Daniel!

Betrifft: AW: und um die richtigen Werte zu ermitteln ...
von: neopa C
Geschrieben am: 06.08.2020 13:36:23

Hallo Michael,

... schreibe folgende Formel in D6:

=WENNFEHLER(INDEX(C:C;AGGREGAT(15;6;ZEILE(A$2:A$5)/(A$2:A$5=B6)/(B$2:B$5=C6);ZEILE(A1)));"")


und kopiere diese nach unten.

Gruß Werner
.. , - ...

Beiträge aus dem Excel-Forum zum Thema "Matrix mit S-Verweis"