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.




Labels:

Reacties

Een reactie posten

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...
1 reactie
Meer lezen

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...
Een reactie posten
Meer lezen

Excel: laatste datum voor een groep, draaitabel of matrixformule?

Via een Excel groep krijg ik de vraag hoe je de laatste datum voor een groep er uit kunt pikken. We geven hier even de voorbeelddata: Voor zover ik kan zien zijn er in ieder geval twee mogelijkheden: met matrixformules en met een draaitabel . Oplossing: draaitabel We zullen het in dit voorbeeld maar even helemaal volgens de regels van de Excel kunst doen. Voor het maken van de draaitabel heb ik de lijst eerst omgezet naar een tabel ( INVOEGEN => DRAAITABEL ). De naam veranderen we dan even van Tabel1 in draaitabel . Vervolgens maken we de draaitabel. Via Waardeveldinstellingen kiezen we dan voor het datumveld voor Max en bij Getalnotatie voor Datum . De kopjes zetten we even om naar Naam en Laatste datum . Klaar. Oplossing: matrixformules Voor dat we de matrixformules gaan maken, creëren we eerst namen met flexibele bereiken: datum =VERSCHUIVING(Blad1!$B$2;0;0;AANTALARG(Blad1!$B:$B)-1;1) naam =VERSCHUIVING(Blad1!$A$2;0;0;AANTALARG(Blad1!$A:$A...
Een reactie posten
Meer lezen