vrijdag 3 maart 2017

Excel: lijngrafiek met zon en wolk voor hoogste en laagste waarde(n)

In Excel kun je ook hele speelse grafieken maken. Mooi voorbeeld is een lijngrafiek waarbij de hoogste en laagste waarde(n) door respectievelijk een zonnetje en een wolkje worden weergegeven.


Ook nog wel gemakkelijk te maken. Ik heb de bedragen gesplitst met formules:

jaar bedrag kolomzon kolommaan
2000  €   340,00 #N/B #N/B
2001  € -100,00 #N/B -100
2002  €   400,00 #N/B #N/B
2003  €   700,00 #N/B #N/B
2004  €   800,00 800 #N/B
2005  €   600,00 #N/B #N/B
2006  €   430,00 #N/B #N/B
2007  €   450,00 #N/B #N/B
2008  €   450,00 #N/B #N/B
2009  € -100,00 #N/B -100
2010  €   800,00 800 #N/B
2011  €   150,00 #N/B #N/B
2012  €   700,00 #N/B #N/B
2013  €   600,00 #N/B #N/B

Formule voor de hoogste waarden:

=ALS(B2=MAX($B$2:$B$15);MAX($B$2:$B$15);NB())

Formule voor de laagste waarden:

=ALS(B2=MIN($B$2:$B$15);MIN($B$2:$B$15);NB())

Vervolgens maak je een lijngrafiek met drie lijnen. Voor de lijnen met hoogste en laagste waarden zet je de markering op een rondje met doorsnee 60. De lijnkleuren heb ik uitgezet. 

Via INVOEGEN => VORMEN kies je een zonnetje en een wolkje. Beide geef je als grootte 2cm bij 2 cm. Via kopiëren - plakken plak je beide vormen in de markeringspunten van de hoogste en laagste waarden, Voor de laatste heb ik ook nog de labels aangezet en deze gecentreerd.

Bijbehorende bestand ExcelGrafiekZonnetje.xlsx is te downloaden via:

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

Voor verder Excel tips klik hier.

donderdag 23 februari 2017

Excel: Gannt chart met handleiding

Inleiding


Uitwerking

Voor ons voorbeeld gebruiken we de volgende gegevens:

Taak
Startdatum
Duur in dagen
Taak 1
3-2-2017
1
Taak 2
4-2-2017
1
Taak 3
4-2-2017
5
Taak 4
11-2-2017
1
Taak 5
11-2-2017
12
Taak 6
25-2-2017
3
Taak 7
28-2-2017
1
Taak 8
28-2-2017
4
Taak 9
6-3-2017
3
Taak 10
6-3-2017
6
Taak 11
12-3-2017
3
Taak 12
17-3-2017
3
Taak 13
20-3-2017
1
Taak 14
23-3-2017
5
Taak 15
26-3-2017
7
Taak 16
29-3-2017
4
Taak 17
1-4-2017
3
Taak 18
4-4-2017
6
Taak 19
7-4-2017
8
Taak 20
10-4-2017
9
Taak 21
13-4-2017
10
Taak 22
16-4-2017
12
Taak 23
19-4-2017
14
Taak 24
22-4-2017
15
Taak 25
25-4-2017
13
Taak 26
28-4-2017
5

§   Plaats deze data in A1:C27.
§   Plaats in D1 het label Einddatum.
§   Plaats in D2 de formule:

  =B2+C2

§   Plaats in E1:H1 de labels Voor, Na, Lijn, Breedte lijn.
§   Plaats in E2 de formule:

  =ALS(EN(B2<$J$1;D2<$J$1);C2;ALS(EN(B2<$J$1;D2>=$J$1);$J$1-B2;0))

§   Plaats in F2 de formule:

  =ALS(EN(B2>=$J$1;D2>=$J$1);C2;ALS(EN(B2<$J$1;D2>=$J$1);D2-$J$1;0))

§   Zet in J1 de formule:

  =VANDAAG()

§   Typ in K1 en M1 resp. Min datum en Max datum.
§   Typ in L1 en N1 resp. 3-2-2017 en 13-5-2017.
§   Type in G2 de formule:

  =$J$1

§   Typ in H2 de waarde 0,2.
§   Selecteer D2:H2.
§   Trek dit door tot D27:H27.
§   Voor de grafiek selecteer A1:C27.
§   Kies Invoegen Þ 2D staaf Þ gestapelde staaf.
§   Maad de ontstane grafiek groter.

We krijgen dan:


 §   Klik met de rechter muisknop op één van de staven.
§   Klik op Gegevens selecteren.

We krijgen dan:


 §   Klik op bewerken.



§   Verander de B in een A.
§   Klik op OK.
§   Klik op Toevoegen.
§   Laat de reeksnaam leeg.
§   Kies als reekswaarden B2:B27.


§   Verander de volgorde van de reeksen.


§   Klik op OK.
§   Klik met de rechter muisknop op de horizontale as.
§   Kies As opmaken.
§   Zet de minimum waarde op 42769 en de maximum waarde op 42868.

We hebben dan:


§   Klik met de rechter muisknop op één van de rode staven.
§   Kies Gegevenreeks opmaken.
§   Kies Opvulling en lijn.
§   Kies bij Opvulling voor Geen opvulling.
§   Klik met de rechter muisknop op de Y-as met Taak 1 – Taak 26.
§   Kies bij Opties voor as voor Categorieën in omgekeerde volgorde.

We hebben dan zoiets:


§   Klik met rechter muisknop op één van de blauwe staven.
§   Kies Gegevenslabels toevoegen.
§   Klik met rechter muisknop op één van de blauwe staven.
§   Kies Gegevensreeks opmaken.
§   Kies voor Opvulling en lijn.
§   Zet de kleur op lichtgroen.
§   Klik met de rechter muisknop op de bovenste as.
§   Klik op Secundaire rasterlijnen toevoegen.
§   Klik met de rechter muisknop op de secundaire rasterlijnen.
§   Kies raterlijnen opmaken.
§   Zet de kleur op donkerder grijs.

We hebben dan een simpel planbord.



We gaan deze nog verder uitbouwen met een datumbalk en verschillende kleuren voor voor en na deze datumbalk.

§   Klik met de rechter muisknop op één van de groene staven.
§   Kies Gegevens selecteren.


§   Verwijder de reeks Duur in dagen.
§   Klik op Toevoegen.
§   Voeg als Reekswaarden E2:E27 toe.
§   Klik op OK.
§   Klik nog eens op Toevoegen.
§   Voeg als Reekswaarden F2:F27 toe.
§   Klik op OK.

We krijgen dan:


§   Klik met de rechter muisknop op één van de groene staven.
§   Kies Gegevenlabels toevoegen.
§   Doe het zelfde bij de blauwe staven.

We zien dan steeds een hinderlijke 0 verschijnen.

§   We klikken met de rechter muisknop op één van de gegevenlabels.
§   We kiezen Gegevenlabels opmaken.
§   Bij Notatie kiezen we dan Categorie: aangepast en Notatiecode: 0;;.
§   Doe dat voor beide kleuren.

Ten slotte moeten we nog de datumlijn toevoegen.

§   Klik met de rechter muisknop op één van de groene staven.
§   Kies Gegevens selecteren.
§   Klik op Toevoegen.
§   Voeg als Reekswaarden toe G2:G27.
§   Klik op OK.
§   Klik nog eens op Toevoegen.
§   Voeg als Reekswaarden toe H2:H27.

Het resultaat is een behoorlijke bende!


§   Klik met de rechter muisknop op één van de staven.
§   Kies Ander type grafiekreeks.



§   Vink bij de onderste twee de Secundaire as aan.
§   Klik op OK.
§   Klik met de rechter muisknop op de horizontale as.
§   Kies As opmaken.
§   Zet de minimum waarde op 42769 en de maximum waarde op 42868.

We krijgen dan:


§   Klik met de rechter muisknop op één van de paarse staven.
§   Kies bij Opvulling en lijn voor Geen Opvulling.
§   Kies één van de blauwe staafjes.
§   Kies voor de kleur zwart.
§   Klik op Opties voor reeks.
§   Zet de Breedte tussenruimte op 0%.
§   Pas eventueel de linker as labels weer aan naar Taak 1 – Taak 26.

We zijn nu een heel eind.


Grafieken in Excel hebben het probleem dat de minimum en maximum waarde voor een as zich niet automatisch aanpassen. Dit moeten we doen met VBA en een knop.

§   Houd de ALT toets ingedrukt en druk op F11.

Het VBA scherm verschijnt.

§   Voeg bij het VBAProject van deze werkmap een module in.
§   Typ daar de volgende code:

  Sub SchaalAanpassen()
    ActiveSheet.ChartObjects(1).Activate
    ActiveChart.Axes(xlValue,xlSecondary).MinimumScale = Range("L1").Value
    ActiveChart.Axes(xlValue).MinimumScale = Range("L1").Value
    ActiveChart.Axes(xlValue,xlSecondary).MaximumScale = Range("N1").Value
    ActiveChart.Axes(xlValue).MaximumScale = Range("N1").Value
  End Sub

§   Sluit het VBA scherm weer via het kruisje rechtsboven.
§   Kies Ontwikkelaars Þ Invoegen Þ Knop.
§   Teken een knop in de cellen O1:P1.
§   Koppel de knop aan de macro SchaalAanpassen.
§   Verander het opschrift van de knop in Schaal aanpassen.


Een druk op de knop zal dan de minimum en maximum waarden van de horizontale schalen aanpassen op basis van de waarden in de cellen L1 en N1. Denk er wel aan de werkmap op te slaan als een XLSM bestand, met macro's.

Voor verder Excel tips klik hier.