Excel 2013: ZÄHLENWENNS mit verbundenen Zellen

[Ralf Brinkmann]

Hallo zusammen!

Ich habe auf einem Monatsblatt für die Urlaubseintragung lauter
verbundene Zellen (immer zwei nebeneinander) für die Datumstage und
darunter für die einzelnen Beschäftigten (aus kosmetischen Gründen).
Oben unter den Datumszahlen teilt sich jeder Tag aber nochmal in Tages-
und Nachtschicht.

Gedacht ist es so, dass jeder Mitarbeiter an jedem Tag seines
Urlaubsblocks ein "U" einträgt, egal, ob er an dem Tag Tagesschicht,
Nachtschicht oder frei hat, denn wenn er fehlt, dann ja den ganzen Tag
und die ganze Nacht über den gesamten Zeitraum.

Nun würde ich gerne mit Hilfe der Funktion ZÄHLENWENNS rechts pro
Beschäftigtem die tatsächliche Anzahl seiner Urlaubstage zusammenzählen.
Die Formel soll also zählen, wenn in der Mitarbeiterzeile (lauter
verbundene Zweierzellen) ein "U" steht und ober in der Schichtfolge
(normale Einzelzellen) bei den Tages- oder Nachtschichten der Buchstabe
seiner Gruppe, also zum Beispiel "A". Geht das irgendwie?

Bei meinen Versuchen zählt die Formel oben immer nur jede zweite Zelle,
also die jeweils linke pro Tag. Kriege ich das Ding irgendwie dazu, auch
die rechten Zellen zu berücksichtigen?

Datum [ 01 ] [ 02 ] [ 03 ]
Schicht [A][B] [C][A] [D][E]

Name [ ] [ ] [ ] [ZÄHLENWENNS ...]

Gruß, Ralf

[Claus Busch]

Hallo Ralf,

Post by Ralf Brinkmann
Ich habe auf einem Monatsblatt für die Urlaubseintragung lauter
verbundene Zellen (immer zwei nebeneinander) für die Datumstage und
darunter für die einzelnen Beschäftigten (aus kosmetischen Gründen).
Oben unter den Datumszahlen teilt sich jeder Tag aber nochmal in Tages-
und Nachtschicht.

ich weiß nicht, ob ich deinen Tabellenaufbau richtig verstanden habe.
Probiere es mal mit folgender Funktion und rufe diese im Blatt auf mit
z.B.: =ZählenSpez(B3:F3;"A";"U")
Sollte das nicht funktionieren, stelle mal eine Demomappe mit einem
kleinen Ausschnitt dieser Tabelle zur Verfügung.

Function ZählenSpez(Bereich As Range, str1 As String, _
str2 As String) As Integer
Dim rngC As Range, ar As Range

For Each rngC In Bereich
If rngC = str2 Then
For Each ar In rngC.MergeArea
If ar.Offset(-1, 0) = str1 Then
ZählenSpez = ZählenSpez + 1
End If
Next
End If
Next
End Function


Mit freundlichen Grüßen
Claus

[Ralf Brinkmann]

Hallo Claus!

Post by Claus Busch
ich weiß nicht, ob ich deinen Tabellenaufbau richtig verstanden habe.
Probiere es mal mit folgender Funktion und rufe diese im Blatt auf mit
z.B.: =ZählenSpez(B3:F3;"A";"U")

Ich glaube, da fehlt der zweite Bereich.

Post by Claus Busch
Function ZählenSpez(Bereich As Range, str1 As String, _
str2 As String) As Integer
Dim rngC As Range, ar As Range
For Each rngC In Bereich
If rngC = str2 Then
For Each ar In rngC.MergeArea
If ar.Offset(-1, 0) = str1 Then
ZählenSpez = ZählenSpez + 1
End If
Next
End If
Next
End Function

Nochmal kurz zur Verdeutlichung:

Oben stehen von links nach rechts die Datumstage von 1 bis 31 in
Doppelzellen, darunter in Einzelzellen die Schichten, da jeder Tag in
einen Tagesschicht und eine Nachtschicht unterteilt ist.

Links in Spalte A stehen dann untereinander die Namen der Beschäftigten.
In jeder Namenszeile können nun zu den Datumstagen Urlaubstage
eingetragen werden ("U"). Am Ende der Zeile soll gezählt werden, an
wievielen Tagen in der Zeile ein "U" steht und gleichzeitig oben der
Schichtbuchstabe entweder im Tagesdienst oder im Nachtdienst dieses
Tages. Damit weiß man, wieviele reine Urlaubstage (oder -schichten)
jemand innerhalb eines Blocks von zum Beispiel 2 Wochen genommen hat.

