Weitere Formeln in Teilergebnis-Zeilen einfügen

23.07.2025 16:58:09

AndiP28

Hallo,

ich werte Zeitdaten aus. Dazu werden Teilergebnisse nach Person und Datum (verknüpftes Feld) gebildet. In jede dieser Teilergebnis-Zeilen möchte ich noch in weiteren Spalten Formeln einfügen. Meine Programmierung hierzu sieht aktuell so aus:

'Suche nach der nächsten Teilergebnis-Zeile:

Range("A2").Select

            Do Until Right(ActiveCell, 8) = "Ergebnis"

            ActiveCell.Offset(1, 0).Activate

        Loop

'Formeln ergänzen:

            ActiveCell.Offset(0, 1).Activate

            ActiveCell.FormulaR1C1 = "=R[-1]C"

            ActiveCell.Offset(0, 1).Activate

            ActiveCell.FormulaR1C1 = "=R[-1]C"

            ActiveCell.Offset(0, 1).Activate

            ActiveCell.FormulaR1C1 = "=R[-1]C"

            ActiveCell.Offset(0, 5).Activate

            ActiveCell.FormulaR1C1 = _

                "=IF(AND(RC[-2]>TIME(9,0,0),RC[-1]TIME(6,0,0),""0:30"", ""0:00""))"

            ActiveCell.Offset(0, 1).Activate

            ActiveCell.FormulaR1C1 = "=SUMIF(C[-9],R[-1]C[-9],C[-3])-RC[-1]"

            ActiveCell.Offset(0, 1).Activate

            ActiveCell.FormulaR1C1 = _

                "=IF(R[-1]C[-8]=R[1]C[-8],1-R[-1]C[-5]+R[1]C[-5],"""")"

            ActiveCell.Offset(0, 1).Activate

            ActiveCell.FormulaR1C1 = _

                 "=IF(OR(AND(RC[-1]>TIME(0,1,0),RC[-1]TIME(10,0,0)),""überprüfen"","""")"

            Reihe = ActiveCell.Row

            Range("B" & Reihe & ":L" & Reihe).Select

            Selection.Copy

            ActiveCell.Offset(1, -1).Activate



        Do Until ActiveCell = ""

            Do Until Right(ActiveCell, 8) = "Ergebnis"

                ActiveCell.Offset(1, 0).Activate

                If ActiveCell = "" Then GoTo Ende

                

            Loop

            ActiveCell.Offset(0, 1).Activate

            ActiveSheet.Paste

            ActiveCell.Offset(1, -1).Activate

        Loop

Da wir zahlreiche Mitarbeiter haben, geht es um ca. 30.000 Zeilen, die so durchlaufen werden müssen, was aber ewig (ich spreche von mehr als 1 Stunde) dauert.
Bestimmt geht das eleganter und vor allem auch schneller.

Vielen Dank für Eure Hilfe

Beispieldatei: https://www.herber.de/bbs/user/178141.xlsx

AW: Weitere Formeln in Teilergebnis-Zeilen einfügen

23.07.2025 17:57:33

Yal

Hallo Andreas,

"Excel gut" bedeutet in dem Fall, man kann Bäume mit dem Axt sehr gut fällen, aber gleichzeitig ignorieren, dass es Kettensäge seit Jahren gibt...

Wenn Du uns genauer erklärst, was Du aus diesen Daten machen möchtest, könnten wir dir zeigen, wie man mit ein Bisschen Power Query Transformation (Standardteil von Excel seit Vers. 2016) die Daten leicht und zuverlässig mit einer Pivottable auswerten kann.

VG
Yal

AW: Weitere Formeln in Teilergebnis-Zeilen einfügen

23.07.2025 20:20:50

AndiP28

Hallo Yal,

danke für Deine Anleitung.

Um bei Deinem Vergleich zu bleiben: Die Kettensäge sägt hier auf einem bereits mit der Axt gefällten Baum herum…

Dies liegt aber vor allem an meiner schlechten Erklärung. Deshalb hier nachgereicht:

Die Ausgangslage sind die Spalten A bis H.
In der Zeile der Teilergebnisse sind zunächst nur die Spalte A (Verknüpfung), G (Arbeitszeit) und H (gestempelte Pausen) durch die Teilergebnis-Funktion gefüllt.

Was ich nun möchte, ist dass

1. In Spalte B die Personalnummer des Mitarbeiters aus der vorherigen Zeile übernommen wird
2. In Spalte C der Name des Mitarbeiters aus der vorherigen Zeile übernommen wird
3. In Spalte I der Pausenabzug ermittelt wird, wobei
a. die Pause 45 Minuten betragen muss, wenn die Arbeitszeit aus Spalte G mehr als 9 Stunden beträgt,
b. die Pause 30 Minuten betragen muss, wenn die Arbeitszeit mehr als 6 Stunden beträgt,
c. mindestens die gestempelte Pause aus Spalte H betragen muss
4. In Spalte J die Arbeitszeit des jeweiligen Tages addiert und die Pausen abgezogen werden.
5. In Spalte K die Ruhenszeit ermittelt wird – dies ist die Zeit zwischen der letzten Gehen-Zeit eines Tages und der ersten Kommen-Zeit des nächsten Tages.

Dies habe ich mit meinem übermittelten Makro eintragen lassen, was aber bei einer hohen Zeilenzahl ewig dauert.

Die hauptsächliche Frage für mich ist nun, wie ich die entsprechenden Formeln z.B. aus Zeile 4 schnell in viele Teilergebnis-Zeilen eintragen lassen kann.

Viele Grüße und nochmals Danke

AW: Weitere Formeln in Teilergebnis-Zeilen einfügen

24.07.2025 18:42:44

Yal

Hallo Andreas,

ja, man unterschätzt, dass "Erklären" auch eine Kompetenz ist, die auch mühsam gelernt werden muss (bin selber immer noch dabei :-)

Mit eigenen Worte: Du willst die gebuchten Zeiten prüfen, ob die Pausenzeit richtig eingestempelt worden sind.
D.h. für jeden Tag und Mitarbeiter rechnen, wieviel Arbeitszeit und wieviele Pause vorliegen.

In einem Datensystem, das die Aufgabe einfach macht, würden die Anfang und Ende pro Arbeitsphase auf eine Zeile vorliegen ("Datensatz") und daraus die Arbeitszeit zu rechnen. Dasselbe mit den Pausen und noch einmal separat die Ruhezeit (Unterschied zwischen letzte "Gehen" am Tag1 und erste "Kommen" am Tag 2)

In folgende Beispiel habe ich deine Eingangstabelle so abgeschnitten, dass nur Personal (Nr + Name), Datum, Art und Zeit vorliegen (Arbeitsblatt "Basis"). Daraus die "Kommen" und "Gehen" pro Tag zusammengebracht (unabhängig von deren Reihenfolge!) und die Arbeitszeit und Pausen berechnet. Ruhezeit ist separat berechnet worden.

Die Auswertung erfolgt mit einer Pivottable. Aber die Datensätze herauszufiltern, bei dem Pausenzeit fehlen, wäre nur noch ein paar Klicks entfernt.
https://www.herber.de/bbs/user/178149.xlsx

(nun, es ist vielleicht weit entfernt, von was Du gefragt hattest. Aber ist ist nun mal so, dass wir nicht nur helfen, sondern vor allem unseren Spass haben ;-) Die Aufgabe, verschiedene Datensätze genau zusammenzubringen war für mich spannend genug, dass ich mich ans Werk setzen könnte).

VG
Yal

AW: Weitere Formeln in Teilergebnis-Zeilen einfügen

23.07.2025 18:21:39

Yal

Probieren geht über studieren:
- gehe auf die Zelle A1
- Menü "Einfügen", "Tabelle"
- im Menü "Tabellenentwurf", ändere den Namen der Tabelle von "Tabelle1" in "tblZeiten" (absolut nicht notwendig. Nur um zu zeigen)
- Menü "Daten", "Aus Tabelle/Bereich"
- Du bist im Power Query Editor
- Spalten "Arbeitszeit" bis "Ruhezeit" markieren
- Rechtsklick auf die Spaltenkopf, "Typ ändern", "Dauer"
- Spalte "Datum" markieren, Kopf rechtsklicken, "Typ ändern", "Datum"
- Spalte "Verknüpfung" nach Text filtern, "Endet mit...", "Ergebnis"
- Spalten "Verknüpfung", "Art", "Zeit", "Spalte3" markieren, Kopf rechtsklicken "entfernen"
- Menü "Datei", "Schliessen & laden in..."
- "Pivottable-Bericht" in einem neuen Blatt
- Du bist wieder in Excel klassik
- verschiebe den Feld "Datum" aus dem Feld-Liste im Assistent rechts in dem Quadrant "Zeilen"
- normalerweise wird das Datum erkannt und daraus Jahr, Quartal und Monat extrahiert.
- schiebe alles zurück in die Feld-Liste, was Du nicht brauchst
- verschiebe "Personal" ins Quadrant "Spalten"
- verschiebe "Arbeitszeit2" in Quadrant "Werte". Daraus entsteht "Summe von Arbeitszeit2"
- weitere Spielerei wie es dir gefällt.

Wenn die Daten aus einer Datei kommen, kannst Du diese Power Query Transformation + Auswertung direkt auf die Datei richten. Wenn die Datei immer dieselbe Namensmuster mit Zeitstempel-Endung haben, kannst Du alle Dateien aus einem Verzeichnis mit einer einzige PQ auswerten.

Keine Formel. Keine VBA.

Mehr Info über Power Query: https://excelhero.de/pivot-tabellen/pivot-tabellen-ganz-einfach-erklaert/

VG
Yal

AW: Weitere Formeln in Teilergebnis-Zeilen einfügen

23.07.2025 19:25:42

daniel

Hi
es ist sehr langsam, wenn man jede Zelle einzeln befüllt.
es ist noch viel langsamer, wenn man jede Zelle vorher selektiert und aktiviert. Das darf man auch weglassen. Der Recorder muss das so aufzeichnen, weil Mausbenutzer so arbeiten, aber Makros können und dürfen auch Zellen direkt bearbeiten, ohne sie zu selektieren und aktivieren.

man muss auch nicht jede Zelle einzeln befüllen.
Alle Zellen, die die selbe Formel bekomme (per Copy-Paste übertragbar), kann man auch in einem Schritt gemeinsam befüllen.

der folgende Code blendet zunächst über die Gruppierung die Zwischenzeilen aus und schreibt dann spaltenweise die Formeln in die übrig gebliebenen sichtbaren Zellen.
das sollte ratz-fatz gehen:

Sub Makro1()

ActiveSheet.Outline.ShowLevels RowLevels:=2

With ActiveSheet.Cells(1, 1).CurrentRegion 'Liste auf dem Tabellenblatt

    With Intersect(.Cells, .Offset(1, 0)) ' Liste ohne die Überschrift

        With .SpecialCells(xlCellTypeVisible) ' nur die sichtbaren Zeilen

            Intersect(Range("B:D"), .Cells).FormulaR1C1 = "=R[-1]C"

            Intersect(Range("I:I"), .Cells).FormulaR1C1 = "=IF(AND(RC[-2]>TIME(9,0,0),RC[-1]TIME(6,0,0),""0:30"", ""0:00""))"

            Intersect(Range("J:J"), .Cells).FormulaR1C1 = "=SUMIF(C[-9],R[-1]C[-9],C[-3])-RC[-1]"

            Intersect(Range("K:K"), .Cells).FormulaR1C1 = "=IF(R[-1]C[-8]=R[1]C[-8],1-R[-1]C[-5]+R[1]C[-5],"""")"

            Intersect(Range("L:L"), .Cells).FormulaR1C1 = "=IF(OR(AND(RC[-1]>TIME(0,1,0),RC[-1]TIME(10,0,0)),""überprüfen"","""")"

        End With

    End With

End With

ActiveSheet.Outline.ShowLevels RowLevels:=3

            

End Sub

Gruß Daniel

AW: Weitere Formeln in Teilergebnis-Zeilen einfügen

23.07.2025 20:36:37

AndiP28

Hallo Daniel,

danke - das scheint die Lösung zu sein.

Allerdings erhalte ich beim Ausführen des Makros (ich habe vorher in den Ergebniszeilen die Werte aus den Spalten B, C, D, I, J, K und L gelöscht) den Laufzeitfehler '438": Objekt unterstützt diese Eigenschaft oder Methode nicht.

Was mache ich falsch?

AW: Weitere Formeln in Teilergebnis-Zeilen einfügen

23.07.2025 20:46:01

daniel

dein Fehler ist, dass du mir verschweigst, in welcher Programmzeile der Fehler auftritt und mit welcher Datei du den Code getestet hat.
bitte teste den Code mit der Datei, die du hier hochgeladen hast.
bei mir hat der Code mit deiner Datei fehlerfrei funktioniert.

Falls du noch keine Gruppierung eingerichtet hast, könntest du du auch den Autofilter verwenden.
Ersetze dann das

ActiveSheet.Outline.ShowLevels RowLevels:=2

duch

ActiveSheet.Cells(1, 1).CurrentRegion.Autofilter Field:=1, Criteria1:="*Ergebnis"

und das

ActiveSheet.Outline.ShowLevels RowLevels:=3

am Ende durch

ActiveSheet.Cells(1, 1).CurrentRegion.Autofilter

Gruß Daniel

AW: Weitere Formeln in Teilergebnis-Zeilen einfügen

23.07.2025 21:33:09

AndiP28

Hallo Daniel,

sorry, der Fehler erscheint, wenn Spalte I gefüllt werden soll.

Die Beispieldatei diente zur Veranschaulichung, wie das Ergebnis aussehen sollte.

Füge nochmals die "Rohdatei" bei.
https://www.herber.de/bbs/user/178142.xlsx

Könntest Du das anhand der nochmals prüfen.

Tausend Dank

AW: Weitere Formeln in Teilergebnis-Zeilen einfügen

23.07.2025 21:58:37

daniel

Hi
das Problem ist die ForenSoftware, die mit Größer- und Kleiner-Zeichen nicht immer sauber umgehen kann, da diese auch zur Kennzeichnung von Formatierungen verwendet werden. Als Code formatiert, klappt es meistens, aber leider nicht immer und dann "verschwinden" manche Teile des Codes

du hast ja sicherlich noch deinen alten Origninal-Code, den du aufgezeichnet und hier eingestellt hast (da ist übrigens das gleiche passiert)
kopiere dort die Formel für die Spalte I und füge sie in den neuen Code ein.

Gruß Daniel

Weitere Formeln in Teilergebnis-Zeilen einfügen

Forumthread: Weitere Formeln in Teilergebnis-Zeilen einfügen

9
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Weitere Formeln in Teilergebnis-Zeilen einfügen

Forumthread: Weitere Formeln in Teilergebnis-Zeilen einfügen

9 Beiträge zum Forumthread Beiträge zu diesem Forumthread

9
Beiträge zum Forumthread

Beiträge zu diesem Forumthread