Generisk formel
=A1+(COUNTIF(exp_rng,A1)-1)*adjustment
Sammendrag
For å bryte bånd kan du bruke en hjelpekolonne og COUNTIF-funksjonen til å justere verdier slik at de ikke inneholder duplikater, og derfor ikke vil resultere i bånd. I eksemplet vist er formelen i D5:
=C5+(COUNTIF($C$5:C5,C5)-1)*0.01
Kontekst
Noen ganger, når du bruker funksjoner som SMALL, LARGE eller RANK for å rangere høyeste eller laveste verdier, ender du med bånd, fordi dataene inneholder duplikater. En måte å bryte bånd som dette er å legge til en hjelpekolonne med verdier som er justert, og deretter rangere disse verdiene i stedet for originalene.
I dette eksemplet er logikken som brukes til å justere verdiene tilfeldig - den første dupliserte verdien vil "vinne", men du kan justere formelen slik at den bruker logikk som passer til din spesielle situasjon og brukstilfelle.
Forklaring
I kjernen bruker denne formelen COUNTIF-funksjonen og et utvidende område for å telle forekomster av verdier. Den utvidende referansen brukes slik at COUNTIFS returnerer et løpende antall forekomster, i stedet for et totalt antall for hver verdi:
COUNTIF($C$5:C5,C5)
Deretter trekkes 1 fra resultatet (som gjør tellingen av alle ikke-dupliserte verdier null) og resultatet multipliseres med 0,01. Denne verdien er "justering", og med vilje liten for ikke å påvirke den opprinnelige verdien vesentlig.
I eksemplet vist har Metrolux og Diamond begge det samme estimatet på $ 5000. Siden Metrolux vises først i listen, er antall løp på 5000 1 og kanselleres ved å trekke 1, så estimatet forblir uendret i hjelpekolonnen:
=C8+(COUNTIF($C$5:C8,C8)-1)*0.01 =C8+(1-1)*0.01 =C8+0 =C8
For Diamond er løpstallet på 5000 imidlertid 2, så estimatet justeres:
=C11+(COUNTIF($C$5:C11,C11)-1)*0.01 =C11+(2-1)*0.01 =C11+1*0.01 =C11+0.01
Til slutt brukes de justerte verdiene for rangering i stedet for de opprinnelige verdiene i kolonnene G og H. Formelen i G5 er:
=SMALL($D$5:$D$12,F5)
Formelen i H5:
=INDEX($B$5:$B$12,MATCH(G5,$D$5:$D$12,0))
Se denne siden for en forklaring på disse formlene.
Midlertidig hjelpesøyle
Hvis du ikke vil bruke en hjelpekolonne i den endelige løsningen, kan du bruke en hjelpekolonne midlertidig for å få beregnede verdier, og deretter bruke Lim inn spesial for å konvertere verdier "på plass" og slette hjelpekolonnen etterpå. Denne videoen demonstrerer teknikken.
