workshop rooster in Excel maken

Workshop rooster maken in Excel.

Als basis voor deze workshop gebruik ik een maand rooster wat ik ooit ontwikkeld heb voor een relatie die een uitgaansgelegenheid runt.

Uitgangspunt Flexibele rooster plekken en beschikbaarheid van medewerkers

  • voor de medewerkers  is een schema waarin tot 125 personen per komende maand kunnen aangeven welke datum s men kan werken.
  • Voor de werkgever een flexibel aantal datums per maand. In het hoogseizoen bijna alle dagen van de week open en buiten het hoogseizoen 2 a 3 dagen in de week open.
  • Tevens moet het mogelijk zijn in drukke periodes meer werkplekken in het rooster re creëren. In de zomer maanden zijn er bv 2 buiten terrassen en wordt 1x per week een buiten barbecue gehouden
  • Er moet een overzicht per medewerker uit het rooster komen en per werkplek moeten uren per dienst kunnen worden opgegeven

 

In deze workshop laat ik U per tabblad meekijken en krijgt U uitleg hoe je het allemaal aan elkaar koppelt

 

Tabblad Werkplekken:

In kolom B vindt U het id nr, dit nummer wordt straks gebruikt om met verticaal zoeken de omschrijving in het rooster in te vullen. In kolom D vindt U de te berekenen werkuren – de pauzes

Lijst medewerkers:

In onderstaande lijst kunt U tot 150 verschillende medewerkers plaatsen.

  • De datums uit het rooster vindt U in regel 5 vanaf kolom G (kolomnummer van dit bestand =6) U kunt hier 31 dagen kwijt: kolomnummer 6 t/m 36. De betekenis komt bij het rooster ter sprake.
  • In kolom E regel 5 ziet U 7 staan  de formule hier =MAAND(G5) cel eigenschap is ingesteld als getal (zonder decimalen).
  • De bedoeling van JA – NEE is of iemand op die datum beschikbaar is. Hiervoor is een formulier voor de medewerkers ontwikkeld wat de medewerker van de bedrijfswebsite kan downloaden. Dit moet voor de 15 van de komende maand worden ingeleverd,
  • Bij binnenkomst wordt dit z.s.m.  het in het rooster verwerkt.
  • Als de werkgever ieder dag open is kun je de datums automatisch invoeren.
  • De start datum in G5 plaatsen  in H5 zet je =G5+1 , deze formule kopieer je door naar achteren dan staat er bv in K5 = J5+1. Deze datum rij kan voor de werkgever dus steeds aangepast worden naar de openingsdatum.
  • Met de formule SOMMEN.ALS worden het aantal werkuren per medewerker uitgerekend in kolom E(.=SOMMEN.ALS(‘Dienst Calculator ‘!$R$16:$R$625;’Dienst Calculator ‘!$I$16:$I$625;$B6;’Dienst Calculator ‘!$E$16:$E$625;$E$5) uitleg van deze formule volgt nog
  • waarbij in kolom F dan weer staat =D x E

 

Het roosterblad de diensten

Het roosterblad is op onderstaan plaatje geheel geopend, een aantal hulp kolommen kun je verbergen na de uitleg zal dat wat duidelijker zijn.

  • DATUM KOLOM -D: geeft hier de datum op, zodra alle werkplekken zijn ingedeeld sluit U de datum af met een X in de kantlijn (kolom A)
  • KOLOM A:  d.m.v. voorwaardelijke opmaak krijgt U de gekleurde regel. In het voorbeeld ziet U op zondag dat de Bar 1 3 werkplekken vraagt op dinsdag zijn dat er maar 2
  • KOLOM K: De X in de kantlijn is ook verwerkt in de formule in kolom K. Het eerste nummer 6 in kolom K is er door mij handmatig ingezet en verwijst naar de 6e kolom op het blad medewerkers. Zolang U de datum niet heeft afgesloten door een X te plaatsen in A  blijven alle nummer eronder op 6 staan.
  • Vervolg K: Dit nummer is nodig om de Ja of de Nee op te halen. Formule in kolom K .=ALS(A18=”X”;K18+1;K18)  d.w.z. in A19 staat: als in kolom A regel 18 een X staat dan moet bij het nummer uit K18, 1 worden opgeteld, zo niet dan moet het nummer uit K18 zo blijven. Deze kolom is verder niet van belang die zou U kunnen dichtschuiven.
  • KOLOM I en J: Zodra U een medewerkersnummer invult in kolom I wordt de naam opgezocht met verticaal zoeken, tevens wordt kolom L ingevuld met de NEE of de NEE, voorwaardelijke opmaak geeft de rode kleur
  • KOLOM J: Formule in kolom J  =ALS($I16=””;””;VERT.ZOEKEN($I16;Medewerkers!$B$6:$AK$382;2;ONWAAR)) als in kolom I16 niets staat dan moet er niets gebeuren anders verticaal zoeken het nummer uit I16 in het bestand medewerkers in de 2e kolom
  • KOLOM L: Formule in L   =ALS(I20=””;””;VERT.ZOEKEN(I20;Medewerkers!$B$6:$AK386;K20;ONWAAR)) dit is dezelfde formule als hierboven, alleen wordt niet het kolom nummer opgegeven dat wordt opgezocht in kolom K op dit tabblad.

Het roosterblad de Uren

Er zijn 2 mogelijkheden waarmee de gewerkte uren berekend kunnen worden. Bij de eerste versie van dit rooster werd uitgegaan van de  uren die werden opgegeven op het tabblad werkplekken (D)  Toen we gingen proefdraaien kwam de opmerking hoe om te gaan als een medewerker een uur eerder naar huis gaat of dat door drukke de dienst een uur uitloopt. Gekozen werd voor een andere optie: toevoeging van kolommen M t/m R. De begin- en eindtijd handmatig invoeren. Er is in kolom N en P geen opmaak gebruikt, je zou de tijden naar beneden kunnen kopiëren. Kolom Q rekent de uren uit. Je hebt nu 2 mogelijkheden om aan de uren te komen. Mogelijkheid 1 is de inhoud van kolom M en mogelijkheid 2 is de inhoud van kolom Q afhankelijk van Uw keuze in cel D2 wordt kolom R berekend.Kolom R is de ophaal kolom voor de uren op het tabblad medewerkers in kolom E

Gebruikte formules:

  • Kolom M: =ALS($G16=””;””;VERT.ZOEKEN($G16;werkplekken!$B$4:$D$24;3;ONWAAR)) Hier staat als in kolom G niets is ingevuld, dan niets anders moet je verticaal zoeken naar de inhoud van cel G16 op het tabblad werkplekken in de 3 kolom.
  • Kolom Q: =($P16-$N16+($P16<$N16))*24 rekenen met tijden.
  • Kolom R: =ALS($D$9=1;M17;Q17) in cel D9 maakt U een keuze welke berekeningsmethode U gaat gebruiken voor de werkuren. Hier staat als de inhoud van D9 = 1 dan de inhoud van kolom M, anders de inhoud van kolom Q.
  • Rest nog 1 formule: hoe komen  we aan het totaal werkuren op het tabblad medewerkers in kolom E: .=SOMMEN.ALS(‘Dienst Calculator ‘!$R$16:$R$625;’Dienst Calculator ‘!$I$16:$I$625;$B6;’Dienst Calculator ‘!$E$16:$E$625;$E$5). Ik heb er een plaatje van gemaakt om het wat duidelijker te kunnen uitleggen. Het optelbereik is de kolom waar de uren moeten worden opgehaald Kolom R. Er zijn 2 criteria waar op moet worden gezocht op medewerkersnummer kolom B op het blad medewerkers en het maandnummer dat staat in cel E5 (E5 is een vast cel adres daarom staan er dollartekens bij het cel adres. Het criteria bereik zijn de kolommen I (medewerkersnummer) en E (maandnummer) op het rooster.

Tabel database met filter mogelijkheden:

In cel C 3 staat een verwijzing naar de eerste datum van het rooster  bv =dienstcalculator D17  in cel D3 staat =dienstcalculator H17 zo ook id nummer medewerker en de naam van de medewerker deze formule trekken we door naar onderen. in kolom B heb ik de regels genummerd van 1 tot 625 (de laatste regel) waarom dat wordt U dadelijk duidelijk. Vervolgens selecteer je het hele bestand. Ga boven in het lint van Excel naar invoegen en klik op tabel. Geef nog even aan dat de eerste een kopregel is. U heeft Nu een volledige automatische database. Waar in U kunt filteren op iedere kolom. U kunt ook sorteren op werkplek of op naam medewerker. Wilt u de database weer in zijn originele staat  sorteer dan op kolom B

 

Hier gesorteerd op naam medewerker:

iets meer weten over:

Dan hebben we bij deze alles besproken en zou U in staat moeten zijn een prachtig rooster te kunnen maken. Hoeveel tijd gaat er in zitten? bent U redelijk thuis in Excel: trek er dan ongeveer 3 avonden voor uit. Vindt U dat te veel werk dit rekenmodel is ook te koop de kosten bedragen 75 euro meer info via de contact pagina