Tagarchief: Index

Scrollen in dashboard


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

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


Voetbalcompetities



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.


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

Transponeren




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!)


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

 

 

Verder zoeken



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.


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

 

 

Top-5; verschillende methodes



Het komt regelmatig voor, dat je een ranking wilt aanbrengen in je gegevens: welke producten verkopen het beste, in welke maanden hebben we het beste resultaat gehaald, bij welke productiestraten is het minste uitval.
In dit artikel zal ik diverse methoden de revue laten passeren, waarmee dat mogelijk is, met hun voor- en nadelen. Het maakt dan niet uit of het over de beste 3 gaat, de hoogste 5 scores of de slechtste 10.

Methode 1: easy does it!

Stel je hebt een overzicht van verkochte aantallen per maand en je wilt weten welke maand het beste is geweest?

Sorteer op Aantal en je bent klaar!

Voordeel: heel snel resultaat.

Deze methode kent echter een paar nadelen:

  1. je past op deze manier de bron-gegevens aan en dat druist in tegen regel 1 van goed Excel-gebruik.
  2. wijzigen de gegevens of komen er maanden bij, dan moet de sortering opnieuw worden doorgevoerd
  3. resultaten moeten ‘met de hand’ overgenomen worden in een rapportage

Methode 2:  maak een grafiek

In het tabblad Top5 van het Voorbeeldbestand zijn de gegevens uitgezet in een grafiek; ik heb als type een Spreidingsgrafiek gekozen, zodat de datums op een juiste tijdschaal op de as worden weergegeven en niet ‘gewoon’ achter elkaar (wijzig de laatste datum maar eens in 1-12-18).
Ga met de muis naar de hoogste waarde en Excel zal de onderliggende gegevens van het punt van de grafiek laten zien.

Voordeel: snel resultaat, waarbij goed is te zien waar de hoogste (of laagste) resultaten zitten, wat (globaal) de verschillen zijn en of er veel vergelijkbare resultaten zijn. In het voorbeeld zijn er zes  resultaten boven de 15 en nog drie anderen er vlak bij; of een top-3 (of top-5) hier veel zegt?

Nadeel: resultaten moeten ‘met de hand’ opgezocht en overgenomen worden in een rapportage.

NB wil je kijken wat er met de grafiek gebeurt als je andere brongegevens hebt, kopieer dan de cellen uit kolom D en plak ze ‘hard’ in kolom C (via Plakken speciaal/Waarden).
In de kolom Random worden door Excel telkens nieuwe data gegenereerd mbv de formule: =ASELECTTUSSEN(1;2000)/100 ofwel een willekurig getal tussen 1 en 2000 (inclusief grenzen) en deel dat door 100, zodat een getal tussen 1 en 20 (met maximaal 2 decimalen) ontstaat.

Methode 3: gebruik een Draaitabel

  1. selecteer een willekeurige cel in de brondata; deze zijn vastgelegd in de vorm van een Excel-tabel met de naam tblData. Hoe dat moet en wat de voordelen zijn: kijk op 10 voordelen van tabellen en Tabellen (deel 2).
  2. kies in de menutab Invoegen in het blok Tabellen de optie Draaitabel en klik op het tussenscherm op OK.
  3. sleep in Lijst met draaitabelvelden het veld Maand naar Rijlabels en het veld Aantal naar het Waardegebied
  4. klik rechts op één van de getallen in de tweede kolom en kies de optie Sorteren en dan Sorteren van hoog naar laag
  5. bijna klaar; Excel laat nu nog alle maanden zien, maar we willen alleen maar de beste 5 resultaten: klik rechts op één van de maanden, kies Filteren en dan de optie Top-tien.
    Zorg dat in het tweede veld in plaats van de standaard 10 een 5 komt, de rest is OK.

Bekijk het resultaat in het tabblad Top5 van het Voorbeeldbestand.

Voordeel: de resultaat-tabel kan zo in een standaard-rapportage worden overgenomen.

Nadeel: wijzigen de gegevens of komen er nieuwe maanden bij? Vergeet niet de draaitabel te Vernieuwen (door ergens in de tabel rechts te klikken).

NB1 doordat de gegevens in een Excel-tabel zijn vastgelegd, ‘weet’ Excel wanneer er nieuwe regels zijn toegevoegd, dus de bron van de draaitabel hoeft niet meer aangepast te worden.

NB2 het Top-10-filter kan ook ingesteld worden door op het blokje achter Maand te klikken. De Top-10 vindt u dan onder Waardefilters.

NB3 wilt u niet de 5 bovenste gegevens maar de onderste, wijzig dan Top in Onder.

Optie 2 van het Top-10-filter

Dit Top-10-filter kent nog 2 andere opties. Wanneer u in plaats van Items kiest voor Procent dan zal Excel die maanden laten zien, die er samen voor zorgen, dat het ingestelde percentage minimaal wordt bereikt.

20% van 239,72 (het totaal Aantal uit het tabblad Top5 van het Voorbeeldbestand) is 47,944, dus aug-17 is nog nodig om dit Totaal te bereiken.

Optie 3 van het Top-10-filter

De derde mogelijkheid is om een harde grens meet te geven; in dit voorbeeld willen we die maanden zien die samen minimaal 100 opleveren.

Methode 4: gebruik de functie GROOTSTE

De functie GROOTSTE kent 2 parameters:
* de Matrix (bereik), waarin het grootste getal moet worden gezocht
* K,  het volgnummer; wil je de grootste waarde dan is K=1, wil je de één na grootste dan is K=2 etc.

Dus de formule in cel K5
=GROOTSTE(tblData[Aantal];I5)
haalt uit de kolom Aantal van de tabel tblData het grootste getal op (cel I5 is gelijk aan 1).

Deze formule is naar beneden gekopieerd; zie het tabblad Top5 in het Voorbeeldbestand.

Nu moet nog kolom J met de bijbehorende maand gevuld worden. In cel J5 staat daartoe de volgende formule:
=INDEX(tblData[Maand];VERGELIJKEN(K5;tblData[Aantal];0))
De functie Index zoekt in de kolom Maand van de tabel tblData die rij op, die overeenkomt met het resultaat van de functie Vergelijken; deze functie beoordeelt op welke positie de inhoud van cel K5 staat in de kolom Aantal van de tabel tblData. De 0 geeft aan dat er een exacte match moet zijn (zie ook Alternatief voor vert.zoeken en Zoeken: Index en Vergelijken).

Voordelen: de resultaat-tabel kan zo in een standaard-rapportage worden overgenomen en de tabel past zich automatisch aan aan wijzigingen in de brongegevens en hoeft dus niet vernieuwd te worden zoals bij een draaitabel.

Nadelen: ‘ingewikkelde’ formules nodig en methode werkt niet altijd goed als getallen in de kolom Aantal gelijk  zijn.

NB1 de resultaten van de GROOTSTE-tabel kunnen toegevoegd worden aan de grafiek.

NB2 wilt u niet de top-gegevens achterhalen maar de onderste, gebruik dan de functie KLEINSTE.

Methode 4: gebruik de functie GROOTSTE (bis)

Op het tabblad Top5_2 van het Voorbeeldbestand staat een nieuw databestand, waarin ook dubbele aantallen voorkomen.

Maken we hierop een draaitabel met een Top-5, dan lost Excel het probleem voor ons simpel op: hij maakt automatisch een Top-6!

 

Er is natuurlijk wel een oplossing om het tweede nadeel van de vorige methode op te vangen.
Het opzoeken van het grootste aantal (en de één na grootste etc) is ook hier niet het probleem (zie kolom J), maar wel het opzoeken van de daarbij behorende maand (het Aantal 16 kan horen bij jun-16, okt-16 en jun-17).

Ieder resultaat van de functie GROOTSTE krijgt in kolom K een SubNr mee. In cel K4 staat daartoe de formule:
=AANTAL.ALS($J$4:J4;J4)
Hiermee wordt het aantal keren geturfd, dat de waarde van cel J4 (de laatste parameter) voorkomt in het bereik $J$4:J4. Tsja, dat is natuurlijk altijd 1!
Maar wat gebeurt er als we de formule naar beneden kopiëren? In cel K5 komt dan automatisch =AANTAL.ALS($J$4:J5;J5): er wordt gekeken hoe vaak J5 voorkomt in het bereik van J4 tot en met J5! Op deze manier krijgen dubbelen ieder een  eigen volgnummer.

Het opzoeken van de corresponderende maand is een uitdaging. In cel L4 staat de formule:
={INDIRECT(“B”&KLEINSTE(((tblData2[Aantal]=J4)*RIJ(tblData2[Aantal]))+((tblData2[Aantal]<>J4)*10^8);K4))}

OEPS! Met dank aan Chandoo heb ik dit alternatief gevonden. Probeer de formule te begrijpen door in de menutab Formules in het blok Formules controleren de optie Formules evalueren te kiezen:

  1. Eerst zoeken we alle aantallen, die  gelijk zijn aan J4 (tblData2[Aantal]=J4); dit levert een reeks op met Waar en Onwaar
  2. deze reeks vermenigvuldigen we met de overeenkomende rijnummers (*RIJ(tblData2[Aantal])), waardoor we een reeks overhouden met rijnummers, waarin J4 voorkomt, en nullen
  3. als J4 NIET in een rij voorkomt, dan tellen we daar een groot getal (1 met 8 nullen) bij op (+((tblData2[Aantal]<>J4)*10^8))
  4. dan nemen we de kleinste (of één na kleinste etc.; afhankelijk van K4) van die reeks (KLEINSTE)
  5. als laatste wordt met INDEX de waarde in die rij in kolom B opgehaald.

Komt u er niet uit? Neem contact op met G-Info.

LET OP de formule in L4 is ingevoerd door op Ctrl-Shift-Enter te drukken (CSE-methode); het is een zogenaamde matrix- of array-formule. De formule kan we gewoon naar beneden gekopieerd worden.
Zie voor meer uitleg over de gehanteerde methode het artikel SOMPRODUCT: meer dan SOM en PRODUCT. Ook de voorbeelden uit de werkmap, die Ton Spies mij toestuurde, kunnen hiervoor gebruikt worden.

Voordelen: de resultaat-tabel kan zo in een standaard-rapportage worden overgenomen en de tabel past zich automatisch aan aan wijzigingen in de brongegevens en hoeft dus niet vernieuwd te worden zoals bij een draaitabel.

Nadeel: zeer ‘interessante’ formules zijn nodig.


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