Categorie archief: Excel

Van rijen naar kolommen, deel 2

Excel,

Excel AccessIn mijn vorige artikel (Van rijen naar kolommen) heb ik oplossingen aangedragen voor het ‘verplaatsen’ van gegevens van rijen/records naar kolommen/velden; een oplossing in Excel én eentje in Access.
Voor mijn Access-oplossing had ik het gevoel, dat het slimmer/handiger zou moeten kunnen, maar tot nu heb ik nog geen alternatief mogen ontvangen.

Wel heb ik zelf voor de Excel-routine een betere oplossing bedacht; die wil ik jullie niet onthouden.

Probleem

Iemand had mij het volgende gevraagd:

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

Een voorbeeldje zie je hiernaast; de bedoeling is dat alle polissen en bijbehorende waardes van leningnummer 1 achter dit leningnummer komen, voor 2 idem etc.
Niet alle leningen hebben evenveel polissen.

Excel

Bij mijn vorige oplossing (zie Van rijen naar kolommen en het  Voorbeeldbestand 1) had ik enkele tussenstappen nodig om tot het juiste resultaat te komen. Ik realiseerde me later dat het makkelijker en eleganter zou moeten kunnen.

Het bovenstaande probleem is eigenlijk terug te brengen tot de vraag: als we in een bepaalde rij kijken, kunnen we dan achterhalen wat het volgnummer is van de polis bij het betreffende leningnummer?

Rijen1In het Voorbeeldbestand 2 heb ik daartoe in het tabblad Basis een nieuwe kolom (E) toegevoegd. In de eerste regel komt de formule:
=AANTAL.ALS($B$3:B3;B3)
Ik moet toegeven: dat ziet er wel wat vreemd uit. We turven daar hoe vaak de inhoud van B3 (achter de punt-komma) voorkomt in het bereik B3:B3 (voor de punt-komma). Dat is natuurlijk 1 keer.
Maar wanneer we deze formule naar beneden kopiëren, zien we het nut hiervan (dankzij het absoluut maken van de eerste B3 met behulp van de $-tekens). In E4 komt dan de formule
=AANTAL.ALS($B$3:B4;B4)
Hoe vaak komt de waarde uit B4 voor in het bereik B3:B4. Dit is precies wat we wilden weten: zo krijgt ieder polisnummer een volgnummer per lening zonder dat we het bestand hoeven te sorteren.

De gegevens (inclusief kolom E) gebruiken we als basis voor een draaitabel:

  1. zet de cursor ‘ergens’ in het gegevensblok, bijvoorbeeld cel B3
  2. Rijen2kies in de menutab Invoegen in het blok Tabellen de optie Draaitabel
  3. Excel heeft het hele blok gegevens dan geselecteerd (in dit geval B2:E21); het enige wat we hoeven doen is op OK te klikken
  4. in een nieuw tabblad komt de lay-out van de draaitabel. Rijen3Rechts kunnen we de inhoud aanpassen: sleep LenNr naar de Rijlabels, VolgNr naar de Kolomlabels en PolisNr en Waarde naar het Waardegebied.
  5. Nog wat aanpassingen (verwijder de rij- en kolomtotalen) en het gewenste resultaat is klaar.

In het Voorbeeldbestand 2 in het tabblad Ovz is dit resultaat terug te vinden, inclusief een vergelijking met de Access-uitkomst.

De ‘moderne’ standaard-lay-out van een draaitabel is niet altijd even duidelijk (wat doen die kopjes Kolom- en Rijlabels daar?).
In het tabblad Ovz2 staat dezelfde draaitabel maar met een andere indeling:

  1. zet de cursor ergens in de draaitabel
  2. kies in de menutab Hulpmiddelen voor draaitabellen het tabblad Ontwerpen
  3. binnen Rapportindeling kiezen we dan Tabelweergave
  4. nog wat kleurtjes en lijnen et voilà:

Rijen4

Cadeau

Los van de inhoud van dit artikel: ik kwam pas op de site www.myonlinetraininghub.com terecht. Daar is een e-book met Tips & Tricks te downloaden.
Altijd interessant en het mag verder verspreid worden, dus bij deze: Excel_Tips&Tricks_e-BookV1.1

Van rijen naar kolommen

Access, Excel, ,

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

Excel en het World-Wide-Web

Excel, , , , , , ,

Nee, dit is geen aankondiging van een nieuwe thriller, die ik geschreven zou hebben (dat laat ik aan een buurman over: zie glengoutstap.nl); in dit artikel wil ik laten zien welke mogelijkheden er zijn om Excel en het internet (www, world-wide-web) met elkaar te verbinden.

Het gaat dan wel om een eenzijdige verbintenis: hoe kunnen we met Excel op een makkelijke (?) manier informatie aan het internet onttrekken.

Methode 1

De meest gebruikte methode is:

  1. zoek op internet de pagina met gegevens, waar je iets mee wilt gaan doen in Excel
  2. selecteer met de muis de betreffende data
  3. kies Kopiëren
  4. ga naar Excel en kies Plakken

Voor eenmalige acties is hier niets mis mee; dit is dan zeker efficiënt en effectief. Maar wordt het een dagelijkse handeling om bijvoorbeeld koersgegevens op te halen, dan kan dat ook anders.

Methode 2

behr.nlVoor onderzoek naar koersverloop van indexen en bedrijven kom ik regelmatig op de site www.behr.nl; niet voor privé-gebruik ;-).
Daar kun je onder andere historische dagkoersen ophalen.
Even zoeken, maar via de URL www.behr.nl/Beurs/Slotkoersen/.a/aegon zijn de dagkoersen van Aegon te vinden.

Behr.nlBehr levert als resultaat een pagina met datums en de daarbij behorende koersen. Geen verdere poespas daar omheen; dus zeker ook geschikt voor een snelle kopieer- en plak-actie.

Maar heb je dit overzicht vaker nodig dan gaat we dat slimmer aanpakken:

  1. zorg in Excel, dat er een nieuwe werkmap geopend klaar staat (bijvoorbeeld door Ctrl-N te drukken)
  2. Webkies binnen de menutab Gegevens in het blok Ext. geg. ophalen (ofwel Externe gegevens ophalen) de optie Van web
  3. WebEr opent zich een nieuw scherm; op de adresregel moet de hierboven genoemde URL ingevoerd worden.
  4. klik op de button Ga naar
  5. de betreffende internet-pagina wordt opgehaald en onder de adresregel weergegeven. Alle blokken die op die pagina als een tabel zijn opgemaakt hebben een tekentje Web gekregen. Door daar op te klikken kan aangegeven worden of dat betreffende blok wel of niet moet worden opgehaald. In dit geval klikken we alleen op het 2e pijltje; dat wordt dan een groen vinkje.
  6. kies dan onderaan het webquery-scherm de optie Importeren
  7. nog even aangeven waar de gegevens moeten komen en klaar: de data staan nu in Excel om verder verwerkt te worden.
  8. sla het bestand op met de naam Aegon.xlsx.

Moet de lijst morgen (of volgende week, volgende maand) geupdate worden:

  1.  open het bestand Aegon.xlsx
  2. klik met de muis rechts op één van de gevulde cellen en kies de onderste optie Vernieuwen

In het Voorbeeldbestand staat op het tabblad Aegon deze web-query. Daar kan die dus ook bijgewerkt worden.

LET OP de koppeling tussen Excel en internet is gebaseerd op het gebruik van tabellen op web-pagina´s.
Door de implementatie van nieuwere technieken bij de ontwikkeling van web-pagina’s komt het steeds vaker voor, dat Excel geen gegevens of veel teveel gegevens gaat ophalen. Aandachtspuntje voor Bill Gates!

Gegevens ‘knippen’

Helaas zijn de data, die op deze manier bij Behr.nl worden opgehaald, niet direct bruikbaar:  op iedere rij staat de datum en de koers in dezelfde cel (gescheiden door een :-teken), de datum-notatie ‘ziet er niet uit’ en de decimalen in de koers worden vooraf gegaan door een punt ipv een komma.
In het tabblad Heineken in het Voorbeeldbestand zijn daarom 2 kolommen toegevoegd, die ons het omzetten uit handen nemen: in de eerste kolom wordt mbv de functie DATUM een echte datum gecreëerd:

=DATUM(“20″&LINKS(B2;2);DEEL(B2;3;2);DEEL(B2;5;2))

PS1 de 2 meest linkse posities van cel B2 geven het jaar weer, het gedeelte vanaf positie 3 en 2 lang (dus positie 3 en 4) vormt de maand en de posities 5 en 6 vormen de dag.
PS2 aangezien Behr geen eeuw-aanduiding gebruikt ‘plakken’ we zelf er 20 voor, anders gaat Excel met datums uit de 20e eeuw werken.

De koers bepalen we op de volgende manier:

=WAARDE(SUBSTITUEREN(RECHTS(B2;LENGTE(B2)-7);”.”;”,”))

We nemen van B2 het rechtse gedeelte (we laten 7 tekens weg: de datum en het :-teken) en substitueren de punt door een komma. Omdat Excel dan ‘denkt’, dat dit een tekst oplevert, moeten we de waarde daarvan nemen.

Gegevens opmaken

In het Voorbeeldbestand worden ook de koersen van KPN opgehaald (op het tabblad KPN, dus).
Deze pagina is verder opgemaakt met kaders etc. Zorg er voor, dat bij het Vernieuwen deze opmaak niet wordt overschreven: klik rechts op één van de cellen uit de web-query (dus niet de hulp-kolommen) en kies de optie Eigenschappen van gegevensbereik …
Zorg dat Celopmaak behouden staat aangevinkt.
Experimenteer met de diverse opties in dit keuzescherm.

AEX

Het verloop van de AEX-index (en de aandelen, die daar onderdeel van uitmaken) zijn te vinden op www.beurs.nl/koersen/aex/p1.

Helaas is dit ook zo’n site met veel ‘ballast’ rond de cijfers: zie het tabblad AEX in het Voorbeeldbestand. Pas op regel 139 komen de data, waar we naar op zoek zijn.

Nog enkele opmerkingen bij dit tabblad:

  1. na het invoeren van het adres in de web-query komt er een Script-foutmelding: op Ja klikken
  2. Webtijdens het bewerken van de Webquery is via de button Opties… de opmaak ingesteld op HTML-indeling. Een groot gedeelte van de internet-opmaak en -mogelijkheden blijft dan gehandhaafd.
  3. WebDaarnaast is via Eigenschappen van gegevensbereik … ingesteld dat iedere minuut de pagina wordt geactualiseerd.

NB1 hoewel de site beurs.nl de koersen echt real-time laat zien, is dat op deze manier niet het geval. Een vertraging van 20-30 minuten is normaal.

NB2 ’s avonds zullen de koersen niet meer veranderen; de AEX sluit om 17:30 uur (en gaat om 9 uur weer van start).

Opmaak AEX

WebIn het tabblad AEX2 is een mooier overzicht van de AEX-koersen te vinden.  Met behulp van de functie VERT.ZOEKEN worden de gegevens uit het tabblad AEX opgehaald (en nu maar hopen dat de lay-out van de site en dus van het tabblad AEX niet verandert!).
Door gebruik te maken van voorwaardelijke opmaak is snel te zien welke koersen veel of weinig zijn gestegen of gedaald ten opzichte van de slotkoers van de vorige dag (de grens, waarbij een regel groen of rood wordt, is in cel C2 aan te passen).

NB bij het Vert.Zoeken wordt gebruik gemaakt van de functie KOLOM. Deze retourneert het kolomnummer van de cel, die als parameter wordt meegegeven. Geef je geen parameter mee dan geeft de functie de kolom terug van de cel waar de functie in staat.
Op deze manier is het mogelijk om de formule in cel C5 door te kopiëren naar het hele gebied zonder dat er nog aanpassingen nodig zijn. Let ook op de notatie van de Zoekwaarde ($B5; de verwijzing is dus half absoluut en half relatief).

Wandelingen op Veldkruus.nl

In het Voorbeeldbestand wordt op het tabblad Veldkruus het totaal-overzicht van de wandel- en fiets-tochten van de website Veldkruus.nl opgehaald.
Duidelijk is te zien, dat ‘harde returns’ in teksten in Excel als nieuwe regels tevoorschijn komen; voor een snelle verwerking van de gegevens (bijvoorbeeld sorteren op lengte) wordt het er dan niet makkelijker op.

NB even reclame maken voor een andere hobby van mij: met enkele ex-collega’s proberen we de veldkruisen, kapelletjes etcetera in Zuid-Limburg in kaart te brengen. Om het nog aantrekkelijker te maken zetten we ook ‘kruis-tochten’ langs deze objecten uit: zie Veldkruus.nl.

Dynamische web-query’s

Een groot nadeel van de hiervoor geschilderde koppeling tussen Excel en het Web is, dat de query een statisch karakter heeft: heb je een mooi overzicht van Heineken gemaakt en je wilt dezelfde gegevens van Starbucks zien, dan moet je alles opnieuw uitvoeren.

Gelukkig kunnen we de query met wat kunstgrepen dynamischer maken. Aan de hand van de slotkoersen van Behr.nl gaan we dit uitwerken. Het begin is hetzelfde als hierboven al aangegeven:

  1. zorg in Excel, dat er een nieuwe werkmap geopend klaar staat (bijvoorbeeld door Ctrl-N te drukken)
  2. kies binnen de menutab Gegevens in het blok Ext. geg. ophalen de optie Van web
  3. in het ‘Nieuwe web-query’-scherm voeren we op de adresregel de URL www.behr.nl/Beurs/Slotkoersen/.a/aegon in.
  4. klik op de button Ga naar
  5. de betreffende internet-pagina wordt opgehaald en onder de adresregel weergegeven. Alle blokken die op die pagina als een tabel zijn opgemaakt hebben een tekentje Web gekregen. In dit geval alleen op het 2e pijltje klikken.

In plaats van importeren, slaan we deze query op. Rechts boven (naast Opties…) zit daarvoor een button Web9. Sla de query op met de naam Slotkoersen.iqy , bijvoorbeeld op het Bureaublad. Kies daarna Annuleren.

Web10Nu gaan we dat bestand, Slotkoersen.iqy, openen met een simpele tekstverwerker (in het voorbeeld met Kladblok; in de Verkenner rechtsklikken op het bestand).
Iedere web-query moet met de eerste 2 regels beginnen; de 3e regel herkennen we natuurlijk direct: dat is onze URL.
Dan een lege regel; gevolgd door de mogelijke instellingen voor web-query’s.

We passen de derde regel aan: op de plaats waar nu hard staat wat gezocht moet gaan worden (in dit geval ‘.a/aegon’) komt een parameter Fonds:

http://www.behr.nl/Beurs/Slotkoersen/[“Fonds”, “Geef codering van fonds (incl. letterindeling zoals ‘.k/kpn’)”]

LET OP de plaats van de [‘s, aanhalingstekens en de komma zijn wezenlijk

Sluit Kladblok en sla de query onder dezelfde naam op en dubbelklik op de bestandsnaam.
Excel zal opstarten en vraagt naar een code voor het fonds (zoals we hiervoor in de iqy bij de parameter hebben ingevoerd) en laat daarna de betreffende gegevens zien.
Klik rechts op een cel en kies Vernieuwen: dezelfde vraag komt weer en nu heb je de mogelijkheid om een ander fonds op te halen.
Zie ook het tabblad Behr1 in het Voorbeeldbestand.

Web11Het kan nog mooier:

  1. dubbelklik op Slotkoersen.iqy
  2. geef een code op: bijvoorbeeld .h/heineken
  3. tik dan in cel B1 in: .a/aegon
  4. rechtsklik op een cel van de web-query en kies Vernieuwen
  5. in plaats van een code tikken we nu in =b1 en zetten de 2 vinkjes aan
  6. tik in cel B1 een andere code: .s/Starbucks en de gegevens worden automatisch opgehaald!

In het tabblad Behr2 van het Voorbeeldbestand is dit nog iets verder uitgewerkt met behulp van Gegevens-validatie.

Cel.Lezen

Excel, ,

Een paar weken geleden kreeg ik via de website ginfo.nl een vraag over de Excel-functie Cel.Lezen.

Ik begon aan mijn geheugen te twijfelen, want ik (her)kende de functie niet.
Dus maar even gegoogeled; wat bleek: Cel.Lezen is een restant uit een ver verleden, toen VBA nog niet geïmplementeerd was, een zogenaamde Excel4Macro.
cellezenCel.Lezen is één van de vele functies, die niet meer gedocumenteerd zijn, maar nog wel gebruikt kunnen worden; niet direct, als een standaard-functie, maar via een omweg (het gebruik van de optie Namen beheren).
Steeds meer mogelijkheden van Cel.Lezen zijn (of worden) in nieuwere versies van Excel ingebouwd, maar soms kan deze functie toch nog goed bruikbaar zijn.
En los daarvan: het is nooit weg om nog eens met het onderdeel Namen van Excel te stoeien!

Gebruik van Cel.Lezen

Wat kun je doen met Cel.Lezen?
Deze macro/functie levert 66 (!) verschillende soorten informatie over de inhoud of de opmaak van een cel. CelLezenHierbij valt te denken aan de celverwijzing (welke rij, welke kolom) maar ook de inhoud, of de cel een formule bevat, welke kleuren gebruikt zijn etc.
In het Voorbeeldbestand is in het tabblad InfoType een totaal-overzicht opgenomen.

De functie Cel.Lezen heeft 2 parameters nodig: de eerste is het InfoTypeNummer en de tweede is de cel, waarvan de informatie opgehaald moet worden.

CelLezenZoals hiervoor al aangegeven, kan Cel.Lezen niet rechtstreeks aangeroepen worden; dit kan wel via een eigen formule/naam geregeld worden.

Een voorbeeld (zie tabblad VB in het Voorbeeldbestand):
het is altijd goed om te weten of en waar in een kolom (bijvoorbeeld C) formules staan.
We gaan dat in kolom D als volgt aangeven:

  1. plaats de cursor in cel D3
  2. kies binnen de menu-tab Formules de optie Namen beheren en dan de button Nieuw
  3. CelLezenvul het scherm in:
    * kies als naam BevatFormule
    * het bereik, waar deze naam geldig is, beperken we tot het betreffende tabblad (zie hieronder), dus Vb
    * en we verwijzen niet, zoals gebruikelijk is voor een Naam, naar een cel(bereik), maar naar een formule, namelijk
    =CEL.LEZEN(48;C3)
    (InfoTypeNummer 48 geeft als resultaat WAAR als de betreffende cel een formule bevat)
    * klik OK
  4. in het overzichtsscherm Namen beheren zien we onze nieuwe naam
  5. tik in cel D3 de formule =BevatFormule
  6. kopieer de formule in D3 naar beneden tot en met cel D15

NB1 als je goed kijkt in het overzichtsscherm Namen beheren, dan zul je zien dat onze formule iets is aangepast: aan cel C3 is ook de bladnaam Vb toegevoegd; dit is niet te voorkomen. Dat is ook de reden, dat we hiervoor de naam alleen maar geldig maken op het tabblad Vb

NB2 we hebben een relatieve verwijzing naar C3 gebruikt (zonder $-tekens); dit zorgt er voor, dat de formule in D4 naar C4 ‘kijkt’ etc.
Het effect hiervan zie je ook in Namen beheren: de formule bij Verwijst naar: is afhankelijk van de plaats van de cursor in het tabblad.

BevatFormule (vervolg)

Hiervoor is met behulp van de zelf-gedefinieerde naam BevatFormule een mogelijkheid gecreëerd om zichtbaar te maken of cellen al dan niet een formule bevatten.

In het tabblad Vb2 van het Voorbeeldbestand is een alternatieve methode gebruikt:
op dezelfde manier als hiervoor is, met cel D3 geselecteerd, een nieuwe naam gemaakt, BevatFormule2.
Deze naam is alleen geldig in Vb2 en heeft als verwijzing
=CEL.LEZEN(48;D3)

LET OP deze formule verwijst dus naar de cel zelf. Wanneer we ergens in het tabblad dan ook intikken =BevatFormule2, dan zal het resultaat altijd WAAR zijn!

Deze naam is in het voorbeeld gebruikt om via Voorwaardelijke opmaak zichtbaar te maken waar ergens in kolom C een formule wordt gebruikt.

NB de naam BevatFormule2 kan ook voor de gehele werkmap werkend worden gemaakt: wijzig de verwijzing naar cel D3 in INDIRECT(“RK”;Onwaar)

Overzicht

CelLezenOm de werking van Cel.Lezen verder te verduidelijken is in het Voorbeeldbestand het tabblad Ovz opgenomen. Hierbij is gebruik gemaakt van de naam CelInfo met als formule
=CEL.LEZEN($C$2;B5)
LET OP de eerste parameter is absoluut, de tweede relatief: waar de formule ook gebruikt wordt, het InfoType is altijd de inhoud van cel C2

Door in cel C2 een nummer tussen 1 en 66 in te voeren wordt in C5:C9 de informatie over de overeenkomende cellen in kolom B weergegeven.

Met behulp van InfoTypeNummer=1 zien we de (absolute) celverwijzingen etc.

Herberekenen

Eén van de nadelen van dit soort implementatie van functies is, dat Excel het resultaat niet altijd bijwerkt.
Excel probeert namelijk zo efficiënt met de rekentijd om te gaan en beoordeelt na het aanpassen van een cel welke andere cellen allemaal opnieuw berekend moeten worden.
CelLezenMaar, wanneer je bijvoorbeeld de achtergrondkleur van een cel wijzigt, zal Excel geen enkele cel spontaan gaan herberekenen. En helaas ook niet wanneer we Excel daartoe dwingen door op F9 (Handmatig herberekenen) te drukken.

Wijzig in het tabblad Kleur van het Voorbeeldbestand bijvoorbeeld de achtergrondkleur van cel B3 in grijs. Wat gebeurt er met C3? En als je op F9 drukt? Niets dus.
Het enige wat helpt, is om cel C3 te selecteren, op F2 te drukken (inhoud cel wijzigen) en dan op Enter.
Voor de code-kolom is als Naam de volgende formule gebruikt:
=CEL.LEZEN(38;B3).

Maar voor alles (?) is een oplossing. We moeten gewoon zorgen dat onze formule een element bevat, dat bij een herberekening altijd een nieuwe waarde krijgt. Een voorbeeld van zoiets is de functie Nu(), die bij iedere wijziging in Excel opnieuw wordt bepaald.
Dus nogmaals een Naam vastleggen (CelKleur2):
=CEL.LEZEN(38+Nu()*0;B3).
Wijzig één van de kleuren in kolom E en druk op F9: et voilà!

NB1 de functie Nu() levert een getal op; dat zou de inhoud van de eerste parameter veranderen, dat is natuurlijk ook weer niet de bedoeling. Vandaar het vermenigvuldigen met 0.

NB2 de truc om te zorgen dat Excel wat ‘actiever’ is bij het herberekenen heb ik ooit op de site van Jan Karel Pieterse gevonden: jkp-ads.com.

Waarschuwingen

Waarschuwing1: aangezien Cel.Lezen voor Excel een soort macro is, moet een bestand, dat gebruik maakt van deze formule, opgeslagen worden met de extensie xlsm.

Waarschuwing2: bij het googelen kwam ik enkele keren tegen, dat het kopieren van cellen met verwijzingen naar Cel.Lezen naar andere tabbladen, kan zorgen voor een crash van Excel. OEPS!

Waarschuwing3: of Microsoft de  Excel4Macro’s in een volgende versie nog zal blijven ondersteunen is maar de vraag.

Temperaturen en thermometer

Excel, , , , , , ,

temp1Het is vandaag toch geen weer om buiten te zijn, dus maar achter de PC gekropen om een artikel voor G-Info te schrijven.

Het onderwerp lag voor de hand: waar temp2hebben we het allemaal over de laatste dagen? Juist ja, de temperaturen!

Eens kijken of we (met behulp van Excel) nog wat informatie kunnen toevoegen. Uit KNMI-gegevens maken we een grafiek waardoor het onderwerp nog meer gaat leven.

Voor de fun: in het Voorbeeldbestand vind je ook een “live”-thermometer.

Brongegevens

temp3Voordat we temperatuurgrafieken kunnen maken hebben we brongegevens nodig.
Even “googelen”: al snel bleek, dat de KNMI heel veel (historisch) materiaal klaar heeft staan, dat je gratis kunt downloaden (zie http://www.knmi.nl/klimatologie/uurgegevens/select_uur).

Ik heb voor dit artikel alleen de temperatuur per uur voor drie weerstations voor het lopende jaar binnengehaald (hoewel de gegevens over de duur van de neerslag ook wel eens interessant zouden kunnen zijn!)

Na het downloaden hebben we een tekstbestand op de PC staan; deze gegevens allereerst maar eens in Excel geplakt (zie het tabblad KNMI in het Voorbeeldbestand).
Na diverse regels toelichting komen de gegevens waar het ons om te doen is: het weerstationnummer, de datum, het uur en de temperatuur. Per dag zien we alle gegevens in 1 cel komen; gescheiden door een komma.
Dat wordt “knippen”:

  1. selecteer alle cellen met temperatuurgegevens (in het tabblad KNMI de cellen B17:B13120)
  2. temp4kies in de menutab Gegevens de optie Tekst naar kolommen
  3. er opent zich een nieuw scherm: kies daar de optie Gescheiden en klik op de knop Volgende
  4. zorg dat in het volgende scherm bij Scheidingstekens ook Komma staat aangevinkt en klik op de knop Voltooien
  5. alle gegevens zijn nu mooi over kolommen verdeeld

Wat opvalt: de datum staat in het militaire formaat (jjjjmmdd, dus 4 cijfers voor het jaar, 2 voor de maand en 2 voor de dag; in die volgorde!).
Daarnaast moeten we de temperatuur nog door 10 delen om netjes graden Celsius te krijgen.

TIP op de volgende manier gaat dat het snelst:

  1. klik ergens in een lege cel de waarde 10 in
  2. kopieer deze cel (bijvoorbeeld via Ctrl-C)
  3. selecteer alle temperatuurcellen
  4. klik met de rechtermuisknop ergens in de selectie
  5. kies de optie Plakken speciaal …
  6. kies bij Bewerking de optie Delen
  7. klik op de knop OK
  8. maak de cel uit punt 1 weer leeg

Temperatuurgrafiek1

Om op de standaardmanier een grafiek te maken moeten we de gegevens van de KNMI nog iets anders indelen: na het “knippen” zetten we de temperaturen van de drie weerstations achter elkaar in verschillende kolommen en zetten nog wat verklarende teksten boven de kolommen (zie tabblad Grafiek1).

temp5Maak op de ‘gewone’ manier een grafiek.
Zorg dat op de x-as de Datum en het Uur zichtbaar worden door én de gegevens van kolom B én die van kolom C op te geven.

Door de grote hoeveelheid dagen en daarbinnen de temperatuur per uur wordt de grafiek moeilijk te lezen.

Temperatuurgrafiek2

Een alternatieve grafiek kunnen we creëren door middel van een draaitabel. Een groot voordeel hierbij is dat de brongegevens niet hoeven worden aangepast.

In het Voorbeeldbestand zijn in het tabblad KNMI_data de brongegevens in een Excel-tabel opgenomen. Nieuwe gegevens kunnen onderaan worden toegevoegd; zijn ze nog niet ‘geknipt’, doe dat dan hier.  Excel neemt automatisch de deling van de KNMI-temperatuur door 10 voor zijn rekening en als ’toegift’ wordt de datum in een normaal formaat gezet.

In een draaitabel worden alle gegevens uit de tabel in een bruikbaar formaat gezet (zie tabblad Draai in het Voorbeeldbestand).
Hierbij zijn een paar handigheidjes verwerkt:

  1. in de kolommen zijn de weerstations weergegeven; standaard komen hier de nummers tevoorschijn. Deze nummers heb ik overschreven met de betreffende namen. Voortaan houdt Excel deze wijziging vast.
  2. in de rijen staan naast de uren ook de echte datums uit KNMI_data. Deze zijn echter zodanig gegroepeerd, dat er ook een onderscheid in jaren en maanden wordt gemaakt:
    * klik met de rechtermuisknop op een van de datums
    * temp6kies de optie Groeperen
    * in het vervolgscherm de grenzen ruim genoeg zetten, zodat toekomstige gegevens ook direct goed verwerkt worden
    * klik bij de optie Op de Dagen, Maanden en Jaren aan
    * klik op de knop OK
    * sleep de Jaren en Maanden van de Rijlabels naar het Rapportfilter

Wanneer de draaitabel actief is (klik ergens in de draaitabel) komt er bovenaan een nieuwe set tabbladen tevoorschijn: Hulpmiddelen voor draaitabellen. Daar vinden we binnen de menutab Opties een knop Draaigrafiek.

temp1Het resultaat staat op het tabblad Grafiek2.
Via de filters op Jaren, Maanden en Station kunnen meer of minder en andere gegevens zichtbaar gemaakt worden.

LET OP als er regels aan de tabel KNMI_data zijn toegevoegd, kies dan wel nog de optie Vernieuwen in de Draaitabel of de Draaigrafiek (hoe? Klik rechts ergens in de tabel of aan de rand van de grafiek).

Thermometer

Terwijl ik dit aan het schrijven ben, kijk ik af en toe op de thermometer om te kijken of mijn warmtegevoel overeen komt met de werkelijkheid. Nou, hierbinnen is het bijna 30 graden en zo voelt het ook! Dus nog maar wat water drinken.

Maar dit is wel aanleiding om te kijken of we in Excel ook een thermometer kunnen inbouwen.
temp7Daar gaat ie (zie tabblad Thermometer in het Voorbeeldbestand):

  1. om de keuze van weerstation te vergemakkelijken maken we een klein tabelletje met ‘rugnummers’. Wanneer we aan de tabel KNMI_data ook andere weerstations toevoegen dan moet dit lijstje uitgebreid worden
  2. temp8in de cellen F2 en F3 kan gekozen worden voor welke dag en welk station de temperatuur moet worden weergegeven.
    Denk aan de datum-notatie!
    Cel F3 is van een Gegevensvalidatie voorzien, zodat daar alleen nummers gekozen kunnen worden uit het lijstje van punt 1. In cel F4 komt dan automatisch de naam van het station via de formule
    =VERT.ZOEKEN(F3;Stations;2;ONWAAR)
    De waarde van cel F3 (in dit geval 380) wordt opgezocht in de tabel met de naam Stations (het lijstje uit punt 1). Als die gevonden wordt, dan wordt de waarde uit de 2e kolom teruggegeven (de naam dus). Door Onwaar geven we aan, dat we een exacte match willen; de lijst hoeft dus niet gesorteerd te zijn.
  3. dan gaan we de temperatuur opzoeken, die bij die dag én dat station hoort en dat per uur:
    =SOMMEN.ALS(KNMI_data[Temp];KNMI_data[Datum];$F$2;KNMI_data[Station];Thermometer!$F$3;KNMI_data[Uur];E10)
    Niet schrikken! Verticaal zoeken met verschillende voorwaarden kan niet, dus daarom iets ingewikkelder: tel alle temperaturen in de kolom Temp van de tabel KNMI_data op (we zorgen, dat er maar 1 temperatuur wordt opgehaald, dus dat is een makkie voor Excel) als aan de volgende voorwaarden wordt voldaan:
    * in de kolom Datum van de tabel KNMI_data staat de waarde uit cel F2
    * én in de kolom Station staat de waarde uit cel F3
    * én het Uur komt overeen met cel E10
    Door de relatieve en absolute verwijzingen kunnen we de formule uit F10 naar beneden kopiëren.
  4. omdat op deze manier de waarde 0 (nul) ontstaat als er geen gegevens zijn staat er nog een ALS-formule omheen, die een lege resultaat genereert als er geen temperatuur te vinden is.
  5. temp2in de cellen F5, F6 en F7 worden de minimum-, maximum- en laatst bekende waarde opgehaald (voor de historische data zal dit dus altijd 12 uur ’s nachts zijn).
    NB de formule in cel F7, die als matrixformule is ingevoerd (zie de accolades), vergt teveel uitleg voor dit weer! Probeer zelf de logica te ontrafelen. Kom je er niet uit; stuur een berichtje via de site.
  6. dan een grafiek maken met de 3 waardes uit de cellen F5, F6 en F7, zodat we naast de “actuele” temperatuur ook het minimum en maximum van die dag zien.
    De actuele/laatste waarde van die dag als een staafdiagram, de andere twee als lijngrafiek, inclusief markering.
    Omdat we onder 0 graden ook een markering willen zien, is er nog een reeks toegevoegd met de waarde -50.
  7. Bijna klaar: het moet er nog als een echte thermometer uitzien. Dus een afbeelding van een thermometer gegoogeled en daar de grafiek overheen gelegd, zodanig dat de schalen overeen komen.
    Dan in de grafiek alle overbodige zaken als assen en rasterlijnen verwijderen en het grafiek- en tekengebied transparant maken (de optie Geen opvulling).
    De 2 objecten (Grafiek en Afbeelding) allebei selecteren en Groeperen kiezen, zodat ze samen verplaatst kunnen worden: daar is onze thermometer!

NB door de tabel in het tabblad KNMI_data aan te vullen met de meest recente gegevens zal de thermometer een echt actuele stand weergeven.
Helaas nog geen real-time! Iemand een idee hoe dat zou kunnen??