Folgendes Problem:
Ich habe für jeden Monat eine Anwesenheitsliste nach folgendem Schema:
anwesend x 2 0 0 Urlaub u 2 0 0
Überstunden g 1 3 3
krank k 0 0 0
frei f 0 2 2
Sa So Mo
Schicht Skill Team Name 1 2 3
spät 1 FCB Maier x f f
spät 1 FCB Müller x f f
früh 2 FCB Lerby u g g
früh 2 FCB Ballack u g g
früh 3 FCB Matth. g g g
Der Bereich oben zählt zusammen, wie viele Mitarbeiter pro Tag anwesend sind, krank sind, Urlaub haben, etx. Konkret werden einfach die entsprechenden Kürzel (x= anwesend, k= krank, u = urlaub,...) zusammengezählt. (Leider stimmt hier die Formatierung nicht. Die Wochentage stehen normal über den Datumswerten (1,2,3...), usw.)
Die Mitarbeiter arbeiten in Früh- und Spätschicht und haben verschiedene Skills. Die Kürzel links neben den Namen kennzeichnen die Schicht und den Skill.
Das Zusammenzählen ließe sich prime mit z. B. "=ZÄHLENWENN f20:f50;"u")" erledigen.
Allerdings möchte ich, dass die Zählung in Abhängigkeit eines Autfilters gemacht wird. Zum Beispiel möchte ich nur die Spätschicht betrachten. Also filtere ich nach Spätschicht.
Die ZÄHLENWENN-Funktion versagt hier, weil sie den Autofilter nicht berücksichtigt.
Mit der TEILERGEBNIS-Funktion wäre es möglich, den Autofilter zu berücksichtigen. Aber TEILERGEBNIS zählt einfach stur alle Einträge im angegebenen Bereich und unterscheidet nicht nach u, k, x, etc.
Eigentlich bräuchte ich eine Kombination aus ZÄHLENWENN und TEILERGEBNIS.
BORIS hat mir diesbezüglich schon weitergeholfen und folgende Formel entwickelt:
=SUMMENPRODUKT(TEILERGEBNIS(3;INDIREKT("F"&ZEILE($14:$60)))*(F$14:F$60="u"))
Was macht die Formel? Hier SINNGEMÄSS die Erklärung von Boris:
"Vereinfacht gesagt, werden die sichtbaren Zeilen mit den sichtbaren Zeilen in Spalte F verglichen. Steht in Spalte F ein "u", dann ergibt (F20:F60="u") für die jeweilige Zeile den Wert WAHR - und WAHR bedeutet - auch vereinfacht gesagt - 1.
Wenn also die mit Teilergebnis ermittelte Zeile (in Kombination mit INDIREKT) sichtbar ist UND in Spalte F ein "u" steht, dann hat man 2 WAHR-Werte, und das ist 1*1, also 1.
Und die Summe der Einsen (daher SUMMENPRODUKT) gibt dir die Anzahl der "u" aus der gefilterten Liste wieder."
So weit so schön. Es funktioniert auch perfekt. Ich habe aber noch mehr vor:
BEISPIELSWEISE soll, wenn eine bestimmte Anzahl an Urlauben überschritten wird, die Zelle rot markiert werden (oder ähnliches).
Die Abhängidkeiten sind recht komplex. Bedingte Formatierung bringt mich NICHT weiter. Fakt ist, ICH BRAUCHE DEFINITIV VBA DAZU.
Der erste Schritt wäre, die Formel "=SUMMENPRODUKT(TEILERGEBNIS(3;INDIREKT("F"&ZEILE($14:$60)))*(F$14:F$60="u"))" in VBA zu übersetzen. Und soweit bin ich schon:
Ich übergebe per Funktionsaufruf der selbstgeschriebenen Funktion Urlaub ein Range-Objekt (z. B. =urlaub(f20:f60)). Die Funktion selbst sieht in etwa so aus:
Function urlaub(r As Range)
urlaub = Application.WorksheetFunction.SumProduct(Subtotal(3, INDIRECT("F" & r.Row) * r = "u"))
End Function
Leider meckert Excel, weil es INDIRECT nicht kennt. Es gibt leider keine VBA-Ensprechung für INDIREKT(Verweis;A1). (Obwohl der Makrorecorder Indirekt in INDIRECT übersetzt hat)
Ein Lösungsweg wäre, eine eigen Funktion INDIRECT zu schreiben. Aber wie geht das? Bestimmt gibt es auch noch andere Möglichkeiten.
Es wäre schon, wenn mir jemand auf die Sprünge helfen könnte!!!
Viele Grüße,
Max