KKLEINSTE über 2 Bereiche
Bruno Sutter
Ich hatte hier schon mal das Problem mit KKLEINSTE mit einer WENN Bedingung
�ber 2 Bereiche gepostet. Als Antwort hatte ich dann die folgende Formel
erhalten, welche tadellos funktioniert:
{=MIN(WENN((C7:C19="S");E7:E19);WENN((J7:J16="S");L7:L16))}
Dies gibt mir den kleinsten Wert zur�ck, den ich als Streichresultat
ben�tze. Nun werden wir ab n�chstes Jahr 2 Streichresultate haben. Ich habe
sowohl mit der MIN- als auch mit der KKLEINSTE;1- und ;KKLEINSTE;2-Funktion
lange ge�bt aber ich kriege es einfach nicht hin.
Wie m�sste die Formel mit der MIN oder KKLEINSTE Funktion aussehen, damit
ich die Summe der 2 Streichresultate erhalte?
Vielen Dank f�r Eure wie immer super Ideen.
Freundliche Gr�sse
Bruno
Alexander Wolff
K1 und K2 gemeinsam in _einer_ Reihe auftauchen.
Wenn Du wirklich nur so wenige Daten wie im Beispiel hast, f�hre sie doch an
dritter Stelle zusammen (=hintereinander) und werte sie dann aus.
--
Moin+Gruss Alexander - MVP for MS Excel - www.xxcl.de - mso2000sp3 --7-2
Bruno Sutter
Entschuldige, ich verstehe nicht ganz. Ich m�chte doch einfach die 2
kleinsten Resultate, welche in E7:E19 und L7:L16 stehen erhalten, wenn in
derselben Zeile in den Spalten zuvor, also C7:19 und J7:J16 jeweils ein "S"
steht.
In den Spalten E und J stehen Buchstaben f�r "Stand" und "Feste". Nun z�hlen
die entsprechenden Resultate jeweils f�r den Stand- oder Festsieger. Ich
brauche nun f�r den Standsieger die 2 niedrigsten Resultate.
Hoffe, dass ich mich verst�ndlich ausdr�cken konnte.
Vielen Dank nochmals
Gruss
Bruno
"Alexander Wolff" <oo...@gmx.de> schrieb im Newsbeitrag
news:4b76897e$0$3294$8e6e...@newsreader.ewetel.de...
Bernd P
=KKLEINSTE(D;2)
Definiere den Bereich D:
E7:E19;J7:J16
[klappt mit Excel 2007]
Viele Grüße,
Bernd
Alexander Wolff
>> dass K1 und K2 gemeinsam in _einer_ Reihe auftauchen.
>>
>> Wenn Du wirklich nur so wenige Daten wie im Beispiel hast, f�hre sie doch
>> an dritter Stelle zusammen (=hintereinander) und werte sie dann aus.
> Entschuldige, ich verstehe nicht ganz. Ich m�chte doch einfach die 2
> kleinsten Resultate, welche in E7:E19 und L7:L16 stehen erhalten, wenn in
> derselben Zeile in den Spalten zuvor, also C7:19 und J7:J16 jeweils ein
> "S" steht.
>
> In den Spalten E und J stehen Buchstaben f�r "Stand" und "Feste". Nun
> z�hlen die entsprechenden Resultate jeweils f�r den Stand- oder
> Festsieger. Ich brauche nun f�r den Standsieger die 2 niedrigsten
> Resultate.
Nochmal mein Vorschlag: Beziehe X1:X23 auf C7:C19;J7:J16 und Y entsp. auf
E;L
{=KKLEINSTE(WENN($X$1:$X$23="S";$Y$1:$Y$23);1)}
{=KKLEINSTE(WENN($X$1:$X$23="S";$Y$1:$Y$23);2)}
Matrixformeln sollten Dir ein Begriff sein; sonst hier in der Gruppe suchen!
Alexander Wolff
Klaus "Perry" Pago
"Bruno Sutter" <bruno....@aktiv.ch> schrieb im Newsbeitrag
news:uimQlHKr...@TK2MSFTNGP02.phx.gbl...
> Hallo Alexander
> Entschuldige, ich verstehe nicht ganz. Ich m�chte doch einfach die 2
> kleinsten Resultate, welche in E7:E19 und L7:L16 stehen erhalten, wenn in
> derselben Zeile in den Spalten zuvor, also C7:19 und J7:J16 jeweils ein
> "S" steht.
>
> In den Spalten E und J stehen Buchstaben f�r "Stand" und "Feste". Nun
> z�hlen die entsprechenden Resultate jeweils f�r den Stand- oder
> Festsieger. Ich brauche nun f�r den Standsieger die 2 niedrigsten
> Resultate.
wenn zwischen den Spalten D und K kein "S" mehr vorkommt, kann man alles zu
einem gro�en Bereich zusammenfassen:
=KKLEINSTE(WENN(D7:K19="S";E7:L19;"");1)
=KKLEINSTE(WENN(D7:K19="S";E7:L19;"");2)
getestet mit xl2007
Gru�
Klaus
Andreas Killer
> {=MIN(WENN((C7:C19="S");E7:E19);WENN((J7:J16="S");L7:L16))}
>
> Dies gibt mir den kleinsten Wert zurï¿œck, den ich als Streichresultat
> benï¿œtze. Nun werden wir ab nï¿œchstes Jahr 2 Streichresultate haben. Ich habe
Man kann mit VBA eine benutzerdefinierte Funktion schreiben und diese
statt einer Formel verwenden.
Dadurch bietet sich der Vorteil das eine beliebige Anzahl von
Bereichen und beliebige Anzahl an Resultaten mï¿œglich wird.
Der Code muss in ein normales Modul, wie's geht steht hier:
http://www.online-excel.de/excel/singsel_vba.php?f=44#s2
Als Formel verwendest Du
{=Streichresultat("S";C7:C19;E7:E19;J7:J16;L7:L16)}
und gibst sie als Matrixformel ï¿œber 2 Zellen ein um die 2 kleinsten
Werte zu erhalten. (Bei einer Eingabe ï¿œber 3 Zellen gibt sie Dir die 3
kleinsten Werte, usw.)
Auch weitere Bereiche sind problemlos mï¿œglich, Du musst die Angaben
natï¿œrlich 2 Bereiche (je einen Prï¿œf- sowie Wertebreich) erweitern.
Die Bereiche mￜssen die gleiche Grￜￜe (Anzahl Zellen) und Form (Anzahl
Zeilen/Spalten) haben.
Andreas.
Function Streichresultat(Prï¿œfText As String, _
ParamArray Bereiche()) As Variant
Dim I As Integer, Prï¿œf, Wert
Dim X As Long, Y As Long
Dim Res(), Cnt As Long
'Wenn Fehler dann nï¿œchster Bereich
On Error GoTo Nï¿œchster
'Durchlaufe alle Bereiche
For I = 0 To UBound(Bereiche) Step 2
'Prï¿œfbereiche einlesen
Prï¿œf = Bereiche(I)
'Werte einlesen
Wert = Bereiche(I + 1)
'Druchlaufe alle Zellen im Prï¿œfbereich
For Y = 1 To UBound(Prï¿œf)
For X = 1 To UBound(Prï¿œf, 2)
'Ist die Zelle gleich dem Prï¿œftext?
If StrComp(Prï¿œfText, Prï¿œf(Y, X), vbTextCompare) = 0 Then
'Ja, Wert merken
Cnt = Cnt + 1
ReDim Preserve Res(1 To Cnt)
Res(Cnt) = Wert(Y, X)
End If
Next
Next
Nï¿œchster:
Next
'Bei Fehler zum Ende
On Error GoTo ExitPoint
'Gefundene Werte sortieren
For X = 1 To Cnt - 1
For Y = X + 1 To Cnt
If Res(X) > Res(Y) Then
Wert = Res(X)
Res(X) = Res(Y)
Res(Y) = Wert
End If
Next
Next
'Ein #NV "anhï¿œngen"
ReDim Preserve Res(1 To Cnt + 1)
Res(Cnt + 1) = CVErr(xlErrNA)
'Zeilen oder Spalten ausgeben?
With Application.Caller
If .Rows.Count > .Columns.Count Then _
Res = WorksheetFunction.Transpose(Res)
End With
Streichresultat = Res
ExitPoint:
End Function
Klaus "Perry" Pago
> =KKLEINSTE(WENN(D7:K19="S";E7:L19;"");1)
> =KKLEINSTE(WENN(D7:K19="S";E7:L19;"");2)
als Matrixformeln eingeben.
Ansonsten g�be es noch die M�glichkeit, jeden Bereich
f�r sich nach den beiden Kleinsten zu untersuchen und
aus den 4 Werten dann die beiden Kleinsten zu ermitteln.
Gru�
Klaus
Bruno Sutter
Habe dies ausprobiert. Funktioniert nicht so richtig, da zwischen den
Spalten D und E andere Zahlen stehen.
Trotzdem vielen Dank und Gruss
Bruno
"Klaus "Perry" Pago" <pe...@dachs.ping.de> schrieb im Newsbeitrag
news:%233Ng09K...@TK2MSFTNGP02.phx.gbl...
Bruno Sutter
Ja mit Hilfspalten geht das schon, aber h�tte lieber eine L�sung ohne diese,
da dies ja nur ein Beispiel ist. Die MIN Formel, die ich hatte , ist ja auch
schon eine Matrixformel.
Vielen Dank und Gruss
Bruno
"Alexander Wolff" <oo...@gmx.de> schrieb im Newsbeitrag
news:4b769d37$0$3293$8e6e...@newsreader.ewetel.de...
Bernd P
Hallo Alexander,
Dann vielleicht D und K mit ReturnNonEmpty oder einer Abwandlung davon
"verpacken" damit die Einzelbereiche zusammengefasst werden?!
http://sulprobil.com/html/concatenate.html
Viele Grüße,
Bernd
Bruno Sutter
Ja, genau so geht es, so kann ich das Ding auch 50x runterkopieren.
Herrlich, was man alles mit VBA erreichen kann. Eigentlich alles, oder?
Vielen Dank und Gruss
Bruno
"Andreas Killer" <andreas...@gmx.net> schrieb im Newsbeitrag
news:4b76a746$0$6724$9b4e...@newsspool2.arcor-online.net...
> Bruno Sutter schrieb:
>
>> {=MIN(WENN((C7:C19="S");E7:E19);WENN((J7:J16="S");L7:L16))}
>>
>> Dies gibt mir den kleinsten Wert zur�ck, den ich als Streichresultat
>> ben�tze. Nun werden wir ab n�chstes Jahr 2 Streichresultate haben. Ich
>> habe
> Man kann mit VBA eine benutzerdefinierte Funktion schreiben und diese
> statt einer Formel verwenden.
>
> Dadurch bietet sich der Vorteil das eine beliebige Anzahl von Bereichen
> und beliebige Anzahl an Resultaten m�glich wird.
>
> Der Code muss in ein normales Modul, wie's geht steht hier:
> http://www.online-excel.de/excel/singsel_vba.php?f=44#s2
>
> Als Formel verwendest Du
>
> {=Streichresultat("S";C7:C19;E7:E19;J7:J16;L7:L16)}
>
> und gibst sie als Matrixformel �ber 2 Zellen ein um die 2 kleinsten Werte
> zu erhalten. (Bei einer Eingabe �ber 3 Zellen gibt sie Dir die 3 kleinsten
> Werte, usw.)
>
> Auch weitere Bereiche sind problemlos m�glich, Du musst die Angaben
> nat�rlich 2 Bereiche (je einen Pr�f- sowie Wertebreich) erweitern.
>
> Die Bereiche m�ssen die gleiche Gr��e (Anzahl Zellen) und Form (Anzahl
> Zeilen/Spalten) haben.
>
> Andreas.
>
> Function Streichresultat(Pr�fText As String, _
> ParamArray Bereiche()) As Variant
> Dim I As Integer, Pr�f, Wert
> Dim X As Long, Y As Long
> Dim Res(), Cnt As Long
> 'Wenn Fehler dann n�chster Bereich
> On Error GoTo N�chster
> 'Durchlaufe alle Bereiche
> For I = 0 To UBound(Bereiche) Step 2
> 'Pr�fbereiche einlesen
> Pr�f = Bereiche(I)
> 'Werte einlesen
> Wert = Bereiche(I + 1)
> 'Druchlaufe alle Zellen im Pr�fbereich
> For Y = 1 To UBound(Pr�f)
> For X = 1 To UBound(Pr�f, 2)
> 'Ist die Zelle gleich dem Pr�ftext?
> If StrComp(Pr�fText, Pr�f(Y, X), vbTextCompare) = 0 Then
> 'Ja, Wert merken
> Cnt = Cnt + 1
> ReDim Preserve Res(1 To Cnt)
> Res(Cnt) = Wert(Y, X)
> End If
> Next
> Next
> N�chster:
> Next
> 'Bei Fehler zum Ende
> On Error GoTo ExitPoint
> 'Gefundene Werte sortieren
> For X = 1 To Cnt - 1
> For Y = X + 1 To Cnt
> If Res(X) > Res(Y) Then
> Wert = Res(X)
> Res(X) = Res(Y)
> Res(Y) = Wert
> End If
> Next
> Next
> 'Ein #NV "anh�ngen"
Andreas Killer
> Ja, genau so geht es, so kann ich das Ding auch 50x runterkopieren.
Das freut mich. Allerdings solltest Du dieses Thema noch weiter
mitlesen, vielleicht gibt es ja doch eine einfache Formel-Lï¿œsung, denn
Formeln sind "von Natur aus" schneller als VBA.
> Herrlich, was man alles mit VBA erreichen kann. Eigentlich alles, oder?
Naja, fast alles. :-)
Andreas.
Alexander Wolff
Ja, mit
{=KKLEINSTE(WENN(returnnonempty($A$1:$A$5;$D$1:$D$5)="S";returnnonempty($B$1:$B$5;$E$1:$E$5));2)}
klappt es, wenn ich in Deiner Function ein If ...
...
For Each vI In v
For Each vJ In vI
' If Len(vJ) = 0 Then
i = i + 1
vR(i) = vJ
' End If
Next vJ
Next vI
...
... auskommentiere (die Bereiche sind hier nicht wie beim OP genannt).
--
Klaus "Perry" Pago
"Bruno Sutter" <bruno....@aktiv.ch> schrieb im Newsbeitrag
news:%23nUlY9P...@TK2MSFTNGP04.phx.gbl...
> Habe dies ausprobiert. Funktioniert nicht so richtig, da zwischen den
> Spalten D und E andere Zahlen stehen.
dann eben auf die harte Tour :)
die kleinste Zahl:
=KKLEINSTE(WENN(WENN({1.0.0.0.0.0.0.1;1.0.0.0.0.0.0.1;1.0.0.0.0.0.0.1;1.0.0.0.0.0.0.1;1.0.0.0.0.0.0.1;1.0.0.0.0.0.0.1;1.0.0.0.0.0.0.1;1.0.0.0.0.0.0.1;1.0.0.0.0.0.0.1;1.0.0.0.0.0.0.1;1.0.0.0.0.0.0.0;1.0.0.0.0.0.0.0;1.0.0.0.0.0.0.0}=1;D7:K19;0)="S";WENN({1.0.0.0.0.0.0.1;1.0.0.0.0.0.0.1;1.0.0.0.0.0.0.1;1.0.0.0.0.0.0.1;1.0.0.0.0.0.0.1;1.0.0.0.0.0.0.1;1.0.0.0.0.0.0.1;1.0.0.0.0.0.0.1;1.0.0.0.0.0.0.1;1.0.0.0.0.0.0.1;1.0.0.0.0.0.0.0;1.0.0.0.0.0.0.0;1.0.0.0.0.0.0.0}=1;E7:L19;0));1)
die zweitkleinste Zahl:
=KKLEINSTE(WENN(WENN({1.0.0.0.0.0.0.1;1.0.0.0.0.0.0.1;1.0.0.0.0.0.0.1;1.0.0.0.0.0.0.1;1.0.0.0.0.0.0.1;1.0.0.0.0.0.0.1;1.0.0.0.0.0.0.1;1.0.0.0.0.0.0.1;1.0.0.0.0.0.0.1;1.0.0.0.0.0.0.1;1.0.0.0.0.0.0.0;1.0.0.0.0.0.0.0;1.0.0.0.0.0.0.0}=1;D7:K19;0)="S";WENN({1.0.0.0.0.0.0.1;1.0.0.0.0.0.0.1;1.0.0.0.0.0.0.1;1.0.0.0.0.0.0.1;1.0.0.0.0.0.0.1;1.0.0.0.0.0.0.1;1.0.0.0.0.0.0.1;1.0.0.0.0.0.0.1;1.0.0.0.0.0.0.1;1.0.0.0.0.0.0.1;1.0.0.0.0.0.0.0;1.0.0.0.0.0.0.0;1.0.0.0.0.0.0.0}=1;E7:L19;0));2)
beide Formeln als Matrixformeln eingeben.
(mit xl2007 getestet)
Gru�
Klaus