MaxWenn / MaxIf / max Wenn Kombination
Gerhard
ich habe hier und über Google nach einer Funktion gesucht, die die
Möglichkeiten von MAX und SUMMEWENN kombiniert - und bin icht fündig
geworden.
Deshalb habe ich mich selbst versucht - hier das Ergebnis:
Public Function MaxWenn(STRsuche As String, Sbereich As Range,
Ebereich As Range) As Double
Dim Zelle As Range
For Each Zelle In Sbereich
If Zelle.Value = STRsuche Then
MaxWenn = WorksheetFunction.MAX(MaxWenn, Cells(Zelle.Row,
Ebereich.Column))
End If
Next
End Function
Funktioniert nach meinen bisherigen Kenntnissen gut. Lässt sich
bestimmt noch verbessern - bin halt kein VBA Profi.
Kurze Erklärung: STRsuche enthält den Suchstring (oder Bezug auf den
Suchbegriff), in Sbereich wird gesucht und in Ebereich wird der der
Maximalwert ermittelt.
Ich hoffe, der eine oder andere kann etwas damit anfangen :-)
Alexander Wolff
> Kurze Erklärung: STRsuche enthält den Suchstring (oder Bezug auf den
> Suchbegriff), in Sbereich wird gesucht und in Ebereich wird der der
> Maximalwert ermittelt.
>
> Ich hoffe, der eine oder andere kann etwas damit anfangen :-)
Ich vermute, Du möchtest einen Kommentar? :-)
SUMMEWENN ist für mich eigentlich nicht daran beteiligt. Du möchtest einfach
ein bedingtes MAX. Und das ginge - neben Deiner funktionierenden
VBA-Funktion - auch mit folgender Tabellenfunktion:
{=MAX(WENN(SBereich=STRSuche;EBereich))}
{} bedeutet: Nicht {} eingeben, sondern mit Strg-Umsch-Eingabe eingeben.
--
Moin+Gruss Alexander - MVP for MS Excel - www.xxcl.de - mso2000sp3 --7-2
Gerhard
On 16 Apr., 14:34, "Alexander Wolff" <oo...@gmx.de> wrote:
> Ich vermute, Du möchtest einen Kommentar? :-)
Nur positive ;-) Eigentlich wollte ich die Funktion lediglich anderen
zur Verfügung stellen, da ich eine reine VBA Lösung bisher nicht
finden konnte.
>
> SUMMEWENN ist für mich eigentlich nicht daran beteiligt. Du möchtest einfach
> ein bedingtes MAX. Und das ginge - neben Deiner funktionierenden
> VBA-Funktion - auch mit folgender Tabellenfunktion:
Danke auf jeden Fall für das Feedback. Kombination aus SUMMEWENN und
MAX ist vielleicht wirklich unglücklich ausgedrückt. Die
Parameterübergabe ist ähnlich wie bei SUMMEWENN, es wird der rechte
Teil von SUMME*WENN* verwendet :-)
>
> {=MAX(WENN(SBereich=STRSuche;EBereich))}
>
> {} bedeutet: Nicht {} eingeben, sondern mit Strg-Umsch-Eingabe eingeben.
Diese Matrixfunktion hatte ich mehrfach gefunden, trotzdem danke. Ich
bastele gerade an einer formelfreien Mappe.
Nochmals: Vielen Dank für Dein Feedback!
Grüße
Gerhard
Alexander Wolff
Sehr interessant! Zu welchem Zweck? Um Logik und Inhalt zu trennen, etwa zum
.CSV-Versand oder überhaupt zum Weiterreichen an Fremde, die den Aufbau
nicht nachvollziehen können sollen? Platz sparen ist auch ein Argument -
genauso, wie partiell mögliche Neuberechnung auf eigenes Risiko (man muss
dann den "Dependency Tree" selbst ablaufen).
--
Gerhard Ortmann
> Die Tastatur von Gerhard wurde wie folgt gedrückt:
>> bastele gerade an einer formelfreien Mappe.
>
>
> Sehr interessant! Zu welchem Zweck? Um Logik und Inhalt zu trennen, etwa
> zum .CSV-Versand oder überhaupt zum Weiterreichen an Fremde, die den
> Aufbau nicht nachvollziehen können sollen? Platz sparen ist auch ein
> Argument - genauso, wie partiell mögliche Neuberechnung auf eigenes Risiko
> (man muss dann den "Dependency Tree" selbst ablaufen).
Aus mehreren Gründen eigentlich:
1. Neugier - ich arbeite noch nicht sehr lange mit VBA
2. Platzersparnis. Ich habe jetzt von Anfang des Jahres ca. 45.000
Datensätze, aus denen bestimmte Werte herausgesucht werden müssen. Wie
aufgebläht Excel-Dateien sind, wenn man mit S(W)VERWEIS arbeitet, ist ja
bekannt :-/
3. Schutz - ein weiteres Problem. Der beste Schutz für eine Formel ist, wenn
es keine Formel gibt :-)
Alle Bereiche sind mir ganz gut gelungen, glaube ich.
Andreas Killer
> 1. Neugier - ich arbeite noch nicht sehr lange mit VBA
Dann ist vielleicht folgende Function für Dich interessant.
Andreas.
Public Function WennWert(Wert, Wenn, Dann, Optional Sonst, _
Optional Textvergleich) As Variant
'Sucht WERT in WENN und gibt den entsprechenden Wert in DANN _
zurück, ansonsten SONST
'Im Prinzip ermöglicht diese Funktion diesen Terminus in einer _
Tabelle:
'select case Wert
' case Wenn(1): WennWert = Dann(1)
' case Wenn(2): WennWert = Dann(2)
' case else: WennWert = Sonst
'endif
'Für WENN und DANN sind Bereiche/Listen/Einzelwerte möglich:
'=WennM(A1;B1:C3;D1:E3;F1)
'=WennM(A1;{"a";"b";"c";"d";"e";"f"};D1:E3;F1)
'=WennM(A1;B1:C3;{"a";"b";"c";"d";"e";"f"};F1)
'=WennM(A1;"a";"b";F1)
'Praktisches Beispiel liefert den Umrechnungsfaktor für mm:
'=WennM(A1;{"Zoll";"mm";"cm"};{25,4;1;10};0;WAHR)
Dim I As Long, X As Long, Y As Long, r As Range, Found As Boolean
On Error GoTo Errorhandler
If IsMissing(Textvergleich) Then Textvergleich = True Else _
Textvergleich = CBool(Textvergleich)
'#NV Nicht gefunden als Default
WennWert = CVErr(xlErrNA)
Select Case TypeName(Wenn)
Case "Range"
For X = 1 To Wenn.Columns.Count
For Y = 1 To Wenn.Rows.Count
If Textvergleich Then
If StrComp(Wert, Wenn.Cells(Y, X), vbTextCompare) = 0 _
Then
Found = True
Exit For
End If
Else
If Wert = Wenn.Cells(Y, X) Then
Found = True
Exit For
End If
End If
Next
If Found Then Exit For
Next
Case "Variant()"
For I = LBound(Wenn) To UBound(Wenn)
If Textvergleich Then
If StrComp(Wert, Wenn(I, 1), vbTextCompare) = 0 Then
Found = True
Exit For
End If
Else
If Wert = Wenn(I, 1) Then
Found = True
Exit For
End If
End If
Next
Case Else
If Textvergleich Then
Found = StrComp(Wert, Wenn) = 0
Else
If Wert = Wenn Then Found = True
End If
End Select
If Not Found Then
If Not IsMissing(Sonst) Then WennWert = Sonst
Exit Function
End If
Select Case TypeName(Dann)
Case "Range"
If X > 0 Then
'Wenn war auch ein Range
WennWert = Dann.Cells(Y, X)
Else
'Wenn war eine Liste/Wert
For X = 1 To Dann.Columns.Count
For Y = 1 To Dann.Rows.Count
I = I - 1
If I <= 0 Then
WennWert = Dann.Cells(Y, X)
Exit Function
End If
Next
Next
End If
Case "Variant()"
'Wenn WENN ein Range war, dann Index berechnen
If X > 0 Then I = (X - 1) * Wenn.Rows.Count + Y
WennWert = Dann(I, 1)
Case Else
WennWert = Dann
End Select
Exit Function
Errorhandler:
WennWert = CVErr(xlErrValue)
End Function
Gerhard Ortmann
>
> Public Function WennWert(Wert, Wenn, Dann, Optional Sonst, _
> Optional Textvergleich) As Variant
> 'Sucht WERT in WENN und gibt den entsprechenden Wert in DANN _
> zurück, ansonsten SONST
>
Klingt interessant. Ich schaue mal, ob ich vielleicht demnächst einen
Einsatzzweck dafür finde. Ich darf doch?
Ganz lieben Dank auf jeden Fall!
VG + und noch einen schönen Sonntag
Gerhard
Bernd P
Leider arbeitet Deine Funktion nicht korrekt. Beispiel: Gib in A1 und
A2 ein einfaches "a" ein (ohne Gänsefüßchen). Dann in B1 die Zahl -1
(minus Eins). Und lass B2 leer (im Zweifel ENTF auf B2 drücken).
=MAX(B1:B2)
ergibt korrekt -1.
Deine Funktion
=MaxWenn("a",A1:A2,B1:B2)
ergibt 0.
Ein Beispiel für eine korrekte Funktion MaxIf erhältst Du, wenn Du in
http://www.sulprobil.com/html/medianif.html
alle "median" durch "max" ersetzt.
Viele Grüße,
Bernd
Gerhard
> Hallo Gerhard,
>
> Leider arbeitet Deine Funktion nicht korrekt. Beispiel: Gib in A1 und
> A2 ein einfaches "a" ein (ohne Gänsefüßchen). Dann in B1 die Zahl -1
> (minus Eins). Und lass B2 leer (im Zweifel ENTF auf B2 drücken).
>
danke für Dein Feedback.
Mag sein, dass die Funktion bei falschen Eingaben und/oder negativen
Werten nicht korrekt funktioniert. Im von mir benutzten Report
funktioniert es, weil weder Fehleingaben noch negative Werte
vorkommen.
Die von Dir aufgezeigte Lösung ist mir bekannt, wie weiter oben
bereits angeführt. Ich hatte jedoch geschrieben, dass eine VBA Lösung
wichtig sei.
Trotzdem ist Dein Hinweis auf die Korrektheit natürlich wichtig. Ich
schau mal, woran es liegen könnte.
Guten Wochenstart!
Gerhard
Bernd P
Ich habe Dir einen Tip fuer eine korrekte VBA Loesung gegeben. Meine
VBA Funktion sammelt die Argumente, fuer die der Stringvergleich
passt, und gibt sie dann gesammelt an die entsprechende Worksheet
Funktion (bei mir Median, aber mit Suchen und Ersetzen machst Du
wirklich einfach Max daraus). Damit ist ein IDENTISCHES Verhalten wie
die Worksheet Funktion gesichert, denke ich.
Viele Gruesse,
Bernd
Gerhard Ortmann
> Hallo Gerhard,
Hallo Bernd :-),
>
> Ich habe Dir einen Tip fuer eine korrekte VBA Loesung gegeben.
Mein Gott, wer lesen kann, ist klar im Vorteil - mea culpa :-/
Ich habe Deinen Lösungslink nur überflogen, eine Arrayfunktion gesehen und
gedacht - toll, kenne ich schon.
Das wirklich wichtige habe ich übersehen! Nun gut, Deine Funktion
funktioniert. Glaube ich Dir ;-) .. ich verstehe sie zwar noch nicht ganz,
aber das ist ja erst einmal egal.
Aber hast Du vielleicht eine Erklärung, warum meine im von Dir vorgestellten
Beispiel NICHT funktioniert? Von der Logik her sollte es doch eigentlich
passen, oder?
Sei mir nicht böse, aber ich lerne gerade VBA ein wenig kennen und Deine
Funktion einfach abkupfern, wer zu einfach und der Lerneffekt für mich gleich
Null. Ich WILL, dass MEINE funktioniert :-)
Any ideas?
> Viele Gruesse,
> Bernd
Dir nen schönen Abend
Gerhard
Bernd P
> ...
> ... Ich WILL, dass MEINE funktioniert :-)
> ...
Kann ich gut nachvollziehen.
Deine Logik war eigentlich vollkommen richtig, das Problem war etwas
gemein im Standardwert der Funktion versteckt. Der ist halt Null, wenn
er ohne vorherige Wertzuweisung referenziert wird, und Null ist
groesser als ev. vorkommende negative Werte.
So wird's richtig (eine von Millionen Moeglichkeiten):
Function MaxWenn(STRsuche As String, Sbereich As Range, _
Ebereich As Range) As Double
Dim Zelle As Range
Dim bWertGefunden As Boolean
bWertGefunden = False
For Each Zelle In Sbereich
If Zelle.Value = STRsuche And Zelle.Value <> "" Then
If bWertGefunden Then
MaxWenn = WorksheetFunction.Max(MaxWenn, _
Cells(Zelle.Row, Ebereich.Column))
Else
MaxWenn = Cells(Zelle.Row, Ebereich.Column)
bWertGefunden = True
End If
End If
Next
End Function
Aber eine kleine Anmerkung: Die Konstruktion Cells(Zelle.Row,
Ebereich.Column)
gefaellt mir nicht so gut, weil sie notwendigerweise
spaltenorientierte und nicht-verschobene (!) Eingaben erwartet.
Ich gebe jedoch zu, dass ich voreingenommen bin :-)
Viele Gruesse,
Bernd
Gerhard
> Kann ich gut nachvollziehen.
danke :-) ... ich denke, Du kennst das ;-)
> Deine Logik war eigentlich vollkommen richtig, das Problem war etwas
> gemein im Standardwert der Funktion versteckt. Der ist halt Null, wenn
> er ohne vorherige Wertzuweisung referenziert wird, und Null ist
> groesser als ev. vorkommende negative Werte.
Verstehe ich noch nicht so ganz, aber das wird schon :-)
>
> FunctionMaxWenn(STRsuche As String, Sbereich As Range, _
> Ebereich As Range) As Double
Danke :-)
>
> Aber eine kleine Anmerkung: Die Konstruktion Cells(Zelle.Row,
> Ebereich.Column)
> gefaellt mir nicht so gut, weil sie notwendigerweise
> spaltenorientierte und nicht-verschobene (!) Eingaben erwartet.
Nicht-verschobene Eingaben sind ja so unüblich nicht, oder? -->
SUMMEWENN
Aber die Hauptursachen dürften wohl meine (noch?) begrenzten VBA-
Fähigkeiten und
meiner klaren Aufgabenstellung:
Eine Spalte (Zeilen 1-65536) zum Suchen, eine mit dem Ergebnis. Keine
negativen Werte.
Ich gebe freimütig zu, dass Deine Funktion, so sie so funktioniert,
wie erwartet, deutlich
eleganter ist :-)
>
> Ich gebe jedoch zu, dass ich voreingenommen bin :-)
Das ist nicht schlimm - viele Sichtweisen erweitern den Horizont. Ich
bin's ja auch ;-)
>
> Viele Gruesse,
> Bernd
Dir nen schönen Tag,
Gerhard