Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1472to1476
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

Formel zum übertragen von Zeiten

Formel zum übertragen von Zeiten
11.02.2016 21:02:28
Zeiten
https://www.herber.de/bbs/user/103480.xlsm
Hallo ich habe in der Arbeitsmappe eine Stundenübersicht und ein Stundenblatt für den Monat.
Ist es irgendwie möglich das er im 1. Tabellenblatt "Stundenzettel" die Zeiten vom Tabellenblatt "Dienstzeiten Januar" übernimmt...?
Ich dachte mir so in der Art man vergleicht den Namen oder die LfdNr und das Datum und wenn das übereinstimmt übernimmt man die Zeit...?
Ist das in irgendeiner Form realisierbar...?
Danke für eure Mithilfe:)

5
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Formel zum übertragen von Zeiten
12.02.2016 02:40:55
Zeiten
hallo
anbei eine Makro Lösung das die Stundenzeiten kopiert
Bitte einmal testen, wenn das Ergebnis nicht okay ist Datei ohne Speichern schliessen.
Ein Nachtei ist zur Zeit das die Set Anweisung: Set DZt = Sheets("Dienstzeiten Januar")
sich hier auf den Monat Januar bezieht und für Februar im Makro geandert werden muss.
Es ginge evtl. auch über den Namen der VBA Komponente, die ist mir aber nicht bekannt.
Ich nehme an das ab 5 Std. 30 Minuten Pause abzuziehen sind: Sonst PAZ=500 aendern
PS: die Beispieldatei lief auf meinem PC extrem langsam, ich nehme an das es sehr viele
Formeln gibt. Bleibt die Frage ob man sie nicht durch Konstante Werte ersetzen könnte?
Würde mich freuen wenn es klappt.
mfg Piet
Option Explicit
'Dienstzeiten: Datum ab D2, G2, J2
'Dienstzeiten: C=Name, B=DG Diestgrad, A=LfNr (Zeilen Ende 83)
'Stundenzettel G4 Name, C6-Beginn, D6-Ende, B=Datum, A=Wo-Tag
Const PAZ = 500 'Pausenabzug ab: 5 Stunden
Const Pause = 30 'Pausenzeit in Minuten
Dim DZt As Object, Stz As Object 'Tabellen Namen
Dim MName, Monat, Tag, DZ, SZ, sp 'Mitarbeiter, Objekte
Dim DZAdr, AZeit, EZeit, Stunden 'DZ-Adr Dienstzeit Adr
Sub Stunden_Daten_übertragen()
Set Stz = Sheets("Stundenzettel")
Set DZt = Sheets("Dienstzeiten Januar")
On Error GoTo Fehler:  Err = Empty
'Berechnen abschalten  (dauert sonst ewig)
Application.Calculation = xlManual
'Monat + Mitarbeiter laden zum vergleichen
Monat = Month(Stz.Range("G2"))
MName = Stz.Range("G4").Value
'Schleife zum suchen des Mitarbeiter in Dienstzeiten
For Each DZ In DZt.Range("C3", DZt.[c200].End(xlUp))
If MName = DZ.Value Then DZAdr = DZ.Address: Exit For
Next DZ
'Schleife zum auflisten der Tage
For Each SZ In Stz.Range("B6:B46")
If Monat = Month(SZ) Then
'Tag über Offset ab Spalte D2
Tag = Day(SZ) - 1: sp = Tag * 3 + 2
AZeit = DZt.Range(DZAdr).Cells(1, sp)
EZeit = DZt.Range(DZAdr).Cells(2, sp)
SZ.Cells(1, 2) = AZeit  'Anf-Zeit
SZ.Cells(1, 3) = EZeit  'End-Zeit
'Stundenzeiten berechnen über 1200 (12:00)
Stunden = 1200 - AZeit + EZeit - 1200
If Stunden > PAZ Then Stunden = Stunden - Pause
SZ.Cells(1, 4) = Stunden / 100   '900 = 9:00
End If
Next SZ
'** kein -Exit Sub- damit Berechnen aktiviert wird!!
Fehler:  Application.Calculation = xlAutomatic
'ggf. Fehlermeldung ;  nur bei Err > 0
If Err > 0 Then MsgBox "unerwarteter Fehler"
End Sub

Anzeige
AW: Formel zum übertragen von Zeiten
12.02.2016 09:49:20
Zeiten
Hallo Piet,
danke dir erstmal. Leider versteh ich das noch nicht ganz. Erstmal kurz zur Problematik das die Tabelle zu langsam ist. Wenn mir da irgendjemand bessre Formel geben könnte bin ich da natürlich ganz Ohr aber da ist mein Latein momentan noch am Ende:/ zusätzlich kommt noch dazu das eigentlich noch für jeden Monat ein Tabellenblatt dazu kommt.
Ich bin gern für Anregungen und Verbesserungsvorschläge offen. Ich möchte ja auch dazu lernen.
Jetzt zu deinem Tipp. Erstmal würde es mir reichen wenn er mir die Dienstbeginn und Endzeiten in das Tabellenblatt übertragen würde.
Den Kopf des Stundenzettel übernimmt er ja schon automatisch. Meine Überlegung wäre nun das er Überprüft welche LfdNr eingetragen ist und übernimmt dann von dem anderen Tabellenblatt die Dienstbeginn und Endzeiten dazu müsste er noch das Datum überprüfen.
Nur weiß ich nicht wie ich Excel das mit teilen soll "wenn LfdNr 1 dann prüfe das Datum und übernimm aus der Zelle bei dem anderen Tabellenblatt vom gleichen Datum die Dienstbeginn Zeit".
Vielleicht kannst du mir weiterhelfen oder wer anderes. Ich hoffe ich konnte das einigermaßen verständlich erklären;)

Anzeige
AW: Formel zum übertragen von Zeiten
12.02.2016 12:39:37
Zeiten
hallo
habt ihr schon mal mit VBA Makro gearbeitet? Am besten einfach mal testen. Im der Datei sind ja mehrere leere Module vorhanden, ich glaube von 1-7 Das Makro in Modul 1 kopieren, Stundenzettel anklicken mit F5 das Makro starten. Es hat bei mir funktioniert.
Zur Funktion:
das Makro holt sich zuerst den Mitarbeiter Namen aus dem Stundenzettel und sucht im Blatt Dienstzeiten gezielt nach diesem Namen. Ich denke er kommt nur einmal vor. Dann sucht er im Stundsenzettel nach dem 1.Datum des aktuellen Monats. İm Beispiel gab es ja auch Eintraege für Dez. 2015. Die überspringe ich.
Dann holt er zu jedem Tag den entsprechenden Tag in den Dienststunden, beginnend ab Spalte D (Offset) Ich gehe davon aus, das sich die Spalten nie aendern. Das prüfe ich nicht. Dann werden die gefundenen Stunden übertragen und die Zeit berechnet.
Ich berücksichtige auch den Pausenabzug ab 5 Stunden!
Diesen Test einfach mal zu Fuss machen, indem das Makro über F5 gestartet wird. Wenn es laeuft kann man ja einen Button einfügen um das Makro über Button zu starten. Oder Makro Dialog, denn alle Makros werden ja im Menü in einer ListBox angezeigt.
Noch ein Tipp von mir über den Unterschied zwischen Formeln und Werten (Constants). Jede Formel hat den Nachteil das sie neu berechnet wird. Vor allem wenn die Option auf Calculation=Automatic steht. Man kann diese Funktion auf manuell stellen. Dann wird nur berechnet wenn man es selbst will.
Wenn diese Datei aber staendig erweitert wird sollte man ernsthaft überlegen eine Mustervorlage zu erstellen, für jeden Monat, auch mit Formeln, und dann wenn man sie für den taeglichenm Gebrauch benutzt (mit neuem Datei Namen) alle Bereiche wo sich die Daten nicht mehr aendern auf Constant zu setzen. Das laesst sich sehr leicht mit einem Makro erledigen. Damit reduziert man Berchnungszeiten drastisch. Sie entfaellen ja. Nehmen wir z.B. Die Überschriftszeile für Datum von D3-xx3
Dieses Datum aendert sich ja nicht mehr, warum es staendig mit berechnen lassen? Macht wenig Sinn! Diesen Bereich kann man komplett kopieren, und über die Special einfügen Methode als Werte eingeben. Jetzt wird der Wert der Formel in die Zelle kopiert, die Formel ist gelöscht. Hier ist es sinnvoll. Bereiche deren Wert konstant bleiben lieber mit dieser Methode auf Constant setzen.
İm Makro sieht das so aus:
Range(Bereich).Kopie
Range(Bereich).PasteSpecial xlValues
Man muss also nur einmal wissen welche Bereich immer gleich bleiben, und löscht alle Formeln!
Das Progamm wird dadurch wesentlich schneller. Wohlgemerkt: die Bereiche, die konstant bleiben!
Das gilt vor allem wenn die Dienstzeiten abgeschlossen sind und es keine weiteren Eintrage gibt.
Dann kann man alles auf Konstant setzen. Es gibt ja keine Aenderungen mehr.
Mal in Ruhe drüber nachdenken, und evtl. eine existierende Datei kopieren, und mit der Kopie mal experimentieren.
Da kann man nichts falsch machen. Ist ja nur eine Kopie zum üben! So mache ich es in der Praxis.
mfg Piet

Anzeige
AW: Formel zum übertragen von Zeiten
14.02.2016 16:40:16
Zeiten
Hallo Piet,
leider habe ich noch nie mit VBA Makro's gearbeitet. Leider versteh ich auch nicht ganz was ich wohin kopieren muss:/
Erstmal gilt es für mich das Problem zu lösen, bevor ich zu deinen anderen Tipps komme:D
Vielleicht kannst du mir da noch weiterhelfen. Dazu noch eine Sache mit der Pause. Die muss nicht zwingend mitgerechnet werden. Wichtig ist erstmal das die Anfangs und die Endzeit übertragen wird. Die Pausenzeit würde ich später berechnen. Das hängt dann mit der Spalte "GB" und "PGB" zusammen.
Wie gesagt ich brauch erstmal noch Starthilfe.
Mit freundlichen Grüßen
Marco

Anzeige
AW: Formel zum übertragen von Zeiten
14.02.2016 21:21:13
Zeiten
Hallo Piet,
ich habe es nach rum probieren doch hinbekommen:) das sieht schon mal sehr sehr gut aus:)
Kann ich das Makro so erweitern das es die Wochenstunden aus dem Tabellenblatt Stundenübersicht übernimmt...?
Dann ist noch ein Problem mit den Monatsübergreifenden Stunden. Die ganze Arbeitsmappe ist immer auf ein Jahr ausgelegt. Somit existieren immer 12 Tabellenblätter für die Einzelstunden und für Wochenstunden und leider ist die Arbeitsmappe dadurch groß und langsam. Ich würde mich da über jede Hilfe freuen das es einfacher geht doch leider sind meine Excelkenntnisse einfach zu gering. Das Makro müsste alle Tabellenblätter mit den Wochenstunden überprüfen und wenn das Datum mit dem Datum vom Tabellenblatt Stundenübersicht übereinstimmt einfügen. Ist das möglich...?
Danke dir erstmal für deine Hilfe!
Anzeige

Links zu Excel-Dialogen

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige