Tagarchief: RIJ

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:


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:

 

 

Excel-functies N en T



 

Excel kent 2 functies waarvan het nut niet helemaal duidelijk is: N() en T().
Het lijkt er op, dat het nog restanten zijn uit het verleden. Volgens Microsoft bestaan ze nog “in verband met compatibiliteit met andere spreadsheet-programma’s“.

Allebei de functies kennen maar 1 argument/parameter; de functie N() zet de parameter zo mogelijk om in een getal (Numeriek) en de functie T() levert als resultaat een Tekst, als het argument ook een tekst is.

NB de 2 haakjes in de tekst staan er bewust omdat iedere Excel-functie deze haakjes nodig heeft; bij verreweg de meeste functies moeten tussen de haakjes één of meer argumenten opgegeven worden.

Hieronder zullen we kijken hoe deze functies zich in de ‘normale’ praktijk gedragen, maar ik zal ook laten zien hoe de functies zich goed laten gebruiken in meer creatieve/excentrieke toepassingen.

De functie N()

Zoals al aangegeven zet deze functie het argument om in een getal; tenminste als dat (makkelijk) kan, anders levert de functie de waarde 0 terug; zie het tabblad Vb1 in het Voorbeeldbestand.

Uit de eerste 2 voorbeelden blijkt, dat de N-functie een combinatie van tekst en cijfers NIET kan omzetten naar een getal.
Een geheel getal of een getal met decimalen levert wel de juiste waarde op.
Wanneer de parameter een getal met een decimale punt bevat wordt dit door Excel als tekst gezien (de inhoud van de cel is dan ook automatisch links uitgelijnd) .

In cel B8 staat een formule, die als resultaat een getal oplevert; de N-functie neem deze waarde over.
Een datum is voor Excel een getal (het aantal dagen na 1-1-1900; zie het artikel Data (datums)). De N-functie zet een harde datum (in cel B9) dan ook ‘gewoon’ om naar het betreffende getal en ook het resultaat van een functie (VANDAAG() in cel B10). Ook als de cel een uitgebreide datum-opmaak heeft gekregen (cel B11) levert dit hetzelfde resultaat.

NB Excel heeft de cellen B8:B11 automatisch rechts uitgelijnd; daaraan is al te zien dat hij ze als numerieke waarden interpreteert.

De cellen B12:B14 bevatten teksten (automatisch links uitgelijnd) en de N-functie levert dan ook de waarde 0 terug.
Cel B12 bevat een ‘optelling’ (met het &-teken) van een tekst en een datum (dus een getal) en wordt daardoor vanzelf een tekst: =”Vandaag: “&VANDAAG()
In B13 is gebruik gemaakt van de functie TEKST om de datum een opmaak mee te geven: =”Vandaag: “&TEKST(VANDAAG();”dd-mm-jjjj”)
B14 bevat alleen de TEKST-functie, waardoor het resultaat (uiteraard) direct een tekst wordt: =TEKST(VANDAAG();”dddd d mmmm jjjj”)

De teksten Waar en Onwaar worden door de N-functie vertaald naar 1, respectievelijk 0. Ook als het het resultaat is van een formule (zie cel B17 met de formule =2>1).
De resultaten van formules die een foutwaarde opleveren (bijvoorbeeld =1/0 in cel B18) worden door de N-functie overgenomen.
Wanneer N() een lege parameter meekrijgt (zie cellen B19 en C19) dan wordt het resultaat 0.

NB let op de automatische uitlijning door Excel van de cellen B15:B18.

N-functie in plaats van ALS

De N-functie kan ook goed gebruikt worden ter vervanging van de ALS-functie. Dit levert een iets kortere en beter leesbare formule op.
Stel we hebben de situatie, dat een bedrag met een bepaalde provisie wordt verhoogd wanneer dat bedrag hoger of gelijk is aan een grenswaarde (zie tabblad Vb1 in het Voorbeeldbestand).
De bijbehorende formule is dan:
=ALS(G4>=G2;G4*(1+G3);G4)
of (als we de betreffende cellen een naam hebben gegeven):
=ALS(Bedrag>=Grenswaarde;Bedrag*(1+Provisie);Bedrag)

