Tagarchief: Aselect

VBA-routines versnellen

Wanneer u net begint met het ontwikkelen van VBA-routines om werkzaamheden te automatiseren, zult u versteld staan van de tijdwinst die dit kan opleveren.

Maar ….  als dan de hoeveelheid gegevens, waarmee u werkt, gaat groeien dan kan het zo maar zijn dan zo’n routine tientallen secondes bezig is en dat u denkt: kan dat niet sneller!

Het antwoord is (hoogst waarschijnlijk): JA, dat kan zeker sneller.

In dit artikel enkele tips voor efficiëntere VBA-routines.

Gebruik van Select

Wanneer u bepaalde handelingen in Excel wilt automatiseren, is de macro-recorder van VBA een uiterst handig hulpmiddel. U neemt de toetsaanslagen op en volgende keer kunt u met één druk op een knop diezelfde toetsaanslagen herhalen. Vaak wordt zo’n recorder-opname dan gebruikt als basis voor een uitgebreidere routine.

In dit voorbeeld geldt dat ook (zie Blad1 in het Voorbeeldbestand):

  • we hebben een tabel (met de naam Tabel1) met 2 kolommen: in de eerste staan maanden en in de tweede worden willekeurige bedragen met 2 decimalen gegenereerd, liggend tussen 1 en 2:
    =ASELECTTUSSEN(100;200)/100
  • het eerste bedrag (cel C3) heeft de naam Bedrag1 gekregen, terwijl het bereik van alle bedragen de naam Bedr heeft.
  •  in kolom F worden het aantal regels, het totaalbedrag en het gemiddelde bepaald. Het tweede gemiddelde is gelijk aan het  totaalbedrag gedeeld door het aantal; een simpel voorbeeld van een ingebouwde controle.
  • als voorbeeld heb ik een VBA-routine gemaakt (gebaseerd op een macro-opname), die ook dezelfde berekeningen uitvoert.
    Deze routine selecteert cel Bedrag1  en “loopt” zolang er geen lege cel is naar beneden; ondertussen wordt het aantal geturfd en een lopend totaal bepaald:
  • op het einde van de VBA-routine zorgen we dat de cursor weer in cel A1 komt en worden de resultaten via een pop-up zichtbaar gemaakt.
    Ook de verstreken tijd is in de pop-up opgenomen.
  • klik op de button Select in het Voorbeeldbestand om de VBA-routine uit te voeren.

NB1 door de rechte haken rond een naam weet VBA, dat het een naam in de Excel-sheet is, dus niet de naam van een VBA-variabele.

NB2 zo ziet een info-scherm op de MAC er uit; de PC-versie oogt anders.

NB3 mijn MAC heeft dus ruim 11 seconden nodig voor de 1.000 verplaatsingen en berekeningen! Op een PC duurt het (uiteraard afhankelijk van de soort machine) nog geen seconde.

NB4 wil je de gebruikte routine zien: klik Alt-F11 of ga via de menutab Ontwikkelaars naar Visual Basic.

Gebruik van Select-2

Het grootste gedeelte van de benodigde tijd van de vorige VBA-routine gaat zitten in het fysiek verplaatsen van de cursor (via Offset).

Met behulp van het VBA-commando Application.ScreenUpdating = False kunnen we er voor zorgen, dat het verversen van het scherm niet meer plaats vindt. Het resultaat is, dat de MAC binnen een halve seconde klaar is!

Klik op de button Select2 om de alternatieve VBA-routine uit te voeren.

NB vergeet niet op het einde van zo’n routine de scherm-verversing weer aan te zetten: Application.ScreenUpdating = True

Gebruik van Range

Veel Excel-ontwikkelaars vinden het gebruik van de Select-methode uit den boze. Bij een prima alternatief wordt het belangrijkste gedeelte van de routine:

De berekeningen worden uitgevoerd voor iedere cel in het bereik Bedr. De computer gaat nu in sneltrein-verhaal langs de inhoud van de cellen zonder dat er een verplaatsing van de cursor nodig is.

De MAC is nu binnen 1/100 van een seconde klaar! Test de routine door op de button Range te klikken.

Gebruik van een array

