Generieke formule
=A1+(COUNTIF(exp_rng,A1)-1)*adjustment
Samenvatting
Om banden te verbreken, kunt u een hulpkolom en de AANTAL.ALS-functie gebruiken om waarden aan te passen zodat ze geen duplicaten bevatten en daarom niet resulteren in gelijkspel. In het getoonde voorbeeld is de formule in D5:
=C5+(COUNTIF($C$5:C5,C5)-1)*0.01
Context
Soms, wanneer u functies zoals SMALL, LARGE of RANK gebruikt om de hoogste of laagste waarden te rangschikken, krijgt u een gelijkspel, omdat de gegevens duplicaten bevatten. Een manier om banden op deze manier te verbreken, is door een hulpkolom toe te voegen met waarden die zijn aangepast, en die waarden vervolgens te rangschikken in plaats van de originelen.
In dit voorbeeld is de logica die wordt gebruikt om waarden aan te passen willekeurig - de eerste dubbele waarde zal "winnen", maar je kunt de formule aanpassen om logica te gebruiken die past bij jouw specifieke situatie en use case.
Uitleg
In de kern gebruikt deze formule de AANTAL.ALS-functie en een uitbreidend bereik om het aantal keren dat waarden voorkomen te tellen. De uitbreidende verwijzing wordt gebruikt zodat AANTAL.ALS een lopend aantal exemplaren retourneert, in plaats van een totaal aantal voor elke waarde:
COUNTIF($C$5:C5,C5)
Vervolgens wordt 1 afgetrokken van het resultaat (waardoor de telling van alle niet-dubbele waarden nul is) en wordt het resultaat vermenigvuldigd met 0,01. Deze waarde is de "aanpassing" en opzettelijk klein om de oorspronkelijke waarde niet wezenlijk te beïnvloeden.
In het getoonde voorbeeld hebben Metrolux en Diamond beide dezelfde schatting van $ 5000. Omdat Metrolux als eerste in de lijst verschijnt, is de lopende telling van 5000 1 en wordt deze opgeheven door 1 af te trekken, zodat de schatting ongewijzigd blijft in de hulpkolom:
=C8+(COUNTIF($C$5:C8,C8)-1)*0.01 =C8+(1-1)*0.01 =C8+0 =C8
Voor Diamond is de lopende telling van 5000 echter 2, dus de schatting wordt aangepast:
=C11+(COUNTIF($C$5:C11,C11)-1)*0.01 =C11+(2-1)*0.01 =C11+1*0.01 =C11+0.01
Ten slotte worden de aangepaste waarden gebruikt voor rangschikking in plaats van de oorspronkelijke waarden in de kolommen G en H. De formule in G5 is:
=SMALL($D$5:$D$12,F5)
De formule in H5:
=INDEX($B$5:$B$12,MATCH(G5,$D$5:$D$12,0))
Zie deze pagina voor uitleg van deze formules.
Tijdelijke hulpkolom
Als u geen hulpkolom in de uiteindelijke oplossing wilt gebruiken, kunt u tijdelijk een hulpkolom gebruiken om berekende waarden op te halen, en vervolgens Plakken speciaal gebruiken om waarden "op hun plaats" te converteren en daarna de hulpkolom te verwijderen. Deze video demonstreert de techniek.
