Microsoft Excel

Herbers Excel/VBA-Archiv

SVERWEIS ... aber etwas anders

Betrifft: SVERWEIS ... aber etwas anders von: Michael Mueller
Geschrieben am: 24.09.2014 13:22:59

Hallo.

Ich möchte aus folgenden Daten immer den letzten Eintrag aus B zu einem Namen aus A haben:

Müller____5
Meier_____18
Schmitt___20
Huber_____3
Meier_____9
Müller____13
Müller____11
Schmitt___21
Meier_____16

Wenn ich nun SVERWEIS benutze findet er immer den ersten Eintrag, so liefert
SVERWEIS("Meier";A:B;2;FALSCH)
die 18 zurück, ich möchte aber die 16 haben.

Weiß jemand von Euch wie man das realisieren könnte?

Danke
Michi

  

Betrifft: AW: SVERWEIS ... aber etwas anders von: Rudi Maintaire
Geschrieben am: 24.09.2014 13:29:50

Hallo,

ABCDE
1a1   
2b2 a5
3a3   
4c4   
5a5   
6b6   
7d7   

ZelleFormel
E2=VERWEIS(2;1/(A1:A100=D2);B1:B100)


Gruß
Rudi


  

Betrifft: AW: SVERWEIS ... aber etwas anders von: Michael Mueller
Geschrieben am: 24.09.2014 13:48:25

Danke, funktioniert prima!

Aber kann mir das mal jemand erklären warum es funktioniert?
Für mich macht die Formel augenscheinlich keinen Sinn :-/

Michi


  

Betrifft: AW: SVERWEIS ... aber etwas anders von: Klaus M.vdT.
Geschrieben am: 24.09.2014 15:54:41

Hallo Michi,
easy :-)
Der Zelleninhalt von A1:A17 ist entweder "a" oder nicht, je nachdem ergibt die Matrix "Wahr" oder "Falsch".
=VERWEIS(2;1/(A1:A100=D2);B1:B100)
Löst also auf nach:
=VERWEIS(2;1/({WAHR,FALSCH,WAHR,FALSCH,WAHR,FALSCH,FALSCH};B1:B199)
WAHR ist eins, FALSCH ist null. Aufgelöst:
=VERWEIS(2;1/({1,0,1,0,1,0,0};B1:B199)
1/1 ergibt 1, 1/0 ergibt ... den #DIV! Fehler. Der Teil mit "eins durch"
=VERWEIS(2;1/({1,0,1,0,1,0,0};B1:B199)
löst also auf nach:
=VERWEIS(2;{1,div0,1,div0,1,div0,div0};B1:B199)
Die Matrix im VERWEIS ignoriert Fehlerwerte. VERWEIS(2; (beachte: Bereich.Verweis Schalter nicht gesetzt, daher unscharfe Suche)
Sucht jetzt den Wert 2 in der Matrix (1,-,1,-,1,-,-) und findet ihn nicht, da keine 2 vorkommt. Gemäß der Bereich.Verweis Regeln wird also der letzte nahe dran stehende Wert genommen, unter Ignorierung der Fehlerwerte.
(1,-,1,-,1,-,-) ist in der B1:B7 Matrix der 5te Wert, darum wird das Ergebnis aus B5 zurück gegeben.
Nochmal zum Verständnis: die 2 dient nur dazu, mit VERGLEICH nichts zu finden - und das 1/ nur dazu, Fehlerwerte zu erzeugen.
=VERWEIS(99999;37/(A1:A100=D2);B1:B100)
funktioniert ebenfalls, sieht aber noch komischer aus.

Grüße,
Klaus M.vdT.


  

Betrifft: AW: SVERWEIS ... aber etwas anders von: Michael Mueller
Geschrieben am: 24.09.2014 16:12:28

Hi Klaus,

vielen Dank für Deine Mühe und die ausführliche Erklärung.
Jetzt hab ich es verstanden, vor allem dank dem Satz
"Gemäß der Bereich.Verweis Regeln wird also der letzte nahe dran stehende Wert genommen, unter Ignorierung der Fehlerwerte."

Michi


  

Betrifft: Danke für die Rückmeldung! owT. von: Klaus M.vdT.
Geschrieben am: 25.09.2014 10:18:45

.


  

Betrifft: AW: SVERWEIS ... aber etwas anders von: Klaus M.vdT.
Geschrieben am: 24.09.2014 13:32:20

Hi Michi,
auf die schnelle mit Hilfsspalte?
https://www.herber.de/bbs/user/92789.xlsx

Alternative ohne Hilfsspalte:
http://www.excelformeln.de/formeln.html?welcher=26

Grüße,
Klaus M.vdT.


  

Betrifft: AW: SVERWEIS ... aber etwas anders von: GuentherH
Geschrieben am: 24.09.2014 14:16:26

mit einem dictionary- Objekt könnte man eine VBA-Funktion dafür schreiben.
Bin leider kein Spezialist darin.
Gruß,
Günther


 

Beiträge aus den Excel-Beispielen zum Thema "SVERWEIS ... aber etwas anders"