Tagarchief: Spreidingsgrafiek

Op koers



Soms overkomt het je: krijg je een Excel-vraag en er gaat een heel nieuwe wereld voor je open.
Een paar weken geleden kreeg ik de vraag hoe je er voor zorgt dat, bij het uitzetten van een te vliegen traject, bij het berekenen van een gecorrigeerde koers er geen negatieve waarden of waarden groter dan 360o ontstaan.
Redelijk eenvoudig, maar toen kwam de volgende vraag: kun je gecorrigeerde koersen ook in Excel berekenen?

Na wat over en weer mailen werd het me duidelijk dat het een amateur-vlieger (zeg je dat zo?) heel wat tijd kost voordat hij kan gaan vliegen: routes, koersen moeten van te voren worden uitgestippeld, een zogenaamde vluchtlog moet worden opgesteld etcetera.

Maar zo’n nieuwe wereld is dan weer mooi een aanleiding om een Excel-artikel te schrijven.

Opmaak koersen

Bij het invullen van een vluchtlog moet niet alleen de koers worden uitgestippeld, ook moet deze theoretische koers nog gecorrigeerd worden voor wat betreft het effect van wind. Voor ons buitenstaanders: als de wind van rechts komt, dan zullen we ook iets meer naar rechts moeten ‘sturen’ dan de theoretische koers aangeeft.
Bij zo’n correctie kan het natuurlijk voorkomen dat we een koers kleiner dan 0 of groter dan 360 krijgen; dat moet dan nog aangepast worden.

In het Voorbeeldbestand op het tabblad KK staat een recht toe recht aan berekening. In kolom B staat de kaartkoers (theoretische koers), in kolom C is de correctie vanwege de wind opgenomen. In kolom D tellen we die 2 waardes op. Maar als die optelling negatief is, dan tellen we er nog eens 360 bij op. Wordt de som groter dan 360 dan trekken we er 360 vanaf.

Dit was eigenlijk de oorspronkelijke vraag, die ik kreeg. Maar uit onze correspondentie bleek, dat vliegeniers gewend zijn om koersen altijd met 3 getallen weer te geven en de correctie krijgt altijd een + of – teken.

Het eerste is in Excel snel opgelost. De cellen met koersen krijgen allemaal de opmaak zoals hiernaast weergegeven:

  1. selecteer de cellen die dezelfde opmaak moeten krijgen (eventueel Ctrl ingedrukt houden als de cellen niet aaneengesloten zijn)
  1. klik rechts en kies Celeigenschappen (of druk op Ctrl-1)
  2. kies de categorie Aangepast
  3. en het veld onder Type krijgt de waarde 000 (dus altijd 3 cijfers weergeven)

Om het tweede ‘probleem’ op te lossen krijgen de cellen in kolom C een andere opmaak:
Met het gedeelte voor de eerste punt-komma geven we aan dat positieve getallen altijd 2 cijfers bevatten en van een plus-teken moeten worden voorzien; via het tweede blokje bepalen we hoe negatieve getallen er uit moeten zien en als laatste dat een nul-waarde door 2 nullen wordt weergegeven.

NB de inhoud van de cellen verandert hierdoor niet, we hebben alleen de opmaak aangepast.

Bij de berekening van de Kompaskoers (kolom D) hebben we een geneste Als-functie gebruikt. Zoals zo vaak: in Excel kunnen we dat ook op een kortere manier oplossen.

Met behulp van de functie Rest bepalen we wat er overblijft wanneer we delen door 360 en en het gehele deel ‘weggooien’. Bij getallen tussen 0 en 360 is de rest na deling het getal zelf, bij getallen groter dan 360 wordt er 360 vanaf getrokken.

Maar de formule werkt ook als de optelling een negatief getal oplevert!
Dat blijkt ook uit kolom F, waar we de werking van de Rest-functie hebben nagebootst:
=D3-360*GEHEEL(D3/360)

Windcorrectie

Na het beantwoorden van de eerste vraag, kreeg ik een mail terug met een dankwoord en de opmerking: “Maar de windcorrectie berekenen in Excel is zeker te ingewikkeld?

Wat bleek: als de windsnelheid en -richting bekend zijn moeten er allerlei handmatige acties op aparte apparaten uitgevoerd worden om de windcorrectie te bepalen.

Eerste reactie mijnerzijds was: dat kan niet zo moeilijk zijn, een beetje rekenen met vectoren, daar hebben ze goniometrie voor uitgevonden! Maar dat viel toch wel tegen. Dus ik moet bekennen dat ik toen toch maar even Google heb gebruikt.

Op de site en.wikipedia.org/wiki/E6B vond ik het volgende (zie ook het tabblad Heading in het Voorbeeldbestand):

Op het tabblad Heading staat een voorbeeld van deze berekeningen.

NB zorg wel dat de snelheden in dezelfde eenheden zijn uitgedrukt!

Aangezien Excel binnen de goniometrische functies met radialen werkt is de 2e set formules gebruikt. De cellen in kolom C hebben een toepasselijke naam gekregen, zodat de formules beter leesbaar en dus beter controleerbaar zijn.

NB De Excel-vertaling van sin-1 is de functie Boogsin; de waarde voor π krijgen we door de functie Pi() te gebruiken.

In kolom D zorgen we voor een afronding naar gehele getallen:

Uiteraard kunnen deze berekeningen ook in één keer (zie cel C15):
=AFRONDEN(180/PI()*BOOGSIN(Vw*SIN(PI()*(w-d)/180)/Va);0)

Vluchtlog

Als voorbeeld kreeg ik het voorbereidende materiaal voor een vlucht van Teuge naar Texel. Een uittreksel:

In het tabblad Route van het Voorbeeldbestand hebben we op basis van bovenstaande informatie de vluchtlog enigszins nagebouwd.

De vluchtlog is een Excel-tabel met de naam tblHeading. In de gebruikte formules wordt dan ook verwezen naar de kolom-koppen (een @ betekent: haal de waarde uit een kolom in dezelfde regel waar de formule staat). De formule in kolom L wordt alleen berekend als geen van de cellen in dezelfde regel in de kolommen G, H, I of J leeg is.

NB1 het rekenen met richtingen/graden gaat in de vliegerij iets anders dan in de wiskunde. Vliegen naar het noorden is richting 0o, naar het oosten 90o etc. Een windrichting betekent dat de wind vanuit die richting komt.

NB2 het omrekenen van graden naar radialen en vv kunnen we ook aan Excel overlaten (zie kolom M):
=ALS(OF([@d]=””;[@Va]=””;[@w]=””;[@Vw]=””);””;GRADEN(BOOGSIN([@Vw]*SIN(RADIALEN([@w]-[@d]))/[@Va])))

In kolom N wordt dan de te vliegen koers bepaald: =ALS([@∆a]=””;””;REST([@d]+[@∆a];360))

Uiteraard willen we ook weten hoelang we straks onder weg zullen zijn. In kolom O wordt de zogenaamde grondsnelheid bepaald, rekening houdend met wind mee of tegen:
=ALS([@Heading]=””;””;WORTEL([@Va]^2+[@Vw]^2-2[@Va][@Vw]COS(PI()([@d]-[@w]+[@∆a])/180)))

Dan kunnen we per stap (of in vliegtermen Leg) de benodigde tijd uitrekenen (kolom P):
=ALS([@Vg]=””;””;[@Afstand]/[@Vg]/24)

NB we delen ook nog eens door 24, omdat het resultaat van Afstand/snelheid het aantal uren is. Op deze manier komt in kolom P een waarde, die het dagdeel aangeeft. Door dan de cellen een opmaak van uren en minuten te geven is het resultaat voor ons makkelijk interpreteerbaar.

Route-grafiek

Nou we toch bezig zijn: we willen natuurlijk ook nog wel ‘zien’ hoe we gaan vliegen; een plaatje zou wel mooi zijn.

Alles kan! (?)
We moeten dan wel eerst de gewenste koers (dus NIET de te vliegen koers) vertalen naar wiskundige hoeken. Wat blijkt: dat is niet zo ingewikkeld, hoek = 90o – koers.

Aangezien de x-coördinaat van het eindpunt van een stap berekend kan worden via de cosinus van de hoek en de y-coördinaat met de sinus zijn we er al bijna.

In kolom S op het tabblad Route van het Voorbeeldbestand worden de x-coördinaten van de stappen bepaald, in kolom T de y-coördinaten.
Door de vorige coördinaten daar telkens bij op te tellen krijgen we de vliegroute.

NB wanneer een stap niet gevuld is wordt met behulp van de functie NB() aangegeven dat die coördinaat niet bestaat. Bij het maken van een grafiek worden die punten niet meegenomen door Excel (zie Grafiek zonder nullen).

Op basis van deze tabel is bovenstaande grafiek gemaakt. Gebruik daarvoor een spreidingsdiagram.

NB1 het omrekenen van graden naar radialen gebeurt, ter illustratie, in de kolommen S en T op een verschillende manier.

NB2 door de grafiek te laten tekenen met vloeiende lijnen ontstaat een iets reëler beeld.

NB3 om een goede weergave van de vliegroute te krijgen moet je de hoogte en/of breedte van de grafiek zodanig aanpassen, dat de rasterlijnen vierkanten vormen.

NB4 disclaimer: ik ga er wel van uit dat piloten van bijvoorbeeld een 737 Max deze spreadsheet niet gaan gebruiken!

De labels van de grafiek kunnen bijvoorbeeld nog aangevuld worden met de te vliegen koers, zie het tabblad grafRoute van het Voorbeeldbestand.


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


Analoge klok in Excel

astronomische klok praagIedereen die wel eens in Praag is geweest, heeft waarschijnlijk wel een paar keer stil gestaan bij het astronomische uurwerk in het centrum.
Zeker wanneer op het hele uur de 12 apostelen zich vertonen, is het een drukte van jewelste op het Oudestadsplein.

Daar stond ik dus ook, toen we een paar weken geleden een bezoek brachten aan deze prachtige stad.

Het bloed kruipt waar het niet gaan kan: ik dacht meteen, dat moet in Excel na te bouwen zijn!
Maar om alle facetten van de klok te implementeren valt onder de grotere uitdagingen; laten we maar eerst beginnen om een ‘gewone’, analoge klok in Excel op te zetten.

Basis

Uiteraard hebben we de huidige tijd nodig, waar we de klok op gaan baseren: in Excel gebruiken we daar de formule =NU() voor; in het Voorbeeldbestand staat die in cel C2.
Klok1LET OP dit is één van de weinige formules, die geen paramaters kent, maar om voor Excel duidelijk te maken dat het een formule betreft zijn wel de 2 haakjes nodig.

LET OP 2 tijdens het schrijven van het artikel veranderde de inhoud van cel C2 constant. In de volgende voorbeelden komen de getallen dan ook niet meer overeen met de tijd hierboven.

Uit de tijd leiden we het uur, de minuten en de secondes af met de formule =UUR(C2), =MINUUT(C2) respectievelijk =SECONDE(C2).
Omdat we een analoge klok gaan maken, die maar 12 uren kent, staat in cel E3 de formule =REST(E2;12). Hiermee berekenen we de restwaarde als we de uren van cel E2 delen door 12. Uiteraard had dit ook met de formule = ALS(E2>12;E2-12;E2) gekund, maar de eerste formule is korter (en interessanter!?).

Wijzers

Maar hoe gaan we nu die wijzers tekenen in Excel?
Daartoe moeten we ons realiseren, dat de tijd (beter gezegd het aantal seconden na 12 uur) de hoek van de wijzers bepaalt.

We gaan eerst de kleine wijzer tekenen:

  1. Klok3het aantal seconden na 12 uur berekenen we in cel C9: =(E3*60+H3)*60+K3
  2. om hierna de hoek te kunnen bepalen, moeten we weten hoeveel seconden er maximaal in 24 uur zitten; zie cel C10
  3. door de inhoud van cel C9 te delen door die van cel C10, weten we welk gedeelte van de klok door de kleine wijzer is afgelegd (cel C11)
  4. de zo berekende fractie moeten we nog in een hoek vertalen: in C12 komt de formule =C11*360, omdat een hele cirkel 360 graden is.

Nu we de hoek weten, moeten we dit gaan omzetten naar een punt op de klok (cirkel). Gelukkig hebben we vroeger allemaal goed opgelet bij wiskunde en weten natuurlijk(?) nog dat de x-coördinaat van zo’n punt wordt berekend door de cosinus van de hoek te nemen en de y-coördinaat door de sinus.
Maar we zijn nog niet klaar: in de wiskunde (en in Excel) wordt een hoek bepaald ‘rekenend tegen de klok in’; voor ons rekenwerk gebruiken we dan ook de cosinus en sinus van -C12.
Omdat Excel als basis voor de hoek de positieve x-as neemt (een hoek van 0 graden komt dus overeen met een horizontale lijn naar rechts) en we 0 uur netjes bovenaan willen laten beginnen tellen we bij onze berekende hoek nog 90 graden op.
Daarmee zou de x-coördinaat van de kleine wijzer dus worden =COS(-C12+90). Helaas Excel kent ons graden-systeem niet, maar werkt met radialen (jaja, de middelbare schooltijd is toch niet voor niets geweest!). Aangezien 360 graden overeen komt met 2π radialen, delen we onze berekende graden door 360 en vermenigvuldigen met 2π; de x-coördinaat wordt nu =COS((-C12+90)*2*PI()/360).
LET OP ook de functie PI kent geen parameters, maar de 2 haakjes zijn wel nodig.

Hetzelfde verhaal geldt ook voor de y-coördinaat: =SIN((-C12+90)*2*PI()/360).
Maar het is een kleine wijzer: dus we vermenigvuldigen de coördinaten nog met bijvoorbeeld 0,6 (staat in cel C13 en is dus makkelijk aanpasbaar).
Door nu een lijn te tekenen (en daar wat opmaak aan mee te geven) van de oorsprong van het assenstelsel (met de coördinaten 0,0) naar de zo berekende coördinaten hebben we onze wijzer. Gebruik daarvoor geen lijngrafiek maar een spreidingsgrafiek.

NB Bij een lijngrafiek komen de elementen op de x-as allemaal op dezelfde afstand van elkaar; bij een spreidingsgrafiek geef je ook de exacte positie op de x-as mee, zodat je precies kunt aangeven waar het resultaat terecht zal komen. Een spreidingsgrafiek wordt bijvoorbeeld gebruikt als op de x-as datums worden uitgezet, waarbij niet iedere datum in de bron-gegevens voorkomt. Bij een lijngrafiek worden de datums ‘gewoon’ naast elkaar geplaatst; bij een spreidingsgrafiek heeft iedere datum zijn eigen positie op de x-as en zie je onmiddellijk waar er datums ontbreken.

Klok4Het tekenen van de grote en de seconde-wijzer gaat op dezelfde manier. Bedenk daarbij dat het maximale aantal seconden voor de grote wijzer maar 3600 is (60 minuten) en voor de secondewijzer slechts 60.

Klok5Om onze klok nog verder aan te kleden, zetten we ook de uren er op: op dezelfde manier als voor de wijzers bepalen we de hoeken en daarmee de coördinaten. De lijnen die daardoor ontstaan maken we ‘onzichtbaar’ (bij Lijnkleur de optie Geen lijn aanvinken); wel geven we de lijnen een label mee, et voilà.

Nu nog even op F9 drukken (Herberekenen) en de tijd in cel C2 en dus op de klok wordt geactualiseerd.

Automatische klok

Om iedere keer op F9 te drukken wanneer we de tijd willen weten, is natuurlijk niet te bedoeling.
Daarom nog een stukje VBA er achteraan (opgeslagen in zogenaamde macro’s, vandaar dat het Voorbeeldbestand de extensie xlsm heeft):

  1. druk op Alt-F11 (openen van de Visual Basic-editor)
  2. kies Invoegen en dan Module
  3. in de module voegen we een globale variabele toe (die is dus voor het gehele project geldig), waarmee we onthouden of de klok moet lopen of niet:
    Global KlokAan As Boolean
  4. dan een kleine VBA-routine:
    Sub KlokActief()
        If KlokAan Then
            Application.OnTime Now + TimeValue(“00:00:01”), “KlokActief”
            Application.Calculate
        End If
    End Sub
    Wanneer deze routine aangeroepen wordt, wordt allereerst gecontroleerd of de variabele KlokAan Waar is (True). Zo ja dan wordt er voor gezorgd dat 1 seconde later dezelfde routine opnieuw wordt opgestart; daarna wordt de herberekening uitgevoerd.
  5. Om de klok te kunnen starten (en ook weer uit te zetten) voegen we nog een routine toe:
    Sub KlokAanUit()
        If KlokAan Then
            KlokAan = False
        Else
            KlokAan = True
            KlokActief
        End If
    End Sub
    Als de klok aan staat, wordt die uitgezet en anders gaat de klok aan en wordt de routine KlokActief aangeroepen (die er zelf voor zorgt, dat hij iedere seconde wordt uitgevoerd, zolang KlokAan Waar is).
  6. nog een button toevoegen, die verwijst naar de macro KlokAanUit en we zijn klaar:
    ga in Excel naar de menu-optie Ontwikkelaars (eventueel eerst nog activeren via Bestand/Opties/Lint aanpassen en daar rechts bij Hoofdtabbladen een vinkje aan zetten bij Ontwikkelaars) en kies in het blok Besturingselementen de optie Invoegen en kies het eerste symbool, de Knop.

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