Как предотвратить изменение случайных чисел в Excel

Обновлено: 04.07.2024

Я запускаю случайную выборку, присваивая номера данных и запуская СЛУЧМЕЖДУ для случайного выбора данных. Единственная проблема заключается в том, что после запуска RANDBETWEEN значения меняются всякий раз, когда в электронной таблице выполняется какая-либо работа.

Как сделать значения постоянными, чтобы предотвратить их изменение?

Эта тема заблокирована. Вы можете подписаться на вопрос или проголосовать за него как полезный, но вы не можете отвечать в этой теме.

Сообщить о нарушении

Домогательство – это любое поведение, направленное на то, чтобы побеспокоить или расстроить человека или группу людей. К угрозам относятся любые угрозы самоубийства, насилия или причинения вреда другому человеку. Любой контент на тему для взрослых или неуместный для веб-сайта сообщества. Любое изображение, ссылка или обсуждение наготы. Любое поведение, которое является оскорбительным, грубым, вульгарным, оскверняет или демонстрирует неуважение. Любое поведение, которое может нарушать лицензионные соглашения с конечными пользователями, включая предоставление ключей продукта или ссылок на пиратское программное обеспечение. Незапрашиваемая массовая рассылка или массовая реклама. Любые ссылки на вирусы, шпионское ПО, вредоносное ПО или фишинговые сайты или их пропаганда. Любой другой неприемлемый контент или поведение, как это определено Условиями использования или Кодексом поведения. Любое изображение, ссылка или обсуждение, связанные с детской порнографией, детской наготой или другим жестоким обращением с детьми или их эксплуатацией.

Я запускаю случайную выборку, назначая номера данных и запуская RANDBETWEEN для случайного выбора данных. Единственная проблема заключается в том, что после запуска RANDBETWEEN значения меняются всякий раз, когда в электронной таблице выполняется какая-либо работа. Как сделать значения постоянными, чтобы предотвратить их изменение?

Хотя я сам часто использую метод копирования и вставки специального значения, это может быть неудобно, если у вас много таких формул и вы хотите иметь возможность периодически легко генерировать новые случайные данные.

В качестве альтернативы можно спрятать вызов RANDBETWEEN в энергонезависимой определяемой пользователем функции VBA. Например:

Функция myRANDBETWEEN(lo As Double, hi As Double) As Double
myRANDBETWEEN = Evaluate("randbetween(" & lo & "," & hi & ")")
Конечная функция

Вы используете myRANDBETWEEN точно так же, как использовали бы RANDBETWEEN, например. =мойСЛУЧМЕЖДУ(1,1000).

Нажмите ctrl+alt+F9, чтобы сгенерировать новый набор случайных данных и пересчитать всю книгу (фактически все книги открываются в одном экземпляре Excel).

Предупреждение: myRANDBETWEEN работает значительно медленнее, чем RANDBETWEEN.

Чтобы создать описанную выше пользовательскую функцию, выполните следующие действия:

В Excel нажмите клавиши ALT+F11, чтобы открыть окно VBA.
В VBA нажмите «Вставить», затем «Модуль», чтобы открыть панель редактирования VBA.
Скопируйте текст UDF выше и вставьте его в панель редактирования VBA.
Теперь вы можете закрыть окно VBA.

Сообщить о нарушении

Функция Excel СЛУЧМЕЖДУ возвращает случайное целое число между двумя заданными числами. СЛУЧМЕЖДУ пересчитывает каждый раз, когда рабочий лист открывается или изменяется.

bottom — целое число, представляющее нижнее значение диапазона.
top — целое число, представляющее верхнее значение диапазона.

Функция СЛУЧМЕЖДУ возвращает случайное целое число между двумя числами. Результат СЛУЧМЕЖДУ является автоматическим, и новое случайное число будет пересчитываться каждый раз, когда рабочий лист открывается или изменяется.

Функция СЛУЧМЕЖДУ принимает два аргумента: нижний и верхний. Нижний представляет собой нижнюю границу случайного числа, а верхний представляет верхнюю границу. RANDBETWEEN включает как верхнее, так и нижнее значения в диапазоне целых чисел, которые могут быть возвращены.

Примеры

Ниже приведены основные примеры формул СЛУЧМЕЖДУ:

Несколько результатов

