Általános képlet
=A1+(COUNTIF(exp_rng,A1)-1)*adjustment
Összegzés
A kapcsolatok megszakításához használhat egy segítő oszlopot és a COUNTIF függvényt az értékek beállításához, hogy azok ne tartalmazzanak duplikátumokat, és ezért nem eredményeznek kapcsolatokat. A bemutatott példában a D5 képlete a következő:
=C5+(COUNTIF($C$5:C5,C5)-1)*0.01
Kontextus
Előfordul, hogy amikor olyan funkciókat használ, mint a KIS, NAGY vagy RANK a legmagasabb vagy legalacsonyabb értékek rangsorolásához, akkor kapcsolatokhoz jut, mivel az adatok duplikátumokat tartalmaznak. Az ilyen kapcsolatok megszakításának egyik módja az, ha hozzáad egy segítő oszlopot korrigált értékekkel, majd rangsorolja ezeket az értékeket az eredetik helyett.
Ebben a példában az értékek beállításához használt logika véletlenszerű - az első duplikált érték "nyer", de a képletet úgy módosíthatja, hogy az adott helyzetnek és felhasználási esetnek megfelelő logikát használjon.
Magyarázat
Lényegében ez a képlet a COUNTIF függvényt és egy kibővített tartományt használ az értékek előfordulásainak számításához. A bővülő referenciát úgy használjuk, hogy a COUNTIFS az események összesített számát adja vissza az egyes értékek összesített száma helyett:
COUNTIF($C$5:C5,C5)
Ezután levonunk 1-et az eredményből (ami az összes nem duplikált érték számát nullává teszi), és az eredményt megszorozzuk 0,01-gyel. Ez az érték a "kiigazítás", és szándékosan kicsi, hogy ne befolyásolja érdemben az eredeti értéket.
A bemutatott példában a Metrolux és a Diamond egyaránt 5000 dollárra becsülik. Mivel a Metrolux az első helyen jelenik meg a listán, az 5000 futási szám 1, és 1 kivonásával törlődik, így a becslés változatlan marad a segítő oszlopban:
=C8+(COUNTIF($C$5:C8,C8)-1)*0.01 =C8+(1-1)*0.01 =C8+0 =C8
Diamond esetében azonban az 5000 futási szám 2, így a becslés kiigazításra kerül:
=C11+(COUNTIF($C$5:C11,C11)-1)*0.01 =C11+(2-1)*0.01 =C11+1*0.01 =C11+0.01
Végül a kiigazított értékeket használjuk a rangsoroláshoz a G és a H oszlop eredeti értékei helyett. A G5 képlete a következő:
=SMALL($D$5:$D$12,F5)
A H5 képlete:
=INDEX($B$5:$B$12,MATCH(G5,$D$5:$D$12,0))
Ezen képletek magyarázatát ezen az oldalon találja.
Ideiglenes segítő oszlop
Ha nem akar egy segítő oszlopot használni a végső megoldásban, akkor ideiglenesen segítő oszlopot használhat a kiszámított értékek megszerzéséhez, majd a Speciális beillesztés segítségével konvertálja az értékeket "a helyére", és utána törölje a segítő oszlopot. Ez a videó bemutatja a technikát.
