Bedingte Formatierung in Abhängikeit von SVerweis

Moderator: ModerationP

Bedingte Formatierung in Abhängikeit von SVerweis

Beitragvon Abalone76 » 06. Okt 2017, 16:13

Hallo Ihr Lieben,ich knabber gerade an einem nicht so ganz trivialen Problem, vielleicht habt ihr eine Idee:

Wir haben in der Arbeit ein Ampelsystem für Überstunden:
kleiner -40 rot
größer 60 rot
zwischen -40 und -20 gelb
zwischen 40 und 60 gelb
zwischen -20 und 40 grün

Für alle die Vollzeit arbeiten kann ich das einfach in eine Bedingte Formatierung mit 5 Regeln packen und bekomme den gewünschten Effekt. Nun habe ich aber eine relativ kleine Anzahl Mitarbeiter, die Teilzeit arbeiten. Anhand der untenstehenden nach Nachname sortierten Tabelle
Nachname Vorname %
A a 88
B b 80
C c 79
D d 76
E e 87
F f 60
G g 90
H h 51
I i 23

hat jeder dieser Mitarbeiter einen Prozentwert, der auch auf die Ampelwerte wirkt.

In meiner Liste geht es um Anträge zu Mehrarbeit (bin im Betriebsrat), dort werden nach Eingang die Anträge aufgelistet, die die Mitarbeiter oder Führungskräfte schicken. Diese Liste enthält unter anderem den Nachnamen und den aktuellen Ampelstand. Ich kann also nie genau sagen, welcher Mitarbeiter wo in der Liste auftauchen wird.

Ich will nun die bedingte Formatierung im Ampelstand so anpassen, dass entweder halt mit 100% gerechnet wird oder beim jeweiligen Mitarbeiter aus der TZ-Tabelle mit seinem individuellen %-Satz.

Würde mich freuen, bald von Euch zu hören! :-)

LG

Abalone76
Abalone76
 

Re: Bedingte Formatierung in Abhängikeit von SVerweis

Beitragvon Flotter Feger » 06. Okt 2017, 16:38

Hallöchen,

zu allerst mal
hat jeder dieser Mitarbeiter einen Prozentwert, der auch auf die Ampelwerte wirkt.
generell - nein !!!
egal ob ein MA 0,5 oder 1,0 PJ arbeitet, hat er 40 ÜS dann hat er die ... aus die Maus.

Wie du dann die Bewertung der ÜS für Teilzeitkräfte über irgendwelche Faktoren übernimmst, das ist wieder ein ganz anderes Blatt.

Theoretisch hätte ein MA mit 0,5 PJ einen Faktor von 2 Berechnet aus 100% / (0,5 * 100) ... aber das sollte doch schon allgemein bekannt sein ...

BTW ... mit vernünftigen, fähigen, sozialkompetenten Führungskräften ... usw, usw ... :doubt:
VG Sabina

bei mir läuft Win 7 32-Bit und Office 2010 32-Bit
Benutzeravatar
Flotter Feger
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 847
Registriert: 24. Okt 2016, 16:40

Re: Bedingte Formatierung in Abhängikeit von SVerweis

Beitragvon Abalone76 » 06. Okt 2017, 16:56

Hallo Flotter Feger,
generell - nein !!!
egal ob ein MA 0,5 oder 1,0 PJ arbeitet, hat er 40 ÜS dann hat er die ... aus die Maus.

Ist schon klar, dass die Anzahl der Überstunden unabhängig vom Faktor der Teilzeit ist.

