Обща формула
=A1+(COUNTIF(exp_rng,A1)-1)*adjustment
Обобщение
За да прекъснете връзките, можете да използвате помощна колона и функцията COUNTIF, за да коригирате стойностите, така че да не съдържат дубликати и следователно няма да доведат до връзки. В показания пример формулата в D5 е:
=C5+(COUNTIF($C$5:C5,C5)-1)*0.01
Контекст
Понякога, когато използвате функции като SMALL, LARGE или RANK, за да класирате най-високите или най-ниските стойности, в крайна сметка получавате връзки, тъй като данните съдържат дубликати. Един от начините за прекъсване на връзки като този е да добавите помощна колона със стойности, които са коригирани, след което да ги класирате вместо оригиналите.
В този пример логиката, използвана за коригиране на стойности, е произволна - първата дублирана стойност ще „спечели“, но можете да настроите формулата, за да използвате логика, която отговаря на вашата конкретна ситуация и случай на употреба.
Обяснение
В основата си тази формула използва функцията COUNTIF и разширяващ се обхват за преброяване на появите на стойности. Използва се разширяващата се препратка, така че COUNTIFS връща текущ брой повторения, вместо общ брой за всяка стойност:
COUNTIF($C$5:C5,C5)
След това 1 се изважда от резултата (което прави броя на всички не дублиращи се стойности нула) и резултатът се умножава по 0,01. Тази стойност е "корекцията" и умишлено малка, за да не повлияе съществено на първоначалната стойност.
В показания пример Metrolux и Diamond имат една и съща оценка от $ 5000. Тъй като Metrolux се появява първи в списъка, текущият брой на 5000 е 1 и се анулира чрез изваждане на 1, така че оценката остава непроменена в помощната колона:
=C8+(COUNTIF($C$5:C8,C8)-1)*0.01 =C8+(1-1)*0.01 =C8+0 =C8
Въпреки това, за Diamond броят на 5000 е 2, така че оценката се коригира:
=C11+(COUNTIF($C$5:C11,C11)-1)*0.01 =C11+(2-1)*0.01 =C11+1*0.01 =C11+0.01
И накрая, коригираните стойности се използват за класиране вместо първоначалните стойности в колони G и H. Формулата в G5 е:
=SMALL($D$5:$D$12,F5)
Формулата в H5:
=INDEX($B$5:$B$12,MATCH(G5,$D$5:$D$12,0))
Вижте тази страница за обяснение на тези формули.
Временна помощна колона
Ако не искате да използвате помощна колона в крайното решение, можете временно да използвате помощна колона, за да получите изчислени стойности, след това използвайте Специално поставяне, за да преобразувате стойности „на място“ и след това да изтриете помощната колона. Това видео демонстрира техниката.
