Tagarchief: Sorteren

Kindernamen ofwel Sorteren in draaitabel



Kindernamen

Bij een analyse van de populariteit van diverse kindernamen gebruikte ik (uiteraard) draaitabellen. Bij het sorteren realiseerde ik me weer dat daar bij een draaitabel wel wat haken en ogen aan zitten, maar dat deze optie ook extra mogelijkheden biedt.
Een mooie aanleiding voor een nieuw artikel.

Brongegevens

Gelukkig hoeven we voor een overzicht van namen, die in de loop van de jaren aan kinderen zijn gegeven, niet zelf op onderzoek uit; al jaren publiceert het SVB deze.
Op de website svbkindernamen.nl kunnen we de namen van de laatste 5 jaar terugvinden.

NB om te vermijden dat namen naar personen kunnen worden herleid, publiceert het SVB namen, die minder dan 25 keer voorkomen, niet.

Met wat kopiëren en plakken kunnen we de overzichten van de site snel overhevelen naar Excel.
Het tabblad Data van het Voorbeeldbestand bevat alle namen van de laatste 5 jaar met het aantal keren dat ze toegekend zijn en de rangorde in het betreffende jaar; de gegevens zijn aangevuld met 2 extra kolommen: het jaar en of het een jongens- of meisjesnaam is.

De gegevens staan in een Excel-tabel tblData. Zoals te zien is, zijn nog enkele hulpkolommen en -cellen toegevoegd:

  1. in cel C3 (met de naam MaxJr) wordt het hoogste jaar opgehaald.
    Tik in cel C3 in: =max( en wijs dan met de muis de bovenrand van cel B6 aan (het muisteken wordt een pijltje naar beneden) en geef een muisklik. Excel vult zelf de formule aan: =max(tblData[Jaar]
    Druk op Enter.
  2. op een vergelijkbare manier bevat cel C4 (met de naam MinJr) het laagste jaar
  3. in de nieuwe kolom Lengte wordt het aantal letters van de naam bepaald:
    =LENGTE([@Voornaam])
    De functie Lengte berekent hier de lengte van de naam in de kolom Voornaam in deze tabel (aangeduid door de rechte haken) en wel de cel in de overeenkomende rij (aangeduid door de @).
    NB je hoeft dit soort notaties niet te onthouden; klik bij het invoeren van de formule op de gewenste cel en Excel vult alles automatisch in.
  4. de beginletter van de naam vinden we met de volgende formule:
    =LINKS([@Voornaam])
    Om te zorgen dat Ömer ook onder de O komt is de gebruikte formule iets uitgebreid:
    =ALS(LINKS([@Voornaam])=”Ö”; “O”;LINKS([@Voornaam]))
  5. in de kolom daarnaast bepalen we of de naam vaker voorkomt dan het jaar daarvoor:
    =ALS([@Jaar]=MinJr;”-“;
         ALS([@Aantal] >
              SOMMEN.ALS([Aantal];
                   [Jaar];[@Jaar]-1;[JM];[@JM];[Voornaam];[@Voornaam]
              );
         “J”;”N”))
    De eerste Als zorgt er voor, dat als het om een naam uit het eerste jaar gaat (MinJr), de aanduiding een streepje wordt (we weten niet of die in populariteit gestegen is of gedaald).
    Anders: als het Aantal uit de betreffende regel groter is dan ‘iets anders’ dan wordt het resultaat gelijk aan J, anders N.
    Maar wat is dat ‘iets anders’? Met de functie Sommen.als tellen we alle (geen @) gegevens op uit de kolom Aantal, die voldoen aan de voorwaardes daarna: het Jaar moet gelijk zijn aan het jaar uit de huidige regel minus 1, de code JM moet gelijk zijn aan die uit de huidige regel én de Voornaam moet overeenkomen.
    NB als de bron-data consistent zijn dan levert deze exercitie maar 1 resultaat op: het aantal keren dat de naam een vorig jaar voorkwam.
  6. om straks te kunnen zien of een naam in de loop van de jaren alleen maar populairder wordt ziet u nog een kolom SteedsStijgend met de formule:
    =AANTALLEN.ALS(
         [JM];[@JM];
         [Voornaam];[@Voornaam];
         [StijgendJN];”J”
         )
         =MaxJr – MinJr
    Op een vergelijkbare manier als hiervoor met Sommen.als bepalen we met behulp van Aantallen.als eerst het aantal keren dat bij een naam de codering StijgendJN gelijk is aan J.
    Daarna vergelijken we of dit aantal gelijk is aan MaxJr minus MinJr; als dat zo is, dan wordt het resultaat WAAR, anders ONWAAR.
    NB door het gebruik van de cellen MaxJr en MinJr hoeft er aan ons ‘analyse-systeem’ niets meer gewijzigd te worden wanneer er gegevens van andere jaren worden toegevoegd.

Jaar-overzicht

Allereerst maken we een overzicht van totalen per jaar (zie het tabblad JrOverz# van het Voorbeeldbestand).
De eerste conclusie zou kunnen zijn, dat er per jaar ruim 110.000 kinderen worden geboren en dat er blijkbaar per jaar meer jongens bij komen dan meisjes.

Deze getallen toch maar eens checken. Via Statline van het CBS zien we dat het aantal geboren kinderen in deze jaren ongeveer 170.000 is geweest en dat er ongeveer 5% meer jongens dan meisjes worden geboren:

NB wereldwijd worden er zelfs 7% meer jongens dan meisjes geboren. Volgens diverse bronnen zou uit onderzoek blijken, dat vrouwelijke embryo’s een net iets grotere kans hebben om te overlijden in de buik. Bij de bevruchting zou de verhouding wel degelijk 50/50 zijn.

Dat de totalen in het SVB-overzicht lager zijn is te wijten aan het uitsluiten van namen die minder dan 25 keer per jaar voorkomen. De verhouding jongens-meisjes volgens het SVB ligt nog verder scheef; blijkbaar worden voor meisjes vaker dan voor jongens originele namen bedacht!

Wanneer we dit overzicht beperken door de beginletter van de naam als filter te gebruiken, kunnen we diverse opvallende ontdekkingen doen.
Bijvoorbeeld: er zijn 3x zoveel meer jongens- dan meisjesnamen met een B in gebruik (tenminste namen die vaker dan 25 keer voor komen). Wanneer we daar de aantallen voor corrigeren, dan is de verhouding jongens t.o.v. meisjes binnen deze categorie iets groter dan over de hele populatie.

Bij deze draaitabel is de sortering nog rechttoe rechtaan: bij het aanmaken van de draaitabel heeft Excel de rijen en kolommen oplopend gesorteerd. Wil je de sortering anders hebben:

  • klik op een rij- of kolomnaam
  • klik op de menutab Gegevens
  • klik op of
  • of klik rechts op een rij- of kolomnaam en kies binnen de menu-optie Sorteren de gewenste volgorde:

Top-10

Laten we eens een Top-10 maken (zie ook het artikel Top-5):

  1. klik ergens in het tabblad Data van het Voorbeeldbestand op een cel in de tabel tblData
  2. kies in de menutab Invoegen in het blok Tabellen de optie Draaitabel en klik op OK
  3. plaats het Jaar in de Kolommen, Voornaam in de Rijen en Aantal en Populariteit in het Waarden-gebied

  1. klik rechts op één van de voornamen in de draaitabel en kies de optie Filteren en daarna de optie Top-tien.
    Zorg dat de volgende instelling is geselecteerd en sluit af via OK:
  2. de namen staan nu nog in alfabetische volgorde; we willen ze natuurlijk van hoog naar laag van het aantal keren dat ze gebruikt zijn:
    klik rechts op één van de voornamen in de draaitabel en kies de optie Sorteren en daarna Meer sorteeropties. Vul het vervolgscherm als volgt in en klik op OK:

Om een top-10 van jongens óf meisjes en/of andere deelselecties te maken zijn in het tabblad Top10 van het Voorbeeldbestand de velden JM, BegLetter en Lengte in het Filters-gebied geplaatst:

In het overzicht dat we op deze manier hebben gemaakt, staan de namen in de volgorde van het Eindtotaal. Wil je bijvoorbeeld de volgorde van 2019 zien, doe dan het volgende:

  1. klik rechts op één van de voornamen en kies de opties Sorteren en dan Meer sorteeropties
  2. kies op het vervolgscherm Meer opties
  3. vul het volgende scherm als volgt in:

LET OP op deze manier is de sortering wel aan te passen aan het gewenste jaar. De namen van de top-10 veranderen daardoor niet. De filtering wordt door Excel altijd op basis van het Eindtotaal gemaakt.

Top-10 per jaar

Een echte top-10 per jaar kunnen we alleen maken door ook het Jaar in het Filters-gebied van de draaitabel te plaatsen.

Zie het tabblad JrTop10 van het Voorbeeldbestand.

Wat opvalt: korte, krachtige namen zijn erg in trek!

Tendens over de jaren

Bij een ‘analyse’ mag een verloop in de tijd niet ontbreken (zie het tabblad JrTendens in het Voorbeeldbestand):

  1. maak een draaitabel op basis van de brongegevens
  2. plaats het Jaar in de Kolommen, de Voornaam in de Rijen en 2x het Aantal in het Waarden-gebied
  3. om een deelselectie te kunnen maken plaatsen we JM, BegLetter en Lengte in het Filters-gebied
  4. klik rechts op een van de getallen in de eerste Aantal-kolom, pas de naam aan (StDal)
  5. kies Waarden weergeven als en vul het vervolgscherm in:

Wanneer we nu het overzicht voor jongens maken, valt direct een vreemde sortering van de namen op.

Alle namen staan netjes alfabetisch op de eerste 2 na!
De reden daarvoor zag ik zo gauw niet, tot ik me ineens realiseerde dat Excel Jan en Sep waarschijnlijk als maandaanduiding ziet. Wat blijkt: Excel gaat bij een sortering in een draaitabel niet zonder meer uit van een standaard ‘alfabetische’ sortering maar gaat ook op zoek naar Aangepaste lijsten (zie Doorvoeren en sorteren). Als er gegevens zijn die aan een Aangepaste lijst voldoen, dan worden deze gegevens eerst gesorteerd, daarna komen de anderen in alfabetische volgorde.

Als je het probleem onderkent, is de oplossing vaak dichtbij.
Bij een sortering buiten een draaitabel kun je op de grote button Sorteren klikken en krijg je een vervolgscherm waarin je kunt aangeven op welke manier er gesorteerd moet worden:

Standaard staat de sortering op A-Z, maar die kun je wijzigen. Waaronder de optie om een Aangepaste lijst te gebruiken.
Bij een draaitabel werkt dit echter niet. Via de volgende weg kun je dit oplossen:

  1. klik rechts op een van de namen en kies de optie Sorteren en dan Meer sorteeropties
  2. klik op de button Meer opties
  3. zet de optie Automatisch sorteren in het vervolgscherm uit en kies als Sorteervolgorde: Geen berekening

    Bij Sorteervolgorde zit nu de optie verstopt of je wel of niet een Aangepaste lijst wilt gebruiken!

NB aan het veld StDal is een Voorwaardelijke opmaak toegevoegd, zodat de tendens in de loop van de jaren sneller duidelijk is.

Steeds populairdere namen

Wanneer we een overzicht willen hebben van alle namen die in de loop der jaren steeds populairder worden, maken we een draaitabel waaraan we als filter ook het veld SteedsStijgend toevoegen (zie het tabblad JrStijgend van het Voorbeeldbestand).

De naam Fos zal in 2015 ook wel zijn voorgekomen, maar het aantal zal lager dan 25 zijn geweest; door het SVB worden die in het kader van de privacy weggelaten.

Selectie

Ben je op zoek naar een handig hulpmiddel om een naam te vinden voor een kind (populair of juist niet, beginnend met een bepaalde letter, een korte of lange naam)?
Het tabblad Selectie van het Voorbeeldbestand bevat een draaitabel met slicers waarmee een keuze maken heel eenvoudig is (?):

NB de inhoud van de slicers past zich automatisch aan aan keuzes die in andere slicers zijn gemaakt. Door als beginletter G te kiezen bij de jongens zijn er alleen nog namen met 4 tot 8 letters over.


Verkiezingsuitslagen en dynamische draaitabellen



Deze week las ik een artikel over dynamische draaitabellen gemaakt met PowerPivot (een invoegtoepassing waarmee u krachtige gegevensanalyses in Excel kunt uitvoeren).
De draaitabel liet een overzicht zien, gesplitst naar landen. Werd er echter (met behulp van een slicer) één land geselecteerd, dan veranderde de draaitabel automatisch in een overzicht, gesplitst naar steden.
In PowerPivot kunnen dit soort zaken geïmplementeerd worden met behulp van een eigen programmeertaal.

De uitdaging was om te kijken of dit ook ‘gewoon’ in Excel geregeld kan worden. Ik dacht de verkiezingsuitslagen van vorige week mooi als voorbeeld te kunnen gebruiken (overzicht per provincie verandert automatisch in een overzicht per gemeente), maar helaas . Omdat de uitslagen van 2017 nog niet (op detail-niveau) bekend zijn heb ik de data van 2012 gebruikt. Met een paar kleine aanpassingen kunnen de resultaten van 2017 ingekopieerd worden en kunnen er dan ook vergelijkingen tussen de jaren gemaakt worden. Dat doen we later nog een keer; beloofd is beloofd!

Basis

De gegevens heb ik kunnen downloaden van www.verkiezingsuitslagen.nl in de vorm van een CSV-bestand (Comma Seperated Values). Zie het tabblad Uitslagen 2e Kamer 2012 in het Voorbeeldbestand.

Zoals te zien is, krijg je een tekstbestand in Excel, dat nog wat aanpassingen verdient:

  1. allereerst moeten de teksten over verschillende kolommen verdeeld worden:
    * selecteer alle cellen in kolom A, die omgezet moeten worden (A1 t/mA443)
    * kies in de menutab Gegevens in het blok Hulpmiddelen voor gegevens de optie Tekst naar kolommen
    * aangezien de kolommen geen standaardbreedte hebben maar door een komma van elkaar zijn gescheiden, kiezen we in het vervolgscherm de optie Gescheiden en klikken op Volgende
    * zorg dat in het volgende scherm de optie Komma staat aangevinkt en klik op Voltooien
  2. even wat regels over de totalen per partij verwijderen (de eerste 22), de gegevens in de vorm van een tabel gieten (kies in de menutab Invoegen de optie Tabel) en de onderste (dreigende) regels verwijderen.
  3. de kopregel aanpassen, zodat de breedte van de kolommen wat beperkt blijft (via Ctrl-1/Uitlijning)
  4. even sorteren op CBS-code
  5. dan blijken veel plaatsen al per provincie bij elkaar te staan: een kolom Provincie toevoegen, aan ieder blok een Provincienaam toevoegen en dan ‘met de hand’ nog wat correcties doorvoeren (wat de systematiek van het CBS voor de codes is, is me niet helemaal duidelijk!)
  6. zo krijgen we een overzicht van de stemmen per gemeente (zie tabblad Uitslagen in het Voorbeeldbestand)

Verkiezingsuitslagen

In dit artikel gaan we alleen wat doen met de partij-uitslagen, dus allerlei kolommen over geldige stemmen enzovoorts kunnen weg.

Om de overzichten zo flexibel mogelijk te kunnen maken moet de bron nog in een zogenaamde database-structuur worden gegoten: niet alle partijen naast elkaar, maar per plaats een aparte regel voor iedere partij met daarbij het aantal stemmen.
Op deze manier ontstaat een basis-bestand met 8.778 regels; voor een mens niet zo handig maar voor Excel geen punt!
Zie het tabblad Basis in het Voorbeeldbestand.

Overzicht per provincie

Allereerst gaan we een overzicht per provincie maken:

  1. selecteer een cel in het basis-bestand
  2. kies Invoegen/Draaitabel en klik OK (Excel kiest automatisch als bron de totale tabel tblBasis)
  3. op het nieuwe werkblad gaan we de draaitabel vormgeven:
    * sleep het veld Partij naar Kolomlabels
    * sleep het veld Provincie naar Rijlabels
    * sleep Aantal naar het Waarde-gebied
    * sleep nogmaals Aantal naar het Waarde-gebied
    * zorg dat Waarden in de Rijlabels staat
    * klik rechts op een van de cellen in een regel met Som van Aantal2, kies Waardeveldinstellingen en dan bij Waarden weergeven als de optie % van rijtotaal
    * wijzig één van de namen Som van Aantal2 in Perc
  4. en nog een sortering aanbrengen:
    * klik met de muis rechts op één van de partijen
    * kies Sorteren en dan de optie Meer sorteeropties
    * in het vervolgvenster kiest u Aflopend en in het bijbehorende keuzeveld de optie Som van Aantal (of Perc; dat maakt in dit geval niets uit). Op deze manier zullen de partijen altijd gesorteerd worden van meeste stemmen naar minste; ook als de draaitabel van inhoud verandert.

Zo ontstaat het overzicht zoals weergegeven in het tabblad Ovz1 van het Voorbeeldbestand.

Overzicht per gemeente

Als we nu willen weten hoe bijvoorbeeld de uitslag van de provincie Groningen tot stand is gekomen, moeten we de draaitabel aanpassen (of een nieuwe maken):

  1. maak een kopie van het tabblad Ovz1 (‘met de muis vastpakken’ en, met de Ctrl-toets ingedrukt, verslepen)
  2. versleep het veld Provincie van Rijlabels naar het Rapportfilter
  3. sleep het veld Plaats naar Rijlabels (boven Waarden!)
  4. selecteer bij Provincie de keuze Groningen (in het tabblad Ovz2 is dat cel C2)

Dynamische draaitabel

Maar zou het niet mooi zijn, als de draaitabel eerst een overzicht van de provincies vertoont, maar dat deze automatisch de plaatsen laat zien als we slechts 1 provincie kiezen?

Om dit voor elkaar te krijgen, moeten we de volgende stappen doorlopen:

  1. maak eerst een nieuwe draaitabel aan vergelijkbaar met hiervoor (zie tabblad Ovz3 in het Voorbeeldbestand)
  2. cel E3 (waar een bepaalde provincie kan worden gekozen) heeft de naam Prov gekregen
  3. aan het basisbestand is een kolom PrPl toegevoegd met de volgende formule:
    =ALS(LINKS(Prov;4)=”(All”;[@Provincie];[@Plaats])
    Dus als in E3 de optie alle provincies is gekozen, dan komt er in de toegevoegde kolom de Provincie te staan, anders de Plaats.
    NB1 @Provincie betekent in een tabel: haal de inhoud op uit de kolom Provincie in dezelfde regel
    NB2 we controleren of de eerste 4 karakters van de cel Prov gelijk is aan (All, omdat Excel soms (Alles), soms (Alle) en in de Engelstalige versie (All) laat zien
  4. sleep nu het veld PrPl naar de Rijlabels van de draaitabel in Ovz3 (eerst even de draaitabel Vernieuwen, omdat anders de nieuwe kolom nog niet in de bron is opgenomen)
  5. voeg nog een slicer toe voor de Provincie (zie het artikel Slicers in Excel)

Wanneer we nu ‘spelen’ met de opties in de cel Prov (of met de slicer) zien we, dat het systeem nog niet vlekkeloos werkt: bij het veranderen van 1 naar alle provincies of andersom moet de draaitabel eerst vernieuwd worden, voordat de gewenste resultaten zichtbaar zijn. Dit komt omdat dan wel de kolom PrPl in de bron wordt aangepast, maar de draaitabel nog de oorspronkelijke inhoud in het geheugen heeft.
Met VBA kunnen we dit automatiseren.

NB Vernieuwen van een draaitabel kan door ergens rechts te klikken in een draaitabel en dan Vernieuwen te kiezen of (als een cel van een draaitabel is geselecteerd) op Alt-F5 te drukken.

VBA

Met de volgende code is het actualiseren van de draaitabel te automatiseren:

Public ProvOud
Public Vernieuwen As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
If [Prov] <> ProvOud And Not Vernieuwen Then
Vernieuwen = True
ActiveSheet.PivotTables(“Draaitabel3”).PivotCache.Refresh
End If
ProvOud = [Prov]
Vernieuwen = False

End Sub

Doe nu het volgende:

  1. open (zonodig) het Voorbeeldbestand
  2. klik met de rechter muisknop op het tabblad Ovz3
  3. kies de optie Programmacode weergeven
  4. zorg dat (links) in het projectenoverzicht het blad Ovz3 is geselecteerd
  5. plak dan in het code-scherm de bovenstaande code
  6. sluit de VBA-editor
  7. Sla het bestand op als Excel-werkmap met macro’s; dus met de extensie xlsm
  8. wissel nu tussen provincies afzonderlijk en alle provincies

Doorvoeren en sorteren

De titel klinkt als een term uit de breiwereld, maar het heeft toch echt alles met Excel te maken.

Een paar weken geleden, tijdens een Excel-cursus die ik gaf, bleek weer eens hoe weinig mensen de handige optie ‘Doorvoeren’ van Excel ten volle kennen; laat staan gebruiken.
En bijna niemand weet dat je je eigen reeksen kunt maken, die op dezelfde manier gebruikt kunnen worden; en dat deze reeksen ook voor sortering gebruikt kunnen worden.

Doorvoeren

Laten we eerst even kijken wat doorvoeren eigenlijk is:
Doorvoereneen cel,  gevuld met een waarde (dat kan een getal, datum of tekst zijn) kun je snel kopiëren door de zogenaamde vulgreep rechtsonder in de cel met de muis naar beneden of naar rechts te verslepen.

Stel dat cel B1 het getal 1 bevat (download  het Voorbeeldbestand);  wanneer we dan de vulgreep ‘naar beneden trekken’ tot in rij 10 dan zal Excel alle tussenliggende cellen met een 1 vullen.
DoorvoerenMaar let op: rechtsonder komt een kleine button tevoorschijn: de Opties voor Automatisch doorvoeren.
Deze opties zijn context-gevoelig, dat wil zeggen dat het aantal opties en de mogelijkheden afhankelijk zijn van de inhoud van de gekopieerde cel.
DoorvoerenIn dit geval (we hebben het cijfer 1 gekopieerd) zal Excel ons 4 opties tonen wanneer we op de button klikken: Cellen kopiëren (in dit geval de standaardactie), Reeks doorvoeren, Alleen opmaak doorvoeren en Doorvoeren zonder opmaak.
De laatste 2 opties spreken voor zich. De tweede (Reeks doorvoeren) is het meest interessante: wanneer je daar nu op klikt zal Excel niet de 1 uit cel B1 kopiëren, maar de volgende rijen vullen met 2, 3 etcetera!

NB in iedere regel van de Opties voor Automatisch doorvoeren is een letter onderstreept; dat betekent dat je niet met de muis op zo’n optie hoeft te klikken, je kunt ook de betreffende onderstreepte letter op het toetsenbord intikken.

Laten we dat ook eens met een datum doen: in het Voorbeeldbestand is cel C1 gevuld met 1-1-2015.
DoorvoerenTrek de vulgreep van deze cel naar rij 10 en de cellen worden gevuld met de eerste 10 dagen van het jaar!
DoorvoerenKlik op de optie-button rechtsonder en we zien de diverse mogelijkheden. Blijkbaar gebruikt Excel bij een datum doorvoeren standaard de 2e optie (Reeks); dit in tegenstelling tot getallen, waar standaard de kopieeractie wordt uitgevoerd.
De eerste vier opties snappen we nu, de vijfde is exact gelijk aan de tweede, maar de zesde is om van te watertanden! En de zevende en de achtste zijn ook uiterst handig.
Probeer de mogelijkheden uit. Trek de reeks verder door dan 31 dagen en daarna ook voorbij februari: Excel weet precies hoeveel dagen er in een maand zitten.

Nog even terug naar de getallen: wanneer we de cellen D1 en D2 vullen met respectievelijk 1 en 2, we selecteren de TWEE cellen Doorvoerenen slepen de vulgreep naar beneden, dan kiest Excel wel als standaard de optie Reeks doorvoeren (en krijgen we dus de getallen 1 t/m 10); cellen kopiëren levert in dit geval allemaal blokjes van de getallen 1 en 2 op.

Stel we vullen de cellen E1 en E2 met de getallen 1 en 3; doorvoeren van deze 2 cellen levert een mooie reeks van 1, 3, 5, 7 etc.
LET OP:  met een reeks als 1, 4, 9 (de volgende zal dan toch wel 16 zijn?) kan Excel niet overweg.

Dit opent perspectieven: willen we een overzicht van alle maandagen in 2015 dan vullen we een cel met de eerste maandag (5-1-2015) en de cel daaronder met de tweede (12-1-2015), we selecteren deze twee cellen en slepen de vulgreep!

Teksten doorvoeren

Hiervoor hebben we gezien welke mogelijkheden Excel biedt voor het doorvoeren van getallen (voor Excel zijn datums gewoon getallen maar met een speciale opmaak).
Welke mogelijkheden zijn er voor teksten?

In het Voorbeeldbestand is cel F1 gevuld met de tekst een. Voer deze cel door en Excel vult alle cellen met dezelfde tekst. Klikken op de button Opties voor Automatisch doorvoeren laat zien dat er maar drie mogelijkheden zijn: Kopiëren (de standaardactie) en wel of niet de opmaak doorvoeren.

Zijn er twee cellen gevuld (G1 en G2 met een en twee), worden deze geselecteerd en doorgevoerd, dan ontstaat er een opeenvolging van deze twee tekstcellen.

Verrassing!?
DoorvoerenCel H1 is gevuld met het woord ma.
Wanneer we deze cel doorvoeren, ontstaat er de bekende reeks ma, di , wo, do, vr, za, zo!
Is de doorvoering groter dan 7 rijen of kolommen, dan wordt de reeks herhaald.

En ook hier zit onder de button Opties voor Automatisch doorvoeren de handige optie om alleen Weekdagen door te voeren.

Wat de mogelijkheden zijn, wanneer we een cel met het woord maandag, jan of januari doorvoeren, zal nu wel niet meer verbazen.

NB wanneer de eerste cel met een hoofdletter begint (of alles is in hoofdletters) dan zal Excel bij het doorvoeren dat automatisch overnemen.

Snel doorvoeren

Stel dat we van een grote reeks getallen het kwadraat zouden willen weten (en wie wil dat niet?) dan kunnen we diverse, benodigde handelingen snel uitvoeren (zie ook het tabblad SnelDoorv in het Voorbeeldbestand):

  1. in de cellen B1 en B2 zetten we de getallen 1 respectievelijk 2
  2. selecteer deze 2 cellen en voer met behulp van de vulgreep deze selectie naar beneden door voor het aantal getallen, waarvan je het kwadraat wilt weten
  3. in cel C1 zetten we de tekst in het kwadraat =
  4. dubbelklik op de vulgreep van cel C1 en de tekst wordt automatisch zo vaak naar beneden gekopieerd als er in kolom B getallen staan!
  5. in cel D1 komt de formule =B1^2
    Het caret-teken wordt in Excel gebruikt om een machtsverheffing aan te duiden (zoals Wikipedia zegt: Not to be confused with Carrot or Carat).
  6. dubbelklikken op de vulgreep van cel D1 en ook deze formule is tig-keer gekopieerd

LET OP: dubbelklikken heeft alleen effect als de kolom links of rechts van de door te voeren cel gevuld is.

Sorteren

De reeksen voor de dagen en maanden kunnen ook gebruikt worden om een logische sortering door te voeren.

DoorvoerenIn het Voorbeeldbestand heb ik ook een tabblad Sorteren opgenomen, waarin een overzicht staat van de omzet van een heel kleine witgoedhandel: per verkocht apparaat staat vermeld in welke maand voor welk bedrag dit is verkocht.

Om dit schema wat overzichtelijker te maken is het handig om te sorteren op de maand: selecteer een cel in de maand-kolom en sorteer in de volgorde A-Z (met behulp van de button Steekproef binnen de menuoptie Gegevens of door rechts te klikken en dan Sorteren te kiezen).
Het resultaat is waarschijnlijk niet wat je verwacht (of gehoopt) had: Excel zal standaard de gewone alfabetische volgorde aanhouden.

DoorvoerenMaar daar kunnen we wat aan doen:

  1. klik rechts op een cel in de maand-kolom
  2. kies Sorteren en dan de optie Aangepast sorteren
  3. klik in het vervolgscherm onder Volgorde op het keuzedriehoekje Doorvoeren
  4. klik op Aangepaste lijst en kies de lijst met maanden
  5. nog 2x op OK klikken en de nieuwe sortering is klaar!

De nieuw ‘geleerde’ volgorde blijft actief totdat deze op de voorgaande manier wordt aangepast.

Eigen sortering/reeks

We kunnen onze omzet ook op product sorteren: we krijgen dan de volgorde Koelkast, Magnetron, Strijkijzer.

Wanneer we (om een of andere reden) de keukenapparaten achteraan willen hebben, is dat standaard niet mogelijk, maar …..

  1. open het tabblad Sorteren van het Voorbeeldbestand
  2. klik rechts op een cel in de product-kolom
  3. kies Sorteren, Aangepast sorteren, keuzedriehoekje bij Volgorde en dan Aangepaste lijst
  4. Doorvoerenin het blok Gegevens in lijst zetten we de drie apparaten in de gewenste volgorde (gescheiden door Enter)
  5. klik op Toevoegen
  6. 2x OK en de nieuwe sortering is klaar!

NB deze aangepaste lijst zal door Excel voortaan ook gebruikt worden bij het Doorvoeren. Tik in een cel Strijkijzer in en voer de cel met behulp van de vulgreep door: de volgende cel wordt Magnetron, dan Koelkast, opnieuw Strijkijzer etc.

NB2 de aangepaste lijsten worden NIET in het betreffende werkblad vastgelegd, waarin ze zijn gemaakt, maar vormen een aanpassing op de Excel-omgeving. Ook in andere werkbladen kunnen de nieuwe reeksen dus meteen toegepast worden.

Dergelijke aangepaste lijsten zijn zeer bruikbaar: bij een bedrijf-eigen productindeling, bij waarderingscodes (denk aan de financiële waardering AAA, AA+, AA, AA-, A, BBB …) etcetera