Kalenderübersicht mit Rechenfunktion (Datenbank)

[Spinner87]

Hallo, ich habe mal wieder ein Problem. Ich habe einen Kalender in Excel erstellt. Soweit so gut. Dieser Kalender zeigt mir die Wochen für 4 Jahre (Brauche ich als Überblick) an. 

Sinn der Tabelle ist Auszubildende auf bestimmte Plätze zu verteilen. Damit möchte ich einen Überblick erhalten, ob der Soll der verschiedenen Stellen erfüllt wird und gleichzeitig ob die Belastung der einzelnen Plätze gleichmäßig verteilt ist. So ganz bin ich mit meiner Tabelle nicht zufrieden. Und sie rechnet auch noch nicht korrekt. Bin aber für völlig andere Aufbauten offen ^^

Die Tabelle ist so aufgebaut, dass ich in dem Arbeitsblatt "Kalender KW" in dem jeweiligen Kalenderjahr und der entsprechenden Kalenderwoche einen Namen (eines Azubis) per Dropdownfeld auswählen kann. Links die Spalte A sind die jeweiligen Plätze. Einige stehen öfter da, weil z. B. auf dem Platz 1 können zeitgleich 3 Auszubildende sein. Auf dem Platz 9 zeitgleich 12 und auf dem Platz ZS zeitgleich nur einer.

Aufgrund der Eingabe soll mir dann eine Summe gebildet werden in dem Arbeitsblatt "Plätze" zu dem entsprechenden Kalenderjahr. Außerdem in dem Blatt "Auswertung" soll mir die Anzahl der jeweiligen Summen an Wochen auf den jeweiligen Plätzen der Auszubildenden die in der Spalte A automatisch durch das Arbeitsblatt "Auszubildende" stehen angezeigt werden. Da neben stehen die "Soll"Werte die ich dann entsprechend erfasse und rechts die Differenz.

Die Kunst ist aber, dass die Auszubildenden bzw. die Kalenderjahre in der Zukunft flexibel sind. Heißt Wenn ich dann für das Kalenderjahr 2025 die erfasse oder 2026 muss die Tabelle entsprechend weiter funktionieren. Ich hoffe ich habe das verständlich erklärt. Die Tabelle hängt dran. Als Namen der Azubis habe ich gerade "a" oder "aa" oder "aaa" genommen. Das sind die unterschiedlichen Einstellungszeiträume.

Ich hoffe sehr das ihr mir helfen könnt. Bis jetzt bin ich noch nie enttäuscht worden ^^ ICh danke euch schonmal im voraus. 

Euch einen schönen Sonntag.
LG Mike

[shift-del]

Moin Mike

Ich habe mal eben die Anzahl der Formeln drastisch reduziert.
Der Rest läuft über Pivot oder PQ.
Über bedingte Formatierung wird angezeigt ob mehr Azubis als vorgegeben eingesetzt werden.

[Spinner87]

Hi Detlef,

danke dafür. Grundsätzlich gar nicht verkehrt. Sieht auf dem ersten Blick ganz gut aus. Hast du noch eine Idee wie ich ein PDF für die Abteilungen daraus basteln kann. Also für das Jahr 2024 als Übersicht, wann die jeweilige Abteilung mit Azubis "belastet" ist?

Bei "Einstellungen" müsste ich für jeden Azubi eine KW erstellen. Aktuell reden wir von ca. zeitgleich 21 Azubis. Das könnte etwas umständlich werden. Über die 3 Jahre sind es zu Spitzenzeiten über 40 Azubis. Daher war mein Gedanke als Spaltenüberschrift die KW und in die Zeile die Abteilungen bereits vorgegeben. Wobei mir dein Ansatz sehr gut gefällt. 

Hast du dafür evtl. eine andere Idee?

Danke dir. LG Mike

[shift-del]

Bei "Einstellungen" müsste ich für jeden Azubi eine KW erstellen.

Falls du "Einteilungen" meinst, dann musst du nur für jede tatsächliche Einteilung einen Eintrag machen - nicht für alle möglichen Kombinationen.
Wenn Olaf nur in 12 Wochen über 3 Departments verteilt eingesetzt wird, dann sind das nur 12 Einträge.

[Spinner87]

Guten Morgen, ja sorry meinte ich natürlich. Aber genau das ist doch das Problem. Ich habe um bei deinem Beispiel zu bleiben 21 Azubis die in den 12 Wochen da sind. Dann habe ich 21 die eine Woche bzw. Die 12 Wochen zu erfassen. Wenn ich das jetzt aufs Jahr multipliziere hätte ich bei 21 Azubis x 52 Wochen 1092 Zeilen zu erfassen.