Datum [ 01 ] [ 02 ] [ 03 ] [ .. ]
Schicht [A][B] [C][A] [D][E] [ .. ]

Name [ ] [ ] [ ] [ .. ] [Berechnung der Anzahl]
Name [ ] [ ] [ ] [ .. ] [Berechnung der Anzahl]
Name [ ] [ ] [ ] [ .. ] [Berechnung der Anzahl]
Name [ ] [ ] [ ] [ .. ] [Berechnung der Anzahl]

Die Funktion oder Formel müsste also in der Namenszeile nachsehen und
oben in der Schichtzeile.

Gruß, Ralf

[Claus Busch]

Hallo Ralf,

Post by Ralf Brinkmann
Die Funktion oder Formel müsste also in der Namenszeile nachsehen und
oben in der Schichtzeile.

das macht die Funktion doch. Du gibst als Bereich den Namensbereich an.
Ist dort ein U drin, schaut die Funktion in der Schichtzeile nach A und
zählt hoch.


Mit freundlichen Grüßen
Claus

[Claus Busch]

Hallo Ralf,

Post by Claus Busch
das macht die Funktion doch. Du gibst als Bereich den Namensbereich an.
Ist dort ein U drin, schaut die Funktion in der Schichtzeile nach A und
zählt hoch.

sorry, mein Fehler. Da die Schichtzeile ja konstant ist und du die
Formel herunterziehen möchtest, muss das Offset dynamisch sein.
Probiere es so:

Function ZählenSpez(Bereich As Range, str1 As String, _
str2 As String) As Integer
Dim rngC As Range, ar As Range
Dim myOffset As Integer

For Each rngC In Bereich
If rngC = str2 Then
myOffset = 2 - rngC.Row
For Each ar In rngC.MergeArea
If ar.Offset(myOffset, 0) = str1 Then
ZählenSpez = ZählenSpez + 1
End If
Next
End If
Next
End Function


Mit freundlichen Grüßen
Claus

[Ralf Brinkmann]

Hallo Claus!

Post by Claus Busch
Da die Schichtzeile ja konstant ist und du die
Formel herunterziehen möchtest, muss das Offset dynamisch sein.
Function ZählenSpez(Bereich As Range, str1 As String, _
str2 As String) As Integer
Dim rngC As Range, ar As Range
Dim myOffset As Integer
For Each rngC In Bereich
If rngC = str2 Then
myOffset = 2 - rngC.Row
For Each ar In rngC.MergeArea
If ar.Offset(myOffset, 0) = str1 Then
ZählenSpez = ZählenSpez + 1
End If
Next
End If
Next
End Function

So ganz verstehe ich es noch nicht. Ich habe die Funktion in ein neues
VBA-Modul eingefügt. Dann habe ich in dem Summenfeld am Ende der
Namneszeile die Formel =ZÄHLENSPEZ(C4:BL21;"A";"U") eingetragen. C4 ist
also oben links die erste Zelle mit der Tagesschicht des Tages 01 und
BL4 ist die äußerste rechte Zelle in der Zeile mit dem Namen, so dass
oben Tages- und Nachtschicht des Tages 31 mit eingeschlossen sind. War
das mit dem Herunterziehen so gemeint?

Jedenfalls kommt als Ergebnis nur 0 heraus, auch wenn bei 5
eingetragenen "U"s 2 Übereinstimmungen an Tages- oder Nachtschicht
gezählt werden müssten.

Auch wenn ich nur die Namenszeile (C21:BL21) eintrage, bleibt das
Ergebnis 0. Woher weiß die Funktion denn, wo sich die Schichtzeile
befindet?

Gruß, Ralf

[Claus Busch]

Hallo Ralf,

Post by Ralf Brinkmann
Auch wenn ich nur die Namenszeile (C21:BL21) eintrage, bleibt das
Ergebnis 0. Woher weiß die Funktion denn, wo sich die Schichtzeile
befindet?

ich bin davon ausgegangen, dass die Datumswerte in Zeile 1 stehen und
die Schichtzeile die Zeile 2 ist. Sollte das nicht der Fall sein, musst
du im Code myOffset anpassen.
In der Formel solltest du immer nur eine Zeile als Bereich angeben.


Mit freundlichen Grüßen
Claus

[Ralf Brinkmann]

Hallo Claus!

Post by Claus Busch
ich bin davon ausgegangen, dass die Datumswerte in Zeile 1 stehen und
die Schichtzeile die Zeile 2 ist.

Ach so. Nein, Datumswerte in Zeile 3, Schichtzeile Zeile 4. Oben drübder
stehen noch Jahr und Monat. :)

Post by Claus Busch
Sollte das nicht der Fall sein, musst
du im Code myOffset anpassen.

Ok, ich habe jetzt aus der 2 eine 4 gemacht. Das Ergebnis ist trotzdem
0.

Post by Claus Busch
In der Formel solltest du immer nur eine Zeile als Bereich angeben.

Habe ich jetzt gemacht: =ZählenSpez(C21:BL21;"A";"U")

Gruß, Ralf

[Ralf Brinkmann]

Hallo Claus!

Post by Ralf Brinkmann

Post by Claus Busch
Sollte das nicht der Fall sein, musst
du im Code myOffset anpassen.

Ok, ich habe jetzt aus der 2 eine 4 gemacht. Das Ergebnis ist trotzdem
0.

Ich habe gerade etwas gemerkt: Die Funktion unterscheidet zwischen Groß-
und Kleinschreibung. Mit großem "U" zählt es.

Gruß, Ralf

[Claus Busch]

Hallo Ralf,

Post by Ralf Brinkmann
Ok, ich habe jetzt aus der 2 eine 4 gemacht. Das Ergebnis ist trotzdem
0.

Post by Claus Busch
In der Formel solltest du immer nur eine Zeile als Bereich angeben.

Habe ich jetzt gemacht: =ZählenSpez(C21:BL21;"A";"U")

ohne deine Tabelle kann ich nicht feststellen wo der Fehler liegt. Bei
meiner nachgestellten Tabelle funktioniert es.


Mit freundlichen Grüßen
Claus

[Ralf Brinkmann]

Hallo Claus!

Post by Claus Busch

Post by Ralf Brinkmann
Habe ich jetzt gemacht: =ZählenSpez(C21:BL21;"A";"U")

ohne deine Tabelle kann ich nicht feststellen wo der Fehler liegt. Bei
meiner nachgestellten Tabelle funktioniert es.

Es funktioniert auch. Aber etwas ist lustig:

Trage ich die Formel wie oben ein, mit Großbuchstaben, dann wird nur
gezählt, wenn die "U" auch groß geschrieben sind.

Trage ich =ZählenSpez(C21:BL21;"A";"u") ein, dann werden alle
Eintragungen mit großem oder kleinem "U" richtig gezählt.

Nehme ich =ZählenSpez(C21:BL21;"a";"u"), dann wählt es wieder nicht
richtig.

Die Zellen in der Namenszeile sind als Standard formatiert. Die Zeichen
in der Schichtzeile werden mit ihrem ASCII-Wert errechnet (daher muss es
da wohl genau stimmen) und sind als Zahlenformat mit ;;;
benutzerdefiniert unsichtbar gemacht.

Also jedenfalls weiß ich jetzt, wie es funktioniert und kann ein wenig
weiter spielen. Vielen Dank!

Gruß, Ralf

[Claus Busch]

Hallo Ralf,

Post by Ralf Brinkmann
Trage ich die Formel wie oben ein, mit Großbuchstaben, dann wird nur
gezählt, wenn die "U" auch groß geschrieben sind.
Trage ich =ZählenSpez(C21:BL21;"A";"u") ein, dann werden alle
Eintragungen mit großem oder kleinem "U" richtig gezählt.
Nehme ich =ZählenSpez(C21:BL21;"a";"u"), dann wählt es wieder nicht
richtig.

ändere den Code, dann ist es egal wie die Buchstaben in der Tabelle und
der Funktion geschrieben werden:

Function ZählenSpez(Bereich As Range, str1 As String, _
str2 As String) As Integer
Dim rngC As Range, ar As Range
Dim myOffset As Integer

For Each rngC In Bereich
If rngC = LCase(str2) Or rngC = UCase(str2) Then
myOffset =4 - rngC.Row
For Each ar In rngC.MergeArea
If ar.Offset(myOffset, 0) = LCase(str1) _
Or ar.Offset(myOffset, 0) = UCase(str1) Then
ZählenSpez = ZählenSpez + 1
End If
Next
End If
Next
End Function


Mit freundlichen Grüßen
Claus

[Ralf Brinkmann]

Hallo Claus!

Post by Claus Busch
Function ZählenSpez(Bereich As Range, str1 As String, _
str2 As String) As Integer
Dim rngC As Range, ar As Range
Dim myOffset As Integer
For Each rngC In Bereich
If rngC = LCase(str2) Or rngC = UCase(str2) Then
myOffset =4 - rngC.Row
For Each ar In rngC.MergeArea
If ar.Offset(myOffset, 0) = LCase(str1) _
Or ar.Offset(myOffset, 0) = UCase(str1) Then
ZählenSpez = ZählenSpez + 1
End If
Next
End If
Next
End Function

Funktioniert super! Ich bin immer wieder überrascht, was in Excel alles
möglich ist.

Gruß, Ralf