Обща формула
=RANK(A1,values)+COUNTIF(exp_rng,A1)-1
Обобщение
За да сортирате динамично данни, които съдържат само числови стойности, можете да използвате помощна колона и формула, създадена с функциите RANK и COUNTIF. В показания пример формулата в D5 е:
=RANK(C5,sales)+COUNTIF($C$5:C5,C5)-1
където "продажби" е наименуваният диапазон C5: C11.
Обяснение
Забележка: тази формула е настройка за формула, която може да извлича и показва данни, използвайки предварително зададен ред на сортиране в помощна колона. Един пример тук.
Сърцевината на тази формула е функцията RANK, която се използва за генериране на ранг на стойностите на продажбите, където най-големият брой е класиран # 1:
=RANK(C5,sales)
Тук RANK използва посочения диапазон "продажби" (C5: C11) за удобство. По подразбиране RANK ще присвои 1 на най-високата стойност, 2 на втората най-висока стойност и т.н. Това работи перфектно, докато цифровите стойности са уникални. Въпреки това, за да обработим числови стойности, които съдържат дубликати, трябва да използваме функцията COUNTIF за прекъсване на връзките. Това се прави чрез добавяне на резултата от този фрагмент към стойността, върната от RANK:
COUNTIF($C$5:C5,C5)-1
Забележете, че диапазонът е въведен като смесена препратка, която ще се разширява при копиране на формулата в таблицата. Както е написано, тази препратка ще включва текущия ред, така че изваждаме 1, за да "нулираме" първата поява. Това означава, че изразът ще върне нула за всяка числова стойност, докато не бъде открит дубликат. При втората инстанция изразът ще върне 1, при третата инстанция ще върне 2 и т.н. Това ефективно прекъсва връзките и позволява на формулата да генерира последователен списък с числа без пропуски.
След като формулата е на мястото си, данните могат да бъдат сортирани от помощната колона. Също така може да бъде извлечен с INDEX, като се използват стойностите в помощната колона.
Забележка: Тази формула е адаптирана от пример в отличната книга Control + Shift + Enter, от Mike Girvin.
