vrijdag 17 februari 2017

Excel: Visual Interaction, Color Linked Charts

Power BI

In Power BI  you can put more charts on one page. Next you can choose to make these charts interactive, given a connection between the sources.

In this example we can see two charts, created in Power BI. The upper one shows amounts per year; the lower obe amounts per year per month.


In this case a click on one of the years in the lower chart hightlights the corresponding data in the upper chart.

Excel

Could something the like be done in Excel. Visual interacting by clicking part of a chart does not work in Excel (without using VBA). But it is possible to create a similar visual interaction by clicking a year (G2:G5)  in the examples below.


When we pick another year within the range G2:G5 we get:


Etc..

To do this I used the function CELL("ADDRESS"). To make Excel recalculate whenever the selection changes, we need a little VBA

You can download the example ExcelInteractief.xlsm through:

https://drive.google.com/drive/u/0/folders/0B7HgkOwFZtdZVmhRQUZFM28yc1U

Excel: visuele interactie, kleurgelinkte grafieken

Power BI

In Power BI kun je meer grafieken op één pagina plaatsen. Vervolgens kun je kiezen om deze grafieken interactief te maken, gesteld dat er een verband is.

In het onderstaande voorbeeld zien we twee grafieken, gemaakt in Power BI. De bovenste geeft bedragen per jaar per maand weer; de onderste bedragen per jaar.


In dit geval laat een klik op één van de onderste jaren dan de kleuren oplichten van de maanden uit hetzelfde jaar in de bovenste grafiek.

Excel

Bij mij rijst dan de vraag of ik iets vergelijkbaars kan realiseren in Excel. Interacterend door in Excel op een onderdeel van de grafiek te klikken lukt niet. Wel is het mogelijk een verband te realiseren door in het gegeven voorbeeld op een jaar te klikken. 


Verplaatsen we de cursor binnen het bereik G2:G5 naar een ander jaar dan krijgen we:


Enzovoort.

Om het een ander voor elkaar te krijgen heb ik gebruik gemaakt van de functie CEL("ADRES"). Via VBA moet er dan bij het verplaatsen van de cursor wel steeds herberekend worden.

Bijbehorende bestand ExcelInteractief.xlsm is te downloaden via:

https://drive.google.com/drive/u/0/folders/0B7HgkOwFZtdZVmhRQUZFM28yc1U

Voor verder Excel tips klik hier.

donderdag 9 februari 2017

Excel: grafiek met achtergrondskleur voor de kwalificatie

Onderstaand zien we een grafiek waarbij de achtergrondkleur de categorie kwalificeert waarin de waarde valt.



De grafiek is gebaseerd op de volgende data:

Datums
Percentage 
Ondermaats
Gemiddeld
Goed
1-5-2013
30%
65%
15%
40%
1-6-2013
60%
65%
15%
40%
1-7-2013
55%
65%
15%
40%
1-8-2013
60%
65%
15%
40%
1-9-2013
90%
65%
15%
40%
1-10-2013
85%
65%
15%
40%
1-11-2013
100%
65%
15%
40%
1-12-2013
60%
65%
15%
40%
1-1-2014
110%
65%
15%
40%
1-2-2014
80%
65%
15%
40%
1-3-2014
70%
65%
15%
40%

§   Vul de reeks A1:E1 met bovenstaande labels.
§   Vul de reeks A2:B12 met bovenstaande waarden in de kolommen Datums en Percentage.
§   Vul de reeks C2:D2 met de waarden 65%, 15%.
§   In E2 plaatsen we de formule:

=120%-D2-C2

§   In C3 plaatsen we de formule:

 =C$2

§   Die trekken we door naar beneden tot aan C12 en naar links tot aan E12.
§   Met de cursor in de data kiezen voor Aanbevolen grafiek (versie 2013).

We krijgen dan:


Voor de onderste drie rechte lijnen kiezen we dan een ander grafiektype: Gestapeld vlak.

§   Klik met de rechter muisknop op één van de lijnen.
§   Kies Ander type grafiekreeks.
§   Kies voor de onderste drie Gestapeld vlak.


§   Klik op OK.
§   Klik met de rechter muisknop de linker Y-as.
§   Kies As opmaken.
§   Zet de maximum waarde op 1,2.
§   Klik met de rechter muisknop op de legenda onder.
§   Kies Legenda opmaken.
§   Kies Links.
§   Verwijder de optie Percentage uit de legenda.
§   Selecteer de percentage lijn in de grafiek.
§   Klik met de rechter muisknop op de percentage lijn.
§   Kies Gegevenlabels toevoegen.
§   Vervang de grafiektitel door: Percentage: goed, gemiddeld, ondermaats
§   Maak ten slotte de grafiek wat groter.

Voor verder Excel tips klik hier.

maandag 6 februari 2017

Friesland: de winterelfstedentocht 2017 voor fietsers en steppers

Inleiding

In de winter van 2015 werd er in Leeuwarden in de WTC hallen een grote fietsbeurs georganiseerd. Een van de de onderwerpen op deze beurs was fietskleding om fietsen in de winter comfortabeler te maken. Deze beurs vormde ook de aanleiding om éénmalig een winterelfstedentocht voor fietsers te organiseren. Hoe kon je beter laten zien dat ook lange fietstochten in de winter mogelijk zijn? Stichting Friesland Beweegt nam de uitdaging op zich.

De eerste editie: 8 februari 2015

De volledige elfstedentocht met een afstand van rond de 200 kilometer wordt voor 8 februari 2015 uitgeschreven. Naast gewone fietsen mogen ook ligfietsen, velomobielen (de "sigaren") en steppers meedoen. Daarnaast is er nog een verkorte editie, een zevenstedentocht door het onderste deel van Friesland over 90 kilometer. Er kunnen zich maximaal 500 deelnemers inschrijven.

Ondanks de vrij lage temperaturen en de harde noordwestenwind is de tocht een groot succes. Dat het wel droog bleef, speelde een belangrijke rol. Veel fietsers en een zevental steppers slagen erin de gehele tocht te voltooien. Een verslag van de ervaringen van de steppers verschijnt op de website van TV Bolsward.

Bikkels uit Bolsward en Sneek op de step bij Bartlehiem.

Van de zevenstedentocht heb ik geen gegevens. Wel hebben ook aan die editie een flink aantal steppers meegedaan.

Zelf ben ik begin december onder het mes geweest en onvoldoende hersteld om ook maar aan deelname te denken. Wel ben ik die dag toeschouwer in Dokkum. Op dat moment heb ik drie verschillende elfstedentochten gedaan: fietsen in de zomer, de kano-editie en de wandelelfstedentocht. Uiteraard is mijn belangstelling gewekt. Bovendien ben ik dan al in training voor de stepelfstedentocht.

De tweede editie: 14 februari 2016

Succes smaakt naar meer. Friesland Beweegt besluit een nieuwe tocht te organiseren. Weer twee varianten maar nu met maximaal 1500 deelnemers. Deze keer is de inschrijving in een ommezien vol. Nu zit ik er ook bij, met maat Ritske. Speciaal voor de gelegenheid heb ik tijdens een vakantie in Canada een dure windstopper gekocht.

Als we op 13 februari de stempelkaarten ophalen is het prima weer. De verwachting is evenwel onheilspellend. We besluiten het er toch op te wagen. De volgende ochtend in de auto motregent het. Er staat een harde wind uit het noordoosten. Het nieuws rept van een ijzelfront bij Zwolle. Aangekomen in Leeuwarden motregent het nog steeds. Het lijkt ook niet erg druk. Later horen we dat er totaal maar zo'n 900 mensen vertrokken zijn.

We gaan ondanks de barre omstandigheden toch van start. Anders dan de meesten fietsen we direct naar de Boksumerdam waar de Zwette ongeveer begint, start punt van DE TOCHT. Het is donker en regenachtig, maar met de wind in de rug fietst het goed over het smalle fietspad. Continu dertig op de teller.

Bij de eerste stempelpost in Sneek wordt ons langzaam duidelijk dat het wel erg onaangenaam weer is. Ik heb de verkeerde handschoenen aan. Ze raken doorweekt. Voor de rest hou ik het nog wel aardig warm. Ook Ritske heeft problemen om zijn handen warm te houden.

We stempelen in IJlst en fietsen door naar Sloten met de wind nog steeds vol in de rug. In Sloten komen we Suzan Kroontje achterop, op de step. Ik ken haar van de stepelfstedentocht. Volledig in regenpak is ze met de zevenstedentocht bezig. Knap,

In Sloten raak ik Ritske kwijt. We treffen elkaar pas weer in Stavoren. We moeten stempelen bij hotel De Vrouwe van Stavoren. Het is daar tjokvol. We gaan een hapje eten bij Tip Stavoren. Hier zit gek genoeg niemand. We proberen ook onze kleren en handschoenen wat te drogen. Het weer heeft zijn tol geëist.

Na het eten stappen we weer op. We moeten gelijk langs de dijk fietsen. Allemaal drek en lastige steentjes en een bulderende noordooster tegen. De motregen slaat ons in het gezicht. Ik voel me niet in orde en begin de kou te voelen. Stempelen in Hindeloopen en Workum. Met hangen en wurgen weet ik Bolsward nog te halen. Handen kletsnat en ijskoud. 

Na het stempelen duiken we weer een café in. Hier neem ik het besluit te stoppen. Ritske gaat alleen door. Hij heeft wel koude handen maar voelt zich sterk genoeg een poging te wagen. Het is al na enen als hij opstapt. Ik bel mijn zwager uit Leeuwarden en die pikt me op in Bolsward.

Dan begint het lange wachten. Via de radio horen we dat de tocht is stil gelegd vanwege het barre weer. De stempelposten vanaf Franeker worden gesloten. Mensen die Franeker bereikt hebben, wordt verzocht direct naar Leeuwarden door te fietsen. Ik probeer Ritske te bellen maar krijg hem niet te pakken. Grote kans dat hij niets hoort.

Later bel ik zijn ouders in Dokkum een paar keer. Uiteindelijk blijkt hij daar gepasseerd te zijn. Tegen zevenen is hij eindelijk in Leeuwarden. Toch nog redelijk vief. Wel hadden zijn koude handen bellen onmogelijk gemaakt. Hij had onderweg nog van alles meegemaakt. Mensen die in Franeker toch nog een stempel hadden weten te krijgen. Mensen die bij Bartlehiem in de sloot gefietst waren. We horen ook nog dat er iemand in Dokkum was gearriveerd met een lichaamstemperatuur van 34 graden. Bar en boos.

Uiteindelijk schijnen rond de 400 mensen gefinisht te zijn. Niet duidelijk is hoeveel er eigenlijk de gehele tocht hebben afgelegd. Waarschijnlijk veel minder.  

Maar mijn vader zei altijd: "Takom jier komt der wer in jier."

De derde editie: 5 februari 2017

En inderdaad, mijn vader had gelijk, er komt weer een jaar. Ondanks de problemen van 2016 komt er een nieuwe editie. En wij zijn er weer bij. Wel geleerd van de vorige keer. Ik heb nu waterdichte handschoenen en een salopette met een waterafstotende bovenlaag.

In november geef ik zeven mensen op met als groepsnaam: 11-steden dwingers. Ritske heeft er twee gedaan, Durk en Peter doen hun derde verschillende, het wordt mijn zesde en de zevende van Frans. De andere twee hebben niet eerder meegedaan maar vallen uiteindelijk ook af.

Dan begint het trainen. Ik heb een uitstekende conditie overgehouden aan de MTB elfstedentocht van begin september 2016. Vanaf november krijg ik allerlei gezondheidsproblemen die mijn conditie aantasten. In een maand tijd word ik zelfs twee keer geopereerd. De laatste keer eind december. Toch zit ik begin januari weer op de fiets. De conditie is duidelijk teruggelopen. Maar eens kijken.

Op 4 februari halen Ritske en ik de startkaarten uit Leeuwarden. De weersverwachtingen stemmen heel positief: wel kil en koud maar heel weinig wind en uit uiteenlopende richtingen.

Ik ben er klaar voor.

Op 5 februari komt Frans uit Workum aangereden, de rest uit de stad Groningen. Durk  staat om zes uur voor de deur. Onderweg motregent het nog wat, maar dat stopt als we er bijna zijn. Half zeven zijn we bij Leeuwarden. We pakken daar de verkeerde afslag en komen na enig zoeken op een nagenoeg leeg stuk parkeerterrein bij de ijshal. Als we uitstappen blijken we precies naast Ritske en Peter te staan. Toeval is logisch.

We maken ons startklaar en dan bel ik Frans. Die blijkt de goede afslag genomen te hebben die helaas afgesloten is. Hij is er nog lang niet. We gaan de hal in en stellen ons op voor de start van zeven uur. Daar worden we nog minutenlang getrakteerd  op de woordenbrij van een lokale spraakwaterval. Niet geïnteresseerd.

Na vertrek uit de hal bel ik Frans opnieuw. Die verwacht nog wel even nodig te hebben. Ik besluit daarop zijn startkaart af te geven aan organisator Eric Lansu. We kunnen hier niet te lang staan te kleumen. Frans snapt het. Hij is trouwens met zijn ATB. Wij allemaal op de racefiets. Rond kwart over zeven vertrekken we.

We herhalen onze aanpak van vorig jaar en fietsen direct naar het begin van het fietspad langs de Zwette. Het gaat dan allemaal heel voorspoedig. Het is droog. Het wegdek is vochtig, maar er staat praktisch geen wind.

We fietsen niet op de GPS maar op mijn geografische kennis. Bij de afslag Bozum raken we elkaar even kwijt. Durk en ik nemen de afslag naar Deersum. De andere twee volgen de officiële route. Vermakelijk is hoe de GPS rijders beginnen te roepen als ze een foute afslag vermoeden. Maar ja, je zult hier maar niet bekend zijn.

Bij Scharnegoutum pikken we de anderen weer op. Dan zijn we weldra bij de eerste stempelpost: Sneek. Later hoor ik dat hier een grote 'stempelkaartfraude' plaatsgevonden heeft. Een club heeft alle stempels maar in één keer laten ponsen. Het gaat om de zwager van Durk. Die zou in Sneek starten. Het verhaal is wel zwaar overtrokken. Ze hebben de tocht wel gewoon gefietst en zelfs nog twee fietsers uit de sloot gehaald.

We zijn nu snel in IJlst. Opnieuw stempelen. Dan een lange, saaie weg naar Sloten. In de verte zien we de zendmast van Spannenburg boven de laaghangende wolken uittorenen. Mooi gezicht. In Sloten geen Suzan Kroontje. Ze doet deze keer niet mee. Wel zien we na Sloten heel veel andere steppers. Meest met grote wielen. We maken even een praatje. Ze lijken allemaal met de zevenstedentocht bezig. Later horen we dat vijf steppers de hele tocht gedaan hebben. Een prestatie van formaat.

We passeren Balk. Weldra zijn we nu bij het Reaklif met de beroemde steen: "Leaver dea as slaef". Af en toe steekt er een vleugje wind op maar veel stelt het niet voor. Stempelen in Stavoren. Even wat eten en dan langs de dijk door naar Hindeloopen. Vorig jaar begonnen hier de problemen. Nu is het alleen even oppassen met het wegdek en de modder. 

Stempelen in Hindeloopen en door naar Workum. De officiële route telt een tunneltje dat flink onder water staat. Een beetje ongemak. En al weer stempelen in Workum. Ik zie zo gauw Frans zijn vrouw niet staan. Zal thuis wel met de soep voor Frans bezig zijn. 

We gaan door naar Bolsward. Daar zullen we even wat gaan eten. Even wat meer wind in de rug en het tempo schiet omhoog naar 32-33 per uur. Vanaf Tjerkgaast sturen ze ons langs het smalle pad langs de Parregaastervaart. Ik begin honger te krijgen. De uitsmijter wacht. In Bolsward stempelen we. Dan gaan we naar het café waar we vorig jaar ook zaten. Even bunkeren. Een uitsmijter, cola en een kop koffie voor mij. Ik bel Frans. Die zit op dat moment al in Hindeloopen. Alles is goed gegaan met zijn startkaart. Goed nieuws. Die haalt zijn zevende tocht wel.

Als we weer op de fiets stappen zijn we allemaal wat koud en stijf. Even de benen weer los fietsen. Vlak buiten Bolsward raak ik plotseling met mijn voorwiel het achterwiel van Ritske. Ik kom weer los en verlies de controle. Ik schiet de berm in en kom hard op mijn arm, zij en knie terecht. Dan voel ik mijn hoofd flink op de grond bonken. Ik kom overeind. Beduusd. Denk even dat mijn bril stuk is. Een andere fietser stopt en zegt dat mijn helm in tweeën ligt.

Mijn fiets is helemaal o.k. Ik stap weer op. Ik denk aan het zesde kruisje. Doorzetten. Het idee zonder helm verder te moeten vind ik nu even niet leuk. Ik denk er nog over mijn zwager uit Leeuwarden te bellen. Of die met een helm naar Franeker kan komen. Toch maar niet. We besluiten met zijn vieren rustig door te fietsen en risico's te mijden. Dus niet meer aansluiten bij andere groepen. Is ook niet echt nodig. Er staat toch weinig wind.

In Harlingen is de volgende stempelpost. Ik meld me nog even bij de EHBO. Die manen me voorzichtig te zijn. Als ik me niet goed voel, moet ik dat melden. Ik zeg dat ik in gezelschap van drie psychologen ben die me vast wel in de gaten houden. Ik hoor Peter naast me lachen.

In Franeker worden er foto's gemaakt door Franeker Actueel. Heeft de fotograaf iets gezien aan mijn gezicht? De grassprieten zitten nog op mijn WF11ST muts.

Foto: Hans Menninga



Rechtsachter staan RitskeDurk en PeterFoto: Hans Menninga

In Franeker staan ook een aantal mensen van een fietsclub uit Workum. Deze mensen zullen ons later steeds opnieuw inhalen. Uiteindelijk zijn we toch eerder in Leeuwarden. Terwijl wij de tactiek volgen van rustig maar gestaag doorfietsen kiezen zulke mensen kennelijk voor flink tempo met langere pauzes.

Ik vind na Franeker toch feilloos de binnendoor weg naar Schalsum. Het valt wel mee met mijn kop. Wel heb ik wat hoofdpijn. Ik kijk nu ook regelmatig op de kilometerteller. De kilometers vliegen gelukkig voorbij. We zijn heel snel in Stiens, de 'geheime' stempelpost. Ik heb 150 kilometer op de teller. Ik hoor iemand van de organisatie zeggen dat we er 155 gedaan hebben. Kan kloppen: mijn kilometerteller is niet goed afgesteld. Nog maar 45 kilometer dus.

Bij de EHBO biets ik even twee paracetamolletjes voor de hoofdpijn. Daarna ben ik even vergeten of ik gestempeld heb. En jawel: de psychologen grijpen in. Ritske en ik volgen niet de officiële route, een smal paadje langs de Dokkumer Ee, maar een wat bredere weg. Het lijkt alsof er wat meer wind opsteekt en dat we die straks tegen zullen hebben. In Dokkum treffen we Ritskes zus en moeder nog. We moeten komen koffie drinken.  Dat slaan we maar even over.

Ritske met moeder Affie

De stempelpost in Dokkum is bij De Posthoorn. Als we arriveren komen Durk en Peter net naar buiten lopen. Wij gaan ook stempelen. Daarna moeten we nog even in de slag met een familiedelegatie van Ritske. Ook zijn vader is present. We blijven niet te lang. Anders slaat de kou toe. Het is tien voor vier en we kunnen mooi voor het donker binnen zijn.

Peter in Dokkum bij de Posthoorn. Foto: Sjoerd Hofstra

Ik zie een beetje op tegen het laatste stuk maar gelukkig begin ik me weer steeds beter te voelen. Ik krijg ook weer praatjes. Met de wind blijkt het opnieuw mee te vallen. We kachelen voorspoedig naar Leeuwarden. Wel blijft Peter elke sprint naar plaatsnaamborden winnen. Wat wil je ook met zulke turbodijen.

Bij Wyns pakken we toch de officiële route: het Wynsumer binnenpaad. Veel drek en koeienstront en in het donker geen aanrader. Nu geen probleem. Bij het plaatsnaambord van Leeuwarden doet Ritske nog een slimme sprintpoging. Hij denkt Peter de pas af te snijden. Maar nee, de turbodijen winnen opnieuw. Wij kijken lachend toe.

Nog vier kilometer door Leeuwarden en we zijn bij de IJshal. Tien voor vijf. Medailles halen en organisator Eric Lansu bedanken. Petje af voor hem en zijn medewerkers. En dan inpakken en wegwezen.

De medaille van elfstedentocht nummer zes is binnen:

Nummer zes

Voor mensen die zich willen vereeuwigen op de ELFSTEDEN  WALL OF FAME.

woensdag 1 februari 2017

Excel: Speedometer met handleiding

Inleiding

Echte speedometers met wijzertjes en zo kent het reguliere Excel niet. We kunnen wel zelf zoiets maken. En dat gaan we doen.

Hoe maken we zoiets?

§   We typen vanaf cel A1:

cijfers
1
1
1
1
1
1
1
1
1
1
10

De onderste 10 is de optelsom van de bovenste waarden.

§   We maken hier een ringgrafiek van.
§   We wissen de labels.
§   We laten deze ring 270 graden laten draaien.



§   Voor het onderste stuk kiezen we dan: Geen opvulling.

  
§   Voor de wijzer typen we:

D1
Wijzer

D2
50
aangestuurd door een schuifbalk
D3
1
bepaalt de breedte van de wijzer
D4
49
=D5-D2-D3
D5
100


§   We selecteren dan D2:D5.
§   Klikken op kopiëren.
§   Klikken op de grafiek.
§   Klikken op plakken.

We krijgen dan automatisch een tweede ringgrafiek. Het geheel ziet er zo uit:



§   De rode wijzer geven we de kleur zwart.
§   Alle andere segmenten zetten we op Geen opvulling.
§   We kiezen dan voor de buitenste cirkel een ander grafiektype: Cirkel.
§   De draaiing gaat dan terug naar 0 graden; die zetten we terug op 270 graden.
§   We koppelen de gegevens ook aan de Secundaire as.



§   We selecteren dan de zwarte wijzer.
§   We kiezen via de tab Indeling Þ Gegevenslabel toevoegen Þ Einde buiten.

We hebben dan:



§   We dubbelklikken dan op het label (de cursor staat er nu in).
§   We gaan in de formulebalk staan.
§   We maken in de formulebalk een harde verwijzing naar cel D2

Let op! Bladnummer kan afwijken!

  =Blad2!$D$2

§   Sluit af met een druk op ENTER.
We hebben nu dit (alleen de schuifbalk moet nog worden gemaakt):



§   Nu maken we nog een schuifbalk die loopt van 0 tot 100 en een koppeling heeft met cel D2.


Tot slot plaatsen we de labels in de cijferreeks.

§   Ga naar Gegevensbron selecteren.
§   Selecteer Cijfers.
§   Kies bij Horizontale aslabels voor de knop Bewerken.
§   Selecteer het bereik B2:B11.





We selecteren de reeks via de Tab Indeling.



§   We kiezen Gegevenslabels Opmaken Þ Meer opties voor gegevenslabels Þ Categorienaam


§   Daar kiezen we Categorienaam
§   Via de Tab Ontwerpen Þ Gegevens selecteren passen we de Aslabels van de langste reeks aan naar:



Ringgat terug naar 10% en een ronde vorm maken van 0,6 bij 0,6 cm zwart van kleur

§   Selecteer Reeks "Cijfers" via de tab Indeling links bovenin. 



§   Kies de knop



§   Zet de grootte van het ringgat terug naar 10%.
§   Maak een ronde vorm (cirkel) van 0,6 cm bij 0,6 cm, kleur is zwart.
§   Plaats de vorm op het ringgat.
§   Zet de lettergrootte van de labels desgewenst naar 9 punten.





Voor verder Excel tips klik hier.