Microsoft Excel

Herbers Excel/VBA-Archiv

Sverweis mehrere Ergebnisspalten

Betrifft: Sverweis mehrere Ergebnisspalten von: Thomas
Geschrieben am: 27.04.2015 21:10:17

Hallo,

ich bin auf der suche nach einem SVERWEIS bei mehrfach vorkommenden Suchkriterium

und mehreren Ergebnisspalten.

Mit der Aray Formel =WENNFEHLER(INDEX(C:C;KKLEINSTE(WENN(A$1:A$99=$I$1;ZEILE($1:$99));ZEILE(C1)));"") erwische ich alle treffer der Spalte c. Nun habe ich aber auch noch werte in der Spalte E.

Kann mir jemand ein tipp geben?



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

Liebe Grüße Thomas

  

Betrifft: Das geht, ist aber ziemlich komplex! Dauert! orT von: Luc:-?
Geschrieben am: 28.04.2015 05:24:16

Morrn, Luc :-?


  

Betrifft: mit vba macro ändern? von: Thomas
Geschrieben am: 28.04.2015 10:11:33

Hallo,


erstmal besten Dank für dein Interesse.

Geht es womöglich mit vba besser?. das anliegende Macro macht so etwas ähnliches nur mit Datumswerten

Bekommt man dies so umgeschrieben das es mit Zahlwerten anstatt mit datumswerten funktioniert? Habe mich schon versucht aber ich schaffe es nicht.



liebe Grüsse Thomas







Public Sub prcCopyDatumsbereich()	
  Dim wksQuelle As Worksheet	
  Dim wksZiel As Worksheet	
  	
  Dim Zeile_Z1 As Long, Zeile_Z As Long, Zeile_Q As Long, StatusCalc As Long	
  Dim rngCopy As Range	
  Dim varStart As Variant, varEnde As Variant, SpalteDatum As Long	
  	
  With Application	
    .ScreenUpdating = False	
    StatusCalc = .Calculation	
    .Calculation = xlCalculationManual	
  End With	
  	
  Set wksZiel = ActiveWorkbook.Worksheets(2) '2 ggf. durch Blattname in Anführungszeichen  _	
ersetzen	
  SpalteDatum = 4  'Spate D	
  With wksZiel	
  	
    varStart = .Range("i1")	
    varEnde = .Range("i1")	
    	
    Zeile_Z1 = Application.WorksheetFunction.Max(3, _	
          .Cells(.Rows.Count, SpalteDatum).End(xlUp).Row) + 1	
    Zeile_Z = Zeile_Z1 - 1	
  End With	
  	
  Set wksQuelle = ActiveWorkbook.Worksheets(1) '1 ggf. durch Blattname in Anführungszeichen  _	
ersetzen	
  	
  With wksQuelle	
  For Zeile_Q = 1 To .Cells(.Rows.Count, SpalteDatum).End(xlUp).Row	
    If IsDate(.Cells(Zeile_Q, SpalteDatum)) Then	
      If .Cells(Zeile_Q, SpalteDatum) >= varStart _	
          And .Cells(Zeile_Q, SpalteDatum) <= varEnde Then	
        Set rngCopy = .Rows(Zeile_Q)	
        Zeile_Z = Zeile_Z + 1	
        rngCopy.Copy wksZiel.Cells(Zeile_Z, 1)	
      End If	
    End If	
  Next	
  End With	
  	
  If Zeile_Z > Zeile_Z1 Then	
    With wksZiel	
      .Range(.Rows(Zeile_Z1), .Rows(Zeile_Z)).Sort _	
          Key1:=.Cells(Zeile_Z1, SpalteDatum), order1:=xlAscending, Header:=xlNo	
    End With	
  End If	
  With Application	
    .ScreenUpdating = False	
    .Calculation = StatusCalc	
  End With	
End Sub



  

Betrifft: Nee, da würde ich dann meine, auch hier im ... von: Luc:-?
Geschrieben am: 28.04.2015 15:16:24

…Archiv zu findenden UDFs oder Werners Matrixformellösung vorziehen, Thomas;
allerdings sind 1zellige MatrixFmln weniger performant als mehr­zellige, weil bei ersteren in jeder Zelle die gesamte Matrix neu berechnet wdn muss, was bei kleinen Daten­mengen und wenig derar­tigen Fmln aber nicht relevant sein dürfte.
Ich bin zwar auch klassisch auf eine kürzere Fml gekommen als neopa, die hat aber den Nachteil, dass sie nur paarig fktioniert, d.h., du kannst nur eine Leerzelle in einem C;E-Paar nicht weglassen, ohne die Reihenfolge durch­einander­zu­bringen. Außerdem darf dann auch nur die E-Zelle leer sein. Mit diesen Einschrän­kungen bin ich auf folgd mehr­zellige MatrixFml gekommen:
I2:I9:{=WENNFEHLER(INDEX((C1:C15;E1:E15);KKLEINSTE(WENN(INDEX(A1:A15;GANZZAHL((ZEILE(1:30)-1)/2)+1)=I1;GANZZAHL((ZEILE(1:30)-1)/2)+1);ZEILE(1:30));1;2^REST(ZEILE(1:30)-1;2));"")}
Überzählige Zellen wdn mit Leer-Strings gefüllt, 0 zwischendurch kann mit benutzer­definiertem Format 0;;;@ ausgeblendet wdn.
Dein Wunschergebnis erhältst du mit neopas 1zelliger MatrixFml, die nach Bedarf gezogen wdn kann (was neben der Beschränkung auf 1 Zelle und der damit verbundenen höheren Tabellen­flexibilität oft der einzige Vorteil 1zelliger MatrixFmln ist).
Falls du lieber eine kürzere Fml hättest, musst du VBA-basierte UDFs einsetzen wie in folgd mehr­zelliger MatrixFml:
I2:I8:{=MTRANS(VSplit(VJoin(VSplit(VJoin(WENN(A1:A20=I1;C1:C20&"|"&E1:E20;"");"|";-1);"|");"|";-1);"|"))}
Überzählige Zellen enthalten einen nicht per Fml eliminierbaren #NV-Fehlerwert, was daran liegt, dass der Ergebnis­Vektor auf die relevante Werte­anzahl festgelegt wird.
Die beiden UDFs sind im Archiv zu finden. Von VJoin solltest du aber mindestens Version1.1 verwenden. Beide sind auch in 2 hochge­ladenen Dateien zu finden, VJoin auch in der neuesten Version1.3 (die Enum mitkopieren!). Die unkompliziertere Datei scheint der Server mal wieder verbummelt zu haben!
Übrigens bin ich nicht auf das Anpassen von Fremd-Makros angewiesen, ich korrigiere sie höchstens. ;-]
Gruß, Luc :-?


  

Betrifft: gelöst besten Dank an allen Helfern von: Thomas
Geschrieben am: 28.04.2015 18:19:17

Hallo Luc,

vielen dank für deine Antwort die Formel von neopa C funktioniert super. Ich konnte Sie auch schon anpassen. Habe Sie mal mit 5000 Daten Sätzen getestet und hatte keine Probleme.

Die UDF habe ich mir schon mal gezogen aber da kann ich noch nicht viel sagen dies muss ich mir erstmals in ruhe reinziehen es ist schon sehr interessant.

Ich glaube Dir das Du solche Marcos auch schreiben kannst, dieses Marco hat mal FCS für mich geschrieben und es leistet sehr gute Dienste ich bin sehr stolz darauf. Mein Gedanke war nur vielleicht ist es schnell gemacht aus ein Datumsformat ein Zahlenformat zu machen dann macht es nicht mehr soviel Arbeit. War nicht bös gemeint.

Die UDF kann ich mir erst am WE anschauen. Mal sehen



Liebe grüsse Thomas


  

Betrifft: auch ohne Hilfsspalte / Matrixformel lösbar ... von: der neopa C
Geschrieben am: 28.04.2015 10:07:39

Hallo Thomas,

... doch in XL2007 müßte ich dazu etwas länger nachdenken. Dagegen könnte ich eine solche Formellösung in XL2010 leicht und locker anbieten. Aber Du hast ja "nur" XL2007.

Hier dafür deswegen mein Matrixformelvorschlag für die bedingte Auflistung von Daten aus Spalte C:E mit XL2007. Die Formel einfach nach unten kopieren:

 ABCDEFG
11 der die der
21 müllerAott die
32 b y  müller
43 a z A
54 a   ott
61 restNest  rest
76 a   Nest
87 a   a
98 a   meire
109 a    
1110 a    
121 a meire  
1312 a    
1499 a    
1514 a    
1699      
1716      
1817      
1918      
2019      
21       

Formeln der Tabelle
ZelleFormel
G1{=WENNFEHLER(INDEX(A:E;KÜRZEN((10^4-KKLEINSTE(10^4-(C$1:E$99<>"")*(A$1:A$99=1)*(ZEILE(A$1:A$99)*100+SPALTE(C1:E1)); SUMME((A$1:A$99=1)*(C$1:E$99<>""))+1-ZEILE(A1)))/100;); REST((10^4-KKLEINSTE(10^4-(C$1:E$99<>"")*(A$1:A$99=1)*(ZEILE(A$1:A$99)*100+SPALTE(C1:E1)); SUMME((A$1:A$99=1)*(C$1:E$99<>""))+1-ZEILE(A1))); 100)); "")}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!



Gruß Werner
.. , - ...


  

Betrifft: klappt von: Thomas
Geschrieben am: 28.04.2015 15:57:09

Hallo,


besten Dank die Formel klappt super.



vielen Dank

Liebe Grüsse Thomas


 

Beiträge aus den Excel-Beispielen zum Thema "Sverweis mehrere Ergebnisspalten"