Archieven

Excel

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

Draaitabel per maand

V. Hoe kan ik een draaitabel maken per maand?
A. Begin met een blad met de gegevens, met daarin een kolom datum naast de andere kolommen (in dit voorbeeld: bedrag en rubriek). Bij het maken van de draaitabel krijg je dit te zien:

Je ziet dat Excel de datums (ik gebruik even dit woord, omdat data dubbelzinnig is) groepeert per Kwartaal.

Dat is niet wat je wil: je wil het per maand. Rechtsklik en kies Groeperen.

Dan kan je met Ctrl+klik (meervoudig selecteren) de kwartalen uitselecteren.

Dan krijg je dit:

Maar mogelijk is dat ook niet wat je wil. Als je in de voorgaande stap ook de jaren wegselecteert, krijg je dit:

TEXT functie in Excel

V. Met de TEXT functie in Excel kan ik een datum omzetten in een weekdag of een maand. Maar als ik TEXT intik, wordt dat TEKST en krijg ik een foutmelding. Wat kan ik doen?

A.Wat je ziet, is het gevolg van autocorrectie. De eenvoudigste oplossing is om te dubbelklikken op het TEXT element in de uitvouwlijst die verschijnt tijdens het typen.

Alternatieven:

  • met F2 de functie bewerken en TEKST terug wijzigen in TEXT
  • of Excel in het Nederlands zetten via File, Options, Language – DutchSet as default

En: je kan de weekdag weergeven/berekenen met =TEXT(datum,”dddd”), de maand met =TEXT(datum,”mmmm”).

Van etiketten naar tabel

V. Ik heb hier een document ‘geërfd’ om etiketten af te drukken. Kan ik dat terug in Excel krijgen zonder alles over te tikken?

A. Dat Word document bevat de etiketten in de vorm van een tabel. Je kan dat relatief eenvoudig omzetten in een Excel werkblad.

  1. Kies een teken dat nergens in de adressen voorkomt. Bijvoorbeeld puntkomma of ster.
  2. Ctrl+H (zoeken en vervangen), ^p (enter), het teken dat je koos in 1
  3. Selecteer de hele tabel met het knopje linksboven. Ga naar Indeling, Converteren naar tekst. Kies voor alineamarkeringen
     
  4.  Nu omgekeerd: terug naar een tabel. Je kan dit doen in Word: tekst selecteren, Invoegen, tabel, Tekst naar tabel converteren. Kies als lijstscheidingsteken het symbool van 1. Of in Excel: plakken, selecteren, Gegevens, Tekst naar kolommen. Zeg dat je tekst Gescheiden is, en geef het teken op van in de eerste stap.

Voorwaardelijke opmaak van een grafiek

V. Ik weet hoe ik voorwaardelijke opmaak van cellen kan doen in Excel. Maar kan ik dat ook doen met grafieken?

A. Ja, dat kan. Het mees voor de hand liggend zijn kolommen – en de oplossing is het maken van een gestapelde kolommen. Bijvoorbeeld door het uitlichten van minimum en maximum, of het laten aanduiden van waarden onder een bepaalde waarde en boven een andere bepaalde waarde.

 

Dat gaat zo: een kolom met de waarden die in de grafiek moeten worden opgenomen, een kolom (of enkele kolommen) met de waarde die moet worden uitgelicht, en een kolom met de restwaarde.

Bijvoorbeeld: minimum en maximum uitlichten.

  • een kolom met de waarden
  • een kolom voor het minimum
    =ALS(A2=MIN(A:A);MIN(A:A);0)
  • een kolom voor het maximum
    =ALS(A2=MAX(A:A);MAX(A2);0)
  • en een kolom met de restwaarde voor de grafiek – als er geen minimum of maximum is
    =A2-(B2+C2)
  • dat ziet er zo uit – en dan maak je een gestapelde kolomgrafiek

Bijvoorbeeld: het laten markeren van waarden onder een bepaalde waarde, en andere boven een bepaalde waarde.

  • een kolom met de waarden
  • een plaats waar de minimumgrens staat en een waar de maximumgrens staat
  • een kolom met de waarden onder de ondergrens
    =ALS(A2<$H$1;A2;0)
  • een kolom met de waarden boven de bovengrens
    =ALS(A2>$H$2;A2;0)
  • en een kolom met de restwaarde voor de grafiek – de waarden tussen de ondergrens en de bovengrens
    =A2-(B2+C2)

Interesse? Dit is het uitgewerkte voorbeeld, met randomgetallen voor de waarden.

Meer weten?

 

Trendlijn met volle lijn

V. Als ik een trendlijn toevoeg aan een grafiek in Excel krijg ik een stippellijn. Kan ik daar geen volle lijn van maken?

A. Jawel.

  • Voeg een trendlijn toe aan de grafiek vie +, Trendlijn. Kies voor “Meer opties”
  • Ga naar Type streepje en kies naar de lijn die je wil gebruiken

    Je
    kan zo overigens ook een pijl toevoegen, als je dat zou willen.

Reeks van gelijke op opeenvolgende getallen in Excel

Excel kan voor jou een reeks maken vertrekkende van één of twee getallen die je opgeeft.

  • Plaats het eerste getal (of de eerste twee getallen) in een cel (of cellen), selecteer en trek naar beneden
  • Excel zal die éne cel kopiëren, of de reeks uitbreiden door verder te rekenen vertrekkende van die twee eerst opgegeven getallen.
  • Niet het gewenste effect? Druk op de knop die verschijnt vlak na het kopiëren en kies voor Cellen kopiëren / Copy cells om kopijen te verkrijgen, of Reeks doorvoeren / Fill series
    xl-contextcopynl xl-contextcopy
  • Je kan op die manier ook opmaak kopiëren (Alleen opmaak doorvoeren / Fill formatting only) of een kolom aanvullen (Flash-opvulling / Flash fill)

Hele kolom aanvullen

Als je wil, kan Excel voor jou een reeks van getallen aanvullen.
flashfill1

Ga in de reeks staan die moet worden aangevuld, en druk op Gegevens, Flash fill

flashfill2

Je kan hetzelfde effect bereiken met de sneltoets Ctrl+E

Tijd of datum in Excel

V. Ik heb een cel met daarin een tijd en datum. Is het mogelijk om alleen de tijd of alleen de datum te gebruiken of te tonen.

A. Om alleen de tijd of alleen de datum te tonen kan de celeigenschappen instellen. “Tijd” en “korte datumnotatie” / “lange datumnotatie” gaan alleen dat deel van de informatie tonen.

xl-tijdofdatum

Om alleen de datum te behouden gebruik je de functie INTEGER, duie laat vallen wat na de komma staat (de uren)
=INT(datum)
=INTEGER(datul)

Om alleen de tijd te behouden gebruik je de functie REST
=MOD(datum;1)
=REST(datum;1)

Zie ook: Datum en tijd uit een cel abstraheren met Excel – http://www.bevio.nl/2013/03/datum-en-tijd-uit-een-cel-abstraheren/

Tabelmodellen in Word

V. Als ik in Word een tabel invoeg, krijg ik keuze uit een aantal modellen. Kan ik daar tabellen aan toevoegen?

tabeltemplate1

A. Ja hoor. Maak een tabel die je als model wil opslaan. Selecteer ze, en druk op alt+f3 (autotekst fragment maken). Geef een naam, en sla op in reeks Tabellen.

tabeltemplate2

Zie ook: Word 2013: Save Table Templates for Quicker Access – http://www.tech-recipes.com/rx/43847/word-2013-save-table-templates-for-quicker-access/

In Excel kaarten maken met regionale verdeling van gegevens

Je kan met behulp van Excel een grafische voorstelling maken van gegevens.

Speciaal plakken in Excel

V. Is het mogelijk om iets anders te doen dan Ctrtl+V om te plakken in Excel? Dikwijls komt er opmaak mee die ik niet wil, en soms wil ik alleen de opmaak (en niet de inhoud) plakken.

A. Ja.

  • Je kan de opmaak kopiëren/plakken met de borstel. Ga op de cel staan met de bedoelde opmaak. Klik op de borstel. Klik nu op de cel die die opmaak moet overnemen. Klaar!
  • Maar je kan nog véél meer doen met speciaal plakken
    xl-speciaalplakken
    Van links naar rechts en van onder naar boven kan je plakken:

    • (1) alles (‘gewoon’ plakken)
    • alleen de formules
    • alleen de opmaak van formules en getallen
    • de opmaak van de bron behouden
    • (2) zonder randen
    • met behoud van de breedte van de bronkolom
    • transponeren (rijen en kolommen wisselen)
    • (3) alleen de waarden
    • opmaak van waarden en getallen
    • opmaak van waarden en bronnen
    • (4) alleen de opmaak
    • koppeling plakken
    • afbeelding plakken
    • gekoppelde afbeelding

Voor meer details verwijzen we graag naar: Opmaak kopiëren/plakken gebruiken  – https://support.office.com/nl-nl/article/Opmaak-kopiëren-plakken-gebruiken-4bb415a9-d4e4-42b7-b579-170adc594e40

Spreadsheet hergebruiken

V. Is het mogelijk om een spreadsheet die ik gemaakt heb, te hergebruiken voor een volgend project? Met lege gegevens wel te verstaan. Tot nu toe heb ik een bestand alleen-lezen gemaakt en dat steeds gekopieerd, maar die kopij is natuurlijk ook alleen-lezen.

A. Het kan veel eenvoudiger: sla de spreadsheet waarvan je wil vertrekken, op als sjabloon (template).

xl-templatemaken

Dubbelklikken op die template zal een nieuwe, lege spreadsheet maken op basis van die template (d.w.z. met een kopij van die template)

En als je die template / dat sjabloon opslaat in de map van de sjablonen, kan van in Excel met een Bestand, Nieuw, zo’n kopij maken.

Als je meer informatie nodig hebt:

Cellen kopiëren of verplaatsen zonder de adressen te laten aanpassen

V. Als ik cellen kopieer passen de adressen zich aan – tenzij de adressen met dollartekens. Kan ik cellen verplaatsen (dus niet) kopiëren) zónder dat adressen opschuiven? En van één blad naar een adres blad?
A. Ja, er zijn drie opties:

Lijstscheidingsteken aanpassen in Windows 10

Het lijstscheidingsteken is het teklen waarvan Windwos verwacht dat het waarden scheidt die in andere cellen thuishoren.

Bijvoordeeld een namenlijst:

csv

Bij het openen van dit bestand in Excel gaat Excel de cellen herkennen – tenminste als het juiste lijstscheidingsteken is gebruikt. Als dat niet het geval is, kan je aan Excel in de tab DATA vragen om tekst om te zetten in kolommen, en dan het lijstscheidingsteken kiezen. Je krijgt daarbij een preview van het resultaat:

csv-tekstnaarkolommen

Welk lijstscheidingsteken gebruikt wordt, hangt af van het systeem – van de instellingen in Windows. Dat kan met name een probleem zijn als je een CSV bestand krijgt uit Angelsaksisch gebied, waar het lijstscheidingsteken een puntkomma (;) is, terwijl het bij ons een komma (,) is. Maar je kan dat instellen in het ‘klassieke’ control panel, dat je kan opstarten via Win+R (=uitvoeren), control (+enter).
csv-settings1

Daar vind je die instelling onder “De notatie voor datum, tijd of getallen wijzigen”, dan “Meer instellingen”  – en daar het vierdelaatste element.

csv-settings2

Gelinkte velden

V. Hoe kan ik in Excel gelinkte velden maken, waarbij een waarde in de eerste kolom een vooraf gededinieerde waarde in de tweede kolom geeft?

A. Dat kan met vertikaal zoeken. Maak (op een tweede blad) een lijst met eerste en tweede waarden. De eerste waarden moeten alfabetisch gesorteerd zijn.

a abc
b bbc
d dbc
c cbs

Je kan op het eerste blad (dat met de berekeningen) die waarden oproepen met
=VLOOKUP(A1;Sheet2!A1:B4;2)

of

=VERT.ZOEKEN(A1;Blad2!A1:B4;2)

waarde-eerste-kolom-geeft-waarde-in-tweede-kolom

Meer info op http://www.schoutsen.nl/standaard-excel-functies/vlookup-verticaal-zoeken

Van adresetiketten naar adreslijst

V. Een hele tijd geleden heb ik adresetiketten gemaakt van een adreslijst. Die adreslijst ben ik kwijt – maar kan ik nu nog de adreslijst terugvinden?
A. Ja, er zijn een pak mogelijkheden. eerst en vooral moet je zoeken – tik enkele namen in het zoekvak van Windows in – mogelijk staat het adresbestand op een onverwachte plaats. Niet? dan kan je dat adresbestand herstellen.

  • Open het Word bestand met de etiketten. In de tabel gaan staan
    Layout, Data, Convert to Text
    Separate with: *
    Dat zal een * plaatsen tussen de teksten in de verschillende cellen
    wd2013-tabletotext wd2013-tabletotext2
  • Ctrl+H (vervangen)
    vervang ^b (sectie-eindes) door niets (leeglaten)
    wd2013-tabletotext4-removesectionbreaks
    vervang ^p^p door ^p (twee enters door één enter)
    vervang ^p door ;
    vervang * door ^p (een enter na elk stuk dat een cel was
    Dan staat er ; tussen de velden, en een enter na elk record (elk adresgegegeven)
  • Op de eerste regel tik je
    naam; straat; gemeente
  • Herlees manueel of je gegevens er goed gestructureerd uitzien. Op elke regel moet je
    naam; straat; gemeente
    zien staan.
  • Selecteer alles en plak in Excel – Plakken special, onopgemaakte tekst
  • Selecteer in Excel, Gegevens, Tekst naar kolommen – en geef ; als lijstscheidingsteken.
    wd2013-tabletotext3 wd2013-tabletotext4

 

Rekenblad sorteert niet zoals verwacht

V. Examentijd, dus puntenlijsten. Maar ik zie dat de spreadsheets in Google Drive niet sorteren zoals ik verwacht. Wat kan ik doen?

A. Ja, het kan behoorlijk tricky zijn in Google Drive.

  • Om te beginnen moet je de te sorteren gegevens selecteren. Maar dan nog. Als je ‘gewoon’ sorteert via Gegevens, Blad sorteren
    googledrivesort1
    kan je niet opgeven dat je kolomkoppen of veldnamen hebt, en dan krijg je iets als:
    googledrivesort1b
  • Je moet kiezen voor Gegevens, bereik sorteren,
    googledrivesort2
    en aangeven dat de eerste rij berichtkoppen bevat.
    googledrivesort3
  • Overigens: in Excel is het een stuk eenvoudiger: kies voor aangepast sorteren, zeg dat de gegevens data headers hebben,
    googledrivesort4
    en duid aan in welke volgorde je ze gesorteerd wil hebben.googledrivesort5
    Excel zal je sorteervoorkeuren mee opslaan.

 

Excel zegt dat er te weinig geheugen is om af te drukken

V. Ik probeer een Excel bestand aqf te drukken, maar Excel zegt dat er te weinig ruimte is. Wat kan ik doen? Ik heb nog veel geheugen en veel schijfruimte vrij.

A. Mogelijk moet je de tijdelijke bestanden eens opruimen. Herstart, en dan

  • Win+R, %temp% (+enter)
  • Ctrl+A (alles sleecteren), DEL
    Als er bevetsiging gevrgad wordt om een bestand te wissen, zeg je nee.
  • Herstart

Twee reeksen metingen samenvoegen

V. Ik heb twee reeksen metingen – telkens met een uur en een gemeten waarde. Hoe kan ik daarvan één grafiek maken?

meting-a meting-b

A. Plak de metingen op ene nieuw blad – de tijden onder elkaar, meting A in een tweede kolom, en meting B in een derde kolom (tenzij je weet dat de metingen mogen samengevoegd worden).

meting-ab

Sorteer op moment, en werk van daar verder.

Excel zeurt over externe links

V. Ik heb hier enkele Excel files die, als ik ze open, een waarschuwing geven over externe files. Wat is dat?

A. Blijkbaar heb je in die bewuste spreadsheets verwezen naar andere spreadsheets. Excel wil je dat laten weten, en vragen of je die externe links wil updatens (=de laatste gegevens van die externe bestanden inlezen).

xlzietexternelinks1

In die spreadsheet met externe links kan je zoeken (Ctrl+F) naar rechte haken [

 

xlzietexternelinks2

om ze te vinden:

xlzietexternelinks3

Draaitabel is leeg

V. Ik maak een draaitabel, maar die is leeg. Wat kan ik doen?

xl-legedraaitabel1
A. Klik in de draaitabel
xl-legedraaitabel2
dan kan je rechts de draaitabelvelden beginnen toevoegen om verder te werken.

xl-legedraaitabel3

Overigens: zo kan je ook extra  velden toevoegen aan een draaitabel als je per ongeluk ergens op geklikt hebt en dan draaitabelvelden wil toevoegen, zoals in dit voorbeeld:
xl-legedraaitabel4

Word en Excel 2013/2016 standaard – Off 2007 ook op toestel

V. Kan ik Word en Excel 2013 niet als standaard instellen op mijn Windows 10 toestellen? Het Office 365 abonnement zal niet verlengd worden, en daarom heb ik de oude Office 2007 ook nog staan. Maar zowel in Word als Excel blijven die 2007 standaard, niet 2013.

A.Van makkelijk naar moeilijker

Inclusieve en exclusieve kwartielen

V. Wat is het verschil tussen inclusief en exclusief kwartiel? Excel stelt me de functies QUARTILE.INC en QUARTILE.EXC voor.

quartileinx-exc

A. QUARTILE.EXC (in het Nederlands KWARTIEL.EXC) neemt de uitersten niet mee voor het bereken van de kwartielen. Zie ook:

KWARTIEL.EXC, functie – https://support.office.com/nl-nl/article/KWARTIEL-EXC-functie-5a355b7a-840b-4a01-b0f1-f538c2864cad
KWARTIEL.INC, functie – https://support.office.com/nl-nl/article/KWARTIEL-INC-functie-1bbacc80-5075-42f1-aed6-47d735c4819d
end e vroegere functie KWARTIEL
KWARTIEL, functie – https://support.office.com/nl-nl/article/KWARTIEL-functie-93cf8f62-60cd-4fdb-8a92-8451041e1a2a

 

Dubbels verwijderen in Excel

Het verwijderen van dubbels in Excel is heel handig.

xl-duplicata

Even opletten: het is niet hoofdlettergevoelig. Dat betekent dat

Jan
jan
jAn
jaN

als dubbels worden gezien.

Velden sorteren in draaitabel

V. Hoe kan ik velden sorteren in een draaitabel?

A. Naast “Rijlabels” staat een pijl

xl-sorteerpivot1

Daarop klikken geeft je sorteeropties

xl-sorteerpivot2

Maar je kan ook manueel de volgorde wijzigen. Selecteer het label en de waarde die erbij hoort – en als je de vierpuntige pijl ziet, kan je verslepen.

Zie bv. ook How to sort a pivot table manually – https://exceljet.net/lessons/how-to-sort-a-pivot-table-manually

Lange tekst zichtbaar maken in Excel cel

V. Ik heb wat langere tekst in een cel staan in Excel en als ik afdruk staat die tekst er niet helemaal op. Ik kan natuurlijk de kolom verbreden, maar dat is dan te breed. Wat kan ik doen?

A. Stel Tekstterugloop in.

  • Als je vertrekt van:
    xl-textwrap1
  • Selecteer de hele kolom (of rij – afhankelijk van wat je wil) en klik op de opdracht TEKSTTERUGLOOP.
    xl-textwrap2
  • En je krijgt:
    xl-textwrap3

Kolom deleten is geen kolom deleten?

V. Iets vreemd in Excel: als ik een kolom wil verwijderen, wordt ze soms leeggemaakt en soms verwijderd. Wat is dat?
A. Als je een kolom of rij geselecteerd hebt, kan je op twee manieren verwijderen – en het resultaat is niet hetzelfde.

  • Als je drukt op de toets DELET wordt de inhoud van de geselecteerde cellen verwijderd. Maar de geselecteerde rijen / kolommen zelf blijven staan.
  • Als je verwijdert met de opdracht Verwijderen onder de rechtermuisknop, worden de geselecteerde rijen / kolommen verwijderd.
    xl-deletecolumn

RC verwijzingen in Excel

V. Ik krijg de formules in een vreemde weergave in Exel: die worden weergegeven mer RC


A. Dat is een optie in Excel: File, Options, Formulas. Schakel de R1C1 optie uit

Excel: rijen en kolom die herhaald worden bij afdrukken

V. Hoe kan ik een rij (of twee of drie) herhalen bij het afdrukken van een Excel tabel?

A. Ga naar Page layout, Print titles.

image059

In het volgende scherm kan je aanduiden welke kolom(men) of rij(en) moeten herhaald worden – maar bv. ook of je celranden wil afdrukken.

image061

 

Afdruk samenvoegen – datum wordt getal

V. Als ik in Word een standaardbrief maak met gegevens uit Excel, wwodren data omgezet in een getal of in Amerikaanse notatie. Wat kan ik doen?

A. Probeer

{MERGEFIELD datum \@ “dd/MMMM/yy”}

na het weergeven van de veldcodes. Zie het gesprek op http://answers.microsoft.com/nl-nl/office/forum/office_365hp-word/afdruk-samenmvoegen-word-draait-datumnotatie-om-vs/89df729a-7a0d-444f-a0af-97d2c23c664e?auth=1 voor meer achtergrond.

Excel bladen apart versturen via mail

V. Kan ik van één Excel werkboek verschillende bladen naar verschillende correspondenten sturen?

A. Neen, dat gaat niet – als je een Excel werkboek toevoegt, is het noodzakelijkerwijze het hele werkboek. Dit kan wel (afhankelijk van er precies op die bladen staat ):

  • elk van die bladen kopiëren naar een nieuwe werkmap. Rechtsklik op zo’n tab om die te kopiëren. zie ook. Sending Single Worksheets via E-mail – http://excelribbon.tips.net/T008508_Sending_Single_Worksheets_via_E-mail.html ;
  • of je maakt de mailberichten, en plakt daarin telkens het stuk van het werkblad dat ze mogen zien;
  • mogelijk best: druk het Excel werkblad af als PDF – en kies daarbij voor het hele rekenblad. Je krijgt een PDF met alle bladen. Splits die PDF met iets als http://www.splitpdf.com/ en je hebt één PDF per bestemmeling. Maar die PDF heeft natuurlijk geen (interactieve) berekeningen meer, alleen resultaten.

0, nul of niets in Excel

V. Wat is het verschil tussen 0, nul of niets (lege cel) in Excel?

A. Terwijl een lege cel gewoon leeg is, is 0 een getal (en dan is de cel niet leeg) en “nul” is een string of letterreeks waar je (in principe) niet mee kan rekenen.

nul-1

Het verschil valt vooral op bij het maken van grafieken. 0, nul of niets (lege cel) geven een blanco in een kolomgrafiek.

nul-2

Maar bij ene lijngrafiek wordt er alleen 0 weergegeven (springt de grafiek naar de X-as) bij 0 en nul.

nul-3

En in dit bestand kan je alles eens bekijken.

Maanden tussen twee data

V. Hoe kan ik in Excel berekenen hoeveel maanden er tussen twee data liggen?

A. De functie MAAND doet dat voor jou. Stel: A2 is de eerste datum en B2 de tweede. Dan:

=MAAND(B2-A2)

op rij 2, en nadien doortrekken.

Maar omdat die functie telt tot 12 (er zijn maar 12 maanden in een jaar) moeten we rekening houden met het geval beide data in een ander jaar liggen. Daarvoor gebruiken we JAAR. Die functie heeft 1900 als nulpunt (0/1/1900 is het nulpunt van rekenen met data in Excel), en dus bekomen we

=MAAND(B2-A2)+(JAAR(B2-A2)-1900)*12

 

Mailbevestiging: Excel online of Google drive?

V. Ik wil een online inscvhrijvingsformulier maken voor iets dat ik organiseer. Ik wil de inschrijver een beevstigingsbericht sturen, en ik wil graag een Excel lijst hebben met de inschrijvingen. Gebruik ik best OneDrive of Google Drive?

A. Online Excel (op OneDrive) en Google forms (op Google drive) hebben niet dezelfde mogelijkheden. Dit is een overzicht van de

Excel online

  • Het resultaat is een Excel spreadsheet direct opvraagbaar en gesynchroniseerd
  • Real-time berekeningen met de resultaten (op een tweede blad) zijn intuitief.

 

Google forms

  • Notifications – je kan een bevestigingsbericht sturen. Dat gaat zo:
    Add-ons, Form notifications
    googleformsnotifications1
    Geef aan dat je respondenten wil verwittigen, en duid aan welk formulierveld het mailadres bevat.
    googleformsnotifications2
  • Je kan een  “Andere” optie voorzien in een meerkeuzevraag. En je kan naar een bepaald punt in het formulier springen in functie van een antworod.
    googleformsoptions
    Excel online kent deze mogelijkheid (nog) niet.
  • Echter: geen van beide kent berekende velden (bv. inschrijvingsprijs op basis van een antoord of reeks antwoorden. Maar er zijn andere mogelijkeden zoals Eventbrite of andere.
  • Of op een WordPress site een component als Formidable forms (https://wordpress.org/plugins/formidable/), Custom Contact Forms (https://wordpress.org/plugins/custom-contact-forms/), Participants Database (https://wordpress.org/plugins/participants-database/).

 

 

Accenten verdwijnen uit Excel bestand bij export naar CSV

V. Ik heb in Excel een lijst gemaakt met technische termen. Ik moet die lijst hebben in CSV formaat, maar de geaccentueerde letters verdwijnen. Wat kan ik doen?

A. Dat heeft te maken met encodering. Mogelijke oplossingen zijn:

  • Exporteer als tab delimited tekstbestand. Dat is nog geen écht CSV bestand, maar dat is op te lossen door het bestand in Word te openen (Word, File, Open) en dan te vervangen (Ctrl+H) tab ^(^t) door komma of puntkomma. Welk van de twee hangt af van wat het lijstscheidingsteken is op het systeem waar je dat CSV bestand gaat gebruiken. Let bij het opslaan wel goed op dat je je als tekstbestand opslaat.
  • Sla het document eerst op als XLS (97-2003 indeling), en van daar naar CSV.
  • Nog niet? Probeer te converteren van in Google drive of met LibreOffice.

Er is een interessant gesprek over dit onderwerp op http://stackoverflow.com/questions/4221176/excel-to-csv-with-utf8-encoding.

Excel geeft valuta, Word ziet getal

V. Ik doe een mail merge met een veld dat als valuta is opgemaakt (27,00 €), maar in Word krijg ik 27. Wat is dat?

A. In plaats van samen te voegen met het veld bedrag, zou ik een bijkomend veld maken tekst-bedrag, met als berekening

=TEXT(A2;”#,00 €”)

Excel geeft immers aan Word een getal door, want dat is wat het is (opgemaakt als valuta, maar dus wel een getal). En Word geeft dat getal.

De oplossing is om Word een string te geven.

Tweedegrootste getal van reeks

V. Hoe kan ik het tweedegrootste getal vinden in een reeks getallen? MAX geeft het grootste  – maar het tweedegrootste dan? En … bestaat dat ook voor het tweedekleinste?

A.  MIN en MAX geven de kleinste resp. grootste waarde. Je bent op zoek naar KLEINSTE en GROOTSTE

 

WEEKDAG functie

V. Ik wil in Excel de weekdag berekenen van een datum. Naar verluidt kan dat met WEEKDAG – maar ik zie twee mogelijke parameters die maandag als 1 weergeven en zondag als 7. Is er een verschil?
weekday2-11

A. Neen, er is geen verschil – WEEKDAG(datum;2) en WEEKDAG(datum;11) geven hetzelfde resultaat. Maar misschien vind je =TEKST(datum;”dddd”) nog interessanter: de schrijft de weekdag uit.

 

EXCELlente statistiek

Excel bevat behoorlijk wat tools om aan ernstige statistiek te doen. In deze post wil ik wat inspiratie geven.

Willekeurige getallen bij wijze van voorbeeld / test

V. Ik zou een Excel willen tetsen en daarvoor heb ik willekeurige getallen nodig. Kan ik dat niet door Excel laten in plaats van zelf vanalles in te tikken?

A. Ja – met behulp van de functie RAND() [in het Nederlands ASELECT()] die een willekeurig kommagetal generereert tussen 0 en 1. Daarmee kan je dit bv. doen:

  •  =integer(aselect()*100) geeft willekeurig getal tussen 0 en 100
  •  =integer(aselect()*60)+20 geeft willekeurig getal tussen 20 en 80
  • Willekeurige waarde uit een reeks waarden of strings:
    =INDEX($A$1:$A$4;INTEGER(ASELECT()*4+1)) [met in dit voorbeeld vier waarden die staan van A1 tot A4]

    zie voor dit laatste bv.
    http://www.exceltip.com/working-with-formulas/random-selection-from-a-list.html

 

Excel: rijen en kolommen draaien

V. Ik heb een grafiek gemaakt maar de rijen en kolommen staan verkeerd. Moet ik herbeginnen?

switchen1

A. Neen – klik op Kolommen / rijen switchen
switchen2

en het is gebeurd

switchen3

Velden sorteren in draaitabel

V. Kan ik velden sorteren in een draaitabel? En hoe dan?

A. Rechtsklik op een kolomkop / veldnaam in de draaitabel, en klik op Sorteer.

xl-pivot-sortfield1

En bij “meer opties”

xl-pivot-sortfield2

Zie ook: http://www.ehow.com/how_7259051_sort-order-pivot-table.html

Velden kwijt voor draaitabel

V. Ik ben of een andere manier het rechterstuk van het scherm kwijtgeraakt en ik kan niet meer verder met mijn draaitabel. Wat kan ik doen?
A. Ik denk dat je de lijst met velden kwijt bent. De opdracht om die terug te halen vindt je na rechtermuisklik op de draaitabel.

xlpivot-showfieldlist

ip-location