Dagplanning / Agenda in Excel

 Op het Microsoft-Excel-forum kwam Piet (in 2016, dit is een update) met een mooie vraag. Hij had in Excel het onlinesjabloon "Dagplanning" gedownload. Dit bestand toont een weekplanning waarbij je de weekdatum en de persoon kunt kiezen. Deze planning wordt gevuld met gegevens uit een tabel op het tweede werkblad.

Maar Piet wenste hierop twee aanpassingen.

In de eerste plaats zou hij graag zien dat het een planning werd met per half uur een regel in plaats van per uur. Maar daarnaast zag hij ook graag dat hij een afspraak kon plannen van meerdere half uren waarbij hij een begin- en eindtijd op kon geven.

De eerste wens was vrij vlug opgelost. Op het planningswerkblad waren de gegevens opgebouwd als een tabel. Door de onderste rij van de tabel te selecteren en daarna met de vulgreep (het kleine blokje in de rechteronderhoek) de rij omlaag te slepen, werd de tabel vergroot. Door het tweede tijdstip te wijzigen van 07:00 naar 06:30 en daarna de reeks van de eerste twee cellen omlaag te slepen werd overal een nieuw tijdstip vermeld en de formules in de tabel bleken hier prima mee uit de voeten te kunnen.

Maar hoe regel je een afspraak van 8 tot 10 uur zonder dat je vier keer een afspraak in de gegevenstabel moet plaatsen. Dat was lastig. Want er moest gezocht worden op drie criteria:

- begindatum en -tijd

- einddatum en -tijd

- wie

Na enig zoekwerk op internet kwam ik een webpagina tegen (zodra ik het opnieuw gevonden heb, plaats ik hier de link). Op die website werd de formule =ZOEK() gebruikt op een creatieve manier die precies de mogelijkheden bood die ik nodig had.

Allereerst heb ik op de Gegevensinvoer-werkblad een extra kolom ingevoegd waar de einddatum en tijd vermeld kunnen worden. De tabel ziet er nu als volgt uit:

Er moet nu een formule komen waarbij op de planning de datum en de tijd worden vergeleken met deze tabel. De datum/tijd in de planning moeten gelijk of hoger zijn dan in de eerste kolom en kleiner dan in de tweede kolom. Daarnaast moet ook nog de naam op het planningswerkblad overeenkomen met de kolom WIE.

Dat is gelukt met de ZOEK-formule. In cel C7 op het planningswerkblad is de volgende formule geplaatst:

=ALS.FOUT(ZOEKEN(2;1/(tblGegevens[DATUM EN TIJD VAN]<=(C$6+$B8))/(tblGegevens[DATUM EN TIJD TOT]>C$6+$B8))/(tblGegevens[WIE]=$H$4);tblGegevens[TAAK]);"")

De naam "tblGegevens" verwijst naar de tabelnaam op het gegevensinvoer-werkblad en de namen tussen de rechte haken zijn de namen van de kolommen. De zoek-formule zoekt naar de taak waarbij in de tabel de datum-tijd-van kleiner of gelijk is aan de waarde van de datum boven en de tijd links van de betreffende cel, de datum-tijd-tot groter is dan de datum boven en de tijd links van de betreffende cel en waarbij in de kolom WIE de naam overeenkomt met cel H$. Als deze regel gevonden is moet de tekst uit de kolom TAAK worden weergegeven.

Maar wat doet nu dat ZOEKEN(2;1/ precies.

De formule zoekt naar de waarde twee. Die zoekt het in een gebied waarvan de voorwaarden gelijk zijn aan hetgeen hierboven beschreven. Door "1/" te gebruiken worden de opzoekwaarden ineens omgezet naar WAAR of NIET WAAR of wel in enen en nullen. Als een regel niet aan een voorwaarde voldoet dan zit er in de formule dus een nul en 1/0 geeft een foutmelding. Blijft over alleen die regels die aan alle eisen voldoen want daar wordt het resultaat 1/1=1. En omdat een 2 niet wordt gevonden in de tabel, zoekt de ZOEK-formule naar de laatste waarde die het dichts bij het zoekgetal komt. En zo vindt het de regel die aan alle eisen voldoet. Let op! Formules als VERT.ZOEKEN() en HOR.ZOEKEN() geven de eerst gevonden waarde als resultaat, deze zoekformule vindt de laatst gevonden waarde als resultaat. 

Hiermee krijgt een afspraak van 8:00 tot 10:00 uur in de planning in vier cellen dezelfde taakomschrijving. Door gebruik te maken van de voorwaardelijke opmaak kun je regelen dat een cel een achtergrond en lijnen krijgt en dat in bepaalde gevallen de tekst dezelfde kleur moet krijgen als de achtergrond. Daarmee regel je dat alleen in de eerste cel van een afspraak de tekst staat en in de andere cellen het lijkt alsof ze leeg zijn.

De voorwaardelijke opmaak bestaat uit vier regels die voor het gehele gebied gelden. De voorwaarden lijken op elkaar maar verschillen allen een klein beetje. De derde voorwaarde is op de afbeelding niet volledig te zien. Op de puntjes moet "C9)" staan.

Het voorbeeld bestand kun je HIER downloaden.
En is iets niet duidelijk, stuur dan een berichtje via het contactformulier van mijn website.