Tagarchief: eigen functie

Formules, Namen, Eigen functies



De grote kracht van Excel (en ieder ander spreadsheet-programma) is dat je gebruik kunt maken van allerlei formules. Of het nu berekeningen zijn of logische constructies (bijvoorbeeld met behulp van de ALS-functie) of combinaties hiervan: ieder spreadsheet wordt met formules ‘aan elkaar geknoopt’.

Bij de opbouw van formules is het zeer aan te raden om Namen te gebruiken in plaats van cel-verwijzingen als C4 of bereik-aanduidingen als F5:F20. De leesbaarheid van formules gaat daarmee met sprongen vooruit.

In dit artikel daarom (nogmaals) aandacht voor het maken en gebruik van Namen. Ook het opslaan van formules in een Naam komt aan bod. Maar ik zal ook laten zien dat het gebruik van Eigen functies (met behulp van VBA) veel flexibeler is.

Namen definieren

Een naam vastleggen in Excel kan op diverse manieren:

  1. de eerste is een rechtstreekse handmatige invoer (zie tabblad VB1 in het Voorbeeldbestand): selecteer een cel (in dit voorbeeld C2), klik in het Naamvak, links van de Formulebalk, en tik de gewenste naam in (hier dus Korting1) en druk op Enter.
    LET OP een naam mag geen spaties bevatten en geen celverwijzing voorstellen zoals A1 of EXP10. Het is ook af te raden om bestaande functienamen in te voeren (zoals Som); dit is voor Excel geen echt probleem maar voor de gebruiker van het spreadsheet kan het zeer verwarrend zijn.
    NB1 gebruikelijk is om op de plaats waar je een spatie zou willen zetten het _-teken te plaatsen (de underscore; bijvoorbeeld Korting_1) of het tweede gedeelte met een hoofdletter te laten beginnen (bijvoorbeeld KortPerc).
    NB2 worden er meerdere cellen tegelijkertijd geselecteerd, dan krijgt het totale bereik de ingevoerde naam; de afzonderlijke cellen hebben dan geen naam.
  2. plaats een omschrijving links of boven de cel die een naam moet krijgen.
    Selecteer de omschrijving en de betreffende cel (in het voorbeeld B2:C2) en kies in de menutab Formules in het blok Gedefinieerde namen de optie Maken obv selectie.
    In het pop-up venster heeft Excel in dit geval al ‘gezien’ dat er links van de cel een omschrijving staat; deze zal als naam voor cel C2 worden gebruikt.
    NB1 als er spaties staan in de omschrijving dan vervangt Excel deze automatisch door een underscore.
    NB2 stelt de omschrijving een celverwijzing voor dan plaatst Excel een underscore achter de naam (Rij1 wordt dan Rij1_) .
    NB3 zijn er naast de omschrijving meerdere cellen geselecteerd dan krijgt het totale bereik van die cellen de tekst van de omschrijving als naam.
  3. selecteer de cel (of cellen) die een naam moet krijgen en kies in de menutab Formules in het blok Gedefinieerde namen de optie Naam definiëren.
    Voer een naam en eventueel een opmerking in. Controleer of de verwijzing klopt.
    Het Bereik voor een naam staat standaard op Werkmap; de naam kan overal gebruikt worden. In dit geval is deze korting alleen maar geldig op het tabblad Vb1, dus moet het bereik daaraan aangepast worden.
    NB klik je op het ‘vinkje’ achter het Naamvak, dan zie je alle namen die voor dit tabblad geldig zijn (inclusief de namen die voor de hele werkmap gelden).
  4. kies in de menutab Formules in het blok Gedefinieerde namen de optie Namen beheren en klik op de button Nieuw

NB wanneer op een van deze manieren een naam wordt ingevoerd zal Excel de verwijzing standaard ´absoluut´ maken; zie de $-tekens.

Namen beheren

Wil je kijken welke namen er in het Excel-bestand worden gebruikt en/of deze aanpassen kies dan in de menutab Formules in het blok Gedefinieerde namen de optie Namen beheren.

Hier is het mogelijk om een nieuwe naam toe te voegen of een bestaande te verwijderen.
Ook kan een naam bewerkt worden.

LET OP Bij bewerken kunnen de naam, opmerkingen en verwijzing aangepast worden, dus NIET het bereik. Wil je deze wijzigen dan moet je een nieuwe naam toevoegen met een ander bereik en de andere verwijderen.

Gebruik van Namen 1

In het tabblad Vb1 van het Voorbeeldbestand zijn netto-bedragen berekend met behulp van een zelf-gedefinieerde naam. In cel D7 staat de formule =C7*(1-Korting1).
Wanneer deze naar beneden gekopieerd wordt, wijzigt C7 automatisch in C8 etcetera. De korting is via de naam absoluut gedefinieerd en verwijst dus ook bij het kopiëren altijd naar cel C2.

Bij het invoeren van de formule laat Excel bij het intikken van de letters ko alle mogelijkheden zien, die voor dit tabblad gelden. De opties met ƒx zijn functies binnen Excel (standaard of eigen functies, zie hierna), de andere zijn namen.
Dubbelklik op de gewenste functie of naam of selecteer deze met de cursor en druk dan op de tab-toets.

Gebruik van Namen 2

Bij het definiëren van namen hoeft u zich niet te beperken tot cel-verwijzingen. Het is ook mogelijk om formules op te nemen.

Voor het tabblad Vb2 in het Voorbeeldbestand is voor de naam Kort2a als verwijzing de formule =1-Vb2!$C$2 ingevoerd. Dat houdt in dat de berekening van het netto-bedrag er dan iets anders kan uitzien:
het oorspronkelijke bedrag wordt vermenigvuldigd met Kort2a. Het mag duidelijk zijn dat vooral bij uitgebreide/complexe formules hierdoor het spreadsheet overzichtelijker blijft.

In kolom E staat een vergelijkbare formule, maar daar wordt gebruik gemaakt van Kort2b, die gedefinieerd is als =1-‘Vb2’!Korting2

LET OP wanneer binnen zo’n formule gebruik wordt gemaakt van een naam, die niet voor de hele werkmap geldig is, dan moet deze naam voorafgegaan worden door de naam van het tabblad, gevolgd door een !-teken. De aanhalingstekens zijn eigenlijk alleen maar nodig als de naam van het tabblad spaties bevat.

Gebruik van Namen 3

In het vorige voorbeeld is het kortingspercentage via een Naam omgezet in een kortingsfactor. Maar ook het netto-bedrag kan via zo’n constructie worden bepaald; zie het tabblad Vb3 in het Voorbeeldbestand.
Netto3a is gedefinieerd als
=’Vb3′!C7*(1-‘Vb3!Korting3)

LET OP C7 is relatief (zonder $-tekens) gedefinieerd. En dit geredeneerd vanuit cel D7. Dit betekent dat dezelfde naam Netto3a vanuit cel D8 verwijst naar cel C8. Selecteer maar eens een cel ergens in kolom D en kies dan Namen beheren. Of kies de optie Broncellen aanwijzen in het blok Formules controleren.

Maar wanneer we de naam Netto3a in een andere kolom plaatsen gaat het mis. Zie cel H21 in het tabblad Vb3. De naam Netto3a hanteert nu als bron de cellen uit kolom G.

Met de formule in Netto3b is dit opgelost: =’Vb3′!$C7*(1-!Korting3)
Dus de kolom is absoluut (altijd C), maar de rij relatief; zie cel H23.

NB de exacte locatie van de cel met de naam Korting3 is weggelaten; alleen het !-teken is voldoende. Een nadeel hiervan is dat Broncellen aanwijzen dan niet meer volledig is. In het Voorbeeldbestand is wel de naam van het tabblad opgenomen omdat Mac-gebruikers anders in de problemen komen.

In het tabblad Vb3 van het Voorbeeldbestand is nog een derde naam (Netto3c) opgenomen met als formule =’Vb3′!$C7:$C40*(1-!Korting3)

De werking is hetzelfde maar het voordeel is dat je nu het totaal van de netto-bedragen kunt bepalen met de formule =SOM(Netto3c). Deze formule moet wel ergens in rij 7 staan; zie het resultaat in de cellen H7 en H8.

LET OP Excel weet niet dat de inhoud van zo’n naam een formule is en zal het resultaat niet automatisch herbereken (bijvoorbeeld bij het wijzigen van het kortingspercentage). Daarom is de SOM nog aangevuld met +0*NU(); aangezien NU ieder moment van waarde verandert zal Excel de totale formule bij iedere wijziging van het spreadsheet wel opnieuw berekenen.

Gebruik van Namen 4

Wanneer gebruik wordt gemaakt van Excel-tabellen (en zoals we allemaal weten heeft dat grote voordelen) dan moet goed opgelet worden bij het definiëren van Namen.

In het tabblad Vb4 van het Voorbeeldbestand bestaat het systeem uit de tabel tblNetto. Binnen dat tabblad is de naam Netto4a gedefinieerd als =tblNetto[Bedrag]*(1-!Korting4)

De opzet van de formule is vergelijkbaar met de naam Netto3c. De werking is nu echter anders: in iedere rij komt nu hetzelfde resultaat, namelijk het nettobedrag dat hoort bij de eerste regel. De SOM-formule werkt wel goed en is nu onafhankelijk van de plaats.

Voor de juiste berekening in de tabel hebben we een andere naam nodig:
=’Vb4′!$C7*(1-!Korting4) of =tblNetto[@Bedrag]*(1-!Korting4)

NB de @ zorgt er voor dat met het bedrag in de betreffende regel wordt gerekend.

Eigen functie

Zoals hiervoor duidelijk mag zijn geworden kleven er aan het gebruik van formules in Namen wel nogal wat problemen of laten we zeggen aandachtspunten.
Wel is een voordeel van die systematiek dat beginnende gebruikers niet zomaar formules kunnen aanpassen.

Met een klein beetje VBA lossen we voorgaande aandachtspunten dat snel en vakkundig op:

  1. druk op Alt-F11 of kies de optie Visual Basic in het blok Programmacode van de menutab Ontwikkelaars
  2. zoek het betreffende project (in dit geval Formules.xlsm) en klik daarop
  3. als er nog geen Modules zijn, kies dan eerst Invoegen/Module
  4. dubbelklik op een module en plaats daar de volgende functie-definitie
    Function BerekenNetto(Bedr, KortPerc)
          BerekenNetto = Bedr * (1 – KortPerc)
    End Function
    De eigen functie BerekenNetto kent 2 parameters: een bedrag en een kortingspercentage. Het resultaat van de gewenste berekening wordt aan de functienaam gekoppeld.

In het tabblad Vb5 van het Voorbeeldbestand wordt deze eigen functie gebruikt. Omdat we binnen een Excel-tabel werken kunnen we weer naar de Bedrag-kolom wijzen (denk aan de @).

Maar de formule kan ook ergens anders in het tabblad staan, en wordt dan iets als =BerekenNetto(C7;Korting5)

Overzicht Namen

Zeker als er veel namen gebruikt worden in een Excel-systeem is het belangrijk om het systeem goed te documenteren.
Eén van de onderdelen van de documentatie moet een goede beschrijving van de verschillende Namen zijn (zie het tabblad Docu in het Voorbeeldbestand).

Gelukkig kunnen we een groot gedeelte automatisch laten genereren:

  1. selecteer een tabblad met één of meer geldige namen
  2. selecteer ergens een lege cel; zorg dat rechts en daaronder genoeg lege cellen zijn
  3. kies in de menutab Formules in het blok Gedefinieerde namen de optie Gebruiken in formule; in het vervolgscherm kiest u Namen plakken
  4. wanneer u dan op Lijst plakken klikt, wordt er een overzicht in het tabblad geplaatst
  5. kopieer de gegevens naar een documentatie-tabblad
  6. herhaal alle stappen voor ieder tabblad

NB alle namen die geldig zijn voor de hele werkmap komen dan meerdere keren voor; dus is er nog wat schoning vereist.


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

 

 

Teksten samenvoegen

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

tekst samenvoegenIn 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:

  1. 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
  2. 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.
  3. 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)
  4. het resultaat tekst samenvoegen is niet precies wat we willen; er moet nog een spatie tussen de 2 voornamen: =TEKST.SAMENVOEGEN(B4;” “;C4)
  5. deze formule kopiëren we naar alle cellen waar namen moeten worden samengevoegd (in het voorbeeld B13, B14 en C14)
  6. 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: tekstsamen3
    * ‘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
  7. 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.

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

  1. leg het jaar vast in cel C2 en de maand in C3
  2. typ de volgende formule in cel B7:
    =”Overzicht van “&C3&” “&C2
    (denk aan de spaties op de juiste plaatsen!)
  3. 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
tekstsamen5 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.

tekstsamen6In 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:tekstsamen7
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.

tekstsamen8In 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:

  1. voeg op de plaats van kolom C een nieuwe kolom in
  2. in de nieuwe cel C3 plaatsen we de volgende functie:
    =TekstSamenvoegenRange(D3:E3)
  3. kopieer cel C3 naar beneden (dubbelklik op de vulgreep van cel C3)
  4. ook nu klopt nog niet alles: in regel 7 moeten 3 cellen samengevoegd worden. Daar moet de formule dus worden: =TekstSamenvoegenRange(D7:F7)
  5. kopieer deze cel naar alle regels waar drie cellen moeten worden samengevoegd
  6. selecteer cel D2, ‘pak’ de rand vast en sleep naar C2
  7. selecteer de hele kolom C (klik op de C boven de kolom)
  8. kies kopiëren en daarna direct Plakken/waarden; op deze manier is de inhoud van kolom C ‘hard’ gemaakt
  9. voeg op de plaats van kolom D een nieuwe kolom in
  10. in de nieuwe cel D3 plaatsen we de volgende functie:
    =TekstSamenvoegenRange(G3:I3)
  11. kopieer cel D3 naar beneden (dubbelklik op de vulgreep van cel D3)
  12. 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.
  13. pas op dezelfde manier de overige formules aan, waar dat nodig is.
  14. sleep het kopje uit cel F2 naar D2
  15. 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:

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

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

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: