Top 10 grootste waarden bepalen o.b.v. 2 criteria

[Lun4t1c]

Hallo,

Ik heb een tabel met gegevens waaruit ik de top 10 grootste waarden wil vinden.
In deze tabel staat voor verschillende productgroepen per winkel de afwijking tussen daadwerkelijk verkocht en verwachte verkopen.

Het is me gelukt om de top 10 te bepalen met mijn eerste criteria zijnde dat er enkel naar 1 specifieke productgroep wordt bekeken.
Probleem is nu nog dat in de top 10 altijd de winkel 'totaal' bovenaan staat...

Eigenlijk wil ik dus in mijn formule in kolom D een tweede criteria opnemen die aangeeft dat de regel waarin 'totaal' staat niet wordt bekeken.
Dit lukt me echter niet... Iemand die me kan helpen?

Bekijk bijlage Voorbeeld .xlsm

Zie bijlage voor een voorbeeld met gereduceerde data.

 

[jkpieterse]

Is het geen optie om de totalen uit je gegevens te verwijderen?

 

[Lun4t1c]

Goede vraag, maar dat is eigenlijk niet mogelijk.
O.b.v. dezelfde data worden er ook wat andere analyses gemaakt waar het totaal wel nodig is.

Tevens wordt de data zo af en toe ververst en onze systemen draaien dit uit met de regel 'totaal' er tussen.
Ik streef erna de data niet te hoeven bewerken maar dat de berekeningen direct na het inplakken van de nieuwe data functioneerd.

 

[AlexCEL]

Wat een vreselijk ingewikkelde formules heb je gebruikt.... de sheet wordt er enorm traag van. Als je zoveel herhaling ziet in een formule moet je eens afvragen of er niet slimmere oplossingen zijn. En die zijn er uiteraard....

Hierbij mijn voorstel (versie 2 n.a.v. commentaar hieronder: verwijzingsfoutje gefixed + sheet houdt nu rekening met gelijke afwijkingen).

Bekijk bijlage Voorbeeld (AC) (2).xlsm

 

[E v R]

Die totaal tellingen sluiten niet met de som van ma-zo...
Voor de top tien kun je ook van de niet matrix-formule Aggregaat gebruikmaken,
De formule van Alex hierboven om de juiste winkels hier bij te zoeken lijkt mij nu nog niet juist

 

[AlexCEL]

Ik zal er nog even naar kijken!

Edit: gefixed, ontbraken paar $-tekens...

 

[E v R]

gefixed...

:thumb:

evt andere formule in B6 en verder: (nu nog met hele kolommen)

=AGGREGATE(14;6;(Data!$W:$W=Rapportage!$A$1)*(Data!$AF:$AF)*(LEFT(Data!$X:$X;6)<>"Totaal");ROW()-5)

 

[Lun4t1c]

Super, bedankt voor de hulp, beide formules werken prima.
Voor formule versie 1 vraag ik me nog wel af waar het gedeelte 1+RIJ('Data'!$X$2:$X$175)/10000000 voor dient. Kan je me dit uitleggen?

Ik heb nog wel een vervolg uitdaging:
De top 10 wordt nu enkel op basis van de kolom 'Totaal' bepaald.
Eigenlijk zou ik ook graag willen dat de top 10 over de hele week wordt bepaald (bv op dinsdag bij winkel 333 was de afwijking 2000 en op 2 : woensdag bij 444 was het 1800).
Voor kolom B lukt het me om de formules aan te passen, maar het lukt me dan niet om het filiaal er automatisch bij te vinden...

Kolom B maak ik als volgt:
=GROOTSTE(ALS(ISGETAL('Data'!$Y$2:$AE$175);'Data'!$Y$2:$AE$175;0)*ALS('Data'!$W$2:$W$175=$A$1;1;0)*ALS(ISGETAL('Data'!$X$2:$X$175);1;0)*(1+RIJ('Data'!$X$2:$X$175)/10000000);$R6)
of =AGGREGAAT(14;6;('Data'!$W:$W=Rapportage!$A$1)*('Data'!$Y:$AE)*(LINKS('Data'!$X:$X;6)<>"Totaal");$R6)

Wat zou de formule in kolom C dan moeten zijn...?

 

[jkpieterse]

Als je de totalen er toch tussenuit zou kunnen halen (kijk eens naar Powerquery!), dan is het super simpel om via een draaitabel de top 10 te laten bepalen. Komen er geen formules meer aan te pas...

 

[AlexCEL]

Het stukje "1+RIJ('Data'!$X$2:$X$175)/10000000" zorgt ervoor dat elke afwijking uniek wordt. Dit is van belang bij het zoeken van een winkelnummer bij de afwijking, indien er gelijke afwijkingen zijn.

Ik heb een opzet gemaakt voor een weekoverzicht, kun je daarin zien hoe de formules aangepast moeten worden.

Bekijk bijlage Voorbeeld (AC) (2.1).xlsm

 

[Lun4t1c]

Ok bedankt!
Ik ben bij deze voldoende geholpen

 

[AlexCEL]

Mooi zo. Als je tevreden bent mag je de vraag (rechts bovenaan de pagina) op 'opgelost' zetten.