NB een blok cellen een naam geven kan makkelijk als volgt:

  1. selecteer de cellen F2 t/m G4
  2. kies in de menutab Formules in het blok Gedefinieerde namen de optie Maken o.b.v. selectie
  3. in dit geval moet alleen Linkerkolom aangevinkt staan
  4. klik op OK
  5. controleer even wat er gebeurd is: kies in de menutab Formules in het blok Gedefinieerde namen de optie Namen beheren

Maar de formule kun je ook op een andere manier (eenvoudiger?) schrijven:
=Bedrag*(1+ALS(Bedrag>=Grenswaarde;Provisie;0))
of
=Bedrag*(1+Provisie*ALS(Bedrag>=Grenswaarde;1;0))
of door de N-functie te gebruiken:
 =Bedrag*(1+Provisie*N(Bedrag>=Grenswaarde))

N-functie in plaats van een opmerking

Er kan niet genoeg benadrukt worden hoe belangrijk het is om een spreadsheet van voldoende documentatie te voorzien (voor uzelf en/of voor anderen).

Die documentatie kan vastliggen in Word-files, in een apart tabblad en/of door cellen met formules van opmerkingen te voorzien (zie cel G5 in het tabblad Vb1 van het Voorbeeldbestand).

Maar plaats u veel opmerkingen in een spreadsheet dan wordt het daardoor ook niet overzichtelijker. Commentaar kan ook met de N-functie aan een cel worden toegevoegd. In cel G9 wordt daartoe bij de formule de N() met als parameter het commentaar (als tekst!) opgeteld; die laatste levert toch de waarde 0 op.

De functie T()

De T-functie zal nu weinig verrassingen meer opleveren: als de parameter een tekst is (of via een formule een tekst bevat) dan zal de T-functie de tekst terugleveren en anders niets (een lege tekst).

Aangezien WAAR en ONWAAR intern Excel als numeriek worden gezien (1 of 0) levert T() dus een lege waarde op.

Bij een foutmelding als parameter wordt de foutmelding overgenomen en een leeg argument voor de T-functie geeft een leeg resultaat.

T() in de praktijk

In een bepaalde toepassing wilt u teksten uit verschillende kolommen aan elkaar koppelen. Zoals we gezien hebben kan dat gemakkelijk met behulp van het &-teken. In het tabblad Vb2 van het Voorbeeldbestand hebben we dat gedaan in cel E3; daar zijn tussen de teksten ook nog extra spaties toegevoegd.

In deze toepassing mogen de cellen echter allen gekoppeld worden als het een tekst bevat; hier komt de T-tunctie om de hoek kijken (zie cel E4).

Maar nou krijgen we problemen met de hoeveelheid tussenspaties. In cel E5 is dat als volgt opgelost:
=ALS(T(B5)=””;””;T(B5)&” “)&ALS(T(C5)=””;””;T(C5)&” “)&ALS(T(D5)=””;””;T(D5))

Als T(B5) een leeg resultaat oplevert, dan hoeft er niets te gebeuren, anders komt er de tekst T(B5) met een extra spatie.
Idem voor C5 en voor D5 bijna hetzelfde; de extra spatie vervalt.

In cel E6 staat een alternatief:
=ALS(N(B6);””;T(B6)&” “)&ALS(N(C6);””;T(C6)&” “)&ALS(N(D6);””;T(D6))

De twee laatste formules hebben als probleem dat als de laatste kolom een getal bevat (en ook als de tweede en de laatste kolom getallen bevatten) er nog een spatie op het einde van het resultaat staat.
Dat lossen we als volgt op: =SPATIES.WISSEN(E6)

NB maar we hadden natuurlijk dan ook niet zo ingewilled hoeven te doen met de ALS-functie, want SPATIES.WISSEN verwijdert niet alleen alle spaties aan de voor- en achterkant, maar wijzigt meerdere, tussenliggende spaties in één spatie. Dus de formule in E4 had ook kunnen zijn: =SPATIES.WISSEN(T(B4)&” “&T(C4)&” “&T(D4))

T-functie en CSE

In de dagelijkse praktijk hebben de T- en N-functies weinig toegevoegde waarde en dus ook weinig toepassingen.
Bij meer ingewikkelde (database-) problemen zie je toch nog wel eens opduiken.

In het Voorbeeldbestand heb ik een idee van Chandoo overgenomen in het tabblad Data.
Uit een HR-systeem is een overzicht gekomen van medewerkers, maar wel in een speciale vorm: in de oneven regels (met donkere achtergrond) staat de medewerker en direct daaronder (met lichtere achtergrond) zijn of haar leidinggevende (de tabel met medewerkers heeft de naam tblData gekregen).
Ook de leidinggevende kan weer een leidinggevende hebben, dus namen kunnen dubbel voorkomen (zie bijvoorbeeld A. Niesen; leidinggevende van A. Smit (regel 4) en als medewerker (regel 7)).

De bedoeling is om uit dit bestand bij een bepaalde medewerker zijn/haar leidinggevende op te zoeken. Het mag duidelijk zijn, dat een gewone VERT.ZOEKEN hier niet volstaat; we mogen alleen maar zoeken in de cellen met een donkere achtergrond.

Wanneer we dus A. Niesen zoeken moeten we op regel 7 uitkomen; ofwel in het derde blokje van de medewerker/baas-combinatie.

NB om de invoer van een naam te vergemakkelijken heb ik aan cel E2 een Validatie-regel toegevoegd: alleen maar namen uit de reeks met de naam KeuzeLijst zijn toegestaan en kunnen via de -button gekozen worden.

De formule in cel E3 ziet er zo uit:
=VERGELIJKEN(E2;T(VERSCHUIVING(tblData[[#Kopteksten];[Medewerkers]];RIJ($A$1:$A$100)*2-1;;1;1));0)

Wanneer we dit wat overzichtelijker laten zien:
01:=VERGELIJKEN(
02:     E2;
03:     T(
04:          VERSCHUIVING(
05:               tblData[[#Kopteksten];[Medewerkers]];
06:               RIJ($A$1:$A$100)*2-1;
07:               ;
08:               1;
09:               1
10:          )
11:     );
12:     0
13:)

Met Vergelijken (regel 1) gaan we de medewerker (regel 2) opzoeken.
Waar gaan we zoeken? Natuurlijk in de tabel tblData (regel 5). Maar niet in alle namen; alleen in de eerste, derde, vijfde etc. Met Verschuiving (regel 4) wordt een eigen reeks gemaakt door te verschuiven vanaf de koptekst Medewerkers (regel 5).
Hoeveel gaan we verschuiven? Als je weet dat RIJ(A1:A100) het rijnummer van de betreffende cellen genereert (regel 6; dus de getallen 1 t/m 100) dan is duidelijk dat door vermenigvuldiging met 2 en dan er 1 vanaf trekken de getallen 1, 3, 5 etc ontstaan. Met deze reeks gaan we naar beneden verschuiven vanaf de kopregel.
We blijven met de verschuiving in dezelfde kolom, dus de parameter in regel 7 is leeg.
Het resultaat van de verschuiving moet 1 cel hoog zijn (regel 8) en 1 cel breed (regel 9).
Om nu te zorgen, dat er een reeks van echte namen ontstaat komt de T-functie om de hoek kijken (regel 3).
Dus we gaan de naam uit E2 vergelijken met onze eigen reeks, waarbij een exacte match nodig is (vandaar de 0 in regel 12). Het resultaat is het volgnummer van het de combi medewerker/baas.

LET OP om te zorgen, dat de functie RIJ alle benodigde getallen genereert moet de formule in E3 als een zogenaamde array- of matrix-formule worden ingevoerd. Sluit de invoer NIET af met Enter, maar met Control-Shift-Enter (ofwel een CSE-invoer).

NB bekijk de werking van de diverse onderdelen van de formule:

  1. selecteer cel E3
  2. kies in de menutab Formules in het blok Formules controleren de optie Formule evalueren
  3. kijk welk gedeelte van de formule onderstreept is, druk dan op Evalueren en bekijk het resultaat
  4. herhaal stap 3 totdat de hele formule geëvalueerd is.

 

In cel E4 de naam van de baas opzoeken is nu een ‘peulenschil’:

=INDEX(tblData[Medewerkers];E3*2)


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