Постановка проблеми
Припустимо, що нам потрібно створити набір цілих випадкових чисел без повторень у заданому діапазоні значень. Приклади на ходу:
- генерування унікальних випадкових кодів для продуктів або користувачів
- призначення людей на завдання (кожне випадковим чином зі списку)
- перестановка слів в пошуковому запиті (привіт seo-шникам)
- грати в лото тощо.
Спосіб 1. Простий
Для початку розглянемо простий варіант: нам потрібно отримати випадковий набір з 10 цілих чисел від 1 до 10. За допомогою вбудованої в Excel функції МІЖ СПРАВОЮ (КРАЙ МІЖ) унікальність не гарантується. Якщо ви введете його в комірку аркуша і скопіюєте на 10 клітинок вниз, то легко можуть статися повторення:
Тому підемо іншим шляхом.
У всіх версіях Excel є функція RANK (RANG), призначений для ранжування або, іншими словами, визначення верхньої позиції числа в наборі. Найбільше число в списку має ранг=1, друге вгорі має ранг=2 і так далі.
Давайте введемо функцію в клітинку A2 СЛЧИС (RAND) без аргументів і скопіювати формулу на 10 клітинок вниз. Ця функція згенерує нам набір із 10 випадкових дробових чисел від 0 до 1:
У наступному стовпчику введемо функцію RANKвизначити позицію в рейтингу для кожного отриманого випадкового числа:
Ми отримуємо в стовпці B те, що хотіли – будь-яку бажану кількість неповторюваних випадкових цілих чисел від 1 до 10.
Чисто теоретично може виникнути ситуація, коли СЛЧИС дасть нам два однакових випадкових числа в стовпці A, їхні ранги збігаються, і ми отримаємо повторення в стовпці B. Проте ймовірність такого сценарію надзвичайно мала, враховуючи той факт, що точність становить 15 знаків після коми.
Спосіб 2. Складний
Цей метод трохи складніший, але використовує лише одну формулу масиву. Припустимо, нам потрібно створити список із 9 неповторюваних випадкових цілих чисел у діапазоні від 1 до 50 на аркуші.
Введіть наступну формулу в комірку A2, клацніть у кінці Ctrl + Shift + Enter (щоб ввести її як формулу масиву!) і скопіюйте формулу до потрібної кількості клітинок:
Спосіб 3. Макрос
І, звичайно, вирішити проблему можна за допомогою програмування на Visual Basic. В одній із старих статей про випадкову вибірку я вже цитував макрофункцію масиву Lotto, яка видає необхідну кількість випадкових неповторюваних чисел із заданого інтервалу.
- Як підрахувати кількість унікальних значень у діапазоні
- Випадковий вибір елементів зі списку