Загальна формула
=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 отримує ім'я в цій позиції.
