Summe nach Kriterium

[schauan]

Hallo Moppel,

mein Beitrag war kein Hinweis an Dich sondern an die Antworter. Für Dich könntest Du jedoch daraus entnehmen, wie man den Rechenweg bei der Fragestellung hätte beschreiben können, damit man es gleich versteht. Ich habe auch erst die Tore der letzten 3 Heimspiele zusammengezählt und als das nicht stimmte, die Tore der jeweiligen Mannschaft bei den letzten 3 Heim- und Gastspielen ...

Per VBA könnte man das so lösen. Kannst Du mit Makros umgehen, ist VBA erlaubt?

Code:

Public Function Letzte3(rngZelle As Range) As Integer
'als rngzelle in der Formelzeile die Zelle in Spalte B waehlen
'Berechnung bei jeder Aenderung im Blatt
Application.Volatile
'Gehe bei Fehler zu Ende
On Error GoTo ende
'Variablendeklarationen
'Integer -> Bei mehr als 35768 Spiel-Zeilen Long nehmen!
Dim iRow As Integer, iPlay As Integer, iTor As Integer
'String
Dim strTeam As String
'Vorletzte Zeile ermitteln
iRow = rngZelle.Row - 1
'Teameintrag Heimmannschaft in Spalte B ermitteln
strTeam = Cells(rngZelle.Row, 2).Value
'Schleife solange nicht erstes Spiel und 3 Spiele erreicht sind
'(iplay beginnt ebi 0 - erstes Spiel)
Do While iRow > 1 And iPlay <= 2
 'Wenn der Eintrag in Spalte B das Heimteam ist, dann
 If Cells(iRow, 2) = strTeam Then
   'Spielanzahl hochsetzen
   iPlay = iPlay + 1
   'Toranzahl aus Spalte B uebernehmen
   iTor = iTor + Cells(iRow, 4)
 'Oder Wenn der Eintrag in Spalte C das Heimteam ist, dann
 ElseIf Cells(iRow, 3) = strTeam Then
   'Spielanzahl hochsetzen
   iPlay = iPlay + 1
   'Toranzahl aus Spalte C uebernehmen
   iTor = iTor + Cells(iRow, 5)
 'Ende Wenn der Eintrag in Spalte B das Heimteam ist, dann
 End If
 'Zaeilenzaehler Countdown
 iRow = iRow - 1
Loop
'Ergebnis bilden, Anzahl Tore oder bei weniger als 3 Spielen 88
If iPlay < 3 Then Letzte3 = 88 Else Letzte3 = iTor
'Sprungmarke "ende"
ende:
End Function

[Luffy]

Hey Schauan, kann man das ohne VBA auch lösen? (Nur aus Interesse)

[schauan]

Hallo Luffy,

das ist mir heute zu kompliziert, hab etwas anderen Stress. Auf die Schnelle fällt mir da jetzt auch nichts ein und vielleicht geht das auch nicht Das interessante daran ist, über zwei "unsortierte" Spalten die letzten n Einträge bedingt zu finden. Na ja, und dann muss hin und her addiert werden ...

Eventuell bekommen unsere Formelspezis, z.B. Jockel oder Edgar, da was gebacken.

[Moppel]

Per VBA könnte man das so lösen. Kannst Du mit Makros umgehen, ist VBA erlaubt?

Hallo Schauan,
GANZ GROßER SPORT !
.. ich habe Null Ahnung von VBA aber ein Modul anlegen und eine Funktion hinein kopieren / aufzurufen bekomme ich schon gebacken.

Ich finde es stark, wie du den Code kommentiert hast und deshalb habe dann auch gleich die Funktion auf das ermitteln der Tore vom GastTeam erstellt.

Code:

Public Function Letzte3GT(rngZelle As Range) As Integer
'als rngzelle in der Formelzeile die Zelle in Spalte B waehlen
'Berechnung bei jeder Aenderung im Blatt
Application.Volatile
'Gehe bei Fehler zu Ende
On Error GoTo ende
'Variablendeklarationen
'Integer -> Bei mehr als 35768 Spiel-Zeilen Long nehmen!
Dim iRow As Integer, iPlay As Integer, iTor As Integer
'String
Dim strTeam As String
'Vorletzte Zeile ermitteln
iRow = rngZelle.Row - 1
'Teameintrag Gastmannschaft in Spalte C ermitteln
strTeam = Cells(rngZelle.Row, 3).Value
'Schleife solange nicht erstes Spiel und 3 Spiele erreicht sind
'(iplay beginnt ebi 0 - erstes Spiel)
Do While iRow > 1 And iPlay <= 2
'Wenn der Eintrag in Spalte C das Gastteam ist, dann
If Cells(iRow, 3) = strTeam Then
  'Spielanzahl hochsetzen
  iPlay = iPlay + 1
  'Toranzahl aus Spalte C uebernehmen
  iTor = iTor + Cells(iRow, 5)
'Oder Wenn der Eintrag in Spalte B das Gastteam ist, dann
ElseIf Cells(iRow, 2) = strTeam Then
  'Spielanzahl hochsetzen
  iPlay = iPlay + 1
  'Toranzahl aus Spalte C uebernehmen
  iTor = iTor + Cells(iRow, 4)
'Ende Wenn der Eintrag in Spalte C das Gastteam ist, dann
End If
'Zaeilenzaehler Countdown
iRow = iRow - 1
Loop
'Ergebnis bilden, Anzahl Tore oder bei weniger als 3 Spielen 88
If iPlay < 3 Then Letzte3GT = 88 Else Letzte3GT = iTor
'Sprungmarke "ende"
ende:
End Function

Ich möchte jetzt nicht unverschämt werden Schauan, - ich bin bestens bedient mit der Funktion,- aber könntest die Funktion noch ein wenig ändern/ergänzen?
Es werden ja die Treffer aus den letzten 3 vorherigen Spielen ermittelt.
Wenn ich jetzt aus den letzten 4 Spielen die Summe der Treffer erhalten möchte, müßte ich eine geänderte Funktion aufrufen (in der zB. das iPlay <= 2 verändert entsprechend eingetragen wird)
Würde sich dieses " <= 2" auf einen Wert im Tabellenblatt berufen, wäre das alles natürlich noch konfortabeler.
Geht da noch was bei Gelegenheit zu machen Schauan ?
Ansonsten, vielen Dank für deine tolle Arbeit!!

[schauan]

Hallo Moppel,

das geht z.B. recht einfach mit einer Erweiterung des Funktionsaufruf:

Public Function Letzte3(rngZelle As Range, iCntPlay as Integer) As Integer

In der Funktion nimmst Du dann bei der Schleife:

Do While iRow > 1 And iPlay < iCntPlay


(ungetestet)

[Moppel]

Hallo Schauan,
bin wohl mal wieder Panne.
Ich habe den Code wie beschrieben geändert (Funktionsname: "letzten3abfrage") und der Zelle "G1" den Namen "iCntPlay" gegeben.
Funktionert leider nicht. Habe ich da was mißverstanden?
anbei nochmal die excel-mappe mit fehlerfreier Funktion und fehlerhaften.

[Moppel]

Habe mal probiert:
Do While iRow > 1 And iPlay <= Range("G1")
als auch
Do While iRow > 1 And iPlay <= Cells(1, 7)
klappt aber auch nicht.

[schauan]

Hallöchen,
Den Code musst Du so ändern, wie ich es geschrieben habe. Beim Funktionsaufruf übergibst Du einen zweiten Parameter, das könnte die Zahl direkt sein oder die Zelle mit der Zahl.