Live-Forum - Die aktuellen Beiträge
Datum
Titel
29.03.2024 13:14:12
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1484to1488
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

CountIf mal umfangreicher

CountIf mal umfangreicher
11.04.2016 20:08:00
Rene
Hallo Forumsfreunde,
ich habe anbei eine CountIf Aufgabe, welche etwas aufwändiger und damit zu kompliziert für mich ist. Ich hoffe auf eure Hilfe und beginne gleich mal mit der Problembeschreibung.
Ich arbeite an einem Dienstplan, in welchem auf der linken Seite die Mitarbeiter eingetragen werden können. Die Zeilen sind nicht abschließend, so dass ich mittels Makro weitere Mitarbeiter hinzufügen kann. Unter den Namen befindet sich die Abteilung.
Was soll nun konkret passieren.
1. Wenn ich einen Diensteintrag mache (T für Tagdienst oder N für Nachtdienst, usw.) soll er mir in der Zeile Dienststärke die geplanten Mitarbeiter zusammenzählen.
Die Form soll folgendermaßen sein „Abteilungen mit A / Abteilungen mit B“
Kurzgesagt also z.B. wie in Spalte 4: 1/3
2. Diese Zählweise soll für jede Spalte bis zum 31.12. gelten
3. Wenn ich Mitarbeiter hinzufüge oder lösche, soll sich das CountIf anpassen.
Ich danke schon jetzt für eure Hilfe, zumal ich denke, dass es nicht ganz einfach ist.
Anbei noch die Datei https://www.herber.de/bbs/user/104904.xlsm

8
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: CountIf mal umfangreicher
12.04.2016 19:59:55
Piet
Hallo Rene
hier eine einfache Makro Lösung. In ein Modulblatt kopieren und testen.
Vielleicht gibt es noch andere Lösungen.
mfg Piet

Option Explicit      'Piet für Herber Forum  12.4.2016
Const JahrBer = "D9:PC9"
Dim MA As Object, Txt As String
Dim DZeile As Integer, Zeile As Integer
'Dienststärke per VBA ermitteln
Sub Dienststärke_ermitteln()
Dim a As Integer, b As Integer, j As Integer
With Worksheets("Grundgerüst")
'Dienststärke Zeile zum ausfüllen
DZeile = Cells(200, "B").End(xlUp).Row - 8
'End Zeile für Suchlauf Abteilungen
Zeile = Cells(200, "B").End(xlUp).Row - 4
'Schleife für Jahr 2016 bis 31.12.
For Each MA In Range(JahrBer)
a = 0:  b = 0:  Txt = Empty
'Wochentag prüfen  (Leerfelder)
If MA.Offset(-5, 0)  Empty Then
'Schleife für Abteilung A + B zählen
For j = 1 To Zeile Step 4
If MA.Cells(j, 1)  "" Then Txt = Cells(j + 9, "B")
If InStr(Txt, "ung A") Then a = a + 1
If InStr(Txt, "ung B") Then b = b + 1
Txt = Empty
Next j
'Abteilung A + B auswerten + auflisten
If a + b > 0 Then Txt = CStr(a & "/" & b)
If Txt  Empty Then MA.Cells(DZeile, 1) = "'" & Txt
End If
Next MA
End With
End Sub

Anzeige
AW: CountIf mal umfangreicher
13.04.2016 19:40:25
Rene
Hallo Piet,
erstmal danke für die Mühe. Ich habe den Code getestet und es funktioniert soweit gut.
Was ich jetzt noch benötige, ist eine Unterscheidung in den Diensteinträgen. Ich möchte also bestimmte Tage nur nach den Eintrag "T" auswerten, andere nur nach "N". Ich könnte mir auch vorstellen, noch eine weitere Zeile Dienststärke zu haben, die sich unter der schon vorhandenen befindet. Oben Auswertung "T" und in der unteren Zeile "Auswertung "N".
Was ich noch nicht verstehe, sind die folgenden Codezeilen.
'Dienststärke Zeile zum ausfüllen
DZeile = Cells(200, "B").End(xlUp).Row - 8
'End Zeile für Suchlauf Abteilungen
Zeile = Cells(200, "B").End(xlUp).Row - 4
Ich weiß nicht was das "B" bedeutet. Ist damit Spalte B gemeint? Mir sagt diese Schreibweise unter Cells nichts. Und warum zähle ich von der letzten Zeileneintragung 8 bzw. 4 Zeilen nach oben. Irgendwie habe ich hier einen Klemmer.
René

Anzeige
AW: CountIf mal umfangreicher
13.04.2016 22:04:18
Piet
Hallo Rene,
zunaechst einmal, es freut mich das mein Programm bei dir funktioniert.
Const JahrBer = "D9:PC9" solltest du auf PD9 oder PE9 abaendern (Schaltjahr=366 Tage)
kleiner Excel Grundkurs:
Ich gehe davon aus das du bisher nur die Adressierung des Makro Recorder kennst.
Ausser Range gibt es Cells(z, s). Dabei ist z=Zeile und s=Spalte, beginnend ab "A1"
Cells(2,3) entspricht Range("C2"). Man kann bei Cells die Spalte als Text angeben.
DZeile = Cells(200, "B").End(xlUp).Row - sucht die letzte Zelle in Spalte "B" (Dienststärke)
Die gefundene Zeile bezieht sich hier immer auf "A1". Warum aber die Korrektur -4 und -8 ?
For j = 1 To Zeile Step 4
Beim Programmieren sollte man sich das Leben leicht machen. Sinnvoll programmieren.
Schau mal hier: MA.Cells(j, 1) Mit MA ist immer die Zeile 9 gemeint, von Range("D9:PC9") = JahrBer
Step 4, springe um vier, bot sich an um den Text "T" oder "N" auszuwerten, ob vorhanden oder nicht.
Zeile ist in der For Next Schleife die letzte auszuwertende Zeile vor der Zeile -Dienststärke-
Die Variante MA.Cells(j, 1) ist eine sinnvolle Kombination aus Range und Cells. MA=Range("D9:PC9")
For Each MA In Range(JahrBer) bedeutet, jede einzelne Zelle wird intern aktiviert! (ohne Select!)
Immer in der Zeile 9. - Optisch gut sichbar wenn du nur zum Testen den Befehl Ma.Select einfügst.
Der zweite Teil mit MA.Cells(j, 1) bezieht sich jetzt auf die aktive Zelle nach unten, bis Zeile.
Die Basis Adresse für Cells ist jetzt die Zelle MA, nicht mehr "A1"! Was bleibt ist (z,s) = (j,1)
Weil sich die Basis Adresse für Cells auf MA bezieht müssen Zeile und DZeile korrigiert werden.
Ich hoffe das Programm ist so verständlich erklärt, das du es evtl. selbst abändern kannst.
Zur neuen Anfrage:
Es ist kein Problem und nicht viel Aufwand auch T oder N auszuwerten.
TxtN = MA.Cells(j, 1) - aktuellen Text laden zum auswerten von "T" oder "N"
Wenn es nur bestimmte Tage sein sollen braucht man dafür eine Hilfszeile, denn das Makro kann ja
nicht selbst wissen was und wie es auswerten soll? Das braucht konkrete Zeichen zum auswerten!
Auch die Überlegung einer zweiten Zeile für Dienststärke "N" ist kein Problem.
Ich denke das Makro ist so simpel aufgebaut das man es leicht erweitern kann.
Ich bin bereit dabei behilflich zu sein. Brauche dann aber konkrete Informationen.
mfg Piet

Anzeige
AW: CountIf mal umfangreicher
14.04.2016 22:49:45
René
Hallo Piet,
danke für die Unterstützung bei meinen Fragen.
Bei der Frage zu den beiden Codezeilen habe ich mich vielleicht etwas komisch ausgedrückt. Ich verstehe die -8 und die -4 nicht.
Dienststärke Zeile zum ausfüllen
DZeile = Cells(200, "B").End(xlUp).Row - 8
End Zeile für Suchlauf Abteilungen
Zeile = Cells(200, "B").End(xlUp).Row - 4
~f~
Dienststärke: Gehe zu Zeile 200 in der Spalte "B". Danach suche aufwärts die zuletzt beschriebene Zeile. Wenn du diese gefunden hast, gehe 8 Zeilen nach oben.
Man ist dann in der Zeile 22?
Das Makro funktioniert, aber ich kann nicht nachvollziehen was in Zeile 22 gesucht wird. Bei Row -4 landet Excel in Zeile 26. Okay, das ist die letzte Zeile mit einer Abteilungsangabe.
Für das Ausfüllen einer zweiten Zeile der Dienststärke, habe ich den Code um diese Zeilen ergänzt
~f~
Dienststärke untere Zeile ausfüllen
If a + b > 0 Then Txt = CStr(a & "/" & b)
If Txt <> Empty Then MA.Cells(DZeile, 1).Offset(1, 0) = "'" & Txt
~f~
Für die Auswertung von "T" bzw. "N" brauche ich nochmal deine Hilfe.
Du hast geschrieben: TxtN = MA.Cells(j, 1) - aktuellen Text laden zum auswerten von "T" oder "N"
Wie soll die Codezeile hier genau aussehen?
Eine weitere Zeile die ich nicht genau verstehe ist die folgende:
~f~
If Txt <> Empty Then MA.Cells(DZeile, 1) = "'" & Txt
Danke für deine Hilfe

