Arbeitszeitnachweis mit Excel und Auswertung erstellen
Hallo Freunde der Nacht,
Ich grübel gerade über ein Problem.
Mein Arbeitszeitnachweis erfolgt in Excel.
Spalte A=Datum
Spalte B=automatisch Anzeige Wochentag aus Spalte A
Spalte C=Kunde
Spalte D=Tätigkeit
Spalte E=Startzeit
Spalte F=Endzeit
Spalte G=Dauer (Differenz aus Spalte F und E)
Die Zeiten sollen alle komplett in einer Tabelle stehen. Die Auswertung soll dann auf einem anderen Tabellenblatt erfolgen.
Kann man automatisch Spalte A durchsuchen lassen, um auf dem anderen Tabellenblatt einen Wert z.B. Januar=xxx Stunden; Februar=xxx Stunden erstellen zu lassen?
Es sollen auf dem 2. Tabellenblatt die Monate mit den jeweils geleisteten Stunden aufgelistet werden.
Hat jemand eine Idee, wie ich das am besten realisieren kann?
LG
OOOmorpheusOOO
Ich grübel gerade über ein Problem.
Mein Arbeitszeitnachweis erfolgt in Excel.
Spalte A=Datum
Spalte B=automatisch Anzeige Wochentag aus Spalte A
Spalte C=Kunde
Spalte D=Tätigkeit
Spalte E=Startzeit
Spalte F=Endzeit
Spalte G=Dauer (Differenz aus Spalte F und E)
Die Zeiten sollen alle komplett in einer Tabelle stehen. Die Auswertung soll dann auf einem anderen Tabellenblatt erfolgen.
Kann man automatisch Spalte A durchsuchen lassen, um auf dem anderen Tabellenblatt einen Wert z.B. Januar=xxx Stunden; Februar=xxx Stunden erstellen zu lassen?
Es sollen auf dem 2. Tabellenblatt die Monate mit den jeweils geleisteten Stunden aufgelistet werden.
Hat jemand eine Idee, wie ich das am besten realisieren kann?
LG
OOOmorpheusOOO
Bitte markiere auch die Kommentare, die zur Lösung des Beitrags beigetragen haben
Content-ID: 201213
Url: https://administrator.de/forum/arbeitszeitnachweis-mit-excel-und-auswertung-erstellen-201213.html
Ausgedruckt am: 23.12.2024 um 03:12 Uhr
16 Kommentare
Neuester Kommentar
Hi OOOMorpheusOOO
schau mal hier http://www.controllingportal.de/Marktplatz/Excel-Tools/Arbeitszeitnachw ...
Warum das Rad neu erfinden.
Vieleicht ist es schon das was du willst.
Gruß
schau mal hier http://www.controllingportal.de/Marktplatz/Excel-Tools/Arbeitszeitnachw ...
Warum das Rad neu erfinden.
Vieleicht ist es schon das was du willst.
Gruß
Ohne es jetzt ausprobiert zu haben: Es könnte mit der "Histogramm"-Funktion gehen: https://support.microsoft.com/kb/214269/de
Da findet man auch relativ viel im Netz zu.
Da findet man auch relativ viel im Netz zu.
Hallo,
Gruß,
Peter
Zitat von @OOOmorpheusOOO:
Kann man automatisch Spalte A durchsuchen lassen, um auf dem anderen Tabellenblatt einen Wert z.B. Januar=xxx Stunden; Februar=xxx Stunden erstellen zu lassen?
Ja, kann man.Kann man automatisch Spalte A durchsuchen lassen, um auf dem anderen Tabellenblatt einen Wert z.B. Januar=xxx Stunden; Februar=xxx Stunden erstellen zu lassen?
Es sollen auf dem 2. Tabellenblatt die Monate mit den jeweils geleisteten Stunden aufgelistet werden.
Und?Hat jemand eine Idee, wie ich das am besten realisieren kann?
Das wurde alles schon erfunden. Es gibt Tausende von Excel Arbeitszeitberechnungen und Beipiele im Grossen Netz deiner Wahl. Musst du etwas suchen. Du kannst es dir aber auch selbst erstellen. So geheimnissvoll tut Excel gar nicht. Drück mal in Excel die F1 Taste. Du kannst mit Formeln direkt bei der Eingabe Arbeiten oder auch das ganze per VBA (Viusal Basic für Applikationen) nehmen. Was möchtest du bzw. was kannst du bzw. was willst du noch lernen? Es gibt auch vom Hersteller deines Excels etliche Beispiele auf seinen eigenen Seiten sowie umfangreiche Office Foren wo alles vorgekaut liegt.Gruß,
Peter
Moin OOOmorpheusOOO,
du kannst es mit den Funktionen SUMMEWENN(), SUMMENPRODUKT() oder auch mit SUMME() und WENN() machen.
Wenn deine Detailstunden in Blatt "Stunden" stehen, in Zelle $A2:A$1000 die Tages-Datumswerte und in Spalte $G$2:$G$1000 die Stunden, dann geht es z.B mit dieser Formel von einem anderen Tabellenblatt aus
Formel mit Strg-Shift-Enter abschicken und für 12 Monate in die darunterliegenden 11 Zellen ziehen.
Ich gehe in der Formel davon aus, dass du nur die Stundenerfassung eines Jahres in dem Detailblatt hast.
Statt der "ZEILEN($A$1:$A1)"-Mimik kannst du natütrlich auch selbst einen Wert eintragen oder den aus der Nachbarzelle holen.
Wie schon Pjordorf schrieb: alles ist auch in der Excel-F1-Hilfe verständlich beschrieben.
Grüße
Biber
du kannst es mit den Funktionen SUMMEWENN(), SUMMENPRODUKT() oder auch mit SUMME() und WENN() machen.
Wenn deine Detailstunden in Blatt "Stunden" stehen, in Zelle $A2:A$1000 die Tages-Datumswerte und in Spalte $G$2:$G$1000 die Stunden, dann geht es z.B mit dieser Formel von einem anderen Tabellenblatt aus
=SUMME(WENN(MONAT(Stunden!$A$2:$A$1000)=Zeilen(A$1:A1);Stunden!$G2:$G1000;0))
Ich gehe in der Formel davon aus, dass du nur die Stundenerfassung eines Jahres in dem Detailblatt hast.
Statt der "ZEILEN($A$1:$A1)"-Mimik kannst du natütrlich auch selbst einen Wert eintragen oder den aus der Nachbarzelle holen.
Wie schon Pjordorf schrieb: alles ist auch in der Excel-F1-Hilfe verständlich beschrieben.
Grüße
Biber
Moin OOOmorpheusOOO,
also, der Part "ZEILE(Stunden!A:A)" ist suboptimal.
Plan war, das dort "ZEILEN(A$1:A1)" steht , also
-> ZEILEN(Von SpalteA$absolutZeile1 bis SpalteArelativZeile1)
--> ergibt in der ersten eingegebenen Zeile das Ergebnis "1" (A$1:A1=eine Zeile)
--> die 1 nehme ich für die Suche nach "Januar"
--> ergibt in der ersten runterkopierten Zeile das Ergebnis "2" (weil A$1:A2=zwei Zeilen)
--> die 2 nehme ich für die Suche nach "Februar"
--> ergibt in der nächsten runterkopierten Zeile das Ergebnis "3" (weil $A1:A3=drei Zeilen)
--> die 3 nehme ich für die Suche nach "Dingenskirchen"...den Monat nach Februar
Grüße
Biber
also, der Part "ZEILE(Stunden!A:A)" ist suboptimal.
Plan war, das dort "ZEILEN(A$1:A1)" steht , also
-> ZEILEN(Von SpalteA$absolutZeile1 bis SpalteArelativZeile1)
--> ergibt in der ersten eingegebenen Zeile das Ergebnis "1" (A$1:A1=eine Zeile)
--> die 1 nehme ich für die Suche nach "Januar"
--> ergibt in der ersten runterkopierten Zeile das Ergebnis "2" (weil A$1:A2=zwei Zeilen)
--> die 2 nehme ich für die Suche nach "Februar"
--> ergibt in der nächsten runterkopierten Zeile das Ergebnis "3" (weil $A1:A3=drei Zeilen)
--> die 3 nehme ich für die Suche nach "Dingenskirchen"...den Monat nach Februar
Grüße
Biber
Moin OOOmorpheusOOO,
meinetwegen auch mit Monat und Jahr
[mit Strg-Shift-Enter eingeben]
Damit wird das Datum in deiner Spalte Stunden!A als "JJJJ vollerMonatsname" formatiert.
Und du kannst in die Spalte neben der Formel diese Texte hinschreiben.
Sprich: wenn die Formel in Spalte B ab Zeile 2 steht, kann kann in Spalte A2, A3 etc geschrieben werden:
2001 Januar (in A2)
2001 Februar (in A3)
2001 März (in A4)
Und die Formel in B2, B3, B4...kann darauf verweisen:
[mit Strg-Shift-Enter eingeben]
Grüße
Biber
meinetwegen auch mit Monat und Jahr
=SUMME(WENN(TEXT(Stunden!$A$2:$A$1000;"JJJJ MMMM")="2001 März";Stunden!$G2:$G1000;0))
Damit wird das Datum in deiner Spalte Stunden!A als "JJJJ vollerMonatsname" formatiert.
Und du kannst in die Spalte neben der Formel diese Texte hinschreiben.
Sprich: wenn die Formel in Spalte B ab Zeile 2 steht, kann kann in Spalte A2, A3 etc geschrieben werden:
2001 Januar (in A2)
2001 Februar (in A3)
2001 März (in A4)
Und die Formel in B2, B3, B4...kann darauf verweisen:
=SUMME(WENN(TEXT(Stunden!$A$2:$A$1000;"JJJJ MMMM")=$A2;Stunden!$G2:$G1000;0))
Grüße
Biber
Moin OOOmorpheusOOO,
das kann nur noch ein lösbares Problem sein
Wenn nix hülft, dann schick mir eine (ggf abgespeckte+anonymisierte) Kopie der Tabelle an die Mailadresse in meinem Profil.
ABER erst die beiden wahrscheinlichsten Fehler überprüfen!
Grüße
Biber
das kann nur noch ein lösbares Problem sein
- entweder du hast die Formeln nicht mit Strg-Shift-Enter abgeschickt (wenn doch, dann sollten vor/nach der Formel geschweifte Klammern {} angezeigt werden)
- oder der Vergleich geht wegen einem Leerzeichen zuviel/zuwenig in die Grütze.
Wenn nix hülft, dann schick mir eine (ggf abgespeckte+anonymisierte) Kopie der Tabelle an die Mailadresse in meinem Profil.
ABER erst die beiden wahrscheinlichsten Fehler überprüfen!
Grüße
Biber
Moin OOOmorpheusOOO,
und eine leicht korrigierte Version ist auf dem Rückweg.
Es waren tatsächlich nur kleine Unsauberkeiten, die den Vergleich ins Leere laufen liessen.
1) In der Spalte Stunden!A2:A1000 waren gar keine Datumswerte eingetragen
2) In der Spalte A der Auswertungstabelle stand nicht, wie ich es gemeint hatte, der Text "2001 Januar" "2001 Februar" etc, also der Datumsschablone "JJJJ MMMM" entsprechend, sondern ein Datumswert
Jan 13 (=eigentlich eingegeben 01.01.2013, formatiert wie links stehend)
Feb 13 (=eigentlich eingegeben 01.02.2013, formatiert wie links stehend)
Okay, kann man machen.. aber dann muss die Formel geändert werden auf "Vergleiche Äpfel mit Äpfeln" bzw
und bitte mit Strg-Shift-Enter eingeben
bzw. vergleiche zwei gleich formatierte ("harmonisierte" sagen wir Insider *g ) Werte.
Genauso ginge auch
... oder irgendetwas, wo Jahr+Monat des Datums enthalten ist.
Zu deiner Zusatzfrage
Dein "Stunden"-Blatt erscheint so
d.h. in der rechten Spalte steht die Formel nach unten gezogen.
Bei allen Feldern ohne Datumswert ("leer") erscheint "Samstag", weil Excel das leere Datumsfeld als Datum 0 == 00.01.1900 interpretiert.
Und -wie alle Redmonder PraktikantInnen als erstes beigebracht bekommen: der 00.01.1900 war ein Samstag.
Grüße
Biber
und eine leicht korrigierte Version ist auf dem Rückweg.
Es waren tatsächlich nur kleine Unsauberkeiten, die den Vergleich ins Leere laufen liessen.
1) In der Spalte Stunden!A2:A1000 waren gar keine Datumswerte eingetragen
2) In der Spalte A der Auswertungstabelle stand nicht, wie ich es gemeint hatte, der Text "2001 Januar" "2001 Februar" etc, also der Datumsschablone "JJJJ MMMM" entsprechend, sondern ein Datumswert
Jan 13 (=eigentlich eingegeben 01.01.2013, formatiert wie links stehend)
Feb 13 (=eigentlich eingegeben 01.02.2013, formatiert wie links stehend)
Okay, kann man machen.. aber dann muss die Formel geändert werden auf "Vergleiche Äpfel mit Äpfeln" bzw
=SUMME(WENN(TEXT(Stunden!$A$2:$A$1000;"JJJJMMMM")=TEXT($A2;"JJJJMMMM");Stunden!$G2:$G1000;0))
bzw. vergleiche zwei gleich formatierte ("harmonisierte" sagen wir Insider *g ) Werte.
Genauso ginge auch
=SUMME(WENN(TEXT(Stunden!$A$2:$A$1000;"MMJJJJ")=TEXT($A2;"MMJJJJ");Stunden!$G2:$G1000;0))
Zu deiner Zusatzfrage
Dein "Stunden"-Blatt erscheint so
Datum Wochentag
11.01.2013 Freitag
01.02.2013 Freitag
01.02.2013 Freitag
Samstag
Samstag
Samstag
=TEXT(A2;"TTTT")
Bei allen Feldern ohne Datumswert ("leer") erscheint "Samstag", weil Excel das leere Datumsfeld als Datum 0 == 00.01.1900 interpretiert.
Und -wie alle Redmonder PraktikantInnen als erstes beigebracht bekommen: der 00.01.1900 war ein Samstag.
Grüße
Biber
Moin OOOmorpheusOOO,
Ich formuliere es mal so:
Nein, die vorhin in den Ring geworfene Formel ist auf maximal 1000 Einzelzeilen ausgelegt.
Wenn du also 200 Tage pro Jahr arbeitest und pro Tag auch mal 4 oder 5 Einzelbuchungen erfasst (unterschiedliche Kunden/Projekte/Tätigkeiten)..
--> Dann wirst du noch vor dem angedeuteten Ende deines Planungshorizontes die Formel zumindest anpassen müssen.
Die Formel solte eigentlich nur appetit machen auf Selber-Weiter-Experimentieren.
Wenn du etwas nach oben scrollst, habe ich geschrieben "Du kannst es mit SUMMENPRODUKT(), SUMMEWENN() oder SUMME() und WENN() machen..".
Andere haben auch auf die schnelle (aber dafür statische) Drei-Mausklicks-Pivot-Auswertung verwiesen.
Schau dir die Formeln in der Excel-Hilfe an und kaspere die vorgeturnten Beispiele nach.
Dann und nur dann wirst du mit einer selbstgehäkelten Arbeitszeiterfassung über den Winter kommen.
Excel ist das einzige Programm aus der Redmonder Software-Klitsche, bei dem ich wirklich an vielen Stellen den Spass und die Detailverliebtheit der Programmierer spüren kann - auch wenn "Microsoft" draufsteht, ist es einfach ein gutes Programm.
Und sobald du ein bisschen den Zugang dazu gefunden hast, sobald du weisst, wie die Applikation (oder deren Programmierer) ticken -> dann kommst du sehr schnell zu sehr viel Möglichkeiten.
Im Moment hast du ein, zwei übern-Zaun-geworfene Formeln zum Copy&Pasten - das hilft nur wenig.
Bastele weiter dran, lös die nächsten zwei Problemchen selbst ... beim dritten Problem treffen wir uns wieder hier und dann gibts die nächste Hilfe.
Grüße
Biber
Zitat von @OOOmorpheusOOO:
Auf den ersten und zweiten und dritten Blick könnte ich damit ja auch dann das Jahr 2014 mit abdecken?
Manchmal macht mir die heutige Jugung richtig Angst... wenn du dir im Januar 2013 Sorgen machst, ob du auch deine 2014er Stunden erfassen kannst...Auf den ersten und zweiten und dritten Blick könnte ich damit ja auch dann das Jahr 2014 mit abdecken?
Ich formuliere es mal so:
Nein, die vorhin in den Ring geworfene Formel ist auf maximal 1000 Einzelzeilen ausgelegt.
Wenn du also 200 Tage pro Jahr arbeitest und pro Tag auch mal 4 oder 5 Einzelbuchungen erfasst (unterschiedliche Kunden/Projekte/Tätigkeiten)..
--> Dann wirst du noch vor dem angedeuteten Ende deines Planungshorizontes die Formel zumindest anpassen müssen.
Die Formel solte eigentlich nur appetit machen auf Selber-Weiter-Experimentieren.
Wenn du etwas nach oben scrollst, habe ich geschrieben "Du kannst es mit SUMMENPRODUKT(), SUMMEWENN() oder SUMME() und WENN() machen..".
Andere haben auch auf die schnelle (aber dafür statische) Drei-Mausklicks-Pivot-Auswertung verwiesen.
Schau dir die Formeln in der Excel-Hilfe an und kaspere die vorgeturnten Beispiele nach.
Dann und nur dann wirst du mit einer selbstgehäkelten Arbeitszeiterfassung über den Winter kommen.
Excel ist das einzige Programm aus der Redmonder Software-Klitsche, bei dem ich wirklich an vielen Stellen den Spass und die Detailverliebtheit der Programmierer spüren kann - auch wenn "Microsoft" draufsteht, ist es einfach ein gutes Programm.
Und sobald du ein bisschen den Zugang dazu gefunden hast, sobald du weisst, wie die Applikation (oder deren Programmierer) ticken -> dann kommst du sehr schnell zu sehr viel Möglichkeiten.
Im Moment hast du ein, zwei übern-Zaun-geworfene Formeln zum Copy&Pasten - das hilft nur wenig.
Bastele weiter dran, lös die nächsten zwei Problemchen selbst ... beim dritten Problem treffen wir uns wieder hier und dann gibts die nächste Hilfe.
Grüße
Biber