Excel: mal wieder eine Frage

dödel

New member
Registriert
11 Apr. 2016
Beiträge
102
Moin liebes Forum,
ich habe mal wieder eine Excel-Frage, ein passender Titel viel mir leider nicht ein... Fachbegriffmandel :confused:

Ich möchte gerne anhand meines Dienstplans eine Tabelle erstellt haben, in der die entsprechenden Stunden eingetragen werden.
Mein Problem dabei: etliche Schichten sind tagesübergreifend, beispielsweise S1 von 17:00 Uhr - 01:30 Uhr. Was bedeutet, wenn eine S1-Schicht am 01. beginnt, müssen am 01. nur 7 Stunden eingetragen sein, am 2. dann 1,5 Stunden.
Soweit würde ich das wohl noch mit etwas googelei hinbekommen, aber der gemeine Trick dabei ist: Wenn am 2. wieder eine S1 stattfinden, dann müßen zu den bereits im Feld stehenden 1,5 Stunden, 7 Std. addiert werdern, oder ein entsprechend anderer Wert wenn eine andere Schicht stattfindet.

Beispielbeschreibung:
Dienstplan: hier stehen die Abkürzungen der einzelnen Schichten
Stundenzettel (handeintrag): Die entsprechenden Stunden würden so aussehen wenn man sie per Hand einträgt.
Stundenzettel (automatisch): In dieser Tabelle sollte alles automatisch eingetragen werden, sobald man im Dienstplan eine Schicht eingibt.

:eek::eek:

Anhang anzeigen Mappe12.zip
 
Vermutlich nicht die besten Lösungen, aber die ersten:
- Verschachtelte WENN-Bedingungen (wenn gestern S1 und heute S1 dann x Stunden, sonst wenn gestern S1 und heute S2 y Stunden, sonst ...)
- Hilfstabelle mit allen Kombinationen von Schichten heute und gestern und den sich für heute ergebenden Stunden, dann im aktuellen Feld die passende Reihe aus dieser Hilfstabelle raussuchen.
 
Vermutlich nicht die besten Lösungen, aber die ersten:
- Verschachtelte WENN-Bedingungen (wenn gestern S1 und heute S1 dann x Stunden, sonst wenn gestern S1 und heute S2 y Stunden, sonst ...)
- Hilfstabelle mit allen Kombinationen von Schichten heute und gestern und den sich für heute ergebenden Stunden, dann im aktuellen Feld die passende Reihe aus dieser Hilfstabelle raussuchen.

:eek: es muss nicht die beste Lösung sein, hauptsache es funktioniert zuverlässig.
ok, kurze Mathenachhilfestunde: Wir haben 8 verschiedene Schichten, maximal zwei Werte können in einem Feld zusammentreffen (Überhang vom Vortag + der aktuelle Tag) = 8^2= 64 Kombinationen für die Hilfstabelle??


@ctulhu
danke, aber möchte mich nur ungerne für jedes Problemchen in einem neuen Forum anmelden, ich denke dass es hier auch genügend Leute mit völlig ausreichendem knowhoff gibt, wenn nicht sogar etliche Profis.
 
Zuletzt bearbeitet:
Hi,
muss man den letzten Tag des Vormonats nicht auch berücksichtigen?
In der Beispieltabelle muss die Zelle D3 N1 und nicht nur N als Wert haben.
Folgendes Makro sollte funktionieren:

Sub Stunden()
EndeDesMonats = 5 'Anpassen auf echtes Ende
For spalte = 2 To EndedesMonats
Select Case Cells(4, spalte)
Case Is = "S1"
heute = 7
Case Is = "N1"
heute = 2
End Select
Select Case Cells(4, spalte - 1)
Case Is = "S1"
gestern = 1.5
Case Is = "N1"
gestern = 6
End Select
Cells(22, spalte) = heute + gestern
Next
End Sub
 
Für die ersten 4 Tage funktioniert das Makro :thumbsup:

Jetzt muss ich nur noch verstehen wie es funktioniert um es anpassen zu können, damit wäre der Sonntag dann auch verplant :cool:
 
excel.JPG

Hi, ich habe eine Spalte eingefügt, um den Wert des Letzten des Vormonates zu berücksichtigen.
Kannst ja eine Verknüpfung anlegen, wenn du die Blätter pro Monat erstellt hast.

Das abgeänderte Makro läuft von Tag 1 -31 bzw. Mitarbeiter Müller - Meyer.
Um es wirklich schön zu machen, könnte man noch die letzte Spalte/Zeile herausfinden.

Sub Stunden()
BeginnDesMonats = 3 'Spalte mit dem ersten Tag des Monats
EndeDesMonats = 33 ' Spalte mit dem letzten Tag des Monats
BeginnMitarbeiter = 4 'Erster Zeile Mitarbeiter
EndeMitarbeiter = 7 'Letzte Zeile Mitarbeiter

For Zeile = BeginnMitarbeiter To EndeMitarbeiter
For Spalte = BeginnDesMonats To EndeDesMonats
heute = 0
Select Case Cells(Zeile, Spalte)
Case Is = "S1"
heute = 7
Case Is = "N1"
heute = 2
End Select
gestern = 0
Select Case Cells(Zeile, Spalte - 1)
Case Is = "S1"
gestern = 1.5
Case Is = "N1"
gestern = 6
End Select
Cells(Zeile + 18, Spalte) = heute + gestern 'Summe 18 Zeilen tiefer eintragen
Next
Next
End Sub
 
Geht auch ohne Macro :)
Unschön an dieser Lösung ist die Anpassung an neue Schichten etwas umständlich ist. Dafür müsste man sinnvollerweise mit SVERWEIS und einer Zeitentabelle arbeiten. Ginge aber auch. Stunden aus dem Vormonat könnten in einer Hilfsspalte aufgenommen werden und einfach in Tag 1 addiert werden.
 

Anhänge

  • Schichten.zip
    6,5 KB · Aufrufe: 3
Danke mythink, großes Kino :thumbup:

Ich habe das Makro erstmal an die Arbeitszeiten angepasst, das funktioniert einwandfrei.
Nächster Plan wäre die anfallenden Nachtstunden separat anzuzeigen, jeder Mitarbeiter bekommt dann also zwei Reihen (ich denke aber dass ich das mit deiner tollen Vorarbeit selbst hinbekomme)
Kann man das problemlos in einem Makro lösen oder macht es Sinn ein zweites zu erstellen?


Sub Stunden()
BeginnDesMonats = 3 'Spalte mit dem ersten Tag des Monats
EndeDesMonats = 33 ' Spalte mit dem letzten Tag des Monats
BeginnMitarbeiter = 4 'Erster Zeile Mitarbeiter
EndeMitarbeiter = 7 'Letzte Zeile Mitarbeiter

For Zeile = BeginnMitarbeiter To EndeMitarbeiter
For Spalte = BeginnDesMonats To EndeDesMonats
heute = 0
Select Case Cells(Zeile, Spalte)
Case Is = "S1"
heute = 7
Case Is = "N2"
heute = 8.5
Case Is = "F"
heute = 8
Case Is = "S"
heute = 8
Case Is = "N"
heute = 2
Case Is = "SF"
heute = 7
Case Is = "NF"
heute = 8.5
End Select
gestern = 0
Select Case Cells(Zeile, Spalte - 1)
Case Is = "S1"
gestern = 1.5
Case Is = "N"
gestern = 6
Case Is = "SF"
gestern = 1.5
End Select
Cells(Zeile + 18, Spalte) = heute + gestern 'Summe 18 Zeilen tiefer eintragen
Next
Next
End Sub
 
