Office: Probleme bei Formelberechnung

Hallo zusammen,

seit Monaten hatte ich an einer Stundenberechnungs-tabelle in Excel gearbeitet. Klappte alles prima bis nun mein Arbeitgeber im neuen Formular die Spalte "Tag" hinzu genommen hat, daher stehe ich jetzt an einem Problem und komme nicht weiter.

Es ist schwierig für mich dies zu erklären, aber ich versuchs mal:

Bei dem angehängten Excel File sind 6 Tabellenblätter.
In der Tabelle "Januar" gebe ich ein, an welchen Daten ich gearbeitet habe.
Der Tag wir automatisch berechnet.
Da das nicht regelmäßig ist und an immer unterschiedlichen Tagen sein kann, kann ich die Daten nicht "vorausfüllen".

Mein Arbeitgeber verlangt die Abgabe der Stunden auf einem besonderen Formular. In diesem Falle ist das die Tabelle "KW1" (oder KW2/3/4 etc)

Bisher war es so, dass in der Tabelle "KW1" das Feld "Tag" also Montag bis Freitag nicht vorausgefüllt war. Ich konnte also problemlos die Stunden- und Pausezeiten etc. direkt von der anderen Tabelle (Januar) verlinken.

Jetzt sind die Tage vorausgefüllt und die Tabelle "KW1" etc. muss immer mit Montag beginnen.

Wenn ich jetzt wie in diesem Beispiel am 5.1 gearbeitet habe, was ein Mittwoch ist, wie bekomme ich es dann hin, dass die errechneten Stunden- und Pausezeiten auf der Tabelle "KW1" etc. auch erst am Mittwoch stehen und nicht am Montag? :?: :?:

Nehmen wir an ich habe im Januar 20 Tage gearbeitet, dann würde ich gerne die errechneten Stunden- und Pausezeiten in den Tabellen "KW1-KW4" automatisch ausfüllen lassen.

Falls ihr euch jetzt fragt, warum ich die Berechnung nicht kleich in den "KW" Tabellen mache, ganz einfach, in meiner Monatstabelle lasse ich mir automatisch auch die ganzen Ãœberstunden, Nachtstunden etc. ausrechnen
und sehe direkt was ich verdiene, deshalb brauche ich die Monatsübersicht für mich und die "KW" Tabellen für den Arbeitgeber.

Die Spalten "Überstunden/Nachtsunden" etc. habe ich in diesem Beispiel mal schwarz eingefärbt, da es um die nicht geht und nicht noch mehr Verwirrung aufkommen soll :-)

Ich hoffe echt, dass mich irgendjemand versteht und mir helfen kann.
Habe versucht das so einfach wie möglich zu erklären.

Viele Grüße & danke schonmal,
Philipp

P.S.: Ich arbeite mit der Excel 2007 Version

 

Hallo,

statt in mehreren Foren Dein Anliegen einzustellen und sich dann nicht mehr zu melden, solltest Du vielleicht nur in einem posten und da auch nachschauen!

http://www.office-loesung.de/ftopic476535_0_0_asc.php

Gruß

Edgar

 

Hallo!

Was machst Du, wenn Du an das Ende des Januars kommst? Der 31.1.2011 gehört bereits zur KW5. Was machst Du, wenn sich also die Monate und die KW überschneiden?
Ich habe Dir in den KW-Blättern in Spalte Y Formeln eingefügt: dabei enthält Y4 die Nr. der KW und Y3 das Kalenderjahr. Auf Basis dieser beiden Zellen errechnen sich dann die Kalenderdaten der folgenden 7 Wochentage in dieser Spalte.