In het vorige voorbeeld hebben we gebruik gemaakt van een Excel-bereik (in dit geval met de naam Bedr).
Bij heel grote bestanden kan er tijdwinst geboekt worden door dit bereik eerst in een aparte array (rij, reeks, matrix) te plaatsen:

  • het bereik met de naam Bedr (de truc met de rechte haken werkt nu niet meer) wordt cel voor cel in een array aBedr gestopt
  • voor ieder element van de array wordt het lopende totaal bepaald (Ubound=UpperBound=bovengrens)

Sneller dan het geluid!? De routine heeft minder dan 1/10.000 seconde nodig.

Gebruik van Tabel

Een alternatief voor de vorige oplossing is:

  • de kolom Bedrag uit Tabel1 wordt in de array aBedr gestopt
  • de bovengrens van de array plaatsen we in de variabele N (in het vorige voorbeeld moet de bovengrens iedere keer opnieuw worden bepaald; niet efficiënt)

Gebruik van Tabel-2

In een variant van de vorige oplossing schrijven we de resultaten ook nog weg in de Excel-sheet (in cellen van kolom G met de namen Aantal, Totaal en Gem1_):

NB1 cel G5 heeft NIET de naam Gem1. Er bestaat al een cel met die naam, namelijk de cel in kolom GEM, rij 1. Dit wordt vaak opgelost door een underscore er voor of er achter te zetten.

NB2 doordat er cellen in de sheet worden aangepast gaat Excel alle formules doorrekenen; dus alle bedragen in Bedr veranderen NADAT de VBA-berekening is uitgevoerd.

Effect van groter bestand op de berekeningen

Door Tabel1 uit te breiden naar bijvoorbeeld 100.000 regels is het verschil in benodigde tijd beter te beoordelen. Trek daartoe de vulgreep van de tabel rechtsonder naar beneden.


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

Sparklines

Volgens Microsoft:

Een sparkline is een kleine grafiek in een cel van een werkblad met een visuele weergave van de gegevens. Gebruik sparklines om trends in een reeks waarden aan te geven, zoals seizoensmatige stijgingen of dalingen of economische cycli, of om maximum- en minimumwaarden te markeren.

Anders gezegd: de sparkline-grafiek is niet bedoeld om details te onderscheiden, maar vooral om snel bepaalde trends of uitschieters te signaleren.

Hieronder gaan we eerst kijken hoe je een sparkline in je werkmap opneemt en daarna worden enkele extra mogelijkheden belicht en ook een aandachtspunt bij het gebruik van draaitabellen.

Hoe maak je een sparkline?

Laten we een simpel voorbeeld nemen: de inkomsten en uitgaven per maand en het resultaat daarvan. Zie het tabblad InUit in het Voorbeeldbestand.

LET OP de getallen zullen afwijken van de figuur hierboven. Met de functie ASELECTTUSSEN worden iedere keer nieuwe cijfers gegenereerd (druk op F9); dit om de consequenties voor de grafieken makkelijk te kunnen laten zien.

  1. selecteer het blokje getallen, dat in grafieken moet worden weergegeven (in het voorbeeld de cellen C4:N6; dus zonder de verklarende teksten! Een sparkline geeft alleen getallen weer.)
  2. kies in de menutab Invoegen in het blok Sparklines de optie Lijn
  3. in het nieuwe venster, dat automatisch opent, is het gegevensbereik al ingevuld. De plaats van de sparklines moet nog ingevuld worden. In dit geval moeten die achter de gegevens (dus in kolom O) komen; we vullen bij Locatiebereik O4:O6 in.
  4. klik op OK
  5. maak de kolom met de sparklines (in dit geval O) zo breed als gewenst om de resultaten het beste tot hun recht te laten komen.

LET OP het aantal cellen in locatiebereik moet gelijk zijn aan het aantal regels in het Gegevensbereik anders krijg je een foutmelding.

NB1 De sparklines mogen overal in het werkblad staan en ze mogen onder of naast elkaar komen. Maar zoals op de Microsoft-website staat:  “Plaats een sparkline dichtbij de gegevens voor het optimale effect.

NB2 Je kunt bij punt 1 ook beginnen met de selectie van de locatiecellen (waar moeten de sparklines komen). In stap 3 moet dan het gegevensbereik worden opgegeven.

Sparklines opmaken

Hoewel in bovenstaand voorbeeld het verloop in de tijd al goed zichtbaar is, kunnen we bepaalde kenmerken nog accentueren:

  1. selecteer één van de sparklines; klik bijvoorbeeld met de muis in cel O4
  2. kies in de nieuwe menutab Hulpmiddelen voor sparklines de optie Ontwerpen
  3. vink in het blok Weergeven de opties Hoge punt en Negatieve punten aan
  4. kies in het blok Stijl eventueel een andere voor-geprogrammeerde opmaak

Deze opmaak wordt automatisch tegelijkertijd toegepast op alle drie de sparklines. Dit komt omdat Excel deze drie grafieken in één groep heeft geplaatst (klik in cel O4, kies in de menutab Hulpmiddelen voor sparklines de optie Ontwerpen en kies de optie Gegevens bewerken; nu zie je dat alle drie de sparklines bij elkaar horen).

Bij de inkomsten is de hoogste waarde (Hoge punt) goed, maar bij de uitgaven is dit natuurlijk niet het geval. De toppen moeten een verschillende kleur krijgen, daarom mogen de grafieken niet meer als één groep geselecteerd worden :

  1. selecteer de cellen met sparklines (O4:O6) en kies in de menutab Hulpmiddelen voor sparklines binnen de optie Ontwerpen de optie Groep opheffen
  2. selecteer alleen cel O4; kies binnen de optie Markeringskleur bij Hoge punt een groene kleur
  3. selecteer alleen cel O5; kies binnen de optie Markeringskleur bij Hoge punt een rode kleur
  4. selecteer alleen cel O6; kies binnen het blok Groeperen de optie As/As weergeven

NB Klik in cel O4, kies in de menutab Hulpmiddelen voor sparklines de optie Ontwerpen en kies de optie Gegevens bewerken; nu zie je dat deze sparkline alleen bij gegevens uit regel 4 hoort.

In het tabblad InUit2 van het Voorbeeldbestand is nog een andere optie gebruikt; bij de eerste 2 grafieken is als Type de optie Kolom gekozen:

NB de sparklines worden in Excel als achtergrond van een cel opgenomen. Dat betekent dat we nog steeds andere informatie in de cel kunnen opnemen, bijvoorbeeld toelichtende tekst.

Sparklines en draaitabellen

Wanneer je sparklines achter een draaitabel plaatst kan het voorkomen, dat wanneer deze draaitabel ‘groeit’ door nieuwe informatie die aan de bron wordt toegevoegd, de draaitabel over de sparklines valt.

Een oplossing is om van te voren te zorgen dat de draaitabel al alle mogelijke items bevat, zodat deze nooit meer groter kan worden (tabblad Ovz in het Voorbeeldbestand):

Aan de bron zijn hiertoe lege regels voor Nov en Dec toegevoegd; zie tabblad Data in het Voorbeeldbestand.

NB Het Resultaat staat niet in de bron, maar wordt in de draaitabel berekend:

  1. selecteer in de draaitabel één van de soorten(In of Uit); klik bijvoorbeeld met de muis in cel A5
  2. kies in de menutab Hulpmiddelen voor draaitabellen binnen Opties in het blok Berek. de optie Velden, Items en sets

    Kies dan de optie Nieuw item.
    NB we gaan binnen het veld Soort een nieuw item maken gebaseerd op andere items binnen Soort. De optie Nieuw veld wordt gebruikt om een nieuw veld (naast Soort, Maand en Bedrag) te maken.
  3. in het vervolgvenster geven we het nieuwe item een Naam, bijvoorbeeld Res (Resultaat is al toegevoegd) en als Formule tikken we in: = In-Uit en klikken op Toevoegen en OK

 

 


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

EK-2016: wie wordt kampioen?

ek2016Er is al veel over geschreven: Nederland is er niet bij!

Waar niet bij? Ik bedoel het EK-voetbal natuurlijk. Dus dan maar de focus op België? Of toch Duitsland? Of gastland Frankrijk? Tsja, wie zal op 10 juli aangewezen worden als de kampioen van Europa?

Daarom de WK-voorspeller van 2 jaar geleden maar eens opgepoetst; alle landen ingevoerd, schema nagekeken om tot de ontdekking te komen, dat de indeling van de achtste finales wel heel anders gaat dan bij het WK.

In dit artikel bespreek ik dan ook niet meer de werking en de nauwkeurigheid van de voorspeller (kijk daarvoor naar WK- voorspeller deel 1, deel 2 en deel 3), maar alleen naar de indeling van de ploegen na de groepswedstrijden.
ek2016aO ja, België heeft volgens dit Excel-model trouwens ongeveer 10% kans om kampioen te worden!

