Categorie archief: Excel

Teksten samenvoegen

28 maart 2015Excel, VBA (Visual Basic for Applications)Datum, eigen functie, Namen, Tekst, Tekst.SamenvoegenGijs Verbruggen

Het is weer tijd voor het doen van aangifte voor de belastingen. Dan is het altijd handig om Excel achter de hand te hebben om bedragen op te tellen etc.
Maar wist u, dat Excel ook teksten kan ‘optellen’? Nou ja, beter gezegd samenvoegen.

Het komt in Excel nogal eens voor, dat de inhoud van verschillende cellen moet worden samengevoegd.
Zeker wanneer de gegevens uit een ander systeem of uit een Pdf-bestand worden overgenomen, komen bijvoorbeeld dubbele voor- en achternamen vaak in verschillende cellen terecht.

TEKST.SAMENVOEGEN

In het Voorbeeldbestand staat op het werkblad Namen een klein lijstje namen. Al snel is te zien dat de regels 4, 6 en 7 nog aanpassing nodig hebben.

Gelukkig heeft Excel een ingebouwde functie voor het samenvoegen van teksten: TEKST.SAMENVOEGEN (klinkt logisch, toch?).

Op basis van de geïmporteerde namen maken we als volgt een nieuw overzicht:

iets onder het bestaande overzicht (in het voorbeeld in cel B10) verwijzen we naar de eerste cel van de kop door in te tikken =B2
deze formule kopiëren we één cel naar rechts en net zo vaak naar beneden als er regels zijn in het oorspronkelijke bestand. Gebruik hiervoor de vulgreep van cel B10; zie het artikel Doorvoeren en sorteren.
de meeste cellen zijn natuurlijk meteen goed, maar bijvoorbeeld de tweede naam kan nog wel een aanpassing gebruiken. In de betreffende cel (in het voorbeeld B11) moeten de 2 voornamen gecombineerd worden: =TEKST.SAMENVOEGEN(B4;C4)
het resultaat is niet precies wat we willen; er moet nog een spatie tussen de 2 voornamen: =TEKST.SAMENVOEGEN(B4;” “;C4)
deze formule kopiëren we naar alle cellen waar namen moeten worden samengevoegd (in het voorbeeld B13, B14 en C14)
maar nu kloppen de achternamen van Marie Jose nog niet; de formule voegt de verkeerde cellen samen. Dit lossen we snel op:
* selecteer de betreffende cel (in dit geval C13)
* klik in de formulebalk of druk op F2 om de formule aan te passen
* de cellen, die in de formule gebruikt worden hebben dan een gekleurde rand, overeenkomend met de celverwijzing in de formule:
* ‘pak’ met de muis de groene rand vast en sleep 1 cel naar rechts
* idem met de blauwe rand; door dit slepen wordt de formule automatisch aangepast
de achternaam van de tweede persoon is nog niet juist; de celverwijzing dient nog aangepast te worden.

Is het bestand veel groter, dan moet per regel bekeken worden welke formule waar moet komen; het handigste gaat dit door de 2 cellen van de tweede naam te Kopiëren en overal waar dezelfde situatie zich voordoet te Plakken. Idem voor de vierde en de vijfde naam (dan hebben we alle mogelijkheden gehad).

Alternatieve samenvoeging

Waar we in Excel een optelling van getallen regelen via het +-teken, kunnen we ook teksten ‘optellen’. Alleen wordt daar een ander teken voor gebruikt: & (ampersand; zie het interessante artikel op Wikipedia).

Een alternatieve (simpelere) manier om de naam Jan Willem te maken krijgen we dan als volgt: =B4&” “&C4

Zie verder het Voorbeeldbestand.

Flexibele overzichten

Tekst-samenvoegingen kunnen goed gebruikt worden om rapportages/overzichten flexibeler te maken.
Hoe vaak gebeurt het niet dat je een overzicht van een vorig jaar of vorige maand kopieert en vergeet om alle ‘harde’ jaar- en maand-aanduiding aan te passen.

Het is dan ook een goede gewoonte om harde gegevens (vaak parameters genoemd) apart vast te leggen en daar in formules naar te verwijzen.

In het Voorbeeldbestand op het werkblad Parameters is dit in het kort uitgewerkt:

leg het jaar vast in cel C2 en de maand in C3
typ de volgende formule in cel B7:
=”Overzicht van “&C3&” “&C2
(denk aan de spaties op de juiste plaatsen!)
verander een input-variabele en bekijk het resultaat.

In plaats van 2 variabelen kunnen we ook eenzelfde resultaat bereiken door uit te gaan van één datum (cel C4).
In cel F7 voeren we de volgende formule in: =”Overzicht van “&C4
Helaas, het resultaat is niet precies wat we verwachten! Excel “vergeet” nu dat we te maken hebben met een datum. We moeten zelf nog wat denkwerk verrichten: =”Overzicht van “&TEKST(C4;”mmmm jjjj”). Door opmaak aan de broncel mee te geven, krijgen we wel het gehoopte resultaat.

NB1 omdat we weten, dat cel C4 een datum voorstelt, geven we die cel met behulp van de functie TEKST een datum-opmaak; ‘speel’ met de notatie-instellingen: het aantal m‘s, zet er ook eens een d in etc.

NB2 met dezelfde functie TEKST kan ook de opmaak van een getal worden aangepast. De opmaak volgt dezelfde notatie als bij Celeigenschappen.

De formules in de Excel-sheet worden beter leesbaar wanneer de cellen met parameters een Naam krijgen. Geef je aan cel C2 de naam Jaar mee en aan C3 de naam Maand dan wordt de formule in B7 =”Overzicht van “&Maand&” “&Jaar
Zeker wanneer verwijzingen zich op andere werkbladen bevinden is een dergelijke formule veel duidelijker.

In het kader van flexibiliteit: om bij een volgende maand niet allerlei wijzigingen te hoeven doorvoeren, moeten de datums natuurlijk ook afhankelijk zijn van een parameter:
via de functies DATUM, JAAR en MAAND wordt gebruik gemaakt van de waarde in cel C4.
NB met DATUM zijn allerlei mogelijkheden denkbaar. Voorbeeld: wil je de laatste dag hebben van de maand, waarin de datum in C4 valt?
=DATUM(JAAR(C4);MAAND(C4)+1;1)-1
ofwel maak een datum door het jaar van C4 te nemen, de maand van C4 plus 1 (de volgende maand dus) en de eerste dag. Door dan van het resultaat 1 af te trekken weten we zeker dat we op de laatste van de betreffende maand uitkomen!

Eigen functies

In het eerste voorbeeld waren we al even bezig om alle namen goed te krijgen, ook al betrof het maar een bestandje met enkele regels.
Zeker wanneer het meer regels betreft en met meer variaties dan wordt het netjes samenvoegen van teksten een behoorlijk karwei.

In het Voorbeeldbestand heb ik in het tabblad Tennis een overzicht opgenomen van een tenniscompetitie, zoals die binnen is gehaald vanuit een PDF. Zoals te zien is moet er flink ‘gepoetst’ worden. Voor dit soort werk heb ik een eigen TekstSamenvoeg-functie geschreven. Laten we die eerst aan het werk zetten:

voeg op de plaats van kolom C een nieuwe kolom in
in de nieuwe cel C3 plaatsen we de volgende functie:
=TekstSamenvoegenRange(D3:E3)
kopieer cel C3 naar beneden (dubbelklik op de vulgreep van cel C3)
ook nu klopt nog niet alles: in regel 7 moeten 3 cellen samengevoegd worden. Daar moet de formule dus worden: =TekstSamenvoegenRange(D7:F7)
kopieer deze cel naar alle regels waar drie cellen moeten worden samengevoegd
selecteer cel D2, ‘pak’ de rand vast en sleep naar C2
selecteer de hele kolom C (klik op de C boven de kolom)
kies kopiëren en daarna direct Plakken/waarden; op deze manier is de inhoud van kolom C ‘hard’ gemaakt
voeg op de plaats van kolom D een nieuwe kolom in
in de nieuwe cel D3 plaatsen we de volgende functie:
=TekstSamenvoegenRange(G3:I3)
kopieer cel D3 naar beneden (dubbelklik op de vulgreep van cel D3)
op verschillende plaatsen klopt het nu niet, bijvoorbeeld in regel 4. Selecteer cel D4 en druk op de functietoets F2. De cellen G4 t/m I4 hebben nu een blauwe rand. Pak de hoekpunt linksonder en sleep deze één cel naar rechts en druk op Enter.
pas op dezelfde manier de overige formules aan, waar dat nodig is.
sleep het kopje uit cel F2 naar D2
maak kolom D hard en verwijder alle kolommen E t/m J

De VBA-routine van deze functie is te vinden in Module1 van het Voorbeeldbestand. Druk daartoe op Alt-F11 (dus de Alt-toets inhouden en op de functietoets F11 drukken). De commentaarregels (groene tekst) lichten de bedoeling van de functie en de diverse stappen toe.

De functie kent een optionele parameter, de KoppelTekst (die hoeft dus niet ingevuld te worden; in dat geval wordt hier dan een spatie gekozen).
wanneer we in cel C3 hadden ingevoerd =TekstSamenvoegenRange(D3:E3;” “) dan was het resultaat dus hetzelfde geweest (denk aan de aanhalingstekens om de spatie!). Maar het had ook =TekstSamenvoegenRange(D3:E3;” | “) kunnen zijn etc.
Door =TekstSamenvoegenRange(D3:E3;TEKEN(10)) worden de elementen gescheiden door een ‘harde-return’ (ofwel nieuwe-regel-opdracht). Vink bij Celeigenschappen/Uitlijning dan wel de optie Terugloop aan.

Ik heb nog een andere functie toegevoegd: TekstSamenvoegenTeller.
Kies opnieuw het tabblad Tennis van het originele Voorbeeldbestand en voer het volgende uit:

voeg op de plaats van kolom C TWEE nieuwe kolommen in
in cel D3 tikken we het getal 2 in; in de nieuwe cel C3 plaatsen we de volgende functie:
=TekstSamenvoegenTeller(E3;D3)
kopieer de cellen C3 en D3 naar beneden
in sommige cellen zal de waarde 2 gewijzigd moeten worden in 3

Voetbal en gegevensvalidatie

27 februari 2015ExcelGegevensvalidatie, Grafiek, Grootste, Index, Indirect, Namen, Vergelijken, voorwaardelijke opmaakGijs Verbruggen

Hoewel de titel van dit artikel misschien anders doet vermoeden (het gaat dus NIET over het checken van persoonsbewijzen bij een voetbalwedstrijd), gaan we het hebben over het valideren van de invoer van gegevens in een cel in Excel.

De aanleiding hiervoor was een discussie op een internet-forum over “Lege velden in keuzelijst met invoervak“.
Al snel kwam daar de opmerking langs, dat het onderliggende probleem waarschijnlijk makkelijker en beter met gegevensvalidatie kon worden opgelost; dan was er ook geen VBA nodig.
Het probleem in het kort: de toegestane invoer in een cel is afhankelijk van de keuze, die in een andere cel is gemaakt.

Om de daar geschetste oplossing in de praktijk te kunnen laten zien, heb ik de stand van de twee Nederlandse profvoetbalcompetities genomen (Bron: www.voetbaluitslagen.nl).
NB Het valt me nu pas op hoe goed de stad Eindhoven het doet!

Stand voetbalcompetities

Dus eerst maar eens een voorbeeld opzetten (zie Voorbeeldbestand):

eerst de standen van de twee competities onder elkaar gezet, gesorteerd op clubnaam, waarbij de kolom met de punten (Pnt.) voor ons het belangrijkste is
dan een schema opzetten van de beste 3 per competitie (zie hierboven).
Daarbij maak ik gebruik van de functie Grootste:
=GROOTSTE(H15:H32;1)
Dit levert het maximum op van de Eredivisiepunten; in het Voorbeeldbestand, tabblad CompOvz1, heb ik deze reeks een naam gegeven, ErePnt, zodat de bedoeling van de formule duidelijker is.
Door de 1 te veranderen in 2 vinden we het één na hoogste puntentotaal enz.
Via Index en Vergelijken (zie het artikel over Verticaal zoeken) vinden we de bijbehorende clubs.
NB zoals uit de stand hierboven mag blijken gaat het bij een gelijk aantal punten niet altijd goed; er wordt geen rekening gehouden met doelsaldo.
dan nog wat Voorwaardelijke opmaak ‘strooien’ over het overzicht (zie mijn vorige blog) en we krijgen wat beter inzicht in de voetbal-verhoudingen in Nederland.
NB we zien nu wel dat AZ en Feyenoord een gelijk aantal punten hebben
de verhoudingen tussen de clubs kunnen ook grafisch worden weergegeven.
Op de x-as staan de gegevens van de kolommen B en C; Excel zorgt zelf voor een duidelijke lay-out wat betreft de indeling van de twee competities.
De titel is dynamisch, wat in dit geval inhoudt dat deze verandert wanneer de datum in cel B2 wordt gewijzigd:
* maak een willekeurig titel aan,
* klik ergens in de titel,
* kies de formulebalk en tik een verwijzing naar de cel met de gewenste titel-tekst in, inclusief de naam van het tabblad en een !
In het voorbeeld staat de titel-tekst in cel N27, waar met behulp van de functie Teken een scheiding tussen de twee elementen van de titel is gemaakt.

Combinatie van grafieken

De grafiek kan wel wat duidelijker: een betere scheiding tussen de twee competities, wie staan bovenaan, hoe staat mijn favoriete club er voor?

in het Voorbeeldbestand, op het tabblad CompOvz2, is het onderscheid tussen de competities geregeld door de behaalde punten in 2 verschillende kolommen te plaatsen, een extra kolomgrafiek toe te voegen, de overlap van de grafieken op 100% te zetten en de kleuren aan te passen
daarna is er een label toegevoegd aan de nummers 1 tot en met 3: achter iedere club (in kolom F) staat een formule, die kijkt of de club bij de eerste 3 hoort:
=ALS(D15=Ere_1;1;ALS(D15=Ere_2;2;ALS(D15=Ere_3;3;””)))
Ere_1 is de naam van de cel, die het aantal punten van de aanvoerder van de ranglijst bevat etc.
Het bereik F15:F52 is als een nieuwe grafiek toegevoegd; bij Opmaak is gekozen voor Geen opvulling maar wel zijn Gegevenslabels toegevoegd
om onze favoriete club er te laten uitspringen, voegen we een nieuwe grafiek toe, die alleen de punten van deze club bevat; zie kolom G:
=ALS(C15=Voorkeur;D15+E15;””)
Geef deze grafiek een afwijkende kleur.

Gegevensvalidatie

In het vorige voorbeeld staat de naam van de favoriete club in cel J4, die de naam Voorkeur heeft gekregen.
De (groene) kolom in de grafiek bij de favoriet wordt natuurlijk alleen maar zichtbaar, wanneer die cel een bestaande clubnaam bevat. In Excel dwingen we dat af via Gegevensvalidatie.

selecteer cel J4 in het tabblad CompOvz2 van het Voorbeeldbestand
kies binnen de menutab Gegevens in het blok Hulpmiddelen voor gegevens de optie Gegevensvalidatie
er opent zich een keuzescherm:
bij Toestaan kiezen we de optie Lijst
en als Bron geven we het bereik op, waar alle clubnamen staan; in het voorbeeld heeft dat bereik de naam Teams.
LET OP: denk aan het =-teken

Selecteer J4 en tik een clubnaam in; komt deze niet (precies) in de clublijst voor dan krijgt u een foutmelding. Het is daarom handiger om het ‘vinkje’ achter de cel te gebruiken, zodat u de keuzelijst kunt gebruiken.

‘Meervoudige’ keuzelijst

In het vorige voorbeeld bestaat de lijst, waaruit een favoriete club gekozen kan worden, uit 38 teams.
Via scrollen in de zijbalk van de keuzelijst is de gewenste club nog vrij snel te vinden. Bij langere lijsten is dat vaak onhandig.

Als voorbeeld zou het in dit geval makkelijker zijn om eerst een competitie te kiezen (Eredivisie of Jupiler) en daarna pas een club uit de gekozen competitie.
Dat is wat ik bedoel met ‘meervoudige’ keuzelijst: de inhoud van de tweede wordt bepaald door de keuze in de eerste.

Op de volgende manier is dit in te regelen (zie het Voorbeeldbestand, tabblad CompOvz3):

voor de competitie-keuze voegen we weer een gegevensvalidatie toe (aan cel J3): kies binnen de menutab Gegevens in het blok Hulpmiddelen voor gegevens de optie Gegevensvalidatie
bij Toestaan kiezen we opnieuw de optie Lijst
bij Bron wordt meestal een celbereik van toegestane elementen opgegeven.
In dit geval hebben we maar 2 mogelijkheden: Eredivisie of Jupiler. De namen van de betreffende clubs liggen vast in de celbereiken EreTeams, respectievelijk JupTeams. Deze 2 namen geven we als bron van de lijst op, gescheiden door een ; (punt-komma).
aan cel J4 geven we weer een gegevensvalidatie.
Bij Toestaan kiezen we weer Lijst; bij Bron zijn we geneigd om de cel J3 mee te geven, maar helaas: Excel zal dan alleen de letterlijke tekst uit die cel aan de keuzelijst meegeven (dus EreTeams of JupTeams).
Nee, bij Bron moeten we invullen
=INDIRECT(J3)
De functie Indirect zorgt er voor, dat niet de inhoud van J3 zelf wordt gebruikt, maar de inhoud van het bereik waar J3 naar verwijst (EreTeams of JupTeams dus).

Voorwaardelijke opmaak

12 februari 2015ExcelCeleigenschappen, Opmaak, voorwaardelijke opmaakGijs Verbruggen

Het zuiden van Nederland maakt zich op voor Carnaval.

Waar de schmink bij Carnaval meer bedoeld is om het echte gezicht te verbergen of te versieren, is de opmaak bij Excel bedoeld om zaken te benadrukken, duidelijker te maken.

Zoals in eerdere artikelen beloofd, gaan we het deze keer hebben over Voorwaardelijke opmaak.
Iedereen maakt er wel eens gebruik van; misschien zonder het zelf te weten!
Er zijn allerlei vormen: van het simpel tussenvoegen van een punt, wanneer getallen groter zijn dan 1.000 tot het tonen van grafische tekens in een cel afhankelijk van andere gegevens in de Excel-werkmap.

Celeigenschappen/opmaak

Bij celeigenschappen vinden we de meest gebruikte opmaak-opties van Excel. We beperken ons deze keer tot de opmaak van cellen met numerieke waarden.

Hebt u dat ook met grote getallen: hebben we het nou over tonnen of over miljoenen?
Zie Voorbeeldbestand

Excel biedt ons een snelle mogelijkheid om deze getallen beter leesbaar te maken:

selecteer alle getallen die een nieuwe opmaak moeten krijgen
kies Start in de menubalk en binnen het blok Getal, de optie Duizendtalnotatie (de 000)
met de 2 knoppen rechts daarvan kun je meer of minder decimalen weergeven

NB de inhoud van de cellen verandert niet, slechts de weergave.

Moeten de negatieve getallen een andere kleur krijgen (meestal kiezen we daarvoor rood):

selecteer alle cellen die deze opmaak moeten krijgen
klik met de rechtermuisknop op de selectie en kies Celeigenschappen; dit kan ook sneller mbv Ctrl-1 (Ctrl vasthouden en de 1 indrukken)
kies het tabblad Getal en bij de Categorie weer Getal
kies geen decimalen, wel scheiding voor duizendtallen en kleur

Naast de standaard-opmaak die in Excel is ingebouwd, kunnen we de opmaak ook nog voor een groot gedeelte zelf bepalen.
Daartoe moeten we weer even terug naar de Celeigenschappen, het tabblad Getal. Wanneer we bij Categorie de optie Aangepast kiezen, laat Excel bij Type de code zien die gebruikt is voor de opmaak, bijvoorbeeld:
In dit geval bestaat de code uit 2 secties (gescheiden door een punt-komma); het eerste geeft aan hoe positieve getallen moeten worden weergegeven, de tweede geldt voor negatieve getallen. Zonder op alle details in te gaan, lijkt me de hoofdstrekking van de diverse codes duidelijk.

NB pas de opmaak via een van de standaardopties aan en bekijk bij Aangepast welke codes daarvoor gebruikt worden.

Excel kent in principe 4 opmaak-secties: voor positieve getallen, negatieve, voor de nul-waarde en voor tekst. Maar we mogen ook eigen secties maken; bijvoorbeeld als we alle getallen groter dan 100 blauw willen maken, gebruiken we in een sectie [Blauw][>100]#.##0

Deze laatste handigheid kan mooi toegepast worden bij grafieken, aangezien Celopmaak ook gebruikt kan worden bij het opmaken van de assen. Op deze manier kunnen bepaalde gebieden op een as een aparte kleur krijgen.

NB om nog meer mogelijkheden te ontdekken: bekijk alle opmaak in het tabblad CelOpmaak van het Voorbeeldbestand.

Opmaak afhankelijk van inhoud cel

Heel vaak kan het handig zijn om de opmaak van een cel afhankelijk te maken van de inhoud daarvan: onderscheid maken tussen positieve en negatieve waarden, analyse van nauwelijks zichtbare verschillen (of andersom wanneer zijn 2 waardes gelijk), controlegetal moet 0 (nul) zijn etc.

Dit is met Celeigenschappen niet of slechts moeizaam te regelen, waarbij de opmaak beperkt blijft tot het kleuren van de tekst.
Met de optie Voorwaardelijke opmaak komen we een stuk verder.

Laten we als voorbeeld de aan- en afmeldingen eens bekijken van een sportschool (zie Voorbeeldbestand, tabblad CelVoorw). Hiernaast is te zien, dat er in januari meer leden zijn vertrokken dan er zijn aangemeld. Om snel inzicht te krijgen in het maandresultaat hebben de cellen in kolom E een opmaak gekregen, die afhangt van de inhoud: voorwaardelijke opmaak dus.
Hoe doen we dat:

selecteer alle cellen in kolom E die de opmaak moeten krijgen
kies Start in de menubalk en binnen het blok Stijlen de optie Voorwaardelijke opmaak
in het submenu op Nieuwe regel klikken
neem als type Alleen cellen opmaken met
en zorg dat onderstaande regel wordt aangemaakt
kies een daarbij passende opmaak, bijvoorbeeld groene opvulling

Aanpassen voorwaardelijke opmaak
We zouden op dezelfde manier ook een rode kleur kunnen toevoegen aan cellen met een negatieve waarde, maar dat gaan we anders doen:

selecteer een cel in kolom E
kies Start in de menubalk en binnen het blok Stijlen de optie Voorwaardelijke opmaak
in het submenu op Regels beheren klikken
Excel is zo slim om te zien, dat we de huidige selectie willen beheren. Duidelijk is te zien wat onze eerste regel doet en op welke cellen deze betrekking heeft. De voorwaarde en de opmaak zijn te veranderen door Regel bewerken te kiezen.
we voegen een regel toe via Nieuwe regel. Zorg dat de cel een rode achtergrond krijgt als de waarde kleiner is dan nul.
vergeet niet deze regel van toepassing te laten zijn op alle relevante cellen in kolom E

NB druk op de functietoets F9 om de sheet opnieuw door te rekenen; aangezien de aan- en afmeldingen aselect gekozen worden, zal het resultaat ook iedere keer anders zijn.

Andere voorbeelden
In de kolommen G t/m J worden twee resultaten vergeleken. Als de waarde in kolom H gelijk is aan de waarde in I, dan zorgt de formule in kolom J ( in J3 staat bijvoorbeeld =H3=I3) er voor, dat het resultaat WAAR verschijnt (zo niet, dan ONWAAR). Via voorwaardelijke opmaak is in één oogopslag te zien waar de kolommen gelijke waardes bevatten (eventueel even F9 drukken voor nieuwe gegevens).

In cel O3 is een verbandcontrole ingebouwd: is het resultaat nul, dan kleurt de cel groen, anders rood (probeer maar eens uit als ergens in kolom O een resultaat wordt gewist of, wat in de praktijk nog wel eens wil gebeuren, als de formule in O door een harde waarde wordt overschreven.

Opmaak afhankelijk van inhoud van een andere cel

“Maar, wat als ….”
Een bekende frase in automatiseringsland: iedere keer moeten er weer grenzen verlegd worden.

In het Voorbeeldbestand heb ik een tabblad Energie opgenomen, waarop per dag de meterstanden worden genoteerd. Excel berekent voor ons dan het dagverbruik.
Bovenaan berekenen we het lopende gemiddelde voor het verbruik van stroom en gas, zodat we in ieder geval kunnen zien of we onder of boven het gemiddelde scoren.
Met behulp van Voorwaardelijke opmaak willen we dat natuurlijk direct laten zien; eerst voor de stroom:

selecteer alle relevante cellen in kolom F (in dit geval F7:F75)
kies Start in de menubalk en binnen het blok Stijlen de optie Voorwaardelijke opmaak en dan weer Nieuwe regel
als type kunnen we niet meer de tweede optie gebruiken; de voorwaarde hangt ook af van de inhoud van een andere cel. We moeten een formule gaan gebruiken, dus kiezen de laatste optie.
tik dan als formule in: =F7<$F$3, kies een bijpassende Opmaak (tekst groen bijvoorbeeld; we zitten onder het gemiddelde) en klik OK
NB een formule moet altijd met het =-teken beginnen, daarachter volgt de voorwaarde: F7<$F$3.
F7 is een relatieve verwijzing, $F$3 een absolute; dat betekent dat als Excel deze voorwaarde één cel lager toepast hij deze interpreteert als F8<$F$3 etc.
op dezelfde manier kunnen we een kleur geven aan het verbruik dat hoger uitkomt dan gemiddeld

Kijk in het Voorbeeldbestand hoe de overige opmaak is geregeld door Regels beheren op te roepen.

Nog een ander voorbeeld
Een klas heeft 3 proefwerken gehad en we zien direct wie gemiddeld lager dan 5,5 staat.

Ook hier is gebruik gemaakt van voorwaardelijke opmaak: op de cellen B4:F11 is de volgende formule als Nieuwe Regel ingevoerd:
=$F4<5,5
In de formule is de kolomnaam F absoluut, want voor alle cellen in B4:F11 kijken we voor de voorwaarde naar kolom F, maar de rijnaam 4 is relatief; dus in de cellen B5:F5 zal Excel beoordelen of F5 kleiner is dan 5,5 etc.

Aantallen turven

26 januari 2015Access, ExcelAantal, Aantal.als, Aantalarg, Array-formule, Besturingselement, Gekoppelde afbeelding, Herhaling, Integer, Kopiëren, Namen, Plakken speciaal, Rest, voorwaardelijke opmaakGijs Verbruggen

De afgelopen weken kreeg ik in diverse situaties vragen over het tellen van aantallen.

Wat simpel lijkt, wil in geautomatiseerde systemen nog wel eens ingewikkelder uitpakken.
Of het nu het tellen is van het aantal regels in een Excel-bestand, het aantal lege velden in een Excel-kolom, het aantal records in een Access-tabel of het aantal niet-lege namen in een Access-record: het blijft goed opletten wat en hoe er geteld moet worden.

Aantallen turven:
toen ik dit intikte, gingen mijn gedachten weer terug naar de vele gezellige avonden, die we lang geleden met een groepje hebben doorgebracht met het kaartspel Toepen. Bij dit spel worden de strafpunten op een bier- (of sinas-?)viltje geturfd door het zetten van streepjes.

Voor de liefhebbers volgt hieronder, na wat uitleg over het turven van aantallen in Excel en Access, een “geautomatiseerd bierviltje”.
Ik heb daarbij ook een weinig bekende manier van kopiëren gebruikt.

Excel

In het Voorbeeldbestand staat in het tabblad Aantal vanaf cel D15 een overzicht van de omzet per maand en regio van een kleine witgoedhandel (ze verkopen alleen stofzuigers, strijkijzers en broodroosters).

Uiteraard willen we de totale omzet weten (dat lossen we simpel op via een Som-formule); maar vandaag zijn we meer geïnteresseerd in Aantallen.

Excel kent diverse standaard-functies om aantallen te berekenen.
De meest simpele (en meest gebruikte) functie is Aantal. Maar als we die toepassen op het bereik D15:D158 (de maanden) dan krijgen we als resultaat 0; idem voor Regio en Product. Pas het turven van aantallen in de kolom Bedrag levert een aantal groter dan nul op, namelijk 142. De functie Aantal telt alleen maar de inhoud van numerieke cellen!

NB een datum is voor Excel ook een getal; dus de functie Aantal turft ook datums (zie ook de cellen L18:M21 in het Voorbeeldbestand).

De functie Aantalarg voldoet al beter aan de verwachtingen (zie het overzicht hierboven): deze functie telt hoeveel cellen er gevuld zijn, onafhankelijk van de inhoud.

Mbv de functie Aantal.lege.cellen (denk aan de puntjes!) kunnen we analyseren of en hoeveel lege cellen er in een cellenbereik voorkomen.

Voor de volledigheid heb ik ook nog enkele voorbeelden van de functies Aantal.als en Aantallen.als opgenomen; zie voor nadere uitleg het artikel Tellen met voorwaarden.

Turf-alternatief

Om snel te kunnen achterhalen in welke regel er gegevens ontbreken, heb ik achter iedere regel, in kolom H, mbv de functie Aantalarg geteld hoeveel velden er gevuld zijn.
Door cellen, die NIET 4 opleveren, via Voorwaardelijke opmaak te kleuren zien we direct in welke regels het fout gaat
(in één van de komende artikelen zal ik op Voorwaardelijke opmaak terugkomen).

Voor de liefhebbers nog een andere manier van tellen waarbij gebruik wordt gemaakt van zogenaamde Array- of Matrix-formules.
Wanneer we in Excel in een cel intypen =D15<>”” (we kijken dus of cel D15 NIET leeg is) dan levert dit de waarde WAAR op (als die cel gevuld is tenminste!). Maar intern vertaalt Excel dit naar een 1 (en ONWAAR naar een 0; het blijft een computerprogramma!). Daar kunnen we gebruik van maken:
typ in een cel =1*(D15<>””) en we krijgen als resultaat 1.
NB denk aan de haakjes rond de voorwaarde.

We zouden in cel I15 kunnen intikken =1*(D15<>””)+1*(E15<>””)+1*(F15<>””)+1*(G15<>””) om te achterhalen hoeveel cellen er niet leeg zijn, maar dat kan makkelijker:

tik in cel I15 in =Som(1*(
selecteer de cellen D15 t/m G15
en tik dan in (allemaal in cel I15) <>””))
niet op Enter drukken, maar Ctrl-Shift-Enter

Excel zal nu voor alle cellen in het bereik D15:G15 beoordelen of die leeg is; vermenigvuldigd met 1 levert dit een serie 0’en en 1’en op, die door de functie Som bij elkaar worden opgeteld.

Door de combinatie Ctrl-Shift-Enter wordt er een Array- of Matrix-formule ingevoerd; in de formulebalk wordt dit weergegeven door {} rond de formule.

NB alleen op bovenstaande manier kun je een array-formule invoeren; het plaatsen van accolades rond een formule werkt niet!

Kopieer de array-formule naar beneden (via de vulgreep rechtsonder in cel I15 natuurlijk; zie het artikel Doorvoeren en sorteren).

Access

Binnen dit programma moeten we het doen met veel minder (functie-)mogelijkheden. Voor het tellen van het aantal records in een tabel kunnen we een Totaal-query maken en dan per veld in de regel Totaal: aangeven dat we het Aantal willen zien.
Het resultaat hiervan komt overeen met het gebruik van de functie Aantalarg in Excel; dus het aantal niet-lege velden wordt geturfd voor ieder type veld.

Willen we het aantal niet-lege velden binnen een record weten (net als de berekening in de cellen H15 en I15 hiervoor in Excel) dan zullen we zelf een formule moeten intypen in de query.

Optie 1:
Gevuld: IIf([Maand]<>””;1;0) + IIf([Regio]<>””;1;0) + IIf([Product]<>””;1;0) + IIf([Bedrag]<>0;1;0)

De eerste als-voorwaarde (in Access Iif) levert een 1 als Maand niet leeg is, anders 0; de tweede voorwaarde hetzelfde voor Regio enz. Door de 4 resultaten van de Iif’s op te tellen zien we per record hoeveel velden er gevuld zijn.

NB Bedrag is in Access een numeriek veld; de inhoud kunnen we dan niet vergelijken met een lege tekst, dus we controleren of de waarde niet gelijk is aan 0.

Optie 2:
Gevuld2: IIf(Not IsNull([Maand]);1;0) + IIf(Not IsNull([Regio]);1;0) + IIf(Not IsNull([Product]);1;0) + IIf(Not IsNull([Bedrag]);1;0)

Vergelijkbaar met Optie 1, maar we controleren mbv een Access-functie IsNull of een veld leeg is.

Optie 3:
Gevuld3: -(Not IsNull([Maand])) – (Not IsNull([Regio])) – (Not IsNull([Product])) – (Not IsNull([Bedrag]))

Dit is iets korter dan optie 2, waarbij we gebruik maken van de eigenschap dat de waarde WAAR in Access door -1 wordt voorgesteld (en ONWAAR door 0; dit laatste is WEL hetzelfde als in Excel!?).

NB denk aan de juiste plaats en het aantal van de haakjes.

Toepen

Zoals beloofd een “Excel-bierviltje”; zie het Voorbeeldbestand.
De (straf)puntentelling bij het kaartspel Toepen gebeurt van oudsher door het zetten van streepjes, waarbij een vijftal door een dwarsstreep wordt aangegeven. Heb je 15 strafpunten, dan lig je er uit; 14 punten is ook een bijzondere situatie: de tegenstanders krijgen bij verlies 2 strafpunten (of ze passen voor 1 punt). In het Brabants wordt dit “Pulleke” genoemd, op het “scorebord” aangeduid door een P.

In het tabblad ToepBasis worden alle benodigdheden voor het scorebord klaar gezet:

in het blok B2:C4 staan de gebruikte symbolen, waarbij de cellen C2 t/m C4 een overeenkomende naam hebben gekregen (zie het Naamvak, linksboven)
in de cellen B8:D12 staan de spelersnamen en hun scores: de stand binnen het lopende ‘potje’ en het aantal gewonnen ‘potjes’.
F6:H12 is het scorebord/Excel-bierviltje.
In kolom F worden de deelnemers gecombineerd met hun resultaten door cellen en teksten met behulp van het &-teken te koppelen; in cel F8 staat bijvoorbeeld =B8&” (“&C8&”/”&D8&”)”
kolom G is de meest interessante: hier wordt de score uit kolom C (de cellen C8:C12 hebben samen de naam Score gekregen) omgezet in de juiste streepjes:
=ALS(Score>=15;Vijftal&Vijftal&Vijftal;ALS(Score=14;Vijftal&Vijftal&Paal;HERHALING(Vijftal;INTEGER(Score/5))&HERHALING(Streepje;REST(Score;5))))
* als de Score groter of gelijk aan 15 is, dan wordt 3x de tekst uit de cel met de naam Vijftal geplaatst
* bij een Score van 14, twee vijftallen en het teken voor het “Pulleke”
* anders via de functie HERHALING zoveel vijftallen als we krijgen wanneer we Score door 5 delen (INTEGER betekent geheel getal) en zoveel streepjes als de REST is bij het delen van de Score door 5.
kolom H bevat een simpelere vorm van de formule uit kolom G
nog wat Voorwaardelijke opmaak toevoegen (‘kapot’: rode achtergrond, ‘Pulleke’: oranje achtergrond, meer dan 9 punten: oranje letters) en het Scorebord is klaar!

LET OP: de naam Score bevat de cellen C8 t/m C12. Wanneer we in kolommen rechts hiervan in een formule verwijzen naar Score, dan neemt Excel de met de rij overeenkomende waarde uit deze reeks. Dus in cel G8 wijst Score naar de waarde in cel C8, in G9 naar cel C9 etc.

Het “Excel-bierviltje” vinden we terug op het tabblad ToepScore.
Om het turven nog makkelijker te maken bevat het tabblad diverse Besturingselementen, een 10-tal zogenaamde Kringvelden . Hiermee is het mogelijk om de scores snel te verhogen en te verlagen. De Kringvelden zijn gekoppeld aan de diverse scores in het vorige tabblad; klik met de rechtermuisknop op een Kringveld en bekijk de instellingen onder de optie Besturingselement opmaken …
Kringvelden (en andere besturingselementen) vinden we onder de menu-tab Ontwikkelaars.

Het scoreformulier zelf is GEEN serie cellen maar een Excel-afbeelding; wanneer je er op klikt, zie je in de hoeken en in het midden van de randen plaatsen waar je met de cursor de afbeelding kunt vergroten en verkleinen.
En niet zo maar een statisch plaatje; nee, een zogenaamde Gekoppelde afbeelding. De inhoud van de afbeelding verandert met de inhoud van de cellen, waaraan deze gekoppeld is! Mooier kunnen we het niet maken….

Hoe maken we een gekoppelde afbeelding?

selecteer de cellen, die in de afbeelding moeten worden weergegeven (voor ons scorebord de cellen F6:H12 in het tabblad ToepBasis)
klik rechts in de selectie en kies Kopiëren
ga naar de cel waar de afbeelding moet komen, klik rechts en kies de optie Plakken speciaal ….
In het submenu kies je onder Andere plakopties de 4e mogelijkheid, Gekoppelde afbeelding
pas via de grepen aan de zijkant van de afbeelding de grootte aan en/of verplaats het plaatje naar de gewenste locatie.

NB Gekoppelde afbeeldingen kunnen een belangrijke rol spelen in rapportages. Wanneer je daar meerdere overzichten onder elkaar wilt weergeven, zal het meestal zo zijn, dat de gewenste kolom-breedtes van die overzichten niet met elkaar overeenkomen; maar Excel kan standaard maar één breedte weergeven.
Zet de overzichten naast elkaar op een tabblad (of nog beter op verschillende tabbladen) en maak van deze overzichten Gekoppelde afbeeldingen; deze kun je dan onder elkaar zetten, zonder dat de kolombreedtes elkaar nog beïnvloeden.

LET OP Gekoppelde afbeeldingen vergen veel van Excel en van je PC! Sla je werk regelmatig op en gebruik niet teveel dynamische afbeeldingen in één werkmap.

Kalender 2015

19 januari 2015ExcelIndex, Indirect, Namen, Verschuiving, voorwaardelijke opmaakGijs Verbruggen

Ik dacht het nieuwe jaar te beginnen met het maken van een Excel-kalender en die te beschrijven in een G-Info-artikel.
Maar ‘helaas’ te laat: de Indiase Excel-guru Chandoo heeft vorige week een heel mooie kalender gepubliceerd; die wil ik u niet onthouden.
Ik heb deze omgezet in het Nederlands en de basiskalender iets anders ingericht, zodat de opbouw waarschijnlijk begrijpelijker is (hoewel de Chandoo-routine voor de liefhebbers echt kicken is!).

Deze keer geen uitleg van het Voorbeeldbestand; ‘kijk’ zelf rond en ontdek hoe allerlei handige opties van Excel (zoals Voorwaardelijke opmaak) gebruikt kunnen worden om een mooie Excel-toepassing te creëren.
Maar begrijp je een optie niet: schroom niet om G-Info om uitleg te vragen!

Ben je geïnteresseerd in de oorspronkelijke kalender (en meer van het werk van Chandoo) en zijn achtergrondartikelen: kijk dan op Chandoo.org.

De aangepaste, Nederlandstalige versie kun je hier downloaden.