Случайные числа без повторов
Предположим, что нам необходимо создать набор целых случайных чисел без повторов в заданном интервале значений. Примеры с ходу:
- генерация уникальных случайных кодов для товаров или пользователей
- назначение людей на задачи (каждому по случайной из списка)
- перестановки слов в поисковом запросе (привет seo-шникам)
- игра в лото и т.д.
Способ 1. Простой
Для начала рассмотрим простой вариант: нам необходимо получить случайный набор из 10 целых чисел от 1 до 10. Использование встроенной в Excel функции СЛУЧМЕЖДУ (RANDBETWEEN) уникальности не гарантирует. Если ввести ее в ячейку листа и скопировать вниз на 10 ячеек, то запросто могут случиться повторы:
Поэтому мы пойдем другим путем.
Во всех версиях Excel есть функция РАНГ (RANG) , предназначенная для ранжирования или, другими словами, определения топовой позиции числа в наборе. Для самого большого числа в списке ранг=1, второе в топе имеет ранг=2 и т.д.
Введем в ячейку А2 функцию СЛЧИС (RAND) без аргументов и скопируем формулу вниз на 10 ячеек. Эта функция сгенерирует нам набор из 10 случайных дробных чисел от 0 до 1:
В соседний столбец введем функцию РАНГ, чтобы определить позицию в рейтинге для каждого полученного случайного числа:
Получим в столбце В то, что хотели — любое нужное количество неповторяющихся случайных целых чисел от 1 до 10.
Чисто теоретически, может возникнуть ситуация, когда СЛЧИС выдаст нам два одинаковых случайных числа в столбце А, их ранги совпадут и мы получим повтор в столбце В. Однако, вероятность такого сценария крайне мала, учитывая тот факт, что точность составляет 15 знаков после запятой.
Способ 2. Сложный
Этот способ чуть сложнее, но использует всего одну формулу массива. Допустим, нам нужно создать на листе список из 9 неповторяющихся случайных целых чисел в интервале от 1 до 50.
Введите в ячейку А2 следующую формулу, нажмите в конце Ctrl+Shift+Enter (чтобы ввести ее как формулу массива!) и скопируйте формулу вниз на требуемое количество ячеек:
Способ 3. Макрос
Ну и, конечно, можно решить задачу с помощью программирования на Visual Basic. В одной из старых статей про случайную выборку я уже приводил макро-функцию массива Lotto, которая выдает требуемое количество случайных неповторяющихся чисел из заданного интервала.
Как заполнить диапазон ячеек случайными числами?
Для тестирования формул, моделирования различных процессов в Excel и решения других задач может использоваться генерация случайных чисел и заполнение диапазона ячеек случайными или рандомными числами. Для генерации случайных чисел, как правило, используется оператор Random, что в переводе с английского означает случайный, произвольный, выборочный.
Стандартные функции Excel
В категории математических функций есть две функции, при помощи которых можно сформировать случайные числа. СЛЧИС не имеет аргументов и возвращает случайное дробное положительное число от 0 до 1. Еще одна функция СЛУЧМЕЖДУ в качестве аргументов имеет нижнюю и верхнюю границу и возвращает случайное целое число, находящееся между указанных границ. Эта функция возвращает как положительные, так и отрицательные числа.
Надстройка «Пакет анализа»
Встроенная в excel надстройка «Пакет анализа» устанавливается через диалоговое окно менеджера надстроек. После установки на вкладке «Данные» ленты Excel появляется новая кнопка «Анализ данных». Одним из инструментов анализа является «Генерация случайных чисел». Подробнее об этом инструменте можно узнать в справке. Этот инструмент применяется для заполнения диапазона случайными числами, извлеченными из одного или нескольких распределений. С помощью этой процедуры можно моделировать объекты, имеющие случайную природу, по известному распределению вероятностей.
Генератор случайных чисел
Кроме стандартных средств в Excel может быть установлена дополнительная надстройка – простой, готовый к использованию генератор случайных чисел, который позволяет быстро заполнять выделенный диапазон ячеек случайными числами в различных вариациях.
Целые числа
В диалоговом окне надстройки указывается адрес выделенного диапазона, минимальное и максимальное значение интервала случайных чисел, после чего нажимается кнопка «Пуск». Значения нижней и верхней границы может быть, как положительным, так и отрицательным.
Целые числа без повторов
Если к вышесказанному добавить установку флажка в поле «Целые числа без повторения значений», то выделенный диапазон ячеек заполняется только уникальными целыми значениями. При этом если количество ячеек в выделенном диапазоне превышает количество уникальных чисел в заданном интервале, то некоторые ячейки могут остаться незаполненными.
Дробные числа с заданным количеством знаков
Для генерации дробных чисел достаточно в соответствующем поле задать количество знаков после запятой. При этом все случайные числа будут иметь заданное, либо меньшее количество значимых разрядов.
Дробные числа с разным количеством знаков после запятой
Кроме всего прочего в надстройке предусмотрена возможность генерации случайных (рандомных) чисел с различным количеством знаков после запятой. Это количество может варьироваться от 0 до 15.
Как создать список со случайными числами в Excel
В таблицах Microsoft Excel часто используется нумерация различного характера, но она не всегда должна быть последовательной. В некоторых случаях юзеру требуется создать список с уникальными числами, который может быть довольно длинным, из-за чего ручной ввод становится проблемой. В программе доступны вспомогательные инструменты, которые помогут автоматизировать этот процесс и создать неограниченное количество случайных чисел.
Способ 1: Использование функции СЛУЧМЕЖДУ
В Excel есть встроенная функция, автоматически выбирающая случайное число из заданного диапазона. Вы можете использовать ее в том случае, если в списке приемлемы повторы. Для генерирования случайных неповторяющихся чисел лучше обратитесь к следующему способу ниже.
Выберите первую клетку списка с числами и объявите формулу =СЛУЧМЕЖДУ().
Введите нижнюю границу (минимальное допустимое число), после которого поставьте точку с запятой.
Введите максимально допустимое число, закройте круглую скобку, если не сделали этого ранее, и нажмите по клавише Enter для применения функции.
Вы увидите, как в ячейке отобразилось случайное число, а это значит, что функция работает исправно.
Теперь ее можно растянуть на все остальные клетки, входящие в список, чтобы получить подходящий для вас результат.
Способ 2: Создание списка со случайными неповторяющимися числами
К сожалению, пока возможности Excel в плане рандомного генератора ограничены описанной выше функцией, и создать список со случайными неповторяющимися числами в пару кликов не получится. Однако можно при помощи вспомогательных инструментов достичь желаемого результата, зная всего пару хитростей.
Создайте первое случайное число в ячейке A2, выполнив операцию сложения с любым для вас шагом. Это может быть +1, +3 или даже +10. То есть каждое следующее число будет больше предыдущего на указанный шаг.
Вы можете растянуть этот список вниз самостоятельно, создав уникальные числа с определенным шагом увеличения.
Однако, если нужно получить больше 100 единиц в перечне, растягивать его не очень удобно. Можно пойти более простым путем. Сначала скопируйте первое значение.
После этого нажмите сочетание клавиш Ctrl + G и в поле «Ссылка» введите номер последней ячейки списка.
Перейдите к ней, зажмите Ctrl + Shift + Стрелка Вверх для выделения всех ячеек до первой.
Первое значение у вас уже скопировано, поэтому остается только вставить саму функцию, используя сочетание клавиш Ctrl + V. Теперь у вас в распоряжении есть большой список с неповторяющимися числами.
Скопируйте весь перечень, откройте контекстное меню, щелкнув по любой ячейке правой кнопкой мыши, и используйте специальную вставку, чтобы вставить только значения. Так вы удалите функцию суммы, которая больше в списке не нужна.
Выделите любую клетку с числом и убедитесь, что функция отсутствует и осталось только значение.
Список с неповторяющимися числами есть, но все они расположены в порядке возрастания. Нужно исправить эту ситуацию, сделав их случайными. В этом поможет инструмент сортировки данных и вспомогательная функция.
Создайте название для текущего списка, а рядом пропишите еще один заголовок.
После этого выделите первую ячейку второго списка и задайте там формулу =СЛЧИС(). Она генерирует случайное нецелое число в ячейке.
Растяните функцию, чтобы она заканчивалась там, где заканчивается последнее число созданного ранее списка.
Выделите первый заголовок и перейдите на вкладку «Данные».
Включите функцию «Фильтр».
Теперь нажмите кнопку со стрелкой, расположенную справа от названия списка с нецелыми числами, и выберите один из вариантов сортировки.
Вместе со вторым столбцом отсортирован и первый – все числа в нем не повторяются и являются случайными.
Удалите ненужные вспомогательные элементы, оставив только готовый перечень. Теперь его можно использовать в личных целях.
Может показаться, что генерирование случайных чисел в Excel занимает много времени. Однако вам понадобится всего пара минут, чтобы ознакомиться с моей инструкцией и реализовать ее самостоятельно. Никаких сложностей и тонкостей нет, поэтому у вас обязательно все получится.
Случайные (рандомные) числа в таблицах «Excel».
Иногда при расчетах необходимо, чтобы в ячейке проставлялось случайное число из выбранного диапазона значений.
Например, при тестировании каких-нибудь методик аналитики данных разрабатываемых Вами нужно, чтобы ячейка принимала значения от 10 до 200.
В таком случае Вам следует воспользоваться функцией Excel «СЛУЧМЕЖДУ( ; )». Эта функция присваивает ячейке рандомное/случайное значение из указанного диапазона значений.
Способ применения функции «СЛУЧМЕЖДУ( ; )»:
- Установить курсор в ячейку, которой присваиваете значение;
- Выбрать функцию «СЛУЧМЕЖДУ( ; )»;
- В меню указать начальное и конечное число диапазона или ячейки, содержащие эти числа;
- Нажать «ОК»
Наряду с функцией «СЛУЧМЕЖДУ» существует «СЛЧИС()», эта функция в отличие от «СЛУЧМЕЖДУ» выбирает случайное число из диапазона от 0 до 1. То есть присваивает ячейке случайное дробное число до единицы.
Способ применения функции «СЛЧИС()»:
- Установить курсор в ячейку, которой присваиваете значение;
- Выбрать функцию «СЛЧИС()»;
- Нажать «ОК»
Добавить комментарий Отменить ответ
Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.