Tagarchief: Opmaak

Kleurgebruik



Dit artikel gaat vooral over het gebruik van kleuren in Excel, maar is op ongeveer dezelfde manier geldig voor alle andere Office-programma’s van Microsoft.

Maar, hoewel kleuren het meest kenmerkende/zichtbare/opvallende aspect van de opmaak zijn, spelen ook het gebruikte lettertype en bepaalde effecten een rol. Dit komt binnen de Office-programma’s allemaal samen in de zogenaamde thema’s.

Wat is een thema?

Een thema is een verzameling voorkeur-instellingen binnen Microsoft-Office voor wat betreft kleuren, lettertypen (fonts) en bepaalde effecten. De laatsten zijn vooral van belang bij het gebruik van Powerpoint, het presentatieprogramma.
Door een bepaald thema te kiezen veranderen in de hele werkmap de kleuren (onder bepaalde voorwaarden; zie hieronder), het lettertype en eventuele effecten.

Het grote voordeel is dat op deze manier een consistent gebruik van de vormgeving is gewaarborgd.

Hoe kies je een thema?

In de praktijk is het het makkelijkste om een thema te kiezen wanneer je al een Excel-werkmap hebt waar kleuren en/of grafieken voorkomen.
Download het Voorbeeldbestand en selecteer het tabblad Ovz1.

Selecteer binnen de menu-tab Pagina-indeling in het blok Thema’s de optie Thema’s.
Er opent dan een overzicht met diverse standaard-thema’s; het aantal en welke het zijn is afhankelijk van de geïnstalleerde versie van Office.

Beweeg nu met de muis over de diverse thema’s; niet klikken. De consequenties voor de lay-out worden dan direct zichtbaar in het achterliggende werkblad. De breedte van de Excel-kolommen wordt automatisch aangepast aan het lettertype van het thema.

Bent u tevreden met een bepaalde opmaak: klik met de muis op het betreffende thema en deze opmaak zal dan in de hele werkmap worden toegepast. Dus ook in het Voorbeeldbestand op de tabbladen Ovz2 en Data, waar sparklines, een Excel-tabel en een overzicht met zelf toegevoegde kleuren zijn te vinden.

Wilt u alleen de kleuren binnen de werkmap aanpassen, kies dan in het blok Thema’s voor de optie Kleuren.
Een vergelijkbaar verhaal geldt natuurlijk ook als u alleen het Lettertype of de Effecten wilt aanpassen.

Verschil tussen Thema- en Standaard-kleuren

Zoals de naam al aangeeft, zullen de kleuren die via Themakleuren zijn ingesteld veranderen wanneer een ander thema wordt gekozen. Alle andere kleuren (Standaard of die via Meer kleuren zijn ingesteld) blijven altijd hetzelfde onafhankelijk van de keuze van het thema.

Wanneer u in Office een kleur kiest opent er altijd een scherm zoals hiernaast weergegeven.

Bovenaan staan de 10 basis-kleuren binnen het gekozen thema. De eerste kleur wordt gebruikt als standaard-achtergrond (ga met de muis er ‘boven hangen’ en de omschrijving wordt zichtbaar). De tweede kleur is de standaard-tekstkleur.
Kleur nummer 3 is een tweede achtergrond-kleur, terwijl nummer 4 een alternatieve tekstkleur is.
De laatste 6 zijn zogenaamde accent-kleuren, vooral gebruikt in grafieken.

Onder de basis-kleuren staan 5 varianten van deze kleuren. In het eerste schema op het tabblad Ovz1 van het Voorbeeldbestand ziet u of dit lichtere of donkerder varianten zijn.

NB gebruik op één pagina niet te veel verschillende kleuren; het wordt dan al snel een ‘kermis’. Beter is om 1 of 2 basis-kleuren te gebruiken en de verschillen te laten zien door varianten van deze kleuren te gebruiken; zie het verschil tussen de twee grafieken hiernaast (tabblad Ovz2 van het Voorbeeldbestand).

Dan volgen 10 Office-standaardkleuren. Zoals al eerder aangegeven: worden deze gebruikt binnen Excel dan zullen deze niet veranderen wanneer er een ander thema wordt gekozen.

Hebt u niet genoeg aan deze standaardkleuren, dan kunt u via Meer kleuren een andere standaard-kleur kiezen of ‘mengen’ (zie hierna).

Eigen thema maken

Wilt u consistente rapportages en presentaties maken met Excel, Word, Access en/of Powerpoint dan zijn aangepaste thema’s van groot belang.
Hierbij kunt u denken aan bedrijfskleuren en -lettertypen of kleuren die aan een bepaalde situatie moeten worden aangepast.

  1. selecteer allereerst een Thema dat het meest voldoet aan uw wensen
  2. kies dan Kleuren in het blok Thema’s van de menu-tab Pagina-indeling
  3. via de optie Nieuwe themakleuren maken onderaan komt u in een scherm waarin u de diverse basiskleuren kunt aanpassen
  4. geef dit kleurenschema een toepasselijke naam en kies Opslaan
  5. op een vergelijkbare manier kunnen ook de thema-lettertypen gewijzigd worden
  6. kies als laatste binnen de optie Thema’s de optie Huidig thema opslaan. Geef ook hier weer een toepasselijke naam op.

NB1 het nieuwe thema komt onder het kopje Aangepast tevoorschijn.

NB2 wijzig de eerste 2 kleuren binnen een thema niet; laat deze altijd op zwart en wit staan

NB3 het effect van kleurenwijzigingen zijn in het voorbeeldscherm rechts direct zichtbaar

Thema importeren

