Сортування у випадковому порядку списку числових значень у 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))

шукає значення із відсортованого списку та повертає номер позиції (функція ПОШУКПОЗ() ), потім за номером позиції виводиться значення із вихідного списку (функція ІНДЕКС() ).