Tagarchief: F9

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?


Dynamische grafieken

Niet iedereen zal meteen weten, wat hiermee bedoeld wordt.
De term dynamische grafiek wordt in de Excel-omgeving gebruikt, wanneer een grafiek zich direct aanpast aan wijzigingen in de bron-spreadsheet zonder dat de gebruikte bereiken voor de x- en y-waarden hoeven worden aangepast.
Het bekendste voorbeeld: er komen omzet-gegevens van een nieuwe maand beschikbaar; deze worden onderaan een reeks toegevoegd. Dan zou het fijn zijn als de bijbehorende grafiek dit automatisch zou overnemen.

Eenvoudige oplossing

Op allerlei manieren is er een bepaalde vorm van dynamiek te implementeren. DynGraf1

Laten we eerst een eenvoudige implementatie bekijken.
In het Voorbeeldbestand op het tabblad DynGraf1 zijn bedragen (kolom C) voor iedere maand (kolom A) opgenomen; in kolom B hebben de maanden een volgnummer gekregen.
Stel dat we alleen de cijfers tot en met juni willen weergeven:

  1.  cel D1 krijgt dan de waarde 6
  2. in D4 staat de formule: =ALS(B4<=$D$1;A4;””)
    Dus als het volgnummer kleiner of gelijk is aan de waarde in cel D1, dan wordt de waarde uit A4 (de maand) overgenomen en anders komt er niets (er staan alleen maar 2 aanhalingstekens)
  3. idem voor cel E4: =ALS(B4<=$D$1;C4;””)
  4. D4 en E4 kopiëren we naar beneden
  5. dan maken we een grafiek, die voor de x-waarden (horizontale as) “kijkt” naar de cellen D4 t/m D15 en voor de y-waarden naar E4 t/m E15
  6. de titel van de grafiek maken we dynamisch door een verwijzing te maken naar cel B17; dit gaat als volgt:
    * geef de grafiek een willekeurige titel
    * klik in de titel, dan in de formulebalk en voer daar het =-teken in, gevolgd door een verwijzing naar de gewenste cel: DynGraf2 en druk op Enter
  7. in cel B17 zorgen we dat de tekst aangepast wordt voor de maandkeuze:
    =”Bedragen t/m “&VERT.ZOEKEN($D$1;B4:D15;3;ONWAAR)
    Hier worden 2 teksten gekoppeld (zie het artikel Teksten samenvoegen): de “harde” tekst Bedragen t/m en de betreffende maand, die met de functie verticaal zoeken wordt opgehaald (zoek in de eerste kolom van het blok B4:D15 naar de waarde uit cel D1; geef de corresponderende waarde uit de 3e kolom van dat blok terug)

Een groot nadeel van de bovenstaande methode is, dat er voor de ontbrekende maanden een lege ruimte in de grafiek overblijft.
Dat kunnen we voorkomen door de grafiek dynamisch te maken met behulp van de functie Verschuiving.

Verschuiving

Voordat we deze functie voor grafieken gaan gebruiken eerst wat uitleg:
overal waar je in Excel een verwijzing naar een cel of een groep cellen (een cel-bereik of -range) gebruikt kun je ook VERSCHUIVING gebruiken.
De verwijzing zal dan niet naar de opgegeven cel zijn, maar naar 1 of meerdere cellen verschoven. DynGraf3Een voorbeeld:

  1. kies in het Voorbeeldbestand het tabblad DynGraf1
  2.  in cel H4 zetten we de formule =VERSCHUIVING(C4;2;0)
  3. het resultaat is 120; de functie geeft de inhoud van de cel 2 rijen onder en 0 kolommen rechts van C4 (dus C6)
  4. zo geeft de formule =VERSCHUIVING(C4;1;2) de waarde 110 (namelijk de inhoud van cel E5)
  5.  =VERSCHUIVING(E3;3;-2) werkt ook!! Resultaat is 120; de inhoud van cel C6.
  6. =VERSCHUIVING(D3;D1;0) en =VERSCHUIVING(D3;D1;1) leveren de maand en het bedrag afhankelijk van de waarde in cel D1
  7. Maar deze functie kent nog meer parameters (optioneel; zijn dus niet verplicht). We kunnen ook nog aangeven hoeveel rijen en kolommen het bereik vanuit de nieuwe positie moet hebben:
    =VERSCHUIVING(D3;1;1;3;1) geeft als resultaat de inhoud van de cellen E4:E6; namelijk, vanuit cel D3 startend, 1 rij naar beneden en 1 kolom naar rechts (dus E4) nemen we een bereik van 3 rijen hoog en 1 kolom breed.
    Wanneer we deze formule intikken in Excel, levert dit het resultaat 100; Excel laat de waarde van de eerste cel (E4 dus) zien.
    NB wil je weten wat Excel intern als resultaat van de functie vasthoudt:
    * klik in de formulebalk achter de betreffende functie en druk op F9
    * in plaats van de formule komt nu het resultaat: DynGraf4; dus wel degelijk de inhoud van drie cellen E4, E5 en E6
    * druk daarna niet op Enter maar op Esc
  8. maar als het resultaat van de vorige functie de inhoud van 3 cellen is, dan kunnen we natuurlijk ook nog het volgende doen:
    =SOM(VERSCHUIVING(D3;1;1;D1;1))
    Het resultaat is de som van de bedragen van de maanden, waarbij cel D1 bepaalt hoeveel maanden er worden meegenomen.

 Complexere (maar mooiere) oplossing

DynGraf5Nu we met Verschuiving overweg kunnen, kunnen we die voor onze dynamische grafiek gebruiken:

  1. kies opnieuw in het Voorbeeldbestand het tabblad DynGraf1
  2. scrol naar beneden zodat rij 70 in beeld is
  3. hier staat nog een grafiek die afhankelijk is van de waarde in cel D1; maar deze heeft geen “lege” maanden
  4. voordat je een dergelijke grafiek kunt maken, moeten binnen Excel enkele nieuwe namen gedefinieerd worden, die voor de x- en y-waarden worden gebruikt:
    * ga naar de menu-tab Formules
    * kies binnen het blok Gedefinieerde namen de optie Namen beheren
    * kies de optie NieuwDynGraf6, bij Naam tikken we in Maand en Verwijst naar wordt =verschuiving($A$49;0;0;$D$1;1)
    * klik OK; in het overzicht van namen is onze nieuwe naam Maand opgenomen, waarbij Excel aan de cellen A49 en D1 automatisch de naam van het betreffende tabblad en een ! heeft toegevoegd.
    LET OP maak verwijzingen naar cellen ALTIJD absoluut (met de $-tekens) anders zal de naam relatief zijn t.o.v. de cel, die op het moment van aanmaken geselecteerd is
  5. op dezelfde manier maken we een nieuwe naam Bedrag: =verschuiving($B$49;0;0;$D$1;1) of
    =verschuiving($A$49;0;1;$D$1;1)
  6. bij het maken van de grafiek geven we als Aslabelbereik =DynGraf1!Maand op en als Reekswaarden de naam =DynGraf1!Bedrag.
    LET OP begin met het =-teken en laat de hierboven gedefinieerde naam voorafgaan door de naam van het tabblad en een !
  7. Verander de waarde in D1 en bekijk het resultaat

NB wat gebeurt er als je in D1 bijvoorbeeld 15 intikt?

NB2 zet in D1 de formule =AANTAL(B49:B60) en maak cel B60 leeg

Nog complexere (en nog mooiere) oplossingen

DynGraf7In het Voorbeeldbestand heb ik nog 2 alternatieve oplossingen opgenomen.

In het eerste voorbeeld kun je ook de beginmaand opnemen, zodat de grafiek niet altijd met januari hoeft te beginnen, maar je bijvoorbeeld alleen de resultaten van het lopende kwartaal kunt laten zien.

Open het tabblad DynGraf2 en bekijk de opbouw van de sheet en de grafiek.
In dit tabblad worden de namen Maand2, Bedrag2 en Totaal gebruikt. De laatste is toegewezen aan het tekstvak, dat over de grafiek ligt.

DynGraf8In het laatste voorbeeld (tabblad DynGraf3) kunnen de grenzen met behulp van zogenaamde Schuifbalken worden ingesteld.

Om deze in een sheet te plaatsen kies je in de menu-tab Ontwikkelaars in het blok Besturingselementen de optie Invoegen. Klik dan binnen het blok Formulierbesturingselementen de optie Schuifbalk; geef met de cursor in de sheet aan waar de balk moet komen. DynGraf9Daarna kan na rechtsklikken op de schuifbalk het besturingselement opgemaakt worden.

Zoals te zien is kunnen hier de minimale en maximale waarden worden vastgelegd.  Van groot belang is de optie Koppeling met cel.


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