Supportnet Computer
Planet of Tech

Supportnet / Forum / Tabellenkalkulation

Datumsabfrage über mehrere Blätter





Frage

Hallo, ich suche eine Lösung, weiß aber noch nicht wie ich es realisieren kann. Hier wurde mir bisher immer weitergeholfen, deshalb versuche ich mein Glück einmal hier. Grundlage. Ich habe eine Excel Mappe mit 52 Blättern (1 Blatt pro KW) Nun benötige ich ein weiteres Blatt, mit einer Monatsübersicht. Das Datum in den Blättern ist so festgelegt, dass die Tage (Mo-So) (Zeile E) pro Woche imer fix sind, nur jedes Jahr eben das Datum (Zeile F) wechselt. z.B. In KW 1 für 2008 steht am Montag 31.12.2007. Dies ist auch meine Grundlage (F14) für meine Datumsberechnung. Alle weiteren Tage errechnen sich folgendermassen =F14+1 etc. So, wie bekomme ich nun eine vernünftige Abfrage hin, dass ich auf der Monatsübersicht für Januar auch nur die Tage vom Januar und in der Monatsübersicht vom Februar auch nur die Tage vom Februar gelistet bekomme ? Das wäre einmal die esrste Hürde, die ich nehmen müsste. Gruß Blacky

Antwort 1 von Marie

Ich versteh nicht recht was Du willst?? Willst Du jetzt noch 12 Monatsübersichten anlegen? Und was willst Du denn da berechnen? Excel ist so intelligent, dass du nur in die oberste Spalte ein datum eintragen musst oder Mo Di Mi oder was immer Du willst und dann das Feld an der rechten unteren Ecke runterziehen so weit es nötig ist. Also kopier in Dein Januarblatt, wenn das am 1. Januar anfangen soll, den 1. januar rein, wenn das per Code sein muss, dann such den halt und nimm den Wochentag dazu. Dann ziehste entweder händisch beide zellen jeweils runter bis du an den 1.2. kommst, den verschiebste dann mitsamt dem Wochentag in das Februarblatt, erste Zeile, ziehst dann wieder runter bis zum 1.März usw.

Oder muss es Code sein? Wenn ja musste bitte genauer beschreiben ob das 12 Blätter sein müssen, die erst angelegt werden müssen oder ob die bereits vorhanden sind oder was Du genau machen willst.

Gruß Marie

Antwort 2 von 090365

Hallo,

genau, ich muss 12 weitere Monatsblätter einfügen.

Mein Datum steht in einer Zeile pro KW. Da der 01.01. nicht immer ein Montag ist, kann ich also nicht nach Wochentagen suchen, sondern muss die Zeile nach dem 01.01. abfragen.

Wenn ich ihn gefunden habe benötige ich Werte aus der Spalte
in der dieses Datum steht.

Diese müssen dann in die neue Monatsübersicht.

Gruß

Blacky

Antwort 3 von Blackv

Prost Marie - Springmaus

Antwort 4 von Marie

@Blacky: Du hast doch morgen Schule, also hör auf zu prosten

Bist Du das hier, der fragt??? Oder ist das ein anderer Blacky???

Ja, is doch einfach, sichst nach dem Datum und den Wochentag nimmste dann mit, der steht doch daneben. hab ich doch genauso geschrieben oben.

Nu sag, ob Du das nur einmal machen musst, dann haste das mit Kopieren des ersten jeden Monats und Runterziehen in 5 Minuten. Oder ob Du das öfter brauchst und deshalb mit Code machen musst.

ferner hatte ich bereits oben schon gefragt, ob die Tabellenblätter schon da sind oder per Code angelegt werden müssen.

Also nu gib mal konkret Antwort, soll ich Dir code bosseln oder nein oder wat???

Gruß Marie

Antwort 5 von 090365

Hallo,

irgendwie sind die Antworten, die gestern hier waren leider verschwunden.

Da ich mich wohl sehr unklar ausgedrückt habe,
habe ich einmal eine Beispieldatei zum Download bereitgestellt.

http://www.schwarz-strickner.de/vorlage.zip

Dort ist ein Blatt Januar eingefügt.
Diesen Monat und weitere möchte ich aus der bestehenden
Vorlage automatisch ausfüllen.
Ich darf keine Makros benutzen.

Mein Problem ist, dass sich das Datum in Zeile 14 jedes Jahr in
einer anderen Zelle befindet.

Vielleicht ist nun mein Frage klarer formuiert.

Gruß

Blacky

Antwort 6 von Aliba

Hi Blacky,

hab dir mal ein paar Formeln geändert.

Als erstes musst du nun in Projektübersicht heisst das glaube ich immer den 1.1.des entsprechenden Jahres eintragen.
In KW 01 habe ich dir erläutert, wo ich Formeln geändert habe. Das muss für alle weiteren KW-Tabellen gemacht werden.
Hätte ich dir auch gemacht, aber irgendein Tabellenblatt hatte Zellschutz, so daß die Änderungen nicht gleichzeitig gemacht werden konnten und ich nicht soviel Zeit habe.
Musst Du halt selbst erledigen.
Die Datumsberechnung in den weiteren Blättern brauchst Du natürlich nicht zu ändern.

In Dein januarblatt habe ich eine Hilfszeile und 2 Hilfsspalten eingetragen. Diese sind nur für die Vereinfachung und Übersichtlichkeit der Formeln. Die können eingeblendet werden.
Habe dir auch noch das Februarblatt erstellt, wegen den im Schaltjahr wechselndem Datum.

Um die anderen Monatszusammenfassungen zu erstellen, musst Du nur das Januarblatt kopieren, bei den Monaten mit 30 Tagen die letzte Datumszeile slächen und die Formel in D4 auf das vorherige Monatsblatt zu ändern.

Also guck mal hier:
http://www.netupload.de/detail.php?img=76ce87e395a262b10e70299f0171...

CU Aliba

Antwort 7 von 090365

Hallo Aliba,

das sieht ja schon ganz gut aus.

Aber ich habe es leider noch nicht ganz verstanden.
Wenn ich im ersten Blatt z.B. 01.01.2008 eintrage,
dann stimmen die Abrfragen der neuen Blätter nicht mehr.
Es kommt #NAME? in allen Spalten außer dem Datum und Wochentagen.
Wobei bei Februar 2008 am 298.02. ein Zahlenwert statt dem
Wochentag stehtsteht.

Vielleicht bin ich nur zu beschränkt und mache was falsch.

Gruß

Blacky

Antwort 8 von Aliba

Hi Blacky,

könnte sein daß die von mir verwendete Formel KALENDERWOCHE ein Add-in ist.

Porbiere mal folgendes:

Unter EXTRAS - Add-in-Manager

sollte ein Eintrag stehen "Analyse-Funktion"
Hier mal einen Haken setzen und Excel gegebenenfalls neu starten.

Wenn Du dann darauf hin gewiesen wirst, daß du das nicht darfst (Rechte) oder die Analyse-Funktion nicht installiert ist, dann noch mal melden.

CU Aliba

Antwort 9 von 090365

Hallo,

da muss ich mich noch mal melden.
Das oben Beschriebene brachte keine Besserung.

Wäre klasse, wenn das mit Standardeinstellungen
funktionieren würde.

Gruß

Blacky

Antwort 10 von Aliba

Hi Blacky,

du konntest die Einstellungen ändern, hast Excel geschlossen und noch einmal geöffnet und hast immer noch die Fehlermeldungen NAME ???

CU Aliba

Antwort 11 von 090365

Hallo Aliba,

ja ich konnte es ändern, aber es hat sich an der Berechnung nichts geändert. Ich habe Excel auch neu gestartet.
Mir wäre es recht, wenn ich nichts ändern muss, da es auf verschiedenen Rechnern mit unterschiedlichen Usern laufen muss.

Gruß Blacky

Antwort 12 von Aliba

Hi Blacky,

also gut dann hier ohne KALENDERWOCHE:

http://www.netupload.de/detail.php?img=c6272ceae299850fc8d241def6d7...

CU Aliba

Antwort 13 von 090365

Hallo Aliba,

ich verneige mich vor dir.

Wahnsinn, auf den ersten Blick ist das genau das was ich
gesucht habe.

