Tagarchief: CSE

Wie, wat, waar, hoe?

Excel, , , ,
Voorbeeldbestand: VrWoorden.xlsxDownloaden

Ik kwam pas een (oud) artikel van Chandoo tegen, waarin hij aangeeft dat hij af en toe door de bomen het bos niet meer ziet: zijn artikelen en video’s genereren zoveel reacties, dat hij niet snel meer ziet of er vragen bijzitten die beantwoord moeten worden.

Zijn oplossing gaan we in dit artikel van G-Info eens onder de loep nemen.

Probleem

Chandoo wilde dus snel zijn reacties filteren, zodat hij alleen vragen overhield. Door alleen te selecteren op het feit of een zin een vraagteken bevat, levert meestal niet het gewenste resultaat op (zoals uit het simpele voorbeeld hiernaast mag blijken).

Zijn oplossing was om om te onderzoeken of bepaalde woorden voorkomen in de reacties.
Dat bleek nog niet voldoende: ook bepaalde combinaties van woorden kunnen aangeven dat het een vraag betreft:

Oplossing

Om te controleren of een zin een vraag bevat, moeten we analyseren of één of meerdere ‘Vraag-woorden‘ in de zin voorkomen.

In het Voorbeeldbestand zijn de woorden die aangeven of we te maken hebben met een vraag opgenomen in een Excel-tabel met de naam tblVrWoorden (zie kolom B in het tabblad Vraag). De tabel-kolom met woorden heeft de naam Vraag-woorden.

NB voor uitleg over de voordelen van en toelichting op het gebruik van Excel-tabellen zie de artikelen Kunst en Excel en Tabellen (deel 2).

In cel E3 staat de formule =AANTAL.ALS(D3;”*”&tblVrWoorden[Vraag-woorden]&”*”)

Ofwel: bepaal het aantal keren dat één van de mogelijke Vraag-woorden voorkomt in D3.

Controleer de werking van de formule in E3 door middel van de menu-optie Formule evalueren in de menutab Formules.
Klik op Evalueren en Excel zal de stap die onderstreept is uitvoeren:

Blijf op Evalueren klikken om alle berekeningstappen na te lopen. Doe je dit ook voor de cellen E4, E5 etcetera dan is duidelijk te zien dat Excel telkens maar één vraagwoord onder de loep neemt in plaats van de hele reeks.

NB de functie Aantal.Als herkent het gebruik van wildcards; door vóór en achter de vraagwoorden een * te plaatsen geven we aan, dat er nog iets vóór of achter het woord (of woordcombinatie) mag staan ofwel dat zo’n woord willekeurig ergens in de tekst kan voorkomen.

Maar doe nu eens het volgende:

  1. selecteer cel E6
  2. klik in de formulebalk achter de formule
  3. druk op het toetsenbord op de functietoets F9 (Berekenen)
  4. de formule verandert in: ={0;0;0;1;0;0;1;0;0;0;0}
  5. druk NIET op Enter maar op Esc

NB je kunt ook een gedeelte van een formule laten berekenen: selecteer bijvoorbeeld in de formulebalk alleen het gedeelte achter de punt-komma en vóór het haakje-sluiten en druk dan op F9.

Nu is te zien, dat Excel toch slimmer is dan we dachten! Hij heeft wel alle Vraag-woorden vergeleken met de inhoud van cel D6 en geconstateerd dat er 2x sprake is van een vraag (de 4e en 7e optie). De functie Aantal.Als levert een array van resultaten op en daar kunnen we gebruik van maken.

In kolom F bepalen we de Som van de waardes uit de array, die door Aantal.Als is gegenereerd.

LET OP we moeten Excel wel ‘influisteren’ dat we te maken hebben met een array-berekening: sluit de formule NIET af met Enter maar met Ctrl-Shift-Enter, de zogenaamde CSE-methode.

Een alternatieve oplossing, waarbij de CSE-methode niet nodig is, is het gebruik van de functie SomProduct (zie kolom G). Lees ook het artikel SOMPRODUCT: meer dan SOM en PRODUCT.

Ander voorbeeld

Een garage heeft een goederenoverzicht met codes en omschrijvingen (zie het tabblad Onderdelen van het Voorbeeldbestand).
Om de efficiency en de consistentie te waarborgen wordt afgesproken dat aan de servicecorner deze codes ook gebruikt moeten worden bij het aanmaken van de werkbonnen.

Om verschil te maken met bijvoorbeeld klantnummers wordt vóór een goederencode een # geplaatst.

In kolom C kunnen we met een vergelijkbare formule als hiervoor beoordelen of er sprake is van een onderdeel (let op het # achter het eerste sterretje).

Als we de garage dan toch automatiseren: in kolom D zoeken we, als er sprake is van een Onderdeel, het betreffende nummer op:
=ALS([@[Onderdeel?]]=”Ja”;WAARDE(DEEL([@Opmerking];VIND.ALLES(“#”;[@Opmerking])+1;7));””)
en in kolom E de daarbij behorende omschrijving:
=ALS([@[Onderdeel?]]=”Ja”;INDEX(tblOnderdelen[Omschrijving];
VERGELIJKEN([@[Welk?]];tblOnderdelen[Goederencode];0));””)

Nog een voorbeeld

Een school werkt niet met cijfers voor proefwerken met met lettercodes (zie het tabblad Proefwerk van het Voorbeeldbestand).

Ook ligt in een tabelletje vast welke codes overeenkomen met een voldoende.

Nu wordt per leerling in kolom P de tabel met voldoende-codes vergeleken met de 10 proefwerkkolommen en wordt het aantal voldoendes bepaald. Nog wat Voorwaardelijke opmaak en we zien snel hoe de diverse leerlingen er voor staan.

LET OP dit is weer een CSE-formule.

NB1 telt nv niet mee als voldoende (de betekenis is ineens niet voldoende 😉) dan verwijderen we de betreffende rij in de tabel tblVoldCd. Het resultaat in kolom P past zich direct aan.

NB2 om te zien wat de invloed is van de proefwerk-resultaten op kolom P staat vanaf rij 10 in het tabblad Proefwerk een overzicht waarbij de resultaten bij iedere druk op de F9-toets willekeurig veranderen.

Cadeaus

Decembermaand is de tijd van cadeaus. Voor de liefhebbers hier 2 links:

  1. Chandoo: uitleg van diverse ‘interessante’ formules
  2. How to Excel: een overzicht van diverse Excel-websites

Niet-aaneengesloten bereiken

Excel, , , , ,
Voorbeeldbestand: Bereiken.xlsxDownload

Een niet-aaneengesloten bereik is een selectie van meerdere cellen waarbij ieder gedeelte uit één of meer cellen kan bestaan, maar waarbij die cellen niet allemaal op elkaar aansluiten.

Hieronder gaan we kijken hoe je dit soort bereiken kunt selecteren en wanneer je ze in formules wèl, maar zeker ook wanneer je ze niet kunt gebruiken. En uiteraard zoeken we ook oplossingen voor dat laatste.

Cellen selecteren

Een aaneengesloten gebied van cellen selecteren gaat het makkelijkst met behulp van het toetsenbord: gebruik de cursor-toetsen om de eerste cel te selecteren, hou Shift ingedrukt en gebruik dan weer de cursortoetsen om het hele gebied te selecteren.

Wanneer je een niet-aaneengesloten gebied wilt gebruiken dan zul je de muis ter hand moeten nemen:

  1. selecteer de eerste cel van het bereik door met de linker muisknop te klikken
  2. met de linkermuisknop ingedrukt kun je eventueel nog meer AANSLUITENDE cellen selecteren
  3. hou de Ctrl-toets ingedrukt en selecteer ergens anders één of meer aaneengesloten cellen
  4. herhaal punt 3 zoveel als nodig

Functies en niet-aaneengesloten bereiken

Op het tabblad SubTot van het Voorbeeldbestand vindt u een kwartaaloverzicht van enkele afdelingen.

In kolom G3 staat het jaartotaal van de afdeling Dir:

  1. tik in =som(
  2. klik met de linkermuisknop op cel C3
  3. ga, met de muisknop ingedrukt, naar cel F3
  4. druk op Enter

NB1 Excel vult automatisch het haakje-sluiten aan

NB2 tussen haakjes komt automatisch C3:F3, ofwel alle cellen van C3 tot en met F3

NB3 een snellere methode om van een naast-gelegen bereik de Som te bepalen: plaats de cursor in cel G3 en druk op de toetscombinatie Alt en =

Maar wanneer we alleen de cellen opgeteld willen hebben waarvan de waardes groter dan 10 zijn dan wordt het lastiger:

  1. tik in cel H3 =som(
  2. klik met de linkermuisknop op cel D3
  3. ga, met de muisknop ingedrukt, naar cel E3
  4. druk op Enter

Nu voor cel H4:

  1. we beginnen weer met =som(
  2. klik op cel D4
  3. klik dan, met Ctrl ingedrukt, op cel F4
  4. druk op Enter
  5. het resultaat is: =SOM(D4;F4)

NB tussen haakjes staat nu een ; als scheidingsteken. Voor Excel betekent dit dat aan de functie 2 parameters worden meegegeven. In dit geval bestaat iedere parameter uit 1 cel.

Voor cel H5:

  1. start met =som(
  2. klik op cel C5
  3. klik dan, met Ctrl ingedrukt, op cel E5
  4. verschuif de cursor, nog steeds Ctrl ingedrukt, naar F5
  5. druk op Enter
  6. het resultaat is: =SOM(C5;E5:F5)

NB ook hier worden aan de Som-functie 2 parameters meegegeven; de eerste bestaat uit 1 cel, de tweede uit een bereik van meerdere cellen.

Op een vergelijkbare manier kun je ook het totaal van alle aantallen groter dan 10 bepalen (zie cel C9).

NB1 uiteraard is de berekening in cel C10 een stuk eenvoudiger: =SOM(H3:H7)

NB2 plaats de cursor achter de formule en je hebt een gekleurd controlemiddel om te zien of de juiste cellen zijn geselecteerd:

In cel C12 is te zien, dat ook de functie Gemiddelde met niet-aaneengesloten bereiken overweg kan.

LET OP: voor deze functie kun je NIET de kortere formule =GEMIDDELDE(H3:H7) gebruiken!

Maar …. bovenstaande formules werken alleen maar in een statische situatie. Wanneer de kwartaalcijfers nog kunnen wijzigen hebben we een probleem; dan moeten alle formules gecontroleerd en eventueel aangepast worden.

In het tabblad Aselect van het Voorbeeldbestand ziet u een formule waarbij wijzigingen in de brongegevens geen problemen meer opleveren:
=SOM.ALS(C3:F3;”>10″)

Ofwel: sommeer alle getallen in het bereik C3:F3 die groter dan 10 zijn.

LET OP de voorwaarde waaraan de cellen moeten voldoen, moet tussen aanhalingstekens staan. Wel kan de voorwaarde in een cel worden opgenomen; dan blijven de aanhalingstekens achterwege; zie cel H3: =SOM.ALS(C3:F3;$G$2)

Het bepalen van de Som of Gemiddelde van alle waardes onder een voorwaarde is op deze manier ook een stuk eenvoudiger: =GEMIDDELDE.ALS(C3:F7;”>10″)

Bij voorgaande Als-formules was het bereik waar de voorwaarde op los gelaten moest worden aaneengesloten. Wanneer dat niet het geval is hebben we een probleem!

Probleem

In het tabblad Probleem van het Voorbeeldbestand zien we een projectenoverzicht over de maanden. Per maand is er een Raming en een Actueel resultaat. Delta is het verschil tussen die twee (Actueel – Raming). Wanneer de Actuele stand kleiner is dan de Raming is dat gunstig (dus als Delta < 0 is).

NB De Delta-kolommen hebben een voorwaardelijke opmaak gekregen.

Per project willen we het totaal van de negatieve Delta’s weten.

In cel S4 staat een formule, die dit probleem zou kunnen tackelen. De Raming en Actueel-kolommen zijn immers altijd positief (of 0): =SOM.ALS(C4:R4;”<0″)

Maar als we de Correctie-kolom gaan gebruiken en daar komen negatieve waardes voor, dan werkt de formule niet meer als verwacht/gehoopt.

We zouden dan iets willen gebruiken als =SOM.ALS( (E4;I4;M4;Q4) ;”<0″ )
Dus als eerste parameter geen aaneengesloten bereik, maar losse cellen.

Helaas! Excel raakt hier de weg kwijt. Deze functie is heel strikt: de eerste parameter moet een bereik zijn, de tweede moet de voorwaarde bevatten. En de tweede parameter begint achter de eerste punt-komma.

Gelukkig kunnen we dit probleem wel oplossen met de formule in T4:
=SOMMEN.ALS(C4:R4;C4:R4;”<0″;C$3:R$3;”Delta”)
Neem de Som van alle cellen uit C4:R4 (de eerste parameter), waarbij de cellen in C4:R4 (tweede parameter) kleiner zijn dan 0 en de cellen C3:R3 de waarde Delta bevatten.

LET OP bij de cellen C3:R3 is de rij-aanduiding absoluut gemaakt (zie $-tekens), zodat er bij het naar beneden kopiëren de formule naar de juiste rij blijft verwijzen.

Wat te doen, als de kopregel niet zo’n handige aanduiding van de betreffende kolommen heeft? Dan hebben we nog een ‘leuk’ alternatief (zie cel U4):

LET OP dit is een zogenaamde CSE-, array– of matrix-formule; deze wordt niet afgesloten door op Enter te drukken maar tegelijkertijd Ctrl-Shift-Enter. Excel plaats dan automatisch de accolades rond de formule.

Korte uitleg: door de CSE-methode ‘dwingen’ we Excel om (in dit geval) 4 keer de Als-functie uit te voeren; telkens met een andere kolom door middel van de Kiezen-functie.

Functies en niet-aaneengesloten bereiken (conclusie)

Wanneer kun je niet-aaneengesloten bereiken gebruiken binnen een functie?

Kort door de bocht: alleen bij de simpele rekenkundige functies als Som, Aantal, Gemiddelde etcetera.
Bij het invoeren van dit soort functies zie je dat je meer dan 1 dezelfde soort parameter kunt opgeven.

NB de rechte haken geven aan dat de tweede parameter optioneel (niet verplicht) is. De … geven aan, dat er nog meer parameters opgegeven kunnen worden.

Bij andere functies zie je dat de verschillende parameters ieder een ‘andere’ rol vervullen. Logisch dat je dan niet een willekeurig aantal bereiken als 1 parameter kunt opgeven.

Vreemd eigenlijk: als je naar het scherm met Functieargumenten van Som kijkt (gebruik de button naast de formulebalk), dan zie je als toelichting dat je getallen als parameter kunt opgeven. Nergens een woord over bereiken!

Waarschijnlijk toch de meest gebruikte optie van Excel.

Toetje

Voor de liefhebbers bevat het Voorbeeldbestand nog enkele sheets waarmee de opzet van de ‘Probleem’-sheet op een geheel andere wijze wordt aangepakt; de basisgegevens worden in een database-vorm ingevoerd. Het resultaat-overzicht wordt daardoor een stuk flexibeler.

Frequentietabel en histogram

Excel, , , , , , ,
Voorbeeldbestand: Interval.xlsxDownload

Overal kom je ze tegen, histogrammen: een grafische weergave van een frequentietabel.

Iedereen die met Excel werkt, heeft wel eens zoiets gemaakt.

In dit artikel laten we diverse mogelijkheden de revue passeren om zo’n overzicht te maken. We zoomen daarbij vooral in op verschillende methoden om een frequentietabel te maken. Volgende keer komen diverse alternatieven voor het histogram aan bod.

Voor het maken van een frequentietabel is de functie INTERVAL heel erg handig. Maar ook enkele ‘vreemde’ eigenschappen daarvan komen aan bod (zoals beloofd in Excel en kaarten 2).

Basis

Zoals gezegd: je komt ze bijna dagelijks tegen, histogrammen (en dus ook frequentietabellen). Ieder krantenartikel, waarin aantallen voorkomen, wordt wel verduidelijkt (?) met een grafiekje. Maar ook HR-medewerkers zijn er verzot op (leeftijds-, functie- en salarisopbouw kunnen daar goed mee worden geïllustreerd) en ook leerkrachten in het onderwijs gebruiken het veel (om de verdeling van proefwerkpunten te bepalen bijvoorbeeld).

In het Voorbeeldbestand op het tabblad Basis staat een fictief overzicht van de resultaten van 5 proefwerken van 10 leerlingen.
Met behulp van de functie ASELECTTUSSEN worden (bij een wijziging in Excel of het drukken op F9) iedere keer nieuwe resultaten tussen 2 en 10 gegeneerd (inclusief die 2 grenzen).

Op hetzelfde tabblad staat een vergelijkbaar overzicht maar daar krijgen de proefwerkresultaten 1 decimaal. De gebuikte formule wordt dan: =ASELECTTUSSEN(20;100)/10

Deze overzichten gebruiken we in het vervolg van het artikel als voorbeeld-input.

Frequentietabel 1

We beginnen met een proefwerkoverzicht zonder decimalen. Daarvan gaan we een frequentietabel maken: hoe vaak komt ieder cijfer voor.

NB direct is te zien dat het voorbeeld niet echt reëel is: het cijfer 10 komt wel heel vaak voor! Maar hoe de verdeling van de overige cijfers is, is zelfs met dit kleine aantal moeilijker te beoordelen.

Op het tabblad FreqTabel1 van het Voorbeeldbestand staat ons eerste resultaat. In kolom J wordt de absolute frequentie van ieder mogelijk cijfer geturfd:
=AANTAL.ALS($C$3:$G$12;I3)

In cel J3 tellen we het aantal keren, dat de waarde uit I3 voorkomt in het bereik C3:G12.

NB ter controle tellen we onderaan in kolom J de frequenties op, zodat we zeker weten dat alle cijfers zijn meegenomen.
Selecteer de cel onder de frequenties en druk op Alt-= (dus de Alt-toets vasthouden en op = drukken). Deze sneltoets werkt ook als je getallen in een rij wilt optellen.

In kolom K bepalen we de cumulatieve absolute frequentie door bij het vorige resultaat in die kolom het resultaat uit kolom J op te tellen (in cel K4 staat de formule =K3+J4).

De formules in de kolommen L en M, waarin de relatieve en cumulatieve relatieve frequenties worden bepaald, spreken voor zich.

Absolute frequentie 2

Een alternatieve methode voor het bepalen van de absolute frequentie is het gebruik van de Excel-functie Interval (zie het tabblad FreqTabel1 van het Voorbeeldbestand).

Allereerst de volgende waarschuwing: Interval is een ‘vreemde’ functie. Niet alleen de input wordt gevormd door een gebied/bereik van cellen (net als bij Som bijvoorbeeld) ook het resultaat bestaat uit meer dan één getal. Het is een zogenaamde matrix-functie.

  1. selecteer eerst de cellen waar het resultaat moet komen. Interval levert de frequenties op, horend bij een gewenst interval. In dit geval bij de cijfers 1 t/m 10. Dus het resultaat moet uit 10 cellen bestaan.
    LET OP het resultaat van de functie Interval moet altijd in een kolom komen, dus selecteer cellen ONDER ELKAAR. In het voorbeeld de cellen N3:N12.
  2. tik in =interval(
  3. voer dan de eerste parameter van de functie in, de Gegevensmatrix. Dit zijn de brongegevens, in dit geval het bereik C3:G12.
  4. tik in ;
  5. dan komt de tweede parameter, de Interval_verw. Dit moeten cellen zijn die de gewenste intervallen aangeven; in het voorbeeld de cellen I3:I12.
  6. tik in )
  7. sluit de functie NIET af door op Enter te drukken, maar tegelijkertijd op Ctrl-Shift-Enter. Op deze manier wordt een matrix-formule ingevoerd, ook wel een CSE-formule genoemd.
    In de 10 cellen staat nu overal exact dezelfde formule:
    {=INTERVAL(C3:G12;I3:I12)}
    LET OP de accolades hebben we niet zelf ingevoerd; dit is een indicatie dat hier een matrix-formule staat. Onderdelen van een matrix-formule kunnen niet worden gewijzigd, er kunnen geen regels tussengevoegd worden etc. Wil je een wijziging doorvoeren: selecteer alle bij elkaar horende cellen, klik in de Formulebalk en druk op Ctrl-Enter. Nu wordt in alle cellen dezelfde formule ingevoerd, maar niet als matrix-formule en dus horen ze niet meer bij elkaar.
    NB in de Engelstalige Excel-versies heet deze functie Frequency; dit geeft beter de bedoeling van de functie aan dan Interval.

NB1 het is in een matrix-formule niet nodig om de bereiken absoluut (met $-tekens) in te voeren.

NB2 hiervoor hebben we net gedaan of de Interval-functie de frequenties van de diverse cijfers heeft geturfd, maar eigenlijk zijn de waardes in het bereik Interval_verw de te hanteren bovengrenzen van de diverse intervallen. Maar aangezien de proefwerkpunten geen decimalen bevatten, zijn de bovengrenzen ook de enige cijfers die in dat interval voorkomen.

Cumulatieve absolute frequentie 2

In cel O3 van het tabblad FreqTabel1 van het Voorbeeldbestand hebben we de formule
=INTERVAL($C$3:$G$12;I3)
geplaatst.
Er is maar 1 bovengrens van een interval, dus ook maar 1 resultaat. De formule hoeft dan ook niet met CSE afgesloten te worden, een Enter volstaat.

Deze formule turft het aantal keren, dat een getal uit het bereik van de eerste parameter kleiner of gelijk is aan de waarde in cel I3. In dit geval dus <=1; dat komt in het voorbeeld niet voor.
Wanneer deze formule naar beneden gekopieerd wordt krijgen we de gewenste cumulatieve resultaten.

Histogram

Bij een frequentietabel hoort ook de grafische weergave, een histogram.

Maak een grafiek met daarin de absolute frequentie en de cumulatie daarvan, zorg dat op de x-as de intervalgrenzen komen (dus de cijfers 1 t/m 10) en zorg dat het een combinatiegrafiek wordt met de cumulatieve waarden uitgezet op de secundaire as.

LET OP kies bij het gebruik van een secundaire as de maximale waarden van de assen zodanig dat de horizontale rasterlijnen aan allebei de kanten bij weergegeven getallen uitkomen. In dit geval is het maximum rechts 4x groter dan links.

In een volgend artikel zullen we diverse andere methoden voor het maken van een histogram de revue laten passeren met daarbij de voor- en nadelen van de diverse alternatieven.

NB wanneer de brongegevens in de loop van de tijd nog uitgebreid worden, plaats deze dan in een Excel-tabel. Na uitbreiding hiervan wordt automatisch de frequentietabel geactualiseerd (zie het tweede blok op het tabblad FreqTabel1 van het Voorbeeldbestand).
In het voorbeeld was het dan wel logischer geweest als ik de proefwerken in de rijen had geplaatst en de leerlingen in de kolommen!
Maar in mijn-tijd-als-docent was dit wel de indeling in de lerarenagenda:

Frequentietabel 2

We gebruiken hetzelfde soort proefwerkoverzicht als in het vorige hoofdstuk, maar nu met 1 decimaal (zie het tabblad FreqTabel2 van het Voorbeeldbestand).
Dankzij de voorwaardelijke opmaak van Excel zien we snel waar de hoge en lage punten zitten.

Hadden we dat vroeger in de lerarenagenda ook maar zo makkelijk gehad. Toen gebruikten we gekleurde pennen (exacter uitgedrukt: pennen met gekleurde inkt) met alle problemen van dien, als een cijfer achteraf nog aangepast moest worden.

Om de voorwaardelijke opmaak te kunnen toepassen gebruiken we een hulpkolom I; in de cellen I3:I6 staan de cijfers 4, 6, 8 en 10. De regels voor de voorwaardelijke opmaak zien er als volgt uit:

De vorige opmaak leverde wel een bonte kermis op. Het mag wel wat subtieler.
Maar daardoor is het iets moelijker om te zien in welke categorie een cijfer valt.

De gebruikte regels:

Hé, wat vreemd. Alle cijfers krijgen de goede kleur maar zijn allemaal cursief en vet, terwijl dat in de opmaakregels alleen voor de laagste en hoogste punten is ingesteld.
Als deskundige Excel-ler hebt u natuurlijk al gezien hoe dat komt. We nemen als voorbeeld het cijfer 5,6 : dit voldoet niet aan de eerste regel maar wel aan de tweede, dus krijgt het een oranje opmaak.
Maar hij voldoet ook aan de derde regel! Dus zou het cijfer een licht-groene opmaak moeten krijgen, maar dat kan Excel niet: én oranje én licht-groen. Het cijfer krijgt de eerste kleur.
5,6 voldoet ook aan regel 4. Donker-groen maken kan Excel niet meer, maar wel cursief en vet.

We moeten ook de laatste kolom binnen de opmaak-regels gebruiken (Stoppen indien Waar).
Als een cijfer aan een regel voldoet dan worden de volgende regels niet meer uitgevoerd.

NB1 Het laatste vinkje hoeft niet meer; de opmaak-routine stopt toch al

NB2 bij het opmaken van het ‘kermis-overzicht’ is de stop-optie niet nodig omdat ook hier geldt dat Excel maar één opvulkleur aan een cel kan toewijzen.

Even genoeg over de opmaak; dit artikel gaat over frequentietabellen.
Om de proefwerkresultaten met decimalen in de juiste categorie onder te brengen gebruiken we weer de functie Aantal.Als.

Maar wel iets ingewikkelder dan hierboven: in cel K3 staat de formule
=AANTAL.ALS($C$3:$G$12;”<=”&I3)
Turf het aantal getallen in het bereik C3:G12, die voldoen aan de voorwaarde dat ze kleiner of gelijk zijn aan de waarde in cel I3.

LET OP de voorwaarde moet een tekst-vorm hebben: dus de tekst <= (zie de “-tekens) wordt met behulp van & samengevoegd met de inhoud van cel I3.

Deze is formule is naar beneden gekopieerd. Helaas, nu hebben we niet de frequenties maar de cumulatieven.
Geen nood, in kolom L gaan we de frequenties bepalen. De eerste berekening is oké. Dus cel L3 is hetzelfde als K3.
In cel L4 plaatsen we de formule =AANTAL.ALS($C$3:$G$12;”<=”&I4)-L3. Maar die voldoet niet als we die “naar beneden kopiëren”: we hadden =AANTAL.ALS($C$3:$G$12;”<=”&I5)-SOM($L$3:L3) moeten gebruiken.

In kolom M hebben we nog een alternatieve berekening voor de frequenties: M3 is weer gelijk aan K3, in M4 staat de formule =AANTALLEN.ALS($C$3:$G$12;”<=”&I4;$C$3:$G$12;”>”&I3)
Dus een Als met 2 voorwaarden, dat kan alleen met de functie AantalLEN.als.
Deze formule kan naar beneden gekopieerd worden.

NB wil je toch Aantal.Als gebruiken: in cel M4 had ook de formule
=AANTAL.ALS($C$3:$G$12;EN(“<=”&I4;”>”&I3))
kunnen staan. Hierbij zijn met behulp van de functie EN 2 voorwaarden gecombineerd tot 1.

Zoals uit de vorige alinea’s mag blijken is het maken van een frequentietabel met behulp van Aantal.Als niet altijd even makkelijk. We mogen blij zijn dat Excel de functie Interval kent:

  1. selecteer de cellen N3:N6
  2. tik in de de formulebalk in: =INTERVAL(C3:G12;I3:I6)
  3. druk op Ctrl-Shift-Enter
  4. klaar!

NB1 niet tevreden met de grenzen van de intervallen? Is alles boven 5,5 een voldoende? Wijzig de inhoud van cel I4 in 5,5 en de hele sheet wordt automatisch aangepast. Niet alleen de frequentietabellen maar ook de voorwaardelijke opmaak.

Maar wat gebeurt er als je de 10 in cel I6 wijzigt in 9? Dan worden niet alle punten in de frequentietabel meegeteld; dat is niet de bedoeling. Gelukkig heeft Microsoft daar rekening mee gehouden.

Ter verduidelijking bevat het tabblad FreqTabel2 nog een ander overzicht:

  1. selecteer de cellen J10:J22
  2. tik in de de formulebalk in: =INTERVAL(C3:G12;I10:I19)
  3. druk op Ctrl-Shift-Enter

Bewust is het resultaatbereik groter gekozen dan we gewend zijn; de eerste 10 regels zijn logisch: eerst wordt het aantal proefwerkresultaten kleiner of gelijk aan 1 geturfd (0 dus), dan hoeveel groter dan 1 en kleiner of gelijk aan 2 etc.
Maar dan komt er nog een resultaat; in dit geval 0. Excel levert via de Interval-functie altijd één waarde meer dan het aantal gedefinieerde intervallen. Hier wordt geteld hoeveel brongegevens groter zijn dan de hoogst gedefnieerde intervalgrens.
Wijzig de 10 in bijvoorbeeld 9,5 en je ziet het volgende resultaat:

De laatste 2 regels laten zien, dat de Interval-functie geen resultaat meer oplevert, dus we hadden hiervoor bij punt 1 beter de cellen J10:J20 kunnen kiezen.

Interval-functie

Laten we de Interval-functie nog eens even verder onder de loep nemen.
In het tabblad Interval van het Voorbeeldbestand ziet u weer dezelfde brongegevens staan. Op basis van de 10 intervalgrenzen in kolom I zijn de bijbehorende frequenties in kolom J met behulp van de Interval-functie bepaald, inclusief 1 extra cel (en 2 niet-nuttige cellen).

De frequentietabel in de kolommen L en M laat zien dat de Interval-functie ook werkt wanneer de grenzen in de volgorde hoog-laag staan (in de kolom M staat de formule .

En nog mooier: de intervalgrenzen mogen ook willekeurig door elkaar staan (zie de kolommen O en P)! Maar wanneer zou je zoiets nu doen?

De kolommen R en S laten nog een andere eigenschap van de functie zien: wanneer een interval een tweede keer voorkomt dan wordt de bijbehorende frequentie 0, zodat we geen dubbeltelling krijgen (dit geldt ook als zo’n grens nog vaker voorkomt). Consequentie is wel dat de frequenties van andere intervallen veranderen. En dat is natuurlijk terecht omdat de getallen in kolom R de bovengrens van een interval aanduiden.

Interval-functie 2

Hiervoor hebben we al regelmatig de functie Interval gebruikt, waarbij de werking (na wat oefening) ‘normaal’ begint aan te voelen.

We oefenen nog even met een nieuw voorbeeld (zie het tabblad Interval2 van het Voorbeeldbestand): van enkele leerlingen hebben we scores verzameld. De data zijn vastgelegd in een Excel-tabel met de naam tblLLscore.
Het aantal records staat onder de kolom LLnr, daarnaast is het totaal van de scores bepaald met de formule .
De twee totalen zijn (bijna) automatisch gegenereerd door in de menutab Hulpmiddelen voor tabellen de Totaalrij te activeren:

Een overzicht van de verdeling van de scores is nu snel gemaakt:

  1. plaats de scores 0 tot en met 10 in een kolom (hier kolom E)
  2. selecteer daarnaast de cellen in kolom F, tik in de formule =INTERVAL(tblLLscore[Score];E3:E13) en druk op Ctrl-Shift-Enter
  3. in kolom G sommeren we de scores als de score overeenkomt met cel E3, E4 etc.
  4. onderaan sommeren we de resultaten van de kolommen F en G

LET OP de resultaat-tabel in de kolommen E:G kan niet de vorm van een Excel-tabel krijgen; Excel staat een combinatie van zo’n tabel en een matrix-formule niet toe!

We weten nu hoe de verdeling over de verschillende scores is, maar hoe is de verdeling over de leerlingen?
In het voorbeeld is het aantal leerlingen beperkt; we zien in één oogopslag dat alleen de nummers 1 tot en met 5 voorkomen. Maar hoe weet je dat wanneer de bron veel uitgebreider is? Een oplossing is het gebruik van Filter:

  1. kies in de menutab Gegevens in het blok Sorteren en filteren de optie Geavanceerd
  2. vul het scherm van het Uitgebreid filter in zoals hiernaast
  3. klik op OK

Het klopt, er zijn 5 verschillende leerlingennummers.
We hebben de kolom I aangevuld met J en K en daar de Excel-tabel tblLLresult van gemaakt.
In kolom J staat de formule =AANTAL.ALS(tblLLscore[LLnr];[@LLnr]) en in K: =SOM.ALS(tblLLscore[LLnr];[@LLnr];tblLLscore[Score])

En de tabel heeft ook een Totaalrij gekregen.

Een andere methode om vanuit de basis tot een leerlingen-overzicht te komen is door het gebruik van Power Query.
In de kolommen M:O staat het resultaat daarvan.

Wat gebeurt er als er nieuwe scores bij komen?

  1. selecteer met de cursor cel C16 (de laatste score)
  2. druk op de Tab-toets
  3. de Totaalrij van de tabel verschuift automatisch naar beneden
  4. voeg nu nieuwe gegevens toe: leerling 3 heeft een 7 gescoord.

Alle overzichten zijn overeenkomstig aangepast. Het resultaat van Power Query hebben we wel moeten Vernieuwen (rechts klikken op één van de cellen in M:O).

Voeg op dezelfde manier voor leerlingnummer 6 de score 8 toe:

Helaas: ons leerlingenoverzicht is niet meer compleet. We zullen handmatig het leerlingnummer 6 moeten toevoegen om alles weer kloppend te maken. Fijn dat we controle-totalen hebben toegevoegd!

NB misschien toch handiger om voor dit soort overzichten draaitabellen te gebruiken?

Interval-functie-3a

Maar de functie heeft een speciale eigenschap, die goed gebruikt kan worden bij het turven van het aantal unieke gegevens (en dat was uiteindelijk de aanleiding voor dit artikel).

Op het tabblad Uniek van het Voorbeeldbestand hebben we de kolom met leerlingnummers gekopieerd naar kolom B.

Daarnaast hebben we met behulp van de Interval-functie een frequentieoverzicht voor deze leerlingen gemaakt door de Gegevensmatrix en Interval_verw gelijk te maken.

Leerling 1 komt 4 keer voor, de volgende 3 keer etc.
Komt het leerlingnummer nogmaals voor dan is de bijbehorende frequentie 0; we krijgen dus geen dubbeltellingen!

Door alle frequenties in kolom C op te tellen weten we dus ook het totaal aantal vastgelegde scores.

Maar hoe bepalen we nu het aantal unieke leerlingnummers? Met de volgende formule is dit eenvoudig:
LET OP om Excel te ‘dwingen’ om de ALS uit te voeren voor alle cellen in C3:C16 moet de formule afgesloten worden met Ctrl-Shift-Enter.

We hebben de frequenties van kolom C niet per se nodig. In cel G3 staat de formule =SOM(ALS(INTERVAL(B3:B16;B3:B16)>0;1;0)) die ook het juiste aantal unieke leerlingen oplevert.
NB deze formule kan met Enter afgesloten worden; blijkbaar snapt Excel door het gebruik van Interval dat hij/zij de ALS vaker moet uitvoeren.

Nog korter (in cel H3): =SOM(1*(INTERVAL(B3:B16;B3:B16)>0))

NB1 denk aan de extra haakjes na 1*.

NB2 wil je kijken hoe deze formules werken? Gebruik de optie Formules/Formules evalueren.

Interval-functie-3b

Maar de weg van een Excel-ler is niet altijd even geplaveid, dat weet u ongetwijfeld.
Wanneer we niet te maken hebben met leerlingnummers maar met namen (zoals in het tabblad Uniek2 van het Voorbeeldbestand) dan werkt de Interval-functie niet meer!

Gelukkig is daar wat op te vinden. Ter verduidelijking hebben we in kolom E een formule staan met de functie Vergelijken: vergelijk de naam van de leerling in dezelfde regel (zie de @) met ALLE namen in de Leerling-kolom. Als die te vinden is (en dat is hier natuurlijk altijd zo!) dan levert de functie de positie op van deze naam; komt de naam vaker voor dan wordt telkens dezelfde positie opgeleverd.
Op deze manier vertalen we de namen dus naar getallen, waarbij dezelfde namen dezelfde getallen opleveren. U voelt het al aankomen: op deze hulpkolom kunnen we wel de Interval-functie loslaten!

We hebben de hulpkolom E niet nodig: zoals te zien is levert de formule in cel J3 direct de juiste informatie.

Scrollen in dashboard

Excel, VBA (Visual Basic for Applications), , , , ,
Voorbeeldbestand: DatumVerschil.xlsxDownload

LET OP: na het downloaden de extensie wijzigen in xlsb

Excel wordt steeds vaker gebruikt om het management via dashboards van actuele informatie te voorzien.
Maar ook in dit soort rapportages is de ruimte beperkt. Daarnaast is niet iedere manager geïnteresseerd in dezelfde informatie.

Dit soort problemen is handig op te lossen door gebruikers in de digitale rapportages door de overzichten te laten scrollen.

In dit artikel komen enkele methoden aan bod hoe dit te realiseren. En als we toch bezig zijn: met een beetje VBA kunnen we de overzichten ook makkelijk sorteren.

Basis-materiaal

We hebben een overzicht van afdelingen (het tabblad Data in het Voorbeeldbestand bevat 30 regels) met per afdeling het toegekende kosten-budget (altijd 100.000), de werkelijk gemaakte kosten, het aantal uitgebrachte offertes en het gerealiseerde aantal contracten. Daarnaast bevat het overzicht nog enkele KPI’s: kosten/budget, kosten/offertes en kosten/contracten.

De gegevens zijn opgeslagen in de vorm van een Excel-tabel met de naam tblAfd. In cel L2 (met de naam AantAfd) wordt het aantal afdelingen in de tabel geteld:
=AANTALARG(tblAfd[Afdeling])
Dit aantal kunnen we later goed gebruiken om bepaalde zaken te automatiseren.

NB de nummering van de afdelingen is zodanig dat er altijd 2 cijfers gebruikt worden; dit om een juiste sortering te krijgen. Anders krijg je Afd1, Afd10, Afd11, … , Afd2, Afd20 etc.

Overzicht 1

Wanneer nou blijkt, dat we in een bepaalde rapportage slechts ruimte voor 10 regels hebben; welke afdelingen (van de 30) moeten we dan laten zien? Dat hangt natuurlijk af van de wensen van de ontvangers en die kunnen wel eens tegenstrijdig zijn.
Daarom gaan we nu het overzicht met 10 regels zo aanpassen, dat hierbij gekozen kan worden welke regels zichtbaar zijn.

In het tabblad SelIndex van het Voorbeeldbestand geven we in cel C2 (met de naam Start) aan, welke regel van de 30 als bovenste moet worden weergegeven; de volgende 9 worden ook getoond.

In cel F3 staat de formule:
=INDEX(tblAfd[Afdeling];RIJ()-RIJ(AfdKop)+Start-1)
Ofwel: zoek met de functie INDEX in de kolom Afdeling van de tabel tblAfd die rij op die overeenkomt met de RIJ() van (in dit geval) cel F3, minus het rij-nummer van de kop van het overzicht plus de waarde van de cel Start.

NB1 cel F2 heeft de naam AfdKop gekregen.

NB2 voor het bepalen van de juiste regel hadden we natuurlijk ook een hulpkolom kunnen gebruiken met daarin de waardes 1 t/m 10. De gebruikte berekening maakt het mogelijk om de hulpkolom weg te laten en is flexibel genoeg om het overzicht eventueel later nog te verplaatsen. Ook kunnen we deze formule zonder verdere aanpassingen naar beneden kopiëren.

NB3 de formule kan ook naar rechts gekopieerd worden; Excel wijzigt de kolom-verwijzing Afdeling automatisch naar Budget etc.

NB4 om de juiste regel te selecteren moeten we de berekening nog corrigeren met -1.

Wanneer nu de waarde in de cel Start wordt gewijzigd zal het overzicht zich automatisch aanpassen.

Dat aanpassen kan natuurlijk nog veel mooier en makkelijker met behulp van een schuifbalk:

  1. kies in de menutab Ontwikkelaars in het blok Besturingselementen de optie Invoegen
  2. in het vervolgmenu kiest u binnen het blok Formulierbesturingselementen de optie Schuifbalk
  3. de cursor wordt dan een kruisje; teken, met de linkerknop ingedrukt, de gewenste vorm van de schuifbalk op de gewenste plaats (dit kan allemaal later nog aangepast worden).
  4. klik met de rechtermuisknop op de nieuwe schuifbalk en kies Besturingselement opmaken
  5. zorg dat een koppeling met cel C2 wordt gemaakt (u kunt hier ook de naam Start gebruiken), de minimumwaarde wordt 1 en het maximum 21.

LET OP kies NIET een ActiveX-besturingselement; deze leveren in de praktijk nogal eens crashes van Excel op.

NB als je de schuifbalk wilt verplaatsen of de grootte corrigeren, klik dan eerst rechts op de schuifbalk.

Overzicht 2

Een andere methode om zo’n overzicht met de juiste gegevens te vullen is door gebruik te maken van de functie VERSCHUIVING.

In het tabblad SelVersch1 van het Voorbeeldbestand ziet u in cel F3 de formule:
=VERSCHUIVING(tblAfd[[#Kopteksten];[Afdeling]];
RIJ()-RIJ(AfdKop)+Start-1;
KOLOM()-KOLOM(AfdKop))
ofwel: haal de waarde op uit de cel, die gevonden wordt door vanuit de Afdelings-koptekst van de tabel tblAfd een aantal rijen naar beneden te gaan en een aantal kolommen naar rechts.

NB1 de constructie na het eerste (-haakje hoeft u niet zelf in te tikken; klik gewoon op de betreffende cel en Excel vult de formule vanzelf aan.

NB2 de ingegeven minimum- en maximumwaardes voor de schuifbalk zorgen er voor dat het overzicht geen blanco regels zal bevatten. Helaas is dat niet het geval als in de cel Start een te grote of te kleine waarde wordt ingevoerd.

Overzicht 3

De functie VERSCHUIVING kent nog meer parameters/argumenten. Daar hebben we in het overzicht op het tabblad SelVersch2 van het Voorbeeldbestand gebruik van gemaakt. Cel F3 bevat de formule:
=VERSCHUIVING(tblAfd[[#Kopteksten];[Afdeling]];Start;0;10;8)
Ofwel: selecteer een bereik van cellen, die, gerekend vanaf de koptekst Afdeling, een aantal rijen gelijk aan Start lager begint. Het begin is 0 kolommen verschoven. Het resulterende bereik moet 10 regels hoog en 8 kolommen breed zijn.

LET OP deze formule levert een blok van 80 cellen op. Daarom moet de formule op een speciale manier worden ingevoerd:

  • selecteer eerst met de muis alle cellen waar het overzicht moet komen (in het voorbeeld de cellen F3 tot en met M12)
  • voer dan bovenstaande formule in
  • druk in plaats van op Enter tegelijkertijd op Ctrl-Shift-Enter (de zogenaamde CSE-methode).
  • Excel plaats dan accolades rond de formule

We willen er ook voor zorgen, dat er geen lege regels komen.
De cel Start heeft daartoe een gegevens-validatie gekregen: bij Toestaan is de optie Aangepast ingevuld en bij Formule:
=EN(Start>0;Start<=AantAfd-9)
Dus de waarde in de cel Start moet aan 2 voorwaarden voldoen: groter dan nul EN kleiner of gelijk aan het aantal regels in de bron (minus 9).

LET OP vergeet het eerste =-teken niet

Maximum in schuifbalk

Maar wat als er nu een afdeling bij komt? De tabel tblAfd wordt automatisch aangepast, de teller van het aantal afdelingen zal ook direct opgehoogd worden, de gegevensvalidatie uit het vorige overzicht zal daarom ook goed werken.
Maar … de schuifbalken hebben nog steeds een maximum van 21.

Alleen met behulp van een (kleine) VBA-routine kunnen we het maximum van de schuifbalk automatisch laten meelopen met het aantal regels in de bron-gegevens.

In het overzicht van tabblad SelVersch3 van het Voorbeeldbestand is deze routine geïmplementeerd. Probeer maar eens uit: voeg een regel in het tabblad Data toe en beweeg de schuifbalk op en neer.

Hoe kun je de eigenschappen van de schuifbalk door VBA laten aanpassen?

  1. klik rechts op de schuifbalk
  2. kies de optie Macro toewijzen
  3. als er nog geen macro aan de schuifbalk gekoppeld is dan kiest u Nieuw, anders Bewerken
  4. vul onderstaande programmacode in en sluit de Visual Basic-editor

NB1 afhankelijk van de Excel-versie kan de omschrijving van de subroutine iets anders zijn, bijvoorbeeld Sub Schuifbalk1_BijWijzigen. Laat de naam staan zoals die door Excel is gegenereerd.

NB2 afhankelijk van de situatie kan er ook sprake zijn van Schuifbalk2 etc.

NB3 VBA is altijd Engelstalig. Dus binnen de subroutine is sprake van een ‘shape’ met de naam Scroll Bar 1.

NB4 met de constructie Range(“AantAfd”).Value wordt de waarde uit de cel met de naam AantAfd opgehaald.

‘Automatisch’ sorteren

Zoals al eerder aangegeven zal niet ieder ontvanger van de rapportage de focus op dezelfde KPI leggen. We maken de rapportage zodanig dat de gebruiker zelf kan aangeven welke sortering de gegevens moeten hebben.

Klik op één van de keuzerondjes en de gegevens worden op de betreffende kolom gesorteerd (zie het tabblad Sortering in het Voorbeeldbestand).

  1. keuzerondjes worden op een vergelijkbare manier als schuifbalken aan het tabblad toegevoegd.
  2. klik rechts op één van de keuzerondjes en kies de optie Besturingselement opmaken
  3. koppel het besturingselement aan een cel in Excel; in het voorbeeld C4 ofwel KolomNr
  4. klik rechts op het eerste keuzerondje en kies de optie Macro toewijzen en zorg dat de volgende routine gekoppeld wordt:

NB in dit geval heeft het eerste rondje al het volgnummer 2.

De macro-toewijzing moet voor ieder keuzerondje apart worden uitgevoerd.

Bovenstaand subroutine roept een andere routine aan, Sortering. Deze ziet er als volgt uit (de basis is gemaakt door een macro op te nemen terwijl de sortering handmatig wordt uitgevoerd):


  • als eerste wordt de variabele Kolom gevuld: op basis van de waarde in cel KolomNr wordt één van de kolomkoppen gekozen
  • met het commando With wordt er voor gezorgd dat alle volgende opdrachten betrekking hebben op een Sort van de tabel (ListObject) tblAfd
  • vorige sorteringen verwijderen
  • nieuwe sortering toevoegen
  • het te sorteren bereik heeft een Header/kop
  • de sortering is niet gevoelig voor hoofd- en kleine letters
  • de sortering-orientatie is verticaal (inhoud van een kolom is bepalend)
  • de PinYin-regel mag ook weggelaten worden; alleen interessant bij Chinese tekens
  • en als laatste wordt de sortering toegepast

Transponeren

Excel, , , , ,
Voorbeeldbestand: Transponeren.xlsxDownload


Transponeren: zoals vaker heeft ook dit woord verschillende betekenissen, afhankelijk van de context.

In de muziekwereld is Transponeren het verplaatsen van een stuk muziek naar een andere toonsoort. De muziek klinkt daardoor hoger of lager. Als je gitaar speelt met een Capo transponeer je ook.

In Excel wordt er mee bedoeld dat we gegevens, die eerst horizontaal zijn weergegeven, zodanig verplaatsen dat het een verticaal overzicht wordt (of andersom).

In dit artikel enkele eenvoudige Excel-transponeer-tips, waaronder een bespreking van de functie TRANSPONEREN en een alternatief met behulp van de veelzijdige functie INDEX.

Transponeren m.b.v. kopiëren/plakken

Laten we maar eens beginnen met een simpele praktijk-situatie (zie het tabblad TransP1 van het Voorbeeldbestand).
In de loop van 2017 hebben we een maandoverzicht gemaakt; geen probleem. Maar nu we weer aan een nieuw jaar zijn begonnen, merken we dat het niet zo handig was om het overzicht “in de breedte” te maken. Binnenkort past het niet meer op een A4. Het overzicht hadden we niet horizontaal moeten maken, maar verticaal.

Dus we moeten alles opnieuw invoeren? Nee, natuurlijk niet! Het toverwoord is transponeren:

  1. selecteer de cellen die omgezet moeten worden; in het voorbeeld B2:P4
  2. klik met de rechter muisknop op de selectie en kies Kopiëren of
    druk op Ctrl-C
  3. klik met de rechter muisknop in de cel waar het nieuwe overzicht moet komen (bijvoorbeeld in cel B6) en kies de button  (de R-toets indrukken geeft hetzelfde resultaat)
    Een andere manier: wanneer u na het rechts-klikken Plakken speciaal …. kiest, dan krijgt u een nieuw venster waarin u de optie Transponeren kunt aanvinken
    Klik nog wel even op OK.
  4. verwijder eventueel de rijen met het oude overzicht.

NB wanneer je in stap 2 Knippen kiest (of Ctrl-X) dan is de optie Transponeren niet beschikbaar

Transponeren met opmaak en formules 1

Wanneer we hetzelfde doen met een overzicht met opmaak en formules kan het resultaat wel eens tegenvallen.
In het voorbeeld op tabblad TransP2 van het Voorbeeldbestand bestaat de opmaak uit dikkere randen, een vet lettertype en getallen die opgemaakt zijn met 2 decimalen en er staan formules in rij 5.
Niet alle opmaak wordt getransponeerd; wel worden formules omgezet (zie bijvoorbeeld cel E12).

Gelukkig hebben we nog wel wat alternatieven achter de hand:

  1. volg in stap 3 (zie hierboven) de omslachtige methode (dus Plakken speciaal kiezen of 2 keer op de punt-toets drukken) en vink in het pop-up-scherm niet alleen Transponeren aan, maar ook de optie Waarden.
    Groot nadeel is dat alle opmaak weg is en er geen formules meer in het overzicht staan, maar harde waardes (zie tabblad TransP2).
  2. volg in stap 3 (zie hierboven) de omslachtige methode (dus Plakken speciaal kiezen of 2 keer op de punt-toets drukken) en vink in het pop-up-scherm niet alleen Transponeren aan, maar ook de optie Alles behalve randen.
    Het enige wat je nu nog moet doen is opnieuw de randen aanbrengen.

Transponeren met opmaak en formules 2

Nog een voorbeeld maar met een formule, die naar een cel buiten het overzicht verwijst (zie het tabblad TransP3 van het Voorbeeldbestand).

Wanneer we dit overzicht Transponeren (met de optie Alles behalve randen) dan zien we iets vreemds in de laatste 2 rijen. De formules leveren fout-meldingen op. Als we naar de ‘bron’ kijken (cellen O7 en P7) dan blijkt daar een relatieve verwijzing naar cel C2 te staan (dus zonder de $-tekens). Bij het Transponeren gaan deze verwijzingen ‘de mist in’.

Wanneer het Transponeren plaats vindt naar een ander tabblad dan zullen alle formules foutieve waardes opleveren: de nieuwe formules verwijzen naar cel C2 in het nieuwe tabblad en daar staat waarschijnlijk geen BTW-percentage.

De handigste oplossing voor dit soort problemen is om zoveel mogelijk Namen te gebruiken in formules:

  1. selecteer cel C2
  2. klik in het Naamvak links boven
  3. vervang daar C2 door de naam BTW en druk op Enter
  4. vervang in de formules in rij 7 alle verwijzingen naar C2 door de naam BTW
  5. Transponeren mag nu geen problemen meer opleveren

NB wanneer je in een formule een verwijzing naar een zelf-gedefinieerde naam gebruikt dan is dit altijd automatisch een absolute verwijzing.

Transponeren met opmaak en formules 3

Nog een voorbeeld: een overzicht per maand en regio. In het tabblad TransP4 van het Voorbeeldbestand kunt u zien dat er ook nog totaal-formules onder en rechts er van staan.

NB Totalen zijn altijd snel aan een dergelijk overzicht toegevoegd:

  1. klik met de muis in een van de cellen van het overzicht
  2. druk op Ctrl-A; alle cellen van het overzicht zullen worden geselecteerd
  3. druk op Shift (vasthouden) en pijl naar rechts; de selectie wordt met 1 kolom uitgebreid
  4. druk op Shift (vasthouden) en pijl naar beneden; de selectie wordt met 1 rij uitgebreid
  5. klik met de muis op 

In een andere rapportage hebben we hetzelfde overzicht nodig, maar dan anders gerangschikt: de maanden in de rijen en de regio’s in de kolommen.

Hierboven hebben we gezien hoe het transponeren, inclusief opmaak en formules in zijn werk gaat.

Het grote nadeel van de methode zit hem in het feit, dat er nu twee overzichten zijn zonder koppeling. Als er cijfers veranderen moeten deze wijzigingen op 2 plaatsen worden doorgevoerd. Dit is natuurlijk fout-gevoelig; daarom zal ik hieronder enkele alternatieve methoden laten zien.

De functie Transponeren

In het tabblad TransP5a van het Voorbeeldbestand is nogmaals het regio-maand-overzicht opgenomen.

Dit overzicht gaan we Transponeren met de Excel-functie met diezelfde naam:

  1. klik in de cel waar het nieuwe overzicht moet komen, in het voorbeeld is dit B11
  2. druk nu de Shift-toets in en hou die ingedrukt en druk zo vaak op de pijl naar rechts als er in het oorspronkelijke overzicht rijen zijn
  3. nog steeds met de Shift-toets ingedrukt zo vaak op de pijl naar beneden drukken als er in het oorspronkelijke overzicht kolommen zijn
  4. voer dan de volgende formule in =TRANSPONEREN(B2:N6)
    LET OP dit is een zogenaamde matrix- of array formule; deze moeten we activeren door niet alleen op Enter te drukken, maar tegelijkertijd op Ctrl-Shift-Enter (de CSE-methode). In alle cellen komt dezelfde formule te staan; Excel plaatst er automatisch accolades omheen.

NB hebt u geen zin om de originele rijen en kolommen te tellen, selecteer dan eerst met de muis alle cellen van het oorspronkelijke overzicht en kijk (zonder de muisknop los te laten) in het naamvak links boven. In dit voorbeeld zien we dan 5R x 13K staan, dus 5 rijen en 13 kolommen; het nieuwe overzicht krijgt dan 13 rijen en 5 kolommen.

Wijzigt nu één van de bedragen in het oorspronkelijke overzicht dan wordt deze mutatie in het nieuwe overzicht automatisch overgenomen.

Misschien had u het al gezien: het oorspronkelijke overzicht is in de vorm van een Excel-tabel ingevoerd (voor de voordelen hiervan zie onder andere de artikelen Kunst en Excel en Tabellen deel 2).
Wanneer we de regio’s nu uitbreiden met bijvoorbeeld Noord-Oost dan weet Excel dat de tabel groter is geworden (tik de omschrijving in cel B7 en druk op Enter).
Maar hoe zit het met het getransponeerde overzicht? Klik op één van de cellen en u ziet dat de formule automatisch is gewijzigd in =TRANSPONEREN(B2:N7).

Helaas hebben we bij het opstellen van dit overzicht geen rekening gehouden met deze uitbreiding, dus moeten we dit overzicht nog corrigeren:

  1. klik met de muis in één van de cellen van het getransponeerde overzicht
  2. druk op Ctrl-A; alle cellen van het overzicht zullen worden geselecteerd
  3. druk op Shift (vasthouden) en pijl naar rechts; de selectie wordt met 1 kolom uitgebreid
  4. klik in de formulebalk achter de bestaande formule (of druk F2)
  5. druk tegelijkertijd Ctrl-Shift-Enter

LET OP hebt u teveel rijen of kolommen geselecteerd bij het aanmaken van het nieuwe overzicht (of bij de uitbreiding daarvan) dan plaatst Excel de foutboodschap #N/B in de betreffende cellen (Niet Beschikbaar).

In het tabblad Trans5b van het Voorbeeldbestand is dit probleem opgelost door middel van Voorwaardelijke opmaak. De gehanteerde voorwaarde is daarbij =ISNB(B2) met als opmaak een witte kleur bij Lettertype (klik maar eens op Ctrl-A).

Wanneer de oorspronkelijke tabel een totaal-rij heeft en/of een totaal-kolom, is dit voor deze methode geen enkel probleem. De inhoud daarvan wordt bij het transponeren overgenomen, maar niet als formule (zie het tabblad TransP6).

NB helaas geeft de functie Transponeren alleen de inhoud van de oorspronkelijke cellen terug; het is op geen enkele manier mogelijk om de opmaak automatisch over te nemen.  Ook kun je van het resultaat geen Excel-tabel maken.

Transponeren met de functie Index

Voordat we deze functie hier gaan gebruiken, moeten we nog twee andere Excel-functies bespreken: Kolommen en Rijen.

LET OP deze functies niet verwarren met de functies Kolom en Rij, die respectievelijk het kolomnummer en rijnummer van een cel opleveren.

Wanneer we ergens in een spreadsheet de formule =KOLOMMEN(A1) intikken, dan krijgen we als resultaat 1: het aantal kolommen in dat bereik.
Met de vulgreep naar beneden kopiëren en naar rechts levert allemaal 1’en op; de functie blijft telkens maar naar 1 cel kijken (dus 1 kolom).
Maar tikken we nu als formule in =KOLOMMEN($A$1:A1) en kopiëren we die met de vulgreep naar beneden en naar rechts, dan krijgen we een heel ander resultaat (zie kolommen R tot en met U in het tabblad TransP7 van het Voorbeeldbestand).
Iets vergelijkbaars gebeurt er als we de formule =RIJEN ($A$1:A1) kopiëren.
Dus: als we de KOLOMMEN-formule naar beneden kopiëren dan verandert er niets, kopiëren we naar rechts dan neemt het resultaat iedere keer met 1 toe. Bij RIJEN is dit net andersom.
Deze eigenschap van KOLOMMEN en RIJEN wordt in veel (geavanceerde) toepassingen gebruikt, wanneer een teller nodig is in een formule.

Deze ’truc’ gaan we gebruiken om met behulp van de functie INDEX te transponeren:

  1. klik met de muis in de cel waar het eerste resultaat moet komen (in het voorbeeld van tabblad TransP7 is dit cel B14)
  2. daar beginnen we de formule met =INDEX($B$2:$O$9;
    Dan moeten we opgeven uit welke rij van het bereik B2:O9 er iets opgehaald moet worden: hier moet dat rij 1 zijn, maar als we de formule dadelijk naar beneden ’trekken’ dan moet dit 1 blijven; trekken we naar rechts dan moet er telkens een volgende rij gekozen worden. Dat is precies wat we hierboven met de functie KOLOMMEN hadden bereikt.
  3. Dus de formule wordt nu =INDEX($B$2:$O$9;KOLOMMEN($A$1:A1);
    Uit welke kolom moet de formule iets ophalen? De eerste keer uit kolom 1, maar als de formule naar beneden gekopieerd wordt dan moet dat telkens een volgende kolom zijn; bij het kopiëren naar rechts mag de kolom niet veranderen.
  4. In cel B14 komt =INDEX($B$2:$O$9;KOLOMMEN($A$1:A1);RIJEN($A$1:A1))
  5. Kopieren we deze formule te ver naar rechts of naar beneden dan zal Excel een foutmelding geven (de kolom of rij bestaat niet in B2:O9).
    Daarom hebben we in het voorbeeld de formule uitgebreid met een foutafhandeling:

    =ALS.FOUT(INDEX($B$2:$O$9;KOLOMMEN($A$1:A1);RIJEN($A$1:A1));””)

Nog even wat opmaak regelen en het getransponeerde overzicht is klaar.

In het tabblad TransP7 heb ik van een getransponeerd overzicht (vanaf rij 40) een Excel-tabel gemaakt zodat er weinig meer gedaan hoeft te worden aan de opmaak.
Opletten dat je de tabel maakt zonder kolomkoppen (dat zijn immers formules). Er ontstaat dan een aparte kop-regel, die met behulp van Gegevens/Overzicht/Groeperen, wanneer gewenst, kan worden ‘verborgen’.
Door middel van de nieuwe kopjes kan er makkelijk gefilterd worden; zorg dan dat de eerste regel (de oude kop) blijft staan, eventuele lege regels niet worden meegenomen en de totalen ook niet (want die kloppen niet met de filtering!)