Tagarchief: lengte

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.


Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd?


Cel & Info

Naar aanleiding van een vraag van een collega (“Kan ik de naam van een tabblad in een cel weergeven?“) kwam ik 2 ondergewaardeerde(?) functies van Excel tegen: CEL en INFO.

Ik dacht, dat het antwoord op de vraag mbv INFO zou kunnen worden gevonden, maar dat was niet het geval; wel met CEL.
Dus maar eens even de 2 functies onder de loep nemen en kijken wat de mogelijkheden en verschillen zijn.

Functie INFO

Deze functie retourneert informatie over de huidige besturingsomgeving.
De formule =INFO(“versie-excel”) bijvoorbeeld levert op mijn PC  als resultaat 14.0.

LET OP: de gewenste informatie (in het voorbeeld excel-versie) moet tussen aanhalingstekens staan, anders denkt Excel dat je een verwijzing naar een zelf-gedefinieerde naam meegeeft.

INFO() kan 7 verschillende soorten informatie genereren:

  1. map: de laatst gebruikte/actieve directory (dus niet persé de directory waarin de onderliggende werkmap wordt weggeschreven!!)
  2. aantal_bladen: aantal werkbladen, die op dit moment binnen Excel gebruikt worden (al dan niet zichtbaar; ook bladen in verborgen werkmappen worden meegeteld)
  3. begin: geeft aan welke cel linksboven op dit moment zichtbaar is (altijd voorafgegaan door $A:)
  4. versie_besturing: onder welke versie van het  besturingssysteem wordt Excel gebruikt
  5. herberekening: staat Excel in de Automatische of Handmatige stand? (aan te passen via Formules/Berekeningsopties)
  6. versie_excel
  7. systeem: naam van de besturingsomgeving; Macintosh = ‘mac’ en Windows = ‘pcdos’

LET OP: in eerdere versies van Excel was het ook mogelijk om informatie over het geheugen op te vragen ( “beschikbaar_geheugen”, “geheugen_gebruikt” en “totaalgeheugen”); deze opties worden niet meer ondersteund.

De 5e optie (herberekening) is handig bij het gebruik van grote spreadsheets. Wanneer de rekentijd exorbitant groot wordt, is het gebruikelijk om tussentijds herberekening op Handmatig te zetten. Om te voorkomen dat bij het beoordelen van de resultaten naar ‘oude’ uitkomsten wordt gekeken, kun je de status van herberekening op deze manier bij de resultaten weergeven.
In het Voorbeeldbestand is via voorwaardelijke opmaak de status van herberekening duidelijk te onderscheiden.

Voor de overige opties zie ik weinig toepassingsmogelijkheden in de praktijk. Graag een reactie, wanneer u wel een zinvolle toepassing hiervoor heeft.

Functie CEL

Waar de functie INFO() informatie over de besturingsomgeving levert, geeft de functie CEL() u inzicht in de opmaak, locatie of inhoud van een cel.
De formule =CEL(“bescherming”;A1) bijvoorbeeld geeft de waarde 0 als cel A1 niet is vergrendeld en 1 als de cel wel is vergrendeld.

Bij deze functie kent Excel 12 opties:

  1. adres: de verwijzing naar de betreffende cel
  2. kolom: kolomnummer van de cel
  3. kleur: de waarde 1 als de cel kleuropmaak voor negatieve waarden bevat, anders is het resultaat 0 (nul)
  4. inhoud: de inhoud van de cel
  5. bestandsnaam: de bestandsnaam (met het volledige pad en naam van het werkblad) van het bestand dat de cel bevat; deze is leeg als het bestand nog niet is opgeslagen
  6. notatie: code voor de getalnotatie van de cel. De standaardnotatie levert een S, een datum voor D1 etc. Experimenteer met de mogelijkheden.
  7. haakjes: de waarde 1 als de cel een notatie bevat waarmee positieve waarden of alle waarden tussen haakjes worden geplaatst; anders is het resultaat 0
  8. voorvoegsel: geeft als resultaat een enkel aanhalingsteken (‘) als de cel links uitgelijnde tekst bevat, een dubbel aanhalingsteken (“) als de cel rechts uitgelijnde tekst bevat, een caret (^) als de cel gecentreerde tekst bevat, een backslash (\) als de cel uitgevulde tekst bevat en lege tekst (“”) als de cel iets anders bevat
  9. bescherming: de waarde 0 als de cel niet is vergrendeld en 1 als de cel wel is vergrendeld
  10. rij: rijnummer van de cel
  11. type: geeft als resultaat “g” (leeg) als de cel leeg is, “l” (label) als de cel een tekstconstante bevat en “w” (waarde) als de cel iets anders bevat
  12. breedte: de kolombreedte van de cel, afgerond op een geheCel Infoel getal

In het Voorbeeldbestand op het tabblad Cel2 ziet u wat de diverse opties opleveren (alle formules ‘kijken’ naar cel C2).

LET OP: in de gebruikte formules (bijvoorbeeld in cel C7 staat =CEL(B7;$C$2)) worden geen aanhalingstekens gebruikt, aangezien naar de inhoud van cellen wordt verwezen (hier B7).

LET OP: als het argument Verw een cellenbereik is, haalt de functie CEL alleen de informatie op voor de cel linksboven in het bereik.

Veel van bovenstaande opties kunnen worden gebruikt bij het controleren, beveiligen en fool-proof maken van spreadsheets.

Zoals u ziet geeft de functie CEL() niet direct een antwoord op de vraag van mijn collega (wat is de naam van het tabblad), maar die kunnen we via het type bestandsnaam wel afleiden:
=RECHTS(CEL(“bestandsnaam”;A1);LENGTE(CEL(“bestandsnaam”;A1))-VIND.ALLES(“]”;CEL(“bestandsnaam”;A1)))

Wanneer we =CEL(“bestandsnaam”;A1) gebruiken, zien we dat de naam van het tabblad wordt vooraf gegaan door een ] (rechte sluithaak).
Via VIND.ALLES zoeken we de positie van dit teken op. Alles tot en met die positie kunnen we missen, oftewel alles rechts daarvan hebben we nodig. Hoe lang is dat gedeelte dat we nodig hebben? De oorspronkelijke lengte minus de positie van het ]-teken. Simpel toch?

Functie CEL (2e versie)

In de voorbeelden op het tabblad Cel2 hebben we gebruik gemaakt van de uitgebreide versie van de CEL-functie (ook de meest gebruikte en gangbare).

Bij het gebruik van de functie kun je echter ook de verwijzing naar een cel weglaten. Excel zal dan alle informatie, zoals hiervoor besproken, laten zien van de laatst gebruikte cel. Op deze manier is het mogelijk om te volgen welke activiteit en op welke plaats de vorige handeling in Excel is geweest (soms moet er eerst een herberekening plaats vinden, via de F9-toets, voordat het resultaat zichtbaar is).

Experimenteer met de mogelijkheden met behulp van het tabblad Cel1 in het voorbeeldbestand.


Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd: