AW: Ich schau erst am Mo wieder hier rein...
04.06.2016 18:27:44
Michael
Hi zusammen,
mein Kollege M(m) hat völlig Recht: lieber Biergarten oder Fischamend, am End lädt man sich mit der Tabelle Arbeit auf den Hals: sie läßt mehr Fragen offen als sie beantwortet.
a) welche Spalte enthält eigentlich die zu behandelnden Werte?
b) soll die Formel überhaupt rechnen, wenn das untere Kriterium fehlt (z.B. HL in B32: es kommt kein HL mehr in der Tabelle vor), und wenn ja, wie?
Insgesamt erscheint mir die Sache so kompliziert zu sein, daß es wahrscheinlich geschickter ist, sie in VBA zu lösen: entweder, indem man die *Formeln* mit VBA schreibt (was aber den Anwender in Sicherheit wiegen könnte, daß die Ergebnisse passen, obwohl das nicht der Fall ist, je nach dem, wie und wo er neue Zeilen eingibt) oder gleich *Werte*.
Ich hab's mal schnell ziemlich allg. programmiert:
Option Explicit
Const abZeile = 4 'ohne Überschriften und "D"
Const wSp = "C" 'Spalte mit Werten kannst Du hier aussuchen
Public wennA, wertA, ausgA
Function werteSuchen(ByVal wenn$, ByVal dann$, ByVal OP$, ByVal Modus As Boolean) As Boolean
Dim von&, i&, z&
Dim wSum&
z = 0
wSum = 0
werteSuchen = True
For i = abZeile To UBound(wennA)
If von > 0 And wennA(i, 1) = dann Then
z = z + 1
wSum = wSum + wertA(i, 1)
End If
' MsgBox UBound(ausgA) & " : " & UBound(ausgA, 2)
If wennA(i, 1) = wenn Then
If von > 0 Then
If OP = "Sum" Then
ausgA(von, 1) = wSum
Else
If z > 0 Then ausgA(von, 1) = wSum / z Else werteSuchen = False
End If
wSum = 0
z = 0
End If
von = i
End If
Next
If Modus Then
If OP = "Sum" Then
ausgA(von, 1) = wSum
Else
If z > 0 Then ausgA(von, 1) = wSum / z Else werteSuchen = False
End If
Else
ausgA(von, 1) = "n.v."
End If
End Function
Sub werteSchreiben()
Dim maxZ&, Funk&, i&
Dim ok As Boolean
Dim wie As Variant
wie = Range("P1:S3") ' hier nach Bedarf erweitern bis z.B. S4, falls nötig
wie(1, 1) = "HL": wie(1, 2) = "AP": wie(1, 3) = "MW": wie(1, 4) = False
wie(2, 1) = "AP": wie(2, 2) = "A": wie(2, 3) = "Sum": wie(2, 4) = True
wie(3, 1) = "A": wie(3, 2) = "TA": wie(3, 3) = "Sum": wie(3, 4) = True
maxZ = Range("B" & Rows.Count).End(xlUp).Row
wennA = Range("B1:B" & maxZ)
wertA = Range(wSp & 1 & ":" & wSp & maxZ)
ausgA = Range("I1:I" & maxZ)
For i = 4 To maxZ
If wennA(i, 1) "TA" Then ausgA(i, 1) = Empty
Next
For Funk = 1 To 3
ok = werteSuchen(wie(Funk, 1), wie(Funk, 2), wie(Funk, 3), wie(Funk, 4))
MsgBox "Test mit: " & wie(Funk, 1) & " / " & wie(Funk, 2) & " / " & _
wie(Funk, 3) & " / " & wie(Funk, 4) & ": " & ok
Next
Range("I1:I" & maxZ) = ausgA
End Sub
Die Datei: https://www.herber.de/bbs/user/105995.xlsm
Deine Sachen änderst Du ggf. im Array "wie": mit "Sum" wird summiert (war einfacher zu Testen), sonst der Mittelwert gebildet, außer wenn z=0, das geht natürlich nicht.
Mit Modus=true wird ein Ergebnis geschrieben, auch wenn "Wenn" in der Tabelle nicht mehr vorkommt, mit Modus=false erscheint "n.v." in der Tabelle.
Übrigens habe ich Deine Ausgabespalte von % auf Standard gesetzt, nur zur Info.
Schöne Grüße,
Michael
