Generisk formel
=A1+(COUNTIF(exp_rng,A1)-1)*adjustment
Resumé
For at bryde bånd kan du bruge en hjælpekolonne og COUNTIF-funktionen til at justere værdier, så de ikke indeholder dubletter og derfor ikke resulterer i bånd. I det viste eksempel er formlen i D5:
=C5+(COUNTIF($C$5:C5,C5)-1)*0.01
Sammenhæng
Nogle gange, når du bruger funktioner som SMALL, LARGE eller RANK til at rangere højeste eller laveste værdier, ender du med bånd, fordi dataene indeholder dubletter. En måde at bryde bånd som dette er at tilføje en hjælpekolonne med værdier, der er blevet justeret, og derefter rangere disse værdier i stedet for originalerne.
I dette eksempel er logikken, der bruges til at justere værdier, tilfældig - den første duplikatværdi vil "vinde", men du kan justere formlen til at bruge logik, der passer til din særlige situation og brugssag.
Forklaring
I kernen bruger denne formel COUNTIF-funktionen og et ekspanderende område til at tælle forekomster af værdier. Den ekspanderende reference bruges således, at COUNTIFS returnerer et løbende antal forekomster i stedet for et samlet antal for hver værdi:
COUNTIF($C$5:C5,C5)
Dernæst trækkes 1 fra resultatet (hvilket gør optællingen af alle ikke-duplikerede værdier nul), og resultatet ganges med 0,01. Denne værdi er "justering" og med vilje lille for ikke at påvirke den oprindelige værdi væsentligt.
I det viste eksempel har Metrolux og Diamond begge det samme skøn på $ 5000. Da Metrolux vises først på listen, er det løbende antal på 5000 1 og annulleres ved at trække 1, så estimatet forbliver uændret i hjælpekolonnen:
=C8+(COUNTIF($C$5:C8,C8)-1)*0.01 =C8+(1-1)*0.01 =C8+0 =C8
For Diamond er løbstallet på 5000 dog 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
Endelig bruges de justerede værdier til rangering i stedet for de oprindelige værdier i kolonne G og H. Formlen i G5 er:
=SMALL($D$5:$D$12,F5)
Formlen i H5:
=INDEX($B$5:$B$12,MATCH(G5,$D$5:$D$12,0))
Se denne side for en forklaring af disse formler.
Midlertidig hjælper kolonne
Hvis du ikke vil bruge en hjælpekolonne i den endelige løsning, kan du midlertidigt bruge en hjælpekolonne til at få beregnede værdier og derefter bruge Indsæt speciel til at konvertere værdier "på plads" og slette hjælpekolonnen bagefter. Denne video demonstrerer teknikken.
