Tagarchief: Als

Excel-functies N en T



Excel kent 2 functies waarvan het nut niet helemaal duidelijk is: N() en T().
Het lijkt er op, dat het nog restanten zijn uit het verleden. Volgens Microsoft bestaan ze nog “in verband met compatibiliteit met andere spreadsheet-programma’s“.

Allebei de functies kennen maar 1 argument/parameter; de functie N() zet de parameter zo mogelijk om in een getal (Numeriek) en de functie T() levert als resultaat een Tekst, als het argument ook een tekst is.

NB de 2 haakjes in de tekst staan er bewust omdat iedere Excel-functie deze haakjes nodig heeft; bij verreweg de meeste functies moeten tussen de haakjes één of meer argumenten opgegeven worden.

Hieronder zullen we kijken hoe deze functies zich in de ‘normale’ praktijk gedragen, maar ik zal ook laten zien hoe de functies zich goed laten gebruiken in meer creatieve/excentrieke toepassingen.

De functie N()

Zoals al aangegeven zet deze functie het argument om in een getal; tenminste als dat (makkelijk) kan, anders levert de functie de waarde 0 terug; zie het tabblad Vb1 in het Voorbeeldbestand.

Uit de eerste 2 voorbeelden blijkt, dat de N-functie een combinatie van tekst en cijfers NIET kan omzetten naar een getal.
Een geheel getal of een getal met decimalen levert wel de juiste waarde op.
Wanneer de parameter een getal met een decimale punt bevat wordt dit door Excel als tekst gezien (de inhoud van de cel is dan ook automatisch links uitgelijnd) .

In cel B8 staat een formule, die als resultaat een getal oplevert; de N-functie neem deze waarde over.
Een datum is voor Excel een getal (het aantal dagen na 1-1-1900; zie het artikel Data (datums)). De N-functie zet een harde datum (in cel B9) dan ook ‘gewoon’ om naar het betreffende getal en ook het resultaat van een functie (VANDAAG() in cel B10). Ook als de cel een uitgebreide datum-opmaak heeft gekregen (cel B11) levert dit hetzelfde resultaat.

NB Excel heeft de cellen B8:B11 automatisch rechts uitgelijnd; daaraan is al te zien dat hij ze als numerieke waarden interpreteert.

De cellen B12:B14 bevatten teksten (automatisch links uitgelijnd) en de N-functie levert dan ook de waarde 0 terug.
Cel B12 bevat een ‘optelling’ (met het &-teken) van een tekst en een datum (dus een getal) en wordt daardoor vanzelf een tekst: =”Vandaag: “&VANDAAG()
In B13 is gebruik gemaakt van de functie TEKST om de datum een opmaak mee te geven: =”Vandaag: “&TEKST(VANDAAG();”dd-mm-jjjj”)
B14 bevat alleen de TEKST-functie, waardoor het resultaat (uiteraard) direct een tekst wordt: =TEKST(VANDAAG();”dddd d mmmm jjjj”)

De teksten Waar en Onwaar worden door de N-functie vertaald naar 1, respectievelijk 0. Ook als het het resultaat is van een formule (zie cel B17 met de formule =2>1).
De resultaten van formules die een foutwaarde opleveren (bijvoorbeeld =1/0 in cel B18) worden door de N-functie overgenomen.
Wanneer N() een lege parameter meekrijgt (zie cellen B19 en C19) dan wordt het resultaat 0.

NB let op de automatische uitlijning door Excel van de cellen B15:B18.

N-functie in plaats van ALS

De N-functie kan ook goed gebruikt worden ter vervanging van de ALS-functie. Dit levert een iets kortere en beter leesbare formule op.
Stel we hebben de situatie, dat een bedrag met een bepaalde provisie wordt verhoogd wanneer dat bedrag hoger of gelijk is aan een grenswaarde (zie tabblad Vb1 in het Voorbeeldbestand).
De bijbehorende formule is dan:
=ALS(G4>=G2;G4*(1+G3);G4)
of (als we de betreffende cellen een naam hebben gegeven):
=ALS(Bedrag>=Grenswaarde;Bedrag*(1+Provisie);Bedrag)

NB een blok cellen een naam geven kan makkelijk als volgt:

  1. selecteer de cellen F2 t/m G4
  2. kies in de menutab Formules in het blok Gedefinieerde namen de optie Maken o.b.v. selectie
  3. in dit geval moet alleen Linkerkolom aangevinkt staan
  4. klik op OK
  5. controleer even wat er gebeurd is: kies in de menutab Formules in het blok Gedefinieerde namen de optie Namen beheren

Maar de formule kun je ook op een andere manier (eenvoudiger?) schrijven:
=Bedrag*(1+ALS(Bedrag>=Grenswaarde;Provisie;0))
of
=Bedrag*(1+Provisie*ALS(Bedrag>=Grenswaarde;1;0))
of door de N-functie te gebruiken:
 =Bedrag*(1+Provisie*N(Bedrag>=Grenswaarde))

N-functie in plaats van een opmerking

Er kan niet genoeg benadrukt worden hoe belangrijk het is om een spreadsheet van voldoende documentatie te voorzien (voor uzelf en/of voor anderen).

Die documentatie kan vastliggen in Word-files, in een apart tabblad en/of door cellen met formules van opmerkingen te voorzien (zie cel G5 in het tabblad Vb1 van het Voorbeeldbestand).

Maar plaats u veel opmerkingen in een spreadsheet dan wordt het daardoor ook niet overzichtelijker. Commentaar kan ook met de N-functie aan een cel worden toegevoegd. In cel G9 wordt daartoe bij de formule de N() met als parameter het commentaar (als tekst!) opgeteld; die laatste levert toch de waarde 0 op.

De functie T()

De T-functie zal nu weinig verrassingen meer opleveren: als de parameter een tekst is (of via een formule een tekst bevat) dan zal de T-functie de tekst terugleveren en anders niets (een lege tekst).

Aangezien WAAR en ONWAAR intern Excel als numeriek worden gezien (1 of 0) levert T() dus een lege waarde op.

Bij een foutmelding als parameter wordt de foutmelding overgenomen en een leeg argument voor de T-functie geeft een leeg resultaat.

T() in de praktijk

In een bepaalde toepassing wilt u teksten uit verschillende kolommen aan elkaar koppelen. Zoals we gezien hebben kan dat gemakkelijk met behulp van het &-teken. In het tabblad Vb2 van het Voorbeeldbestand hebben we dat gedaan in cel E3; daar zijn tussen de teksten ook nog extra spaties toegevoegd.

In deze toepassing mogen de cellen echter allen gekoppeld worden als het een tekst bevat; hier komt de T-tunctie om de hoek kijken (zie cel E4).

Maar nou krijgen we problemen met de hoeveelheid tussenspaties. In cel E5 is dat als volgt opgelost:
=ALS(T(B5)=””;””;T(B5)&” “)&ALS(T(C5)=””;””;T(C5)&” “)&ALS(T(D5)=””;””;T(D5))

Als T(B5) een leeg resultaat oplevert, dan hoeft er niets te gebeuren, anders komt er de tekst T(B5) met een extra spatie.
Idem voor C5 en voor D5 bijna hetzelfde; de extra spatie vervalt.

In cel E6 staat een alternatief:
=ALS(N(B6);””;T(B6)&” “)&ALS(N(C6);””;T(C6)&” “)&ALS(N(D6);””;T(D6))

De twee laatste formules hebben als probleem dat als de laatste kolom een getal bevat (en ook als de tweede en de laatste kolom getallen bevatten) er nog een spatie op het einde van het resultaat staat.
Dat lossen we als volgt op: =SPATIES.WISSEN(E6)

NB maar we hadden natuurlijk dan ook niet zo ingewilled hoeven te doen met de ALS-functie, want SPATIES.WISSEN verwijdert niet alleen alle spaties aan de voor- en achterkant, maar wijzigt meerdere, tussenliggende spaties in één spatie. Dus de formule in E4 had ook kunnen zijn: =SPATIES.WISSEN(T(B4)&” “&T(C4)&” “&T(D4))

T-functie en CSE

In de dagelijkse praktijk hebben de T- en N-functies weinig toegevoegde waarde en dus ook weinig toepassingen.
Bij meer ingewikkelde (database-) problemen zie je toch nog wel eens opduiken.

In het Voorbeeldbestand heb ik een idee van Chandoo overgenomen in het tabblad Data.
Uit een HR-systeem is een overzicht gekomen van medewerkers, maar wel in een speciale vorm: in de oneven regels (met donkere achtergrond) staat de medewerker en direct daaronder (met lichtere achtergrond) zijn of haar leidinggevende (de tabel met medewerkers heeft de naam tblData gekregen).
Ook de leidinggevende kan weer een leidinggevende hebben, dus namen kunnen dubbel voorkomen (zie bijvoorbeeld A. Niesen; leidinggevende van A. Smit (regel 4) en als medewerker (regel 7)).

De bedoeling is om uit dit bestand bij een bepaalde medewerker zijn/haar leidinggevende op te zoeken. Het mag duidelijk zijn, dat een gewone VERT.ZOEKEN hier niet volstaat; we mogen alleen maar zoeken in de cellen met een donkere achtergrond.

Wanneer we dus A. Niesen zoeken moeten we op regel 7 uitkomen; ofwel in het derde blokje van de medewerker/baas-combinatie.

NB om de invoer van een naam te vergemakkelijken heb ik aan cel E2 een Validatie-regel toegevoegd: alleen maar namen uit de reeks met de naam KeuzeLijst zijn toegestaan en kunnen via de -button gekozen worden.

De formule in cel E3 ziet er zo uit:
=VERGELIJKEN(E2;T(VERSCHUIVING(tblData[[#Kopteksten];[Medewerkers]];RIJ($A$1:$A$100)*2-1;;1;1));0)

Wanneer we dit wat overzichtelijker laten zien:
01:=VERGELIJKEN(
02:     E2;
03:     T(
04:          VERSCHUIVING(
05:               tblData[[#Kopteksten];[Medewerkers]];
06:               RIJ($A$1:$A$100)*2-1;
07:               ;
08:               1;
09:               1
10:          )
11:     );
12:     0
13:)

Met Vergelijken (regel 1) gaan we de medewerker (regel 2) opzoeken.
Waar gaan we zoeken? Natuurlijk in de tabel tblData (regel 5). Maar niet in alle namen; alleen in de eerste, derde, vijfde etc. Met Verschuiving (regel 4) wordt een eigen reeks gemaakt door te verschuiven vanaf de koptekst Medewerkers (regel 5).
Hoeveel gaan we verschuiven? Als je weet dat RIJ(A1:A100) het rijnummer van de betreffende cellen genereert (regel 6; dus de getallen 1 t/m 100) dan is duidelijk dat door vermenigvuldiging met 2 en dan er 1 vanaf trekken de getallen 1, 3, 5 etc ontstaan. Met deze reeks gaan we naar beneden verschuiven vanaf de kopregel.
We blijven met de verschuiving in dezelfde kolom, dus de parameter in regel 7 is leeg.
Het resultaat van de verschuiving moet 1 cel hoog zijn (regel 8) en 1 cel breed (regel 9).
Om nu te zorgen, dat er een reeks van echte namen ontstaat komt de T-functie om de hoek kijken (regel 3).
Dus we gaan de naam uit E2 vergelijken met onze eigen reeks, waarbij een exacte match nodig is (vandaar de 0 in regel 12). Het resultaat is het volgnummer van het de combi medewerker/baas.

LET OP om te zorgen, dat de functie RIJ alle benodigde getallen genereert moet de formule in E3 als een zogenaamde array- of matrix-formule worden ingevoerd. Sluit de invoer NIET af met Enter, maar met Control-Shift-Enter (ofwel een CSE-invoer).

NB bekijk de werking van de diverse onderdelen van de formule:

  1. selecteer cel E3
  2. kies in de menutab Formules in het blok Formules controleren de optie Formule evalueren
  3. kijk welk gedeelte van de formule onderstreept is, druk dan op Evalueren en bekijk het resultaat
  4. herhaal stap 3 totdat de hele formule geëvalueerd is.

In cel E4 de naam van de baas opzoeken is nu een ‘peulenschil’:

=INDEX(tblData[Medewerkers];E3*2)


 

Van rijen naar kolommen

Iemand vroeg mij vorige week of ik kon helpen:

“Ik heb een bestand met leningnummers. Daar staan ook polisnummers en poliswaarden in, maar ze staan per lening onder elkaar; ik wil voor iedere lening 1 regel met daarachter alle polisgegevens.”

Nog net voordat ik begon te lachen (“Dat is zo geregeld!”, dacht ik) hoorde ik dat het bestand in Access stond. Oeps, dat wordt lastiger.
Ik zal alvast verklappen: het heeft me wel wat hoofdbrekens gekost. Ik denk ook nog steeds dat het simpeler moet kunnen. Dus bij deze een oproep aan Access-kenners: kom met een betere oplossing!

Excel-oplossing

Rijen1Voordat we het probleem in Access gaan aanpakken, eerst maar even een oplossing in Excel; dat is iets makkelijker!

Hiernaast staat een voorbeeldje van het bestand: zoals te zien is horen bij LenNr=1 meerdere Polisnr’s etc.

In het Excel-voorbeeldbestand zijn deze gegevens in het tabblad Basis terug te vinden.

In het tweede tabblad (Tussen) is een kopie gemaakt van deze gegevens en heb ik de kop van de tabel al uitgebreid met Polis1, Waarde1, Polis2 etc. In het voorbeeld is het maximaal aantal polissen per lening 4, maar ik heb al ruimte gemaakt voor 5 polissen:

Rijen2

Via wat handige formules kunnen we het bestand uitbreiden; maar zorg wel eerst dat alle regels van dezelfde lening onder elkaar komen, dus sorteren op LenNr.

  1. onder het kopje Polis1 komt het polisnummer van de eerste lening; eigenlijk zouden we in die cel (E3) kunnen verwijzen naar C3, maar ik heb daar een formule geplaatst, die direct naar beneden gekopieerd kan worden:
    =ALS(B3<>B2;C3;””)
    Dus als in kolom B een ander LenNr staat dan in de vorige regel, dan de inhoud van kolom C kopiëren, anders de cel leeg laten (2 “-tekens achter elkaar).
  2. onder Waarde1 (cel F3 dus) staat de formule
    =ALS(E3<>””;D3;””)
    Als in de vorige stap een polisnummer in ingevuld dan ook de waarde daarvan ophalen.
  3. Dan onder Polis2 (cel G3):
    =ALS(E3<>””;ALS(B4=B3;C4;””);””)
    We willen alle polisnummers op eenzelfde regel krijgen, dus Polis2 mag alleen maar ingevuld worden als er ook een Polis1 is (ALS(E3<>””).
    Daarna vergelijken we het LenNr van de huidige regel met de volgende regel; als dat hetzelfde nummer is dan halen we het betreffende polisnummer op
  4. Voor Waarde2 geldt hetzelfde als hiervoor bij Waarde1.
  5. De formule onder Polis3 mag niet meer verrassend zijn:
    =ALS(G3<>””;ALS(B5=B4;C5;””);””)
  6. Ook de andere kolommen krijgen vergelijkbare formules en dan alles naar beneden kopieren.Rijen3
Bijna klaar: alle polissen zijn mooi van de rijen naar de kolommen verplaatst, maar we hebben nog wat overbodige regels over:

Rijen4

  1. klik met de muis ergens in de tabel met gegevens
  2. kies de menutab Gegevens
  3. en binnen het vak Sorteren en filteren de optie Filter
  4. klik op het ‘vinkje’ achter Polis1 (de filteropties voor deze kolom) Rijen5
  5. zorg dat helemaal onderaan het vinkje bij (Lege cellen) uit staat
  6. selecteer de overblijvende gegevens en kopieer die naar een ander tabblad (zie Resultaat)

Access

Nu gaan we hetzelfde doen in Access.
In het Access-voorbeeldbestand (dit is gezipt, omdat de download anders vast kan lopen) staat hetzelfde basisbestand, in de tabel Basis.

Rijen6Om problemen te voorkomen bij het wegschrijven van de tussenstappen, creëren we eerst de Tussen-tabel met per veld het juiste gegevenstype.
NB Wanneer we Access de tabel laten aanmaken via een Tabelmaak-query, gaat Access ‘gokken’ welke type het veld moet krijgen; dat strookt lang niet altijd met onze wensen!

Rijen7Dan de eerste stap: via een query selecteren we van alle leningnummers het kleinste polisnummer (had ook de maximum-waarde kunnen zijn).
Via de button Rijen8 wordt dit een Toevoeg-query; records toevoegen aan de hiervoor aangemaakte tabel Tussen.

NB Access groepeert zelf per leningsnummer; de records hoeven dan ook niet gesorteerd te zijn, in tegenstelling tot de Excel-oplossing.

Het ophalen van een mogelijk tweede polisnummer is al wat ingewikkelder:

Rijen9Vanuit Basis selecteren we opnieuw de minimale waarde voor het polisnummer, maar via de zogenaamde outer-joins naar de Tussen-tabel wordt er voor gezorgd, dat combinaties van LenNr en PolisNr, die al voorkomen, niet meer meedoen (zie bij Criteria de voorwaarde Is Null).

Ook voor de volgende polisnummers zijn op vergelijkbare manier query’s gebouwd, waarbij meerdere koppelingen met dezelfde Tussen-tabel zijn aangemaakt.

Rijen10De tussentabel bevat nu records, waarbij leningnummers nog wel in verschillende records voorkomen, maar waar wel al de polisnummers op de ‘juiste’ plaats staan.

Ook missen we de poliswaardes nog in de tussentabel. Dat is opgelost door 5 Bijwerk-query’s, waarmee vanuit de Tussen-tabel aan de hand van LenNr en PolisNr de overeenkomende waarde in Basis wordt opgehaald:
Rijen11Rijen12
Om het gewenste overzicht te krijgen (alle leningnummers uniek met daarachter één of meerdere polisnummers) hebben we nog een laatste query nodig.

Transponeren

Voor diegene, die zich door de titel van het artikel (Van Rijen naar Kolommen) hebben laten misleiden, nog een toegift:
soms wil je in Excel gegevens, die onder elkaar staan, naast elkaar hebben of andersom. Hiervoor zijn geen ingewikkelde formules nodig: Excel heeft daar een ingebouwd commando voor:

  1. selecteer in het Excel-voorbeeldbestand alle relevante gegevens in het tabblad Basis
  2. klik met de rechter muisknop en kies Kopiëren
  3. selecteer de cel waar het resultaat moet komen; dit kan ook in een ander tabblad of in een andere werkmap zijn.
  4. Rijen13klik daar opnieuw met de rechter muisknop
  5. en kies nu Plakken speciaal…
  6. in het vervolgscherm staat een optie Transponeren; zet het vinkje aan en kies OK