Weergave dashboard

[Matthias07]

Goedemiddag,

Een tijdje terug heeft iemand mij geholpen met creëren van een formule voor mijn dashboard in Excel. Een voorbeeld van dit dashboard is als bijlage toegevoegd. Bekijk bijlage Dashboard voorbeeld.xlsx

Er is echter een probleem naar voren gekomen. In het huidige dashboard is het mogelijk om de gegevens per maand en per kwartaal naar voren te halen. Indien ik de gegevens in werkblad DASHBOARD NUMMER per kwartaal wil laten zien, laat ik cel C5 leeg en voer ik bijvoorbeeld in cel C6 kwartaal 1 in. Het dashboard haalt dan de per KPI het resultaat en de norm van kwartaal 1 naar voren. Het probleem waar ik mee zit, zit hem bij de NORM.

Wanneer ik de gegevens van bijvoorbeeld kwartaal 1 opvraag, dan telt hij de normen januari, februari en maart bij elkaar op. Dit maakt voor sommige KPI'en, zoals de OMZET niet uit, maar met betrekking tot bijvoorbeeld de KPI % RESULTAAT wel. Als ik bijvoorbeeld respectievelijk voor de maanden januari, februari en maart 5%, 5% en 5% als norm heb ingevoerd, dan zegt hij dat de norm 15% is. De norm voor dat kwartaal betreft echter 5%.

Nu heb ik in gedachten om aan het dashboard een keuzemogelijkheid toe te voegen waarin je de weergave MAAND, KWARTAAL en JAAR kunt selecteren. Indien je bijvoorbeeld de weergave KWARTAAL selecteert, zou het dashboard de norm van het betreffende kwartaal naar voren moeten halen. Hiertoe heb ik bij werkblad OMZET de kolommen NORM MAAND, NORM KWARTAAL en NORM JAAR toegevoegd.
Indien je dus de weergave op kwartaal heb gezet, zou hij de normgegevens van de kolom NORM KWARTAAL vanuit het werkblad OMZET naar voren moeten halen.

Wie kan mij helpen om de formule in cel E14 in werkblad DASHBOARD NUMMER zo aan te passen dat hij dit realiseert?

Ik hoor graag van jullie!

Met vriendelijke groet,


Matthias

 

[Gijsbert1]

In de formule moet in dit geval geen: SOMMEN.ALS maar: GEMIDDELDEN.ALS worden gebruikt.

 

[Matthias07]

Bedankt, Gijsbert1!

 

[Matthias07]

Hallo Gijsbert1,

Met betrekking tot de NORM heb je helemaal gelijk. Echter, als ik GEMIDDELDEN.ALS ook gebruik bij RESULTAAT gaan de cijfers niet kloppen. Ik zal even een voorbeeld geven:

Omzet januari: Resultaat januari: % resultaat januari:

€ 10.000 € 700 7%

Omzet februari: Resultaat februari: % resultaat februari:

€ 5.000 € 300 6%

Omzet maart: Resultaat maart: % resultaat maart:

€ 1000 € 50 5%


Als je nu het gemiddelde pak, dan heb je: 7% + 6% + 5% / 3 = 6%

Het daadwerkelijke percentage van het 1e kwartaal is echter:
totaal resultaat / totale omzet X 100%
€ 1.050 / € 16.000 X 100% = 6,56%

Volgens mij moet ik de werkbladen van de KPI'en met percentages daarom wel zo aanpassen, zoals ik aangaf. Weet jij hoe ik de formule hiervoor moet aanpassen?

Ik hoor graag van je.

Vriendelijke groet,


Matthias de Wit

 

[Gijsbert1]

Als je nu even een voorbeeld bestandje maakt waarbij duidelijk wordt wat je wilt dan ga ik bovenstaand mogelijk begrijpen! :thumb:
In het voorbeeld bestandje in Post #3 staan volgens mij niet de juiste formules in bij resultaat! Laat staan de juiste verwijzingen naar tabbladen waaruit je de info haalt.

Graag het bestand met de formule (die dus niet lukt) op de juiste plek laten zien!
Nu is het maar gokken wat je bedoelt!

 

[Matthias07]

Reactie weergave dashboard Gijsbert1

Hallo Gijsbert,

Het was inderdaad wat onduidelijk Ik zal het nu proberen te verduidelijken. Als bijlage heb ik een nieuw voorbeeld toegevoegd.

In het werkblad DASHBOARD.. kan je nu aan de hand van een selectie in de cellen C3 (jaar), C4 (maand), C5 (kwartaal) en C6 (projectteam) de gewenste gegevens naar voren halen. Zoals je kunt zien presenteert het bestand de %RESULTAATgegevens van projectteam Gert in het jaar 2016. Aangezien ik CEL C4 leeg heb en in C5 kwartaal 1 heb geselecteerd, laat hij alle ingevoerde %RESULTAATgegevens van Gert in het 1e kwartaal van het jaar 2016 zien. Zowel de daadwerkelijke cijfers (RESULTAAT) als de gewenste cijfers (NORM). Bij de NORM pakt hij nu het gemiddelde van het jaar (op aanwijzing van jou heb ik SOMMEN.ALS aangepast naar GEMIDDELDEN.ALS).
Bij RESULTAAT telt hij echter alle resultaatpercentages van het 1e kwartaal, die ik in werkblad %RESULTAAT heb ingevoerd, bij elkaar op. Dit doet het bestaand door alle maanden van het 1e kwartaal bij elkaar op te tellen. Aangezien er geen goede resultaatcijfers naar voren komen als je bijvoorbeeld voor het 1e kwartaal het gemiddelde neemt van de resultaatcijfers van de maanden januari, februari en maart (dit heb ik in mijn vorige bericht proberen duidelijk te maken), is mijn gedachte om per kwartaal nog eens apart de resultaatcijfers in te voeren (en daarnaast ook per jaar). Daarom heb ik in het werkblad %RESULTAAT een kolom RESULTAATKWARTAAL toegevoegd (en dus ook RESULTAATJAAR).

Nu heb ik aan het dashboard in CEL C8 een selectiemogelijkheid toegevoegd waar aan de hand van de selectie de gegevens naar voren moeten komen. Dus als ik in CEL C8 de weergave KWARTAAL selecteer, dient hij niet uit kolom RESULTAATMAAND, maar uit de kolom RESULTAATKWARTAAL de te halen uit werkblad %RESULTAAT. Datzelfde geldt als ik bij weergave JAAR selecteer.

Hoe moet ik de formule in CEL D13 van werkblad DASHBOARD.. aanpassen zodat hij de gegevens aan de hand van de weergave naar voren haalt?

Ik hoor graag van jou.

Vriendelijke groet,


Matthias de Wit

 

[Gijsbert1]

Als je nu het gemiddelde pak, dan heb je: 7% + 6% + 5% / 3 = 6%

Het daadwerkelijke percentage van het 1e kwartaal is echter:
totaal resultaat / totale omzet X 100%
€ 1.050 / € 16.000 X 100% = 6,56%

Oke, dit is duidelijk!
Ik ga ervan uit dat de percentages middels de omzetcijfers gegenereerd worden, echter in dit voorbeeld bestand zitten geen omzetcijfers en zijn de percentages handmatig ingevuld!
Wat de formule moet is me wel duidelijk, maar hoe kan er nu een formule gemaakt worden als de info ontbreekt?

 

[Matthias07]

Gijsbert,

Dat klopt, maar dat wordt buiten dit bestand om berekend. De percentages zullen dus periodiek handmatig worden ingevoerd in het werkblad %RESULTAAT.

Het gaat mij met betrekking tot de formule bij RESULTAAT in het werkblad DASHBOARD PROJECTTEAMS erom dat dus de link wordt gelegd tussen de WEERGAVE die je selecteert (maand, kwartaal of jaar) en de kolom waar hij dan de gegevens weghaalt uit het werkblad %RESULTAAT (resultaatMAAND, resultaatKWARTAAL of resultaatJAAR).

 

[Gijsbert1]

Verander eens in de formules de verwijzingen naar kolom resultaatMAAND op de volgende manier:

=GEMIDDELDEN.ALS([COLOR="#FF0000"]Tabel2[resultaatMAAND][/COLOR];Tabel2[Jaar];$C$3;Tabel2[ProjectleiderManager];$C$6)

=GEMIDDELDEN.ALS([COLOR="#FF0000"]INDIRECT("Tabel2[resultaat"&C8&"]")[/COLOR];Tabel2[Jaar];$C$3;Tabel2[ProjectleiderManager];$C$6)

Het woord "MAAND" is hier eruit gehaald en vervangen door een verwijzing naar cel C8 met de "indirect" functie.
Als bijvoorbeeld cel C8 het woord "KWARTAAL" bevat dan verwijzen we naar: Tabel2[resultaatKWARTAAL]

(Wel even bij invoerblad de waarden onder dropdown weergave in hoofdletters aanpassen! of de kolomtitels uit je tabel voorzien van gewone letters)

De nieuwe formule in D12 zou er zo uit kunnen zien:

=ALS($C$4="";ALS($C$5="";GEMIDDELDEN.ALS(INDIRECT("Tabel2[resultaat"&C8&"]");Tabel2[Jaar];$C$3;Tabel2[ProjectleiderManager];$C$6);GEMIDDELDEN.ALS(INDIRECT("Tabel2[resultaat"&C8&"]");Tabel2[Jaar];$C$3;Tabel2[ProjectleiderManager];$C$6;Tabel2[Kwartaal];$C$5));GEMIDDELDEN.ALS(INDIRECT("Tabel2[resultaat"&C8&"]");Tabel2[Jaar];$C$3;Tabel2[ProjectleiderManager];$C$6;Tabel2[Datum];DATUM($C$3;MAAND($C$4);1)))

 

[Matthias07]

Gijsbert,

Dank je wel! Ik ga er naar kijken. Niet om alvast te ver vooruit te lopen, maar moet het geen SOMMEN.ALS zijn i.p.v. GEMIDDELDEN.ALS? Aangezien ik apart elke maand per persoon het % resultaat invoer en vervolgens aan het einde van het kwartaal apart nog een het % resultaat per kwartaal (beide in andere kolommen). Ook vul ik aan het einde van het jaar nog eens per persoon het % resultaat in van dat jaar (ook weer in een andere kolom).

 

[Matthias07]

Gijsbert,

Ik heb het net uitgeprobeerd en het werkt perfect! Bedankt weer voor je hulp!

Vriendelijke groet,


Matthias de Wit