Чтобы сгенерировать несколько случайных чисел в нескольких ячейках, выберите целевые ячейки, введите функцию СЛУЧМЕЖДУ и нажмите Control + Enter, чтобы ввести одну и ту же формулу во все ячейки в один раз.

Статические результаты

СЛУЧМЕЖДУ возвращает новое случайное значение каждый раз при пересчете листа, включая изменения, внесенные в несвязанные ячейки в той же книге. Чтобы предотвратить изменение случайных чисел, скопируйте ячейки, содержащие СЛУЧМЕЖДУ, в буфер обмена, затем используйте Специальная вставка > Значения для преобразования в текст. Чтобы получить одно случайное число, которое не изменяется, введите СЛУЧМЕЖДУ в строке формул, нажмите F9, чтобы преобразовать формулу в статический результат, и нажмите клавишу ВВОД, чтобы ввести значение в ячейку.

Примечание. В Excel 365 функция СЛУЧАЙСЧИСЛ представляет собой более гибкую альтернативу. RANDARRAY может генерировать случайные десятичные числа и случайные целые числа, а также может возвращать более одного случайного значения одновременно.

Если у вас есть список случайных чисел в Excel, сгенерированный функцией СЛУЧАЙ или СЛУЧМЕЖДУ, каждый раз, когда вы редактируете какое-либо значение в книге, список автоматически обновляется новыми случайными числами. Чтобы остановить это и заморозить случайные числа, вы можете скопировать и вставить их как значения или установить расчет формул вручную.

Заморозить случайные числа

Вставить значения

Скажем, у вас есть следующий список случайных чисел в диапазоне B2:B9, созданный с помощью функции СЛУЧМЕЖДУ.

Если вы измените какое-либо значение в книге, список чисел будет создан заново.

Чтобы зафиксировать случайные числа, выполните следующие действия:

Выделите диапазон ячеек со случайными числами (B2:B9), щелкните правой кнопкой мыши в любом месте выделенной области и выберите "Копировать" (или используйте сочетание клавиш CTRL + C).

Щелкните правой кнопкой мыши первую ячейку в диапазоне (B2) и выберите "Значения" в параметрах вставки.

В результате диапазон теперь представляет собой список чисел без формулы. Если вы измените любую ячейку на листе, значения в столбце B не изменятся.

Изменить параметры расчета

В Excel для вычисления формул задан автоматический режим, что означает, что при изменении значения в книге все формулы пересчитываются. Чтобы предотвратить это и рассчитывать формулы только тогда, когда вы хотите, установите параметры расчета вручную. Таким образом, вы также заморозите свой список случайных чисел. Для этого на ленте выберите «Формулы» > «Параметры расчета».

Теперь, несмотря на то, что в столбце B есть формула СЛУЧМЕЖДУ, значения обновляются только тогда, когда вы нажимаете кнопку "Рассчитать сейчас" на вкладке "Формулы" или устанавливаете для параметров расчета значение "Автоматически".

См. также…

Заморозить случайные числа в Google Таблицах

В Google Таблицах вы можете использовать параметр Вставить значения, чтобы зафиксировать случайные числа способом, описанным выше, но невозможно установить вычисление формулы вручную.

Возможно, вы пытались использовать Excel для генерации случайного числа для розыгрыша.

Это легко. Например, если вы хотите сгенерировать случайное число, вы можете использовать функцию СЛУЧМЕЖДУ.

Это даст вам случайное число от 1 до 1000. Однако СЛУЧМЕЖДУ пересчитывает всякий раз, когда на листе происходит обновление. Другими словами, он продолжает давать вам новое значение, когда вы работаете на одном и том же рабочем листе. Такова природа «Случайного», но вы не хотите, чтобы это было удачным розыгрышем. Так как вы хотите «заморозить» результат розыгрыша.

Возможно, вам сразу придет в голову Копировать и вставить значение…. НО это не работает 😦

Пересчет происходит перед вставкой значения…

Не беспокойтесь! Существует трюк с мышью, позволяющий выполнить действие «Вставить значение» без запуска пересчета. Смотрите это:

В чем хитрость?

Выберите диапазон ячеек.
Переместите курсор к границе выбранного диапазона.
Когда появится крестик со стрелкой, нажмите правой кнопкой мыши и УДЕРЖИВАЙТЕ.
Перетащите его в другое место, а затем обратно.
Отпустите правую кнопку мыши
Выберите «Копировать сюда только как значения».
ГОТОВО

Короче говоря, перетащите его, щелкнув правой кнопкой мыши, а затем выберите «Копировать сюда только как значения».

Так просто. 🙂

Вы можете посмотреть его на моем канале YouTube:

Оцените это:

Вот так:

Связанные

О МФ

Знаток Excel, который только что перешел на должность, связанную с анализом данных в текущей компании. 😊 В последнее время влюбился в Power Query и Power BI.😍 Продолжайте изучать новые возможности Excel и Power BI и удивляйтесь новым открытиям.

Эта запись была опубликована в Советы по Excel с пометками Копировать и вставить, Советы по работе с мышью, Вставить значение. Добавьте постоянную ссылку в закладки.

6 ответов на вопрос Заморозить случайный результат с помощью трюка с мышью

Я не знаю, что делает ваш Excel и/или файл, но я попытался воссоздать поведение пересчета перед вставкой. Я делал это десять раз подряд, используя как клавиатуру (мой предпочтительный метод), так и мышь, и вставляемые значения никогда не отличались от скопированных. Я также вел еженедельную группу пользователей Excel в течение последних 3 с лишним лет, и я использовал этот подход RANDBETWEEN сотни раз. Я ни разу не видел, чтобы вставленные значения отличались от скопированных.

Здравствуйте, Дэвид! Я полагаю, что вы используете Excel 2016 или более позднюю версию… проблема с пересчетом решена «частично»… но не полностью. Это что-то странное.
Попробуйте это…
Скопируйте диапазон ячеек с помощью Randbetween
Щелкните правой кнопкой мыши, чтобы открыть контекстное меню
Наведите указатель мыши на значок вставки значения (но не нажимайте)
Подождите немного
Затем наведите указатель мыши на обычный значок Вставить
К настоящему моменту… вы увидите пересчитанные результаты
Поскольку проблема пересчета возникает независимо от того, какой метод вы делаете
Вот что Я испытал.
Сообщите мне, если найдете то же самое.

Нет, я все еще использую версию 2013 года. Я попробовал шаги, которые вы предложили, и это не имело значения. Я могу делать все с помощью мыши, наводя курсор на значения вставки, затем вставляя, и даже вперед и назад с задержкой в 30-60-90 секунд, и все равно вставляются те же значения, которые были скопированы. Или, если я использую клавиатуру, я могу копировать и ждать те же самые длительные периоды перед вставкой значений, и это все еще работает.

И я никогда не видел того, что вы описываете, в версиях 2007 и 2003 годов. Прежде чем стать ведущим еженедельника, о котором я упоминал, я много лет был его соведущим. Таким образом, я регулярно использую трюк RAND и RANDBETWEEN со вставкой значений уже более десяти лет в трех версиях Excel и никогда не видел его, ни разу. Это почти похоже на что-то из настройки, надстройки или процедуры события VBA на вашем компьютере, которая вызывает это нестандартное поведение.

Спасибо за подробности, Дэвид.
Это очень интересно. В моем случае все ровно наоборот. Я перешел на Excel 2016 с 2010 года… не имел опыта работы с Excel 2013. Когда я впервые использовал Excel 2016, я думал, что проблема решена, так как я могу вставить значение, полученное из Randbetween, без использования этого трюка с мышью… пока однажды я не обнаружил, что это произошло опять таки. С некоторыми пробами и ошибками я нашел способ вызвать это странное поведение. Поэтому я продолжаю использовать этот трюк с мышью, если хочу, чтобы генерировались именно случайные значения.
Я сталкивался с этим «странным» поведением пересчета в течение многих лет, когда использовал Excel 2010 или более раннюю версию. Ни в одном случае я не мог сохранить свои случайные значения.
Не уверен, в чем разница… кто-нибудь еще сталкивался с этим? Пожалуйста, оставьте свои комментарии
Аррр… просто чтобы подтвердить, что вы не отключили автоматический расчет, верно?

Нет, я никогда не работаю в ручном режиме расчета, если могу. Возможно ли, что у вас есть «личная» рабочая книга или надстройка, которая вызывает одну из этих процедур обработки событий и может содержать код, вызывающий неожиданный перерасчет?

Workbook_SheetBeforeRightClick
Workbook_SheetSelectionChange
Workbook_SheetChange
Workbook_SheetCalculate

Worksheet_BeforeRightClick
Worksheet_SelectionChange
Worksheet_Change
Worksheet_Calculate

Читайте также: