Clever-Excel-Forum

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

Hallo, ich würde hier eine Hilfsspalte einrichten und eine PivotTabelle hernehmen..:

Arbeitsblatt mit dem Namen 'Auswertung'
	I	J
1	Summe von Dauer2
2	Datum	Ergebnis
3	17. Feb	2,59
4	16. Feb	3,69
5	15. Feb	3,33
6	14. Feb	2,44
7	13. Feb	3,26
8	Gesamtergebnis	15,31

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

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

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)

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

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

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

texel

Jockel

lupo1

Ego

texel

texel

texel