Сортировка в случайном порядке списка ЧИСЛОвых значений в MS 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))

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

Связанные статьи

Похожие задачи
Прочитайте другие статьи, решающие похожие задачи в MS Excel. Это позволит Вам решать широкий класс подобных задач.
Средняя: 4 (4 оценок)

Комментарии

Лион (не проверено)

Благодарю за понятный и оч нужный пример..час искал,пока не нашел Вашу тему!!!!!!