Heeft iemand een mooi of in ieder geval een bruikbaar thema gemaakt dan kunt u die importeren.

  1. download als voorbeeld het G-Info-thema
  2. vanwege beveiligingsredenen heeft deze een verkeerde extensie; wijzig in de map Downloads de naam van het bestand GInfo.zip in GInfo.thmx
  3. kies in de menu-tab Pagina-indeling in het blok Thema’s de optie Thema’s
  4. selecteer dan onderaan in het nieuwe scherm de optie Thema’s zoeken
  5. blader naar de Doenloads-map en kies het bestand GInfo.thmx
  6. wilt u dit thema vaker gebruiken vergeet dan niet om het op uw eigen systeem te bewaren: kies de optie Huidig thema opslaan

NB1 wilt u nog iets anders uitproberen: download het Carnaval-thema

NB2 hebt u zelf een thema opgeslagen en wilt u dit met anderen delen?
De plaats van dit thema is afhankelijk van het besturingssysteem en office-versie. Waarschijnlijk kunt u het in een vergelijkbare map vinden:
C:\Users\gebruikersnaam\AppData\Roaming\Microsoft\Sjablonen\Document Themes
Eventueel kunt u binnen de Verkenner zoeken naar UwThemanaam.thmx.

Meer kleuren

Wilt u uw bedrijfskleuren gebruiken of kleuren die u op een andere website tegenkomt dan zult u de codes daarvan moeten zien te achterhalen. Dat kan op diverse manieren:

  1. vraag uw marketingafdeling naar de kleurcodes.
    Er zijn 2 soorten nummering gangbaar: een hexadecimale code (bijvoorbeeld D1D2D4 voor lichtgrijs) of de daarmee samenhangende RGB-code (bijvoorbeeld 209 voor Rood, 210 voor Groen en 212 voor Blauw levert dezelfde kleur lichtgrijs).
    NB Hoe u een decimale code omzet naar de drie RGB-getallen kunt u zien op het tabblad Data van het Voorbeeldbestand.
  2. installeer (gratis) de extensie Eye Dropper in de internet-browser Google Chrome
  3. gebruik het (gratis) programma ColorCop, te downloaden via de website colorcop.net
  4. of via één van de vele andere programma’s (te vinden via een Google-zoekopdracht)

Hebt u de RGB-codes van de kleur of de kleuren, die u wilt gaan gebruiken, dan kunt u deze invoeren in het scherm Aangepast binnen de optie Meer kleuren.


Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd:

 

 

Draaitabel opmaken

Kleine ergernissen kunnen soms uitgroeien tot grote.

Iedereen, die regelmatig een draaitabel maakt, kan dat waarschijnlijk beamen: de draaitabel is snel gemaakt, maar dan moeten er nog diverse handelingen uitgevoerd worden om de lay-out zodanig aan te passen, dat de draaitabel er uitziet, zoals je graag wilt.
Veel van die instellingen zitten niet bij elkaar in de menu-structuur, dus is het zoeken, muizen en klikken geblazen.

Eén van die instellingen is bijvoorbeeld de rapportindeling: iemand bij Microsoft heeft bedacht,  dat de standaard-instelling daarvoor Compact moet zijn en deze standaard is niet te wijzigen.
Deze week bleek uit een vraag van een oud-collega, dat ik niet de enige ben die liever met de ‘klassieke’ indeling werkt.

PT1In dit artikel zal ik laten zien, dat het mogelijk is om alle aanpassingen, die je vaak maakt, op één formulier te plaatsen, zodat het lastige zoeken naar de diverse menu-opties achterwege kan blijven.

Hieronder zal ik uitleggen hoe zo’n  formulier wordt opgebouwd; de gegevens, die ik als voorbeeld gebruik, de daarbij behorende draaitabel en een reeds gebouwd (uitgebreider) formulier vind je in het Voorbeeldbestand.

Formulier maken

Een formulier voor Excel wordt gemaakt in de VBA-editor.
PT2In de editor kom je, wanneer je in de menutab Ontwikkelaars in het blok Programmacode de optie Visual Basic kiest.
Maar veel makkelijker is natuurlijk de toetscombinatie Alt-F11.

NB Is de menutab Ontwikkelaars niet zichtbaar, pas dan de Menubalk aan bij Bestand/Opties.

