Office: Zellbezug beim Kopieren von Zeilen

Guten Abend,

ich führe eine Dienstplan Datei in Excel für zahlreiche Mitarbeiter. Die ist wie folgt aufgebaut:
Kopfzeile mit Namen und Anzahl der Arbeitsstunden, darunter pro Tag eine Zeile mit Dienstzeiten, am Ende Rechnung Stunden und Urlaubstage, etc.

Wenn ich einen neuen Monat anlege, kopiere ich das ganze und füge es darunter wieder an. Dann nehmen die neuen Zeilen den Bezug nach oben.

Die Formel, welche die Arbeitsstunden pro Tag berechnet, eben auch bei Urlaub und Krankheit beziehen sich auf den Kopf, Zelle Arbeitsstunden.

Wenn ich die "Tabelle" kopiere, bleibt der Zellbezug ohne manuelle Änderung immer bei der ersten Eingabe in der Datei. ($)

Wie kann ich pro kopierter "Tabelle" den Bezug so einstellen, dass der Bezug dann auf genau das gleiche Feld, aber auf dem der neuen Tabelle entsteht? (Die Werte ändern sich je Monat immer mal...)

In Bezug auf die Datei, in dieser Tabelle beziehen sich die Felder F9 bis F18 auf F4.
In der kopierten Tabelle soll es sich dann auf F xx beziehen.

Die Formel lautet:

=WENN(D174<E174;ABS(E174-D174);WENN(D174>E174;((24-D174)+E174);WENN(D174=99;F$4;WENN(D174=98;F$4;""))))

Für Eure Rückmeldungen danke ich.

Sabrina2311

 

Ein Screenshot bringt da nix - poste mal eine Datei dazu.

 

Kann es auch eine VBA Lösung sein?

LG Armin

 

Moin Sabrina,

etwas überrascht, dass Dir noch keiner der alten XL-Hasen hier über den Weg gehoppelt ist, der Deine Mappe mal gleich nach dem Motto zerlegt hat, dass man sowas doch nicht mit Mappe pro Monat oder Copy 'n Paste macht, sondern dazu eine Datenbasis erstellt und diese dann verarbeitet.

Diese tatsächlich vernünftige Herangehensweise bedeutet leider häufig, dass lange gewachsenen Projekte mehr oder weniger komplett über den Haufen geschmissen werden müssten und neu aufgebaut, was nicht jede(r) so gern mag. Allerdings entspricht sie dem guten alten EVA-Prinzip der EDV: Eingabe, Verarbeitung, Ausgabe, vor dem man sich besonders bei größeren Datenmengen nicht drücken sollte.

