Grootste op basis van berekende voorwaarden

[Ronald19822]

Mijn vraag
Ik heb een tabel. Voor de casus zal ik hem fictief simpel beschrijven.

Kolom A: Order ID
Kolom B: Inkoopprijs order
Kolom C: Verkoopprijs order

Een 2e tabel in kolom D Order ID's die niet meegenomen moeten worden.

Ik wil de grootste marge of een top 5 van grootste marges. Hiervoor kan ik dus MAX of GROOTSTE gebruiken. De volgende formule werkt.

=AFRONDEN(GROOTSTE(ALS.FOUT(TabelOrders[Verkoopprijs]/TabelOrders[Inkoopprijs]-1;"");1);2)

Ik heb gister en vandaag inmiddels 2 uur zitten stuntelen om ervoor proberen te zorgen dat de GROOTSTE wordt gebaseerd op een lijst zonder de ID's uit de 2e tabel. Met MAX.ALS.VOORWAARDEN variaties met ALS formules kwam ik er niet uit. Dit zou toch mogelijk moeten zijn? Iemand een goede tip of de oplossing?

[Reptile209]

Je kunt de gevonden marge uit GROOTSTE in tabel 1 vermenigvuldigen met 0 als het ordernummer ook in tabel 2 voor komt.

Met vergelijken() kan je de ID's in tabel2 zoeken, je krijgt een #N/B als hij NIET gevonden wordt, en >0 als hij WEL gevonden wordt. Zet dat met een ALS om naar respectievelijk 0 en 1 en dan ben je er volgens mij.

[g0tanks]

Verplaatst eerst kolom D naar een ander tabblad, want het is niet overzichtelijk om twee verschillende tabellen op hetzelfde tabblad te hebben. Daarna lijkt mij een nette en makkelijke aanpak om wat hulpkolommen toe te voegen.

Kolom A t/m C blijven hetzelfde
Kolom D wordt de marge met dezelfde formule die je nu ook al gebruikt, maar dan zonder GROOTSTE()
Kolom E bevat ja of nee afhankelijk van of de order moet worden meegenomen, dat kan je bijv. met VERGELIJKEN() opzoeken in de andere lijst

Nu filter je in kolom E op ja en sorteer je kolom D van groot naar klein om je top 5 marges te zien (en meer).

[Lustucru]

Reptile209 schreef op vrijdag 8 mei 2020 @ 15:14:
Je kunt de gevonden marge uit GROOTSTE in tabel 1 vermenigvuldigen met 0 als het ordernummer ook in tabel 2 voor komt.

Met vergelijken() kan je de ID's in tabel2 zoeken, je krijgt een #N/B als hij NIET gevonden wordt, en >0 als hij WEL gevonden wordt. Zet dat met een ALS om naar respectievelijk 0 en 1 en dan ben je er volgens mij.

Dat ongeveer, en dat kan in één matrixformule. Even uitgaande van een tabel met in A de id's, in B de waarden en in C de uit te sluiten Id's dan heb je hier genoeg aan:

code:

=GROOTSTE((B1:B10)*ISNB(VERGELIJKEN(A1:A10;C1:C10;0));1)

Je bouwt dus eerst een matrix op (vandaar de invoer met ctrl+shift+enter) van de waarden in B die je vermenigvuldigt met het resultaat van ISNB(vergelijken()). Die geeft 1 als hij niet voorkomt, 0 als hij wel voorkomt. Daar neem je dan de k-e grootste van.

[Reptile209]

Lustucru schreef op vrijdag 8 mei 2020 @ 18:51:
[...]


Dat ongeveer, en dat kan in één matrixformule. Even uitgaande van een tabel met in A de id's, in B de waarden en in C de uit te sluiten Id's dan heb je hier genoeg aan:

code:

=GROOTSTE((B1:B10)*ISNB(VERGELIJKEN(A1:A10;C1:C10;0));1)

Je bouwt dus eerst een matrix op (vandaar de invoer met ctrl+shift+enter) van de waarden in B die je vermenigvuldigt met het resultaat van ISNB(vergelijken()). Die geeft 1 als hij niet voorkomt, 0 als hij wel voorkomt. Daar neem je dan de k-e grootste van.

offtopic:
ik ben altijd een beetje huiverig voor matrixformules voor mensen die daar niet heel erg in thuis zijn (zowel TS zelf als anderen die mogelijk met zijn sheet moeten werken). Met een paar hulpkolommen is - vind ik - de logica makkelijker te volgen en te troubleshooten. En bij niet al te extreem gebruik maakt het volgens mij qua performance ook niet echt uit.

Maar nette oplossing hoor (hoewel TS de 0 en 1 van ISNB volgens mij nog moet inverteren) als ik de vraag goed gelezen heb)

[Ronald19822]

Dank allemaal. Het is gelukt met behulp van het ISNB-trucje.