Categorie archief: Excel

Rapportage-stramien



Binnen ieder bedrijf is het van belang om zoveel mogelijk processen te standaardiseren en vaak ook te automatiseren.

Ook wanneer we Excel als hulpmiddel bij ons werk gebruiken is het belangrijk om zoveel mogelijk handmatige handelingen, die regelmatig terugkomen, te vermijden. Power Query, draaitabellen, en VBA kunnen daarbij een grote rol spelen.

Voor diegene, die vaak verschillende rapportages moeten maken, is een andere invalshoek belangrijk. Ga niet voor iedere nieuwe rapportage weer opnieuw het wiel uitvinden, maar gebruik een flexibel stramien. Hoe dat in zijn werk gaat zullen we in dit artikel aan de hand van een direct inzetbaar Voorbeeldbestand laten zien.

Bedrijf

Het eerste tabblad van het Voorbeeldbestand, Bedrijf, bevat diverse bedrijfsspecifieke gegevens, inclusief een logo.

NB1 wanneer je dit logo vervangt (door er rechts op te klikken) zorg dan dat het nieuwe plaatje een transparante achtergrond heeft.

NB2 diverse cellen in dit tabblad (en ook op andere plaatsen in de werkmap) hebben een naam gekregen, die in de rest van het rapportage-stramien gebruikt worden. Cel C2 bijvoorbeeld heeft de naam BedrNaam.

In cel C8 wordt de meest recente datum uit het Data-bestand (zie hierna) opgehaald met behulp van de functie MAX. De inhoud van cel C9 bepaalt of deze datum als referentie dient voor de rapportage of een andere (cel C10).

NB3 op diverse plaatsen in dit stramien zul je zien dat de invoer in cellen beperkt is met behulp van Gegevensvalidatie; zie bijvoorbeeld de cel C9 (alleen Ja en Nee zijn toegestaan).

In de regels 13 tot en met 22 kun je zelf de diverse onderdelen (en teksten) kiezen die in de kop- en voetregel van een rapportage moeten komen.

NB4 in cel G19 staat een spatie om er voor te zorgen dat de voetregel aan de rechterkant niet tegen de rand aan komt.

Experimenteer met de mogelijkheden en beoordeel het effect op de rapportages in de tabbladen Ovz1 en Ovz2.

Basis-instellingen

Het tabblad Basis van het Voorbeeldbestand begint met een standaardopmaak voor datums (in dit geval hebben we gekozen voor 3 letters voor de maand, een spatie en 4 cijfers voor het jaar).
Daaronder staan 6 items die als onderdeel voor de kop- en voetregels kunnen dienen.
In het blok in de kolommen E en F staan diverse opmaak-omschrijvingen en -codes die bij het weergeven van gegevens in de rapportage gebruikt kunnen worden.

NB aangezien alle keuze-items in Excel-tabellen zijn opgenomen zal een gewenste uitbreiding van de opties direct overal in de werkmap geëffectueerd worden.

Overige instellingen

In het eerste blok van de Overige instellingen (zie het tabblad Instel van het Voorbeeldbestand) wordt van maximaal 10 items vastgelegd wat de inhoud is, welke opmaak uit de lijst van het tabblad Basis deze moeten hebben en eventuele opmerkingen er in de rapportage moeten worden weergegeven.
Voor uw eigen rapportage moeten/kunnen de kolommen Naam, Opmaak en Opmerkingen aangepast worden.

Daaronder staat een Excel-tabel met daarin de omschrijvingen van de berekeningen die op de gegevens kunnen worden toegepast. De berekeningen zelf staan op het tabblad Berekeningen. Ook hier zult u voor uw eigen rapportage aanpassingen moeten doorvoeren en wel in de tweede en derde kolom.

NB in het voorbeeld-stramien staan 10 berekeningen, maar dit mogen er ook meer (of minder) zijn.

LET OP de opmaak van berekende items is standaard gelijk aan de opmaak van de onderliggende data; in het voorbeeld hierboven zal de opmaak van Waarde deze maand in het geval van Verkoop-cijfers gelijk zijn aan Valuta, maar die van Klanten heeft dus de opmaak Getal.
Maar bij berekening 7 (in dit geval) geven we aan dat alle items hiervan de opmaak % met 1 decimaal zullen hebben.

Als laatste geven we nog aan wat voor een soort rapportage het hier betreft.
Er zijn 2 mogelijkheden: alleen de data van het lopende jaar worden getoond (YtD = Year to Date) of altijd de laatste 12 maanden (YoY = Year on Year).

Iedere rapportage-pagina heeft bovenaan 3 blokjes met de belangrijkste gegevens/berekeningen.
Bij Indeling overzichtsblokken wordt bij DataNr allereerst aangegeven welke 3 van de 10 items moeten worden getoond; bij Berekening kun je 5 opties kiezen die voor deze items moeten worden weergegeven.

De sparkline daaronder geeft het verloop in de tijd weer van het betreffende item (YtD of YoY). Met de instellingen zoals hierboven is het resultaat:

Data

In het Voorbeeldbestand is het tabblad Data gevuld met fictieve gegevens. Het systeem is beperkt tot 10 kolommen met gegevens waarvan de namen vastliggen in het tabblad Instel.

Per maand dienen alle gewenste kolommen met de juiste data gevuld te worden. Gebruik je ook berekeningen waarbij de actuele gegevens afgezet worden tegen de beoogde resultaten dan dienen ook de Doel-kolommen gevuld te worden.

Berekeningen

In het tabblad Berekeningen van het Voorbeeldbestand worden allereerst van alle 10 items (en de daarbij behorende doelen) de YoY-data opgehaald.

Daaronder wordt de opmaak van (nu) maximaal 16 berekeningen per item bepaald.
De betreffende formule is uitdagend te noemen:
=ALS(INDEX(tblBerekOpties[Opmaak];[@Nr])=0;INDEX(tblOpmaak[OpmCd];VERGELIJKEN(INDEX(tblDataInd[Opmaak];VERGELIJKEN(tblYoY[[#Kopteksten];[Kol1]];tblDataInd[Kolom];0));tblOpmaak[Naam];0));INDEX(tblOpmaak[OpmCd];VERGELIJKEN(INDEX(tblBerekOpties[Opmaak];[@Nr]);tblOpmaak[Naam];0)))

In het derde blok worden de benodigde berekeningen gedefinieerd. In cel D41 staat een simpele verwijzing naar een cel uit het eerste blok.

De formule waarmee berekening 8 (het gemiddelde van de laatste 3 maanden) wordt uitgevoerd, is:
=GEMIDDELDE(VERSCHUIVING(D16;-2;0;3;1))
ofwel we gaan vanaf cel D16 2 regels omhoog en 0 kolommen naar rechts/links en kiezen dan een blok cellen, 3 hoog en 1 breed; van deze range wordt het gemiddelde bepaald.

Nog even de vorige 2 stappen combineren met (in cel D61) de formule:
=ALS.FOUT(TEKST(D41;D21);0)

Dus met de functie TEKST wordt de inhoud van cel D41 opgemaakt met de inhoud van cel D21. Als dit onverhoopt een probleem oplevert, dan wordt de waarde 0 weergegeven.

Met al dat voorwerk kunnen we nu de blokjes in de kop van de rapportages samenstellen. Ook de cijfers voor de sparklines worden klaar gezet.

Omdat we ook een grafiek in de rapportage willen opnemen, moeten we de benodigde gegevens nog even klaar zetten.

Onderaan staat een draaitabel. Een Slicer in de rapportage bepaalt welk item we willen weergeven. Het betreffende nummer gebruiken we in het grafiekblok.

Rapportage-pagina’s

In het tabblad Ovz1 van het Voorbeeldbestand staat een eerste voorbeeld van een rapportage gebaseerd op alle berekeningen uit de rest van het werkblad. Wijzigingen in de diverse tabbladen hebben direct effect op het resultaat. Kies in de slicer aan de rechterkant van de grafiek een ander item en je krijgt de bijbehorende grafiek.

Wil je naast de drie belangrijkste items met de berekeningen in de blokjes aan de bovenkant van de pagina alle info in één oogopslag: zie het tabblad Ovz2 van het Voorbeeldbestand.

Uiteraard is dit nog geen totaal rapportage; ongetwijfeld kunt u nog andere invalshoeken vinden die van belang zijn. Kopieer een Ovz-tabblad en ga aan de slag!


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


Reviews



Alle (on-line) bedrijven willen tegenwoordig van hun klanten graag een review ontvangen.
Dit is niet alleen interessant voor de marketingafdeling maar ook voor nieuwe kopers. Ikzelf kijk tenminste ook altijd gauw door de lijst reviews om te zien wat anderen van het product vinden.

Meestal kun je een product waarderen met 1-5 sterren.
De betrouwbaarheid van de gemiddelde score (zoals hierboven de 4,7) wordt natuurlijk steeds groter met het aantal reviews dat er is uitgebracht. Maar de kwaliteit van de score wordt ook bepaald door de spreiding van de uitgebrachte scores. Vandaar dat je meestal nog kunt doorklikken:

In dit artikel gaan we wat mogelijkheden binnen Excel langs waarmee we bij een review-overzicht snel en interactief op de details kunnen inzoomen (het idee en de cijfers zijn ontleend aan een artikel van Chandoo).

Basis-gegevens

In het Voorbeeldbestand in het tabblad Reviews ziet u een bestand met voor enkele top-deals van Bol.com een reviewer-code met de daarbij behorende FICTIEVE score.

Door middel van een draaitabel kunnen we snel een overzicht met gemiddeldes genereren:

Maar om makkelijk een overzicht van de onderliggende details te kunnen tonen gaan we een andere methode hanteren.

Samenvattend overzicht

In kolom C hebben we de namen van de aanbiedingen geplaatst, waar we de gemiddelde Rating van willen weten (zie het tabblad Ovz1 van het Voorbeeldbestand).

NB wil je snel alle items hebben, die in het basis-bestand voorkomen, dan liggen er 3 methodes voor de hand:

  1. maak een draaitabel zoals hierboven en kopieer de namen naar de gewenste plaats
  2. gebruik de optie Geavanceerd in het blok Sorteren en filteren van de menutab Gegevens:
    en vul bij Kopiëren naar de gewenste plaats in
  3. kopieer de kolom met de aanbiedingen en kies dan de optie Dubbele waarden verwijderen in het blok Hulpmiddelen voor gegevens van de menutab Gegevens.

In cel D3 staat de formule =GEMIDDELDE.ALS(tblReviews[Aanbieding];C3;tblReviews[Rating])
Ofwel bepaal het Gemiddelde van alle Ratings als de naam in C3 gelijk is aan een naam in de kolom Aanbieding van de Excel-tabel tblReviews.

In kolom E wordt het gemiddelde grafisch weergegeven. Daarbij worden 3 symbolen uit het font Wingdings2 gebruikt: (zie het tabblad Berekeningen).

De cellen met de symbolen hebben respectievelijk de namen crkVol, crkHalf en crkLeeg gekregen.

Cel E3 bevat de formule:
=HERHALING(crkVol;GEHEEL(D3+0,25))
&HERHALING(crkHalf;ALS(EN(REST(D3;1)>0,25;REST(D3;1)<0,75);1;0))
&HERHALING(crkLeeg;GEHEEL(5-(D3-0,25)))

Eerst wordt het aantal volle cirkels bepaald door het gehele deel van de waarde in D3 te nemen (door daar 0,25 bij op te tellen wordt een waarde van bijvoorbeeld 1,8 ook voorzien van 2 volle cirkels); de functie Herhaling genereert een tekst met het gewenste aantal volle cirkels.

Als de Rest van deling van D3 door 1 groter is dan 0,25 EN kleiner dan 0,75 dan zal de tweede Herhaling-functie een halfvolle cirkel opleveren, anders niets.

Als laatste wordt het aantal toe te voegen lege cirkels gegeneerd.

Door middel van het &-teken worden de drie teksten aan elkaar gekoppeld.

LET OP op deze manier wordt een tekst gemaakt die uit symbolen uit het font Wingdings2 bestaat. Om deze tekst in Excel zichtbaar te maken moeten de betreffende cellen in kolom E dan ook het lettertype Wingdings2 hebben.

Zoals hiervoor gezegd hebben we details over het aantal reviews en de spreiding in de scores nodig om de gemiddeldes op waarde te schatten.

Detail overzicht

In het tabblad Berekeningen van het Voorbeeldbestand vindt u een overzicht van de scores van een bepaalde aanbieding.
Afhankelijk van de waarde in cel C2 (met de naam aanbNr) wordt in cel D2 (met de naam aanbNm) de naam van de betreffende aanbieding opgehaald door middel van de functie Index (lstAanbiedingen is de lijst producten in kolom C van het tabblad Ovz1).

Cellen B1:B9 bevatten de getallen 1 t/m 5; alleen hebben die een Aangepaste notatie gekregen: “Score “0
Selecteer daartoe de cellen, druk op Ctrl-1 (Celeigenschappen) en kies het tabblad Getal:

In cel C5 staat de formule: =AANTALLEN.ALS(tblReviews[Aanbieding];aanbNm;tblReviews[Rating];B5)
Ofwel bepaal het aantal als de waarde in de kolom Aanbieding van de Excel-tabel tblReviews gelijk is aan aanbNm en de waarde in Rating gelijk is aan de waarde in B5 (het woord Score daar is opmaak!).
Cel C5 heeft ook weer een Aangepaste notatie (0) gekregen.

In cel C10 wordt de som bepaald van de cellen daarboven met als Aangepaste notatie 0 “reviews”. Dit levert het totaal aantal reviews voor de betreffende aanbieding. Dit totaal wordt in de cellen D5:D9 overgenomen. Dit hebben we nodig voor het maken van de bijbehorende grafiek.

Detail grafiek

Van het detail-overzicht kunnen we nu makkelijk een grafiek maken:

  1. selecteer de cellen B4:D9 in het tabblad Berekeningen
  2. kies in de menutab Invoegen in het blok Grafieken de optie Kolom- of staafdiagram
  3. kies het eerste staafdiagram (Gegroepeerde staaf)

We hebben op deze manier een grafische weergave van de aantallen per score vergeleken met het totaal aantal reviews.

Deze grafiek is gekopieerd naar het tabblad Ovz1; zoals te zien is doet wat opmaak wonderen:

  1. verwijder de horizontale as, de gridlijnen en de legenda
  2. klik op Grafiektitel, plaats de cursor in de Formulebalk, tik in = en klik met de muis op cel C10 van het tabblad Berekeningen
  1. klik ergens in de grafiek met de rechter muisknop en kies Gegevens selecteren. Verplaats Aantal naar beneden
  2. klik rechts op één van de staven en kies de optie Gegevensreeks opmaken.
    Zet de Overlapping van reeks op 100%.
  3. klik rechts op één van de scores en kies As opmaken.
    Zet het vinkje aan bij Categorieën in omgekeerde volgorde.
  4. klik rechts op één van de grote staven en kies de optie Gegevenslabels toevoegen.
    Klik rechts op één van de labels en kies de optie Gegevenslabels opmaken. Kies de optie Waarde uit cellen en selecteer de cellen C5:C9. Vink de optie Waarde uit.
  5. pas de overige opmaak naar wens aan.

Interactief details tonen (1)

Nu moeten we nog een methode vinden om vanuit het review-overzicht de detail-grafiek te tonen. Op het tabblad Ovz1 van het Voorbeeldbestand hebben we gebruik gemaakt van Keuzerondjes:

  1. kies in de menutab Ontwikkelaars in het blok Besturingselementen de optie Invoegen

    en kies daar de optie Keuzerondje (Formulierbesturingselement).
    NB zie je geen menutab Ontwikkelaars? Klik ergens in het menugedeelte met de rechter muisknop en kies de optie Het lint aanpassen. Zorg dan dat in het rechterblok de optie Ontwikkelaars is aangevinkt.
  2. klik in de cel waar je het eerste rondje wilt hebben. Verschuif het rondje eventueel nog zodat hij precies op de gewenste plaats staat.
  3. pas de standaard-tekst Keuzerondje 1 aan; in dit geval wissen we de tekst helemaal.
  4. klik rechts op het rondje en kies de optie Besturingselement opmaken. Zorg dat bij Koppeling met cel een verwijzing naar de cel Berekeningen!$C$2 komt.
  5. klik rechts op het rondje en kies Kopiëren
  6. toets 8 keer Ctrl-V (plakken) in
  7. plaats het onderste rondje op de juiste plaats
  8. selecteer alle rondjes door er op te klikken met Ctrl ingedrukt
  9. kies in de menutab Hulpmiddelen voor tekenen/Opmaak in het blok Rangschikken de optie Uitlijnen en kies daarna Links uitlijnen; daarna nogmaals Uitlijnen met de optie Verticaal verdelen.

Wanneer je nu een rondje aanklikt zal het overeenkomende nummer in cel C2 van het tabblad Berekeningen worden geplaatst. Het detail-overzicht en de bijbehorende grafiek(en) passen zich dan automatisch aan.

NB aan de cellen C3:E11 is een voorwaardelijke opmaak gekoppeld die er voor zorgt dat de achtergrond van de gekozen aanbieding wordt gekleurd:

Interactief details tonen (2)

Een andere methode is in het tabblad Ovz2 van het Voorbeeldbestand geïmplementeerd.

Als je daar op één van de cellen C3:E11 klikt dan start een VBA-routine die er voor zorgt dat cel C2 (met de naam aanbNr) van het tabblad Berekeningen wordt aangepast.

  1. klik met de rechter muisknop op de naam van één van de tabbladen en kies de optie Programmacode weergeven
  2. klik in het Project-overzicht aan de linkerkant van het scherm dubbel op het blad Ovz2
  3. plaats in het codescherm de volgende programma-regels:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim topRij As Integer
         If Not (Application.Intersect(Target, Range(“lstOvz2”).Cells) Is Nothing) Then
              topRij = Range(“lstOvz2”).Cells(1, 1).Row
              [aanbNr] = Target.Row() – topRij + 1
         End If
    End Sub

    Kortweg: als in dit werkblad de selectie wordt veranderd (door bijvoorbeeld het klikken met de muis of het gebruik van de cursortoetsen) dan zal de VBA-routine kijken of er een overlap is tussen de cel die is geselecteerd (de Target) en de cellen van lstOvz2 (dus C3:E11). Is dat zo dan wordt het rijnummer van de eerste cel van deze lijst in de variabele topRij geplaatst. Door deze topRij af te trekken van het rijnummer van de Target-cel weten we welke aanbieding aangeklikt is (nog wel even corrigeren met +1).

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


Wie, wat, waar, hoe?



Ik kwam pas een (oud) artikel van Chandoo tegen, waarin hij aangeeft dat hij af en toe door de bomen het bos niet meer ziet: zijn artikelen en video’s genereren zoveel reacties, dat hij niet snel meer ziet of er vragen bijzitten die beantwoord moeten worden.

Zijn oplossing gaan we in dit artikel van G-Info eens onder de loep nemen.

Probleem

Chandoo wilde dus snel zijn reacties filteren, zodat hij alleen vragen overhield. Door alleen te selecteren op het feit of een zin een vraagteken bevat, levert meestal niet het gewenste resultaat op (zoals uit het simpele voorbeeld hiernaast mag blijken).

Zijn oplossing was om om te onderzoeken of bepaalde woorden voorkomen in de reacties.
Dat bleek nog niet voldoende: ook bepaalde combinaties van woorden kunnen aangeven dat het een vraag betreft:

Oplossing

Om te controleren of een zin een vraag bevat, moeten we analyseren of één of meerdere ‘Vraag-woorden‘ in de zin voorkomen.

In het Voorbeeldbestand zijn de woorden die aangeven of we te maken hebben met een vraag opgenomen in een Excel-tabel met de naam tblVrWoorden (zie kolom B in het tabblad Vraag). De tabel-kolom met woorden heeft de naam Vraag-woorden.

NB voor uitleg over de voordelen van en toelichting op het gebruik van Excel-tabellen zie de artikelen Kunst en Excel en Tabellen (deel 2).

In cel E3 staat de formule =AANTAL.ALS(D3;”*”&tblVrWoorden[Vraag-woorden]&”*”)

Ofwel: bepaal het aantal keren dat één van de mogelijke Vraag-woorden voorkomt in D3.

Controleer de werking van de formule in E3 door middel van de menu-optie Formule evalueren in de menutab Formules.
Klik op Evalueren en Excel zal de stap die onderstreept is uitvoeren:

Blijf op Evalueren klikken om alle berekeningstappen na te lopen. Doe je dit ook voor de cellen E4, E5 etcetera dan is duidelijk te zien dat Excel telkens maar één vraagwoord onder de loep neemt in plaats van de hele reeks.

NB de functie Aantal.Als herkent het gebruik van wildcards; door vóór en achter de vraagwoorden een * te plaatsen geven we aan, dat er nog iets vóór of achter het woord (of woordcombinatie) mag staan ofwel dat zo’n woord willekeurig ergens in de tekst kan voorkomen.

Maar doe nu eens het volgende:

  1. selecteer cel E6
  2. klik in de formulebalk achter de formule
  3. druk op het toetsenbord op de functietoets F9 (Berekenen)
  4. de formule verandert in: ={0;0;0;1;0;0;1;0;0;0;0}
  5. druk NIET op Enter maar op Esc

NB je kunt ook een gedeelte van een formule laten berekenen: selecteer bijvoorbeeld in de formulebalk alleen het gedeelte achter de punt-komma en vóór het haakje-sluiten en druk dan op F9.

Nu is te zien, dat Excel toch slimmer is dan we dachten! Hij heeft wel alle Vraag-woorden vergeleken met de inhoud van cel D6 en geconstateerd dat er 2x sprake is van een vraag (de 4e en 7e optie). De functie Aantal.Als levert een array van resultaten op en daar kunnen we gebruik van maken.

In kolom F bepalen we de Som van de waardes uit de array, die door Aantal.Als is gegenereerd.

LET OP we moeten Excel wel ‘influisteren’ dat we te maken hebben met een array-berekening: sluit de formule NIET af met Enter maar met Ctrl-Shift-Enter, de zogenaamde CSE-methode.

Een alternatieve oplossing, waarbij de CSE-methode niet nodig is, is het gebruik van de functie SomProduct (zie kolom G). Lees ook het artikel SOMPRODUCT: meer dan SOM en PRODUCT.

Ander voorbeeld

Een garage heeft een goederenoverzicht met codes en omschrijvingen (zie het tabblad Onderdelen van het Voorbeeldbestand).
Om de efficiency en de consistentie te waarborgen wordt afgesproken dat aan de servicecorner deze codes ook gebruikt moeten worden bij het aanmaken van de werkbonnen.

Om verschil te maken met bijvoorbeeld klantnummers wordt vóór een goederencode een # geplaatst.

In kolom C kunnen we met een vergelijkbare formule als hiervoor beoordelen of er sprake is van een onderdeel (let op het # achter het eerste sterretje).

Als we de garage dan toch automatiseren: in kolom D zoeken we, als er sprake is van een Onderdeel, het betreffende nummer op:
=ALS([@[Onderdeel?]]=”Ja”;WAARDE(DEEL([@Opmerking];VIND.ALLES(“#”;[@Opmerking])+1;7));””)
en in kolom E de daarbij behorende omschrijving:
=ALS([@[Onderdeel?]]=”Ja”;INDEX(tblOnderdelen[Omschrijving];
VERGELIJKEN([@[Welk?]];tblOnderdelen[Goederencode];0));””)

Nog een voorbeeld

Een school werkt niet met cijfers voor proefwerken met met lettercodes (zie het tabblad Proefwerk van het Voorbeeldbestand).

Ook ligt in een tabelletje vast welke codes overeenkomen met een voldoende.

Nu wordt per leerling in kolom P de tabel met voldoende-codes vergeleken met de 10 proefwerkkolommen en wordt het aantal voldoendes bepaald. Nog wat Voorwaardelijke opmaak en we zien snel hoe de diverse leerlingen er voor staan.

LET OP dit is weer een CSE-formule.

NB1 telt nv niet mee als voldoende (de betekenis is ineens niet voldoende 😉) dan verwijderen we de betreffende rij in de tabel tblVoldCd. Het resultaat in kolom P past zich direct aan.

NB2 om te zien wat de invloed is van de proefwerk-resultaten op kolom P staat vanaf rij 10 in het tabblad Proefwerk een overzicht waarbij de resultaten bij iedere druk op de F9-toets willekeurig veranderen.

Cadeaus

Decembermaand is de tijd van cadeaus. Voor de liefhebbers hier 2 links:

  1. Chandoo: uitleg van diverse ‘interessante’ formules
  2. How to Excel: een overzicht van diverse Excel-websites

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


Doorvoeren 2



In een eerder artikel (Doorvoeren en sorteren) hebben we het al eens gehad over de mogelijkheden om gegevens in Excel snel in te voeren/aan te vullen.

In dit artikel komen (nogmaals) wat tips langs maar gaan we ook wat meer complexe alternatieven behandelen.

Herhaling

Even in het kort een opsomming van de basis-items uit het vorige artikel:

  1. wat bedoelen we met doorvoeren: een cel, gevuld met een waarde of formule, kun je snel kopiëren door de zogenaamde vulgreep rechtsonder in de cel met de muis naar beneden of naar rechts te verslepen.
  2. na het gebruik van de vulgreep komt rechtsonder een kleine button tevoorschijn: de Opties voor Automatisch doorvoeren.
    LET OP de opties zijn contextgevoelig; dat wil zeggen dat het aantal opties en de mogelijkheden afhankelijk zijn van de inhoud van de gekopieerde cel.
  1. als links of rechts van een cel al een kolom is gevuld dan kun je ook met de muis dubbelklikken op de vulgreep van die cel: Excel kopieert dan automatisch de cel zo vaak naar beneden als er cellen in de kolom daarnaast gevuld zijn.
    LET OP met dubbelklikken kun je geen rijen automatisch vullen
  2. het effect van het doorvoeren van een cel hangt af van de inhoud van die cel; daar gaat de rest van onderstaand artikel over.
  3. voor meer informatie: zie het artikel Doorvoeren en sorteren.

NB1 meestal zul je de vulgreep naar rechts of naar beneden trekken. Maar naar links of naar boven kan ook met dezelfde soort resultaten.

NB2 hierboven is sprake van een vulgreep van één cel. Maar als je eerst een groep cellen selecteert, dan kun je ook de vulgreep van die groep gebruiken.

Doorvoeren van tekst

Laten we beginnen met het doorvoeren van een cel die een tekst bevat.
In het tabblad VulgreepTxt van het Voorbeeldbestand beginnen we met cel C3.

‘Pak met de muis’ de vulgreep van cel C3 en trek die naar beneden tot en met cel C14. Het resultaat: alle betreffende cellen van kolom C worden gevuld met de tekst Een.

Maar wanneer we nu de cellen D3:D4 selecteren en de vulgreep naar beneden trekken dan worden de betreffende cellen van kolom D afwisselend gevuld met de tekst Een en Twee.

Het resultaat van het op deze manier doorvoeren van de cellen E3:E5 zal dan niet meer verbazen.

NB omdat de cellen B3:B14 al gevuld zijn kun je ook iedere keer op de betreffende vulgreep dubbelklikken.

LET OP met behulp van deze methode vult Excel alle nieuwe cellen met ‘harde’ waardes. Wanneer de start-gegevens wijzigen moet de Doorvoer-actie dus opnieuw worden uitgevoerd!
Dit geldt voor alle methodes die in dit artikel behandeld worden (nou ja, bijna alle).

Het wordt wat interessanter wanneer we de tekst jan (cel F3) gaan doorvoeren: Excel vult de cellen daaronder (of daarnaast) met de daarop volgende maand-aanduidingen!

NB1 voor de uitleg hiervan en wat je daarmee kunt doen, zie Doorvoeren en sorteren.

NB2 begint de maandaanduiding met een hoofdletter dan beginnen de gekopieerde cellen ook met een hoofdletter.

Eindigt een tekst op een getal (zoals hiernaast waar we met productnummer 1 beginnen) en we gebruiken de vulgreep dan nummert Excel de serie automatisch door.

NB dit is eigenlijk een voorbeeld van het doorvoeren van een getal (zie hierna).

LET OP deze ’truc’ werkt alleen als het getal op het einde van de tekst staat. Maar begint de tekst met een getal EN een spatie dan werkt het weer wel!

Een speciale variant van het kopiëren van meerdere cellen ziet u hier. Een constant verloop van cellen met tekst met daartussen een lege regel.

LET OP vergeet bij de selectie niet om onderaan ook een lege cel mee te nemen.

Doorvoeren tekst met Opties

Zoals we in de Herhaling hiervoor al hebben gezien krijg je na het doorvoeren altijd nog een button met Opties.
Deze zijn contextgevoelig.

In het geval van het doorvoeren van teksten zijn de mogelijkheden beperkt:

Cellen kopiëren is de standaard-bewerking, het wel of niet doorvoeren van opmaak spreekt voor zich. De laatste optie zullen we hieronder toelichten.

NB de Opmaak doorvoeren geldt ook voor Voorwaardelijke opmaak. Dit is makkelijk als je achteraf een cel een voorwaardelijke opmaak hebt gegeven en die wilt doorvoeren naar de rest van de kolom of rij (zie kolom M van het tabblad VulgreepTxt).

Tekst Snel aanvullen

Wanneer we in het tabblad VulgreepTxt van het Voorbeeldbestand de kolommen L en M hebben doorgetrokken naar rij 14 en daarna de vulgreep gebruiken in kolom N dan krijgen we het resultaat zoals links weergegeven.

Kiezen we bij de Opties het onderdeel Snel aanvullen dat verandert het resultaat. Excel combineert de gegevens uit de kolommen L en M.

LET OP niet altijd ‘begrijpt’ Excel direct wat je wilt bereiken met Snel aanvullen. Vaak helpt het om nog enkele voorbeelden meer met de hand te vullen.

Doorvoeren van getallen

Wanneer we cellen met getallen gaan Doorvoeren dan wordt het wat interessanter ofwel we hebben meer mogelijkheden (zie het tabblad VulgreepNum van het Voorbeeldbestand).

Voeren we één cel door (cel C3) dan zal het resultaat standaard alleen maar kopieën van deze cel bevatten (dus de waarde 1).

Selecteren we eerst 2 cellen (met bijvoorbeeld de waardes 1 en 2) dan vult Excel de volgende cellen ook met oplopende waardes.

Hetzelfde resultaat (dus opvolgende getallen) kunnen we ook bereiken vanuit het doorvoeren van één cel, maar dan moet je bij Opties het onderdeel Reeks doorvoeren kiezen.

NB kies je niet 2 opeenvolgende getallen dan zal Excel de reeks aanvullen met ook telkens hetzelfde interval:
LET OP als je meer dan 2 cellen selecteert dan wordt het voor Excel steeds moelijker te ‘bedenken’ wat de volgende getallen zullen moeten zijn:

Wil je gewoon eenzelfde reeks getallen kopiëren kies dan na het doorvoeren de optie Cellen kopiëren.

Zoals we al eerder gezien hebben gedraagt een cel die een tekst bevat, eindigend op een getal, bij het doorvoeren net alsof de cel een getal bevat. Dit geldt ook als er meerdere cellen geselecteerd worden om door te voeren.

Doorvoeren van getallen 2

Tot nu toe hebben we de vulgreep gebruikt om de inhoud van cellen ‘door te voeren’. Voor numerieke cellen zijn er nog meer mogelijkheden, maar dan moeten we wel via de menu-structuur aan de slag.

Eerst een simpel voorbeeld (zie het tabblad DoorvoerenNum van het Voorbeeldbestand):

  1. selecteer cel C3
  2. kies in de menutab Start in het blok Bewerken de optie Doorvoeren:
  3. kies de optie Reeks en er opent een pop-up.
  4. aangezien we de reeks naar beneden willen kopiëren kiezen we Kolommen .
  5. ook vullen we de Interval– en Eindwaarde in.
  6. klik op OK.

Wanneer het aantal te vullen cellen beperkt is in aantal, dan is de volgende methode handiger:

  1. in het tabblad DoorvoerenNum is cel D3 gevuld met de waarde 1
  2. selecteer de cellen D3:D12
  3. kies in de menutab Start in het blok Bewerken de optie Doorvoeren/Reeks
  4. Kolommen is automatisch geactiveerd
  5. kies een Intervalwaarde; de Eindwaarde hoeven we niet in te vullen omdat we al een bereik hebben geselecteerd

Maar het interval hoeft niet lineair te zijn; we kunnen ook voor Groei kiezen. Excel zal dan iedere cel met de Intervalwaarde vermenigvuldigen om de volgende celwaarde te bepalen (zie kolom H in het tabblad DoorvoerenNum).

Excel kan ook een trend gebruiken om cellen te vullen:

  1. de cellen F3:F5 in het tabblad DoorvoerenNum zijn gevuld met de waardes 1, 2 en 3
  2. selecteer de cellen F3:F12
  3. kies de optie Doorvoeren zoals hiervoor besproken
  4. vink Trend aan en klik OK

LET OP met de laatste optie (Trend) kunnen gegevens overschreven worden!
De cellen G3:G5 waren gevuld met de waardes 1, 3 en 4. Na het doorvoeren met een Trend-reeks worden deze 3 getallen gewijzigd:

NB1 deze methode is geschikt om snel bestaande gegevens om te zetten in een trendlijn: vul alle bekende waardes in, selecteer deze cellen (ze moeten in een rij of kolom staan) en kies Doorvoeren/Reeks/Trend. Maar let dus op: alle waardes worden overschreven, dus gebruik een kopie van de bekende gegevens.

NB2 door niet alleen cellen met bekende gegevens te selecteren maar daarnaast/ daaronder ook lege cellen kan de berekende trend ook naar de toekomst doorgetrokken worden.

Doorvoeren van datums

Omdat datums voor Excel niets anders zijn dan getallen (met een speciale opmaak) zou je verwachten dat het Doorvoeren van datums hetzelfde zou zijn, maar niets is minder waar; er komen diverse mogelijkheden bij!

In het tabblad VulgreepDtm van het Voorbeeldbestand is cel C3 naar beneden doorgevoerd. Waar bij een getal de kopieën allemaal dezelfde waarde krijgen zien we nu dat Excel de datum ophoogt.

NB Zoals al eerder aangegeven worden alle cellen gevuld met een ‘harde’ waarde. Wil je een Excel-systeem flexibeler maken dan kun je beter de methode uit kolom D gebruiken; daar is de formule in D4 naar beneden gekopieerd. Wijzig je de datum in D3 dan is automatisch de hele kolom aangepast.

In kolom E zijn de cellen E3:E4 doorgetrokken: ook de andere cellen krijgen dan datums, die 7 dagen uit elkaar liggen. In dit geval krijgen we dus een reeks maandagen.

In kolom F is cel F3 met de vulgreep naar beneden gekopieerd. Het resultaat is standaard een opeenvolging van dagen. Maar zoals hiernaast te zien is, kunnen we bij Opties ook kiezen dat Excel alleen werkdagen doorvoert.

NB kolom F bevat ‘gewone’ datums, maar wel met een speciale opmaak.

De overige opties in dat scherm spreken voor zichzelf:

Het doorvoeren van datums via de menu-optie Doorvoeren biedt vergelijkbare mogelijkheden.

Doorvoeren van formules

De effecten van het doorvoeren van cellen met formules zijn helemaal anders dan bij cellen met harde waardes.

Om dat te laten zien gaan we een overzicht maken van het resultaat van sparen: hoe ontwikkelt het spaarbedrag zich in de loop van de tijd wanneer we maandelijks een bedrag inleggen (zie het tabblad VulgreepForm van het Voorbeeldbestand:

  1. in de cellen C2:C4 staan de basis-gegevens
  2. cel B7 bevat de start-maand; eigenlijk een dag-aanduiding maar met een speciale opmaak
  3. selecteer cel B7 en trek de vulgreep naar beneden t/m cel B30
    NB mooi toch: Excel ‘ziet’ aan de opmaak dat we geïnteresseerd zijn in de maanden en hoogt de cellen in kolom B op met 1 maand in plaats van (standaard voor een datum) met een dag.
  1. cel C7 bevat een verwijzing naar het startkapitaal: =C4
  2. de rente in de eerste maand berekenen we als volgt: =C7*$C$2/12
    NB de betekenis van de $-tekens zien we hierna
  3. de inleg in de maand: =$C$3
  4. en op het einde van de eerste maand hebben we dan in kas: =SOM(C7:E7)
  5. de begin-stand van de 2e maand is gelijk aan de eindstand van de 1ste, dus cel C8 bevat de formule: =F7
  6. de rest van de berekeningen voor de 2e maand zijn gelijk aan die van de vorige maand: selecteer de cellen D7:F7, trek de vulgreep 1 rij naar beneden. De rente in de 2e maand heeft nu de formule: =C8*$C$2/12
  7. voor de 3e maand zijn de berekeningen nu gelijk aan die van de 2e: selecteer de cellen C8:F8 en dubbelklik op de vulgreep
  8. de belangrijkste gegevens staan in een apart overzichtje bij elkaar:

Het gebruik van de vulgreep bij cellen met formules werkt dus echt anders dan hiervoor. Het grootste gedeelte van de formule blijft hetzelfde, behalve dat verwijzingen naar cellen veranderen: wordt de formule naar beneden gekopieerd dan wijzigen de rij-aanduidingen. Hadden we de vulgreep naar rechts getrokken dan zouden de kolom-aanduidingen zijn veranderd.
Wanneer een kolom- en/of rij-aanduiding wordt voorafgegaan door een $-teken blijft deze aanduiding bij de kopieer-actie ongewijzigd. We noemen dit een absolute verwijzing in tegenstelling tot een relatieve verwijzing wanneer er geen $-teken wordt gebruikt.


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


Grenzen aan de groei – 2


LET OP: na het downloaden de extensie wijzigen in xlsb


In het vorige artikel (Grenzen aan de groei – 1) hebben we beloofd dat we een poging zouden wagen om het model uit het rapport van de Club van Rome in Excel te implementeren, althans een vereenvoudigde versie daarvan.
In dat artikel is te lezen hoe we dat zouden willen doen en aan de hand van wat vingeroefeningen hebben we laten zien dat het (in theorie) mogelijk zou moeten zijn.

Die laatste conclusie staat nog steeds, maar helaas hebben we wel moeten constateren dat de hoeveelheid verbanden tussen de diverse variabelen en de daarbij behorende parameters zo groot is dat een totale implementatie heel erg veel tijd gaat kosten. Dus deze keer vormt het Voorbeeldbestand geen afgerond project.
In dit artikel zullen we laten zien hoe ver we gekomen zijn en welke Excel-opties daarbij zijn gebruikt.

Voor eenieder de uitdaging om de ‘handdoek in de ring’ weer op te rapen en het model verder uit te werken!

Nogmaals: het model

(zie het tabblad SystemDynamics van het Voorbeeldbestand)

Dit is een schematische weergave van het (vereenvoudigde) ‘Club van Rome’-model; voor nadere uitleg zie het vorige artikel.

Belangrijk om te weten is het volgende:

  1. de pijlen geven aan welke relaties er tussen de variabelen zijn onderkend
  2. de pijlen laten zien in ‘welke richting’ de beïnvloeding loopt
  3. we onderkennen in het model 3 soorten variabelen: de Inputs, de Kritische Factoren en de Tussen-variabelen.
    De Input-variabelen worden niet beïnvloed door de omgeving, maar kunnen wel in de loop van de jaren variëren.
    De KF’s zijn die 5 variabelen die in alle grafieken van het rapport terugkomen.
    Alle overige vallen onder de categorie Tussen-variabelen.
  4. het rapport van de Club van Rome is in 1972 gepubliceerd; waar in dit artikel naar het verleden wordt verwezen bedoelen we dan ook de periode van 1900 tot en met 1970.
  5. voor diverse variabelen is in de literatuur (zie het tabblad Docu van het Voorbeeldbestand) te achterhalen wat de waardes in het verleden zijn geweest. Dit is de eerste basis van de implementatie van het model.
  6. bij de verdere implementatie heeft iedere variabele een eigen tabblad gekregen (behalve de Inputs; zie hierna). Alle aannames voor de berekening van een variabele staan in het betreffende tabblad vermeld. Vaak is daar ook een grafiek opgenomen van die variabele om snel het resultaat van de aannames te controleren.
Tabblad T15

Implementatie

Tabblad Beschr

In dit tabblad van het Voorbeeldbestand staat een overzicht van alle gebruikte variabelen met daarbij (zover al uitgezocht) de meest relevante berekening:

De betekenis van de Code en de Naam moge duidelijk zijn; de kolom Schema bevat de tekst zoals die op het tabblad SystemDynamics wordt gebruikt. In de kolom Omschrijving staat in het kort een nadere toelichting op de variabele en de laatste kolom bevat de (belangrijkste) formule voor de berekening van die variabele.

NB de codes onder de 10 voor de Tussenvariabelen hebben een extra 0 gekregen; dit om bij standaard-sorteringen altijd direct de juiste volgorde te hebben (anders zou bijvoorbeeld T10 vóór T2 komen).

Tabblad Inputs

Dit tabblad bevat de gegevens van de 4 Input-variabelen. Deze vormen samen de Excel-tabel tblInputs.

Op dit moment zijn alleen de I3 en I4 gevuld en per kolom hebben alle jaren dezelfde waarde. De juiste interpretatie van deze variabelen vergt nog onderzoek.

De KF-tabbladen

Zoals gezegd hebben alle KF’s (en ook de Tussen-variabelen) een eigen tabblad. Deze bladen hebben allemaal dezelfde structuur: de eerste kolom bevat de jaren en de tweede kolom de daarbij behorende waarde. Samen vormen deze 2 kolommen een Excel-tabel met een overeenkomende naam (in bovenstaand voorbeeld tblKF1).

NB de waarde-kolom heeft een voorwaardelijke opmaak: wanneer een cel een formule bevat dan wordt de inhoud in het groen, vet en cursief weergegeven.

In de volgende kolommen staat altijd de code en naam van de variabele, eventueel gebruikte bronnen en de diverse aannames.
De rest van het tabblad wordt gebruikt om zo nodig extra berekeningen uit te voeren, de aannames toe te lichten etcetera.

NB bij KF5 wordt een zogenaamde Eigen functie gebruikt (zie voor een toelichting het vorige artikel, Vingeroefening 2).

De T-tabbladen

Zoals aangegeven hebben deze tabbladen dezelfde structuur als de KF’s. In het voorbeeld hierboven is te zien dat voor T12 een formule wordt gebruikt, waarbij de gevoeligheid (GF) voor de invloed van een variabele kan worden ingesteld. Daarnaast kan aangegeven worden of de beïnvloeding een vertraging V kent en of er over een periode P gemiddeld moet worden (zie het artikel Grenzen aan de groei – 1).

LET OP in dat artikel hebben we, bij het gebruik van een periode, een rekenkundig gemiddelde gebruikt. Dat is, theoretisch gezien, niet juist. Hoewel dat in dit model, bij niet te grote ontwikkelingen in de tijd, niet echt relevant is, hebben we toch de betere methode gehanteerd waarbij het gemiddelde wordt bepaald met behulp van de Pde-machts wortel (ofwel tot de macht 1/P).

Resultaten

In principe zijn de resultaten van het model bekend als alle tabbladen zijn gevuld met waardes en formules. Door ’te spelen’ met de aannames, en vooral met de eventuele GF-, V– en P-waardes kan het model gefinetuned worden.

We zijn gestart met de onderkant van het model, het gedeelte rond de Industriële productie. De bovenkant is op de uitkomsten daarvan gebaseerd. Het implementeren van deze onderkant heeft heel wat hoofdbrekens gekost (wat is de betekenis van de variabelen, hoe zijn deze variabelen van elkaar afhankelijk, hoe kunnen we de afhankelijkheid modelleren, welke GF-, V– en P-waardes geven een zo getrouw mogelijk beeld van de werkelijkheid etcetera).

Door tijdgebrek moet G-Info het verder vullen van het model dan ook aan anderen overlaten.

Wel zullen we hieronder nog laten zien op welke manier de resultaten in Excel gemakkelijk kunnen worden weergegeven.

Tabblad Variabelen

Op dit tabblad in het Voorbeeldbestand staan de 25 variabelen van het model nogmaals in een overzicht. Maar ditmaal met aanvullende gegevens, waarmee we de lay-out van de output kunnen sturen:

  1. in de eerste kolom staat aangegeven welk soort variabele het betreft: Input, KF of Tussen
  2. de tweede kolom geeft aan of de betreffende variabele in de standaard-grafieken van het rapport van de Club van Rome is opgenomen
  3. in dat rapport wordt een variabele altijd op dezelfde (onzichtbare) schaal weergegeven. Dat kunnen we nabootsen door aan te geven welke waarde van de variabele overeenkomt met de onderkant van het grafiekgebied (de kolom GrafMin) en welke waarde met de bovenkant (GrafMax)
  4. in de laatste kolom staat een formule waarmee het label bij de betreffende lijn in de grafiek wordt bepaald. Standaard is het een combinatie van de 3e en 4e kolom, gescheiden door een ‘harde return’:

Dit overzicht is een Excel-tabel met de naam tblVar.

Query’s en verbindingen

Alle resultaten moeten nu nog geschaald worden. Dit kan uiteraard op de diverse tabbladen zelf door extra kolommen toe te voegen, maar we hebben er voor gekozen om dit met behulp van Power Query te implementeren.

NB1 Ziet u de Excel-verbindingen aan de rechterkant van het scherm niet, kies dan in de menutab Gegevens de optie Query’s en verbindingen.

Voor alle Excel-tabellen is een verbinding gemaakt. Wil je bekijken hoe die er uit ziet? Klik rechts op een verbinding en kies de optie Bewerken (of dubbel-klik op een query-naam).

NB2 de Input-query’s zijn iets ingewikkelder omdat daar één specifieke kolom uit de input-tabel moet worden opgehaald.

Bij het bewaren van de query’s is de optie Alleen verbinding maken gekozen en is de optie Toevoegen aan gegevensmodel aangevinkt.
Die laatste optie zorgt er voor dat de query’s in het gegevensmodel van deze Excel-sheet worden opgeslagen.

Het gegevensmodel bevat nog één extra query, q_GrafData. Deze combineert alle andere verbindingen tot één database. Deze database vormt de basis voor een draaitabel en een daarbij behorende grafiek.
Ook deze query is opgeslagen met de eigenschappen Alleen verbinding en Toevoegen aan gegevensmodel.

Bekijk de query door dubbelklikken op de naam.

Tabblad GrafData

Op het tabblad GrafData van het Voorbeeldbestand staat een draaitabel, gebaseerd op de query q_GrafData. Hoe genereer je zo’n draaitabel?

  1. selecteer een lege cel, waar de draaitabel moet komen
  2. kies in de menutab Invoegen in het blok Tabellen de optie Draaitabel
  3. in het pop-up scherm ziet u dat Excel het gegevensmodel zal gaan gebruiken:
  4. klik op het driehoekje vóór de gewenste query uit het gegevensmodel en sleep de benodigde velden naar de juiste plaats:

In het voorbeeld hebben we ook 3 slicers toegevoegd waarmee het maken van keuzes vergemakkelijkt wordt. Hierboven hebben we met een klik op Ja in de eerste slicer alleen die variabelen geselecteerd, die ook in het rapport van de Club van Rome in de standaard-grafieken voorkomen.

LET OP als er iets aan de parameters van het model wordt gewijzigd dan worden de gegevens op het betreffende tabblad direct gewijzigd. Ook resultaten van formules op andere tabbladen kunnen daardoor wijzigen. De query’s en de draaitabel wijzigen niet automatisch mee! Alles zal vernieuwd moeten worden.
Op het tabblad GrafData wordt met één klik op de betreffende button een VBA-routine gestart die deze totale verversing van het Excel-systeem voor zijn rekening neemt. Dit kan wel enkele minuten duren!

Tabblad Grafiek

Het tabblad Grafiek van het Voorbeeldbestand bevat een draaitabelgrafiek. Dit is de grafische weergave van de gegevens van de draaitabel van het tabblad GrafData. Dus: ook keuzes gemaakt met de slicers worden in deze grafiek meegenomen.

Duidelijk is te zien dat de implementatie van het model nog lang niet klaar is. De industriële productie en de hulpbronnen geven bijvoorbeeld wel al een verwacht verloop, terwijl de blijvende toename van de bevolking of voedsel per hoofd niet reëel is.
Het model in het Voorbeeldbestand is dan ook nog maar voor een klein gedeelte geïmplementeerd. Zoals gezegd: tijdgebrek noopt ons om de rest aan andere Excel-liefhebbers over te laten.


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