Tagarchief: Index

Data verrijken

Excel, , , , ,
Voorbeeldbestand: DataVerrijkenDownload

Iedere dag krijgen of vinden we wel gegevens, waarvan je denkt: interessant, maar als ik die met een andere set combineer (verrijken van data) kan ik er nog veel meer informatie uit halen.

In dit artikel geef ik uitleg over verschillende methoden zoals bestaande tabellen uitbreiden met extra kolommen, gebruik maken van het Excel-gegevensmodel en het gebruik van Power Query.

Brongegevens

We gaan als voorbeeld een Excel-tabel gebruiken met daarin het aantal verkochte producten, uitgesplitst naar datum, klant- en productcode.
In het tabblad Verkoop van het Voorbeeldbestand vindt u 1000 records in de Excel-tabel tblVerkoop.

Welke klanten komen hierin voor? Welke producten zijn verkocht? Over welke periode gaat dit bestand?
Meer vragen dan antwoorden.

Verkoopoverzicht

Om snel inzicht te krijgen in bovenstaande vragen maken we op basis van het bestand een draaitabel:

  1. Plaats de cursor ergens in de tabel tblVerkoop.
  2. Kies in de menutab Invoegen in het blok Tabellen de optie Draaitabel en klik op OK.
  3. Sleep de Datum naar de Rijen, Aantal naar het Waarden-gebied en Prod naar Kolommen.

We zien dus direct (tabblad OvzVerkoop) dat het 6 producten betreft en dat het over de periode 2018-2020 gaat.

Klik je op de + voor een jaar, dan zie je welke kwartalen en maanden er in het overzicht voorkomen.

NB1 vanaf versie 2016 zal Excel datums, die in de kolommen of rijen worden geplaatst, direct groeperen naar Jaren, Kwartalen en Maanden.
Gebeurt dit niet automatisch, klik dan met de rechtermuisknop op een datum en kies Groeperen; selecteer daar dan de gewenste opties.

NB2 wil je geen groepering naar kwartaal? Klik rechts op een van de tijdaanduidingen, kies Groeperen en deselecteer de optie Kwartalen.

Maar hoe zit het met de klanten?

  1. Verwijder Prod uit de Kolommen.
  2. Verplaats Jaren van Rijen naar Kolommen.
  3. Sleep Klanten naar Rijen.

Zie het tabblad OvzVerkoop.

NB Excel plaatst de klanten bij het aanmaken van de draaitabel netjes in alfabetische volgorde, maar door de methode van codering komt K10 direct na K1. Gelukkig is dit snel verholpen: selecteer de cel met K10, ‘pak’ met de muis de rand en sleep de code naar beneden.

‘Toevallig’ weet ik dat de klanten over 2 regio’s zijn verdeeld: K2, K5, K7 en K8 horen bij de regio Zuid, de anderen bij Noord.
Die moeten we nu dus nog groeperen (zie Groeperen in een draaitabel): selecteer de draaitabel-rij met K2 (het muis-symbool is dan een pijltje naar rechts), houd Ctrl ingedrukt en selecteer op dezelfde manier K5, K7 en K8. Klik rechts en kies Groeperen. Nog even de namen van de groepen aanpassen (gewoon overschrijven) en de groep Noord naar boven verslepen: klaar!

Op deze manier hebben we handmatig de eerste verrijkingen aangebracht.

Verrijking door toevoegen kolommen

Maar we weten nog meer over onze klanten en de producten:

Tabblad Klant
Tabblad Product

Hiermee kunnen we onze dataset uitbreiden. Dezelfde gegevens van het tabblad Verkoop zijn allereerst gekopieerd naar VerkBerek van het Voorbeeldbestand.

Aan de Excel-tabel (met de naam tblVerkBerek) zijn nieuwe kolommen toegevoegd:

  1. Regio, in cel F3 staat de formule:
    =INDEX(tblKlant[Regio];VERGELIJKEN([@Klant];tblKlant[Klant];0))
    NB1 hier is een alternatief voor verticaal zoeken gebruikt. De avz-truc komt goed van pas! Zie zoeken-index-en-vergelijken.
    NB2 alle berekeningen worden binnen een Excel-tabel uitgevoerd, waardoor we gestructureerde verwijzingen kunnen gebruiken. Zie voor de voordelen van tabellen: kunst-en-excel.
    NB3 na het invoeren van de formule in F3 wordt deze automatisch naar beneden doorgevoerd, zodat alle cellen in die kolom een consistente formule hebben.
  2. In cel G3 halen we op een vergelijkbare manier de naam van het product op:
    =INDEX(tblProd[ProdNaam];VERGELIJKEN([@Prod];tblProd[Product];0))
  3. De totale kosten, die met een verkoop zijn gemoeid worden in cel H3 bepaald:
    =[@Aantal]* INDEX(tblProd[KostPrijs]; VERGELIJKEN([@Prod];tblProd[Product];0))
  4. In cel I3 bepalen we de omzet:
    =[@Aantal]* INDEX(tblProd[VerkPrijs]; VERGELIJKEN([@Prod];tblProd[Product];0))
  5. Daarmee kunnen we in J3 de bruto-winst berekenen:
    =[@Omzet]-[@Kosten]
  6. Maar (sommige) klanten krijgen korting; in cel K3:
    =INDEX(tblKlant[Korting];VERGELIJKEN([@Klant];tblKlant[Klant];0))
  7. Waarmee we ook een netto-winst kunnen bepalen in cel L3:
    =[@Omzet]*(1-[@Korting])-[@Kosten]

Op basis van deze nieuwe dataset kunnen we diverse analyses uitvoeren, uiteraard met behulp van een draaitabel. Bijvoorbeeld (zie tabblad OvzVerkBerek van het Voorbeeldbestand):

Verrijken m.b.v. gegevensmodel

Een nadeel van de vorige methode is, dat het bestand meteen een stuk groter wordt wanneer er kolommen aan een tabel worden toegevoegd. Dat valt nog wel mee als het over 1.000 records gaat, maar als het er een miljoen zijn en als het meer kolommen betreft….

De koppelingen, die we hiervoor met Index/Vergelijken hebben gemaakt, kunnen sinds versie 2013 ook intern in Excel met behulp van een gegevensmodel worden vastgelegd zonder dat dit extra ruimte in beslag neemt. Wel moeten de basisgegevens in Excel-tabellen vastliggen. In het Voorbeeldbestand bevatten de tabbladen Verkoop, Klant en Product onze basisgegevens.

Nu gaan we het gegevensmodel vullen door de relaties tussen deze tabellen vast te leggen:

  1. Kies in de menutab Gegevens in het blok Hulpmiddelen voor gegevens de optie Relaties (de button met 3 tabellen en lijntjes daar tussen).

  1. U legt een relatie vast door op de knop Nieuw te klikken:

    De eerste tabel moet de gegevens bevatten, die uitgebreid gaan worden; in dit geval dus tblVerkoop.
    We gaan via de klant-code gegevens opzoeken, dus geven als kolom Klant op.
    Daaronder moeten we aangeven in welke tabel we gaan zoeken, in het huidige voorbeeld tblKlant.
    Ook in deze tabel bevat de kolom Klant de overeenkomende gegevens.
    Klik op OK.
    NB1 in de laatste stap staat het woord primair; hiermee wordt bedoeld, dat de kolom unieke gegevens moet bevatten. Anders kan er geen relatie aangemaakt worden.
    NB2 heb je meer dan 1 kolom nodig om een relatie te leggen (bijvoorbeeld Naam en Afd) dan zul je een kunstgreep moeten uithalen: in beide tabellen moet je de 2 kolommen aan elkaar koppelen in een nieuwe kolom, bijvoorbeeld Naam&Afd.
  2. Maak op dezelfde manier ook een relatie tussen tblVerkoop en tblProd; de kolomnamen die daarbij gebruikt worden zijn respectievelijk Prod en Product.
    De namen hoeven dus niet hetzelfde te zijn.
  3. Sluit het scherm Relaties beheren.

Nu het gegevensmodel is gevuld kunnen we op basis daarvan overzichten maken met behulp van draaitabellen (in het tabblad OvzVerkModel1 van het Voorbeeldbestand staat een voorbeeld):

  1. Plaats de cursor in een lege cel. Hier zal het overzicht komen, dus meestal doe je dit op een nieuw tabblad.
  1. Start via Toevoegen/Draaitabel een nieuwe draaitabel
  2. Hiernaast is te zien, dat Excel nu automatisch weet, dat we ons gegevensmodel als bron willen gebruiken. Klik OK.

  1. Het overzicht van draaitabel-velden ziet er iets anders uit dan bij een ‘normale’ draaitabel.
    Alle tabellen uit het gegevensmodel zijn beschikbaar met alle bijbehorende kolommen.
    Hiernaast zijn alle 4 gebieden gevuld met gegevens uit 3 verschillende tabellen.
    NB1 bevat de werkmap nog meer Excel-tabellen (net als het Voorbeeldbestand), dan zijn die ook in dit overzicht zichtbaar.
    NB2 heb je velden naar de gebieden versleept en klik je bovenaan op Actief, dan zie je alleen de tabellen die gebruikt zijn in dit overzicht.

Helaas: op deze manier kun je alleen velden uit de basis-tabel (in het voorbeeld tblVerkoop) in het waarden-gebied plaatsen. Sleep je bijvoorbeeld Korting in het waarden-gebied dan krijg je de volgende melding:

Dit scherm krijg je ook als je niet alle tabellen via relaties met elkaar hebt verbonden, maar wel kolommen uit die tabellen gebruikt.

Wat nu wel kan: in de draaitabel kunnen unieke waarden geteld worden. Laten we even via een voorbeeld kijken (tabblad OvzVerkModel2 van het Voorbeeldbestand):

  1. Maak een draaitabel aan zoals hiervoor beschreven.
  2. Plaats Regio en Klant in de Rijen.
  3. Sleep Aantal naar het Waarden-gebied.
  4. Sleep daarna 2x de Datum naar het Waarden-gebied.
  5. Klik op het vinkje achter Datum2 en kies Waardeveldinstellingen.
  6. Helemaal onderaan krijg je nu de mogelijkheid om te kiezen voor Uniek aantal.

Telling van Datum geeft het aantal datums weer per klant, dus eigenlijk het aantal keer dat een klant voorkomt (als de datum is gevuld); het totaal is dan ook precies 1.000.

Unieke telling van Datum2 laat zien hoe vaak een unieke datum voorkomt bij een klant.

Verrijken m.b.v. Power Query

En dan nu een oplossing die als een soort combinatie van de vorige 2 gezien kan worden: Power Query. Binnen dit Excel-onderdeel leggen we de verbanden tussen de tabellen vast én we maken daar allerlei berekeningen die we nodig hebben:

  1. Selecteer een cel in de tabel tblKlant.
  2. Kies in de menutab Gegevens in het blok Ophalen en transformeren de optie Uit tabel.
  1. In principe zijn alle kolommen in orde. Aangezien Korting een percentage is, kunnen we de lay-out nog aanpassen:
    * klik op de 1.2 naast Korting
    * wijzig de instelling naar Percentage.
  1. Kies dan het vinkje naast Sluiten en laden.
  2. Klik op Sluiten en laden naar.
  3. Zorg dat de optie Alleen verbinding maken is geselecteerd en klik op de button Laden.
  4. Voer de vorige stappen ook uit voor de tabel tblProduct. Zorg dat KostPrijs en VerkPrijs de instelling Decimaal getal krijgen.
  1. Als laatste doet u hetzelfde met de tabel tblVerkoop, maar nog NIET sluiten. Wijzig de opmaak van de kolom Datum in alleen Datum.
  2. Voordat we Power Query afsluiten moeten we nog relaties leggen tussen de 3 tabellen.
    Kies het vinkje achter Query’s samenvoegen en kies de optie Samenvoegen als nieuw.

  1. Maak de verbindingen zoals hierboven en klik op OK. Wijzig de naam van de nieuwe query in PQtblVerkoop.
  2. Om de gegevens uit tblKlant als extra kolommen toe te voegen klikt u op het symbool rechts van tblKlant. Stel de opties in zoals hierboven en klik OK.
  3. Doe hetzelfde met tblProduct.
  4. Nu gaan we nog wat extra kolommen toevoegen: klik in de kolom Datum, klik op de menutab Kolom toevoegen en kies binnen de optie Datum voor het Jaar. Doe hetzelfde om de maand, de naam van de maand en het kwartaal toe te voegen.
  5. Kies daarna de optie Aangepaste kolom binnen Kolom toevoegen. De naam wordt Omzet en de formule =[Aantal]*[VerkPrijs].
  6. Voeg op dezelfde manier nog 3 kolommen toe:
    Kosten: =[Aantal]*[KostPrijs]
    Brutowinst: =[Omzet]-[Kosten]
    Nettowinst: =[Aantal]*([VerkPrijs]*(1-[Korting])-[Kostprijs])
  7. Zorg dat de 4 toegevoegde kolommen de instelling Decimaal getal hebben.
  1. Kies nu Sluiten en laden en zorg dat er alleen een verbinding tot stand wordt gebracht.
    LET OP zorg wel dat deze query PQtblVerkoop in de laatste stap wordt toegevoegd aan het gegevensmodel.

Nu staat alles klaar om overzichten te maken. Op het tabblad OvzVerkPQ van het Voorbeeldbestand vindt u een voorbeeld:

De gegevens van de bruto- en nettowinst zijn niet als getallen zichtbaar. Met behulp van Voorwaardelijke opmaak hebben die gegevensbalken gekregen:

  1. Selecteer een cel in een betreffende rij.
  2. Kies in de menutab Start in het blok Stijlen de optie Voorwaardelijke opmaak.
  3. Kies dan Gegevensbalken en een kleur(overgang).
  4. Naast de cel staat nu het tekentje van een draaitabel, kies daar de onderste optie.
  5. Via Regels beheren/Regel bewerken de optie Alleen balk weergeven aanvinken.
  6. Pas de rijhoogte naar wens aan.

De Aantallen hebben op een vergelijkbare manier een Voorwaardelijke opmaak gekregen, namelijk Kleurenschalen.

