Zellen anhand Excel Datenbank erstellen

[mrhydejack]

Hallo liebe Leute

Ich stehe vor einer etwas komplexeren Aufgabe und habe keine Ahnung wie ich das lösen soll. Vielleicht könnt ihr mir helfen.

Ich muss Liste erstellen, die einen Zeileneintrag (Datumsangabe) pro Arbeitstag, Pro Person enthält (Der ganze Rest der Liste ist Nebensache, die kann ich selber lösen).

Ich habe folgende Informationen

Name Person
Pensum Person (Arbeitstage pro Woche)
Zeitraum = 06.01.2020 - 31.12.2020
Arbeitstage pro Woche = eine Arbeitswoche enthält 5 Arbeitstage

Nun will ich daraus mit einer Funktion / Formel oder sehr wahrscheinlich einem Makro diese Liste automatisch generieren, habe aber keine Ahnung wie. Es spielt dabei keine Rolle an welchen Arbeitstage die Person anwesend ist. Deshalb gehe ich von der Annahme aus, dass jeder immer am Montag startet und an einem Stück sein Pensum absolviert (z.B 5 Arbeitstage = Mo - FR, 3 Arbeitstage = Mo - Mi, 1 Arbeitstag = Mo). Die Liste sollte aber auch gleich die Daten enthalten.

Ausgangslage
Name / Arbeitstage pro Woche

Zielliste:
Datum / Name

Ich habe ein Beispiel angehängt.

Ich hoffe jemand kennt sich mit sowas aus

Vielen Dank bereits im Voraus!

LG

[maninweb]

Hallo,

in Deinem Profil steht Excel 2010 drin. Eine Formellösung für Excel 2010 kann ich aktuell leider nicht anbieten.

Mit einer neueren Excel Version - besser gesagt ab Excel 2016 und vielleicht interessant für andere hier - lässt sich das
mit einer Matrixformel lösen. Die Formel setzt voraus, dass in Deiner Beispieldatei in der neu hinzugefügten Spalte A
ein Index (einfach 1, 2, 3 ...) enthalten ist. Dann ergibt sich als Matrixformel (Eingabe mit Strg+Umschalt+Enter):

Code:

Für die Namen I2={INDEX($B$3:$B$9;--TEIL(TEXTKETTE(WIEDERHOLEN(TEXT($A$3:$A$9;"00");$C$3:$C$9));2*(ZEILE()-ZEILE($I$1))-1;2);1)}
Für die Daten H2= WENN(I2<>I1;DATUM(2020;1;6);H1+1)

Für's Datum der Einfachheit halber DATUM() verwendet, lässt sich aber z.B. in eine Zelle auslagern. Und ginge bis
zu 99 Personen.

Gruß

[mrhydejack]

Hallo!

Erstmal sorry, 2016 ist genau was ich brauche.

Schon mal vielen Dank für deine Hilfe!

Ich habe deine Formeln mal im File "Beispiel Aufgabe neu" genutzt.

Leider funktioniert das bei mir nicht so richtig. Ich habe in einem Register (Master) ca. 30 Personen mit unterschiedlichen Pensen (Arbeitstagen pro Woche). Daraus soll ich in einem neuen Register die Liste erstellen.
Wenn ich deine Formeln nutze, macht es mir nur zwei Einträge und der Rest weisst einen Fehler aus. Aber auch die ersten zwei können nicht stimmen. Theoretisch müsste es ja schon für die erste Person für das ganze 1 Jahr jeweils 5 Tage pro Woche erstellen. Das wären dann bei 52 Kalenderwochen bereits 260 Zeilen. Dieses Prozedere müsste es dann für jede einzelne Person aus Register "Master" ausführen, so dass im Register "Plan" schlussendlich für jede Person für das ganze Jahr jeweils einen Zeileneintrag pro Arbeitstag im Jahr 2020 existiert.

Ich bin nicht sicher ob du mich falsch verstanden hast, oder ob ich jetzt etwas in der Formel falsch gemacht habe.

Danke für die Hilfe!

Lg

[shift-del]

Moin

Ich habe da mal was (mit PQ) gebastelt.

[maninweb]

Hallo,

generell: Du musst die Formel als Matrixformel eingeben: Formel eingeben und statt Enter dann Strg+Umschalt+Enter drücken.
Danach werden die geschweiften Klammern von Excel automatisch gesetzt. In Deiner Datei steht dann in der Tabelle Plan in B2:

Code:

{=INDEX(Master!$B$2:$B$30;--TEIL(TEXTKETTE(WIEDERHOLEN(TEXT(Master!$A$2:$A$30;"00");Master!$C$2:$C$30));2*(ZEILE()-ZEILE($B$1))-1;2);1)}

Dass allerdings jetzt das ganze Jahr aufgefüllt werden sollte, habe ich übersehen. Ich würde dann eher die Lösung von shift-del
mit Power Query empfehlen und bevorzugen.

Gruß

[snb]

PHP-Code:

Sub M_snb()
    sn = Sheet1.Cells(1).CurrentRegion
    y = CLng(DateSerial(2020, 1, 4) - Weekday(DateSerial(2020, 1, 4),2))
    
    With CreateObject("scripting.dictionary")
        For j = 1 To 52
           For jj = 1 To 5
               For jjj = 2 To UBound(sn)
                  If sn(jjj, 3) >= jj Then .Item(.Count) = Array(y + 7 * j + jj, sn(jjj, 2))
               Next
           Next
        Next
        
        Sheet2.Cells(1, 4).Resize(.Count, 2) = Application.Index(.items, 0, 0)
    End With
End Sub 


[LCohen]

Hallo maninweb,

hast Du als MVP eine Erklärung dafür, warum MS - neben TEXTVERKETTEN() - zusätzlich das nirgends fremdunterstützte TEXTKETTE() bereitstellt?

Ich habe - für die Crossportabilität - bislang ausschließlich excel:TEXTVERKETTEN (docs:TEXTJOIN, LO:VERBINDEN, ...) verwendet.

[maninweb]

Hallo LCohen,

ob folgendes der Grund ist, kann ich leider nicht mit 100-prozentiger Sicherheit sagen, jedoch ist mit der Einführung von
TEXTKETTE die Funktion VERKETTEN in den Kompatibilitätsmodus gewandert. TEXTKETTE ist somit anscheinend als
vollständiger Ersatz von VERKETTEN gedacht, während TEXTVERKETTEN dann neu ist.

Gruß

[shift-del]

Hi Shift-Del

Danke für deinen Lösungsvorschlag, genau sowas suche ich. Leider habe ich keine Erfahrung mit PQ... Wäre es möglich, dass du mir eine Kurzanleitung dazu geben könntest, wie du vorgegangen bist damit ich das nachvollziehen kann oder ist das zu komplex?

Das wäre sehr hilfreich.

Besten Dank!

Die Umsetzung ist etwas komplexer als dass man diese hier im Detail erklären könnte.

Grundsätzlich:
Man erstellt eine Kalendertabelle mit Kalenderwoche und Startdatum der KW.

Die Datensätze in der Datentabelle werden dupliziert anhand der Arbeitstage pro Woche.
Dann macht man einen Crossjoin, d.h. alle Datensätze der Datentabelle werden mit allen Datensätzen der Kalendertabelle zusammengeführt.
Das Enddatum ergibt sich aus Wochenbeginn + Dauer - 1 Tag.