Обща формула
=INDEX(data,MATCH(ROWS(exp_range),sort_values,0))
Обобщение
За да сортирате произволно съществуващите стойности с формула, можете да използвате формула INDEX и MATCH заедно с помощни колони, както е показано на екранната снимка. В показания пример формулата в E5 е:
=INDEX(names,MATCH(ROWS($D$5:$D5),sort,0))
където "names" е наименуваният диапазон B5: B11, "rand" е наименуваният диапазон C5: C11, а "sort" е наименуваният диапазон D5: D11.
Обяснение
Тази формула зависи от две помощни колони. Първата помощна колона съдържа произволни стойности, създадени с функцията RAND (). Формулата в C5, копирана надолу, е:
=RAND()
Функцията RAND генерира произволна стойност на всеки ред.
Забележка: RAND е променлива функция и ще генерира нови стойности при всяка промяна на работния лист.
Втората помощна колона съдържа числата, използвани за сортиране на данни, генерирани с формула. Формулата в D5 е:
=RANK(C5,rand)+COUNTIF($C$5:C5,C5)-1
Вижте тази страница за обяснение на тази формула.
Формулата в E5 е:
=INDEX(names,MATCH(ROWS($D$5:$D5),sort,0))
Тук функцията INDEX се използва за извличане на стойности в посочения диапазон "имена", като се използват стойностите за сортиране в посочения диапазон "сортиране". Действителната работа по определяне на стойността за извличане се извършва с помощта на функцията MATCH в този фрагмент:
MATCH(ROWS($D$5:$D5),sort,0)
Вътре в MATCH, функцията ROWS получава разширяващ се диапазон като стойност за търсене, която започва като една клетка и се разширява, когато формулата се копира надолу в колоната. Това увеличава справочната стойност, започвайки от 1 и продължавайки до 7. MATCH след това връща позицията на справочната стойност в списъка.
Позицията се подава към INDEX като номер на реда и INDEX извлича името на тази позиция.