Kleine lettertjes: als het model klopt tenminste en alle aannames de werkelijkheid goed benaderen.

Indeling achtste finales

Bij dit EK gaan de nummers 1 en 2 automatisch over naar de knock-out-fase. Van de 6  landen, die bij de groepswedstrijden op plaats 3 eindigen, gaan de beste 4 verder.
ek2016bVoor de indeling van deze 4 is een ingewikkeld schema bedacht (zie hiernaast; bron is WIKIPEDIA)).
De indeling is dus afhankelijk van de groepen waaruit de 3e plaatsen komen (aangeduid met de letters A t/m F).

In het Voorbeeldbestand worden daarom de volgende stappen doorlopen om tot de juiste indeling van de achtste finales te komen (zie tabblad Groepsfase):

  1. in de cellen X4 t/m X9 worden de punten van de landen opgehaald die derde zijn geworden in de 6 groepen
  2. met behulp van de functie GROOTSTE (waarbij ook kan worden aangegeven of je echt de grootste wilt hebben of de een na grootste enz) worden de beste 4 resultaten opgehaald in de cellen X12 t/m X15
  3. in de cellen Y12 t/m Y15 worden dan met behulp van de functies INDEX en VERGELIJKEN de corresponderende groepsletters opgehaald
  4. nu de moeilijkste stap: in cel Y16 (met de naam Serie3) moeten de 4 groepsletters gesorteerd worden.
    Om de functie GROOTSTE te kunnen gebruiken moeten we letters eerst vertalen naar getallen met de functie CODE (een A wordt dan 65, een B 66 etc).
    Als we via GROOTSTE de getallen in de juiste volgorde hebben staan, wordt het getal weer terugvertaald naar een letter met de functie TEKEN. De letters worden aan elkaar ‘geplakt’ met behulp van het &-teken.
    We moeten Excel nog wel aangeven, dat in de formule de functie GROOTSTE op een serie cellen moet worden losgelaten; daarom is de invoer van de formule niet met Enter afgesloten maar met Ctrl-Shift-Enter (we hebben dus een zogenaamde CSE-formule ingevoerd; zie ook het artikel SOMPRODUCT: meer dan SOM en PRODUCT).
  5. Het resultaat in cel Y16 (=Serie3) wordt in het tabblad Finales gebruikt om de juiste tegenstander van de poule-winnaars op te halen; bijvoorbeeld in cel B9 moet de tegenstander van de winnaar van groep B komen:
    =VERT.ZOEKEN(Serie3;Series3;3;ONWAAR)
    Series3 is de naam van de cellen met het Wikipedia-overzicht in het tabblad Groepsfase; de tegenstander van de B-winnaar staat in de derde kolom.

Verwerken resultaten

De eerste wedstrijden zijn ondertussen gespeeld. Geef in kolom M van het tabblad Groepsfase de juiste punten aan de betreffende ploeg.

Wanneer je de resultaten op deze manier in het spreadsheet verwerkt, blijken de kansen voor Duitsland en Frankrijk dankzij hun gewonnen wedstrijden al iets te zijn toegenomen.

 


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

Slicers in Excel

SlicerEén van de weinige woorden, die in de Nederlandstalige versie van Excel niet zijn vertaald, is Slicer.
Ik merk in de praktijk, dat het niet voor iedereen meteen duidelijk is, wat er in dit geval mee wordt bedoeld. In ieder geval niet zo’n machine zoals hiernaast afgebeeld!

De functionaliteit Slicer vinden we sinds versie 2010 terug bij het onderdeel Draaitabellen en is bedoeld om sneller, makkelijker en overzichtelijker selecties te maken/filters te definiëren.

In dit artikel zal ik de meest gangbare toepassingen toelichten. Hoewel er een directe koppeling tussen een Slicer en een Draaitabel bestaat, zullen we zien, dat met een klein beetje VBA de inhoud van de slicers ook op andere plaatsen en op andere manieren gebruikt kan worden.

LET OP Slicers worden door Excel voor de Mac niet ondersteund; bijgaand Voorbeeldbestand kunt u op de Mac dan ook beter niet opstarten.

Basisgegevens

Slicer1Om het gebruik van een Slicer toe te lichten hebben we basisgegevens nodig aan de hand waarvan we een draaitabel kunnen maken.
In het Voorbeeldbestand zien we in het tabblad Basis een fictief omzet-overzicht, gesplitst naar Jaar, Maand, Regio en Soort product.
Slicer2De gegevens zijn opgeslagen in de vorm van een tabel met behulp van de optie Invoegen/Tabel. Excel kent daarbij automatisch de naam Tabel1 aan dit gebied toe.
Een groot voordeel hiervan is, dat als deze tabel wordt uitgebreid met nieuwe regels of kolommen, alle draaitabellen, die hierop gebaseerd zijn, automatisch rekening houden met die uitbreiding. Hetzelfde geldt ook, wanneer er gegevens uit de tabel worden verwijderd: bij het vernieuwen van de draaitabel zul je merken, dat de draaitabel zich daar automatisch aan aanpast.

Slicer3NB1 een ander voordeel van een tabel merk je, wanneer je in het bestand naar beneden scrolt: zonder dat er titels geblokkeerd hoeven te worden, blijven de kopjes van de tabel zichtbaar.

NB2 het voorbeeld is gemaakt door voor alle kolommen willekeurige resultaten te genereren met behulp van de functie ASELECTTUSSEN, eventueel aangevuld met de functie KIEZEN (zie de cellen H3 t/m L3)

Draaitabel

In het basisbestand zijn 100.000 regels/records opgenomen. Met behulp van een draaitabel kunnen deze razendsnel gerubriceerd worden: welke jaren komen voor, zijn alle maanden gevuld, welke regio’s worden gebruikt, welke producten hebben een omzet gegenereerd en wat zijn de bijbehorende bedragen?

Hoe maken we een draaitabel?

  1. Slicer4plaats de cursor ergens in de tabel met basisgegevens, bijvoorbeeld door in cel B4 te klikken
  2. kies in het menutabblad Invoegen in het blok Tabellen de optie Draaitabel
  3. u ziet dan een tussenscherm, waarin vrijwel altijd de voorgestelde keuzes zullen voldoen: Excel heeft de tabel waarin de cursor staat als basis geselecteerd en het resultaat zal in een nieuw werkblad komen
  4. klik op OK
  5. Slicer5er opent zich een nieuw werkblad met rechts een overzicht van alle velden/kolommen uit de geselecteerde tabel.
    Voor ons eerste overzicht vinken we het Jaar, de Regio en de Omzet aan.
    Excel zal proberen te bedenken waar we de gegevens willen hebben. In dit geval gaat hij de Som van de jaren bepalen, maar dat is natuurlijk niet de bedoeling: sleep het blokje “Som van Jaar” van -waarden naar de Kolomlabels en Excel gaat de omzet uitsplitsen naar jaar.

Slicer7“Sneller dan het geluid” zien we dat de verdeling van de omzet in dit geval redelijk evenredig over de regio’s en jaren heeft plaats gevonden (niet vreemd natuurlijk omdat we de gegevens aselect hebben gegenereerd!).

Om de omzet beter te kunnen lezen gaan we de opmaak aanpassen: dit doen we NIET door de opmaak van de cellen B2:E9 aan te passen; als er straks een jaar bijkomt moeten we die kolom dan weer opnieuw opmaken.
Nee, klik met de rechter muisknop op één van de cijfers (bijvoorbeeld cel B5), kies Getalnotatie en binnen de categorie Getal geen decimalen en een scheidingsteken voor duizendtallen.

Filters in Draaitabellen

In het Voorbeeldbestand is op het tabblad Draai1 een ander voorbeeld van een draaitabel gegenereerd: in de rijen is de omzet opgesplitst naar de maanden en in de kolommen naar product.
Slicer8Om een filtering naar Jaar en/of Regio mogelijk te maken zijn die 2 velden in het blok Rapportfilter geplaatst. Deze 2 velden zijn daardoor boven de draaitabel terecht gekomen; door middel van de vinkjes in de cellen B1 en B2 kan dan een jaar of regio geselecteerd worden.

Daarnaast zijn nog de volgende aanpassingen doorgevoerd:

  1. Slicer9via het tabblad Ontwerpen in Hulpmiddelen voor Draaitabellen hebben we binnen Rapportindeling de Tabelweergave gekozen.
    Welke optie het meest geschikt is, hangt zeer sterk af van de inhoud en opzet van de draaitabel; experimenteer hiermee!
    NB Hulpmiddelen voor draaitabellen is alleen zichtbaar wanneer de cursor ergens in de draaitabel staat.
  2. naast de Som van Omzet hebben we ook aantallen en gemiddelde omzet toegevoegd. Om dit te bereiken sleept u de Omzet uit de Lijst met draaitabelvelden opnieuw naar het waarden-gebied.
    Excel zal dan opnieuw de som bepalen; om dit te veranderen moet u in de draaitabel rechts klikken op een cel, die u wilt wijzigen; kies de optie Waarden samenvatten per en kies daar Aantal of Gemiddelde

Compacte draaitabellen

Slicer10Eén van de nadelen van een standaard-draaitabel is dat de kolommen nogal breed worden doordat Excel namen genereert als Som van Omzet etc.

Gelukkig is dit snel aan te passen: in het Voorbeeldbestand is in het tabblad Draai2 de kop in cel B6 aangepast door in die cel nieuwe tekst in te tikken (in dit geval Aantal).

LET OP u zult merken, dat cel C6 zich niet laat veranderen in Omzet; Excel weet dan niet meer of u nu het veld uit de basisgegevens bedoelt of de kolom in de draaitabel. In dit geval hebben we dat opgelost door een spatie achter het woord Omzet te plaatsen! Had er natuurlijk ook vóór mogen staan.

Slicers

Om het filteren (zoals in het plaatje hierboven voor Jaar en Regio) makkelijker te maken is in Excel de optie Slicer ontwikkeld.

Hoe maakt u een slicer?

  1. Activeer de draaitabel, waarvoor een slicer gemaakt moet worden. Klik daartoe met de muis op één van de cellen van de draaitabel.
  2. in het tabblad Hulpmiddelen voor draaitabellen, dat dan beschikbaar komt, kiest u Opties
  3. in het blok Sorteren en filteren kiest u de button Slicer invoegen
  4. alle velden uit de brongegegevens komen in aanmerking; in dit voorbeeld vinken we alleen Jaar en Regio aan. Klik op OK.
  5. Standaard worden de slicers verticaal weergegeven: alle opties onder elkaar.
    Omdat nu de slicers geselecteerd zijn, ziet u bovenaan Hulpmiddelen voor slicers. Kies daar eventueel een andere Slicerstijl en pas het aantal gewenste Kolommen aan (het aantal opties naast elkaar)


Het filteren doet u nu door de betreffende button aan te klikken.
Meerdere selecties binnen een categorie nodig? Hou Ctrl ingedrukt en klik op de gewenste buttons.
Alle items nodig? Klik op Slicer12 .

In het tabblad Draai3 van het Voorbeeldbestand ziet u, dat de velden Jaar en Regio in het Rapportfilter niet meer nodig zijn; deze zijn vervangen door de slicers.

LET OP zorg bij het printen van de draaitabel, dat ook de slicers zichtbaar zijn, anders is niet duidelijk welke filtering is toegepast.
In het tabblad Draai3 is een draaigrafiek weergegeven (in dit geval een weinig-zeggende, maar voor het voorbeeld is de inhoud niet relevant); deze is gebaseerd op de onderliggende draaitabel en dus ook op de slicers. Wanneer deze grafiek apart geprint zou worden is geen enkele informatie over een eventuele filtering zichtbaar.

Inhoud slicer

Om te voorkomen, dat bij bovenstaand probleem er iedere keer handmatig een tekst aan het overzicht of de grafiek moet worden toegevoegd, zou een dynamische referentie naar de inhoud van de slicers mooi zijn. Dit zou dan in een tekst kunnen worden opgenomen.

Helaas kent Excel deze mogelijkheid (nog) niet; er is een interne koppeling tussen de inhoud van de slicer en de filtering in de draaitabel, maar die is niet standaard met een functie uit te lezen.

slicer13Op internet heb ik echter een UDF (User Definied Function) gevonden op de site jkp-ads.com waarmee dit wel mogelijk is. Zo’n UDF wordt in een module van VBA vastgelegd (zie hieronder).

In het tabblad Draai4 van het Voorbeeldbestand is deze toegepast om de tekst in cel B7 op te kunnen bouwen:
=”Omzet voor ” & ALS(I2=”Alles”;”alle jaren”;I2) & ” en ” & ALS(J2=”Alles”;”alle regio’s”;J2)

Met behulp van het &-teken worden teksten aan elkaar gekoppeld: allereerst de tekst Omzet voor, dan (afhankelijk van de inhoud van cel I2) de tekst alle jaren of de inhoud van I2 etc.

Maar in I2 en J2 staat toch niets? Op het oog niet nee, maar de tekstkleur van die cellen is op Wit ingesteld!

Laten we I2 eens bekijken:
=GetSelectedSlicerItems(“Slicer_Jaar1”)

Hier wordt de UDF GetSelectedSlicerItems aangeroepen met de parameter Slicer_jaar1. Dit is de naam die we terugvinden als we rechtsklikken op de eerste slicer en dan de Slicerinstellingen bekijken.

slicer14

VBA gebruikt de omschrijving zoals vermeld achter Naam om in formules te gebruiken.

Grafiektitel

Dezelfde tekst die boven de draaitabel staat, willen we ook als grafiektitel:

  1. klik op de grafiek
  2. kies in Hulpmiddelen van Draaigrafieken, die dan zichtbaar wordt, de optie Indeling
  3. kies in het blok Labels, de optie Grafiektitel en kies Boven grafiek
  4. tik dan direct in de formulebalk ( dus achter de FunctieInvoeren) de formule: =Draai4!$B$7 of tik in = en klik met de muis op de betreffende cel
  5. druk de Enter-toets in

VBA

Deze keer geen uitleg van de VBA-routine; ik denk dat hij voor een beginnende VBA’er goed te volgen zal zijn.

Maar hoe voegt u een routine, die u ergens hebt gevonden (op internet, in een andere Excel-toepassing) aan uw eigen werkmap toe?

  1. kopieer de routine, die hebt gevonden (bijvoorbeeld met behulp van Ctrl-C)
  2. ga naar de werkmap waar u de routine wilt hebben
  3. klik Alt-F11, de Visual Basis Editor opent dan
  4. klik in de menu-tabs op Invoegen en dan Module
  5. in de zo nieuw gemaakte module plakt u de routine uit het  de eerste stap (bijvoorbeeld met behulp van Ctrl-V)

De UDF GetSelectedSlicerItems kan de volgende resultaten opleveren:

  1. Alles, als er geen filter is gedefinieerd
  2. Niets, als er door andere filtering voor deze slicer geen mogelijkheden zijn
  3. Geen slicer gevonden, als de tekst die als parameter wordt meegegeven geen bestaande slicer is
  4. het gekozen filter of bij meervoudige filtering de keuzes, gescheiden door een komma

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

Loterij

loterij2Vorige week kreeg ik de vraag, hoe je met Excel het makkelijkst de winnaars van een wedstrijd zou kunnen selecteren.
De bedoeling was om uit een (grote) hoeveelheid goede inzenders willekeurig drie personen er uit te lichten.

Een poosje geleden heb ik al iets geschreven over steekproeven; dezelfde systematiek is ook voor dit probleem toepasbaar.
Maar deze keer een iets andere benadering.

Loterij1

loterijIn het Voorbeeldbestand heb ik in het tabblad Loterij een overzicht opgenomen van alle verkochte loten, inclusief de corresponderende naam (in dit geval de lotnummers 1 t/m 100 en een fictieve naam, die daarvan is afgeleid; bijvoorbeeld bij het eerste lot hoort Naam 1).
NB in plaats van lotnummers kan dit overzicht ook alle goede inzenders van een wedstrijd voorstellen

loterij2Op het tabblad Uitslag worden 3 winnaars geselecteerd:

  1. Allereerst tellen we het aantal verkochte loten (of dus het aantal goede inzenders). In cel C2 staat daartoe de formule:
    =AANTAL(Loterij!B:B)
    Ofwel tel het aantal getallen in kolom B van het tabblad Loterij.
    LET OP zijn in kolom B geen nummers opgenomen maar teksten (bijvoorbeeld A1, A2, B1 etc) dan moet u de formule AANTALARG gebruiken en van het resultaat 1 aftrekken, omdat dan ook het woord LotNr in cel B2 wordt meegeteld
  2. uit het aantal verkochte loten worden dan 3 willekeurige getallen getrokken (cellen C5, C6 en C7):
    =ASELECTTUSSEN(1;$C$2)
    Dus neem een willekeurig getal tussen 1 en de waarde in C2 (in het voorbeeld dus 100); de grenzen doen ook mee.
  3. in cel D5 zoeken we dan de corresponderende naam op:
    =VERT.ZOEKEN(C5;Loterij!B:C;2)
    Deze formule zoekt de waarde uit cel C5 op in kolom B van Loterij en geeft als resultaat de corresponderende cel uit kolom C.
    LET OP bovenstaande formule werkt alleen goed, als de lotnummers in volgorde in kolom B staan en er geen “gaten” zijn. Beter is om de formule
    =VERT.ZOEKEN(C5;Loterij!B:C;2;ONWAAR) te gebruiken; de laatste parameter zorgt er voor, dat Excel naar een exacte match gaat zoeken.
  4. de formule in D5 kan naar beneden gekopieerd worden
  5. bij iedere wijziging in de werkmap worden de Aselect-formules opnieuw berekend; dus iedere keer zullen er andere winnaars tevoorschijn komen. Dit gebeurt ook door op de functietoets F9 te drukken: herberekenen.
    Belangrijk is om van tevoren duidelijk met de “notaris” af te spreken hoe vaak er herberekend zal worden, voordat de definitieve uitslag wordt bepaald.

LET OP met bovenstaande methode is het mogelijk dat prijswinnaar 2 en/of 3 gelijk is aan prijswinnaar 1. Dat is natuurlijk niet de bedoeling. Druk dan nog een keer op F9.

NB worden er aan de lijst in het tabblad Loterij nummers en namen toegevoegd of worden er verwijderd, dan zullen de resultaten in Uitslag direct daaraan worden aangepast; we kijken immers naar alle rijen in de kolommen B en C.

Loterij2

Het Voorbeeldbestand bevat ook een tabblad Loterij2; deze is vergelijkbaar met de eerste, maar is in de vorm van een Excel-tabel opgevoerd. Een groot voordeel hiervan is dat we niet alle cellen uit de kolommen B en C hoeven mee te nemen in de formules: wanneer de tabel wordt uitgebreid of verkleind dan zullen alle corresponderende formules zich automatisch daaraan aanpassen.

loterij3De formule in C2 ziet er anders uit:
=AANTAL(LoterijOvz2[LotNr])
Tel het aantal nummers in de kolom met als kopje LotNr uit de tabel LoterijOvz2 (de tabel uit het tabblad Loterij2).

De rest van kolom C is hetzelfde. Maar in kolom D gebruiken we geen VERT.ZOEKEN maar de functie INDEX. In cel D5 komt dan de formule =INDEX(LoterijOvz2[Naam];C5).
Haal in de kolom met als kopje Naam uit de tabel LoterijOvz2 de waarde op in die regel, die overeenkomt  met de waarde in C5.

NB aanpassingen aan de tabel in Loterij2 worden automatisch meegenomen in de resultaten.

LET OP ook hier kan het nodig zijn om een keer extra op F9 te drukken om geen dubbele prijswinnaars te krijgen.

Loterij3

loterij4In het tabblad Loterij3 van het Voorbeeldbestand is in de Excel-tabel een extra kolom opgenomen, waarin iedere regel van een willekeurig getal tussen 0 en 1 wordt voorzien dmv de formule =ASELECT().
De kans, dat hier dubbele getallen in voorkomen, is heel erg klein.

NB deze functie kent geen parameters, maar, zoals achter iedere functie, dienen er wel 2 haakjes te staan (openen en sluiten).

loterij5De bepaling van de winnaars gaat nu iets anders: in cel C3 wordt de eerste winnaar bepaald door het grootste getal (MAX) in de kolom Aselect van de tabel LoterijOvz3 op te zoeken. In D3 wordt met VERT.ZOEKEN het corresponderende lotnummer gevonden en in E3 met INDEX de naam (zoals uit de formules van kolom F blijkt kunnen we dat laatste ook met VERT.ZOEKEN in de hele tabel LoterijOvz3).

Maar hoe vinden we nu de 2e- en 3e- prijswinnaars? Dan kunnen we niet meer MAX gebruiken.
In cel C4 staat dan ook een andere formule: =GROOTSTE(LoterijOvz3[Aselect];2)
ofwel zoek de tweede in grootte in  de kolom Aselect van de tabel LoterijOvz3.
U begrijpt: in plaats van MAX in cel C3 hadden we ook de functie GROOTSTE met een parameter 1 kunnen gebruiken!

NB uiteraard hadden we in dit geval de winnaars ook kunnen selecteren met de functie KLEINSTE.


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