ooomorpheusooo
Goto Top

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

Content-Key: 201213

Url: https://administrator.de/contentid/201213

Ausgedruckt am: 28.03.2024 um 13:03 Uhr

Mitglied: hajowe
hajowe 04.02.2013 um 09:55:46 Uhr
Goto Top
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ß
Mitglied: OOOmorpheusOOO
OOOmorpheusOOO 04.02.2013 um 10:01:30 Uhr
Goto Top
Hallo hajowe,

vielen Dank für die schnelle Antwort!
Das ist leider nicht das, was ich gesucht habe. in dem ersten Tabellenblatt habe ich alle Monate, die auf dem 2. Blatt automatisch auseinander gefrickelt werden sollen.
Januar-Dezember bekomme ich ja noch allein hin geschrieben, dann soll aus Blatt 1 Spalte A das Datum durchsucht werden und die Dauer aus Spalte G zusammen gezählt werden.

Geht das mit nur Excel überhaupt oder geht das schon in Access mit den Abfragen?

LG
Mitglied: dummer-esel
dummer-esel 04.02.2013 um 10:35:47 Uhr
Goto Top
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.
Mitglied: Pjordorf
Pjordorf 04.02.2013 um 11:46:45 Uhr
Goto Top
Hallo,

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.

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
Mitglied: OOOmorpheusOOO
OOOmorpheusOOO 04.02.2013 um 12:51:03 Uhr
Goto Top
Hallo Peter,
vielen Dank für den üppigen Hinweis.

Jetzt habe ich versucht eine Pivot-Tabelle zu erstellen. Den Monat konnte ich mir auch anzeigen lassen, wie verknüpfe ich jetzt aber den Monat mit den zugehörigen Stunden?

Ich bin kein Programmierer und will / werde es auch nie werden. Was ich lernen darf lerne ich.

Hat noch jemand eine Idee?

LG
OOOmorpheusOOO
Mitglied: Biber
Biber 04.02.2013 um 12:55:45 Uhr
Goto Top
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

=SUMME(WENN(MONAT(Stunden!$A$2:$A$1000)=Zeilen(A$1:A1);Stunden!$G2:$G1000;0))
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
Mitglied: OOOmorpheusOOO
OOOmorpheusOOO 04.02.2013 um 13:46:50 Uhr
Goto Top
Hallo Biber,

Danke für den Hinweis.

Die Formel habe ich natürlich gleich ausprobiert.
Leider hat sie nicht den gewünschten Erfolg gebracht.
Egal, was ich in die Quelltabelle eintrage, es kommt nur 0 raus.

Die Formel habe ich natürlich (hoffentlich richtig) angepasst.


=SUMME(WENN(MONAT(Stunden!$A$2:$A$1000)=ZEILE(Stunden!A:A);Stunden!$G2:$G1000;0))

Irgendwie denke ich falsch - habe ich im Gefühl.

MfG
OOOmorpheusOOO
Mitglied: Biber
Biber 04.02.2013 aktualisiert um 14:12:46 Uhr
Goto Top
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
Mitglied: OOOmorpheusOOO
OOOmorpheusOOO 04.02.2013 aktualisiert um 14:34:12 Uhr
Goto Top
Hallo Biber,
danke für den Tip!

Ich schlüssele nochmal die bereits erstellte Tabelle auf.


Blatt Stunden:

Spalten:
A Datum (Formatierung: Datum; *14.03.2001)
B Wochentag (nur Text; Formel =TEXT(A5;"TTTT"))
C Kunde (nur Text ohne Formel)
D ausgeführte Arbeiten (nur Text ohne Formel)
E Startzeit (hh:mm)
F Endzeit (hh:mm)
G Dauer (Formel =REST(F3-E3;1)*24)


Im neuen Blatt

Januar: xxx Stunden (automatisch ausgewertet vom Blatt Stunden)
Februar: xxx Stunden (automatisch ausgewertet vom Blatt Stunden)
...
...
...

Meiner Meinung nach müsste doch da eine Formel rein, die Spalte A nach Monaten auswertet (und vlt. auch nach Jahr) und die Passenden Einträge aus Spalte G addiert und dann da hin schreibt...

Na da hab ich ja wieder in ein Wespennest gestochen face-wink

MfG

OOOmorpheusOOO
Mitglied: Biber
Biber 04.02.2013 aktualisiert um 14:49:19 Uhr
Goto Top
Moin OOOmorpheusOOO,

meinetwegen auch mit Monat und Jahr
=SUMME(WENN(TEXT(Stunden!$A$2:$A$1000;"JJJJ MMMM")="2001 März";Stunden!$G2:$G1000;0))  
[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:
=SUMME(WENN(TEXT(Stunden!$A$2:$A$1000;"JJJJ MMMM")=$A2;Stunden!$G2:$G1000;0))  
[mit Strg-Shift-Enter eingeben]

Grüße
Biber
Mitglied: OOOmorpheusOOO
OOOmorpheusOOO 04.02.2013 um 14:54:08 Uhr
Goto Top
Hallo Biber,
danke dafür! Kannst du mir verraten, warum in der Berechnung dann jedes Mal 0 raus kommt?

Die Tabelle ist prall gefüllt...


MfG
OOOmorpheusOOO
Mitglied: Biber
Biber 04.02.2013 aktualisiert um 15:39:31 Uhr
Goto Top
Moin OOOmorpheusOOO,

das kann nur noch ein lösbares Problem sein face-wink

  • 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
Mitglied: OOOmorpheusOOO
OOOmorpheusOOO 04.02.2013 um 16:27:30 Uhr
Goto Top
Hallo Biber,
Mail ist dann doch raus...

LG
OOOmorpheusOOO
Mitglied: Biber
Biber 04.02.2013 aktualisiert um 17:30:31 Uhr
Goto Top
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
=SUMME(WENN(TEXT(Stunden!$A$2:$A$1000;"JJJJMMMM")=TEXT($A2;"JJJJMMMM");Stunden!$G2:$G1000;0))  
und bitte mit Strg-Shift-Enter eingeben
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))  
... oder irgendetwas, wo Jahr+Monat des Datums enthalten ist.

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
d.h. in der rechten Spalte steht die Formel nach unten gezogen.
=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
Mitglied: OOOmorpheusOOO
OOOmorpheusOOO 04.02.2013 aktualisiert um 19:17:55 Uhr
Goto Top
Hallo Biber,

auch wenn ich zugeben muss, diese Formel nicht so recht zu verstehen...- oder fast gar nicht... - so bin ich dir für deine Hilfe sehr dankbar.

In einer ruhigen Minute werde ich es auch schaffen, mich mit den Formeln zu beschäftigen!

Auf den ersten und zweiten und dritten Blick könnte ich damit ja auch dann das Jahr 2014 mit abdecken? - Anhand des Blattes Auswertung Stunden "Januar 2013" werden die Daten aus der Stundenübersicht geholt richtig?

LG
OOOmorpheusOOO
Mitglied: Biber
Biber 04.02.2013 um 22:14:52 Uhr
Goto Top
Moin OOOmorpheusOOO,

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...

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