Microsoft Excel

Herbers Excel/VBA-Archiv

[vba] eigene Formel aus konkretem Beispiel


Betrifft: [vba] eigene Formel aus konkretem Beispiel von: Jaco
Geschrieben am: 24.03.2018 01:18:23

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 : )

      

    Betrifft: AW: [vba] eigene Formel aus konkretem Beispiel von: Robert Werner
    Geschrieben am: 24.03.2018 09:21:22

    Hallo Jaco,

    dafür brauchst Du keine eigen VBA-Funktion. Folgende Formel in die Zelle D17 eintragen und nach rechts ziehen:
    =ZÄHLENWENNS(D6:D15;"x";$B$6:$B$15;"<>""")+ZÄHLENWENNS(D6:D15;"";$B$6:$B$15;"*")

    Die Formel zählt alle Zellen der entsprechenden Spalte (Zeile 6 bis 15), in denen ein "x" steht oder die leer sind und ein Name in Spalte B vorhanden ist. Nach Farben kann man in einer Excel-Datei meines Wissens nach nicht suchen/zählen. Aber wie ich Deiner Beispieldatei entnehme, trägst Du da ja sowieso irgendwas ein (s. Legende).
    Sollten mehr Mitarbeiter vorhanden sein, brachst Du nur den Zellbereich in der Formel anzupassen, also z. B. statt D6:D15 und $B$6:$B$15 schreibst Du D6:D100 und $B$6:$B$100.

    Gruß
    Robert


      

    Betrifft: kleine Korrektur von: Robert Werner
    Geschrieben am: 24.03.2018 09:23:54

    nimm diese Formel:
    =ZÄHLENWENNS(D6:D15;"x";$B$6:$B$15;"*")+ZÄHLENWENNS(D6:D15;"";$B$6:$B$15;"*")

    Gruß
    Robert


      

    Betrifft: AW: kleine Korrektur von: Jaco
    Geschrieben am: 24.03.2018 20:02:23

    Danke Robert

    Mir fehlt leider einfach der regelmäßige Umgang mit Excel.
    Die Formel scheint schlüssig. Werde sie das nächste Mal auf der Arbeit ausprobieren.
    In OpenOffice musste ich allerdings "<>" anstelle von "*" setzen und "" scheint gar nicht das Gewünschte zu bewirken. Aber bevor ich mir da jetzt den Wolf suche, werde ich es gleich in Excel versuchen.

    Die 8 in der Legende war wie gesagt nur ein Platzhalter.
    Normalerweise wäre das Kästchen leer und nur gelb oder schwarz gefüllt.
    Das Kann ich aber ja dann über einen Buchstaben in der selben Farbe lösen.
    Ist unsichtbar und fällt mit rein in die Formel.

    Zum Glück konnte ich die Kollegen, die sich noch weniger mit Excel auskennen, schon davon überzeugen nur die Legende zu verwenden oder bereits existierende Einträge zu kopieren.
    Da macht keiner mehr die Kästchen per Formatierung farbig.

    Das "*" ist in Excel also ein Platzhalter für einen beliebigen Eintrag außer leer?


      

    Betrifft: benutzerdefinierte Funktion Farben+Werte zählen von: fcs
    Geschrieben am: 24.03.2018 11:05:46

    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


      

    Betrifft: AW: benutzerdefinierte Funktion Farben+Werte zählen von: Jaco
    Geschrieben am: 24.03.2018 21:13:12

    Danke Franz für dieses vba Beispiel.

    Ja die (sichtbare) Formatierung und das Aussehen der Tabelle ist aufgrund des Nutzerstamms leider nicht verhandelbar : )

    In der bisher verwendeten Formel habe ich mich wegen der inhaltslosen farbigen Zellen eines Tricks bedient.
    Diese sind so formatiert, dass negative Zahlen rot angezeigt werden, alle anderen Zellen sind "normal" also negative Zahlen= schwarz formatiert.
    Das Ändern dieser Formatierung kann ich über eine normale Formel abfragen und wird auch aktualisiert wenn ich etwas ändere.
    Leider weiß ich diese Formel gerade nicht aus dem Stehgreif und Open Office scheint sie nicht zu haben.
    Wenn man das auch über vba abfragen könnte, wäre ich also noch einen Schritt weiter.

    Würde mir das so vorstellen:

    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
    

    Gibt es also einen vba Befehl für diese Art der Formatierung und wäre das so sinnvoll?


      

    Betrifft: AW: benutzerdefinierte Funktion Farben+Werte zählen von: fcs
    Geschrieben am: 25.03.2018 20:43:27

    Hallo Thomas,

    die Funktion in Excel heißt ZELLE("Format";Zelladresse)
    Unter VBA kann man das Zahlenformat als Zelleigenschaft abfragen/setzen, unter anderem auch die Farbinformation.

    Die VBA-Funktion muss dann so aussehen:

    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
    

    Das Volatile würde ich drin lassen, wegen der Verwendug des Formats.
    Es reduziert die Performance in einer Datei nur wenn sehr viele Formeln enthalten sind.

    Wenn Abwesenheitstage per kopieren der Legenden-Zellen markiert werden, dann kannst du das Volatile auch rausschmeißen, denn das Einfügen von Zellen löst das Change-Ereignis ebenso aus wie das manuelle Ändern des Zellinhalts.

    Gruß
    Franz


      

    Betrifft: AW: benutzerdefinierte Funktion Farben+Werte zählen von: Jaco
    Geschrieben am: 25.03.2018 22:52:28

    Wider vielen Dank.

    Werde das so ausprobieren.


    Beiträge aus dem Excel-Forum zum Thema "[vba] eigene Formel aus konkretem Beispiel"