Excel tips

  • 02/03/2007

Inleiding

"Wat de meeste Excelgebruikers nog niet wisten… En, ik meen het."

Hier volgen een aantal tips om direct toe te passen in Excel. Misschien ken je er al enkele van (dan pleit dat voor jou!). Anders nodig ik je uit ze eens uit te proberen. Vaak zijn ze handig, tijdsbesparend of gewoon leuk. Ik voeg er regelmatig een aantal bij. (Ze werken allemaal in Excel 2003. Ik heb ze niet voor andere versies getest, maar normaal gezien werken ze ook daar.)

Excel tips

  1. Via Data > Sorteren kan je rijen sorteren op 3 kolommen, zowel aflopend als oplopend. Wist je dat je ook kolommen kan sorteren? Bij Data > Sorteren kies je onderaan Opties… en vervolgens Van links naar rechts sorteren.
  2. Schrijf nooit zoiets als: =SOM(A1-B1). Dit is voldoende: =A1-B1. De reden waarom je toch de juiste uitkomst krijgt, is omdat je bij de eerste (foute) functie, niets optelt bij A1-B1. De andere argumenten van de SOM functie zijn nl. weggelaten, en dus tel je 0 op. Het geeft hetzelfde resultaat, maar is minder efficiënt. Hetzelfde geldt voor: =SOM(D2/A1), =PRODUCT(D2/A1) of nog talloze andere varianten.
  3. Een standaardgrafiek maken van traag naar snel: Selecteer je gegevens. Traagste methode: kies Invoegen > grafiek. Minder traag: klik op het icoon Wizard grafieken in de Standaard werkbalk. Snelste methode: druk F11.
  4. Deze is grappig: voer de volgende VBA-code uit in een bestandje. Code afkomstig van Dave Hawley. 
    Sub Spreken()
    
	CreateObject("SAPI.SpVoice").Speak "How are you? I am Stephen Hawking."
		
    End Sub
  5. Drink je een biertje tijdens het Excellen en heb je er geen plaats voor op een overvol bureau? Deze code maakt er zeker plaats voor: 
    Declare Sub mciSendStringA Lib "winmm.dll" (_
	ByVal lpstrCommand As String, _
	ByVal lpstrReturnString As Any, _
	ByVal uReturnLength As Long, _
	ByVal hWndCallback As Long)
	
    Sub biertje()
    
	mciSendStringA "Set CDAudio Door Open", 0&, 0, 0
	
    End Sub
  6. Moet je veel gegevens horizontaal inbrengen in Excel? Ga naar Extra > Opties > Bewerken en zet de Richting bij Selectie verplaatsen na Enter op rechts.
  7. Stel: je hebt gegevens staan in kolom B, en in kolom A wil je een oplopende nummering toepassen. Typ dan een 1 in de eerste cel, een 2 in de tweede cel, selecteer beide cellen en dubbelklik op de vulgreep van de tweede cel. De vulgreep bevindt zich in de rechterbenedenhoek en verandert in een kruis als je het met de muis aanwijst.
  8. Wist je dat je de Autosom ook kan gebruiken voor elke functie die je zelf wil? Klik op de kleine neerwaartse driehoek naast Autosom in de Standaard werkbalk. Autosom heeft als pictogram de Griekse hoofdletter S(igma).
  9. De tabjes van de tabbladen onderaan kan je een kleur meegeven. Ga naar Opmaak > Blad > Tabkleur… of klik met de rechtermuisknop op de bladtab en kies Tabkleur…
  10. Wil je een element zoals een knop of een checkbox mooi uitlijnen ten opzichte van een cel / cellen? Houd dan tijdens het tekenen van de contouren van het element Alt ingedrukt.
  11. Voor zij die met een scrollmuis (IntelliMouse) werken: je kan de zoom van het tabblad vergroten of verkleinen door Ctrl in te drukken en het muiswiel naar boven of beneden te scrollen.
  12. Heb je een aantal rijen verborgen in je bestand - bv. door een Autofilter - en wil je toch totalen maken? Of andere berekeningen met enkel zichtbare rijen? Gebruik de SUBTOTAAL functie met het passende argument.
  13. Wil je alle bestanden in je Excel applicatie tegelijk opslaan of sluiten? Houd Shift ingedrukt en kies dan Bestand > Opslaan of Bestand > Sluiten.
  14. Belangrijke sneltoetscombinaties: kopiëren = Ctrl + C, knippen = Ctrl + X, plakken = Ctrl + V. Alternatieven: kopiëren = Ctrl + Insert, knippen = Shift + Delete, plakken = Shift + Insert.
  15. Is je bestand onredelijk groot, bv. 5 MB voor enkel wat simpele formules? Druk Ctrl + End om tot bij de laatste cel te komen. Is die ver verwijderd van de cellen die je eigenlijk gebruikt, verwijder dan tussenliggende kolommen en rijen en sla het bestand op.
  16. Bij het programmeren in VBA, is het heel belangrijk dat alle variabelen gedeclareerd zijn, en dan nog goed gedeclareerd ook! Een veelgemaakte fout is zoiets als: 
    Sub SlechteDeclaraties()
    
	Dim dtoh1, dtoh2, dtoh3, dtoh4 As Date
	Dim s1, s2, s3, s4, s5 As String
	
    End Sub
    Hier is dtoh4 gedeclareerd als Date, s5 als String, en AL de andere variabelen als Variant (want er staat niets achter). De Date en String types gelden ENKEL voor de laatste variabelen, en zijn niet toepasbaar op de eerdere variabelen uit het rijtje. Je moet voor gewone variabelen dus ALTIJD schrijven: Dim [naam van de variabele] As [Type van variabele].
  17. Snel simpele statistieken berekenen van bepaalde cellen? Selecteer die cellen en kijk onderaan rechts in de statusbalk. Je krijgt bv. automatisch de som van de geselecteerde cellen. Klik met de rechtermuisknop in de statusbalk om som te veranderen in Gemiddelde, Aantal, Aantal nrs, Max of Min.
  18. Je kan een module in VBA invoegen door in de Project Explorer een rechtermuisklik te doen op het juiste bestand, vervolgens Import File&hellips; te kiezen en dan de navigeren naar het .bas bestand. Snellere methode is vanuit Windows Verkenner het .bas bestand te slepen naar het gewenste bestand in de Project Explorer.
  19. Snel door een groot bereik navigeren met de pijltjestoetsen? Houd Ctrl ingedrukt terwijl je erop drukt. Zo kan je naar de laatste niet-lege cel in je rij of kolom gaan. Of naar de eerstvolgende niet-lege cel in een rij of kolom.
  20. Switch tussen absolute en relatieve celverwijzingen met F4: klik in de formulebalk het bereik aan, en druk enkele malen op F4. Er zijn 4 verschillende mogelijkheden.
  21. Wil je in een cel niet 1 maar meerdere regels typen: doe dan Alt + Enter na elke regel als er nog een volgt.
  22. In een keer alle objecten in een werkblad (zoals pijlen, grafieken, Autovormen, afbeeldingen, …) selecteren? Druk F5, kies vervolgens Speciaal… (onderaan op het schermpje) en daarna kies je Objecten. Heel handig als je veel objecten tegelijkertijd wilt verwijderen.
  23. Volgens Excel is er in elke maand een dag 0: de laatste dag van vorige maand vind je met: =DATUM(JAAR(VANDAAG());MAAND(VANDAAG());0) Korter is natuurlijk =DATUM(2007;3;0) voor de laatste dag van februari. Maar dit laatste is uiteraard minder flexibel. De laatste dag van de huidige maand vind je via =DATUM(JAAR(VANDAAG());MAAND(VANDAAG())+1;0)
  24. Opslaan Als… doe je snel met functietoets F12.
  25. Het komt al eens voor dat men een cel leeg maakt met een spatie. Dit stuurt andere formules in de war en is af te raden. Één keer op Del(ete) drukken is genoeg.
  26. Het laatste getal in een kolom (rij is analoog), vind je met: =ZOEKEN(9,99999999999999E+307E+307;A:A)
  27. De laatste tekst in een kolom (rij is analoog), vind je met: =ZOEKEN(HERHALING("z";255);A:A)
  28. (De cel met) Het laatste getal OF de laatste tekst in een kolom (rij is analoog), vind je hier.
  29. Meerdere acties uitvoeren op dezelfde cellen maar in verschillende tabbladen? Houd Ctrl ingedrukt en klik de juiste tabbladen onderaan aan. Doe de acties een keer op het tabblad dat je ziet. Excel doet ze na op de andere bladen. Deselecteer de bladen nadien.
  30. Voor de programmeurs onder ons! Gemiddeld enkele keren per jaar 'verkloot' ik wel mijn VBE. Alle vensters zijn ofwel weg, ofwel krijg ik ze met geen moeite van de wereld terug gedockt (docking wil zeggen: het vast naast mekaar en boven mekaar plaatsen van vensters). Om dit te verhelpen, moet je naar het Register gaan. Je komt in het Register via Start > Uitvoeren, dan typ je regedit, en dan kies je OK. Je gaat dan een bepaalde key verwijderen (in het Engels hier): My Computer \ HKEY_CURRENT_USER \ Software \ Microsoft \ VBA \ 6.0 \ Common. Start opnieuw Excel en VBA op, en er zal een nieuwe ingang in het Register aangemaakt worden. Met de originele instellingen.
  31. Je eigen sneltoetscombinatie maken? Via Extra > Macro… > Nieuwe macro opnemen, neem je een nieuwe macro op. In hetzelfde dialoogvenster kan je via Opties een sneltoetscombinatie opgeven.
  32. Een keuzelijst in een cel maken kan je via Data > Validatie > Lijst.
  33. Wil je dezelfde actie uitvoeren in meerdere tabbladen? Selecteer dan eerst de tabbladen met Shift en/of Ctrl. Dan voer je de acties uit op het actieve tabblad. Vergeet niet achteraf de selectie van de tabbladen ongedaan te maken, anders zal je blijven werken in alle geselecteerde tabbladen (bvb. kolommen verwijderen gebeurt dan in meer dan 1 tabblad)
  34. Zoek je in een handomdraai alle tekstcellen op je tabblad? Ziehier: 
    Sub CellenMetTekst()
    
	Dim rToEvaluate As Range
	Dim rSmaller As Range
	Dim rTemp As Range

	Set rToEvaluate = Cells
	'OR: Columns(2), Rows(5), ActiveSheet.UsedRange, ...

	Set rSmaller = Application.Intersect(rToEvaluate, ActiveSheet.UsedRange)

	On Error Resume Next
	Set rTemp = rSmaller.SpecialCells(xlCellTypeFormulas, 2)
	Set rTemp = Application.Union(rTemp, rSmaller.SpecialCells(xlCellTypeConstants, 2))
	On Error GoTo 0

	MsgBox rTemp.Address
	
    End Sub
    In commentaar staat dat je deze methode ook kan toepassen op een kolom of een rij of nog veel meer; laat je verbeelding maar spreken.

Homepage

Rubriek onderdelen

Over Wim

Wim Gielis is Business Intelligence consultant en Excel expert

Andere links