workshop planning en beheer van vergaderzalen met excel

 

 

Workshop Planning en beheer van vergaderzalen.

Met een weinig voorkennis van Ms Excel kunt U dit rekenmodel zelf maken

Op deze pagina wordt uitgelegd hoe U dat met behulp van Ms Excel kunt doen. Alle technieken die ik daar voor gebruik leg ik U uit en vindt U dat het allemaal te veel tijd kost, dan kunt U rekenmodel hieronder eventueel ook bestellen als uitgangspunt voor Uw eigen Excel rekenmodel vergaderzalen.

Het geheel wordt gemaakt als tabel (database) het voordeel van een tabel is dat je makkelijk kunt sorteren en iets uit kunt filteren, ik kom hier nog op terug.

Eerst even een plaatje:

vergad01

 

Het linker deel van de tabel.

Alle kolommen die niet direct noodzakelijk zijn kun je dichtvouwen door op het min teken te klikken, ik kom hier nog op terug. (functie groeperen) hoe doe je dat: selecteer de gewenste kolommen (C en D) onder het menu gegevens klikt U uiterst links op het icoon groeperen. Wilt U een groep opheffen selecteer de gewenste kolommen en klik op het icoon groep opheffen

Ik heb gekozen om van de reserveringsdatum een berekening te maken van maand en weeknummer. Als je later een overzicht per week op maand wil hebben kun je die makkelijk uitfilteren. Formules: =MAAND()  en is WEEK() in cel C3 staat bv =WEEK(F3) in kolom D3 staat =MAAND(F3)

De berekeningen in kolom C en D vouwen we dicht door op het min teken te klikken. Iedere boeking krijgt automatisch een boekingsnummer. Het boekingsnummer zou later eventueel gebruikt kunnen worden in een bezoekerslijst. Hoe maak je een automatische nummering: Als je in Cel E3 een 1 plaatst zet je in cel E4 =E3+1, deze formule kopieer je door naar beneden

De begintijd en de eindtijd zijn beiden keuzelijstjes, U kunt steeds ieder uur kiezen oplopend met 15 minuten. De keuzelijstjes maak je met validatie. Onder aan deze pagina vindt U uitleg met schermvoorbeelden over het maken van keuzelijstjes d.m.v. validatie

U weet nu hoe U kolommen  kunt groeperen en daardoor gedeeltes van het werkblad kunt dichtvouwen. Uw weet nu hoe U week en maandnummer kunt berekenen. Ook kunt U nu een automatische nummerlijst maken.

De vergaderzalen staan vermeld op een 2e tabblad en worden automatisch ingevuld door het nummer van de zaal in te vullen, dit gebeurd met de functie verticaal zoeken dit wordt U op deze webpagina nog uitgelegd.

 

vergad02

 

Het rechter deel van het rekenmodel.

In dit gedeelte van het rekenmodel kunt U aangeven of er bezoekers verwacht worden en hoeveel, tevens kunt U aangeven of er gebruik gemaakt wordt van ene lunch en met hoeveel.

Zodra U een kruisje plaatst wordt het vakje automatisch ingekleurd, zo kunt U ook een kruisje zetten voor broodjes, koffie en bloemen (jubilaris?).

