373 Aufrufe
Gefragt in Tabellenkalkulation von
Hallo ihr fleißigen Helfer!

Ich habe einen Kalender mit Excel erstellt mit 12 Blättern, für jeden Monat eins. Jeder Monat beginnt mit dem ersten des Monats. Die Daten aller Blätter (Datum, Wochentag) werden automatisch übernommen von dem Januarblatt bei Eingabe der Jahreszahl.
Ich möchte, dass in den Blättern die Stunden der Wochentage, also Montag bis Sonntag angezeigt werden. Da sich aber die Wochentage natürlich in jedem Jahr in der Reihenfolge der Spalten ändern, muss ich jedes Jahr den Bereich in den Blättern ändern, wo mir eben Mo - So die Arbeitsstunden angezeigt werden sollen.

Meine Frage, kann man das automatisieren, dass sich die Wochenstundenanzeige an die Änderungen des nächsten Jahres anpassen?

Beste Grüße

17 Antworten

+1 Punkt
Beantwortet von raisix Mitglied (287 Punkte)
Hallo,

sind die die Summen der einzelnen Wochen sehr wichtig?

Ansonsten macht die Lösung von Steffen Hanske genau das was du benötigst. Nur ohne Wochensummen dafür aber Monatssummen.

Die Regelarbeitszeit kann dort sehr individuell eingestellt werden.

Wenn du auf eine Umsetzung in deiner Vorlage bestehst, würde ich die Regelarbeitszeit der Tage bevorzugt mittels der Formel SVERWEIS bereitstellen anstatt mittels VBA. Ich habe nichts gegen VBA, aber nur wenn man es nicht oder nur schwer mittels Formeln lösen kann.

Die Wochensummen, würde ich neben den Tagen ausrechnen, da es bei der variablen Datumsanzeige schwierig ist, eine Summenspalte unter den jeweiligen Wochen darzustellen. Hierfür kann man bestimmt etwas mit der Formel KALENDERWOCHE machen.

Beste Grüße
0 Punkte
Beantwortet von twototoulouse Mitglied (370 Punkte)
Hallo Raisix

Super, vielen Dank für die Tipps, versuche das mal umzusetzen.Mit den Wochensummen wäre schon wichtig, Gute Idee sie neben die Tage zu setzen.

Hallo Karin!

Eine Option wäre noch, das Gerüst des Kalenders mit den Wochentagen (Mo-So) und den Summenzeilen fest so stehen zu lassen und nur das Datum des jeweiligen Monats an die Wochentage anpasst.

Also wenn der 1. des Monats z.Bsp. ein Mittwoch ist, dann fängt der Kalender eben erst, hier im Januar, in der Zelle A 11 an. Darüber die Zellen  neben Montag und Dienstag  bleiben dann eben leer. Dann müsste ich noch eine Woche unten anhängen, das wäre ja kein Problem.

Beste Grüße und vielen Dank für Eure Mühe
+1 Punkt
Beantwortet von beverly_ Experte (3.3k Punkte)

Hi,

die Zeilen für die Wochen-Summen zu verschieben ist bei deinem Tabellenaufbau schwerlich möglich, da du verbundene Zellen in Spalte A verwendest - man müsste dazu nämlich die komplette Tabelle immer wieder neu erstellen. Man könnte aber die Startzeile für den Monatsersten variabel gestalten - d.h. für das Jahr 2023 würde der 01.01. in A15 stehen. Es müsste aber noch eine weitere Summen-Zeile unten ergänzt werden, da sonst die Zellen für die letzten Monatstage nicht reichen würden. Außerdem ist dein Mappenaufbau kontraproduktiv, denn Tabelle Januar ist anders aufgebaut als die anderen Blätter - dort muss oberhalb der eigentlichen Tabelle jeweils 1 Zeile eingefügt werden, damit sie identisch aufgebaut sind.

