[vba] eigene Formel aus konkretem Beispiel

Hallo zusammen,
Wir haben auf der Arbeit eine recht einfache Excel- Liste zum Pflegen der Mittarbeiteranwesenheit.
Auch wenn ich mich in Excel etwas auskenne, so fehlt mir die Routine darin, da es auch nicht zu meinem Tätigkeitenfeld gehört.
Meine Herangehensweise ist also vermutlich häufig unnötig kompliziert.
Vorab: Ich habe eine Testdatei als Anhang angefügt. Hier Daheim habe ich leider nur open office zur Verfügung.
https://www.herber.de/bbs/user/120645.xls
In dieser Anwesenheit befinden sich also in Spalte "B" die Namen der Mitarbeiter untereinander, ab Spalte "D" dann fortlaufend die Arbeitstage nebeneinander.

An jedem Tag kann ein Mitarbeiter nun anwesend sein (mit einem "X" markiert) oder wenn der Tag noch in der Zukunft liegt geplant anwesend sein. (noch keine Eintragung, die Zelle ist leer)

Oder er ist abwesend. (wird mit einem anderen Buchstaben angezeigt oder durch das farbliche Unterlegen der Zelle)

Bisher habe ich unter der ganzen Tabelle eine weitere Spalte eingefügt, die mir die Anzahl der anwesenden Mitarbeiter berechnet.
Unter dem jeweiligen Tag addiere ich also die Anzahl Zellen, in dem ein Mitarbeiter anwesend ist.
Das sieht dann so aus: (Die Tabelle beginnt in Zeile 6.)


=SUMME(
WENN(UND(ODER(D6="";D6=" ";D6="x");NICHT(ODER(D6=8;$B6="";$B6=" ")));1);
WENN(UND(ODER(D7="";D7=" ";D7="x");NICHT(ODER(D7=8;$B7="";$B7=" ")));1);
WENN(UND(ODER(D8="";D8=" ";D8="x");NICHT(ODER(D8=8;$B8="";$B8=" ")));1);
WENN(UND(ODER(D9="";D9=" ";D9="x");NICHT(ODER(D9=8;$B9="";$B9=" ")));1)
)

Wenn also ein Tag keine Eintragung oder ein X hat, ist der Mitarbeiter anwesend
solange er existiert (also in Spalte B ein Name steht)
und das Feld nicht mit einer Farbe hinterlegt ist (hier mit "=8" als Platzhalter).
(In Excel habe ich die farbigen Felder mit "Zahl=rot wenn negativ" formatiert, was ich in einer Funktion abfragen kann.
Von open office aus kann ich diesen Befehl leider nicht mehr nachvollziehen.)
Wie man sich vorstellen kann, gibt das Ganze bei dutzenden Mitarbeitern ein heilloses Durcheinander sobald einer ausscheidet, Zeilen gelöscht oder neue eingefügt werden.
Deswegen möchte ich das Ganze irgendwie als eigene Funktion über vba festhalten.
Und hier bräuchte ich eure Hilfe.
Ich stelle mir das ungefähr so vor:
Für die Funktion sollen nur die einzelnen Anwesenheitsfelder eines Tags (Schnittmenge aus allen Namen und einem einzigen Tag) markiert werden müssen.
Die Funktion will ich dann durch Ziehen unter alle Tage erweitern können.
(folgende Syntax ist natürlich Schwachsinn)


function Anwesenheit (Tagbereich as range)
c = 0
for each Tagbereich
dim Name as range
range(Name).value = ("B","Zeile von each Tagbereich")      '(in "B" ist der Name)
if Tagbereich = "" or Tagbereich = " " or Tagbereich = "x" and
not Tagbereich = Farbcode1 or Tagbereich = Farbcode2 or Name = "" or Name = " "
then c = c + 1
end if
end each        '(oder so)
Anwesenheit = c
end function

Ich hoffe ich konnte mich halbwegs verständlich ausdrücken und dass nicht noch jemand beim Lesen
meiner Funktionsvorstellung Haare verloren oder sich ihm die Zehennägel hochgerollt haben.
Ich denke anhand der Beispieldatei wird die ganze Sache auch klarer.
Ich wäre für Hilfe dankbar : )

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

Hallo Jaco,
ist halt Schade, dass ihr auch farbige Zellen ohne Inhalt zur Kennzeichnung verwendet.
Sonst könnte man mit Funktionen wie SUMMENPRODUKT arbeiten.
Generell muss du wissen:
Die Änderung des Zellformats löst keine Neuberechnung von Formeln aus.
Auch indirekt innerhalb der benutzdefinierten Funktion ermittelte Eingabe-Zellen lösen bei Änderung des Zellinhalts keine Neuberechnung der Formel aus.
Deshalb sollte man alle Eingabezellen/-Zellbereiche die für eine Formel relevant sind auch als Parameter angeben.
In benutzerdefinierten Funktionen kann man die Volatile-Eigenschaft für die Funktion auf True setzen.
Dann werden alle Zellen mit dieser Funktion neu berechnet sobald eine beliebige Zelle neu berechnet wird.
Ein anderer Trick: Man baut in die Formel eine Funktion ein, die von Haus aus volatil ist, aber das Ergebnis nicht verändert. z.B
+ HEUTE()*0 bei Zahlen
& TEXT(HEUTE();"") bei Texten
Hier deine Datei mit benutzerdefinierten Funktionen und entsprechenden Formeln.
https://www.herber.de/bbs/user/120650.xls
Gruß
Franz

Public Function Anwesenheit(Namen as Range; Tagbereich As Range) ' die Namen sind markiert, die Funktion wird also aktualisiert wenn sich ein Name ändert ' - ich muss sie aber nicht unbedingt in der Formel verwenden? ' Application.Volatile True - brauche ich dann ja nicht mehr? ' Formelbeispiel: =Anwesenheit($B$6:$B$16;D6:D16) Dim c As Integer Dim Zelle As Range c = 0 For Each Zelle In Tagbereich.Cells 'Name in Spalte B prüfen If Trim(Zelle.Parent.Cells(Zelle.Row, 2)) "" Then 'Inhalt prüfen - Inhalt = "x" oder leer und Zelle nicht mit negativeZahl=rot formatiert If LCase(Zelle.Value) = "x" Or Trim(Zelle.Value) = "" 'Kenne den Befehl nicht - negative Zahlen in Zelle als automatisch rot formatiert And (Zelle.negativevalue.red = False) Then c = c + 1 End If End If Next Anwesenheit = c End Function

Public Function Anwesenheit(Namen as Range, Tagbereich As Range) ' Formelbeispiel: =Anwesenheit($B6:$B16; D6:D16) Application.Volatile True Dim c As Integer Dim Zelle As Range c = 0 For Each Zelle In Tagbereich.Cells 'Name in Spalte B prüfen If Trim(Zelle.Parent.Cells(Zelle.Row, 2)) "" Then 'Zellinhalt prüfen - Inhalt = "x" oder Zelle ist leer und Zahlenformat enthält nicht _ Rot If LCase(Zelle.Value) = "x" _ Or (Trim(Zelle.Value) = "" _ And (InStr(Zelle.NumberFormat, "[Red]")) = 0) Then c = c + 1 End If End If Next Anwesenheit = c End Function

Beliebteste Forumthreads (12 Monate)

[vba] eigene Formel aus konkretem Beispiel

7 Beiträge zum Forumthread Beiträge zu diesem Forumthread

VBA Beispiele zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

7
Beiträge zum Forumthread

Beiträge zu diesem Forumthread