Als U vergaderzaal voorzien is van vaste items zoals bv een beamer of ene flap-over is dat zichtbaar door de gekleurde blokjes. Overigens geeft U deze vaste items op op het tabblad instellingen en door het nummer van de vergaderzaal in te vullen worden deze kleurtjes automatisch ingevuld (dit gaat met voorwaardelijke opmaak en wordt nog uitgelegd.

het tabblad instellingen.

vergadinstell

 

Op het tabblad instellingen kunt U al de vergaderzalen plaatsen die U wilt beheren, tevens kunt U het aantal plaatsen opgeven. 

Alle aanwezige items zoals een beamer, pc, flap-over kruist U hier aan. Ook kunt U de omschrijvingen van de items hier aanpassen. De inhoud van deze cellen zijn gelinkt met de inhoud van de cellen in het boekingsblad.

Heeft U alleen vaste vergaderingen en werkoverleggen dan kunt U het rechterdeel hier gebruiken om kolom M op het boekingsblad automatische te vullen (dit bespreek ik later nog)

Het grote voordeel van een Ms Excel tabel:

Als je het ingebouwde filter in de kolomkoppen nader bekijkt, krijg je al direct een indruk wat er allemaal kan met het rekenmodel hierboven.

In het voorbeeld hieronder heb ik op het pijltje geklikt in de kolomkop van de boekingsdatum, door de vinkjes weg te halen bij de maanden kun je 1 of meerder maanden uitfilteren.

Kijk ook eens naar de uitgebreide mogelijkheden bij de datum filters. Iedere kolom kop is voorzien van een filter, zo kun je hier bv filteren op week en maandnummer of op vergaderzaal. Je kunt zelfs filteren op de kleurtjes achteraan op het blad boekingen.

Maar het grootste voordeel is wel dat U alles op volgorde van binnenkomst kunt invoeren, U hoeft geen regels tussen te voegen om de datum op volgorde te houden, gewoonweg even sorteren van oud naar nieuw.

vergadfilter

 

Hierboven heeft U kennis kunnen nemen van het werken met het ingebouwde filter, U weet nu dat U op allerlei items iets kunt uitfilteren. tevens weet U hoe U een lijst kunt sorteren

 

Uitleg over de diversen technieken, functies en formules.

 

In het gele vak zijn alle cellen samengevoegd tot 1 cel de formule =NU() geeft de datum en de tijd weer

In kolom C wordt het weeknummer uitgerekend van de reserveringsdatum       =WEEKNUMMER(G3)

In kolom D wordt het maandnummer uitgerekend van de reserveringsdatum   =MAAND(G3)

In kolom K wordt de vergaderzaal ingevuld die op het blad instellingen nummer 2 heeft, de formule verwijst naar het nummer in kolom J. De formule is: =ALS(J3=””;””;VERT.ZOEKEN(J3;instellingen!$B$2:$C$30;2;ONWAAR)).

Hier staat simpel gezegd Als in Cel J3 niets staat; dan niets; anders moet er verticaal gezocht worden de inhoud van J3 (2) op het blad instellingen het adres B2 tot C30; er moet gezocht worden in de 2e kolom; ONWAAR is een instelling die opgeeft dat er exact moet worden gezocht.

Hierboven heeft U kennis kunnen nemen van de formule verticaal zoeken. Onder aan deze pagina wordt er nog wat dieper op deze functie ingegaan. Als U deze functie goed beheerst zult U er heel veel gebruik van maken

 

Met validatie gaan we het keuzelijstje maken

urenlijstjemkn1

 

De begin en eindtijd invoeren gaat door middel van een keuzelijstje waarin je de uren en kwartieren kunt kiezen.

Bepaal eerst waar je het urenlijstje wilt hebben in het voorbeeld heb ik 07:00 geplaatst in cel AQ1.

We willen kunnen kiezen uit uren en kwartieren dan hebben we 96 cellen nodig naar beneden en die willen we natuurlijk niet allemaal handmatig invoeren.

Daarom ga je rechtsboven naar het menu doorvoeren en kies je in het vervolgscherm voor reeks, je krijgt dan onderstaand invul scherm.

urenlijstjemkn2

We willen de tijd om de 15 minuten kunnen kiezen daarom heb ik bij intervalwaarde 0:15 ingevoerd, als je op OKÉ  klikt zie je hieronder het resultaat.

urenlijstjemkn3

Je lijstje is nu klaar nu moeten we dat nog koppelen aan de cellen in kolommen begin- en eindtijd dat doen we met validatie.

U heeft hierboven kennis kunnen nemen van het aanmaken van een reeks een onderdeel van de functie doorvoeren. De verschillende mogelijkheden vindt U onder het icoon Doorvoeren uiterst rechtsboven  in het startmenu

 

Gegevens validatie:

In het menu gegevens vindt je rechtsboven de functie gegevens validatie.

menu gegevens

Wat is gegevensvalidatie?

Gegevensvalidatie is een functie van Excel waarmee u beperkingen kunt definiëren voor de gegevens die in een cel kunnen of moeten worden ingevoerd. U kunt gegevensvalidatie configureren om te voorkomen dat gebruikers ongeldige gegevens invoeren. Als u wilt, kunt u toestaan dat gebruikers ongeldige gegevens invoeren, maar wel een waarschuwing ontvangen wanneer ze deze in de cel typen. U kunt ook berichten weergeven om aan te geven welke invoer u in de cel verwacht en instructies om gebruikers te helpen fouten te corrigeren.  een complete uitleg vindt U hier:

Maar wij beperken ons tot het aanmaken van een lijst met tijden. Het eerste wat U doet klik in het rekenmodel op de eerste cel in de kolom begintijd, als je deze cel hebt geselecteerd klik je op het icoon gegevens validatie.

Je krijgt dan onderstaand keuzescherm:

validatielijst

 

In het scherm toestaan kun je verschillende keuzes maken ik heb voor Lijst gekozen, dan klik je in de eerste cel van je lijst waar 07:00 in staat houdt de muis vast en schuif helemaal naar beneden naar de laatste cel waar 06:45 in staat.

In het vakje Bron staat $AQ$1: $AQ$95 dat is het adres van de kolom waar je de tijden in hebt gezet. Geef een enter. Je keuzelijstje staat nu in de eerste cel.

Klik in je eerste cel met het keuzelijstje en selecteer de hele kolom. Met het toetsenbord commando Controltoets D (ezelsbruggetje down) kopieer je de validatie naar beneden. Je kolom Begintijd is nu klaar.

Klik in de eerste cel toetsenbord commando Controltoets C (ezelsbruggetje kopiëren), ga naar de eerste cel van de kolom eindtijd toetsenbord commando Controltoets V (ezelsbruggetje verplaatsen) je validatie is nu gekopieerd naar de eerste cel van de kolom Eindtijd en  kopieer dit nog even door naar beneden. Controltoets D.

Je hoeft nu alleen nog maar je gemaakte keuzelijst opzij van het rekenmodel te verbergen. Selecteer de kolomletters AQ rechtsklik met de muis en kies voor verbergen.

Maar let op:  Tot regel 95 mag je in je rekenmodel geen regels verwijderen, omdat je dan je lijstje beschadigd (wat je weg haalt zit niet meer in je keuzelijstje.

U heeft hierboven kennis kunnen nemen van de functie validatie waarmee U onder andere een keuzelijstje kunt maken. De functie validatie vindt U in het menu Gegevens onder het menu validatie ongeveer midden boven het scherm.

Verticaal zoeken:

De functie verticaal zoeken wordt in dit rekenmodel veel gebruikt daarom gaan we deze functie hier uitgebreid behandelen.

Als je deze functie 100% door hebt zul je hem hele vaak gebruiken. In dit rekenmodel wordt alles opgezocht op het tabblad instellingen hieronder vindt U een afbeelding. 

In het boekingsformulier wordt de vergaderzaal opgezocht d.m.v. verticaal zoeken, je vult het nummer van de zaal in waarna de naam van de zaal wordt opgezocht om de formule helemaal te begrijpen moet U iets weten van cel adressen:

Zaal 1 met op het plaatje hieronder staat in kolom C op regel 3 dit korten we af op C3. 

Alle nummers van de zaal staan in kolom B.  Alle andere vaste gegevens staan in kolom C t/m kolom J

Alle gegevens die opgezocht kunnen worden staan in een bestand beginnende bij B3 (zaal nummer 1) en het laatste wat opgezocht kan worden is een X die kan staan in cel J30. 

Het complete adres is dan B3:J30 op het tabblad instellingen  in een formule zou er staan “instellingen” B3:J30 Onthoud ook nog even de zaalnummers staan in kolom 1, de naam van de zaal in kolom 2, aantal plaatsen in kolom 3.

bladinstellingen

 

De formule op het bladboekingen in cel J3 is:  =ALS(J3=””;””;VERT.ZOEKEN(J3;instellingen!B2:C30;2;ONWAAR))  Hierbij verwijst J3 naar de inhoud van cel J3, daar staat het nummer wat je hebt ingevuld en waarbij een vergaderzaal moet worden opgezocht. De cel adressen B2:C30 heb ik  zojuist hierboven uitgelegd.

Omdat we niet willen dat de cel adressen bij kopiëren naar beneden mee veranderen (bij 1o regels naar beneden zou C30 C40 zijn) plaatsen we dollartekens, de formule wordt dan: =ALS($J3=””;””;VERT.ZOEKEN($J3;instellingen!$B$2:$C$30;2;ONWAAR)), als je goed kijkt heb ik dat ook gedaan bij het zoekveld J3 =$J$3 geworden omdat we ook naar rechts moeten kopiëren (voor aantal plaatsen en de kruisjes wat aanwezig is) Als je het $ teken hebt geplaatst  klik  je in het boekingsformulier op cel K3 en selecteer de hele kolom naar beneden en met het toetsenbord commando Controltoets D kopieer je de hele formule naar beneden.

Weet je het nog de 2 achteraan in de formule staat voor de 2e kolom waar de naam van de vergaderzaal in staat. In de 3e kolom staan de aantal plaatsten per vergaderzaal.

Kopieer en verplaats de formule naar de kolom waar de aantallen in staan. Controltoets C (kopiëren), Controltoets V (verplaatsen) de zoekkolom J blijft het zelfde en heb je mee gekopieerd, je hoeft nu alleen nog maar de 2 te veranderen in een 3  (aantal plaatsen staat in kolom D kijk maar hier boven en dat is de derde kolom). Je hoeft nu alleen nog maar de formule naar beneden te kopiëren.

Op dezelfde manier kun je de formule ook kopiëren in de achterste kolommen Y t/m AD waar de vaste items worden aangegeven met een X, je hoeft alleen maar de nummers achteraan in de formule te veranderen 4 t/m 9 en de formule naar beneden te kopiëren

U weet nu wat verticaal zoeken is en beheerst deze functie, wilt U nog meer voorbeelden type de term verticaal zoeken eens in het zoekveld voor deze website geheel rechtsboven. U zult dan nog een aantal andere voorbeelden op deze website tegen komen.

De kruisje moeten we nu nog veranderen in gekleurde blokjes, dat doen we met voorwaardelijke opmaak, daar ga ik een volgende keer mee verder, dus wordt vervolgd.

Niet gevonden wat U zocht:

 x