Fange ich also mal mit der Eingabe an: Bewegungsdaten: Welche Daten erhälst Du pro Tag und Mitarbeiter (Arbeitsbeginn/Ende/Pausen, Fehlgründe Urlaub, Fortbildung, Krank [#1]) und welche Stammdaten führst Du: MA-Nummer, Abteilung, Soll-AZ je Wochentag, Urlaubsanspruch, Fortbildungsplan, usw. [#3]). Wichtig, wie werden die Daten erfasst? Bekommst Du Werte aus Zeiterfassungssystem oder musst Du von Hand erfassen? [#4]
Der erste Teil der Verarbeitung ist lediglich der Vorgang die Daten strukturiert in Deine Datenbasis zu schreiben. Der zweite Teil hängt dann davon ab, was Du aus den Daten für Schlüsse/Informationen ziehen willst. Das kann man mit automatisierten oder einzeln konfigurierbaren Abfragen erledigen: Tages/Wochen/Monatsbericht gesamt/je Abteilung usw. Welche Auswertungen brauchst Du wann? [#5]
Die Ausgabe erfolgt dann einfach in ein leeres Arbeitsblatt, das als PDF oder Mailverteiler weitergegeben wird. Was hätten's denn gern? [#6]

Die [#] dienen als Marker für Deine detaillerte Antwort.

@EbyAS / Pumuckel hat oben schon die Frage nach VBA gestellt. Du hast selbst aber schon mit der ersten naheliegenden Technik, den Formeln angefangen. VBA ist aber auch m.E. der beste Weg, denn die Formeln werden in einer Weise übersetzt, die Deine Daten und Berechnungen vor un/beabsichtigten Fehlern durch Dich/andere Benutzer sicher schützen. Zudem bekommst Du damit z.B. die Möglichkeit Ein/Ausgabe in Formularen zu organisieren.

Nur so als kleine Fantasie: Du schmeisst morgens Deine Arbeitsmappe an und Dich begrüßt ein Formular mit einer Liste der MA, die am Vortag zur Arbeit erwartet wurden. In der Liste lässt Du alle erfüllten Pflichten völlig unangetastet, änderst nur Fehl/Überzeiten - und fertig. Löschst Du z.B. den Arbeitsbeginn, fliegt das Ende gleich mit raus: Der Kollege war nicht da. Geplante/genehmigte Abwesehenheiten werden automatisch berücksichtigt. Ist dafür ein Tages/Wochen/Monats/Abteilungs / persönlicher Bericht fällig, werden diese automatisch als neues Arbeitsblatt erzeugt. Wär das was?

In VBA leicht zurealisieren (auch wenn Du (noch) kein Visual Basic for Applications kannst) und sowohl skalierbar, als auch zukunftssicher.

Lass wissen
Liebe Grüße

 

Hi

gehe so vor:
1. stelle sicher (bspw durch Formatierung), dass jeder Monat die selbe Anzahl an Zeilen belegt, beispielsweise immer 40 Zeilen also:
Januar: 1-40
Februar: 41-80
März 81-120
April: 121-160
usw.

in diesem Aufbau steht dann dies Stundenanzahl in:
Januar: F4
Februar: F44
März: F84
usw.

damit könntest du dann den Bezug auf folgende Weise berechnen:
esetze also das F$4 durch: Index(F:F;Untergrenze(Zeile()-1;40)+4)

das Untergrenze ist ein Abrunden auf ein Vielfaches des angegebenen Wertes, dh für alle Zeilen zwischen 1 und 40 bekommst du 4, für alle Zeilen zwischen 41 und 80 dann 44, für alle Zeilen zwischen 81 und 120 dann 84 usw

Gruß Daniel

 

Hallo,
mit einer Mustertabelle könnte ich eventuell auch helfen.
Wenn diese natürlich zu viel Arbeit macht, bin ich raus.

MfG Günter

 

Guten Abend, erstmal vielen lieben Dank für die Hilfe. Ich denke, ich habe es verstanden. Allerdings geht es um ca. 50 Mitarbeiter, bei denen ich dann jeden Monat die erste Formel manuell ändern und für alle Tage kopieren müsste. Dann könnte ich auch manuell jeden Monat auf die andere Zelle umstellen.

 

WO ist die Datei ?

 

Guten Abend, vielen Dank für diese umfangreiche Antwort. Leider sagt mir die ganze Thematik nichts und ich müsste mich erstmal damit befassen. In der Datei arbeite ausschließlich ich. Am Ende gebe ich die einzelnen Seiten als PFD Druck Version weiter. Dass soll auch so bleiben. Alles andere wie Überstunden muss ich ohnehin manuell erfassen, da diese bei uns in Papierform eingereicht werden. Geleistete Stunden und alles andere berechnen sich automatisch aus den Dienstzeiten. Mir geht es lediglich darum, dass ich die Formeln bei Änderung der durchschnittlich täglichen Arbeitszeit manuell ändern muss und möchte erreichen, dass sich die Formel pro Blatt auf die jeweilige Zelle auf genau diesem Blatt bezieht und nicht auf die Blätter vom Dateianfang.

LG Sabrina2311

 

Ich verstehe die Frage nicht. Es handelt sich um eine Excel Datei, welche auf meinem Laptop gespeichert ist. - auf die Frage von Onur -

 

Entschuldigung, jetzt habe ich es verstanden. Anbei die Datei.

 

Und was genau soll die Formel unten jetzt berechnen?
=WENN(D174<E174;ABS(E174-D174);WENN(D174>E174;((24-D174)+E174);WENN(D174=99;F$4;WENN(D174=98;F$4;""))))
Und wozu dient die 98 bzw 99 ???

 

Das ist die Kennzeichnung von Urlaub oder Krankheit. Durch die Formel werden automatisch die Stunden gerechnet, welche ich jetzt in der Datei rot markiert habe. In der jetzigen Datei ist der Bezug bei Mitarbeiter x in den Formeln von F9 bis F39 zu F4. Also im Blatt Januar. Ich möchte aber, dass Excel im Februar, also für die Zellen F65 bis F92 den Bezug von F4 auf F60 verschiebt.

 

Welche Excel-Version hast du denn ??

 

Wieso ? Ändern sich denn die Sollstunden pro Tag monatlich `?