Der Code wird ausgeführt, wenn du in Tabelle Januar das Jahr änderst - es werden alle Tabellen durchlaufen und die Spalten A und B mit den Tagen des jeweiligen Monats gefüllt.

Deine Formeln in den Summen-Zeilen kann man wesentlich einfacher schreiben - man muss nicht jede Zelle prüfen ob sie leer ist, es reicht zu prüfen, ob die Anzahl an Zahlen im Bereich 0 ist.

Außerdem habe ich die Formel für die bedingte Formatierung in den Spalten A unb B geändert, damit leere Zellen richtig erkannt werden.

Die Formeln für die Überträge vom Vormonat müssen ebenfalls angepasst werden, da die Zeile mit der Monatssumme nun eine andere ist - für die meinsten Monate sollte ich das korrigiert haben, überprüfe es allerdings nochmal.

https://supportnet.de/forum/?qa=blob&qa_blobid=5508134914626118727

Bis später, Karin

0 Punkte
Beantwortet von twototoulouse Mitglied (370 Punkte)
Hi Karin,

mir fehlen die Worte, dass Du Dir soviel Mühe gemacht hast.

Genau so hatte ich mir das zuletzt gedacht.

Da kann man garnicht genug danken. So werde ich das übernehmen.

Vielen lieben Dank für Eure Hilfe.

Mit besten Grüßen
+2 Punkte
Beantwortet von xlking Experte (1.5k Punkte)

Hallo zusammen,

auch von meiner Seite noch ein Lösungsvorschlag. Hatte ich bereits am Freitag erstellt, kam aber noch nicht dazu den zu posten. Schau dir mal diese Datei an:

https://supportnet.de/forum/?qa=blob&qa_blobid=10083514547986400880

Hier wird dein ursprüngliches Format verwendet und in jeder Eingabezelle prüft eine Formel, ob eine Zwischensumme angezeigt werden soll oder nicht. Damit ist die Zeilenposition variabel. Die Zwischensumme selbst wird über bedingte Formatierung fett und mit Rand dargestellt. Das Datum in A9 muss nur der Erste des jeweiligen Monats sein. Alles Andere wird berechnet. Für die Eingabe von Daten können die Formeln problemlos überschrieben werden. Aber bitte nur, wenn eine graue Null angezeigt wird.

Ganz ohne Makros gings dann aber doch nicht. Da du ja sicher nicht willst, dass versehentlich an der falschen Stelle Formeln überschrieben werden. Einfach auf den Button im Januar klicken, dann prüft das Makro in allen Monaten ob es sich um eine Summenzeile handelt. Wenn nicht, werden die Zellen als Eingabezelle gesetzt und dann das Blatt geschützt. Passwort zum Entschützen ist aktuell "Kalender". Kannst du aber gern im Code ändern.

Bei Änderung des Jahres, werden die Formeln aus Zeile 47 bis hoch nach Zeile 9 kopiert. Dabei kommt es unweigerlich zum Überschreiben evtl. bereits getätigter Eingaben. Deshalb gibt's vorher eine Frage ob man das will. Wenn du hier Nein sagst, bleiben die Eingabedaten zwar erhalten, passen jedoch nicht mehr zu den Datumsangaben und Summenzeilen.

Auch, wenn es vielleicht jetzt keine Rolle mehr spielt. Probier's mal aus, wenn auch nur aus Interesse. Ist vielleicht eine Alternative für künftige Projekte.

Gruß Mr. K.

0 Punkte
Beantwortet von raisix Mitglied (287 Punkte)
Hallo xlking,

wirklich eine sehr tolle und geniale Lösung!

Beste Grüße
0 Punkte
Beantwortet von twototoulouse Mitglied (370 Punkte)
Hallo xlking!

Unglaublich, vielen Dank für Deine Mühe!

Das ja mehr als eine interessante Lösung. Soviel Arbeit, vielen Dank! Diese Alternative wird auf jeden Fall von mir übernommen.

Mit besten Grüßen
...