Anzeige
AW: CountIf mal umfangreicher
16.04.2016 16:56:52
Piet
Hallo Rene
anbei eine kleine Beispiel Datei zum Testen, aber im Excel 2003 Format.
Zum Testen spielt das keine Rolle. Dort gibt es auch drei Demo Programme
um die For Next Schleifen und den Unterschied Offset und Cells zu erklaeren.
Das Original Makro wurde etwas geaendert, einige Variablen Namen geaendert,
damit man leichter sieht welche Variable für was zustaendig ist. Vorher hatte ich
Txt mehrfach verwendet, überschrieben, es ist aber besser die Dinge zu trennen.
Bei Row -4 ist mir aufgefallen das es eigentlich Row-12 (auf 10 gesetzt) sein soll.
Warum das so ist erkennst du am besten im Demo Programm mit .Select + MsgBox (Step)
Zur Anfrage: If Txt Empty Then MA.Cells(DZeile, 1) = "'" & Txt - Hier habe ich
nicht zwischen "T/N" unterschieden, sondern ausgewertet wenn die Zelle gefüllt ist.
MA.Cells(DZeile, 1).Offset(1, 0) = "'" & Txt kannst du so schreiben:
MA.Cells(DZeile +1, 1) = "'" & Txt
Bei der "T/N" Auswertung kann ich dir noch helfen, dazu sollte aber wissen woran ich sehen
kann was wann und wie ausgewertet werden soll? Am besten einen Wert in einer Hilfszeile
Das Makro können wir erweitern sobald ich weiss was da gefordert ist.
Würde mich freuen wenn die Demos dir helfen den Programmaufbau zu verstehen.
https://www.herber.de/bbs/user/105010.xls

Anzeige
AW: CountIf mal umfangreicher
16.04.2016 18:58:12
René
Hallo Piet,
danke für die Demodatein. Ich würde sie mir noch einige Male auf der Zunge zergehen lassen, um diese Unterscheidungen zu verinnerlichen.
Deinen Code habe ich nunmehr etwas abgeändert und ihn an meine Bedürfnisse angepasst. Die Auswertung nach T und N wird nunmehr vorgenommen und die Berechnung findet in den beiden untersten Zeilen statt. Ich habe den Code noch etwas kommentiert, so wie ich ihn verstehe.
Die Abteilungen habe ich in Amtsbezeichnungen gewandelt. Mir war es bei den ersten Erläuterungen einfacher, nach zwei Abteilungen zu fragen und den ganzen anderen Kram wegzulassen. In der Realität werden es Amtsbezeichnungen sein, welche zwei Laufbahnen betreffen.
Nach etwas belesen, suchen und abändern sowie deiner grandiosen Vorarbeit, bin ich einen großen Schritt weiter.
https://www.herber.de/bbs/user/105012.xlsm
Vielen, vielen Dank dafür.

Anzeige
AW: CountIf mal umfangreicher
16.04.2016 17:02:10
Piet
Nachtrag
denke bitte daran das der Thread in der Forum Liste schon weit unten steht und auslaeuft.
Dann wird er vom System automatisch gelöscht. Wir sollten vorher fertig werden!
mfg Piet

AW: gute Zusammenarbeit, bin beeindruckt ...
17.04.2016 13:18:57
Piet
Hallo Rene,
ich habe mir deine Beispiel Datei angesehen und bin beeindruckt. Freut mich sehr.
Dieser Thread ist ein Zeichen für gute Zusammenarbeit zwischen Frager und Ratgeber.
Sinn eines Forum ist auch, nicht immer die direkte Lösung zu liefern, sondern den Frager
durch Beispiel Datein in die Lage zu versetzen den Programmaufbau selbst zu verstehen
und dann den Code auf die eigenen Bedürfnisse aendern zu können. Gute Zusammenarbeit!
Und, das Programm wurde sehr gut kommentiert. Alle Achtung.
mfg Piet
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige