Office: (Office 2016) Anzahl Blocks freier Tage

Hallo zusammen,

bei der MA-Planung werden freie Tage durch ein"X" gekenzeichnet. Sie können einzeln oder in Blocks von zwei oder mehr Tagen stehen.

Nun muss ich die Anzahl ermitteln, wo mindestens zwei Tage zusammenhängend eingetragen sind. Das heißt, auch wenn drei oder mehr X-Tage zusammen stehen, darf dieses nur als ein Block gerechnet werden.

Bei einem Versuch mit SUMMENPRODUKT gelingt mir nur ein Teilerfolg. Stehen nur zwei X-Tage zusammen, erhalte ich das richtige Ergebnis. Stehen aber mehrere X-Tage zusammen (z.B. drei), dann erhalte ich als Ergebnis zwei.

Ich würde mich freuen, wenn mir da jemand auf die Sprünge helfen könnte!

 

Hallo,

wenn du deine im Arbeitsblatt in AY5:AY6 enthaltenen Formeln
=SUMMENPRODUKT((LINKS(K5:AM5;1)="X")*(LINKS(L5:AN5;1)="X")*(LINKS(M5:AO5;1)<>" "))
=SUMMENPRODUKT((LINKS(K6:AM6;1)="X")*(LINKS(L6:AN6;1)="X")*(LINKS(M6:AO6;1)<>" "))
geringfügig auf
=SUMMENPRODUKT((LINKS(K5:AM5;1)="X")*(LINKS(L5:AN5;1)="X")*(LINKS(M5:AO5;1)<>"X"))
=SUMMENPRODUKT((LINKS(K6:AM6;1)="X")*(LINKS(L6:AN6;1)="X")*(LINKS(M6:AO6;1)<>"X"))
änderst, dann funktionieren sie.
Der Vergleich im 3. LINKS-Ausdruck muss auf ungleich "X" erfolgen (Der Vergleich im 3. LINKS-Ausdruck auf ungleich " " bewirkte, dass natürlich auch "X" ungleich " " war und somit mitgezählt wurde und somit ein falsches Resultat ergab).

 

Hi Anton,

für Deine Antwort erstmal Danke!

Die Variante mit "<>X" hilft tatsächlich weiter, wenn... ja wenn da jetzt nicht das Monatsende der Sache ein Problem bereiten würde.

Wenn am Ende des Monats, was gleich Ende des Arrraybereichs ist, auch ein "X" steht, wird wieder falsch gezählt. Füge ich nach dem 31.ten
eine leere Spalte dazu und erweitere die Bereiche klappts wieder. Leider kann ich dort aber keine Spalte einfügen, da ich mit ähnlicher Formel die freien Wochenenden durchzähle. Wenn dann der Samstag auf den Letzten des Monats und der Sonntag auf den Ersten des Monats fällt würde diese Berechnung nicht mehr stimmig sein.

 

Hallo,

ich dachte, es würde angesichts der verwendeten Formel eh klar sein, dass eine zusätzliche Spalte benötigt wird.

Ich habe mich nach Alternativformeln umgesehen und eine solche gefunden in
http://www.excelformeln.de/formeln.html?welcher=335
Ich habe dir in den Zellen L18:L19 eine an deine Verhältnisse angepasste Version eingefügt - doch diese Formel benötigt ebenfalls am Monatsende eine Leerspalte, um erwartungsgemäß zu funktionieren. Außerdem ist sie um einiges länger.

Wirklich Abhilfe schafft hier nur eine benutzerdefinierte VBA-Funktion. Ich habe sie dir in die Excel-Datei eingefügt.
Wie sie im Arbeitsblatt einzusetzen ist, habe ich dir in den Zellen L24:L25 bzw. T24:T25 gezeigt.

Sie ist ganz einfach anzuwenden und sie kann 1 bis 3 Parameter besitzen:
Der 1. Parameter gibt den Zellbereich an, in dem nach Zellgruppen gesucht werden soll, zB. K5:AO5 bzw. K6:AO6
Der 2. Parameter ist optional, er ist nur dann anzugeben, wenn nach einem anderen Zeichen als dem "X" gesucht werden soll.
Der 3. Parameter ist ebenfalls optional und nur anzugeben, wenn mehr als 2 Zeichen (zB. "X") zu einer Gruppe gehören müssen, damit sie als solche gezählt wird.

Nachfolgend der Makro-Sourcecode:

Code:

Option Explicit

Public Function GruppenZählen(Bereich As Range, Optional Ch$ = "X", Optional MinCh& = 2) As Long
  Dim AnzGruppen As Long
  Dim AnzInGruppe As Long
  Dim Zelle As Range
  
  AnzGruppen = 0: AnzInGruppe = 0
  For Each Zelle In Bereich.Cells
    If Zelle.Value = Ch$ Then
      AnzInGruppe = AnzInGruppe + 1
    Else
      If AnzInGruppe >= MinCh& Then AnzGruppen = AnzGruppen + 1
      AnzInGruppe = 0
    End If
  Next Zelle
  If AnzInGruppe >= MinCh& Then AnzGruppen = AnzGruppen + 1
  
  GruppenZählen = AnzGruppen
  
End Function

Damit das VBA-Makro funktioniert, ist gegebenenfalls die Makroausführung zu aktivieren (entweder nach dem Öffnen der Excel-Datei oder aber in den Excel-Optionen).

 

Hi Anton,

Deine VBA Lösung ist und klappt hervorragend.

Vielen Dank für Deine Hilfe und schöne Grüße