'Umgekehrte' Funktionsweise SVERWEIS

Computer: Software & Programmierung Office Software
1

Hallo zusammen,

ich suche für folgendes Problem eine Formel,ohne mit WENN x-fach verschachteln zu müssen:

In den Spalten B bis L stehen Werte, in Spalte M ein Datum. Den Wert (Suchkriterium), den ich suche, kann in allen Spalten von B bis L sowie allen Zeilen vorkommen.
Das Datum soll gefunden werden, wenn erstmals der gesuchte Wert im Matrix (von oben an) auftaucht. Also eigentlich der klassische SVERWEIS, nur dass dieser eben nicht nur eine Spalte, sondern mehrere Spalten durchsuchen soll.

Ist das möglich?

Alternativ habe ich momentan eine WENN-Formel zehnmal verschachtelt mit SVERWEIS und suche so Spalte für Spalte ab (macht diese dann aber natürlich super lang).

Vielen lieben Dank vorab für eure Hilfe.

Gruß
Christian

2

Grüezi Christian

In den Spalten B bis L stehen Werte, in Spalte M ein Datum.
Den Wert (Suchkriterium), den ich suche, kann in allen Spalten
von B bis L sowie allen Zeilen vorkommen.
Das Datum soll gefunden werden, wenn erstmals der gesuchte
Wert im Matrix (von oben an) auftaucht. Also eigentlich der
klassische SVERWEIS, nur dass dieser eben nicht nur eine
Spalte, sondern mehrere Spalten durchsuchen soll.

Ist das möglich?

Mit einer Hilfsspalte und den folgenden Formeln kannst Du das relativ elegant lösen:

Tabellenblatt: [Mappe1]!Tabelle1
 │ M │ N │ O │ P │ Q │
───┼────┼───┼───┼──────┼───┤
 1 │ 1 │ 0 │ │ 3670 │ 3 │
───┼────┼───┼───┼──────┼───┤
 2 │ 2 │ 0 │ │ │ │
───┼────┼───┼───┼──────┼───┤
 3 │ 3 │ 1 │ │ │ │
───┼────┼───┼───┼──────┼───┤
 4 │ 4 │ 0 │ │ │ │
───┼────┼───┼───┼──────┼───┤
 5 │ 5 │ 0 │ │ │ │
───┼────┼───┼───┼──────┼───┤
 6 │ 6 │ 0 │ │ │ │
───┼────┼───┼───┼──────┼───┤
 7 │ 7 │ 0 │ │ │ │
───┼────┼───┼───┼──────┼───┤
 8 │ 8 │ 0 │ │ │ │
───┼────┼───┼───┼──────┼───┤
 9 │ 9 │ 0 │ │ │ │
───┼────┼───┼───┼──────┼───┤
10 │ 10 │ 0 │ │ │ │
───┼────┼───┼───┼──────┼───┤
11 │ 11 │ 0 │ │ │ │
───┼────┼───┼───┼──────┼───┤
12 │ 12 │ 0 │ │ │ │
───┼────┼───┼───┼──────┼───┤
13 │ 13 │ 0 │ │ │ │
───┼────┼───┼───┼──────┼───┤
14 │ 14 │ 0 │ │ │ │
───┼────┼───┼───┼──────┼───┤
15 │ 15 │ 0 │ │ │ │
───┼────┼───┼───┼──────┼───┤
16 │ 16 │ 0 │ │ │ │
───┼────┼───┼───┼──────┼───┤
17 │ 17 │ 0 │ │ │ │
───┼────┼───┼───┼──────┼───┤
18 │ 18 │ 0 │ │ │ │
───┼────┼───┼───┼──────┼───┤
19 │ 19 │ 0 │ │ │ │
───┼────┼───┼───┼──────┼───┤
20 │ 20 │ 0 │ │ │ │
───┼────┼───┼───┼──────┼───┤
21 │ 21 │ 0 │ │ │ │
───┼────┼───┼───┼──────┼───┤
22 │ │ │ │ │ │
───┴────┴───┴───┴──────┴───┘
Benutzte Formeln:
N1 : =WENN(ZÄHLENWENN($B1:blush:L1;$P$1);1;0)
Q1 : =INDEX($M:blush:M;VERGLEICH(1;$N:blush:N;0))

Tabellendarstellung erreicht mit dem Code in FAQ:2363

Mit freundlichen Grüssen

Thomas Ramel

  • MVP für MS-Excel -
3

Hallo Thomas,

danke für deine schnelle Antwort :smile:

Die Formel ist schon richtig klasse und auch das Ergebnis am Ende richtig, aber…: Meinst du, das Ganze geht auch ohne Hilfsspalte, sondern alles in einer Zelle?

Ich habe ca. 50 verschiedene Suchkriterien (ich möchte eine dynamische Liste erstellen, wer wie oft und insgesamt in unseren Sportmannschaften zum Einsatz gekommen ist, also zu jedem Spieler durchsuche ich alleSpiele, ob er dabei war und möchte so auch gleichzeitig das Debütdatum ermitteln…). Deshalb diese Formel, und 50 x 2 Hilfsspalten sprengt mein Sheet :wink:

Vielen Dank vorab für deine tolle Hilfe!

Gruß
Christian

4

Hallo Christian,
Du kannst auch die SuchFunktion verwenden. Mit dem MakroRekorder aufgezeichnet erhältst Du einen Code ähnlich dem folgenden:

On Error GoTo Hell 'Fehlermeldung bei erfolgloser Suche abfangen
Columns(„B:L“).Find(What:=SuchString, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Zeile = ActiveCell.Row
Spalte=ActiveCell.Column
On Error GoTo 0 ’ Fehlerbehandlung ausschalten

Wie man das „activate“ hier wegbekommt, habe ich noch nicht herausgefunden.

Freundliche Grüße
Thomas

5

Hallo Christian,

In den Spalten B bis L stehen Werte, in Spalte M ein Datum.
Den Wert (Suchkriterium), den ich suche, kann in allen Spalten
von B bis L sowie allen Zeilen vorkommen.

Das Datum soll gefunden werden, wenn erstmals der gesuchte
Wert im Matrix (von oben an) auftaucht.

was genau meisnt du mit „von oben an“)
Meinst du damit die Suchreihenfolge:
B1,B2,B3,B4,…
oder
B1,C1,D1,E1,…
Beides ist für mich „von oben an“.

Gruß
Reinhard

6

OT Find ohne Activate

On Error GoTo Hell 'Fehlermeldung bei erfolgloser Suche
abfangen
Columns(„B:L“).Find(What:=SuchString, After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Zeile = ActiveCell.Row
Spalte=ActiveCell.Column
On Error GoTo 0 ’ Fehlerbehandlung ausschalten

Wie man das „activate“ hier wegbekommt, habe ich noch nicht
herausgefunden.

Hallo Thomas,

z.B. so:

Dim Such As Range, Zeile As Long, Spalte As Long
Set Such = Columns(„B:L“).Find(What:=SuchString, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Such Is Nothing Then Exit Sub
Zeile = Such.Row
Spalte = Such.Column

Gruß
Reinhard

1 „Gefällt mir“
7

Hallo Reinhard,

ich meine

B1,C1,D1,E1,…

also erst die Spalten, dann die Zeilen

(B2, C2, D2, E2, …, B3, C3, D3, E4, …)

Danke dir!

Gruß
Christian

8

B1,C1,D1,E1,…
also erst die Spalten, dann die Zeilen

Hallo Christian,

Tabellenblatt: [Mappe2]!Tabelle1
 │ K │ L │ M │ N │ O │
──┼──────────┼──────────┼────────┼──────────┼────────┤
1 │ │ │ Datum1 │ Spieler1 │ Datum4 │
──┼──────────┼──────────┼────────┼──────────┼────────┤
2 │ │ │ Datum2 │ Spieler2 │ #NV │
──┼──────────┼──────────┼────────┼──────────┼────────┤
3 │ │ Spieler5 │ Datum3 │ Spieler3 │ #NV │
──┼──────────┼──────────┼────────┼──────────┼────────┤
4 │ Spieler1 │ │ Datum4 │ Spieler4 │ #NV │
──┼──────────┼──────────┼────────┼──────────┼────────┤
5 │ │ │ Datum5 │ Spieler5 │ Datum3 │
──┴──────────┴──────────┴────────┴──────────┴────────┘
Benutzte Formeln:
O1: =INDEX(M:M;VERGLEICH(1;1/MMULT(1\*($B$1:blush:L$50=N1);ZEILE($2:blush:12)^0);0))
O2: =INDEX(M:M;VERGLEICH(1;1/MMULT(1\*($B$1:blush:L$50=N2);ZEILE($2:blush:12)^0);0))
O3: =INDEX(M:M;VERGLEICH(1;1/MMULT(1\*($B$1:blush:L$50=N3);ZEILE($2:blush:12)^0);0))
O4: =INDEX(M:M;VERGLEICH(1;1/MMULT(1\*($B$1:blush:L$50=N4);ZEILE($2:blush:12)^0);0))
O5: =INDEX(M:M;VERGLEICH(1;1/MMULT(1\*($B$1:blush:L$50=N5);ZEILE($2:blush:12)^0);0))

K1:open\_mouth:5
haben das Zahlenformat: Standard

Tabellendarstellung erreicht mit dem Code in FAQ:2363

Gruß
Reinhard

9

Hallo Reinhard,
so hast Du auch noch die FehlerBehandlung eliminiert :smile:
Vielen Dank und Gruß
Thomas

Hallo Thomas,

z.B. so:

Dim Such As Range, Zeile As Long, Spalte As Long
Set Such = Columns(„B:L“).Find(What:=SuchString,
After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Such Is Nothing Then Exit Sub
Zeile = Such.Row
Spalte = Such.Column

Gruß
Reinhard

10

Hallo Reinhard,

geneu das ist ist, genial, tausend Dank!!! :smile:

Christian

11

Hallo Reinhard,

eine Frage habe ich dazu noch: wie muss ich deine tolle Formel umstellen, wenn nun nicht der erste zutreffende Wert, sondern der 2., 3., … Wert damit ermittelt werden soll?

Habe schon alles versucht an Zahlen daran zu ändern, aber es kommt immer #NV zurück :frowning:

Vielen Dank vorab für deine Mühe.

Gruß
Christian

12

Hallo Christian,

eine Frage habe ich dazu noch: wie muss ich deine tolle Formel
umstellen, wenn nun nicht der erste zutreffende Wert, sondern
der 2., 3., … Wert damit ermittelt werden soll?

wie soll das von statten gehen?
Du schreibst in X1 eine 1, in X2 eine 2 usw.
Dann soll in Y1, Y2 usw jeweils der soundsovielte Trffer erscheinen?

Kann man lösen nur mit einer Formel oder nicht, dann bleiben nur Hilsspalten bzw. Vba.
Denn Suchfunktionen in Excel werten nur den ersten Treffer aus bzw. finden den, die anderen suchen sie gar nicht.
Da muß man schon schauen wie man Excel dazu „überredet“ auch den zweiten dritten Treffer zu finden.

Mal angenommen, ich löse das, kommt dann noch eine Abänderung?

Gruß
Reinhard

13

Hallo Reinhard,

danke für die Info, dass Excel immer nur den ersten Treffer sucht. Wenn das komplizierter wird, dann arbeite ich besser mit Hilfsspalte (muss ja nicht zu umständlich werden).

Möchte eben gerne alles aus meiner Spielerstatistik-Datenbank herausholen :wink:

Trotzdem Danke für die viele Mühe, die ich dir schon bereitet habe.

Gruß
Christian

14

so hast Du auch noch die FehlerBehandlung eliminiert :smile:

Hallo Thomas,

korrekter Umgang mit allen Fehlern die auftreten können ist schwierig genug.
Lassen wir mal die Kommentierung jeder Codezeile, bzw. jedes Codeabschnitts weg die auch Zeitraubend ist.

So erfordert je nach problematik eine gute Fehlerbehandlung nicht 2-5 % des zeitaufwandes für den Gesamtcode sondern eher
30 -60 %.

Dazu gehört auch voraussehbare Fehler gar nicht entstehen zu lassen.
Wie hier. Find liefert ein Range oder Nothing.
Fängt man das Nothing ab so entsteht gar keine Fehlermeldung.

Oder wenn du in Vba SVerveis benutzt so könnte da ja auch #NV! kommen, also ein Fehler.
Ergo prüfe ich vor der SVerweis Codezeile mit Zählenwenn ob der Begriff überhaupt in der Suchspalte von SVerweis steht und werte das aus.
Logisch meine ich beides in der Vba-Schreibweise,

Gruß
Reinhard

15

Hallo Reinhard,
vielen Dank für Deine Antwort. Hat leider etwas gedauert, dass ich antworten kann. Zum Renovierungsstress sind noch ein paar Faktoren hinzu gekommen. Unter anderem ein neuer Server in der Firma und viele verlorene Einstellungen am PC. So muss ich auch die PfadAngaben in meinen Makros alle anpassen :frowning:

so hast Du auch noch die FehlerBehandlung eliminiert :smile:

Hallo Thomas,

korrekter Umgang mit allen Fehlern die auftreten können ist
schwierig genug.

Das sehe ich auch so. On error … goto ist klar nur eine NotLösung. Deswegen freue ich mich immer, wenn Fehler gar nicht erst entstehen. Und wenn sie für den User sichtbar werden, dann sollte wenigstens ein hilfreicher Hinweis für ihn dabei herausspringen und eine aussagefähige Fehlernummer, die man per copy und paste in google einfügen kann. Aber das schaffen ja nicht einmal Hersteller von Standardsoftware :wink:

Lassen wir mal die Kommentierung jeder Codezeile, bzw. jedes
Codeabschnitts weg die auch Zeitraubend ist.

Würde ich nie freiwillig weglassen :wink: Aber was macht man nicht Alles im Alltag mehr oder weniger in Eile …
Jedenfalls hat es mir auch nie leidgetan um die Zeit für Kommentare zu meinem Code ( oder aus dem Internet kopiertem :smile: Davon profitiert man
ja auch schnell und vor Allem auch nach Jahren wieder.
Deshalb lasse ich ein Makro während der LaufZeit auch nicht gerne in den Debugger laufen. Was soll ein unbedarfter User da?

So erfordert je nach problematik eine gute Fehlerbehandlung
nicht 2-5 % des zeitaufwandes für den Gesamtcode sondern eher
30 -60 %.

Ja, aber Dein Code ist kurz und wirkungsvoll. Werde ich in mein „Projekt“ übernehmen.

Dazu gehört auch voraussehbare Fehler gar nicht entstehen zu
lassen.
Wie hier. Find liefert ein Range oder Nothing.
Fängt man das Nothing ab so entsteht gar keine Fehlermeldung.

Abfangen geht vor Behandeln :smile:

Oder wenn du in Vba SVerveis benutzt so könnte da ja auch #NV!
kommen, also ein Fehler.
Ergo prüfe ich vor der SVerweis Codezeile mit Zählenwenn ob
der Begriff überhaupt in der Suchspalte von SVerweis steht und
werte das aus.

Da wäre ich irgendwann auch drauf gekommen www wer weiss wann :wink:

Logisch meine ich beides in der Vba-Schreibweise,

Diesen Satz habe ich jetzt nicht verstanden (?)

Gruß
Reinhard

Freundliche Grüße
Thomas

16

Logisch meine ich beides in der Vba-Schreibweise,

Diesen Satz habe ich jetzt nicht verstanden (?)

Hallo Thomas,

sorry, war da so am Schreiben und hab das so geschrieben.

Damit meine ich einzig, nicht das benutzen von
.Vlookup(A1,B2:B100,…
sondern
.Vlookup(Range(„A1“), Range(„B2:B100“),…

Daß ich dir das nicht erzählen muß ist mir klar durchs Mitlesen hier aber ich war halt so am Formulieren meiner Antwort und habe diese Vba-Basic erwähnt weil ich beim Schreiben meiner antwort und deren Ausformulierung schon längst vergessen habe an wenn ich das schreibe :frowning: :smile:

Gruß
Reinhard