Archieven

Recent

Excel

Externe links vinden in Excel

Soms krijg je bij het openen van een Excel werkboek de vraag of externe links moeten bijgewerkt worden. Dikwijls echter wéét je niet eens of er externe links zijn – die kunnen het resultaat zijn van het aanklikken van een cel in een blad dat openstaat in een ander venster, en dat onbedoeld een ander werkblad is.

Het is makkelijk die terug te vinden: zoeken naar een formule met .xl

Ctrl+F, Zoeken naar, .xl – Zoeken in, Formules.

Meer details in https://support.office.com/en-us/article/Find-links-external-references-in-a-workbook-fcbf4576-3aab-4029-ba25-54313a532ff1

Excel insluiten in Onenote

OneNote is een schitterende tool om notities en allerlei gegevens bij te houden. Wist je dat je er ook een Excel bestand kan in insluiten? Dat gaat zo:

  1. Invoegen, Werkblad, Nieuw Excel werkblad
  2. Excel start op.
  3. Maak je gegevens op, maak je berekeningen enz.
  4. Sluit Excel. Zeg JA als Excel vraagt of je wil opslaan

Om ingesloten gegevens te wijzigen / bewerken:

  1. Dubbelklik op het Excel icoon van het ingesloten bestand.
    Of klik op BEWERKEN linksboven op de tabel.
    Dubbelklik niet in de tabel – dat gaat een tekstvak maken dat over de tabel zal komen.
  2. Sluit Excel. Zeg OPSLAAN als Excel vraagt of je wil opslaan

Statistische analyse in Excel

Excel bevat een indrukwekkende reeks statistische tools. Het beste startpunt is Analysis Toolpak dat je moet activeren in de opties van Excel, onder add-ins.

Als je een set van gegevens hebt, kan je Data analysis uitgeven van onder Data, Data Analysis. Daarin staat Descriptieve statistiek:

Je stelt in welke gegevens je wil analyseren en welke opties je wil gebruiken

en je krijgt een mooi resultaat:

Bron van draaitabel achterhalen

V. Als ik een draaitabel heb gemaakt, kan ik nog zien welke de basisgegevens zijn? Ik zie wel de namen van de kolommen, maar niet waar die kolommen dan staan.
A. Klik op de draaitabel, dan Analyseren, Andere gegevensbron, Andere gegevensbron.

 

Je zal zien waarop je bestaande draaitabel gebaseerd is.

Het x-grootste getal vinden

V. Hoe kan ik het x-grootste getal vinden in Excel?

A. Met de functie GROOTSTE. Daarnaast is er ook de functie RANG, die weergeeft welke rang een waarde heeft in een reeks. Bijvoorbeeld 200 willekeurige getallen in de A kolom, en

  • dan vind je met RANG de hoeveelste (hoe grootste) een bepaalde waarde is
  • en met GROOTSTE vind je de x-grootste waarde

Zin om te spelen? dat kan hier.

Enquêtes verwerken: reeksen van antwoorden

Soms gaat een reeks van antwoorden gebruikt worden voor verschillende vragen. Dat is bv. het geval met waardeschalen. De eenvoudigste manier om die te verwerken, is tellen met AANTAL.ALS.

Nadeel is dat je geen draaitabellen kan gebruiken om antwoorden op verschillende vragen te combineren. Dat is op te lossen door de antwoorden op twee vragen te combineren met &:

en die te tellen met AANTAL.ALS waarbij je het criterium van AANTAL.ALS ook samenvoegt met &:

Excel: leeftijd in jaar en maand

V. Kan ik de leeftijd laten berekenen / weergeven in jaren en maanden?

A. Ja, dat kan.

  • Als de geboortedatum in A1 staat, kan je de leeftijd berekenen met =INT((TODAY()-A1)/365,25)
  • Je kan jaren en maanden hebben met
    =TODAY()-A1
    opgemaakt als j “j” m “m” in celeigenschappen, aangepast.

    Maar… Excel begint de maanden te tellen vanaf 1 (tot 12), en niet vanaf 0 tot 11.
  • Als dat een probleem is, kan je als volgt berekenen:
    =INT((TODAY()-A1)/365,25) & “j ” & MONTH(TODAY()-A1)-1 & “m”

Antwoorden op enquêtes omzetten in cijfers en grafieken

V. Ik heb een aantal antwoorden op een set van vragen. Hoe krijg ik daar grafieken uit?

A. Zo kan je dat doen:

Optellen over werkbladen heen

Een Exceltip: als je een aantal werkbladen hebt met dezelfde structuur (waar hetzelfde gegeven op die verschillende bladen in dezelfde cel hebt staan), kan je transversaal rekenen – je kan een som of gemiddelde berekenen. Bijvoorbeeld: als ik drie opeenvolgende bladen heb met op A1 een waarde dei me interesseert, kan ik die optellen met =SOM(Blad1:Blad3!A1)

Meer weten? Lees bv.

How to Sum Across Worksheets in Microsoft Excel
Sum Across Multiple Sheets in Microsoft Excel
Totaling Across Worksheets

Jokertekens om te zoeken of te filteren in Excel

Een Excel tip: je kan jokertekens gebruiken om gegevens (strings of, om het niet technisch te zeggen, woorden) te zoeken. Daarbij staat * voor “gelijk wat” en ? voor “één letter (gelijke welke letter)”. Bijvoorbeeld:

Filteren met a?a en a* geeft respectievelijk:

Je kan daarmee ook zoeken

Verder moet je nog weten dat je met ~* naar een ster kan zoeken, en met ~? naar een vraagteken.

Meer weten? Lees dan

The 3 Best Wildcards in Excel:  Asteriks (*), Question Mark (?), and Tilde (~)
Find or replace text and numbers on a worksheet
Guide to Using Wildcards in Excel Worksheet Functions

 

Gelijke cellen laten oplichten

V. Kan Excel gelijke cellen in twee verschillende kolommen laten oplichten? Ik bedoel: als de waarde in de tweede kolom gelijk is aan de waarde in de eerste kolom?

A. Ja, dat kan met voorwaardelijke opmaak, maar er is een trucje.

  • Selecteer de eerste cel van de tweede kolom (de eerste cel die moet oplichten)
  • Start tab, Voorwaardelijke opmaak, Nieuwe regel
  • Cellen opmaken met, Celwaarde gelijk aan
  • Tik =A2 (de truc is: géén “”, géén dollartekens)
      
  • Stel de opmaak in
  • Kopieer de opmaak naar beneden

 

Grafiek maken voor zwart/wit afdrukken

V. Ik heb grafieken in een tekst opgenomen die gekopieerd is, maar die grafieken zijn niet te te lezen. Is dat op te lossen?

A. Je kan een grafiek voorbereiden voor gebruik in zwart/wit afdrukken.

Het was eenvoudiger in Excel 2010. Zie https://www.ablebits.com/office-addins-blog/2012/03/28/excel-charts-tips/

In Excel 2013 en 2016 doe je dat zo.

  • Maak de grafiek
  • Rechtsklik op een regio / segment en kies Opvulling, Bitmappatronen, Meer bitmappatronen. Kies rechts voor Patroonopvulling
     
  • TIP. In het geval van cirkelgrafieken, zorg ervoor dat de labels duidelijk vermeld zijn. Voeg Gegevenslabels toe (via +), kies rechts voor Categorienaam en Labellijnen toevoegen

 

 

 

Examen genereren uit vragenlijst

V. Hoe zou ik een examen kunnen laten genereren uit een (Access) databank? Bv. drie vragen van een bepaalde categorie, twee van een andere enz. Ik denk dat het met Access best zou gaan.

A. Neen, Access is niet echt geschikt. Met Access kan je een lijst maken, maar niet een willekeurige selectie genereren. Ik zou dit doen, met Word en Excel:

  • Maak een lijst van alle vraagcategorieën die je nodig hebt / wil gebruiken. Noteer die, en maak een code voor elk.
  • Stel je examenvragen op. Selecteer elk van de vragen, en druk Alt+F3 om een tekstfragment te maken. Begin de naam van elk van die fragmenten met de passende code van de vorige stap.
  • Lijst alle tekstfragmenten op – daarvoor kan je gebruikmaken van de code voorgetseld in Stackoverflow. Ik zou die code zo aanpassen en plakken in een nieuw te maken module (via Alt+F11):
    Sub ListBuildingBlocks()
    Dim oTemplate As Template
    Dim oBuildingBlock As BuildingBlock
    Dim i As Integer
    For Each oTemplate In Application.Templates
      For i = 1 To oTemplate.BuildingBlockEntries.Count
      Set oBuildingBlock = oTemplate.BuildingBlockEntries.item(i)
      Debug.Print oBuildingBlock.Name>
      Next
    Next
    End Sub

    en die code uitvoeren in het immediate window. Het resultaat is de lijst van alle beschikbare tekstfragmenten.

  • In Excel twee kolommen maken: een eerste met de namen van de tekstfragmenten, en een tweede met een willekeurig getal (=RAND()).
  • Filter op de tekstfragmenten – met behulp van de code waarmee elke fragmentnaam start.
  • Sorteer op het willekeurig getal en neem er zoveel je nodig hebt – bv. vier voor die ene categorie waar je nu mee bezig bent.
  • Selecteer de namen van de fragmenten, en plak in Word.
  • Druk op F3 na elk van de namen van de fragmenten
  • En je hebt je examen. Je hebt kunnen gebruikmaken van alle opmaakmogelijkheden van Word (met name enters en inspringen maar ook afbeeldingen en lijsten of tabellen, wat niet zo maar kan in Excel of Access) en Excel heeft voor de selectie gezorgd.

Tweede draaitabel toevoegen op zelfde tabblad

V. Kan ik een tweede draaitabel toevoegen op eenzelfde tabblad?

A. Ja.

  • Maak de eerste draaitabel op een nieuw tabblad.
  • Maak de tweede draaitabel. Kies voor Bestaand werkblad, en kies in een volgende stap voor het tabblad waar je het eerste draaitabel hebt geplaatst.
  • Kies een cel twee rijen onder de eerste draaitabel, en bevestig.

Draaitabel verwijderen

V. Kan ik een draaitabel verwijderen uit een werkboek? Ik zie dat nergens staan…

A. Dit is de standaardmanier:

Delete a PivotTable – https://support.office.com/en-us/article/Delete-a-PivotTable-1de9b894-9178-43b3-b436-92e3ddb9175b
Maar helaas werkt dat zo niet. Dit is wat je kan doen:

  • de gehele draaitabel selecteren en op DEL drukken – maar daarmee zal ze niet weg zijn
  • het tabblad deleten waar de draaitabel op staat – als er op dat tabblad niets anders staat
  • de rijen selecteren waar de draaitabel op staat, en die rijen verwijderen
ip-location