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