Damit Du nach dem Januar auch den Februar als weiteres Blatt einfügen kannst und obwohl die KW5 sich sowohl im Januar als auch Februar befindet, wirst Du trotzdem diese Woche brauchen.
Dazu habe ich Dir eine VBA-Makro-Funktion eingebaut namens MonatFeld(Datum;SpalteNr):
Sie hat 2 Parameter: das Datum bezieht sich auf das Datum in der Y-Spalte und die SpalteNr bezieht sich auf die Spaltennummer in den Monatsblättern.
So lautet zB. die Formel im Blatt KW1 in Zelle D7:
=MonatFeld($Y7;2)
Aufgrund der Datumsangabe in Y7 weiß die Funktion, dass es sich um ein Januardatum handelt und sucht im Januar-Blatt nach diesem Datum. Hat es dieses gefunden, wird der Inhalt der 2. Spalte (B-Spalte) aus der gefundenen Datumszeile zurückgegeben.
Dadurch kannst Du auch die KW5 ohne Probleme meistern, denn das Makro schaut automatisch aufgrund des Datums im 1. Parameter der Funktion im richtigen Monatsblatt nach.

Du musst nur auf eine Kleinigkeit achten: falls Du die österreichische Deutsch-Sprachversion installiert hättest, müsstest Du das Januar-Blatt in Jänner-Blatt umbenennen, denn sonst findert Excel dieses Blatt nicht.

Code:

Public Function MonatFeld(Dt As Date, Spalte%) As Variant
  Dim Ws As Worksheet
  Dim MoName$
  Dim RgDatum As Range, RgZeile As Range
  
  MoName$ = MonthName(Month(Dt))
  For Each Ws In Worksheets
    If Ws.Name = MoName$ Then Exit For
  Next Ws
  
  MonatFeld = ""
  Set RgDatum = Ws.Range("B9:B39")
  For Each RgZeile In RgDatum.Cells
    If RgZeile.Value = Dt Then
      MonatFeld = RgZeile.Offset(0, Spalte% - 2).Value
      Exit For
    End If
  Next RgZeile
End Function

 

Hallo Anton,

vielen lieben Dank für Deine Hilfe. Mann o Mann, das wird langsam echt komplex. Deine Frage bezüglich KW5 ist vollkommen gerechtfertigt, das fällt mir nun auch auf :-O
Die Lösung sieht ja auch super aus, nur verstehe ich ein paar Dinge nicht:

Wenn ich auf dem Blatt "Januar" in C10 und C11 Uhrzeiten eingebe, müssten sie ja auf dem Blatt "KW1" in F15 und G15 erscheinen. Das tun sie auch, jedoch steht da in beiden Felder satt meiner eingegebenen Uhrzeit nur 00:00.

Auch wenn ich im Blatt Januar ein weiteres Datum hinzu füge, erscheint das nicht auf den KW Blättern. Du hast jedoch in allen felder schon Formeln eingegeben, die mir auch logisch sind und eigentlich funktionieren müssten,
jedoch warum werden die Werte nicht angezeigt?

Das selbe passiert auch auf dem Februar Blatt. Wenn ich am 3.2. eine Uhrzeit eingebe, steht auf dem "KW5" Blatt nur 00:00.
Wenn ich im Februar z.B. einen Tag hinzu füge (4.2.) erscheint dieser garnicht im "KW5" Blatt.

Ich glaub ich steh auf dem Schlauch :-/
Hilf mir bitte runter :-)

Viele Grüße,
Philipp

 

Hallo!

Des Rätsels Lösung ist leider etwas heimtückisch:
Ich habe eine benutzerdefinierte Funktion "DatumFeld" programmiert, die 2 Parameter hat ("Datum" und "Spalte"). Damit eine Funktion neu berechnet wird, muss sich mindestes einer ihrer Parameter geändert haben. Da sich "Datum" bzw. "Spalte" nicht ändern, wird auch die Funktion bei einer nachträglichen Änderung des Wertes im Monats-Blatt nicht neu berechnet.
Es gibt aber ein Gegenmittel, indem man "Application.Volatile True" als 1. ausführbaren Befehl in die Funktion einfügt - dann wird sie stets berechnet, wenn sich in der Arbeitsmappe sich etwas geändert hat.
Die benutzerdefinierte Funktion lautet also:

Code:

Public Function MonatFeld(Dt As Date, Spalte%) As Variant
  Dim Ws As Worksheet
  Dim MoName$
  Dim RgDatum As Range, RgZeile As Range
  Application.Volatile True    '<-- diese Zeile einfügen
  
  Dt = CLng(Dt)
  MoName$ = MonthName(Month(Dt))
  For Each Ws In Worksheets
    If Ws.Name = MoName$ Then Exit For
  Next Ws
  
  MonatFeld = ""
  Set RgDatum = Ws.Range("B9:B39")
  For Each RgZeile In RgDatum.Cells
    If RgZeile.Value = Dt Then
      MonatFeld = RgZeile.Offset(0, Spalte% - 2).Value
      Exit For
    End If
  Next RgZeile
End Function

Du müsstest also diese 1 Zeile in die Funktion einfügen oder gleich den ganzen obigen Code kopieren und anstelle der bisherigen Funktion einfügen.

 

Hallo Anton...

vielen dank für Deine wiedermal ausführliche Antwort! :-)

Erklärung hab ich verstanden - geändert - funktioniert prima :-)

Jetzt hätte ich noch eine Frage, ob das technsich möglich ist kann ich mir kaum vorstellen, aber das weißt Du sicherlich besser:

Nehmen wir an ich schreibe im Blatt "Januar" am 03.01.2011
Arbeitszeit von 08:00 bis 17:00 und 01:00 Pause.
Bisher wurde dann im Blatt "KW1" folgendes eingetragen:

F7 08:00
G7 17:00
J7 01:00

Wäre es möglich, dass der Eintrag auch folgender maßen erfolgt:

F7 08:00
G7 12:00
F8 13:00
G8 18:00
J7 12:00
J8 13:00

Die 4 Stunden bis zur Pause sind fiktiv und sollen als Standardwert genommen werden. Es spielt keine Rolle wann die Pause gemacht wurde, nur dass sie gemacht wurde.

Das heißt, wenn ich eintrage von 08:00 bis 18:00 gearbeitet + 01:00 Pause
sollte eingetragen werden:
08:00 bis 12:00 (Anfangszeit immer + 4 Stunden bis Pause)
12:00 bis 13:00 Pause
13:00 bis 18:00

Wenn im Monatsblatt keine Pause eingetragen wird, dann reicht der Eintrag wie bisher, also:
F7 08:00
G7 18:00
J7 leer

Wäre das technisch überhaupt möglich?

Viele Grüße,
Philipp

 

Hallo Philipp,

in der beiliegenden Datei habe ich im Arbeitsblatt "KW1" Deine Wünsche in den Zeilen 7 und 8 eingebaut; entsprechend müsstest Du sie in den restlichen Zeilen und Blättern einbauen.
Dabei ist zu beachten, dass ich auch in Zelle Z7 und Z8 eine Formel eingegeben habe, um den Einsatz der MonatFeld-Funktion zu minimieren.
Zelle Z7 enthält die Pause (falls): entspricht Spalte E=5 der Monatsblätter
Zelle Z8 enthält das Arbeitsende: entspricht Spalte D=4 der Monatsblätter.

 

Hallo Anton,

so schnell??? das ging ja ruck zuck!
Mann o Mann...wenn ich Dein Wissen hätte!!
Vielen Dank!

Das sieht so einfach aus, aber da fehlen mir wohl doch die Basics.

Das dürfte ich hin kriegen...ansich kapier ich die Formel, manchmal komm ich durcheinander wegen den ganzen Formeln und Verweisen, aber das wird schon :-)

Ich habe gesehen, dass egal wie ich die Zeiten ändere, die Pause nun immer um 12:00 anfängt. Ist das so von Dir gewollt oder ist das technisch nicht möglich, die Pause immer 4 Stunden nach Arbeitsbeginn anfangen zu lassen?

Viele Grüße,
Philipp :-D

 

Hallo Philipp!

Um das einzubauen:
statt der bisherigen Formel in Zelle G7:
=WENN(Z7=0;Z8;0,5)
folgende Formel dort eingeben:
=WENN(Z7=0;Z8;F7+1/6)
Da die Einheit bei der Datums-Zeit-Rechnung jeweils 1 Tag ist, entspricht 0,5 der Uhrzeit 12:00 Uhr Mittag, während 4 Stunden umgerechnet 1/6 Tag sind.

 

Hallo Anton,

