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) & C

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

Crystal Reports: On SQL Expression Fields

This is based on an article from the SAP site: http://search.sap.com/notes?id=0001217871&boj=/sap/bc/bsp/spn/scn_bosap/notes.do?access=69765F6D6F64653D3939382669765F7361706E6F7465735F6E756D6265723D30303031323137383731 I tried to add some more examples to it. SQL Expression Field definition SQL Expression fields are similar to formula fields, but they are written in Structured Query Language (SQL). They are useful in optimizing report performance because the tasks they execute are performed on the database server. You can use SQL Expression fields to perform pre-defined functions on database fields. The list of available functions depends on the type of database in use. This list is available in the Function Tree of the SQL Expression Editor dialog box. NOTE : If Crystal Reports recognizes the function name it will turn blue in the SQL Expression Editor dialog box. Additional database functions may be available to you, depending on your database, but they will