Gegevens in dashboard automatisch filteren

[Matthias07]

Goedemiddag,

Voor een opdracht op stage ben ik bezig met het creëren van een dashboard in Excel. In de bijlage heb ik een gedeelte van dit dashboard toegevoegd. Wanneer u het dashboard opent, kunt u zien dat het bestand onderverdeeld is in 8 werkbladen. Werkbladen 2, 3 en 4 betreffen de dashboards en werkbladen 5, 6 , 7 en 8 betreffen de werkbladen waar de gegevens voor het dashboard moeten worden ingevoerd. De dashboards zijn op 3 niveaus gecreëerd: TOTAALniveau (werkblad 2), LETTERniveau (werkblad 3) en NUMMERniveau (werkblad 4).

Nu wil ik graag aan elk dashboard een filter toevoegen. Als voorbeeld neem ik werkblad 4. In dit werkblad wil ik bijvoorbeeld door middel van een selectie van de MAAND augustus in het JAAR 2016 van NUMMER 7 alle gegevens uit de werkbladen 5, 6, 7 en 8 filteren.

Nu is mijn vraag hoe ik dit kan realiseren? Ik heb al dingen geprobeerd met macro's, maar ik kom er niet uit. Zou u mij kunnen helpen hoe ik in de 3 dashboard de gegevens uit de werkbladen 5, 6, 7 en 8 kan filteren na een vooraf gegeven selectie?

Ik verneem graag van u.

Met vriendelijke groet,


Matthias

 

[Matthias07]

Heeft iemand misschien nog een idee om mij op weg te helpen?

 

[Gijsbert1]

Matthias,

Zie bestand: Bekijk bijlage 274097

Hiervoor heb ik in werkblad 4, Cel D12 en E12, 2 formules geplaatst. (Matrix formules die afgesloten zijn door Ctrl+Shift+Enter)
Om dit te maken heb ik in werkblad 1 de maanden aangepast. Januari = 01-01-2016 en Februari = 01-02-2016, enz.
Met celnotatie = mmmm zien ze er weer uit als normale maandnotaties!
Deze celnotatie heb ik ook naar cel C5 van werkblad 4 gekopieerd uiteraard!

Nu kun je het voor ieder KPI toepassen!

Wel nog 1 opmerking!
Je hebt alle tabellen (van werkbladen 5,6,7 en 8) doorgetrokken tot de laatste regel(dat zijn er meer dan 1 miljoen)
Daarom heb ik de formules ook maar naar hele kolommen laten kijken!
Dit maakt je bestand wel enorm zwaar! zeker met deze matrixformules.

 

[Matthias07]

Gijsbert,

Dank je wel voor je reactie! Dit helpt enorm! Ik ga er mee aan de slag om alle drie de dashboards aan de hand van jouw voorbeelden aan te passen.

Indien er nog vragen zijn, kan je ik dan nogmaals benaderen?

Nogmaals bedankt.

Vriendelijke groet,


Matthias

 

[plongske]

Tabellen hoef je toch niet door te trekken?
Deze passen zichzelf aan als er gegevens aan worden toegevoegd.

 

[Gijsbert1]

@Matthias,
Vragen kan altijd, maar ga inderdaad eerst zelf even aan de slag. Je hebt voer genoeg zou ik zeggen!

@plongske,
Jouw commentaar heeft betrekking op mijn opmerking in post #3
TS kan hier zelf wel even mee aan de slag lijkt me.

 

[Matthias07]

Beste Gijsbert,

Ik heb 1 vraag:

Kan de formule ook zo aangepast worden, dat wanneer je in CEL C5 geen maand invult hij in het dashboard gewoon alle gegevens van het jaar wat je in CEL C4 hebt ingevuld laat zien? Dan zou de formule helemaal geweldig zijn

Ik hoor graag van je.

Met vriendelijke groet,


Matthias

 

[Gijsbert1]

Bij deze.
Met een "niet matrix" oplossing maar met een gewone "sommen.als" formule:
Werkblad 4 D12:

=ALS($C$5="";SOMMEN.ALS('5 omzet'!$D:$D;'5 omzet'!$B:$B;$C$4;'5 omzet'!$C:$C;$C$6);SOMMEN.ALS('5 omzet'!$D:$D;'5 omzet'!$B:$B;$C$4;'5 omzet'!$C:$C;$C$6;'5 omzet'!$A:$A;DATUM($C$4;MAAND($C$5);1)))

Werkblad 4 E12:

=ALS($C$5="";SOMMEN.ALS('5 omzet'!$E:$E;'5 omzet'!$B:$B;$C$4;'5 omzet'!$C:$C;$C$6);SOMMEN.ALS('5 omzet'!$E:$E;'5 omzet'!$B:$B;$C$4;'5 omzet'!$C:$C;$C$6;'5 omzet'!$A:$A;DATUM($C$4;MAAND($C$5);1)))

 

[Piet Bom]

Ik heb een oplossing met SOMPRODUCT() formules

 

[plongske]

@Gijsbert
Naar mijn vermoeden wist TS dit niet gezien de tabellen doorgetrokken waren.
Ook dienen de formules dan aangepast te worden.

@ Mathias
Ik heb de gele cellen reeds aangepast naar de sommen. als formule van Gijsbert maar dan in tabel vorm
De andere cellen zijn nog de matrix formules van Gijsbert reeds in tabel vorm

 

[Matthias07]

Gijsbert, Piet Blom en Plongske,

Hartstikke bedankt voor jullie hulp! Hier kan ik heel veel mee

Vriendelijke groet,


Matthias

 

[Matthias07]

Goedemiddag,

Ik heb nog een vraag met betrekking tot de formules in CEL D13 en E13 van WERKBLAD 4 in het bijgevoegd bestand Bekijk bijlage Dashboard voorbeeld.xlsx.

Ik heb in werkblad 5 (OMZET) een nieuwe kolom toegevoegd: kwartaal.
Ik heb aan het dashboard in werkblad 4 een nieuwe keuzemogelijkheid (drop-down mogelijkheid) toegevoegd: kwartaal.

Nu is mijn vraag hoe de formules in de cellen D13 en E13 in het dashboard van werkblad 4 aangepast moeten worden dat het dashboard ook de gegevens van bijvoorbeeld kwartaal 1 in 2016 laat zien. Dus dat hij het totaal pakt van de maanden JANUARI, FEBRUARI en MAART.
Daarnaast moet tevens de mogelijkheid blijven bestaan dat je in het dashboard alleen de gegevens van bijvoorbeeld de maand januari op kan vragen.

Ik hoor graag van jullie.

Met vriendelijke groet,


Matthias

 

[Gijsbert1]

Formule voor kwartaal in:
D13:

=SOMMEN.ALS(Tabel2[Omzet];Tabel2[Jaar];$C$4;Tabel2[Nummer];$C$7;[COLOR="#FF0000"]Tabel2[Kwartaal];$C$6[/COLOR])

E13:

=SOMMEN.ALS(Tabel2[Norm];Tabel2[Jaar];$C$4;Tabel2[Nummer];$C$7;[COLOR="#FF0000"]Tabel2[Kwartaal];$C$6[/COLOR])

Als je de mogelijkheid wilt behouden om ook op de maand te kunnen blijven kijken zul je daar wel een keuze functionaliteit voor in moeten bouwen!

 

[Gijsbert1]

Of, omslachtig, als je 1 van de 2 leeg laat (C5 of C6), wordt het zo:
D13:

=ALS($C$5="";ALS($C$6="";SOMMEN.ALS(Tabel2[Omzet];Tabel2[Jaar];$C$4;Tabel2[Nummer];$C$7);SOMMEN.ALS(Tabel2[Omzet];Tabel2[Jaar];$C$4;Tabel2[Nummer];$C$7;Tabel2[Kwartaal];$C$6));SOMMEN.ALS(Tabel2[Omzet];Tabel2[Jaar];$C$4;Tabel2[Nummer];$C$7;Tabel2[Datum (mnd - jr)];DATUM($C$4;MAAND($C$5);1)))

E13:

=ALS($C$5="";ALS($C$6="";SOMMEN.ALS(Tabel2[Norm];Tabel2[Jaar];$C$4;Tabel2[Nummer];$C$7);SOMMEN.ALS(Tabel2[Norm];Tabel2[Jaar];$C$4;Tabel2[Nummer];$C$7;Tabel2[Kwartaal];$C$6));SOMMEN.ALS(Tabel2[Norm];Tabel2[Jaar];$C$4;Tabel2[Nummer];$C$7;Tabel2[Datum (mnd - jr)];DATUM($C$4;MAAND($C$5);1)))

 

[Matthias07]

Gijsbert, het werkt goed! Je wordt weer vriendelijk bedankt!