Professionele Excel expert diensten van JVGPRO

Top2000 planningssheet

Het is najaar 2014, locatie NPO Hilversum. Na afloop van een Excel training komen twee cursisten naar mij toe:  “Jan, jij weet zo veel van Excel, heb jij een oplossing voor ons Radio 2 Top2000 planning probleem?”. Het gaat hier om. 2000 liedjes met allen, uiteraard, verschillende speelduur, verdeeld over (toen nog) 156 uur radio, rekening houdende met wisselende reclameblokken per uur, nieuwsblokken en variabele spreektijd voor de discjockey. Om het geheel te laten passen in die 156 uur mag je lange nummers inkorten (een eis die later is ingetrokken). Nou, die uitdaging wilde ik wel aangaan en ’s avonds had ik de eerste ruwe versie gereed. Het sheet kan de liedjes strak over de uren verdelen waarbij spreektijd van de DJ en de maximale speelduur van een nummer dynamisch per uur kunnen worden aangepast. Op die manier kan de planning zodanig gemaakt worden dat je uiteindelijk op 31 december mooi om 23:59 uitkomt.
Inmiddels heeft dit sheet al voor het vierde jaar zijn werk gedaan. Zodra de Top2000 stemming sluit wordt eerst bij de NPO intern de balans opgemaakt en een voorlopige lijst samengesteld. De telling is voor een groot deel simpel omdat je bij de stemming kunt kiezen uit een flinke lijst met voorgeprogrammeerde nummers (die overigens ieder jaar wordt aangepast). Het grote probleem zit hem in de vrije nummers. Medewerkers van de NPO moeten die vrije lijst grotendeels handmatig doorlopen om de nummers te verifiëren. Ongekend hoe creatief de stemmers zijn in schrijfwijzen van titels en artiesten! De voorlopige lijst ontvang ik binnen 24 uur na sluiting van het Top2000 team (in de maanden vooraf is dit weekend bij mij al afgeblokt door de NPO) en met die lijst ga ik een eerste planning maken om te zien of er geen gekke dingen naar voren komen. In Nederland zijn dan slechts een handvol mensen op de hoogte van de uitkomst! In de uren daarna komen aanpassingen en verbeteringen naar voren en meestal is zondags de lijst wel klaar wat mij betreft. Intern gaat de NPO dan verder met de andere voorbereidingen.

Aanpak
Hoe pak je dat dan aan? Om te beginnen vind ik dat in deze gevallen er scheiding moet zijn tussen invoer en presentatie. Het sheet kent dus 1 tab waarin de ruwe data ingevoerd wordt en persé geen berekeningen plaats vinden. In alle andere tabs tref je dan ook alleen maar formules en verwijzingen naar het bronsheet. In volgende jaren hoef ik dan alleen in de ruwe data tab de nieuwe gegevens in te laden en klaar is kees.

2 belangrijke tabs.
Dan zijn er twee belangrijke tabbladen waar alles om draait: Parameters en Verdeling. In de Parametertab staan een aantal parameters ten behoeve van het rekenproces. Denk aan begintijd, eindtijd, maximale spreektijd van de DJ per uur, lengte van het nieuwsblok etc. en een aantal basale signaleringen. Die signaleringen zijn belangrijk omdat de invoer niet altijd even goed bleek. Soms zaten er nummers tussen waarbij geen tijdsduur opgegeven was of waarbij het tijdformaat onjuist was. Ook bevat de parametersheet een grote tabel waarin ieder speeluur per dag is weergegeven. De tabel laat zien hoeveel speelduur er per uur is, hoeveel restant er is, welke spreekduur gehanteerd wordt en hoeveel reclametijd dat uur bevat. De planner kan daarin direct zien in welk uur er veel slack zit en waar het wringt. In de tabel kunnen sommige van die waarden direct aangepast worden waarbij het effect in de planning direct zichtbaar is. Omdat het best een grote tabel is (835 cellen) en daarmee onoverzichtelijk kan worden, is er erg veel gebruik gemaakt van voorwaardelijke opmaak om zodoende afwijkingen te kunnen signaleren. In feite is er nog een ander parametersheet en dat is het STER tijden sheet. Hierin staat per uitzenduur vermeld hoeveel reclametijd gewenst is.

