vrijdag 31 oktober 2014

Excel: Changing Chart Data Bases On A List Using Indirect

I used Data Validation to limit the input of cell F1. Now I want the choice I make in cell F1 to determine which range of data is shown in my chart.


First I created a name based on the range B1:D1 and called it List. Then I created three names Apples, Pears and Bananas based on the columns underneath these lables. So Apples refers to B2:B13 and so on.

Then I created another name Chart based on the formula:

=INDIRECT($F$1)

letting it refer to the outcome of the choice I make in cell F1, and of course indirectly to the data to which the picked name refers.

Next I created a column chart based on B1:B13. In this column chart I made a couple of changes. First I made the Series name refer to F1. Secondly I made the Series values refer to the name Chart.


And finally the Axis label range should of course refer to A2:A13.


Now you can simply choose the column you want show in your chart by takinig you pick in cell F1!

There is one flaw. A soon as you are trying to change the names Apples, Pears and Bananas into dynamic names, it is not working anymore. At least I could not get it working. Maybe someone has got the right idea.

According to this post, it is simply impossible: http://forum.chandoo.org/threads/indirect-using-dynamic-named-range.12282/

You can download the example Excelchartindirect.xlsx through:

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

zaterdag 25 oktober 2014

Excel: draaitabel over meerdere tabellen nu mogelijk in versie 2013

Met de invoegtoepassing POWERPIVOT (beschikbaar sinds versie 2010) kunnen we nu meerdere tabellen uit een database benaderen en aan elkaar linken. Deze gelinkte tabellen kunnen we vervolgens in een draaitabel presenteren.

Het zelfde is nu ook mogelijk in regulier Excel versie 2013.


Om het een en ander voor elkaar te krijgen, vergt de nodige stappen en die stappen gaan we hier laten zien.

In mijn voorbeeld heb ik uit de database Noordenwind drie tabellen naar Excel bladen gekopieerd:
  • Klanten.
  • Orders.
  • Orderinformatie.
Ik heb de Excel bladen dezelfde namen gegeven. Via INVOEGEN => TABEL heb ik nu van alle drie de lijsten tabellen gemaakt.


Vervolgens heb ik de door Excel gegeven namen tabel1, tabel2 en tabel3 veranderd in de namen Klanten, Order en Orderinformatie. Dit doen we via FORMULES => NAMEN BEHEREN:


Deze drie tabellen gaan we vervolgens aan elkaar linken. Dat linken lukt pas als een gewone lijst is omgezet naar een tabel.

Het linken gaat via GEGEVENS => RELATIES. We klikken daar op nieuw en linken Klanten aan Orders via het veld Klantnummer.


En vervolgens Orders aan Orderinformatie via het veld Order-id


Ten slotte ziet het er dan zo uit:


We sluiten dit scherm. Dan gaan we naar INVOEGEN => DRAAITABEL.



Daar klikken we Een externe gegevensbron gebruiken aan. Vervolgens kiezen we Verbinding kiezen.


Daar kiezen we Tabellen.


En dan kiezen we de optie Tabellen in werkmapgegevensmodel. We klikken op Openen en dan op OK.

We krijgen dan een blad met het draaitabelmodel. Links zien we de drie tabellen. Uit elk van deze tabellen kunnen we nu velden aan de draaitabel toevoegen.

Reguliere Excel tabellen en tabellen die via de PowerPivot add-in gekoppeld zijn, zijn ook te koppelen. In versie 2010 kon dat alleen via de add-in.


In het gegeven voorbeeld zijn de tabellen Order Details en Orders gekoppeld via de PowerPivot add-in; Customers is gekopieerd uit een Access database. Alle drie de tabellen verschijnen in de diagramweergave en zijn daar door mij gekoppeld. We hadden dat in deze versie dus ook kunnen doen via de tab Gegevens => Relaties beheren:


Een draaitabel kunnen we dan zowel via de PowerPivot add-in als via het reguliere Excel maken. We kunnen dan wel zien dat de herkomst van de tabellen verschillend is.


Bijbehorende bestand Exceldraaitabel.xlsx met alleen reguliere tabellen is te downloaden via:

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

Voor verder Excel tips klik hier.

woensdag 22 oktober 2014

Access: hoe kunnen we de omvang van een database beïnvloeden?

Inleiding

De omvang van de database in Access heeft een limiet van 2 Gigabyte. Boven deze limiet ontstaan er problemen. De database wordt onbetrouwbaar.

Hieronder de specificaties van Microsoft voor een MDB of ACCDB database:

Bepalend voor de grootte

Uiteraard is het de hoeveelheid data die bepaalt hoe groot een Access database wordt. Maar dit is te simpel gesteld. Er zijn allerlei factoren die in verschillende mate invloed hebben op de grootte. We gaan deze factoren op een rijtje zetten.

  • Aantal objecten of tabellen, query’s, formulieren, rapporten, VBA modules.
  • Aantal kolommen of velden.
  • Gegevenstype en veldeigenschappen.
  • Toegevoegde OLE objecten aan de database.
  • SQL als Querydef.
  • In VBA gecreëerde objecten.
  • Verwijzingen.
  • Invoegtoepassingen / addins.
  • Aantal geregistreerde gebruikers voor de database.

Mogelijke aanpassingen van de grootte van de database

Tabellen: aantal records

Tabellen groeien evenredig aan het aantal records. Het kan dus de moeite lonen records over te hevelen naar tabellen voor historische gegevens in een andere database. Elke nieuwe database kan opnieuw 2 gigabyte groot zijn. Externe koppelingen naar een derde database laten de database met de gekoppelde tabellen nauwelijks groeien.

Tabellen: aantal kolommen

Hoe meer kolommen, hoe groter de tabel. Het kan dus schelen om het aantal kolommen te reduceren. Is de Id die Access vaak automatisch toevoegt, strikt nodig? Is een tabel goed genormaliseerd? Afgeleide kolommen kunnen eventueel verwijderd worden.

Tabellen: gegevenstypen en veldeigenschappen

Als we bij een kolom kiezen voor numeriek dan kiest Access bij veldeigenschappen automatisch voor Lange integer waar een gewone Integer of een Byte minder ruimte in beslag neemt. Het zelfde geldt voor dubbele en enkele precisie.

Algemene regel voor het reduceren van de omvang is dus: stel kolommen zo krap mogelijk in.

Rapporten

Rapporten nemen onevenredig veel ruimte in. Om de omvang van de database te beperken moeten we dus geen overbodige rapporten laten staan.

Formulieren en query’s

Deze objecten nemen relatief de minste ruimte in binnen een database.

VBA-modules

De grootte van VBA modules is evenredig aan het aantal regels dat ze tellen. Zorg ervoor dat er binnen de VBA editor geen onnodige of niet gebruikte verwijzingen staan. Hetzelfde geldt voor de invoegtoepassingen.

OLE objecten

Deze objecten nemen met name in oudere Access versies zeer veel ruimte in. In Access 2007 is er een extra optie gekomen om dit probleem te beteugelen.

Overig

Bij het aanbrengen van veranderingen geeft Access gebruikte blokken niet automatisch vrij. Daartoe moeten we de database comprimeren. Ook een oplossing kan zijn het creëren van een nieuwe database en het importeren van alle objecten uit het origineel.

Ook zogenaamde non-querydef  SQL kunnen de database flink laten uitdijen al verdwijnt dit effect weer na het comprimeren. Wat is non-querydef  SQL? Elke keer als we een SQL string buiten een reguliere Query gebruiken (bijvoorbeeld achter een rapport of formulier, of elementen op een formulier),  moet Access die string elke keer binden als deze gedraaid wordt. Dit proces kost veel ruimte. Echte Query’s worden gebonden als ze opgeslagen worden. Access weet dan als ze gedraaid worden hoe het deze Query’s moet uitvoeren.

Excel: Data Validation And Stepped Choices With Names And VBA

In Excel you can validate a cell through Data => Data Validation.

We then will get this window:


At Allow we then can choose List and then at Source we pick the range where the data are.

Want we want to do here is select a continent first and then automatically restrict the choice in the next cel to countries from the specific continent.

In the example we see the data (in Dutch) which we are going to use in our example, A1:E6:

afrikaamerikaaustraliëaziëeuropa
nigeriabraziliënieuw zeelandchinanederland
boeroendiargentiniëaustraliëjapanbelgië
liberiaequadornieuw guineathailandpolen
sierra leoneperupakistanduitsland
mauretaniëboliviaindiaengeland

We will use names here to make the stepped choices work.

We select A1:E6. We then choose the tab Formulas and  then Create From Selection. We'll get:



Here we check the upper box only. Then we click OK. We now created five names:

afrika
amerika
australië
azië
europa

Next we create the name Werelddelen based on the range A1:E1.

Cell H1 in the same sheet we validate using a formula:

=werelddelen



In cell I1 next to it we'll do the same using a different formula:

=INDIRECT(H1)



From this moment on the choice we make in cell H1 will determine the offered options of the list in cell I1.


There is still a problem left: in cell I1 the choice we made earlier is still there if we make another choice in cell H1. We need  VBA to correct this:.

Behind our sheet we put this code:

Private Sub Worksheet_Change(ByVal Target As Range)
    'MsgBox Target.Address
    If Target.Address = Range("H1").Address Then
        Range("I1").Value = ""
        Range("I1").Select
    End If
End Sub

Using VBA it is also possible to make the list in cell I1 drop down automatically when we make another choice in cel H1.

Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
    On Error GoTo foutje: 
    If ActiveCell.Validation.Type > 0 Then Application.SendKeys ("%{down}") 

    Exit Sub 
foutje: 
End Sub


You can download the example Excelgetraptekeuze.xlsm through:

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

maandag 20 oktober 2014

Excel: Multi Thermometer Graph

On the Internet, I found several examples of Excel thermometer charts. In all the examples I could find, people use SmartArt i.e. shapes to create the ball of the thermometer. The more thermometers you then create, the more laborious. Moreover, the SmartArt ball is independent of the graph. Once you move the graph or width changes, the ball is not coming with it.

In my example, I created ​​the ball using a line chart with markers. The marker you then change into a ball. The size of this ball you can increase. And it's done. You can then smoothly increase the number of thermometers.



You can download the example file ExcelThermometer.xlsx via

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

zaterdag 18 oktober 2014

Excel: logboek bijhouden van bestede tijd aan een Excel bestand

Tijdens een VBA cursus kwam de vraag om in een logboek bij te houden wie er in welke werkmap hoe lang gewerkt had op welke datum. Een leuke vraag met naar mijn smaak een leuk resultaat.

De oplossing in VBA ziet er uiteindelijk zo uit. Achter ThisWorkBook coderen we de gebeurtenissen BeforeClose en Open. De code ziet er zo uit:


Option Explicit

Public datOpen As Date

Public Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim datSluit As Date
    Dim strGebruiker As String
    Dim strWbNaam As String
    
    strGebruiker = Environ("Username")
    datSluit = Now()
    strWbNaam = ThisWorkbook.Name
    Call logBoek(strWbNaam, strGebruiker, datOpen, datSluit)
End Sub

Public Sub Workbook_Open()
    datOpen = Now()
End Sub

De aangeroepen procedure logBoek zetten we in een VBA module.

Public Sub logBoek(strWbNaam As String, strGebruiker As String, datOpen As Date, datSluit As Date)
    Dim intRows As Integer
    Dim datDuur As Long
    Dim strPad As String
    
    'pad instellen waar het logboek staat
    strPad = "D:\data\walmar\cursussen\cursusmateriaal\Excel\Oefeningen\"
    
    'gewerkte tijd in seconden uitrekenen: verschil tijd van openen en sluiten
    datDuur = DateDiff("s", datOpen, datSluit)
    
    'testen of het logboek open of gesloten is
    If Not IsBookOpen("Logboek.xlsm") Then
        Workbooks.Open (strPad & "Logboek.xlsm")
    End If

    'communicatie met het scherm uitzetten
    Application.ScreenUpdating = False
    Workbooks("Logboek.xlsm").Sheets("Blad1").Activate
    
    'uitrekenen op welke rij in Logboek.xlsm de waarden toegevoegd moeten worden
    intRows = Workbooks("Logboek.xlsm").Sheets("blad1").UsedRange.Rows.Count + 1
    With Workbooks("Logboek.xlsm").Sheets("Blad1").Cells(intRows, 1)
        .Offset(0, 0).Value = strWbNaam
        .Offset(0, 1).Value = strGebruiker
        .Offset(0, 2).Value = datOpen
        .Offset(0, 3).Value = datSluit
        .Offset(0, 4).Value = datDuur
    End With
    
    'werkboek bewaren en sluiten
    ActiveWorkbook.Save
    ActiveWorkbook.Close
    
    'communicatie met het scherm uitzetten
    Application.ScreenUpdating = True
End Sub

Function IsBookOpen(ByRef BookName As String) As Boolean
    On Error Resume Next
    IsBookOpen = Not (Application.Workbooks(BookName) Is Nothing)
End Function

In logboek.xlsm zien we dan verschijnen:

bestand gebruiker geopend gesloten duur in sec
Wijzigingslog.xlsm hessel 18-10-2014 11:21 18-10-2014 11:22 86
Wijzigingslog.xlsm hessel 18-10-2014 11:22 18-10-2014 11:23 46

Bijbehorende bestanden zitten in Wijzigingslog.zip en dat is te downloaden via:

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

Verdere tips: je kunt het bestand exceltips.pdf (laatst gewijzigd (12-9-2014) hier downloaden:


woensdag 15 oktober 2014

Excel: datavalidatie en getrapte keuze

In Excel kun je een cel valideren via de tab Gegevens =>gegevensvalidatie.

We krijgen dan dit dialoogvenster:


Bij Toestaan kunnen we dan kiezen voor Lijst en vervolgens bij Bron voor het bereik waarin de data staan.

Bij een getrapte keuze kiezen we bijvoorbeeld in een eerste cel het werelddeel en in de cel ernaast willen we dan alleen landen zien die bij het betreffende werelddeel horen.

In het onderstaande voorbeeld geven we de data die we in ons voorbeeld gaan gebruiken, van A1:E6:

afrika amerika australië azië europa
nigeria brazilië nieuw zeeland china nederland
boeroendi argentinië australië japan belgië
liberia equador nieuw guinea thailand polen
sierra leone peru pakistan duitsland
mauretanië bolivia india engeland

We gaan hier namen gebruiken om de getrapte keuze te laten werken.

We selecteren A1:E6. We kiezen dan de tab Formules en vervolgens Maken o.b.v. selectie. We krijgen dan:


Hierin vinken we alleen het bovenste vakje aan. Dan klikken we op OK. We hebben dan vijf namen gemaakt:

afrika
amerika
australië
azië
europa

Vervolgens maken we nog de naam Werelddelen gebaseerd op het bereik A1:E1.

Cel H1 van hetzelfde blad valideren we vervolgens met de formule:

=werelddelen


In de naastliggende cel I1 doen we hetzelfde maar dan met de formule:

=INDIRECT(H1)


Vanaf dit moment zal de gemaakte keuze in cel H1 de geboden lijst in I1 bepalen.


We blijven nog met één probleem zitten: in cel I1 zou niet de oude keus moeten blijven staan als we in H1 een ander werelddeel kiezen. Daarvoor hebben we VBA nodig.

Achter ons blad plaatsen we dan de code:

Private Sub Worksheet_Change(ByVal Target As Range)
    'MsgBox Target.Address
    If Target.Address = Range("H1").Address Then
        Range("I1").Value = ""
        Range("I1").Select
    End If
End Sub

Het is ook mogelijk met VBA de vervolglijst in cel I1 automatisch open te klappen.

Via +Ron Kanij heb ik de volgende code gekregen:

Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
    On Error GoTo foutje: 
    If ActiveCell.Validation.Type > 0 Then Application.SendKeys ("%{down}") 

    Exit Sub 
foutje: 
End Sub


Het bestand Excelgetraptekeuze.xlsm is te downloaden via:

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

Verdere tips: je kunt het bestand exceltips.pdf (laatst gewijzigd (12-9-2014) hier downloaden:



woensdag 8 oktober 2014

Friesland: de zeventiende eeuwse kerk van OL Vrouwenparochie (Froubuurt) wordt gerestaureerd

Op dit moment ligt de vloer eruit van de kerk van OL Vrouwenparochie.


Bij het verwijderen van de vloer en het uitgraven van de bovenlaag zijn nogal wat brokstukken van onbekende grafzerken tevoorschijn gekomen.

In Grafschriften en andere genealogische en heraldieke merkwaardigheden in en om de kerken tussen Flie en Lauwers. Deel II Het Bildt uit 1952 zijn alle toen bekende grafzerken beschreven. Ik heb ze ook in mijn boek en database overgenomen.

Nu is er dus nieuwe informatie beschikbaar gekomen. De oudste brokstukken zijn uit de zestiende eeuw. De eerste steen voor de huidige kerk is in 1670 gelegd. We hebben dus te maken van restanten uit de oude kerk.

We zullen hier de foto's van de brokstukken tonen en deze onder voorbehoud proberen te beschrijven.

1. Hester Wiarda en zoon


De tekst voor zover nu duidelijk:

... zijnde ... leit alhier begraven met ... Johan... ...oudt zynde 15 d[agen]

Het betreft hier Hester Wiarda (het Wiarda wapen: een zwaan, is duidelijk herkenbaar), de eerste vrouw van schoolmeester Johannes Jans Swart, van wie de grafzerk al langer bekend was. Volgens de stamboom Wiarda van Simon Wierstra zou zijn een kleindochter zijn van Here Wiarda en Hester de Gavere, echtpaar te Harlingen. Waarschijnlijk is ze begraven met Ian Iohansoon, het zoontje dat maar 15 dagen oud werd.

2. Aesge Cornelis



E C

... ...3 den 22 ianuar[ius] is in den heere gerust [den] ee[rsame] Eesge Kornelis en ... ...er smit tot Oude Bil... .... [7?]5 iaar ende leit alhier [be]graven

Aesge Cornelis was smid te Oude Bildtzijl. Op zijn grafzerk is ook duidelijk een aambeeld herkenbaar. Vermoedelijk is hij 22 januari 1733 op 75 jarige leeftijd overleden. De grafzerk van zijn vrouw en vermoedelijk één van zijn kinderen was al bekend.

3. Kunske Klases



Den 28 apr[il 1720 is in den] heere gerust ... Kunskie Cl[ases] oldt in h[aer 59e] iaer huisvro[uw va]n m[r Jo]hannis Swart sch[oolmeester] ... Vrouwe parochie ... begraven

Kunske Klases was de tweede vrouw van schoolmeester Johannes Jans Swart.

4. Pier Theunis en anderen



[Ao] 1591 de ... apr... sterf Marij [P]eter Iacop dr de huisfrou va Pyr Toenis z

Ao 1619 de 21 ianiar sterf Sij Pier Hendricks wijf olt ...

Volgens Het Bildt kenner +kees kuiken : de wapenzerk met het ruitvormige vrouwenwapen, Q: 1 en 2 links geschuinbalkt (3), 2 en 3 een klimmende leeuw, is van [...] Jakob[...],. de huisvrouw van Pier Teunis. Pier staat op de Bildtkaart uit 1570 in de buurt van Vrouwenparochie en een Teunis Piers, waarschijnlijk zijn vader, werd daar in 1552 gemonsterd. 

Van zijn vader Teunis Piers was de grafzerk al bekend.

5. Teunis Piers en dorpsrechter Pier Teunis, met wapen




Thoni... [P]iers 1562

Ao 1591 de ... ...lij sterf de ... Pijer T.... rechter va ... Vrouwe Pr...

Vader en zoon Teunis Piers en Pier Teunis. Zie verder de informatie van Kees Kuiken bij de vorige zerk.

6. Onbekend


Ao 1699 den ... heere gerust de ... den huis[frou] van ... 43 iaer en leit al...

7. Onbekend (linker brokstuk)


... sterf de ... huisfrou ... is hier beg... Maius ...same Ja... 


8. Onbekend


Tekst nog onduidelijk.

9. Onbekend


.... ...ge C ... ...s zoon ...

10. Onbekend


...OM...

11. Onbekend


...gen out ...

De foto's zijn van Melle Koopmans en mij.