maandag 30 september 2013

Excel: Displaying Moving Maximum in a Graph

In this graph I will show the moving maximum in a different color than the other values.


Here are the data, A1:C14:

Year Apples Above
2001 23 23
2002 45 45
2003 6 0
2004 78 78
2005 23 0
2006 54 0
2007 12 0
2008 56 0
2009 63 0
2010 72 0
2011 39 0
2012 90 90
2013 31 0
  • In column C2:C14 from C2 down I created the formula:
=IF($B$2:$B$14>=SUBTOTAL(4;$B$2:B2);B2;0)
  • The graph I based on both series.
  • Series overlap: 100%.
  • Gap width: 150%.
  • I checked: Show data in hidden rows and columns.
  • I hid column C.
You can also download the file movingmax.zip through this link:

zondag 29 september 2013

Excel 2010/2013/2016: tips en trucs; Nederlands

Word ook lid van mijn Linkedin Groep: Excel en VBA Nederland. Daar komen allerlei geavanceerde tips en trucs aan de orde. Ook kunnen mensen hier zelf vragen stellen en beantwoorden.



Je kunt het bestand exceltips2017.pdf (laatst gewijzigd (27-2-2017) hier downloaden:



Ook zijn er nog de tips voor Word: 

http://hesseldewalle.blogspot.nl/2013/09/word-2010-tips-trucs.html


en Word voor lange documenten: 


http://hesseldewalle.blogspot.nl/2013/10/word-2010-lange-documenten.html

INHOUDSOPGAVE EXCEL TIPS & TRUCS

1 Algemeen 4
1.1 Werkbalk snelle toegang 4
1.2 Bestand direct als e-mail bijlage 4
1.3 Wijzigingen bijhouden/archiveren 4
1.4 Zoeken en vervangen van de asterisk * 6
1.5 Zoeken in meerdere werkbladen 6
2 Bewerken 7
2.1 Bewerken van de inhoud van een cel 7
2.2 Bewerken van de inhoud van een cel beperken 7
3 Indeling werkblad 8
3.1 Breder maken kolommen 8
3.2 Kolommen verbergen en terugzetten 8
3.3 Opmerkingen invoegen 9
3.4 Opmerkingen verwijderen 9
3.5 Titels blokkeren 10
3.6 Splitsen 10
3.7 Meerdere weergaven 11
3.8 Inzoomen 12
3.9 Toevoegen werkblad 12
3.10 Werkblad verplaatsen naar een andere werkmap 12
3.11 Twee werkmappen vergelijken 13
3.12 Grootte formulescherm 13
3.13 Naamvak groter trekken 13
4 Beveiliging 15
4.1 Beveiliging van een werkblad 15
4.2 Beveiliging van een bereik 15
5 Invoer 17
5.1 Valideren van gegevens 17
5.2 Snel reeksen maken 18
5.3 Reeksen maken met de Smart Tag 20
5.4 Eigen reeksen maken 20
5.5 Alfabet 22
5.6 Meer cellen tegelijkertijd vullen 22
5.7 Meer dan één regel in een cel geforceerd afbreken 22
5.8 Tekst uit meerdere cellen in één cel plaatsen 23
5.9 Flash fill (nieuw in Excel 2013) 23
6 Opmaak 25
6.1 Celinhoud kleuren via Celeigenschappen Þ Aangepast 25
6.2 Cellen conditioneel opmaken 26
6.3 Getallen van een label voorzien 26
6.4 Getallen weergeven als duizendtallen 27
6.5 Tekst aanvullen met streepjes of puntjes 27
6.6 Getallen omzetten naar tekst 28
6.7 Optellen van uren 28
6.8 Uren en minuten omzetten naar decimale cijfers 29
6.9 Eigen datumformaat 29
6.10 Datum in het Fries of welke taal ook maar 29
6.11 Invoeren van breuken 30
6.12 Voorloopnullen 30
6.13 Romeinse getallen 31
6.14 Opmaakcombinaties 31
6.15 Opmaak kopiëren 31
6.16 Voorwaardelijke opmaak 32
6.17 Rijen om en om kleuren met voorwaardelijke opmaak 33
6.18 Nog een Voorwaardelijke opmaak met een formule 34
6.19 Dubbellen kleuren met voorwaardelijke opmaak 34
6.20 Voortschrijdend maximum markeren met voorwaardelijke opmaak 35
6.21 Opmaak veranderen via een celstijl 36
6.22 Tabbladen kleuren 37
6.23 Checkbox als opmaak 39
6.24 Verschillende opmaak binnen één cel 39
6.25 Snelle analyse (Excel 2016) 39
7 Verplaatsen van de cursor 41
7.1 Binnen hetzelfde blad 41
7.2 Van blad naar blad 41
8 Selecteren 42
9 Kopiëren, knippen en plakken 43
9.1 Verplaatsen en/of kopiëren van blad naar blad met de ALT 43
9.2 Transponeren via plakken speciaal 43
9.3 Excel als carbonpapier 43
9.4 Cellen omwisselen 43
10 Rekenen en formules 45
10.1 Formules maken 45
10.2 Je hoeft je formules niet met = te beginnen 45
10.3 De formulebalk en tooltips 45
10.4 Direct sommeren 45
10.5 Directe sommering via selectie 46
10.6 Extra opties bij Autosom 46
10.7 Doorlopende totalen met sommeren 46
10.8 Totale kolom of rij sommeren 47
10.9 Sommeren met een naam 47
10.10 Somproduct om selectief op te tellen 48
10.11 Sommen.als 49
10.12 Verschil tussen datums 50
10.13 Werkdagen tellen 50
10.14 Weeknummers 51
10.15 Weeknummers berekenen met een functie 51
10.16 Weeknummers berekenen met formule 53
10.17 Laatste dag van de maand bepalen 53
10.18 Omzetten van getallen naar datums 53
10.19 Wissen speciaal 53
10.20 Rekenen met plakken speciaal 55
10.21 Omzetten van tekstgetallen naar echte getallen 55
10.22 Controleren van formules 56
10.23 Formules afdrukken 56
10.24 Meerdere bladen tegelijk optellen 56
10.25 Expliciete intersectie 57
10.26 Impliciete intersectie 57
10.27 Plaatje naar bereik laten verwijzen 57
11 Printen 59
11.1 Afdrukken algemeen 59
11.2 Afdrukken: rijen en kolommen vastzetten 61
12 Databases & lijsten 62
12.1 Data importeren van internet/intranet 62
12.2 Autofilter: aantal records als resultaat 62
12.3 Autofilter: filteren op kleur 62
12.4 Autofilter en de aggregatiefuncties van de statusbalk 62
12.5 Autofilter: niet voor alle kolommen uit het lijstbereik 63
12.6 Autofilter en subtotalen 63
12.7 Uitgebreid filter 65
12.8 Uitgebreid filter om dubbele waarden eruit te halen 66
12.9 Sorteren op meer dan drie kolommen 67
12.10 Sorteren speciaal 67
12.11 Bereik omzetten naar een tabel 68
12.12 Filteren op celkleur of tekstkleur 71
12.13 Slicers 72
13 Zoeken 73
13.1 Zoeken in meerdere lijsten 73
14 Grafieken 74
14.1 Elementen van een grafiek selecteren met het toetsenbord 74
14.2 Taartpunten happen 74
14.3 Onderdelen afwijkend kleuren 74
14.4 Een staaf opvullen met plaatjes 75
14.5 Grafiek met dubbele Y-as 76
14.6 Verborgen rijen of kolommen meenemen 77
14.7 Flexibele bereiken in een grafiek 78
14.8 Ontbrekende data weergeven in een grafiek 79
14.9 De 0 verbergen op de Y-as van een grafiek 80
14.10 Negatieve waarden anders kleuren 80
14.11 Titel naar een cel laten verwijzen 81
14.12 Y-as labels in staafgrafiek links en rechts 82
14.13 X-as labels onder negatieve waarden 83
14.14 De volgorde van de categorieën omkeren 84
14.15 Een extra X-as 86
14.16 De Y-as van positie laten wisselen 87
14.17 Reeksen toevoegen aan een grafiek 88
14.18 Opmaak van de ene grafiek naar de andere plakken 90
14.19 2013: verschillende grafiektypen voor verschillende reeksen 91
14.20 Lijngrafieken: hoe voorkomen we weergave 0 waarden 92
15 Draaitabellen 95
15.1 Dynamisch bereik met de functie VERSCHUIVING 95
15.2 Dynamisch bereik op basis van tabel 95
15.3 Groeperen op datum 97
15.4 Groeperen op getallen 98
15.5 Uitkomst weergeven als percentage van de kolom 99
15.6 Running totals of cumulatief 100
15.7 Zelf groepen maken 101
15.8 2013: uniek aantal in draaitabel 102
15.9 Draaitabel gebaseerd op meer bladen (2013) 104
15.10 Draaitabelweergave: rapportindeling 108
16 VBA & macro's 110
16.1 VBA code direct aan blad toevoegen. 110

zaterdag 28 september 2013

Excel: double column, double X-axis; first solution

Here are the data, B3:G5:

til 18 til 30 til 50 til 80 til 100
Male 75 80 85 80 70
Female 60 65 70 65 60

I created two names.

Tussen

=5+MAX(Dubbelekolom!$C$4:$G$4)+MAX(Dubbelekolom!$C$5:$G$5)-(Dubbelekolom!$C$4:$G$4)-(Dubbelekolom!$C$5:$G$5)

and

Totaal

=Dubbelekolom!$C$4:$G$4+Dubbelekolom!$C$5:$G$5+Dubbelekolom!Tussen

The two real series (selected from the sheet) in the graph are:

C4:G4 and C5:G5

In between those two I put a name as a series: =Dubbelekolom!Tussen
And as last series: =Dubbelekolom!Totaal

Totaal is linked to the secondary axis and gets an extra X-axis, which I put at the top. As color I chose no color. As to color, the same goes for Tussen.

Both Y-axis should have a maximum of 160 and a minimum of 0. Then I deleted both Y-axis.

And of course, the color of Male should be blue, and Female pink.

Now the graph should look like this:


You can also download the file dubbelekolom.zip through this link:

https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing

Crystal Reports vs. SS RS / Report Builder: Displaying Boolean Field Types

Crystal Reports

In Crystal Reports a Boolean Field type is simply represented by the words True or False when you include such a field in your report.
  • Right-click the field.
  • Choose Format Field.
  • Choose the tab Boolean.

Nice, but not as nice as when you would change the True and False into checked or unchecked check boxes.
  • Right-click the field.
  • Choose Format Field.
  • Choose the tab Font.
  • Pick the font Wingdings.
  • Choose the tab Common.

  • Click the X+2 after Display string.
  • Fill in the Formula:

if {Products.Discontinued}= true then
    chr(254) //checked checkbox
else
    chr(111) //unchecked checkbox
  • Click OK.
Then we will get:


Report Builder / Reporting Services

Report Builder will normally also represent a Boolean Field type as either True or False
  • In order to change it here, add a column to the tablix.
  • Right click it.
  • Choose Expression.

  • Fill in the Formula as shown in the window or like here
=IIF(Fields!Discontinued.Value=True, Chr(254), Chr(111))
  • Click OK.
  • Right click the expression.
  • Choose Text Box Properties.
  • Choose Font.
  • Choose Wingdings.
  • Click OK.
When you now run the report the result would be the same as in Crystal Reports.




vrijdag 27 september 2013

Crystal Reports: alternating row coloring and hidden rows

A couple of years ago I ran into a problem with Crystal Reports. I had used alternating row coloring based on the formula:

if recordnumber mod 2 = 0 then
    color(255,200,255)
else
    color(255,255,200)

I got:


But now I had to hide some rows bases on a formula, let's say:

{Products.UnitPrice}>50

But now all off a sudden I got irregularities in my alternating row coloring, like this:


After some thinking I created a Running total call Counter:


As Type of Summary I used count. As a formula I used:

{Products.UnitPrice}<=50

The opposite of the suppressing formula.

I then replaced the formula for the alternating row coloring with

if {#counter} mod 2 = 0 then
    color(255,200,255)
else
    color(255,255,200)

And my report was working again!

Excel: grafiek opleuken van gemeenten onder en boven de financiële streep

Altijd leuk om 's ochtends in de krant een creatieve grafiek te zien. Vanochtend ééntje over de financiële positie van de diverse Nederlandse gemeenten.

De oorspronkelijke bron was de Amsterdamse Rekenkamer:



























Naar mijn smaak nogal onoverzichtelijk. De Trouw had er een veel mooiere grafiek van gemaakt met vooral een mooi onderscheid tussen gemeenten boven en onder de streep. Ik heb gekeken of deze grafiek ook in Excel te maken was. Hier mijn resultaat:



































Het een en ander was met een paar simpele trucjes te doen. Hier de data (A1:F9):

geen probleem instabiel instabiel en kwetsbaar probleemgevallen kwetsbaar
2007 241 -6 -3 -12 -58
2008 230 -12 -5 -10 -63
2009 165 -9 -13 -15 -118
2010 174 -19 -15 -6 -106
2011 163 -25 -24 -7 -101
Aantal gemeenten die financieel fit zijn
Aantal gemeenten met financiële problemen

  • Ik heb de aantallen voor onder de streep negatief gemaakt.
  • Vervolgens heb ik een gestapelde kolomgrafiek gemaakt.
  • Toen gekozen voor Rijen / kolommen omdraaien.
  • De kleuren van de kolommen heb ik handmatig aangepast.
  • De Y-as heb ik gemaximaliseerd op 250 en geminimaliseerd op 200.
  • De notatie van de Y-as heb ik aangepast tot: #.##0;#.##0; zo worden ook negatieve getallen positief weergegeven.
  • Voor de groene en rode labels heb ik rechthoeken gemaakt en die laten verwijzen naar de cellen met de teksten: =$A$8 en =$A$9.
  • De X-as moet op Laag worden gezet.
  • De pijltjes zijn ook gewoon als symbolen ingevoegd.
Het bestand is te vinden via www.walmar.nl/zips/gemeenten.zip. In dit voorbeeld heb ik gebruik gemaakt van namen en zijn de aantallen niet langer negatief gemaakt. Ik heb vervolgens de namen vermenigvuldigd met -1. Deze namen zijn vervolgens in de grafiek gebruikt.

Hetzelfde idee is ook toe te passen op een staafdiagram, zoals te zien in deze grafiek over de aansluiting van opleiding op de arbeidsmarkt per land:


Voor verder Excel tips klik hier.

donderdag 26 september 2013

Friesland: de kerk van Dongjum

Van deze kerk is begin november 2013 de vloer deels verwijderd. Hierbij foto's van een ons nog onbekende grafzerk:


Hier lezen we

GOSLINGA OCKINGA

met vaag de wassenaar van het Goslinga wapen en de lelie van het Ockinga wapen. Het gaat om het echtpaar Johannes van Goslinga en Joukje van Ockinga, de ouders van Johan van Goslinga van de tweede foto.

En de tweede foto:


Op de twee foto is duidelijk te lezen

IOHAN VAN GOSLINGA FEDD SOPHIA VAN CAMMINGA

Het gaat hier om de de grafzerk van de op 27 oktober 1688 overleden Johan van Goslinga en zijn vrouw Fetje Sophia van Camminga. Opvallend genoeg is er geen randtekst, maar zo te zien is deze grafzerk de deksteen van een grafkelder

Op de website van Simon Wierstra (http://simonwierstra.nl/) vinden we de opmerking:

Hij wil begraven worden te Dongjum en het gebeente van zijn vrouw moet uit Arum overgebracht worden naar Dongjum.

Deze Johan van Goslinga was de vader van Sicco van Goslinga. In de kerk van Dongjum vinder we een prachtig praalgraf van deze Sicco van Goslinga. Op de website www.reliwiki.nl treffen we een prachtige foto aan:



















Bij de preekstoel zijn ook een aantal grafzerken onder de vloer zichtbaar. Naar alle waarschijnlijkheid gaat dit om ons reeds bekende stenen. Hoe dan ook, dit gedeelte van de vloer wordt niet verwijderd.

Er wordt nog een tweede stuk vloer verwijderd maar het ziet er niet naar uit dat er nog meer onbekende stenen tevoorschijn komen. We wachten af.

Word 2010 tips & trucs

Je kunt het bestand wordtips.pdf hier downloaden:

https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing

INHOUDSOPGAVE

1             Algemeen         
1.1          Werkbalk snelle toegang            
1.2          Laatst geopende document sluiten       
1.3          Bestand direct als e-mail bijlage              
1.4          Schermopnamen invoegen       
1.5          CTRL + Muiswiel: in- en uitzoomen        
1.6          Andere TAB als start TAB instellen         
1.7          Versies beheren            
2             Automatismen
2.1          Autocorrecties algemeen          
2.2          Zinnen beginnen met een hoofdletter
2.3          Correctie van dubbele hoofdletters      
2.4          Fragmenten van meerdere regels         
2.5          Automatische opmaak na een opsommingsteken         
3             Wijzigingen bijhouden 
4             Cursor verplaatsen        
5             Selectie              
6             Opmaak controleren    
6.1          Manieren om opmaakcodes zichtbaar te maken            
7             Tekenopmaak 
7.1          Verwijderen tekstopmaak        
7.2          Verwijderen van opmaak met plakken speciaal              
7.3          Kwasten van opmaak   
7.4          Aanbrengen van opmaak           
7.5          Superscript en subscript             
8             Alinea-opmaak
8.1          Verwijderen alinea-opmaak     
8.2          De decoratieve initiaal 
8.3          Meer alineastijlen in dezelfde alinea.   
9             Tabellen             
9.1          Tekst naar tabel converteren   
9.2          Tabel weg met Backspace          
9.3          Tab toets voor nieuwe rij bij tabel         
9.4          Rechts achteraan tabel voor rij invoegen           
9.5          Lege regel voor tabel die helemaal bovenaan staat       
9.6          Rij van een tabel als veldnamenrij         
9.7          Rij van een tabel niet splitsen over twee pagina’s          
10           Afdrukken         
10.1       Meer pagina’s op een A4-tje    
11           Koppelingen Excel         
11.1       Gewoon koppelen        
11.2       Plakken speciaal             
12           Grote documenten       
12.1       Snel even tekst produceren     
12.2       Koppen              
12.3       Koppen nummeren      
12.4       Inhoudsopgave              
12.5       Index   
12.6       Afbeeldingen comprimeren in Word     


woensdag 25 september 2013

Crystal Reports vs. SS RS / Report Builder: Running Totals

Crystal Reports
In Crystal Reports you can easily create a Running Total based on a numeric field. We'll give a simple example.

  • right click the numeric field.
  • choose Insert.
  • click Running Total.
We'll get:



















  • We could now simply click OK, and our Running Total is there.
  • We can also click Use a formula under Evaluate.
  • Click the X+2 button.
  • Then fill in the formula:
{Order Details.Quantity}>100

Now we would have a conditional Running Total, only totaling the values over 100.

SS Reporting Services / Report Builder
We'll now try the same example in Report Builder.
  • To our table we have to add a column.
  • Right-click this column.
  • Click Expression
We'll get:























  • There fill in the formula:
=RunningValue(Fields!Quantity.Value,SUM,"dstOrder")
  • Clicking OK would give us a column with a Running Total.
We still need to add the condition. Now the formula becomes a little more difficult:

=RunningValue(iif(Fields!Quantity.Value>100,Fields!Quantity.Value, nothing) ,SUM,"dstOrder")

But in the end the result would be the same: a conditional Running Total, only totaling the values over 100

dinsdag 24 september 2013

Crystal Reports vs. SS RS / Report Builder: alternating row/group colors

Crystal Reports
In Crystal Reports it is quite easy to define alternating row colors.

  • You just go to the Section Expert.
  • Click Details.
  • Choose Color.
  • Click the formula button x+2.

















  • There you fill in a formula like this:

if recordnumber mod 2 = 0 then
    crAqua
else
    crNoColor

When you want to alternate group colors, in stead of clicking Details, you click a Group header or footer. The formula is just slightly different:

if groupnumber mod 2 = 0 then
    crAqua
else
    crNoColor

SS Reporting Service / Report Builder
In SSRS or Report Builder we can do the same but is just a little bit more complicated. First take a look at row coloring.
  • Select a cell in a row.
  • Right click.
  • Select Text Box Properties.
  • Select Fill. 























  • Click fx behind Fill color.
  • Type the Formula. 
=IIf(RowNumber(Nothing) Mod 2=0, "Silver", "Transparent")
  •  Repeat this for every cell in the row.
We can also do this by selecting the entire row and using the properties to change the fill color.

For Group rows we again have to select a cell. This time the formula is a lot more complicated. It would have to look like this:

=IIF(RunningValue(Fields!Category.Value,COUNTDISTINCT,"NameGroup") MOD 2 = 0,"PaleGreen","White")

And once more, we have to repeat this for each cell in the group row.

Crystal Reports vs. SS RS / Report Builder: Resetting page number per group and overall total page number

Crystal Reports 2011
In Crystal Reports it is quite easy to reset the page number for every new group. We can do this by using the Section Expert:


























Presenting the page number and the total page number per group is now quite easy. We could do so by using for instance a formula consisting of special fields:

PageNumber & " "& TotalPageCount

But we'll have a problem when we want to add the overall total pagenumber. Crystal Reports does not have an overal total pagecount special field. The only way to solve this problem - to my knowledge - is using a combination of a subreport and a shared variable.

Report Builder / SS Reporting Services
In Report Builder we can use the Built-in Fields for solving this problem.


























When we want the page numbers per group, we use PageNumber and TotalPages; when we want the overall numbers, you use OverallPageNumber and OverallTotalPages.

Member
Description
PageNumber
The current page number relative to page breaks that reset the page number. At the beginning of report processing, the initial value is set to 1. The page number increments for each rendered page.
To number pages within page breaks for a rectangle, a data region, a data region group, or a map, on the PageBreak property, set the ResetPageNumber property to True. Not supported on tablix column hierarchy groups.
PageNumber can only be used in an expression in a page header or page footer.
TotalPages
The total number of pages relative to page breaks that reset PageNumber. If no page breaks are set, this value is the same as OverallTotalPages.
TotalPages can only be used in an expression in a page header or page footer.
OverallPageNumber
The page number of the current page for the entire report. This value is not affected by ResetPageNumber.
OverallPageNumber can only be used in an expression in a page header or page footer.
OverallTotalPages
The total number pages for the entire report. This value is not affected by ResetPageNumber.
OverallTotalPages can only be used in an expression in a page header or page footer.




maandag 23 september 2013

Crystal Reports: presenting data in a graphical way without a chart

Based on some simple data I tried to create a special graphical presentation:



Here are the data, an Excel sheet:


year number month amount
20091jan100
20092feb200
20093mrt100
20094apr200
20095mei-100
20096jun200
20097jul100
20098aug200
20099sep-200
200910okt200
200911nov100
200912dec200
20101jan100
20102feb200
20103mrt100
20104apr200
20105mei-100
20106jun200
20107jul100
20108aug200
20109sep-200
201010okt200
201011nov100
201012dec200
20111jan100
20112feb200
20113mrt100
20114apr200
20115mei-100
20116jun200
20117jul100
20118aug200
20119sep-200
201110okt200
201111nov100
201112dec200
20121jan100
20122feb200
20123mrt100
20124apr200
20125mei-100
20126jun200
20127jul100
20128aug200
20129sep-200
201210okt200
201211nov100
201212dec200

Furthermore, I created two formula fields.

Blockleft:

if {Sheet1_.amount}>0 then
    replicatestring(chrw(110),abs({Sheet1_.amount}\10))

Blockright:

if {Sheet1_.amount}<=0 then
   replicatestring(chrw(110),abs({Sheet1_.amount}\10))

  • In the Details section I put  the fields Blockright, Month, Blockleft.
  • The fields Blockright and Blockleft I gave the font WingDings.
  • Blockright should be right aligned and Blockleft left aligned.
  • Blockright should get the color red; Blockleft the color green.
  • I grouped on Year.
  • The rows I colored alternating using the formula:
if recordnumber mod 2 = 0 then
    color(255,255,200)
else
    color(255,200,255)