Aber die Ampelregelung ist in einer GBV an den Faktor angepasst.
ein MA mit VZ darf bis zu 60 ÜS haben, darüber kommt er in die rote Phase der Ampel, was dann passiert ist in der GBV geregelt, in dem Zusammenhang ist nur wichtig: Mitarbeiter in ROT darf keine weiteren Überstunden mehr machen! Ein MA mit 50% (oder auch 0,5 PJ (was auch immer PJ heißt :P ) darf halt entsprechend nur 30 ÜS haben!

LG

Abalone76
Abalone76
 

Re: Bedingte Formatierung in Abhängikeit von SVerweis

Beitragvon Flotter Feger » 06. Okt 2017, 20:29

Hallöchen,

ich würde das so machen ...
Unbenannt.png

Die drei Formeln sind meine Bedingten Formatierungen für C3 nach deinen Stundenvorgaben.
Habe da mal den TZ-Faktor mit eingearbeitet.
So bleiben die Stunden korrekt erhalten, aber die Ampel-Farbe wird an den Faktor angepasst.

Hast du es dir ungefähr so vorgestellt ?

PS: PJ bedeutet Personen-Jahr
Du hast keine ausreichende Berechtigung, um die Dateianhänge dieses Beitrags anzusehen.
VG Sabina

bei mir läuft Win 7 32-Bit und Office 2010 32-Bit
Benutzeravatar
Flotter Feger
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 847
Registriert: 24. Okt 2016, 16:40

Re: Bedingte Formatierung in Abhängikeit von SVerweis

Beitragvon Abalone76 » 09. Okt 2017, 00:08

Hi Sabina,

Ja prinzipiell so war das gedacht. Danke für die Begriffsklärung PJ!! Wenn (um bei deinem Beispiel zu bleiben) klar wäre, dass der Mitarbeiter in Zeile 3 die 50% hat, würde ich das genau so machen. Aber die Schwierigkeit ist ja, dass die Zeilen nach datum des Antrags befüllt werden. Bei unseren knapp 100 MA am Standort kommen im Jahr zwischen 300 und 500 Anträge zusammen, bei größeren Standorten werden es leicht über 2000... deswegen brauch ich ne Prüfung ob der MA in einer Zeile TZ'ler ist oder nicht und wenn ja mit welchem Faktor.
Ich bin mir Grad net klar, wie ich aus der Prüfung und dem Faktor die, für die bedingte Formatierung mit Formel nötige, "WAHR()"-Bedingung erzeugen soll.

Ja, klar könnte ich den Faktor in eine Zelle der Zeile eintragen und in die Berechnung mit einbeziehen, aber das wäre weder elegant noch bei den großen Standorten mit 300 bis 1000 MA und dementsprechend höherem TZ-Anteil praktikabel...
Abalone76
Neuling
 
Beiträge: 2
Registriert: 08. Okt 2017, 23:44

Re: Bedingte Formatierung in Abhängikeit von SVerweis

Beitragvon HKindler » 09. Okt 2017, 12:35

Hi,

da könnte der SVERWEIS helfen.

Nehmen wir mal an, dein Mitarbeiter (bzw. dessen Personalnummer) steht im Blatt "Überstunden" in Spalte A., die Überstunden in Spalte B.
Eine Liste der Mitarbeiter steht im Blatt "MA" ab Zeile2 bis z.B. Zeile 100. In Spalte A wieder die PNr., in Spalte B der prozentuale Anteil:
Code: Alles auswählen
PNr.   Stundenanteil [%]
11111   100%
22222   66%
22333   100%
12345   25%
54321   33%
33333   75%
44444   80%

Dann kommt im Blatt "Überstunden" in Spalte C die Formel:
Code: Alles auswählen
=SVERWEIS(A2;MA!A2:B8;2;FALSCH)
Die einzelne 2 im SVERWEIS bezieht sich auf die 2. Spalte des Bereichs (hier: Spalte B), das FALSCH sorgt für einen exakten Vergleich.

Jetzt hast du in Spalte C den Prozentanteil des Mitarbeiters. Die Formel für die bed.Form. lautet nun z.B. für Rot:
Code: Alles auswählen
=(B2>(60*C2))+(B2<(-40*C2))
natürlich könnte man die Formel aus C2 auch direkt hier einsetzen. Ich halte es allerdings so für übersichtlicher.
Gruß,
Helmut

----------------------------
Windows 8.1 (64 Bit) / Office 2013 (32 Bit)
HKindler
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 1500
Registriert: 04. Jul 2013, 09:02

Re: Bedingte Formatierung in Abhängikeit von SVerweis

Beitragvon Abalone76 » 11. Okt 2017, 08:12

Hallo Helmut,

Vielen Dank für diesen gedanklichen Reset.
Das ich nen Sverweis brauch, wusste ich. Aber das die Grundformel relativ einfach ist, hatte ich verdrängt!!

LG
Som
Abalone76
Neuling
 
Beiträge: 2
Registriert: 08. Okt 2017, 23:44


Zurück zu Excel Forum (provisorisch)

Wer ist online?

Mitglieder in diesem Forum: 0 Mitglieder und 20 Gäste