Maandelijks archief: september 2014

Tellen met voorwaarden

Het is weer de tijd van tennistoernooien.

Op het eerste gezicht lijkt er geen verband met Excel te bestaan, maar ook hier kan Excel ingezet worden.
Ik sprak iemand, die een toernooi had georganiseerd en ’s avonds nog snel de resultaten moest bepalen: “ik heb alle uitslagen met punten per wedstrijd in Excel staan, maar ik moet nu snel van iedereen afzonderlijk de punten bij elkaar hebben.”
In Excel kan dat makkelijk met Tellen met voorwaarden.

AANTAL.ALS

Tellen met voorwaardenDe eerste functie die we zullen bekijken is AANTAL.ALS().

Ik heb een Voorbeeldbestand gemaakt, dat iets ingewikkelder is dan het verwerken van de resultaten van een tennistoernooi.
Het is  een overzicht, waarin per maand en per regio de omzet van diverse vertegenwoordigers/agenten is opgenomen.
Wanneer we nu bijvoorbeeld willen weten hoe vaak een omzet van € 7.500 voorkomt (de omzet staat in de cellen E3:E202) dan kunnen we de volgende formule gebruiken: =AANTAL.ALS(E3:E202;7500).

Tellen met voorwaardenDe eerste parameter/argument van de functie is het bereik van de gegevens, waarvan u de aantallen wilt weten en het tweede argument is de voorwaarde waaraan ze moeten voldoen om mee te mogen tellen.
Door in te typen =AANTAL.ALS(B3:B202;”jan”) zien we dat er voor januari 22 keer een omzet is geregistreerd.

LET OP: als de voorwaarde een tekst is dan moeten er “ (aanhalingstekens) om de voorwaarde gezet worden.

Tellen met voorwaardenIn het voorbeeld hiernaast ziet u dat u op deze manier snel een jaaroverzicht kunt maken; op de plaats van de voorwaarde staat een verwijzing naar een cel, die de gewenste voorwaarde bevat.
Door in de cel I6 het bereik dmv de $-tekens absoluut te maken en de voorwaarde (G6) niet, kan deze formule direct naar beneden gekopieerd worden en hoeft dan in de andere cellen niet meer aangepast te worden.
Onze tennis-organisator zou een dergelijk overzicht dus ook voor de deelnemers aan het toernooi kunnen maken en Excel haalt de gewenste gegevens dan op.

Jokers

Zoals op veel meer plaatsen in Excel kunnen bij tekst-voorwaarden zogenaamde Jokers (wildcards) worden ingezet.
Tellen met voorwaardenHiernaast staan drie voorbeelden van het gebruik van het * (sterretje; dit joker-teken geeft aan, dat wat er vanaf die positie komt niet relevant is): in het eerste geval tellen we de aantallen van de maanden die beginnen met een ‘j’, in de tweede regel alle regio’s, die eindigen op een ‘d’ (Noord en Zuid dus) en als laatste alle regio’s, die eindigen op een ‘t’ (Oost en West).

Tellen met voorwaardenIn de praktijk komt het gebruik van een ander joker-teken (het vraagteken, ?) minder voor: waar het sterretje (*) een willekeurig aantal tekens vervangt, geeft het vraagteken aan, dat het niet relevant is wat er op die ene positie staat. In het voorbeeld zoeken we alle agenten, waar op de tweede plaats een ‘a’ staat.

Numerieke voorwaarden

Tellen met voorwaardenWanneer we een voorwaarde opgeven voor getallen dan is het niet alleen mogelijk om exacte getallen te zoeken (in het eerste voorbeeld hierboven moest de omzet exact gelijk zijn aan 7.500), maar ook vergelijkingen als kleiner dan (<), groter of gelijk aan (>=) zijn mogelijk.

SOM.ALS

In de voorgaande voorbeelden hebben we met behulp van AANTAL.ALS aantallen geteld. In Excel is het ook mogelijk om op een vergelijkbare manier getallen op te tellen (voor onze toernooi-directeur zeker zo interessant!).

Tellen met voorwaardenIn het voorbeeld hiernaast wordt door de formule =SOM.ALS(E3:E202;7500) alle omzet, die voldoet aan de voorwaarde, dat de omzet gelijk is aan 7.500, opgeteld. Aangezien we hiervoor hebben gezien dat dit in het voorbeeldbestand 4 keer voorkomt, is de som gelijk aan 30.000.

Tellen8De SOM.ALS-formule is echter nog krachtiger: het is mogelijk om een derde argument aan de functie mee te geven, het zogenaamde Optelbereik.

NB een dergelijk argument, dat niet altijd hoeft te worden ingevuld, wordt optioneel genoemd. Bij het intikken van een formule worden dergelijke parameters met []-haken aangeduid. Bij het scherm met Functieargumenten (ga naar een cel met een SOM.ALS-formule en klik op FunctieInvoeren), zijn optionele argumenten lichtgrijs.

Een uitbreiding van SOM.ALS is de functie SOMMEN.ALS. Misschien wat verwarrend: de volgorde van de argumenten is heel anders dan bij SOM.ALS.
Tellen met voorwaardenHet eerste argument van SOMMEN.ALS is het optelbereik, het tweede een criteriumbereik en het derde de voorwaarde. Op deze manier is de werking van deze functie exact gelijk aan SOM.ALS. De kracht van de uitgebreidere functie is, dat er meerdere voorwaarden als argument meegegeven kunnen worden.

Draaitabel

Veel van bovenstaande berekeningen kunnen veel makkelijker, sneller, flexibeler (bedenk nog maar een paar ander loftuitingen) met een draaitabel worden bepaald; zie het tabblad Draai in het Voorbeeldbestand.

Waar het aankomt op voorwaarden met Jokers of vergelijkingstekens (<, > etc) zijn bovenstaande formules echter een must-have (of must-know?).

Functie ToonFormule

In de voorbeelden is gebruik gemaakt van de Eigen Functie ToonFormule(). Andere voorbeelden van eigen functies zijn te vinden in het artikel G-Info en de functie G_Info().

Wilt u de functie ToonFormule gebruiken dan kunt u deze terugvinden in Module1 van de Visual Basic-omgeving (zie het artikel over VBA voor uitleg hierover). Kopieer de functie naar een Module in de VBA-omgeving van uw eigen spreadsheet.


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

G-Info en de functie G_Info()

In mijn vorige artikel heb ik het gehad over de functies CEL() en INFO().
LET OP
: in Excel en Access wordt een functie altijd gevolgd door 2 ronde haken. Bij de meeste functies worden tussen die haakjes argumenten meegegeven.

De naam van die tweede functie triggerde me om een eigen functie G-Info() te ontwikkelen. Maar wat zou die functie moeten doen?

Deze week kwam ik op een idee: wij moesten al nadenken wanneer we volgend voorjaar een korte vakantie zouden plannen; ergens met Hemelvaart of Pinksteren. Maar op welke data vallen die volgend jaar?

Ik wist nog wel, dat deze feestdagen afhankelijk zijn van Pasen, maar wanneer is het nu precies eerste Paasdag?

Pasen

Even “googlen” en al snel was ik een stap verder. Wikipedia:

Het concilie van Nicea bepaalde in 325 dat Pasen moet worden gevierd op de zondag na de eerste volle maan in de lente.

We hoeven dus alleen maar te weten wanneer de lente begint, wanneer het dan weer volle maan wordt en wat dan de eerstvolgende zondag is en we zijn klaar!!

PasenNog maar even verder op Google rondgeneusd en toen bleken er al diverse voorbeelden beschikbaar voor het bepalen van de datum van eerste Paasdag.

Dat is dus minder makkelijk dan ik gedacht had!
Met dank aan de bedenkers van deze routine gaan we dit gebruiken als bouwsteen voor onze nieuwe functie G-Info().

In het Voorbeeldbestand kunt u deze functie aan het werk zien:

Pasenwanneer in cel C2 een jaartal wordt ingetikt zal in cel D2 worden weergegeven, wanneer Pasen in dat jaar valt.

In cel D2 staat de formule =BepaalPasen(Jaar). Wanneer deze functie wordt ingetikt, zal in eerste instantie in D2 een getal komen. Door deze cel een datum-opmaak mee te geven zien we, dat Pasen in 2014 op 20 april is geweest. Ik heb de cel een aangepaste opmaak meegegeven: niet alleen de datum komt in cel D2, deze wordt vooraf gegaan door de tekst “Pasen valt op”.

LET OP: het Voorbeeldbestand bevat een viertal zelf-gedefinieerde namen, zodat de gebruikte formules veel beter leesbaar zijn. Cel C2 bijvoorbeeld heeft de naam Jaar gekregen.
Voor uitleg over het definiëren van namen, zie het artikel Namen ipv celverwijzingen.

G_Info()

De functie G_Info() vormt een uitbreiding op de functie BepaalPasen. Als parameter/argument wordt niet allen het jaar meegegeven, maar ook van welke feestdag de datum bepaald moet worden. Als derde parameter dient nog meegegeven te worden of de functie een datum moet retourneren (een getal dus) of een tekst.

FeestdagenAls voorbeeld: in C7 staat de formule =G_Info(Jaar;$B7;C$6).
Deze levert als resultaat de datum voor Pasen (cel B7) in het jaar 2014 en wel als echte datum (niet als tekst; cel C6)). Het resultaat is een getal, die een datumopmaak mee heeft gekregen.

PS1 let op de $-tekens in de formule; deze zijn zodanig gekozen, dat bij het kopiëren van de formule (naar rechts en naar beneden) de juiste verwijzingen blijven bestaan.
PS2 ik had de formule graag G-Info genoemd, maar het -streepje is in een functienaam niet toegestaan.

De formule in cel D7 geeft ook de Paasdatum weer, maar nu is het resultaat een tekst.

Naast de Paasdatum levert G_Info() ook de data voor Pinksteren, Carnaval, Hemelvaart, Kerstmis en Nieuwjaar (die laatste 2 liggen nogal voor de hand!). Zoals we hierna zullen zien, zijn uitbreidingen makkelijk door te voeren. Een verjaardag of huwelijksdag vergeten is er dus niet meer bij!

VBA

Eigen functies (in het Engels: User Definied Function, UDF) worden op dezelfde plaats gedefinieerd als waar macro’s worden geregistreerd: in de VBA-editor. Wanneer u daar nog nooit mee hebt gewerkt, bekijk dan het betreffende artikel.

Een korte uitleg van het programma:

PasenVBA1

Allereerst definiëren we de naam van de functie: G_Info.
NB zoals al eerder aangegeven is een – (streepje) niet toegestaan.
Daar achter, tussen haakjes, geven we aan welke parameters/argumenten/waarden aan de functie worden meegegeven: het Jaar, waarvoor de feestdagen bepaald moeten worden (moet een Integer=geheel getal zijn), voor welke Feestdag moet er informatie opgeleverd worden (een String=tekst) en een parameter TekstUitvoer (van het type Boolean; kan alleen de waarden WAAR of ONWAAR bevatten).
De groene tekst (achter het ‘-teken) is commentaar en wordt door het programma niet gebruikt.
In het vervolg van het programma willen we tusseninformatie ‘onthouden’ daarom definiëren we via Dim een variabele x.
PasenVBA2Het programma moet nu afhankelijk van de inhoud van de parameter Feestdag de datum van Pasen of van Pinksteren of van Carnaval etcetera genereren.
Binnen VBA is daar een handige constructie voor:
Select Case ….
     Case Voorwaarde1
Vervolg1
      Case Voorwaarde2
Vervolg2
……
End Select
Wanneer in onze functie aan de parameter Feestdag de waarde Pasen wordt meegegeven, moet het programma de Paasdatum gaan bepalen. Afhankelijk van de inhoud van TekstUitvoer (WAAR of ONWAAR) levert het programma een tekst of een datum/getal op. Dit resultaat wordt in de hulpvariabele x bewaard.
PasenVBA3De Select-optie wordt afgesloten met een Case Else: als er aan de parameter Feestdag een andere tekst wordt meegegeven dan waar in ons programma rekening mee is gehouden, zal dit programmagedeelte worden uitgevoerd.

Na afsluiting van de Select-constructie (dus na End Select) moeten we nog zorgen, dat de de functie in Excel het resultaat van het programma weergeeft. Aangezien we het resultaat in de variabele x hebben vastgelegd sluiten we de functie af met G_Info=x.

Het mag nu niet moeilijk meer zijn om de functie uit te breiden met voor u belangrijke feestdagen als verjaardag, huwelijksdag enzovoorts.

PS de twee in het Voorbeeldbestand gebruikte functies (BepaalPasen en G_Info) zijn zonder aanpassingen ook bruikbaar in Access. Kopieer de functies naar de VBA-omgeving (Module) van Access en u kunt ze direct in een query inzetten.

Kalender

In het voorbeeld is ook een jaaroverzicht opgenomen, waarin direct de diverse feestdagen worden geaccentueerd:
Feestdagen2In dag 1 van januari staat de formule =Datum(Jaar;Maand;Dag) en deze formule is naar rechts en naar beneden gekopieerd. Deze Excel-functie genereert afhankelijk van het jaar, maand- en dagnummer de betreffende datum (in Excel-formaat, dus een getal; zie ook het betreffende artikel over Datums).
Jaar is de naam van cel C2, Maand de naam van het bereik met de cijfers 1 t/m 12 voor de maandomschrijvingen en Dag is het bereik met de cijfers 1 t/m 31.
Alle betreffende cellen hebben bij cel-eigenschappen het datumformaat “ddd” meegekregen: dus alleen de weekdag wordt afgekort getoond.

De blanco cellen op het einde van diverse maanden, de kleuren voor de weekenden en de kleuren voor de feestdagen worden m.b.v.  Voorwaardelijke opmaak geregeld (via de menu-optie Start in het blok Stijlen).
Feestdagen3


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