Tagarchief: SOMMEN.ALS

Niet-aaneengesloten bereiken

2 oktober 2021ExcelBereik, CSE, Indirect, RIJ, SOM.ALS, SOMMEN.ALSGijs Verbruggen

Voorbeeldbestand: Bereiken.xlsx Download

Een niet-aaneengesloten bereik is een selectie van meerdere cellen waarbij ieder gedeelte uit één of meer cellen kan bestaan, maar waarbij die cellen niet allemaal op elkaar aansluiten.

Hieronder gaan we kijken hoe je dit soort bereiken kunt selecteren en wanneer je ze in formules wèl, maar zeker ook wanneer je ze niet kunt gebruiken. En uiteraard zoeken we ook oplossingen voor dat laatste.

Cellen selecteren

Een aaneengesloten gebied van cellen selecteren gaat het makkelijkst met behulp van het toetsenbord: gebruik de cursor-toetsen om de eerste cel te selecteren, hou Shift ingedrukt en gebruik dan weer de cursortoetsen om het hele gebied te selecteren.

Wanneer je een niet-aaneengesloten gebied wilt gebruiken dan zul je de muis ter hand moeten nemen:

selecteer de eerste cel van het bereik door met de linker muisknop te klikken
met de linkermuisknop ingedrukt kun je eventueel nog meer AANSLUITENDE cellen selecteren
hou de Ctrl-toets ingedrukt en selecteer ergens anders één of meer aaneengesloten cellen
herhaal punt 3 zoveel als nodig

Functies en niet-aaneengesloten bereiken

Op het tabblad SubTot van het Voorbeeldbestand vindt u een kwartaaloverzicht van enkele afdelingen.

In kolom G3 staat het jaartotaal van de afdeling Dir:

