Doorgaan naar hoofdcontent

Excel: Dynamische grafiek op basis van een geparametriseerde Power Query tabel

Dynamische grafieken in Excel

Grafieken in Excel zijn dynamisch te maken met allerlei complexe formules (bijv. index, vergelijken en verschuiving). Makkelijker is het de grafiek te baseren op een Excel tabel (te maken via Invoegen => Tabel).

Nog leuker wordt het als een dergelijke tabel het resultaat is van een Power Query die we met behulp van keuzelijsten kunnen aansturen. Hieronder een voorbeeld.

De tabel met parameters

Deze heb ik linksboven geplaatst. De naam van deze tabel is Parameters (via Formules => Namen beheren). Deze tabel wordt in Power Query gebruikt voor het oppikken van variabelen uit Excel, in dit geval DatumBegin en DatumEind. Deze variabelen worden via Gegevens => Gegevensvalidatie begrensd door de datum uit de eerste kolom van het blad Data.

De gebruikte gegevens

Deze staan op het blad Data en lopen van 1-1-2020 tot en met 20-8-2020. In de tweede kolom staan willekeurige bedragen. Deze gegevens zijn via From Table / Range ingeladen in Power Query

De grafiek

De grafiek linksonder is gebaseerd op de twee kolommen van de tabel rechts. Deze tabel is geladen uit Power Query waar de gegevens uit het blad Data op basis van de variabelen voorbewerkt zijn. 

Power Query

In Power Query heb ik een functie fnGetParameter gemaakt die ervoor zorgt dat de parameters uit Excel gebruikt kunnen worden in een query.

De code is als volgt:

(ParameterName as text) =>
let
    //in the Excel file there has to be a parameter table named Parameters
    ParamSource = Excel.CurrentWorkbook(){[Name="Parameters"]}[Content],
    ParamRow = Table.SelectRows(ParamSource, each ([Parameter] = ParameterName)),
    Value=
    if Table.IsEmpty(ParamRow)=true then 
        null
    else 
        Record.Field(ParamRow{0},"Value")
in
    Value

Verder is er in Power Query een query met de naam Data, gebaseerd op de gegevens uit het blad Data en gekoppeld aan de variabelen uit Excel. De code is als volgt:

let
    strDatumBegin =fnGetParameter("DatumBegin"),
    strDatumEind =fnGetParameter("DatumEind"),
    intDagBegin = Date.Day(strDatumBegin),
    intJaarBegin = Date.Year(strDatumBegin),
    intMaandBegin = Date.Month(strDatumBegin),    
    intDagEind = Date.Day(strDatumEind),
    intJaarEind = Date.Year(strDatumEind),
    intMaandEind = Date.Month(strDatumEind),    
    Source = Excel.CurrentWorkbook(){[Name="data"]}[Content],
    ChangedType = Table.TransformColumnTypes(Source,{{"datum", type date}}),
    FilteredRows = Table.SelectRows(ChangedType, each [datum] >= #date(intJaarBegin, intMaandBegin, intDagBegin) 
    and [datum] <= #date(intJaarEind, intMaandEind, intDagEind))
in
    FilteredRows

Zodra de variabelen worden aangepast, levert deze query een andere dataset en laadt deze in Excel. De grafiek past zich dan vanzelf aan.

VBA

Steeds als de variabelen in Excel worden aangepast, moet de query in Power Query worden uitgevoerd. Om dit een zetje te geven heb ik achter ThisWorkbook het volgende VBA script toegevoegd. Het script checkt of er in de cellen C3 of C4 iets veranderd is. Zo ja, dan wordt de query ververst.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$C$3" Or Target.Address = "$C$4" Then
        ActiveWorkbook.RefreshAll
    End If
End Sub

Dynamische grafiek

In plaats van einddatum 20-1-2020 nu 31-1-2020 gekozen. En prompt krijgen we een andere grafiek.




Reacties

Populaire posts van deze blog

Excel: VBA script om wachtwoord te verwijderen

Af en toe krijg ik een vraag om een wachtwoord van een Excel blad te halen. Doodsimpel met VBA. Hier een script dat ik gebruik:

Sub WachtwoordCrack()
    Dim a As Integer, b As Integer, c As Integer, d As Integer, _
    e As Integer, f As Integer, g As Integer, h As Integer, _ 
I As Integer, j As Integer, k, m As Integer
    Dim begin As Date, eind As Date
    Dim duur As String
    Dim objSheet As Worksheet
    begin = TimeValue(Time)
    On Error Resume Next
    For Each objSheet In Application.Worksheets
        For a = 65 To 66: For b = 65 To 66: For c = 65 To 66
            For d = 65 To 66: For e = 65 To 66: For f = 65 To 66
                For g = 65 To 66: For h = 65 To 66: For I = 65 To 66
                    For j = 65 To 66: For k = 65 To 66: For m = 32 To 126
                        ActiveSheet.Unprotect Chr(a) & Chr(b) & _ 
 Chr(c) & Chr(d) & Chr(e) & Chr(f) & _ 
 Chr(g) & Chr(h) & Chr(I) & Chr(j) & Chr(k) & Chr(m)
                        If Ac…

Excel 2013: uniek aantal in draaitabel

Tot en met versie 2010 was het in Excel lastig om in een draaitabel een uniek aantal (DISTINCT COUNT) te tellen. We geven een voorbeeld op basis van een verkoperslijst. In deze lijst kunnen we zien welke verkopers welke artikelen hebben verkocht. Willen we nu in een draaitabel laten zien hoeveel artikelen een verkoper heeft verkocht, dan krijgen we wel de aantallen maar niet de unieke aantallen te zien.


Om toch de unieke aantallen te laten zien, hebben we een aantal stappen nodig.

Op het moment dat we de draaitabel invoegen, krijgen we in Excel 2013 dit dialoogvenster:


Onderaan zien we daar een nieuwe optie: Deze gegevens toevoegen aan het gegevensmodel. Deze optie moeten we aanvinken, voor we op OK klikken.

We krijgen dan een iets ander beeld dan normaal:


Normaliter krijgen we alleen de veldnamen. Nu zien we er het woord Bereik boven staan.

Voor het voorbeeld heb ik nu Verkoper toegevoegd aan Rijen en Artikelomschrijving aan Waarden.

Het resultaat is identiek aan het bovenste plaatje…

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.