Verdeling tab

De Verdeling tab is het hart van de planning. In deze tab worden de 2000 nummers over de 7 dagen/159 uren verdeeld worden. Een gegeven is daarbij dat ieder nummer zijn eigen speelduur kent en in principe volledig afgespeeld dient te worden. Het aantal nummers dat dus in één uur past is daarmee wisselend. Het sheet moet dus bepalen of een nummer nog in uur X past, of dat een uur X+1 gestart moet worden en of er een misschien overgang in de dag nodig is. Lijkt simpel, maar het was wel dé uitdaging. Gebruik maken van standaard tijdrekening in Excel werkte niet en je moet zelf uren en dagen tellen. Bijvoorbeeld nummer 1978 eindigt op 10:56:52 en de duur van het volgende nummer is 00:03:12. Dat gaat op 4 seconden na dus niet in het uur passen, je hebt 11:00:00-10:56:52=00:03:08 over, en moet je een nieuw uur starten. In dit sheet staan de 2000 nummers aflopend onder elkaar en wordt per nummer de starttijd berekend rekening houdende met de ingestelde parameters die dynamisch opgezocht worden. De formules hiervoor zijn uiteindelijk best complex geworden. Visueel zijn in dat sheet de nodige opmaakregels toegepast. Niet dat dat technisch noodzakelijk is, maar wel om snel, en met name visueel,  een controle te kunnen doen. Overigens moest om de tijdrekening goed te laten werken, het 1904 datumsysteem in Excel ingesteld worden.

top 2000 uurverdeling

Top2000 poster

Overige sheets
Het sheet kent naast de 2 basale tabs, nog een aantal afgeleide tabbladen. Nogmaals “afgeleid” omdat er maar één tab is waarin de 2000 nummers vermeld staan. In alle andere tabs wordt verwezen naar die bron. Een belangrijke tab is een genormaliseerd overzicht van de top2000 met de uur indeling en daarbij de indicatie stijgers of dalers. Vanuit die tab wordt, via een macro, een extern spreadsheet aangemaakt waarin per dag een tab is opgenomen met de nummers per uur. Een sheet welke door de  diskjockeys gebruikt wordt om hun planning te maken.
Een ander spannen tab is degene die als basis moet dienen voor de bekende Top2000 poster en de hand-outs in het Top2000 café. Om te komen tot de postertab is een macro geschreven die de poster samenstelt. De indeling bleek technisch te complex om direct die poster te genereren. De macro maakt een apart spreadsheet met de posterdata aan. Data waarin de datum en tijd van het speeluur als tekst zijn opgeslagen om te voorkomen dat bij derden de datums fout gaan lopen omdat daar het 1904 datumsysteem niet vanzelfsprekend is.
Er is een tab Statistiek ingebouwd waarin een beperkt aantal statistieken wordt berekend. Heel leuk, maar vooral bedoeld om het hele proces te controleren. Op het moment dat ergens een fout zit, zijn die statistieken duidelijk voor een ieder direct nonsens geworden.

Leerpunten
Snelheid is belangrijk bij het proces op tot de planning te komen. Het sheet bevat daarom verregaande automatisering en een minimum aan afhankelijkheden van gegevens. Vanwege dit punt is ook erg veel gebruik gemaakt van voorwaardelijke opmaak. De tabs tonen de Top2000 dan ook overal in een formaat waarin je duidelijk een uuroverzicht heb (automatische lijnen en kleuren). Ook zijn veel controles ingebouwd die optische signalering hebben.
Een nadeel is dat, vanwege het rekenen met tijden, gebruik gemaakt moest worden van het 1904 datum systeem in Excel. Er zijn weinig Excel gebruikers die dat snappen en copy/paste van de lijst naar een “gewoon” spreadsheet gaat dan fout.

Meer weten? Stuur mij een email.

© 2019 ExcelProServices.nl

Thema door Anders Norén