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