IndexVergleich finde maximalwert ohne Array

Bild

Betrifft: IndexVergleich finde maximalwert ohne Array
von: Daniel
Geschrieben am: 12.09.2015 14:05:16

Hallo zusammen, ich hoffe Ihr könnt mir mal wieder weiterhelfen.
Ich möchte eine IndexVergleichabfrage machen bei der der Maximalwert der Ergebnissspalte bei mehreren gefundenen werten in der Suchspalte ausgegeben wird.
Nun habe ich einige funktionen gefunden die das mit einer recht komplizierten Array formel lösen. Allerdings soll die Formel in einer durch eine externe Quelle (ERP) befüllten Tabelle Stehen die bei jedem export länger wird.
Also war meine Idee eine funktion in vba zu schreiben die per .match zum ersten vorkommniss läuft, den entsprechenden wert in eine Variable schreibt, den nächsten sucht und prüft ob das hier zurückgegebene Ergebniss größer als der letzte ist und so weiter...
in diesem Vereinfachten Fall sollte 6 ausgegeben werden...


Ergebnis Suchspalte
3       	2
4       	1
3       	3
6       	1
5        	1
meine bisherigen versuche so eine schleife zu basteln sind kläglich gescheitert :(
ich hoffe ihr könnt mir helfen.

Bild

Betrifft: Index/Vergleich brauchst Du da nicht
von: WF
Geschrieben am: 12.09.2015 14:40:09
Hi,
die Arrayformel lautet:
{=MAX((A:A)*(B:B=1))}
Das solltest Du in VBA fummeln können.
WF

Bild

Betrifft: AW: IndexVergleich finde maximalwert ohne Array
von: Daniel
Geschrieben am: 12.09.2015 15:42:48
Hier ist was ich bisher habe... irgendwo hab ich nen denkfehler :/


Option Explicit
Function MatchMax(MRange As Range, Cnumb As Double, Crit As Range, CRange As Range) ' Mrange= _
Matrix for Index;_
        'Cnumb=Column number of matrix from which to return the value;  Crit=Searched Criteria;  _
Crange=Search Range for Match
    Dim TempV As Long
    Dim TempI As Long
    Dim TempV2 As Long
    Dim SubRange As Range
    Dim lrow As Long
    Dim ColumnC As Long
    Dim ColumnM1 As Long
    Dim ColumnM2 As Long
    Dim Count As Long
    Dim i As Long
    
    
    TempV2 = 0
    ColumnC = CRange.Column
    ColumnM1 = MRange.Column
    ColumnM2 = MRange.ColumnS.Count + ColumnM1
    lrow = CRange.Rows.Count
    Count = WorksheetFunction.CountIf(CRange, Crit)
    TempI = WorksheetFunction.Match(Crit.Value, CRange, 0)
    
    TempV = WorksheetFunction.Index(MRange, WorksheetFunction.Match(Crit.Value, CRange, 0),  _
Cnumb)
    For i = 0 To Count
        If TempV2 < TempV Then
            TempV2 = WorksheetFunction.Index(Cells(TempI, ColumnM1), Cells(lrow, ColumnM2), _
            WorksheetFunction.Match(Crit, Range(Cells(TempI, ColumnC), _
            Cells(lrow, ColumnC)), 0), Cnumb)
            TempI = WorksheetFunction.Match(Crit, Range(Cells(TempI, ColumnC), Cells(lrow,  _
ColumnC)), 0)
        End If
    Next i
    
    MatchMax = TempV2
    
            
End Function


Bild

Betrifft: AW: IndexVergleich finde maximalwert ohne Array
von: Sepp
Geschrieben am: 12.09.2015 16:05:23
Hallo Daniel,
ohne VBA und ohne Matrixformel.
Tabelle1

 ABCDE
1EergebnisSuchwert Max A wenn B=148
2303   
3463   
4121   
5503   
6463   
753   
863   
9471   
10242   
11443   
1283   
1372   
14441   
15341   
16102   
17452   
1823   
19272   
20442   
21421   
2291   
23212   
24481   
2512   
26253   
27373   
28471   

Formeln der Tabelle
ZelleFormel
E1=AGGREGAT(14;6;A:A/(B:B=1); 1)


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Sepp


Bild

Betrifft: AW: IndexVergleich finde maximalwert ohne Array
von: Daniel
Geschrieben am: 12.09.2015 19:45:44
Hallo Sepp,
super! Aggregate kannte ich noch gar nicht... super lösung vielen dank!!
Aber könntest du dir den Code doch einmal ansehen und mir sagen warum ich #wert bekomme?
Als Versuch die gleiche Tabelle wie vorher. Hier der code der die beiden ersten richtig findet und dann einfach abbricht (TempV2 findet den letzten wert (5) nicht)
Hier der code:


Option Explicit
Function MatchMax(MRange As Range, Cnumb As Double, Crit As Range, CRange As Range) ' Mrange= _
Matrix for Index;_
        'Cnumb=Column number of matrix from which to return the value;  Crit=Searched Criteria;  _
Crange=Search Range for Match
    Dim TempV As Long
    Dim TempI As Long
    Dim TempV2 As Long
    Dim SubRange As Range
    Dim lrow As Long
    Dim ColumnC As Long
    Dim ColumnM1 As Long
    Dim ColumnM2 As Long
    Dim Count As Long
    Dim i As Long
    
    
    TempV2 = 0
    ColumnC = CRange.Column
    ColumnM1 = MRange.Column
    ColumnM2 = MRange.ColumnS.Count + ColumnM1
    lrow = CRange.Rows.Count
    Count = WorksheetFunction.CountIf(CRange, Crit)
    TempI = WorksheetFunction.Match(Crit.Value, CRange, 0)
    
    
    
    TempV = WorksheetFunction.Index(MRange, WorksheetFunction.Match(Crit.Value, CRange, 0),  _
Cnumb)
    For i = 0 To Count
            TempI = TempI + WorksheetFunction.Match(Crit, Range(Cells(TempI + 1, ColumnC),  _
Cells(lrow, ColumnC)), 0)
   
      
            TempV2 = WorksheetFunction.Index(Range(Cells(TempI, ColumnM1), Cells(lrow, ColumnM2) _
), _
            TempI, Cnumb)
        If TempV2 > TempV Then
            TempV = TempV2
        End If
    Next i
    
    MatchMax = TempV
    
            
End Function


Bild

Betrifft: AW: IndexVergleich finde maximalwert ohne Array
von: Daniel
Geschrieben am: 12.09.2015 19:46:33
vorweg schon einmal vielen Dank ;)
Gruß

Bild

Betrifft: AW: IndexVergleich finde maximalwert ohne Array
von: Sepp
Geschrieben am: 12.09.2015 20:14:26
Hallo Danie,
ich würde so eine Funktion, wenn ich schon eine für dieses Problem bräuchte, so schreiben.

Public Function MatchMax(ValuesRange As Variant, CriteriaRange As Variant, Criteria As Variant) As Variant
Dim vntV As Variant, vntC As Variant
Dim lngI As Long, dblTmp As Double

On Error GoTo ErrExit

vntV = ValuesRange
vntC = CriteriaRange

If UBound(vntV, 1) <> UBound(vntC, 1) Then GoTo ErrExit

For lngI = 1 To UBound(vntV, 1)
  If vntC(lngI, 1) = Criteria Then
    If IsNumeric(vntV(lngI, 1)) Then
      dblTmp = Application.Max(dblTmp, vntV(lngI, 1))
    End If
  End If
Next
MatchMax = dblTmp
Exit Function
ErrExit:
MatchMax = CVErr(xlErrValue)
End Function



In der Tabelle dann so:
Tabelle1

 ABCDE
1EergebnisSuchwert Max A wenn B=148
2303   
3463   
4121   
5503   
6463   
753   
863   
9471   
10242   
11443   
1283   
1372   
14441   
15341   
16102   
17452   
1823   
19272   
20442   
21421   
2291   
23212   
24481   
2512   
26253   
27373   
28471   

Formeln der Tabelle
ZelleFormel
E1=MatchMax(A2:A28;B2:B28;1)


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Sepp


Bild

Betrifft: AW: IndexVergleich finde maximalwert ohne Array
von: Daniel
Geschrieben am: 12.09.2015 21:16:04
Lach, das muss ich mir mal in ruhe ansehen
Vielen Dank!!

Bild

Betrifft: AW: IndexVergleich finde maximalwert ohne Array
von: Daniel
Geschrieben am: 13.09.2015 15:02:17
HI
ich würde da noch folgende kleine Optimierung mit reinbringen:

vntV = Intersect(ValuesRange, ValuesRange.Worksheet.UsedRange).Value
vntC = Intersect(CriteriaRange, CriteriaRange.Worksheet.UsedRange).Value
das erlaubt dir, das MatchMax ohne Performanceverlust mit offenen Zellbezügen einzusetzen
=MatchMax(A:A;B:B;1)

wenn du ohne diesen Zusatz eine ganze Spalte angibst, läuft die Schleife auch über alle Zellen dieser Spalte, dh bis 1,04 Mio.
mit diesem Zusatz wird der angegebene Bereich mit dem tatsächlich benutzen Bereich des Blattes verglichen und angepasst, so dass die Schleife maximal bis zur letzten Zelle des genutzen Bereichs laufen muss.
Gruss Daniel

Bild

Betrifft: AW: IndexVergleich finde maximalwert ohne Array
von: Daniel
Geschrieben am: 13.09.2015 16:10:02
HI
ein Denkfehler ist das "For i = 0 To Count"
die Match-Funktion bringt dir einen Fehler, wenn sie den Wert nicht finden kann.
Das bedeutet, dass du das Match auch nur so oft verwenden darfst, wie der Suchbegriff in der Spalte vorkommt, und das wäre "For i = 1 To Count"
allerdings hast du ja das erste Match schon vor Klammer stehen, dh dann wäre es "For i = 2 to Count"
(ich würde hier aber das erste Match in die Klammer mit aufnehmen)
kleiner Tip am Rande: "Count" ist als Variablenbezeichung ungünstig, weil es auch eine VBA-Funktion ".Count" gibt. Das kann zwar funktionieren, aber es kann auch zu Fehlern führen, oder zumindest zu Verwirrung beim Programmierer
So ganz generell zu deinem Code:
die Idee ist war ganz gut, aber wenn man sich ein bisschen tiefer mit VBA auskennt und auch weiss wie die einzelnen VBA-Funktionen funktionieren, dann wird klar, dass allein schon das Worksheetfunction.CountIf und jedes Worksheetfunction.Match intern genau die Schleife ausführen, wie Sepp in seinem Code.
Dh. wenn du erst die Anzahl der Fundstellen ermittelt hast, hat Sepp mit seiner Vorgehensweise schon das vollständige Ergebnis.
Gruss Daniel

Bild

Betrifft: wenn schon ne Krücke, dann ohne Aggregat
von: WF
Geschrieben am: 12.09.2015 20:08:44
Hi,
ohne { }:
=MAX((MMULT((B:B=1)*A:A;1)))
WF

 Bild

Beiträge aus den Excel-Beispielen zum Thema "IndexVergleich finde maximalwert ohne Array "