Wijzigt er iets aan de basisgegevens of hebt u nieuwe gegevens aan de tabellen toegevoegd? Door te klikken op de button

Wijzigt er iets aan de basisgegevens of hebt u nieuwe gegevens aan de tabellen toegevoegd?
Door te klikken op de button Alles vernieuwen in het blok Verbindingen van de menutab Gegevens, worden alle koppelingen, het gegevensmodel en alle draaitabellen ververst.

Scrollen in dashboard

Excel, VBA (Visual Basic for Applications), , , , ,
Voorbeeldbestand: DatumVerschil.xlsxDownload

LET OP: na het downloaden de extensie wijzigen in xlsb

Excel wordt steeds vaker gebruikt om het management via dashboards van actuele informatie te voorzien.
Maar ook in dit soort rapportages is de ruimte beperkt. Daarnaast is niet iedere manager geïnteresseerd in dezelfde informatie.

Dit soort problemen is handig op te lossen door gebruikers in de digitale rapportages door de overzichten te laten scrollen.

In dit artikel komen enkele methoden aan bod hoe dit te realiseren. En als we toch bezig zijn: met een beetje VBA kunnen we de overzichten ook makkelijk sorteren.

Basis-materiaal

We hebben een overzicht van afdelingen (het tabblad Data in het Voorbeeldbestand bevat 30 regels) met per afdeling het toegekende kosten-budget (altijd 100.000), de werkelijk gemaakte kosten, het aantal uitgebrachte offertes en het gerealiseerde aantal contracten. Daarnaast bevat het overzicht nog enkele KPI’s: kosten/budget, kosten/offertes en kosten/contracten.

De gegevens zijn opgeslagen in de vorm van een Excel-tabel met de naam tblAfd. In cel L2 (met de naam AantAfd) wordt het aantal afdelingen in de tabel geteld:
=AANTALARG(tblAfd[Afdeling])
Dit aantal kunnen we later goed gebruiken om bepaalde zaken te automatiseren.

NB de nummering van de afdelingen is zodanig dat er altijd 2 cijfers gebruikt worden; dit om een juiste sortering te krijgen. Anders krijg je Afd1, Afd10, Afd11, … , Afd2, Afd20 etc.

Overzicht 1

Wanneer nou blijkt, dat we in een bepaalde rapportage slechts ruimte voor 10 regels hebben; welke afdelingen (van de 30) moeten we dan laten zien? Dat hangt natuurlijk af van de wensen van de ontvangers en die kunnen wel eens tegenstrijdig zijn.
Daarom gaan we nu het overzicht met 10 regels zo aanpassen, dat hierbij gekozen kan worden welke regels zichtbaar zijn.

In het tabblad SelIndex van het Voorbeeldbestand geven we in cel C2 (met de naam Start) aan, welke regel van de 30 als bovenste moet worden weergegeven; de volgende 9 worden ook getoond.

In cel F3 staat de formule:
=INDEX(tblAfd[Afdeling];RIJ()-RIJ(AfdKop)+Start-1)
Ofwel: zoek met de functie INDEX in de kolom Afdeling van de tabel tblAfd die rij op die overeenkomt met de RIJ() van (in dit geval) cel F3, minus het rij-nummer van de kop van het overzicht plus de waarde van de cel Start.

NB1 cel F2 heeft de naam AfdKop gekregen.

NB2 voor het bepalen van de juiste regel hadden we natuurlijk ook een hulpkolom kunnen gebruiken met daarin de waardes 1 t/m 10. De gebruikte berekening maakt het mogelijk om de hulpkolom weg te laten en is flexibel genoeg om het overzicht eventueel later nog te verplaatsen. Ook kunnen we deze formule zonder verdere aanpassingen naar beneden kopiëren.

NB3 de formule kan ook naar rechts gekopieerd worden; Excel wijzigt de kolom-verwijzing Afdeling automatisch naar Budget etc.

NB4 om de juiste regel te selecteren moeten we de berekening nog corrigeren met -1.

Wanneer nu de waarde in de cel Start wordt gewijzigd zal het overzicht zich automatisch aanpassen.

Dat aanpassen kan natuurlijk nog veel mooier en makkelijker met behulp van een schuifbalk:

  1. kies in de menutab Ontwikkelaars in het blok Besturingselementen de optie Invoegen
  2. in het vervolgmenu kiest u binnen het blok Formulierbesturingselementen de optie Schuifbalk
  3. de cursor wordt dan een kruisje; teken, met de linkerknop ingedrukt, de gewenste vorm van de schuifbalk op de gewenste plaats (dit kan allemaal later nog aangepast worden).
  4. klik met de rechtermuisknop op de nieuwe schuifbalk en kies Besturingselement opmaken
  5. zorg dat een koppeling met cel C2 wordt gemaakt (u kunt hier ook de naam Start gebruiken), de minimumwaarde wordt 1 en het maximum 21.

LET OP kies NIET een ActiveX-besturingselement; deze leveren in de praktijk nogal eens crashes van Excel op.

NB als je de schuifbalk wilt verplaatsen of de grootte corrigeren, klik dan eerst rechts op de schuifbalk.

Overzicht 2

Een andere methode om zo’n overzicht met de juiste gegevens te vullen is door gebruik te maken van de functie VERSCHUIVING.

In het tabblad SelVersch1 van het Voorbeeldbestand ziet u in cel F3 de formule:
=VERSCHUIVING(tblAfd[[#Kopteksten];[Afdeling]];
RIJ()-RIJ(AfdKop)+Start-1;
KOLOM()-KOLOM(AfdKop))
ofwel: haal de waarde op uit de cel, die gevonden wordt door vanuit de Afdelings-koptekst van de tabel tblAfd een aantal rijen naar beneden te gaan en een aantal kolommen naar rechts.

NB1 de constructie na het eerste (-haakje hoeft u niet zelf in te tikken; klik gewoon op de betreffende cel en Excel vult de formule vanzelf aan.

NB2 de ingegeven minimum- en maximumwaardes voor de schuifbalk zorgen er voor dat het overzicht geen blanco regels zal bevatten. Helaas is dat niet het geval als in de cel Start een te grote of te kleine waarde wordt ingevoerd.

Overzicht 3

De functie VERSCHUIVING kent nog meer parameters/argumenten. Daar hebben we in het overzicht op het tabblad SelVersch2 van het Voorbeeldbestand gebruik van gemaakt. Cel F3 bevat de formule:
=VERSCHUIVING(tblAfd[[#Kopteksten];[Afdeling]];Start;0;10;8)
Ofwel: selecteer een bereik van cellen, die, gerekend vanaf de koptekst Afdeling, een aantal rijen gelijk aan Start lager begint. Het begin is 0 kolommen verschoven. Het resulterende bereik moet 10 regels hoog en 8 kolommen breed zijn.

LET OP deze formule levert een blok van 80 cellen op. Daarom moet de formule op een speciale manier worden ingevoerd:

  • selecteer eerst met de muis alle cellen waar het overzicht moet komen (in het voorbeeld de cellen F3 tot en met M12)
  • voer dan bovenstaande formule in
  • druk in plaats van op Enter tegelijkertijd op Ctrl-Shift-Enter (de zogenaamde CSE-methode).
  • Excel plaats dan accolades rond de formule

We willen er ook voor zorgen, dat er geen lege regels komen.
De cel Start heeft daartoe een gegevens-validatie gekregen: bij Toestaan is de optie Aangepast ingevuld en bij Formule:
=EN(Start>0;Start<=AantAfd-9)
Dus de waarde in de cel Start moet aan 2 voorwaarden voldoen: groter dan nul EN kleiner of gelijk aan het aantal regels in de bron (minus 9).

LET OP vergeet het eerste =-teken niet

Maximum in schuifbalk

Maar wat als er nu een afdeling bij komt? De tabel tblAfd wordt automatisch aangepast, de teller van het aantal afdelingen zal ook direct opgehoogd worden, de gegevensvalidatie uit het vorige overzicht zal daarom ook goed werken.
Maar … de schuifbalken hebben nog steeds een maximum van 21.

Alleen met behulp van een (kleine) VBA-routine kunnen we het maximum van de schuifbalk automatisch laten meelopen met het aantal regels in de bron-gegevens.

In het overzicht van tabblad SelVersch3 van het Voorbeeldbestand is deze routine geïmplementeerd. Probeer maar eens uit: voeg een regel in het tabblad Data toe en beweeg de schuifbalk op en neer.

Hoe kun je de eigenschappen van de schuifbalk door VBA laten aanpassen?

  1. klik rechts op de schuifbalk
  2. kies de optie Macro toewijzen
  3. als er nog geen macro aan de schuifbalk gekoppeld is dan kiest u Nieuw, anders Bewerken
  4. vul onderstaande programmacode in en sluit de Visual Basic-editor

NB1 afhankelijk van de Excel-versie kan de omschrijving van de subroutine iets anders zijn, bijvoorbeeld Sub Schuifbalk1_BijWijzigen. Laat de naam staan zoals die door Excel is gegenereerd.

NB2 afhankelijk van de situatie kan er ook sprake zijn van Schuifbalk2 etc.

NB3 VBA is altijd Engelstalig. Dus binnen de subroutine is sprake van een ‘shape’ met de naam Scroll Bar 1.

NB4 met de constructie Range(“AantAfd”).Value wordt de waarde uit de cel met de naam AantAfd opgehaald.

‘Automatisch’ sorteren

Zoals al eerder aangegeven zal niet ieder ontvanger van de rapportage de focus op dezelfde KPI leggen. We maken de rapportage zodanig dat de gebruiker zelf kan aangeven welke sortering de gegevens moeten hebben.

Klik op één van de keuzerondjes en de gegevens worden op de betreffende kolom gesorteerd (zie het tabblad Sortering in het Voorbeeldbestand).

  1. keuzerondjes worden op een vergelijkbare manier als schuifbalken aan het tabblad toegevoegd.
  2. klik rechts op één van de keuzerondjes en kies de optie Besturingselement opmaken
  3. koppel het besturingselement aan een cel in Excel; in het voorbeeld C4 ofwel KolomNr
  4. klik rechts op het eerste keuzerondje en kies de optie Macro toewijzen en zorg dat de volgende routine gekoppeld wordt:

NB in dit geval heeft het eerste rondje al het volgnummer 2.

De macro-toewijzing moet voor ieder keuzerondje apart worden uitgevoerd.

Bovenstaand subroutine roept een andere routine aan, Sortering. Deze ziet er als volgt uit (de basis is gemaakt door een macro op te nemen terwijl de sortering handmatig wordt uitgevoerd):


  • als eerste wordt de variabele Kolom gevuld: op basis van de waarde in cel KolomNr wordt één van de kolomkoppen gekozen
  • met het commando With wordt er voor gezorgd dat alle volgende opdrachten betrekking hebben op een Sort van de tabel (ListObject) tblAfd
  • vorige sorteringen verwijderen
  • nieuwe sortering toevoegen
  • het te sorteren bereik heeft een Header/kop
  • de sortering is niet gevoelig voor hoofd- en kleine letters
  • de sortering-orientatie is verticaal (inhoud van een kolom is bepalend)
  • de PinYin-regel mag ook weggelaten worden; alleen interessant bij Chinese tekens
  • en als laatste wordt de sortering toegepast

Voetbalcompetities

Excel, VBA (Visual Basic for Applications), , , , ,
Voorbeeldbestand: VoetbalComp.xlsmDownload

Je hoeft niet per se van voetballen te houden om dit artikel te kunnen waarderen.
Ik kreeg van Nico van der Meer een mooi Excel-systeem toegestuurd, dat hij graag met anderen wil delen: het verwerken van voetbaluitslagen tot overzichten en standen, inclusief consequenties voor nacompetities, Champions League etcetera.

In eerste instantie dacht ik: deze sheet bevat wel veel formules, daar zullen we snel wat fouten of mogelijke problemen uit kunnen halen.
Maar niets was minder waar; dus ik geef de werkmap door met maar een paar kleine aanpassingen (verbeteringen?).

Met het systeem van Nico kun je de hele competitie 2018-2019 plezier hebben; alles is al voorbereid, inclusief speeldata en zelfs tijden!
Maar, zoals uit het plaatje hierboven mag blijken, zijn dit soort competities behoorlijk onderhevig aan wijzigingen, dus voor volgend jaar zul je wel weer flink wat aanpassingen moeten doen.

Hieronder zal ik de werking van het systeem kort toelichten en ook een handreiking geven over de aanpassingen voor een volgende competitie-ronde.
Uiteraard zal ik ook de meest gebruikte Excel-functies en de gebruikte VBA-routines toelichten.

Voetbal-competitie

Voordat er zo’n mooi overzicht ontstaat moet er natuurlijk wat voorwerk verricht worden.
In het tabblad Info van het Voorbeeldbestand heeft Nico al diverse aanwijzingen gegeven.

Het systeem omvat 3 competities, Ere-, Eerste – en Tweede Divisie (de EersteDivisie heeft nu de naam KeukenKampioen).
In het tabblad Data liggen de gegevens van de competitieronde en van de teams per divisie vast. Ook kan daar aangegeven worden of de beveiliging (ter voorkoming van het overschrijven van formules) wel of niet moet worden aangezet.

Het tabblad Speelschema bevat per divisie een overzicht van alle rondes en de wedstrijden die daarbinnen gespeeld moeten worden; dit alles inclusief de betreffende datum en tijd. Deze gegevens zijn op internet terug te vinden; via de hyperlink boven iedere divisie zijn de relevante zaken te benaderen.

Klik rechts op een Hyperlink en dan Hyperlink bewerken om te zien hoe deze opgebouwd is.

LET OP om een hyperlink te bekijken moet de beveiliging van het tabblad verwijderd zijn. Dit kan via rechtsklikken op de tab onderaan of door in het tabblad Data aan te geven dat alle beveiligingen verwijderd moeten worden.

Wilt u met dit systeem de resultaten van een competitie volgen dan moeten op dit tabblad Speelschema de uitslagen handmatig ingevoerd worden (voor de Eredivisie in de kolommen C en D).

Resultaten en stand Eredivisie

In het tabblad Eredivisie van het Voorbeeldbestand worden de ingevulde wedstrijdresultaten automatisch verwerkt.

In cel G6 wordt bijvoorbeeld het aantal doelpunten opgehaald voor de thuisclub van de wedstrijd PSV – AZ Alkmaar mbv de formule
=ALS(ISLEEG(VERT.ZOEKEN(TEKST($B6;)&” – ” & TEKST($G$5;);SchemaED;2;0)); “”;VERT.ZOEKEN(TEKST($B6;)&” – “&TEKST($G$5;);SchemaED;2;0))

Via Verticaal Zoeken wordt de wedstrijd (B6, dus PSV, tegen G5, dus AZ) opgezocht in het bereik met de naam SchemaED. De inhoud van de 2e kolom wordt als resultaat teruggegeven. De derde parameter (een 0, nul) geeft aan dat een exacte zoekactie naar de wedstrijdnaam moet worden uitgevoerd.
Als de zoekactie niets oplevert (er is nog niets ingevuld) dan komt er in dit schema ook niets te staan (de dubbele aanhalingstekens), anders wordt het resultaat van de zoekactie in de cel geplaatst.

NB1 de namen van de teams worden door een spatie, een min-teken en opnieuw een spatie van elkaar gescheiden. Bij de opvoer in Speelschema moet dit ook exact zo zijn gedaan.

NB2 Nico heeft de team-namen niet rechtstreeks aan elkaar gekoppeld, maar heeft de functie Tekst gebruikt. Waarschijnlijk om er zeker van te zijn dat Excel deze betreffende cellen zeker als tekst zal zien. Naar mijn idee overbodig.

De overige formules onder en naast het schema wijzen zichzelf.

Ook de standentabel wordt automatisch ingevuld.
Via Vergelijken wordt gekeken op welke positie het betreffende team in de vorige tabel staat en dan wordt via Index het gewenste aantal winst-punten opgehaald (uit en thuis opgeteld).

De formules in de overige kolommen zijn vergelijkbaar of ‘rechttoe-rechtaan’.

In kolom BW staat een code, die gebruikt wordt om de teams in de juiste volgorde te kunnen sorteren en de Plaats in de stand te bepalen.
Wanneer namelijk nieuwe resultaten worden toegevoegd in Speelschema, dan worden wel alle berekeningen direct uitgevoerd en ook de Plaats wordt bepaald, maar de volgorde in de standenlijst verandert niet. Wil je ook de volgorde juist hebben, klik dan op de knop Standen bijwerken. De betreffende VBA-routine (zie hierna) zal dan de sortering uitvoeren.

In cel BW6 staat de formule =WAARDE( TEKST(BS6;”000″)&TEKST(100+BT6;”000″)&TEKST(BU6;”000″)&TEKST(100-BN6;”00″)&TEKST(AANTAL.ALS($BM$6:$BM$23;”>=”&BM6);”00″))

Ook hier wordt de functie Tekst gebruikt; in dit geval om zeker te weten dat het betreffende getal altijd met exact hetzelfde aantal cijfers wordt weergegeven.
Met de functie Waarde wordt van de resulterende tekst weer een getal gemaakt.

Nico heeft in een opmerking bij Code netjes vermeld hoe de code is opgebouwd.

NB bij de Eredivisie zijn na 3 speelrondes al geen gelijke standen meer; bij de KK-divisie na 2 speelrondes nog wel.

In kolom BL wordt de plaats van het team in de stand bepaald: =RANG(BW6;$BW$6:$BW$23)
Met behulp van Voorwaardelijke opmaak krijgen bepaalde Plaatsen ook nog een kleurcode; dit is dus onafhankelijk van de sortering.

Overige divisies en nacompetitie

Bij de KK-divisie heeft Nico ook nog overzichten gemaakt ten behoeve van de bepaling van de periode-kampioenen. Zover ik nu heb kunnen zien heeft hij alle lastige afwijkingen en problemen daarmee uitstekend opgelost.

Wat de nacompetitie betreft: de maker van het systeem is nog niet helemaal zeker over de juistheid daarvan.

“De nacompetitie is voor mij nog experimenteel gezien de complexe regels.
Ik had niet verwacht, dat ik tot iets zou kunnen komen, maar al werkende daaraan ontstond er toch iets”.

Nieuwe competitieronde

Wilt u dit systeem ook een volgend seizoen gebruiken, dat moet er nogal wat aangepast worden:

  1. in het tabblad Data van het Voorbeeldbestand het seizoen aanpassen
  2. ook in Data de teams in de drie divisies aanpassen (hoeven niet per se gesorteerd te zijn)
  3. op het tabblad Data de teams van de Eredivisie selecteren en Ctrl-C (kopiëren) drukken; in het tabblad Eredivisie op de cel BM6 rechts klikken en Plakken speciaal/Waarden kiezen
  4. dezelfde werkwijze hanteren voor de teams van de eerste en tweede divisie
    LET OP bij de eerste divisie moeten de teams vijf keer geplakt worden.
  5. in het tabblad Speelschema alle uitslagen wissen en de wedstrijden die gespeeld zullen gaan worden overschrijven.
    LET OP zorg dat de namen van de clubs exact gelijk zijn aan die in het tabblad Data en dat de 2 ploegen gescheiden worden door een spatie, min-teken en nog een spatie

VBA

Met behulp van de knoppen Standen bijwerken worden bijbehorende VBA-routines uitgevoerd.
Voor de Eredivisie ziet die er als volgt uit:
Het bereik met de naam StandEredivisie wordt gesorteerd op kolom BW  en wel in Aflopende volgorde (grootste getal eerst) en het bereik heeft geen Kop.

Alle andere sorteringen gaan op dezelfde manier. Bij de KK-divisie worden 5 sorteringen achter elkaar uitgevoerd; dus voor alle schema’s van het betreffende tabblad.

In het Excel-object ThisWorkbook staat de volgende routine:

De For-Next-loop loopt alle tabbladen langs en afhankelijk van de inhoud van de cel met de naam BevJN (deze staat in het tabblad Data) wordt de beveiliging aan of uit gezet (zonder wachtwoord).

Application.ScreenUpdating zorgt er voor, dat het scherm niet gaat ‘flikkeren’ bij het selecteren van een tabblad.

 

Transponeren

Excel, , , , ,
Voorbeeldbestand: Transponeren.xlsxDownload


Transponeren: zoals vaker heeft ook dit woord verschillende betekenissen, afhankelijk van de context.

In de muziekwereld is Transponeren het verplaatsen van een stuk muziek naar een andere toonsoort. De muziek klinkt daardoor hoger of lager. Als je gitaar speelt met een Capo transponeer je ook.

In Excel wordt er mee bedoeld dat we gegevens, die eerst horizontaal zijn weergegeven, zodanig verplaatsen dat het een verticaal overzicht wordt (of andersom).

In dit artikel enkele eenvoudige Excel-transponeer-tips, waaronder een bespreking van de functie TRANSPONEREN en een alternatief met behulp van de veelzijdige functie INDEX.

Transponeren m.b.v. kopiëren/plakken

Laten we maar eens beginnen met een simpele praktijk-situatie (zie het tabblad TransP1 van het Voorbeeldbestand).
In de loop van 2017 hebben we een maandoverzicht gemaakt; geen probleem. Maar nu we weer aan een nieuw jaar zijn begonnen, merken we dat het niet zo handig was om het overzicht “in de breedte” te maken. Binnenkort past het niet meer op een A4. Het overzicht hadden we niet horizontaal moeten maken, maar verticaal.

Dus we moeten alles opnieuw invoeren? Nee, natuurlijk niet! Het toverwoord is transponeren:

  1. selecteer de cellen die omgezet moeten worden; in het voorbeeld B2:P4
  2. klik met de rechter muisknop op de selectie en kies Kopiëren of
    druk op Ctrl-C
  3. klik met de rechter muisknop in de cel waar het nieuwe overzicht moet komen (bijvoorbeeld in cel B6) en kies de button  (de R-toets indrukken geeft hetzelfde resultaat)
    Een andere manier: wanneer u na het rechts-klikken Plakken speciaal …. kiest, dan krijgt u een nieuw venster waarin u de optie Transponeren kunt aanvinken
    Klik nog wel even op OK.
  4. verwijder eventueel de rijen met het oude overzicht.

NB wanneer je in stap 2 Knippen kiest (of Ctrl-X) dan is de optie Transponeren niet beschikbaar

Transponeren met opmaak en formules 1

Wanneer we hetzelfde doen met een overzicht met opmaak en formules kan het resultaat wel eens tegenvallen.
In het voorbeeld op tabblad TransP2 van het Voorbeeldbestand bestaat de opmaak uit dikkere randen, een vet lettertype en getallen die opgemaakt zijn met 2 decimalen en er staan formules in rij 5.
Niet alle opmaak wordt getransponeerd; wel worden formules omgezet (zie bijvoorbeeld cel E12).

Gelukkig hebben we nog wel wat alternatieven achter de hand:

  1. volg in stap 3 (zie hierboven) de omslachtige methode (dus Plakken speciaal kiezen of 2 keer op de punt-toets drukken) en vink in het pop-up-scherm niet alleen Transponeren aan, maar ook de optie Waarden.
    Groot nadeel is dat alle opmaak weg is en er geen formules meer in het overzicht staan, maar harde waardes (zie tabblad TransP2).
  2. volg in stap 3 (zie hierboven) de omslachtige methode (dus Plakken speciaal kiezen of 2 keer op de punt-toets drukken) en vink in het pop-up-scherm niet alleen Transponeren aan, maar ook de optie Alles behalve randen.
    Het enige wat je nu nog moet doen is opnieuw de randen aanbrengen.

Transponeren met opmaak en formules 2

Nog een voorbeeld maar met een formule, die naar een cel buiten het overzicht verwijst (zie het tabblad TransP3 van het Voorbeeldbestand).

Wanneer we dit overzicht Transponeren (met de optie Alles behalve randen) dan zien we iets vreemds in de laatste 2 rijen. De formules leveren fout-meldingen op. Als we naar de ‘bron’ kijken (cellen O7 en P7) dan blijkt daar een relatieve verwijzing naar cel C2 te staan (dus zonder de $-tekens). Bij het Transponeren gaan deze verwijzingen ‘de mist in’.

Wanneer het Transponeren plaats vindt naar een ander tabblad dan zullen alle formules foutieve waardes opleveren: de nieuwe formules verwijzen naar cel C2 in het nieuwe tabblad en daar staat waarschijnlijk geen BTW-percentage.

De handigste oplossing voor dit soort problemen is om zoveel mogelijk Namen te gebruiken in formules:

  1. selecteer cel C2
  2. klik in het Naamvak links boven
  3. vervang daar C2 door de naam BTW en druk op Enter
  4. vervang in de formules in rij 7 alle verwijzingen naar C2 door de naam BTW
  5. Transponeren mag nu geen problemen meer opleveren

NB wanneer je in een formule een verwijzing naar een zelf-gedefinieerde naam gebruikt dan is dit altijd automatisch een absolute verwijzing.

Transponeren met opmaak en formules 3

Nog een voorbeeld: een overzicht per maand en regio. In het tabblad TransP4 van het Voorbeeldbestand kunt u zien dat er ook nog totaal-formules onder en rechts er van staan.

NB Totalen zijn altijd snel aan een dergelijk overzicht toegevoegd:

  1. klik met de muis in een van de cellen van het overzicht
  2. druk op Ctrl-A; alle cellen van het overzicht zullen worden geselecteerd
  3. druk op Shift (vasthouden) en pijl naar rechts; de selectie wordt met 1 kolom uitgebreid
  4. druk op Shift (vasthouden) en pijl naar beneden; de selectie wordt met 1 rij uitgebreid
  5. klik met de muis op 

In een andere rapportage hebben we hetzelfde overzicht nodig, maar dan anders gerangschikt: de maanden in de rijen en de regio’s in de kolommen.

Hierboven hebben we gezien hoe het transponeren, inclusief opmaak en formules in zijn werk gaat.

Het grote nadeel van de methode zit hem in het feit, dat er nu twee overzichten zijn zonder koppeling. Als er cijfers veranderen moeten deze wijzigingen op 2 plaatsen worden doorgevoerd. Dit is natuurlijk fout-gevoelig; daarom zal ik hieronder enkele alternatieve methoden laten zien.

De functie Transponeren

In het tabblad TransP5a van het Voorbeeldbestand is nogmaals het regio-maand-overzicht opgenomen.

Dit overzicht gaan we Transponeren met de Excel-functie met diezelfde naam:

  1. klik in de cel waar het nieuwe overzicht moet komen, in het voorbeeld is dit B11
  2. druk nu de Shift-toets in en hou die ingedrukt en druk zo vaak op de pijl naar rechts als er in het oorspronkelijke overzicht rijen zijn
  3. nog steeds met de Shift-toets ingedrukt zo vaak op de pijl naar beneden drukken als er in het oorspronkelijke overzicht kolommen zijn
  4. voer dan de volgende formule in =TRANSPONEREN(B2:N6)
    LET OP dit is een zogenaamde matrix- of array formule; deze moeten we activeren door niet alleen op Enter te drukken, maar tegelijkertijd op Ctrl-Shift-Enter (de CSE-methode). In alle cellen komt dezelfde formule te staan; Excel plaatst er automatisch accolades omheen.

NB hebt u geen zin om de originele rijen en kolommen te tellen, selecteer dan eerst met de muis alle cellen van het oorspronkelijke overzicht en kijk (zonder de muisknop los te laten) in het naamvak links boven. In dit voorbeeld zien we dan 5R x 13K staan, dus 5 rijen en 13 kolommen; het nieuwe overzicht krijgt dan 13 rijen en 5 kolommen.

Wijzigt nu één van de bedragen in het oorspronkelijke overzicht dan wordt deze mutatie in het nieuwe overzicht automatisch overgenomen.

Misschien had u het al gezien: het oorspronkelijke overzicht is in de vorm van een Excel-tabel ingevoerd (voor de voordelen hiervan zie onder andere de artikelen Kunst en Excel en Tabellen deel 2).
Wanneer we de regio’s nu uitbreiden met bijvoorbeeld Noord-Oost dan weet Excel dat de tabel groter is geworden (tik de omschrijving in cel B7 en druk op Enter).
Maar hoe zit het met het getransponeerde overzicht? Klik op één van de cellen en u ziet dat de formule automatisch is gewijzigd in =TRANSPONEREN(B2:N7).

Helaas hebben we bij het opstellen van dit overzicht geen rekening gehouden met deze uitbreiding, dus moeten we dit overzicht nog corrigeren:

  1. klik met de muis in één van de cellen van het getransponeerde overzicht
  2. druk op Ctrl-A; alle cellen van het overzicht zullen worden geselecteerd
  3. druk op Shift (vasthouden) en pijl naar rechts; de selectie wordt met 1 kolom uitgebreid
  4. klik in de formulebalk achter de bestaande formule (of druk F2)
  5. druk tegelijkertijd Ctrl-Shift-Enter

LET OP hebt u teveel rijen of kolommen geselecteerd bij het aanmaken van het nieuwe overzicht (of bij de uitbreiding daarvan) dan plaatst Excel de foutboodschap #N/B in de betreffende cellen (Niet Beschikbaar).

In het tabblad Trans5b van het Voorbeeldbestand is dit probleem opgelost door middel van Voorwaardelijke opmaak. De gehanteerde voorwaarde is daarbij =ISNB(B2) met als opmaak een witte kleur bij Lettertype (klik maar eens op Ctrl-A).

Wanneer de oorspronkelijke tabel een totaal-rij heeft en/of een totaal-kolom, is dit voor deze methode geen enkel probleem. De inhoud daarvan wordt bij het transponeren overgenomen, maar niet als formule (zie het tabblad TransP6).

NB helaas geeft de functie Transponeren alleen de inhoud van de oorspronkelijke cellen terug; het is op geen enkele manier mogelijk om de opmaak automatisch over te nemen.  Ook kun je van het resultaat geen Excel-tabel maken.

Transponeren met de functie Index

Voordat we deze functie hier gaan gebruiken, moeten we nog twee andere Excel-functies bespreken: Kolommen en Rijen.

LET OP deze functies niet verwarren met de functies Kolom en Rij, die respectievelijk het kolomnummer en rijnummer van een cel opleveren.

Wanneer we ergens in een spreadsheet de formule =KOLOMMEN(A1) intikken, dan krijgen we als resultaat 1: het aantal kolommen in dat bereik.
Met de vulgreep naar beneden kopiëren en naar rechts levert allemaal 1’en op; de functie blijft telkens maar naar 1 cel kijken (dus 1 kolom).
Maar tikken we nu als formule in =KOLOMMEN($A$1:A1) en kopiëren we die met de vulgreep naar beneden en naar rechts, dan krijgen we een heel ander resultaat (zie kolommen R tot en met U in het tabblad TransP7 van het Voorbeeldbestand).
Iets vergelijkbaars gebeurt er als we de formule =RIJEN ($A$1:A1) kopiëren.
Dus: als we de KOLOMMEN-formule naar beneden kopiëren dan verandert er niets, kopiëren we naar rechts dan neemt het resultaat iedere keer met 1 toe. Bij RIJEN is dit net andersom.
Deze eigenschap van KOLOMMEN en RIJEN wordt in veel (geavanceerde) toepassingen gebruikt, wanneer een teller nodig is in een formule.

Deze ’truc’ gaan we gebruiken om met behulp van de functie INDEX te transponeren:

  1. klik met de muis in de cel waar het eerste resultaat moet komen (in het voorbeeld van tabblad TransP7 is dit cel B14)
  2. daar beginnen we de formule met =INDEX($B$2:$O$9;
    Dan moeten we opgeven uit welke rij van het bereik B2:O9 er iets opgehaald moet worden: hier moet dat rij 1 zijn, maar als we de formule dadelijk naar beneden ’trekken’ dan moet dit 1 blijven; trekken we naar rechts dan moet er telkens een volgende rij gekozen worden. Dat is precies wat we hierboven met de functie KOLOMMEN hadden bereikt.
  3. Dus de formule wordt nu =INDEX($B$2:$O$9;KOLOMMEN($A$1:A1);
    Uit welke kolom moet de formule iets ophalen? De eerste keer uit kolom 1, maar als de formule naar beneden gekopieerd wordt dan moet dat telkens een volgende kolom zijn; bij het kopiëren naar rechts mag de kolom niet veranderen.
  4. In cel B14 komt =INDEX($B$2:$O$9;KOLOMMEN($A$1:A1);RIJEN($A$1:A1))
  5. Kopieren we deze formule te ver naar rechts of naar beneden dan zal Excel een foutmelding geven (de kolom of rij bestaat niet in B2:O9).
    Daarom hebben we in het voorbeeld de formule uitgebreid met een foutafhandeling:

    =ALS.FOUT(INDEX($B$2:$O$9;KOLOMMEN($A$1:A1);RIJEN($A$1:A1));””)

Nog even wat opmaak regelen en het getransponeerde overzicht is klaar.

In het tabblad TransP7 heb ik van een getransponeerd overzicht (vanaf rij 40) een Excel-tabel gemaakt zodat er weinig meer gedaan hoeft te worden aan de opmaak.
Opletten dat je de tabel maakt zonder kolomkoppen (dat zijn immers formules). Er ontstaat dan een aparte kop-regel, die met behulp van Gegevens/Overzicht/Groeperen, wanneer gewenst, kan worden ‘verborgen’.
Door middel van de nieuwe kopjes kan er makkelijk gefilterd worden; zorg dan dat de eerste regel (de oude kop) blijft staan, eventuele lege regels niet worden meegenomen en de totalen ook niet (want die kloppen niet met de filtering!)

 

Verder zoeken

Excel, , , , , , , ,
Voorbeeldbestand: ZoekenNietUniek.xlsxDownload

Zoeken in Excel blijft een belangrijke en veel gebruikte optie.
Ik heb daar dan ook al enkele keren een artikel met tips over geschreven.

Recent kreeg ik een opmerking op het artikel Alternatief voor Verticaal.zoeken:

Als er dubbele waarden of woorden in een reeks staan waar je de functie vergelijken op los laat dan is het resultaat de positie van de eerste waarde die hij vindt. Betekent dat dan dat je de functie vergelijken moet toepassen op een gegevensreeks met unieke waarden of tekens?

Mijn antwoord hield in, dat hij daar gelijk in had. En dat je dus altijd moet opletten met zoeken in Excel. En dat dit inspiratie was voor een artikel over het zoeken in niet-unieke gegevens.

Dus deze keer wat inspiratie voor uw speurwerk in Excel.

Basisgegevens

Uiteraard hebben we een voorbeeld nodig, waar we wat mee kunnen oefenen.
In het tabblad Data van het Voorbeeldbestand staat een overzicht van personen, die een betaling aan mij hebben gedaan. We hebben afgesproken, dat iedere werkdag iemand een bedrag stort (was het maar waar!).

Het overzicht begint op 1 september (cel C3. De dag van de week is via cel-opmaak zichtbaar gemaakt; druk Ctrl-1).
In de cel daaronder staat de formule:
=WERKDAG(C3;1)

Omdat de gegevens in een Excel-tabel staan (met de naam tblBetalingen) wordt deze formule automatisch in de rest van de kolom doorgevoerd; voeg maar eens een nieuwe persoon onderaan toe.

U ziet dat sommige personen 1 betaling hebben gedaan, anderen 2 en enkelen hebben er 3 gedaan.
Maar hoeveel heeft iedereen nu gestort en wanneer was de laatste betaling per persoon? Tijd voor nadere analyse.

Aantal-analyse

Allereerst moeten we een overzicht hebben wie er allemaal mee betalen:

  1. klik ergens in de tabel
  2. kies dan in de menutab Gegevens in het blok Sorteren en filteren de optie Geavanceerd
  3. in het vervolgscherm kunt u diverse zaken instellen:
    * we gaan niet filteren, maar kopiëren dus de 2e optie aanvinken
    * het Lijstbereik invullen; waar moeten de gegevens vandaan komen? Wijs met de muis de bovenrand van de eerste kolom aan tot de cursor verandert in een zwarte pijl naar beneden en klik dan
    * in het veld Kopiëren naar moet een verwijzing komen naar de eerste cel waar gegevens moeten worden weggeschreven
    * en als laatste aangeven dat we alleen unieke waarden willen overhouden
    * klik dan op OK

Er blijken tot nu toe 11 betalers te zijn; zie tabblad Ovz1 van het Voorbeeldbestand.

Het aantal betalingen per persoon is nu snel gevonden. In cel C3 staat de formule:
=AANTAL.ALS(tblBetalingen[Naam];B3)

Nog even naar beneden kopiëren, et voilà.

Twee personen hebben dus al 3 keer betaald, vier al 2 keer en 5 mensen nog maar 1 keer.

Bedrag-analyse

We gaan nu via Zoeken analyseren hoeveel iedereen heeft betaald (ja, ik weet het: mbv. een draaitabel is dat in een mum van tijd gebeurd!).

Zoals hierboven al opgemerkt kan Excel alleen maar naar unieke sleutels zoeken (dit in tegenstelling tot bijvoorbeeld Access, dat bij het zoeken naar Piet 2 verschillende records zou opleveren).

We moeten dus alle regels in Excel uniek maken; dat doen we door aan de namen een volgnummer te koppelen.
Aan de basistabel zijn daarom 2 hulpkolommen toegevoegd (zie tabblad Data in het Voorbeeldbestand):

  1. in kolom E (Hulp1) staat de formule
    =AANTAL.ALS(VERSCHUIVING(tblBetalingen[[#Kopteksten];[Naam]];1;0;RIJ()-RIJ(tblBetalingen[[#Kopteksten];[Naam]]));[@Naam])
    Turf het aantal keren, dat de Naam uit de betreffende rij voorkomt in het bereik dat door Verschuiving wordt bepaald.
    De Verschuiving is spannender:
    * het bereik begint in de cel waar de kop van de naam-kolom staat (klik bij het invullen van de formule op die cel en de verwijzing wordt automatisch gegenereerd)
    * maar start 1 regel lager
    * en 0 kolommen naar rechts of links
    * en de lengte van het bereik is gelijk aan de Rij waarin de formule staat minus de rij waar de kolomkop staat
    * in de eerste regel is het bereik dus maar 1 cel groot, in de tweede regel 2 cellen etc.
  2. in kolom F (Hulp2) staat:
    =[@Naam]&[@Hulp1]
    Ofwel: koppel de Naam uit de betreffende rij aan het volgnummer uit Hulp1

De gegevens in Hulp2 zijn nu altijd uniek, ook na uitbreiding van de tabel. Dus op die kolom kunnen we nu onze zoekacties uitvoeren. Aangezien Verticaal.zoeken niet ‘naar links’ kan zoeken gebruiken we de formule met de Index-Vergelijken-combinatie (zie het betreffende artikel daarover).

In het Voorbeeldbestand in het tabblad Ovz1 staat in cel D16 de formule
=ALS.FOUT(INDEX(tblBetalingen[Bedrag];VERGELIJKEN($B16&D$15;tblBetalingen[Hulp2];0));””)

Vergelijken zoekt in de Hulp2-kolom op welke plaats de combinatie van cel B16 en cel D15 (naam met volgnummer dus) staat. De nul geeft aan, dat we een exacte vergelijking willen uitvoeren.
Als we die plaats weten dan wordt met behulp van Index het bijbehorende Bedrag opgehaald.
Omdat de naam of de combinatie met het volgnummer niet hoeven voor te komen, zorgen we er voor dat, als de Index-formule een fout oplevert, er in de cel niets komt te staan (de dubbele aanhalingstekens).

LET OP in principe kan de formule uit D16 naar rechts en naar beneden gekopieerd worden. Doe dit niet door de vulgreep rechts onder van cel D16 te verslepen, maar gebruik de toets-combinaties Ctrl-C en Ctrl-V.

In kolom C nog even een Som-formule plaatsen en we weten hoeveel iedereen in totaal heeft betaald.

NB we zijn er hier van uit gegaan, dat er maximaal 5  betalingen per persoon plaats vinden. Klopt dit niet (meer) dan moeten er nog kolommen worden toegevoegd. Vergeet niet de Som-formule in kolom C aan te passen!

Datum-analyse

Op welke dagen zijn de betalingen verricht?

Op exact dezelfde manier als hiervoor halen we nu de Datum op in plaats van het Bedrag.
Deze keer plaatsen we in kolom C de Max-formule om de laatste betaaldatum per persoon te achterhalen.

Alternatieven zonder hulp-kolommen

Voor de liefhebbers staan in het tabblad Ovz2 van het Voorbeeldbestand nog 2 alternatieven, waarbij formules worden gebruikt die geen hulpkolommen nodig hebben.

In cel D3 wordt aan de hand van de naam in B3 en het volgnummer in D2 op de volgende manier het bijbehorende bedrag opgehaald:
={ALS.FOUT(
INDEX(tblBetalingen[Bedrag];
KLEINSTE(
(tblBetalingen[Naam]=$B3)*(RIJ(tblBetalingen[Naam]));
D$2 + AANTAL.ALS(tblBetalingen[Naam];”<>”&$B3)) –
RIJ(tblBetalingen[[#Kopteksten];[Naam]])
)
;””)}

Gebruik in de menutab Formules in het blok Formules controleren de optie Formules evalueren om te onderzoeken hoe de formule werkt.

NB Deze formule is ingevoerd door in plaats van op Enter op Ctrl-Shift-Enter te drukken, de zogenaamde CSE-methode.

Wil je de CSE-methode vermijden dan wordt de formule:
=ALS.FOUT(INDEX(tblBetalingen[Bedrag];INDEX(KLEINSTE((tblBetalingen[Naam]=$B9)*(RIJ(tblBetalingen[Naam]));D$2+AANTAL.ALS(tblBetalingen[Naam];”<>”&$B9))-RIJ(tblBetalingen[[#Kopteksten];[Naam]]);0));””)

Zie cel D9 in het tabblad Ovz2 van het Voorbeeldbestand.