Office: (Office 365) Personalplanung und Qualifikationsauswertung

Hallo Forum ich habe versucht mit ChatGPT folgende Problemstellung zu lösen, ohne Erfolg: Hier die Fragestellung:

Ziel:
Ich möchte eine Excel-Tabelle für die Personalplanung nutzen, um die Anwesenheit und Qualifikationen meiner Mitarbeiter auszuwerten. Dabei sollen Mindestanforderungen an bestimmte Qualifikationen pro Tag überprüft werden. Zusätzlich muss berücksichtigt werden, dass ein Mitarbeiter mit mehreren Qualifikationen immer nur eine Qualifikation erfüllen kann. Es gibt eine Priorisierung der Qualifikationen (Fzf > DL > EL).

Tabellenaufbau:

1. Blatt "Funktionen"
   
   • Spalte A: Namen der Mitarbeiter
   • Spalte B: Qualifikation EL (1 = Ja, 0 = Nein)
   • Spalte C: Qualifikation DL (1 = Ja, 0 = Nein)
   • Spalte D: Qualifikation Fzf (1 = Ja, 0 = Nein)
2. Blatt "Personalplanung"
   
   • Spalte A: Namen der Mitarbeiter
   • Spalten B bis AF: Tage des Monats (1. bis 31. Tag)
   • Werte in den Tages-Spalten:
     • DS: Mitarbeiter ist anwesend
     • ND: Mitarbeiter ist nur Nachts anwesend. Funktion soll mit 0,5 gezählt werden.
     • TD: Mitarbeiter ist nur Tags anwesend. Funktion soll mit 0,5 gezählt werden.
3. Blatt "Auswertung"
   
   • Spalte A: Tage des Monats (1 bis 31)
   • Spalte B: Mindestanzahl der Mitarbeiter mit Qualifikation EL
   • Spalte D: Mindestanzahl der Mitarbeiter mit Qualifikation DL
   • Spalte F: Mindestanzahl der Mitarbeiter mit Qualifikation Fzf
   • Spalten C, E und G: Auswertung der tatsächlich anwesenden Mitarbeiter mit den jeweiligen Qualifikationen.

Anforderungen:

1. Zählung der Qualifikationen pro Tag:
   In den Spalten C (EL), E (DL) und G (Fzf) des Blatts "Auswertung" soll die Anzahl der anwesenden Mitarbeiter mit der jeweiligen Qualifikation gezählt werden.
   
   • Wichtig: Ein Mitarbeiter kann pro Tag nur eine Qualifikation erfüllen, und es gilt folgende Priorisierung:
     • Fzf hat höchste Priorität
     • Danach DL
     • Danach EL
2. Berücksichtigung der Anwesenheit:
   Es sollen nur Mitarbeiter gezählt werden, die im Blatt "Personalplanung" mit DS als anwesend markiert sind.
   
   
3. Mindestanforderungen:
   Die Auswertung soll mit den Mindestanforderungen (EL_min, DL_min, Fzf_min) verglichen werden, um zu prüfen, ob die Anforderungen pro Tag erfüllt sind.
   

Aktueller Stand und Probleme:
Ich habe versucht, die Auswertung mit Excel-Formeln (z. B. SUMPRODUCT, IF, MATCH) umzusetzen, aber folgende Probleme treten auf:

1. Fehlerhafte Werte wie #WERT! oder #NV:
   Diese entstehen bei falschen Verweisen oder nicht übereinstimmenden Bereichen zwischen den Tabellen.
   
   
2. Gleiche Ergebnisse für jeden Tag:
   Die Auswertung liefert für jeden Tag identische Werte, obwohl die Anwesenheit und Qualifikationen unterschiedlich sind.
   

Frage an das Forum:
Wie kann ich diese Auswertung am besten in Excel umsetzen, sodass:

• Mitarbeiter korrekt anhand ihrer Qualifikationen und Anwesenheit gezählt werden,
• die Priorisierung der Qualifikationen (Fzf > DL > EL) berücksichtigt wird,
• Jeder anwesende Mitarbeiter nur für eine Funktion gezählt wird. (Wenn für Fzf gezählt wurde, steht er nicht mehr für DL oder EL zur Verfügung.

Falls jemand eine funktionierende Lösung (z. B. mit verbesserten Formeln, Hilfsspalten oder VBA) hat, wäre ich sehr dankbar für Tipps oder Beispiele.

 

Moin iluvatar,

Du beschreibst Dein Thema zur Datenverarbeitung schon als Ergebnis auf Tabellenblättern mit fertigen Layout. Ich fürchte, der einzige Weg, das schneller zu lösen ist, dass Du dieses Papierdenken mal als erstes über Bord wirfst. Es hat -sorry to be that hard- nichts mit EDV zu tun.

Ein Datenmodell, das zu jedem Mitarbeiter seine Stammdaten wie Namen, Qualifikation und Verfügbarkeit enthält, im Abgleich mit Deiner Planung ist da ein (!) deutlich besserer Ansatz. Eigentlich musst Du dieser Daten-Basis dann nur noch die richtige Frage stellen:

Sind mit meiner Planung für Schicht X alle Qualifikationen verfügbar? ​

Auf dieses Ergebnis (ausgegeben als MsgBox oder Tabelle) -und die tatsächlich verfügbaren Mitarbeiterkapazitäten (Arbeitszeitkonto als Stichwort)- kannst Du dann durch Personalverschiebungen reagieren und erneut abfragen.

Ein klassisches Query-Thema. Ich bin relativ sicher, dass Dir die PowerQuery-Fachleute hier zügig zu einer Lösung verhelfen können.

Ich könnte es (vermutlich etwas aufwändiger) mit VBA, allerdings mit der Option, dass dann zumindest theoretisch gültige Planungen automatisch erstellt werden könnten. Ganz sicher würde dann ich (aber das nur am Rande) Deine kryptischen Abkürzungen etwas vereinfachen. Was bitte bedeutet Fzf ? Hierarchische Systeme wie A besser B besser C oder eine Binär-Reihe Q1 bis Q3: Wer Q6 hat kann Q1 + Q2 + Q3, Q4 kann Q1 + Q3, usw. Daraus ließe auch gut der "Verbrauch" der Qualifikationen errechnen.

Schöne Grüße

 

Hallo

Ich habe mir erlaubt deine Datei nur auf die schnelle an zuschauen.
Die Tabellenblätter Funktionen und Personalplanung würde ich an deine Stelle als formatierte Tabellem (Intelligente Tabellen) aus gegeben bei dieser Datenstruktur.
Dann klappt das ohne Probleme mit der Auswertung ganz ohne Formeln und ohne VBA.
Ist für die Lösung Power Query ???
Werde am Wochenende mal wieder aus Zeitgründen wieder rein schauen hier.

 

Moin

Leider fehlt das Wunschergebnis.

 

Hallo,

in der beiliegenden Datei habe ich folgende Änderungen durchgeführt:

1) die Tabelle im Arbeitsblatt "Funktionen" habe ich in eine intelligente Tabelle namens "tbFunktionen" umgewandelt mit einer zusätzlichen berechneten Spalte "Effektiv".

2) die Tabelle im Arbeitsblatt "Personalplanung" habe ich in eine intelligente Tabelle namens "tbPlanungen" umgewandelt.

3) im Arbeitsblatt "Auswertung" habe ich im Bereich der Tabelle "1.Versuch" 3 Arrayformeln eingefügt:
in Zelle C3: =LET(suSpPlan; NACHSPALTE(tbPlanungen; LAMBDA(sp; ZÄHLENWENNS(sp; "DS"; tbFunktionen[EL]; 1))); WEGLASSEN(MTRANS(suSpPlan); 1))
in Zelle E3:=LET(suSpPlan; NACHSPALTE(tbPlanungen; LAMBDA(sp; ZÄHLENWENNS(sp; "DS"; tbFunktionen[DL]; 1))); WEGLASSEN(MTRANS(suSpPlan); 1))
in Zelle G3:=LET(suSpPlan; NACHSPALTE(tbPlanungen; LAMBDA(sp; ZÄHLENWENNS(sp; "DS"; tbFunktionen[Fzf]; 1))); WEGLASSEN(MTRANS(suSpPlan); 1))

4) im Arbeitsblatt "Auswertung" habe ich im Bereich der Tabelle "2.Versuch" 3 Arrayformeln eingefügt:
in Zelle K3:=LET(suSpPlan; NACHSPALTE(tbPlanungen; LAMBDA(sp; ZÄHLENWENNS(sp; "DS"; tbFunktionen[Effektiv]; "EL"))); WEGLASSEN(MTRANS(suSpPlan); 1))
in Zelle M3:=LET(suSpPlan; NACHSPALTE(tbPlanungen; LAMBDA(sp; ZÄHLENWENNS(sp; "DS"; tbFunktionen[Effektiv]; "DL"))); WEGLASSEN(MTRANS(suSpPlan); 1))
in Zelle O3:=LET(suSpPlan; NACHSPALTE(tbPlanungen; LAMBDA(sp; ZÄHLENWENNS(sp; "DS"; tbFunktionen[Effektiv]; "Fzf"))); WEGLASSEN(MTRANS(suSpPlan); 1))

Dabei habe ich bei der Tabelle für 2.Versuch die Priorisierung Fzf > DL > EL berücksichtigt, während beim 1.Versuch jede einzelne Funktion (pro Mitarbeiter) gezählt wurde.