[shift-del]

Wenn alle 21 Azubis für 52 Wochen eingeteilt werden dann hast du recht.
Aber die 1092 Einträge hättest du auch in deiner Kreuztabelle machen müssen.

[ynckprn]

HI, 

Code:

Option Explicit

Sub UpdatePivotTable()
    ' Set the pivot table and its source data
    Dim pivotTable As PivotTable
    Set pivotTable = ThisWorkbook.Worksheets("Plätze").PivotTables("PivotTable1")
    Dim sourceData As Range
    Set sourceData = ThisWorkbook.Worksheets("Kalender KW").Range("A1:ZZ1000")

    ' Update the pivot table's data source
    pivotTable.ChangePivotCache pivotCache
    pivotTable.PivotCache.Refresh

    ' Update the pivot table's filters
    pivotTable.PivotFields("Jahr").PivotItems("2025").Visible = True
    pivotTable.PivotFields("Jahr").PivotItems("2026").Visible = True

    ' Clear the pivot table's data
    pivotTable.ClearAllData

    ' Refresh the pivot table
    pivotTable.Refresh

    ' Update the formulas on the "Plätze" sheet
    UpdateFormulas

    ' Update the formulas on the "Auswertung" sheet
    UpdateFormulasForAuswertung
End Sub

Sub UpdateFormulas()
    ' Update the formulas on the "Plätze" sheet
    Dim formula As String
    formula = "=SUMIFS(Kalender KW!A:A, Kalender KW!B:B, ""&A2&"")"
    ThisWorkbook.Worksheets("Plätze").Range("A2").Formula = formula

    ' Update the formulas for each place
    Dim place As Integer
    For place = 1 To 12
        formula = "=SUMIFS(Kalender KW!A:A, Kalender KW!B:B, ""&A2&"")"
        ThisWorkbook.Worksheets("Plätze").Cells(place, 2).Formula = formula
    Next place
End Sub

Sub UpdateFormulasForAuswertung()
    ' Update the formulas on the "Auswertung" sheet
    Dim formula As String
    formula = "=A2-B2"
    ThisWorkbook.Worksheets("Auswertung").Range("A2").Formula = formula

    ' Update the formulas for each place
    Dim place As Integer
    For place = 1 To 12
        formula = "=A2-B2"
        ThisWorkbook.Worksheets("Auswertung").Cells(place, 2).Formula = formula
    Next place
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Update the pivot table whenever a change is made in the "Kalender KW" sheet
    If Target.Parent.Name = "Kalender KW" Then
        UpdatePivotTable
    End If
End Sub

[Spinner87]

Lässt es sich das noch über eine Funktion lösen das ich sage max Mustermann von kW 3 bis kW 10 in der Abteilung xy? Und dann erfolgt eine entsprechende Erfassung? 

Bei meiner Tabelle waren die kW und die Abteilung bereits erfasst heißt ich hätte nur 21 Klicks bei dir sind es 63 Klicks. 

Mir gefällt dein Ansatz aber sehr gut. Vielleicht hast du ja noch eine Idee oder jemand anderes? 

Vielen Dank auf jedenfall

---

Hi danke für den Code. Ich kenne mich damit leider nicht aus aber auf Arbeit (öffentlicher Dienst) ist die Funktion gesperrt. Ich kann leider keine Makros und keine Codes nutzen.

[shift-del]

Lässt es sich das noch über eine Funktion lösen das ich sage max Mustermann von kW 3 bis kW 10 in der Abteilung xy? Und dann erfolgt eine entsprechende Erfassung? 

Bestimmt läßt sich das einrichten. PQ kennt Sequenzerzeugung ({401..433}).
Die Funktion speist dann die Einteilungstabelle.
Aus praktiablen Gründen müssen beide Werte innerhalb des selben Jahres liegen.

Ich glaube aber dass Einzelwochenerfassung immer noch schneller ist.
Man muss ja nicht das Dropdown verwenden. Eintippen oder kopieren ist immer möglich.

[Spinner87]

Bei 21 Azubis die teilweise 12 bis 15 Wochen in einer Abteilung sind glaube ich sollte schneller gehen wenn man das direkt eingeben kann denke ich. Das ist für meine Chefin. 

Könntest du mir das mal als Variante erstellen? Dann kann ich das mal probieren? Damit es im gleichen Kalenderjahr sein muss, ist halb so wild man kann ja sagen 

Max Mustermann von 1.11.24 bis 31.12.24 und dann erneut von 01.01.25 bis 30.01.25. Das sollte ja dann nicht das Problem sein. 

Danke dir.