tik in =som(
klik met de linkermuisknop op cel C3
ga, met de muisknop ingedrukt, naar cel F3
druk op Enter

NB1 Excel vult automatisch het haakje-sluiten aan

NB2 tussen haakjes komt automatisch C3:F3, ofwel alle cellen van C3 tot en met F3

NB3 een snellere methode om van een naast-gelegen bereik de Som te bepalen: plaats de cursor in cel G3 en druk op de toetscombinatie Alt en =

Maar wanneer we alleen de cellen opgeteld willen hebben waarvan de waardes groter dan 10 zijn dan wordt het lastiger:

tik in cel H3 =som(
klik met de linkermuisknop op cel D3
ga, met de muisknop ingedrukt, naar cel E3
druk op Enter

Nu voor cel H4:

we beginnen weer met =som(
klik op cel D4
klik dan, met Ctrl ingedrukt, op cel F4
druk op Enter
het resultaat is: =SOM(D4;F4)

NB tussen haakjes staat nu een ; als scheidingsteken. Voor Excel betekent dit dat aan de functie 2 parameters worden meegegeven. In dit geval bestaat iedere parameter uit 1 cel.

Voor cel H5:

start met =som(
klik op cel C5
klik dan, met Ctrl ingedrukt, op cel E5
verschuif de cursor, nog steeds Ctrl ingedrukt, naar F5
druk op Enter
het resultaat is: =SOM(C5;E5:F5)

NB ook hier worden aan de Som-functie 2 parameters meegegeven; de eerste bestaat uit 1 cel, de tweede uit een bereik van meerdere cellen.

Op een vergelijkbare manier kun je ook het totaal van alle aantallen groter dan 10 bepalen (zie cel C9).

NB1 uiteraard is de berekening in cel C10 een stuk eenvoudiger: =SOM(H3:H7)

NB2 plaats de cursor achter de formule en je hebt een gekleurd controlemiddel om te zien of de juiste cellen zijn geselecteerd:

In cel C12 is te zien, dat ook de functie Gemiddelde met niet-aaneengesloten bereiken overweg kan.

LET OP: voor deze functie kun je NIET de kortere formule =GEMIDDELDE(H3:H7) gebruiken!

Maar …. bovenstaande formules werken alleen maar in een statische situatie. Wanneer de kwartaalcijfers nog kunnen wijzigen hebben we een probleem; dan moeten alle formules gecontroleerd en eventueel aangepast worden.

In het tabblad Aselect van het Voorbeeldbestand ziet u een formule waarbij wijzigingen in de brongegevens geen problemen meer opleveren:
=SOM.ALS(C3:F3;”>10″)

Ofwel: sommeer alle getallen in het bereik C3:F3 die groter dan 10 zijn.

LET OP de voorwaarde waaraan de cellen moeten voldoen, moet tussen aanhalingstekens staan. Wel kan de voorwaarde in een cel worden opgenomen; dan blijven de aanhalingstekens achterwege; zie cel H3: =SOM.ALS(C3:F3;$G$2)

Het bepalen van de Som of Gemiddelde van alle waardes onder een voorwaarde is op deze manier ook een stuk eenvoudiger: =GEMIDDELDE.ALS(C3:F7;”>10″)

Bij voorgaande Als-formules was het bereik waar de voorwaarde op los gelaten moest worden aaneengesloten. Wanneer dat niet het geval is hebben we een probleem!

Probleem

In het tabblad Probleem van het Voorbeeldbestand zien we een projectenoverzicht over de maanden. Per maand is er een Raming en een Actueel resultaat. Delta is het verschil tussen die twee (Actueel – Raming). Wanneer de Actuele stand kleiner is dan de Raming is dat gunstig (dus als Delta < 0 is).

NB De Delta-kolommen hebben een voorwaardelijke opmaak gekregen.

Per project willen we het totaal van de negatieve Delta’s weten.

In cel S4 staat een formule, die dit probleem zou kunnen tackelen. De Raming en Actueel-kolommen zijn immers altijd positief (of 0): =SOM.ALS(C4:R4;”<0″)

Maar als we de Correctie-kolom gaan gebruiken en daar komen negatieve waardes voor, dan werkt de formule niet meer als verwacht/gehoopt.

We zouden dan iets willen gebruiken als =SOM.ALS( (E4;I4;M4;Q4) ;”<0″ )
Dus als eerste parameter geen aaneengesloten bereik, maar losse cellen.

Helaas! Excel raakt hier de weg kwijt. Deze functie is heel strikt: de eerste parameter moet een bereik zijn, de tweede moet de voorwaarde bevatten. En de tweede parameter begint achter de eerste punt-komma.

Gelukkig kunnen we dit probleem wel oplossen met de formule in T4:
=SOMMEN.ALS(C4:R4;C4:R4;”<0″;C$3:R$3;”Delta”)
Neem de Som van alle cellen uit C4:R4 (de eerste parameter), waarbij de cellen in C4:R4 (tweede parameter) kleiner zijn dan 0 en de cellen C3:R3 de waarde Delta bevatten.

LET OP bij de cellen C3:R3 is de rij-aanduiding absoluut gemaakt (zie $-tekens), zodat er bij het naar beneden kopiëren de formule naar de juiste rij blijft verwijzen.

Wat te doen, als de kopregel niet zo’n handige aanduiding van de betreffende kolommen heeft? Dan hebben we nog een ‘leuk’ alternatief (zie cel U4):

LET OP dit is een zogenaamde CSE-, array– of matrix-formule; deze wordt niet afgesloten door op Enter te drukken maar tegelijkertijd Ctrl-Shift-Enter. Excel plaats dan automatisch de accolades rond de formule.

Korte uitleg: door de CSE-methode ‘dwingen’ we Excel om (in dit geval) 4 keer de Als-functie uit te voeren; telkens met een andere kolom door middel van de Kiezen-functie.

Functies en niet-aaneengesloten bereiken (conclusie)

Wanneer kun je niet-aaneengesloten bereiken gebruiken binnen een functie?

Kort door de bocht: alleen bij de simpele rekenkundige functies als Som, Aantal, Gemiddelde etcetera.
Bij het invoeren van dit soort functies zie je dat je meer dan 1 dezelfde soort parameter kunt opgeven.

NB de rechte haken geven aan dat de tweede parameter optioneel (niet verplicht) is. De … geven aan, dat er nog meer parameters opgegeven kunnen worden.

Bij andere functies zie je dat de verschillende parameters ieder een ‘andere’ rol vervullen. Logisch dat je dan niet een willekeurig aantal bereiken als 1 parameter kunt opgeven.

Vreemd eigenlijk: als je naar het scherm met Functieargumenten van Som kijkt (gebruik de button naast de formulebalk), dan zie je als toelichting dat je getallen als parameter kunt opgeven. Nergens een woord over bereiken!

Waarschijnlijk toch de meest gebruikte optie van Excel.

Toetje

Voor de liefhebbers bevat het Voorbeeldbestand nog enkele sheets waarmee de opzet van de ‘Probleem’-sheet op een geheel andere wijze wordt aangepakt; de basisgegevens worden in een database-vorm ingevoerd. Het resultaat-overzicht wordt daardoor een stuk flexibeler.

Grafieken verduidelijken en interactief maken

8 oktober 2020ExcelIndex, Indirect, SOMMEN.ALS, SOMPRODUCT, VergelijkenGijs Verbruggen

Voorbeeldbestand: GebGewicht.xlsx Download

“Een grafiek zegt meer dan 1000 getallen“.
Met deze parafrasering van een bekende zegswijze ben ik het meestal eens, maar soms valt de uitwerking tegen.

In dit artikel wil ik (aan de hand van cijfers over gemiddelde geboortegewichten) met enkele voorbeelden laten zien hoe je een grafiek aan duidelijkheid kunt laten winnen.

Basis-gegevens

Op zoek naar gegevens over de spreiding van gewichten van baby’s bij hun geboorte kon ik bij het CBS niet veel vinden dat bruikbaar was voor dit artikel. Wel zag ik op de website van het Nederlands Tijdschrift Voor Geneeskunde een onderzoek, waarvan de resultaten wel geschikt waren:

LET OP dit onderzoek stamt uit 1990 (over geboortes in de jaren 70-80); we weten dat het gemiddeld geboortegewicht in de loop van de tijd is gestegen. Volgens het CBS was dit gemiddelde in 2005 ongeveer 60 gram hoger dan in 1990. Wanneer we dit doortrekken zullen de gemiddelde gewichten nu zeker 100 tot 150 gram hoger uitvallen dan de cijfers in bovenstaand onderzoek.

In het tabblad Data van het Voorbeeldbestand zijn deze cijfers overgenomen:

NB1 de cijfers zijn iets anders gerubriceerd, zodat we daar makkelijker een grafiek van kunnen maken.

NB2 Week geeft de zwangerschapsduur aan, n het aantal waarnemingen in de betreffende categorie en de percentielkolommen (P₅, P₁₀ etc) dat gewicht waarbij 5%, 10% etc van de geboortegewichten kleiner zijn. Dus in de eerste regel: van de 65 meisjes (het eerste kind, geboren in de 36e week) is het gemiddelde gewicht 2486 gram, is 5% lichter dan 1790 gram en 95% lichter dan 3250 gram.

De gegevens zijn opgenomen in de Excel-tabel met de naam tblData.

Grafiek 1

Door de combinatie van 2 soorten geslacht en 2 soorten geborenen kent het onderzoek dus 4 verschillende categorieën.

Per categorie kunnen we een grafiek maken, maar in het tabblad Selectie van het Voorbeeldbestand staat een interactieve variant: afhankelijk van de keuze voor Geslacht en Eerst/later wordt de grafiek automatisch aangepast.

NB de cellen C2 en C3 zijn met behulp van Gegevensvalidatie afgeschermd, zodat alleen zinvolle invoer mogelijk is.

Op basis van deze cellen (C2 en C3) wordt een hulptabel ingevuld. Hierbij wordt de functie SOMMEN.ALS gebruikt:

in de eerste parameter wordt aangegeven waar de gegevens kunnen worden gevonden. In dit geval gebruiken we de functie INDIRECT om de juiste kolom in de tabel tblData te benaderen.
de tweede en derde parameter bepalen het eerste criterium waaraan de gegevens, die we willen ophalen, moeten voldoen (alle regels in de tabel tblData waar in de kolom Geslacht de waarde van cel C2 staat)
via het vierde en vijfde argument wordt de soort geboorte (al dan niet Eerstgeborene) geselecteerd
en het zesde en zevende argument bepalen de juiste week

NB door de 3 criteria voldoet altijd maar 1 cel uit de basis-tabel. Het resultaat is dus geen echte som, maar slechts die ene waarde.

Op basis van deze hulptabel kunnen we snel een grafiek genereren:

Maar wat zien we hier nu eigenlijk?
Met enkele aanpassingen krijgen we een grafiek die beter te begrijpen is:

NB misschien moet de betekenis van P5 etc nog toegelicht worden; dit is afhankelijk van de doelgroep.

Wat is er toegevoegd:

uiteraard een legenda
de reeks Gem is naast de P50 gezet: klik rechts in de grafiek, kies de optie Gegevens selecteren en gebruik in het vervolgscherm de pijltjes:

NB het gemiddelde en de 50-percentiel zijn in de meeste gevallen ongeveer gelijk aan elkaar
om duidelijker te maken welke lijn het gemiddelde is, heeft deze lijn markeringen gekregen
bij de horizontale as is aangegeven wat de getallen voorstellen
en misschien wel het belangrijkste: de grafiektitel, die aangeeft wat er in de grafiek staat en welke selectie daarbij is gemaakt.
In cel L17 van het tabblad Selectie wordt de tekst voor de titel gemaakt:

Tussen de &-tekens staan verwijzingen naar de cellen met de namen Geslacht (C2) en Eerst_later (C3).
Achter het woord gram staat een ‘harde return’, druk bij het invoeren op Alt-Enter.
Maak dan een willekeurige grafiektitel, klik daarin en zet in de formulebalk : =Selectie!L17
LET OP vergeet niet ook de naam van het werkblad in te voeren; je kunt ook na het intikken van het =-teken met de muis op de cel met de tekst klikken.

Om de gegevens van een pasgeborene makkelijk te kunnen vergelijken is de invoer nog uitgebreid met een geboorteweek en in de hulptabel wordt dan met Voorwaardelijke opmaak de overeenkomende regel geaccentueerd:

Op het tabblad Selectie van het Voorbeeldbestand worden ook diverse gemiddeldes berekend:

In M2 wordt het gemiddelde bepaald van alle gemiddelde gewichten uit het onderzoek.
Maar …. dit is niet juist! Per geboorteweek is het aantal baby’s niet gelijk. De betreffende gemiddelde gewichten moeten gewogen worden met deze aantallen.

Om deze ‘weging’ makkelijk te kunnen uitvoeren is aan de brongegevens een berekende kolom toegevoegd, n*Gem (zie het tabblad Data).

In cel M3 wordt op de juiste manier het gemiddelde bepaald:
=SOM(tblData[n*Gem])/SOM(tblData[n])
ofwel de som van alle waardes in de kolom n*Gem in de tabel tblData gedeeld door de som van alle waardes in de kolom n van die tabel.

NB op de CBS-site is te lezen: “In de periode 1989-1991 woog een baby gemiddeld 3 372 gram bij geboorte, in de periode 2004-2006 was dat 3 434 gram.“
Dus het gemiddelde gewicht van 1990 is ongeveer gelijk aan dat uit het onderzoek met geboortes uit 1970-1980.

Afhankelijk van de waardes in de cellen C2 (met de naam Geslacht) en C3 (met de naam Eerst_later) wordt het gemiddelde van die selectie bepaald:
=SOMMEN.ALS(tblData[n*Gem];tblData[Geslacht];Geslacht;tblData[Eerst/later];Eerst_later)/
SOMMEN.ALS(tblData[n];tblData[Geslacht];Geslacht;tblData[Eerst/later];Eerst_later)
Dus in plaats van een gewone SOM-formule gebruiken we nu SOMMEN.ALS, waarmee we criteria kunnen opgeven voor het optellen.

Op een vergelijkbare manier worden ook de gemiddeldes voor het geselecteerde geslacht en de geselecteerde soort geboorte berekend.

Wil je liever geen hulpkolom (n*Gem) gebruiken dan biedt de functie SOMPRODUCT uitkomst (zie cel N3 in het tabblad Selectie; deze functie vermenigvuldigt alle elementen uit een reeks met de overeenkomende elementen van de andere reeksen en sommeert de resultaten daarvan).
Ook de andere gemiddeldes kunnen op een vergelijkbare manier bepaald worden. In cel N4 staat bijvoorbeeld de formule:
=SOMPRODUCT(tblData[n];tblData[Gem];
1*(tblData[Geslacht]=Geslacht);
1*(tblData[Eerst/later]=Eerst_later))/
SOMPRODUCT(tblData[n];
1*(tblData[Geslacht]=Geslacht);
1*(tblData[Eerst/later]=Eerst_later))

De criteria die we willen meegeven, staan in aparte reeksen; bijvoorbeeld de kolom Geslacht wordt vergeleken met de waarde in de cel Geslacht (dit is cel C2). Dit levert een reeks op van diverse WAAR’s en ONWAAR‘s; door deze met 1 te vermenigvuldigen wordt dit een reeks 1’n en 0‘n.

Grafiek 2

Wanneer je alleen de grafiek toont (en dus niet de hulptabel) dan is het niet altijd even makkelijk om de exacte waardes af te leiden.
Daarom zijn in bovenstaande grafiek (zie het tabblad Selectie2 van het Voorbeeldbestand) ook de waardes van het gemiddelde en van de P25 en P75 opgenomen van de geselecteerde geboorte-week.
De hulptabel heeft daartoe een extra kolom (WkSel) gekregen:

NB de functie NB() levert als resultaat #N/B. Het voordeel hiervan is dat deze waarde in een grafiek niet wordt weergegeven (zie het artikel Grafiek zonder nullen).
In Excel wordt intern WAAR omgezet in de waarde 1. De reeks WkSel is aan de grafiek toegevoegd en aan de secundaire as gekoppeld. Nog een markering toevoegen aan deze ‘lijn’ en op de juiste plaats zien we een signalering.
Door ook een kolom Label toe te voegen aan de hulptabel kunnen we deze extra signalering van een Gegevenslabel voorzien. Voor de exacte implementatie, zie het tabblad Selectie2 van het Voorbeeldbestand.
Kies je nu een andere geboorteweek (cel C4) dan past de signalering zich automatisch aan:

Grafiek 3

Dit is eigenlijk geen andere grafiek, alleen de manier van selecteren is anders (makkelijker; zie het tabblad Selectie3 van het Voorbeeldbestand).

De hoofdselecties worden gemaakt met behulp van Keuzerondjes, de geboorteweek selecteer je met een Schuifbalk.

Deze voeg je toe via de menutab Ontwikkelaars. In het blok Besturingselementen kies je de optie Invoegen. Selecteer de gewenste optie binnen de Formulierbesturingselementen. Na de selectie kun je zo’n element op de gewenste plaats ‘met de muis tekenen’.

Bij Keuzerondjes is de volgende werkwijze het handigst:

teken één keuzerondje
klik rechts, verander de tekst in m, kies Besturingselement opmaken en maak een koppeling met een cel (in het voorbeeld op tabblad Selectie3 is dat N2)
tik Ctrl-C en ergens anders Ctrl-V, verander de tekst in v
voeg eventueel een groepsvak toe, die je om deze twee keuzerondjes tekent
doe hetzelfde voor het soort geborene; maak daar een koppeling met een andere cel (in het voorbeeld N3)
door een keuzerondje aan te klikken wordt er in cel N2, respectievelijk N3 de waarde 1 of 2 geplaatst; in de cellen daarnaast wordt een ‘vertaling’ gemaakt.

De Schuifbalk is eenvoudiger:

teken deze op de plaats waar je hem wilt hebben (kun je later natuurlijk nog aanpassen)
klik rechts, kies Besturingselement opmaken en maak een koppeling met een cel (in het voorbeeld op tabblad Selectie3 is dat N4).
Stel ook de minimum- (in het voorbeeld 36) en de maximumwaarde (43) in.

Grafiek 4

Met behulp van het systeem in het tabblad Vergelijken van het Voorbeeldbestand kun je snel een eigen waarneming afzetten tegen de populatie uit het onderzoek.

Voer de naam, de zwangerschapsduur (in weken) en het geboortegewicht in. In de grafiek is direct de relatieve positie tussen de percentiellijnen te zien. Door wat extra berekeningen wordt via een label ook een schatting voor de absolute positie weergegeven.

Een korte toelichting op de berekening:

zoek eerst de rij op van de geboorteweek (in cel N10 met de functie VERGELIJKEN)

met behulp van deze waarde en de functies VERGELIJKEN en VERSCHUIVING wordt in cel N11 de kolom opgezocht waarvan de score gelijk of kleiner is aan het geboortegewicht uit cel D4.
LET OP1 de derde parameter in de VERGELIJKEN-functie is weggelaten; meestal is deze parameter gelijk aan 0 (nul; dan wordt een exacte match gezocht). Vaak zal het gezochte geboortegewicht namelijk niet in de tabel voorkomen.
LET OP2 wanneer je de derde parameter weglaat (beter gezegd: als die niet gelijk is aan 0) dan moet de reeks waarin je zoekt, gesorteerd zijn.
in cel N12 bepalen we de percentiel-waarde die bij die kolom hoort
ook het bijbehorende gewicht wordt dan opgezocht in cel N13
in kolom O worden de vorige 2 stappen uitgevoerd voor een hogere percentielkolom
we interpoleren tussen de hiervoor bepaalde waardes om een schatting te krijgen voor het percentiel dat bij het opgegeven geboortegewicht hoort (zie cel O14)

Om de markering op de juiste plaats te krijgen voegen we een nieuwe kolom in de hulptabel toe met voor elke geboorteweek de waarde NB() en alleen in de betreffende geboorteweek komt het geboortegewicht (zie de kolom Verg in de hulptabel op het tabblad Vergelijken).

Net als bij de vorige grafiek moet aan de hierbij behorende ‘lijn’ een markering meegegeven worden, voordat het markeringspunt zichtbaar zal zijn. Geef deze reeks ook een label mee (zie kolom VergLabel).

Als laatste moeten nog de verticale en horizontale lijnen ingevoegd worden:

de verticale kan het makkelijkst door een nieuwe reeks te maken net als in de vorige grafiek (zie kolom VergWk in de hulptabel van het tabblad Vergelijken). Kies als grafiektype een Kolomdiagram en koppel deze aan de secundaire as.
de horizontale lijn wordt geproduceerd door een reeks toe te voegen met voor iedere week dezelfde waarde, namelijk het geboortegewicht uit cel D4.

NB het ‘systeem’ is nog niet fool-proof: vul je een hoog geboortegewicht in, dan zal het zoeken naar het overeenkomende percentiel problemen opleveren.

Kindernamen ofwel Sorteren in draaitabel

6 augustus 2020ExcelAANTALLEN.ALS, Draaitabel, lengte, Links, SOMMEN.ALS, Sorteren, Top-10Gijs Verbruggen

Voorbeeldbestand: Kindernamen.xlsx Download

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:

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.
op een vergelijkbare manier bevat cel C4 (met de naam MinJr) het laagste jaar
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.
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]))
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.
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):

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

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:
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:

klik rechts op één van de voornamen en kies de opties Sorteren en dan Meer sorteeropties
kies op het vervolgscherm Meer opties
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):

maak een draaitabel op basis van de brongegevens
plaats het Jaar in de Kolommen, de Voornaam in de Rijen en 2x het Aantal in het Waarden-gebied
om een deelselectie te kunnen maken plaatsen we JM, BegLetter en Lengte in het Filters-gebied
klik rechts op een van de getallen in de eerste Aantal-kolom, pas de naam aan (StDal)
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:

klik rechts op een van de namen en kies de optie Sorteren en dan Meer sorteeropties
klik op de button Meer opties
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.

Corona-data

27 maart 2020ExcelDraaigrafiek, Draaitabel, SOMMEN.ALS, Tekst, voorwaardelijke opmaakGijs Verbruggen

Voorbeeldbestand van 29-3: Corona.xlsx Download

Vanaf 31 maart publiceert het RIVM andere gegevens dan daarvoor. Helaas is het dus niet meer mogelijk om via het voorbeeldbestand de (ver)spreiding van Corona op een consistente manier te volgen.
Op de site https://nlcovid-19-esrinl-content.hub.arcgis.com/ zijn wel nog diverse overzichten en kaarten te vinden.

Corona: de laatste weken beheerst deze crisis niet alleen het nieuws maar ook ons leven. Het einde is nog niet in zicht.
Het zou mooi zijn als we met Excel een bijdrage zouden kunnen leveren aan de oplossing er van.
Helaas, maar wat we wel kunnen, is proberen inzicht te geven in de omvang en voortgang van de besmettingen en overlevenden.

Het RIVM publiceert dagelijkse nieuwe gegevens en ook een kaartje dat de verspreiding van de besmettingen laat zien.

Voor G-Info is dit een goede aanleiding om eens te kijken hoe we van gegevens, die we over Corona kunnen vinden, informatie kunnen maken. Niet voor niets is onze hoofddoelstelling: van Gegevens naar Informatie.

In dit artikel gaan we eerst op zoek naar gegevens rond Corona. Daarna kijken we welke informatie we daaraan kunnen ontlenen. We zullen daarom diverse (vaak met behulp van draaitabellen gemaakte) overzichten bekijken. Als laatste zult u zien dat Voorwaardelijke opmaak heel handig is om snel binnen een grote hoeveelheid gegevens de uitschieters te signaleren.

Brongegevens

In dit artikel focussen we ons op de situatie in Nederland. Het is dan ook logisch dat we terecht komen bij het RIVM. Dit instituut publiceert iedere dag een update van de situatie op hun website rivm.nl.

Op deze site hebben we vanaf de uitbraak in Nederland kunnen terugvinden hoeveel mensen er besmet zijn geraakt en hoeveel daarvan er ondertussen zijn overleden (zie het tabblad DataNed van het Voorbeeldbestand).

Deze gegevens zijn dagelijks handmatig ingevoerd in een Excel-tabel met de naam tblNed. De kolom Cum bevat een formule, die een lopend cumulatief bepaalt (in cel D9 staat bijvoorbeeld =D8+C9); op een vergelijkbare manier worden ook de 5e en 7e kolom gevuld. In de laatste kolom (Actief) berekenen we het aantal personen, dat op dit moment nog besmet is: =[@Cum]-[@CumHerst]-[@CumOvl].

NB1 het RIVM geeft aan, dat de door hen gehanteerde cijfers geen exacte waarheid weergeven:
“Het werkelijke aantal besmettingen met het nieuwe coronavirus ligt hoger dan het aantal dat hier genoemd wordt. Dit komt omdat niet iedereen met mogelijke besmetting getest wordt, maar vooral patiënten die zo ziek zijn dat ze in het ziekenhuis opgenomen worden en zorgverleners.
Het aantal gemelde patiënten en overleden patiënten kan per dag verschillen om verschillende redenen. Zo zien we dat overleden patiënten niet altijd op dezelfde dag gemeld worden.”
Voor inzicht in de verspreiding lijken ze mij echter significant genoeg.

NB2 gegevens over personen, die hersteld zijn, zijn niet bekend bij het RIVM of worden niet geregistreerd. Op de website van de Johns Hopkins University over Corona worden wel aantal vermeld. Of deze op dit moment de werkelijkheid benaderen betwijfel ik.

NB3 dit artikel is in de loop van enkele dagen geschreven. Aangezien de werkmap in die dagen continu is bijgewerkt kunnen data in de afbeeldingen afwijken van die in het Voorbeeldbestand.

NB4 boven de tabel staat een dynamische kopregel; de inhoud past zich aan aan de datums in de tabel:
=”Corona in Nederland van “&TEKST(MIN(tblNed[Datum]);”d mmm jjjj”)&” t/m “&TEKST(MAX(tblNed[Datum]);”d mmm jjjj”)
Teksten worden aan elkaar gekoppeld met behulp van het &-teken; de minimum- en maximum-datum wordt met behulp van de functie Tekst van het gewenste formaat voorzien.

Als tweede bron is dagelijks een bestand gedownload van de RIVM-site (via het pijltje naast de kaart met gemelde Corona-gevallen).

In het Voorbeeldbestand is dit geautomatiseerd via de Power Query-tabel op het tabblad DagInput. Het bestand bevat het totaal aantal bekende Corona-gevallen per gemeente. Om de resultaten van de gemeentes met elkaar te kunnen vergelijken is dit aantal genormaliseerd (de kolom Aantal per 100.000 inwoners), namelijk door te delen door het aantal inwoners van die gemeente (en met 100.000 te vermenigvuldigen).

Deze gegevens (zonder de kopregel) zijn iedere dag gekopieerd naar de Excel-tabel tblGem op het tabblad DataGem van het Voorbeeldbestand.

LET OP vanaf 27 maart bevat het bestand van het RIVM 4 namen van gemeenten, die anders gespeld worden dan daarvoor:
Bergen (L.) moet zijn: Bergen (L)
Bergen (NH.) moet zijn: Bergen (NH)
Hengelo moet zijn: Hengelo (O)
s-Gravenhage moet zijn: ‘s-Gravenhage (in de Excel-cel moet dus een dubbele ‘ komen)
De Power Query routine is hier op aangepast, anders met de hand wijzigen in de tabel tblGem.

Een ander bronbestand (zie het tabblad ProvGemeente) is een overzicht van alle gemeentes in Nederland met daarachter het inwoneraantal, een indeling naar klein, middelgroot en groot en de bijbehorende provincie.

NB De gegevens zijn ontleend aan de website van het Ministerie van Sociale Zaken en Werkgelegenheid (stand van 1 jan 2019).

Overzicht stand van zaken Nederland

Het overzicht in het tabblad DataNed van het Voorbeeldbestand laat het verloop in de tijd zien van het aantal besmettingen, overledenen en herstelden. Maar hoe de getallen zich tot elkaar verhouden is moeilijk te onderscheiden. Een grafiek is geschikter om dit te laten zien:

De eerste grafiek (staafdiagram) laat het aantal nieuwe geregistreerde besmettingen, herstelden en overledenen per dag zien (tabblad GrafNed1).
De lijngrafiek (tabblad GrafNed2) toont de cumulatieven daarvan in de tijd.
In de derde grafiek (tabblad GrafNed3) wordt door het gebruik van vlakken de opdeling van het aantal cumulatieve besmettingen zichtbaar gemaakt.

NB1 bij het schrijven van dit artikel was het aantal herstelden nog gering (volgens de gehanteerde bronnen); resultaten daarvan zijn in de grafieken dan ook nauwelijks/niet zichtbaar.

NB2 in de laatste grafiek moet het oranje vlak (de actieve besmettingen) in de loop van de tijd steeds meer de veel besproken ‘uitgevlakte’ curve laten zien. Het groene vlak (herstelden) zal steeds groter moeten worden. Een fictief voorbeeld staat hiernaast.

Overzicht per gemeente, basis

Zoals bij de bronnen aangegeven bevat het tabblad DataGem van het Voorbeeldbestand de totaal-aantallen geregistreerde besmettingen per gemeente per dag.

Om hierna diverse analyses op de gegevens uit te kunnen voeren zijn de brongegevens in deze tabel verrijkt:

in kolom F wordt de dagelijkse groei van het aantal voor een gemeente berekend:
=[@Aantal]-SOMMEN.ALS([Aantal];[Datum];[@Datum]-1;[Plaats];[@Plaats])
Trek van het Aantal in een bepaalde rij het Aantal af van de vorige dag bij die gemeente.
NB SOMMEN.ALS telt alle Aantallen op waarvan de Datum gelijk is aan de Datum in die regel minus 1 en waar de Plaats gelijk is aan de Plaats in die regel. In principe is er altijd maar 1 geval, die aan deze voorwaarden voldoet.
de indeling van de gemeente naar grootte wordt in kolom G bepaald door dat gegeven op te zoeken in tblGemProv in het tabblad ProvGemeente:
=INDEX(tblGemProv[Grootte];VERGELIJKEN([@Plaats];tblGemProv[Gemeentenaam];0))
op een vergelijkbare manier worden in de kolommen H en I respectievelijk de provincie en het aantal inwoners van een gemeente opgehaald.
de genormaliseerde aantallen per gemeente (uitgedrukt in Aantal besmettingen per 100.000 inwoners) wordt door het RIVM aangeleverd; kolom D.
Om provincies goed met elkaar te kunnen vergelijken moeten ook de aantallen per provincie worden genormaliseerd. In kolom J wordt de benodigde berekening uitgevoerd:
=[@Aantal]/ INDEX($N$5:$N$16;VERGELIJKEN([@Prov];$M$5:$M$16;0)) *100000
NB de kolommen M en N bevatten een draaitabel die het aantal inwoners per provincie bepaald.

Overzicht per provincie (1)

Waar in Nederland zitten de meeste Corona-gevallen? Met behulp van een draaitabel kunnen we snel een overzicht per provincie maken (gerangschikt van noord naar zuid; zie tabblad OvzProv1 van het Voorbeeldbestand):

Uit de bijbehorende draaigrafiek kunnen makkelijker conclusies getrokken worden (tabblad GrafProv1):

in absolute zin is het aantal besmettingen vanaf het begin (van de registratie in dit Excel-bestand, 19 maart) het grootst in Noord-Brabant, gevolgd door Noord- en Zuid-Holland en Gelderland en Limburg.
Deze laatste provincie kende een relatief grote groei op 24 maart.
De hoop dat de groei in Brabant zou gaan afvlakken zien we nog niet terug (dan zouden de lijnen daar steeds dichter bij elkaar moeten gaan liggen; op 27 maart zien we zelfs weer een groei) .
Ook neemt de groei in Noord- en Zuid-Holland toe.
In Limburg blijft de groei de laatste dagen gelijk.
De noordelijke provincies en Flevoland en Zeeland lijken nog weinig ‘geraakt’.

LET OP wanneer er gegevens voor een nieuwe datum zijn toegevoegd dan moet de draaitabel vernieuwd worden. Aangezien alle draaitabellen in deze werkmap dezelfde bron gebruiken (tblGem) worden al deze draaitabellen dan tegelijkertijd ververst.

Overzicht per provincie (2)

Wanneer we eenzelfde draaitabel sorteren van ‘hoog naar laag’ op de gegevens van de laatste dag (tabblad OvzProv2) dan ziet de bijbehorende draaigrafiek er als volgt uit (tabblad GrafProv2):

Noord- en Zuid-Holland gaan ongeveer gelijk op
Gelderland ‘haalt Limburg langzaamaan in’

Overzicht per provincie (3)

Maar als je provincies echt met elkaar wilt vergelijken moet je ook de verschillen in grootte daarbij betrekken. Op het tabblad OvzProv3 is daarom een draaitabel gemaakt van de genormaliseerde aantallen:

relatief zijn er dus in Brabant en Limburg de meeste besmettingen (toch gerelateerd aan Carnaval?)
we horen weinig over Utrecht, maar die komt in dit overzicht op de 3e plaats
Noord-Holland heeft relatief duidelijk meer besmettingen dan Zuid-Holland
de provincie Zeeland kende in het begin relatief evenveel besmettingen als Zuid-Holland; deze laatste vertoont echter in de loop van de tijd een grotere groei.
maar het virus moet ook in de ‘kleinere’ provincies niet onderschat worden

Overzicht per gemeente (1)

Voor diegene die nog wat ‘dieper willen kijken’ kunnen we op basis van de RIVM-cijfers op gemeenteniveau inzoomen. Het mooiste is natuurlijk om dit op een kaart zichtbaar te maken. De stand van 24 maart hebben we met behulp van Datawrapper.de ‘vertaald’:

*Nieuwsgierig naar details? Klik op de afbeelding.*

‘Uiteraard’ komen daarmee de grote plaatsen boven drijven. Dat is ook de reden, dat het RIVM vanaf het begin een genormaliseerde kaart heeft getoond. Dat hebben we voor 24 maart ook gedaan:

Op deze manier is beter te onderscheiden waar het virus zich vooral heeft verspreid. Naast de bekende plaatsen in Oost-Brabant zien we ook mogelijke haarden in Alphen-Chaam en Peel en Maas.

Aangezien bovenstaande methode nogal bewerkelijk is, zullen we voor een verdere detaillering gebruik maken van draaitabellen.
Op het tabblad OvzGem1 van het Voorbeeldbestand ziet u per provincie de verdeling naar gemeente. Om goed zicht te krijgen op de groei per dag kunt u (handmatig) een sortering aanbrengen; in het voorbeeld is dit op de laatste kolom uitgevoerd.

Vooral in de plaatsen aan de oost-kant van Brabant is het aantal besmettingen weer fors toegenomen. Op 27 maart kent ook Boekel weer 9 nieuwe gevallen, terwijl de dagen daarvoor de groei nul was.

NB1 in het voorbeeld hierboven zijn enkele dagen niet zichtbaar; via Beeld/Blokkeren is in cel E7 een Titelblokkering geplaatst.

NB2 wilt u een andere provincie zien? Maak een keuze in cel C2.

Overzicht per gemeente (2)

Grote steden kennen in absolute zin al snel veel besmettingen. Het effect daarvan kunnen we bekijken op het tabblad OvzGem2 van het Voorbeeldbestand.

Overzicht per gemeente (3)

Maar pieken zijn duidelijker te zien wanneer we de genormaliseerde aantallen in een draaitabel zetten (zie tabblad OvzGem3 in het Voorbeeldbestand):

In deze draaitabel is 2 keer het veld Aantal per 100.000 inwoners geplaatst.

Door rechts te klikken op de 2e kolom kunnen de Waardeveldinstellingen aangepast worden. Zoals u hiernaast kunt zien, zal Excel het Verschil met de Vorige Datum berekenen.

NB De kolomnamen zijn handmatig aangepast.

Voor allebei de waardevelden is een Voorwaardelijke opmaak ingesteld zodat uitschieters snel zichtbaar worden. Kijk in de menutab Start in het blok Stijlen bij Voorwaardelijke opmaak/Regels beheren.

LET OP wanneer er gegevens van een nieuwe datum bij zijn gekomen dan moet ook voor de nieuwe kolommen de Voorwaardelijke opmaak ingeregeld worden in de kolom Van toepassing op.

Unieke waarden

2 mei 2018ExcelAantal.als, Aantalarg, Draaitabel, Gegevensvalidatie, Namen, SOM.ALS, SOMMEN.ALS, Verschuiving, voorwaardelijke opmaakGijs Verbruggen

Voorbeeldbestand: Uniek.xlsx Download

Er bestaan nogal wat situaties, waarbij je wilt weten of items allemaal verschillend zijn of niet.
En, als ze niet allemaal verschillend zijn, welke unieke exemplaren komen er dan voor?

In Excel kennen we een dergelijk probleem ook: welke unieke waarden komen er in een rij of kolom voor?

Deze keer zullen we enkele methoden de revue laten passeren; van simpele tot complexe, van opmaak tot selectie.

Voorwaardelijke opmaak

In het Voorbeeldbestand heb ik op het tabblad Data1 een tabel opgenomen met in de kolommen oa de afdelingen en het soort bedrag.

Alle cellen in die kolommen hebben een voorwaardelijke opmaak gekregen:

selecteer cel C3
kies in de menutab Start in het blok Stijlen de optie Voorwaardelijke opmaak
klik op de optie Nieuwe regel
in het vervolgscherm kiezen we als Type: Een formule gebruiken
en de formule wordt:
=AANTAL.ALS(C$3:C3;C3)=1
ofwel: als in het bereik C$3:C3 de waarde uit cel C3 1 keer voor komt mag de cel een opmaak krijgen.
Dat is natuurlijk altijd waar!
Maar wat gebeurt er als we deze voorwaardelijke opmaak ook ‘loslaten’ op een cel lager? Excel interpreteert de formule dan als =AANTAL.ALS(C$3:C4;C4)=1 en kijkt of de waarde uit C4 slechts 1 keer voor komt in het bereik C$3:C4.
Ook wanneer deze formule in kolom D wordt toegepast is de controle precies wat we zoeken.
NB om de formule ook in andere kolommen te kunnen gebruiken is alleen de 3 absoluut gemaakt als begin van het bereik (vandaar het $-teken vóór de 3) en is niet het meer voor de hand liggende $C$3 gebruikt.
voeg nog de gewenste opmaak toe (hier is voor een licht-groene opvulling van de cel gekozen)
klik op OK
zorg dat de opmaak op alle relevante cellen van kolom C en D wordt toegepast:
* kies in de menutab Start in het blok Stijlen de optie Voorwaardelijke opmaak
* klik op de optie Regels beheren
* vul bij Van Toepassing op het gewenste bereik in (in dit geval $C$3:$D$32)

NB1 het overzicht is een Excel-tabel. Daarom zal Excel, wanneer er een regel aan wordt toegevoegd, ook de Voorwaardelijke opmaak direct meenemen; het bereik wordt automatisch aangepast.

NB2 welke unieke waarden er in een tabel-kolom zitten ziet u ook direct door op het ‘vinkje’ achter een kolomkop te klikken.

NB3 het vorige ‘recept’ kan met 1 stap ingekort worden: selecteer in de eerste stap niet één cel, maar het totale bereik waar de opmaak voor moet gelden; stap 8 kan dan vervallen.

Via draaitabel

De vorige oplossing was een optische methode om unieke waarden te vinden; bij een grote tabel niet echt handig.

Het maken van een draaitabel is echter een simpele en doeltreffende manier om snel de unieke waarden in een kolom te vinden:

selecteer een cel in de tabel met gegevens, bijvoorbeeld B2
kies in de menutab Invoegen in het blok Tabellen de optie Draaitabel
in het vervolgscherm is alles al goed ingevuld; de bron voor de draaitabel is de totale Excel-tabel (met de naam tblData1)
klik OK
sleep het veld Afd naar de Rijlabels en daar zijn alle unieke afdelingsnamen!
hetzelfde kunt ook doen voor de Soort, maar u kunt beter de vorige draaitabel uitbreiden: sleep Soort naar Kolomlabels.
Sleep ook nog Bedrag naar het Waarde-gebied en u hebt meteen een overzicht van de verdeling van de bedragen naar afdeling en soort!

Zie ook het tabblad Data1 in het Voorbeeldbestand.

Even een uitstapje: wilt u weten wat het totaal van Srt1 is dan kunt u ook de volgende formule gebruiken: =SOM.ALS(tblData1[Soort];”Srt1″;tblData1[Bedrag]) maar beter is het volgende:

plaats in een bepaalde cel de waarde Srt1, bijvoorbeeld in G14 (zie het tabblad Data1 in het Voorbeeldbestand)
in de cel daarnaast gaan we een formule opbouwen:
tik in =som.als(
wijs met de cursor de bovenkant van de kolom Afd aan (de cursor wordt dan een zwarte pijl naar beneden) en klik.
De formule wordt dan aangevuld met tblData1[Soort] ofwel alle cellen in de kolom Soort van de Excel-tabel tblData1.
tik in ; (de punt-komma) en klik op cel G14 en plaats weer een ;
als laatste voegen we de kolom Bedrag toe, die onder voorwaarden gesommeerd moet worden; dat gaat op dezelfde manier als in stap 3.
nog een haakje sluiten en Enter

Door in G14 een andere soort in te typen, krijgt u daarvan het totaal.

Maar wat als er aan 2 (of meer voorwaarden moet worden voldaan?

U moet dan de functie SOMMEN.ALS gebruiken.
Deze werkt net iets anders: eerst geeft u het bereik op, waarvan u de som wilt bepalen, dan het eerste criterium-bereik met daarachter het criterium zelf, daarna een nieuw criterium-bereik met criterium etc.

Gegevensvalidatie

Maar in G14 kunnen nu willekeurige teksten worden ingevoerd. Dat is natuurlijk niet de bedoeling; we willen alleen bestaande soorten kunnen opgeven.

Dit gaat eenvoudig met gegevens-validatie:

plaats de cursor in de cel die u wilt valideren (bijvoorbeeld cel G19 zoals in het tabblad Data1)
kies in de menutab Gegevens in het blok Hulpmiddelen voor gegevens de optie Gegevensvalidatie
kies bij Toestaan: de optie Lijst
en bij Bron: kiest u de kolom Soort uit de Excel-tabel op de manier zoals hierboven aangegeven.
NB Excel vertaalt de kolom direct naar daadwerkelijke cellen, maar als de tabel groter (of kleiner wordt) verandert dit bereik mee.
klik op OK.

Oeps, het resultaat is niet helemaal wat we hoopten. In de keuzelijst staan alle voorkomens uit de kolom Soort, we willen natuurlijk alleen unieke waarden hebben!

Unieke waarden via filtering

Om een lijstje te maken van unieke waarden (maar u wilt geen draaitabel gebruiken) dan kent Excel nog een andere aanpak:

kies in de menutab Gegevens in het blok Sorteren en filteren de optie Geavanceerd
in het Uitgebreid filter kiezen we als Actie de optie Kopiëren naar andere locatie
voor het Lijstbereik selecteert u alle gegevens uit de kolom Afd van de tabel, INCLUSIEF de kop
bij Kopiëren naar vult u de cel in, waar de unieke waarden moeten komen (in het voorbeeld heeft deze cel de naam AfdKop gekregen)
vergeet niet het vinkje te plaatsen bij Unieke records!

In het tabblad Data1 van het Voorbeeldbestand ziet u het resultaat van deze actie.

Ook voor de kolom Soort zijn de unieke waarden opgehaald.

NB1 Wat direct opvalt is dat er geen (automatische) sortering plaats vindt. De volgorde is gelijk aan de volgorde in de bron.

NB2 als er regels aan de bron-tabel worden toegevoegd dan moeten de ophaal-acties opnieuw worden uitgevoerd.
Excel ‘onthoudt’ de gegevens van de laatste actie. Dit is ook te zien in het overzicht Namen beheren in de menutab Formules.

LET OP1 de locatie, waar de unieke waarden moeten komen, moet op hetzelfde tabblad staan als waar de brongegevens zich bevinden.

LET OP2 vergeet niet om bij het Uitgebreid filter ook de kop van de kolom mee te nemen, anders krijgt u de eerste waarde dubbel. Dit is nergens in de Microsoft-documentatie terug te vinden!

Gegevensvalidatie

Op basis van deze unieke waarden kunnen nu makkelijk Gegevensvalidaties worden gemaakt.
Maar om het geheel dynamisch te houden (als er nieuwe unieke waarden bijkomen moet dit automatisch worden meegenomen bij de gegevensvalidatie) wordt als bron-lijst niet een hard bereik genomen (bijvoorbeeld R3:R5).

Nee, we creëren een dynamisch bereik door een naam toe te voegen:

kies in de menutab Formules in het blok Gedefinieerde namen de optie Naam definiëren
vul een naam in (bijvoorbeeld AfdUniek)
en plaats de formule
=VERSCHUIVING(AfdKop;1;0;AANTALARG(Data1!$R:$R)-1)
in het vak Verwijst naar.

Bij gegevensvalidatie (zie cel G20 in het tabblad Data1 van het Voorbeeldbestand) gebruikt u als Bron de naam AfdUniek (vergeet niet het =-teken daar voor te zetten!).

In de cellen G21 en H21 is op een vergelijkbare manier gegevensvalidatie toegepast, maar daarbij zijn als bron voor de verschuiving 2 simpele draaitabellen gebruikt.

LET OP worden er regels aan de oorspronkelijke tabel toegevoegd, vergeet dan niet de draaitabellen te vernieuwen anders bevatten de gegevensvalidaties niet de meest recente gegevens.