Dynamische TOPX

[robina1511]

Beste Excel gebruikers,

Ik worstel met een probleempje om uit een lijst data met verkoopsgegevens per:

-jaar
-maand
-productrange
-accountmanager
-klant

een dynamische topX lijst te genereren,
Ik heb gepoogd om het probleem te schetsen aan de hand van een gereduceerd lijstje in bijlage.

Aan de hand van een draaitabel kan ik makkelijk het gewenste resultaat bekomen,echter probeer ik rond deze
data een leuk ogend salesdashboard op te bouwen.
Daarom zou ik het heel erg verkiezen dit resultaat via formules samen te stellen en zo alle vrijheid voor weergave te behouden en niet meer ruimte in te nemen dan strikt noodzakelijk.

Mijn grootste dank alvast voor alle tips en iedereen alsnog een vrolijke kerst toegewenst.

 

[Cobbe]

Kun je hier een of ander uithalen?

 

[robina1511]

Hallo Cobbe,

Bedankt voor jouw reactie,

Wat ik misschien niet duidelijk genoeg verwoordde is:
Ik wil graag een topX lijstje bekomen op klantenomzet van hoog naar laag,
volgens de variabelen die verschillen per jaar,periode,AM....

Beste groeten Robina

 

[sylvester-ponte]

zie grijze cellen:

 

[robina1511]

Goedemorgen Sylvester,

Bedankt voor jouw reactie,
De manier waarop jij de functies combineert zijn voor mij nieuw,
ik ga hiermee even aan de slag om het helemaal proberen te begrijpen.

Beste groeten Peter.

 

[robina1511]

Ik heb begrepen dat de matrixformule de rij bepaalt die voldoet aan de voorwaarden die gedefinieerd worden bij de variabelen,dat zijn steeds unieke records.Daarna wordt de klant en omzetwaarde opgezocht met een index functie.

Wanneer ik nu de variabelen ga beperken tot:
-accountmanager
-jaar
-periode

Zullen er eventueel meerdere records opgeteld dienen te worden,(product1,product2,.....)

Hoe kan ik het dan oplossen ?


Mvg Peter

 

[VenA]

Ik begrijp de beperking die je aan een draaitabel toewijst niet helemaal. Het is juist een krachtig en snel middel om gegevens te groeperen.

 

[robina1511]

Hallo VenA,

Bedankt voor jouw reactie,

Ik gebruik graag en vaak draaitabellen;
als ik niet voor mijn deadline een geschikte functie kan aanwenden zal ik het met een draaitabel moeten doen,
waarschijnlijk voeg ik dan enkele slicers toe.
Maar deze keer is het uitzicht van het rapport nogal belangrijk.
Vandaar dat ik het graag in een functie vervat zag en zo meer vrijheid heb om de lay-out te verzorgen.

Wat mij wel boeit in jouw bijlage,
hoe koppel jij het veldje "top" aan het "top10 filter" van de draaitabel ,is dat met een besturingselement ?


Beste groeten

Peter.

 

[VenA]

In de module van Sheet1 staat een beetje code die ervoor zorgt dat de 'top x' aangepast wordt.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) = "E1" Then
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
        With ActiveSheet.PivotTables("PivotTable1")
            .PivotFields("Customer").ClearAllFilters
            .PivotFields("Customer").PivotFilters.Add xlTopCount, .PivotFields("turnover "), [E1].Value
        End With
        .EnableEvents = True
    End With
End If
End Sub

Hetzelfde kan je ook doen voor de rapportfilters. Door de draaitabel te verbergen kan je deze wel gebruiken voor de rapportage die je zelf voor ogen hebt.

 

[robina1511]

Ok VenA,

ik zie wat je bedoelt ,dank daarvoor.
Veel bewondering voor je manier van werken en VBA skills.

Graag had ik toch nog de manier gevonden zoals Sylvester de oplossing aanbracht,
met enkel jaar,periode en accountmanager als criteria.

Beste groeten

Peter

 

[WHER]

Indien je het absoluut met formules wil aanpakken, bekijk dan deze bijlage eens.

Wel best een aantal cases handmatig narekenen om zeker te zijn dat alles klopt.

 

[sylvester-ponte]

bedoel je zo iets? je regelt het filter met het filter zelf:
om de formules eenvoudig te houden heb ik in kolom A een zichtbaarhijds formule gezet.
nu moet je zelf de grijze cellen nog sorteren. het kan natuurlijk veel uitgebreider.
het gaat om het idee.

 

[pixcel]

Nmm moet een dashboard functioneel, robuust en eenvoudig aanpasbaar zijn ("ja dat ziet er goed uit, maar ik wil eigenlijk...").
Bijlage is voor 2013 en mbv Power Pivot.

 

[robina1511]

Beste Allen,

Mag ik iedereen nogmaals hartelijk danken voor de geleverde bijdrage,
De werkwijze van Wher droeg mijn voorkeur weg maar mijn bestand "groeide" hierdoor nogal explosief,
Daardoor is het uiteindelijk toch een draaitabel met slicers geworden.
Desalniettemin ben ik zeer tevreden met hetgeen ik kon bijleren.

Beste groeten

Peter