Maandelijks archief: april 2016

Kunst en Excel

Excel,

Perry2Een tentoonstelling over het leven en het werk van Grayson Perry in het Bonnefantenmuseum in Maastricht (nog te zien tot en met 5 juni 2016; laat deze kans niet voorbij gaan!) ontroerde mij zeer.
Waarom eigenlijk? Ik weet nog steeds niet of ik zijn (of haar?) vazen nu zo mooi vind. Ook vond ik de wandkleden in eerste instantie niet echt speciaal. Het is toch alleen maar raar om met een roze motor door Europa te toeren met je lievelingsbeer achterop? Een huis bouwen met allemaal vreemde hoekjes en beelden, dat is toch niet normaal?
PerryMotor

Maar alles kwam in perspectief te staan, toen ik de films over zijn leven en werk had bekeken: toen kwam het besef, dat iemand probeert ons een spiegel voor te houden door zijn/haar (fantasie)wereld en leven aanschouwelijk te maken. Maar ik blijf het moeilijk vinden om het gevoel, dat de tentoonstelling bij mij opriep, onder woorden te brengen.

Dat wordt dan het bruggetje naar Excel: het gaat mij makkelijker af om (een onderdeel van) Excel uit te leggen (hoop ik!).

De tentoonstelling liet me ook weer zien, dat ik zelf dit soort kunstzinnige creativiteit mis. Maar voor mij is de Kunst van het Programmeren (was een vak op de TH-Eindhoven) ook een vorm van creativiteit is: hoe los je slim (en mooi) bepaalde problemen op.

Laten we overgaan naar Excel. Deze keer ga ik mijn belofte inlossen en iets vertellen over tabellen in Excel (en waarom we die veel meer moeten gebruiken ofwel de 10 voordelen van Tabellen).

Tabel maken

Nu de tentoonstelling van Perry de wereld overgaat (na Maastricht gaat die naar Denemarken), moet alles natuurlijk geïnventariseerd worden, inclusief de grootte van de objecten.

Kunst1In het Voorbeeldbestand in het tabblad Cat1 heb ik een begin gemaakt met de catalogus: in de kolommen staan de rubrieken die ik wil vastleggen en in de rijen komen dan alle objecten.

Van dit overzicht gaan we een Excel-tabel maken:

  1. selecteer één van de cellen in het overzicht (door er op te klikken), bijvoorbeeld cel C6
  2. kies in de menutab Invoegen binnen het blok Tabellen de optie Tabel
  3. Kunst3Excel beoordeelt zelf welke gegevens waarschijnlijk in de tabel moeten worden opgenomen. In dit geval klopt dat niet helemaal (de kopregel Vazen nemen we niet mee): $B$2 moet gewijzigd worden in $B$3. Het vinkje bij kopteksten is wel juist.

Kunst4En daar is ons nieuwe overzicht: automatisch inclusief Filters bij de koppen (voordeel 1), ‘zebra-lijntjes’ voor de leesbaarheid (voordeel 2), Kunst6een eigen menutab voor diverse instellingen Kunst5 (voordeel 3) en het gegevensgebied heeft direct een naam (hier Tabel4, te vinden in het tabblad Hulpmiddelen voor tabellen en is aanpasbaar; voordeel 4)

Tabel uitbreiden

De grootste voordelen worden pas zichtbaar als we de tabel gaan uitbreiden (zie het Voorbeeldbestand, tabblad Cat2):Kunst7

  1. de logistiek-medewerker van Perry wil niet alleen de H, L en B  van de objecten weten, maar ook hoeveel ruimte deze innemen.
    In cel G3 komt de tekst Inhoud (cm3).
    Aan de zebra-lijntjes is te zien dat Excel automatisch de tabel uitbreidt met een extra kolom (voordeel 5).
    NB selecteer de 3 achter cm, druk op Ctrl-1 (Celeigenschappen) en kies Superscript
  2. start een formule in cel G4 door het =-teken in te tikken, klik dan met de muis in cel D4, tik het *-teken, klik in E4, weer het *-teken, klik in F4 en druk dan op Enter.
    In plaats van de standaardverwijzing =D4 * E4 * F4 heeft Excel een beter leesbare formule geplaatst (voordeel 6).
  3. Nog mooier: de formule is direct naar beneden naar alle rijen gekopieerd (voordeel 7).
    Wel zelf even de opmaak regelen: kies alle cellen met de formule in kolom G en pas de lay-out aan (bijvoorbeeld via Ctrl-1).
  4. We voegen nog 2 vazen aan de lijst toe: in het tabblad Cat2 staan de gegevens daarvan. Selecteer deze gegevens, kies Kopiëren en Plak ze in B9.
    Excel zal de tabel automatisch uitbreiden (zie de zebra-lijntjes) en vult achteraan ook direct de juiste formule toe (voordeel 8).

NB de naamgeving [@Hoogte] is even wennen: de rechte haken geven aan, dat het een naam in een tabel is; door de @ ‘weet’ Excel dat hij niet zomaar een hoogte moet nemen maar die uit dezelfde rij en Hoogte is één van de kopteksten.

Analyse van tabelgegevens

Een betrokken archivaris wil vanuit het overzicht wat extra informatie ophalen (zie het Voorbeeldbestand, tabblad Cat3).
Kunst8

  1. aangezien de archivaris niets kan met cm3, heeft hij een kolom met de inhoud in liters toegevoegd door een koptekst in te tikken en de formule =[@[Inhoud (cm3)]]/1000 (ja, de archivaris kan met Excel omgaan!)
  2. Kunst9aangezien hij wil weten hoeveel werken er uit de diverse jaren komen maakt hij een draaitabel:
    * selecteer één van de cellen in het overzicht (door er op te klikken), bijvoorbeeld cel C6
    * kies in de menutab Invoegen binnen het blok Tabellen de optie Draaitabel
    * Excel kiest automatisch het juiste bereik (hier tblCat3), klik OK
    * sleep Jaar naar de Rijlabels en Omschr naar het Waarden-gebied.
  3. Kunst10nu hij toch bezig is:  misschien is het ook wel handig om te weten hoe hoog de stapel wordt wanneer de werken per jaar in het magazijn moeten worden bewaard:
    * sleep ook de Hoogte naar het Waarden-gebied

Maar wat nu als we nog meer vazen aan onze catalogus toevoegen:

  1. op het tabblad Cat3 van het Voorbeeldbestand staan nog 2 extra exemplaren: kopieer de gegevens en plak ze in cel B11.
    De tabel wordt automatisch uitgebreid en de formules achteraan worden ook naar de nieuwe regels doorgetrokken
  2. Jammer, de draaitabel is niet gewijzigd! Iedereen, die gebruik maakt van dit (verders) formidabele hulpmiddel weet, dat na het aanpassen/uitbreiden van de brongegevens de draaitabel ververst moet worden: klik met de rechter muisknop ergens in de draaitabel en kies Vernieuwen.
    Bij ‘gewone’ Excel-overzichten is dit niet voldoende; dan moet ook nog worden aangegeven dat de draaitabel naar een groter bereik moet ‘kijken’.
    Is de bron een tabel dan is alleen Vernieuwen van de draaitabel voldoende; intern weet Excel dat de tabel ‘gegroeid’ is (voordeel 9).

Verwijzingen naar tabellen

Een andere archivaris is wat minder handig met Excel en weet niet goed hoe hij met draaitabellen moet omgaan (hij vergeet altijd Vernieuwen te kiezen en komt dus met de verkeerde cijfers).

Hij maakt daarom zijn eigen ‘draaitabel’ (creatief toch?) om de totale hoogte en lengte van de vazen per jaar te krijgen:

  1. Kunst11in het Voorbeeldbestand in het tabblad Cat4 komt in cel K19 het jaar waarvan hij het overzicht wil hebben (in het voorbeeld 2000).
  2. en daaronder de formule
    =SOM.ALS(tblCat4[Jaar];K19;tblCat4[Hoogte])
    Ook hier geldt weer: als de tabel wordt uitgebreid met nieuwe vazen dan zal de formule alle nieuwe regels automatisch in het resultaat meenemen.
    NB1 gelukkig hoeft dit niet ‘met de hand’ zo ingevoerd te worden: tik het begin van de formule tot en met het haakje-openen in en selecteer met de muis de cellen B4 tot en met B12, tik de punt-komma etc.
    NB2 in dit geval staat voor de velden Jaar en Hoogte geen @, omdat niet de gegevens uit de overeenkomende regel moeten worden meegenomen maar alles uit de betreffende kolom.
    NB3 omdat de verwijzingen buiten de tabel staan wil Excel wel weten uit welke tabel de gegevens moeten worden opgehaald; vandaar de tblCat4 voor de velden.
  3. voor de lengte in cel K21 tikken we eenzelfde soort formule in (of we kopiëren de formule van K20 en dan op de juiste plaatsen wat aanpassingen)
  4. in J20 en J21 nog een tekst (Hoogte resp. Lengte) en het overzicht is klaar.

NB het voordeel van deze ‘eigen draaitabel’ is dat die bij iedere wijziging van de bron direct wordt aangepast; Vernieuwen is niet nodig.

LET OP In het Voorbeeldbestand staan in J20 en J21 geen harde teksten, maar verwijzingen naar de kopjes in de tabel: =tblCat4[[#Kopteksten];[Hoogte]].
Wanneer de kopjes wijzigen (bijvoorbeeld ipv Hoogte wordt H (cm) gebruikt) dan past het overzicht zich automatisch aan. De draaitabel laat ons dan in de steek!

Kunst12Een van de vele opties van tabellen, die je terug vindt onder Hulpmiddelen voor tabellen, is het al dan niet zichtbaar maken van een Totaalrij (voordeel 10):

  1. selecteer één van de cellen in de tabel van tabblad Cat4 (door er op te klikken), bijvoorbeeld cel C6
  2. vink in de menutab Hulpmiddelen voor tabellen binnen het blok Opties voor tabelstijlen de optie Totaalrij aan
  3. onderaan iedere kolom kan nu een bepaald soort totaal gekozen worden (Som, Aantal, Gemiddelde etcetera).

Kunst13NB naar de totalen kunnen ook weer verwijzingen gemaakt worden; in cel K23 wordt de totale hoogte opgehaald:
=tblCat4[[#Totalen];[Hoogte]]

Cadeau: dit was voordeel nummer 11!

Draaitabel opmaken

Excel, VBA (Visual Basic for Applications), ,

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.