Tagarchief: SOM.ALS

Niet-aaneengesloten bereiken

Excel, , , , ,
Voorbeeldbestand: Bereiken.xlsxDownload

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:

  1. selecteer de eerste cel van het bereik door met de linker muisknop te klikken
  2. met de linkermuisknop ingedrukt kun je eventueel nog meer AANSLUITENDE cellen selecteren
  3. hou de Ctrl-toets ingedrukt en selecteer ergens anders één of meer aaneengesloten cellen
  4. 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:

  1. tik in =som(
  2. klik met de linkermuisknop op cel C3
  3. ga, met de muisknop ingedrukt, naar cel F3
  4. 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:

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

Nu voor cel H4:

  1. we beginnen weer met =som(
  2. klik op cel D4
  3. klik dan, met Ctrl ingedrukt, op cel F4
  4. druk op Enter
  5. 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:

  1. start met =som(
  2. klik op cel C5
  3. klik dan, met Ctrl ingedrukt, op cel E5
  4. verschuif de cursor, nog steeds Ctrl ingedrukt, naar F5
  5. druk op Enter
  6. 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.

Tour de France 2020

Excel, , , , , ,
Voorbeeldbestand: Tour2020.xlsxDownload

De Tour de France: ieder jaar kijk ik er weer naar uit, deze keer wat langer dan anders!
Een artikel op de site van G-Info met de Tour-gegevens als basis mag dan ook niet ontbreken.

Overal vind je wel standen en overzichten, dus dat gaan we niet overdoen. Het leek me wel aardig om te kijken of we een overzicht kunnen maken van de meest constant-presterende renners.
We zullen daarbij allerlei manieren van tellen en zoeken gaan gebruiken, variërend van de functie AANTAL.ALS, de combinatie van Index en Vergelijken tot het gebruik van Gegevensvalidatie en Keuzelijsten.

Doel

Dit jaar gaat de Tour al direct los: de eerste dagen moeten veel ‘heuvels’ en bergen bedwongen worden (zie het tabblad Etappes in het Voorbeeldbestand). De sprinters hebben dan niet al te veel kans. Misschien vallen er zelfs al renners uit die categorie uit voordat ze aan een massa-sprint kunnen beginnen.

Daarom gaan we in dit artikel eens kijken welke renners zich het meest in de top-10 laten zien: dat noemen we dan maar de meest-constante renners.

Bron-gegevens

Etappes

Onder andere op www.touretappe.nl kun je een overzicht van de etappes vinden. Op het tabblad Etappes van het Voorbeeldbestand heb ik die overgenomen. Alle etappes zijn via de optie Koppeling (rechts klikken op een cel) aan een pagina van die site gelinked, zodat de details van een etappe direct zijn te vinden.
Met behulp van voorwaardelijke opmaak zijn de soorten etappes zichtbaar gemaakt. Onderaan wordt het aantal per soort geteld. In cel H26 staat daartoe de formule:
=AANTAL.ALS(tblEtappes[Type];G26)
Tel het Aantal Als in de kolom Type van de Excel-tabel tblEtappes de waarde uit cel G26 (hier Bergen) voor komt.
Van de 21 etappes zijn er dus 9 als berg-etappe gekwalificeerd!

Teams en renners

Op het tabblad Teams van het Voorbeeldbestand staat de definitieve deelnemerslijst (in een Excel-tabel tblTeams) zoals die op de site wielerflits.nl is terug te vinden. Op die site zijn de ploegen en renners voorzien van een landen-vlaggetje; bij het plakken in Excel wordt dit vertaald naar een code. Die kunnen we goed gebruiken om ons overzicht te verrijken met echte landnamen.

Aan de tabel tblTeams zijn daarom 2 kolommen toegevoegd:

  • Nr: ieder team en renner krijgt een nummer: het team van de vorige winnaar heeft nummer 0, de kopman van dat team krijgt nummer 1 en de overige renners krijgen hun nummer in alfabetische volgorde.

NB1 is Dumoulin bijgelovig? Hij heeft nummer 13 geruild met de Noor Grøndahl Jansen.

NB2 de kolom Nr kun je handigst op de volgende manier vullen: de eerste cel (D6) krijgt nummer 0 en in de cel daaronder plaatsen we de formule =D6+1. Deze formule doorvoeren naar alle cellen daaronder. Wis dan in de lege regels de cel in kolom D en vul bij het team het volgende tiental in (Jumbo krijgt dan nummer 10, BORA 20 etc).

  • Land: aan de hand van de vlagcode uit de eerste kolom bepalen we uit welk land het team of renner komt (zie tabblad Landen). Dat zou met VERT.ZOEKEN kunnen, maar we gebruiken liever de universeel toepasbare INDEX-VERGELIJKEN-methode (zie het artikel Zoeken: index en vergelijken, inclusief de avz-truc).

Landen

Aan iedere unieke VlagCd uit het tabblad Teams hebben we een land-omschrijving gekoppeld (in de Excel-tabel tblLand van het tabblad Landen in het Voorbeeldbestand).

In de derde kolom van die tabel (AantRenners) bepalen we het aantal renners per land: =AANTAL.ALS(tblRenners[Land];[@Land])
Turf het Aantal Als in de kolom Land van de tabel tblRenners de waarde uit de kolom Land in deze regel (vandaar de @) voor komt.

NB de tabel tblRenners is terug te vinden op het tabblad Renners van het Voorbeeldbestand; zie hierna.

Punten

Op het tabblad Punten van het Voorbeeldbestand hebben we vastgelegd hoe de puntenverdeling voor de eerste 10 renners van iedere etappe moet zijn.

NB1 mocht het eindresultaat straks niet bevallen, dan kunt u natuurlijk proberen uw favoriete renner te helpen door de puntenverdeling aan te passen 😉

NB2 een totaal-regel onder een Excel-tabel wordt automatisch gegenereerd als de betreffende optie is aangevinkt op de menutab Ontwerpen.

Uitslagen

Uitslag 2e etappe op letour.fr

De uitslagen verwerken is heel eenvoudig: vul van de eerste 10 renners hun rugnummers in bij de betreffende etappe (zie het tabblad Uitslagen van het Voorbeeldbestand).
Op de officiële tour-site www.letour.fr kun je die rugnummers in de uitslagen vinden.

Maar wat als je alleen maar de namen hebt?
(Er zijn waarschijnlijk nog wel meer mensen die dan meteen aan Theo Koomen, of was het Barend Barendse, moeten denken: “Aan namen heb ik niks. Rugnummers moet ik hebben“).
In Excel zijn er dan allerlei opties om het rugnummer te vinden. Hier komen er een paar:

  1. ga naar het tabblad Teams van het Voorbeeldbestand, druk in Ctrl-F, tik een gedeelte van de naam in en klik op Alles zoeken. In het onderste gedeelte van het zoek-scherm komen alle cellen die voldoen.

    Klik op de gewenste naam en u ziet het rugnummer daarnaast staan.
  2. gebruik Index en Vergelijken:

    In cel O2 van het tabblad Uitslagen wordt eerst met behulp van de functie Vergelijken gekeken op welke positie in de kolom Naam van de tabel tblTeams de invoer in cel H2 staat. Deze functie kent zogenaamde ‘wildcards’, dus we hoeven maar een gedeelte van de naam in te tikken (de *’s geven aan dat het er niet toe doet, wat er voor en achter de inhoud van cel H2 staat). Daarna wordt deze positie gebruikt om met behulp van de functie Index het betreffende Nr op te halen.
    Ter controle halen we in cel P2 op een vergelijkbare manier de naam op die hoort bij het rugnummer.
    LET OP Vergelijken geeft de eerste positie terug waarvan de naam voldoet aan de voorwaarde. Is het niet de juiste naam? Tik meer letters in, bijvoorbeeld daniel f om de renner met nummer 76 op te zoeken.
  3. denk je het rugnummer wel ongeveer te weten omdat je het team kent en je weet welk tiental bij deze ploeg hoort:

    De ploeg van Jumbo-Visma begint met renner 11, Dumoulin zit vooraan in het alfabet (en hij is geen kopman!), dus zal het wel 13, 14 of 15 zijn.
    Tik het nummer in in cel O4 en je ziet of je goed hebt gegokt.
  4. Via de menutab Gegevens in het blok Hulpmiddelen voor gegevens is aan cel H6 een Gegevensvalidatie toegewezen:

    Alleen gegevens uit kolom G van het tabblad Teams zijn toegestaan.
    In die kolom G staat voor iedere renner (en team) een koppeling van nummer en naam met een extra spatie daartussen:

    NB kolom G is standaard niet zichtbaar; via Groeperen kan de kolom ‘ingeklapt’ worden.
    LET OP je kunt een kolom ook Verbergen (via rechtsklikken op een kolomletter) maar ik ben daar geen voorstander van: het zichtbaar maken is niet zo makkelijk en vaak zie je niet dat er een kolom verborgen is.
  1. een andere, minder gebruikte, optie is een keuzelijst (met invoervak).
    Kies in de menutab Ontwikkelaars in het blok Besturingselementen de optie Invoegen.

Klik op de 2e optie binnen de Formulierbesturingselementen.

‘Teken’ nu met de cursor het gebied waar de keuzelijst moet komen.

Dan komt de vraag om het besturingselement op te maken: zorg dat in het Invoerbereik de cellen geselecteerd worden met de namen van de renners en dat er een Koppeling komt met de cel naast het invoervak (zie het tabblad Uitslagen in het Voorbeeldbestand).

Wanneer je nu een naam selecteert dan komt in de gekoppelde cel de positie van deze renner in de lijst te voorschijn. Met behulp van de formule =INDEX(tblTeams[Nr];Uitslagen!N8) wordt het rugnummer opgehaald.

  1. een andere keuzelijst maakt gebruik van Active-X; iets ingewikkelder maar wel een stuk flexibeler.

Kies opnieuw in de menutab Ontwikkelaars in het blok Besturingselementen de optie Invoegen. Maar, let op, klik dan op de 2e optie binnen de Active-X besturingselementen.
‘Teken’ weer met de cursor het gebied waar de keuzelijst moet komen. Nu moet je de Eigenschappen aanpassen: klik op de betreffende button in de menubalk en vul de 4 eigenschappen in zoals hiernaast (achter de pijltjes).

LET OP bij het gebruik van Active-X-elementen moet je de Ontwerpmodus uitzetten, wanneer je deze wilt gebruiken (en andersom als je de eigenschappen wilt aanpassen).

Resultaten per renner

In het tabblad Renners van het Voorbeeldbestand worden de resultaten per renner ‘automatisch’ bepaald; alleen de kolom Nr bevat harde waarden, de overigen worden afgeleid of berekend:

  1. in cel H6 staat de formule:
    =ALS.FOUT(
    INDEX(tblPunten[Punten];
    VERGELIJKEN([@Nr];INDIRECT(“tblUitslagen[“&H$4&”]”);0));
    “”)
    Aangezien cel H4 de waarde 1 bevat, wordt de 2e parameter binnen de Vergelijken-functie INDIRECT(“tblUitslagen[1]”); Excel vertaalt dit dan naar een bereik van cellen en wel de eerste kolom in de tabel tblUitslagen.
    De Vergelijken-functie kijkt dan of het rugnummer in die kolom voorkomt. De positie daarvan (1 tot 10) wordt gebruikt om met behulp van de functie Index het daarbij behorende aantal punten te genereren. Als een renner geen top-10-resultaat in een etappe heeft behaald, dan zou er een foutmelding komen; met de functie Als.Fout zorgen we er voor dat in dat geval de cel gevuld wordt met een lege tekst.
    Deze formule kan naar beneden en rechts gekopieerd, zodat voor alle renners voor alle etappes de resultaten worden bepaald.
  2. in de kolommen Naam en Land worden de gegevens opgehaald uit het tabblad Teams
  3. zo ook voor de kolom Team, behalve dat daarvoor een berekening rond het rugnummer plaats vindt:
    =INDEX(tblTeams[Naam];
    VERGELIJKEN(AFRONDEN.BENEDEN([@Nr];10);tblTeams[Nr];0)
    )
    Het rugnummer wordt dus naar beneden afgerond op het dichtstbijzijnde veelvoud van 10.
  4. in de kolom TotaalPunten wordt het totaal van de renner over alle etappes berekend: =SOM(tblRenners[@[Etap1]:[Etap21]])
  5. Dan blijft er nog 1 kolom over: Rang.
    Via de formule =RANG.GELIJK([@TotaalPunten];[TotaalPunten]) wordt in die kolom per renner de rangorde in het totaal bepaald.

Kies met het driehoekje achter Rang de gewenste sortering en u weet welke renner(s) bovenaan staat/staan.

LET OP wanneer er weer nieuwe uitslagen zijn toegevoegd, worden alle formules automatisch herberekend, maar …. de sortering wordt niet vanzelf aangepast. Die moet u zelf nogmaals uitvoeren.

Resultaten per team

In het tabblad Teams van het Voorbeeldbestand wordt op de ondertussen bekende manier per renner de TotaalPunten van die renner opgehaald. Het totaal per team berekenen we met een gewone SOM-formule.

In datzelfde tabblad staat ook een ranglijst van de teams. De formules daarin mogen geen verrassing meer zijn.

Boven die tabel staat een controlegetal: het totaal aantal punten van alle renners gedeeld door het totaal aantal dat per etappe verdiend kan worden. Dit moet een geheel getal zijn. Met voorwaardelijke opmaak krijgt de cel een kleur.

Resultaten per land

Om het totaal aantal punten per land te bepalen gebruiken we op het tabblad Landen van het Voorbeeldbestand de formule:
=SOM.ALS(tblRenners[Land];[@Land];tblRenners[TotaalPunten])

LET OP gebruik de gegevens van tblRenners en niet van tblTeams anders worden ook de totalen van de teams meegeteld.

Frankrijk heeft de meeste renners rond rijden, logisch (?) dat dit land dan bovenaan staat.

We delen het aantal punten door het aantal renners per land en we krijgen een andere ranglijst.

Resultaten per land en team

In het tabblad OvzLandTeam van het Voorbeeldbestand staat een draaitabel op basis van de tabel tblRenners. En de rijen én de kolommen worden daarin automatisch gesorteerd (zie ook het artikel Kindernamen).

Bovenin ziet u ook weer een controlegetal; als de uitslagen compleet zijn ingevuld zal dit een geheel getal zijn.

Genormeerde resultaten per land

Het tabblad OvzLandTeam van het Voorbeeldbestand bevat ook een draaitabel, nu op basis van de tabel tblLand.

Per land wordt het aantal renners geteld met daarnaast het aantal genormeerde punten (ofwel het totaal aantal punten gedeeld door het aantal renners).

NB in het hele Tour de France-systeem worden alle overzichten direct geactualiseerd na invoer van een uitslag, omdat die allemaal met formules zijn opgebouwd. Dat geldt niet voor de 2 Ovz-tabbladen: dat zijn draaitabellen en die moeten na het opvoeren van nieuwe uitslagen handmatig Vernieuwd worden (met de muis rechtsklikken op een cel in de draaitabel).

LET OP allebei de draaitabellen dienen Vernieuwd te worden aangezien ze op verschillende bronnen zijn gebaseerd.

Absoluut en relatief in Excel-tabellen

Excel, , ,
Voorbeeldbestand: TabelRef.xlsxDownload

Absolute en relatieve verwijzingen in Excel: dat blijft altijd even opletten!
Maar sinds we met zijn allen steeds meer gebruik maken van Excel-tabellen komt daar ineens een struikelblok(je) bij.

In dit artikel kijken we eerst in algemene zin naar de implementatie van tabel-verwijzingen (Gestructureerde verwijzingen of Structured references in Microsoft-taal); daarna komt een methode langs hoe we ook tabel-verwijzingen absoluut kunnen maken.

Voorbeeld-bestand

In het Voorbeeldbestand staat in het tabblad CelRef een overzicht van bedragen per kwartaal en regio.

Er loopt blijkbaar een afspraak dat, als het bedrag groter is dan 100, er dan een korting van 10% wordt gegeven.
De daarbij gehanteerde formule is:
=ALS(D3>100;10%;0)

Ofwel (geredeneerd vanuit cel E3) als de waarde in cel D3 groter is dan 100, dan komt in E3 de waarde 0,1 anders 0.

NB 10% is voor Excel slechts een opmaak van een numerieke waarde; intern wordt gerekend met 0,1.

De formule is F3 (het bedrag inclusief korting) kan dan zijn:
=D3*(1-E3)

Allebei de formules kunnen naar beneden gekopieerd worden; dat gaat het makkelijkst door dubbel te klikken op het vierkantje rechtsonder aan de rand van een cel (de vulgreep) als die cel is geselecteerd.

Aangezien alle cel-verwijzingen relatief zijn (er staan nergens $-tekens bij de cellen D3 en E3) zullen de formules zich automatisch aanpassen: D3 wordt D4 etc.

LET OP om kolom F niet te breed te maken is de tekst in cel F2 gescheiden door een harde return (Alt-Enter). Een kop nooit over 2 cellen verdelen; altijd een harde return gebruiken.

Willen we weten hoe de verdeling over de kwartalen en/of de regio’s is, dan ligt het maken van een draaitabel voor de hand.

LET OP een bug in Excel kan er bij het aanpassen van de opmaak van de data voor zorgen dat de kop die met een harde return is gescheiden, inhoudelijk wijzigt (alles vanaf de harde return wordt verwijderd); hier is dan een handmatige aanpassing nodig.

Tabel-verwijzingen

Wanneer we dezelfde gegevens in een Excel-tabel opnemen (zie het tabblad TablRef van het Voorbeeldbestand) dan gaat Excel bij het maken van de kortingsformules automatisch met gestructureerde verwijzingen werken in plaats van celverwijzingen:

  1. selecteer cel E3
  2. tik in =als(
  3. klik met de muis in cel D3; Excel plaatst automatisch de tekst [@Bedrag] in de formule
  4. maak de formule af en druk op Enter
  5. Excel zal automatisch de formule naar de overige cellen van dezelfde kolom kopiëren.

Gelukkig hoeven we de gestructureerde verwijzingen dus niet zelf te typen; de notatie daarvan is niet altijd even eenvoudig.
De vierkante haken om de diverse onderdelen van een formule maken voor Excel het verschil tussen gestructureerde en cel-verwijzingen.
Het ‘apestaartje’ (@) voor een kolomnaam betekent dat de waarde uit dezelfde rij uit de betreffende kolom wordt opgehaald.

LET OP in cel F3 staat de formule =[@Bedrag]*(1-[@[Korting%]]); aangezien het %-teken in Excel diverse betekenissen kan hebben zijn nog eens extra vierkante haken om de kolomnaam geplaatst.
Andere speciale tekens die eenzelfde behandeling nodig hebben:
tab, nieuwe regel, Enter-teken, komma (,), dubbele punt (:), punt (.), vierkante haak openen ([), vierkante haak sluiten (]), hekje (#), enkel aanhalingsteken (‘), dubbel aanhalingsteken (“), accolade openen ({), accolade sluiten (}), dollarteken ($), accent circonflexe (^), en-teken (&), sterretje (*), plusteken (+), gelijkteken (=), minteken (-), groter dan (>), kleiner dan (<) en het deelteken (/).

Totalen

Aan een tabel kunnen snel totalen toegevoegd worden:

  1. klik op een cel van de tabel
  2. kies in de menutab Hulpmiddelen voor tabellen de optie Ontwerpen
  3. en vink de optie Totaalrij aan
  4. in de nieuwe regel onderaan de tabel kan dan per kolom het soort totalen gekozen worden (Som, Aantal, Gemiddelde etc.)

Een draaitabel die gebaseerd is op een tabel met totalen, zal deze totalen buiten beschouwing laten (gelukkig!); zie tabblad TablRefTot van het Voorbeeldbestand.

Externe verwijzing naar een tabel

Wanneer u een bepaald overzicht wilt genereren en geen gebruik wilt/kunt maken van draaitabellen dan kunt u via externe verwijzingen gebruik maken van de tabel-gegevens:

  1. selecteer cel J21 in het tabblad TablRefTot
  2. tik in =som.als(
  3. wijs met de muis de bovenkant van de kolom Regio van de tabel daarnaast aan (de cursor wordt dan een pijltje naar beneden) en klik; Excel plaats automatisch de tekst tblKwReg2[Regio] in de formule
  4. tik in ;J$20; en voeg op dezelfde manier de kolom Bedrag toe

NB omdat het verwijzingen buiten de tabel zijn plaatst Excel automatisch de naam van de betreffende tabel voor de rechte haken.

Door de formule nu naar rechts te kopiëren krijgen we ook het totaal-bedrag van regio Zuid te zien. Tenminste: als we op de juiste manier kopiëren!
Selecteer cel J21, klik op Ctrl-C, selecteer cel K21 en klik Ctrl-V en alles gaat goed.
Maar wanneer de vulgreep rechtsonder cel J21 wordt gebruikt, gaat het mis! Dan wordt de verwijzing naar Regio ineens Bedrag en Bedrag wordt Korting%!
DUS: met kopiëren/plakken worden de externe verwijzingen als absoluut beschouwd, maar gebruiken we de vulgreep dan ziet Excel de verwijzingen als relatief!

Ditzelfde effect zien we ook als we verwijzingen naar de tabellen-koppen maken (niet de bovenkant van een kolom aanklikken, maar de cel zelf) en die kopiëren (zie cellen I34 tot en met K35 van het tabblad TablRefTot van het Voorbeeldbestand).

NB wanneer gegevens aan de tabel worden toegevoegd (selecteer cel F32 en druk op Tab; de totalen schuiven automatisch naar beneden) zullen alle overzichten automatisch de nieuwe gegevens meenemen (wel nog via rechtsklikken de draaitabel Vernieuwen).
Dit geldt ook voor formules die naar de totaalrijen verwijzen.

Absoluut en relatief in gestructureerde verwijzingen

In het tabblad TablRefTot2 van het Voorbeeldbestand is een resultaat-overzicht per regio en kwartaal opgenomen. Dit overzicht is zelf ook weer een tabel (met de naam tblResult). Om dit te realiseren wordt de functie SOMMEN.ALS gebruikt.

LET OP de syntax is echt anders dan van SOM.ALS.

De formule in cel J26 berekent de som van die bedragen uit de kolom Bedrag van de tabel tblKwReg2b, waarbij de Regio-kolom van die tabel de koptekst van de kolom Noord van tblResult bevat en waarbij de waarde in de Kwartaal-kolom van de tabel tblKwRegTot2b dezelfde is als die in de Kwartaal-kolom in dezelfde tabel en rij waar deze formule staat.

De formule in J26 kopiëren naar J27 levert geen problemen op welke methode ook gehanteerd wordt (kopiëren/plakken of de vulgreep).

Maar: in het voorbeeld is J26 naar K26 gekopieerd met behulp van de vulgreep; de verwijzingen worden dan als relatief beschouwd. Dat klopt voor de regio-kop in de resultaat-tabel (die moet Zuid worden) maar niet voor de verwijzingen naar de bron-tabel tblKwRegTot2.
Cel J27 is via kopiëren/plakken naar K27 gekopieerd. De verwijzingen blijven absoluut, maar dat klopt dan niet voor de regio-kop!

Oplossing: verwijzingen die absoluut moeten blijven moeten dubbel in de formule worden opgenomen, gescheiden door een dubbele punt (:).
De formule die hier nodig is wordt dan (zie cel J31):
=SOMMEN.ALS(tblKwReg2b[[Bedrag]:[Bedrag]];
tblKwReg2b[[Regio]:[Regio]]; tblResult2[[#Kopteksten];[Noord]];
tblKwReg2b[[Kwartaal]:[Kwartaal]]; tblResult2[@[Kwartaal]:[Kwartaal]])

Wanneer deze formule via de vulgreep naar rechts en naar beneden wordt gekopieerd, krijgen we in alle cellen de juiste resultaten!

NB1 alle kolomnamen hebben vierkante haken, maar de dubbelen krijgen daar omheen nog een extra set rechte haken. Let ook op de notatie bij de @.

NB2 ook de laatste parameter (Kwartaal) moet in dit geval een tabel-aanduiding krijgen, ook al is de verwijzing binnen de tabel zelf.

 

Subtotaal

Excel, , , ,
Voorbeeldbestand: Subtotaal.xlsxDownload

Subtotalen: in de dagelijkse praktijk zijn we meestal niet alleen geïnteresseerd in totalen, maar willen we ook aantallen, bedragen etc. zien per jaar of per maand, per afdeling of per soort of ….
Subtotalen dus.

Eigenlijk vind ik, dat er maar één goede methode is om subtotalen te bepalen en dat is met behulp van draaitabellen. Bij de meeste andere methodes worden namelijk de basis-gegevens aangepast en dat druist in tegen goed Excel-gebruik.

Wanneer een (sub)totaal moet wijzigen, als er regels in de bron-data worden verborgen (maar wie wil dat nou?), alleen dan zul je de functie SUBTOTAAL moeten gebruiken.

Maar omdat het altijd goed is om meerdere alternatieve methodes te kennen, volgt hieronder een uitleg van verschillende manieren om subtotalen te genereren.

Brongegevens

In het Voorbeeldbestand op het tabblad Data staat een serie bedragen (200 regels), waarbij ieder Bedrag drie kenmerken heeft: het Jaar, de Maand en een Regio.

Het totaal van de bedragen kunnen we snel vinden door de gehele kolom te selecteren (klik op de betreffende kolom-letter, in dit geval E) en kijk rechtsonder in de statusbalk:
Afhankelijk van de Excel-versie zie je tegelijkertijd ook het gemiddelde, aantal enz. of je kunt deze oproepen door op het vinkje te klikken.

Maar wanneer je alleen het totaal van 2015 wilt weten of van de regio noord dan wordt het wat ingewikkelder: eerst sorteren op de betreffende kolom, dan alle bedragen van het jaar of regio selecteren en dan onderaan het subtotaal aflezen. Maar ondertussen hebben we iets met onze brongegevens gedaan (namelijk gesorteerd) en dat willen we niet; er kan altijd iets mis gaan bij zo’n activiteit.

En willen we het totaal van een ander jaar of andere regio dan moeten we opnieuw beginnen. Dat moet dus anders kunnen.

Draaitabel

Wat te doen?

  1. selecteer één van de cellen van de brongegevens
  2. kies in de menutab Invoegen in het blok Tabellen de optie Draaitabel
  3. kies in het vervolgscherm OK
  4. sleep het veld Jaar naar het Rijlabels-gebied
  5. sleep het veld Bedrag naar het Waardegebied

En we hebben een overzicht van het totaalbedrag en subtotalen per jaar.

Maar als we nu toch bezig zijn, dan kunnen we dit overzicht nog wel wat aanpassen:

  1. sleep het Jaar naar het Kolomslabels-gebied
  2. sleep het veld Maand naar het Rijlabels-gebied
  3. sleep het veld Bedrag voor de tweede keer naar het Waardegebied
  4. klik op de 2e Som van Bedrag en wijzig bij Waardeveldinstellingen de Som in Gemiddeld
  5. sleep Waarden naar het Rijlabels-gebied

Dus door het simpel verslepen van velden kunnen we Excel snel diverse totalen en subtotalen laten bepalen.

Filter

De eerste alternatieve methode, die me invalt, is het gebruik van filters:

  1. selecteer een cel in het bronbestand
  2. kies in de menutab Gegevens in het blok Sorteren en filteren de optie Filter
  3. maak via de ‘vinkjes’ in de koppen de gewenste selectie
  4. selecteer alle overblijvende bedragen en onder in de statusbalk komt de som (en/of gemiddelde enzovoort) tevoorschijn

Zie het tabblad Filter in het Voorbeeldbestand; zoals daar te zien is trekken de functies SOM, GEMIDDELDE etc. zich niets aan van een filtering.
Dus dit is geen structurele oplossing.

SOM.ALS

Met behulp van ALS-formules kunnen wel subtotalen bepaald worden. In cel E3 in het tabblad Als van het Voorbeeldbestand staat de volgende formule: =SOM.ALS(B7:B206;E2;E7:E206)

Dit betekent, dat als er in het bereik B7:B206 de waarde uit cel E2 staat (in dit geval 2015) dan mag de overeenkomende waarde uit kolom E meegeteld worden.

Hetzelfde idee gaat op voor Aantal, Gemiddelde etc.

LET OP ook deze formules trekken zich niets aan van een mogelijke filtering van de brongegevens.

Wil je een iets complexer subtotaal (bijvoorbeeld van 1 jaar slechts het totaal van 1 maand) dan komt de volgende formule in beeld: =SOMMEN.ALS(E7:E206;B7:B206;E2;C7:C206;F2)

NB misschien wat verwarrend, maar de volgorde van de parameters is net wat anders; zie ook het artikel Tellen-met-voorwaarden.

Excel-tabel

De vorige ALS-formules werken nog makkelijker met een Excel-tabel (zie het tabblad AlsTabel in het Voorbeeldbestand):

  1. selecteer een cel in het bronbestand
  2. kies in de menutab Invoegen in het blok Tabellen de optie Tabel
  3. zorg dat het vinkje aanstaat bij kopregel en klik op OK

De formule om het subtotaal voor 2015/feb te bepalen wordt dan:

=SOMMEN.ALS(tblData[Bedrag];tblData[Jaar];JrSel;tblData[Maand];MndSel)

Hierbij hebben de invoercellen voor het jaar en de maand de namen JrSel, respectievelijk MndSel gekregen.

Ook hier geldt weer, dat filteren in de tabel geen invloed heeft op de formules (al zou het voorbeeld hierboven anders doen vermoeden; wijzig de filtering maar eens!).

Maar de Excel-tabel kent wel een totaliseer-optie, die rekening houdt met filtering:

  1. selecteer een cel in de Excel-tabel
  2. kies in de nieuwe menutab Hulpmiddelen voor tabellen/Ontwerpen  in het blok Opties voor tabelstijlen de optie Totaalrij.

In de cel in de onderste regel in de Bedrag-kolom komt nu automatisch de formule =SUBTOTAAL(109;[Bedrag]); het totaal van de gefilterde bedragen.

In de cel daarvoor is ‘handmatig’ de formule =SUBTOTAAL(101;[Bedrag]) geplaatst; deze zorgt voor het gemiddelde van de zichtbare bedragen (zie hierna voor de betekenis van de codes 109 en 101).

NB probeer het effect uit van de dubbele vinkjes in de onderste regel van de tabel.

SUBTOTAAL

De subtotaal-functionaliteit hoeft niet beperkt te blijven tot Excel-tabellen.
Nee, hebt u een database met gegevens dan kan Excel ook op de volgende manier een subtotalen-overzicht genereren:

  1. selecteer een cel in het bronbestand
  2. kies in de menutab Gegevens in het blok Overzicht de optie Subtotaal
  3. in het vervolgscherm kan worden aangegeven voor welk veld er subtotalen moeten komen (Bij iedere wijziging in), u kunt aangeven of u de som of gemiddelde wilt zien (of nog wat anders) en van welk veld u de som wilt zien.
  4. de drie onderste vinkjes spreken voor zich
  5. en klik op OK

Het resultaat is een brij van details en totalen (zie het tabblad Subtotaal in het Voorbeeldbestand).

LET OP Bij het gebruik van deze functionaliteit is het van groot belang, dat de bron-gegevens gesorteerd staan! En wel op de kolom, waarvan de subtotalen bepaald moeten worden.

In het tabblad SubTot2 zijn de gegevens eerste gesorteerd op Jaar en pas toen zijn op bovenstaande manier subtotalen bepaald.

Excel groepeert automatisch alle bij elkaar behorende regels; gebruik de + en – knoppen om meer of minder details te zien. Ook kun je gebruik maken van de cijfers linksboven (in dit geval 1, 2 en 3; er zijn drie niveau’s: totaal, subtotaal per jaar en detail).

NB als de sortering op meer dan 1 kolom is doorgevoerd dan kunnen ook op meerdere niveaus subtotalen worden bepaald. Als bijvoorbeeld binnen het jaar ook op de maand is gesorteerd, dan kunnen eerst subtotalen voor het maandniveau bepaald worden en daarna op jaar-niveau (vergeet niet het vinkje uit te zetten bij Huidige subtotalen vervangen!).

In cel E71 heeft Excel automatisch de formule =SUBTOTAAL(9;E3:E70) geplaatst. De formules voor de andere jaren zijn vergelijkbaar. Voor het totaal in E206 staat echter de formule =SUBTOTAAL(9;E3:E204).
Excel laat tussenliggende subtotalen dus automatisch buiten de berekening!

Ook kunnen meerdere soorten subtotalen onder elkaar geplaatst worden; zie het tabblad SubTot3 in het Voorbeeldbestand.

Ook nu is het zaak om niet te vergeten het vinkje weg te halen bij Huidige subtotalen vervangen.

SUBTOTAAL 2

Maar de functie Subtotaal kunt u ook zelf overal in een sheet plaatsen.
De functie kent in principe 2 parameters: de eerste (het functiegetal) geeft aan welke bewerking moet worden uitgevoerd (som, gemiddelde, aantal) en de tweede welk bereik bij de berekening moet worden meegenomen.
Excel kent 11 soorten berekeningen (zie het tabblad SubTot4a in het Voorbeeldbestand), waarvan het bepalen van het gemiddelde (functiegetal=1), aantal (functiegetal=2) en som (functiegetal=9) de meest gebruikte zijn.

Het functiegetal kan ook 100 groter gekozen worden; het verschil zit hem er in, dat in het tweede geval verborgen regels niet meetellen!

Bekijk op het tabblad SubTot4a het effect van het wijzigen van het functiegetal in regel 40.

Filteren op een of meerdere kolommen heeft op allebei de soorten functiegetallen hetzelfde effect: uitgefilterde waarden worden niet meegeteld.
Het groeperen van regels heeft voor de subtotalen hetzelfde resultaat als het verbergen van regels.
Bekijk het effect van de 3 bewerkingen (filteren, groeperen en verbergen) in het voorbeeld op het tabblad SubTot4b.

LET OP Blijkbaar is dit zo ingewikkeld dat Excel bij het aan- en uitzetten van bewerkingen af en toe de verkeerde resultaten oplevert!!

NB het groeperen van regels heeft verreweg de voorkeur boven het verbergen van regels. In het tweede geval komt het nogal eens voor dat je niet in de gaten hebt dat er regels ‘weg zijn’.

 

Unieke waarden

Excel, , , , , , , ,
Voorbeeldbestand: Uniek.xlsxDownload

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:

  1. selecteer cel C3
  2. kies in de menutab Start in het blok Stijlen de optie Voorwaardelijke opmaak
  3. klik op de optie Nieuwe regel
  4. in het vervolgscherm kiezen we als Type: Een formule gebruiken
  5. 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.
  6. voeg nog de gewenste opmaak toe (hier is voor een licht-groene opvulling van de cel gekozen)
  7. klik op OK
  8. 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:

  1. selecteer een cel in de tabel met gegevens, bijvoorbeeld B2
  2. kies in de menutab Invoegen in het blok Tabellen de optie Draaitabel
  3. in het vervolgscherm is alles al goed ingevuld; de bron voor de draaitabel is de totale Excel-tabel (met de naam tblData1)
  4. klik OK
  5. sleep het veld Afd naar de Rijlabels en daar zijn alle unieke afdelingsnamen!
  6. 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:

  1. plaats in een bepaalde cel de waarde Srt1, bijvoorbeeld in G14 (zie het tabblad Data1 in het Voorbeeldbestand)
  2. in de cel daarnaast gaan we een formule opbouwen:
    tik in =som.als(
  3. 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.
  4. tik in ; (de punt-komma) en klik op cel G14 en plaats weer een ;
  5. als laatste voegen we de kolom Bedrag toe, die onder voorwaarden gesommeerd moet worden; dat gaat op dezelfde manier als in stap 3.
  6. 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:

  1. plaats de cursor in de cel die u wilt valideren (bijvoorbeeld cel G19 zoals in het tabblad Data1)
  2. kies in de menutab Gegevens in het blok Hulpmiddelen voor gegevens de optie Gegevensvalidatie
  3. kies bij Toestaan: de optie Lijst
  4. 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.
  5. 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:

  1. kies in de menutab Gegevens in het blok Sorteren en filteren de optie Geavanceerd
  2. in het Uitgebreid filter kiezen we als Actie de optie Kopiëren naar andere locatie
  3. voor het Lijstbereik selecteert u alle gegevens uit de kolom Afd van de tabel, INCLUSIEF de kop
  4. 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)
  5. 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:

  1. kies in de menutab Formules in het blok Gedefinieerde namen de optie Naam definiëren
  2. vul een naam in (bijvoorbeeld AfdUniek)
  3. 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.