In de VBA-editor maken we nu als volgt een formulier met enkele voorbeeld-opties:

  1. zorg in het Project-overzicht dat een Excel-map is geselecteerd door er op te klikken (heb je geen Excel-map open, ga dan terug naar Excel (Alt-F11), klik Ctrl-N en ga weer naar VBA)
  2. kies in de menu-balk Invoegen en daarbinnen de optie UserForm
  3. PT3er opent zich een leeg raster (het nieuwe formulier) waarop we met behulp van de Werkset allerlei besturingselementen kunnen plaatsen
  4. Links onder het Project-overzicht zie je het Eigenschappen-venster van het actieve element; nu staan daar de eigenschappen van het formulier.
    Voorlopig zijn alleen de eigenschap Name, waarmee we het formulier binnen VBA kunnen benaderen,  en de Caption (de titel bovenaan het formulier) relevant.
    De overige eigenschappen zijn vooral bedoeld om de lay-out van het formulier te beïnvloeden.
    Bij Name voeren we in UF_DT2 en bij Caption de tekst Draaitabel opmaken.
  5. Nu gaan we 2 opties toevoegen waarmee we kunnen bepalen of er wel of niet rij- en kolomtotalen moeten worden weergegeven in de draaitabel:
    * klik in het grijze raster
    * sleep vanuit de Werkset het selectievakje PT4 naar het raster op de plaats, waar de optie moet komen en herhaal dat een 2e keer (de eerste krijgt automatisch de naam Checkbox1 en de tweede Checkbox2)
    * de namen laten we zo, maar de Caption veranderen we via de Eigenschappen: de eerste krijgt wordt Rij-totalen, de tweede Kolom-totalen
  6. Voor de Rapportindeling willen we drie opties aanbieden. In tegenstelling tot de opties van het vorige punt sluiten deze elkaar uit: een draaitabel kan niet tegelijkertijd in de Compact- en in de Klassieke weergave staan, terwijl we natuurlijk wel de rij- en kolom-totalen los van elkaar willen kunnen aan- en uitzetten.
    Hiervoor gebruiken we dan ook geen Selectievakjes maar Keuzerondjes:
    * sleep drie keer een keuzerondje PT6 naar het formulier
    * verander de Caption daarvan in Compact, respectievelijk Overzicht en Classic
    * de namen van de rondjes laten we gehandhaafd (die blijven dus OptionButton 1, 2 en 3)
  7. Test het formulier even:
    * druk op F5
    * klik op de diverse opties: de Selectievakjes kunnen los van elkaar aan- en uitgezet worden, terwijl er bij de Keuzerondjes altijd maar één actief is
    * het formulier doet verder nog niets; klik rechtsboven op het kruisje om te sluiten
  8. Dan gaan we 2 opdrachtknoppen toevoegen:
    * sleep 2 keer een Opdrachtknop vanuit de Werkset naar het raster. Heb je te weinig plaats: maak het raster groter door de randen te verslepen of de reeds geplaatste elementen te verplaatsen.
    * de eerste opdrachtknop geven we de volgende eigenschappen:  de naam wordt ok en ook de caption wijzigen we daarin; de tweede krijgt de naam cancel en als caption Annuleren
  9. Klik met de rechter muisknop op de 2e opdrachtknop en kies de optie Programmacode weergeven. Alles wat  PT5tussen de 2 regels staat (nu nog niets!) zal worden uitgevoerd wanneer we op de Annuleer-knop klikken. Tik in de 2e (lege) regel in: unload me
    Dit is voor VBA het commando om het formulier (me) uit het geheugen te verwijderen en dus te sluiten.
    NB omdat VBA de 2 woorden herkent zullen er (na ‘Enteren’) automatisch hoofdletters tevoorschijn komen.
  10. Test het formulier opnieuw:
    * dubbelklik in het projectoverzicht op UF_DT2
    * druk op F5.
    * wat doet de ok-knop? En de annuleer-knop?

NB De programmacode, die nodig is wanneer er op de OK-knop wordt geklikt, is wel wat uitgebreider. Door in het Voorbeeldbestand in de VBA-editor rechts op de ok-knop te klikken kan de programma-code bekeken worden.

LET OP heb je nog meer keuzerondjes nodig, maar moeten die niet afhankelijk zijn van de keuze van de overige rondjes, zorg dan dat ieder blok bij elkaar horende keuzerondjes in een zogenaamd Groepsvak (ook een element van de Werkset) wordt geplaatst.
In het Voorbeeldbestand zijn op die manier de instellingen voor de Rapport-indeling en voor de Veld-opmaak afzonderlijk in  te stellen.

Formulier activeren

Nu moeten we nog een mogelijkheid maken om het formulier in Excel op te roepen:

  1. PT7we gaan weer naar VBA (Alt-F11)
  2. klik in de projectverkenner op de Excel-map, waarin het formulier UF_DT2 staat
  3. kies in de het VBA-menu Invoegen en daarbinnen de optie Module
  4. tik dan in:
    sub Start()
    UF_DT2.show
    end sub
Het formulier kun je voortaan opstarten door de macro Start aan te roepen: kies in Excel (om vanuit VBA naar Excel te gaan: Alt-F11) in de menutab Ontwikkelaars in het blok Programmacode de optie Macro’s, klik op de macro Start en dan op de knop Uitvoeren.
NB1 het macro-overzicht kun je ook bereiken door op Alt-F8 te drukken.

NB2 de regel UF_DT2.Show is voldoende om het formulier te tonen. In het Voorbeeldbestand is die routine uitgebreid met programmacode, die er voor zorgt dat de keuzerondjes en selectievakjes overeenkomen met de instellingen van de draaitabel op dat moment.

NB3 heb je de macro/routine vaak nodig, dan is het handig om daar een toetscombinatie aan te koppelen:

  1. ga naar de macro’s (Alt-F8)
  2. klik op de macro Start
  3. klik op de knop Opties
  4. tik een letter in (eventueel samen met Shift) waarmee de macro opgestart moet worden (bijvoorbeeld q, want de sneltoets Ctrl-q wordt door Windows niet standaard gebruikt)
  5. klik Ok en dan Annuleren (dus niet Uitvoeren!)
  6. probeer de toetscombinatie uit.

Formulier altijd klaar voor gebruik

In het voorbeeld hiervoor en in het Voorbeeldbestand zijn het formulier en de diverse routines in een Excel-werkmap opgenomen en kunnen dus ook alleen gebruikt worden, wanneer deze werkmap is geopend.

Wil je altijd de beschikking hebben over formulieren of routines dan moeten die worden opgenomen in de persoonlijke, onzichtbare werkmap Personal.xlsb.
Hoe kom je aan die werkmap? Het meest eenvoudige is op de volgende manier:

  1. PT8kies in Excel in de menutab Ontwikkelaars in het blok Programmacode de optie Macro opnemen
  2. Zorg dat in het middenvak de optie Persoonlijke macrowerkmap is geselecteerd
  3. klik OK
  4. klik dan met de muis op een willekeurige cel en stop de opname
  5. ga naar de VBA-editor (Alt-F11) en verwijder in de Module1 van Personal.xlsb de zojuist opgenomen macro.

Formulier en routine kopiëren naar de persoonlijke werkmap:

  1. open het Voorbeeldbestand (als dat nog niet gebeurd is)
  2. ga naar de VBA-editor (Alt-F11)
  3. sleep het formulier UF-DT vanuit PT.xlsm naar Personal.xlsb
  4. idem met de Module1
  5. sluit Excel en bevestig de vraag over het opslaan van de persoonlijke map.

Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd:

Weekdagen en weeknummers

Kalender-2016-LandscapeEén van de eerste artikelen op de site van G-Info ging over datums en de mogelijkheden van de diverse opmaak-opties (klik hier voor het betreffende artikel).

Vorige week kreeg ik een vraag over weekdagen en weeknummers in Access. Toen bleek, dat het probleem niet met opmaak was op te lossen, viel het me weer op, hoe ingewikkeld het kan zijn om dit soort vraagstukken met behulp van formules te tackelen.
Ook de (soms) grote verschillen tussen Excel en Access maken het er niet makkelijker op (hoezo, allebei een onderdeel van Microsoft Office!) .

Daarom maar eens wat mogelijkheden (en onmogelijkheden) op een rijtje gezet.

Opmaak in Excel

Zoals gezegd heb ik daar in een eerder artikel al eens over geschreven.
KalenderIn het Voorbeeldbestand heb ik een overzicht gemaakt met de dagen van de laatste week van 2016 en de eerste 2 van 2016.
De datums staan in kolom F; in de kolommen G t/m J staan verwijzingen naar de dagen in kolom F, maar met behulp van celopmaak (Ctrl-1 indrukken) is de lay-out van de cellen veranderd.

Voordeel hiervan is, dat makkelijk en snel de opmaak aangepast kan worden aan je wensen.
Nadeel is echter, dat je niet rechtstreeks formules of filters op deze cellen kunt los laten; de inhoud van die cellen blijft een datum (intern voor Excel een getal). Ook is het niet mogelijk om op deze cellen een Voorwaardelijke opmaak toe te passen.

NB wanneer je een Filter aanbrengt op het overzicht zul je zien, dat de filtermogelijkheden voor de kolommen F t/m J allemaal hetzelfde zijn: Excel ziet, dat het datums betreft en geeft daarom de mogelijkheden om te filteren op jaar, maand of dag. Het is NIET mogelijk om te filteren op bijvoorbeeld maandag.

Kalender2Experimenteer met de aangepaste opmaak via Celeigenschappen (Ctrl-1). Gebruik combinaties van de letters d, m en j; 1 letter, 2 letters etc. Het resultaat zie je direct in het vak Voorbeeld.

Datum-formules in Excel

Wil je kunnen rekenen, filteren, sorteren met de diverse onderdelen van een datum dan voldoet de opmaak niet meer. Je zult dan formules moeten gebruiken, waarmee de datum ‘ontleed’ wordt.

In het Voorbeeldbestand zijn in de kolommen K, L en M formules gebruikt om het jaar, de maand en de dag van een datum op te halen; in cel K4 bijvoorbeeld staat de formule =JAAR(F4).

Niets bijzonders, handig, recht-toe-recht-aan.
Willen we echter de weekdag ophalen (is het een maandag, dinsdag?) , dan blijkt er in Excel geen functie te zijn, die dat rechtsreeks oplevert; wel kunnen we het volgnummer van de dag in de week opvragen met de functie Weekdag.
Daar zit echter een addertje onder het gras: er is in de wereld geen overeenstemming over welk nummer bijvoorbeeld de maandag moet krijgen: 1 zoals in Europa gebruikelijk is of moet het 2 zijn (een nieuwe week begint op zondag)?
Aan de functie Weekdag moet dus altijd kenbaar worden gemaakt wat de eerste dag van de week is (als je dat niet meegeeft, dan wordt de standaard genomen en krijgt zondag volgnummer 1).
In cel N4 staat de formule =WEEKDAG(F4;KzWkDag)

Kalender3

Er wordt dus gezocht naar de weekdag, die hoort bij de datum in cel F4, waarbij als Type_getal de waarde 2 wordt meegegeven (maandag krijgt volgnummer 1).
Type_getal verwijst naar de cel met de naam KzWkDag (cel C3). Via Gegevensvalidatie kunnen daar alleen de voor deze functie toegestane waarden 1, 2, 3 en 11 t/m 17 ingevoerd worden.
Met VERT.ZOEKEN is in cel D3 te zien wat een bepaalde waarde inhoudt. Op het tabblad Parameters is de bron voor de Gegevensvalidatie en het verticaal zoeken te vinden in de kolommen B en C.

NB met een formule kunnen we dus het volgnummer van een weekdag afleiden, willen we echter een omschrijving dan wordt het iets ingewikkelder.
In cel Q4 staat een voorbeeld:
=KIEZEN(N4;”ma”;”di”;”wo”;”do”;”vr”;”za”;”zo”)
Let wel op: de volgorde van de dagen is afhankelijk van het Type_getal, dat in cel N4 is gebruikt!

Met opmaak is het niet mogelijk om in Excel het weeknummer te zien; wel is daar een functie voor. In het Voorbeeldbestand wordt die in kolom O gebruikt.
In cel O4 komt dan de formule =WEEKNUMMER(F4;KzWkNr)

Ook deze functie kent een 2e paramater (het Type_resultaat). Welke keuze hier gemaakt moet worden is nog iets ingewikkelder dan bij Weekdag. Want niet alleen is het van belang op welke dag een nieuwe week begint, ook moeten we aangeven wanneer we met weeknummer 1 beginnen.
Voor dat laatste onderscheidt Excel 2 systemen: bij Systeem 1 is Nieuwjaar het begin van week 1, bij Systeem 2 (het Europese systeem) begint week 1 met die week, waarin de eerste donderdag valt (en de week begint dan automatisch op maandag).

Om Systeem 2 te kiezen moet als 2e paramater voor Weeknummer het getal 21 meegegeven worden.

Opmaak in Access

Net als in Excel kunnen we in Access de datum “in stukken knippen” door een opmaak daaraan mee te geven.
In een query, die gebaseerd is op een tabel met een veld Datum, kunnen we bijvoorbeeld een nieuw veld toevoegen (met de naam JrF) om het jaar af te leiden:
JrF: Format([datum];”yyyy”)

LET OP Access verwacht Engelse functienamen en ook de Engelse afkorting voor het jaar.

We kunnen ook minder letters aan de functie meegeven, maar het resultaat is soms iets anders dan in Excel:
y: dit levert het volgnummer van de dag in het jaar
yy: het jaar in 2 cijfers
yyy: het jaar in 2 cijfers en direct daarachter het volgnummer van de dag in het jaar (dus een combinatie van yy en y)

Voor het maandnummer kan een vergelijkbare formule gebruikt worden:
MndF: Format([datum];”m”)
mm: het maandnummer altijd in 2 cijfers
mmm: de afkorting voor de maand (bijvoorbeeld jan)
mmmm: een volledige maand-aanduiding (bijvoorbeeld januari)

Voor de dag van de maand gebruiken we dan:
DagF: Format([datum];”d”)
dd: het dagnummer altijd in 2 cijfers
ddd: de afkorting voor de dag van de week (bijvoorbeeld ma)
dddd: een volledige dag-aanduiding (bijvoorbeeld maandag)

Binnen Access is het op deze manier ook mogelijk om het kwartaalnummer automatisch te genereren:
KwF: Format([datum];”q”)

Ook kunnen we de w als parameter gebruiken, maar ook nu dienen we nog extra parameters mee te geven:
WkF: Format([datum];”w”;2)
Deze formule levert het volgnummer van de dag in de week op. Maar net als in Excel moeten we dan wel opgeven op welke dag een nieuwe week begint. Geven we als 3e parameter een 0 (nul) mee, dan worden systeeminstellingen gebruikt, 1 dan begint de week op zondag etc.

Als laatste nog:
Wk2F: Format([datum];”ww”;2;2)
Dit levert het weeknummer in het jaar; net als in Excel is het wel van belang welk systeem daarbij gebruikt moet worden.
Als de 4e parameter 0 (nul) is dan worden de systeeminstellingen gebruikt, 1 dan begint de eerste week op Nieuwjaar, 2 dan is de eerste week afhankelijk van de eerste donderdag en 3 dan is de eerste week die week, die volledig in het nieuwe jaar ligt.

LET OP de functie Format retourneert een tekst; wil je rekenen met de uitkomst hiervan neem dan de waarde van het resultaat: Val(Format([datum];”d”))

Datum-formules in Access

Willen we direct rekenen met de dagen, maanden, weken etc (zonder gebruik te maken van de Val-functie) dan kent Access nog de functie DatePart.

Als we bijvoorbeeld in een query als veld opgeven
JrNr: DatePart([datum];”yyyy”)
dan resulteert dit in een getal met 4 cijfers, dat het jaar aangeeft.

Alle instellingen, die hiervoor bij Access-opmaak zijn toegelicht, zijn hier toepasbaar.
Dus bijvoorbeeld DatePart([datum];”ww”;1;2) levert het weeknummer (als getal) op, waarbij een week begint op zondag en de eerste donderdag bepalend is voor de weeknummer 1.


Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd:

Urenregistratie

prikklokHoewel een echte  prikklok niet vaak meer wordt gebruikt, wordt bij veel bedrijven nog steeds gebruik gemaakt van een bepaalde manier van tijdregistratie.
Dit kan nodig zijn om binnen het bedrijf een kostenallocatiemodel te voeden of om als externe inhuur verantwoording af te leggen over de in rekening gebrachte uren, enzovoort.

Daarom in dit artikel aandacht voor Excel als hulpmiddel voor urenregistratie; inclusief wat VBA om het gebruik wat makkelijker te maken.

Basis

Het is een goede gewoonte om bij de opbouw van een Excel-systeem een scheiding aan te brengen tussen de diverse onderdelen; in dit geval hebben we dan ook de invoer van de gemaakte uren en de rapportage daarover in aparte tabbladen opgenomen.

In het Voorbeeldbestand is dit verder uitgewerkt.
De registratie en rapportage zijn geen complexe items, maar we kunnen wel diverse handige Excel-trucjes gebruiken om het systeem flexibeler en fraaier te maken.

uren1

Laten we de kolommen in het tabblad Invoer eens langs lopen:

  1. in kolom A komt de datum: in cel A4 staat de eerste datum waarop de registratie is begonnen; in A5 staat de formule =A4+1; in A6 de formule =A5+1 etc.
    Het voordeel hiervan is, dat als we met een nieuwe registratie willen beginnen, we alleen de datum in cel A4 hoeven te wijzigen
  2. voor een snel inzicht tonen we in kolom B de dag van de week; niet via een Excel-functie Weekdag (die geeft alleen het volgnummer in de week) maar door opmaak.
    De formule in B4 is =A4.
    NB De invoer is als tabel in Excel opgezet; dat houdt onder andere in, dat als je zo’n formule in B4 intypt deze automatisch in de rest van de kolom wordt overgenomen (maar wel met een verwijzing naar A5, A6 etc).
    Via de celeigenschappen hebben de cellen in kolom B een speciale datumopmaak meegekregen, namelijk dddd. Deze zorgt er voor, dat de datum als volledige weekdag wordt weergegeven.
    NB experimenteer met het aantal d’s en kijk wat dit voor invloed heeft op de opmaak.
  3. in kolom C wordt per dag de begintijd ingevoerd
  4. we gaan er van uit, dat een dag uit 2 blokken bestaat; in D komt dan de eindtijd van blok1, in E een eventueel begin van blok2  en in F een eventueel einde.
    De opmaak van de kolommen C, D, E en F is u:mm (dus minstens 1 positie voor het uur en altijd 2 voor de minuten).
  5. uren2dan wordt het spannender: in kolom G komt een formule, die de gewerkte tijd (nou ja, de aanwezigheid) berekent. Dit gebeurt in 2 gedeeltes: in de eerste Als berekenen we het verschil tussen begin- en eindtijd van het eerste blok (als er nog geen eindtijd is (D4=””) dan maken we dat verschil gelijk aan 0); en we tellen daar het resultaat van de tweede Als bij op, die een eventueel verschil van blok2 bepaalt.
    Ook deze kolom krijgt als opmaak u:mm, omdat we natuurlijk het resultaat in uren en minuten willen weten.
    Wanneer het resultaat nul is (in het weekend of op andere (nog) niet gewerkte dagen), willen we in kolom G niets zien: de opmaak is dan ook uitgebreid met een extra voorwaarde: uren3
  6. Kolom H bevat ook het aantal uren, maar nu uitgedrukt als een decimaal getal. Om de gehanteerde formule in die kolom te begrijpen, moet je weten, dat Excel een tijd als een deel van een gehele dag opslaat: 24 uur is het getal 1, 12 uur is 1/2, 6 uur wordt vastgelegd als 1/4 etc.
    Andersom: willen we de tijd uit kolom G (die dus als fractie van een hele dag is opgeslagen) weergeven als uren, dan moeten we die tijd met 24 vermenigvuldigen. Aangezien we met een Excel-tabel werken, wordt dit via een zogenaamde gestructureerde verwijzing in de formule weergegeven: uren4
    (Op tabellen en gestructureerde verwijzingen zal ik een andere keer terugkomen)
  7. in kolom I kunnen (relevante) opmerkingen, die de registratie verduidelijken, worden opgenomen.

Rapportage

De maandrapportage van de tijdsbesteding is in het Voorbeeldbestand in het tabblad MndOvz opgenomen.
Door de datum van de eerste van een maand op te geven, worden op deze pagina de bij die maand behorende gegevens uit de database opgehaald. Hierbij wordt een alternatief voor verticaal zoeken gebruikt.

uren5

  1. van de bovenste 6 rijen is alleen cel D5 echt van belang: deze geeft aan van welke maand de gegevens worden weergegeven (de eerste van de maand moet worden ingetikt; de opmaak laat alleen maar maand en jaar zien).
    De rest is verfraaiing/toelichting.
  2. in kolom B staan vanaf regel 11 de dagen van de betreffende maand weergegeven; althans het volgnummer van de dagen.
  3. in kolom C staat de werkelijke dag, via de formule =$D$5+B11-1 (dus bij de eerste van de maand (D5) wordt het volgnummer opgeteld; omdat we dan altijd 1 dag te ver uitkomen trekken we er nog 1 vanaf).
    NB1 Kolom C is niet verborgen, maar via groepering ‘dichtgeklapt’. Klik op het +-teken boven D om kolom C zichtbaar te maken. Groeperen zit in de menutab Gegevens, in het blok Overzicht.
    NB2 aangezien we natuurlijk alleen maar datums uit de betreffende maand willen zien (en bijvoorbeeld geen 31 april) is de formule vanaf C12 iets ingewikkelder.
  4. laten we dan eens kijken wat de formule in D11 doet:
    =ALS.FOUT(INDEX(Uren;VERGELIJKEN($C11;Uren[Datum];0);D$10);””)
    Uren is de naam van de tabel uit het invoerblad.
    Uren[Datum] is de datum-kolom in die tabel.
    VERGELIJKEN($C11;Uren[Datum];0) kijkt op welke plaats de waarde uit C11 (in dit geval dus 1 april) in die kolom staat; de 0 zorgt er voor, dat Excel de waarde zoekt, onafhankelijk in welke volgorde die ook zouden staan (een exacte match dus).
    INDEX(Uren; ‘plaats van datum’ ;D$10) geeft de waarde van díe cel in de tabel Uren, die in de rij ‘plaats van datum’ staat en in de kolom, die overeenkomt met de waarde in cel D10.
    ALS.FOUT geeft een lege waarde (“”) als één van de formules INDEX of VERGELIJKEN een fout oplevert (bijvoorbeeld als de datum uit C11 niet in de tabel Uren voorkomt).
    NB in het overzicht staat ook een dichtgeklapte rij 10; daar staat in welke kolom Index moet zoeken.
  5. de overige cellen in het overzicht zijn op eenzelfde manier opgezet
  6. onderaan staat nog een totaaltelling: in cel I42 worden de ‘decimale’ uren opgeteld. In H42 de ‘normale’ uren en minuten; wanneer het aantal uren boven de 24 komt, zal Excel standaard weer opnieuw bij 0 beginnen. Willen we die uren boven de 24 zichtbaar maken dan dienen we de betreffende cel een andere opmaak mee te geven: uren6. Let op de vierkante haken!

VBA

Om het gebruik van het spreadsheet wat te vergemakkelijken is nog een VBA-routine toegevoegd.
De bedoeling van de routine is om bij het openen van het bestand de cursor op de juiste plaats te hebben staan om snel nieuwe invoer te kunnen doen.

De VBA-routine gaat automatisch naar het Invoer-blad en zoekt de regel op net onder de laatste invoer.
uren7
VBA-routines worden veelal opgeslagen in zogenaamde Modules; omdat deze routine direct actief moet worden wanneer de werkmap (in het Engels Workbook) wordt geopend staat deze routine in de map ThisWorkbook en heeft de naam Workbook_Open gekregen.

Laten we de routine even stapsgewijs doorlopen:

  1. open het Voorbeeldbestand
  2. ga naar Visual Basic (bijvoorbeeld via de toetscombinatie Alt-F11)
  3. dubbelklik op de map ThisWorkbook
  4. de routine begint met wat toelichtende commentaarregels (de groene regels na de apostrof)
  5. uren8dan worden 2 variabelen gedeclareerd, die we hierna nodig hebben (strGebrNaam en strDagDeel)
  6. de 2 variabelen worden gevuld; de bedoeling lijkt me duidelijk
  7. en dan het ‘echte’ werk:
    uren9
    Eerst selecteren we het tabblad (de sheet) Invoer; dan selecteren we in de kop (Header) van de tabel Uren de cel met het woord Datum.
    Vervolgens wordt de toets-combinatie Ctrl-Pijl-naar-beneden nagebootst en dus de onderste gevulde datum geselecteerd.
    Dan 2 kolommen naar rechts om in de kolom Begin1 te komen, waar we via Ctrl-Pijl-naar-boven de laatst gevulde cel zoeken.
    Die is al gevuld, dus selecteren we de cel daaronder.
    Als laatste wordt een pop-up op het scherm getoond.
    NB vbCrLf is de code, die er voor zorgt dat de volgende tekst, die via het &-teken aan het voorgaande wordt ‘geplakt’, 1 regel lager zal komen (een zogenaamde harde-return). Zie Teksten samenvoegen voor uitleg.

Als de VBA-routine niet duidelijk is, laat dan commentaar of een vraag achter op de website.


Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd:

Voorwaardelijke opmaak

Het zuiden van Nederland maakt zich op voor Carnaval.

CarnavalWaar de schmink bij Carnaval meer bedoeld is om het echte gezicht te verbergen of te versieren, is de opmaak bij Excel bedoeld om zaken te benadrukken, duidelijker te maken.

Zoals in eerdere artikelen beloofd, gaan we het deze keer hebben over Voorwaardelijke opmaak.
Iedereen maakt er wel eens gebruik van; misschien zonder het zelf te weten!
Er zijn allerlei vormen: van het simpel tussenvoegen van een punt, wanneer getallen groter zijn dan 1.000 tot het tonen van grafische tekens in een cel afhankelijk van andere gegevens in de Excel-werkmap.

Celeigenschappen/opmaak

opmaakBij celeigenschappen vinden we de meest gebruikte opmaak-opties van Excel. We beperken ons deze keer tot de opmaak van cellen met numerieke waarden.

Hebt u dat ook met grote getallen: hebben we het nou over tonnen of over miljoenen?
Zie Voorbeeldbestand

Excel biedt ons een snelle mogelijkheid om deze getallen beter leesbaar te maken:

  1. opmaakselecteer alle getallen die een nieuwe opmaak moeten krijgen
  2. kies Start in de menubalk en binnen het blok Getal, de optie Duizendtalnotatie (de 000)
  3. met de 2 knoppen rechts daarvan kun je meer of minder decimalen weergeven

NB de inhoud van de cellen verandert niet, slechts de weergave.

Moeten de negatieve getallen een andere kleur krijgen (meestal kiezen we daarvoor rood):

  1. opmaakselecteer alle cellen die deze opmaak moeten krijgen
  2. klik met de rechtermuisknop op de selectie en kies Celeigenschappen; dit kan ook sneller mbv Ctrl-1 (Ctrl vasthouden en de 1 indrukken)
  3. kies het tabblad Getal en bij de Categorie weer Getal
  4. kies geen decimalen, wel scheiding voor duizendtallen en kleur

Naast de standaard-opmaak die in Excel is ingebouwd, kunnen we de opmaak ook nog voor een groot gedeelte zelf bepalen.
Daartoe moeten we weer even terug naar de Celeigenschappen, het tabblad Getal. Wanneer we bij Categorie de optie Aangepast kiezen, laat Excel bij Type de code zien die gebruikt is voor de opmaak, bijvoorbeeld: opmaak
In dit geval bestaat de code uit 2 secties (gescheiden door een punt-komma); het eerste geeft aan hoe positieve getallen moeten worden weergegeven, de tweede geldt voor negatieve getallen. Zonder op alle details in te gaan, lijkt me de hoofdstrekking van de diverse codes duidelijk.

NB pas de opmaak via een van de standaardopties aan en bekijk bij Aangepast welke codes daarvoor gebruikt worden.

Excel kent in principe 4 opmaak-secties: voor positieve getallen, negatieve, voor de nul-waarde en voor tekst. Maar we mogen ook eigen secties maken; bijvoorbeeld als we alle getallen groter dan 100 blauw willen maken, gebruiken we in een sectie [Blauw][>100]#.##0

Deze laatste handigheid kan mooi toegepast worden bij grafieken, aangezien Celopmaak ook gebruikt kan worden bij het opmaken van de assen. Op deze manier kunnen bepaalde gebieden op een as een aparte kleur krijgen.

NB om nog meer mogelijkheden te ontdekken: bekijk alle opmaak in het tabblad CelOpmaak van het Voorbeeldbestand.

Opmaak afhankelijk van inhoud cel