Hi,
was bedeutet Nachtstunden?
Wenn es z. B. die Zeit zwischen 22 und 4 Uhr ist, kannst du zwei neue Variablen nehmen (Nachtgestern und NachtHeute) und die Werte in den beiden "Select Case" ebenfalls füllen.
 
Genau, bei uns geht es dabei um die Zeiten zwischen 20:00h und 06:00h.

Mir viel aber gerade auf dass es damit nicht getan ist :huh:
Im Endeffekt werde ich pro Mitarbeiter 6 Zeilen brauchen, da es drei verschiedene Stundensätze gibt (SF+NF, F+S+N, S1+N2). Das Ganze dann jeweils für Tag + Nachtstunden, aber das sollte man auch mit diesem Makro lösen können?

Die Tabellen die ich von der Firma zur Verfügung gestellt bekommen habe, sind eine echte Qual, nach jeder Eingabe gibt es eine gefühlte Sekunde Verzögerung, wenn ich di Zuarbeit für die Buchhaltung mache, muss ich an die 1200 Eingaben machen, das macht echt keinen Spaß.
 
Liste doch mal von den Schichten
a) die Stundenverteilung
b) Stundensätze Tag/Nacht
auf.
 
Stundenverteilung.png


nicht über die gleichen Arbeitszeiten von S1/SF und N2/NF stolpern, SF+NF bezeichnet die Schichtführer und die haben einen anderen Stundensatz.
Die Stundensätze an sich sind irrelevant, die trägt die Buchhaltung ein und sind mir auch offiziell nicht bekannt für die Kollegen.
Die drei unterschiedlichen Abrechnungssätze habe ich farblich markiert. Im Grunde brauche ich für jeden Mitarbeiter 3 Ausgaben, 1 x "Abrechnung OS" (Schichten S1+ N2); 1 x "Abrechnung Service" (F + S + N) sowie "Abrechnung Schichtführer" (SF + NF), jeweils in der oberen Reihen die täglich gearbeiteten Stunden und in der Zeile darunter die jeweiligen Nachtstunden.
Die Werte Copy/Paste ich dann in die jeweiligen Abrechnungsvorlagen...oder mache einen Bezug!...den Rest macht dann die Buchhaltung.


Hier noch ein Teilausschnitt eines Abrechnungsformulars:
Abrechnungsformular.png
Die mühselige Handeingabe will ich mir einfach sparen, von den Sheets fülle ich jeden Monat 6-7 Stück aus + Kontrolle....

Pausen kann man übrigens ignorieren, die bekommen wir durchgehend bazahlt.
 
Zuletzt bearbeitet:
Hi,
habe die Select/Case mal geändert.
Ausgabe erfolgt unten getrennt nach gesamt und nachts. Da sind je nach Anzahl der Mitarbeiter die +16/+17 anzupassen

'Option Explicit

Sub Stunden()
BeginnDesMonats = 3 'Spalte mit dem ersten Tag des Monats
EndeDesMonats = 33 ' Spalte mit dem letzten Tag des Monats
BeginnMitarbeiter = 4 'Erster Zeile Mitarbeiter
EndeMitarbeiter = 7 'Letzte Zeile Mitarbeiter
Anzahlmitarbeiter = 1 'Zähler, um die Ergebnisse unereinander zu bekommen
For zeile = BeginnMitarbeiter To EndeMitarbeiter
For spalte = BeginnDesMonats To EndeDesMonats

heute = 0
heutenacht = 0
Select Case Cells(zeile, spalte)
Case "S1", "SF"
heute = 7
heutenacht = 2
Case "N2", "NF"
heute = 8.5
neutenacht = 4.5
Case "F", "S"
heute = 8
Case Is = "N"
heute = 2
heutenacht = 2
End Select

gestern = 0
gesternnacht = 0
Select Case Cells(zeile, spalte - 1)
Case "S1", "SF"
gestern = 1.5
gesternnacht = 1.5
Case Is = "N"
gestern = 6
gesternnacht = 6

End Select

Cells(zeile + 16 + 2 * Anzahlmitarbeiter, spalte) = heute + gestern
Cells(zeile + 17 + 2 * Anzahlmitarbeiter, spalte) = heutenacht + gesternnacht
Next
Cells(zeile + 16 + 2 * Anzahlmitarbeiter, 1) = Cells(zeile, 1)
Cells(zeile + 17 + 2 * Anzahlmitarbeiter, 1) = Cells(zeile, 1)
Cells(zeile + 16 + 2 * Anzahlmitarbeiter, 2) = "gesamt"

Cells(zeile + 17 + 2 * Anzahlmitarbeiter, 2) = "nachts"


Anzahlmitarbeiter = Anzahlmitarbeiter + 1

Next
End Sub
 
Zuletzt bearbeitet:
Vielen Dank für die Arbeit!

Allerdings werden die Nachtstunden nicht richtig berechnet (siehe Bild, die richtigen Werte habe ich unter die Nachtstunden geschrieben und farblich markiert)
Die korrekt anfalleden Nachtstunden hast du aber richtig ins Makro eingetragen, daher finde ich den Fehler nicht :confused:

Rechenfehler.png
 
Hi,
habe mal die Varirablendeklaration erzwungen und so festgestellt, dass ich neutenacht statt heutenacht geschrieben hatte.
Jertzt sollte es passen:

Option Explicit

Sub Stunden()
Dim BeginnDesMonats
Dim EndeDesMonats
Dim BeginnMitarbeiter
Dim EndeMitarbeiter
Dim Anzahlmitarbeiter
Dim heute
Dim heutenacht
Dim gestern
Dim gesternnacht
Dim Zeile
Dim Spalte
BeginnDesMonats = 3 'Spalte mit dem ersten Tag des Monats
EndeDesMonats = 33 ' Spalte mit dem letzten Tag des Monats
BeginnMitarbeiter = 4 'Erster Zeile Mitarbeiter
EndeMitarbeiter = 7 'Letzte Zeile Mitarbeiter
Anzahlmitarbeiter = 1 'Zähler, um die Ergebnisse unereinander zu bekommen
For Zeile = BeginnMitarbeiter To EndeMitarbeiter
For Spalte = BeginnDesMonats To EndeDesMonats

heute = 0
heutenacht = 0
Select Case Cells(Zeile, Spalte)
Case "S1", "SF"
heute = 7
heutenacht = 4
Case Is = "N2"
heute = 8.5
heutenacht = 4.5
Case Is = "NF"
heute = 8.5
heutenacht = 4.5
Case Is = "F"
heute = 8
Case Is = "N"
heute = 2
heutenacht = 2
Case Is = "S"
heute = 8
heutenacht = 2
End Select

gestern = 0
gesternnacht = 0
Select Case Cells(Zeile, Spalte - 1)
Case "S1", "SF"
gestern = 1.5
gesternnacht = 1.5
Case Is = "N"
gestern = 6
gesternnacht = 6


End Select

Cells(Zeile + 16 + 2 * Anzahlmitarbeiter, Spalte) = heute + gestern
Cells(Zeile + 17 + 2 * Anzahlmitarbeiter, Spalte) = heutenacht + gesternnacht
Next
Cells(Zeile + 16 + 2 * Anzahlmitarbeiter, 1) = Cells(Zeile, 1)
Cells(Zeile + 17 + 2 * Anzahlmitarbeiter, 1) = Cells(Zeile, 1)
Cells(Zeile + 16 + 2 * Anzahlmitarbeiter, 2) = "gesamt"

Cells(Zeile + 17 + 2 * Anzahlmitarbeiter, 2) = "nachts"


Anzahlmitarbeiter = Anzahlmitarbeiter + 1

Next
End Sub
 
  • ok1.de
  • thinkstore24.de
  • Preiswerte-IT - Gebrauchte Lenovo Notebooks kaufen
Zurück
Oben