Auswahlliste mit Filter

[diblo]

Hallo, hier ist schon wieder ein neuer, der keine Ahnung hat und eure Hilfe benötigt.

Ich habe vor einen Wochen Dienstplan zu erstellen.
In den Spalten sind die Wochentage Beispiel: Mo. b4 bis b20, Di. c4 bis c20 usw.
und in den Zeilen die Arbeitsplätze Platz 1 b5 bis h5, Platz 2 b6 bis h6.
Das personal kann ich via Auswahlliste schon eintragen.

Um Fehler zu vermeiden würde ich gerne, es so gestalten das wenn ein Name schon in einem Wochentag drin steht. Es nicht mehr möglich ist den selben Namen nochmal in den gleichen Wochentag einzutragen.

Und für jede Woche habe ich eine Spalte angelegt wo Personal drin steht welches sich im Urlaub befindet. Das soll dann auch nicht via Auswahlliste im Dienstplan verfügbar sein.

Ist das überhaupt möglich??

[rammi22]

...In den Spalten sind die Wochentage Beispiel: Mo. b4 bis b20...

???
B4 - B20 sind Zellen, keine Spalten

...und in den Zeilen die Arbeitsplätze Platz 1 b5 ...

...und B5 wäre schon besetzt... Irgendwie konfus...

Lade doch einfach mal deine Tabelle hoch

[diblo]

Ach ja Sorry.

Hab ich mal hoch geladen

[diblo]

Ja super, so was habe ich mir vorgestellt, ich werde gleich mal versuchen. Es für eine andere Woche hin zu bekommen. Aber ich muss ehrlich sagen momentan Verstehe ich leider nicht den Zusammenhang deiner Formeln.

[Axel Richter]

Hallo,

der Tilde-Operator, den HeritzP da verwendet, ist ziemlich neu, siehe: https://help.libreoffice.org/Calc/Opera ... operatoren. Er verbindet damit den Bereich, in dem das Personal steht mit dem Bereich, wo die Urlauber stehen und den Bereichen, wo die Namen in den Wochenspalten stehen und zählt dann in dem so verbundenen Bereich, ob Namen aus dem Personal-Bereich mehrfach vorkommen. Die werden dann ausgeschlossen.

Ich würde da anders vorgehen. Ich würde die Namen aus dem Personal-Bereich ausschließen, die schon mindestens einmal in den Bereichen Urlauber und in den Wochenspalten vorkommen:

Code: Alles auswählen

=WENN((ZÄHLENWENN(Namen_in_Spalte;Siemenspersonal)=0)*(ZÄHLENWENN(Urlauber_in_Woche;Siemenspersonal)=0);Siemenspersonal)

Diese Formel nutzt die Namen

Siemenspersonal =

Code: Alles auswählen

$Personal.$A$2:$A$14

(hattest Du ja schon)

Urlauber_in_Woche =

Code: Alles auswählen

INDEX($I$1:$I$1000;5+GANZZAHL((ZEILE($I3)-ZEILE($A$3))/14)*14):INDEX($I$1:$I$1000;14+GANZZAHL((ZEILE($I3)-ZEILE($A$3))/14)*14)

Namen_in_Spalte =

Code: Alles auswählen

INDEX($A$1:$I$1000;5+GANZZAHL((ZEILE($A3)-ZEILE($A$3))/14)*14; SPALTE(A$1)):INDEX($A$1:$I$1000;14+GANZZAHL((ZEILE($A3)-ZEILE($A$3))/14)*14;SPALTE(A$1))

Sie macht sich außerdem die Matrix-Formel-Funktionalität zu Nutze. ZÄHLENWENN will ja normalerweise nur *einen* Wert bei Kriterium. Hier wird aber ein ganzer Bereich (Siemenspersonal) als Kriterium angegeben. Dadurch wird im Hintergrund je Eintrag in Siemenspersonal ein Ergebnis von ZÄHLENWENN zurückgegeben und ergibt so eine Matrix von z.B. {0;1;0;1;0;0;0;...}. Diese wird dann mit 0 verglichen und ergibt {WAHR; FALSCH; WAHR; FALSCH; WAHR; WAHR; WAHR;...} je nach dem ob =0 oder nicht. Wenn man zwei solcher Matrixen mit einer normalen Multiplikation verbindet, ergibt sich quasi eine UND-Verknüpfung der Matrixen. Es ergibt sich somit je Eintrag in Siemenspersonal nur dann WAHR, wenn beide Matrixen für diese Stelle WAHR enthalten, der Eintrag also weder in Namen_in_Spalte noch in Urlauber_in_Woche enthalten ist.

Das Hauptproblem ist es, das ganze so variabel zu gestalten, dass die Kalenderwochen vervielfältigt werden können. Das habe ich erreicht, indem ich davon ausgegangen bin, dass alle Kalenderwochen 14 Zeilen enthalten. Damit müssen sich die Bereiche Urlauber_in_Woche und Namen_in_Spalte alle 14 Zeilen um 14 Zeilen nach unten verschieben.

Code: Alles auswählen

=GANZZAHL((ZEILE($A3)-ZEILE($A$3))/14)*14

berechnet diesen Offset dadurch, dass die Zeile in

Code: Alles auswählen

ZEILE($A3)

variabel ist.

Damit können neue Kalenderwochen erzeugt werden, indem einfach die 14 Zeilen der vorherigen Kalenderwoche um 14 Zeilen nach unten kopiert werden. Das funktioniert bis Zeile 1000, wegen

Code: Alles auswählen

INDEX($I$1:$I$1000...

in den Namensformeln. Das kann man aber erweitern, wenn nötig. Bitte aber nicht zu üppig, denn Matrix-Formeln werden bei massiver Verwendung schnell zu sehr großen Performance-Bremsen. Dann lieber je Quartal bspw. ein neues Tabellenblatt verwenden.

Siehe Anlage. Die Spalten ab J sind nur Erläuterung und können gelöscht werden.

Da habe ich auch noch eine bedingte Formatierung drin, die Zellen rot färbt, wenn man *nach* Namenszuweisung in den Spalten noch Urlauber nachträgt, die schon in den Spalten stehen.

viele Grüße

Axel

[diblo]

Super aber wie kann ich hier den Personal bereich erweitern für die Auswahl? z.B bis a20

[Axel Richter]

Hallo,

wie kann ich hier den Personal bereich erweitern für die Auswahl? z.B bis a20

Indem Du den Namen "Siemenspersonal" entsprechend anpasst und die weiteren Namen dann im Tabellenblatt "Personal" einträgst?

viele Grüße

Axel

[diblo]

Wo und wie finde ich "Siemenspersonal" um es bearbeiten zu können.
Leider bin ich noch nicht so Vertraut mit dieser Materie.
Ich kenne es nur das Formeln in einer Zelle drin sind.

[Axel Richter]

Hallo,

wer hat denn die Datei erstellt, die Du hier hochgeladen hast? Da war doch der Name schon drin.

Öffne die Datei mit Calc. Wähle Einfügen - Namen - Verwalten. Hier kannst Du alle definierten Namen verwalten. Der Rest sollte selbsterklärend sein.

https://help.libreoffice.org/Calc/Naming_Cells/de

viele Grüße

Axel

[diblo]

Ach ja jetzt fällt es mir wieder ein. Unter Daten- Gültigkeit musste ich einen Namen für die Auswahl zuordnen.