Categorie archief: Excel

Aggregaat

Excel,
Voorbeeldbestand: Aggregaat.xlsx

Regelmatig voorziet Microsoft zijn software van nieuwe functies of andere nuttige (?) uitbreidingen.
Al een poosje hebben Excel-gebruikers de beschikking over de functie AGGREGAAT; de opvolger van Subtotaal.

Na een korte toelichting op deze “nieuwe” functie zullen we inzoomen op één van de belangrijkste pluspunten hiervan: het kunnen omgaan met foutwaarden.

Inleiding

In het Voorbeeldbestand staat in het tabblad Data een kleine tabel met datums, regio’s en bedragen. Zoals te zien is staat er bij 6 januari een foutmelding in de kolom Bedrag.

Wanneer we op basis van deze tabel een draaitabel maken, dan zien we dat Excel automatisch als Waardeinstelling de optie Aantal heeft gekozen (en dat ie de datums automatisch tot maanden heeft gegroepeerd); zie bovenste draaitabel.

Uiteraard kunnen we handmatig de Waardeinstelling veranderen; in de 2e draaitabel is deze gewijzigd in Som. Helaas: niet ieder vakje is nu gevuld met een bedrag. De foutmelding in de bron verhindert dat. Ditzelfde geldt als we de instelling veranderen in bijvoorbeeld Max of Min (zie het Voorbeeldbestand).

Maar misschien kunnen we een andere Excel-optie of -functie gebruiken, zodat we dit probleem kunnen omzeilen?

Subtotaal

Aan deze functie hebben we al eens eerder een artikel gewijd.

Deze Excel-functie bestaat eigenlijk uit 11 verschillende functies (waarvan Gemiddelde, Som, Max, Min en Aantalarg het meest gebruikt worden). Ook kun je door het juiste gebruik van Functie_getallen verborgen waarden al dan niet meenemen (zie het betreffende artikel).

In het tabblad Subtotaal van het Voorbeeldbestand kun je door het aanpassen van cel F3 zien wat het resultaat van de functie Subtotaal is.
Ook hier zullen Som, Min etc foutmeldingen genereren.

Bekijk wat het resultaat is als je in het tabblad Data de groepering sluit ( of een filtering aanbrengt in de tabel met gegevens.
Wijzig ook cel D19 in =115/1 en bekijk de consequenties voor de functie Subtotaal.

Aggregaat

De functie Aggregaat wordt door Microsoft als vervanger voor Subtotaal gepositioneerd. Deze laatste is alleen vanwege compatibiliteit in de gereedschapskist blijven zitten.

In principe kent Aggregaat 2 verschijningsvormen () waarbij we ons hier op de eerste zullen concentreren.

Aan het overzicht op het tabblad Aggregaat van het Voorbeeldbestand is te zien dat het aantal Functies (de eerste parameter) is uitgebreid tot 19.

Dus bijvoorbeeld ook mediaan- en percentiel-berekeningen zijn nu met behulp van deze functie mogelijk.

Maar de 2e parameter is zeker zo interessant: hiermee kunnen we bijvoorbeeld aangeven of foutwaardes en/of verborgen rijen moeten worden genegeerd et cetera.

Als derde parameter geven we het bereik mee, waarop de berekening moet worden uitgevoerd. De vierde parameter wordt alleen gebruikt vanaf de Functie_getallen 14 en hoger.

In het eerste voorbeeld wordt de Som van de diverse kolommen bepaald, waarbij wel of niet de Foutwaarden worden genegeerd.

NB in de formule in cel F4 is de 2e parameter leeg gelaten; dit is hetzelfde als wanneer je daar een 0 als optie zou gebruiken.

LET OP LET OP LET OP LET OP de formules in regel 7 bevatten een 4e parameter. Maar wanneer het Functie_getal kleiner is dan 14 dan wordt deze parameter gezien als een extra bereik; dit bereik wordt dan bij de berekeningen van bijvoorbeeld de Som meegenomen!

In het tweede voorbeeld zal Excel de Grootste waarde in de kolommen opzoeken; dit kan natuurlijk alleen voor getallen (en dus ook datums). Vul je in cel E7 de waarde 2 in dan wordt de één na grootste waarde opgehaald.

Audit

De Aggregaat-functie kan ook goed gebruikt worden bij audit-werkzaamheden of bij de beveiliging van je Excel-systemen. In cel E12 wordt gecontroleerd of de kolom uit E11 foutwaardes bevat of niet:

=ALS(AGGREGAAT(3;0;INDIRECT(“tblData[“&E11&”]”))=AGGREGAAT(3;6;INDIRECT(“tblData[“&E11&”]”));”Geen fouten”;”Fouten gevonden!”)

Cel E12 heeft een voorwaardelijke opmaak.

Aggregaat met complexe bereiken

Op het tabblad Aggr2 van het Voorbeeldbestand staan wat voorbeelden waarmee (in dit geval) het maximale bedrag wordt bepaald.

Zolang er in de kolom Bedrag van de data-tabel fouten voor komen zal de formule in C4 geen resultaat weergeven. Wijzig ter controle cel D19 in het tabblad Data in bijvoorbeeld =115/1.

NB de formule in C4 is niet met Enter afgesloten maar met Ctrl-Shift-Enter; Excel ‘weet’ dan dat het een matrix- of CSE-berekening betreft. Excel plaatst zelf de accolades rond de formule.

In cel C5 wordt het maximum-onder-voorwaarden berekent met de fiormule:

=MAX.ALS.VOORWAARDEN(tblData[Bedrag];tblData[Regio];C2)

Zolang er als Regio niet Oost wordt gekozen levert deze formule een maximum-bedrag op.

De formule in cel C6:

=AGGREGAAT(14;3;tblData[Bedrag]*(tblData[Regio]=C2);1)

berekent altijd een maximale waarde.

LET OP voor het bepalen van het minimum-bedrag moet in de Aggregaat-functie niet alleen de 14 in 15 veranderd worden, ook de 3e parameter heeft een aanpassing ondergaan:
=AGGREGAAT(15;3;tblData[Bedrag]/
(tblData[Regio]=C2);1)

Met behulp van Formule evalueren kun je waarschijnlijk zelf bedenken waarom dat nodig is.

LET OP het gebruik van complexe bereiken is alleen toegestaan bij Functie_getallen 14 en hoger; anders krijgt u de foutmelding #Waarde!

Rapportage-stramien

Excel, , , , ,
Voorbeeldbestand: RapportageStramien.xlsx

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!

Reviews

Excel, VBA (Visual Basic for Applications), , , , , ,
Voorbeeldbestand: Reviews.xlsmDownloaden

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

Wie, wat, waar, hoe?

Excel, , , ,
Voorbeeldbestand: VrWoorden.xlsxDownloaden

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

Doorvoeren 2

Excel, , , ,
Voorbeeldbestand: Doorvoeren2.xlsxDownloaden

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.