Meine Excelkenntnisse reichen leider nicht aus, um
deine Berechnungen nachzuvollziehen.

Dann nehme ich es einfach mal so hin und freue mich,
das es funktioniert.

Werde mich heute Abend mal damit auseinandersetzen
und das Ding in eine endgültige Form bringen.

Wie es aussieht, muss ich die Änderungen in KW01 in keinem
anderen Blatt mehr vornehmen. Das Datum wird anscheinend
richtig berechnet. Ist mir auch recht.

Erst einmal herzlichen Dank

Gruß

Blacky

Antwort 14 von 090365

Hallo,

und schon wieder ich.

Habe was bemerkt.
Du hast in KW 01 meine Datumseingabe geändert.

Nun beginnt aber KW01 im Jahr 2010 am 04.01.2010.
Dann stimmen die Berechnungen in den Monatsübersichten nicht.

Sch... , dachte schon es wäre geschafft.

Hast du dafür vielleicht noch eine Idee ?

Gruß

Blacky

Antwort 15 von Aliba

Hi Blacky,

ursprünglich wollte ich Dich eigentlich nach Deiner Definition der Kalenderwochen fragen. Ob Du eben die Kalenderwochen nach DIN-Norm benötigst. Da jedoch deine Tabellenblätter nur von KW01 bis KW52durchnummeriert waren, bin ich davon ausgegangen, daß Du nicht die KW lt. DIN-Norm benötigst.

Die Berechnungen in den Januar - Dezember - Zusammenfassungen sind dennoch richtig, da Du ja hier wissen willst, was an genau diesen Datumswerten passiert ist.

Es ist natürlich auch kein Problem, das Ganze nach DIN Norm zu machen. allerdings ist dann jetzt die Frage:

Wie soll Dein Jahr den dann betrachtet werden? Soll dann das erste Tabellenblatt z.B: KW 53 heißen , oder soll das erste Kalenderwochenblatt weiterhin KW 01 heissen und dann für z.b. 2010 erst am 4.1. beginnen??
Das würde aber auch heißen, daß Dein Januarblatt auch erst mit dem 4.1. beginnt.

Also bitte weitere Erklärungen, dann wird das schon.

CU Aliba

Antwort 16 von 090365

Hallo Aliba,

so, jetzt kommen wir zur Grundsatzfrage, die sich bis dato noch nicht gestellt hat.
Diese Zeiterfassung habe ich damals gebastelt, als ich noch völlig unbedarft
in Excel war und habe eine völlig vermurkste Vorlage abgeändert.

So wie ich das sehe, hat man die Kalenderwochen nicht errechnet, sondern als festen Zahlenwert eingetragen. Besser wäre wohl, das aus dem Datum abzufragen.
Ich denke an Jahr mit einer KW zu starten, in der der 01.01. enthalten ist, ist die saubere Lösung.
Dann muss ich halt in jedem meiner Blätter das Feld mit der KW ändern.

Meine Monatsübersicht sollte unabhängig von der KW natürlich am 01.01. beginnen.

Man, da schlage ich ja gleich zwei Fliegen mit einer Klappe wenn das funktioniert.

Danke für die Unterstützung.

Gruß

Blacky

Antwort 17 von Aliba

Hi Blacky ,

mit ein paar Einschränkungen/Änderungen ist das wohl machbar.

Hierzu eine Frage:

Ist es zwingend notwendig, daß die Blattregisternamen die Kalenderwochen sind??

Ich würde davon abraten und wenn machbar diese z.B.
Woche1 bis Wochex nennen.

Es gibt nämlich Jahre, in denen die KW 52 zweimal vorkommen, dann gibt es schon das Problem , daß doppelte Registerblattnamen nicht gültig sind.

In den Tabellenblättern selbst steht dann natürlich die richtige Kalenderwoche lt. DIN.

Wenn Du nicht jedes Jahr hier händisch tätig werden willst, halte ich das für angebracht.

Desweiteren genügen 52 Tabellenblätter für ein Jahr meist nicht. In 2012 z.B. werden 54 benötigt. Das sollte dann aber das Maximum sein. Der 1.1. fällt auf einen Sonntag und 2012 ist ein Schaltjahr.

Wenn die Datei für alle diese Gegebenheiten gerüstet ist, musst Du lediglich beim Jahreswechsel einen Zellwert, nämlich das Startdatum im 1. Tabellenblatt ändern und Du hast wieder eine funktionierende Mappe für das neue Jahr.

Desweiteren könnte man auch die für Euch gültigen Feiertage berechnen und automatisch in den Wochenblättern markieren und auch eintragen lassen.
Man könnte sich auch ein Kürzel für die beiden Sondertage Heiligabend und Silvester überlegen und entsprechend die Arbeitszeiten richtig berechnen lassen.

Wie gesagt einziger Knackpunkt momentan die Blattregisternamen.

CU Aliba

Antwort 18 von 090365

Hallo,

Waaaaahnsinn.......

Man das ind ja irre Informationen.

Das mit der Anzahl der Kalenderbläter habe ich auch schon überlegt,
wollte nur keinen Doppelpost machen.

Natürlich kann man die Blattregister umbennen. Der Mitarbeiter druckt
das Blatt aus und unterschreibt es. Da ist eben die KW wichtig.

Die Lösung mit den Feiertagen habe ich ich mal angedacht und in einem
Kalender realisiert. An diese Mappe habe ich mich aber noch nicht herangewagt.

Das mit Heiligabend und Silvester habe ich bis dato immer händisch eingetragen.

Momentan ist das wichtigste, dass ich diese Monatsübersicht irgendwie realisieren kann.

Gruß

Blacky

Antwort 19 von Aliba

Hi Blacky,

hier die neue Datei:
http://www.netupload.de/detail.php?img=3f5cf29547da5019471649b2e4b7...

Was habe ich geändert:

- neue Tabellenblätter eingefügt
Woche53
Woche54
Feiertage
12 x Monatsübersicht

- Registerblätter umbenannt in Woche1 bis Woche54

- bedingte Formatierung in den Wochenblättern eingefügt zur Markierung von Feiertagen

- Formeln eingetragen zur automatischen F - Kennzeichnung der Feiertage

- Im Tabellenblatt Feiertage die Feiertagsformeln eingetragen
(hier musst Du laut Anleitung im Tabellenblatt noch die Feiertage mit "ja" kennzeichnen, bzw. die Kennzeichen rausnehmen.

- Formeln in den Monatsübersichten

- Formeln für die KW-Darstellung in Projektzeitübersicht.

ACHTUNG!!! Du musst lediglich in Projektzeitübersicht in Zelle E6 das Datum des Jahresersten eintragen.

Alles andere ändert sich entsprechend des Datums.

Was ich jetzt nicht angelangt habe, sind die Diagramme und die Jahresübersicht. Diese gehören ergänzt, da ja das alles nicht mehr passt mit den Kalenderwochen.

Deine Berechnungen innerhalb der Wochenblätter habe ich mir auch nicht detailiert angeschaut.

Wegen der beiden Sondertage musst Du noch mal das Handling/die Bedingungen erklären.

CU Aliba

Antwort 20 von 090365

Hallo Aliba,

das ist ja super, ich wollte dir noch anbieten, die Arbeitsmappe vorzubereiten und alle Blätter anders zu benennen. Nun hast du dir auch noch diese Mühe gemacht. Danke.

Die Diagramme werden in dieser Version wahrscheinlich entfernt.
Allerdings werde ich deine Feiertagsberechnung in mein ursprüngliches Formular einarbeiten.

Die Weihnacht und Silvesterregelung sieht so aus, dass für den
24.12. und für den 31.12. je einen halben Tag Urlaub angerechnet wird.
Ich frage aber das "U" ab und ersetze es mit 8 Stunden zur Berechnung.
Dadurch trickse ich jedes Jahr bei der Berechnung manuell.

Zu deinem Blatt Feiertage habe ich noch eine Frage.
Das Datum errechnet sich entgegen deiner Beschreibung automatisch, oder ?

Und was bedeutet
Zitat:
wenn der Kalender auf einem anderen Tabellenblatt ist.

Die Bezeichnung Feiertage ist doch eindeutig, wieso sollte ich die umbenennen ? Oder kapiere ich mal wieder was nicht.

Das ganze zu kapieren habe ich aufgegeben. Das übersteigt eindeutig mein Excelverständnis. :-)

Du hast mir wahnsinnig geholfen. Erst einmal danke.

Gruß

Blacky

Antwort 21 von Aliba

Hi Blacky,

natürlich werden die Feiertage automatisch berechnet. Die Erläuterung im Feiertagsblatt wollte ich eigentlich rausnehmen und durch eine neue ersetzen. hab ich dann doch vergessen.

Ich habe für den Datumsbereich der Feiertage noch einen Namen vergeben, eben "Feiertage". Dies ist notwendig , weil die bedingte Formatierung nur mit Bereichsnamen funktioniert, wenn die Feiertagsdaten wie in diesem Fall auf einem anderen Tabellenblatt liegt als dem, in dem die bedingte Formatierung greifen soll.

CU Aliba

Antwort 22 von 090365

Hi Aliba,

dann habe ich das schon richtig verstanden.

Feiertage ist aber doch die passenden Bezeichnung dafür,
da werde ich nichts mehr ändern.

Ich habe noch mal versucht deine Berechnungen zu durchschauen.
Vielleicht sollte ich vorher noch etwas Mathe studieren.

Keine Chance, werde es als gegeben nehmen müssen.

CU

Blacky

Antwort 23 von 090365

Hallo Aliba,

hast du einen Ansatz für mein Weihnacht-Silvester Problem ?

Ich muss die Datei bis mitte nächster Woche fertig aufbereitet haben.

Wenn es dafür keine Lösung gibt, dann ist das auch nicht weiter schlimm,
dann passe ich da halt jedes Jahr an, oder regle das an einem Tag über Sonderurlaub, der wird auch nicht mitberechnet.

Ich bin allerdings erst wieder ab Sonntag abend online.

Gruß

Blacky

Antwort 24 von Aliba

Hi Blacky,

selbstverständlich gibt es da Ansätze, allerdings musst Du dann die Bedingungen schon genauer erklären:

Erstmal muss geklärt werden, ob Ihr überhaupt arbeitet an diesen Tagen, also muss jeder einen halben Tag Urlaub nehmen und bekommt 4 Stunden geschenkt??

ODER:

Die beiden Tage sind Arbeitstage, an denen nur halbtags gearbeitet wird, und der Nachmittag wird als halber Urlaubstag gezählt??

CU Aliba

Antwort 25 von 090365

Hallo Aliba,

bin erst soeben wieder zurückgekommen.

Wir arbeiten an den Tagen nicht und jeder bekommt vier Stunden geschenkt.
d.h. Mit einem Urlaubstag decke ich beide Tage ab.

Gruß

Blacky

Antwort 26 von Aliba

Hi Blacky,

habe dir das jetzt angepasst mit den Sondertagen.
Musste dazu etliche Formeln anpassen. Sollte jetzt funktionieren.
Auch in der Jahresübersicht habe ich verschiedene Sachen geändert.
Bei der 1.Jahreswoche und der letzten Jahreswoche werden nun zur Sollstundenberechnung nur die Tage berechnet, die auch in diesem Jahr liegen.

In eben diesen Wochen werden auch die Tage markiert, die nicht zu dem Jahr gehören.

Schau dirs mal an:
http://www.netupload.de/detail.php?img=148635cf98d63b1bbd060686648c...

CU Aliba

Antwort 27 von 090365

Hallo Aliba,

nun habe ich aber wirklich ein schlechtes Gewissen.
Ich finde es toll, dass du das gemacht hast, aber wenn der Aufwand so immens war,
dann hätte ich das auch weiterhin händische einfügen können.

Herzlichen Dank, wenn das Projekt fertig ist, werde ich irgendwo dein Kürzel einfügen, da ich das ohne deine Hilfe nicht geschafft hätte.

Schade, dass ich nun mein eigenes Projekt nicht mehr verstehe.
Werde mich im Weihnachtsurlaub mal genauer damit beschäftigen.

So, nun werde ich es grafisch noch aufwerten und ab 01.01.2008 kommt das Ding dann in den Einsatz.

Noch einmal herzlichen Dank

Blacky