Zeiten von einem Fahrer aus mehreren Tabellenblättern

[Hobbit]

Moin Excel Profis,

ich habe eine Tabelle wo ich aus ca. 20 Blätter die Arbeitszeit pro Fahrer brauche.

Im Blatt gesamt steht in Spalte G der Fahrer.
Dann gibt es Blätter die z.B. vom 01.05.2025 - 31.05.2025 bezeichnet sind.
In den Datums blättern stehen in Spalte Q die Fahrer und in Spalte S die Soll Arbeitszeit pro Fahrer, die im Blatt Gesamt in Spalte H zusammengezählt werden soll.
Dann stehen in den Datums blättern in Spalte T die Ist Arbeitszeit pro Fahrer, die im Blatt Gesamt in Spalte I zusammengezählt werden soll.
Könnt ihr da bitte mal schauen, ob das zu bewältigen ist?

Vielen dank für Die Mühe.
MFG Jens

[knobbi38]

Hallo Jens,

ich bin jetzt nicht der Excel Profi, aber wenn du solche Auswertungen machen möchtest, solltest du dich an die Regeln von "normalisierten" Tabellen halten und dein Datenmodell nochmal gründlich überdenken, bevor du damit weiter machst, ansonsten wird das ein Chaos. Diese Regeln sind allgemeingültig und gelten nicht nur für Datenbanken, sondern auch für Excel.

Wenn die Tabellen normalisiert sind, sollte es dann nicht zu schwer sein, solche individuellen Auswertungen und Konsolidierungen mit PowerQuery vorzunehmen.

Der erste Schritt dürfte sicherlich sein, alle Touren aller Fahrer mit einem eindeutigen Index in ein einzelnes Tabellenblatt zu überführen und nicht mit mehreren Blättern mit Datumsangaben zu hantieren. So etwas verkompliziert nur alles.

Gruß Knobbi38

[d'r Bastler]

Moin Jens,

es ist ein altbekanntes Thema, dass Nicht-Programmierer, die sich an Datenverarbeitung machen, bei ihrer Umsetzung zu sehr am gewünschten Ergebnis (möglichst noch Papier-formatiert) orientieren. Aber genau so funktioniert es eben nicht. Ich habe im Moment keine zur Veranschaulichung geeignete Tabelle zur Hand, aber ein Blick in eine professionelle SQL-Tabelle erstaunt selbst mich immer noch, wie völlig unleserlich aber eben höchsteffektiv diese Dinger aufgebaut sind.

Sie sind eben die von Knobbi optimierte Datengrundlage. Alles in ein Blatt, nach Datum, Fahrer und Touren vorstrukturiert und dann die Auswertungen, Berichte, Zusammenfassungen per PQ oder VBA ausgegeben. Ganz nach dem alten EVA-Prinzip: Eingabe (Deine erfassten Daten), Verarbeitung (Filtern und Berechnen der gewünschten Werte) und Ausgabe (auf neuen temporären Blättern, als PDF oder Grafik).

Um die für Nutzer notwendige gute Les/verstehbarkeit zu unterstützen, helfen dann Userforms oder Arbeitsblätter, die das vereinfachen.

Schöne Grüße

[Paul1206]

Hallo Jens,

eigentlich ist schon alles gesagt, wie man es besser auswertbar anstellt.

Da heute kaum was Inspirierendes in den Foren zu finden war habe ich mich mal über eine Lösung für dein Problem hergemacht.

Mein erster Gedanke war eine UDF. Da gibt es bei diesem Konstrukt Probleme mit der Aktualisierung.
Bleibt also nur eine parametrisierte Prozedur als Lösung.

Code: Alles auswählen

Private Sub SummenAZ(ByVal FahrerSpalte As String, _
                        ByVal SollSpalte As String, _
                        ByVal IstSpalte As String, _
                        ByVal GesamtFahrer As String, _
                        ByVal GesamtSoll As String, _
                        ByVal GesamtIst As String)
    
    Dim Ws As Object, i&, j&, k&, arr(), timeSoll As Date, timeIst As Date, Treffer As Variant
    ReDim arr(1 To 10000)
    For Each Ws In ThisWorkbook.Sheets
        If IsDate(Ws.Name) Then k = k + 1: arr(k) = Ws.Name
    Next
    ReDim Preserve arr(1 To k)
    With Tabelle3
        For i = 2 To Tabelle3.Cells(Rows.Count, GesamtFahrer).End(xlUp).Row
            For j = LBound(arr) To UBound(arr)
                Treffer = Application.Match(.Cells(i, GesamtFahrer), Sheets(arr(j)).Columns(FahrerSpalte), 0)
                If Not IsError(Treffer) Then
                    timeSoll = timeSoll + Sheets(arr(j)).Range(SollSpalte & Treffer)
                    timeIst = timeIst + Sheets(arr(j)).Range(IstSpalte & Treffer)
                End If
            Next j
            .Cells(i, GesamtSoll) = CDate(timeSoll): timeSoll = 0
            .Cells(i, 9) = CDate(timeIst): timeIst = 0
        Next i
    End With
End Sub

Sub Berechnen()
    Call SummenAZ(FahrerSpalte:="Q", SollSpalte:="S", IstSpalte:="T", GesamtFahrer:="G", GesamtSoll:="H", GesamtIst:="I")
End Sub

Baue einen Button ins Blatt "Gesamt" ein und weise die Sub "Berechnen" dem Button zu.

Gruß Uwe

[Hobbit]

Moin,
danke für den Code.

Vielen dank für die Mühe.

MFG Jens

[Hobbit]

Moin,
kann es sein wenn noch nicht in alle Blätter Daten vorhanden sind, das der Code nicht funktioniert?

typen unverträglich.png

Vielen Dank für die Mühe.

[Hobbit]

Moin,

klappt jetzt.

Gibt es noch eine Möglichkeit wenn ich in M2 den Fahrer auswähle, das mir dann in Spalte N das Datum, in O die Sollzeit und in P die Istzeit eingetragen wird?

Ich habe hier eine Formel:
=SUMME(SUMMEWENNS(INDIREKT("'"&Alle&"'!S:S");INDIREKT("'"&Alle&"'!Q:Q");$G2)) , aber die ist ja nur zum Summieren.
Kann man die Umbauen als Sverweis oder Index?

Vielen dank für die Mühe.

[Paul1206]

Hallo Jens,

M2 ist der Name/Koordinate einer Zelle in einem Tabellenblatt. Mehr sagt dies Außenstehenden nicht. Um die Sache in richtige Bahnen zu lenken braucht es eindeutige Informationen.

Dann solltest du wissen, dass solche Funktionen wie Indirekt äußerst volatile Funktionen sind, welche man so es irgend anders lösbar ist meiden sollte.

Gruß Uwe

[Hobbit]

Moin,
danke für die Hilfe, aber ich habe es wohl falsch erklärt.
In M2 wähle ich den Fahrer aus und in Spalte N2 - N22 steht das Datum.
Jetzt sollen die einzelnen Register durchsucht werden und die Soll Zeiten in O2 - O22 und die Ist Zeiten in P2 - P22 übertragen werden.
Ich habe mal die Datei angehängt.
Vielen dank für die Mühe.

MFG Jens

[Paul1206]

Hallo Jens,

diese Prozedur ins Modul des Tabellenblattes "Gesamt":

Code: Alles auswählen

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Ws As Object, arrDate(), k&, zDatum As Variant
    If Not Intersect(Target, Range("M2")) Is Nothing Then
        ReDim arrDate(1 To 30, 1 To 4)
        For Each Ws In ThisWorkbook.Sheets
            If IsDate(Ws.Name) Then
                k = k + 1
                arrDate(k, 1) = CDate(Ws.Name)
                zDatum = Application.Match(Target, Sheets(Ws.Name).Columns(17), 0)
                If Not IsError(zDatum) Then
                    arrDate(k, 2) = Sheets(Ws.Name).Cells(zDatum, 19)
                    arrDate(k, 3) = Sheets(Ws.Name).Cells(zDatum, 20)
                    arrDate(k, 4) = IIf(arrDate(k, 3) - arrDate(k, 2) < 0, Format(CDate(arrDate(k, 3) - arrDate(k, 2)), "-hh:mm"), Format(CDate(arrDate(k, 3) - arrDate(k, 2)), "hh:mm"))
                End If
            End If
        Next
        Range("N2:P30").ClearContents
        Range("N2").Resize(k, 4) = arrDate
    End If
End Sub

Die anderen Prozeduren schreibst du in ein allgemeines Modul. Bei VBA zukünftig in die 1. Zeile des jeweiligen Moduls Option Explicit schreiben. Dies verhindert unsinnige Fehler und zwingt zu dich zu einer sauberen Deklaration von Variablen, zumal du vermutlich noch nicht viel Erfahrung mit VBA hast.

Dann sollte man, wenn Hilfe erforderlich ist, die Fragestellung/Problembeschreibung so anlegen, dass man wenigstens die elementaren Zusammenhänge darin abbildet. Dann wird es auch recht zügig Hilfe geben.

So genug geholfen und Hinweise gegeben.

Gruß Uwe