Сортировка в случайном порядке списка ЧИСЛОвых значений в EXCEL

Отсортируем исходный массив ЧИСЕЛ в случайном порядке.

Пусть задан диапазон ячеек содержащий числа ( A2:A8 ).

Задача

Отсортируем числа из диапазона A2:A8 в случайном порядке. Новый список разместим в столбце E .

Решение

Чтобы отсортировать список в случайном порядке будем действовать следующим образом (см. Файл примера ):

• каждому значению списка сопоставим случайное число (для этого используем функцию СЛЧИС() ), расположив его в столбце C ;
• для каждого случайного числа из столбца C с помощью формулы =РАНГ(C7;$C$7:$C$13) определим ранг — величину относительно других значений в списке. Если отсортировать список, то ранг числа будет его позицией. Ранг разместим в столбце D ;
• т.к. массив рангов представляет собой массив порядковых чисел 1, 2, 3, 4, …, то будем интерпретировать их как позиции значения в массиве.
• с помощью формулы =ИНДЕКС($A$7:$A$13;D7) получим список, отсортированный в случайном порядке (столбец E ).

В случае наличия в массиве случайных чисел повторов функция РАНГ() вернет для этих величин одно и тоже значение ранга, что приведет к ошибке сортировки. К счастью, эта вероятность крайне мала: для исходного массива, состоящего из 100 элементов, вероятность повтора будет порядка 1,0Е-13 (10 в минус 13 степени). Для визуального контроля повторов можно использовать Условное форматирование (выделить требуемый диапазон, создать правило Условного форматирования Главная/ Стили/ Условное форматирование/ Правила выделения ячеек/ Повторяющиеся значения... ).

Альтернативный вариант без использования функции РАНГ()

Чтобы отсортировать список (диапазон А25:А31 ) в случайном порядке можно действовать немного по-другому:

• каждому значению списка сопоставить случайное число, расположив его в столбце С ;
• отсортировать список случайных величин, например, по возрастанию (в столбце D );
• сопоставляя случайное число значению исходного списка, получить список, отсортированный в случайном порядке (столбец E ).

Единственной трудностью является возможное совпадение случайных величин. Например, если задать диапазон генерации случайных величин, например, от 1 до 10, а необходимо отсортировать список с более чем 100 значениями, то мы получим гарантированные совпадения, что не позволит восстановить исходный массив.

Для того, чтобы получить набор гарантированно не совпадающих случайных значений, можно использовать следующую формулу: =(СЛУЧМЕЖДУ(1;10)&","&СТРОКА(A25))+0 (столбец С )

Отсортировать список случайных величин можно с помощью формулы (столбец D ): =НАИБОЛЬШИЙ($C$25:$C$31;СТРОКА()-СТРОКА($D$24))

И наконец, следующая формула =ИНДЕКС($A$25:$A$31;ПОИСКПОЗ(D25;$C$25:$C$31;0))

ищет значение из отсортированного списка и возвращает номер позиции (функция ПОИСКПОЗ() ), затем по номеру позиции выводится значение из исходного списка (функция ИНДЕКС() ).