Мне нужно использовать функцию Microsoft Excel RANDBETWEEN, и я знаю, что новый набор случайных чисел генерируется каждый раз, когда вы делаете что-либо еще где-нибудь на рабочем листе. Мне также известны обходные пути для "замораживания" набора случайных чисел, сгенерированного, скажем, для сортировки n списков случайных чисел между a и b. У меня такой вопрос: ПОЧЕМУ меняются случайные числа? Есть ли что-то в алгоритме, что требует этого? FWIW, Google Sheets, LibreOffice Calc, Apple Numbers демонстрируют одинаковое поведение.
5 ответов
28
У Чарльза Уильямса есть хорошее объяснение того, как Excel вычисляет вещи и почему.
http://www.decisionmodels.com/calcsecretsi.htm
По сути, если рабочая книга содержит изменчивые функции (см. Список Чарльза, поскольку изменяющаяся функция изменялась с годами в разных версиях Excel), при изменении какой-либо ячейки в рабочей книге будет произведен пересчет всей рабочей книги (если Excel настроен на автоматический расчет). Если в рабочей книге нет изменчивых функций, будут пересчитаны только те ячейки, на которые повлияло последнее изменение.
Другие здесь заявили, что автоматический расчет всегда вычисляет все в рабочей книге при изменении любой ячейки, но это неправильно. Только изменчивые функции вызывают автоматический пересчет всех ячеек в рабочей книге. Без изменяемых функций Excel пересчитывает только то, что необходимо пересчитать.
Вот почему люди, подобные мне, которые знают разницу, настоятельно советуют по возможности избегать нестабильных функций, таких как OFFSET() или INDIRECT(), в ячейках листа, поскольку сыворотка замедляет работу книги, вызывая полный пересчет после каждого изменения ячейки. Научиться использовать INDEX() вместо OFFSET() может привести к значительному повышению скорости, потому что индекс не является волатильным (см. Статью Чарльза для более подробной информации).
Механизм вычислений Excel основан на философии "recalc or die", которая отличает Excel от конкурирующих продуктов, когда он находился в разработке. Проверьте эту статью: https://www.geekwire.com/2015/recalc-or-die-30-years-later-microsoft-excel-1-0-vets-recount-a-project-that-defied-the -odds/
12
Другие ответы сосредоточены на механике пересчета и роли изменчивых функций, но я думаю, что это упускает часть сути вопроса. В нем рассматриваются правила, которые были применены, но не столько "почему". Я сосредоточусь на этом.
Концепт дизайна
Позвольте мне начать с концепции дизайна и строительных блоков большинства современных электронных таблиц. Есть "разделение ответственности". Функции - это специальные подпрограммы, которые выполняют определенную задачу и выполняют ее каждый раз, когда их просят. Помимо этого, приложение электронной таблицы контролирует, когда их спрашивают.
Сами функции не содержат никакой логики для пересчета или не пересчета. Природа всех функций Excel заключается в выполнении какого-либо типа операции при каждом запуске. Ни одна функция не имеет собственной "памяти" своего текущего значения или истории своих прошлых вычислений. У него нет возможности узнать, впервые ли его просят выполнить свою работу. Так что ни одна нативная функция не может запускаться только один раз. Если какая-либо функция будет пересчитана, она выполнит то, для чего предназначена, и выдаст новое значение.
Приложение для работы с электронными таблицами содержит некоторый интеллект, который позволяет сэкономить время, пропуская ненужные пересчеты (как описано в ответе Тейлина). Единственный момент пересчета времени пропускается, если приложение знает, что значение функции не было затронуто изменениями электронной таблицы, которые вызвали необходимость пересчета вещей.
Так что, если вам нужно запустить функцию один раз, а затем сохранить ее значение? Возьмите пример создания случайного числа, а затем результат не изменится. Это описывает создание случайно сгенерированных констант, и вы можете сделать это с помощью Excel.
Excel не может знать, как вы хотите использовать его возможности; хотите ли вы продолжать создавать новые значения или сохранить последний набор. Каждый вариант - это вариант использования, который поддерживает Excel. Excel учитывает оба случая, предоставляя вам инструменты для расчета новых значений и инструменты для сохранения старых значений. Вы можете строить все, что хотите. Но пользователь обязан заставить его делать то, что он хочет.
Функциональная логика
Итак, давайте посмотрим на логику того, что делает функция. Может быть, логика пересчета имеет смысл пропустить случайную функцию, потому что на ее значение не должны влиять другие изменения в электронной таблице?
Изменяется ли значение функции при пересчете, зависит от ее цели и на чем она основана. Расчет постоянных значений всегда будет давать один и тот же результат. Операция, основанная на значениях ячеек, которые не изменились, даст тот же результат.
Тем не менее, некоторые функции разрабатываются с целью и каждый раз производить разные результаты. Рассмотрим, например, функции, основанные на текущем времени. Они будут давать новый результат, основанный на времени пересчета. У вас не может быть функции, цель которой - создать значение на основе текущего времени и не обновлять его при пересчете.
Цель контроля пересчета состоит в том, чтобы всегда делать все, чтобы отразить текущее состояние вещей при запуске пересчета. Этот критерий не будет соблюден, если функции, основанные на текущем времени, не будут обновлены.
"Случайные" функции, такие как RANDBETWEEN, имеют целью создание нового случайного числа при пересчете. Это то, что они должны делать. Вы можете утверждать, что пересчет может быть пропущен, поскольку на значение не должны влиять другие события, происходящие в электронной таблице.
Если бы это было поведение по умолчанию, вы бы обновляли электронную таблицу, но случайное значение оставалось бы постоянным. Это не очень случайное поведение. Это будет поддерживать один вариант использования, но не другой. Возвращаясь к базовой концепции дизайна, она обрабатывается как любая другая функция. Поведение по умолчанию - Excel пересчитывает его. Если вы хотите сохранить предыдущее значение для своего варианта использования, это зависит от вас, используя доступные инструменты.
2
Каждое изменение в рабочем листе запускает автоматический пересчет всех формул, что является поведением по умолчанию.
Вы можете отключить его или заморозить фактическое значение, преобразовав его в чистое значение (в противоположность формуле).
1
Он классифицируется как "изменчивая" формула и как таковая является частью списка функций, пересчитанного при обнаружении изменения или введении новой функции.
Другим вариантом, который следует рассмотреть, является переключение листа на ручной расчет, чтобы вы могли контролировать, когда это происходит - просто убедитесь, что вы не забыли пересчитать, хотя любые изменения в значениях или формулах не приведут к пересчету ...
0
Несмотря на героические усилия, направленные на то, чтобы это звучало как особенность, а не ошибка, я считаю крайне маловероятным, чтобы такое поведение обычно было выгодным. "Особенности" делают нашу жизнь проще, а "жуки" - сложнее. В моем случае ввод метки или объяснения в ячейку, на которую не ссылается никакая другая ячейка, вызывает повторную рандомизацию, с которой неудобно иметь дело. Я бы назвал это "ошибкой".
Да, я могу контролировать пересчет вручную (боль) или вставлять значения, чтобы предотвратить его (опять же, боль), но в каких случаях повторная рандомизация, вызванная вводом текста в удаленную область листа, облегчает жизнь?
Даже если за обработкой "изменчивых" функций лежит какая-то логика, может показаться, что тривиально легко инициировать пересчет с помощью соответствующих критериев, кроме простого нажатия "ввод" после ввода текста в несвязанных и не связанных ссылках ячейках или других совершенно не связанных изменений. Предотвращение этого должно быть по умолчанию, и я не могу себе представить, что это будет трудно реализовать. Если кто-то хочет эту "функцию", он может включить ее, но я не могу себе представить, когда это будет хорошо. В лучшем случае это может быть относительно безвредным. Для меня это большая проблема из-за типа листа, который я создаю.
Наконец, пакет инструментов анализа позволяет создавать распределения случайных чисел различными способами, а созданные таким образом диапазоны чисел вообще не пересчитываются (повторно рандомизируются), если только вы не скажете это сделать. Поэтому я рекомендую людям использовать Пакет инструментов анализа, когда он соответствует их потребностям.