lustigerweise habe ich vor 5 Minuten die letzt Frage glaub ich selbst beantwortet.
Ich hatte ein Problem, und zwar lief alles super, wenn ich in meinem
Monatsblatt die Zeiten eingegeben hatte, wenn ich aber keine zeit eingegeben
habe, dann stand in G7 und F8 immer #Wert!.

Habe daraufhin die Formel in G7 so geändert:

Code:

=WENN(Z7=0;Z8;WENN(F7="";"";0,5))

und in F8 so:

Code:

=WENN(Z7=0;"";WENN(F7="";"";G7+Z7))

Dann war der Fehler weg. Ich hoffe die Lösung ist gut und richtig?

Um das Zeitproblem zu lösen hab ich lang rum probiert und die Formel in G7 nun so geändert:

Code:

=WENN(Z7=0;Z8;WENN(F7="";"";F7+240/(24*60)))

Funktioniert jetzt prima mit den 4 Stunden, jedoch wäre Deine Lösung mit 1/6 wohl kürzer gewesen ;-)

Viele Grüße,
Philipp

 

Hallo Philipp,

Du hast natürlich recht, ich hatte im Eifer des Gefechtes nicht an die Möglichkeit gedacht, dass auch überhaupt nichts drinnen stehen kann in den Zellen.

 

Hallo Anton,

*lach* ist ja kein Problem - funktioniert jetzt prima :-)
Ich bastle mal die ganzen anderen Monate dazu und schau
ob dann alles läuft. Das wird aber eine Weile dauern.
Falls ich irgendetwas überhaupt nicht blicken sollte, schreib ich hier nochmal.

Ansonsten sag ich schonmal vielen vielen Dank für die tolle Hilfe!!! :-D
Von Tag zu Tag macht mir Excel mehr Spaß :-)

Viele Grüße,
Philipp

 

Hallo Anton,

Du hast mir vor ein paar Wochen bei diesem Thema hier wunderbar geholfen.
Mittlerweile habe ich die komplette Tabelle bestehend aus 12 Monatsblättern und 52 KW Blättern sogut wie fertig. Es ist mir jedoch ein Problem aufgefallen wobei Du mir eventuell noch einen Tip geben könntest ob dies zu lösen ist.

Wenn ich in der Januar Tabelle (welche ja für mich als Übersicht ist) meine Arbeitstage eingebe und dann die Tabellenblätter KW1 - KW4 (für meinen Arbeitgeber) ausdrucken will, dann ist das ja "relativ" einfach - Ich gehe auf jedes Blatt und sage drucken. Wenn ich jetzt aber im z.B. Juni 14 nicht aufeinander folgende Tage gearbeitet habe, muss ich immer die entsprechenden KW Tabellenblätter raussuchen und dann einzeln ausdrucken.

Ist es eventuell möglich, dass man auf dem Monats-Tabellenblatt einen "DRUCKEN" Button erstellt und mit einem Makro einen Befehl gibt, dass nur die KW Tabellenblätter automatisch ausgedruckt werden, auf denen Daten für den entsprechenden Monat eingetragen sind?

Also wenn ich z.B. im Januar Tabellenblatt auf "drucken" klicke, und nur in z.B. KW3 gearbeitet habe, soll auch nur KW3 ausgedruckt werden und wenn ich in KW 2 und 3 gearbeitet habe, soll nur KW2+KW3 ausgedruckt werden etc.

Ist sowas möglich?

Viele Grüße,
Philipp

 

Hallo Philipp,

händisch kann man gleichzeitig mehrere Arbeitsblätter auswählen und, indem man dann die Seitenvoransicht wählt, kann man sich diese Arbeitsblätter in einem Aufwaschen voranschauen bzw. ausdrucken.
Diesen Vorgang kann man auch automatisieren durch folgende 2 Makros (Sub+Function):

Code:

Public Sub MonatDrucken()
  Dim Ws As Worksheet
  Dim RgDatum As Range, RgZeile As Range
  Dim Gefunden As Boolean, IW%, I%, Dt As Date
  Dim KW As Integer, WoBl$, Wochen() As Variant
  ReDim Wochen(6)
  
  IW% = -1
  Set Ws = ActiveSheet
  Set RgDatum = Ws.Range("B9:B39")
  For Each RgZeile In RgDatum.Cells
    If Not IsEmpty(RgZeile.Value) Then
      KW = KalWoch(RgZeile.Value)
      WoBl$ = "KW" & KW
      Gefunden = False
      For I% = 0 To IW%
        If Wochen(I%) = WoBl$ Then Gefunden = True: Exit For
      Next I%
      If Not Gefunden Then
        IW% = IW% + 1
        Wochen(IW%) = WoBl$
      End If
    End If
  Next RgZeile
  ReDim Preserve Wochen(IW%)
  Sheets(Wochen).PrintPreview
End Sub
Function KalWoch(Dt As Date) As Integer
   Dim Wt As Integer
   Dt = Dt - 2: Wt = Dt Mod 7
   KalWoch = (Dt + 2 - DateSerial(Year(Dt + 5 - Wt), 1, Wt - 9)) \ 7
End Function

Installation:
Diese 2 Makros markierst Du mit der Maus, kopierst sie und fügst sie im VBA-Editor in dasselbe Modul ein, in dem Du auch bereits die "MonatFeld(..)"-Funktion stehen hast.
Eine wichtige Einstellung ist dabei zu beachten: Das Makro "MonatDrucken()" nimmt an, dass der Bereich mit dem Tagesdatum sich in B9:B39 der Monatsarbeitsblätter befindet. Falls sich da etwas geändert haben sollte, müsstest Du diese Makrostelle entsprechend anpassen.

Anwendung:
Wenn Du einen bestimmten Monat ausdrucken/voransehen willst, gehst Du in das Arbeitsblatt dieses Monats, drückst die Tastenkombination Alt+F8, gefolgt von der EINGABE-Taste (bzw. Auswahl von "MonatDrucken" und Button "Ausführen").
Daraufhin werden die Arbeitsblätter für die betreffenden Kalenderwochen durchgehend als 1 einzige Voransicht (mit mehreren Seiten) angezeigt.
Diese Voransicht kannst Du natürlich auch audrucken. Die einzelnen Druckbereiche für Kalenderwochenarbeitsblätter müssen natürlich korrekt eingestellt sein.

Anmerkung:
Die Funktion "KalWoch(Dt As Date) As Integer" ermittelt aus der Datumsvariable "Dt" die Kalenderwoche (als Natürliche Zahl zwischen 1 und 53). Diese Funktion kannst Du auch in den Arbeitsblättern verwenden.
Falls in Zelle B10 ein Datum steht, ermittelt die Formel "=KalWoch(B10)" die Kalenderwoche für dieses Datum.

 

Hallo Anton,

vielen Dank für Deine schnelle und wiedermal ausführliche Antwort :-D

Ich habe nun die 2 Makros im VBA Editor eingefügt. Der Bereich mit dem Tagesdatum in den Monatsblättern
ist auch bei B9:B39 geblieben und hat sich nicht verändert.

Wenn ich jetzt jedoch mit Alt+F8 drucken will, erscheint "Laufzeitfehler 13 - Typen unverträglich"
Hab ich die Makros vielleicht doch an der falschen Stelle eingefügt?

Müsste man dem Makro nicht irgendwo noch hinzufügen, dass er auf den KW Blättern dann nur jeweils
den Bereich B1:V22 druckt, sonst werden ja die "Berechnungsspalten" Y und Z mit ausgedruckt, oder?

Ich habe nochmal schnell die Monate Januar+Februar + den KWs als Tabelle angehängt.
Vielleicht habe ich ja doch nur den code falsch eingefügt.

Ãœbrigens ist mir noch ein Problem aufgefallen.
Das mit der Berechnung der Pausenzeit funktioniert ja prima.
Nach 4 Stunden wird immer die angegebene Pause gemacht, so wie es sei soll.
Jedoch gibt es ein Problem wenn ich mit Pausenzeit unter 4 Stunden arbeite.
Ich habe mal das Problembeispiel auf dem Blatt KW1 rot markiert.

Oh Mann....Fragen über Fragen....tut mir ja echt leid dass ich Dich hier so voll Texte
und Deine kostbare Zeit in Anspruch nehme. Sorrryyyy!!

Viele Grüße & vielen Dank,
Philipp