Tagarchief: Tekst.Samenvoegen

Functies grafisch weergeven

Excel, , , ,
Voorbeeldbestand: FunctiesGraf.xlsxDownload

Excel is inzetbaar op veel gebieden; ook binnen het (wiskunde-)onderwijs is het goed bruikbaar.
Ik moest daar deze week aan denken, toen ik bedacht hoe ik 20 jaar geleden met iemand tijdens bijlessen heb zitten worstelen om hem het gevoel bij te brengen hoe 1e- en 2e-graads-functies er in grafiekvorm uitzien.

Eerstegraadsfunctie

Zoals we allemaal (?) weten is de algemene vorm van een eerstegraadsfunctie zoals hiernaast weergegeven.
Door voor a en b waarden in te vullen krijg je een specifieke functie.

In het Voorbeeldbestand in het tabblad 1e graads is de algemene functie ingevoerd door in de menutab Invoegen in het blok Symbolen de optie Vergelijking te kiezen.

De cellen C5 en C6 (naast de teksten a en b) hebben een naam gekregen:

  1. kies de menutab Formules
  2. dan in het blok Gedefinieerde namen de optie Naam definiëren
  3. voer een Naam in, in dit geval _a
  4. kies een Bereik. Standaard is de naam geldig voor de hele Werkmap, maar in dit geval moet het bereik beperkt worden tot het werkblad 1e graads
  5. pas zo nodig de verwijzing nog aan en klik op OK

NB Excel accepteert niet alle letters als Naam; ik heb er in dit geval voor gekozen om alle namen door een _ (underscore) te laten voorafgaan.

In cel B8 wordt de functie tekstueel opgebouwd:
=”ƒ(x) = ” & ALS(_a=0;””;ALS(_a=1;”x”;ALS(_a=-1;”-x”;_a&”x”))) & ALS(_b=0;””;ALS(_b<0;” – “;” + “) & ABS(_b))

De diverse tekstblokken worden m.b.v. het &-teken aan elkaar gekoppeld.

In het eerste blok is de ƒ ingevoerd via Invoegen/Symbool. Kies daar allereerst rechtsonder de optie ASCII (decimaal) en blader dan door de mogelijkheden.
Wiskundige tekens kunnen ook op de volgende site gevonden worden:  Lijst_van_wiskundige_symbolen; gebruik daar kopiëren en plak dan in Excel.

Met de diverse ALS-functies wordt de opmaak geregeld, zodat er bijvoorbeeld geen 0x of 1x in het resultaat komt te staan.

De ABS-functie levert de absolute waarde van een getal; het resultaat is dan altijd groter of gelijk aan 0.

Om de grafiek te kunnen tekenen hebben we een tabel nodig met de bij de functie horende x,y-waarden. In het tabblad 1e graads van het Voorbeeldbestand worden 100 combinaties berekend in de cellen vanaf B32. In cel C32 komt dan de formule =_a*B32+_b

NB Om het effect van veranderingen in a of b goed te kunnen zien zijn de assen ‘vastgezet’: ze lopen allebei van -5 tot +5.

Bij  eerstegraadsfuncties zijn de snijpunten met de assen altijd belangrijk. Die worden in de cellen C10 en C11 berekend met de formules =”(0 , “&_b&”)”, respectievelijk =ALS(_a=0;”geen”;”(“&-_b/_a&” , 0)”)

Omdat het resultaat van de laatste formule vaak geen geheel getal oplevert, is het inzichtelijker om het resultaat zo nodig als een breuk weer te geven (zie cel C15):
=ALS(_a=0;”geen”;”(“&TEKST(-_b/_a;”# ??/??”)&”, 0)”)

M.b.v. de functie TEKST wordt een opmaak meegegeven aan het resultaat -b/a: eventueel een geheel getal (#) en als het nodig is een breuk (met maximaal 2 cijfers in de teller en noemer).

Om alle overbodige spaties te verwijderen, kan de functie SUBSTITUEREN worden gebruikt (zie bijvoorbeeld cel C19 in het tabblad 1e graads).

In de grafiek is ook de functie weergegeven. Dit is als volgt gedaan:

  1. selecteer de cel met de functie en kies kopiëren (Ctrl-C)
  2. klik ergens anders op het werkblad met de rechter muistoets
  3. kies Plakken speciaal …
  4. en in het vervolgscherm de laatste optie van Andere plakopties (Gekoppelde afbeelding)

De inhoud van de oorspronkelijke cel wordt dan dynamisch in een grafisch object weergegeven. Dit object kan via slepen overal in de sheet geplaatst worden, dus ook in de grafiek.

Tweedegraadsfunctie

De opbouw van het tabblad 2e graads van het Voorbeeldbestand is vergelijkbaar met bovenstaande.

Het bepalen van de eventuele snijpunten met de x-as is echter wat ingewikkelder. Weet u nog?

Maar dit levert alleen maar een resultaat op als het getal onder de wortel (de discriminant) niet kleiner is dan nul; dus dat wordt eerste even gecontroleerd.

In C14 staat daarom de volgende formule:
=ALS(Oplos?=”geen”;”geen”;”(“&(-_b-WORTEL(Discr))/(2*_a)&” , 0)”)

Derdegraadsfunctie

In het tabblad 3e graads van het Voorbeeldbestand wordt de grafiek van een derdegraadsfunctie weergegeven. Het bepalen van de snijpunten met de x-as wordt nu wat lastiger; dat valt buiten de scope van dit artikel.

 

Indirecte verwijzing

Excel, , ,

shakespeareDirect or Indirect, that’s the question!

Heb je je dit ook al ooit afgevraagd?
Als ik deze vraag beperk tot het gebruik van Excel, dan denk ik dat het antwoord meestal ontkennend zal zijn.
Het gebruik van de functie INDIRECT is niet wijdverbreid; maar misschien is de uitdrukking Onbekend maakt onbemind wel van toepassing.

Ik kreeg vorige week een vraag van iemand van een bridgeclub, die de wekelijkse resultaten automatisch wilde laten verwerken in een totaal-sheet. Ze dacht, dat daarbij VBA/programmeren aan te pas zou moeten komen, maar hier bleek de functie INDIRECT uitkomst te bieden.

Directe en indirecte verwijzingen

Indirect1Laten we eerst eens even kijken wat dat Indirect nu precies is, voordat we in een (simpel) voorbeeld enkele mogelijkheden gaan onderzoeken.

In het Voorbeeldbestand heb ik in het tabblad Vb1a een maandoverzicht met bedragen staan (kolommen B en C).
Daarnaast staat een compacter overzicht waar maar enkele maanden zijn opgenomen (kolom E). Wanneer we de betreffende bedragen daarnaast willen hebben, dan kunnen we in cel F4 intikken =C3.
Dit wordt een verwijzing genoemd en in dit geval een directe verwijzing: we nemen direct de waarde uit C3 over in cel F4.
In cel G4 zie je hetzelfde resultaat, maar daar staat als formule een indirecte verwijzing
=INDIRECT(“C3”)

Behalve, dat dit nou geen echt zinvol gebruik van deze functie is, kan ik daarmee wel de kracht van de functie uitleggen: Indirect vertaalt inwendig een tekst (let op de aanhalingstekens!) naar een directe celverwijzing.
Uiteraard moet die tekst dan wel een geldige verwijzing voorstellen (dus bijvoorbeeld geen spatie tussen de kolomletter en het rijnummer).

Maar de kracht van deze vertaalmogelijkheid komt pas tot uiting in cel G5. Hier moet het bedrag (in kolom C) van de 3e maand (cel E5) opgehaald worden:
=INDIRECT(“C”&E5)

Door het &-teken worden twee teksten gekoppeld: de letter C en de inhoud van cel E5 (zie het artikel Teksten samenvoegen).

Ai, jammer: dit klopt niet helemaal. Op deze manier wordt een verwijzing naar cel C3 gemaakt; maar maand 3 staat in rij 5, maand 4 in 6 etc. Dus altijd 2 lager dan het maandnummer aangeeft.
Oplossing:
=INDIRECT(“C”&(E5+2))
De letter C wordt aan de inhoud van cel E5 plus 2 gekoppeld; in dit geval levert dat als verwijzing C5 op.

NB de haakjes rond de optelling staan er alleen voor de duidelijkheid; zonder haakjes zou Excel hetzelfde resultaat opleveren.

De formule in cel G5 kan nu naar beneden gekopieerd worden naar G6 t/m G8 en in die cellen verschijnt het bedrag van de betreffende maanden.
Wanneer in de cellen E5 t/m E8 andere maandnummers worden ingevoerd, zullen de overeenkomende bedragen direct in kolom G getoond worden.
Het overzicht is daardoor flexibeler/dynamischer geworden, vergeleken met directe verwijzingen.

NB overal waar je in Excel een directe verwijzing zet (of dit in een formule direct achter het =-teken is, als parameter in een formule, in een rekenkundige bewerking) kan ook een indirecte verwijzing worden geplaatst.

Verwijzingstype

Indirect2In het tabblad Vb2 van het Voorbeeldbestand staat een zelfde soort maandoverzicht, maar nu ‘horizontaal’.

Nu wordt het opzoeken van het bedrag, dat bij de 3e maand (cel B8) hoort, wat lastiger:
alle bedragen staan in rij 3; dus het laatste gedeelte van de verwijzing is altijd gelijk aan 3. Nu de juiste kolom vinden: de 3e maand staat in de 5e kolom etc.; dus ook hier moeten we 2 optellen bij het maandnummer. Maar dat is niet voldoende: het kolomnummer moet omgezet worden naar een kolomletter. In Excel kan dat met de functie TEKEN.
Als we dan ook nog weten, dat de code 65 de letter A voorstelt, 66 de B etc. dan zou de formule in cel D8 te doorgronden moeten zijn:
=INDIRECT(TEKEN(64+B8+2)&”3″)

Het omzetten van een kolomnummer naar een letter is echter niet persé nodig: de functie Indirect kent nog een optionele parameter (die mag maar hoeft dus niet ingevuld te worden). Via deze parameter kunnen we aangeven, dat de verwijzing al dan niet de vorm RxKy (rij x, kolom y) heeft.
Het bedrag van de 3e maand (B8 bevat de waarde 3) ophalen kan dan ook als volgt:
=INDIRECT(“R3K”&(B8+2);ONWAAR)

De parameter ONWAAR geeft aan, dat het verwijzingstype de vorm RxKy heeft; wanneer als parameter WAAR wordt meegegeven (of wanneer de parameter wordt weggelaten zoals hiervoor) dan heeft de verwijzing de standaardvorm A1 (kolomletter, gevolgd door rijnummer).

Verwijzing naar een andere werkmap of werkblad

Vaak worden samenvattingsoverzichten op een ander werkblad (of in een andere werkmap) geplaatst; niet bij de basisgegevens zelf.

Indirect3In het Voorbeeldbestand, tabblad VB3, willen we de gegevens van het tabblad Vb1a overnemen.
In cel C3 staat een directe verwijzing naar cel C3 van het tabblad Vb1a; de naam van het tabblad (Vb1a) wordt gescheiden van de celnaam door een uitroepteken.
De indirecte verwijzing in cel D4 wordt daarom:
=INDIRECT(“Vb1a!C”&(B4+2))

En ook deze formule kan nu zonder verdere aanpassingen naar beneden gekopieerd worden.

LET OP wanneer de naam van een tabblad uit meerdere woorden bestaat, gescheiden door een spatie of een ander leesteken, niet zijnde een underscore (_), dan moeten rond deze naam rechte, enkele aanhalingstekens (‘) geplaatst worden.

Wanneer de brongegevens in een andere werkmap staan, maak dan eerst een directe verwijzing naar een cel in dat bronbestand (door in een cel het =-teken te plaatsen en dan met de muis ergens in het bronbestand te klikken). Binnen de functie Indirect zal dan eenzelfde verwijzing als tekst moeten worden opgebouwd.

Nog een voorbeeld

In het Voorbeeldbestand is een tabblad Vb1b opgenomen, dat qua opzet gelijk is aan het tabblad Vb1a.

Indirect4Het resultaat in de kolommen G en H van het tabblad Vb3 is via de functie Indirect tot stand gekomen.
Bijvoorbeeld in cel G3 staat de formule:
=INDIRECT(“‘”&G$2&”‘!C”&($F3+2))

Let op de plaats van de enkele aanhalingstekens en de gedeeltelijk absolute en gedeeltelijk relatieve verwijzingen naar G$2 en $F3.

De formule in G3 is naar rechts en naar beneden gekopieerd.

Teksten samenvoegen

Excel, VBA (Visual Basic for Applications), , , ,

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