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:
- selecteer cel E6
- klik in de formulebalk achter de formule
- druk op het toetsenbord op de functietoets F9 (Berekenen)
- de formule verandert in: ={0;0;0;1;0;0;1;0;0;0;0}
- 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:
- Chandoo: uitleg van diverse ‘interessante’ formules
- How to Excel: een overzicht van diverse Excel-websites