Dieses Forum nutzt Cookies
Dieses Forum verwendet Cookies, um deine Login-Informationen zu speichern, wenn du registriert bist, und deinen letzten Besuch, wenn du es nicht bist. Cookies sind kleine Textdokumente, die auf deinem Computer gespeichert werden. Die von diesem Forum gesetzten Cookies werden nur auf dieser Website verwendet und stellen kein Sicherheitsrisiko dar. Cookies aus diesem Forum speichern auch die spezifischen Themen, die du gelesen hast und wann du zum letzten Mal gelesen hast. Bitte bestätige, ob du diese Cookies akzeptierst oder ablehnst.

Ein Cookie wird in deinem Browser unabhängig von der Wahl gespeichert, um zu verhindern, dass dir diese Frage erneut gestellt wird. Du kannst deine Cookie-Einstellungen jederzeit über den Link in der Fußzeile ändern.

Summen pro Tag einer wöchentlich erstellten Liste
#1
Hallo zusammen, 

schon immer bin ich per Google auf euch gestoßen, danke im Nachhinein für die bisher gelösten Anfragen, 
jedoch ist meine etwas zu speziell, weshalb ich sie direkt mal in den Anhang gepackt habe...

Kurz gefasst bezogen auf die Auswertung: 
5 Spalten A bis E sind vorhanden, interessant sind C und D.
Spalte C beinhaltet ein Datum im Format Tag Monat Jahr  Minute Sekunde, Spalte D eine Zahl (Sekunden).
Die Liste wird immer in diesem Format generiert und kann von der Formatierung der Spalten her leider nicht geändert werden.


Ausgewertet werden soll dabei die Summe aller Werte in Spalte D, wenn der Tag (dd:mm:yyyy ohne Uhrzeit) in Spalte C gleich ist.
Da der Wert in Spalte D in Sekunden angegeben ist, soll dies noch durch 3600 geteilt und am Ende auf zwei Nachkommastellen gerundet werden, um auf die Stunden zu kommen, was ja kein Problem ist.
Die Summe soll dann bestenfalls in etwa so aussehen:
(In Bezug auf die Werte des Anhangs)
Tag               Dauer
17.02.2017: 2,59h
16.02.2017: 3,69h
15.02.2017: 3,33h
14.02.2017: 2,44h
13.02.2017: 3,26h
Die Werte können gerne einfach so wie oben aufgebaut z.B. in G2:H6 oder auf einer extra Tabelle stehen.

Mein Problem bei der ganzen Geschichte ist die Automatisierung, da ich so die Liste immer händisch mit z.B. 
Code:
=RUNDEN((SUMME(D2:D61)/3600);2)
usw. auswerten muss. 
Wenn es in Richtung VBScript geht, bin ich leider raus, soweit ging es damals in der Schule nicht  Undecided  
Aber auch bei Makros wüsste ich nicht, wie ich automatisch die Tage "eindeutig" in den Zeilen untereinander als Kriterium ausgebe, 
und dann z.B. mit =SUMMEWENN den Wert von Spalte D zu summieren, wenn das Kriterium in Spalte C steht, 
da es wohl ein Problem mit dem als Datum formatieren Feld gibt.
Habe da schon mit TAG(), MONAT() und JAHR() überlegt, jedoch schlägt es da auch fehl. Genauso wie z.B. LINKS(C2;10).

Im Grunde genommen möchte ich mit der Liste ja nur sehen, wie hoch die Dauer pro Tag ist, 
und den Aufwand für die Zukunft bei jeder Auswertung so gering wie möglich halten. 
Bestenfalls natürlich als Makro, da ich es mir mit Formeln Einfügen etwas schwierig vorstelle...

Wie soll ich hier vorgehen? Ich bedanke mich schon einmal fürs Durchlesen und freue mich für jede Hilfestellung  Blush

Gruß,
texel


Angehängte Dateien
.csv   Auswertung.csv (Größe: 12,66 KB / Downloads: 7)
Antworten Top
#2
Hallo, ich würde hier eine Hilfsspalte einrichten und eine PivotTabelle hernehmen..:

Arbeitsblatt mit dem Namen 'Auswertung'
 IJ
1Summe von Dauer2 
2DatumErgebnis
317. Feb2,59
416. Feb3,69
515. Feb3,33
614. Feb2,44
713. Feb3,26
8Gesamtergebnis15,31
Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg
Gruß Jörg
ich muss mich erst wieder ganz langsam heran robben. Also bitte ich um Nachsicht

"Wer immer tut, was er schon kann, bleibt immer das, was er schon ist." - Henry Ford
Antworten Top
#3
Alt-F11, Einfügen Modul, folgenden Code einfügen, F5:

Code:
Sub StdProTag()
    Range("E2:E" & Range("A1").CurrentRegion.Rows.Count).FormulaR1C1 = "=RC[-1]/3600"
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        Range("A1").CurrentRegion, Version:=xlPivotTableVersion14).CreatePivotTable _
        TableDestination:=Range("G1"), TableName:="PivotTable2", _
        DefaultVersion:=xlPivotTableVersion14
    Sheets("Auswertung").Select
    [G1].Select
    ActiveWorkbook.ShowPivotTableFieldList = True
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Datum")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
        "PivotTable2").PivotFields("Agent"), "Summe von Dauer", xlSum
    Range("G2").Select
    Selection.Group Start:=True, End:=True, By:=1, Periods:=Array(False, _
        False, False, True, False, False, False)
End Sub
Antworten Top
#4
Hallo texel,

zur Anzeige ist die Pivottabelle genau das richtige.

Eine mögliche Formel ist, wenn in G2 das Datum steht:

Code:
{=RUNDEN(SUMME((GANZZAHL(C2:C244)=G2)*D2:D244)/3600;2)}


Die geschweiften Klammern werden nicht mit eingegeben, sondern die Formel wird mit Strg+Shift+Enter abgeschlossen. (Matrixformel)
helmut

Für mich ist die Möglichkeit in Excel an Zellen und Bereichen Namen zu vergeben die wichtigste Funktionalität.
Sie macht Formeln und den VBA-code verständlicher. Für Makros gilt die Regel: "Nur über benannte Bereiche auf den Inhalt der Zellen zugreifen."
Und wofür sind Regeln da? Um nachzudenken bevor man sie bricht.





Antworten Top
#5
Danke schon einmal für eure Lösungen! Ich habe mir dann doch mal die Zeit nach der Arbeit genommen, und es zu Hause mal getestet.
Inspiriert von euren Beiträgen, habe ich es dann doch noch einmal selbst mit der Makroaufnahme versucht, womit alles auf Anhieb gut aussieht.


Code:
Sub AgentAuswertung()
'
' AgentAuswertung Makro
'
    Columns("A:B").Select
    Selection.NumberFormat = "0"
    Columns("A:E").EntireColumn.AutoFit
    Range("G1").Select
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Auswertung!R1C3:R1048576C4", Version:=6).CreatePivotTable TableDestination _
        :="Auswertung!R1C7", TableName:="Zeit pro Tag", DefaultVersion:=6
    Sheets("Auswertung").Select
    Cells(1, 7).Select
    With ActiveSheet.PivotTables("Zeit pro Tag").PivotFields("Dauer")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("Zeit pro Tag").PivotFields("Datum")
        .Orientation = xlRowField
        .Position = 2
    End With
    ActiveSheet.PivotTables("Zeit pro Tag").PivotFields("Datum").AutoGroup
    ActiveSheet.PivotTables("Zeit pro Tag").PivotFields("Datum").Orientation = _
        xlHidden
    ActiveSheet.PivotTables("Zeit pro Tag").PivotFields("Minuten").Orientation = _
        xlHidden
    ActiveSheet.PivotTables("Zeit pro Tag").PivotFields("Stunden").Orientation = _
        xlHidden
    ActiveSheet.PivotTables("Zeit pro Tag").AddDataField ActiveSheet.PivotTables( _
        "Zeit pro Tag").PivotFields("Dauer"), "Anzahl von Dauer", xlCount
    With ActiveSheet.PivotTables("Zeit pro Tag").PivotFields("Anzahl von Dauer")
        .Caption = "Summe von Dauer"
        .Function = xlSum
    End With
    ActiveSheet.PivotTables("Zeit pro Tag").CalculatedFields.Add "Dauer in Stunden" _
        , "=ROUND(Dauer/3600,2)", True
    ActiveSheet.PivotTables("Zeit pro Tag").PivotFields("Dauer in Stunden"). _
        Orientation = xlDataField
    ActiveSheet.PivotTables("Zeit pro Tag").PivotFields("Summe von Dauer"). _
        Orientation = xlHidden
    ActiveSheet.PivotTables("Zeit pro Tag").PivotFields("Summe von Dauer in Stunden").Caption = _
        "AgentStunden"
    ActiveSheet.PivotTables("Zeit pro Tag").CompactLayoutRowHeader = "Tag"
    With ActiveSheet.PivotTables("Zeit pro Tag")
        .DisplayFieldCaptions = False
        .DisplayContextTooltips = False
        .ShowDrillIndicators = False
    End With
    ActiveSheet.PivotTables("Zeit pro Tag").PivotFields("Tage").PivotItems( _
        "<2/13/2017").Caption = " "
End Sub
Kann man dabei evtl. noch etwas optimieren/weglassen/kürzen? Wobei die Dauer der einzelnen Schritte (wenn auch redundant) nicht sooo viel Zeit in Anspruch nimmt Angel
Danke schon einmal, soweit ist mir natürlich auch schon sehr gut weitergeholfen worden!
Wenn die Zeit es hergibt, muss ich mich dringend wieder intensiver mit Excel beschäftigen - wenns läuft, dann läufts!  :57:

Gruß,
texel
Antworten Top
#6
Der Post von gestern ist wohl zu lange her, ich kann ihn leider nicht mehr editieren :(
Kann man das Makro unabhängig vom Dateinamen machen, bzw. diesen irgendwie sich selbst ergänzen lassen? 
Die auszuwertenden Dateien werden immer ein Timestamp als Suffix haben, deshalb wird es mit aktuellem Stand schwierig...

Gruß,
texel
Antworten Top
#7
Ok, der Edit geht wohl nach ein paar Stunden schon nicht mehr... gerne teile ich noch freudig mit, dass ich es mit etwas viel Rumprobiererei selbst rausgefunden habe ::)
Im Endeffekt sieht mein PivotCaches.Create jetzt so aus:
Code:
   ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
       "R1C3:R1048576C4", Version:= _
       xlPivotTableVersion15).CreatePivotTable TableDestination:= _
       "R1C7", TableName:="PivotTable1", _
       DefaultVersion:=xlPivotTableVersion15
   ActiveSheet.Select
Zum Glück kann man die Dateinamen einfach weglassen, und den Tabellennamen mit ActiveSheet selektieren, 
da ich immer bewusst vorher die .csv Datei öffne und dann das Makro händisch in dieser aus meiner Personal.xlsb starte.

Danke nochmals für eure Antworten! :)

Gruß,
texel
Antworten Top


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste