Maandelijks archief: mei 2014

Namen ipv celverwijzingen

Bij sommige spreadsheets krijg ik het gevoel, dat de opsteller alles in het werk stelt om het zo ingewikkeld te maken, dat niemand behalve hijzelf er nog iets van begrijpt.
Verwijzingen van de ene kant van een werkblad naar de andere kant en ook nog eens over de werkbladen heen. Je blijft maar zoeken waar alle gegevens vandaan gehaald worden.
(Om eerlijk te zijn: de aanleiding voor dit artikeltje is een spreadsheet van mezelf van vorig jaar, waar ik niet zo makkelijk meer uitkwam!). 

Namen definiëren

Los van verdere structurering van je spreadsheet (waar ik in cursussen een ochtend aan besteed) is het een goede gewoonte om cellen of reeksen van cellen, die vaste waarden bevatten (zogenaamde constanten) een naam te geven.
Het grote voordeel hiervan is, dat verwijzingen veel beter leesbaar worden (voor ons menselijke wezens in ieder geval):
in plaats van een verwijzing als =Blad7!B5 * (1+Blad3!D3) zien we dan =Bedrag * (1+BTW)

Wanneer je een cel (of reeks van cellen) hebt geselecteerd, kun je op een paar verschillende manieren deze cel of het bereik een naam geven:

  1. zoek de menu-optie Formules, het blok Gedefinieerde namen en kies Naam definiëren, tik bij Naam een naam in (klinkt logisch!)
  2. Naamvakiets makkelijker: klik in het Naamvak (waar nu nog de naam van (eerste) gekozen cel staat) en tik de gewenste naam in
  3. nog makkelijker: rechts klikken en Naam definiëren kiezen

Reeks namen definiëren

ReeksNamenSoms heb je een serie cellen, die je allemaal een naam wilt geven. Dat kan dan een klus zijn, maar gelukkig heeft Bill Gates daar een mooie oplossing voor bedacht.
Wanneer je nu de cellen vanaf Doel t/m 30 selecteert, dan menu-optie Formules, het blok Gedefinieerde namen en Maken obv selectie kiest, krijg je een nieuw keuzescherm.Namen o.b.v. selectie
De bedoeling is, dat de cellen in de tweede kolom een naam krijgen zoals in de eerste kolom staat aangegeven, dus alleen het vinkje voor Linkerkolom mag aan staan.
Kies dan OK en je zult zien, dar alle cellen in de rechterkolom een naam hebben (“wandel” er overheen met de cursor en kijk in het Naamvak).
Even experimenteren met de mogelijkheden!

Namen gebruiken

Maar wat is nu het nut van Namen? Nou, bekijk onderstaand voorbeeld
(of open het Voorbeeldbestand):
Ann1

Als je weet, dat het hier een annuïteitenberekening betreft, kun je misschien nog achterhalen wat de verwijzingen allemaal doen, maar of ze naar de juiste cellen verwijzen?
Dan vind ik de formule in D8 in het volgende voorbeeld toch een stuk leesbaarder (en betrouwbaarder!):
Annuiteit

Hoe krijg je Namen in formules?

Wanneer je de diverse constanten, die je gebruikt, al namen geeft voordat je formules gaat invoeren scheelt dat een heleboel werk.

  1. Wanneer je (in bovenstaand voorbeeld) ergens intikt =-BET( en dan met de cursor op C7 in het werkblad Parameters klikt, zal automatisch de naam Rente worden ingevoegd.
    PS met de formule BET bepaal je  voor een annuïteit de periodieke betaling, die hoort bij een te lenen bedrag, een aantal termijnen en een afgesproken rente (nou ja, afgesproken? Eenzijdig bepaald!). Voor onze rekenmachine is een betaling een negatief bedrag, maar dat vind ik niet mooi, dus daarom zet ik er een – (minteken) voor.
    BET
  2. Een andere methode is: tik weer in
    =-BET(
    Je ziet dan dat de formule een rente verwacht, dus we beginnen te tikken Re en ziedaar onze eigen naam Rente staat er al bij! Even dubbelklikken (of met de cursor er naartoe en dan op de Tab-toets drukken).
    Tik  een ; (punt-komma) en tik in lo etc.
  3. je kunt ook op F3 drukken wanneer je een eigen gedefinieerde Naam wilt invoeren.

Achteraf namen gebruiken in plaats van de celverwijzingen? Dat kan nog altijd:
We gaan weer via de menu-optie Formules naar het blok Gedefinieerde namen en kiezen dan NAAST Naam definiëren het vinkje en krijgen dan de mogelijkheid om Namen te gebruiken. Je kunt dan nog aangeven welke namen je in je spreadsheet wilt laten gebruiken.
Helaas heb ik (maar ook Microsoft zelf) moeten constateren, dat het op deze manier vervangen van verwijzingen door namen niet altijd lukt. Dan zul je de formules opnieuw moeten invoeren.

Andere toepassing van Namen

Wanneer je een flinke spreadsheet hebt ontwikkeld met veel bladen en veel informatie op de diverse werkbladen dan is het handig om Namen ook als een soort bladwijzers te gebruiken.

Klik achter het Naamvak op het uitklap-symbool, kies een eigen gedefinieerde naam en  … de cursor staat ineens op een ander tabblad op een andere plaats!!

Nog even over je hypotheek

Het Voorbeeldbestand bevat een annuïteitenberekening. Deze is als voorbeeld bedoeld voor het gebruik van Namen (kijk zeker even op het werkblad Rek2).
Maar natuurlijk kun je die ook gebruiken om een indicatie te krijgen over de kosten van een hypotheek. Wanneer je gegevens op het werkblad Parameters wijzigt, zie je de resultaten in de andere werkbladen.
Kun je achterhalen bij welk rentepercentage je evenveel rente als aflossing betaalt, over de hele looptijd gerekend?
Misschien nog een idee voor een volgende Tip.


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

Tekst-cellen in Excel

Haalt u ook wel eens gegevens uit SAP of een ander systeem over naar Excel?
Hoe vaak gebeurt het dan, dat totalen ineens niet meer aansluiten?

SAP10 tegen 1 hebben de twee systemen elkaar niet helemaal begrepen en is Excel enigszins de kluts kwijt geraakt!

Niet alleen tellen de bedragen niet goed op, ook de datums zijn niet goed overgekomen.

Vervangen

Het eerste wat we gaan doen, is in ieder geval de opmaak aanpassen:

  1. selecteer alle cellen van kolom B door op de betreffende letter boven de kolom te klikken
  2. ga naar de menu-optie Start, kies Zoeken en selecteren en dan Vervangen (of iets korter: druk Ctrl-h in)
  3. bij Zoeken naar: vullen we . (een punt dus) in en bij Vervangen door:  – ( een streepje)
  4. kies Alles vervangen (zie je het streepje onder de s? Dat betekent dat je ook Alt-s kunt indrukken voor hetzelfde resultaat)
  5. selecteer alle cellen van kolom D
  6.  Ctrl-h en vervang de . (punt) door een , (komma)

Misschien is alles goed gegaan en ziet het overzicht er als volgt uit:

SAP2

De datums zijn ineens rechts uitgelijnd (een teken dat Excel de inhoud van de cel als een getal ziet; zie de blog over Datums) en ook alle bedragen.
En de optelling klopt!

Helaas wil het nog wel eens voorkomen, dat Excel zich niet zo makkelijk op het goedeSAP3 spoor laat brengen en ziet het overzicht er nog niet helemaal goed uit.

Waar het op neer komt: Excel blijft hardnekkig  denken dat de cellen D6 en D7 teksten bevatten.

 Tekst-cellen

En helaas: Excel blijft volharden!
Enkele veel gehoorde ‘oplossingen’:

  •  de cellen rechts uitlijnen
  • via celeigenschappen (Ctrl-1) de opmaak op Getal zetten

Nee, dit is allemaal louter opmaak: Excel blijft de cellen als tekst zien en zal deze bij de optelling niet meenemen. Probeer het maar eens in het Voorbeeldbestand.

Maar gelukkig: met een extra zetje laat Excel zich wel vermurwen!
Wanneer je met zo’n cel gaat rekenen (er bijvoorbeeld iets bij optellen) dan probeert Excel de tekst te vertalen in een getal.
Dus we plaatsen in E6 de formule = D6 + 0 en we krijgen het gewenste getal te zien.
Even E6 kopiëren naar E7 en ook het andere getal hebben we.

Maar de getallen moeten in kolom D komen:

  • selecteer de cellen E6 en E7
  • kies kopiëren (Ctrl-c)
  • ga naar cel D6
  • klik rechts en kies bij Plakopties het tweede symbooltje (Waarden)123
    Excel kopieert dan niet de formules uit E6 en E7, maar de ‘harde’ waarden (klinkt logisch, toch?)
  • verwijder cellen E6 en E7

Snelle oplossing

We gaan hetzelfde idee toepassen, maar dan zonder alle tussenstappen:

      1. zet ergens in een lege cel de waarde 0
      2. kopieer deze cel
      3. selecteer de cellen D6 en D7Plakken spec
      4.  rechts klikken en kies dan Speciaal Plakken…
      5. in het volgende scherm
        zet je (onder Bewerking) de optie Optellen aan
      6. klik OK

Vergeet niet de cel met de 0 weer leeg te maken!

LET OP: op deze manier wordt ook de lay-out van de cel met de 0 meegenomen. Wil je dat niet, zet dan in stap 5 ook de optie Waarden aan.

Twee alternatieven:

      1. ipv een cel met de waarde 0 (nul) kun je ook een lege cel kopiëren!
      2. kopieer een cel met de waarde 1 en kies in stap 5 niet Optellen, maar Vermenigvuldigen

Plakken speciaal/Vermenigvuldigen

Uiteraard is de optie Plakken speciaal/Vermenigvuldigen niet ontwikkeld met bovenstaande toepassing in het achterhoofd.
Maar heb je een keer een serie bedragen waar de 21% BTW nog bij moet: kopieer het getal 1,21 en vermenigvuldig op deze manier de gewenste cellen.
Of de budgetten moeten allemaal 5% omhoog (of naar beneden) of ….

Voorbeeldbestand

Probeer in het voorbeeldbestand de mogelijkheden uit.


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

Klembord

Een van de meest gebruikte onderdelen van je computer-besturingssysteem is het Klembord; alleen hebben we het meestal niet in de gaten!
Klembord   Iedere keer als we Kopiëren kiezen (of Ctrl-c natuurlijk) dan wordt de selectie op het systeem-klembord geplaatst.
Frustrerend genoeg is er maar ruimte voor 1 kopieeractie en misschien nog ergerlijker: het gekopieerde verdwijnt ook nog eens, als je voor het ‘plakken’ eerst bijvoorbeeld nog even gauw een tabblad, een rij of kolom toevoegt.
Maar gelukkig is daar iets aan te doen!

Het Office-klembord activeren

Wanneer je het Office-klembord activeert, dan krijg je ineens ruimte voor 24 kopieeracties. Wanneer deze allemaal gebruikt zijn, valt de oudste weg, maar dat brengt in de dagelijkse praktijk normaal gesproken weinig problemen met zich mee.
Waar in Office 2003 het Klembord geactiveerd kon worden door 2x achter elkaar op Ctrl-c te drukken, moeten we in de nieuwe versies het Klembord (eenmalig) activeren, voordat we een kopieeractie uitvoeren, om van de uitgebreide mogelijkheden gebruik te maken (gelukkig kun je nog van alles instellen, zodat het Klembord volgende keer direct opent; zie hieronder bij Opties).
Zolang we het Klembord niet sluiten, zal dit actief blijven.

Daar gaan we dan:

  1. start een Office-programma (het Klembord  werkt in alle Office-programma’s hetzelfde)
  2. klik zo nodig op het tabblad Start
  3. ga naar Klembord en klik op het kleine pijltje rechtsonder   Klembord
  4. daar is het Office-klembord!

Wanneer je nu iets kopieert, dan zul je zien dat dit op het Klembord komt. Wil je deze kopie ergens gebruiken: verplaats de cursor en klik in het Klembord op de betreffende tekst of plaatje.

Let op: er is verschil tussen het systeem- en het Office-Klembord.
Waar het systeem-klembord alleen de laatste kopieer- of plakactie vasthoudt kun je bij het Office-klembord 24 acties bewaren.
Wanneer je iets kopieert (of dat nu via een menu-opdracht, via ‘rechts-klikken’ of via Ctrl-c is), dan wordt deze actie op allebei de Klemborden bewaard.
Plak je iets via een menu-opdracht, via ‘rechts-klikken’of via Ctrl-v, dan komt de informatie van het systeem-klembord (dus altijd de laatst uitgevoerde actie)!
Plakken vanuit het Office-klembord kan alleen met behulp van de muis.

Even ‘spelen’ met wat voorbeeldjes en je zult versteld staan van de mogelijkheden:

  • heb je tijdens je werk regelmatig een groot woord, een lange zin, een pad naar een bepaald bestand nodig: één keer kopiëren naar het Klembord en de hele dag heb je er plezier van!
  •  soms wil je in Excel diverse cellen kopiëren, maar helaas sluiten ze niet precies op elkaar aan; er staan bijvoorbeeld kolommen tussen, die je niet mee wilt nemen.
    Dat kan natuurlijk opgelost worden door Ctrl ingedrukt te houden tijdens het selecteren, maar dat wil nog wel eens mis gaan.
    Makkelijker is het om ieder stuk apart naar het Klembord te kopiëren en met enkele muisklikken deze ergens anders te plakken.

Het Klembord wissen

In de loop van de (werk)dag  kan het klembord wat onoverzichtelijk worden. Maak het je zelf makkelijk door af en toe het klembord te wissen.   Klembord

Het Klembord minimaliseren

Klembord   Wanneer je ruimte-gebrek hebt op je beeldscherm, kun je via hetzelfde pijltje rechtsonder het klembord weghalen.
Let op: het klembord is dan niet meer actief! Items die erop staan worden wel bewaard.

Maar ik vind het handiger om in zo’n geval het klembord heel smal te maken.  Je kopieeracties blijven in ieder geval bewaard.

Klembord-opties

Als het Klembord actief is dan zie je onderaan een knop om Opties in te stellen.

Klembord-opties

Wil je, dat voortaan het klembord altijd beschikbaar is, wanneer je iets kopieert: zet de eerste 2 opties aan.

Het nut van de vierde optie ontgaat mij. Weet iemand wat daar de bedoeling van is?


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

De F2-truc

Na de uitgebreide blog van vorige week nu even een korte tip over het handig gebruik van de F2-toets in ‘vreemde’ situaties.

Het zal je tijdens je werk wel eens zijn opgevallen, dat Excel voor je ‘denkt’. Gelukkig is dit vaak in ons voordeel  (je tikt 9-5 in en Excel verandert dit automatisch in 09-mei. Het wordt dus een datum; ga opnieuw op die cel staan en je zult zien, dat Excel de inhoud heeft verandert in 09-05-2014. Dus er is een echte datum in de cel gezet EN de opmaak van de cel is veranderd!)
Maar zoals JC ooit eens heeft gezegd: elk voordeel heb zijn nadeel.
Niet altijd wordt door Excel (beter gezegd de programmeurs van deze applicatie) goed voorspeld wat jij op dat moment wilt.

Deze week zat ik bij een klant de documentatie van een nieuw opgezette Access-applicatie af te ronden en ineens hoorde ik achter me: Nou doet ie het weer! Inclusief wat krachttermen, die ik hier niet zal herhalen.

Draai1

Waar ging het om: hij had een draaitabel, waarvan hij het basis-bereik wilde vergroten ($D$36 moest $D$66 worden).

Dus hij wil m.b.v. de cursortoetsen naar de 36 ‘wandelen’ en deze aanpassen, maar Excel ‘denkt’ dat hij de celnaam, waar de cursor staat, wil invoegen.

Draai2

Maar gelukkig is er een oplossing: de magische F2-truc!
Klik in zo’n situatie op F2 en Excel staat dan in de Aanpas-modus (Edit-mode); gauw met backspace de ongewenste invoer van Excel verwijderen en met de cursor naar de gewenste plaats en wijzigen maar.

Wanneer dit precies gebeurt, is niet exact te  zeggen (tenminste niet door mij; er zal wel een logica achter zitten), maar je weet nu hoe je snel kunt ingrijpen als het weer eens zover is.


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

Standaardinstellingen Excel (VBA)

Vorige week hebben we gezien dat het handig is om bij het starten van Excel standaardinstellingen in te laden (zie Standaardinstellingen Excel).
Ik heb toen ook beloofd om de gebruikte VBA-routine toe te lichten.

Deze keer zal ik dat dan ook doen, maar wil allereerst voor diegene die nog nooit of nauwelijks met VBA  hebben gewerkt, een korte, algemene toelichting op VBA geven. Het is daardoor ook een wat uitgebreider verhaal geworden; en zeker ook voor de beginnende VBA-programmeur niet al te makkelijk!

VBA (Visual Basic for Applications)

Kortweg zou je kunnen zeggen, dat dit een programmeertaal is waarmee je handelingen, die je in Excel wilt uitvoeren, kunt automatiseren.
Wanneer je bijvoorbeeld vaak je naam en adres-gegevens in Excel moet invoeren, dan kun je dat met VBA simpel zodanig automatiseren, dat dit met ‘één druk op de knop’ wordt uitgevoerd.
Of als je regelmatig een bestand krijgt, waarin na iedere regel met gegevens een lege rij voorkomt, die eruit moet: automatiseren via VBA!
Dit soort handelingen kun je als Macro opnemen en daarna opnieuw ‘afspelen’.  De macro kun je in de VBA-editor bekijken (en daar kun je veel van leren hoe het programmeren in zijn werk gaat!) en eventueel aanpassen. Om dit verder uit te leggen gaat echter te ver voor dit blog.

Het automatiseren van handelingen (macro’s opnemen en uitvoeren) is niet het enige dat met VBA kan: je kunt ook zelf macro’s schrijven (door programmeurs sub-routines genoemd) of eigen functies bedenken, wanneer de standaard-functies van Excel niet voldoende voor je zijn.

VBA-editor

Hieronder gaan we zelf aan de slag met programmeren in VBA. Programmeren is niets anders dan het in de goede volgorde zetten van opdrachten die uitgevoerd moeten worden (klinkt simpel, toch?).
Voor die opdrachten gebruik je een soort tekstverwerker, de zogenaamde VBA-editor.  Waar vinden we de editor, is dan de volgende vraag.
Net als met zoveel zaken in Excel, kun je die op verschillende manieren vinden; vaak nog afhankelijk van de versie die je gebruikt. In de 2010-versie en hoger is deze ‘verstopt’ op het Ontwikkelaars-tabblad, dat standaard niet in de menu-balk zit.
Gelukkig werkt rechtsklikken op een tabblad onderin (en dan Programmacode weergeven kiezen) nog steeds, maar nog makkelijker: er is ook een toets-combinatie: Alt-F11 (dat doet me er aan denken om binnenkort eens een blog aan sneltoetsen te wijden!)
Dus ga naar Excel (als er geen werkmap geopend is, maak dan een nieuwe aan; onthoud even de naam van de geopende werkmap), hou de Alt-toets vast en druk op F11: en daar is de VBA-editor!VBA2

In het linker gedeelte van het scherm zie je een
overzicht van alle openstaande ‘projecten’. De editor noemt iedere werkmap, die in gebruik is, ‘project’. Maar daarnaast zijn add-ins ook projecten.
Klik op het project met de naam
van de werkmap, die je net hebt onthouden (anders even terug naar Excel (Alt-F11), kijken hoe de werkmap heet en weer naar de VBA-editor (Alt-F11)).
Macro’s/subroutines/programma’s/functies worden binnen een project weggeschreven in een Module. Standaard is die er nog niet, dus: kies boven in het menu Invoegen en dan Module.

Programma-1

Aan de slag: we gaan eerst een simpel programmaatje schrijven:

  1. maak een nieuw werkblad in Excel aan; onthoud de naam
  2. ga naar de VBA-editor mbv Alt-F11
  3. zoek het project met de naam uit stap 1 en klik daarop
  4. maak een Module aan: Invoegen/Module
  5.  in je project zie je een nieuwe module met de naam Module1; dubbelklik daarop; we krijgen dan een leeg ‘vel’ in onze ‘tekstverwerker’
  6. nu gaan we programmeren!
    Tik in: Sub ToonBericht()
    en druk op Enter
  7. als alles goed is gegaan heeft de editor een nieuwe regel toegevoegd: End Sub
  8. VBA kent nu een programma (ofwel subroutine, vandaar het woordje Sub) met de naam ToonBericht; hij weet waar dit begint (bij Sub) en waar deze routine eindigt (bij End Sub). Zo kunnen er diverse programma’s in één module komen.
  9. het programma doet nog niets. Tik daarom op de lege regel (tussen Sub en End) het volgende: MsgBox “Een bericht vanuit VBA!”, vbInformation, “Informatie-bericht” (MsgBox is het VBA-commando om een bericht weer te geven; let goed op wat er allemaal op je scherm verschijnt als je iets intikt; het eerste gedeelte achter MsgBox is de boodschap die je weer wilt laten geven; dan komt er een ‘code’ waardoor VBA weet, wat er rond het bericht moet komen (vbInformation is de vb-code voor een Information-boodschap); als derde komt de titel van het bericht)VBA3
  10. nu het programma klaar is gaan we het oproepen vanuit Excel:
    ga van VBA naar Excel: Alt-F11.
    Nu moeten we de macro aan het werk zetten; ga naar het macro-overzicht (via Alt-F8)
    klik op de macro met de naam ToonBericht en kies Uitvoeren
  11. Gelukt?

Programma-2

De techniek uit het eerste programma gebruiken we nu ook voor het ophalen van de directory waarde templates met standaardinstellingen moeten staan:

  1. ga opnieuw naar de VBA-editor mbv Alt-F11
  2. dubbelklik zonodig op Module1
  3. Tik onder het vorige programma:
    Sub ToonDir()
    MsgBox Application.StartupPath, vbInformation, “Gevonden!”
    End Sub
  4. de MsgBox zal het opstartpad van de aanroepende applicatie (Excel dus) tonen
  5. ga van VBA naar Excel: Alt-F11 en voer de macro ToonDir uit(via Alt-F8)

Programma-3

Omdat het resultaat van het vorige programma moeilijk te kopiëren is, heb ik vorige keer een routine gebruikt die het gewenste resultaat in een Excel-cel wegschrijft.
Hieronder staat het programma weergegeven:
VBA1

De groene teksten zijn geen opdrachten binnen VBA, maar is commentaar/uitleg die de programmeur toevoegt, zodat de routine beter door anderen (en later door de programmeur zelf!) te begrijpen is.
Alles wat achter een apostrof-teken (dus ‘) staat, wordt door VBA als commentaar gezien.

Hoewel het bovenstaande in eerste instantie misschien ingewikkeld overkomt, valt het (na wat oefening) heel erg mee. Door zelf macro’s op te nemen en binnen VBA de commando’s te bestuderen, ontstaat er snel inzicht in de structuur van het programmeren en de te gebruiken commando’s.

In het Voorbeeldbestand heb ik bovenstaande routines opgenomen, inclusief een beschrijving hoe een macro te starten m.b.v. een knop.
Ook heb ik daar de 2 voorbeelden van het begin (NAW-gegevens invoegen en lege regels verwijderen) uitgewerkt.


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