Index-Vergelijken; meerdere resultaten?

[soleilrouge]

Hallo,

Ik kom niet uit het volgende probleem. Ik heb 4 lijsten met maten, waarvan ik drie maten wil selecteren met de kleinst bijbehorende waarde <1,2. De prioriteit ligt bij lijst 1, als de waarde daar >1,2, zoek je verder in lijst 2 etc.

Bijgevoegd een dummy file. Ik ben geen held in cse-formules, en heb het idee dat het hem daar in zit.

Kan iemand me verder helpen?



Much appreciated,

soleilrouge

 

[Cobbe]

Kijk eens of je hier een en ander kunt uithalen.
Heb de formules gemaakt zoals ik het zie.

 

[sylvester-ponte]

als in rood 1,1 voor komt en in de andere kleuren 0,5 ?
moet 1,1 dan op de eerste plaats komen?
of moet de kleinere 0,5 in groen op de eerste plaats komen?

 

[MarcelBeug]

Ik ben ook even aan het puzzelen geweest, met als resultaat: algemeen bruikbare formules, maar daar kwam nogal wat bij kijken om dat te bereiken.
Ik heb ook de layout wat gewijzigd om een en ander mogelijk te maken.

In de eerste plaats heb ik namen gedefinieerd voor de bereiken: RodeDelta, RodeMaat, GroeneDelta, GroeneMaat enzovoorts.
Deze kun je terugvinden in het naamvak links van de formulebalk (zie afbeelding).



In G3:G6 vind je de namen terug van de bereiken voor de Delta's. Deze zijn nodig voor de juiste werking van de formules.
Voor dit bereik heb ik ook een naam gedefinieerd: DeltaNamen.

In H2 staat de gezochte grenswaarde (1,2); deze kun je aanpassen. Ik heb er even 10 van gemaakt om de systematiek duidelijk te maken.
Deze cel bevat feitelijk alleen een getal, maar er is met een aangepast nummerformaat "Aantal <" voor geplakt. Bij wijziging alleen het getal invullen.
In H3:H6 staan de gevonden aantallen per groep; in H7 de som van H3:H6.

In I3:I6 staan zoekwaardes, ongeveer de cumulatieven van H3:H6 plus 1, maar dan verschoven en beginnend met 1. Deze zoekwaardes worden gebruikt in kolom L.

In K3:K32 staan de posities 1 t/m 30 (je had 1 t/m 3 gevraagd, maar ik heb het een stukje uitgebreid).
In L3:L32 staat telkens de naam van het bereik voor de delta, waarin de betreffende positie is gevonden. Leeg indien de positie groter is dan H7 (het totaal aantal)
In M3:M32 staat telkens de naam van het betreffende bereik voor de maat.
In N3:N32 staat telkens de positie binnen de groep.
In O3:O32 staat de gevonden Delta. Hierin staat ver achter de komma (en daarom onzichtbaar) nog een rijnummer om ex aequos af te handelen.
In P3: P32 staat de gevonden Maat.

De gevreesde CSE-(of matrix-)formules vind je alleen terug in kolom O en P.

Alle formules op regel 3 zijn gekopieerd naar beneden, dus binnen elke kolom is feitelijk maar 1 formule gebruikt.
De enige uitzondering hierop is het totaal in H7.

 

[pixcel]

Soms, zo niet vaak, wordt een probleem een stuk eenvoudiger als je je gegevens anders structureert. In dit geval:
Zet de gegevens in één tabel van vier kolommen: Categorie, Prioriteit, Maat en Delta (totaal 31+45 rijen).
Maak van die tabel een Excel tabel (Invoegen > Tabel)
Voeg nog een vijfde kolom "Voldoet" toe met de formule =([@Delta] < 1,2)
De tabel filteren op Voldoet = Waar geeft al het gewenste resultaat. Gebruik evt nog een draaitabel.

 

[sylvester-ponte]

zie grijze cellen:

ps pixcel heeft wel een punt

 

[VenA]

ps pixcel heeft wel een punt

zit in de naam:d

 

[sylvester-ponte]

:d

 

[soleilrouge]

Heel erg bedankt heren.

Ik begrijp dat de clou hem vooral zit in de getallen met *0,00001 te vermenigvulden en later af te ronden zodat je uiteindelijk toch onderscheid kan maken in twee dezelfde getallen.

@Cobbe, kan je mij uitleggen hoe je nu bij de laatste maat 130L komt? Die formule (en hulpformule die op 31 eindigt) vat ik niet helemaal..

Ik kom erachter dat mijn bestand niet helemaal aansloot bij wat ik wil bereiken. Heb nu een tweede tabblad toegevoegd met de logica en beter geordend. Ik wil een willekeurige lengte proberen te vergelijken met een tabel met vaste waarden en iedere keer de 3 dichtsbijzijnde maten terugkrijgen. Met bepaalde prioriteiten, deze heb ik met namen gedefinieerd.

Nu kan ik keurig de drie kleinste verschillen krijgen in de juiste prioriteiten, alleen nu is de crux hoe ik bij de bijbehorende maten kom van deze verschillen. Alles is nu geschreven in matrixformules. Moet ik toch eerst de tussenstap met "verschil-berekeningen" ook helemaal uitschrijven? Of is er een kortere weg..

Bekijk bijlage Helpmij-top3.xlsx

 

[Cobbe]

Bij deze is de hulpformule niet meer nodig, ingewerkt in deze formule:

=ALS.FOUT(VERT.ZOEKEN($J$3;INDIRECT("$A$"&ALS.FOUT(VERGELIJKEN($K$3;$D$3:$D$47;0);VERGELIJKEN($K$3;$A$3:$B$47;0))+3 & ":$B$47");2;0);VERT.ZOEKEN($J$3;INDIRECT("$C$"&ALS.FOUT(VERGELIJKEN($K$3;$D$3:$D$47;0);VERGELIJKEN($K$3;$A$3:$B$47;0))+3 & ":$D$47");2;0))

Voor de 3de waarde laat ik het zoeken pas starten vanaf de rij waarop de 2de waarde is gevonden. (Hier rij 31)

 

[soleilrouge]

Bedankt, maar probeerde juist de formule te begrijpen in plaats van klakkeloos over te nemen

Iemand een antwoord op mijn laatste vraag? Of de desbetreffende maat daar ook in te achterhalen is?

 

[sylvester-ponte]

kijk eens naar het voorbeeld in post 6# daar staat de maat ook in

 

[soleilrouge]

Bedankt!! Ben eruit hoor.