Op de website excellentmetExcel van Marianne van Lubek zag ik een mooie vakantieplanning staan waarin na het invoeren van naam, van datum en t/m datum automatisch de vakantieplanning werd uitgetekend met de weeknummers en datums boven de planning. De planning werd gemaakt met voorwaardelijke opmaak. (mijn projectplanning werkt op een soortgelijke manier).
De planning gaf alleen een overzicht van het 3e kwartaal en ik vroeg me af waarom niet een planning voor de andere kwartalen. Marianne gaf daarop aan dat vooral in het 3e kwartaal veel vakanties worden opgenomen en dan is een overzicht van wie wel en niet aan het werk is van belang.
Daar heeft ze gelijk aan, maar toch bleef ik me afvragen of het mogelijk was om in één overzicht snel alle kwartalen te kunnen opvragen. En daarbij vroeg ik me af of het mogelijk zou zijn om meerdere vakanties bij dezelfde medewerker te tonen. En dat kan, tenminste, het is mij gelukt met de nieuwe dynamische functies die in Excel 365 zitten.Want in Excel 365 zijn de functies =UNIEK(), =FILTER() en =SORTEREN() beschikbaar. Dit is mijn eerste bestand waar ik alle drie in één formule combineer.
Het bestand bestaat uit drie werkbladen: Info, Vakantieblad en het verborgen werkblad Hulptabellen. Op werkblad Vakantieblad staat in de eerste kolommen een tabel.
Hier kunnen de namen van de medewerkers, de afdeling en de van- en t/m-datum van de vakanties worden ingevuld. Per medewerker kunnen meerdere data ingevoerd worden en een medewerker kan bij meerdere afdelingen worden ingevuld. Op de Van-kolom is een gegevensvalidatie ingesteld die controleert of de datum wel in het jaar valt wat bij de planning ingesteld kan worden (zie hieronder). Op de T/m-kolom is ook een gegevensvalidatie ingesteld. Ook hier wordt gecontroleerd op de juiste datuminvoer maar ook of de datum in deze kolom gelijk of groter is dan de Van-datum in dezelfde kolom. De gegevens staan in een Excel-tabel. Dat houdt in dat als een medewerker aan de lijst toegevoegd moet worden, deze op de eerstvolgende lege rij ingevuld kan worden. Automatisch zal Excel de vier kolommen oranje kleuren en de gegevensvalidatie doorzetten in de laatste twee kolommen. Als een medewerker verwijderd moet worden dan kan dit door de vier kolommen op de betreffende rij te selecteren en te kiezen voor verwijderen. Automatisch worden alle gegevens overgenomen in de planning. De planning is afhankelijk van enkele in te stellen cellen:
In de eerste cel kan een jaartal tussen 2020 en 2030
ingevoerd worden en in de tweede cel kan een kwartaal tussen 1 en 4 worden
gekozen. Dit wordt gecontroleerd met gegevensvalidatie. De afdelingen worden ook met een gegevensvalidatie
aangegeven maar hier worden de gegevens gehaald uit een lijst die op het
verborgen werkblad Hulptabellen staat vermeld. Daar wordt met de
=UNIEK()-functie alle unieke namen van de kolom Afdeling samengesteld. Zodra er
op het Vakantieblad een nieuwe afdeling wordt toegevoegd is deze ook te vinden
in deze keuzelijst.
In de vakantieplanning worden automatisch de medewerkers
éénmalig (via de UNIEK-functie) getoond die bij de gekozen afdeling horen (via
de FILTER-functie) en de namen worden alfabetisch weergegeven (via de
SORTEREN-functie). Dat zijn toch mooie mogelijkheden die Excel 365 heeft.
De planning had ik willen uittekenen met voorwaardelijke opmaak maar dat is mij niet gelukt. Waarschijnlijk kan de voorwaardelijke opmaak nog niet werken met de tabelnaam en kolomnamen. Daarom heb ik er voor gekozen om in het planningsgedeelte van het bestand een =AANTALLEN.ALS()-functie te gebruiken waarmee geteld wordt of er in de tabel een regel voor komt waarin de naam van de medewerker staat, de geselecteerde afdeling en waarvan de Van- en T/m-kolom een datum bevatten die kleiner of gelijk of groter of gelijk zijn aan de datum in de betreffende kolom. Boven de planning wordt met formules de dag van de week aangegeven en wordt het weeknummer weergegeven. Met dank aan Marianne staat er nu het juiste ISO.WEEKNUMMER. Dit had waarschijnlijk ook gemaakt kunnen worden met =WEEKNUMMER(datum;21). In de laatste drie kolommen zijn extra voorwaardelijke opmaken ingesteld, want niet elk kwartaal heeft evenveel dagen. De kwartalen bevatten namelijk 90 (of bij een schrikkeljaar 91), 91 of 92 dagen. Door de voorwaardelijke opmaak wordt er geen planning in deze dagen getekend.
Meer informatie staat op het info-blad bij het bestand.
Het bestand, geschikt voor Excel 365, kan HIER gedownload
worden.
En is iets niet duidelijk, stuur dan een berichtje via het contactformulier van mijn website.
Geen opmerkingen:
Een reactie posten