Heel vaak kan het handig zijn om de opmaak van een cel afhankelijk te maken van de inhoud daarvan: onderscheid maken tussen positieve en negatieve waarden, analyse van nauwelijks zichtbare verschillen (of andersom wanneer zijn 2 waardes gelijk), controlegetal moet 0 (nul) zijn etc.

Dit is met Celeigenschappen niet of slechts moeizaam te regelen, waarbij de opmaak beperkt blijft tot het kleuren van de tekst.
Met de optie Voorwaardelijke opmaak komen we een stuk verder.

Laten we als voorbeeld de aan- en afmeldingen eens bekijken van een sportschool (zie Voorbeeldbestand, tabblad CelVoorw). Hiernaast is te zien, dat er in januari meer leden zijn vertrokken dan er zijn aangemeld. Om snel inzicht te krijgen in het maandresultaat hebben de cellen in kolom E een opmaak gekregen, die afhangt van de inhoud: voorwaardelijke opmaak dus.
Hoe doen we dat:

  1. opmaakselecteer alle cellen in kolom E die de opmaak moeten krijgen
  2. kies Start in de menubalk en binnen het blok Stijlen de optie Voorwaardelijke opmaak
  3. in het submenu op Nieuwe regel klikken
  4. neem als type Alleen cellen opmaken met
  5. en zorg dat onderstaande regel wordt aangemaakt
    opmaak
  6. kies een daarbij passende opmaak, bijvoorbeeld groene opvulling

Aanpassen voorwaardelijke opmaak
We zouden op dezelfde manier ook een rode kleur kunnen toevoegen aan cellen met een negatieve waarde, maar dat gaan we anders doen:

  1. selecteer een cel in kolom E
  2. kies Start in de menubalk en binnen het blok Stijlen de optie Voorwaardelijke opmaakopmaak
  3. in het submenu op Regels beheren klikken
  4. Excel is zo slim om te zien, dat we de huidige selectie willen beheren. Duidelijk is te zien wat onze eerste regel doet en op welke cellen deze betrekking heeft. De voorwaarde en de opmaak zijn te veranderen door Regel bewerken te kiezen.
  5. we voegen een regel toe via Nieuwe regel. Zorg dat de cel een rode achtergrond krijgt als de waarde kleiner is dan nul.
  6. vergeet niet deze regel van toepassing te laten zijn op alle relevante cellen in kolom E

NB druk op de functietoets F9 om de sheet opnieuw door te rekenen; aangezien de aan- en afmeldingen aselect gekozen worden, zal het resultaat ook iedere keer anders zijn.

Andere voorbeelden
In de kolommen G t/m J worden twee resultaten vergeleken. Als de waarde in kolom H gelijk is aan de waarde in I, dan zorgt de formule in kolom J ( in J3 staat bijvoorbeeld =H3=I3) er voor, dat het resultaat WAAR verschijnt (zo niet, dan ONWAAR). Via voorwaardelijke opmaak is in één oogopslag te zien waar de kolommen gelijke waardes bevatten (eventueel even F9 drukken voor nieuwe gegevens).

In cel O3 is een verbandcontrole ingebouwd: is het resultaat nul, dan kleurt de cel groen, anders rood (probeer maar eens uit als ergens in kolom O een resultaat wordt gewist of, wat in de praktijk nog wel eens wil gebeuren, als de formule in O door een harde waarde wordt overschreven.

Opmaak afhankelijk van inhoud van een andere cel

“Maar, wat als ….”
Een bekende frase in automatiseringsland: iedere keer moeten er weer grenzen verlegd worden.

opmaakIn het Voorbeeldbestand heb ik een tabblad Energie opgenomen, waarop per dag de meterstanden worden genoteerd. Excel berekent voor ons dan het dagverbruik.
Bovenaan berekenen we het lopende gemiddelde voor het verbruik van stroom en gas, zodat we in ieder geval kunnen zien of we onder of boven het gemiddelde scoren.
Met behulp van Voorwaardelijke opmaak willen we dat natuurlijk direct laten zien; eerst voor de stroom:

  1. selecteer alle relevante cellen in kolom F (in dit geval F7:F75)
  2. kies Start in de menubalk en binnen het blok Stijlen de optie Voorwaardelijke opmaak en dan weer Nieuwe regel
  3. als type kunnen we niet meer de tweede optie gebruiken; de voorwaarde hangt ook af van de inhoud van een andere cel. We moeten een formule gaan gebruiken, dus kiezen de laatste optie.
  4. tik dan als formule in: =F7<$F$3, kies een bijpassende Opmaak (tekst groen bijvoorbeeld; we zitten onder het gemiddelde) en klik OK
    NB een formule moet altijd met het =-teken beginnen, daarachter volgt de voorwaarde: F7<$F$3.
    F7 is een relatieve verwijzing, $F$3 een absolute; dat betekent dat als Excel deze voorwaarde één cel lager toepast hij deze interpreteert als F8<$F$3 etc.
  5. op dezelfde manier kunnen we een kleur geven aan het verbruik dat hoger uitkomt dan gemiddeld

Kijk in het Voorbeeldbestand hoe de overige opmaak is geregeld door Regels beheren op te roepen.

Nog een ander voorbeeld
opmaakEen klas heeft 3 proefwerken gehad en we zien direct wie gemiddeld lager dan 5,5 staat.

Ook hier is gebruik gemaakt van voorwaardelijke opmaak: op de cellen B4:F11 is de volgende formule als Nieuwe Regel ingevoerd:
=$F4<5,5
In de formule is de kolomnaam F absoluut, want voor alle cellen in B4:F11 kijken we voor de voorwaarde naar kolom F, maar de rijnaam 4 is relatief; dus in de cellen B5:F5 zal Excel beoordelen of F5 kleiner is dan 5,5 etc.


Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd: