Categorie archief: Excel

Klembord

Access, Excel, ,

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?

De F2-truc

Excel,

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.

Standaardinstellingen Excel (VBA)

Excel, VBA (Visual Basic for Applications),

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.

Standaardinstellingen Excel

Excel, ,

Hoe gek het misschien ook mag klinken: ook bij ervaren Excel-gebruikers kunnen een paar aanpassingen aan de standaard-instellingen veel dagelijks plezier met zich meebrengen (of minstens ergernis voorkomen) .

Aantal werkbladen

Misschien heb je je wel eens afgevraagd, waarom Excel standaard bij het starten van een nieuwe werkmap aankomt met 3 werkbladen. Nou, ik ook. En ik heb geen goede reden kunnen bedenken.
Ik weet wel dat ik er meer last van heb gehad dan plezier: hoe vaak heb ik niet tevergeefs in een Excel-sheet van iemand anders zitten zoeken wat er op Blad2 en Blad3 zou staan (om eerlijk te zijn: ik heb ook vaak in mijn ‘eigen werk’ zitten zoeken!).

Weg ermee, dus:

  1. Kies in Excel bovenaan in het menu: Bestand
  2. Dan in de linkerkolom Opties
  3. en binnen deze Opties de keuze Algemeen
  4. bijna onderaan staat: Aantal op te nemen bladen:
    verander hier de standaardinstelling 3 in 1

Zoals je ziet, ga ik er van uit, dat we met Office 2010 of hoger werken. In vorige versies vind je deze keuze terug in de menucombinatie Extra/Opties.

Gebruikersnaam

Zeker in een zakelijke omgeving komt het nogal eens voor, dat je een Excel-bestand opent en dan de waarschuwing krijgt, dat LC10025 (willekeurig voorbeeld) dit bestand al geopend heeft. Tja, wie moet je nu vragen om het bestand ‘los te laten’?

Daar gaan we weer:

  1. Kies in Excel bovenaan in het menu: Bestand
  2. Dan in de linkerkolom Opties
  3. en binnen deze Opties de keuze Algemeen
  4. helemaal onderaan staat: Gebruikersnaam:
    Vul hier je naam in.
    (Zoals je ziet wordt deze naam binnen alle Office-producten gebruikt).

Templates

Waar de vorige 2 instellingen nog wel door diverse mensen gebruikt worden, zie ik het gebruik van Templates heel erg weinig terugkomen.

Misschien wel bekend van Word (en zeker van Powerpoint) worden ze binnen Excel heel weinig gebruikt.

Maar herken je het volgende: je hebt opnieuw een nuttig (natuurlijk!) Excel-overzicht gemaakt en wilt het printen en merkt (meestal te laat) dat deze net niet op 1 A4 past.
Ook wil je natuurlijk ergens je naam (als trotse maker van dit schoons) hebben staan; oja, ook nog even de naam van het bestand ergens, want dat kunnen we later ook niet meer zo goed herinneren.
Ofwel  bij iedere nieuwe sheet moeten weer (dezelfde) kop- en voetteksten gemaakt worden, de printerinstellingen verandert etc.
Hier komen templates goed van pas!

Een template is niets anders dan een basis-sheet, waarmee je verder gaat werken. Dit kan een heel simpele zijn met maar 1 printerinstelling aangepast, of een bestand met 10 werkbladen met allerlei standaardteksten en basisinformatie al vastgelegd.
De templates worden met de extensie xltx (in ‘oudere’ versies xlt) weggeschreven. Het enig verschil met een xlsx- (resp xls-) bestand is, dat bij het opslaan van een (aangepaste) template als gewoon bestand het origineel niet wordt overschreven.

Zie voor een uitgebreid voorbeeld: G-Info basis-bestand.

Standaard-templates

Maar nog veel handiger: er zijn 2 templates, die door Excel standaard gebruikt worden.
Map.xltx wordt opgeroepen als er een nieuwe werkmap wordt gemaakt (en dus ook bij het opstarten) en Blad.xltx bij het aanmaken van een nieuw werkblad.

Dus als je daar al je gewenste standaard-instellingen vastlegt (en dat mag zo ingewikkeld en zo uitgebreid zijn als je zelf wilt), dan scheelt dat bij iedere nieuwe Excel-werkmap en -werkblad veel tijd!

Wel een klein probleem: Excel verwacht wel dat deze 2 bestanden in een speciale directory staan en het is van diverse technische zaken afhankelijk  waar dat precies is.
Ik heb dan ook een kleine VBA-routine gemaakt, die dit netjes voor je uitzoekt.

Wat moet je doen:

  1. zoek het pad van de opstart-directory op door in het volgende bestand op de button te klikken: Plaats Opstartbestanden
  2. Aangezien sommige tussen-directories verborgen zijn, is de opstart-directory niet makkelijk te bereiken: schrijf het totale pad op of kopieer deze mbv Ctrl-c.
  3. download het bestand Map en bewaar deze ALS TEMPLATE in de hiervoor gevonden directory met de naam Map.xltx
  4. download het bestand Blad en bewaar deze ALS TEMPLATE in de hiervoor gevonden directory met de naam Blad.xltx
  5. sluit Excel en start opnieuw op; tik wat in in cel A1 en ook in E20. Kies printen en bekijk het resultaat.
  6. Pas de 2 xltx-bestanden naar behoefte aan en herhaal de stappen 3 en 4.

Volgende keer zal ik de gebruikte VBA-routine toelichten.

Data (datums)

Excel,

Ik wilde het in dit artikel gaan hebben over een speciaal gebruik van data in Excel. Maar al snel kreeg ik het gevoel, dat het misschien niet duidelijk zou zijn wat ik met dat woord bedoel.

Gevormd (of misvormd) door een klassieke opleiding wil ik daarmee het meervoud van datum aanduiden, maar tegenwoordig wordt het woord data (al dan niet in combinatie met big) meestal in een andere context gebruikt, namelijk om aan te geven dat we het over gegevens hebben.
Even terzijde: hoe vaak wordt het woord data, dat toch echt een meervoudsvorm is, niet in het enkelvoud gebruikt? (“De data wordt door de volgende programma’s gebruikt.”; brrr).

Dus in dit artikel hanteer ik toch maar het meervoud datums.

 Datums

Datum1

Misschien zul je nu denken, dat hier de formule-freak bezig is geweest (trouwens wel een leuke uitdaging om een formule voor C2 te bedenken!).
Nee, in principe staat in alle cellen precies hetzelfde, namelijk het getal 41748:

Datum2

Tip: wanneer je wilt weten welke formules er gebruikt worden in een spreadsheet, schakel dan over naar het formule-scherm. Dit kan via Formules/Formules weergeven, maar veel gemakkelijker is natuurlijk de toetscombinatie Ctrl-T.

Blijkbaar is een datum voor Excel niets anders dan een getal (om precies te zijn geeft het getal aan hoeveel dagen we ondertussen verwijderd zijn van 1 jan 1900). Daarom is het ook zo makkelijk om te achterhalen hoeveel dagen er tussen twee data, sorry datums, zitten: trek de 2 datums/getallen gewoon van elkaar af! Wil je weten hoeveel dagen er sinds je geboorte zijn verstreken:

Datum3

Tip: wil je ergens in Excel de datum van vandaag hebben, gebruik dan de functie =VANDAAG(); denk aan de haakjes! Iedere dag zal hier dus iets anders komen; wil je de datum “hard” invoeren, gebruik dan ctrl-; (dus de Ctrl-toets inhouden en op de punt-komma drukken).

Maar weer terug naar het voorbeeld: als in alle cellen dezelfde waarde staat, waarom zien we dan iets heel anders?
Precies, we zien iets anders: het is een kwestie van opmaak!

Datum-opmaak

Om hier wat duidelijkheid te geven over de mogelijkheden gaan we het voorbeeld stap voor stap opbouwen:

  • in cel B2 tikken we in: 19-4
    Wat is Excel toch “slim”, we zien dat er 19-apr komt te staan: hij snapt dat we een datum bedoelen! Als je de inhoud van de cel bekijkt (19-04-2014) zie je dat er ook nog een jaar aan is toegevoegd (namelijk het huidige).
    Het is nog vreemder: als je op B2 klikt, zie je dus 19-04-2014 staan, maar dat is niet de echte Excel-waarde, dat is  41748 (zie hiervoor).
  • we gaan de opmaak van cel B2 veranderen:
    Klik op cel B2 en kies de Celeigenschappen (rechtsklikken of Ctrl-1),
    Kies als Categorie: Datum.
    Kies dan als Type: 14-03-01
    Klik OK en je zult in B2 onze notatie zien.
  • Nog even terug naar de Celeigenschappen (Ctrl-1 dus):
    Kies als Categorie: Aangepast.
    Bij Type zie je nu: d-mm-jj
    Hier zit de crux van dit artikel: met de combinatie van de letters d, m en j kun je heel veel opmaakkwesties oplossen!
  • In cel C2 komt dezelfde waarde door in te tikken: =B2
    Celeigenschappen (Ctrl-1), Categorie: Aangepast
    Wis alles bij Type (wat staat er nu bij Voorbeeld?) en tik 1 d in, kijk naar Voorbeeld.
    Nog een d (niks veranderd?), nog een d (hé, wat is dat nu?) en nog een d.
  • We krijgen de smaak te pakken (?): in cel D2 komt =C2
    Ctrl-1, Categorie: Aangepast, alles bij Type wissen.
    1 m, nog een m, nog een m, nog een m, nog een m, nog een m, nog …..
  • cel E2 moet nu lukken.

Tip: op deze manier zijn allerlei combinaties in te voeren
(bijvoorbeeld dddd, “de” d”e” mmmm “van het jaar” jjjj).

Voorbeeldbestand

Kijk ook in het voorbeeldbestand en schroom niet om allerlei lettercombinaties uit te proberen.

Let op: we hebben alleen de opmaak van de cellen aangepast! Zoeken (Ctrl-F) naar bijvoorbeeld zaterdag zal in eerste instantie niets opleveren; pas als je in het zoekscherm aangeeft dat hij naar Waarden moet zoeken (niet Formules) zal Excel iets kunnen vinden. Ook sorteren of voorwaardelijke opmaak (om bijvoorbeeld het weekend een andere kleur te geven, zullen niet (direct) werken (in het downloadbestand is een voorbeeld van voorwaardelijke opmaak opgenomen).