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,
| A | B | C | D | E |
1 | a | 1 | | | |
2 | b | 2 | | a | 5 |
3 | a | 3 | | | |
4 | c | 4 | | | |
5 | a | 5 | | | |
6 | b | 6 | | | |
7 | d | 7 | | | |
Zelle | Formel |
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"