Tagarchief: voorwaardelijke opmaak

Sudoku in Excel

Ik kreeg onlangs de volgende vraag: kun je geen artikel schrijven waarin je uitlegt hoe je met behulp van Excel Sudoku-puzzels op kunt lossen.
Mijn antwoord bestond uit 2 woorden ‘Ja’ en ‘maar’.

De Ja, omdat met Excel ‘alles’ mogelijk is en de maar omdat het oplossen van zo’n puzzel meer een programmeer-uitdaging is en niet zozeer iets om de mogelijkheden van Excel voor de dagelijkse praktijk te laten zien.

Maar toen ik er wat langer over nadacht, zag ik wel wat mogelijkheden.
Dus nu toch een Sudoku in Excel, maar niet een machine die de oplossing genereert (wat zou je daar ook mee moeten), wel een systeem dat het zoeken van een oplossing ondersteunt.

Met behulp van Voorwaardelijke opmaak laat Excel zien op welke plaatsen een cijfer geplaatst kan worden. Dubbele invoer in een rij, kolom of blok wordt ook door Voorwaardelijk opmaak gesignaleerd. Bij deze opmaak wordt veelvuldig gebruik gemaakt van de Namen-optie van Excel; niet alleen de standaard-toepassing daarvan maar ook enkele meer ingewikkelde en dus interessante mogelijkheden!

Basis van het G-Info/Sudoku-systeem

Allereerst hebben we natuurlijk een blok van 9 bij 9 nodig, die dan verder is onderverdeeld in kleinere blokken van 3 bij 3 en in rijen en kolommen. Daar gebruiken we een eigenschap van cellen voor; we kunnen de (dikte van de) randen aanpassen (druk op Ctrl-1). Het bereik van de 81 cellen heeft de naam Sudoku gekregen.

Om straks te weten over welke kolom of rij we het hebben, zijn in regel 3 en in kolom B de cijfers 1 t/m 9 ingevuld. De kruising van die twee, dus cel B3, heeft de naam Ref gekregen. Straks moeten we ook weten wat de inhoud van een geselecteerde cel is; die informatie komt in cel O3 met de naam Selectie.
Regel 3 en de kolommen B en O zijn ‘onzichtbaar’ gemaakt . Dit is te zien aan de +-jes; door daar op te klikken, open en sluit je een bepaalde groep cellen (zie het tabblad Sudoku in het Voorbeeldbestand).

Om het geheel wat mooier te maken is het standaard-raster weggehaald (menutab Bestand, dan Opties en daarbinnen Geavanceerd; vink dan Rasterlijnen weergeven uit).
Alle regels en kolommen, die we niet nodig hebben, zijn verborgen (selecteer ze eerst en dan rechts klikken).
En als laatste zijn de klom- en rijnamen weggelaten (menutab Bestand, dan Opties en daarbinnen Geavanceerd; vink dan Rij- en kolomkoppen weergeven uit).

Voor een soepele werking van het spel zijn nog een paar knoppen toegevoegd die bepaalde VBA-routines opstarten; hier komen we later op terug.

Controle 1

Het eerste wat we willen weten als we een Sudoku invullen, is waar overal (als voorbeeld) een 1 staat of waar een 2 etc.
Dat kunnen we met Voorwaardelijke opmaak goed zichtbaar maken; de enige voorwaarde is dat we weten welk cijfer we willen laten oplichten. Daarvoor hebben we een klein VBA-programmaatje nodig:

De subroutine Worksheet_SelectionChange is gekoppeld aan het werkblad Sudoku. Als de selectie in dit werkblad wordt gewijzigd, wordt deze routine automatisch door Excel aangeroepen. Op deze manier bevat de cel Selectie (O3) altijd de laatst geselecteerde waarde uit het bereik Sudoku.

NB bekijk de VBA-routine in het Voorbeeldbestand door bijvoorbeeld met de muis rechts te klikken op de tab Sudoku en dan te kiezen Programmacode weergeven. Eventueel nog dubbelklikken op het blad Sudoku in de Projectverkenner.

Nu kunnen we de Voorwaardelijke opmaak toevoegen:

  1. plaats de cursor in de eerste cel van de Sudoku (D5 dus) en Fselecteer dan de 81 Sudoku-cellen
  2. kies in de menutab Start in het blok Stijlen de optie Voorwaardelijke opmaak en dan Nieuwe regel …
  3. in het vervolgscherm kiezen we de 2e optie (Alleen cellen opmaken met) en geven dan aan dat de opmaak moet worden doorgevoerd als de celwaarde gelijk is aan de inhoud van de cel Selectie (denk aan het =-teken vóór Selectie)
  4. nog even de gewenste Opmaak instellen en OK

Controle 2

De volgende controle zorgt er voor dat we weten of er geen dubbele cijfers in de kolommen, rijen of kleine blokken voorkomen.

Voordat we dit makkelijk en flexibel kunnen doen moeten we wat voorwerk verrichten.
Excel moet automatisch beoordelen bij welke rij, kolom en blok een bepaalde cel hoort. Dan heb ik gedaan door enkele nieuwe Namen in te voeren:

  1. plaats de cursor in de eerste cel van de Sudoku
  2. een nieuwe naam creëren: kies in de menutab Formules in het blok Gedefinieerde namen de optie Naam definiëren
  3. in het vervolgscherm kiezen we als Naam een handige omschrijving, in dit geval RijSel en bij Verwijst naar=Sudoku!$B5
    Denk aan het dollar-teken voor de kolom-letter.
    Afhankelijk waar de cursor staat (nu in regel 5) zal deze naam de waarde uit kolom B van die regel ophalen.
  4. op gelijkwaardige manier bestaat er ook de naam KolomSel met als formule =Sudoku!D$3
    Deze naam geeft dus altijd de waarde uit regel 3 van de kolom waar de cursor staat.
  5. om te bepalen in welk blok de cursor staat is wat ingewikkelder:
    =GEHEEL((Sudoku!RijSel-1)/3)*3+GEHEEL((Sudoku!KolomSel-1)/3)+1
    Hier is de naam BlokSel aan gekoppeld.
  6. nu moeten we nog de inhoud van zo’n rij, kolom en blok kunnen ophalen om de inhoud te kunnen testen. Daartoe zijn nog 3 extra namen gedefinieerd:
    RijTest met de formule =VERSCHUIVING(Sudoku!Ref;Sudoku!RijSel+1;2;1;9)
    KolomTest met =VERSCHUIVING(Sudoku!Ref;2;Sudoku!KolomSel+1;9;1) en
    BlokTest met =VERSCHUIVING(Sudoku!Ref;GEHEEL((Sudoku!BlokSel-1)/3)*3+2;REST((Sudoku!BlokSel-1);3)*3+2;3;3)

Nu kunnen we een nieuwe Voorwaardelijke opmaak toevoegen:

  1. plaats de cursor in de eerste cel van de Sudoku (D5 dus) en selecteer dan de 81 Sudoku-cellen
  2. kies in de menutab Start in het blok Stijlen de optie Voorwaardelijke opmaak en dan Nieuwe regel …
  3. in het vervolgscherm kiezen we nu de laatste optie (Een formule gebruiken). De formule wordt
    =OF(AANTAL.ALS(BlokTest;D5)>1;
    OF(AANTAL.ALS(RijTest;D5)>1;
    AANTAL.ALS(KolomTest;D5)>1))
    Aantal.Als kijkt hoe vaak in een bereik (de eerste parameter) de waarde van de tweede parameter voorkomt.
  4. nog even een Opmaak instellen en OK

NB1 wil je de gebruikte opmaak bekijken kies dan Regels beheren binnen de optie Voorwaardelijke opmaak

NB2 achteraf gezien was het waarschijnlijker makkelijker geweest om voor het testen de functie INDEX te gebruiken in plaats van VERSCHUIVING. Misschien een andere keer.

Controle 3

Als laatste controle voegen we nog wat opmaak toe, die aangeeft op welke plaatsen een bepaald geselecteerd cijfer nog kan worden ingevuld:

  1. plaats de cursor in de eerste cel van de Sudoku (D5 dus) en selecteer dan de 81 Sudoku-cellen
  2. kies in de menutab Start in het blok Stijlen de optie Voorwaardelijke opmaak en dan Nieuwe regel …
  3. in het vervolgscherm kiezen we nu de laatste optie (Een formule gebruiken). De formule wordt
    =EN(D5<=0;
    EN(Selectie>0;
    EN(AANTAL.ALS(BlokTest;Selectie)=0;
    EN(AANTAL.ALS(RijTest;Selectie)=0;
    AANTAL.ALS(KolomTest;Selectie)=0))))
    Dus als de betreffende cel, waarvan de opmaak moet worden ingesteld, een waarde kleiner of gelijk aan nul heeft (dus er is nog geen cijfer ingevuld) EN via Selectie weten we welk cijfer we onder de loep nemen EN in het betreffende blok komt het cijfer nog niet voor EN ook niet in de rin EN niet in de kolom dan moet de opmaak toegepast worden.

Buttons/knoppen

Onder de Sudoku zijn een paar knoppen opgenomen, die het opzetten van een spel kunnen vergemakkelijken (zie het Voorbeeldbestand):

  1. Wissen: de beveiliging van het tabblad wordt verwijderd, het speelveld leeg gemaakt, lettertypes en eventuele kleuren worden gereset. Ook de Selectie en de Starttijd worden leeg gemaakt.
  2. de volgende stap is dat een nieuw spel moet worden opgezet. Doe dit op het tabblad Makkelijk of Moeilijk door onder een bestaand spel ‘met de hand’ cijfers op de juiste plaats te zetten.
    Kies dan op het tabblad Sudoku de knop Kopieer. Er komt een pop-up waarmee kan worden aangegeven welk spel uit de voorraad moet worden opgezet. Selecteer daartoe het betreffende blok met de muis en klik dan op OK.
  3. Als je klaar bent om te gaan spelen, klik dan op de knop Start. Allereerst worden alle opgezette cijfers geblokkeerd en vet gemaakt, het tabblad beveiligd (zonder wachtwoord) en de Starttijd ingevuld.
  4. Wil je tussentijds wat zetten gaan proberen, bewaar dan de tussenstand via de knop Save. De stand van dat moment wordt gekopieerd naar het tabblad SaveGame.

Handleiding

In het tabblad Handleiding staat de werkwijze van het systeem ook uitgelegd en ik heb enkele tips toegevoegd.

Veel speel-plezier!


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

Gantt-grafiek

Henry Laurence Gantt ontwikkelde de Gantt-grafiek rond 1910.
In zijn werk als werktuigkundig ingenieur, managementconsultant en bedrijfsadviseur werd de Gantt-grafiek gebruikt als een visueel hulpmiddel om de planning en voortgang van een project te laten zien. In die tijd werd dit gezien als een opzienbarende innovatie.

Voor (complexe) Gantt-grafieken worden veelal specialistische (project-)programma’s gebruikt, maar voor het gewone werk kunnen we met Excel ook al aardig vooruit.

Aan de hand van een simpele project-opzet zullen we 2 mogelijkheden voor een Gantt-grafiek bekijken.

Project-data

Hiernaast ziet u de gegevens van een fictief project (zie het tabblad ProjData in het Voorbeeldbestand).
Per stap en substap leggen we een volgnummer vast, samen met een omschrijving, een begindatum en het aantal geplande dagen dat de stap duurt (alleen op het laagste niveau).

Uiteraard beïnvloeden de stappen elkaar. In het voorbeeld zijn dat de volgende zaken:

  1. de startdatum van het project leggen we vast in cel D3
  2. in cel D4 komt de echte startdatum van de eerste hoofd-stap, de Brainstorm-sessies. We gebruiken de functie WEEKDAG om te controleren of D3 niet in een weekend valt. Als D3 een zondag is dan is het resultaat van WEEKDAG een 1, bij maandag een 2 etc.
  3. er zijn 2 brainstormsessies, die parallel plaats vinden. De startdatums daarvan (cellen D5 en D6) zijn gelijk aan D4.
  4. In E5 en E6 ligt vast hoeveel dagen deze sessies in beslag nemen.
  5. de einddatum van deze 2 substappen wordt bepaald met de functie WERKDAG. Zo staat in F5 de formule =WERKDAG(D5;E5-1)
    Deze functie bepaalt de einddatum door bij de startdatum (D5) een aantal dagen op te tellen (E5); de functie telt dagen van het weekend daarbij niet mee.
    Omdat op de startdatum ook al gewerkt wordt, verlagen we het aantal te werken dagen met 1.
  6. De afronding van de brainstorm vindt plaats op de eerste werkdag nadat de laatste sessie klaar is; in cel D7 staat daarom de formule =WERKDAG(MAX(F5:F6);1)
  7. De ontwerpfase begint als de brainstorm voorbij is; eerst het Functioneel Ontwerp, daarna het Technisch Ontwerp en vervolgens een Eindoverleg om alle details door te nemen.
    De opzet van de cellen D8:D11 mag dan ook duidelijk zijn.
  8. De Bouw start na de afronding van het Ontwerp (cellen D12 en D13).
  9. Deel2 van de bouw kan pas beginnen na afronding van Deel1 (cel D14=WERKDAG(F13;1)).
  10. Wel kan de bouw van de Schil parallel aan de andere bouwactiviteiten plaats vinden (cel D15 is gelijk aan D12).
  11. De daadwerkelijke oplevering vindt plaats nadat de laatste bouwactiviteit is afgerond; in D16 staat de formule =WERKDAG(MAX(F13:F15);1)

NB de functie WERKDAG kent nog een derde parameter, Vakantiedagen. Leg ergens in uw werkmap een overzicht vast van die dagen, dat er voor het project niet gewerkt kan worden en voeg deze reeks als derde parameter toe aan alle WERKDAG-functies (zie een voorbeeld op het tabblad ProjData).

Gantt-grafiek 1

Op basis van de gegevens uit het tabblad ProjData van het Voorbeeldbestand gaan we nu een grafiek maken. Wel hebben we dan nog 2 hulpkolommen nodig:

  • in kolom G komt de startdatum van de betreffende stap minus 1 (waarom dat zullen we hieronder zien; datums zijn voor Excel gewoon getallen dus in G3 komt de formule =D3-1)
  • in kolom H komt het verschil in kalenderdagen tussen de start- en einddatum van iedere stap (in H3 staat dan de formule =F3-D3+1; met 1 gecorrigeerd omdat de startdatum ook meedoet)

Dan kunnen we de grafiek gaan opbouwen:

  1. selecteer de cellen G3:G16
  2. kies in de menutab Invoegen in het blok Grafieken de optie Staaf
  3. en dan bij 2D-staaf de 2e optie (Gestapelde staaf). Het eerste gedeelte van de grafiek is klaar.
  4. selecteer de cellen H3:H16 en Kopieer deze (Ctrl-C)
  5. klik ergens in de grafiek van punt 3 en Plak (Ctrl-V)
  6. de legenda kan weg: klik er op en druk op Delete
  7. de basis-as moet nog aangepast worden:
    * klik met de rechtermuisknop ergens in de grafiek
    * kies de optie Gegevens selecteren
    * klik op de button Bewerken onder Horizontale aslabels
    * selecteer de cellen B4:C16 en klik op OK
    * nog een keer OK om bij de grafiek terug te komen
  8. eigenlijk willen we alleen de tweede staaf zien; de eerste geeft het verloop tot aan de startdatum weer (vandaar de minus 1 in kolom G):
    * klik rechts op één van de staven, die ‘weg’ moeten
    * kies de optie Gegevensreeks opmaken
    * kies onder Opvulling de optie Geen opvulling en dan de button Sluiten
  9. Nog één aanpassing. De volgorde van de stappen is (voor ons) niet logisch:
    * klik rechts op de Categorie-as
    * kies de optie As opmaken
    * vink de optie Categorieën in omgekeerde volgorde aan en klik op de button Sluiten

In het tabblad Gantt1 ziet u het resultaat van bovenstaande exercitie (met nog enkele aanpassingen, die de grafiek overzichtelijker maken).

NB kijk wat er gebeurt, als je in cel D3 van het tabblad ProjData een andere startdatum voor het project invoert.

Gantt-grafiek 2

Deze Gantt-grafiek is op een heel andere manier tot stand gekomen (het is geen echte grafiek, maar een inkleuring van Excel-cellen; zie tabblad Gantt2 van het Voorbeeldbestand):

  1. het ‘blauwe’ blok zijn allemaal verwijzingen naar het tabblad ProjData; bijvoorbeeld in cel B6 staat de formule =ProjData!B4
  2. in cel H4 staat de formule =D5-5, zodat de ‘grafiek’ niet exact op de startdatum begint, maar 5 dagen eerder.
  3. in I4 staat de volgende dag =H4+1; deze formule is zover naar rechts gekopieerd, dat de einddatum van het project in de rij voorkomt.
  4. in H3 hebben we de Maand zichtbaar gemaakt met de formule =Tekst(H4,”mmm”) en deze formule is ook naar rechts gekopieerd
  5. in regel 2 bepalen we op een vergelijkbare manier het Jaar

Alle overige effecten (verticale lijnen, gekleurde cellen en het al dat niet zichtbaar maken van het jaar en de maand) zijn met behulp van Voorwaardelijke opmaak geïmplementeerd (LET OP deze optie is niet direct beschikbaar; om ongewilde wijzigingen te voorkomen is  het werkblad Gantt2 beveiligd. Verwijder de beveiliging via de menutab Controleren en dan de optie Beveiliging blad opheffen (geen wachtwoord vereist). Vergeet niet daarna de beveiliging weer aan te brengen!):

  1. als de tekst in regel 3 verandert (H$3<>G$3), dan komt er een rand aan de linkerkant van de cellen in de regels 2 t/m 18
  2. als de inhoud van een cel in de 2e en 3e regel gelijk is aan de cel links daarvan (H2=G2) dan krijgt de tekst in die cel een witte kleur en is dus niet meer zichtbaar
  3. de cellen in de regels 4 t/m 18 krijgen een rode linker- en rechterrand als de inhoud van de 4e regel gelijk is aan de datum van vandaag (H$4=VANDAAG()).
  4. ook krijgen de cellen in de regels 4 t/m 18 een groenige achtergrond als de datum in de 4e regel een weekend voorstelt (WEEKDAG(H$4;2)>5; de parameter 2 geeft aan dat de week op een maandag begint)
  5. de cellen in de regels 5 t/m 18 krijgen een bruinige achtergrond als de datum in de 4e regel groter of gelijk is aan de startdatum in kolom D EN de datum in de 4e regel kleiner of gelijk is aan de datum in de kolom E.

Welke methode heeft de voorkeur?

Mijn persoonlijke voorkeur gaat naar de 2e methode uit. Hierbij is ieder facet van de ‘grafiek’ in te stellen.

Dit is meteen ook het grootste nadeel van deze methode. Dus heb je snel een grafiek nodig, gebruik dan methode 1.

Laat in een reactie hieronder weten, welke methode u prefereert.


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

Zonnepanelen

ZonnepanelenIn het kader van duurzaamheid hebben wij ook zonnepanelen op het dak liggen. Vijf jaar geleden hebben we 2 (kleine) sets laten plaatsen; na dit lustrum zullen we eens kijken hoe we de resultaten inzichtelijk kunnen maken.

Een mooie definitie in dit kader:

“Duurzame ontwikkeling is de ontwikkeling die aansluit op de behoeften van het heden zonder het vermogen van de toekomstige generaties om in hun eigen behoeften te voorzien in gevaar te brengen”

Dataverzameling

zon1Een beetje jaloers kijk ik wel eens naar andere mensen, die recentelijk panelen hebben aangeschaft: de huidige stroom-omvormers communiceren via wifi met centrale servers waar alle gegevens over stroomproductie worden verzameld. Via internet kunnen deze gegevens on-the-fly worden opgevraagd; alle details zijn in te zien, maar ook allerlei grafiekjes staan de ‘stroom-leveranciers’ tot hun beschikking.

In mijn geval moet ik wekelijks een laptop aansluiten op de omvormers en dan kan ik de resultaten van die periode uitlezen.
Uiteraard sla ik die in Excel op, zodat ik ook overzichten kan maken; zie het Voorbeeldbestand, tabblad Data.

Per dag leg ik vast hoeveel kWh een set (met drie panelen) heeft opgeleverd. Voor het gemak laat ik Excel ook de dag van de week weergeven. Naast de datum staat daartoe een kolom met dezelfde inhoud maar met een andere datumopmaak namelijk “dddd” (selecteer een cel in die kolom en kies celeigenschappen m.b.v. Ctrl-1).
De kolom met de kWh’s heeft een voorwaardelijke opmaak: als het resultaat van een dag lager is dan het gemiddelde dan wordt die rood weergegeven.

Wikipedia:

Het kilowattuur (symbool kWh) is een eenheid van arbeid of, populair uitgedrukt, een hoeveelheid elektrische energie. Als men een machine met een vermogen van 1 kW één uur laat werken heeft men een energie van 1 kWh "verbruikt".
De kosten voor opwekking van 1 kWh aan elektriciteit zijn in Nederland ongeveer 4 eurocent voor een elektriciteitscentrale die wordt gestookt op aardgas of steenkool, en 5-8 eurocent, afhankelijk van de standplaats van de molen, voor windenergie (2003). 
De verbruikersprijs ligt echter aanzienlijk hoger vanwege de kosten van bijvoorbeeld service, stroomtransport en energiebelasting. In 2013 kost een kWh ca. 23 eurocent.
Een gemiddeld huishouden verbruikte in Nederland in 2010 ongeveer 3.300 kWh.

Om makkelijk analyses te kunnen uitvoeren zijn aan de lijst nog enkele kolommen toegevoegd met het jaar, de maand en de week.
zon2NB  de gegevens worden opgeslagen in een zogenaamde Excel-tabel met de naam Zon (zie het artikel over de voordelen van tabellen). Formules en lay-out worden bij het toevoegen van nieuwe resultaten automatisch doorgevoerd.

Gemiddeldes en totalen

zon3Wat hebben de 3 panelen in die 5 jaar opgeleverd? Het Voorbeeldbestand bevat een tabblad Ovz met enkele statistieken.

In cel C2 wordt het totaal aantal opgeleverde kWh’s bepaald, de SOM van alle  waardes in de kolom kWh van de Excel-tabel Zon. Die cel heeft de naam TotKWH gekregen.

NB wanneer de tabel Zon wordt uitgebreid met volgende dagen dan zal de formule direct geactualiseerd worden met de nieuwe gegevens.

In cel C3 (met de naam AantDg) staat de volgende formule om het aantal geregistreerde dagen te tellen:
=MAX(Zon[Datum])-MIN(Zon[Datum])+1

Dus de grootste datum in de tabel minus de kleinste; gecorrigeerd met 1 omdat de eerste dag ook meetelt.

NB aangezien ik zeker weet dat alle dagen gevuld zijn had ik ook kunnen gebruiken:
=AANTAL(Zon[Datum])

Het gemiddelde per dag hebben we nu ook: in cel C4 (met de naam GemDag; deze naam wordt in de voorwaardelijke opmaak van het tabblad Data gebruikt) staat de formule:
=TotKWH/AantDg

Door in C5 het gemiddeld aantal dagen per jaar in te vullen weten we het jaargemiddelde.

zon4Natuurlijk willen we ook de totalen per jaar zien. In cel F3 staat daartoe de formule:
=SOM.ALS(Zon[Jaar];E3;Zon[kWh])

Dus de som van alle kWh’s waarvoor geldt dat in de kolom Jaar de waarde uit cel E3 staat.

Kopieer de formule naar beneden en klaar is …!

Het lijkt er op, dat we in 2016 het resultaat van het topjaar (2015) niet gaan bereiken.

Om wat beter inzicht te krijgen hoe de resultaten over de tijd zijn verdeeld, maken we wat grafieken.

Grafieken

zon5

Het resultaat van de afgelopen 5 jaar in beeld: in het blauw de dagresultaten en de rode lijn geeft het totaal in de loop van de tijd weer.

Hoe is deze grafiek gemaakt:

  1. zon6op basis van de gegevens in het tabblad Data is een draaitabel gemaakt (via de menu-optie Invoegen en dan Tabellen/Draaitabel); zie het tabblad OvzDag in het Voorbeeldbestand:
    als rij-label is daar de Datum genomen en de kWh’s zijn 2 keer in het waardegebied gezet.
  2. de 2e kWh-kolom heeft een aparte weergave:
    zon7Via rechtsklikken op één van de waardes in die kolom is de optie Waarden weegeven als gekozen. Na het klikken van de optie Voorlopig totaal in … kan één van de velden in Rijlabels worden gekozen (in dit voorbeeld hebben we er maar 1, namelijk Datum).
    Op deze manier creëren we een lopend cumulatief: het resultaat is de som van alle kWh’s tot en met de betreffende datum.
  3. Kies bij Opties in het tabblad Hulpmiddelen voor Draaitabellen het onderdeel Draaigrafiek; zorg dat de dagresultaten Kolom-grafiek als type krijgen en het lopend cumulatief een lijngrafiek en plaats deze laatste op de secundaire as.
  4. Nog even een sprekende titel:
    * in cel F2 staat de formule:
    =”Zonnepanelen set 1: “&TEKEN(13)&TEKST(GemDag;”0,0″)&” kWh gem per dag en “&TEKST(TotKWH;”#.##0″)&” totaal na “&TEKST(AantDg;”#.##0″)&” dagen”
    Hier worden diverse teksten aan elkaar ‘geplakt’ met het &-teken; naast letterlijke teksten ook verwijzingen naar bepaalde cellen (inclusief opmaak mbv de functie TEKST). De functie TEKEN zorgt met de code 13 voor een overgang naar een nieuwe regel (niet direct zichtbaar in de cel F2).
    * in het tabblad Hulpmiddelen voor Draaigrafieken zorgen we via de Indeling en de optie Grafiektitel dat er een titel tevoorschijn komt; terwijl deze actief is tikken we in de formulebalk in =OvzDag!$F$2 (of we klikken met de muis op de betreffende cel)

Maar een grafiek per dag is wel heel gedetailleerd; in het Voorbeeldbestand is een tabblad OvzWk opgenomen waarin opnieuw een draaitabel is gemaakt op basis van de gegevens uit het tabblad Data.
In de rijen staan nu 2 velden: het Jaar en de Week. De 2e kolom van de kWh’s krijgt een voorlopig totaal op basis van de Week, zodat een lopend cumulatief per jaar ontstaat.
zon8Op eenzelfde manier is een grafiek per maand gemaakt (zie tabblad OvzMnd):

zon9

Om de resultaten per maand in de loop van de tijd beter te kunnen vergelijken hebben we ook nog tabblad OvzMnd2:

zon10

Duidelijk is te zien, dat de afgelopen maand (juni 2016) ver achter is gebleven bij het resultaat in dezelfde maand de jaren daarvoor; 60 in plaats van 90 kWh!

NB juni 2011 is zo laag omdat die maand slechts 2 productiedagen telde.


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

Elf-proef

bsnBSN: iedereen heeft er een, maar weinig mensen zullen dit nummer uit hun hoofd kennen.
Bij het invullen worden dan ook vaak fouten gemaakt. Iedere instantie wil daarom graag geautomatiseerd checken of het ingevoerde getal juist is.

De controle die je op een BSN kunt uitvoeren is een variant van de elf-proef. Vóór het IBAN-tijdperk werd deze gebruikt om bankrekeningnummers te controleren.

Hoewel de toepassing van de elf-proef voor bankrekeningnummers niet meer geldt, komt deze in de praktijk toch nog regelmatig voor. In Excel is die in te bouwen, maar vergt nogal wat hulpkolommen; de elf-proef is dan ook bij uitstek geschikt om via VBA in een eigen formule te verwerken.
Hieronder (na uitleg van de elf-proef) dan ook voorbeelden in Excel en met behulp van VBA (deze functie is dan ook zonder meer bruikbaar in Access).
Daarna behandelen we een alternatieve functie die ook geschikt is voor de controle van een BSN.

Elf-proef

Wikipedia: “De elfproef (11-proef) is een test die in het Nederlandse elektronische betalingsverkeer werd uitgevoerd op negen- en tiencijferige Nederlandse bankrekeningnummers, voor de invoering van het IBAN, om te controleren of het nummer een geldig rekeningnummer kan zijn. Varianten van de elfproef die gebruikmaken van een controlecijfer, worden toegepast bij andere belangrijke nummers, zoals het burgerservicenummer en het betalingskenmerk op een acceptgiro.

Het laatste cijfer van het rekeningnummer wordt met 1 vermenigvuldigd, het voorlaatste met 2, het op twee na laatste met 3, enzovoorts. De producten worden bij elkaar opgeteld en vervolgens wordt de som gedeeld door 11. Het resultaat van deze deling moet voor een geldig rekeningnummer een geheel getal zijn.

Duidelijk toch: ieder cijfer van het rekeningnummer moeten we vermenigvuldigen met zijn gewicht, die producten optellen, de som moet dan deelbaar zijn door 11 oftewel de rest (na deling; bijvoorbeeld bij het delen van 24 door 11 is de rest 2) moet dan nul zijn.

Excel-voorbeeld 1

Ik bedacht me dat ik ooit eens voor een toepassing de functie Elfproef had gemaakt en die later ook bij cursussen gebruikt had. Even zoeken en ja hoor gevonden.

Maar voordat we die gaan gebruiken kijken we eerst in Excel hoe de elfproef werkt: ziet tabblad Vb1 van het Voorbeeldbestand.
Elfproef1Van het banknummer in kolom A wordt telkens, van achter af, een cijfer geïsoleerd (kolommen D, F, H etcetera; bekijk ook de formules in de kolommen E, G enzovoort) en die cijfers worden in kolom C met hun gewicht vermenigvuldigd. In kolom C worden die producten ook nog opgeteld en wordt de Rest bepaald bij deling door 11. Als daar 0 uitkomt dan is het antwoord ok, anders Geen juist nr.

Elfproef2Deze elfproef is bedoeld voor bankrekeningnummers bestaande uit 9 of 10 cijfers. Kolom A bevat dan ook een input-controle, zodat we in de berekeningen niet allemaal controles hoeven in te bouwen.
De input-controle is geïmplementeerd met behulp van Gegevens-validatie: kies de menutab Gegevens en dan in het blok Hulpmiddelen voor gegevens de optie Gegevensvalidatie. De drie tabbladen zijn als volgt ingevuld:

Elfproef3

Elfproef4Elfproef5

VBA-voorbeeld 1

Om al die hulpkolommen te vermijden is in kolom B gebruik gemaakt van een eigen functie ElfProef1, die dezelfde resultaten oplevert:
Elfproef6In het Voorbeeldbestand is de functie te vinden door de VBA-editor te starten (Alt-F11 of  via het tabblad Ontwikkelaars of door onderaan rechts te klikken op de naam van het tabblad Vb1 en Module1 te kiezen).

Enige uitleg is op zijn plaats:

  1. In Excel wordt de functie aangeroepen met als parameter (de verwijzing tussen de haakjes) een bankrekeningnummer (in het voorbeeld door te verwijzen naar de cellen in kolom A). In de definitie van de Function ElfProef1 staat dan ook dat er een parameter getal wordt doorgegeven van het type Long (een groot geheel getal). Consequentie hiervan is wel, dat als de functie in Excel verwijst naar een tekst (bijvoorbeeld naar cel B1) de functie niets doet en als resultaat #WAARDE! teruggeeft.
    Achter de functie staat As String: het resultaat van de functie is een tekst (string).
  2. ‘groene’ teksten is voor VBA commentaar; hebben verder geen functie. Invoeren door een enkele aanhalingsteken in te tikken.
  3. met Dim geven we aan welke variabelen we in het programma/de functie gaan gebruiken
  4. om hierna makkelijk de cijfers één voor één te kunnen ‘losknippen’ maken we met behulp van de VBA-functie Str van het getal een tekst (string)
  5. door middel van de For-loop wordt de berekening uitgevoerd: de eerste keer wordt i gelijk aan 1, via Next wordt die 2 enzovoort net zolang tot i groter is dan de lengte van de tekst; dan gaat het programma verder met de opdracht na Next
  6.  in de loop wordt de waarde (Val) bepaald van de diverse cijfers en die waarde wordt met i vermenigvuldigd en bij het (reeds bestaande) lngResult opgeteld.
    Omdat het laatste cijfer het gewicht 1 krijgt etc moeten we met het losknippen achteraan beginnen: dus we nemen het gedeelte (Mid) van de tekst dat start op positie lengte + 1  – i; de laatste parameter van de Mid-functie (het getal 1) geeft aan dat we 1 positie uit de tekst knippen.
  7. we gaan er van uit, dat het bankrekeningnummer voldoet, dus geven standaard als output voor de functie ok mee
  8. maar we moeten nog testen wat de deling door 11 oplevert: hier delen we de som door 11, nemen dan het gehele deel daarvan (de functie Int), vermenigvuldigen dan weer met 11 en kijken  of dit weer gelijk is aan de oorspronkelijke som. Zo niet (<> betekent niet gelijk) dan wordt de output van de functie ONGELDIG.

NB de kolommen B en C zijn van een voorwaardelijke opmaak voorzien, zodat snel duidelijk is wanneer een banknummer aan de elfproef voldoet.

Excel-voorbeeld 2

In het Voorbeeldbestand is een tweede voorbeeld opgenomen (zie tabblad Vb2).
Elfproef7We maken van het bankrekeningnummer uit cel A3 (met dezelfde gegevensvalidatie als in voorbeeld 1) eerst een tekst van 10 tekens: we zetten daartoe vóór het rekeningnummer eerst 10 nullen (mbv het &-teken) en nemen dan de 10 rechtse tekens daarvan (cel B3).

NB in cel B4 staat een alternatief met de functie Herhaling, waardoor het duidelijker is dat we 10 keer de nul er voor zetten.

In de kolommen ontleden we de tekst weer in 10 losse gedeelten (van achter naar voren); er wordt een 0 bij opgeteld om Excel automatisch de tekst te laten omzetten naar een getal. In rij 4 wordt de vermenigvuldiging uitgevoerd, waarna in cel D4 de Som daarvan wordt bepaald. In cel D3 bepalen we dan of een deling door 11 als rest 0 oplevert.

In cel D5 wordt de vermenigvuldiging, optelling en check in één formule uitgevoerd:

=ALS(REST(SOMPRODUCT(E2:N2;E3:N3);11)=0;”ok”;”ONGELDIG”)

Voor uitleg over de functie SOMPRODUCT zie mijn vorige artikel.

VBA-voorbeeld 2

In de VBA-editor is ook een functie ElfProef2 opgenomen (alles met isBSN even overslaan).

De verschillen met de vorige functie:

  1. de input-parameter getal is gedeclareerd als String (tekst)
  2. een voordeel daarvan is, dat we de functie exacter kunnen laten aangeven wanneer de invoer niet aan onze eisen voldoet:
    If getal = “” Or IsNull(getal) Or Not IsNumeric(getal) Then
            ElfProef2 = “Geen juiste invoer”
            Exit Function
        End If
    Dus als de invoer leeg is (op 2 manieren) of als het geen numerieke waarde (getal) is dan wordt de output van de functie Geen juiste invoer en wordt deze afgebroken.
    NB deze test kan uitgebreid en nog concreter gemaakt worden afhankelijk van de behoefte.
  3. in de functie vertalen we het getal naar een tekst van 10 tekens (vergelijkbaar met wat we in Excel hebben gedaan)
  4. de constructie lngResult Mod 11 is vergelijkbaar met de functie Rest in Excel

BSN

Zoals hiervoor aangegeven wordt voor de controle van het BSN een vorm van de elfproef wordt gebruikt: het enige verschil met de standaard is, dat het laatste cijfer niet met +1 maar met -1 wordt vermenigvuldigd.

Bij de functie ElfProef2 is daar al rekening mee gehouden:

  1. optioneel (dus niet verplicht) kan aan de functie een tweede parameter isBSN worden meegegeven (Optional isBSN As Boolean = False). Een variabele van het type Boolean kan alleen de waarde WAAR of ONWAAR (True of False) hebben;  als de parameter niet wordt meegegeven dan krijgt deze de waarde ONWAAR/False.
  2. dmv de regel
    If    i = 1   And    isBSN     Then    hulp   =   – hulp
    zorgen we er voor, dat als i gelijk is aan 1 EN isBSN waar is dat dan het tegenovergestelde van hulp wordt opgeteld

Elfproef8In het tabblad BSN van het Voorbeeldbestand zien we dat het standaard-gebruik van de functie ElfProef2 in cel C3 aangeeft dat we te maken zouden hebben met een ongeldig BSN.

Gebruiken we echter de vorm =ElfProef2(B3;WAAR), zoals in cel D3, dan levert dit als resultaat ok op.


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

Excel en het World-Wide-Web

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.


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