Generisk formel
=A1+(COUNTIF(exp_rng,A1)-1)*adjustment
Sammanfattning
För att bryta band kan du använda en hjälpkolumn och COUNTIF-funktionen för att justera värden så att de inte innehåller dubbletter och därför inte leder till band. I exemplet som visas är formeln i D5:
=C5+(COUNTIF($C$5:C5,C5)-1)*0.01
Sammanhang
Ibland, när du använder funktioner som SMALL, LARGE eller RANK för att ranka högsta eller lägsta värden, slutar du med band eftersom informationen innehåller dubbletter. Ett sätt att bryta band så här är att lägga till en hjälpkolumn med värden som har justerats och sedan rangordna dessa värden istället för originalen.
I det här exemplet är logiken som används för att justera värden slumpmässig - det första duplikatvärdet kommer att "vinna", men du kan justera formeln för att använda logik som passar din specifika situation och användningsfall.
Förklaring
I grunden använder denna formel COUNTIF-funktionen och ett expanderande intervall för att räkna förekomster av värden. Den expanderande referensen används så att COUNTIFS returnerar ett löpande antal händelser, istället för ett totalt antal för varje värde:
COUNTIF($C$5:C5,C5)
Därefter subtraheras 1 från resultatet (vilket gör att antalet icke-duplicerade värden är noll) och resultatet multipliceras med 0,01. Detta värde är "justering" och avsiktligt litet för att inte påverka det ursprungliga värdet.
I det visade exemplet har Metrolux och Diamond båda samma uppskattning på $ 5000. Eftersom Metrolux dyker upp först i listan är det löpande antalet på 5000 1 och avbryts genom att subtrahera 1, så uppskattningen förblir oförändrad i hjälpkolumnen:
=C8+(COUNTIF($C$5:C8,C8)-1)*0.01 =C8+(1-1)*0.01 =C8+0 =C8
För Diamond är dock löpantalet på 5000 2, så uppskattningen justeras:
=C11+(COUNTIF($C$5:C11,C11)-1)*0.01 =C11+(2-1)*0.01 =C11+1*0.01 =C11+0.01
Slutligen används de justerade värdena för rangordning istället för de ursprungliga värdena i kolumnerna G och H. Formeln i G5 är:
=SMALL($D$5:$D$12,F5)
Formeln i H5:
=INDEX($B$5:$B$12,MATCH(G5,$D$5:$D$12,0))
Se den här sidan för en förklaring av dessa formler.
Tillfällig hjälparpelare
Om du inte vill använda en hjälpkolumn i den slutliga lösningen kan du tillfälligt använda en hjälpkolumn för att få beräknade värden och sedan använda Klistra in special för att konvertera värden "på plats" och ta bort hjälpkolumnen efteråt. Denna video visar tekniken.
