Microsoft Excel

Herbers Excel/VBA-Archiv

SVERWEIS für zweite Zeile | Herbers Excel-Forum


Betrifft: SVERWEIS für zweite Zeile von: Horst
Geschrieben am: 29.11.2009 21:02:24

Hallo Excel-Gemeinde!

Ist es möglich, vom SVerweis für ein und dasselbe Suchkriterium mehrere Rückgabewerte zu bekommen?

Beispiel: In Spalte A steht das Datum, in Spalte B steht das gesuchte Kriterium. Nun ist das Problem, dass das gleiche Datum z.b. der 25.11.09 in Spalte A mehrmals vorkommen kann und in Spalte B für denselben Tag unterschiedliche Kriterien stehen. Die SVerweis-Funktion gibt mir nur jeweils die erste Zeile von Spalte B zurück. Wie erreiche ich, dass auch in der zweiter und dritten Zeile der Datumsspalte gesucht wird?

Besten Dank für Eure Vorschläge!

  

Betrifft: AW: SVERWEIS für zweite Zeile von: hary
Geschrieben am: 29.11.2009 21:08:13

hallo Horst
schau mal hier. Glaube das ist was Du brauchst.
http://www.excelformeln.de/formeln.html?welcher=28
Gruss Hary


  

Betrifft: AW: SVERWEIS für zweite Zeile von: Horst
Geschrieben am: 29.11.2009 21:11:30

Danke Hary!

Das hab' ich auch gerade entdeckt, bin gerade am experimentieren. Wenn jemand aber auf Anhieb die Lösung weiß, bitte reinstellen!


  

Betrifft: AW: SVERWEIS für zweite Zeile von: hary
Geschrieben am: 29.11.2009 21:29:09

Hallo

Tabellenblattname: Tabelle1

 

A

B

C

D

E

1

01.11.2009

a

gesucht

01.11.2009

a

2

02.11.2009

a

 

 

b

3

03.11.2009

f

 

 

c

4

04.11.2009

a

 

 

 

5

05.11.2009

a

 

 

 

6

06.11.2009

a

 

 

 

7

07.11.2009

a

 

 

 

8

08.11.2009

e

 

 

 

9

09.11.2009

a

 

 

 

10

01.11.2009

b

 

 

 

11

02.11.2009

a

 

 

 

12

03.11.2009

d

 

 

 

13

04.11.2009

a

 

 

 

14

01.11.2009

c

 

 

 


Benutzte Formeln:
E1: =INDEX(B:B;VERGLEICH(D1;A:A;))
E2: =WENN(MAX(ISTNV(VERGLEICH(B$1:B$99;E$1:E1;))*(A$1:A$99=$D$1))=0;"";INDEX(B:B;MIN(WENN(ISTNV(VERGLEICH(B$1:B$99;E$1:E1;))*(A$1:A$99=$D$1);ZEILE($1:$99)))))
E3: =WENN(MAX(ISTNV(VERGLEICH(B$1:B$99;E$1:E2;))*(A$1:A$99=$D$1))=0;"";INDEX(B:B;MIN(WENN(ISTNV(VERGLEICH(B$1:B$99;E$1:E2;))*(A$1:A$99=$D$1);ZEILE($1:$99)))))
E4: =WENN(MAX(ISTNV(VERGLEICH(B$1:B$99;E$1:E3;))*(A$1:A$99=$D$1))=0;"";INDEX(B:B;MIN(WENN(ISTNV(VERGLEICH(B$1:B$99;E$1:E3;))*(A$1:A$99=$D$1);ZEILE($1:$99)))))
Ansatz reicht?
Gruss hary


  

Betrifft: ab E2 Matrix Formel strg+shift+enter owT von: hary
Geschrieben am: 29.11.2009 21:30:36

.


  

Betrifft: AW: ab E2 Matrix Formel strg+shift+enter owT von: Horst
Geschrieben am: 29.11.2009 21:54:33

Danke Hary!

Hab' die Matrixformel bereits implementiert, läuft soweit alles prima. Mein Problem ist nur, dass ich knapp 40 verschiedene Suchkriterien habe und das gleiche Datum bis zu fünfmal vorkommen kann. Muss ich jetzt für das jeweilige Suchkriterium in Spalte 1,2,3,4,5 jeweils 'einzeln' suchen oder gibt es eine Formel, die mir mit einem Mal (sozusagen untereinander) alle Suchkriterien eines bestimmten Datums ausgibt.


  

Betrifft: AW: SVERWEIS für zweite Zeile von: Andre´
Geschrieben am: 29.11.2009 21:48:18

Hallo Horst,

hier noch eine Möglichkeit: https://www.herber.de/bbs/user/66267.xls


MFG Andre


  

Betrifft: AW: SVERWEIS für zweite Zeile von: Horst
Geschrieben am: 29.11.2009 22:08:43

Danke André!

Die VBA-Funktion is ja Weltklasse! :-)) Kann man die auch so umschreiben, dass statt "a,b,c" die Suchkriterien in Spalten nebeneinander (ohne Beistrich) geschrieben werden; also so wie in der darunter angegebenen Formel?


  

Betrifft: AW: SVERWEIS für zweite Zeile von: Andre´
Geschrieben am: 29.11.2009 22:28:49

Hallo Horst,
meist Du so: https://www.herber.de/bbs/user/66269.xls

MFG Andre


  

Betrifft: AW: SVERWEIS für zweite Zeile von: Horst
Geschrieben am: 29.11.2009 22:32:56

Perfekt, das ist es was ich meine:

Wie müsste ich den VBA-Code ändern, dass er die Kriterien nicht durch Beistrich trennt, sondern jeweils in neue Spalten schreibt wie in 66269.xls?

Public Function SVERWEIS2(Kriterium As String, Bereich As Range, SuchSpalte As Integer,  _
ErgebnissSpalte As Integer, Optional Trenner As String = ", ") As String
    Dim arrTmp
    Dim L As Long
arrTmp = Bereich
For L = 1 To UBound(arrTmp)
    If arrTmp(L, SuchSpalte) = Kriterium Then _
    If InStr(1, SVERWEIS2, arrTmp(L, ErgebnissSpalte)) = 0 Then _
    SVERWEIS2 = SVERWEIS2 & arrTmp(L, ErgebnissSpalte) & Trenner
Next
SVERWEIS2 = Left(SVERWEIS2, Len(SVERWEIS2) - Len(Trenner))
End Function



  

Betrifft: kann Dir nicht weiter helfen oT. von: Andre´
Geschrieben am: 29.11.2009 22:47:14




  

Betrifft: AW: kann Dir nicht weiter helfen oT. von: Horst
Geschrieben am: 29.11.2009 22:56:04

Trotzdem besten Dank! Im Prinzip müsste in der zweiten Zeile des Codes statt dem "," nur angegeben werden, dass in eine neue Spalte geschrieben wird. Die Frage ist nur, wie der VBA-Befehl dazu lautet?


  

Betrifft: Da diese udFkt nicht von mir ist, enthält... von: Luc:-?
Geschrieben am: 30.11.2009 02:02:04

...sie diese Fktionalität nicht, Horst... ;-)
Du kannst das integrieren, indem du in der Wertzuweisungszeile noch ein Split(..., ", ") um den Zuweisungswert legst. Das Ergebnis ist dann immer ein 1zeiliger Vektor (Feld, Array, ggf aus nur 1em Wert), der per Matrixfml-Schreibweise auf die markierten Zellen einer Zeile aufgeteilt wdn kann. Ggf kannst du auch eine 2.udFkt, die nur das erledigt, um die 1. „herumlegen“ [Alle RecherchenSplitt als Bsp].
Gruß Luc :-?


  

Betrifft: AW: SVERWEIS für zweite Zeile von: Andre´
Geschrieben am: 30.11.2009 07:47:33

Hallo alle zusammen,
in meiner ersten Antwort in diesem Beitrag:

https://www.herber.de/forum/archiv/1120to1124/t1120889.htm#1120896

muss die enthaltene Excel Datei: 66267.xls

einen andere Verknüpfung erhalten haben.

Jedenfalls ist das nicht die von mir hochgeladene Datei, deshalb erneut für andere User:


https://www.herber.de/bbs/user/66272.xls

MFG Andre


Beiträge aus den Excel-Beispielen zum Thema "SVERWEIS für zweite Zeile"