Alle berichten van Gijs Verbruggen

Formules, Namen, Eigen functies

De grote kracht van Excel (en ieder ander spreadsheet-programma) is dat je gebruik kunt maken van allerlei formules. Of het nu berekeningen zijn of logische constructies (bijvoorbeeld met behulp van de ALS-functie) of combinaties hiervan: ieder spreadsheet wordt met formules ‘aan elkaar geknoopt’.

Bij de opbouw van formules is het zeer aan te raden om Namen te gebruiken in plaats van cel-verwijzingen als C4 of bereik-aanduidingen als F5:F20. De leesbaarheid van formules gaat daarmee met sprongen vooruit.

In dit artikel daarom (nogmaals) aandacht voor het maken en gebruik van Namen. Ook het opslaan van formules in een Naam komt aan bod. Maar ik zal ook laten zien dat het gebruik van Eigen functies (met behulp van VBA) veel flexibeler is.

Namen definieren

Een naam vastleggen in Excel kan op diverse manieren:

  1. de eerste is een rechtstreekse handmatige invoer (zie tabblad VB1 in het Voorbeeldbestand): selecteer een cel (in dit voorbeeld C2), klik in het Naamvak, links van de Formulebalk, en tik de gewenste naam in (hier dus Korting1) en druk op Enter.
    LET OP een naam mag geen spaties bevatten en geen celverwijzing voorstellen zoals A1 of EXP10. Het is ook af te raden om bestaande functienamen in te voeren (zoals Som); dit is voor Excel geen echt probleem maar voor de gebruiker van het spreadsheet kan het zeer verwarrend zijn.
    NB1 gebruikelijk is om op de plaats waar je een spatie zou willen zetten het _-teken te plaatsen (de underscore; bijvoorbeeld Korting_1) of het tweede gedeelte met een hoofdletter te laten beginnen (bijvoorbeeld KortPerc).
    NB2 worden er meerdere cellen tegelijkertijd geselecteerd, dan krijgt het totale bereik de ingevoerde naam; de afzonderlijke cellen hebben dan geen naam.
  2. plaats een omschrijving links of boven de cel die een naam moet krijgen.
    Selecteer de omschrijving en de betreffende cel (in het voorbeeld B2:C2) en kies in de menutab Formules in het blok Gedefinieerde namen de optie Maken obv selectie.
    In het pop-up venster heeft Excel in dit geval al ‘gezien’ dat er links van de cel een omschrijving staat; deze zal als naam voor cel C2 worden gebruikt.
    NB1 als er spaties staan in de omschrijving dan vervangt Excel deze automatisch door een underscore.
    NB2 stelt de omschrijving een celverwijzing voor dan plaatst Excel een underscore achter de naam (Rij1 wordt dan Rij1_) .
    NB3 zijn er naast de omschrijving meerdere cellen geselecteerd dan krijgt het totale bereik van die cellen de tekst van de omschrijving als naam.
  3. selecteer de cel (of cellen) die een naam moet krijgen en kies in de menutab Formules in het blok Gedefinieerde namen de optie Naam definiëren.
    Voer een naam en eventueel een opmerking in. Controleer of de verwijzing klopt.
    Het Bereik voor een naam staat standaard op Werkmap; de naam kan overal gebruikt worden. In dit geval is deze korting alleen maar geldig op het tabblad Vb1, dus moet het bereik daaraan aangepast worden.
    NB klik je op het ‘vinkje’ achter het Naamvak, dan zie je alle namen die voor dit tabblad geldig zijn (inclusief de namen die voor de hele werkmap gelden).
  4. kies in de menutab Formules in het blok Gedefinieerde namen de optie Namen beheren en klik op de button Nieuw

NB wanneer op een van deze manieren een naam wordt ingevoerd zal Excel de verwijzing standaard ´absoluut´ maken; zie de $-tekens.

Namen beheren

Wil je kijken welke namen er in het Excel-bestand worden gebruikt en/of deze aanpassen kies dan in de menutab Formules in het blok Gedefinieerde namen de optie Namen beheren.

Hier is het mogelijk om een nieuwe naam toe te voegen of een bestaande te verwijderen.
Ook kan een naam bewerkt worden.

LET OP Bij bewerken kunnen de naam, opmerkingen en verwijzing aangepast worden, dus NIET het bereik. Wil je deze wijzigen dan moet je een nieuwe naam toevoegen met een ander bereik en de andere verwijderen.

Gebruik van Namen 1

In het tabblad Vb1 van het Voorbeeldbestand zijn netto-bedragen berekend met behulp van een zelf-gedefinieerde naam. In cel D7 staat de formule =C7*(1-Korting1).
Wanneer deze naar beneden gekopieerd wordt, wijzigt C7 automatisch in C8 etcetera. De korting is via de naam absoluut gedefinieerd en verwijst dus ook bij het kopiëren altijd naar cel C2.

Bij het invoeren van de formule laat Excel bij het intikken van de letters ko alle mogelijkheden zien, die voor dit tabblad gelden. De opties met ƒx zijn functies binnen Excel (standaard of eigen functies, zie hierna), de andere zijn namen.
Dubbelklik op de gewenste functie of naam of selecteer deze met de cursor en druk dan op de tab-toets.

Gebruik van Namen 2

Bij het definiëren van namen hoeft u zich niet te beperken tot cel-verwijzingen. Het is ook mogelijk om formules op te nemen.

Voor het tabblad Vb2 in het Voorbeeldbestand is voor de naam Kort2a als verwijzing de formule =1-Vb2!$C$2 ingevoerd. Dat houdt in dat de berekening van het netto-bedrag er dan iets anders kan uitzien:
het oorspronkelijke bedrag wordt vermenigvuldigd met Kort2a. Het mag duidelijk zijn dat vooral bij uitgebreide/complexe formules hierdoor het spreadsheet overzichtelijker blijft.

In kolom E staat een vergelijkbare formule, maar daar wordt gebruik gemaakt van Kort2b, die gedefinieerd is als =1-‘Vb2’!Korting2

LET OP wanneer binnen zo’n formule gebruik wordt gemaakt van een naam, die niet voor de hele werkmap geldig is, dan moet deze naam voorafgegaan worden door de naam van het tabblad, gevolgd door een !-teken. De aanhalingstekens zijn eigenlijk alleen maar nodig als de naam van het tabblad spaties bevat.

Gebruik van Namen 3

In het vorige voorbeeld is het kortingspercentage via een Naam omgezet in een kortingsfactor. Maar ook het netto-bedrag kan via zo’n constructie worden bepaald; zie het tabblad Vb3 in het Voorbeeldbestand.
Netto3a is gedefinieerd als
=’Vb3′!C7*(1-‘Vb3!Korting3)

LET OP C7 is relatief (zonder $-tekens) gedefinieerd. En dit geredeneerd vanuit cel D7. Dit betekent dat dezelfde naam Netto3a vanuit cel D8 verwijst naar cel C8. Selecteer maar eens een cel ergens in kolom D en kies dan Namen beheren. Of kies de optie Broncellen aanwijzen in het blok Formules controleren.

Maar wanneer we de naam Netto3a in een andere kolom plaatsen gaat het mis. Zie cel H21 in het tabblad Vb3. De naam Netto3a hanteert nu als bron de cellen uit kolom G.

Met de formule in Netto3b is dit opgelost: =’Vb3′!$C7*(1-!Korting3)
Dus de kolom is absoluut (altijd C), maar de rij relatief; zie cel H23.

NB de exacte locatie van de cel met de naam Korting3 is weggelaten; alleen het !-teken is voldoende. Een nadeel hiervan is dat Broncellen aanwijzen dan niet meer volledig is. In het Voorbeeldbestand is wel de naam van het tabblad opgenomen omdat Mac-gebruikers anders in de problemen komen.

In het tabblad Vb3 van het Voorbeeldbestand is nog een derde naam (Netto3c) opgenomen met als formule =’Vb3′!$C7:$C40*(1-!Korting3)

De werking is hetzelfde maar het voordeel is dat je nu het totaal van de netto-bedragen kunt bepalen met de formule =SOM(Netto3c). Deze formule moet wel ergens in rij 7 staan; zie het resultaat in de cellen H7 en H8.

LET OP Excel weet niet dat de inhoud van zo’n naam een formule is en zal het resultaat niet automatisch herbereken (bijvoorbeeld bij het wijzigen van het kortingspercentage). Daarom is de SOM nog aangevuld met +0*NU(); aangezien NU ieder moment van waarde verandert zal Excel de totale formule bij iedere wijziging van het spreadsheet wel opnieuw berekenen.

Gebruik van Namen 4

Wanneer gebruik wordt gemaakt van Excel-tabellen (en zoals we allemaal weten heeft dat grote voordelen) dan moet goed opgelet worden bij het definiëren van Namen.

In het tabblad Vb4 van het Voorbeeldbestand bestaat het systeem uit de tabel tblNetto. Binnen dat tabblad is de naam Netto4a gedefinieerd als =tblNetto[Bedrag]*(1-!Korting4)

De opzet van de formule is vergelijkbaar met de naam Netto3c. De werking is nu echter anders: in iedere rij komt nu hetzelfde resultaat, namelijk het nettobedrag dat hoort bij de eerste regel. De SOM-formule werkt wel goed en is nu onafhankelijk van de plaats.

Voor de juiste berekening in de tabel hebben we een andere naam nodig:
=’Vb4′!$C7*(1-!Korting4) of =tblNetto[@Bedrag]*(1-!Korting4)

NB de @ zorgt er voor dat met het bedrag in de betreffende regel wordt gerekend.

Eigen functie

Zoals hiervoor duidelijk mag zijn geworden kleven er aan het gebruik van formules in Namen wel nogal wat problemen of laten we zeggen aandachtspunten.
Wel is een voordeel van die systematiek dat beginnende gebruikers niet zomaar formules kunnen aanpassen.

Met een klein beetje VBA lossen we voorgaande aandachtspunten dat snel en vakkundig op:

  1. druk op Alt-F11 of kies de optie Visual Basic in het blok Programmacode van de menutab Ontwikkelaars
  2. zoek het betreffende project (in dit geval Formules.xlsm) en klik daarop
  3. als er nog geen Modules zijn, kies dan eerst Invoegen/Module
  4. dubbelklik op een module en plaats daar de volgende functie-definitie
    Function BerekenNetto(Bedr, KortPerc)
          BerekenNetto = Bedr * (1 – KortPerc)
    End Function
    De eigen functie BerekenNetto kent 2 parameters: een bedrag en een kortingspercentage. Het resultaat van de gewenste berekening wordt aan de functienaam gekoppeld.

In het tabblad Vb5 van het Voorbeeldbestand wordt deze eigen functie gebruikt. Omdat we binnen een Excel-tabel werken kunnen we weer naar de Bedrag-kolom wijzen (denk aan de @).

Maar de formule kan ook ergens anders in het tabblad staan, en wordt dan iets als =BerekenNetto(C7;Korting5)

Overzicht Namen

Zeker als er veel namen gebruikt worden in een Excel-systeem is het belangrijk om het systeem goed te documenteren.
Eén van de onderdelen van de documentatie moet een goede beschrijving van de verschillende Namen zijn (zie het tabblad Docu in het Voorbeeldbestand).

Gelukkig kunnen we een groot gedeelte automatisch laten genereren:

  1. selecteer een tabblad met één of meer geldige namen
  2. selecteer ergens een lege cel; zorg dat rechts en daaronder genoeg lege cellen zijn
  3. kies in de menutab Formules in het blok Gedefinieerde namen de optie Gebruiken in formule; in het vervolgscherm kiest u Namen plakken
  4. wanneer u dan op Lijst plakken klikt, wordt er een overzicht in het tabblad geplaatst
  5. kopieer de gegevens naar een documentatie-tabblad
  6. herhaal alle stappen voor ieder tabblad

NB alle namen die geldig zijn voor de hele werkmap komen dan meerdere keren voor; dus is er nog wat schoning vereist.


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

Samengestelde interest

Samengestelde interest of rente op rente: een onderwerp, dat regelmatig weer vragen oproept. Zijn daar in Excel nu wel of niet functies voor?

De aanleiding om hier over te schrijven was de volgende vraag:

“Volgens Microsoft-informatie bestaat er geen functie voor samengestelde interest in Excel.
Vroeger, al weer 50 jaar geleden, heb ik op de middelbare school nog financiële rekenkunde gekregen.
Daarbij gebruikte je de diverse rentetabellen met de diverse percentages om een bepaald bedrag uit te kunnen rekenen. Dat waren de kleine en grote A- en S-tabellen, als ik het mij nog goed herinner.”

In dit artikel zal ik laten zien, dat Microsoft zichzelf iets tekort doet. Het hangt van de situatie af. Maar zo gauw het wat ingewikkelder wordt, zul je toch zelf een systeem in Excel moeten bouwen, een soort (complexe) rentetabellen.

Rente op rente, variant 1a

Voor samengestelde interest zijn allerlei varianten denkbaar.
De meest eenvoudige is de situatie, dat u een bedrag op een spaarrekening zet waar jaarlijks een rente wordt bijgeschreven (ja, dat bestaat nog!). U laat na het eerste jaar alles op de rekening staan en krijgt in het tweede jaar dus ook rente vergoed over de rente van het eerste jaar (vandaar de naam Rente op rente).

Nou dit is zo simpel, dat we daar ook eigenlijk geen functie voor nodig hebben.
Op het einde van het eerste jaar wordt er rente bijgeschreven; uw tegoed is dan Bedrag * (1 + Rente%).
Op het einde van het tweede jaar wordt dit resultaat weer vermenigvuldigd met (1 + Rente%). Het mag duidelijk zijn, dat op het einde van de looptijd de Toekomstige Waarde gelijk is aan het gestorte Bedrag maal (1 + Rente%) tot de macht Looptijd.

In het Voorbeeldbestand in het tabblad TW1 staat deze berekening van de Toekomstige Waarde in cel C8:
=Bedrag*(1+Rente)^Looptijd

In de formule staan geen celverwijzingen; de betreffende cellen C4 t/m C6 hebben een naam gekregen waardoor formules die daar naar verwijzen veel beter leesbaar zijn.

NB1 de eenvoudigste manier om een cel een naam te geven is door deze cel te selecteren en dan in het naamvak linksboven de gewenste naam in te tikken.

NB2 cellen met een groene achtergrond zijn invoer-cellen.

NB3 de vraag bovenaan het tabblad is dynamisch; in cel B1 staat de formule
=”Wat is “&Bedrag&” euro over “&Looptijd&” jaar waard bij “&TEKST(Rente;”0,0%”)&” rente?”
M.b.v. het &-teken worden teksten tussen aanhalingstekens samengevoegd met namen van cellen. Om te zorgen dat Rente een mooie opmaak krijgt is gebruik gemaakt van de functie TEKST.

NB4 misschien wat verwarrend: in dit geval wordt het woord Rente gebruikt waar een rentepercentage wordt bedoeld.

Rente op rente, variant 1b

Maar de berekening kan ook met de standaard Excel-functie TW uitgevoerd worden. De benodigde formule staat in cel C9 van het tabblad TW1 van het Voorbeeldbestand:
=TW(Rente;Looptijd;0;-Bedrag)

Zoals u ziet kent deze functie 4 parameters; de betekenis van de eerste 2 moge duidelijk zijn. Met de derde parameter kunt u aangeven of er nog vaste periodieke betalingen plaats vinden (in dit voorbeeld niet; hier komen we later nog op terug). De vierde parameter geeft aan wat de Huidige Waarde is; in dit voorbeeld ‘geven’ we  dit bedrag aan de bank, vandaar het minteken.

LET OP1 als het minteken wordt weggelaten wordt het resultaat van de formule negatief.

LET OP2 als na het invoeren van de TW-functie op Enter wordt gedrukt, zal Excel het resultaat van de formule weergeven. Maar dat niet alleen: ook wordt de opmaak van de cel gewijzigd in Valuta.

Rente op rente, variant 1c

In de vorige voorbeelden is gewerkt met een jaarlijkse bijschrijving van de rente. In het tabblad TW2 van het Voorbeeldbestand kunt u zien wat het effect is van de verrekening van een maandrente (jaarrente gedeeld door 12). Afhankelijk van de keuze in cel C6 (J of M is mogelijk) wordt de JrMndFactor in cel C7 gelijk aan 1 of 12.

NB als er met maandrente wordt gerekend moet de looptijd niet in jaren maar in maanden worden uitgedrukt.

Rente op rente, variant 2

Maar wat als er niet één storting plaats vindt, maar ieder jaar (aan het begin) een overboeking plaats vindt?
Over het eerste bedrag wordt het aantal keren, dat de Looptijd is, rente uitgekeerd; over de tweede storting 1 keer minder etc. Met behulp van een wiskundige afleiding kan ook hier de TW uitgedrukt worden in een formule.

NB voor de liefhebbers: TW = Bedrag*(1+r)^n + Bedrag*(1+r)^n-1 + .. + Bedrag*(1+r).
Vermenigvuldig deze vergelijking met (1+r) en trek ze van elkaar af. Allerlei termen vallen dan tegen elkaar weg. Nog wat reshuffelen en klaar is Kees.

In het Voorbeeldbestand is deze formule in cel C8 van het tabblad TW3 opgenomen.

Maar ook hier kunnen we de standaard Excel-functie TW gebruiken; zie cel C9 in het tabblad TW3:
=TW(Rente;Looptijd;-Bedrag;0;1)

Omdat het hier periodieke betalingen/stortingen betreft, wordt het bedrag in de derde parameter geplaatst (met een min-teken). De vierde parameter (de huidige of startwaarde) is in dit geval 0.

NB de functie TW kent een optionele vijfde parameter (Type). Als deze 1 is dan betreft het een storting aan het begin van iedere periode (prenumerando); wordt hier 0 ingevuld of wordt deze weggelaten dan stelt het een storting op het einde van de periodes weer (postnumerando).

Bij alle bovenstaande methodes is het nadeel, dat er telkens slechts het resultaat na één looptijd zichtbaar is.

In dit soort situaties is het vaak veel prettiger om een overzicht/systeem op te zetten, waar het verloop in de tijd zichtbaar is.

Op het tabblad TW3 in het Voorbeeldbestand staat zo’n systeem:

  1. het startbedrag van het eerste jaar is het stortingsbedrag uit cel C4: =Bedrag
  2. in de kolom daarnaast wordt de rente over jaar 1 berekend: =[@StartBedr]*Rente
    dus het startbedrag uit dezelfde regel (vandaar de @) maal de rente uit cel C5
  3. in de volgende kolom staat het resultaat op het einde van het jaar: =[@StartBedr]+[@Rente]
  4. het startbedrag voor jaar 2 wordt dan het eindresultaat van jaar 1 plus de nieuwe storting: =H5+Bedrag
  5. aangezien we alles in de vorm van een Excel-tabel hebben gezet, worden de formules in de rente- en eind-kolom automatisch ingevuld
  6. de formule uit punt 4 kan naar beneden gekopieerd worden

Bij wijziging van het stortingsbedrag en/of rente-percentage zien we nu in één oogopslag wat de consequentie daarvan is in de loop van de tijd.

Rente op rente, variant 3a

Maar wat als de stortingen en/of het rentepercentage nu niet meer constant zijn in de loop van de tijd?

In het tabblad TW4 van het Voorbeeldbestand is het systeem uit het tabblad TW3 iets aangepast: er zijn kolommen voor het stortingsbedrag en het rentepercentage bij gekomen.

NB in deze 2 nieuwe kolommen staan alleen in de eerste rij ‘harde’ waarden (namelijk 100, respectievelijk 3%); daaronder staan altijd verwijzingen naar de regel daarboven. Een wijziging in één van de jaren wordt dan automatisch naar de toekomst doorgevoerd.

Rente op rente, variant 3b

Op het tabblad TW4 staat nog een tweede systeem.
We weten natuurlijk niet exact welk bedrag we in de toekomst kunnen sparen en wat de rente zal zijn. Maar we kunnen wel schattingen maken: ieder jaar denken we 10% meer te kunnen sparen (in het voorbeeld staat deze schatting nog op 0%) en we verwachten (hopen?), dat de rente ieder jaar 2% zal stijgen (relatief dus, geen harde 2%. In dat geval hadden we gezegd, dat het percentage met 2 procent-punt zou stijgen).

Welke van bovenstaande varianten de voorkeur heeft, is natuurlijk afhankelijk van uw eigen wensen. Betreft het slechts een eenmalige berekening of is er veel flexibiliteit en inzicht gewenst etcetera.


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

Grafische analyse in Excel

Onlangs kwam ik op de website www.exceluser.com enkele nuttige artikelen tegen: 3 Simple Tricks to Improve Analytical Charting in Excel3 More Simple Tricks to Improve Excel Charts for Business, Introducing the Power of Year-Over-Year Performance Charts in Excel en What Inflation? How Excel Charts Can Help You Avoid ‘Anecdotal Economics’.

In dit artikel zal ik een paar ideeën van Charles W. Kyd de revue laten passeren en aan de hand van voorbeelden de principes daarachter uitleggen.

Wat laat je zien?

Hiernaast ziet u een simpel omzet-overzicht van een bedrijfje (zie het tabblad Vb1 in het Voorbeeldbestand).
In de maandrapportage kun je zo’n overzicht opnemen, maar je kunt er ook voor kiezen om een grafiek te tonen.

Directeur tevreden, iedereen tevreden?
Nee dus, de marketingmanager vindt dat zo niet goed zichtbaar is, dat de marketing-activiteiten hun vruchten afwerpen en wil een andere grafiek.

Dan blijkt dat de productie-manager weer andere wensen heeft (een collega heeft in de maand maart zijn werkzaamheden overgenomen) en vindt dat het beter is om de groei/krimp van de omzet in de loop van de tijd te laten zien.

Waar cijfer-overzichten veelal de interpretatie van de data aan de lezer overlaten, mag het duidelijk zijn dat grafische presentaties het gevaar van misleiding met zich mee brengen.

Onvergelijkbare gegevens vergelijken

Stel we hebben een maandoverzicht waarin 2 soorten bedragen voorkomen, die qua grootte ver bij elkaar uit de buurt liggen (zie het tabblad Vb2 in het Voorbeeldbestand).

Wanneer we deze in een standaard lijn-grafiek weergeven, dan zijn er weinig details per maand uit de grafiek af te leiden.

Eén van de meest gehanteerde oplossingen is om de twee reeksen aan verschillende assen toe te wijzen.
NB het kleurgebruik moet de lezer helpen om te zien welke reeks bij welke as hoort.

Als vooral het verloop van de tijd van belang is en niet de exacte hoogte van de bedragen, dan is een andere oplossing om de reeksen te indexeren.
Dan is er maar één verticale as nodig; dit voorkomt misverstanden.
Het betekent wel dat er aan de basisgegevens kolommen moeten worden toegevoegd met een berekening zoals =[@Bedr1]/$C$6

Verloop AEX

Eén van de artikelen op ExcelUser.com is geschreven naar aanleiding van het dalen van de koersen in Amerika (What Inflation?). Deze daling liep synchroon met de correctie van de bitcoin-koersen. Maar was/is hier sprake van een correctie of begint de inflatie weer toe te nemen? Of nog erger: zitten we weer aan het begin van een recessie?

En hoe zit dit in Nederland? Geeft de AEX ook aanleiding tot dit soort bespiegelingen?
De bedoeling van dit artikel is niet om hier nu een sluitend antwoord op te geven; wel zal ik aan de hand van enkele voorbeelden laten zien hoe grafieken het proces om te komen tot beantwoording kunnen ondersteunen.

In het tabblad AEX van het Voorbeeldbestand staat een overzicht van de AEX-resultaten vanaf januari 2000 (alleen koersen per werkdag zijn beschikbaar). We zullen alleen de kolom Datum en Close (koerswaarde op het moment van sluiten van de beurs) gebruiken.

De gegevens zijn opgeslagen in de vorm van een Excel-tabel met de naam tblAEX. Een van de grote voordelen van deze vorm is, dat wanneer er nieuwe cijfers beschikbaar zijn en deze onderaan toegevoegd worden, alle formules en grafieken automatisch aangepast worden. Een update van het analyse-systeem is dus een ‘fluitje van een cent’.

Om makkelijk te kunnen inzoomen op gedeeltes van het verloop van de AEX is deze grafiek dynamisch gemaakt. Dus wat er getoond wordt is afhankelijk van de inhoud van bepaalde cellen, in dit geval F3 en F4 (zie ook het artikel dynamische-grafieken).

In het tabblad AEX_Ovz van het Voorbeeldbestand staan daartoe 2 hulp-tabellen.
In cel C3 (met de naam MinDtm) wordt het minimum van de kolom Datum uit de tabel tblAEX bepaald; zo ook in cel C4 het maximum. Onder andere deze 2 waardes worden gebruikt om de mogelijke invoer in de cellen F3 (met de naam Start) en F4 (met de naam Eind) te begrenzen (met behulp van Gegevens-validatie).
In cel G3 (met de naam StartRij) wordt dan bepaald in welke rij van de AEX-tabel de datum uit F3 staat: =VERGELIJKEN(Start;tblAEX[Datum])

LET OP de derde parameter in de functie VERGELIJKEN is weggelaten; dat betekent dat de functie de grootste datum zoekt die kleiner of gelijk is aan Start. De kolom Datum in de tabel tblAEX moet dan wel oplopend gesorteerd zijn!

Zo wordt ook de rij van de laatste gewenste datum opgezocht in cel G4 (met de naam EindRij).

Namen definieren

In het voorgaande heb ik al regelmatig Namen gebruikt en dan vooral met het doel formules leesbaarder te maken. Ook hebben alle Excel-tabellen een naam gekregen, waardoor in formules direct duidelijk is naar welke tabel verwezen wordt. Het gebruik van tabellen heeft daarnaast als voordeel dat formules en grafieken verwijzen naar kolomnamen i.p.v. naar cel-bereiken (dus =tblVb1[Omzet] in plaats van =C3:C8)

Maar om dynamische grafieken te kunnen creëren hebben we ook iets ingewikkelder constructies als Naam nodig:

  1. kies in de menutab Formules in het blok Gedefinieerde namen de optie Namen beheren
  2. klik op de button Nieuw…
  3. geef een naam op; als eerste voorbeeld rngDtm
  4. de formule onder Verwijst naar wordt dan
    =VERSCHUIVING(tblAEX[[#Kopteksten];[Datum]];StartRij;0;EindRij-StartRij+1)
  5. klik Sluiten

NB de functie VERSCHUIVING geeft als resultaat de reeks cellen, die ontstaat als we vanaf de kop van de kolom Datum in de tabel tblAEX het aantal StartRij naar beneden gaan en 0 kolommen naar rechts/links, waarbij de lengte van de reeks bepaald wordt door de formule EindRij-StartRij+1.

De formule voor rngClose is dan gelijk aan =VERSCHUIVING(tblAEX[[#Kopteksten];[Close]];StartRij;0;EindRij-StartRij+1)

LET OP Deze rng-namen kunnen nu via Gegevens selecteren aan een grafiek worden gekoppeld; denk er wel aan om niet =rngDtm in te vullen, maar =AEX!rngDtm, dus voor alle namen een naam van een tabblad, aangevuld met een !-teken.

Vul je in F3 de waarde 1-1-2007 in en in cel F4 de waarde 31-12-2013 dan ontstaat automatisch de volgende grafiek:

AEX in perspectief

Eén van de tips van ExcelUser.com is om een grafiek van een (historisch) perspectief te voorzien. In dit geval kan het interessant zijn om te zien wat het verband is tussen het koersverloop en recessies.

Daartoe zijn in het tabblad Recessie van het Voorbeeldbestand de recessies vanaf 2000 vastgelegd met een begin en een einddatum. Dit in de vorm van een Excel-tabel met als naam tblRecessie.

De tabel tblAEX in het tabblad AEX is uitgebreid met een kolom F waarin de formule
=ALS([@Datum]<=INDEX(tblRecessie[Eind]; VERGELIJKEN([@Datum];tblRecessie[Start]));
1;NB())
staat.

Korte toelichting: zoek met de functie VERGELIJKEN de Datum uit dezelfde regel (vandaar de @) op in de Start-kolom van tblRecessie ; als dit niets oplevert dan is het resultaat van de totale formule #N/B.
Dan wordt met behulp van de functie INDEX gekeken of deze datum kleiner of gelijk is aan de overeenkomende regel in de Eind-kolom van tblRecessie.
Als dat zo is, dan wordt het resultaat 1 en anders ook weer #N/B.
Het resultaat #N/B wordt in een grafiek niet weergegeven.

Deze recessie-gegevens nemen we in de grafiek op; niet rechtstreeks maar ook weer via een naam-formule: rngRecessie
=VERSCHUIVING(tblAEX[[#Kopteksten];[Recessie]];StartRij;0;EindRij-StartRij+1)

Deze range krijgt een eigen type grafiek, namelijk een Vlak en we zetten deze uit op de secundaire as. Zorg dat het maximum van deze as altijd 1 is en verwijder alle kenmerken van de as.

Op een vergelijkbare manier zijn ook de minimum- en maximum-koersen in de grafiek opgenomen.

 

Alarmbellen?

Als we inzoomen op het koersverloop van dit jaar blijft de vraag of er alarmbellen moeten gaan rinkelen; bijna 50 punten verschil tussen het minimum en het maximum, is dat een teken dat het fout gaat?

Die vraag is natuurlijk niet zo maar te beantwoorden, maar zoals Charles W. Kyd in één van zijn artikelen aangeeft: probeer een andere invalshoek.

Daarom zijn de AEX-gegevens uitgebreid met een kolom, die aangeeft hoeveel procent de koers op een bepaalde datum is gestegen of gedaald ten opzichte van het jaar daarvoor:

=[@Close]/INDEX([Close];VERGELIJKEN(ZELFDE.DAG([@Datum];-12);[Datum]))-1

Deel de slotkoers van een dag door de overeenkomende dag 12 maanden terug en trek daar 1 vanaf.

Ook nu is de derde parameter van de functie VERGELIJKEN weggelaten; dus soms wordt de slotkoers niet vergeleken met de slotkoers exact 12 maanden terug, maar de dichtstbijliggende datum er voor.

De grafiek hiervan is via de naam rngCloseMutJr weergegeven op het tabblad AEX_Ovz2 van het Voorbeeldbestand.

In de grafiek is te zien, dat fikse negatieve resultaten veelal samen vallen met recessies. Begin 2018 scoort nog steeds flink positief.
Om toch maar een voorspelling te doen: de conclusie lijkt gerechtvaardigd dat de economie positieve perspectieven laat zien.


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

Kleurgebruik

Dit artikel gaat vooral over het gebruik van kleuren in Excel, maar is op ongeveer dezelfde manier geldig voor alle andere Office-programma’s van Microsoft.

Maar, hoewel kleuren het meest kenmerkende/zichtbare/opvallende aspect van de opmaak zijn, spelen ook het gebruikte lettertype en bepaalde effecten een rol. Dit komt binnen de Office-programma’s allemaal samen in de zogenaamde thema’s.

Wat is een thema?

Een thema is een verzameling voorkeur-instellingen binnen Microsoft-Office voor wat betreft kleuren, lettertypen (fonts) en bepaalde effecten. De laatsten zijn vooral van belang bij het gebruik van Powerpoint, het presentatieprogramma.
Door een bepaald thema te kiezen veranderen in de hele werkmap de kleuren (onder bepaalde voorwaarden; zie hieronder), het lettertype en eventuele effecten.

Het grote voordeel is dat op deze manier een consistent gebruik van de vormgeving is gewaarborgd.

Hoe kies je een thema?

In de praktijk is het het makkelijkste om een thema te kiezen wanneer je al een Excel-werkmap hebt waar kleuren en/of grafieken voorkomen.
Download het Voorbeeldbestand en selecteer het tabblad Ovz1.

Selecteer binnen de menu-tab Pagina-indeling in het blok Thema’s de optie Thema’s.
Er opent dan een overzicht met diverse standaard-thema’s; het aantal en welke het zijn is afhankelijk van de geïnstalleerde versie van Office.

Beweeg nu met de muis over de diverse thema’s; niet klikken. De consequenties voor de lay-out worden dan direct zichtbaar in het achterliggende werkblad. De breedte van de Excel-kolommen wordt automatisch aangepast aan het lettertype van het thema.

Bent u tevreden met een bepaalde opmaak: klik met de muis op het betreffende thema en deze opmaak zal dan in de hele werkmap worden toegepast. Dus ook in het Voorbeeldbestand op de tabbladen Ovz2 en Data, waar sparklines, een Excel-tabel en een overzicht met zelf toegevoegde kleuren zijn te vinden.

Wilt u alleen de kleuren binnen de werkmap aanpassen, kies dan in het blok Thema’s voor de optie Kleuren.
Een vergelijkbaar verhaal geldt natuurlijk ook als u alleen het Lettertype of de Effecten wilt aanpassen.

Verschil tussen Thema- en Standaard-kleuren

Zoals de naam al aangeeft, zullen de kleuren die via Themakleuren zijn ingesteld veranderen wanneer een ander thema wordt gekozen. Alle andere kleuren (Standaard of die via Meer kleuren zijn ingesteld) blijven altijd hetzelfde onafhankelijk van de keuze van het thema.

Wanneer u in Office een kleur kiest opent er altijd een scherm zoals hiernaast weergegeven.

Bovenaan staan de 10 basis-kleuren binnen het gekozen thema. De eerste kleur wordt gebruikt als standaard-achtergrond (ga met de muis er ‘boven hangen’ en de omschrijving wordt zichtbaar). De tweede kleur is de standaard-tekstkleur.
Kleur nummer 3 is een tweede achtergrond-kleur, terwijl nummer 4 een alternatieve tekstkleur is.
De laatste 6 zijn zogenaamde accent-kleuren, vooral gebruikt in grafieken.

Onder de basis-kleuren staan 5 varianten van deze kleuren. In het eerste schema op het tabblad Ovz1 van het Voorbeeldbestand ziet u of dit lichtere of donkerder varianten zijn.

NB gebruik op één pagina niet te veel verschillende kleuren; het wordt dan al snel een ‘kermis’. Beter is om 1 of 2 basis-kleuren te gebruiken en de verschillen te laten zien door varianten van deze kleuren te gebruiken; zie het verschil tussen de twee grafieken hiernaast (tabblad Ovz2 van het Voorbeeldbestand).

Dan volgen 10 Office-standaardkleuren. Zoals al eerder aangegeven: worden deze gebruikt binnen Excel dan zullen deze niet veranderen wanneer er een ander thema wordt gekozen.

Hebt u niet genoeg aan deze standaardkleuren, dan kunt u via Meer kleuren een andere standaard-kleur kiezen of ‘mengen’ (zie hierna).

Eigen thema maken

Wilt u consistente rapportages en presentaties maken met Excel, Word, Access en/of Powerpoint dan zijn aangepaste thema’s van groot belang.
Hierbij kunt u denken aan bedrijfskleuren en -lettertypen of kleuren die aan een bepaalde situatie moeten worden aangepast.

  1. selecteer allereerst een Thema dat het meest voldoet aan uw wensen
  2. kies dan Kleuren in het blok Thema’s van de menu-tab Pagina-indeling
  3. via de optie Nieuwe themakleuren maken onderaan komt u in een scherm waarin u de diverse basiskleuren kunt aanpassen
  4. geef dit kleurenschema een toepasselijke naam en kies Opslaan
  5. op een vergelijkbare manier kunnen ook de thema-lettertypen gewijzigd worden
  6. kies als laatste binnen de optie Thema’s de optie Huidig thema opslaan. Geef ook hier weer een toepasselijke naam op.

NB1 het nieuwe thema komt onder het kopje Aangepast tevoorschijn.

NB2 wijzig de eerste 2 kleuren binnen een thema niet; laat deze altijd op zwart en wit staan

NB3 het effect van kleurenwijzigingen zijn in het voorbeeldscherm rechts direct zichtbaar

Thema importeren

Heeft iemand een mooi of in ieder geval een bruikbaar thema gemaakt dan kunt u die importeren.

  1. download als voorbeeld het G-Info-thema
  2. vanwege beveiligingsredenen heeft deze een verkeerde extensie; wijzig in de map Downloads de naam van het bestand GInfo.zip in GInfo.thmx
  3. kies in de menu-tab Pagina-indeling in het blok Thema’s de optie Thema’s
  4. selecteer dan onderaan in het nieuwe scherm de optie Thema’s zoeken
  5. blader naar de Doenloads-map en kies het bestand GInfo.thmx
  6. wilt u dit thema vaker gebruiken vergeet dan niet om het op uw eigen systeem te bewaren: kies de optie Huidig thema opslaan

NB1 wilt u nog iets anders uitproberen: download het Carnaval-thema

NB2 hebt u zelf een thema opgeslagen en wilt u dit met anderen delen?
De plaats van dit thema is afhankelijk van het besturingssysteem en office-versie. Waarschijnlijk kunt u het in een vergelijkbare map vinden:
C:\Users\gebruikersnaam\AppData\Roaming\Microsoft\Sjablonen\Document Themes
Eventueel kunt u binnen de Verkenner zoeken naar UwThemanaam.thmx.

Meer kleuren

Wilt u uw bedrijfskleuren gebruiken of kleuren die u op een andere website tegenkomt dan zult u de codes daarvan moeten zien te achterhalen. Dat kan op diverse manieren:

  1. vraag uw marketingafdeling naar de kleurcodes.
    Er zijn 2 soorten nummering gangbaar: een hexadecimale code (bijvoorbeeld D1D2D4 voor lichtgrijs) of de daarmee samenhangende RGB-code (bijvoorbeeld 209 voor Rood, 210 voor Groen en 212 voor Blauw levert dezelfde kleur lichtgrijs).
    NB Hoe u een decimale code omzet naar de drie RGB-getallen kunt u zien op het tabblad Data van het Voorbeeldbestand.
  2. installeer (gratis) de extensie Eye Dropper in de internet-browser Google Chrome
  3. gebruik het (gratis) programma ColorCop, te downloaden via de website colorcop.net
  4. of via één van de vele andere programma’s (te vinden via een Google-zoekopdracht)

Hebt u de RGB-codes van de kleur of de kleuren, die u wilt gaan gebruiken, dan kunt u deze invoeren in het scherm Aangepast binnen de optie Meer kleuren.


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: